Generische Formel
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Zusammenfassung
Um die niedrigsten n-Werte in einem Bereich zu summieren, können Sie eine Formel verwenden, die auf der SMALL-Funktion und der SUMPRODUCT-Funktion basiert. In der generischen Form der Formel (oben) repräsentiert rng einen Bereich von Zellen, die numerische Werte enthalten, und n repräsentiert die Anzahl der niedrigsten zu summierenden Werte. Im gezeigten Beispiel enthält E5 die folgende Formel:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
Dies gibt die Summe der drei kleinsten Werte in B5 zurück: B14, 60
Erläuterung
In seiner einfachsten Form gibt SMALL den "n-ten kleinsten" Wert in einem Bereich zurück. Beispielsweise:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Wenn Sie SMALL jedoch als zweites Argument eine Array-Konstante (z. B. eine Konstante in der Form (1,2,3)) zuweisen, gibt SMALL anstelle eines einzelnen Ergebnisses ein Array von Ergebnissen zurück. Beispielsweise:
=SMALL(A1:A10,(1,2,3))
gibt den 1., 2. und 3. kleinsten Wert im Bereich A1: A10 zurück.
Im gezeigten Beispiel arbeitet SMALL von innen nach außen und gibt die 3 kleinsten Werte im Bereich B5 zurück: B14:
=SMALL(B4:B14,(1,2,3))
Das Ergebnis ist ein Array wie folgt:
(10,20,30)
Dieses Array wird direkt an die Funktion SUMPRODUCT zurückgegeben, die die Zahlen summiert und die Summe zurückgibt:
SUMPRODUCT((10,20,30)) // returns 60
Array-Formel mit SUM
Es ist üblich, SUMPRODUCT wie oben zu verwenden, da es Arrays nativ verarbeiten kann, ohne als Array-Formel einzugeben. Sie können jedoch auch eine Array-Formel mit der SUMME-Funktion wie folgt schreiben:
(=SUM(SMALL(B4:B13,(1,2,3))))
Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Wenn n groß wird
Wenn n groß wird, wird es mühsam, die Array-Konstante von Hand zu erstellen. Das Eingeben einer Array-Konstante mit 20 oder 30 Elementen dauert lange. In diesem Fall können Sie eine Verknüpfung zum Erstellen der Array-Konstante verwenden, die die Funktionen ROW und INDIRECT verwendet. Um beispielsweise die unteren 20 Werte in einem Bereich namens "rng" zusammenzufassen, können Sie eine Formel wie die folgende schreiben:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Hier konvertiert INDIRECT die Zeichenfolge "1:20" in den Bereich 1:20, der direkt an SMALL zurückgegeben wird.
Variable n
Um die a-Formel einzurichten, bei der n eine Variable in einer anderen Zelle ist, können Sie in INDIRECT verketten. Wenn A1 beispielsweise N enthält, können Sie Folgendes verwenden:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Dadurch kann ein Benutzer den Wert von n direkt im Arbeitsblatt ändern.
