Christian hat einen Bereich von 18 Zellen, die Werte enthalten. Er möchte die Summe der 12 größten Werte in diesem Bereich finden und fragt sich, wie er die Aufgabe erfüllen soll.

Es gibt verschiedene Möglichkeiten, wie Sie dieses Problem angehen können. Sie können beispielsweise die Werte so filtern, dass Sie nur die obersten zwölf Werte haben, und diese dann summieren. Bei anderen Ansätzen werden zusätzliche Spalten zum Speichern von Zwischenwerten verwendet. Ich gehe jedoch davon aus, dass Sie einen Ansatz bevorzugen, bei dem keine zusätzlichen Spalten verwendet werden.

Nehmen wir zunächst an, dass Ihr Bereich von 18 Zellen A1: A18 ist. Mit der Funktion LARGE können Sie die größten Werte ermitteln. Wenn Sie beispielsweise diese Formel verwenden, wird der zweitgrößte Wert im Bereich ermittelt:

=LARGE(A1:A18,2)

Dies ist der zweite Parameter der Funktion, der angibt, welcher größte Wert in der gewünschten Reihenfolge angezeigt wird. So können Sie die Summe der 12 größten Werte mithilfe einer Formel wie der folgenden ermitteln:

=LARGE(A1:A18,1)+LARGE(A1:A18,2)+LARGE(A1:A18,3)+LARGE(A1:A18,4)

+LARGE(A1:A18,5)+LARGE(A1:A18,6)+LARGE(A1:A18,7)+LARGE(A1:A18,8)

+LARGE(A1:A18,9)+LARGE(A1:A18,10)+LARGE(A1:A18,11)+LARGE(A1:A18,12)

Es gibt jedoch kürzere Formeln, mit denen Sie die Aufgabe ausführen können.

Zum Beispiel könnten Sie einfach die sechs kleinsten Werte von der Summe des Bereichs auf folgende Weise subtrahieren:

=SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3)

-SMALL(A1:A18,4)-SMALL(A1:A18,5)-SMALL(A1:A18,6)

Auf Wunsch können Sie auch die SUMIF-Funktion verwenden, um die Werte zu vergleichen und nur dann zu summieren, wenn das von Ihnen angegebene Kriterium erfüllt ist. Betrachten Sie beispielsweise diese beiden Formeln:

=SUMIF(A1:A18,">="&LARGE(A1:A18,12))

=SUMIF(A1:A18,">"&SMALL(A1:A18,6))

Die erste Formel summiert alle Werte, die größer oder gleich dem zwölftgrößten Wert im Bereich sind. Der zweite ist in seiner Wirkung ähnlich; Es summiert alle Werte, die größer als der sechstkleinste Wert sind.

Diese beiden Formeln funktionieren hervorragend, wenn an der festgelegten „Grenze“ keine doppelten Werte vorhanden sind. Wenn es jedoch mehr als einen Wert gibt, der als zwölftgrößter oder sechstkleinster Wert gilt, geben die Formeln nicht die erwarteten Summen zurück. In der ersten Formel ist die Summe zu groß (da alle doppelten Werte zur Summe addiert werden) und in der zweiten Formel ist die Summe zu klein (da alle doppelten Werte von der Summe ausgeschlossen sind).

Um dies zu umgehen, kehren Sie entweder zu einer der früheren Formeln zurück (diejenigen, die SUMIF nicht verwenden) oder ändern die SUMIF-Formel so, dass die Möglichkeit doppelter Werte berücksichtigt wird:

=SUMIF(A1:A18,">"&LARGE(A1:A18,12))+LARGE(A1:A18,12)

* (12-COUNTIF(A1:A18,">"&LARGE(A1:A18,12)))

Die vielleicht beste Formel, um das gewünschte Ergebnis zu erzielen, ist eine Wendung gegenüber der früheren Verwendung der LARGE-Funktion:

=SUM(LARGE(A1:A18,{1,2,3,4,5,6,7,8,9,10,11,12}))

Diese Formel verwendet ein Array (den Teil in geschweiften Klammern), ist jedoch keine Arrayformel. Es wird das Array als zweiter Parameter der LARGE-Funktion verwendet, wodurch alle zwölf größten Werte zurückgegeben werden. Diese werden dann summiert und ein einzelner Wert zurückgegeben.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9420) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Summiert nur den größten Teil eines Bereichs.