Pöördtabel Top 10 Filter annab nähtavate ridade koguarvu
Liigendtabelid pakuvad Top 10 filtrit. See on lahe. See on paindlik. Aga ma vihkan seda ja ütlen teile, miks.
Siin on pöördtabel, mis näitab tulusid klientide kaupa. Tulu kokku on 6,7 miljonit dollarit.
Mis siis, kui minu mänedžeril on kuldkala tähelepanu ja ta soovib näha ainult klientide esiviisikut?
Alustamiseks avage rippmenüü A3 ja valige Value Filters, Top 10.
Ülimalt paindlik dialoog Top 10 Filter võimaldab nuppu Ülemine / Alumine. See võib teha 10, 5 või muud numbrit. Viie miljoni dollari saamiseks võite küsida esiletõstetud esemeid, 80% või piisavalt kliente.
Kuid siin on probleem: tulemuseks olev aruanne näitab kõigi klientide kogusummade asemel viit klienti ja nende klientide koguarvu.
Kuid kõigepealt mõned olulised sõnad AutoFilteri kohta
Mõistan, et see tundub seinast väljas oleva küsimusena. Kui soovite tavalises andmekogumis rippmenüüd Filtri sisse lülitada, kuidas seda teha? Siin on kolm tõeliselt levinud viisi:
- Valige oma andmetes üks lahter ja klõpsake vahekaardi Andmed ikooni Filter.
- Valige kõik oma andmed klahvikombinatsiooniga Ctrl + * ja klõpsake vahekaardi Andmed ikooni Filter.
- Andmete tabelina vormindamiseks vajutage klahvikombinatsiooni Ctrl + T.
Need on kolm tõeliselt head viisi. Niikaua kui te mõnda neist tunnete, pole absoluutselt vaja teada muud moodi. Kuid siin on uskumatult ebaselge, kuid maagiline viis filtri sisselülitamiseks:
- Minge oma päiste rea juurde, minge paremasse parempoolsesse lahtrisse. Liigutage üks lahter paremale. Teadmata põhjusel filtreerib Excel selles lahtris olles ja ikoonil Filtri klõpsates vasakul asuva andmekomplekti. Mul pole aimugi, miks see töötab. Sellest ei tasu tõesti rääkida, sest filtri rippmenüüde sisselülitamiseks on juba kolm väga head viisi. Nimetan seda lahtrit maagiarakuks.
Ja nüüd, tagasi pöördtabelite juurde …
Niisiis, on reegel, mis ütleb, et te ei saa automaatfiltreid kasutada pöördtabelis olles. Vaadake allpool? Filtri ikoon on hall, kuna olen valinud liigendtabelisse lahtri.
Ma pole kunagi mõelnud, miks Microsoft seda hallitab. See peab olema midagi sisemist, mille kohaselt ei saa AutoFilter ja liigendtabel koos eksisteerida. Seega on Exceli meeskonnas keegi, kes vastutab filtriikooni hallitamise eest. See inimene pole võlukambrist kunagi kuulnud. Valige pöördtabelis lahter ja filter muutub halliks. Klõpsake pöördtabeli väljaspool ja filter on uuesti lubatud.
Aga oota. Aga maagiline lahter, millest ma teile just rääkisin? Kui klõpsate viimasest pealkirjast paremal asuvas lahtris, unustab Excel halli filtriikooni!
Tõepoolest, Excel lisab automaatfiltri rippmenüüd teie liigendtabeli ülemisele reale. Ja AutoFilter töötab teisiti kui pöördlaua filtrid. Minge rippmenüüsse Tulu ja valige Numbrifiltrid, top 10 …
Valige dialoogis Top 10 automaatfiltrit Top 6 üksust. See pole kirjaviga … Kui soovite viit klienti, valige 6. Kui soovite 10 klienti, valige 11.
Autofiltri jaoks on summaarne rida andmete suurim element. Esimesed viis klienti hõivavad andmetes positsioone 2–6.
Ettevaatust
Selge, et rebite selle nipiga auku Exceli kangas. Kui muudate hiljem alusandmeid ja värskendate pöördtabelit, ei värskenda Excel filtrit, sest Microsoftile teadaolevalt ei ole mingit võimalust pöördtabelisse filtrit rakendada!
Märge
Meie eesmärk on hoida see Microsofti eest saladuses, sest see on päris lahe funktsioon. See on juba mõnda aega "katki" olnud, nii et on palju inimesi, kes võivad praeguseks sellele tugineda.
Täiesti seaduslik lahendus rakenduses Excel 2013+Kui soovite pivot-tabelit, mis näitab teile viit kõige paremat klienti, kuid kõigi klientide koguarvu, peate oma andmed Excelisse teisaldama. Kui teil on Excel 2013 või 2016, on selleks väga mugav viis. Selle näitamiseks kustutasin algse pöördtabeli. Valige Lisa, Pivot-tabel. Enne kui klõpsate nuppu OK, valige ruut, mis ütleb Lisa need andmed andmemudelisse.
Ehitage pöördtabel tavapäraselt. Kasutage A3 rippmenüüd, et valida Väärtusfiltrid, Top 10, ja küsige viie parima kliendi seast. Kui pivot-tabelis on valitud üks lahter, minge lindi vahekaardile Kujundus ja avage rippmenüü Vahesummad. Viimane valik rippmenüüs on Kaasa filtreeritud üksused kokku. Tavaliselt on see valik hall. Kuna aga andmed salvestatakse andmemudelisse tavalise pöördliikmälu asemel, on see valik nüüd saadaval.
Valige suvand Kaasa filtreeritud üksused koguarvu ja teie Kokku sisaldab nüüd tärn ja kõigi andmete koguarv.
See trikk tuli mulle algselt Danilt minu seminaril Philadelphias. Täname Miguel Caballerot selle funktsiooni soovitamise eest.
Vaata videot
- Pöördtabel Top 10 Filter annab nähtavate ridade koguarvu
- Kaasa filtreeritud üksused kokku on hall
- Veider viis andmefiltri kutsumiseks maagiarakust
- Andmefiltrid pole pöördtabelites lubatud
- Excel ei suuda võlurakust andmefiltrit hallitada
- Küsi top 6, et saada top 5 pluss Grand Total
- Kasulik filtreerimiseks konkreetse pöördetaili järgi
- Excel 2013 või uuem: erinev viis tõelise summa saamiseks
- Saatke oma andmed andmemudeli kaudu
- Kaasa filtreeritud üksused kokku on saadaval
- Tärniga saate kokku
- Sain selle triki 10+ aastat tagasi Danilt Philadelphias
Video ärakiri
Õppige Exceli podcastide jaoks, episood 1999 - liigendtabeli tõeline esiviisik
Podcastin kogu seda raamatut. Seal on esitusloend, selle esitusloendi jälgimiseks klõpsake paremas ülanurgas oleval nupul I. Tere tulemast tagasi netisaate juurde. Olen Bill Jelen.
Olgu, nii et me loome Pivot-tabeli ja soovime näidata mitte kõiki kliente, vaid ainult viit klienti. INSERT, pöördtabel. Okei, ma panen kliendi vasakule küljele ja tulud alla. Olgu, siin on kogu meie klientide nimekiri, mis on märgitud 6,7 miljoni dollarini. Exceli abil on esiviisiku tegemine lihtne. Minge reale siltide, väärtusfiltrite 10. kohale. Ei pea olema ülemine. See võib olla ülemine või alumine. Ei pea olema viis. Võib olla kakskümmend, nelikümmend, see võib olla ükskõik mis. Parim kaheksakümmend protsenti, anna mulle piisavalt plaate, et jõuda kolme miljoni dollari või nelja miljoni dollarini, aga siin me läheme. Viis parimat toodet. Pidage nüüd meeles 6,7 miljonit dollarit, klõpsake nuppu OK ja minu suur probleem on see, et see kogusumma ei ole 6,7 miljonit. Kui ma annan selle müügipreemia asepresidendile, saab ta endast välja, öeldes: oodake natuke,Ma tean, et teenisin üle 3,3 miljoni dollari. Õige, nii et me võtame selle tagasi, võtame selle tagasi ja läheme tagasi algandmete juurde.
Nüüd selle järgmise triki, mille õppisin ühel oma Power Exceli seminaril Philadelphias. Kutt nimega Dan teises reas näitas mulle seda. Rohkem kui kümme aastat tagasi näitas ta mulle seda trikki ja kõigepealt peame rääkima Filtritest. Nii et tavaliselt, kui kavatsete kasutada tavalist filtrit, siis seda filtrit, valite oma andmekogumis ükskõik millise lahtri ja klõpsate filtriikooni või mõned inimesed valivad kogu andmekomplekti CONTROL * ja klõpsavad filtriikooni, aga on ka kolmas tee. Viis, mis ei huvita kedagi. Kui lähete kõige viimasesse pealkirja lahtrisse, siis minu puhul on see väärtus L1-s ja liigute üks lahter paremale. Ma nimetan seda võlurakuks, mul pole aimugi, miks, kuid mingil teadmata põhjusel võin sellest lahtrist kõrval asuvat andmekogumit filtreerida. Hea küll, see on nagu imelik viis ja see ei huvita kedagi.
Õige, kuna Filtri käivitamiseks on veel kaks tõeliselt head viisi, ei pea keegi võlurakust teadma, kuid siin on asi, vaadake Pivot-tabeli sisemust, see on hall. Teil pole lubatud neid filtreid kasutada. See on reeglitega vastuolus. Kui ma siia välja tulen, siis olen enam kui teretulnud kasutama filtrit, kuid nad hindavad seda. Ma ei tea, kes see inimene hallitab, kuid nad pole kunagi kuulnud minu väikest juttu võlurakust, sest kui ma lähen kõige viimasesse pealkirjarakku ja lähen ühe lahtriga paremale, vaadake seda, nad unustavad filtri halliks ja nüüd lisasin lihtsalt vanad automaatfiltrid liigendtabelisse. Nii et ma tulen siia ja lähen numbrifiltrite juurde, see erineb väärtusfiltritest. Seda nimetatakse endiselt esikümneks. Veidi teistmoodi, hakkan küsima esiviisikut, mitte esikuuikut.Kuus parimat, kuna selle filtri jaoks on üldsumma vaid üks rida ja kokkuvõte on suurim kirje ning siis, kui küsitakse punkte 2 kuni 6, saan viis parimat.
Olgu, nii me seal oleme. Lahe Filter häkkimine, mis annab meile esiviisiku ja kõigi tegeliku summa. Hästi, paar asja. Ärge unustage võlurakku. Hästi, seda filtrit ei saa kuidagi välja lülitada, kui te ei lähe tagasi võlurakku. Hästi, nii et peate meeles pidama võlurakku. Samuti, kui muudate alusandmeid ja värskendate pöördtabelit, ei kavatse nad filtrit värskendada, kuna Microsofti teada pole teil lubatud filtrit omada.
See on kasulik muude asjade jaoks. Mõnikord on meil tooteid üleval. Läheme siia tabelina. Pole vajalik, mulle lihtsalt meeldib tõelisi pealkirju saada. Gizmo, vidin, vidinad, Doodadid. Olgu, võib-olla olete Doodadsi juht ja peate nägema ainult kliente, kellel oli konkreetne väärtus, ja Doodadsi. Niisiis lähen maagiarakku, lülitan sisse filtri ja seejärel saan Doodadsi alt küsida üksusi, mis on suuremad kui null. Klõpsake nuppu OK. Hea küll, seda tüüpi filtreerimine ei oleks tavalises Pivot-tabelis võimalik, kuid see on võimalik võluraku abil.
Hästi, võtame nüüd loendi tagasi. Lülitame selle filtri välja ja eemaldame liigendtabeli ning kui kasutate Exceli 2013 või uut versiooni, siis näitan teile täiesti seaduslikku viisi, kuidas saada õige kogusumma allosas. Sisestage Pivot-tabel, siin allosas, alates Excel 2013-st see väga ohutu kasti, ei tundu eriti põnev, lisage need andmed andmemudelisse. See saadab andmed kulisside tagant Power Pivot Engine'i. Koostage täpselt sama aruanne. Kliendid vasakul pool. Tulud pöördtabeli südames. Seejärel minge tavaliste filtrite juurde, väärtusfiltrite esikohale 10. Küsige esiviisikut. Pange tähele, et meil on pärast seda teinud 6,7 miljonit dollarit, 3,3 miljonit dollarit, kuid siin on erinevus. Kui lähen vahekaardi Kujundus vahekaardile, on see funktsioon nimega Kaasa filtreeritud üksused kokku,pole enam halliks muutunud. Tavalises Pivot-tabelis pole saadaval. Saame sinna väikese tärni ja see on kõik kokku. Hästi, nüüd töötab see muidugi ainult Excelis 2013 või uuemas.
Hea küll, mul kulub kuus nädalat, enne kui kogu see raamat siin YouTube'is välja tuuakse. Siin on nii palju häid näpunäiteid. Näpunäited, mis võivad teie aega kohe kokku hoida. Osta kohe kogu raamat ja teil on juurdepääs kõigile 40-le. See on tegelikult palju rohkem kui 40 näpunäidet. Exceli kiirklahvid. Igasugust toredat kraami selles raamatus.
Hästi, kokkuvõte. Nii et kui teeme Pivoti tabeli top 10 filtri, annab see meile kogu, kuid ainult nähtavaid ridu, mitte asju, mille see välja filtreeris. Jah, kui läheme teisele vahekaardile ja otsime vahesummasid, filtreeritud üksusi ja kogusummasid, on see hall, kuid on olemas veider viis, kuidas vana andmefiltrit võlurakendusest välja kutsuda. Kõige viimane pealkiri lahter, minge üks lahter paremale, te ei saa kasutada filtreid ja pöördtabeleid, kuid kui te lähete võlurakku, unustavad nad selle halliks. Nüüd numbrifiltris palute esiviisiku saamiseks kuut parimat, millele lisandub kogusumma. Kasulik ka konkreetse Pivoti üksuse filtreerimiseks: Doodad, kõik, mis Doodadis või 5 parimas Doodadis olid suuremad kui 0. Excel 2013 või uuem, on tõelise summa saamiseks erinev viis.Märkige see ruut andmemudeli jaoks ja seejärel lisage filtreeritud üksused kogusummale. Tärniga saate koguarvu. Ja tänu Danile Philadelphias, kes näitas mind ühel mu Power Exceli seminaril, enam kui kümme aastat tagasi, ja andis mulle selle toreda väikese triki. Võimalus filtril hiilida läbi Club Pivoti lauaseina. Tavaliselt ei luba nad seda automaatset filtrit.
Hei, ma tahan tänada teid peatumast. Näeme järgmisel korral veel ühe MRExceli netisaate jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast1999.xlsx
