INDEX und VERGLEICH bei Excel - so werden sie verwendet!

Marco Kratzenberg 2

Die Excel-Funktionen INDEX und VERGLEICH helfen uns, in komplexen Tabellen schnell bestimmte Daten zu finden. Mit wenigen Klicks kommen wir zu genauen Auswertungen. Hier ein Beispiel mit Erklärung.

Video: Fünf weitere Tipps für Excel

Fünf Tipps für Microsoft Excel.

Ein Excel Index zeigt uns die Daten an einer beliebigen Schnittstelle einer Spalte mit einer Linie. Die Funktion vergleich hilft uns dabei, in umfangreichen Tabellen schneller diese Schnittpunkte anzusteuern. Die Methode ist nicht ganz einfach zu verstehen, da beide Funktionen dazu miteinander verknüpft werden müssen. In den folgenden Abschnitten möchte ich versuchen, sie etwas verständlicher zu machen.

Excel Index und Vergleich: Beispiel

Wie schon erwähnt: Der Excel Index gibt den Wert an der Schnittstelle zweier Linien aus. Dabei sind die Zeilen und die Spalten von der ersten bis zur Letzten Zeile durchnummeriert. Die erste Zelle liegt also an der Kreuzung von Zeile 1 und Spalte 1 – und so weiter. Sehen wir uns mal folgende Tabelle an.

Artikel Größe S Größe M Größe L Größe XL
Pullover rot 0 2 3 0
Pullover blau 2 5 4 2
Pullover grün 1 3 6 1

Wieviele blaue Pullover haben wir noch in der Größe M vorrätig? Das ist schnell beantwortet. Der Artikel Pullover blau befindet sich in der 2. Zeile, die Größe M ist in der 2. Spalte. Wir haben also 5 blaue Pullover am Lager. Das war einfach. Wenn wir nun aber eine Tabelle mit 5000 Artikeln und noch mehr Größenwerten haben, dann kann die Suche anstrengend werden.

Hier kommt die Funktion Index ins Spiel, um den richtigen Wert zu suchen und die Excel-Funktion Vergleich sorgt für den richtigen Artikel. Im nachfolgenden Bild haben wir bereits 19 Artikel und 10 unterschiedliche Größenwerte. Sakko dunkelblau befindet sich in Zeile 15 und der Lagerbestand der Größe M in Spalte 2. Das kann man noch manuell raussuchen, zumal ich die Artikel alphabetisch sortiert habe. Es soll uns ja auch nur als Beispiel dienen.

Ich benötige also die Zeile und die Spalte der Tabelle, um den Lagerbestand eines Artikels in einer bestimmten Größe herauszusuchen. Da wäre es doch praktisch, wenn ich einfach den Artikelnamen sowie die Größe in einem Pulldown anklicken kann und dann automatisch den Lagerbestand angezeigt bekomme.

Im nächsten Abschnitt erkläre ich den Aufbau der Tabelle und zeige Euch, wie und wo Ihr Excel Index und Excel vergleich einsetzt, um am Ende den richtigen Lagerbestand angezeigt zu bekommen.

Bilderstrecke starten
11 Bilder
Diese 10 verrückten Projekte wurden tatsächlich in Excel umgesetzt!

Excel Index und Vergleich: So wird’s gemacht!

Wie im obigen Bild zu sehen, habe ich aus den Werten für den Artikelnamen und die Größe je eine Dropdown-Auswahl gemacht. Das geht folgendermaßen:

  • In die Zelle klicken, die eine Dropdown-Auswahl erhalten soll.
  • Oben im Menü den Reiter Daten anwählen und dort Datenüberprüfung auswählen.
  • Im nachfolgenden Fenster bei Zulassen den Wert Liste anklicken.
  • Dann unten ins Feld Quelle klicken.
  • Jetzt mit der Maus die Zellen vom ersten bis zum letzten Artikel markieren.
  • Mit OK bestätigen.

Das macht Ihr je einmal für die Artikel und die Größen. Das hat zwei Gründe. Zum einen können wir Größe und Artikel komfortabel auswählen. Aber zum anderen können wir dieses Feld nun für die Excel-Funktion Vergleich nutzen. Denn dann bekommt Ihr die Nummer der Zeile und Spalte heraus, die Ihr später in der Navigation mit dem Excel Index nutzen können. Doch eins nach dem anderen.

Ihr braucht die Nummer der Zeile (also des Artikels) und die Nummer der Spalte (der Größe). Man kann sie von Hand eingeben, aber dann könnte man genauso gut den Lagerbestand gleich selbst heraussuchen. Also nutzen wir den Vergleich.

Definition der Excel-Funktion Vergleich in unserem Beispiel

Wir haben die Dropdown-Auswahlen angelegt und suchen uns dort einen Artikel und eine Größe raus. Was die Funktion Vergleich nun macht, ist folgendes: Sie vergleich den Wert im Pulldown mit den Werten der Artikel bzw. Größen. Wenn es eine genaue Übereinstimmung gibt, dann werden uns die Zeile oder Spalte in der Zelle ausgegeben, in der wir die Excel-Vergleichs-Funktion nutzen.

