Pagrieziena tabula Top 10 Filter dod redzamo rindu kopsummu
Pagrieziena tabulas piedāvā Top 10 filtru. Tas ir forši. Tas ir elastīgs. Bet es to ienīstu, un es jums pateikšu, kāpēc.
Šeit ir rakurstabula, kurā parādīti ieņēmumi pēc klienta. Kopējie ieņēmumi ir 6,7 miljoni ASV dolāru.
Ko darīt, ja manam vadītājam ir zelta zivtiņas uzmanība un viņš vēlas redzēt tikai piecus klientus?
Lai sāktu, atveriet nolaižamo izvēlni A3 un atlasiet Value Filters, Top 10.
Īpaši elastīgais dialoglodziņš Top 10 Filter ļauj augšup / apakšā. Tas var izdarīt 10, 5 vai jebkuru citu skaitli. Jūs varat lūgt piecus lielākos priekšmetus, 80 procentus vai pietiekami daudz klientu, lai iegūtu 5 miljonus ASV dolāru.
Bet šeit ir problēma: Rezultātā izveidotajā pārskatā tiek parādīti pieci klienti un šo klientu kopsumma, nevis visu kopsumma.
Bet vispirms daži svarīgi vārdi par automātisko filtrēšanu
Es saprotu, ka tas šķiet jautājums, kas nav pie sienas. Ja vēlaties ieslēgt nolaižamās izvēlnes Filtrs parastajā datu kopā, kā jūs to darāt? Šeit ir trīs patiešām izplatīti veidi:
- Datos atlasiet vienu šūnu un cilnē Dati noklikšķiniet uz ikonas Filtrs.
- Atlasiet visus datus, izmantojot Ctrl + *, un cilnē Dati noklikšķiniet uz ikonas Filtrs.
- Nospiediet Ctrl + T, lai datus formatētu kā tabulu.
Šie ir trīs patiešām labi veidi. Kamēr jūs zināt kādu no viņiem, nav absolūti nepieciešams zināt citu veidu. Bet šeit ir neticami neskaidrs, bet maģisks veids, kā ieslēgt filtru:
- Pārejiet uz savu galvenes rindu, dodieties uz labāko labo virsraksta šūnu. Pārvietojiet vienu šūnu pa labi. Nezināmu iemeslu dēļ, atrodoties šajā šūnā un noklikšķinot uz ikonas Filtrs, programma Excel filtrē kreisajā pusē esošo datu kopu. Man nav ne jausmas, kāpēc tas darbojas. Par to tiešām nav vērts runāt, jo jau ir trīs patiešām labi veidi, kā ieslēgt nolaižamās izvēlnes Filtrs. Es šo šūnu saucu par burvju šūnu.
Tagad atgriezieties pie rakurstabulām …
Tātad ir noteikums, kas saka, ka jūs nevarat izmantot automātiskos filtrus, atrodoties rakurstabulā. Skatīt zemāk? Filtra ikona ir pelēka, jo rakurstabulā esmu atlasījis šūnu.
Es nekad īsti neuzskatīju, kāpēc Microsoft to pelēka. Tam jābūt kaut kam iekšējam, kas saka, ka automātiskā filtrēšana un rakurstabula nevar pastāvēt līdzās. Tātad, Excel komandā ir kāds, kurš ir atbildīgs par ikonas Filtra nokrāsošanu. Šī persona nekad nav dzirdējusi par burvju šūnu. Atlasiet šūnu šarnīra tabulā, un filtrs tiek pelēks. Noklikšķiniet ārpus rakurstabulas, un filtrs atkal tiek iespējots.
Bet pagaidi. Kā ar burvju šūnu, par kuru tikko pastāstīju? Ja noklikšķināt šūnā pa labi no pēdējās virsraksta, programma Excel aizmirst pelēkot ikonu Filtrs!
Protams, Excel pievieno automātiskās filtrēšanas nolaižamās izvēlnes jūsu rakurstabulas augšējai rindai. AutoFilter darbojas savādāk nekā pagrieziena tabulas filtri. Pārejiet uz nolaižamo izvēlni Ieņēmumi un izvēlieties Skaitļu filtri, Top 10 …
Top 10 automātiskās filtrēšanas dialoglodziņā izvēlieties Top 6 vienumus. Tā nav drukas kļūda … Ja vēlaties piecus klientus, izvēlieties 6. Ja vēlaties 10 klientus, izvēlieties 11.
Automātiskajam filtram kopējā kopējā rinda ir lielākais datu vienums. Pieci galvenie klienti datos aizņem no 2. līdz 6. pozīcijai.
Uzmanību
Skaidrs, ka ar šo triku jūs plēšat caurumu Excel audumā. Ja vēlāk mainīsit pamatā esošos datus un atsvaidzināsiet rakurstabulu, programma Excel filtru neatsvaidzinās, jo, cik Microsoft zina, nav iespējas filtru piemērot rakurstabulai!
Piezīme
Mūsu mērķis ir paturēt šo Microsoft noslēpumā, jo tā ir diezgan lieliska funkcija. Tas ir "salauzts" jau ilgu laiku, tāpēc ir daudz cilvēku, kuri, iespējams, jau paļaujas uz to.
Pilnīgi likumīgs risinājums programmā Excel 2013+Ja vēlaties rakurstabulu, kurā parādīti pieci galvenie klienti, bet visu klientu kopskaits, dati jāpārvieto ārpus programmas Excel. Ja jums ir programma Excel 2013 vai 2016, ir ļoti ērts veids, kā to izdarīt. Lai to parādītu, es izdzēsu sākotnējo rakurstabulu. Izvēlieties Ievietot, rakurstabulu. Pirms noklikšķināt uz Labi, atzīmējiet izvēles rūtiņu Pievienot šos datus datu modelim.
Veidojiet rakurstabulu kā parasti. Izmantojiet nolaižamo izvēlni A3, lai atlasītu Value Filters, Top 10, un lūdziet piecus klientus. Kad ir atlasīta viena šūna rakurstabulā, lentē atveriet cilni Noformējums un atveriet nolaižamo izvēlni Starpsummas. Galīgā izvēle nolaižamajā izvēlnē ir Iekļaut filtrētos vienumus kopsummā. Parasti šī izvēle ir pelēka. Bet, tā kā dati tiek glabāti datu modelī, nevis parastā šarnīra kešatmiņā, šī opcija tagad ir pieejama.
Izvēlieties opciju Iekļaut filtrētos vienumus kopā, un jūsu kopsummā tagad ir zvaigznīte un visu datu kopsumma.
Šis triks man sākotnēji nāca no Dena manā seminārā Filadelfijā. Paldies Migelam Caballero par šīs funkcijas ieteikšanu.
Skatīties video
- Pagrieziena tabula Top 10 Filter dod redzamo rindu kopsummu
- Iekļaut filtrētos vienumus kopsummā ir pelēks
- Nepāra veids, kā izsaukt datu filtru no burvju šūnas
- Datu filtri nav atļauti rakurstabulās
- Programma Excel nespēj pelēkot datu filtru no burvju šūnas
- Lūdziet top 6, lai iegūtu top 5 plus Grand Total
- Noderīgi filtrēšanai pēc konkrēta šarnīra vienuma
- Excel 2013 vai jaunāka versija: atšķirīgs veids, kā iegūt patieso kopsummu
- Nosūtiet savus datus, izmantojot datu modeli
- Iekļaut filtrētos priekšmetus kopsummā būs pieejams
- Iegūstiet kopā ar zvaigznīti
- Šo triku es iemācījos pirms vairāk nekā 10 gadiem no Dena Filadelfijā
Video atšifrējums
Apgūstiet programmu Excel Podcast, 1999. sērija - rakurstabila patiesais piecinieks
Es aprašu visu šo grāmatu. Ir atskaņošanas saraksts, augšējā labajā stūrī noklikšķiniet uz I, lai sekotu šim atskaņošanas sarakstam. Laipni lūdzam atpakaļ netcast. Es esmu Bils Jelens.
Labi, tāpēc mēs izveidosim rakurstabulu un vēlamies parādīt ne visus klientus, bet tikai piecus klientus. INSERT, rakurstabula. Labi, es noliekšu klientu kreisajā pusē un ieņēmumus. Labi, tāpēc šeit ir viss mūsu klientu saraksts, kas atzīmēts kā 6,7 miljoni dolāru. Excel ļauj viegli izdarīt piecus labākos. Iet uz Row Labels, Value Filters, top 10. Tam nav jābūt augšējam. Tas var būt augšējais vai apakšējais. Nav jābūt pieciem. Tas var būt divdesmit, četrdesmit, tas var būt jebkurš. Labākais astoņdesmit procents, dodiet man pietiekami daudz ierakstu, lai tiktu pie trim miljoniem dolāru vai četriem miljoniem dolāru, bet šeit mēs ejam. Pieci labākie priekšmeti. Tagad atcerieties 6,7 miljonus dolāru, noklikšķiniet uz Labi, un mana lielā problēma šeit ir tā, ka šī kopsumma nav 6,7 miljoni. Kad es to nodošu pārdošanas viceprezidentam, viņš izbīsies, sakot, pagaidiet sekundi,Es zinu, ka es nopelnīju vairāk nekā 3,3 miljonus dolāru. Pareizi, tāpēc mēs to atsauksim, atsauksim un atgriezīsimies pie sākotnējiem datiem.
Tagad šis nākamais triks, ko uzzināju vienā no saviem Power Excel semināriem Filadelfijā. Puisis vārdā Dens otrajā rindā man to parādīja. Pirms vairāk nekā desmit gadiem viņš man parādīja šo triku, un vispirms mums ir jārunā par Filtriem. Tāpēc parasti, ja izmantojat parasto filtru, šo filtru šeit, jūs izvēlaties jebkuru šūnu datu kopā un noklikšķiniet uz filtra ikonas, vai arī daži cilvēki izvēlas visu datu kopu, CONTROL * un noklikšķiniet uz filtra ikonas, bet tur ir trešais ceļš. Veids, kas nevienam nerūp. Ja dodaties uz pašu pēdējo Virsrakstu šūnu, manā gadījumā tas ir L1 izmaksas un dodieties pa labi vienu šūnu. Es to saucu par burvju šūnu, man nav ne jausmas, kāpēc, bet nezināmu iemeslu dēļ no šīs šūnas es varu filtrēt blakus esošo datu kopu. Labi, tas ir kā dīvains veids, un tas nevienu neinteresē.
Pareizi, jo ir vēl divi patiešām labi veidi, kā izsaukt filtru, par burvju šūnu nav jāzina nevienam, taču šeit ir redzams rakurss, skatiet rakurstabulas iekšpusi. Jums nav atļauts izmantot šos filtrus. Tas ir pretrunā ar noteikumiem. Tagad, ja es iznācu šeit, es esmu laipni gaidīts izmantot filtru, bet iekšpusē viņi to novērtē. Es nezinu, kas ir tas cilvēks, kurš to pelē, bet viņi nekad nav dzirdējuši manas mazās runas par burvju šūnu, jo, ja es eju uz pašu pēdējo Virsrakstu šūnu un dodos vienu šūnu pa labi, paskatieties uz to, viņi aizmirst pelēko filtru, un tagad es tikko pievienoju vecos automātiskos filtrus rakurstabulai. Tāpēc es nāku šeit, dodos uz Skaitļu filtriem, kas atšķiras no Vērtību filtriem. To joprojām sauc par Top Ten. Nedaudz savādāk, es prasīšu labāko piecinieku, nevis sešus labākos.Sešas labākās, jo šim filtram kopsumma ir tikai vēl viena rinda, un kopsumma ir vislielākā prece, un tad, kad man tiek prasīts no 2. līdz 6. postenim, es saņemu piecus labākos vienumus.
Labi, tātad, mēs esam. Foršs filtru uzlaušana, kas dod mums piecus labākos priekšmetus un patieso visu cilvēku kopsummu. Labi tagad, pāris lietas. Neaizmirstiet par burvju šūnu. Labi, šo filtru nevar izslēgt, ja vien neatgriežaties burvju šūnā. Labi, tāpēc jums jāatceras burvju šūna. Turklāt, ja maināt pamatā esošos datus un atsvaidzināt rakurstabulu, viņi neatjaunos filtru, jo, cik Microsoft zina, jums nav atļauts izmantot filtru.
Tas ir noderīgi citām lietām. Dažreiz mums ir produkti, kas iet pāri augšai. Dodamies šeit tabulas formā. Nav nepieciešams, man vienkārši patīk iegūt reālus virsrakstus. Gizmo, logrīks, sīkrīki, Doodads. Labi, un varbūt jūs esat Doodads vadītājs, un jums ir jāredz tikai klienti, kuriem bija īpaša vērtība, un Doodads. Tāpēc es eju uz burvju šūnu, ieslēdzu filtru un pēc tam sadaļā Doodads es varu lūgt priekšmetus, kas ir lielāki par nulli. Noklikšķiniet uz Labi. Labi, šāda veida filtrēšana nebūtu iespējama parastajā rakurstabulā, taču tas ir iespējams, izmantojot burvju šūnu.
Labi, tagad atsauksim sarakstu. Izslēgsim šo filtru un noņemsim rakurstabulu, un, ja izmantojat programmu Excel 2013 vai jaunu, es jums parādīšu pilnīgi likumīgu veidu, kā apakšā iegūt pareizo kopsummu. Ievietojiet rakurstabulu šeit apakšā, sākot ar programmu Excel 2013, šī ļoti nekaitīgā lodziņa neizklausās ļoti aizraujoši, pievienojiet šos datus datu modelim. Tas nosūta datus aiz ainas uz Power Pivot Engine. Veidojiet tieši to pašu pārskatu. Klienti pa kreiso pusi. Ieņēmumi rakurstabulas centrā. Pēc tam dodieties uz parastajiem filtriem, Vērtību filtri top 10. Jautājiet piecus labākos. Atkal pamaniet, ka mums ir 6,7 miljoni dolāru pēc tam, kad es to izdarīju, 3,3 miljoni dolāru, bet šeit ir atšķirība. Kad dodos uz cilni Noformējums, sadaļā Starpsummas šī funkcija ar nosaukumu Iekļaut filtrētos vienumus kopsummā,vairs nav pelēks. Parastā pagrieziena tabula nav pieejama. Mēs tur iegūstam nelielu zvaigznīti, un tas ir viss. Labi, tagad, protams, tas darbojas tikai programmā Excel 2013 vai jaunākā versijā.
Labi, paies sešas nedēļas, līdz es visu šo grāmatu izlaižu šeit YouTube. Šeit ir tik daudz labu padomu. Padomi, kas varētu sākt ietaupīt jūsu laiku uzreiz. Pērciet visu grāmatu tūlīt, un jums būs pieeja visiem 40, patiesībā tas ir daudz vairāk nekā 40 padomi. Excel īsinājumtaustiņi. Visu veidu lieliski sīkumi šajā grāmatā.
Labi, atkārtojiet. Tātad, kad mēs veicam Pivot Table top 10 filtru, tas dod mums kopējo, bet tikai redzamās rindas, nevis tās lietas, kuras tā filtrēja. Jā, ja mēs ejam uz otro cilni un meklējam Starpsummas, Filtrētie vienumi un Kopā, tas ir pelēks, taču ir nepāra veids, kā izsaukt veco datu filtru no burvju šūnas. Pati pēdējā virziena šūna, dodieties uz vienu šūnu pa labi, jūs nevarat izmantot filtrus un pagrieziena tabulas, bet, ja jūs dodaties uz burvju šūnu, viņi aizmirst to pelēkot. Tagad Skaitļu filtrā jūs lūdzat sešus labākos, lai iegūtu piecus labākos, kā arī kopējo summu. Noderīga arī filtrēšanai uz konkrētu rakursteni: Doodads, jebkas, kas Doodads vai 5 top Doodads bija lielāks par 0. Excel 2013 vai jaunāka versija, ir cits veids, kā iegūt patieso kopsummu.Atzīmējiet izvēles rūtiņu datu modelim un pēc tam iekļaujiet Filtrētie vienumi kopsummā. Jūs saņemat kopējo summu ar zvaigznīti. Un paldies Danam Filadelfijā, kurš pirms vairāk nekā desmit gadiem parādīja man vienā no maniem Power Excel semināriem un deva man šo lielisko mazo triku. Veids, kā filtrs var ielīst caur Club Pivot galda sienu. Viņi parasti neatļauj šo automātisko filtru.
Hei, es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai saņemtu vēl vienu neto apraidi no MRExcel.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast1999.xlsx
