Vršni filtar top 10 filtra daje ukupno vidljivih redaka
Pivot tablice nude top 10 filtara. Fora je. Fleksibilan je. Ali mrzim to, i reći ću vam zašto.
Evo pivot tablice koja prikazuje prihode po kupcima. Ukupni prihod iznosi 6,7 milijuna USD.
Što ako moj upravitelj ima pozornost zlatne ribice i želi vidjeti samo pet najboljih kupaca?
Za početak otvorite padajući izbornik u A3 i odaberite Filteri vrijednosti, Top 10.
Super fleksibilni dijaloški okvir Top 10 Filter omogućuje gornji / donji dio. Može 10, 5 ili bilo koji drugi broj. Možete tražiti pet najboljih stavki, 80% ili dovoljno kupaca da biste došli do 5 milijuna dolara.
Ali ovdje je problem: rezultirajuće izvješće prikazuje pet kupaca i ukupni udio tih kupaca, umjesto ukupnog broja svih.
Ali prvo, nekoliko važnih riječi o AutoFilteru
Shvaćam da se ovo čini pitanje izvan zidova. Ako želite uključiti padajuće izbornike Filter na uobičajenom skupu podataka, kako se to radi? Evo tri doista uobičajena načina:
- Odaberite jednu ćeliju u svojim podacima i kliknite ikonu Filtar na kartici Podaci.
- Odaberite sve svoje podatke pomoću Ctrl + * i kliknite ikonu Filtar na kartici Podaci.
- Pritisnite Ctrl + T da biste podatke oblikovali u obliku tablice.
To su tri jako dobra načina. Sve dok poznajete bilo kojeg od njih, apsolutno ne treba znati drugi način. Ali evo nevjerojatno nejasnog, ali čarobnog načina za uključivanje filtra:
- Idite na svoj red zaglavlja, idite na krajnju desnu ćeliju zaglavlja. Pomakni jednu ćeliju udesno. Iz nepoznatog razloga, kada ste u ovoj ćeliji i kliknete ikonu Filtriraj, Excel filtrira podatke postavljene s vaše lijeve strane. Nemam pojma zašto ovo djeluje. Zaista ne vrijedi razgovarati o tome jer već postoje tri jako dobra načina za uključivanje padajućih padajućih filtera. Ovu ćeliju nazivam Čarobnom ćelijom.
A sada, Povratak na zaokretne tablice …
Dakle, postoji pravilo koje kaže da ne možete koristiti automatske filtere kada ste u zaokretnoj tablici. Pogledaj ispod? Ikona filtra je siva jer sam odabrao ćeliju u zaokretnoj tablici.
Nikad zapravo nisam razmišljao zašto je Microsoft to zatamnio. To mora biti nešto interno što kaže da AutoFilter i zaokretna tablica ne mogu koegzistirati. Dakle, u Excel timu postoji netko tko je zadužen za presijavanje ikone filtra. Ta osoba nikada nije čula za Čarobnu ćeliju. Odaberite ćeliju u zaokretnoj tablici i filtar će postati siv. Kliknite izvan zaokretne tablice i filtar je ponovno omogućen.
Ali čekaj. Što je s Čarobnom ćelijom o kojoj sam vam upravo rekao? Ako kliknete na ćeliju s desne strane zadnjeg naslova, Excel će zaboraviti sivu ikonu Filtrirati!
Svakako, Excel dodaje padajuće izbornike AutoFilter u gornji redak vaše zaokretne tablice. A AutoFilter djeluje drugačije od filtara zaokretne tablice. Idite na padajući padajući prihod i odaberite Filteri brojeva, Top 10 …
U dijalogu Top 10 AutoFilter odaberite Top 6 stavki. To nije pogreška u kucanju … Ako želite pet kupaca, odaberite 6. Ako želite 10 kupaca, odaberite 11.
Za AutoFilter, ukupni redak je najveća stavka u podacima. Prvih pet kupaca zauzimaju položaje od 2 do 6 u podacima.
Oprez
Jasno je da ovim trikom rupite rupu u Excelu. Ako kasnije promijenite osnovne podatke i osvježite svoju zaokretnu tablicu, Excel neće osvježiti filtar, jer, koliko Microsoft zna, ne postoji način da se filtar primijeni na zaokretnu tablicu!
Bilješka
Cilj nam je zadržati ovu tajnu od Microsofta, jer je to prilično cool značajka. Već je neko vrijeme "slomljen", tako da postoji puno ljudi koji bi se do sada mogli na njega pouzdati.
Potpuno legalno rješenje u programu Excel 2013+Ako želite pivot tablicu koja vam prikazuje pet najboljih kupaca, ali ukupan broj svih kupaca, svoje podatke morate premjestiti izvan Excela. Ako imate Excel 2013 ili 2016, to je vrlo prikladan način. Da bih vam to pokazao, izbrisao sam izvornu zaokretnu tablicu. Odaberite Umetanje, zaokretna tablica. Prije nego što kliknete U redu, označite okvir koji kaže Dodaj ove podatke u model podataka.
Izgradite svoju stožernu tablicu kao i obično. Pomoću padajućeg izbornika u A3 odaberite Filteri vrijednosti, Top 10, i tražite pet najboljih kupaca. S odabranom jednom ćelijom u zaokretnoj tablici, idite na karticu Dizajn na vrpci i otvorite padajući meni Podzbrojevi. Konačni izbor u padajućem izborniku je Uključi filtrirane stavke u zbrojeve. Obično je ovaj izbor zasivljen. No budući da su podaci pohranjeni u podatkovnom modelu umjesto u uobičajenoj predmemoriji zakretanja, ova je opcija sada dostupna.
Odaberite opciju Uključi filtrirane stavke u zbroj, a vaš zbroj sada uključuje zvjezdicu i zbroj svih podataka.
Ovaj trik prvotno mi je došao od Dana na mom seminaru u Philadelphiji. Hvala Miguelu Caballeru što je predložio ovu značajku.
Gledaj video
- Vršni filtar top 10 filtra daje ukupno vidljivih redaka
- Uključi filtrirane stavke u ukupne vrijednosti je sivo
- Čudan način pozivanja filtra podataka iz čarobne ćelije
- Filteri podataka nisu dopušteni u zaokretnim tablicama
- Excel ne uspijeva isključiti filtar podataka iz čarobne ćelije
- Tražite najboljih 6 da biste dobili top 5 i ukupni zbroj
- Korisno za filtriranje prema određenoj zakretnoj stavci
- Excel 2013 ili noviji: drugačiji način za dobivanje istinitog zbroja
- Pošaljite svoje podatke putem podatkovnog modela
- Uključi filtrirane stavke u zbrojeve bit će dostupno
- Nabavite Total sa zvjezdicom
- Ovaj trik naučio sam prije 10 i više godina od Dana u Philadelphiji
Prijepis videozapisa
Naučite Excel za podcast, epizoda 1999 - Pivot Table True Top Five
Podcistiram cijelu ovu knjigu. Postoji popis za reprodukciju, kliknite I u gornjem desnom kutu da biste slijedili taj popis. Dobrodošli natrag na netcast. Ja sam Bill Jelen.
U redu, izradit ćemo pivot tablicu i želimo pokazati, ne sve kupce, već samo pet najboljih kupaca. INSERT, zaokretna tablica. Dobro, stavit ću kupca s lijeve strane i prihoda. Dakle, evo čitavog našeg popisa kupaca zabilježenih kao 6,7 milijuna dolara. Excel, olakšava dobivanje prvih pet. Idite na Oznake redaka, Filteri vrijednosti, vrh 10. Ne mora biti vrh. Može biti gornja ili donja. Ne mora biti pet. Može biti dvadeset, četrdeset, može i svejedno. Top osamdeset posto, dajte mi dovoljno zapisa da dođem do tri milijuna dolara ili četiri milijuna dolara, ali eto, krećemo. Pet najboljih stavki. Sjetite se sada 6,7 milijuna dolara, kliknite U redu i moj veliki problem ovdje je taj što taj ukupan zbroj nije 6,7 milijuna. Kad to dam potpredsjedniku prodaje, on će se izbezumiti, rekavši, pričekajte malo,Znam da sam zaradio više od 3,3 milijuna dolara. U redu, pa ćemo poništiti, poništiti to i vratiti se izvornim podacima.
Sada sljedeći trik koji sam naučio tijekom jednog od svojih Power Excel seminara u Philadelphiji. Tip po imenu Dan u drugom redu, pokazao mi je ovo. Prije više od deset godina pokazao mi je ovaj trik, a prvo moramo razgovarati o Filtrima. Dakle, normalno, ako ćete koristiti uobičajeni filtar, ovaj filtar ovdje, odaberete bilo koju ćeliju u svom skupu podataka i kliknete ikonu filtra, ili neki ljudi odaberu cijeli skup podataka, CONTROL * i kliknu ikonu filtra, ali postoji i treći način. Način do kojeg nikoga nije briga. Ako odete do posljednje Heading Cell, u mom slučaju to je Cost in L1 i idete za jednu ćeliju udesno. To nazivam čarobnom ćelijom, nemam pojma zašto, ali iz nepoznatog razloga iz ove ćelije mogu filtrirati susjedni Skup podataka. Dobro, to je čudan način i nikoga nije briga za ovo.
Točno, jer postoje još dva jako dobra načina za pozivanje filtra, nitko ne mora znati o čarobnoj ćeliji, ali ovdje je stvar, pogledajte unutar zaokretne tablice, siva je. Ne smijete koristiti te filtere. To je protiv pravila. Sada, ako dođem ovdje, više sam nego dobrodošao koristiti filter, ali iznutra oni ocjenjuju. Ne znam tko je osoba koja ovo sivi, ali nikada nisu čuli moj mali razgovor o čarobnoj ćeliji, jer ako odem do zadnje Zadnje stanice i odem jednu ćeliju udesno, pogledajte to, zaboravili su isključiti filtar i sad sam upravo dodao stare automatske filtre u zaokretnu tablicu. Dakle, dolazim ovdje, idem na Filtere brojeva, to se razlikuje od Filtera vrijednosti. I dalje se zove Top Ten. Nešto drugačije, tražit ću prvih pet, ne prvih šest.Šest najboljih jer je za ovaj Filtriranje ukupni zbroj samo još jedan red, a ukupni zbroj je najveća stavka, a onda kad dobijem pitanje od 2. do 6., dobijem pet najboljih stavki.
U redu, eto nas. Kul hack filter, koji nam daje pet najboljih stavki i stvarni ukupan broj svih. U redu, par stvari. Ne zaboravite na čarobnu ćeliju. U redu, nema načina da isključite ovaj filtar, osim ako se ne vratite u čarobnu ćeliju. U redu, morate se sjetiti čarobne stanice. Također, ako promijenite osnovne podatke i osvježite zaokretnu tablicu, neće osvježiti filtar, jer koliko Microsoft zna, ne smijete imati filtar.
Ovo je korisno za druge stvari. Ponekad imamo proizvode koji idu preko vrha. Idemo ovdje u tabličnom obliku. Nije potrebno, samo volim dobivati stvarne naslove. Gizmo, Widget, Gadgeti, Doodads. Dobro, možda ste upravitelj Doodads-a i morate vidjeti samo kupce koji su imali određenu vrijednost i Doodads-ove. Stoga odlazim do čarobne ćelije, uključujem filtar i tada pod Doodads mogu tražiti stavke koje su veće od nule. Kliknite U redu. Dobro, ta vrsta filtriranja ne bi bila moguća na uobičajenoj zaokretnoj tablici, ali je moguća pomoću čarobne ćelije.
U redu, poništimo popis. Isključimo ovaj filtar i uklonimo zaokretnu tablicu, a ako ste u programu Excel 2013 ili novom, pokazat ću vam potpuno legalan način da na dnu dobijete točan zbroj. Umetnite zaokretnu tablicu, ovdje dolje na dnu, počevši od programa Excel 2013, ovaj vrlo bezazleni okvir ne zvuči baš uzbudljivo, dodajte ove podatke u podatkovni model. To podatke šalje iza kulisa u Power Pivot Engine. Izradite potpuno isto izvješće. Kupci dolje s lijeve strane. Prihod u srcu stožera. Zatim idite na redovite Filtere, filtre vrijednosti top 10. Zatražite prvih pet. Opet primijetite da nakon što to učinim imamo 6,7 milijuna dolara, 3,3 milijuna dolara, ali evo razlike. Kada odem na karticu Dizajn, u okviru Međuzbrojevi, ova se značajka naziva Uključi filtrirane stavke u zbrojeve,više nije zasivljeno. Na redovnom pivot stolu nije dostupan. Tamo imamo malu zvjezdicu i to je sve u svemu. Dobro, sada to naravno radi samo u programu Excel 2013 ili novijoj verziji.
U redu, trebat će mi šest tjedana da cijelu ovu knjigu iznesem ovdje na YouTubeu. Ovdje ima toliko dobrih savjeta. Savjeti koji bi vam odmah mogli uštedjeti vrijeme. Kupite cijelu knjigu odmah i imat ćete pristup svih 40, zapravo puno više od 40 savjeta. Excel tipke prečaca. Sve vrste izvrsnih stvari u ovoj knjizi.
U redu, rezime. Dakle, kada napravimo filtar za pivot tablicu 10, daje nam ukupni, ali samo vidljivi redak, a ne ono što je filtrirao. Da, ako odemo na drugu karticu i potražimo međuzbrojeve, filtrirane stavke i ukupne iznose, to je sivo, ali postoji neobičan način pozivanja Starog filtra podataka iz čarobne ćelije. Posljednja ćelija zaglavlja, idite jednu ćeliju udesno, ne možete koristiti filtre i zaokretne tablice, ali ako odete u čarobnu ćeliju, oni će je zaboraviti sivu. Sada u filtru brojeva tražite prvih šest da biste dobili prvih pet, plus ukupni zbroj. Također korisno za filtriranje prema određenoj zaokretnoj stavci: Doodads, sve ono što je imalo više od 0 u Doodads ili top 5 Doodads. Excel 2013 ili noviji, postoji drugačiji način da se dobije True Total.Označite taj okvir za podatkovni model, a zatim uključi filtrirane stavke u zbrojeve. Zbroj dobivate zvjezdicom. I hvala Danu u Philadelphiji koji me pokazao na jednom od mojih Power Excel seminara, prije više od deset godina, i dao mi ovaj sjajni mali trik. Način da se Filter provuče kroz zid pivot stola kluba. Obično ne dopuštaju taj automatski filtar.
Hej, želim ti zahvaliti što si svratio. Vidimo se sljedeći put, za još jedan prijenos s MRExcela.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast1999.xlsx
