Wie SUM & IF-Funktion statt für SUMPRODUCT oder SUMIFS Funktion in Excel verwenden
In diesem Artikel erfahren Sie, wie Sie die IF-Funktion anstelle der SUMPRODUCT- und SUMIFS-Funktion in Excel verwenden.
Szenario:
Mit einfachen Worten, wenn wir mit einem lange verstreuten Datensatz arbeiten, müssen wir manchmal die Summe der Zahlen mit einigen Kriterien darüber finden. Wenn Sie beispielsweise die Summe der Gehälter in einer bestimmten Abteilung ermitteln oder mehrere Kriterien für Datum, Namen, Abteilung oder sogar Zahlen wie Gehälter unter dem Wert oder Menge über dem Wert haben. Dazu verwenden Sie normalerweise die Funktion SUMPRODUCT oder SUMIFS. Aber Sie würden nicht glauben, dass Sie dieselbe Funktion mit der IF-Funktion der Excel-Grundfunktion ausführen.
Wie löse ich das Problem?
Sie müssen sich überlegen, wie dies möglich ist, um mithilfe der IF-Funktion logische Operationen über Tabellenarrays auszuführen. Wenn die Funktion in Excel sehr nützlich ist, werden Sie durch einige schwierige Aufgaben in Excel oder anderen Codierungssprachen geführt. Die IF-Funktion testet Bedingungen für ein 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:
-
link: / math-and-trig-excel-sum-function [SUM-Funktion] -
link: / tips-if-condition-in-excel [IF-Funktion]
Wir werden diese oben genannten Funktionen und einen grundlegenden Sinn für die Datenoperation benötigen. Logische Bedingungen auf Arrays können mit logischen Operatoren angewendet werden. Diese Logikoperatoren arbeiten sowohl mit Text als auch mit Zahlen. Unten ist die generische Formel. \ {} * geschweifte Klammern sind das magische Werkzeug, um Array-Formeln mit IF-Funktion auszuführen.
Allgemeine Formel:
|
\{* =
(logical_1) (logical_2) … (logical_n) , sum_array ) ) }* |
Hinweis: Verwenden Sie für geschweifte Klammern (\ {}) Strg + Umschalt + Eingabetaste *, wenn Sie mit Arrays oder Bereichen in Excel arbeiten. Dadurch werden standardmäßig geschweifte Klammern für die Formel generiert. Versuchen Sie NICHT, geschweifte Klammern hart zu codieren.
Logisch 1: Testbedingung 1 auf Array 1 Logisch 2: Testbedingung 2 auf Array 2 usw. sum_array: Array, Operationssumme wird ausgeführt
Beispiel:
All dies könnte verwirrend zu verstehen sein. Testen wir diese Formel, indem wir sie im folgenden 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 müssen die Menge der an Boston gesendeten Cookies finden, bei denen die Menge größer als 40 ist.
Datentabelle und Kriterientabelle sind im obigen Bild dargestellt. Zum Verständnis haben wir benannte Bereiche für die verwendeten Arrays verwendet. Benannte Bereiche sind unten aufgeführt.
Hier:
Stadt definiert für Array A2: A17.
Für Array B2 definierte Kategorie: A17.
Für Array C2: C17 definierte Menge.
Jetzt können Sie mit der folgenden Formel das gewünschte Ergebnis erzielen.
Verwenden Sie die Formel:
|
\{ =
(City=“Boston“) (Category=“Cookies“) (Quantity>40) , Quantity)) }* |
Erläuterung:
-
City = „Boston“: Überprüft, ob die Werte im Stadtbereich mit „Boston“ übereinstimmen.
-
Category = „Cookies“: Überprüft, ob die Werte im Kategoriebereich mit „Cookies“ übereinstimmen.
-
Menge> 40: Überprüft die Werte im Mengenbereich bis ma. Menge sein Array, wo Summe benötigt wird.
-
Die IF-Funktion überprüft alle Kriterien und das Sternchen char (*) multipliziert alle Array-Ergebnisse.
SUMME (IF (\ {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}))
-
Jetzt gibt die IF-Funktion nur die Mengen zurück, die der 1 entsprechen, und der Rest wird ignoriert.
-
Die SUM-Funktion gibt die SUMME zurück.
Jetzt addiert sich die Menge, die 1 entspricht, nur, um das Ergebnis zu erhalten.
Wie Sie sehen können, wird Menge 43 zurückgegeben, aber es werden drei Cookie-Bestellungen mit den Mengen 38, 36 und 43 an „Boston“ geliefert. Wir benötigten eine Mengenmenge, bei der die Menge über 40 liegt. Die Formel gibt also nur 43 zurück. Verwenden Sie jetzt andere Kriterien, um die SUMMENmenge für die Stadt zu erhalten: „Los Angeles“ & Kategorie: „Balken“ & Menge kleiner als 50.
Verwenden Sie die Formel *
|
\{* =
„Los Angeles“) (Category=“Bars“) (Quantity < 50), Quantity ) ) } |
Wie Sie sehen können, gibt die Formel als Ergebnis die Werte 86 zurück. Dies ist die Summe von 2 Bestellungen, die die Bedingungen mit den Mengen 44 und 42 erfüllen.
Dieser Artikel zeigt, wie ein verschachtelte IF-Formel durch eine einzelne IF in einer Array-Formel 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 SUMPRODUCT-Funktion:
Die Funktion SUMPRODUCT gibt die Summe der entsprechenden Werte im Array zurück. Wir werden also die Arrays dazu bringen, 1s a die True-Anweisungswerte und 0s die False-Anweisungswerte zurückzugeben. Die letzte Summe entspricht also, wenn alle Aussagen wahr sind.
Verwenden Sie die Formel:
|
= ( — (City = „Boston“) , — (Category = „Cookies“) , — (Quantity > 40) , Quantity ) |
-: Operation, mit der alle TRUEs in 1s und False in 0 konvertiert werden.
Die Funktion SUMPRODUCT überprüft erneut die Summe der von der oben erläuterten Funktion SUM und IF zurückgegebenen Mengen.
In ähnlicher Weise bleibt das Ergebnis für das zweite Beispiel gleich.
Wie Sie sehen, kann die Funktion SUMPRODUCT dieselbe Aufgabe ausführen.
Hier finden Sie alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
-
Das sum_array in der Formel funktioniert nur mit Zahlen.
-
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.
-
Negation (-) char ändert Werte, TRUEs oder 1s in FALSEs oder 0s und FALSEs oder 0s in TRUEs oder 1s.
-
Operationen wie gleich (=), kleiner als gleich (⇐), größer als (>) oder ungleich (<> *) können innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.
Hoffe, dieser Artikel über die Verwendung der IF-Funktion anstelle der SUMPRODUCT- und SUMIFS-Funktion in Excel ist erklärend. Weitere Artikel zu Summierungsformeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns an [email protected]
Verwandte Artikel:
link: / summing-excel-sumproduct-function [Verwendung der SUMPRODUCT-Funktion in Excel]: Gibt die SUMME nach Multiplikation von Werten in mehreren Arrays in Excel zurück.
link: / summing-sum-if-date-is-Between [SUMME wenn Datum zwischen liegt]: Gibt die Summe der Werte zwischen bestimmten Daten oder Zeiträumen in Excel zurück.
link: / Summe-Summe-wenn-Datum-ist-größer-als-gegebenes-Datum [Summe, wenn Datum größer als gegebenes Datum ist]: * Gibt die Summe der Werte nach dem gegebenen Datum oder Zeitraum in Excel zurück.
link: / Summieren-2-Wege-zu-Summe-von-Monat-in-Excel [2 Möglichkeiten zur Summe nach Monat in Excel]: * Gibt die Summe der Werte innerhalb eines bestimmten Monats in Excel zurück.
link: / summieren-wie-mehrere-Spalten-mit-Bedingung summieren [Wie mehrere Spalten mit Bedingung summiert werden]: * Gibt die Summe der Werte über mehrere Spalten mit Bedingung in Excel zurück.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfung zur Steigerung Ihrer Produktivität]: Schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter. link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.