Such- und Bezugsversionen II

Versionshinweise

Die hier behandelten Funktionen gehören schon seit einiger Zeit zum Excel-Umfang. Die Hinweise in diesem Workshop zur Handhabung beziehen sich jedoch auf die Excel-Versionen 2010 bzw. 2013.

Für Abfragen von Werten aus Matrizen verwenden Sie wahrscheinlich die Funktion SVERWEIS (bzw. WVERWEIS). Im ersten Teil der Workshopreihe zum Thema Such- und Bezugsversionen hatte ich Ihnen diese vorgestellt.

So einfach und praktikabel diese Funktionen auch sind, so haben sie doch ein paar Schwächen. Spätestens dann kommen zwei weitere Funktionen in's Spiel, die flexibler, aber leider auch etwas komplizierter sind.

In diesem Workshop zeige ich Ihnen, wie die beiden Funktionen INDEX und VERGLEICH genutzt werden, wo ihre Stärken und Fallen liegen.

Beispiel 1

Im ersten Beispiel liegt uns eine kleine Kreuztabelle vor: drei Spalten, fünf Zeilen. Sie wollen per Vorgabe der Zeilen und Spalten den Wert aus dem Schnittpunkt dieser beiden Angaben ermitteln. Dies bewerkstelligen wir mit der Funktion INDEX.

INDEX (Matrixvariante)

Die Funktion INDEX gibt es in zwei Versionen: einer Bezugs- und einer Matrix-Variante. Für dieses Beispiel nutzen wir die Matrixvariante. Die andere schauen wir uns später noch an.

Bei dieser Eingabe in den Zellen G2 + G3 müsste der Wert 230 angezeigt werden, da sich dieser im Schnittpunkt der vierten Zeile und der zweiten Spalte der Matrix befindet. Die Syntax der Funktion (Matrixvariante) lautet:

  • =INDEX(Matrix;[Zeile];[Spalte])

Was bedeuten diese Parameter?

  • Matrix
    Mussparameter. Gibt den Bezug zur Matrix an. In diesem Beispiel B2:D6.
  • Zeile
    Kannparameter. Gibt die Zeile an, die gesucht werden soll. In diesem Beispiel G2.
    Der Parameter kann entfallen, wenn die Matrix aus nur einer Zeile besteht.
  • Spalte
    Kannparameter. Gibt die Spalte an, die gesucht werden soll. In diesem Beispiel G3.
    Der Parameter kann entfallen, wenn die Matrix aus nur einer Spalte besteht.

Geben Sie folgende Formel ein (manuell oder über den Funktionsassistenten):

  • =INDEX(B2:D6;G2;G3)

Das Ergebnis zeigt den Wert an. Die im Bild zu sehende grüne Hintergrundfarbe bei der betreffenden Zelle wurde mit einer bedingten Formatierung und folgender Formel erstellt:

  • =UND($G$2=$A2;$G$3=B$1

fertige Tabelle

Beispiel 2

Auch hier liegt uns wieder eine Kreuztabelle vor, die aber - im Gegensatz zum ersten Beispiel - aus zwei Teilbereichen besteht. Wir wollen nun dynamisch den Wert aus dem Schnittpunkt des gewünschten Teilbereichs ermitteln.

Unsere Tabelle mit zwei Teilbereichen

Sie sehen den linken Teilbereich im Zellbereich von B2:C12 und den rechten Teilbereich von E2:F12. Der mittlere Bereich interessiert in diesem Fall nicht.

Die Syntax der Funktion (Bezugsvariante) lautet:

  • =INDEX(Bezug;[Zeile];[Spalte];[Bereich])

Was bedeuten diese Parameter?

  • Bezug
    Mussparameter. Gibt den Bezug zur Matrix an. In diesem Beispiel B2:C12 und E2:F12.
    Die beiden Bereiche müssen in Klammern geschrieben und mit einem Semikolon vorneinander getrennt werden.
  • Zeile
    Kannparameter. Gibt die Zeile an, die gesucht werden soll. In diesem Beispiel I2.
    Der Parameter kann entfallen, wenn die Matrix aus nur einer Zeile besteht.
  • Spalte
    Kannparameter. Gibt die Spalte an, die gesucht werden soll. In diesem Beispiel I3.
    Der Parameter kann entfallen, wenn die Matrix aus nur einer Spalte besteht.
  • Bereich
    Kannparameter. Gibt die laufende Nummer des Bereichs an. In diesem Beispiel I4.
    Wird der Parameter nicht angegebn, da nur ein Bereich existiert entspricht dies der Matrixvariante.

Geben Sie folgende Formel ein (manuell oder über den Funktionsassistenten):

  • =INDEX((B2:C12;E2:F12);I2;I3;I4)

Auch hier wurde wieder mit bedingter Formatierung (siehe oben) die betreffende Zelle eingefärbt.

Fertige Tabelle Bezugsversion

Wie Sie gesehen haben, liefert die Funktion INDEX auf Basis von Koordinaten aus einer angegebenen Matrix die korrspondierenden Werte - sowohl in der Matrix- als auch in der Bezugsversion.

Beispiel 3

Was aber tun, wenn Sie die Position eines Suchwertes in einer Matrix ermitteln wollen? In den bisherigen Beispielen hatten wir diese Position generell von Hand eingegeben. Das geht aber auch anders. Hier hilft uns die Funktion VERGLEICH, die eben dies bewerkstelligt.

Position ermitteln mit VERGLEICH

Sie sehen eine kleine Matrix von Zelle B1:B4. Aus dieser soll der in Zelle E1 stehende Suchwert gesucht und dessen Position in der Matrix zurückgegeben werden.

Die Syntax der Funktion lautet:

  • =VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])

