Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du beregner delsummer etter gruppe ved hjelp av SUMIFS -funksjonen i Excel og Google Sheets.
Delsumtabell etter kategori eller gruppe
Først vil vi demonstrere hvordan du oppretter en dynamisk subtotalsammendragstabell fra et dataområde i enten Excel 365 og fremover eller Google Regneark.
Vi bruker den UNIKE funksjonen og SUMIFS -funksjonen til å automatisk summe summen Antall produkter av Produktgruppe:
| 1 | = SUMMER (C3: C11, B3: B11, E3) |
For å lage denne subtotaltabellen bruker vi standardapplikasjonen til SUMIFS -funksjonen for å summere Antall produkter som matcher hver Produktgruppe. Imidlertid, før dette er mulig, må vi lage en liste over unike Produktgrupper. Microsoft Excel 365 og Google Sheets -brukere har tilgang til UNIK funksjon for å lage en dynamisk liste over unike verdier fra et celleområde. I dette eksemplet legger vi til følgende formel i celle E3:
| 1 | = UNIK (B3: B11) |
Når denne formelen er angitt, opprettes det automatisk en liste under cellen for å vise alle unike verdier som finnes i Produktgruppe data rekkevidde. I dette eksemplet utvidet listen seg til å dekke E3: E5 for å vise alle tre unike Produktgruppe verdier.
Dette er en dynamisk matrisefunksjon der størrelsen på resultatlisten ikke trenger å være definert, og den vil automatisk krympe og vokse etterhvert som inndataverdiene endres.
Vær oppmerksom på at den unike funksjonen i Excel 365 ikke er store og små bokstaver, men i Google Regneark. Vurder listen {“A”; "en"; "B"; "C"}. Den UNIKE funksjonsutgangen avhenger av programmet:
- {"EN"; "B"; "C"} i Excel 365
- {"EN"; "en"; "B"; "C"} i Google Regneark
Hvis du bruker en Excel -versjon før Excel 365, må du ta en annen tilnærming. Dette diskuteres i neste avsnitt.
Delsumtabell etter kategori eller gruppe - Pre Excel 365
Hvis du bruker en versjon av Excel før Excel 365, er UNIK funksjon ikke tilgjengelig for bruk. For å replikere den samme oppførselen kan du kombinere INDEX -funksjonen og MATCH -funksjonen med en COUNTIF -funksjon for å lage en matriseformel for å lage en liste med unike verdier fra et utvalg av celler:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
For at denne formelen skal fungere, må de faste cellereferansene skrives nøye, med COUNTIF -funksjonen som refererer til området $ E $ 2: E2, som er området som starter fra E2 til cellen over cellen som inneholder formelen.
Formelen må også angis som en matriseformel ved å trykke CTRL + SKIFT + ENTER etter at den er skrevet. Denne formelen er a 1-cellers matriseformel, som deretter kan kopieres inn i cellene E4, E5 osv. Ikke skriv inn dette som en matriseformel for hele området E3: E5 i en handling.
På samme måte som i forrige eksempel, brukes en SUMIFS -funksjon til å summe summen av Antall produkter av Produktgruppe:
| 1 | = SUMMER (C3: C11, B3: B11, E3) |
Sum etter kategori eller gruppe - delsummer i datatabeller
Som et alternativ til oppsummeringstabellmetoden vist ovenfor, kan vi legge til delsummer direkte i en datatabell. Vi vil demonstrere dette ved å bruke IF -funksjonene sammen med SUMIFS -funksjonen for å legge til en Delsum etter gruppe til den originale datatabellen.
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Dette eksemplet bruker en SUMIFS -funksjon nestet i en IF -funksjon. La oss dele opp eksemplet i trinn:
For å legge til sammendragsstatistikk direkte i en datatabell, kan vi bruke SUMIFS -funksjonen. Vi starter med å summe Antall produkter som matcher det relevante Produktgruppe:
| 1 | = SUMMER (C3: C11, B3: B11, B3) |
Denne formelen produserer en delsumverdi for hver datarad. For bare å vise delsummer i den første dataraden i hver Produktgruppe, bruker vi IF -funksjonen. Vær oppmerksom på at dataene allerede må sorteres etter Produktgruppe for å sikre at delsummene vises riktig.
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
IF -funksjonen sammenligner hver datarad Produktgruppe verdi med dataraden over den, og hvis de har samme verdi, sender den ut en tom celle (“”).
Hvis Produktgruppe verdiene er forskjellige, vises summen. På denne måten, hver Produktgruppe sum vises bare én gang (på raden i sin første forekomst).
Sortere datasett etter gruppe
Hvis dataene ikke allerede er sortert, kan vi fortsatt bruke den samme formelen for delsummen.
Datasettet ovenfor er ikke sortert etter Produktgruppe, så Delsum etter gruppe kolonne viser hver delsum mer enn én gang. For å få dataene i det formatet vi ønsker, kan vi velge datatabellen og klikke "Sorter A til Z".
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist noen av formlene uten låste cellereferanser:
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
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 | = HVIS (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
Bruke pivottabeller for å vise delsummer
For å fjerne kravet om å forhåndssortere dataene etter Produktgruppe, kan vi bruke kraften i pivottabeller til å oppsummere dataene i stedet. Pivottabeller beregner delsummer automatisk og viser totaler og delsummer i flere forskjellige formater.
Sum etter kategori eller gruppe i Google Regneark
Disse formlene fungerer på samme måte i Google Regneark som i Excel. Den UNIKE funksjonen er imidlertid store og små bokstaver i Google Regneark.
