SUBTOTAL v Exceli (vzorec, príklady) Použitie funkcie SUBTOTAL

Obsah:

Anonim

Funkcia SUBTOTAL Excel (obsah)

  • SUBTOTAL v Exceli
  • SUBTOTAL Formula v Exceli
  • Ako používať funkciu SUBTOTAL v Exceli?

SUBTOTAL v Exceli

  • Medzisúčet je vstavaná funkcia v Exceli, ktorá je kategorizovaná podľa funkcie matematika / trigger
  • Medzisúčet nielen spočíta čísla v definovanom rozsahu buniek, ale pomôže vykonaním rôznych aritmetických operácií
  • SUBTOTAL je univerzálny. Môžete ho použiť na priemerovanie, zhrnutie, spočítanie veľa buniek a ďalšie výpočty

definícia

Vráti medzisúčet v databáze alebo zozname, tj aby zistil medzisúčet daného rozsahu buniek. Vykonáva určený výpočet pre danú množinu hodnôt.

SUBTOTAL Formula v Exceli

Vzorec pre funkciu SUBTOTAL je nasledujúci:

SUBTOTAL (function_num, ref1, (ref2), …) Kde

Function_num alebo kód operácie: Označuje, ktorá funkcia by sa mala použiť na výpočty medzisúčtov v danom zozname alebo je to typ matematickej operácie, ktorú chcete vykonať

Argument function_num sa uplatňuje na základe nižšie uvedených dvoch kritérií alebo metódy

  1. Zahrnúť skryté hodnoty Ak je argument function_num medzi 1-11, funkcia SUBTOTAL bude obsahovať skryté hodnoty.
  2. Ignorovať skryté hodnoty Keď je argument function_num medzi 101-111, funkcia SUBTOTAL vylúči alebo ignoruje skryté hodnoty. Nájde hodnoty iba pre neviditeľné riadky, ktoré pri výpočte ignorujú skryté hodnoty.
Fun_num
Zahŕňa skryté hodnoty		 Fun_num
Nezahŕňa skryté hodnoty		 funkcie popis
1 101 PRIEMERNÁ Vypočíta priemer zadaného rozsahu.
2 102 COUNT Počíta počet buniek, ktoré obsahujú číselné hodnoty.
3 103 COUNTA Počíta neprázdne bunky v zadanom rozsahu.
4 104 MAX Zistí najväčšiu hodnotu v zadanom rozsahu.
5 105 MIN Zistí najmenšiu hodnotu v určenom rozsahu.
6 106 VÝROBOK Vypočíta súčin buniek v určenom rozsahu.
7 107 SMODCH.VÝBĚR Odhaduje smerodajnú odchýlku v určenom rozsahu.
8 108 STDEVP Vypočítava smerodajnú odchýlku na základe celej populácie.
9 109 SUM Vypočíta súčet zadaného rozsahu.
10 110 VAR Odhaduje rozptyl v špecifikovanom rozsahu.
11 111 var Odhaduje rozptyl na základe celej populácie.

ref1, (ref2): Jeden alebo viac odkazov na bunky obsahujúce hodnoty, ktoré sa majú pre medzisúčet vykonať výpočty.

Ako používať funkciu SUBTOTAL v Exceli?

Pozrime sa, ako funguje funkcia SUBTOTAL v Exceli.

Túto šablónu funkcie SUBTOTAL Function Excel si môžete stiahnuť tu - šablónu funkcie SUBTOTAL Function Excel

Príklad č. 1 - SUBTOTAL vzorec pre hodnoty skryté pomocou Excel Excel alebo riadky skryté filtrom

V nižšie uvedenom príklade obsahuje tabuľka Štvrťročné údaje o predaji, kde jednotlivé štvrťroky v stĺpci E a údaje o predaji v stĺpci F.

Sumová funkcia sa použije v bunke „F16“, tj = SUM (F4: F15).

Vracia alebo vedie k hodnote 1184 .

Funkcia SUM vracia rovnakú hodnotu. tj 1184 namiesto 194 pre údaje Q4, ak sú riadky skryté filtrom (nižšie uvedená snímka obrazovky).

Ak chcem iba správnu hodnotu SUM údajov Q4, musím použiť funkciu SUBTOTAL.

Pred použitím funkcie SUBTOTAL na paneli s nástrojmi údajov použite filter pre riadok záhlavia.

Teraz prepočítame konkrétne údaje o predaji Q1 alebo Q2 alebo Q3 alebo Q4 osobitne pre filtrované údaje.

Použite 109 ako argument function_num vo funkcii SUBTOTAL na zhrnutie údajov. tj = SUBTOTAL (109, C17: C28)

