Download eksemplet på projektmappe
Denne vejledning viser, hvordan du beregner subtotaler efter gruppe ved hjælp af SUMIFS -funktionen i Excel og Google Sheets.
Deltotal efter kategori eller gruppe
Først vil vi demonstrere, hvordan du opretter en dynamisk subtotal -oversigtstabel fra et dataområde i enten Excel 365 og fremefter eller Google Sheets.
Vi bruger funktionen UNIK og SUMIFS til automatisk at subtotalere Antal produkter ved Produktgruppe:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
For at oprette denne subtotaltabel bruger vi standardapplikationen af SUMIFS -funktionen til at summere Antal produkter der matcher hver Produktgruppe. Men før dette er muligt, skal vi oprette en liste over unikke Produktgrupper. Microsoft Excel 365 og Google Sheets -brugere har adgang til den UNIKE funktion til at oprette en dynamisk liste over unikke værdier fra et celleområde. I dette eksempel tilføjer vi følgende formel til celle E3:
| 1 | = UNIK (B3: B11) |
Når denne formel er indtastet, oprettes der automatisk en liste under cellen for at vise alle unikke værdier, der findes i Produktgruppe dataområde. I dette eksempel udvidede listen sig til at dække E3: E5 for at vise alle 3 unikke Produktgruppe værdier.
Dette er en dynamisk array -funktion, hvor størrelsen af resultatlisten ikke behøver at blive defineret, og den vil automatisk krympe og vokse, når inputdataværdierne ændres.
Bemærk, at den unikke funktion i Excel 365 ikke er store og små bogstaver, men i Google Sheets. Overvej listen {"A"; "en"; "B"; "C"}. UNIQUE Function output afhænger af programmet:
- {"EN"; "B"; "C"} i Excel 365
- {"EN"; "en"; "B"; "C"} i Google Sheets
Hvis du bruger en Excel -version før Excel 365, skal du have en anden tilgang. Dette diskuteres i det næste afsnit.
Subtotaltabel efter kategori eller gruppe - Pre Excel 365
Hvis du bruger en version af Excel før Excel 365, er den UNIKE funktion ikke tilgængelig til brug. For at replikere den samme adfærd kan du kombinere INDEX -funktionen og MATCH -funktionen med en COUNTIF -funktion for at oprette en matrixformel for at producere en liste med unikke værdier fra et område af celler:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
For at denne formel skal fungere, skal de faste cellereferencer skrives omhyggeligt, med COUNTIF -funktionen, der refererer til området $ E $ 2: E2, som er intervallet, der starter fra E2 til cellen over cellen, der indeholder formlen.
Formlen skal også indtastes som en matrixformel ved at trykke på CTRL + SKIFT + ENTER, efter at den er skrevet. Denne formel er en 1-celles matrixformel, som derefter kan kopieres ind i cellerne E4, E5 osv. Indtast ikke dette som en matrixformel for hele området E3: E5 i en handling.
På samme måde som i det foregående eksempel bruges en SUMIFS -funktion derefter til at subtotalere Antal produkter ved Produktgruppe:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Sum efter kategori eller gruppe - Subtotaler i datatabeller
Som et alternativ til oversigtstabelmetoden vist ovenfor kan vi tilføje subtotaler direkte til en datatabel. Vi vil demonstrere dette ved at bruge IF -funktionerne sammen med SUMIFS -funktionen til at tilføje en Subtotal efter gruppe til den originale datatabel.
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Dette eksempel bruger en SUMIFS -funktion, der er indlejret i en IF -funktion. Lad os opdele eksemplet i trin:
For at tilføje oversigtsstatistik direkte til en datatabel kan vi bruge SUMIFS -funktionen. Vi starter med at totalere Antal produkter der matcher det relevante Produktgruppe:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Denne formel producerer en delsumværdi for hver datarække. For kun at vise delsummer i den første datarække i hver Produktgruppe, vi bruger IF -funktionen. Bemærk, at dataene allerede skal sorteres efter Produktgruppe for at sikre, at delsummerne vises korrekt.
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
IF -funktionen sammenligner hver datarække Produktgruppe værdi med datarækken over den, og hvis de har den samme værdi, udsender den en tom celle (“”).
Hvis Produktgruppe værdier er forskellige, vises summen. På denne måde hver Produktgruppe sum vises kun én gang (i rækken af sin første forekomst).
Sortering af datasæt efter gruppe
Hvis dataene ikke allerede er sorteret, kan vi stadig bruge den samme formel for delsummen.
Datasættet ovenfor er ikke sorteret efter Produktgruppe, så Subtotal efter gruppe kolonne viser hver subtotal mere end én gang. For at få dataene i det format, vi ønsker, kan vi vælge datatabellen og klikke på "Sort A to Z".
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist nogle af formlerne uden låste cellereferencer:
| 1 | = HVIS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
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 | = HVIS (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Læs vores artikel om låsning af cellereferencer for at lære mere.
Brug af pivottabeller til at vise delsummer
For at fjerne kravet om at forhåndssortere dataene efter Produktgruppe, vi kan bruge kraften i pivottabeller til i stedet at opsummere dataene. Pivottabeller beregner subtotaler automatisk og viser totaler og subtotaler i flere forskellige formater.
Sum efter kategori eller gruppe i Google Sheets
Disse formler fungerer på samme måde i Google Sheets som i Excel. Den UNIQUE -funktion er imidlertid store og små bogstaver i Google Sheets.
