Die Excel-Funktionen INDEX und VERGLEICH sind sich auf den ersten Blick ähnlich und wirken eher langweilig. Die Kombination beider Funktionen sorgt allerdings dafür, dass ihr in komplexen Tabellen blitzschnell und bequem Werte finden könnt. GIGA erklärt euch, wie ihr euch eine „Excel-Suchmaschine“ baut.
Die Excel-Funktion INDEX bekommt als Eingabe einen Bereich, in dem sie suchen soll, dann die Adresse einer Zeile und einer Spalte. Als Ergebnis zeigt sie den Wert dieser Adresse an.
VERGLEICH funktioniert im Grunde genau andersherum: Sie nimmt einen angegebenen Suchbereich und wenn man ihr dann den Inhalt einer Adresse nennt, gibt sie die Zeile oder Spalte aus, in der dieser Inhalt steht.
Wenn ihr diese beiden Funktionen geschickt kombiniert, dann könnt ihr zum Beispiel auch in riesigen Waren-Tabellen schnell herausfinden, wie viele braune Schuhe in Größe 43 noch auf Lager sind, oder welchen Umsatz die Angestellte Müller im Juni gemacht hat.
Was macht die Funktion INDEX?
Tatsächlich ist die Excel-Funktion INDEX nur ein Basiswerkzeug für andere Berechnungen. Grob gesagt tut sie nichts, was man nicht genauso schnell selbst nachsehen könnte: Man wählt eine Zeile und eine Spalte und will den Wert an der Kreuzung der beiden Linien haben. Also gibt man Zeile und/oder Spalte ein, um das Ergebnis angezeigt zu bekommen.
Ihr könnt INDEX aber auch so einsetzen, dass ihr den Suchbereich auf eine einzelne Spalte oder Zeile begrenzt. Wenn ihr etwa eine Spalte voller Namen habt und dann einen bestimmten Namen aussucht, gibt euch INDEX die Zeile an, in der er zu finden ist.
Oder anders gesagt: Die Funktion INDEX bekommt eine Position genannt und Excel gibt dann den Wert an dieser Stelle aus.
So wird die Excel-Funktion INDEX aufgebaut:
=INDEX(Bereich;Zeile;Spalte)
- Den Bereich legt ihr selbst fest. Er kann sich über die gesamte Tabelle erstrecken, oder auch nur über die Werte einer Spalte beziehungsweise Zeile. Es kommt darauf an, welche Absicht ihr mit dem Ergebniswert habt.
- Die Zeile und die Spalte befinden sich innerhalb des gewählten Bereichs und kennzeichnen genau eine Zelle der Tabelle.
- Ihr müsst die Zeile und die Spalte an einer Stelle der Tabelle angeben, um mit INDEX den Inhalt dieser Zelle ausgegeben zu bekommen.
Ein Beispiel dafür seht ihr in dieser einfachen Anwendung:
In diesem Beispiel erstreckt sich also der Bereich von Zelle A1 bis Zelle D4 – das entspricht dem Bereich A1:D4.
Die Formel für die Funktion lautet ganz einfach =INDEX(A1:D4;B6;B7)
Das bedeutet:
- Der Bereich erstreckt sich von A1 bis D4.
- In Zelle B6 gibt man den Wert für die Zeile ein.
- In Zelle B7 soll der Wert für die Spalte eingetragen werden.
Nur im gewählten Bereich gibt die Tabelle korrekte Werte aus. Die Zeilen werden dabei wie immer in Zahlen ausgedrückt. Bei den Spalten werden nun – statt der Buchstaben – ebenfalls Zahlen zur Positionsbestimmung gewählt.
Was macht die Funktion VERGLEICH?
Die Funktion VERGLEICH ist folgendermaßen aufgebaut:
=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)
- Das Suchkriterium ist ein Wert innerhalb eines ausgewählten Bereichs, den ihr eingeben oder auch aus einem Pull-Down-Menü auswählen könnt.
- Die Suchmatrix ist der ausgewählte Bereich, innerhalb dem dieser Wert zu finden ist.
- Der Vergleichstyp bestimmt welche Art von Vergleich durchgeführt wird. In unserem Beispiel wählen wir 0 (Null), damit der Wert genau übereinstimmt.
VERGLEICH gibt nur genau einen Zahlenwert aus. Das kann eine Zeile oder eine Spalte sein und hängt davon ab, was man als Suchmatrix ausgewählt hat. Wenn ihr eine Spalte markiert habt, sucht VERGLEICH nur in dieser Spalte und gibt aus, in welcher Zeile das Ergebnis steht.
Habt ihr eine Zeile markiert, sucht VERGLEICH in dieser Zeile und gibt eine Spaltennummer an. Die Zählung beginnt in beiden Fällen mit der 1 beim ersten ausgewählten Wert.
Beispiel: Ihr habt eine Tabelle, in deren erster Spalte eine lange Liste von Namen steht. Wenn ihr nun mit VERGLEICH zum Beispiel nach „Fritz Fuchs“ sucht, wird die Funktion die Zeile in dieser Spalte ausgeben, in der dieser Name steht.
Was auf den ersten Blick noch sinnlos erscheint, bekommt mehr Bedeutung, wenn man VERGLEICH mehrmals nutzt und das Ergebnis mit INDEX kombiniert.
Hier seht ihr eine einfache Anwendung der Funktion VERGLEICH:
In Excel geschickt mit INDEX und VERGLEICH suchen und finden
- INDEX gibt den Inhalt einer Zelle aus, wenn man der Funktion eine Zeile und eine Spalte übergibt.
- VERGLEICH gibt die Zeile oder Spalte eines Wertes aus, wenn die Funktion diesen Wert im angegebenen Suchbereich findet.
Das lässt sich folgendermaßen kombinieren, wie ihr an einem ganz einfachen Beispiel sehen könnt.
- In der ersten Spalte stehen eine Reihe von Namen.
- In der ersten Zeile stehen Monate.
- Jedem Namen sind in jedem Monat Umsatzwerte zugeordnet.
Das ist nur in simples Beispiel mit wenigen Werten. Natürlich könnte man hier schnell selbst nachgucken, welchen Umsatz der Angestellte Klaasen im Monat Juni (6) gemacht hat. Bei 100 oder mehr Angestellten sind diese Funktionen schon hilfreich.
Pull-Down-Auswahl in Excel anlegen:
Um den gesuchten Namen nicht eintippen zu müssen, haben wir die Zelle A15 in ein Pull-Down-Menü umgewandelt, in dem man die Namen aussuchen kann. Das geht so:
- Klickt in die Zelle, die das Pull-Down-Menü enthalten soll.
- Oben im Menüband klickt auf „Daten“.
- Im Abschnitt „Datentools“ findet ihr das Icon „Datenüberprüfung“ aus dem obigen Bild – klickt darauf.
- Es öffnet sich ein Fenster. Wählt im Bereich „Einstellungen“ unter „Zulassen“ die Option „Liste“.
- Anschließend setzt den Cursor in das Feld „Quelle“.
- Klickt nun in der Tabelle auf den ersten Namen, haltet die Maustaste gedrückt und zieht die Maus bis zum letzten Namen. In „Quelle“ steht nun der ausgewählte Bereich.
- Bestätigt die Auswahl mit einem Klick auf „OK“.
Jetzt könnt ihr neben der Zelle auf den kleinen Pfeil klicken und den gesuchten Namen auswählen.
Nun kombiniert ihr INDEX und VERGLEICH, um am Ende den Monatsumsatz jedes Mitarbeiters ansehen zu können.
Die Aufgabe lautet wie folgt:
Wenn man den Namen eines Angestellten auswählt und den gesuchten Monat als Zahl eingibt, soll sein Umsatz in diesem Monat angezeigt werden.
Dazu braucht man:
- Ein Pull-Down-Menü mit der Liste der Namen.
- Ein Feld, das die Zeile ausgibt, in der dieser Name steht. (Den braucht man später für die Adresse des Umsatzes.)
- Ein Feld, in das man den gesuchten Monat als Zahl eingibt (1 – 12).
- Und ein Ergebnisfeld, in dem die Zeile des Namens und die Spalte des Monats zusammengefasst werden, um damit den Umsatz-Wert in der Zelle anzuzeigen.
In der Tabelle zu sehen:
- Spalte A2 bis Spalte A9: Die Namen der Angestellten
- Zeile B1 bis Zeile M1: Die Monate
- Neben jedem Namen die jeweiligen Monatsumsätze (Bereich B2 bis M9)
- In Zelle A15 das Pull-Down mit den Namen
- In Zelle B15 (Überschrift „Nummer“) ein Feld für die Zeilennummer des Namens
- In Zelle C15 ein Eingabefeld für den Monat
- In Zelle D15 (Überschrift „Umsatz“) das Ergebnis
Das Namensfeld ist ein Auswahlfeld. Wird ein Name ausgewählt, erscheint daneben die Zeilennummer. Damit das klappt, müsst ihr zum ersten Mal die Funktion VERGLEICH einsetzen:
=VERGLEICH(A15;A2:A9;0)
Lautet übersetzt:
Führe einen Vergleich aus, indem du den Wert im Auswahlfeld A15 mit dem Bereich von A2 bis A9 vergleichst und dann angibst, wo der Wert das erste Mal vorkommt. Die „0“ am Ende bedeutet, dass es eine exakte Übereinstimmung sein muss.
Im nächsten Feld gebt ihr von Hand die Zahl des Monats ein.
Das Ergebnisfeld nutzt schließlich die Funktion INDEX, indem es den Ausgabewert aus dem Bereich B2 bis M9 (die Umsatzzahlen) heranzieht und dann die Werte aus B15 (Zeile) und C15 (Monat) nimmt, um auszugeben, welcher Wert sich an der Schnittstelle befindet.
Die Funktion in diesem Feld lautet:
=INDEX(B2:M9;B15;C15)
In einer umfangreichen Liste und mit alphabetisch geordneten Namen ist das natürlich noch sinnvoller. Außerdem könnt ihr zur Bequemlichkeit auch die Monate als Auswahlfeld gestalten und eine VERGLEICH-Funktion einfügen, die aus der Monatsauswahl eine Spaltenzahl generiert.
Ein etwas umfangreicheres Beispiel seht ihr hier. Dort werden aus einer Kleidungsdatenbank mit Größen und Lagerbeständen die verfügbaren Mengen herausgesucht.
