Excel-TippIn unsortierten Listen nach ungefährer Übereinstimmung suchen

Die Funktion SVERWEIS() bietet die Option, Listen mit ungefährer Übereinstimmung zu durchsuchen. Dies setzt aber voraus, dass die erste Spalte in der Tabelle entweder numerisch oder alphabetisch sortiert ist. Nur dann sucht SVERWEIS() nach dem am nächsten liegenden Wert.

Diese Option ist sehr nützlich, kann aber nicht mit den folgenden Eigenschaften einer Datentabelle umgehen:

• Liste oder Datentabelle ist nicht sortiert.
• Es gibt Abweichungen mit unterschiedlichen Vorzeichen (für die Auswertung ist der absolute Wert maßgeblich)

Beispiel für unsortierte Listen zur „ungefähren Auswertung“

Schauen wir uns diese Besonderheiten von Listen oder Datentabellen an einem Beispiel an.

In der folgenden Abbildung sehen Sie eine Liste von Städten mit den jeweiligen Einwohnerzahlen. Die Liste ist nicht sortiert. Die Aufgabe besteht darin, die nächste Übereinstimmung zu einem in der Zelle D1 eingegebenen Zielwert (Einwohnerzahl) zu finden; also die Stadt auszuwählen, die „in etwa“ die angegebene Einwohnerzahl hat.

Ungefähre Auswahl mit der Funktion XVERWEIS()

Obwohl es vielleicht nicht so aussieht, ist dies im Wesentlichen ein Nachschlageproblem. Hierfür können Sie die Funktion XVERWEIS() wie folgt einsetzen:

=XVERWEIS(0;ABS(B2:B11-D1);A2:A11;;1)

Die Formel geht von den folgenden Annahmen aus:

• B2:B11: In diesen Zellen befinden sich die Einwohnerzahlen.
• A2:A11: Stadtnamen, welche zurückgegeben werden sollen.

Schauen wir uns die Funktionsweise der Formel von innen nach außen an.

Zuerst wird die Differenz von jeder einzelnen Zelle im Bereich B2:B11 zur gesuchten Einwohnerzahl in der Zelle D1 ermittelt. Diese Differenz kann sowohl eine positive als auch eine negative Zahl sein.

=XVERWEIS(0;ABS(B2:B11-D1);A2:A11;;1)

Die einzelnen Differenzen werden anschließend an die Funktion ABS() weitergeleitet. Die Funktion ABS() in Excel gibt den absoluten Wert einer Zahl zurück. Das bedeutet, sie entfernt das Vorzeichen einer Zahl, sodass immer ein positiver Wert ausgegeben wird.

Sie haben somit eine Liste von positiven Differenzwerten, welche die Suchmatrix in der Funktion XVERWEIS() darstellt.

=XVERWEIS(0;ABS(B2:B11-D1);A2:A11;;1)

In dieser Liste (positive Differenzen) wird jetzt nach dem Suchkriterium gesucht. Das Suchkriterium ist der Wert 0 im ersten Argument der Funktion XVERWEIS(), denn die Differenz zur gesuchten Einwohnerzahl (5.000.000) soll möglichst gering oder eben 0 sein.

Bei der Suche wird nun der Vergleichsmodus 1 angewendet. Der Vergleichsmodus 1 sucht nach einer genauen Übereinstimmung. Wird keine genaue Übereinstimmung gefunden, dann wird das nächstgrößere Element zurückgeben.

=XVERWEIS(0;ABS(B2:B11-D1);A2:A11;;1)

Zum Schluss wird dann der entsprechende Wert zurückgegeben, der sich in der Rückgabematrix (A2:A11) der Funktion XVERWEIS() an der entsprechenden Stelle befindet.

In unserem Beispiel steht der Wert mit der kleinsten Abweichung an der fünften Stelle in der Suchmatrix (B2:B11). Daher wird der fünfte Wert in der Rückgabeliste A2:A11 als Ergebnis zurückgegeben. Dies ist im Beispiel der Wert Kapstadt.

=XVERWEIS(0;ABS(B2:B11-D1);A2:A11;;1)