Wie man Sum Oben oder Unten N Werte mit Kriterien
Im vorherigen Artikel haben wir gelernt, wie man obere oder untere N-Werte zusammenfasst.
In diesem Artikel versuchen wir, die oberen oder unteren N-Werte mit einem Kriterium zusammenzufassen.
Summe der TOP N-Werte mit Kriterien
Wie löse ich das Problem?
Für diesen Artikel müssen wir den link: / summing-excel-sumproduct-function [SUMPRODUCT function] verwenden. Nun werden wir aus diesen Funktionen eine Formel machen. Hier erhalten wir einen Bereich und ein Kriterium. Wir müssen die Top 5 Werte im Bereich erhalten und die Summe der Werte basierend auf den angegebenen Kriterien erhalten.
_Generische Formel: _
= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )
Liste: Kriterienliste Kriterien: Kriterien für den Übereinstimmungsbereich: Wertebereich Werte: Zahlen, die durch Kommas getrennt sind. Wenn Sie die drei besten Werte suchen möchten, verwenden Sie \ {1, 2, 3}.
Beispiel:
Hier haben wir die Datensatzwerte von A1: D50.
Zuerst müssen wir die fünf besten Werte mit der LARGE-Funktion finden, die der Stadt „Boston“ entspricht, und dann die Summenoperation über diese fünf Werte ausführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten. _Verwenden Sie die Formel: _
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )
_Erläuterung: _
-
Das „Boston“ der Stadt entspricht dem genannten Stadtbereich. Dies gibt ein Array von true und false zurück.
-
link: / logische-formeln-excel-große-funktion [Die GROSSE]Funktion gibt die Top 5 numerischen Werte aus dem Mengenbereich zurück und gibt das Array an die SUMPRODUCT-Funktion zurück.
SUMPRODUCT \ {193, 149, 138, 134, 123} * Die SUMPRODUCT-Funktion ruft ein Array mit den Top-5-Werten ab, wobei ein Array mit den Top-5-Zahlen die Summe dieser Zahlen zurückgibt.
Hier wird der Stadt- und Mengenbereich als benannter Bereich angegeben. Drücken Sie die Eingabetaste, um die Summe der fünf besten Zahlen zu erhalten.
Wie Sie im obigen Schnappschuss sehen können, beträgt diese Summe 737. Die Summe der Werte 193 + 149 + 138 + 134 + 123 = 737.
Sie können die obigen Werte im Datensatz mithilfe der Excel-Filteroption überprüfen.
Wenden Sie den Filter auf die Kopfzeile Stadt & Menge an und klicken Sie auf die Pfeilschaltfläche in der Kopfzeile Stadt. Befolgen Sie die unten gezeigten Schritte.
Schritte:
-
Wählen Sie die Kopfzeilenzelle Stadt aus. Wenden Sie den Filter mit der Tastenkombination Strg + Umschalt + L an. Klicken Sie auf den Pfeil, der als Filteroption angezeigt wird.
-
Wählen Sie die Option (Alle auswählen).
-
Wählen Sie nur die Stadt Boston aus.
-
Wählen Sie jetzt den Mengenkopf.
-
Sortieren Sie die Liste vom größten zum kleinsten und Sie können alle Top 5-Werte anzeigen, die wir mithilfe der Formel berechnet haben.
Wie Sie im obigen GIF sehen können, entsprechen alle 5 Werte den angegebenen Kriterien. Dies bedeutet auch, dass die Formel gut funktioniert, um die Anzahl dieser Werte zu ermitteln
GROSSE N Zahlen
Der obige Prozess wird verwendet, um die Summe einiger Zahlen von oben zu berechnen. Aber für n (große) Anzahl von Werten in einem langen Bereich zu berechnen.
Verwenden Sie die Formel:
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )
Hier erzeugen wir die Summe der Top-10-Werte, indem wir ein Array von 1 bis 10 \ {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10} mit den Excel-Funktionen ROW & INDIRECT.
Hier haben wir die Summe der Top 10 Zahlen, die 1147 ergeben.
Summe der unteren N-Werte mit Kriterien
Wie löse ich das Problem?
Für diesen Artikel müssen wir den link: / summing-excel-sumproduct-function [SUMPRODUCT function] verwenden. Nun werden wir aus diesen Funktionen eine Formel machen. Hier erhalten wir einen Bereich und wir müssen 5 Werte im Bereich unterlegen und die Summe der Werte erhalten.
Allgemeine Formel:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Bereich: Wertebereich Werte: Zahlen, die durch Kommas getrennt werden. Wenn Sie die unteren 3 Werte ermitteln möchten, verwenden Sie \ {1, 2, 3}.
Beispiel:
All dies könnte verwirrend zu verstehen sein. Testen wir diese Formel also, indem wir sie im folgenden Beispiel ausführen.
Hier haben wir einen Wertebereich von A1: D50.
Hier wird der Bereich Stadt & Menge unter Verwendung des benannten Bereichs-Excel-Werkzeugs angegeben.
Zuerst müssen wir die unteren fünf Werte mit der SMALL-Funktion finden, die den Kriterien entspricht, und dann die Summenoperation über diese fünf Werte ausführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten. Verwenden Sie die Formel:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Verwenden Sie geschweifte Klammern NICHT manuell. Geschweifte geschweifte Klammern werden mit Strg + Umschalt + Eingabetaste anstelle von Eingabetaste angewendet.
Erläuterung:
-
link: / static-formulas-excel-small-function [SMALL]
Funktion mit IF-Funktion gibt die unteren 5 numerischen Werte zurück, die mit City „Boston“ übereinstimmen, und gibt das Array an die SUMME-Funktion zurück.
SUMME (\ {23, 27, 28, 28, 30}))
-
Die SUM-Funktion ruft das Array der unteren 5 Werte ab, das ein Array der unteren 5 Zahlen enthält, und gibt die Summe der mit STRG + UMSCHALT + EINGABETASTE verwendeten Zahlen zurück.
Hier wird der Stadt- und Mengenbereich als benannter Bereich angegeben. Drücken Sie Strg + Umschalt + Eingabetaste, um die Summe der unteren 5 Zahlen zu erhalten, da dies eine Array-Formel ist.
Wie Sie im obigen Schnappschuss sehen können, beträgt diese Summe 136.
Der obige Prozess wird verwendet, um die Summe einiger Zahlen von unten zu berechnen. Aber für n (große) Anzahl von Werten in einem langen Bereich zu berechnen.
Verwenden Sie die Formel:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }
Verwenden Sie die geschweiften Klammern NICHT manuell. Verwenden Sie Strg + Umschalt + Eingabetaste anstelle der Eingabetaste.
Hier erzeugen wir die Summe der unteren 10 Werte, indem wir ein Array von 1 bis 10 \ {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10} mit den Excel-Funktionen ROW & INDIRECT.
Hier haben wir die Summe der unteren 10 Zahlen, die 155 ergeben.
Hier sind einige Beobachtungsnotizen unten gezeigt.
Anmerkungen:
-
Die Formel funktioniert nur mit Zahlen.
-
Die Formel funktioniert nur, wenn die Nachschlagetabelle keine Duplikate enthält. Die SUMPRODUCT-Funktion berücksichtigt nicht numerische Werte (wie Text abc) und Fehlerwerte (wie #NUM !, #NULL!) Als Nullwerte.
-
Die Funktion SUMPRODUCT betrachtet den Logikwert TRUE als 1 und False als 0.
-
Das Argumentarray muss dieselbe Länge wie die Funktion haben.
Hoffe, dieser Artikel über die Rückgabe der Summe der oberen 5 Werte oder der unteren 5 Werte mit Kriterien in Excel ist erklärend. Weitere Artikel zu SUMPRODUCT-Funktionen finden Sie hier. Bitte teilen Sie Ihre Anfrage unten im Kommentarfeld. Wir werden Ihnen helfen.
Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. 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: / Summierungssumme-wenn-Datum-größer-als-gegebenes-Datum ist [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 summieren]: Gibt die Summe der Werte über mehrere Spalten mit Bedingung in Excel zurück `link: / tips-excel- Platzhalter [Verwendung von Platzhaltern in Excel *] `: Zählen Sie Zellen, die mit Phrasen übereinstimmen, mithilfe der Platzhalter in Excel
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]
link: / excel-generals-wie-man-eine-dropdown-liste-in-microsoft-excel bearbeitet [Dropdown-Liste bearbeiten]
link: / excel-bereichsname-absolute-referenz-in-excel [Absolute referenz in Excel]
link: / tips-bedingte-formatierung-mit-if-Anweisung [Wenn mit bedingter Formatierung]
link: / logische Formeln-wenn-Funktion-mit-Platzhaltern [Wenn mit Platzhaltern]
link: / lookup-formulas-vlookup-by-date-in-excel [Vlookup-by-date]
link: / tips-Zoll-zu-Fuß [Zoll in Fuß und Zoll in Excel 2016 konvertieren]
link: / excel-text-editing-und-format-join-first-and-last-name-in-excel [Vor- und Nachname in Excel verbinden]
link: / count-count-Zellen-die-entweder-a-oder-b entsprechen [Zähle Zellen, die entweder A oder B entsprechen]