Generische Formel
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Zusammenfassung
Um die unteren n Werte in einem Bereichsübereinstimmungskriterium zu summieren, können Sie eine Arrayformel verwenden, die auf der SMALL-Funktion basiert und in die SUM-Funktion eingeschlossen ist. In der generischen Form der Formel (oben), range1 den Bereich von Zellen gegenüber darstellt Kriterien , range2 enthält numerische Werte , aus der unteren Wert abgerufen werden, und N repräsentiert „n - te“.
Im Beispiel enthält die aktive Zelle die folgende Formel:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Wobei Farbe der benannte Bereich B5: B12 und Wert der benannte Bereich C5: C12 ist.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
In seiner einfachsten Form gibt SMALL den "N-ten kleinsten" Wert in einem Bereich mit dieser Konstruktion zurück:
=SMALL (range,N)
Also zum Beispiel:
=SMALL (C5:C12,2)
gibt den zweitkleinsten Wert im Bereich C5: C12 zurück, der im gezeigten Beispiel 5 ist.
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. Also die Formel:
=SMALL (C5:C12, (1,2,3))
gibt den 1., 2. und 3. kleinsten Wert C5: C12 in einem Array wie diesem zurück: (4,5,7).
Der Trick hier besteht also darin, die Werte basierend auf der Farbe zu filtern, bevor SMALL ausgeführt wird. Wir tun dies mit einem Ausdruck, der auf der IF-Funktion basiert:
IF(color=E5,value)
Dadurch wird das in SMALL eingegebene Array von Werten erstellt. Im Wesentlichen gelangen nur Werte, die der Farbe Rot zugeordnet sind, in das Array. Wenn Farbe gleich "Rot" ist, enthält das Array eine Zahl, und wenn die Farbe nicht Rot ist, enthält das Array FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Die SMALL-Funktion ignoriert die FALSE-Werte und gibt die 3 kleinsten Werte im Array zurück: (4,8,10). Die SUMME-Funktion gibt das Endergebnis 22 zurück.
