So verwenden Sie die SUM- & IF-Funktion stattdessen für die SUMPRODUCT- oder SUMIFS-Funktion in Excel

Anonim

In diesem Artikel erfahren Sie, wie Sie die IF-Funktion anstelle der SUMPRODUCT- und SUMIFS-Funktion in Excel verwenden.

Szenario:

In einfachen Worten, wenn wir mit einem langen verstreuten Datensatz arbeiten, müssen wir manchmal die Summe von Zahlen mit einigen Kriterien darüber finden. Zum Beispiel, die Summe der Gehälter in einer bestimmten Abteilung zu finden oder mehrere Kriterien für Datum, Namen, Abteilung oder sogar Zahlen wie Gehälter unter dem Wert oder die Menge über dem Wert zu verwenden. Dazu verwenden Sie in der Regel die Funktion SUMMENPRODUKT oder ZUSAMMENFASSUNG. Aber Sie würden es nicht glauben, Sie führen die gleiche Funktion mit der Excel-Basisfunktion IF-Funktion aus.

Wie löst man das Problem?

Sie müssen sich überlegen, wie dies möglich ist, logische Operationen über Tabellenarrays mit der IF-Funktion auszuführen. Die IF-Funktion in Excel ist sehr nützlich. Sie wird Sie durch einige schwierige Aufgaben in Excel oder anderen Programmiersprachen führen. Die IF-Funktion testet Bedingungen für Array, die den erforderlichen Werten entsprechen, und gibt das Ergebnis als Array zurück, das den True-Bedingungen als 1 und False als 0 entspricht.

Für dieses Problem verwenden wir die folgenden Funktionen:

  1. Summenfunktion
  2. WENN-Funktion

Wir benötigen diese oben genannten Funktionen und ein gewisses Grundverständnis für die Datenverarbeitung. logische Bedingungen auf Arrays können mit logischen Operatoren angewendet werden. Diese logischen Operatoren funktionieren sowohl mit Text als auch mit Zahlen. Unten hier ist die generische Formel. { } geschweifte Klammern ist das magische Werkzeug, um Array-Formeln mit IF-Funktion auszuführen.

Generische Formel:

{ = SUM ( WENN ( (logisch_1) * (logisch_2) *… * (logisch_n) , Summe_Array ) ) }

Hinweis: Für geschweifte Klammern ( { } ) Verwenden Strg + Umschalt + Eingabetaste beim Arbeiten mit Arrays oder Bereichen in Excel. Dadurch werden standardmäßig geschweifte Klammern für die Formel generiert. Versuchen Sie NICHT, geschweifte Klammern hart zu codieren.

Logisch 1: testet Bedingung 1 auf Array 1

Logisch 2: testet Bedingung 2 auf Array 2 und so weiter

sum_array : Array, Operationssumme wird ausgeführt

Beispiel :

All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen. Hier haben wir Daten von gelieferten Produkten in verschiedene Städte zusammen mit entsprechenden Kategoriefeldern und Mengen. Hier haben wir die Daten und wir müssen die Anzahl der nach Boston gesendeten Cookies ermitteln, wenn die Anzahl größer als 40 ist.

Datentabelle und Kriterientabelle sind in der obigen Abbildung dargestellt. Zum Verständnis haben wir benannte Bereiche für die verwendeten Arrays verwendet. Die benannten Bereiche sind unten aufgeführt.

Hier :

Stadt für Array A2:A17 definiert.

Kategorie für Array B2:A17 definiert.

Für Array C2:C17 definierte Menge.

Jetzt sind Sie bereit, das gewünschte Ergebnis mit der folgenden Formel zu erhalten.

Verwenden Sie die Formel:

{ = SUMME ( WENN ( (City="Boston") * (Category="Cookies") * (Menge>40) , Menge)) }

Erläuterung :

  1. City ="Boston" : prüft die Werte im Stadtbereich auf Übereinstimmung mit "Boston".
  2. Category="Cookies" : überprüft die Werte im Kategoriebereich auf Übereinstimmung mit "Cookies".
  3. Menge > 40 : prüft die Werte im Mengenbereich bis ma
  4. Menge ist ein Array, bei dem die Summe erforderlich ist.
  5. Die IF-Funktion überprüft alle Kriterien und ein Sternchen (*) multipliziert alle Array-Ergebnisse.

