Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die Funktionen SUMPRODUCT und SUMIFS verwenden, um Daten, die bestimmte Kriterien erfüllen, über mehrere Blätter in Excel und Google Sheets zu summieren.
Regelmäßige Summe über mehrere Blätter
Manchmal können Ihre Daten mehrere Arbeitsblätter in einer Excel-Datei umfassen. Dies ist bei regelmäßig erfassten Daten der Fall. Jedes Blatt in einer Arbeitsmappe kann Daten für einen festgelegten Zeitraum enthalten. Wir wollen eine Formel, die Daten aus zwei oder mehr Blättern summiert.
Mit der SUM-Funktion können Sie ganz einfach Daten über mehrere Blätter hinweg summieren, indem Sie a 3D-Referenz:
| 1 | =SUMME(Blatt1:Blatt2!A1) |
Dies ist jedoch mit der SUMIFS-Funktion nicht möglich. Stattdessen müssen wir eine kompliziertere Formel verwenden.
Summe, wenn über mehrere Blätter hinweg
Dieses Beispiel summiert die Anzahl geplanter Lieferungen für jeden Kunde über mehrere Arbeitsblätter, von denen jedes Daten zu einem anderen Monat enthält, mithilfe der Funktionen SUMMEN, SUMMEN und INDIREKT:
| 1 | =SUMMENPRODUKT(SUMIFS(INDIREKT("'"&F3:F6&"'!"&"D3:D7"),INDIREKT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Gehen wir diese Formel durch.
Schritt 1: Erstellen Sie eine SUMIFS-Formel nur für 1 Eingabeblatt:
Wir verwenden die SUMIFS-Funktion, um die Anzahl geplanter Lieferungen von Kunde für ein einzelnes Eingabedatenblatt:
| 1 | =SUMIFS(D3:D7,C3:C7,H3) |
Schritt 2: Fügen Sie der Formel eine Blattreferenz hinzu
Wir behalten das Formelergebnis bei, geben jedoch an, dass sich die Eingabedaten in dem Blatt mit dem Namen . befinden 'Schritt 2'
| 1 | =SUMIFS('Schritt 2'!D3:D7,'Schritt 2'!C3:C7,H3) |
Schritt 3: Nest in einer SUMPRODUCT-Funktion
Um die Formel vorzubereiten, um SUMIFS-Berechnungen über mehrere Blätter hinweg durchzuführen und dann die Ergebnisse zusammenzufassen, fügen wir eine SUMMENPRODUKT-Funktion um die Formel hinzu
| 1 | =SUMMENPRODUKT(SUMIFS('Schritt 3'!D3:D7,'Schritt 3'!C3:C7,H3)) |
Die Verwendung der SUMIFS-Funktion auf einem Blatt ergibt einen einzelnen Wert. Über mehrere Blätter hinweg gibt die SUMIFS-Funktion ein Array von Werten aus (einer für jedes Arbeitsblatt). Wir verwenden die SUMPRODUCT-Funktion, um die Werte in diesem Array zu summieren.
Schritt 4: Ersetzen Sie die Blattreferenz durch eine Liste von Blattnamen
Wir möchten die . ersetzen Blattname Teil der Formel mit einer Datenliste mit den Werten: Jan, Februar, Beschädigen, und April. Diese Liste wird in den Zellen F3:F6 gespeichert.
Die INDIRECT-Funktion sorgt dafür, dass die Textliste mit Blattnamen wird in der SUMIFS-Funktion als Teil einer gültigen Zellreferenz behandelt.
| 1 | =SUMMENPRODUKT(SUMIFS(INDIREKT("'"&F3:F6&"'!"&"D3:D7"),INDIREKT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
In dieser Formel die zuvor geschriebene Bereichsreferenz:
| 1 | 'Schritt 3'!D3:D7 |
Wird ersetzt durch:
| 1 | INDIREKT("'"&F3:F6&"'!"&"D3:D7") |
Die Anführungszeichen erschweren das Lesen der Formel, daher wird sie hier mit zusätzlichen Leerzeichen angezeigt:
| 1 | INDIREKT ( " ' " & F3:F6 & " ' ! " & "D3:D7" ) |
Die Verwendung dieser Art des Verweisens auf eine Liste von Zellen ermöglicht es uns auch, Daten aus mehreren Blättern zusammenzufassen, die keinem numerischen Listenstil folgen. Eine Standard-3D-Referenz würde erfordern, dass die Blattnamen im Stil sind: Input1, Input2, Input3 usw., aber das obige Beispiel ermöglicht es Ihnen, eine Liste von beliebigen zu verwenden Blattnamen und sie in einer separaten Zelle referenziert zu haben.
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir die Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | =SUMMENPRODUKT(SUMIFS(INDIREKT("'"&F3:F6&"'!"&"D3:D7"),INDIREKT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | =SUMMENPRODUKT(SUMIFS(INDIREKT("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIREKT("'"&$F$3:$F$6&"'!"& "C3:C7"),H3)) |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
Summe, wenn über mehrere Blätter in Google Sheets
Die Verwendung der INDIRECT-Funktion zum Verweisen auf eine Liste von Blättern in einer SUMPRODUCT- und SUMIFS-Funktion ist derzeit in Google Sheets nicht möglich.
Stattdessen können für jedes Eingabeblatt separate SUMIFS-Berechnungen durchgeführt und die Ergebnisse addiert werden:
| 1234 | =SUMIFS(Jan!D3:D7,Jan!C3:C7,H3)+SUMIFS(Feb!D3:D7,Feb!C3:C7,H3)+SUMIFS(Mär!D3:D7,Mär!C3:C7,H3)+SUMIFS(Apr!D3:D7,Apr!C3:C7,H3) |
