Pivot-taulukot tarjoavat Top 10 -suodattimen. Se on siistiä. Se on joustava. Mutta vihaan sitä ja kerron sinulle miksi.
Tässä on pivot-taulukko, joka näyttää tulot asiakkaittain. Tulojen kokonaismäärä on 6,7 miljoonaa dollaria. Huomaa, että suurin asiakas, Roto-Rooter, on 9% kokonaistuloista.
Entä jos esimiehelläni on kultakalan tarkkaavaisuus ja hän haluaa nähdä vain viisi parasta asiakasta? Aloita avaamalla avattava valikko A3: ssa ja valitsemalla Arvosuodattimet, Top 10.
Erittäin joustava Top 10 Filter -valintaikkuna sallii Top / Bottom -vaihtoehdon. Se voi tehdä 10, 5 tai minkä tahansa muun numeron. Voit pyytää viisi parasta tuotetta, 80% parhaiten tai tarpeeksi asiakkaita päästäksesi 5 miljoonaan dollariin.
Mutta tässä on ongelma: Tuloksena olevassa raportissa näkyy viisi asiakasta ja näiden asiakkaiden kokonaismäärä kaikkien kaikkien summien sijaan. Roto-Rooter, joka oli aiemmin 9% kokonaismäärästä, on 23% uudesta kokonaismäärästä.
Mutta ensin muutama tärkeä sana AutoFilteristä
Ymmärrän, että tämä tuntuu seinän ulkopuolelta. Jos haluat ottaa käyttöön suodattimen avattavat valinnat tavallisessa tietojoukossa, miten se tehdään? Tässä on kolme todella yleistä tapaa:
- Valitse yksi solu tiedoistasi ja napsauta Tiedot-välilehden Suodatin-kuvaketta tai paina Ctrl + Vaihto + L.
- Valitse kaikki tietosi painamalla Ctrl + * ja napsauta Tiedot-välilehden Suodatin-kuvaketta.
- Alusta tiedot taulukkona painamalla Ctrl + T.
Nämä ovat kolme todella hyvää tapaa. Niin kauan kuin tunnet jonkun heistä, ei ole mitään tarvetta tietää toista tapaa. Mutta tässä on uskomattoman hämärä, mutta maaginen tapa kytkeä suodatin päälle:
- Siirry otsikkorivillesi ja siirry sitten oikeanpuoleiseen otsikkosoluun. Siirrä yksi solu oikealle. Jos olet tuntemattomasta syystä, kun olet tässä solussa ja napsautat Suodatin-kuvaketta, Excel suodattaa vasemmalla olevan tietojoukon. Minulla ei ole aavistustakaan, miksi tämä toimii. Siitä ei todellakaan ole syytä puhua, koska suodattimen avattavat valinnat voidaan ottaa käyttöön jo kolmella tavalla. Kutsun tätä solua taikasoluksi.
Ja nyt, palaa pivot-taulukoihin
On sääntö, jonka mukaan et voi käyttää automaattista suodatinta, kun olet pivot-taulukossa. Katso alempaa? Suodatin-kuvake on harmaa, koska olen valinnut solun pivot-taulukossa.
En tiedä miksi Microsoft hämää tämän. Sen on oltava jotain sisäistä, joka sanoo, että AutoFilter ja pivot-taulukko eivät voi olla rinnakkain. Joten Excel-tiimissä on joku, joka vastaa suodatinkuvakkeen harmahtamisesta. Tuo henkilö ei ole koskaan kuullut taikasolusta. Valitse solu pivot-taulukossa ja suodatin näkyy harmaana. Napsauta pivot-taulukon ulkopuolella ja suodatin otetaan uudelleen käyttöön.
Mutta odota. Entä taikasolu, josta juuri kerroin sinulle? Jos napsautat viimeisen otsikon oikealla puolella olevaa solua, Excel unohtaa harmaata Suodatin-kuvakkeen!
Tosiaan, Excel lisää Automaattisuodatin-pudotusvalinnat pivot-taulukon ylimmälle riville. Ja AutoFilter toimii eri tavalla kuin kääntötaulukon suodatin. Siirry avattavaan Tulot-kohtaan ja valitse Numerosuodattimet, Top 10….
Valitse Top 10 AutoFilter -valintaikkunassa Top 6 kohdetta. Se ei ole kirjoitusvirhe … jos haluat viisi asiakasta, valitse 6. Jos haluat 10 asiakasta, valitse 11.
Automaattisuodattimelle loppusumma on tietojen suurin kohde. Viisi suurinta asiakasta ovat tietojen sijasta 2-6.
Varoitus
On selvää, että repit reiän Excelin kankaassa tällä temppulla. Jos muutat myöhemmin taustalla olevia tietoja ja päivität pivot-taulukon, Excel ei päivitä suodatinta, koska Microsoftin mukaan ei ole mitään tapaa käyttää suodatinta pivot-taulukkoon!
Huomautus
Tavoitteenamme on pitää tämä salassa Microsoftilta, koska se on melko siisti ominaisuus. Se on ollut ”rikki” jo jonkin aikaa, joten monet ihmiset saattavat luottaa siihen jo nyt.
Täysin laillinen ratkaisu Excel 2013+: ssa
Jos haluat pivot-taulukon, joka näyttää viisi parasta asiakasta, mutta kaikkien asiakkaiden kokonaismäärän, sinun on siirrettävä tietosi Excelin ulkopuolelle. Jos sinulla on Excel 2013 tai uudempi Windowsissa, tähän on erittäin kätevä tapa. Tämän osoittamiseksi olen poistanut alkuperäisen pivot-taulukon. Valitse Lisää, pivot-taulukko. Ennen kuin napsautat OK, valitse valintaruutu Lisää nämä tiedot tietomalliin.
Rakenna pivot-taulukko normaalisti. Valitse A3: n avattavasta valikosta Arvosuodattimet, Top 10 ja pyydä viisi parasta asiakasta. Kun yksi solu pivot-taulukossa on valittu, siirry valintanauhan Suunnittelu-välilehdelle ja avaa avattavat välisummat. Viimeinen valinta avattavassa valikossa on Sisällytä suodatetut kohteet yhteensä. Normaalisti tämä valinta näkyy harmaana. Mutta koska tiedot tallennetaan tietomalliin normaalin pivot-välimuistin sijaan, tämä vaihtoehto on nyt käytettävissä.
Valitse Sisällytä suodatetut kohteet kokonaismäärään -vaihtoehto, ja Kokonaissumma sisältää nyt tähden ja kaikkien tietojen kokonaisuuden, kuten alla on esitetty.
Tämä taikasolutemppu tuli minulle alun perin Danilta Philadelphiassa pidetyssä seminaarissani, ja toinen Dan toisti sen 15 vuotta myöhemmin Cincinnatissa pidetystä seminaaristani. Kiitos Miguel Caballerolle tämän ominaisuuden ehdottamisesta.
