Ladda ner exempelarbetsboken
Denna handledning visar hur du beräknar delsummor per grupp med hjälp av SUMIFS -funktionen i Excel och Google Sheets.
Delsumma Tabell efter kategori eller grupp
Först kommer vi att visa hur man skapar en dynamisk subtotalsammanfattningstabell från ett dataintervall i antingen Excel 365 eller Google Sheets.
Vi använder den UNIKA funktionen och SUMIFS -funktionen för att automatiskt delsumma Antal produkter förbi Produktgrupp:
| 1 | = SUMMER (C3: C11, B3: B11, E3) |
För att skapa denna subtotaltabell använder vi standardapplikationen för SUMIFS -funktionen för att summera Antal produkter som matchar var och en Produktgrupp. Men innan detta är möjligt måste vi skapa en lista med unika Produktgrupper. Microsoft Excel 365- och Google Sheets -användare har tillgång till den UNIKA funktionen för att skapa en dynamisk lista med unika värden från ett cellintervall. I det här exemplet lägger vi till följande formel i cell E3:
| 1 | = UNIK (B3: B11) |
När denna formel anges, skapas en lista automatiskt under cellen för att visa alla unika värden som finns i Produktgrupp dataområde. I det här exemplet utvidgade listan sig till E3: E5 för att visa alla tre unika Produktgrupp värden.
Detta är en dynamisk matrisfunktion där resultatlistans storlek inte behöver definieras, och den kommer automatiskt att krympa och växa när inmatningsdatavärdena ändras.
Observera att den unika funktionen i Excel 365 inte är skiftlägeskänslig, men i Google Kalkylark. Tänk på listan {”A”; "A"; "B"; "C"}. Utmatningen UNIK Funktion beror på programmet:
- {"A"; "B"; “C”} i Excel 365
- {"A"; "A"; "B"; “C”} i Google Kalkylark
Om du använder en Excel -version före Excel 365 måste du ta ett annat tillvägagångssätt. Detta diskuteras i nästa avsnitt.
Delsumma Tabell efter kategori eller grupp - Pre Excel 365
Om du använder en version av Excel före Excel 365 är den UNIKA funktionen inte tillgänglig för användning. För att replikera samma beteende kan du kombinera INDEX -funktionen och MATCH -funktionen med en COUNTIF -funktion för att skapa en matrisformel för att skapa en lista med unika värden från ett cellintervall:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
För att denna formel ska fungera måste de fasta cellreferenserna skrivas noggrant, med funktionen COUNTIF som refererar till intervallet $ E $ 2: E2, vilket är intervallet som börjar från E2 till cellen ovanför cellen som innehåller formeln.
Formeln måste också anges som en matrisformel genom att trycka på CTRL + SKIFT + ENTER efter att den har skrivits. Denna formel är a 1-cell matrisformel, som sedan kan kopieras och klistras in i cellerna E4, E5 etc. Ange inte detta som en matrisformel för hela intervallet E3: E5 i en åtgärd.
På samma sätt som i det föregående exemplet används sedan en SUMIFS -funktion för att delsumma Antal produkter förbi Produktgrupp:
| 1 | = SUMMER (C3: C11, B3: B11, E3) |
Summa efter kategori eller grupp - delsummor i datatabeller
Som ett alternativ till den sammanfattande tabellmetoden som visas ovan kan vi lägga till delsummor direkt i en datatabell. Vi kommer att visa detta genom att använda IF -funktionerna tillsammans med SUMIFS -funktionen för att lägga till en Delsumma per grupp till den ursprungliga datatabellen.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
I det här exemplet används en SUMIFS -funktion som ligger i en IF -funktion. Låt oss dela upp exemplet i steg:
För att lägga till sammanfattande statistik direkt i en datatabell kan vi använda SUMIFS -funktionen. Vi börjar med att summera Antal produkter som matchar det relevanta Produktgrupp:
| 1 | = SUMMER (C3: C11, B3: B11, B3) |
Denna formel ger ett subtotalt värde för varje datarad. För att endast visa delsummor i den första dataraden i varje Produktgrupp, använder vi IF -funktionen. Observera att data redan måste sorteras efter Produktgrupp för att säkerställa att delsummorna visas korrekt.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
IF -funktionen jämför varje datarads Produktgrupp värde med dataraden ovanför, och om de har samma värde matar det ut en tom cell (“”).
Om Produktgrupp värdena är olika visas summan. På det här sättet, var och en Produktgrupp summa visas bara en gång (på raden i sin första instans).
Sortera datauppsättningar efter grupp
Om data inte redan är sorterade kan vi fortfarande använda samma formel för delsumman.
Datauppsättningen ovan sorteras inte efter Produktgrupp, så den Delsumma per grupp kolumn visar varje delsumma mer än en gång. För att få data i det format vi vill ha kan vi välja datatabellen och klicka på "Sortera A till Z".
Låsning av cellreferenser
För att göra våra formler lättare att läsa har vi visat några av formlerna utan låsta cellreferenser:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
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 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Läs vår artikel om låsa cellreferenser för att lära dig mer.
Använd pivottabeller för att visa delsummor
För att ta bort kravet på att försortera data efter Produktgrupp, vi kan använda kraften i pivottabeller för att sammanfatta data istället. Pivottabeller beräknar delsummor automatiskt och visar totaler och delsummor i flera olika format.
Summa efter kategori eller grupp i Google Kalkylark
Dessa formler fungerar på samma sätt i Google Kalkylark som i Excel. Den UNIKA funktionen är emellertid skiftlägeskänslig i Google Kalkylark.
