Excel VERGLEICH Funktion
VERGLEICH
Die VERGLEICH-Funktion ist insofern eine tolle Funktion, als du sie in Kombination mit anderen Funktionen – zum Beispiel mit der INDEX-Funktion, der SVERWEIS-Funktion oder der INDIREKT-Funktion – verwenden kannst.
Bei Formeln mit der SVERWEIS-Funktion bist du grundsätzlich mit der Aufgabe konfrontiert, die einzelnen Spalten eines abzufragenden Bereichs manuell durchzuzählen, damit du die entsprechende Zahl (also die Spalte Nr. X) in deine Funktion einbauen kannst. Dies bedeutet für dich, dass du ganz genau abzählen musst, an welcher Position innerhalb eines zuvor definierten Suchbereichs sich eine konkrete Spalte befindet, auf die sich deine Abfrage in der SVERWEIS-Formel bezieht, damit du im Endeffekt auch wirklich jenes Ergebnis bekommst, dass du erhalten möchtest. Diese Art der Vorgehensweise ist ausgesprochen umständlich und zeitaufwendig, denn du musst im Falle der Erstellung von mehreren Formeln – selbst, wenn diese vom Ansatz her identisch sind – immer wieder durchzählen, falls es sich dabei um unterschiedliche Spalten handelt, auf die sich deine Abfragen beziehen.
Während sich das Abzählen bei kleinen Tabellen mit wenigen Spalten zwar zeitaufwendig, aber doch noch relativ einfach gestaltet, ist das Bestimmen der exakten Position einer Spalte in einer Tabelle mit mehreren Dutzend oder 80, 100 und mehr Spalten eigentlich vom Zeitaufwand des wiederholten Abzählens nicht mehr ökonomisch. Hier kann die VERGLEICH-Funktion Abhilfe schaffen und dir sehr viel Zeit sparen, denn diese findet für dich die genaue Position der gesuchten Spalte, das heißt, sie erledigt für dich das Abzählen, dass du ansonsten selbst manuell durchführen müsstest.
In unserem Beispiel wollen wir herausfinden, in welcher Spalte sich die Umsätze befinden. Gesucht ist also die Nummer jener Spalte, in der sich der Wert „Umsatz“ oben im Spaltenkopf befindet.
Dazu gibst du folgende Formel ein:
=VERGLEICH(F7;A1:D1;0)
Die Formel =VERGLEICH sucht also nach jenem Wert, der sich in der Zelle F7 befindet (= „Umsatz“), wobei in unserem Fall innerhalb der Zellen der obersten/ersten Zeile im Bereich der Spalten A bis D – also A1:D1 – gesucht werden muss. Dabei wollen wir, da es sich um ein Wort handelt, eine genaue Übereinstimmung haben, also wählst du aus den drei vorgeschlagenen Optionen 0 aus.
Als Ergebnis bekommst du den Wert „4“, denn die Spalte mit den Umsätzen ist die Spalte Nummer 4 innerhalb des Suchbereichs von A bis D.
Würdest du in die Zelle F7 „ProduktID“ oder „Anzahl“ oder „Preis“ hineinschreiben, dann würdest du in Folge die entsprechenden Ergebnisse „1“ oder „2“ oder „3“ ausgeworfen bekommen.
Die VERGLEICH-Funktion kann jedoch nicht nur für horizontale Abfragen eingesetzt werden, sondern selbstverständlich auch für vertikale. Die Formel der für das im Video [2:00–2:20] angesprochene Beispiel lautet somit wie folgt:
=VERGLEICH(F7;A1:A11;0)
Das ausgeworfene Ergebnis ist „7“, da der gesuchte Wert in der 7. Zeile von oben (Zelle A7) steht.
Da der gesuchte Wert nicht im Spaltenkopf steht, könntest du hier den Suchbereich auf die Zeilen 2 bis 11 einschränken und die Formel dementsprechend adaptieren:
=VERGLEICHF7;A2:A11;0)
Das ausgeworfene Ergebnis lautet jetzt „6“, weil sich der Wert „62-74“ in diesem Fall in der sechsten Zeile (ausgehend von der zweiten Zeile von oben) befindet, denn die Zeile 1 wird ja nicht mehr mitgerechnet.
Ergänzender Hinweis: Wenn du nicht mit einer Referenzzelle arbeitest (in unserem Beispiel F7), sondern den gesuchten Wert direkt in die Formel eingeben möchtest, musst du berücksichtigen, dass bei „62-47“ ein Minuszeichen steht, das von Excel gewissermaßen als Rechenoperation interpretiert wird. Aus diesem Grund musst du in einem solchen Fall den gesuchten Wert zwischen Anführungszeichen setzen.
Solltest du darauf vergessen haben, bekommst du die Fehlermeldung #NV ausgeworfen.
Das Gleiche gilt übrigens auch für den Fall, dass du nicht – wie im allerersten Beispiel beschrieben – den gesuchten Wert in die Zelle F7, sondern direkt in die Formel hineingeschrieben und dabei „Umsatz“ nicht unter Anführungszeichen gesetzt hast. Allerdings erscheint diesmal die Fehlermeldung #NAME? in der Zelle mit der Formel.
INDEX und VERGLEICH
Als Nächstes sehen wir uns an, wie wir die INDEX-Funktion in Verbindung mit der VERGLEICH-Funktion verwenden können. Mit der INDEX-Funktion kannst du einen Wert abhängig von der Position innerhalb eines ausgewählten Bereichs bestimmen. Solltest du diese Funktion noch nicht kennen oder möchtest du dir die genaue Verwendung noch einmal in Erinnerung rufen, dann gehe zum Video.
Bei der INDEX-Funktion musst du bestimmte Koordinaten (Zeile und Spalte) eingeben und bekommst anschließend den entsprechenden Wert, der sich in der betreffenden Zelle befindet. In der Praxis erweist sich das oftmals nicht als praktikabel, denn vor allem bei größeren Tabellen mit einigen Dutzend oder hundert und mehr Spalten und Hunderten oder gar Tausenden Zeilen wird man in der Regel weder 1) die Spalten absuchen, um ein gewisses Kriterium zu finden, noch 2) die Zellen innerhalb dieser Tabelle absuchen, bis man die gesuchte Zelle gefunden hat, um den darin enthaltenen Wert zu eruieren.
Die Lösung bietet die Kombination von INDEX- und VERGLEICH-Funktion.
In unserem Beispiel wollen wir herausfinden, welcher Wert sich an den gemeinsamen Koordinaten von Produkt „A“ und der Farbe „grün“ befindet, das heißt, welcher Preis an der Schnittstelle der beiden vorgegebenen Kriterien „A“ (= Produkt) und „grün“ (= Farbe) eingetragen ist.
Würdest du die INDEX-Funktion allein verwenden, müsstest du zunächst herausfinden, in welcher Zeile das Produkt „A“ steht und in welcher Spalte die Farbe „grün“. Dieses zeitaufwendige Abzählen kannst du dir jedoch ersparen, indem du es mit der VERGLEICH-Funktion umgehst.
Bei der Erstellung der Formel legst du zunächst den Abfragebereich fest, danach lässt du mithilfe der VERGLEICH-Funktion Excel selbst zählen, an welcher Stelle genau sich die beiden Suchkriterien befinden.
Im Video wird dir Schritt für Schritt erklärt, wie du zu der folgenden Formel gelangst:
=INDEX(A1:D4;VERGLEICH(B6;A1:A4;0);VERGLEICH(B8;A1:D1;0))
Beachte dabei stets die korrekte Reihenfolge der Abfragekriterien, also zuerst die ZEILE, dann die SPALTE. (Kleine Eselsbrücke: Die beiden Wörter „zuerst“ und „Zeile“ beginnen jeweils mit einem z bzw. Z, die Wörter „dann“ und „Spalte“ enthalten beide ein a.)
Als Ergebnis bekommst du den Wert „20“ ausgeworfen, weil dieser Preis in jener Zelle steht, wo sich die Zeile von Produkt „A“ und die Spalte mit der Farbe „grün“ überschneiden.
Ergänzender Hinweis: Möchtest du mithilfe der Formel herausfinden, wo genau, d. h. in welcher Zeile und in welcher Spalte sich die beiden Suchkriterien befinden, dann markiere einfach innerhalb der Formel den jeweiligen Abfragebereich und drücke anschließend F9. Wie das genau funktioniert, wird dir im Video gezeigt.
Durch die Anreicherung der INDEX-Funktion mit der VERGLEICH-Funktion wird diese zum einen viel mächtiger und kann zum anderen häufig auch die SVERWEIS-Funktion ersetzen, weil du dir aufgrund der VERGLEICH-Funktion in vielen Fällen das umständliche Durchzählen der Spalten ersparst, das ansonsten für die Bestimmung des Spaltenindex erforderlich wäre.
SVERWEIS und VERGLEICH
Wenn du die SVERWEIS-Funktion innerhalb eines bestimmten Abfragebereichs verwendest, kannst du das erste Suchkriterium stets sehr leicht festlegen, indem du dieses entweder direkt in die Formel integrierst oder zwecks einfacheren Austausches dafür eine Bezugszelle verwendest, in die du das Suchkriterium hineinschreibst. Nachdem du die Matrix (= Abfragebereich) festgelegt hast, wärst du normalerweise jetzt ohne Verwendung der VERGLEICH-Funktion gezwungen, alle Spalten durchzuzählen, um herauszufinden, die wievielte Spalte jene ist, aus der du das Ergebnis bekommen möchtest. Mithilfe der VERGLEICH-Funktion hingegen ersparst du dir dieses Durchzählen.
Die gesuchte Formel lautet somit:
=SVERWEIS(B6;A1:D4;VERGLEICH(B8;A1:D1;0);FALSCH)
Ergänzender Hinweis: Du kannst relativ schnell feststellen, in welcher Spalte und in welcher Zeile sich eine bestimmte Zelle befindet, indem du zunächst in die Zelle A1 klickst und dann mit gedrückter Maustaste den Cursor bis zu der betreffenden Zelle ziehst. In dem kleinen Feld ganz links unmittelbar unterhalb des Menübandes wird dir angezeigt, in der wievielten Zeile und in der wievielten Spalte du gerade mit deinem Mauszeiger bist.
Für die Zelle C4 mit dem Preis „35“ wird dir dann, sofern du die Maustaste noch immer gedrückt hältst, zum Beispiel der Hinweis „4Z x 3S“ (4. Zeile | 3. Spalte) angezeigt. (Du siehst auch hier wiederum ganz deutlich, dass Excel immer zuerst auf die Zeile und danach die Spalte zugreift.)
Zwei Aspekte sind in diesem Zusammenhang allerdings zu beachten:
1) Diese Methode funktioniert nur, solange sich auch die Zelle A1 im sichtbaren Bereich des geöffneten Fensters befindet. Fährst du jedoch mit dem Mauszeiger über den rechten oder über den unteren Rand hinaus und verschwindet dadurch die erste Spalte nach links bzw. die erste Zeile nach oben aus dem Fenster, dann verschwindet auch die betreffende Anzeige in dem kleinen Feld links unterhalb des Menübandes. Bei etwas größeren Tabellen kannst du dir helfen, indem du den Zoom verkleinerst. Das macht aber auch nur bis zu einer gewissen Verkleinerung Sinn, da bei zu geringem Zoom den Inhalt der Spalten- bzw. Zeilenköpfe nicht mehr lesbar ist. In einem solchen Fall könntest du allerdings improvisieren und die gesuchte Zelle farblich hervorheben, um sie leichter zu finden.
2) Diese Methode der Abfrage ist „relativ“, das heißt, das kleine Feld links unterhalb des Menübandes zeigt die Anzahl der zurückgelegten Zeilen und Spalten nicht automatisch in Bezug auf die Zelle A1, sondern immer nur ausgehend von jener Zelle, in die du als Erstes angeklickt hast. Beginnst du nämlich beispielsweise bei Zelle B2 („10“) und ziehst mit gedrückter Maustaste bis zur Zelle C4, so erscheint in dem kleinen Fenster die Anzeige „3Z x 2S“, weil sich in diesem Falle die Zelle mit dem Preis „35“ – ausgehend von B2 – genau 3 Zeilen unterhalb und zwei Spalten rechts von der Ausgangszelle B2 befindet (die Ausgangszelle wird immer als Wert 1 mitgerechnet).
Eine weitere schnelle Alternative, wie du dir das Durchzählen von Spalten ersparen kannst, findest du am Ende des Videos.