„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. Atkreipkite dėmesį, kad didžiausias klientas „Roto-Rooter“ yra 9% visų pajamų.
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ų. Roto-Rooteris, kuris anksčiau buvo 9% visų, yra 23% visų naujųjų.
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 spustelėkite filtro piktogramą skirtuke Duomenys arba paspauskite Ctrl + Shift + L.
- 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 į antraščių eilutę ir eikite į dešiniausią dešiniosios 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š šią ląstelę vadinu stebuklinga ląstele.
Dabar grįžkime prie sukamų lentelių
Yra taisyklė, sakanti, kad negalite naudoti automatinio filtro, kai esate sukimo lentelėje. Žiūrėti žemiau? Filtro piktograma yra pilka, nes aš pasirinkau langelį suvestinėje lentelėje.
Nežinau, kodėl „Microsoft“ tai pilka. 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 niekada nėra girdėjęs apie stebuklingą ląstelę. Pasirinkite langelį suvestinėje lentelėje, o filtras bus pilkas. Spustelėkite už suvestinės lentelės ir filtras vėl įgalinamas.
Bet palauk. O magiškoji 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 „pivot table“ filtras. Eikite į išskleidžiamąjį meniu Pajamos ir pasirinkite Skaičių filtrai, 10 geriausių ….
„Top 10 AutoFilter“ dialogo lange pasirinkite „Top 6“ elementus. Tai nėra klaida … jei norite penkių klientų, rinkitės 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 sistemoje „Windows“ veikia „Excel 2013“ ar naujesnė versija, 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 žymės 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 į visas sumas, o jūsų bendrojoje sumoje dabar yra žvaigždutė ir visų duomenų bendra suma, kaip parodyta žemiau.
Šį stebuklingų ląstelių triuką aš iš pradžių gavau iš Dano per mano seminarą Filadelfijoje, o po 15 metų jį pakartojo kitas Danas iš mano seminaro Sinsinatis. Ačiū Migueliui Caballero, kuris pasiūlė šią funkciją.
