SUBTOTAL v Excelu (formula, primeri) - Uporaba funkcije SUBTOTAL

Excel SUBTOTAL Funkcija (kazalo)

• SUBTOTAL v Excelu
• SUBTOTALNA formula v Excelu
• Kako uporabljati funkcijo SUBTOTAL v Excelu?

SUBTOTAL v Excelu

• Subtotalna funkcija je vgrajena funkcija v excelu, ki jo uvrščamo v funkcijo matematika / trig
• Subtotal ne bo le sešteval števil v določenem območju celic, temveč bo pomagal z izvajanjem različnih aritmetičnih operacij
• SUBTOTAL je vsestranski. Z njim lahko povprečite, seštejete, preštete kup celic in druge izračune

Opredelitev

Vrne vmesno točko v bazo podatkov ali seznam, tj. Če želite izvedeti vmesno točko določenega obsega celic. Izvede določen izračun za dani niz vrednosti.

SUBTOTALNA formula v Excelu

Formula funkcije SUBTOTAL je naslednja:

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

Function_num ali operacijska koda: Nakazuje, katero funkcijo je treba uporabiti za izračun podskupin na določenem seznamu ali je vrsta matematične operacije, ki jo želite izvajati

Argument function_num se uporabi na podlagi spodnjih dveh meril ali metode

1. Vključi skrite vrednosti Ko je argument function_num med 1-11, funkcija SUBTOTAL vključuje skrite vrednosti.
2. Ignoriraj skrite vrednosti Ko je argument function_num med 101-111, bo funkcija SUBTOTAL izključila ali prezrla skrite vrednosti. Vrednosti bodo našli le nevidne vrstice, v izračunu pa bo zanemaril skrite vrednosti.

Fun_num Vključuje skrite vrednosti		Fun_num Izključi skrite vrednosti		Funkcija		Opis
1		101		POVPREČNO		Izračuna povprečje določenega obsega.
2		102		COUNT		Šteje število celic, ki vsebujejo številčne vrednosti.
3		103		COUNTA		Šteje neprazne celice v določenem območju.
4		104		MAX		Ugotovila bo največjo vrednost v določenem območju.
5		105		MIN		Ugotovila bo najmanjšo vrednost v določenem območju.
6		106		IZDELEK		Izračuna produkt celic v določenem območju.
7		107		STDEV		Standardno odstopanje ocenjuje v določenem območju.
8		108		STDEVP		Standardno odstopanje izračuna na podlagi celotne populacije.
9		109		SUM		Izračuna vsoto določenega obsega.
10		110		VAR		Ocenjuje odstopanje v določenem območju.
11		111		VARP		Odstopanje ocenjuje na podlagi celotne populacije.

ref1, (ref2): eno ali več sklicev na celice, ki vsebujejo vrednosti, ki jih je treba izračunati za subtotal.

Kako uporabljati funkcijo SUBTOTAL v Excelu?

Poglejmo, kako deluje funkcija SUBTOTAL v Excelu.

To predlogo SUBTOTAL funkcije Excel lahko prenesete tukaj - SUBTOTAL Function Excel Predloga

Primer # 1 - SUBTOTALNA formula za vrednosti, skrite s pomočjo programa Excel Autofilter ali vrstic, skritih s filtrom

V spodnjem primeru je tabela četrtletni podatki o prodaji, kjer so posamezna četrtletja v stolpcu E in podatki o prodaji v stolpcu F.

Funkcija vsote se uporabi v celici "F16", tj. = SUM (F4: F15).

Vrne ali prinese vrednost 1184 .

Funkcija SUM vrne isto vrednost. to je 1184, namesto 194 za podatke Q4, če vrsticami skrije filter (spodaj omenjeni posnetek zaslona).

Če želim samo pravilne vrednosti SUM Q4, potem moram uporabiti funkcijo SUBTOTAL.

Preden uporabite funkcijo SUBTOTAL, pod orodno vrstico podatkov uporabite filter za vrstico glave.

Zdaj izračunamo posebej Q1 ali Q2 ali Q3 ali Q4 prodajne podatke posebej za filtrirane podatke.

Uporabite 109 kot argument function_num v funkciji SUBTOTAL za povzemanje podatkov. tj = SUBTOTALNO (109, C17: C28)

