SUBTOTAL Formel i Excel (Innehållsförteckning)
- SUBTOTAL Formel i Excel
- Hur använder man SUBTOTAL Formula i Excel?
SUBTOTAL Formel i Excel
Ibland måste vi hitta subtotalen för en kategori från en stor uppsättning data som har flera kategorier. I den typen av situationer hjälper subtotalfunktion oss att hitta den totala kategorin klok. Inte bara subtotalt kan vi beräkna medelvärdet, räkna och max och många fler.
Definition
Funktionen som returnerar subtotal från en lista eller databas kan definieras som Subtotal-funktion. Subtotal är en speciell funktion jämfört med andra excel-funktioner eftersom andra excel-funktioner endast utför en specifik operation, men specialiteten för subtotalfunktion är, den utför inte bara subtotalberäkningen, utan också flera aritmetik och logiska operationer beror på funktionsnumret.
Syntax av SUBTOTAL Formula
Argument:
Funktion num : Den hänvisar till den typ av matematisk operation vi ska utföra på ett specifikt dataintervall.
Ref1, Ref 2: Det avser cellintervallet .
Det finns ett flertal antal funktionsnummer tillgängliga, men inga problem vi behöver inte komma ihåg alla dessa funktionsnummer eftersom, medan du använder SUBTOTAL-formeln kommer excel automatiskt att visa dig listan över tillgängliga funktionsnummer enligt nedan.
Du måste undra varför det finns två funktionsnummer för samma funktion.
- 1 - 11 funktionsnummer bör användas när vi vill ignorera de bortfiltrerade cellerna och överväga de manuellt dolda cellerna.
- 101 - 111 funktionsnummer bör användas när vi vill ignorera alla dolda celler som filtreras bort och manuellt dolda celler.
Vi kommer att se några exempel för att förstå hur subtotal funktion fungerar för olika funktionsnummer.
Hur använder man SUBTOTAL Formula i Excel?
SUBTOTAL Formel i Excel är mycket enkel och enkel. Låt oss förstå hur du använder SUBTOTAL-formeln i Excel med några exempel.
Du kan ladda ner denna SUBTOTAL Excel-mall här - SUBTOTAL Formel Excel-mallExempel 1
Tänk på en liten tabell som innehåller data från olika kategorier enligt nedan.
Om du följer tabellen ovan har vi Produktkategori, Färgkategori och Kvantitet.
Använd SUBTOTAL-formeln enligt nedan med funktion nummer 9.
Efter tillämpning av denna formel är resultatet som visas nedan.
Använd SUBTOTAL-formeln enligt nedan med funktion nummer 109.
Efter applicering av SUBTOTAL Formula visas resultatet nedan.
I formeln har vi använt både funktionsnummer 9 och 109 för att utföra SUM i två olika kolumner. C2: C9 är det dataområde som vi gör beräkningar.
Nu är den totala summan 385 för båda formlerna. Göm några rader och observera resultaten för båda formlerna.
Efter applicering av SUBTOTAL Formula visas resultatet nedan.
Rader från 50 till 60 är dolda, följaktligen har resultaten av funktionsnumret 109 ändrats till 255 eftersom den inte beaktar den manuellt dolda datan medan funktionsnummer 9 totalt förblir densamma vilket innebär att den kommer att överväga data även om vi döljer data manuellt .
Använd filter på data och filtrera endast en färg, här har vi valt den svarta färgen.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln enligt nedan med funktion nummer 109.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Om vi observerar skärmdumpen ovan beaktade båda funktionsnumren inte mängden filtrerad data. När du filtrerar bort data och döljer rader ger båda formlerna samma resultat som båda formlerna inte kommer att ta hänsyn till osynliga data.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln enligt nedan med funktion nummer 109.
Efter tillämpning av denna formel är resultatet som visas nedan.
Exempel 2
Hittills har vi sett SUM-operation, nu kommer vi att utföra AVERAGE-operationen med samma dataområde. 1 och 101 är funktionsnumren som ska utföras i genomsnitt. Gå igenom tabellen ovan för detaljer om funktionsnummer.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
För nästa cell, applicera SUBTOTAL-formeln igen med funktionsnummer 109.
Efter applicering av formeln är resultatet som visas nedan.
Med samma SUBTOTAL-formel kan vi också beräkna medelvärdet med den enda ändringen i funktionsnummer.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
För nästa cell, applicera SUBTOTAL-formeln igen med funktionsnummer 101.
Efter applicering av SUBTOTAL Formula visas resultatet nedan.
Dölj några rader, observera nu förändringarna i genomsnittet. Genomsnittet med funktion nummer 1 förblir detsamma som fortfarande, det beaktar också den dolda mängden. Genomsnittet med funktionsnummer 101 ändrades eftersom det ignorerar de manuellt dolda data. Så här kan du prova resten av funktionsnumren och kontrollera funktionerna hos var och en eftersom det grundläggande konceptet är detsamma som de också fungerar på samma sätt.
Exempel 3
Subtotal har ytterligare en fördel att den inte kommer att ta hänsyn till om det finns några tillgängliga delsiffror i dataintervallet. Vi kommer att se ett exempel för att förstå bättre. Mata in formler för att utföra SUM-driftprodukter klokt som A, B, C och D. Det är inte obligatoriskt att det ska vara produktmässigt kan du också göra med färgmässigt.
Efter applicering av SUBTOTAL-formeln är resultatet som visas nedan.
Använd SUBTOTAL-formeln igen för att kontrollera summan av B.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln igen för att kontrollera SUM för C.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln igen för att kontrollera summan av D.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln igen till nästa cell.
Efter applicering av SUBTOTAL Formula är resultatet som visas nedan.
Använd SUBTOTAL-formeln igen till nästa cell.
Efter applicering av SUBTOTAL Formula visas resultatet nedan.
Om vi observerar ovanstående skärmdumpar finns summan av biprodukterna klokt och medan vi utför den totala summan, inkluderade vi de kategoriska kloka summan i den totala summan (celler från 36 till 48) men det kommer inte att ta hänsyn till det subtotalen (celler från 44 till 47 ). Så vi kan beräkna delsiffrorna mellan dataområdet utan att påverka det totala summan.
Jag hoppas att du förstår hur du använder subtotalen och hur du använder det.
Saker att komma ihåg om SUBTOTAL Formel i Excel
- Medan du ger funktionsnumret inte ger numret som är utanför funktionsnummerområdet eftersom det inte är fördefinierat av excel får vi ett felmeddelande. Vi ska alltid ge numret mellan 1 till 11 och 101 till 111 annars kommer det att kasta felmeddelandet #value.
- När du använder divisionsoperationen bör inget nummer delas med noll. Det betyder att x / 0 är ett felaktigt format.
- Om du använder subtotalen för horisontella data från A1: F1 och döljer eventuella kolumner påverkar inte subtotalen.
- Medan du använder subtotal om några celler inte har data eller icke-numeriska data kommer funktionen att ignorera dessa celler.
- Använd funktionsnumren genom att förstå funktionaliteten och använd annars kan du inte få korrekta resultat som du förväntar dig.
Rekommenderade artiklar
Detta har varit en guide till SUBTOTAL Formula i Excel. Här diskuterar vi hur du använder SUBTOTAL-formel i Excel tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar-
- Hur använder jag Excel IRR-formel?
- Vänster TRIM i Excel (formel, exempel)
- VLOOKUP-handledning i Excel
- Excel ISNUMBER-formel