Nach OFFSET-Gruppen in Zeilen und Spalten summieren

Anonim

In diesem Artikel erfahren Sie, wie Sie die Summe nach Gruppen in Zeilen und Spalten in Excel berechnen.

Zum Beispiel haben Sie eine große Liste von Daten und müssen die SUMME der Zahlen mit einem Satz von n Werten finden.

Für diesen Artikel benötigen wir die folgenden Funktionen:

  1. SUM-Funktion
  2. OFFSET-Funktion

Die SUM-Funktion ist eine eingebaute mathematische Funktion, die die SUMME von Zahlen zurückgibt, die der Funktion als Argument übergeben wurden
Syntax:

= SUMME ( Zahl1 , [Zahl2],… )

Die Funktion OFFSET gibt eine Zelle oder eine angegebene Anzahl von Zellenbereichen zurück, die durch eine Zellreferenz durchlaufen wurden.
Syntax:

= OFFSET (Referenz, Zeilen, Spalten, [Höhe], [Breite])

Referenz : Zellreferenz, von der aus gestartet werden soll
Zeilen : Anzahl der Zeilen, die von der Zellreferenz durchquert werden
Spalten: Anzahl der Spalten, die von der Zellreferenz durchlaufen werden.
[Höhe] : Höhe oder Anzahl der Zellen in der Spalte, die ausgewählt werden soll.
[Breite] : Breite oder Anzahl der Zellen in der Zeile, die ausgewählt werden soll.

Die ersten 3 Argumente der Funktion sind der Pfad zur gewünschten Zelle.
4. und 5. Argument der Funktion gibt die Anzahl der zurückzugebenden Zellen an.
Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier geben wir die Daten an und wir mussten die SUM mit einer bestimmten Anzahl von Zellen finden.
Verwenden Sie die Formel:

= SUM ( OFFSET ( Referenz , ro , co , he , wi ) )

Erläuterung:

  • OFFSET-Funktion startet bei Referenz und Traverse ro Anzahl der Reihen und co Anzahl der Spalten aus dem Zellbezug.
  • Jetzt beginnt die Funktion mit der Auswahl von Zahlengruppen mit er Anzahl der Werte in der Spalte und wir Anzahl der Werte in der Zeile.
  • Die OFFSET-Funktion gibt nun das Zahlenarray an die SUM-Funktion zurück.
  • Die SUM-Funktion addiert alle Zahlen und gibt die SUMME zurück.

Lassen Sie uns diese Formel testen, indem wir sie an einem Beispiel ausführen

Hier haben wir Daten von A1 : D16. Wir müssen die SUMME von Menge und Gesamtpreis in einer Gruppe von 5 mit der angegebenen Referenz aus der Zelle A1 finden.

Zuerst müssen wir die SUMME der ersten 5 Mengen finden, die Werte sind {100, 23, 38, 91, 53}

Verwenden Sie die Formel:

= SUMME ( OFFSET ( A1 , 1 , 2 , 5 , 1 ) )

Erläuterung:

  • Die OFFSET-Funktion beginnt bei der Zelle A1 und erreicht die Zelle C2, indem sie die 1 Zeile und 2 Spalten durchquert.
  • Wir brauchen zuerst 5 Zellen von C2- bis C6-Zelle. Die Höhe 5 und Breite geben uns die Zahlen wie unten gezeigt zurück.

= SUMME ( { 100; 23 ; 38 ; 91 ; 53 } )

  • Jetzt gibt die SUM-Funktion die SUMME der Werte zurück.

Hier wird A1 als Zellreferenz angegeben und der Rest der Argumente wird direkt an die Funktion übergeben.

Wie Sie im obigen Schnappschuss sehen können, ergibt sich die SUMME der ersten fünf Mengen als 305 .
Jetzt erhalten wir die SUM für alle anderen 5er-Gruppen, indem wir das Row-Argument der Funktion ändern

Verwenden Sie die Formel:

= SUMME ( OFFSET ( A1 , 6 , 2 , 5 , 1 ) )


Wie Sie die SUM der nächsten fünf im obigen Schnappschuss sehen können. Ändern Sie für die nächsten fünf das Zeilenargument auf 11.


Rufen Sie die SUM von TotalPrice für dasselbe ab, indem Sie das Column-Argument in die Funktion ändern.


Wie Sie aus der obigen Formel sehen können, können Sie die SUM-Werte nach Gruppen mit der OFFSET-Funktion erhalten.

Hinweise: Die Funktion gibt #WERT zurück! Fehler, wenn das Argument der Funktion nicht numerisch ist.

Ich hoffe, dieser Artikel zum Summieren nach OFFSET-Gruppen in Zeilen und Spalten in Excel ist erklärend. Weitere Artikel zu den SUMPRODUCT-Funktionen finden Sie hier. Bitte teilen Sie Ihre Anfrage unten im Kommentarfeld. Wir werden Ihnen helfen.

So verwenden Sie die OFFSET-Funktion in Excel

So verwenden Sie die SUM-Funktion in Excel

So verwenden Sie die ISFORMULA-Funktion in Excel

So entfernen Sie Text in Excel ausgehend von einer Position

So verwenden Sie die ROWS-Funktion in Excel

Validierung von Texteingaben

Dropdown-Liste in Excel mit Farbe erstellen

Entfernen Sie führende und nachgestellte Leerzeichen aus dem Text in Excel

populäre Artikel

Bearbeiten einer Dropdown-Liste

Absolute Referenz in Excel

Bei bedingter Formatierung

Wenn mit Wildcards

Vlookup nach Datum

Vor- und Nachname in Excel eintragen

Zählen Sie Zellen, die entweder mit A oder B übereinstimmen

Konvertieren Sie Zoll in Fuß und Zoll in Excel 2016

50 Excel-Shortcut zur Steigerung Ihrer Produktivität