Funkcija SUBTOTAL ignorira vrstice, ki jih skriva filter in izračuna pravilen rezultat.

Tu se subtotalna vrednost dinamično spreminja glede na filter, najde seštevek vrednosti samo v vidnih vrsticah; skritih vrstic je funkcija SUBTOTAL zapostavljena. (Spodaj omenjeno npr.)

Q4

Q3

Q2

Q1

Primer # 2 - SUBTOTALNA funkcija za ročno skrite vrstice

V listu excel so bile vrstice št. 15, 16, 19 in 20 skrite s pomočjo možnosti oblikovanja vrstic, tako da označite te vrstice, z desno miškino tipko kliknite miško in izberete Skrij.

Za izračun vsote vidnih celic se v celici C23 preglednice uporablja subtotalna funkcija.

Za seštevanje podatkov moramo uporabiti 109 kot argument function_num v funkciji SUBTOTAL.

tj = SUBTOTALNO (109, C14: C21)

Tukaj funkcija SUBTOTAL ignorira ročno skrite vrstice in izračuna pravilen rezultat.

Našel bo vsoto vrednosti samo v vidnih vrsticah; skritih vrstic je funkcija SUBTOTAL zapostavljena

Primer # 3 - SUBTOTALNA funkcija za normalno vrednost

Ker so vidne vse celice v razponu četrtletnih prodajnih številk, nobena od njih ni filtrirana ali vrstice skrite, vključuje vse vrednosti.

V spodnjem primeru je uporabljenih več subtotalnih funkcij.

V celici se uporablja funkcija C14, C15 in C16 SUBTOTAL.

Za izračun vrednosti SUM se v vmesni funkciji uporabi funkcijska številka 9 ali 109, kjer izračuna vsoto določenega obsega, tj = SUBTOTALNO (9, C4: C11)

Podaril nam bo vsoto vseh vrednosti v C4: C11, pod pogojem, da nobena ni filtrirana in vrstice so skrite v določenem območju.

Za izračun vrednosti MIN se v vmesni funkciji uporabi številka 5 ali 105, kjer bo ugotovila najmanjšo vrednost v določenem območju. V celici C15 se uporabi funkcija SUBTOTAL, to je = SUBTOTAL (5, C4: C11)

Ugotovila bo najmanjšo vrednost v določenem območju, tj. C4: C11

Za izračun MAX vrednosti se v vmesni funkciji uporabi funkcijska številka 4 ali 104, kjer bo ugotovila največjo vrednost v določenem območju. V celici C16 se uporabi funkcija SUBTOTAL, to je = SUBTOTAL (4, C4: C11)

Ugotovila bo največjo vrednost v določenem območju, to je C4: C11.

Spomniti se na funkcijo SUBTOTAL

• Za iskanje vsote filtriranih vrednosti je mogoče uporabiti funkcijo SUBTOTAL.
• Funkcija SUBTOTAL se uporablja za zanemarjanje vrednosti v skritih vrsticah.
• Funkcija _ num argument argumentov ne sme biti večja od 11 ali manjša od 1, podobno ne sme biti večja od 111 ali manjša od 101, sicer bo podala vrednost # VALUE! napaka.
• Funkcija SUBTOTAL je uporabna samo za številčno vrednost.
• Če v območju SUBTOTAL obstajajo podtočki, jih bomo zanemarili, tj. Če so v vhodnem območju druge formule SUBTOTAL prisotne kakršne koli formule SUBTOTAL, potem te vrednosti med izračunom zanemarimo s subtotalnimi funkcijami, tako da se izognemo dvojnemu štetju & preprečuje napako dvojnega štetja.
• SUBTOTALNO funkcijo lahko uporabljamo za dinamično povzemanje podatkov.
• Prazne celice in celice, ki vsebujejo neštevilčne vrednosti, se med izračunom prezrejo s subtotalno funkcijo.

Priporočeni članki

To je vodnik za SUBTOTAL. Tukaj razpravljamo o formuli SUBTOTAL in kako uporabljati funkcijo SUBTOTAL skupaj s primerom excela in prenosljivimi predlogi excela. Te uporabne funkcije si lahko ogledate tudi v odlični obliki -

1. ISKANJE Funkcija v Excelu
2. NAJDANJE Funkcija v Excelu
3. NE v Excelu
4. TRANSPOSE v Excelu