Vytvořte součty ad hoc pro datovou sadu Excel, kde budete filtrovat data. Také v tomto videu: Použití SUBTOTAL (109,) pro sčítání dat, která byla ručně skrytými řádky. Pomocí Ctrl + Ditto zkopírujte přesný vzorec o jeden řádek dolů.
Po použití filtru by bylo skvělé zobrazit celkový počet viditelných buněk.
Vyberte prázdnou buňku pod každým ze svých číselných sloupců. Klikněte na tlačítko Automatické shrnutí.
Místo vkládání vzorců SUM vkládá Excel =SUBTOTAL(9,… )vzorce. Vzorec níže zobrazuje součet pouze viditelných buněk.
Vložte několik prázdných řádků nad svá data. Vyjměte vzorce zpod dat a vložte je do řádku 1 se štítkem Total Visible.
Nyní, když měníte filtry, i když data zaplní více než jednu celou obrazovku dat, uvidíte součty v horní části listu.
Děkujeme Samu Radakovitzovi z týmu Excel za filtrování podle výběru - nikoli za navrhování filtrování podle výběru, ale za formalizaci filtrování podle výběru!
Sledovat video
- Vytvořte součty ad hoc pro datovou sadu Excel, kde budete filtrovat data. Také v tomto videu: Použití SUBTOTAL (109,) pro sčítání dat, která byla ručně skrytými řádky. Pomocí Ctrl + Ditto zkopírujte přesný vzorec o jeden řádek dolů.
Přepis videa
Sponzorem podcastu je kniha „MrExcel XL“, která obsahuje více než 40 tipů na Excel, karikatury Excel, koktejly, tweety a vtipy.
Naučte se Excel z podcastu, epizoda 1976 - Celkově viditelné buňky!
Hej, vítej zpět na netcastu, jsem Bill Jelen. Samozřejmě každý, úplně první vzorec, který většina lidí vytváří, je AutoSum. Jste tady dole pod sloupcem čísel, získáte tlačítko AutoSum a použije funkci sum k sčítání čísel nad ním, dokud nenarazí na vzorec nebo text. Že jo? Nebo Alt + = dělá přesně to samé. Nebo ještě lépe, vyberte více buněk a poté stiskněte AutoSum a všechny tyto vzorce se umístí, aniž byste museli vzorce potvrdit.
Ale tady je skvělý trik, dobře, takže zapneme automatický filtr. Použijeme tedy trik Filtr podle výběru ze včerejšího podcastu. Data jsou filtrována. Půjdu na úplně první prázdný řádek pod daty a stisknu tlačítko AutoSum. Ale víš, děje se něco úplně jiného. Místo funkce SUM dostaneme = SUBTOTAL (9! A co to je, toto je Součet viditelných buněk. Dobře, velmi cool, že to funguje.
Teď je to věc, před téměř deseti lety jsem vedl živý power Excel seminář ve Fort Wayne ve státě IN pro Institut interních auditorů. A někdo mi tento trik ukázal a já ten trik opravdu miluji. Nikdy jsem ten trik neviděl, byl to skvělý, řekl jsem, že je to skvělé, přidám to na seminář. A pak jsem se příští rok vrátil do Fort Wayne a objevil se manažer toho chlapa, správně, první rok nepřišel, protože si myslel, že ví všechno o Excelu. Ale vrátil se, aby si tento trik uplatnil, a říká: „Podívej, slyšel jsem, že můj finanční analytik ti řekl tento trik, ale neřekl ti nejlepší část, jdi nahoru nad svá data a tam vlož dva řádky. A pak jakmile máte celkem viditelné buňky dole, vyjměte, CTRL + X a vložte, CTRL + V, dobře, co to je,pokud přepnete na jiného zákazníka: „Dobře, tak zde vyberu ještě několik zákazníků.“ A teď mám více zákazníků, než kolik se vejde na obrazovku! A nemusím posouvat dolů dolů, abych viděl součty, součty jsou vždy nahoře nahoře! “
Dobře, tento trik, který pomocí automatického součtu pod daty filtru funguje, když skryjete řádky s filtrem. Co když ručně skryjete řádky? Tady jsem vybral několik řádků, již jsem šel do Format, Hide & Unhide, Hide Rows! Takže nyní máme řádky skryté. A přijdu dolů a stisknu Alt + = nebo AutoSum a nedává mi funkci SUBTOTAL, dává mi funkci SUM.
Právě teď, v tomto okamžiku, udělám speciální věc, udělám CTRL + '(ditto, apostrof). Přinese přesně stejný vzorec, aniž by se změnil odkaz na buňku. Je to tedy změna E2: E8, nebo se nezměnila, obvykle, pokud byste kopírovali a vložili do změny E3: E9. Změním to na SUBTOTAL (9 a uvidíte, že to nefunguje, stále nám dává všechny buňky. 1000 + 400 + 800 není 4500, je to včetně skrytých řádků. Ale tady je to, co musíte udělat, dostat se do CTRL + 'a změnit 9 na 109 a získáte: True Total Visible na základě ručně skrytých řádků!
Dobře, tady to máte, takže několik různých triků, AutoSum, Alt + = nám dává pouze funkci SUM. Pokud jste pod daty a stisknete AutoSum, dostanete SUBTOTAL (9, který funguje skvěle pro filtrované), nebo můžete jen ručně zadat SUBTOTAL (109, abyste získali součet viditelných, když jste ručně filtrovali.
Dobře, zde je rekapitulace konceptů v dnešním videu: To mimochodem není ani tip č. 3 v knize, toto je Bonus tip č. 2b vpravo, tedy mezi tipem 2 a tipem 3. Kniha pokrývá všechny tyto položky. Díky všem, kteří to navrhli, jsem poprvé slyšel lidi v kapitole Fort Wayne Indiana v IIA. Je to skvělá kniha, můžete si ji koupit v tištěné podobě nebo v elektronické knize, kliknout na to „i“ v pravém horním rohu. Dostali byste se k dalším informacím o knize.
Dobře, chci vám poděkovat, že jste se zastavili, uvidíme se příště pro další netcast od!
