Wie ich in vielen meiner Blogs erwähnt habe, ist ein SUMPRODUCT eine sehr vielseitige Funktion und kann für mehrere Zwecke verwendet werden. In diesem Artikel werden wir sehen, wie wir diese Funktion verwenden können, um Werte mit mehreren oder Kriterien zu zählen.
Generische SUMPRODUCT-Formel zum Zählen mit mehreren oder Kriterien
| =SUMMENPRODUKT(--(((Kriterien1)+(Kriterien2)+… )>0) |
Kriterien1: Dies ist jedes Kriterium, das ein Array von TRUE und FALSE zurückgibt.
Kriterien2: Dies ist das nächste Kriterium, das Sie überprüfen möchten. Ebenso können Sie beliebig viele Kriterien festlegen.
Die obige generische Formel wird häufig modifiziert, um den Anforderungen zu entsprechen, mit mehreren ODER-Kriterien zu zählen. Aber die Grundformel ist diese. Zuerst werden wir anhand eines Beispiels sehen, wie dies funktioniert, und danach werden wir andere Szenarien besprechen, in denen Sie diese Formel ein wenig ändern müssen.
Problem:
Der Preis in einer Zelle sollte genau in 5 ähnliche Zahlungen aufgeteilt werden. Wenn wir einfach den Preis von 88,04 $ durch 5 teilen und das Ergebnis auf zwei Dezimalstellen runden, erhalten wir Einzelzahlungen von 17,61 $. Dies würde jedoch eine Gesamtzahlung von 88,05 USD ergeben, was nicht der genaue Betrag ist, mit dem wir beginnen. Daher möchten wir 5 ähnliche Zahlen berechnen, die sich zum Gesamtbetrag addieren.
Lösung:
Zuerst Berechnen Sie die erste Zahlung, indem Sie den Preis durch 5 teilen und das Ergebnis runden.
Verwenden Sie die RUNDE-Funktion in der folgenden Formel:
=RUNDE(B1/5,2)
Schritt 2:
Berechnen Sie jede verbleibende Zahlung.
Verwenden Sie die Funktionen ROUND, SUM und ROW in der folgenden Formel:
=RUNDE(($B$1-SUM($B$2:B2))/(5-REIHE()+REIHE($B$2)),2)
Beispiel:
Zählen Sie die Benutzer, wenn der Händlercode übereinstimmt oder das Jahr mit SUMPRODUCT
Hier haben wir also einen Datensatz von Verkäufern. Die Daten enthalten viele Spalten. Was wir tun müssen, ist die Anzahl der Benutzer zu zählen, die den Code "INKA" oder das Jahr "2016" haben. Stellen Sie sicher, dass wenn jemand beides hat (Code als "inka" und Jahr 2016) als 1 gezählt werden sollte.
Hören Sie, wir haben zwei Kriterien. Wir verwenden die oben genannte SUMPRODUCT-Formel:
| =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3))>0)) |
Code und Jahr werden hier als Bereiche bezeichnet. Dies ergibt 7.
In den Daten haben wir 5 Datensätze des INKA-Codes und 4 Datensätze des Jahres 2016. Aber 2 Datensätze haben sowohl "INKA" als auch 2016 als Code bzw. Jahr. Und diese Datensätze werden als 1 gezählt. Und so erhalten wir 7.
Mal sehen, wie es funktioniert?
Schauen wir uns also Schritt für Schritt an, wie die Formel gelöst wird, dann werde ich besprechen, wie es funktioniert.
-
- =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3))>0))
- SUMMENPRODUKT(--(({WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;… }+{FALSCH;FALSCH;FALSCH;WAHR;WAHR;… })>0))
- SUMMENPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMMENPRODUKT(--({WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;…})
- SUMMENPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Die doppelten negativen (--) Vorzeichen werden verwendet, um boolesche Werte in 1s und 0s umzuwandeln. Jeder TRUE-Wert im Array wird also in 1 und FALSE in 0 umgewandelt.
Hinzufügen weiterer Kriterien oder Kriterien zum Zählen mit SUMPRODUCT. Wenn Sie also mehr oder Kriterien zum Zählen hinzufügen müssen, können Sie der Funktion einfach Kriterien mit dem +-Zeichen hinzufügen.
Zum Beispiel, wenn Sie der obigen Formel ein weiteres Kriterium hinzufügen möchten, sodass die Anzahl der Mitarbeiter hinzugefügt wird, die mehr als 5 Produkte verkauft haben. Die SUMPRODUCT-Formel sieht einfach so aus:
| =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3)+(Umsatz>5))>0)) |
Einfach! ist es nicht?
Aber nehmen wir an, Sie möchten zwei Kriterien aus dem Codebereich haben. Angenommen, Sie möchten "INKB" zählen. Wie macht man das dann? Eine Methode verwendet die obige Technik, aber das würde sich wiederholen. Nehmen wir an, ich möchte 10 weitere Kriterien aus dem gleichen Bereich hinzufügen. In solchen Fällen ist diese Technik für das Zählen mit SUMPRODUCT nicht so schlau.
Nehmen wir an, wir haben Daten so angeordnet.
Die Kriteriencodes befinden sich in einer Zeile I2:J2. Wichtig ist hier die Anordnung der Daten. Die SUMMENPRODUKT-Formel für die 3 ODER-Kriterienanzahl-Einstellung lautet:
| =SUMMENPRODUKT(--(((Code=I2:J2)+(Jahr=I3:J3))>0)) |
Dies ist die SUMPRODUCT-Formel zum Zählen mit mehreren Kriterien, wenn mehrere Kriterien aus einem Bereich in Reihe geschrieben werden.
Die obige Formel gibt die richtige Antwort zurück, die 10 ist.
Wenn Sie in J3 ein beliebiges Jahr eingeben, addiert die Formel auch diese Zahl.
Dies wird verwendet, wenn sich die Kriterien in einer Zeile befinden. Funktioniert es, wenn die Kriterien in einer Spalte für den gleichen Bereich gelten? Nein, wird es nicht.
In diesem Beispiel müssen wir mehrere Codes zählen, aber diese Typcodes werden in eine Spalte geschrieben. Wenn wir die obige SUMPRODUCT-Formel verwenden, erhalten wir einen #N/A-Fehler. Wir werden nicht darauf eingehen, wie dieser Fehler auftritt, da dies diesen Artikel zu lang macht. Mal sehen, wie wir das schaffen können.
Damit diese Formel funktioniert, müssen Sie die Codekriterien in die TRANSPOSE-Funktion einschließen. Damit funktioniert die Formel.
| =SUMMENPRODUKT(--(((Code=TRANSPOSE(H3:H4))+(Jahr=TRANSPOSE(I3:I4)))>0)) |
Dies ist die Formel zum Zählen mit mehreren oder Bedingungen im gleichen Bereich, wenn Kriterien in einer Spalte aufgeführt sind.
Hier sind alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
- Die Formel funktioniert sowohl für Text als auch für Zahlen.
- Die Nachschlagewerte können als Zellbezug oder direkt mit Anführungszeichen ( " ) in der Formel als Argumente angegeben werden.
Ich hoffe, Sie haben verstanden, wie Sie einen Betrag in gleiche Zahlungen aufteilen und gleichzeitig Divisions- / Rundungsfehler in Excel vermeiden. Entdecken Sie hier weitere Artikel zu Excel-Lookup-Werten und Excel 2019-Funktionen. 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 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.
Verwenden Sie INDEX und MATCH, um den Wert zu suchen : INDEX & MATCH-Funktion zum Nachschlagen von Werten nach Bedarf.
Summenbereich mit INDEX in Excel : Verwenden Sie die INDEX-Funktion, um die SUMME der erforderlichen Werte zu ermitteln.
So verwenden Sie die INDEX-Funktion in Excel : Finden Sie den INDEX eines Arrays mit der INDEX-Funktion, die mit einem Beispiel erklärt wird.
So verwenden Sie die MATCH-Funktion in Excel : Finden Sie die MATCH im Array mit dem INDEX-Wert in der MATCH-Funktion, die mit einem Beispiel erklärt wird.
So verwenden Sie die LOOKUP-Funktion in Excel : Suchen Sie den Lookup-Wert im Array mithilfe der LOOKUP-Funktion, die mit einem Beispiel erklärt wird.
Populäre Artikel :
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die 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 SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
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.