Kääntötaulukko Top 10 -suodatin antaa näkyvien rivien kokonaismäärän
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.
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.
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.
- Valitse kaikki tietosi painamalla Ctrl + * ja napsauta Suodatin-kuvaketta Tiedot-välilehdessä.
- 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, siirry 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, takaisin Pivot-taulukoihin …
Joten on sääntö, jonka mukaan et voi käyttää automaattisia suodattimia, kun olet pivot-taulukossa. Katso alempaa? Suodatin-kuvake on harmaa, koska olen valinnut solun pivot-taulukossa.
En ole koskaan miettinyt, miksi Microsoft harmaisee tätä. Sen on oltava jotain sisäistä, joka sanoo, että automaattinen suodatin ja pivot-taulukko eivät voi olla rinnakkain. Joten Excel-tiimissä on joku, joka vastaa suodatinkuvakkeen harmahtamisesta. Tuo henkilö ei ole koskaan kuullut taikakennosta. Valitse solu pivot-taulukossa ja suodatin näkyy harmaana. Napsauta kääntötaulukon ulkopuolella ja suodatin otetaan uudelleen käyttöön.
Mutta odota. Entä taikasolu, josta juuri kerroin? 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 suodattimet. Siirry Tulot-pudotusvalikkoon 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+: ssaJos 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 2016, 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 ruutu, jossa lukee 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älilehteen 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 kokonaisuuteen -vaihtoehto, ja loppusumma sisältää nyt tähden ja kaikkien tietojen kokonaismäärän.
Tämä temppu tuli minulle alun perin Danilta seminaarissani Philadelphiassa. Kiitos Miguel Caballerolle tämän ominaisuuden ehdottamisesta.
Katso video
- Kääntötaulukko Top 10 -suodatin antaa näkyvien rivien kokonaismäärän
- Sisällytä suodatetut kohteet kokonaismäärään on harmaana
- Pariton tapa kutsua tietosuodatin taikasolusta
- Tietosuodattimia ei sallita pivot-taulukoissa
- Excel ei harmaasta datasuodatinta taikasolusta
- Pyydä kuusi parasta päästäksesi top 5 plus loppusumma
- Hyödyllinen suodattamiseen tietyn pivot-kohteen mukaan
- Excel 2013 tai uudempi: Eri tapa saada todellinen summa
- Lähetä tietosi tietomallin kautta
- Sisällytä suodatetut kohteet yhteensä on käytettävissä
- Hanki yhteensä tähdellä
- Opin tämän temppun yli 10 vuotta sitten Danilta Philadelphiassa
Videon transkriptio
Opi Excel for Podcast, jakso 1999 - Pivot-taulukko, tosi viisi parasta
Podcasting koko tätä kirjaa. Siellä on soittolista. Voit seurata soittolistaa napsauttamalla oikeassa yläkulmassa olevaa I-painiketta. Tervetuloa takaisin netcastiin. Olen Bill Jelen.
Selvä, joten aiomme luoda pivot-taulukon ja haluamme näyttää kaikki asiakkaat, mutta vain viisi parasta asiakasta. INSERT, kääntötaulukko. Okei, laitan asiakkaan alas vasemmalle puolelle ja tulot. Selvä, joten tässä on koko asiakasluettelomme, jonka arvo on 6,7 miljoonaa dollaria. Excelin avulla on helppo tehdä viisi parasta. Siirry Rivitarrat, Arvosuodattimet, alkuun 10. Ei tarvitse olla alkuun. Se voi olla ylä- tai alaosa. Ei tarvitse olla viisi. Se voi olla kaksikymmentä, neljäkymmentä, se voi olla mitä tahansa. Ylin kahdeksankymmentä prosenttia, anna minulle tarpeeksi ennätyksiä päästäksesi kolmeen miljoonaan dollariin tai neljään miljoonaan dollariin, mutta tässä mennään. Viisi parasta kohdetta. Muista nyt 6,7 miljoonaa dollaria, napsauta OK ja iso ongelmani täällä on, että se loppusumma ei ole 6,7 miljoonaa. Kun annan tämän myyntiosaston johtajalle, hän hämmentyy sanoen: odota hetki,Tiedän, että tein yli 3,3 miljoonaa dollaria. Aivan, joten aiomme kumota, kumota sen ja palata alkuperäisiin tietoihin.
Nyt tämä seuraava temppu, jonka opin yhdellä Power Excel -seminaaristani Philadelphiassa. Kaveri nimeltä Dan rivillä kaksi näytti minulle tämän. Yli kymmenen vuotta sitten hän näytti minulle tämän temppun, ja ensin on puhuttava suodattimista. Joten normaalisti, jos aiot käyttää tavallista suodatinta, tätä suodatinta täällä, valitset minkä tahansa yhden solun tietojoukossasi ja napsautat suodatinkuvaketta tai jotkut valitsevat koko tietojoukon, CONTROL * ja napsauttavat suodatinkuvaketta, mutta on kolmas tapa. Tapa, josta kukaan ei välitä. Jos menet viimeiseen otsikkosoluun, minun tapauksessani, se on L1: n hinta ja mene yksi solu oikealle. Kutsun tätä taikasoluksi, minulla ei ole aavistustakaan miksi, mutta jostain tuntemattomasta syystä voin suodattaa tästä solusta viereisen tietojoukon. Selvä, se on kuin outo tapa, eikä kukaan välitä tästä.
Aivan, koska on olemassa kaksi muuta todella hyvää tapaa käyttää suodatinta, kenenkään ei tarvitse tietää taikasolusta, mutta tässä on asia, katso pivot-taulukon sisällä, se on harmaana. Et saa käyttää näitä suodattimia. Se on sääntöjen vastaista. Jos tulen tänne, olen enemmän kuin tervetullut käyttämään suodatinta, mutta sisällä ne luokitellaan. En tiedä kuka on ihminen, joka harmaa tätä, mutta he eivät ole koskaan kuulleet pientä puhettani taikasolusta, koska jos menen aivan viimeiseen Otsikkosoluun ja menen yhden solun oikealle, katso sitä, he unohtavat harmaata suodattimen ja nyt olen juuri lisännyt vanhat automaattiset suodattimet pivot-taulukkoon. Joten tulen tänne, menen Numerosuodattimiin, joka on erilainen kuin Arvosuodattimet. Sitä kutsutaan edelleen Top Ten. Hieman erilainen, aion pyytää viisi parasta, ei kuusi parasta.Kuusi parasta, koska tälle suodattimelle loppusumma on vain yksi rivi, ja loppusumma on suurin erä, ja kun minulta kysytään nimikkeitä 2–6, saan viisi parasta.
Selvä, niin siellä me olemme. Viileä Filter-hakkerointi, joka antaa meille viisi parasta kohdetta ja kaikkien todellisen kokonaismäärän. Okei, pari asiaa. Älä unohda taikasolua. Selvä, ei ole mitään tapaa sammuttaa tätä suodatinta, ellet palaa taikasoluun. Selvä, joten sinun täytyy muistaa taikasolu. Lisäksi, jos muutat taustalla olevia tietoja ja päivität pivot-taulukon, he eivät aio päivittää suodatinta, koska Microsoftin mukaan sinulla ei ole suodatinta.
Tästä on hyötyä muissa asioissa. Joskus meillä on tuotteita, jotka menevät ylhäältä. Mennään tänne taulukkomuodossa. Ei välttämätöntä, haluan vain saada todellisia otsikoita. Gizmo, Widget, Gadgetit, Doodadit. Selvä ja ehkä olet Doodadsin johtaja ja sinun täytyy nähdä vain asiakkaat, joilla oli erityinen arvo, ja Doodads. Joten menen taikasoluun, kytken suodattimen päälle ja voin sitten Doodads-kohdasta pyytää kohteita, jotka ovat suurempia kuin nolla. Napsauta OK. Selvä, tämän tyyppinen suodatus ei olisi mahdollista tavallisessa pivot-taulukossa, mutta se on mahdollista käyttämällä taikasolua.
Selvä nyt kumotaan luettelo. Sammuta tämä suodatin ja poista pivot-taulukko, ja jos olet Excel 2013: ssa tai uudessa, näytän sinulle täysin laillisen tavan saada oikea summa alareunasta. Lisää pivot-taulukko, tässä alareunassa, Excel 2013: sta alkaen tämä erittäin vaaraton laatikko, ei kuulosta kovin jännittävältä, lisää nämä tiedot tietomalliin. Se lähettää tiedot kulissien takana Power Pivot -moottoriin. Luo täsmälleen sama raportti. Asiakkaat vasemmalla puolella. Tulot pivot-taulukon sydämessä. Siirry sitten tavallisiin suodattimiin, arvo-suodattimien alkuun 10. Kysy viisi parasta. Huomaa jälleen, että meillä on 6,7 miljoonaa dollaria, kun olen tehnyt tämän, 3,3 miljoonaa dollaria, mutta tässä on ero. Kun menen Suunnittelu-välilehteen, Välisummat-kohdassa tämä ominaisuus nimeltä Sisällytä suodatetut kohteet yhteensä,ei ole enää harmaantunut. Tavallisessa kääntötaulukossa ei ole saatavana. Saamme siellä pienen tähtimerkin ja se on kaiken kaikkiaan. Selvä, nyt tietysti se toimii vain Excel 2013: ssa tai uudemmassa.
Selvä, kestää kuusi viikkoa, ennen kuin saan koko kirjan täältä YouTubesta. Täällä on niin paljon hyviä vinkkejä. Vinkkejä, jotka voivat alkaa säästää aikaa heti. Osta koko kirja heti ja sinulla on pääsy kaikkiin 40: een. Se on itse asiassa paljon enemmän kuin 40 vinkkiä. Excel-pikanäppäimet. Kaikenlaisia hienoja juttuja tässä kirjassa.
Selvä, kertaus. Joten kun teemme Pivot-pöydän 10 suodattimen, se antaa meille kokonaismäärän, mutta vain näkyvät rivit, ei tavaraa, jonka se suodatti. Joo, jos siirrymme toiseen välilehteen ja etsimme välisummat, suodatetut erät ja summat, se näkyy harmaana, mutta on outo tapa kutsua vanha tietosuodatin taikasolusta. Viimeinen otsikkosolu, siirry yksi solu oikealle, et voi käyttää Suodattimia ja Pivot-taulukoita, mutta jos menet taikasoluun, he unohtavat harmaata sen. Nyt Numerosuodattimessa pyydät kuutta parasta saadaksesi viisi parasta sekä loppusumman. Hyödyllinen myös suodattamiseen tiettyyn pivot-kohteeseen: Doodadit, kaikki, jotka olivat yli 0 Doodadsissa tai 5 parhaassa Doodadissa. Excel 2013 tai uudempi, on erilainen tapa saada todellinen summa.Valitse tietomallin valintaruutu ja sisällytä sitten Suodatetut kohteet kokonaismäärään. Saat kokonaisuuden tähdellä. Ja kiitos Danille Philadelphiassa, joka näytti minulle yhdessä Power Excel -seminaareissani yli kymmenen vuotta sitten ja antoi minulle tämän suuren pienen temppun. Tapa suodattimelle hiipiä Club Pivot -taulukon läpi. He eivät yleensä salli kyseistä automaattista suodatinta.
Hei, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla, uudelle Netcastille MRExceliltä.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast1999.xlsx
