Excel Wert Aus Matrix Mit 2 Suchkriterien

Excel, das allgegenwärtige Tabellenkalkulationsprogramm, birgt oft ungeahnte Tiefen und Möglichkeiten. Eine dieser Möglichkeiten ist das gezielte Auslesen von Werten aus einer Matrix (einem Bereich von Zellen), basierend auf zwei oder mehr Suchkriterien. Diese Fähigkeit ist besonders nützlich für komplexe Datenanalysen, Berichterstellung und die Automatisierung von Aufgaben, bei denen genaue Datenpunkte aus großen Datensätzen extrahiert werden müssen.

Die Herausforderung: Datenextraktion mit Mehrfachbedingungen

Stellen wir uns vor, wir haben eine Tabelle mit Verkaufsdaten. Jede Zeile repräsentiert einen Verkaufsvorgang, mit Spalten für das Produkt, die Region, den Monat und den Umsatz. Nun möchten wir den Umsatz für ein bestimmtes Produkt in einer bestimmten Region in einem bestimmten Monat ermitteln. Eine einfache Suche mit einer einzigen Bedingung reicht hier nicht aus. Wir benötigen eine Methode, die gleichzeitig alle relevanten Kriterien berücksichtigt.

Es gibt verschiedene Ansätze, um dieses Problem in Excel zu lösen. Wir werden uns hier auf zwei verbreitete und effektive Methoden konzentrieren: die Verwendung der Funktionen INDEX und VERGLEICH (MATCH), sowie die Kombination aus SUMMEWENNS (SUMIFS) oder MITTELWERTWENNS (AVERAGEIFS), je nachdem, welches Ergebnis wir erwarten (Summe, Durchschnitt, etc.).

Methode 1: INDEX und VERGLEICH (MATCH)

Die Kombination aus INDEX und VERGLEICH ist ein Klassiker und bietet eine sehr flexible Lösung. Die INDEX-Funktion gibt den Wert einer Zelle in einem bestimmten Bereich zurück, basierend auf ihrer Zeilen- und Spaltennummer. Die VERGLEICH-Funktion hingegen findet die Position eines Suchwerts in einem Bereich. Indem wir die VERGLEICH-Funktion verwenden, um die Zeilen- und Spaltennummern für die INDEX-Funktion zu ermitteln, können wir den gewünschten Wert aus der Matrix extrahieren.

Grundstruktur:

=INDEX(Datenbereich; VERGLEICH(Suchkriterium1; Suchbereich1; 0); VERGLEICH(Suchkriterium2; Suchbereich2; 0))

Erläuterung:

• Datenbereich: Der Bereich der Zellen, aus dem wir den Wert extrahieren möchten (z.B. der gesamte Umsatzbereich).
• Suchkriterium1: Der Wert, nach dem wir in der ersten Dimension (z.B. Zeilen) suchen (z.B. der Produktname).
• Suchbereich1: Der Bereich, in dem wir nach dem ersten Suchkriterium suchen (z.B. die Spalte mit den Produktnamen).
• Suchkriterium2: Der Wert, nach dem wir in der zweiten Dimension (z.B. Spalten) suchen (z.B. die Region).
• Suchbereich2: Der Bereich, in dem wir nach dem zweiten Suchkriterium suchen (z.B. die Zeile mit den Regionsnamen).
• 0: Gibt an, dass wir eine exakte Übereinstimmung suchen.

Beispiel:

Nehmen wir an, unsere Daten sind wie folgt aufgebaut:

     A       B        C       D
1   Produkt  Region  Monat  Umsatz
2   Apfel    Nord    Januar  100
3   Apfel    Süd     Januar  120
4   Birne    Nord    Januar  80
5   Birne    Süd     Januar  90
6   Apfel    Nord    Februar 110
7   Apfel    Süd     Februar 130

Wir möchten den Umsatz für "Apfel" in der Region "Nord" im Januar ermitteln. Wir können die INDEX/VERGLEICH-Formel wie folgt anwenden:

=INDEX(D2:D7;VERGLEICH("Apfel"&"Nord"&"Januar";A2:A7&B2:B7&C2:C7;0))

Diese Formel verwendet eine Hilfsspalte, in der die Suchkriterien verkettet werden, um eine eindeutige ID zu generieren. Es ist wichtig, hier eine Verkettung zu verwenden, da INDEX/VERGLEICH sonst nur 2 Argumente für VERGLEICH akzeptiert. Das "&" Zeichen verkettet die Strings.

Alternativ, ohne Hilfsspalte (Matrixformel):

=INDEX(D2:D7;VERGLEICH(1;(A2:A7="Apfel")*(B2:B7="Nord")*(C2:C7="Januar");0))

