„Pivot“ lentelėje „Top 10 Filter“ pateikiama matomų eilučių suma
„Pivot“ lentelėse yra „Top 10“ filtras. Tai yra puiku. Jis yra lankstus. Bet aš to nekenčiu ir pasakysiu, kodėl.
Čia yra suvestinė lentelė, rodanti pajamas pagal klientą. Bendros pajamos yra 6,7 mln. USD.
Ką daryti, jei mano vadybininkas atkreipia dėmesį į auksinę žuvelę ir nori pamatyti tik penkis geriausius klientus?
Norėdami pradėti, atidarykite išskleidžiamąjį meniu A3 ir pasirinkite Vertės filtrai, 10 geriausių.
Itin lankstus „Top 10 Filter“ dialogo langas leidžia „Top / Bottom“. Tai gali padaryti 10, 5 ar bet kurį kitą numerį. Galite paprašyti penkių geriausių daiktų, 80 proc. Didžiausių klientų arba pakankamai klientų, kad gautumėte 5 mln. USD.
Tačiau čia yra problema: gauta ataskaita rodo penkis klientus ir jų skaičių iš visų klientų, o ne iš visų.
Bet pirmiausia, keli svarbūs žodžiai apie „AutoFilter“
Suprantu, kad tai atrodo ne sienos klausimas. Jei norite įjungti įprasto duomenų rinkinio išskleidžiamuosius meniu „Filtras“, kaip tai padaryti? Štai trys iš tikrųjų įprasti būdai:
- Pasirinkite vieną langelį savo duomenyse ir skirtuke Duomenys spustelėkite piktogramą Filtras.
- Pasirinkite visus duomenis naudodami Ctrl + * ir skirtuke Duomenys spustelėkite piktogramą Filtras.
- Norėdami formatuoti duomenis kaip lentelę, paspauskite „Ctrl“ + T.
Tai yra trys tikrai geri būdai. Kol pažįstate kurį nors iš jų, visiškai nereikia žinoti kito būdo. Bet čia yra nepaprastai neaiškus, bet stebuklingas būdas įjungti filtrą:
- Eikite į savo antraščių eilutę, eikite į dešiniąją antraštės langelį. Perkelkite vieną langelį į dešinę. Dėl nežinomos priežasties, kai esate šioje langelyje ir spustelėkite piktogramą Filtras, „Excel“ filtruoja kairėje esančius duomenų rinkinius. Neįsivaizduoju, kodėl tai veikia. Apie tai neverta kalbėti, nes jau yra trys tikrai geri būdai, kaip įjungti išskleidžiamuosius filtrus. Aš vadinu šią langelį magija.
Ir dabar, grįžkite į suvestines lenteles …
Taigi yra taisyklė, sakanti, kad negalite naudoti automatinių filtrų, kai esate sukimosi lentelėje. Žiūrėti žemiau? Filtro piktograma yra pilka, nes aš pasirinkau langelį suvestinėje lentelėje.
Aš niekada nesvarstiau, kodėl „Microsoft“ tai pilkina. Tai turi būti kažkas, kas sako, kad „AutoFilter“ ir „Pivot“ lentelė negali egzistuoti kartu. Taigi, „Excel“ komandoje yra kažkas, kuris atsakingas už pilkos spalvos filtro piktogramą. Tas žmogus dar nėra girdėjęs apie „Magic cell“. Pasirinkite langelį suvestinėje lentelėje, o filtras bus pilkas. Spustelėkite ne suvestinės lentelės ribas, o filtras vėl įgalinamas.
Bet palauk. O magijos ląstelė, apie kurią ką tik pasakojau? Jei spustelėsite langelį paskutinės antraštės dešinėje, „Excel“ pamiršta pilką filtro piktogramą!
Tikrai, „Excel“ prideda automatinio filtravimo išskleidžiamuosius meniu prie viršutinės jūsų suvestinės lentelės eilutės. „AutoFilter“ veikia kitaip nei sukamųjų lentelių filtrai. Eikite į išskleidžiamąjį meniu Pajamos ir pasirinkite Skaičių filtrai, 10 geriausių …
„Top 10 AutoFilter“ dialogo lange pasirinkite „Top 6“ elementus. Tai ne klaida … Jei norite penkių klientų, pasirinkite 6. Jei norite 10 klientų, pasirinkite 11.
„AutoFilter“ bendroji eilutė yra didžiausias elementas duomenyse. Penki geriausi klientai užima 2–6 pozicijas duomenyse.
Atsargiai
Aišku, jūs su šiuo triuku suplėšote skylę „Excel“ audinyje. Jei vėliau pakeisite pagrindinius duomenis ir atnaujinsite savo suvestinę lentelę, „Excel“ neatnaujins filtro, nes, kiek žino „Microsoft“, nėra jokio būdo pritaikyti filtrą suvestinei lentelei!
Pastaba
Mūsų tikslas yra išlaikyti tai „Microsoft“ paslaptyje, nes tai yra gana puiki savybė. Jis buvo „sugedęs“ gana ilgą laiką, todėl yra daug žmonių, kurie šiuo metu gali tuo pasikliauti.
Visiškai teisėtas sprendimas „Excel 2013+“Jei norite, kad suvestinė lentelė parodytų penkis geriausius klientus, bet visų klientų skaičių, turite perkelti savo duomenis už „Excel“ ribų. Jei turite „Excel 2013“ ar 2016 m., Tai padaryti yra labai patogus būdas. Norėdami tai parodyti, ištryniau pradinę suvestinę lentelę. Pasirinkite Įterpti, „Pivot“ lentelę. Prieš spustelėdami Gerai, pažymėkite langelį „Pridėti šiuos duomenis prie duomenų modelio“.
Sukurkite sukamą lentelę kaip įprasta. Naudodami išskleidžiamąjį A3 langelį pasirinkite „Vertės filtrai“, „Top 10“ ir paprašykite penkių geriausių klientų. Pasirinkę vieną langelį suvestinėje lentelėje, eikite į juostos skirtuką Dizainas ir atidarykite išskleidžiamąjį meniu Tarpinės sumos. Galutinis išskleidžiamojo meniu pasirinkimas yra Įtraukti filtruotus elementus į visas sumas. Paprastai šis pasirinkimas yra pilkas. Kadangi duomenys saugomi duomenų modelyje, o ne įprastoje pasukamojoje talpykloje, ši parinktis dabar yra prieinama.
Pasirinkite parinktį Įtraukti filtruotus elementus į bendrą sumą, o bendrojoje sumoje dabar yra žvaigždutė ir visų duomenų bendra suma.
Ši gudrybė iš pradžių atkeliavo iš Dano per mano seminarą Filadelfijoje. Ačiū Migueliui Caballero, kuris pasiūlė šią funkciją.
Žiūrėti video
- „Pivot“ lentelėje „Top 10 Filter“ pateikiama matomų eilučių suma
- Įtraukti filtruotus elementus į sumas yra pilkos spalvos
- Keistas būdas iškviesti duomenų filtrą iš stebuklingos langelio
- Duomenų filtrai neleidžiami suvestinėse lentelėse
- „Excel“ nepavyksta pilkos spalvos duomenų filtro iš magijos langelio
- Paprašykite 6 geriausių, kad gautumėte 5 geriausius ir „Total Total“
- Naudinga filtruojant pagal konkretų suvestinį elementą
- „Excel 2013“ arba naujesnė versija: skirtingas būdas gauti tikrąją sumą
- Siųskite savo duomenis naudodamiesi duomenų modeliu
- Įtraukti filtruotus elementus iš viso bus galima
- Gaukite žvaigždutę su „Total“
- Šį triuką išmokau prieš 10 ir daugiau metų iš Dano Filadelfijoje
Vaizdo įrašo nuorašas
Sužinokite apie „Podcast“ skirtą „Excel“ programą, 1999 m. Serija - „Pivot Table“ tikras penketas
Tinklalaiduoju visą šią knygą. Yra grojaraštis, spustelėkite I viršutiniame dešiniajame kampe, kad galėtumėte sekti grojaraštį. Sveiki sugrįžę į internetinę transliaciją. Aš esu Billas Jelenas.
Gerai, todėl sukursime „Pivot“ lentelę ir norime parodyti ne visus klientus, o tik penkis geriausius klientus. INSERT, „Pivot“ lentelė. Gerai, aš padėsiu Klientą kairėje pusėje ir „Pajamos“. Gerai, todėl čia yra visas mūsų klientų sąrašas, pažymėtas kaip 6,7 milijono dolerių. „Excel“ leidžia lengvai atlikti penkis geriausius. Eikite į eilutės etiketes, vertės filtrus, 10 viršų. Nereikia būti viršuje. Tai gali būti viršuje arba apačioje. Nebūtinai turi būti penkeri. Tai gali būti dvidešimt, keturiasdešimt, gali būti bet kas. Į viršų aštuoniasdešimt procentų, duok man pakankamai įrašų, kad pasiekčiau tris milijonus dolerių arba keturis milijonus dolerių, bet štai, einame. Penkios geriausios prekės. Dabar nepamirškite 6,7 milijono dolerių, spustelėkite Gerai ir mano didžiausia problema čia yra ta, kad visa bendra suma nėra 6,7 mln. Kai duosiu tai pardavimų viceprezidentui, jis išsigąs sakydamas: palaukite sekundę,Žinau, kad uždirbau daugiau nei 3,3 milijono dolerių. Teisingai, todėl mes anuliuosime, anuliuosime tai ir grįšime prie pradinių duomenų.
Dabar šį kitą triuką sužinojau per vieną savo „Power Excel“ seminarą Filadelfijoje. Vaikinas vardu Danas antroje eilėje man tai parodė. Tai buvo daugiau nei prieš dešimt metų, kai jis man parodė šį triuką, ir pirmiausia turime kalbėti apie filtrus. Taigi paprastai, jei ketinate naudoti įprastą filtrą, šį filtrą čia, pasirenkate bet kurį langelį savo duomenų rinkinyje ir spustelėkite filtro piktogramą, arba kai kurie žmonės pasirenka visą duomenų rinkinį, CONTROL * ir spustelėkite filtro piktogramą, bet yra ir trečias kelias. Niekam neįdomus būdas. Jei einate į paskutinę antraštės langelį, mano atveju, tai yra kaina L1, ir eikite į vieną langelį į dešinę. Aš tai vadinu stebuklinga ląstele, neįsivaizduoju, kodėl, bet dėl nežinomų priežasčių iš šios langelio galiu filtruoti gretimą duomenų rinkinį. Gerai, tai tarsi keistas būdas ir niekam tai nerūpi.
Teisingai, nes yra dar du tikrai geri būdai, kaip pasinaudoti filtru, niekam nereikia žinoti apie stebuklingą langelį, bet štai kas, žiūrėkite „Pivot“ lentelės viduje, ji yra pilka. Jums neleidžiama naudoti tų filtrų. Tai prieštarauja taisyklėms. Dabar, jei išeisiu čia, aš daugiau nei malonu naudoti filtrą, bet viduje jie įvertinami. Aš nežinau, kas yra tas žmogus, kuris tai pilkina, bet jie niekada negirdėjo mano mažų kalbų apie stebuklingą ląstelę, nes jei aš einu į paskutinę „Heading Cell“ ir einu viena ląstele į dešinę, pažvelk į tai, jie pamiršta pilką filtrą, o dabar aš tiesiog įtraukiau senus automatinius filtrus į „Pivot“ lentelę. Taigi aš ateinu čia, einu į Skaičių filtrus, tai skiriasi nuo vertės filtrų. Jis vis dar vadinamas „Top Ten“. Šiek tiek kitoks, aš paprašysiu penketo, o ne šešių.Šeši geriausi, nes šiam filtrui bendrasis yra tik dar viena eilutė, o bendrasis yra didžiausias elementas, o tada, kai manęs paprašys 2–6 elementų, gausiu penkis geriausius elementus.
Gerai, taigi mes čia. Kietas filtro nulaužimas, kuris suteikia mums penkis geriausius daiktus ir tikrąją visų sumą. Gerai dabar, pora dalykų. Nepamirškite apie stebuklingą langelį. Gerai, negalima išjungti šio filtro, nebent grįšite į stebuklingą langelį. Gerai, todėl reikia prisiminti stebuklingą langelį. Be to, jei pakeisite pagrindinius duomenis ir atnaujinsite „Pivot“ lentelę, jie neatnaujins filtro, nes, kiek žino „Microsoft“, jums neleidžiama turėti filtro.
Tai naudinga kitiems dalykams. Kartais mes turime produktų, einančių per viršų. Eikime čia lentelės forma. Nebūtina, man tiesiog patinka gauti tikras antraštes. „Gizmo“, valdiklis, programėlės, „Doodads“. Gerai, galbūt jūs esate „Doodads“ valdytojas ir jums reikia pamatyti tik tuos klientus, kurie turėjo tam tikrą vertę, ir „Doodads“. Taigi einu į stebuklingą langelį, įjungiu filtrą ir tada skiltyje „Doodads“ galiu paprašyti didesnių nei nulis elementų. Spustelėkite Gerai. Gerai, tokio tipo filtravimo negalima naudoti įprastoje „Pivot“ lentelėje, tačiau tai įmanoma naudojant stebuklingą langelį.
Gerai, dabar panaikinkime sąrašą. Išjunkime šį filtrą ir pašalinsime „Pivot“ lentelę. Jei esate „Excel 2013“ ar naujoje versijoje, aš jums parodysiu visiškai teisėtą būdą, kaip apačioje gauti teisingą sumą. Įterpkite „Pivot“ lentelę, apačioje, čia, apačioje, pradedant „Excel 2013“, šis labai nekenksmingas laukelis neskamba labai įdomiai, pridėkite šiuos duomenis prie duomenų modelio. Tai siunčia duomenis užkulisiuose į „Power Pivot“ variklį. Sukurkite tą pačią ataskaitą. Klientai kairėje pusėje. Pajamos „Pivot“ lentelės centre. Tada eikite į įprastus filtrus, „Value Filters“ 10 geriausių. Paprašykite penkių geriausių. Dar kartą atkreipkite dėmesį, kad po to aš turiu 6,7 milijono dolerių, 3,3 milijono dolerių, bet čia yra skirtumas. Kai einu į skirtuką Dizainas, dalyje Tarpinės sumos, ši funkcija pavadinta Įtraukti filtruotus elementus į sumas,nebėra pilka. Įprastoje „Pivot“ lentelėje nėra. Mes ten gauname šiek tiek žvaigždutės ir tai viskas. Gerai, dabar, žinoma, tai veikia tik „Excel 2013“ ar naujesnėse versijose.
Gerai, užtruks šešias savaites, kol visą šią knygą pateiksiu čia, „YouTube“. Čia tiek daug gerų patarimų. Patarimai, kurie gali padėti jums sutaupyti laiko iš karto. Pirkite visą knygą dabar ir turėsite prieigą prie visų 40, iš tikrųjų tai yra daug daugiau nei 40 patarimų. „Excel“ spartieji klavišai. Visų rūšių puikių dalykų šioje knygoje.
Gerai, pakartok. Taigi, kai mes atliekame „Pivot Table 10“ filtrą, mums pateikiamos visos, bet tik matomos eilutės, o ne ta informacija, kurią jis išfiltravo. Taip, jei einame į antrą skirtuką ir ieškome tarpinių sumų, filtruotų elementų ir sumų, jis yra pilkas, tačiau yra keistas būdas iškviesti senąjį duomenų filtrą iš magijos langelio. Paskutinė antraštės ląstelė, eikite viena langeliu į dešinę, negalėsite naudoti filtrų ir pasukamųjų lentelių, bet jei einate į stebuklingą langelį, jie pamiršta ją išpilkinti. Dabar „Skaičių filtre“ prašote šešių geriausių, kad patektumėte į penketuką ir bendrą sumą. Taip pat naudinga filtruojant pagal konkretų „Pivot“ elementą: „Doodads“, viskas, kas buvo daugiau nei 0 „Doodads“ ar 5 geriausiose „Doodads“. „Excel 2013“ ar naujesnės versijos, yra kitas būdas gauti tikrąją sumą.Pažymėkite šį duomenų langelio laukelį, tada įtraukite Filtruotus elementus į sumą. Visą gausite žvaigždute. Ačiū Danui Filadelfijoje, kuris parodė man daugiau nei prieš dešimt metų viename iš „Power Excel“ seminarų ir padovanojo man šį puikų triuką. Būdas filtrui prasmukti pro „Club Pivot“ stalo sieną. Paprastai jie neleidžia to automatinio filtro.
Ei, noriu padėkoti, kad užsukote. Susitiksime kitą kartą, norėdami gauti dar vieną „MRExcel“ transliaciją.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast1999.xlsx