Was bedeuten diese Parameter?

  • Suchkriterium
    Mussparameter. Gibt den Bezug zu dem Wert an, der gesucht werden soll. In diesem Beispiel E1.
  • Suchmatrix
    Mussparameter. Gibt die Matrix an, in der gesucht wird. In diesem Beispiel B1:B4.
  • Vergleichstyp
    Kannparameter. Gibt an, wie nach dem Wert gesucht werden soll. In diesem Beispiel nicht angegeben.
    Der Parameter kennt drei mögliche Werte:
    • 1 = kleiner als; Sucht den größten Wert, der kleiner oder gleich dem Suchwert ist. Die Suchmatrix muss aufsteigend sortiert sein.
    • 0 = genaue Übereinstimmung; Findet den ersten Wert, der genau dem Suchwert entspricht. Die Reihenfolge der Suchmatrix ist gleichgültig. Dies ist der Vorgabewert.
    • -1 = größer als; Sucht den kleinsten Wert, der größer oder gleich dem Suchwert ist. Die Suchmatrix muss absteigend sortiert sein.

Geben Sie folgende Formel ein (manuell oder über den Funktionsassistenten):

  • =VERGLEICH(E1;B1:B4)

Position ermitteln mit VERGLEICH

Sie haben gesehen, dass die Funktion VERGLEICH anhand eines Wertes dessen Position innerhalb einer Matrix sucht. Wie passen nun diese beiden Funktionen INDEX und VERGLEICH zusammen? Das zeige ich Ihnen im vierten Beispiel dieses Workshops.

Beispiel 4

In diesem Beispiel kombinieren wir nun beide Funktionen.
Wie bereits erwähnt, stoßen Sie mit den Funktionen SVERWEIS und WVERWEIS schnell an Ihre Grenzen. Ein solcher Fall ist, wenn Sie zwei Suchbegriffe haben.

INDEX und VERGLEICH als Kombi

Hier haben wir eine Kreuztabelle mit Hemden verschiedener Hersteller in unterschiedlichen Farben. Per Vorgabe der Farbe und der Marke soll aus dieser Tabelle der Preis ermittel werden. Die Dynamik muss folglich sowohl waagerecht als auch senkrecht gegeben sein.

Schreiben Sie in Zelle B8 die folgende Formel:

  • =INDEX(B2:F4;VERGLEICH(B6;A2:A4;0);VERGLEICH(B7;B1:F1;0))

Der erste Parameter stellt die Matrix dar (B2:F4). Die beiden VERGLEICH-Funktionen liefern die Zeilen- bzw. Spaltenangabe für die INDEX-Funktion. Die Erste ermittelt die in B6 hinterlegte Farbe und sucht diese (genaue Übereinstimmung) in der Matrix A2:A4. Die Zweite macht dasselbe mit der Marke aus B7 und der Matrix in B1:F1. Im Beispiel ergibt sich aus der Farbe die Position 2, aus der Marke die Position 2. Im Schnittpunkt der zweiten Zeile und zweiten Spalte der Matrix steht der Betrag 28,49, den die Funktion INDEX zurückliefert.

Suche mit INDEX und VERGLEICH

In diesem Workshop haben Sie gesehen, wie Sie die Funktionen INDEX und VERGLEICH sinnvoll anwenden können. Für diese beiden Funktionen gibt es reichhaltige Anwendungsfälle. Es lohnt sich also, sich näher mit ihnen zu beschäftigen.

In einem weiteren Workshop werde ich Ihnen dynamische Bereiche mit einer ganz speziellen Funktion vorstellen: BEREICH.VERSCHIEBEN. Es bleibt also spannend.