EXCEL-FORMEL: SUMIFS IM VERGLEICH ZU ANDEREN NACHSCHLAGEFORMELN

Zusammenfassung

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

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

wo Region (B5:B20), Quartal (C5:C20) und Der Umsatz (D5:D20) sind benannte Bereiche .

Das Ergebnis ist ein Q3-Umsatz für die Region Central, 127.250.

Erläuterung

Wenn Sie neu in der SUMIFS-Funktion sind, finden Sie a Grundübersicht mit vielen Beispielen hier .

Die Funktion SUMIFS dient dazu, numerische Werte basierend auf einem oder mehreren Kriterien zu summieren. In bestimmten Fällen können Sie jedoch SUMIFS verwenden, um einen numerischen Wert 'nachzuschlagen', 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, die den Bereich auf den Wert in G4 testet, 'Zentral' zu geben:

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

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

Dann fügen wir das zweite Bereich/Kriterien-Paar hinzu, das Quartal prüft:

Summen kleiner oder gleich

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

Kriterienbereich 2 ist Quartal (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 zu summieren alle passenden Werte . Da es aber nur ein übereinstimmender Wert , ist das Ergebnis das gleiche wie der Wert selbst.

Im Folgenden sehen wir uns mehrere Optionen für die Nachschlageformel an.

Nachschlageformeloptionen

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

Mit SVERWEIS

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

Mit INDEX und MATCH

INDEX und MATCH ist eine sehr flexible Lookup-Kombination, die für alle Arten von Lookup-Problemen verwendet werden kann, und dieses Beispiel ist keine Ausnahme. Mit INDEX und MATCH können wir den Umsatz nach Region und Quartal mit einer Array-Formel wie dieser nachschlagen:

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

Hinweis: Dies ist ein Array-Formel , und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.

Der Trick bei diesem Ansatz ist die Verwendung von Boolesche Logik mit Array-Operationen innerhalb der MATCH-Funktion, um ein Array aus Einsen und Nullen als Lookup-Array zu erstellen. Dann können wir das fragen MATCH-Funktion Finden Sie die Zahl 1. Sobald das Lookup-Array erstellt wurde, wird die Formel wie folgt aufgelöst:

wie man Wochentag in Excel bekommt

 = INDEX (sales, MATCH (1,{0000000000100000},0))

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

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

Mit XVERWEIS

XVERWEIS ist eine flexible neue Funktion in Excel, die Arrays nativ verarbeiten kann. Mit XLOOKUP können wir genau den gleichen Ansatz wie bei INDEX und MATCH verwenden, indem wir 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,{0000000000100000},sales)

Und XLOOKUP gibt das gleiche Ergebnis wie oben zurück, 127.250.

Mehr: XVERWEIS mit mehreren Kriterien

Dynamische Array-Formeln sind erhältlich in Büro 365 nur.

Mit LOOKUP

Die LOOKUP-Funktion ist eine ältere Funktion in Excel, die viele Leute nicht einmal kennen. Eine der Hauptstärken von LOOKUP besteht darin, dass es Arrays nativ verarbeiten kann. LOOKUP hat jedoch einige deutliche Schwächen:

Kann nicht im 'Genau-Match-Modus' gesperrt werden
Geht immer davon aus, dass die Nachschlagedaten sortiert sind, A-Z
Gibt immer eine ungefähre Übereinstimmung zurück (wenn keine genaue Übereinstimmung gefunden werden kann)

Nichtsdestotrotz kann LOOKUP verwendet werden, um dieses Problem wie folgt 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)

Wenn Sie genau hinsehen, können Sie eine einzelne Zahl 1 in einem Meer von #DIV/0 erkennen! Fehler. Dies stellt den Wert dar, den wir abrufen möchten.

Wir verwenden einen Lookup-Wert von 2, da wir nicht garantieren können, dass das Array sortiert ist. Wir zwingen also alle nicht übereinstimmenden Zeilen zu Fehlern und bitten LOOKUP, eine 2. zu finden. LOOKUP ignoriert die Fehler und durchsucht pflichtbewusst das gesamte Array nach 2. Wenn die Zahl 2 nicht gefunden werden kann, 'sichert' LOOKUP und stimmt mit dem letzten nicht fehlerfreien Wert überein, der 1 an 11. Stelle ist. Das Ergebnis ist das gleiche wie oben, 127.250.

Genauere Erklärung hier .

Mit SUMPRODUCT

Sie können wie gewohnt auch das Schweizer Taschenmesser verwenden SUMMENPRODUKT-Funktion auch dieses Problem zu lösen. Der Trick besteht darin, boolesche Logik- und Array-Operationen zu verwenden, um alle bis auf den einen Wert, den wir wollen, auf Null zu setzen:

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

Nachdem die Array-Mathematik in SUMPRODUCT abgeschlossen ist, vereinfacht sich die Formel zu:

 = SUMPRODUCT ({000000000012725000000})

Dies ist technisch gesehen nicht wirklich eine Nachschlageformel, aber sie verhält sich wie eine. Mit nur einem einzigen zu verarbeitenden Array gibt die SUMPRODUCT-Funktion die Summe des Arrays zurück, 12.7250.

Sehen dieses Beispiel für eine ausführlichere Erklärung.

Konvertieren Sie die Dezimalzeit in Stunden und Minuten

Im Geiste kommt die SUMPRODUCT-Option der SUMIFS-Formel am nächsten, da wir summieren Werte basierend auf mehreren Kriterien. Es funktioniert nach wie vor einwandfrei, solange es nur ein übereinstimmendes Ergebnis gibt.

Zusammenfassung

SUMIF kann tatsächlich wie eine Nachschlageformel verwendet werden, und die Konfiguration kann einfacher sein als eine konventionellere Nachschlageformel. Wenn Sie mit einem großen Datensatz arbeiten, ist SUMIFS außerdem eine sehr schnelle Option. Sie müssen jedoch zwei wichtige Anforderungen beachten: