Vrtilna tabela Top 10 Filter prikazuje skupno število vidnih vrstic
Vrtilne tabele ponujajo top 10 filter. Vredu je. Je prilagodljiv. Ampak sovražim in vam bom povedal, zakaj.
Tu je vrtilna tabela, ki prikazuje prihodek po strankah. Skupni prihodek je 6,7 milijona dolarjev.
Kaj pa, če ima moj upravitelj pozornost zlate ribice in želi videti le pet najboljših strank?
Za začetek odprite spustni meni v A3 in izberite Filtri vrednosti, Top 10.
Izjemno prilagodljivo pogovorno okno Top 10 Filter omogoča zgornje / spodnje. Naredi lahko 10, 5 ali katero koli drugo številko. Zahtevate lahko pet najboljših izdelkov, top 80% ali dovolj strank, da pridete do 5 milijonov dolarjev.
Tu pa je težava: poročilo, ki je nastalo, prikazuje pet strank in skupni znesek teh strank namesto skupnih zneskov vseh.
Najprej pa nekaj pomembnih besed o samodejnem filtriranju
Zavedam se, da se to zdi kot vprašanje zunaj stene. Če želite v običajnem naboru podatkov vklopiti spustna menija Filter, kako to storite? Tu so trije zares pogosti načini:
- V podatkih izberite eno celico in na zavihku Podatki kliknite ikono Filter.
- S Ctrl + * izberite vse podatke in na zavihku Podatki kliknite ikono Filter.
- Pritisnite Ctrl + T, če želite podatke oblikovati kot tabelo.
To so trije res dobri načini. Dokler poznate katerega koli od njih, ni treba vedeti drugega načina. Ampak tukaj je neverjetno nejasen, a čaroben način vklopa filtra:
- Pojdite na svojo vrstico glav, pojdite na skrajno desno celico naslova. Premakni eno celico v desno. Iz neznanega razloga Excel, ko ste v tej celici in kliknete ikono za filtriranje, filtrira podatke, nastavljene na vaši levi. Pojma nimam, zakaj to deluje. Zares se ni vredno pogovarjati, ker že obstajajo trije res dobri načini za vklop spustnih menijev Filter. To celico imenujem Čarobna celica.
In zdaj, Nazaj na vrtilne tabele …
Torej obstaja pravilo, ki pravi, da samodejnih filtrov ne morete uporabljati, ko ste v vrtilni tabeli. Glej spodaj? Ikona filtra je siva, ker sem v vrtilni tabeli izbral celico.
Nikoli nisem zares razmišljal, zakaj Microsoft to zasenči. To mora biti nekaj notranjega, kar pravi, da samodejni filter in vrtilna tabela ne moreta sobivati. Torej, v Excelovi skupini je nekdo, ki je zadolžen za sivo ikono filtra. Ta oseba še nikoli ni slišala za Čarobno celico. Izberite celico v vrtilni tabeli in filter postane siv. Kliknite zunaj vrtilne tabele in filter je znova omogočen.
Ampak počakaj. Kaj pa čarobna celica, o kateri sem ti pravkar povedal? Če kliknete celico na desni strani zadnjega naslova, Excel pozabi sivo ikono filtra!
Seveda Excel doda spustne menije AutoFilter v zgornjo vrstico vrtilne tabele. Samodejni filter deluje drugače kot filtri vrtilne tabele. Pojdite na spustni meni Prihodek in izberite Številčni filtri, Top 10 …
V pogovornem oknu Top 10 AutoFilter izberite Top 6 Items. To ni tiskarska napaka…. Če želite pet strank, izberite 6. Če želite 10 strank, izberite 11.
Za AutoFilter je vrstica s skupnim seštevkom največji element v podatkih. Prvih pet strank zaseda položaje od 2 do 6 v podatkih.
Previdno
Jasno je, da s tem trikom trgate luknjo v Excelovem tkivu. Če pozneje spremenite osnovne podatke in osvežite svojo vrtilno tabelo, Excel ne bo osvežil filtra, ker kolikor Microsoft ve, filtra v vrtilni tabeli ni mogoče uporabiti!
Opomba
Naš cilj je, da to zakrijemo Microsoftu, ker je to zelo dobra lastnost. Kar nekaj časa je bil "pokvarjen", zato je že veliko ljudi, ki bi se nanj morda že zanašali.
Popolnoma pravna rešitev v programu Excel 2013+Če želite vrtilno tabelo, ki prikazuje pet najboljših strank, vendar skupni znesek vseh strank, morate podatke premakniti izven Excela. Če imate Excel 2013 ali 2016, obstaja to zelo priročen način. Da vam to pokažem, sem izbrisal prvotno vrtilno tabelo. Izberite Vstavi, vrtilna tabela. Preden kliknete V redu, izberite polje Dodaj te podatke v podatkovni model.
Zgradite svojo vrtilno tabelo kot običajno. V spustnem meniju A3 izberite Filtri vrednosti, Top 10, in povprašajte za pet najboljših strank. Z izbrano eno celico v vrtilni tabeli pojdite na zavihek Oblikovanje na traku in odprite spustni meni Vmesni seštevki. Končna izbira v spustnem meniju je Vključi filtrirane elemente v vsote. Običajno je ta izbira zatemnjena. Ker pa so podatki shranjeni v podatkovnem modelu namesto v običajnem vrtilnem predpomnilniku, je ta možnost zdaj na voljo.
Izberite možnost Vključi filtrirane elemente v skupni znesek in vaša skupna vsota zdaj vključuje zvezdico in vsoto vseh podatkov.
Ta trik me je prvotno prišel od Dana na mojem seminarju v Filadelfiji. Hvala Miguelu Caballeru, ki je predlagal to funkcijo.
Oglejte si video
- Vrtilna tabela Top 10 Filter prikazuje skupno število vidnih vrstic
- Vključi filtrirane elemente v skupne vrednosti je zatemnjeno
- Nenavaden način za priklic podatkovnega filtra iz čarobne celice
- Podatkovni filtri niso dovoljeni v vrtilnih tabelah
- Excel podatkovnega filtra iz čarobne celice ne usivi
- Prosite za top 6, da dobite top 5 plus skupni znesek
- Uporabno za filtriranje po določenem vrtilnem elementu
- Excel 2013 ali novejši: drugačen način, da dobite True Total
- Pošljite svoje podatke prek podatkovnega modela
- Vključi filtrirane elemente v skupne vrednosti bo na voljo
- Pridobite Total z zvezdico
- Tega trika sem se naučil pred desetimi leti pred Danom v Filadelfiji
Video zapis
Naučite se Excel za podcast, Epizoda 1999 - Vrtilna tabela True Top Five
Celotno knjigo objavljam v oddaji. Obstaja seznam predvajanja, kliknite I v zgornjem desnem kotu, da mu sledite. Dobrodošli nazaj na netcast. Jaz sem Bill Jelen.
V redu, torej bomo ustvarili vrtilno tabelo in želeli bomo prikazati ne vseh strank, ampak le prvih pet strank. INSERT, vrtilna tabela. V redu, kupca bom postavil na levo stran in Prihodki. Torej, tukaj je celoten seznam naših strank, zabeleženih kot 6,7 milijona dolarjev. Excel, omogoča enostavno uvrstitev med pet najboljših. Pojdite v Oznake vrstic, Filtri vrednosti, vrh 10. Ni nujno, da je vrh. Lahko je zgoraj ali spodaj. Ni treba, da je pet. Lahko je dvajset, štirideset, lahko je karkoli. Najboljših osemdeset odstotkov, dajte mi dovolj zapisov, da pridem do treh milijonov dolarjev ali štiri milijone dolarjev, toda gremo. Prvih pet predmetov. Zdaj se spomnite 6,7 milijona dolarjev, kliknite V redu in moja velika težava je ta, da ta skupni znesek ni 6,7 milijona. Ko to dam podpredsedniku prodaje, se bo zmešal in rekel, počakajte sekundo,Vem, da sem zaslužil več kot 3,3 milijona dolarjev. Torej, razveljavili bomo, razveljavili in se vrnili k prvotnim podatkom.
Zdaj pa naslednji trik, ki sem se ga naučil med enim od mojih seminarjev Power Excel v Filadelfiji. Tip z imenom Dan v drugi vrstici mi je pokazal to. Pred več kot desetimi leti mi je pokazal ta trik in najprej se moramo pogovoriti o filtrih. Običajno, če boste uporabljali običajni filter, ta filter tukaj, izberete katero koli celico v naboru podatkov in kliknete ikono filtra, ali nekateri izberejo celoten nabor podatkov, CONTROL * in kliknejo ikono filtra, obstaja pa še tretja pot. Tako, da nikogar ne zanima. Če greste na zadnjo naslovno celico, je v mojem primeru to Cost v L1 in se pomaknete eno celico v desno. Temu pravim čarobna celica, ne vem, zakaj, vendar iz neznanega razloga lahko iz te celice filtriram sosednji nabor podatkov. V redu, to je nekako čudno in nikogar to ne zanima.
Kakorkoli, ker obstajata še dva res dobra načina, kako priklicati filter, nihče ne mora vedeti o čarobni celici, toda stvar je, glej znotraj vrtilne mize, je siva. Teh filtrov ni dovoljeno uporabljati. To je v nasprotju s pravili. Zdaj, če pridem sem, sem več kot dobrodošel, da uporabim filter, v notranjosti pa so ocenjeni. Ne vem, kdo je oseba, ki to zasenči, toda še nikoli niso slišali mojega malega govora o čarobni celici, kajti če grem do zadnje celice Heading Cell in grem eno celico na desno, poglej to, pozabili so filtrirati sivo in zdaj sem v vrtilno tabelo dodal stare samodejne filtre. Torej pridem sem, pojdem na Številčni filtri, ki se razlikujejo od filtrov vrednosti. Še vedno se imenuje Top Ten. Nekoliko drugače, prosil bom za prvih pet, ne za najboljših šest.Šest najboljših, ker je za ta filter splošni seštevek le še ena vrstica, splošni seštevek pa je največji element, nato pa ob vprašanju za elemente od 2 do 6 dobim prvih pet elementov.
V redu, tu smo. Kul kramp Filter, ki nam daje prvih pet elementov in dejansko skupno število vseh. V redu, nekaj stvari. Ne pozabite na čarobno celico. No, tega filtra ni mogoče izklopiti, razen če se vrnete v čarobno celico. V redu, zato si morate zapomniti čarobno celico. Če spremenite osnovne podatke in osvežite vrtilno tabelo, filtra ne bodo osvežili, ker kolikor Microsoft ve, filtra ne smete imeti.
To je koristno za druge stvari. Včasih imamo izdelke, ki gredo čez vrh. Pojdimo sem v obliki tabele. Ni nujno, rad bi dobil prave naslove. Gizmo, pripomočki, pripomočki, doodads. V redu in morda ste upravitelj Doodads in morate videti samo stranke, ki so imele določeno vrednost, in Doodads. Torej grem v čarobno celico, vklopim Filter in nato pod Doodads lahko prosim za predmete, ki so večji od nič. Kliknite V redu. V redu, ta tip filtriranja v običajni vrtilni tabeli ne bi bil mogoč, vendar je mogoče s pomočjo čarobne celice.
Zdaj razveljavimo seznam. Izklopite ta filter in odstranite vrtilno tabelo. Če ste v Excelu 2013 ali novejšem, vam bom pokazal povsem zakonit način, kako na dnu dobite pravilno seštevek. Vstavite vrtilno tabelo, spodaj tukaj, začenši v Excelu 2013 to zelo neškodljivo polje ne zveni prav razburljivo, dodajte te podatke v podatkovni model. Ta podatke v zakulisju pošlje v Power Pivot Engine. Sestavite popolnoma enako poročilo. Stranke po levi strani. Prihodki v središču vrtilne tabele. Nato pojdite na običajne filtre, filtri vrednosti na vrh 10. Vprašajte za prvih pet. Ponovno opazite, da imamo po tem 6,7 milijona dolarjev, 3,3 milijona dolarjev, a tu je razlika. Ko grem na zavihek Oblikovanje v razdelku Vmesni seštevki, se ta funkcija, imenovana Vključi filtrirane elemente v vsote,ni več zasenčena. V običajni vrtilni tabeli ni na voljo. Tam dobimo majhno zvezdico in to je vse skupaj. V redu, zdaj to seveda deluje le v Excelu 2013 ali novejšem.
V redu, trajalo bo šest tednov, da bom celo to knjigo objavil tukaj na YouTubu. Tu je toliko dobrih nasvetov. Namigi, ki bi vam lahko takoj prihranili čas. Kupite celo knjigo zdaj in imeli boste dostop do vseh 40, pravzaprav gre za veliko več kot 40 nasvetov. Excelove bližnjice. Vse vrste odličnih stvari v tej knjigi.
V redu, povzemam. Torej, ko naredimo filter vrtilne tabele top 10, nam poda skupno, a le vidne vrstice, ne pa stvari, ki jih je filtriral. Ja, če gremo na drugi zavihek in poiščemo vmesne seštevke, filtrirane elemente in vsote, je siv, vendar obstaja nenavaden način, da iz čarobne celice prikličemo stari filter podatkov. Zadnja zadnja celica, pojdite eno celico v desno, ne morete uporabljati filtrov in vrtilnih tabel, če pa greste v čarobno celico, jo pozabijo sivo obarvati. Zdaj v številskem filtru zahtevate prvih šest, da dobite prvih pet, skupaj z vsoto. Uporabno tudi za filtriranje do določene vrtilne postavke: Doodads, vse, kar je imelo več kot 0 v Doodads ali top 5 Doodads. Excel 2013 ali novejši, obstaja drugačen način, da dobite True Total.Označite to polje za podatkovni model in nato vključi filtrirane elemente v skupne vsote. Skupno dobite z zvezdico. In hvala Danu v Philadelphiji, ki me je pred več kot desetimi leti pokazal na enem izmed mojih seminarjev Power Excel in mi dal ta mali trik. Način, kako se filter lahko prikrade skozi steno Club Pivot Table. Tega samodejnega filtra običajno ne dovolijo.
Hej, rad bi se ti zahvalil, da si se ustavil. Se vidimo naslednjič, za še eno oddajo MRExcel.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast1999.xlsx