Wichtiger Hinweis: Da es sich hier um eine Matrixformel handelt, muss die Eingabe der Formel mit STRG+SHIFT+ENTER bestätigt werden, um die geschweiften Klammern automatisch um die Formel zu setzen: {=INDEX(D2:D7;VERGLEICH(1;(A2:A7="Apfel")*(B2:B7="Nord")*(C2:C7="Januar");0))}. Die Formel multipliziert die Wahrheitswerte (WAHR=1, FALSCH=0) und findet die Zeile, in der alle Bedingungen WAHR sind (also das Produkt aller Bedingungen gleich 1 ist).

Methode 2: SUMMEWENNS (SUMIFS) oder MITTELWERTWENNS (AVERAGEIFS)

Die Funktionen SUMMEWENNS und MITTELWERTWENNS sind speziell für die Berechnung von Summen oder Mittelwerten unter Berücksichtigung mehrerer Bedingungen konzipiert. Sie sind oft einfacher zu verstehen und anzuwenden als die INDEX/VERGLEICH-Kombination, besonders wenn es darum geht, eine Summe oder einen Durchschnitt zu berechnen. Wichtig ist, dass diese Funktionen nur dann sinnvoll sind, wenn genau ein Ergebnis den Suchkriterien entspricht. Wenn mehrere Zeilen die Kriterien erfüllen, werden diese entweder aufsummiert (SUMMEWENNS) oder der Durchschnitt gebildet (MITTELWERTWENNS).

Grundstruktur (SUMMEWENNS):

=SUMMEWENNS(Summenbereich; Kriterienbereich1; Suchkriterium1; Kriterienbereich2; Suchkriterium2; ...)

Grundstruktur (MITTELWERTWENNS):

=MITTELWERTWENNS(Mittelwertbereich; Kriterienbereich1; Suchkriterium1; Kriterienbereich2; Suchkriterium2; ...)

Erläuterung:

• Summenbereich/Mittelwertbereich: Der Bereich der Zellen, der summiert oder gemittelt werden soll (z.B. der Umsatzbereich).
• Kriterienbereich1: Der Bereich, in dem nach dem ersten Suchkriterium gesucht wird (z.B. die Spalte mit den Produktnamen).
• Suchkriterium1: Der Wert, nach dem im ersten Kriterienbereich gesucht wird (z.B. der Produktname).
• Kriterienbereich2: Der Bereich, in dem nach dem zweiten Suchkriterium gesucht wird (z.B. die Spalte mit den Regionsnamen).
• Suchkriterium2: Der Wert, nach dem im zweiten Kriterienbereich gesucht wird (z.B. die Region).
• ...: Weitere Kriterienbereiche und Suchkriterien können hinzugefügt werden.

Beispiel (Fortsetzung des obigen Beispiels):

Um den Umsatz für "Apfel" in der Region "Nord" im Januar zu ermitteln, verwenden wir SUMMEWENNS:

=SUMMEWENNS(D2:D7;A2:A7;"Apfel";B2:B7;"Nord";C2:C7;"Januar")

Diese Formel summiert alle Umsätze, bei denen das Produkt "Apfel", die Region "Nord" und der Monat "Januar" sind.

Wahl der Methode: Vor- und Nachteile

Die Wahl zwischen INDEX/VERGLEICH und SUMMEWENNS/MITTELWERTWENNS hängt von der spezifischen Anforderung ab.

INDEX/VERGLEICH:

• Vorteile: Flexibler, kann beliebige Werte extrahieren (nicht nur Summen oder Mittelwerte). Geeignet, wenn man einen bestimmten Wert benötigt, der den Kriterien entspricht.
• Nachteile: Komplexere Syntax, besonders bei Matrixformeln. Erfordert oft eine Hilfsspalte oder Matrixformel. Bei mehreren übereinstimmenden Einträgen wird nur der erste gefunden.

SUMMEWENNS/MITTELWERTWENNS:

• Vorteile: Einfachere Syntax, speziell für die Berechnung von Summen oder Mittelwerten.
• Nachteile: Weniger flexibel, kann nur Summen oder Mittelwerte berechnen. Nur geeignet, wenn man die Summe oder den Durchschnitt aller Einträge mit den angegebenen Kriterien berechnen möchte, oder wenn garantiert ist, dass nur ein Eintrag den Kriterien entspricht.

Fazit: Die Kunst der Datenextraktion in Excel meistern

Das Extrahieren von Werten aus einer Matrix mit zwei Suchkriterien ist eine wesentliche Fähigkeit für jeden, der Excel zur Datenanalyse verwendet. Die Methoden INDEX/VERGLEICH und SUMMEWENNS/MITTELWERTWENNS bieten unterschiedliche Ansätze, um dieses Ziel zu erreichen. Durch das Verständnis der Stärken und Schwächen jeder Methode können Sie die optimale Lösung für Ihre spezifischen Bedürfnisse auswählen und somit Ihre Datenanalysefähigkeiten erheblich verbessern. Experimentieren Sie mit verschiedenen Datenstrukturen und Kriterien, um ein tiefes Verständnis für diese mächtigen Funktionen zu entwickeln. Denken Sie daran, die Herausforderung der Datenextraktion ist oft der erste Schritt zu wertvollen Erkenntnissen.