Beispiel: Der Artikel Sakko dunkelblau stimmt genau mit dem Artikel Sakko dunkelblau in der Tabelle überein. Dort befindet er sich in Zeile 15. Also gebe ich die 15 aus. Die Größe M passt zur Größe M in der Tabelle, die sich in Spalte 2 befindet. Also gibt der Vergleich hier eine 2 aus.

So ist der Excel Vergleich aufgebaut:

=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)

  • Suchkriterium: Im Beispielbild jeweils die Zellen A23 und A25. Also die selbst erstellen Dropdowns für Artikel und Größe.
  • Suchmatrix: Bei den Artikeln ist damit die gesamte Spalte der Artikelnamen gemeint. Bei den Größen die gesamte Zeile der Größenbezeichnungen. Also A2 bis A20 bzw. B1 bis K1.
  • Vergleichstyp: Hier geben wir eine 0 an. Das bedeutet, dass das Ergebnis des Vergleichs exakt gleich dem Suchkriterium ist. Größe S im Dropdown soll Größe S in der Tabelle entsprechen.

Für die Artikelnamen ist das also die Formel

=VERGLEICH(A23;A2:A20;0)

Für die Größen die Formel

=VERGLEICH(D23;B1:K1;0)

Diese Werte können wir nun endlich in den Index einsetzen, um bei der Auswahl von Artikel und Größe den jeweiligen Lagerbestand angezeigt zu bekommen.

Excel INDEX

Der Excel Index verwendet gegebene Werte, um gezielt zu einer Stelle der Tabelle zu springen und den dortigen Wert auszugeben.

Für den Index brauchen wir drei Werte. Da ist zum einen der Bereich, auf den sich alle Ausgabewerte beziehen. In unserem Fall ist das der gesamte Bereich in dem die Lagerbestände stehen. (B2:K20)

Als nächstes folgt die Zeile. Gemeint ist hier die Zeilennummer in der der Artikel steht.

Nun brauchen wir die Spalte. Das ist in unserem Fall die Spalte in der die Größen stehen.

=INDEX(Bereich;Zeile;Spalte)

Oder in unserem Beispiel:

=INDEX(B2:K20;B23;E23)

Gesucht wird also im Bereich B2:K20 die Zelle, an der sich eine Zeile und eine Spalte schneiden. Die Nummer der Zeile bekommen wir durch die Excel-Funktion VERGLEICH. Das haben wir oben für den Artikel und die Größe erledigt. In dem Beispiel werden diese Zahlenwerte durch den Vergleich mit dem Dropdown ermittelt und in Zelle B23 und E23 ausgegeben. Die können wir also nutzen.

Nun ist alles ganz einfach. Den Artikel aus dem Pulldown wählen und daneben wird die Zeilennummer angezeigt. Die wandert gleich in die Index-Formel. Jetzt die Größe auswählen und wir bekommen die Spalte für den Excel Index. Das Ergebnis der Schnitt-Zelle wird sofort angezeigt. Wir haben noch 4 lange Jeans in XL auf Lager.

So eine Lagerbestandstabelle lässt sich noch wunderbar mit der Excel-Wenn-Dann-Funktion verknüpfen. Dann werden beispielsweise Alle Lagerbestände rot angezeigt, die eine gewisse Mindest- oder Höchstmenge überschreiten. Also muss man nachbestellen oder abverkaufen.

Weitere Excel-Themen

Zu den Kommentaren

Kommentare zu diesem Artikel

Weitere Themen

Neue Artikel von GIGA SOFTWARE

  • Google Docs offline nutzen – so geht's

    Google Docs offline nutzen – so geht's

    Mit Google Docs könnt ihr kostenlos Dokumente, Tabellen und Präsentationen im Browser erstellen und online mit euren Kollegen oder Freunden teilen. Ohne Internet lassen sich die Dokumente aber trotzdem erstellen und bearbeiten. Wir zeigen, wie ihr Google Docs offline nutzt.
    Robert Schanze
  • Chrome: Dark Theme aktivieren – so geht's

    Chrome: Dark Theme aktivieren – so geht's

    Im Browser Google Chrome könnt ihr ein Dark Theme aktivieren, wodurch Menüleisten und Webseiten dunkel und in Schwarz angezeigt werden. Auch könnt ihr Webseiten mit hellem Text auf dunklem Hintergrund anzeigen lassen, damit sie abends am Bildschirm nicht so grell wirken. Wir zeigen, wie das geht.
    Robert Schanze
  • Mehr Instagram-Follower und -Likes bekommen - so geht's

    Mehr Instagram-Follower und -Likes bekommen - so geht's

    Mehr Instagram Follower zu bekommen ist nicht nur gut für das Ego. Es kann jemanden bekannter machen, Artikel verkaufen und eigene Bilder besser verbreiten. Mit ein paar einfachen Tricks erhält man sie auch ohne zu mogeln.
    Marco Kratzenberg
* Werbung