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. Upoštevajte, da največja stranka, Roto-Rooter, predstavlja 9% celotnega prihodka.
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. Roto-Rooter, ki je bil prej 9%, je 23% novega.
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:
- Izberite eno celico v svojih podatkih in kliknite zavihek Filter na zavihku Podatki ali pritisnite Ctrl + Shift + L.
- 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 vrstico glav in nato v 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
Obstaja pravilo, ki pravi, da ne morete uporabljati samodejnega filtra, ko ste v vrtilni tabeli. Glej spodaj? Ikona filtra je siva, ker sem v vrtilni tabeli izbral celico.
Ne vem, zakaj Microsoft to zasenči. To mora biti nekaj notranjega, kar pravi, da AutoFilter 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. In AutoFilter deluje drugače kot filter 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 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 v sistemu Windows Excel 2013 ali novejšo različico, lahko to storite na zelo priročen način. Da vam to pokažem, sem izbrisal prvotno vrtilno tabelo. Izberite Vstavi, vrtilna tabela. Preden kliknete V redu, potrdite 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 vsote in vaša skupna vsota zdaj vključuje zvezdico in vsoto vseh podatkov, kot je prikazano spodaj.
Ta trik s čarobnimi celicami mi je prvotno prišel od Dana na mojem seminarju v Philadelphiji, 15 let kasneje pa ga je ponovil drug Dan od mojega seminarja v Cincinnatiju. Hvala Miguelu Caballeru, ki je predlagal to funkcijo.
