Generische Formel
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Zusammenfassung
Um die Spitzenwerte in einem Bereich zu summieren, können Sie eine Formel verwenden, die auf der Funktion LARGE basiert und in die Funktion SUMPRODUCT eingeschlossen ist. In der generischen Form der Formel (oben) repräsentiert rng einen Bereich von Zellen, die numerische Werte enthalten, und N repräsentiert die Idee des N-ten Wertes.
Im Beispiel enthält die aktive Zelle die folgende Formel:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Erläuterung
In seiner einfachsten Form gibt LARGE den "N-ten größten" Wert in einem Bereich zurück. Zum Beispiel die Formel:
=LARGE(B4:B13, 2)
gibt den zweitgrößten Wert im Bereich B4: B13 zurück, der im obigen Beispiel die Zahl 9 ist.
Wenn Sie jedoch LARGE als zweites Argument eine "Array-Konstante" (z. B. eine Konstante in der Form (1,2,3)) geben, gibt LARGE anstelle eines einzelnen Ergebnisses ein Array von Ergebnissen zurück. Also die Formel:
=LARGE(B4:B13,(1,2,3))
gibt den 1., 2. und 3. größten Wert im Bereich B4: B13 zurück. Im obigen Beispiel, in dem B4: B13 die Zahlen 1-10 enthält, ist das Ergebnis von LARGE das Array (8,9,10). SUMPRODUCT summiert dann die Zahlen in diesem Array und gibt eine Summe von 27 zurück.
SUMME statt SUMPRODUKT
SUMPRODUCT ist eine flexible Funktion, mit der Sie Zellreferenzen für k innerhalb der LARGE-Funktion verwenden können.
Wenn Sie jedoch eine einfache fest codierte Array-Konstante wie (1,2,3) verwenden, können Sie einfach die SUMME-Funktion verwenden:
=SUM(LARGE(B4:B13,(1,2,3)))
Beachten Sie, dass Sie diese Formel als Array-Formel eingeben müssen, wenn Sie Zellreferenzen und keine Array-Konstante für k in LARGE verwenden.
Wenn N groß wird
Wenn N groß wird, wird es mühsam, die Array-Konstante von Hand zu erstellen. Wenn Sie die 20 oder 30 besten Werte in einer großen Liste zusammenfassen möchten, dauert das Eingeben einer Array-Konstante mit 20 oder 30 Elementen lange. In diesem Fall können Sie eine Verknüpfung zum Erstellen der Array-Konstante verwenden, die die Funktionen ROW und INDIRECT verwendet.
Wenn Sie beispielsweise die Top-20-Werte in einem Bereich namens "rng" summieren möchten, können Sie eine Formel wie die folgende schreiben:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variable N.
Bei unzureichenden Daten kann ein festes N Fehler verursachen. In diesem Fall können Sie eine Formel wie die folgende ausprobieren:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Hier verwenden wir MIN mit COUNT, um die obersten 3 Werte oder die Anzahl der Werte zu summieren, wenn sie kleiner als 3 sind.