Funkcia SUBTOTAL ignoruje riadky skryté filtrom a počíta správny výsledok.

Tu sa hodnota medzisúčtu dynamicky mení podľa filtra, nájde súčet hodnôt iba vo viditeľných riadkoch; skryté riadky sú zanedbávané funkciou SUBTOTAL. (Nižšie uvedené napr.)

Q4

Q3

Q2

Q1

Príklad č. 2 - SUBTOTAL Funkcia pre manuálne skryté riadky

V hárku programu Excel boli riadky č. 15, 16, 19 a 20 skryté pomocou možnosti formátovania riadkov, zvýraznením týchto riadkov, kliknutím pravým tlačidlom myši a výberom Skryť.

V bunke C23 tabuľky sa používa medzisúčet na výpočet súčtu viditeľných buniek.

Aby sme zhrnuli údaje, musíme použiť 109 ako argument function_num vo funkcii SUBTOTAL.

tj = SUBTOTAL (109, C14: C21)

Funkcia SUBTOTAL tu ignoruje manuálne skryté riadky a vypočítava správny výsledok.

Súčet hodnôt nájde iba vo viditeľných riadkoch; skryté riadky sú zanedbávané funkciou SUBTOTAL

Príklad č. 3 - SUBTOTAL Funkcia pre normálnu hodnotu

Keďže sú viditeľné všetky bunky v rozmedzí štvrťročných údajov o predaji, žiadna z nich nie je filtrovaná alebo sú skryté riadky, zahŕňa všetky hodnoty.

V nižšie uvedenom príklade sa používajú viaceré medzisúčtové funkcie.

V bunke je použitá funkcia C14, C15 a C16 SUBTOTAL.

Na výpočet hodnoty SUM sa číslo funkcie 9 alebo 109 používa vo funkcii medzisúčtu, kde sa počíta súčet zadaného rozsahu, tj = SUBTOTAL (9, C4: C11)

Dá nám to súčet všetkých hodnôt v C4: C11, za predpokladu, že žiadne nie sú filtrované a riadky sú skryté v tomto špecifikovanom rozsahu.

Na výpočet hodnoty MIN sa číslo funkcie 5 alebo 105 používa vo funkcii medzisúčtu, kde zistí najmenšiu hodnotu v určenom rozsahu. V bunke C15 sa používa funkcia SUBTOTAL, tj = SUBTOTAL (5, C4: C11)

Zistí najmenšiu hodnotu v určenom rozsahu, tj C4: C11

Na výpočet hodnoty MAX sa číslo funkcie 4 alebo 104 používa vo funkcii medzisúčtu, kde zistí najväčšiu hodnotu v danom rozsahu. V bunke C16 je použitá funkcia SUBTOTAL, tj = SUBTOTAL (4, C4: C11)

Zistí najväčšiu hodnotu v určenom rozsahu, tj C4: C11.

Čo je potrebné pamätať na funkciu SUBTOTAL

  • Na nájdenie súčtu filtrovaných hodnôt možno použiť funkciu SUBTOTAL.
  • Funkcia SUBTOTAL sa používa na ignorovanie hodnôt v skrytých riadkoch.
  • Hodnota argumentu funkcie _num by nemala byť väčšia ako 11 alebo menšia ako 1, podobne by nemala byť väčšia ako 111 alebo menšia ako 101, v opačnom prípade poskytne # HODNOTA! chyba.
  • Funkcia SUBTOTAL je použiteľná iba pre číselnú hodnotu.
  • Ak sú v medzisúčte SUBTOTAL prítomné nejaké medzisúčty, budú zanedbané, tj ak sú vo vstupnom rozsahu iného vzorca SUBTOTAL prítomné nejaké už existujúce SUBTOTAL vzorce, potom sa tieto hodnoty počas výpočtu zanedbávajú pomocou medzisúčtu, aby sa zabránilo dvojitému započítaniu & zabraňuje tak chybám dvojitého počítania.
  • Na dynamické zhrnutie údajov možno použiť funkciu SUBTOTAL.
  • Prázdne bunky a bunky obsahujúce nečíselné hodnoty sa pri výpočte ignorujú pomocou medzisúčtu.

Odporúčané články

Toto bol sprievodca SUBTOTAL. Tu diskutujeme SUBTOTAL vzorec a ako používať funkciu SUBTOTAL spolu s vynikajúcim príkladom a stiahnuteľnými vynikajúcimi šablónami. Tieto užitočné funkcie sa môžete tiež pozrieť v exceli -

  1. Funkcia SEARCH v Exceli
  2. NÁJDITE funkciu v Exceli
  3. NIE v Exceli
  4. DOPRAVA v Exceli