Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel SUBTOTAL függvény az Excelben az összefoglaló statisztikák kiszámításához.
RÉSZVÉTEL A funkció áttekintése
A SUBTOTAL függvény Összegző statisztikát számol ki egy adatsorra. A rendelkezésre álló statisztikák közé tartozik, de nem kizárólagosan, az átlagos, szórás, szám, min és max. A teljes listát lásd a funkcióbemenetek részben:
Az ALSÓ ÖSSZES Excel munkalap funkció használatához válasszon ki egy cellát, és írja be:
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
RÉSZVÉTEL Funkció szintaxisa és bemenetei:
| 1 | = RÉSZSZÖVEG (függvényszám, REF1) |
function_num - Szám, amely jelzi, hogy melyik műveletet kell végrehajtani.
REF1 - A kiszámítandó adatokat tartalmazó tartományok vagy hivatkozások.
Mi a SUBTOTAL funkció?
A SUBTOTAL a táblázatok egyik egyedi funkciója, mivel meg tudja különböztetni a rejtett és a nem rejtett cellákat. Ez nagyon hasznosnak bizonyulhat, ha szűrt tartományokkal foglalkozik, vagy ha számításokat kell beállítania a különböző felhasználói választások alapján. Mivel tudja, hogy számításaiból figyelmen kívül hagy más egyéb RÉSZVÉNY-függvényeket, nagy összegzett adatokon belül is használhatjuk, anélkül, hogy félnénk a kétszeres számítástól.
Alapösszegzés SUBTOTAL értékkel
Tegyük fel, hogy volt egy táblázata a rendezett termékértékesítésekről, és minden termékhez szeretne összesítést létrehozni, valamint egy összesített összeget. Használhat egy kimutatást, vagy beszúrhat néhány képletet. Fontolja meg ezt az elrendezést:
Néhány SUBTOTAL függvényt helyeztem el a B5 és B8 cellákban, amelyek így néznek ki
| 1 | = RÉSZSZÖVEG (9, B2: B4) |
A szintaxisból számos számot használhat az első argumentumhoz. A mi konkrét esetünkben 9 -et használunk annak jelzésére, hogy összeget akarunk tenni.
Fókuszáljunk a B9 cellára. Ez a képlet tartalmazza a B oszlop teljes adattartományát, de nem tartalmazza a többi részösszeget.
| 1 | = RÉSZSZÖVEG (9, B2: B8) |
MEGJEGYZÉS: Ha nem szeretné az összesítő képleteket saját maga megírni, lépjen az Adatok szalagra, és használja a Vázlat - Részösszeg varázslót. Ez automatikusan beszúrja a sorokat, és elhelyezi a képleteket.
Különbség az első érvekben
Az első példában 9 -gyel jeleztük, hogy összeget akarunk csinálni. A különbség a 9 és a 109 használata között az lenne, hogy szeretnénk, ha a függvény kezeli a rejtett sorokat. Ha az 1XX jelöléseket használja, akkor a funkció nem tartalmazza azokat a sorokat, amelyeket manuálisan elrejtettek vagy szűrtek.
Íme az előző táblázatunk. A funkciókat áthelyeztük, így láthatjuk a különbséget a 9 és 109 argumentum között. Ha minden látható, az eredmény ugyanaz.
Ha szűrőt alkalmazunk a B oszlop 6 értékének kiszűrésére, akkor a két funkció ugyanaz marad.
Ha manuálisan elrejtjük a sorokat, látjuk a különbséget. A 109 függvény figyelmen kívül hagyta a rejtett sort, míg a 9 funkció nem.
Változtassa meg a matematikai műveletet SUBTOTAL értékkel
Érdemes lehet néha megadni a felhasználónak, hogy módosítsa, milyen típusú számításokat hajt végre. Például az összeget vagy az átlagot akarják megkapni. Mivel a SUBTOTAL egy argumentumszámmal vezérli a matematikai műveletet, ezt egyetlen képletbe írhatja be. Íme a beállításunk:
Létrehoztunk egy legördülő menüt a D2 -ben, ahol a felhasználó kiválaszthatja az „Összeg” vagy az „Átlag” értéket. Az E2 képlet a következő:
| 1 | = RÉSZSZÖVEG (HA (D2 = "Átlag", 1, HA (D2 = "Összeg", 9)), B2: B4) |
Itt az IF függvény határozza meg, hogy mely numerikus érvet adja meg a RÉSZVÉTELhez. Ha A5 „Átlagos”, akkor 1 -et ad ki, és ÖSSZES ÖSSZESEN a B2: B4 átlagát adja. Vagy ha A5 egyenlő „összeggel”, akkor az IF 9 -et ad ki, és más eredményt kapunk.
Ezt a képességet kibővítheti egy keresőtáblázat segítségével, amely még több típusú műveletet sorol fel. A keresési táblázat így nézhet ki
Ezután megváltoztathatja az E2 képletét
| 1 | = RÉSZSZÁM (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Feltételes képletek SUBTOTAL
Bár a SUBTOTAL számos műveletet képes végrehajtani, önmagában nem tudja ellenőrizni a kritériumokat. Ezt a műveletet azonban segítő oszlopban használhatjuk. Ha van egy adatoszlopa, amelyről tudja, hogy lesz mindig Ha van benne egy adat, használhatja a SUBTOTALs lehetőséget a rejtett sorok észlelésére.
Íme a táblázat, amellyel ebben a példában dolgozunk. Végül szeretnénk összegezni az „Apple” értékeit, de hagyni, hogy a felhasználó szűrje a Mennyiség oszlopot.
Először hozzon létre egy segítő oszlopot, amely tartalmazza a SUBTOTAL funkciót. C2 -ben a képlet a következő:
| 1 | = RÉSZSZÖVEG (103, A2) |
Ne feledje, hogy a 103 azt jelenti, hogy COUNTA -t akarunk csinálni. Javaslom a COUNTA használatát, mert ezután feltöltheti az A2 referenciacelláját bármelyik számokat vagy szöveget. Most kap egy táblázatot, amely így néz ki:
Ez elsőre nem tűnik hasznosnak, mert minden érték csak 1. Ha azonban elrejtjük a 3. sort, akkor a C3 -ban az „1” 0 -ra változik, mert egy rejtett sorra mutat. Bár lehetetlen olyan kép megjelenítése, amely az adott rejtett cella értékét mutatja, ellenőrizheti azt a sor elrejtésével, majd egy ilyen alapképlet írásával.
| 1 | = C3 |
Most, hogy van egy oszlopunk, amelynek értéke változni fog attól függően, hogy rejtve van -e vagy sem, készen állunk a végső egyenlet megírására. SUMIFS -ünk így fog kinézni
Ebben a képletben csak akkor fogjuk összegezni a B oszlop értékeit, ha az A oszlop megegyezik az „Apple” értékkel, és a C oszlop értéke 1 (más néven a sor nem rejtett). Tegyük fel, hogy felhasználónk ki akarja szűrni a 600 -at, mert az abnormálisan magasnak tűnik. Láthatjuk, hogy a képletünk helyes eredményt ad.
Ezzel a képességgel csekket alkalmazhat COUNTIFS, SUMIFS vagy akár SUMPRODUCT termékre. Hozzáadja a lehetőséget, hogy a felhasználók irányíthassanak néhány asztali szeletelőt, és készen áll egy fantasztikus irányítópult létrehozására.
SUBTOTAL a Google Táblázatokban
A SUBTOTAL funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
Részletek VBA -ban
A SUBTOTAL funkciót a VBA -ban is használhatja. Típus:application.worksheetfunction.subtotal (function_num, reh1)
A következő VBA utasítások végrehajtása
| 1234567891011121314151617 | Tartomány ("C7") = Application.WorksheetFunction.Subtotal (1, tartomány ("C2: C5"))Tartomány ("C8") = Application.WorksheetFunction.Subtotal (2, tartomány ("C2: C5"))Tartomány ("C9") = Application.WorksheetFunction.Subtotal (4, tartomány ("C2: C5"))Tartomány ("C10") = Application.WorksheetFunction.Subtotal (5, tartomány ("C2: C5"))Tartomány ("C11") = Application.WorksheetFunction.Subtotal (9, tartomány ("C2: CE5"))Tartomány ("D7") = Application.WorksheetFunction.Subtotal (1, tartomány ("D2: D5"))Tartomány ("D8") = Application.WorksheetFunction.Subtotal (2, tartomány ("D2: D5"))Tartomány ("D9") = Application.WorksheetFunction.Subtotal (4, tartomány ("D2: D5"))Tartomány ("D10") = Application.WorksheetFunction.Subtotal (5, tartomány ("D2: D5"))Tartomány ("D11") = Application.WorksheetFunction.Subtotal (9, tartomány ("D2: D5"))Tartomány ("E7") = Application.WorksheetFunction.Subtotal (1, tartomány ("E2: E5"))Tartomány ("E8") = Application.WorksheetFunction.Subtotal (2, tartomány ("E2: E5"))Tartomány ("E9") = Application.WorksheetFunction.Subtotal (4, tartomány ("E2: E5"))Tartomány ("E10") = Application.WorksheetFunction.Subtotal (5, tartomány ("E2: E5"))Tartomány ("E11") = Application.WorksheetFunction.Subtotal (9, tartomány ("E2: E5")) |
a következő eredményeket hozza
A függvény argumentumok (függvényszám stb.) Esetén megadhatja azokat közvetlenül a függvénybe, vagy definiálhat helyettük változókat.
Térjen vissza az Excel összes funkciójának listájához
