Ladda ner exempelarbetsboken
Denna handledning visar hur du använder funktionerna SUMPRODUCT och SUMIFS för att summera data som uppfyller vissa kriterier i flera ark i Excel och Google Sheets.
Vanlig summa över flera ark
Ibland kan din data sträcka sig över flera kalkylblad i en Excel -fil. Detta är vanligt för data som samlas in regelbundet. Varje blad i en arbetsbok kan innehålla data för en viss tidsperiod. Vi vill ha en formel som summerar data i två eller flera blad.
SUMM -funktionen låter dig enkelt summera data över flera ark med hjälp av a 3D -referens:
| 1 | = SUMMA (Sheet1: Sheet2! A1) |
Detta är dock inte möjligt med SUMIFS -funktionen. Istället måste vi använda en mer komplicerad formel.
Summa om över flera ark
Detta exempel summerar Antal planerade leveranser för varje Kund över flera kalkylblad, var och en innehåller data som rör en annan månad, med funktionerna SUMIFS, SUMPRODUCT och INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Låt oss gå igenom denna formel.
Steg 1: Skapa en SUMIFS -formel för endast 1 inmatningsark:
Vi använder SUMIFS -funktionen för att summera Antal planerade leveranser förbi Kund för ett enda inmatningsdatablad:
| 1 | = SUMMER (D3: D7, C3: C7, H3) |
Steg 2: Lägg till en bladreferens till formeln
Vi håller formelresultatet detsamma, men vi anger att inmatningsdata finns i det anropade arket 'Steg 2'
| 1 | = SUMIFIER ('Steg 2'! D3: D7, 'Steg 2'! C3: C7, H3) |
Steg 3: Nest inuti en SUMPRODUCT -funktion
För att förbereda formeln för att utföra SUMIFS -beräkningar över flera ark och sedan för att summera resultaten tillsammans lägger vi till en SUMPRODUCT -funktion runt formeln
| 1 | = SUMPRODUCT (SUMIFS ('Steg 3'! D3: D7, 'Steg 3'! C3: C7, H3)) |
Att använda SUMIFS -funktionen på ett ark ger ett enda värde. I flera ark matar SUMIFS -funktionen ut en rad värden (en för varje kalkylblad). Vi använder SUMPRODUCT -funktionen för att summera värdena i denna array.
Steg 4: Ersätt arkreferensen med en lista med arknamn
Vi vill byta ut Arkets namn del av formeln med en datalista som innehåller värdena: Jan, Feb, Mar, och Apr. Denna lista lagras i cellerna F3: F6.
INDIRECT -funktionen säkerställer att textlistan visas Bladnamn behandlas som en del av en giltig cellreferens i SUMIFS -funktionen.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
I denna formel, den tidigare skrivna intervallreferensen:
| 1 | "Steg 3"! D3: D7 |
Ersätts med:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Citattecken gör formeln svårläst, så här visas den med extra mellanslag:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Genom att använda detta sätt att referera till en lista med celler kan vi också sammanfatta data från flera blad som inte följer en numerisk liststil. En standard 3D -referens skulle kräva att bladnamnen har stilen: Input1, Input2, Input3, etc., men exemplet ovan låter dig använda en lista över alla Bladnamn och att få dem att refereras i en separat cell.
Låsning av cellreferenser
För att göra våra formler lättare att läsa har vi visat formlerna utan låsta cellreferenser:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Men dessa formler fungerar inte korrekt när de kopieras och klistras in någon annanstans i din fil. Istället bör du använda låsta cellreferenser så här:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7")), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Läs vår artikel om låsa cellreferenser för att lära dig mer.
Summa om flera ark i Google Kalkylark
Att använda INDIRECT -funktionen för att referera till en lista över blad i en SUMPRODUCT- och SUMIFS -funktion är för närvarande inte möjligt i Google Kalkylark.
Istället kan separata SUMIFS -beräkningar göras för varje inmatningsark och resultaten läggas ihop:
| 1234 | = SUMMER (Jan! D3: D7, Jan! C3: C7, H3)+SUMMER (feb! D3: D7, feb! C3: C7, H3)+SUMIFIER (Mar! D3: D7, Mar! C3: C7, H3)+SUMMER (apr! D3: D7, apr! C3: C7, H3) |
