Vytvorte súčty ad hoc pre množinu údajov programu Excel, kde budete filtrovať údaje. Aj v tomto videu: Použitie SUBTOTAL (109,) na sčítanie údajov, ktoré boli manuálnymi skrytými riadkami. Pomocou klávesov Ctrl + Ditto skopírujete presný vzorec do jedného riadku.
Po použití filtra by bolo skvelé vidieť celkový počet viditeľných buniek.
Vyberte prázdnu bunku pod každým zo svojich číselných stĺpcov. Kliknite na tlačidlo Automatické zhrnutie.
Namiesto vkladania vzorcov SUM program Excel vkladá =SUBTOTAL(9,… )vzorce. Nasledujúci vzorec zobrazuje súčet iba viditeľných buniek.
Vložte niekoľko prázdnych riadkov nad svoje údaje. Vystrihnite vzorce spod údajov a prilepte ich do riadku 1 so štítkom Total Visible.
Teraz, keď meníte filtre, aj keď údaje zapĺňajú viac ako jednu celú obrazovku s údajmi, uvidíte súčty v hornej časti tabuľky.
Ďakujeme Samovi Radakovitzovi z tímu Excel za filtrovanie podľa výberu - nie za navrhnutie filtra podľa výberu, ale za formalizáciu filtra podľa výberu!
Pozeraj video
- Vytvorte súčty ad hoc pre množinu údajov programu Excel, kde budete filtrovať údaje. Aj v tomto videu: Použitie SUBTOTAL (109,) na sčítanie údajov, ktoré boli manuálnymi skrytými riadkami. Pomocou klávesov Ctrl + Ditto skopírujete presný vzorec do jedného riadku.
Prepis videa
Sponzorom podcastu je kniha „MrExcel XL“, ktorá obsahuje viac ako 40 tipov na Excel, karikatúry Excel, koktaily, tweety a vtipy.
Naučte sa Excel z podcastu, epizóda 1976 - Celkovo viditeľné bunky!
Ahoj, vitaj späť na netcastu, som Bill Jelen. Samozrejme, všetci, úplne prvý vzorec, ktorý väčšina ľudí vytvorí, je AutoSum. Ste tu dole pod stĺpcom čísel, získate tlačidlo Automatické zhrnutie a pomocou funkcie súčtu bude sčítavať čísla nad ním, kým nenarazí na vzorec alebo text. Správny? Alebo Alt + = robí presne to isté. Alebo ešte lepšie, vyberte viac buniek a potom stlačte AutoSum. Všetky tieto vzorce sa vložia bez toho, aby ste ich museli potvrdzovať.
Ale tu je super trik, dobre, zapnime teda automatický filter. Použijeme teda trik Filtrovať podľa výberu, zo včerajšieho podcastu. Údaje sa filtrujú. Prejdem na úplne prvý prázdny riadok pod údajmi a potom stlačím tlačidlo AutoSum. Ale vidíte, deje sa niečo veľmi odlišné. Namiesto funkcie SUM dostaneme = SUBTOTAL (9! A čo to je, toto je Celkom viditeľných buniek. Dobre, super, funguje to.
Teraz je to tak, pred takmer desiatimi rokmi som vo Fort Wayne v štáte IN robil živý seminár programu Excel pre Inštitút interných audítorov. A niekto mi ukázal tento trik a tento trik sa mi naozaj páči. Trik som nikdy predtým nevidel, bol to super, povedal som, že je to super, toto pridám na seminár. A potom nasledujúci rok som bol späť vo Fort Wayne a ukázal sa manažér toho chlapíka, správne, prvý rok neprišiel, pretože si myslel, že vie všetko o Exceli. Ale vrátil sa, aby si tento trik uplatnil, a hovorí: „Pozri, počul som, že môj finančný analytik ti povedal tento trik, ale nepovedal ti to najlepšie, choď hore nad svoje údaje a tam vlož dva riadky. A potom, keď budete mať v dolnej časti celkový počet viditeľných buniek, vystrihnite, CTRL + X a prilepte, CTRL + V, v poriadku, čo to je,ak prepnete na iného zákazníka: „Dobre, tak tu vyberiem ešte niekoľko zákazníkov.“ A teraz mám viac zákazníkov, ako sa zmestí na obrazovku! A nemusím rolovať dole dolu, aby som videl súčty, súčty sú vždy hore hore! “
V poriadku, tento trik, ktorý využíva AutoSum pod údajmi filtra, funguje, keď skryjete riadky s filtrom. Čo ak riadky skryjete ručne? Takže tu som vybral niekoľko riadkov, už som išiel do Format, Hide & Unhide, Hide Row! Takže teraz máme riadky skryté. A zídem dole a stlačím Alt + = alebo AutoSum a nedáva mi funkciu SUBTOTAL, dáva mi funkciu SUM.
Teraz, v tomto okamihu, urobím špeciálnu vec, urobím CTRL + '(ditto, apostrof). Prináša úplne rovnaký vzorec nadol bez zmeny odkazu na bunku. Takže je to E2: E8 zmenené alebo nezmenené, zvyčajne, ak kopírujete a prilepíte zmenu na E3: E9. Zmením to na SUBTOTAL (9 a uvidíte, že to nefunguje, stále nám dáva všetky bunky. 1000 + 400 + 800 nie je 4500, vrátane skrytých riadkov. Ale tu je to, čo musíte urobiť, dostať sa do CTRL + 'a zmeniť tých 9 na 109, a získate: Skutočný celkový viditeľný údaj založený na ručne skrytých riadkoch!
Dobre, máme to, takže niekoľko rôznych trikov, AutoSum, Alt + =, nám dá iba funkciu SUM. Ak ste pod údajmi a stlačíte AutoSum, dostanete SUBTOTAL (9, ktorý funguje skvele pri filtrovaní), alebo môžete len ručne zadať SUBTOTAL (109), aby ste získali celkový údaj, ste ručne filtrovali.
Dobre, tu je rekapitulácia konceptov v dnešnom videu: Toto, mimochodom, nie je ani tipom č. 3 v knihe, jedná sa o bonusový tip # 2b vpravo, teda medzi tipom 2 a tipom 3. Kniha pokrýva všetky tieto položky. Ďakujem všetkým, ktorí to navrhli, ľuďom v kapitole Fort Wayne Indiana v rámci IIA, kde som to prvýkrát počul. Je to skvelá kniha, môžete si ju kúpiť v tlačenej alebo elektronickej knihe, kliknite na to „i“ v pravom hornom rohu. Dostali by ste sa k ďalším informáciám o knihe.
Dobre, chcem sa vám poďakovať za návštevu, uvidíme sa nabudúce pri ďalšom netcaste od!