= SUMME ( WENN ( { 0; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 } , { 33 ; 87 ; 58 ; 38 ; 54 ; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))

  1. Jetzt gibt die IF-Funktion nur die Mengen zurück, die den Einsen entsprechen, und der Rest wird ignoriert.
  2. Die SUM-Funktion gibt die SUMME zurück.

Jetzt addiert sich nur die Menge, die 1 entspricht, um das Ergebnis zu erhalten.


Wie Sie sehen, wird die Menge 43 zurückgegeben, aber es werden drei Cookie-Bestellungen mit den Mengen 38, 36 und 43 nach "Boston" geliefert. Wir brauchten eine Summe der Mengen, bei denen die Menge über 40 liegt. Die Formel gibt also nur 43 zurück. Verwenden Sie jetzt andere Kriterien, um die Summenmenge für Stadt : "Los Angeles" & Kategorie : "Bars" zu erhalten und die Menge kleiner als 50 zu sein.

Verwenden Sie die Formel

{ = SUMME ( WENN ( ( City = "Los Angeles") * (Category="Bars") * (Menge < 50), Menge) ) }

Wie Sie sehen, gibt die Formel als Ergebnis die Werte 86 zurück. Dies ist die Summe von 2 Bestellungen, die die Bedingungen mit den Mengen 44 & 42 erfüllen. In diesem Artikel wird veranschaulicht, wie eine verschachtelte IF-Formel durch eine einzelne IF-Formel in einer Arrayformel ersetzt wird. Dies kann verwendet werden, um die Komplexität in komplexen Formeln zu reduzieren. Dieses spezielle Problem könnte jedoch leicht mit der Funktion SUMIFS oder SUMPRODUCT gelöst werden.

Verwendung der SUMMENPRODUKT-Funktion:

Die SUMPRODUCT-Funktion gibt die Summe der entsprechenden Werte im Array zurück. Wir werden also die Arrays dazu bringen, 1s für die True-Anweisungswerte und 0s für die False-Anweisungswerte zurückzugeben. Die letzte Summe wird also übereinstimmen, wenn alle Aussagen wahr sind.

Verwenden Sie die Formel:

= SUMMENPRODUKT ( -- (Stadt = "Boston") , -- (Kategorie = "Cookies") , -- (Menge > 40) , Menge )

-- : Operation, die verwendet wird, um alle TRUEs in 1s und False in 0 umzuwandeln.

Die Funktion SUMMENPRODUKT überprüft erneut die SUMME der Menge, die von der oben erläuterten SUM- und IF-Funktion zurückgegeben wird.

Für das zweite Beispiel ist das Ergebnis ähnlich.

Wie Sie sehen können, kann die SUMPRODUCT-Funktion die gleiche Aufgabe ausführen.

Hier sind alle Beobachtungshinweise zur Verwendung der Formel.

Anmerkungen:

  1. Das sum_array in der Formel funktioniert nur mit Zahlen.
  2. Wenn die Formel den Fehler #VALUE zurückgibt, überprüfen Sie, ob die geschweiften Klammern in der Formel vorhanden sein müssen, wie in den Beispielen im Artikel gezeigt.
  3. Negation (--) char ändert Werte, TRUEs oder 1s in FALSE oder 0s und FALSE oder 0s in TRUEs oder 1s .
  4. Operationen wie gleich ( = ), kleiner gleich ( <= ), größer als ( > ) oder ungleich ( ) kann innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.

Ich hoffe, dieser Artikel über die Verwendung der IF-Funktion anstelle der SUMPRODUCT- und SUMIFS-Funktion in Excel ist erklärend. Weitere Artikel zum Thema Summenformeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie es mit Ihren fristarts auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site

So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.

SUM, wenn das Datum dazwischen liegt : Gibt die SUMME der Werte zwischen den angegebenen Daten oder dem Zeitraum in Excel zurück.

Summe, wenn das Datum größer als das angegebene Datum ist: Gibt die SUMME der Werte nach dem angegebenen Datum oder Zeitraum in Excel zurück.

2 Möglichkeiten, in Excel nach Monat zu summieren: Gibt die SUMME der Werte innerhalb eines bestimmten Monats in Excel zurück.

So summieren Sie mehrere Spalten mit Bedingung: Gibt die SUMME von Werten über mehrere Spalten mit Bedingung in Excel zurück.

Populäre Artikel :

50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.

Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.

So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.

So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.