Formel

Summiere jede n-te Zeile

Summiere Jede N Te Zeile

Summiere jede n-te Zeile

  Excel-Formel: Summiere jede n-te Zeile Allgemeine Formel
=SUM(FILTER(data,MOD(SEQUENCE(ROWS(data)),n)=0))
Zusammenfassung

Um jede n-te Zeile (d. h. jede zweite Zeile, jede dritte Zeile usw.) zu summieren, können Sie eine Formel verwenden, die auf dem basiert FILTER-Funktion , das MOD-Funktion , und die SUM-Funktion . Im gezeigten Beispiel lautet die Formel in Zelle F6:



=SUM(FILTER(B5:B16,MOD(SEQUENCE(ROWS(B5:B16)),F5)=0))

Mit der Zahl 3 in Zelle F5 für n , das Ergebnis ist 70.

Erläuterung

In diesem Beispiel besteht das Ziel darin, jeden n-ten Wert in einem Datenbereich zu summieren, wie im oben gezeigten Arbeitsblatt zu sehen ist. Zum Beispiel, wenn n =2, wir wollen jeden zweiten Wert (jeden zweiten Wert) summieren, wenn n =3, wir wollen den dritten Wert summieren und so weiter. In dem neuste Excel-Version Am einfachsten geht das mit der FILTER-Funktion. Im Legacy-Excel, Sie können eine alternative Formel verwenden, die auf der SUMPRODUCT-Funktion basiert, wie unten erklärt.





Alle Daten liegen im Bereich B5:B16 und n wird in Zelle F5 als 3 eingetragen.

Wie kopiere ich eine Tabelle in Excel?

Beispielformel

Im gezeigten Beispiel lautet die Formel in Zelle F6:



=SUM(FILTER(B5:B16,MOD(SEQUENCE(ROWS(B5:B16)),F5)=0))

Auf hoher Ebene verwendet diese Formel die FILTER-Funktion, um Werte zu extrahieren, die jeder n-ten Zeile oder Daten zugeordnet sind, und die SUM-Funktion, um die Summe der extrahierten Werte zurückzugeben.

Daten extrahieren

Von innen nach außen arbeitend, besteht der erste Schritt bei diesem Problem darin, die Daten zu sammeln, die summiert werden sollten. Dies geschieht mit der FILTER-Funktion so was:

FILTER(B5:B16,include)

wo enthalten stellt die Formellogik dar, die benötigt wird, um auf jeden n-ten Wert abzuzielen (jeden 3. Wert im Beispiel). Um die benötigte Logik zu konstruieren, verwenden wir eine Kombination aus der MOD-Funktion und der SEQUENCE-Funktion:

MOD(SEQUENCE(ROWS(B5:B16)),F5)=0)

Die ROWS-Funktion gibt die Anzahl der Zeilen im Bereich B5:B16 zurück, also 12:

Anzahl der Monate zwischen zwei Daten in Excel
MOD(SEQUENCE(12),F5)=0)

Mit 12 als Zeilenargument ist die SEQUENCE-Funktion gibt eine Zahl zurück Reihe von 12 Zahlen wie folgt:

{1;2;3;4;5;6;7;8;9;10;11;12}

Wenn wir das obige Array und den Wert für n (3) in die Formel einsetzen, haben wir:

MOD({1;2;3;4;5;6;7;8;9;10;11;12},3)=0)

Das MOD-Funktion gibt den Rest jeder Zahl im Array dividiert durch 3 zurück:

{1;2;0;1;2;0;1;2;0;1;2;0}=0

Das Ergebnis von MOD wird mit Null verglichen, und das Ergebnis ist ein Array von TRUE- und FALSE-Werten:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Beachten Sie, dass jeder 3. Wert WAHR ist. Dies ist der Wert, der an FILTER als zurückgegeben wird enthalten Streit. FILTER verwendet dieses Array, um Werte im Bereich B5:B16 zu 'filtern'. Nur Werte, die TRUE zugeordnet sind, passieren die Filteroperation. Das Ergebnis ist ein Array, das jeden 3. Wert in den Daten enthält:

{20;15;10;25}

FILTER liefert dieses Array von Werten direkt an die SUM-Funktion, die als Endergebnis die Summe (70) zurückgibt. Diese Formel ist dynamisch. Wenn beispielsweise der Wert für n in Zelle F5 in 2 (jeder zweite Wert) geändert wird, ist das neue Ergebnis 120.

Alte Excel-Formel

Im ältere Versionen von Excel die die Funktionen FILTER oder SEQUENCE nicht enthalten, können Sie eine andere Formel verwenden, die auf der Funktion SUMPRODUCT basiert:

Verknüpfung, um zum Ende der Excel-Tabelle zu gelangen
=SUMPRODUCT(--(MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0),B5:B16)

Das Konzept ähnelt der oben erläuterten Formel, aber der Ansatz ist anders. Anstatt nur bestimmte Werte zu extrahieren, „löscht“ diese Formel die Sonstiges Werte. Zuerst verwendet die Formel die ROW-Funktion, um einen relativen Satz von Zeilennummern zu erstellen:

ROW(B5:B16)-ROW(B5)+1

gibt ein numerisches Array wie folgt zurück:

{1;2;3;4;5;6;7;8;9;10;11;12}

Innerhalb der SUMPRODUCT-Funktion verwenden wir wieder die MOD-Funktion, um einen Filter zu konstruieren:

=MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0
=MOD({1;2;3;4;5;6;7;8;9;10;11;12},F5)=0

und MOD gibt ein Array von TRUE FALSE-Werten wie folgt zurück:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Beachten Sie auch hier, dass jeder dritte Wert WAHR ist. EIN Doppel negativ (--) wird verwendet, um die Werte TRUE und FALSE in 1s und 0s umzuwandeln:

=SUMPRODUCT({0;1;0;1;0;1;0;1;0;1;0;1},B5:B16)

Das SUMMENPRODUKT multipliziert dann die beiden Arrays miteinander und gibt die Summe der Produkte zurück. Nur die Werte in B5:B6, die mit Einsen verknüpft sind, überleben diese Operation. Die anderen Werte werden 'genullt':

=SUMPRODUCT({0;0;20;0;0;15;0;0;10;0;0;25}) // returns 70

Das Endergebnis ist 70. Auch diese Formel ist dynamisch. Wenn der Wert für n in Zelle F5 in 2 (jeder zweite Wert) geändert wird, ist das neue Ergebnis 120.

Autor David Bruns


^