Im vorherigen Artikel haben wir gelernt, wie man obere oder untere N-Werte summiert. In diesem Artikel versuchen wir, die oberen oder unteren N-Werte mit einem Kriterium zu summieren.
Summe der TOP N Werte mit Kriterien
Wie löst man das Problem?
Für diesen Artikel müssen wir die SUMMENPRODUKT-Funktion verwenden. Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier werden uns ein Bereich und ein Kriterium vorgegeben. Wir müssen die Top-5-Werte im Bereich ermitteln und die Summe der Werte basierend auf den angegebenen Kriterien ermitteln.
Generische Formel:
= SUMMENPRODUKT ( LARGE ( ( (Liste = Kriterien) * (Bereich), { 1 , 2 ,… . ,n } } )
aufführen: Kriterienliste
Kriterien: Kriterien zu erfüllen
Bereich: Wertebereich
Werte: Zahlen, die durch Kommas getrennt sind. Wenn Sie beispielsweise die Top 3 Werte finden möchten, verwenden Sie { 1 , 2 , 3 }.
Beispiel:
Hier haben wir die Datensatzwerte von A1:D50.
Zuerst müssen wir die Top-5-Werte mit der LARGE-Funktion finden, die der Stadt "Boston" entspricht, und dann eine Summenoperation über diese 5 Werte durchführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten
Verwenden Sie die Formel:
= SUMMENPRODUKT ( GROSS ( ( Stadt = "Boston" ) * (Menge) , { 1 , 2 , 3 , 4 , 5 } ) )
Erläuterung:
- Citys "Boston" passt zu der genannten City-Reihe. Dies gibt ein Array von true und false zurück.
- Die Funktion LARGE gibt die 5 oberen Zahlenwerte aus dem Mengenbereich zurück und gibt das Array an die Funktion SUMMENPRODUKT zurück.
= SUMMENPRODUKT { 193 , 149 , 138 , 134 , 123 }
- Die SUMPRODUCT-Funktion ruft ein Array der Top-5-Werte ab, wobei ein Array der 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 Top 5 Zahlen zu erhalten.
Wie Sie im obigen Schnappschuss sehen können, ist diese Summe 737. Die Summe der Werte 193 + 149 + 138 + 134 + 123 = 737.
Sie können die obigen Werte im Datensatz mit der Excel-Filteroption überprüfen. Wenden Sie den Filter auf die Kopfzeile Stadt & Menge an und klicken Sie auf die Pfeilschaltfläche in der erscheinenden Stadtkopfzeile. Befolgen Sie die Schritte wie unten gezeigt.
Schritte:
- Wählen Sie die Überschriftszelle Stadt aus. Filter mit Verknüpfung anwenden Strg + Umschalt + L
- 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 aus.
- Sortieren Sie die Liste vom größten zum kleinsten und Sie können alle Top-5-Werte anzeigen, die wir mit der Formel berechnet haben.
Wie Sie im obigen Gif sehen können, alle 5 Werte, die den angegebenen Kriterien entsprechen. 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 zu berechnen für n (große) Anzahl von Werten in einem großen Bereich.
Verwenden Sie die Formel:
= SUMMENPRODUKT ( GROSS ( ( Stadt = "Boston" ) * (Menge), REIHE ( INDIREKT ( "1:10" ) )
Hier generieren wir die Summe der Top-10-Werte, indem wir ein Array von 1 bis 10 erhalten { 1; 2 ; 3 ; 4; 5; 6; 7; 8; 9; 10 } mit den Excel-Funktionen ZEILE & INDIREKT.
Hier haben wir die Summe der Top 10 Zahlen, die 1147 ergibt.
Summe der unteren N-Werte mit Kriterien
Wie löst man das Problem?
Für diesen Artikel müssen wir die SUMMENPRODUKT-Funktion verwenden. Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier wird uns ein Bereich gegeben und wir müssen 5 Werte im Bereich unterschreiten und die Summe der Werte erhalten.
Generische Formel:
{ = SUM ( KLEIN ( WENN ( Stadt = "Boston", Menge ) , { 1 , 2 , 3 , 4 , 5 } ) )
Bereich: Wertebereich
Werte : durch Kommas getrennte Zahlen. Wenn Sie beispielsweise die unteren 3 Werte finden möchten, verwenden Sie { 1 , 2 , 3 }.
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 einen Wertebereich von A1:D50.
Hier wird der Stadt- und Mengenbereich mit dem benannten Bereich Excel-Tool angegeben.
Zuerst müssen wir die unteren fünf Werte mit der SMALL-Funktion finden, die den Kriterien entspricht, und dann eine Summenoperation über diese fünf Werte durchführen. Jetzt verwenden wir die folgende Formel, um die Summe zu erhalten
Verwenden Sie die Formel:
{ = SUM ( KLEIN ( WENN ( Stadt = "Boston", Menge ) , { 1 , 2 , 3 , 4 , 5 } ) )
Verwenden Sie KEINE geschweiften Klammern manuell. Gelockte Klammern, die mit dem angewendet werden Strg + Umschalt + Eingabetaste anstelle von nur Eintreten.
Erläuterung:
- Die SMALL-Funktion mit der IF-Funktion gibt die unteren 5 numerischen Werte zurück, die der Stadt "Boston" entsprechen, und gibt das Array an die SUM-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.
Hier wird der Ort & Mengenbereich als benannter Bereich angegeben. Drücken Sie Strg + Umschalt + Eingabetaste um die SUMME der unteren 5 Zahlen zu erhalten, da dies eine Matrixformel 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 zu berechnen für n (große) Anzahl von Werten in einem großen Bereich.
Verwenden Sie die Formel:
{ = SUMME ( KLEIN ( WENN ( Stadt = "Boston" , Menge ) , ZEILE ( INDIREKT ("1:10") ) ) }
Verwenden Sie die geschweiften Klammern NICHT manuell. Verwenden Sie statt der Eingabetaste Strg + Umschalt + Eingabetaste.
Hier erzeugen wir die Summe der unteren 10 Werte, indem wir ein Array von 1 bis 10 erhalten { 1; 2 ; 3 ; 4; 5; 6; 7; 8; 9; 10 } mit den Excel-Funktionen ZEILE & INDIREKT.
Hier haben wir die Summe der unteren 10 Zahlen, die 155 ergeben.
Hier sind einige Beobachtungsnotizen, die unten gezeigt werden.
Anmerkungen:
- Die Formel funktioniert nur mit Zahlen.
- Die Formel funktioniert nur, wenn keine Duplikate in der Nachschlagetabelle vorhanden sind
- Die SUMPRODUCT-Funktion betrachtet nicht-numerische Werte (wie Text abc) und Fehlerwerte (wie #NUM!, #NULL!) als Nullwerte.
- Die Funktion SUMMENPRODUKT betrachtet den logischen Wert TRUE als 1 und False als 0.
- Das Argumentarray muss die gleiche Länge wie die Funktion haben.
Ich hoffe, dieser Artikel zum Zurückgeben der Summe der oberen 5 Werte oder der unteren 5 Werte mit Kriterien in Excel ist erklärend. Weitere Artikel zu den 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 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
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
So verwenden Sie Platzhalter in Excel : Zählen Sie Zellen, die mit Phrasen übereinstimmen, indem Sie die Platzhalter in Excel verwenden
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität
Bearbeiten einer Dropdown-Liste
Absolute Referenz in Excel
Bei bedingter Formatierung
Wenn mit Wildcards
Vlookup nach Datum
Konvertieren Sie Zoll in Fuß und Zoll in Excel 2016
Vor- und Nachname in Excel eintragen
Zählen Sie Zellen, die entweder mit A oder B übereinstimmen