Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du bruker SUMPRODUCT og SUMIFS -funksjonene til å summere data som oppfyller visse kriterier på tvers av flere ark i Excel og Google Sheets.
Vanlig sum på tvers av flere ark
Noen ganger kan dataene dine strekke seg over flere regneark i en Excel -fil. Dette er vanlig for data som samles inn med jevne mellomrom. Hvert ark i en arbeidsbok kan inneholde data for en angitt tidsperiode. Vi vil ha en formel som summerer data i to eller flere ark.
SUM -funksjonen lar deg enkelt summere data på tvers av flere ark ved hjelp av en 3D -referanse:
| 1 | = SUMME (Ark1: Ark2! A1) |
Dette er imidlertid ikke mulig med SUMIFS -funksjonen. I stedet må vi bruke en mer komplisert formel.
Sum hvis på tvers av flere ark
Dette eksemplet vil oppsummere Antall planlagte leveranser for hver Kunde over flere regneark, som hver inneholder data knyttet til en annen måned, ved å bruke funksjonene SUMIFS, SUMPRODUCT og INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
La oss gå gjennom denne formelen.
Trinn 1: Lag en SUMIFS -formel for bare 1 inndataark:
Vi bruker SUMIFS -funksjonen for å summere Antall planlagte leveranser av Kunde for et enkelt inndatablad:
| 1 | = SUMMER (D3: D7, C3: C7, H3) |
Trinn 2: Legg til en arkreferanse til formelen
Vi holder formelresultatet det samme, men vi spesifiserer at inndataene er i arket som heter 'Steg 2'
| 1 | = SUMIFS ('Trinn 2'! D3: D7, 'Trinn 2'! C3: C7, H3) |
Trinn 3: Nest inne i en SUMPRODUCT -funksjon
For å forberede formelen for å utføre SUMIFS -beregninger over flere ark og deretter for å summere resultatene sammen, legger vi til en SUMPRODUCT -funksjon rundt formelen
| 1 | = SUMPRODUCT (SUMIFS ('Trinn 3'! D3: D7, 'Trinn 3'! C3: C7, H3)) |
Bruk av SUMIFS -funksjonen på ett ark gir en enkelt verdi. På tvers av flere ark sender SUMIFS -funksjonen ut en rekke verdier (en for hvert regneark). Vi bruker SUMPRODUCT -funksjonen til å summe verdiene i denne matrisen.
Trinn 4: Erstatt arkreferansen med en liste over arknavn
Vi ønsker å erstatte Arkets navn del av formelen med en dataliste som inneholder verdiene: Jan, Feb, Mar, og Apr. Denne listen er lagret i cellene F3: F6.
INDIRECT -funksjonen sikrer at tekstlisten vises Arknavn behandles som en del av en gyldig cellereferanse i SUMIFS -funksjonen.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
I denne formelen refererer den tidligere skrevne referansen:
| 1 | 'Trinn 3'! D3: D7 |
Er erstattet av:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Anførselstegn gjør formelen vanskelig å lese, så her vises den med ekstra mellomrom:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Ved å bruke denne måten å referere til en celleliste, kan vi også oppsummere data fra flere ark som ikke følger en numerisk listestil. En standard 3D -referanse vil kreve at bladnavnene er i stilen: Input1, Input2, Input3, etc., men eksemplet ovenfor lar deg bruke en liste over alle Arknavn og få dem referert i en egen celle.
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist formlene uten låste cellereferanser:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Men disse formlene fungerer ikke som de skal når de kopieres og limes inn andre steder i filen. I stedet bør du bruke låste cellereferanser slik:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7")), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
Sum Hvis på tvers av flere ark i Google Regneark
Å bruke INDIRECT -funksjonen til å referere til en liste over ark i en SUMPRODUCT og SUMIFS -funksjon er for øyeblikket ikke mulig i Google Regneark.
I stedet kan separate SUMIFS -beregninger gjøres for hvert inndataark og resultatene legges sammen:
| 1234 | = SUMMER (Jan! D3: D7, Jan! C3: C7, H3)+SUMMER (februar! D3: D7, februar! C3: C7, H3)+SUMMER (Mar! D3: D7, Mar! C3: C7, H3)+SUMMER (apr. D3: D7, apr! C3: C7, H3) |
