Excel SUBTOTAL függvény (tartalomjegyzék)
- SUBTOTAL az Excelben
- SUBTOTAL képlet Excelben
- Hogyan lehet használni az SUBTOTAL függvényt az Excelben?
SUBTOTAL az Excelben
- Egy részösszegű függvény az Excelben beépített függvény, amelyet a matematikai / triglic függvény kategóriába sorolunk
- Az részösszeg nem csak a cellák meghatározott számú tartományában szereplő számokat összegzi, hanem különféle számtani műveletek végrehajtásával is segít
- A SUBTOTAL sokoldalú. Használhatja az átlagoláshoz, az összegzéshez, a cellák és egyéb számítások számának számításához
Meghatározás
Részösszeget ad vissza az adatbázisban vagy a listában, azaz egy adott cellatartomány részösszegének megismerésére. Meghatározott számítást hajt végre egy adott értékkészletre.
SUBTOTAL képlet Excelben
A SUBTOTAL függvény képlete a következő:
ÖSSZESEN (funkcionális szám, ref1, (ref2), …) Hol
Function_num vagy műveleti kód: Azt jelzi, hogy melyik funkciót kell használni az adott listán szereplő részösszegek kiszámításához, vagy Ez egy olyan matematikai művelet, amelyet végrehajtani szeretne
Az function_num argumentumot az alább említett két kritérium vagy módszer alapján alkalmazzuk
- Rejtett értékek beillesztése Ha a function_num argumentum 1-11 közötti, a SUBTOTAL függvény rejtett értékeket fog tartalmazni.
- Rejtett értékek figyelmen kívül hagyása Ha a function_num argumentum 101-111 között van, a SUBTOTAL függvény kizárja vagy figyelmen kívül hagyja a rejtett értékeket. Csak akkor látja el az értékeket, ha a láthatatlan sorok figyelmen kívül hagyják a rejtett értékeket a számításban.
| Fun_num Rejtett értékeket tartalmaz | Fun_num Nem tartalmazza a rejtett értékeket | Funkció | Leírás | |||
| 1 | 101 | ÁTLAGOS | Kiszámítja egy megadott tartomány átlagát. | |||
| 2 | 102 | SZÁMOL | Megszámolja a numerikus értékeket tartalmazó cellák számát. | |||
| 3 | 103 | COUNTA | Megszámolja a nem üres cellákat a megadott tartományban. | |||
| 4 | 104 | MAX | Ez kiszámítja a legnagyobb értéket a megadott tartományban. | |||
| 5 | 105 | MIN | Ez kiszámítja a megadott tartomány legkisebb értékét. | |||
| 6 | 106 | TERMÉK | Kiszámítja a meghatározott tartományban lévő sejtek szorzatát. | |||
| 7 | 107 | szórás | Becsli a szórást a megadott tartományban. | |||
| 8 | 108 | STDEVP | A teljes szórás alapján kiszámítja a szórást. | |||
| 9 | 109 | ÖSSZEG | Kiszámítja a megadott tartomány összegét. | |||
| 10 | 110 | VAR | Megbecsüli a megadott tartománybeli varianciát. | |||
| 11 | 111 | VARP | A varianciát a teljes népesség alapján becsüli. |
ref1, (ref2): Egy vagy több hivatkozás cellákra, amelyek tartalmazzák azokat az értékeket, amelyeket a részösszegre vonatkozóan a számításhoz kell végrehajtani.
Hogyan lehet használni az SUBTOTAL függvényt az Excelben?
Nézzük meg, hogy a SUBTOTAL függvény hogyan működik az Excelben.
Itt letöltheti ezt a SUBTOTAL Function Excel sablont - SUBTOTAL Function Excel sablon1. példa - Részösszegű képlet az Excel Autofilter segítségével elrejtett értékekre vagy a szűrő által elrejtett sorokra
Az alább említett példában a táblázat negyedéves értékesítési adatokat tartalmaz, ahol az egyes oszlopok az E oszlopban és az értékesítési adatok az F oszlopban találhatók.
Az összeg funkciót alkalmazzuk az „F16” cellában, azaz = SUM (F4: F15).
Visszaad vagy 1184 értéket eredményez.
A SUM függvény ugyanazt az értéket adja vissza. Vagyis a 1184, a Q4 adatok esetében a 194 helyett, ha a sorokat egy szűrő rejti el (az alábbiakban említett képernyőkép).
Ha csak a Q4 adatok helyes SUM értékét akarom, akkor a SUBTOTAL funkciót kell használni.
A SUBTOTAL funkció alkalmazása előtt az adatszálsor alatt alkalmazzon szűrőt a fejléc sorhoz.
Most kiszámoljuk külön a Q1 vagy Q2, illetve Q3 vagy Q4 értékesítési adatokat egyenként a szűrt adatokhoz.
Használja a 109-et a function_num argumentumként a SUBTOTAL függvényben az adatok összegzéséhez. azaz = ÖSSZESEN (109, C17: C28)
A SUBTOTAL funkció figyelmen kívül hagyja a szűrő által elrejtett sorokat, és kiszámítja a helyes eredményt.
Itt az részösszeg a szűrő szerint dinamikusan változik, csak a látható sorokban találja meg az értékek összegét; a rejtett sorokat a SUBTOTAL funkció figyelmen kívül hagyja. (Például lent)
Q4
Q3
Q2
Q1
2. példa - SUBTOTAL funkció kézi rejtett sorokhoz
Az excel lapon a 15., 16., 19. és 20. sort elrejtették a sorok formázása segítségével, kiemelve ezeket a sorokat, jobb egérgombbal kattintva az egérrel, és kiválasztva az Elrejtés lehetőséget.
Egy részösszeg függvényt használunk a táblázat C23 cellájában a látható cellák összegének kiszámításához.
A SUBTOTAL függvényben 109- et kell használni function_num argumentumként az adatok összegzéséhez.
azaz = ÖSSZESEN (109, C14: C21)
A SUBTOTAL funkció itt figyelmen kívül hagyja a rejtett sorokat, és kiszámítja a helyes eredményt.
Az értékek összegét csak a látható sorokban fogja megtalálni; a rejtett sorokat a SUBTOTAL funkció figyelmen kívül hagyja
3. példa - SUBTOTAL Funkció a normál értékhez
Mivel a negyedéves értékesítési számadatok összes cellája látható, egyiket sem szűrik vagy sorokat nem rejtenek, az összes értéket tartalmazza.
Az alábbiakban említett példában több részösszegű függvényt alkalmazunk.
A cellában a C14, C15 és C16 SUBTOTAL függvényt alkalmazzuk.
Az SUM érték kiszámításához a 9. vagy a 109-es számot használjuk egy részösszegben, ahol kiszámítja a megadott tartomány összegét, azaz = SUBTOTAL (9, C4: C11)
Megkapja a C4: C11 összes értékének összegét, feltéve, hogy egyiket sem szűrjük, és a sorokat a megadott tartományba rejtjük.
A MIN érték kiszámításához az 5. vagy a 105. számot használjuk egy részösszegben, ahol a megadott tartományban a legkisebb értéket fogja meghatározni. A C15 cellában egy SUBTOTAL függvényt alkalmazunk, azaz = SUBTOTAL (5, C4: C11)
Meg fogja találni a legkisebb értéket a megadott tartományban, azaz a C4: C11
A MAX érték kiszámításához a 4. vagy 104. számot használjuk egy részösszegben, ahol a megadott tartomány legnagyobb értékét fogja meghatározni. A C16 cellában egy SUBTOTAL függvényt alkalmazunk, azaz = SUBTOTAL (4, C4: C11)
Ez megtudja a megadott tartományban a legnagyobb értéket, azaz a C4: C11.
Ne feledkezzen meg a SUBTOTAL funkcióról
- A SUBTOTAL funkció alkalmazható a szűrt értékek összegének meghatározására.
- A SUBTOTAL függvényt használja a rejtett sorok értékeinek figyelmen kívül hagyása.
- A funkcionális_numer argumentum értéke nem lehet nagyobb, mint 11 vagy kevesebb, mint 1, hasonlóan nem lehet nagyobb, mint 111 vagy kevesebb, mint 101, különben #VALUE! hiba.
- A SUBTOTAL funkció csak a numerikus értékre alkalmazható.
- Ha vannak részösszegek a SUBTOTAL tartományban, akkor ezeket figyelmen kívül hagyják, azaz ha egy már létező SUBTOTAL képletek vannak jelen egy másik SUBTOTAL képlet bemeneti tartományában, akkor ezeket az értékeket a részösszeg funkció elhanyagolja a számítás során, így elkerülhető a kettős számolás. megakadályozza a kettős számolás hibáját.
- Az SUBTOTAL funkció használható az adatok dinamikus összefoglalására.
- Az üres cellákat és a nem numerikus értékeket tartalmazó cellákat a részösszeg függvénye figyelmen kívül hagyja a számítás során.
Ajánlott cikkek
Ez a SUBTOTAL útmutatója volt. Itt tárgyaljuk a SUBTOTAL képletet és a SUBTOTAL Function használatát, valamint az excel példát és a letölthető excel sablonokat. Ezeket a hasznos funkciókat Excelben is megnézheti -
- KERESÉS funkció Excelben
- FIND funkció Excelben
- NEM az Excelben
- TRANSPOSE az Excel programban