Generische Formel
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Zusammenfassung
Um die obersten n Werte in einem Bereichsübereinstimmungskriterium 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), Bereich einen Bereich von Zellen darstellt, die verglichen werden Kriterien stellen Werte numerische Werte , aus denen Spitzenwerte abgerufen werden, und N die Idee Nth Wert darstellt.
Im Beispiel enthält die aktive Zelle die folgende Formel:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Wobei Farbe der benannte Bereich B5: B12 und Wert der benannte Bereich C5: C12 ist.
Erläuterung
In seiner einfachsten Form gibt LARGE mit dieser Konstruktion den "N-ten größten" Wert in einem Bereich zurück:
=LARGE (range,N)
Also zum Beispiel:
=LARGE (C5:C12,2)
gibt den zweitgrößten Wert im Bereich C5: C12 zurück, der im gezeigten Beispiel 12 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 (C5:C12, (1,2,3))
gibt den 1., 2. und 3. größten Wert C5: C12 in einem Array wie folgt zurück: (12,12,10)
Der Trick hier besteht also darin, die Werte basierend auf der Farbe zu filtern, bevor LARGE ausgeführt wird. Wir machen das mit dem Ausdruck:
(color=E5)
Dies führt zu einem Array von TRUE / FALSE-Werten. Während der Multiplikationsoperation werden diese Werte in Einsen und Nullen gezwungen:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Das Endergebnis ist also, dass nur Werte, die der Farbe "Rot" zugeordnet sind, die Operation überleben:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
und die anderen Werte werden auf Null gezwungen.
Hinweis: Diese Formel verarbeitet keinen Text im Wertebereich. Siehe unten.
Umgang mit Text in Werten
Wenn Sie irgendwo in den Wertebereichen Text haben, löst die Funktion LARGE einen Fehler #VALUE aus und verhindert, dass die Formel funktioniert.
Um Text im Wertebereich zu verarbeiten, können Sie die IFERROR-Funktion folgendermaßen hinzufügen:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Hier fangen wir Fehler von LARGE ab, die durch Textwerte verursacht werden, und ersetzen sie durch Null. Für die Verwendung von IF in LARGE muss die Formel mit Strg + Umschalt + Eingabe eingegeben werden. Daher wechseln wir zu SUMME anstelle von SUMPRODUCT.
Hinweis: Ich bin auf diese Formel gestoßen, die von Barry Houdini auf stackoverflow veröffentlicht wurde.
