Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger funktionerne SUMPRODUCT og SUMIFS til at opsummere data, der opfylder visse kriterier på tværs af flere ark i Excel og Google Sheets.
Regelmæssig sum på tværs af flere ark
Nogle gange kan dine data spænde over flere regneark i en Excel -fil. Dette er almindeligt for data, der indsamles periodisk. Hvert ark i en projektmappe kan indeholde data for en bestemt tidsperiode. Vi ønsker en formel, der summerer data indeholdt i to eller flere ark.
SUM -funktionen giver dig mulighed for nemt at summere data på tværs af flere ark ved hjælp af en 3D -reference:
| 1 | = SUM (Ark1: Ark2! A1) |
Dette er dog ikke muligt med SUMIFS -funktionen. I stedet skal vi bruge en mere kompliceret formel.
Sum hvis på tværs af flere ark
Dette eksempel vil opsummere Antal planlagte leverancer for hver Kunde over flere regneark, der hver indeholder data vedrørende en anden måned ved hjælp af funktionerne SUMIFS, SUMPRODUCT og INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Lad os gå igennem denne formel.
Trin 1: Opret en SUMIFS -formel til kun 1 inputark:
Vi bruger SUMIFS -funktionen til at opsummere Antal planlagte leverancer ved Kunde for et enkelt inputdatablad:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Trin 2: Tilføj en arkreference til formlen
Vi holder formelresultatet det samme, men vi angiver, at inputdataene er i det kaldte ark 'Trin 2'
| 1 | = SUMIFS ('Trin 2'! D3: D7, 'Trin 2'! C3: C7, H3) |
Trin 3: Nest inde i en SUMPRODUCT -funktion
For at forberede formlen til at udføre SUMIFS -beregninger over flere ark og derefter for at summere resultaterne sammen, tilføjer vi en SUMPRODUCT -funktion omkring formlen
| 1 | = SUMPRODUCT (SUMIFS ('Trin 3'! D3: D7, 'Trin 3'! C3: C7, H3)) |
Brug af SUMIFS -funktionen på et ark giver en enkelt værdi. På tværs af flere ark udsender SUMIFS -funktionen en række værdier (en for hvert regneark). Vi bruger SUMPRODUCT -funktionen til at sammenlægge værdierne i dette array.
Trin 4: Udskift arkreferencen med en liste over arknavne
Vi ønsker at udskifte Arknavn del af formlen med en dataliste, der indeholder værdierne: Jan, Feb, Mar, og Apr. Denne liste er gemt i cellerne F3: F6.
INDIRECT -funktionen sikrer, at tekstlisten vises Ark navne behandles som en del af en gyldig cellereference i SUMIFS -funktionen.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
I denne formel refererer den tidligere skrevne rækkevidde:
| 1 | 'Trin 3'! D3: D7 |
Er erstattet af:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Anførselstegn gør formlen vanskelig at læse, så her vises den med tilføjede mellemrum:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Ved at bruge denne måde at referere til en liste over celler giver os også mulighed for at opsummere data fra flere ark, der ikke følger en numerisk listeformat. En standard 3D -reference ville kræve, at arknavnene var i stilen: Input1, Input2, Input3 osv., Men eksemplet ovenfor giver dig mulighed for at bruge en liste over eventuelle Ark navne og at få dem henvist til i en separat celle.
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist formlerne uden låste cellereferencer:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Men disse formler fungerer ikke korrekt, når de kopieres og indsættes andre steder i din fil. I stedet skal du bruge låste cellereferencer som denne:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7")), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Læs vores artikel om låsning af cellereferencer for at lære mere.
Sum Hvis på tværs af flere ark i Google Sheets
Brug af INDIRECT -funktionen til at referere til en liste over ark i en SUMPRODUCT- og SUMIFS -funktion er i øjeblikket ikke mulig i Google Sheets.
I stedet kan der foretages separate SUMIFS -beregninger for hvert inputark og resultaterne tilføjes sammen:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (februar! D3: D7, februar! C3: C7, H3)+SUMIFS (mar! D3: D7, mar! C3: C7, H3)+SUMIFS (apr. D3: D7, apr! C3: C7, H3) |
