Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel SUBTOTAL funktion i Excel för att beräkna sammanfattande statistik.
SUBTOTAL Funktionsöversikt
SUBTOTAL -funktionen beräknar en sammanfattande statistik för en serie data. Tillgänglig statistik inkluderar, men är inte begränsat till genomsnitt, standardavvikelse, antal, min och max. Se hela listan nedan i avsnittet om funktionsingångar:
Om du vill använda funktionen SUBTOTAL Excel -kalkylblad väljer du en cell och skriver:
(Lägg märke till hur formelinmatningarna ser ut)
SUBTOTAL Funktionssyntax och ingångar:
| 1 | = SUBTOTAL (function_num, REF1) |
function_num - Ett nummer som representerar vilken operation som ska utföras.
REF1 - Områden eller referenser som innehåller data att beräkna.
Vad är SUBTOTAL -funktionen?
SUBTOTALEN är en av de unika funktionerna i kalkylblad eftersom den kan se skillnaden mellan dolda celler och icke-dolda celler. Detta kan visa sig vara mycket användbart när det gäller filtrerade intervall eller när du behöver konfigurera beräkningar baserade på olika användarval. Eftersom den också vet att ignorera andra SUBTOTAL-funktioner från sina beräkningar, kan vi också använda den inom stora sammanfattade data utan rädsla för dubbelräkning.
Grundläggande sammanfattning med SUBTOTAL
Låt oss säga att du hade en tabell med sorterad produktförsäljning och ville skapa totaler för varje produkt, samt skapa en total summa. Du kan använda en pivottabell eller infoga några formler. Tänk på denna layout:
Jag har placerat några SUBTOTAL -funktioner i cellerna B5 och B8 som ser ut
| 1 | = SUBTOTAL (9, B2: B4) |
Från syntaxen kan du använda en mängd olika nummer för det första argumentet. I vårt specifika fall använder vi 9 för att indikera att vi vill göra en summa.
Låt oss fokusera på cell B9. Den har denna formel, som inkluderar hela kolumn B -dataområdet, men innehåller inte de andra subtotalerna.
| 1 | = SUBTOTALT (9, B2: B8) |
OBS! Om du inte vill skriva alla sammanfattningsformlerna själv kan du gå till Databand och använda guiden Disposition - Delsumma. Det kommer automatiskt att infoga rader och placera formlerna för dig.
Skillnad i de första argumenten
I det första exemplet använde vi en 9 för att indikera att vi ville göra en summa. Skillnaden mellan att använda 9 och 109 skulle vara hur vi vill att funktionen ska hantera dolda rader. Om du använder 1XX -beteckningarna innehåller funktionen inte rader som har dolts eller filtrerats manuellt.
Här är vårt bord från tidigare. Vi har flyttat över funktionerna så att vi kan se skillnad mellan 9 och 109 argumenten. Med alla synliga är resultaten desamma.
Om vi använder ett filter för att filtrera bort värdet 6 i kolumn B förblir de två funktionerna desamma.
Om vi manuellt döljer raderna ser vi skillnaden. 109 -funktionen kunde ignorera den dolda raden medan funktionen 9 inte gjorde det.
Ändra matematik med SUBTOTAL
Du kanske skulle vilja kunna ge din användare möjlighet att ändra vilken typ av beräkningar som utförs. Vill de till exempel få summan eller genomsnittet. Eftersom SUBTOTAL styr matematikoperationen med ett argumentnummer kan du skriva detta i en enda formel. Här är vår inställning:
Vi har skapat en rullgardinsmeny i D2 där användaren kan välja antingen "Summa" eller "Genomsnitt". Formeln i E2 är:
| 1 | = SUBTOTAL (IF (D2 = "Genomsnitt", 1, IF (D2 = "Summa", 9)), B2: B4) |
Här kommer IF -funktionen att bestämma vilket numeriskt argument som ska ges till SUBTOTALEN. Om A5 är "Genomsnitt", kommer det att mata ut ett 1 och SUBTOTAL ger genomsnittet av B2: B4. Eller, om A5 är lika med "Summa", ger IF ett 9, och vi får ett annat resultat.
Du kan utöka den här funktionen genom att använda en uppslagstabell för att lista ännu fler typer av operationer du vill utföra. Ditt uppslagstabell kan se ut så här
Sedan kan du ändra formeln i E2 till
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Villkorliga formler med SUBTOTAL
Även om SUBTOTAL har många operationer det kan utföra, kan det inte kontrollera kriterier på egen hand. Vi kan dock använda den i en hjälparkolumn för att utföra denna operation. När du har en kolumn med data som du vet kommer alltid har en bit data i den, kan du använda SUBTOTALs förmåga att upptäcka dolda rader.
Här är tabellen vi kommer att arbeta med i det här exemplet. Så småningom skulle vi vilja kunna summera värdena för "Apple", men också låta användaren filtrera kolumnen Antal.
Skapa först en hjälparkolumn som innehåller SUBTOTAL -funktionen. I C2 är formeln:
| 1 | = SUBTOTAL (103, A2) |
Kom ihåg att 103 betyder att vi vill göra en RIK. Jag rekommenderar att du använder COUNTA eftersom du sedan kan få din referenscell på A2 att fyllas med antingen siffror eller text. Du får nu ett bord som ser ut så här:
Detta verkar inte till någon hjälp först eftersom alla värden bara är 1. Om vi döljer rad 3 ändras dock "1" i C3 till 0 eftersom det pekar på en dold rad. Även om det är omöjligt att ha en bild som visar den specifika dolda cellens värde, kan du kontrollera den genom att dölja raden och sedan skriva en grundformel som denna för att kontrollera.
| 1 | = C3 |
Nu när vi har en kolumn som kommer att förändras i värde beroende på om den är dold eller inte, är vi redo att skriva den sista ekvationen. Våra SUMIF kommer att se ut så här
I denna formel kommer vi bara att summera värden från kolumn B när kolumn A är lika med "Apple", och värdet i kolumn C är 1 (aka, raden är inte dold). Låt oss säga att vår användare vill filtrera bort 600, eftersom det verkar onormalt högt. Vi kan se att vår formel ger rätt resultat.
Med den här förmågan kan du tillämpa en check på en COUNTIFS, SUMIFS eller till och med en SUMPRODUCT. Du lägger till möjligheten att låta dina användare styra några bordskärare, och du är redo att skapa en fantastisk instrumentpanel.
SUBTOTAL i Google Kalkylark
SUBTOTAL -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
SUBTOTALA Exempel i VBA
Du kan också använda funktionen SUBTOTAL i VBA. Typ:application.worksheetfunction.subtotal (function_num, reh1)
Utför följande VBA -uttalanden
| 1234567891011121314151617 | Range ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Range ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Range ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Range ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Range ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Range ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Range ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Range ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Range ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Range ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Range ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Range ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Range ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
kommer att ge följande resultat
För funktionsargumenten (function_num, etc.) kan du antingen ange dem direkt i funktionen eller definiera variabler som ska användas istället.
Återgå till listan över alla funktioner i Excel
