Excel Such- und Bezugsversionen II - itService Thomas Käflein

Beratung - Projekte - Schulungen
itService Thomas Käflein
Direkt zum Seiteninhalt

Excel Such- und Bezugsversionen II

Workshops > Excel
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. (zum Workshop)
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 minimal 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-Funktion ( 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 Wert aus 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 Wert aus 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 mit bedingter Formatierung
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.
die Tabelle mit zwei Bereichen
Sie sehen den linken Teilbereich im Zellbereich von B2:C12 und den  rechten Teilbereich von E2:F12. Der mittlere farbig markierte 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 Wert aus 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 Wert aus 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 Wert aus 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 gesuchte 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 war diese Position  bereits (von Hand) vorgegeben. Wir können diese Koordinaten aber auch - wie bei SVERWEIS gesehen - per Funktion ermitteln. Hier hilft uns  die Funktion VERGLEICH weiter.
Position ermitteln per VERGLEICH
Sie sehen eine kleine Matrix von Zelle B1:B4. Aus dieser soll das in  Zelle E1 stehende Suckriterium 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 Wert aus E1.
  • Suchmatrix
    Mussparameter.
    Gibt die Matrix an, in der gesucht wird. In diesem Beispiel B1:B4.
    Es muss sich bei Suchmatrix um eine Zeile oder Spalte handelt.
  • Vergleichstyp
    Kannparameter.
    Gibt an, wie nach dem Wert gesucht werden soll.
    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), mit der Sie den gesuchten Wert finden - exakte Übereinstimmung:
=VERGLEICH(E1;B1:B4)
Position ermittelt mit VERGLEICH
Da der gesuchte Wert in der Matrix enthalten ist, wurde er gefunden. Andernfalls hätte Excel eine Fehlermeldung ausgegeben, da genaue Übereinstimmung gefordert war.
Sie haben gesehen, dass die Funktion VERGLEICH anhand eines Wertes  dessen Position innerhalb einer Matrix sucht. Wie passen nun die 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 zum Beispiel, 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 und findet diesen an Position 2. Die Zweite VERGLEICH-Funktion macht dasselbe mit der Marke aus B7 und der Matrix in B1:F1, die sie in Spalte 2 findet.
Im Beispiel  ergibt sich daher als Zeilen- und Spaltenindex jeweils der Wert 2, der an die INDEX-Funktion übergeben wird. Diese ermittelt aus dem Schnittpunkt den Wert 28,49.
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 die Handhabung dynamischer Bereiche mit einer ganz speziellen Funktion vorstellen: BEREICH.VERSCHIEBEN. Es bleibt also spannend.
Zurück zur Übersicht
Zurück zum Seiteninhalt