Excel-Formel: SUMIFS gegen andere Suchformeln -

Zusammenfassung

In bestimmten Fällen können Sie SUMIFS wie eine Suchformel verwenden, um einen numerischen Wert abzurufen. Im gezeigten Beispiel lautet die Formel in G6:

=SUMIFS(sales,region,G4,quarter,G5)

Dabei werden Region (B5: B20), Quartal (C5: C20) und Umsatz (D5: D20) als Bereiche bezeichnet.

Das Ergebnis ist ein Umsatz im dritten Quartal für die Zentralregion von 127.250.

Erläuterung

Wenn Sie mit der SUMIFS-Funktion noch nicht vertraut sind, finden Sie hier eine grundlegende Übersicht mit vielen Beispielen.

Die SUMIFS-Funktion dient zum Summieren numerischer Werte basierend auf einem oder mehreren Kriterien. In bestimmten Fällen können Sie jedoch möglicherweise SUMIFS verwenden, um einen numerischen Wert zu "suchen", der die erforderlichen Kriterien erfüllt. Die Hauptgründe dafür sind Einfachheit und Geschwindigkeit.

Im gezeigten Beispiel haben wir vierteljährliche Verkaufsdaten für vier Regionen. Wir beginnen damit, SUMIFS einen Summenbereich und die erste Bedingung zu geben, die die Region auf den Wert in G4 "Zentral" testet:

=SUMIFS(sales,region,G4 // sum range, region is "Central"

Summenbereich ist Umsatz (D5: D20)
Kriterienbereich 1 ist Region (B5: B20)
Kriterium 1 ist G4 ("Zentral")

Wir fügen dann das zweite Bereich / Kriterien-Paar hinzu, das das Quartal überprüft:

=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"

Kriterienbereich 2 ist Viertel (C5: C20)
Kriterium 2 ist G5 ("Q3")

Mit diesen Kriterien gibt SUMIFS 127.250 zurück, die zentrale Q3-Verkaufsnummer.

Das Verhalten von SUMIFS besteht darin, alle übereinstimmenden Werte zu summieren. Da es jedoch nur einen übereinstimmenden Wert gibt, entspricht das Ergebnis dem Wert selbst.

Im Folgenden sehen Sie einige Optionen für Suchformeln.

Optionen für Suchformeln

In diesem Abschnitt werden kurz andere Formeloptionen beschrieben, die zum gleichen Ergebnis führen. Mit Ausnahme von SUMPRODUCT (unten) sind dies traditionellere Suchformeln, die die Position des Zielwerts lokalisieren und den Wert an dieser Position zurückgeben.

Mit VLOOKUP

Leider ist VLOOKUP keine gute Lösung für dieses Problem. Mit einer Hilfsspalte ist es möglich, eine VLOOKUP-Formel zu erstellen, die mehreren Kriterien entspricht (Beispiel hier), aber es ist ein umständlicher Prozess, bei dem Sie an den Quelldaten basteln müssen.

Mit INDEX und MATCH

INDEX und MATCH ist eine sehr flexible Suchkombination, die für alle Arten von Suchproblemen verwendet werden kann, und dieses Beispiel ist keine Ausnahme. Mit INDEX und MATCH können wir Verkäufe nach Region und Quartal mit einer Array-Formel wie der folgenden nachschlagen:

(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))

Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.

Der Trick bei diesem Ansatz besteht darin, eine boolesche Logik mit Array-Operationen innerhalb der MATCH-Funktion zu verwenden, um ein Array mit Einsen und Nullen als Sucharray zu erstellen. Dann können wir die MATCH-Funktion bitten, die Nummer 1 zu finden. Sobald das Lookup-Array erstellt wurde, wird die Formel wie folgt aufgelöst:

=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))

Da nur noch 1 im Sucharray verbleibt, gibt MATCH eine Position von 11 an die INDEX-Funktion zurück, und INDEX gibt die Verkaufsnummer an dieser Position zurück, 127.250.

Weitere Informationen finden Sie unter: INDEX und MATCH mit mehreren Kriterien

Mit XLOOKUP

XLOOKUP ist eine flexible neue Funktion in Excel, die Arrays nativ verarbeiten kann. Mit XLOOKUP können wir genau den gleichen Ansatz wie mit INDEX und MATCH verwenden und boolesche Logik und Array-Operationen verwenden, um ein Lookup-Array zu erstellen:

=XLOOKUP(1,(region=G4)*(quarter=G5),sales)

Sobald die Array-Operationen ausgeführt wurden, wird die Formel wie folgt aufgelöst:

=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)

Und XLOOKUP liefert das gleiche Ergebnis wie oben, 127.250.

Mehr: XLOOKUP mit mehreren Kriterien

Mit LOOKUP

Die LOOKUP-Funktion ist eine ältere Funktion in Excel, von der viele Menschen nicht einmal wissen. Eine der Hauptstärken von LOOKUP besteht darin, dass Arrays nativ verarbeitet werden können. LOOKUP weist jedoch einige deutliche Schwächen auf:

Kann nicht im "exakten Übereinstimmungsmodus" gesperrt werden
Es wird immer davon ausgegangen, dass die Suchdaten sortiert sind, AZ
Gibt immer eine ungefähre Übereinstimmung zurück (wenn keine genaue Übereinstimmung gefunden werden kann)

Trotzdem kann LOOKUP verwendet werden, um dieses Problem folgendermaßen zu lösen:

=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)

was vereinfacht zu:

=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)

If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.

We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.

More detailed explanation here.

With SUMPRODUCT

As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:

=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))

After the array math inside SUMPRODUCT is complete, the formula simplifies to:

=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))

This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.

See this example for a more complete explanation.

In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.

Summary

SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements: