Den här artikeln beskriver formelsyntaxen och användningen av funktionen DELSUMMOR i Microsoft Excel.
Returnerar en delsumma till en lista eller databas. Det är vanligtvis lättare att skapa en lista med delsummor med Excel-kommandot på skrivbordet Delsummor i grupp Strukturera flik Data. Men om en sådan lista redan har skapats kan den ändras genom att ändra formeln med funktionen SUBTOTAL.
Funktionen SUBTOTAL har följande argument:
Funktionsnummerär ett obligatoriskt argument. Ett tal från 1 till 11 eller 101 till 111 som representerar funktionen som används för att beräkna delsummor. Funktionerna 1 till 11 tar hänsyn till manuellt dolda rader, medan funktionerna 101 till 111 hoppar över sådana rader; filtrerade celler exkluderas alltid.
Funktionsnummer
| Funktionsnummer
| Fungera |
|---|---|---|
|
PRODUKTER |
||
|
STDEV |
||
|
STDEV |
||
Om det redan finns summeringsformler inuti "referens1;referens2;..."-argumenten (kapslade summor), ignoreras dessa delsummor för att undvika dubbelsummering.
För konstanterna "function_number" från 1 till 11 tar SUBTOTAL-funktionen hänsyn till värdena för raderna som är dolda med kommandot Dölj rader(meny Formatera, undermeny Göm eller visa) i grupp celler flik Hem i Excel-skrivbordsprogrammet. Dessa konstanter används för att få delsummor baserat på de dolda och icke-dolda talen i listan. För konstanterna "function_number" från 101 till 111 exkluderar SUBTOTAL-funktionen radvärden som är dolda med kommandot Dölj rader. Dessa konstanter används för att erhålla delsummor endast med tanke på de dolda talen i listan.
Funktionen SUBTOTAL exkluderar alla rader som inte ingår i filterresultatet, oavsett värdet på konstanten "function_number".
Funktionen SUBTOTAL gäller för datakolumner eller vertikala datauppsättningar. Den är inte avsedd för datarader eller horisontella datauppsättningar. Till exempel, när du definierar delsummor för en horisontell datauppsättning med värdet av konstanten "feature_number" från 101 och högre (till exempel SUBTOTAL(109;B2:G2)), kommer inte resultatet att påverkas av att kolumnen döljs. Det kommer dock att påverkas av att raden döljs vid subtotal av den vertikala datamängden.
Kopiera exempeldata från följande tabell och klistra in den i cell A1 i ett nytt Excel-ark. För att visa formelresultat, välj dem och tryck på F2 följt av ENTER. Ändra bredden på kolumnerna, om det behövs, för att se alla data.
Data |
||
|---|---|---|
|
Formel |
Beskrivning |
Resultat |
|
INTERIM.TOTALS(9;A2:A5) |
Subtotalvärdet för cellområdet A2:A5, erhållet med siffran 9 som första argument. |
|
|
SUBTOTAL(1;A2:A5) |
Subtotalmedelvärdet för cellområdet A2:A5, erhållet med siffran 1 som första argument. |
|
|
Anteckningar |
||
|
Det första argumentet till funktionen SUBTOTAL måste vara ett numeriskt värde (1-11, 101-111). Detta numeriska argument används för att subtotala värdena (cellintervall, namngivna intervall) som anges som följande argument. |
||
Fungera SUBTOTAL() , den engelska versionen av SUBTOTAL(), används för att beräkna en delsumma (summa, medelvärde, antal värden, etc.) i ett intervall som har dolda rader.
Det speciella med funktionen är att den är avsedd att användas i kombination med andra EXCEL-verktyg: och . Se exempelfil.
SUBTOTAL(funktionsnummer;länk1;länk2;...))
Funktionsnummerär ett tal mellan 1 och 11 som anger vilken funktion som ska användas vid beräkning av totalsummorna i listan.
| Funktionsnummer (inklusive dolda värden) |
Funktionsnummer (exklusive dolda värden) |
Fungera |
|---|---|---|
| 1 | 101 | GENOMSNITT |
| 2 | 102 | KOLLA UPP |
| 3 | 103 | RÄKNA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUKTER |
| 7 | 107 | STDEV |
| 8 | 108 | STDEV |
| 9 | 109 | BELOPP |
| 10 | 110 | DISP |
| 11 | 111 | DISP |
Till exempel har SUM()-funktionen kod 9. SUM()-funktionen har också kod 109, dvs. du kan skriva formeln = SUBTOTAL(9;A2:A10) eller = DELÅRSTOTAL(109;A2:A10). Vad är skillnaden - läs nedan. Använd vanligtvis funktionskoder från 1 till 11.
Om det redan finns summeringsformler i argumenten länk1;länk2;... (kapslade totaler), så ignoreras dessa delsummor för att undvika dubbelsummering.
Viktig: Funktion SUBTOTAL() designad för kolumner med data eller vertikala datamängder. Den är inte avsedd för rader med data eller horisontella datauppsättningar (att använda den i det här fallet kan leda till oförutsägbara resultat).
Låt det finnas en initial tabell.
Låt det vara samma källtabell. Dölj produktlinjer Produkt2 via menyn eller via snabbmenyn.
I det här fallet är det skillnad på att använda SUM() funktionskoden: 9 och 109. Funktionen med kod 109 "känner" om raden är dold eller inte. Med andra ord, för en rad koder funktionsnummer 101 till 111 funktioner SUBTOTAL() exkluderar värdena för rader som är dolda med kommandot Hem/ Celler/ Formatera/ Dölj eller Visa . Dessa koder används endast för att få delsummor för listans icke-dolda nummer.
Låt det också finnas en initial tabell. Låt oss skapa en struktur med det inbyggda EXCEL-verktyg - .
Dölj linjerna med Produkt2 genom att klicka på motsvarande "minus"-knapp i strukturen.
Fungera SUBTOTAL() exkluderar alla rader som inte visas av strukturen oavsett vilket kodvärde som används funktionsnummer och, i vårt fall, beräknar summan av endast varorna Produkt1. Detta resultat liknar situationen med autofiltret.
Fungera SUBTOTAL() kan beräkna summan, antalet och medelvärdet av de valda värdena, och inkluderar även 8 andra funktioner (se syntax). Som regel är dessa funktioner tillräckligt, men ibland är det nödvändigt att utöka funktionernas funktioner. SUBTOTAL(). Låt oss överväga ett exempel på att beräkna det geometriska medelvärdet för de värden som väljs av autofiltret. Funktionen SRGEOM() finns inte i listan över funktioner som är tillgängliga via motsvarande koder, men det finns en väg ut.
Uppgiften med selektiv kopiering i redovisningspraxis är mycket vanlig. När det gäller arbetet med resultaten är problemet följande. Det finns en databas som samlar information om varurörelser, Pengar, - vad som helst. Informationen presenteras i detalj. Till exempel, efter datum, dokument, för varje produkt, etc., kommer vi att bygga en analytisk rapport baserad på dessa data. Vi behöver dock inte sådana detaljer i det, vi vill få ett kort och koncist dokument. Med andra ord måste vi ta den ursprungliga basen, bearbeta den, beräkna slutvärdena och sedan använda dem för att bilda den resulterande rapporten.
Vi har ett verktyg för att få resultat: in det här fallet detta är funktionen"Delsummor". Som ett resultat av dess tillämpning kommer speciella poster att visas i databasen, där de totala värdena för de angivna fälten (eller kolumnerna) kommer att beräknas. Ett problem återstår att lösa: hur man separerar dessa summor från huvuddatabasen, så att de sedan kan användas i det fortsatta arbetet.
För att lösa detta problem finns det olika sätt- från organisering av dynamiska länkar till logiska funktioner och formler. Men idag kommer vi att bekanta oss med en annan, inte mindre intressant teknik - vi kommer att använda verktyget "Välja en grupp av celler».
Så det finns en databas, vars fragment visas i fig. 1. Den ger information om försäljning av varor och material för en viss period: datum, produktnamn, pris, försäljningsvolym (kvantitet) och totalbelopp. Vår uppgift är att beräkna den totala försäljningen för varje datum och sedan kopiera dessa summor till ett annat arbetsblad. Det gör vi.
1. Öppna dokumentet, placera den aktiva cellpekaren i dataområdet.
2. Vi kallar menyn " Data", i gruppen " Sortera och filtrera» klicka på ikonen « Sortering » (Fig. 2) — innan du summerar måste databasen sorteras efter nyckelfältet. Fönstret som visas i fig. 3.
3. I det här fönstret, i listan " Sortera efter " Välj alternativ " Datum", i fältet "Beställning » lämna värdet «Från gammalt till nytt».
4. Klicka på "OK" i fönstret "Sortera".
5. I gruppen "Struktur". » klicka på ikonen «Delsumma» (Fig. 2). Ett fönster öppnas som visas i fig. 4.
6. I den från listan "Med varje förändring i:» välj värde « Datum ". I fältet "Operation:" anger du "Belopp ". I parametergruppen "Lägg till totalsummor för:» kryssa för värdena « Antal. ' och 'Belopp'.
7. I fönstret " Delsummor"Tryck" OK ". Rader med totalsummor visas i databasen, och ikoner med grupperingsnivåer visas i det övre vänstra hörnet av kalkylbladet.
8. Vänsterklicka på den andra nivåikonen för att lämna skärmen endast mellanresultat.
Resultatet av vårt arbete visas i fig. 5. Endast totalsummor är synliga på skärmen. Resten av posterna är dolda. Och nu är vår uppgift att kopiera dessa summor till ett annat kalkylblad. Du kommer inte att kunna göra det på vanligt sätt. Detta är lätt att verifiera: välj kolumnerna med databasen, klicka på " ctrl+c ", gå till ett nytt ark och klistra in innehållet från bufferten med kombinationen" ctrl+v ". På bladet tillsammans med resultatetExcel kommer att kopiera alla poster i källdatabasen, vilket vi inte gillar i det här fallet. Naturligtvis skulle det vara möjligt att kopiera varje summa separat. Men det är långt och jobbigt. Därför kommer vi att göra det annorlunda. Det gör vi.
1. Håll ned " ctrl ", klicka successivt på kolumnerna" B, "E" och "F". Kolumnerna "D" och "E" » är inte valda, eftersom det inte finns några värden i dem på den andra grupperingsnivån.
2. Utan att ta bort valet, tryck på " Ctrl+G" eller "F5"-tangenten ". Fönstret "Övergång", som i fig. 6.
3. I det här fönstret trycker du på knappen " Markera… ". Fönstret "Välja en grupp av celler» (Fig. 6).
4. I den sätta på parameter "endast synliga celler och klicka på OK.
5. Utan att ta bort valet, tryck på " Ctrl+C » (kopiera synliga celler till urklipp).
6. Gå till ett nytt blad (i Fig. 5 heter det " Resultat") och tryck på "Ctrl + V » (infoga data från bufferten).
Resultatet av kopieringen visas i fig. 7. Endast de totala värdena finns på det nya bladet. Men det är inte allt. Låt oss återgå till bladet till databasen med resultaten (Fig. 5). Klicka på cellen A7 " - den beräknar den totala mängden varor sålda för " 17/01/2011 ". Vi tittar på formelfältet - det finns ett uttryck: "=DELTOTAL(9;E4:E6)". Detta är en funktion av att summera värdena för det angivna datumet från blocket " E4:E6".
Låt oss gå till arket Resultat "och titta på innehållet i cellen" B3 ". Den innehåller värdet " 8 " och detta gemensamt nummer.
Viktig! När du kopierar fragment av en tabell med " Markera » till det resulterande dokumentet Excel överför endast värden.
Det är allt för idag. Framgångsrikt arbete! Jag ser fram emot dina brev, förslag och kommentarer om [e-postskyddad] , [e-postskyddad] eller på redaktionsforumet.
Låt oss beräkna delsummor i MS EXCEL-tabellen. Till exempel, i en tabell som innehåller försäljningsinformation för flera olika kategorier av varor, beräknar vi kostnaden för varje kategori.
Vi har en tabell över försäljning av varor (produkter upprepas). Se exempelfil.
Beräkna kostnaden för varje artikel med MS EXCEL Delsummor (Data/ Struktur/ Delsummor).
För detta behöver du:
Som du kan se på bilden ovan, efter att ha applicerat verktyget Delsummor, MS EXCEL har skapat tre nivåer av dataorganisation: strukturkontroller har dykt upp till vänster om tabellen. Nivå 1: Totalsumma (kostnad för alla poster i tabellen); Nivå 2: Kostnad för varor i varje kategori; Nivå 3: Alla tabellrader. Genom att trycka på lämpliga knappar kan du visa tabellen i önskad detaljnivå. Figurerna nedan visar nivå 1 och 2.
Att bara kopiera rader med delsummor till ett annat intervall är inte så lätt: även om tabellen är grupperad på nivå 2 (se figuren ovan), sedan genom att markera cellerna med totalsummor (i själva verket intervallet A4:D92) och kopierar vi det till ett annat sortiment får vi hela bordet. För att bara kopiera de totaler vi använder (vi kommer att använda det faktum att MS EXCEL när vi skapar strukturen Delsummor infogar totala rader med ord tillagt Resultat eller i den engelska versionen - Total).
Sektionen "Struktur" har, förutom att gruppera och avgruppera data, ytterligare en funktion - "Subtotals". Med dess hjälp kan du ställa in automatisk gruppering för summerade data. För att göra det tydligare vad i fråga, betrakta som ett exempel en tabell för anställda med resultatet av deras försäljning för kvartalet.
Genom att välja tabellen och klicka på ikonen "Subtotal" kommer Excel att visa ett fönster:
I fältet "Vid varje ändring i:" måste du ange rubriken för tabellkolumnen som summeringen kommer att bestämmas med. Cellerna i de översta raderna i den valda tabellen fungerar som rubrik. Etiketterna i cellerna i den angivna kolumnen kommer att fungera som radrubriker. Programmet kommer att kontrollera titlarna för likheter. Om radrubrikerna stämmer överens, läggs det till det totala antalet, om de inte stämmer överens, så visas resultatet för gruppen som definieras av en rubrik på ytterligare en rad. Därför rekommenderar vi att du sorterar i förväg efter denna kolumn.
I det andra fältet "Operation:" väljer du vilken summa du vill summera: Summa, kvantitet, medelvärde etc. I det sista fältet "Lägg till total efter:" väljer du en kolumn som innehåller nödvändiga data för att visa resultatet.
Nedan finns tre kryssrutor (flaggor):
Knappen Rensa allt tar bort hela datastrukturen.
Så här transformerades tabellen efter att ha tillämpat den beskrivna funktionen på den:
De nya cellerna som lagts till innehåller matematiska funktion SUBTOTALS(), som finns i artikeln om matematiska funktioner - Excel Mathematical Functions.
Subtotalmekanismen har gemensamma funktioner med ett annat kraftfullare verktyg i Excel-applikationen −
