Excel Index-Vergleich

Der Index-Vergleich in Excel besteht aus den zwei Funktionen Index und Vergleich. Ich nutze die Kombination um zum Beispiel Datenlücken in einer Pivot-Tabelle auszugleichen.

Beispielsweise soll das Endresultat eine n*m-Matrix sein, aber in der Pivot-Tabelle sind nur (n-2)*(m-3)-Datensätze. Man könnte die Lücken natürlich auch händisch auffüllen, aber gerade bei steigender Datenmenge erweist sich das nicht als sehr praktikabel.

Daher baut man sich im ersten Schritt seine n*m-Matrix auf. Nehmen wir für unser Beispiel folgende Matrix (Tabellenblatt Tabelle1):

	A	B	C	D
1		Alter	Gewicht	Größe
2	Person A
3	Person B

Und der Ausgangsdatensatz ist unserem Beispiel folgende Tabelle (Tabellenblatt Tabelle2):

	A	B	C
1		Alter	Größe
2	Person A	10	125
3	Person B	15	150

Nun nehmen wir den Index-Befehl zur Hilfe. Die Index-Funktion liefert den Wert einer Zelle einer Matrix zurück. Fügt man im Tabellenblatt Tabelle1 folgenden Befehl ein:

=INDEX(Tabelle2!B2:C3;1;2)

Dann erhält man den Wert 125. Der Index Befehl liefert als Resultat den Wert des Feldes in der ersten Zeile und zweiten Spalte der Matrix B2:C3 zurück (also das Feld C2).

Als nächstes testen wir die Vergleich-Funktion. Diese Funktion liefert mir die Position des Suchkriteriums in der Suchmatrix. Fügt man im Tabellenblatt Tabelle1 folgenden Befehl ein:

=VERGLEICH(A2;Tabelle2!A1:A3;0)

Dann erhält man den Wert 2, denn Person A ist in unserer Suchmatrix an zweiter Position. Wichtig ist auch die 0 als dritter Parameter der Vergleich-Funktion, denn dieser Parameter sagt, dass es eine genaue Übereinstimmung geben muss.

Nun kann man im nächsten Schritt die Funktionen mit einander kombinieren. Für den Zeilen Wert der Index-Funktion (für Feld B2 im Tabellenblatt Tabelle1) kann man schon das VERGLEICH-Beispiel von oberhalb einfügen.

Für den Spalten-Wert passen wir die Vergleich-Funktion etwas an:

=VERGLEICH(B1;Tabelle2!A1:C1;0)

Fügen wir die einzelnen Teile für nun zusammen (das liefert den Wert für das Feld B2 im Tabellenblatt Tabelle1):

=INDEX(Tabelle2!A1:C3;VERGLEICH(A2;Tabelle2!A1:A3;0);VERGLEICH(B1;Tabelle2!A1:C1;0))

Wichtig ist, dass die Suchmatrix für die Zeilenposition gleich viele Zeilen hat wie die Matrix für die Index-Funktion. Ebenfalls muss die Suchmatrix für die Spaltenposition gleich viele Spalten wie die Matrix der Index-Funktion haben.

Wie man schön am Beispiel sieht starten die Matrix und beide Suchmatrizen jeweils beim Feld A1. Die Suchmatrix für die Zeile geht bis zur dritten Zeile (A**3**) und ist damit gleich lang wie die Index-Matrix (C**3**). Für die Spalte geht der Suchmatrix bis zur dritten Spalte (C1) und ist damit ebenfalls gleich lang wie die Index-Matrix (C3).

Damit man nicht für die restlichen Spalten die Funktion von Hand anpassen muss, machen wir uns nun noch einen Trick von Excel zu Nutze. Wenn man ein $-Zeichen vor dem Zellenbereich einer Funktion macht, dann wird diese fixiert.

Unsere Matrix für die Index-Funktion ist immer gleich, daher müssen wir die Funktion folgendermaßen anpassen:

=INDEX(Tabelle2!$A$1:$C$3;VERGLEICH(A2;Tabelle2!A1:A3;0);VERGLEICH(B1;Tabelle2!A1:C1;0))

Nun ist unsere Matrix fixiert, jetzt fixieren wir die Vergleich-Funktion für die Zeile:

=INDEX(Tabelle2!$A$1:$C$3;VERGLEICH($A2;Tabelle2!$A$1:$A$3;0);VERGLEICH(B1;Tabelle2!A1:C1;0))

Die Suchmatrix bleibt wieder für jede Zelle gleich und beim Suchkriterium ändert sich nur die Zeile, aber nicht die Spalte.

Ähnlich verhält es sich mit der Vergleich-Funktion für die Spalte:

=INDEX(Tabelle2!$A$1:$C$3;VERGLEICH($A2;Tabelle2!$A$1:$A$3;0);VERGLEICH(B$1;Tabelle2!$A$1:$C$1;0))

Es wird wieder die Suchmatrix fixiert und beim Suchkriterium ändert sich dieses Mal die Spalte und nicht die Zeile.

Nun kann man das Feld B2 in die restlichen Felder der Matrix im Tabellenblatt Tabelle1 ziehen und es sollte sich folgendes Resultat ergeben:

	A	B	C	D
1		Alter	Gewicht	Größe
2	Person A	10	#NV	125
3	Person B	15	#NV	150

` Beispieldaten herunterladen <http://pata.gonia.org/wp-content/uploads/2012/01/blogbeispiel_index_vergleich.zip>`__