In diesem Artikel erfahren Sie, wie Sie die N größten Werte in einem Bereich in Excel summieren.
Szenario:
In einfachen Worten, bei der Arbeit mit Datenzahlen wird manchmal eine Bedingung gegeben, z. B. wenn wir die Summe der Zahlen der oberen 5 Zahlen des Arrays oder der unteren Zahlen des Arrays erhalten müssen. Sie können die Lösung dieses Problems einfach mit den unten erläuterten Excel-Funktionen durchführen.
Summe der TOP N Werte
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 übersteigen und die Summe der Werte erhalten.
Generische Formel:
| = SUMMENPRODUKT ( GROSS ( Bereich, { 1 , 2 ,… . ,n } } ) |
Bereich : Wertebereich
Werte : durch Kommas getrennte Zahlen. Wenn Sie beispielsweise die Top 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:A23.
Hier wird der Bereich mit dem benannten Bereich Excel-Tool angegeben.
Zuerst müssen wir die Top-5-Werte mit der LARGE-Funktion finden 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 ( Bereich, { 1 , 2 , 3 , 4 , 5 } ) ) |
Erläuterung:
- Die Funktion LARGE gibt die fünf oberen numerischen Werte zurück und gibt das Array an die Funktion SUMPRODUCT zurück.
= SUMMENPRODUKT ({149, 123, 100, 87, 85}))
- Die SUMPRODUCT-Funktion ruft das Array der Top-5-Werte ab, das ein Array der Top-5-Zahlen hat und die SUMME dieser Zahlen zurückgibt.
Hier wird der Bereich als benannter Bereich angegeben. Drücken Sie die Eingabetaste, um die SUMME der Top-5-Zahlen zu erhalten.
Wie Sie in der obigen Momentaufnahme sehen können, ist diese Summe 544. Die Summe der Werte 149 + 123 + 100 + 87 + 85 = 544.
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 ( Bereich, 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 {149; 123 ; 100 ; 87 ; 85; 82; 61; 58; 54; 51 }), was 850 ergibt.
Summe der unteren N-Werte
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:
| = SUMMENPRODUKT ( KLEIN ( Bereich, { 1 , 2 ,… . ,n } } ) |
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:A23.
Hier wird der Bereich mit dem benannten Bereich Excel-Tool angegeben.
Zuerst müssen wir die unteren fünf Werte mit der SMALL-Funktion finden 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:
| = SUMMENPRODUKT ( KLEIN ( Bereich, { 1 , 2 , 3 , 4 , 5 } ) ) |
Erläuterung:
- Die Funktion SMALL gibt die unteren 5 numerischen Werte zurück und gibt das Array an die Funktion SUMPRODUCT zurück.
= SUMMENPRODUKT ( { 23 , 27 , 28 , 28 , 30 } ) )
- Die SUMPRODUCT-Funktion ruft das Array der unteren 5 Werte ab, das ein Array der unteren 5 Zahlen hat und die SUMME dieser Zahlen zurückgibt.
Hier wird der Bereich als benannter Bereich angegeben. Drücken Sie die Eingabetaste, um die SUMME der unteren 5 Zahlen zu erhalten.
Wie Sie im obigen Schnappschuss sehen können, ist diese Summe 136. Die Summe der Werte 23 + 27 + 28 + 28 + 30 = 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:
| = SUMMENPRODUKT ( KLEIN ( Bereich, REIHE ( INDIREKT ( "1:10" ) ) |
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 307 ergibt.
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 Argument Array muss die gleiche Länge haben, sonst die Funktion.
Ich hoffe, dieser Artikel zum Summieren der N größten Werte in einem Bereich in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln 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 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 :
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.