Tavalliset pivot-taulukon suodattimet tarjoavat Näytä kaikki raporttisuodatin -sivut, mutta Slicers eivät tue tätä toimintoa. Nykyään jotkut VBA: n läpi kaikki mahdolliset viipaleyhdistelmät.
Katso video
Videon transkriptio
Opi Excel, Podcast-jakso 2106: Luo PDF jokaisesta kolmen viipaleen yhdistelmästä.
Mikä suuri kysymys meillä on tänään. Joku kirjoitti sisään ja halusi tietää, onko se mahdollista. Tällä hetkellä heillä on 3 viipaletta, jotka käyttävät kääntöpöytää. En tiedä miltä kääntötaulukko näyttää. Se on luottamuksellista. En saa nähdä sitä, joten arvaan vain, eikö? Joten, mitä he tekevät, he valitsevat yhden kohteen jokaisesta viipaleesta ja luovat sitten PDF: n, sitten siirtyvät ja valitsevat seuraavan kohteen ja luovat PDF: n, ja sitten seuraavan kohteen ja seuraavan kohteen, ja voit Kuvittele, että 400 viipaleiden yhdistelmällä tämä voi kestää ikuisesti, ja he sanoivat, onko jokin tapa saada ohjelma käymään läpi kaikki vaihtoehdot?
Sanoin, okei, tässä on joitain vaatimuksia. Ensinnäkin, emme ole Macissa, eikö? Ei Android, ei Excel for iPhone. Tämä on Excel for Windows. Kyllä, he sanoivat. Loistava. Sanoin, toinen todella tärkeä kysymys on, että haluamme valita yhden kohteen leikkurista ja lopulta toisen osan viipaleesta ja sitten toisen osan viipaleesta. Emme tarvitse yhdistelmiä, kuten ANDY, ja sitten ANDY ja BETTY, ja sitten ANDY ja CHARLIE, eikö? Se on ulkona. Aion tehdä vain yhden kohteen jokaisesta viipaleesta. Kyllä kyllä kyllä. Näin se menee. Täydellinen, sanoin. Joten tässä, kerro tämä minulle, valitse kukin viipale, siirry SIIPIKONEIDEN TYÖKALUT, LISÄVARUSTEET ja siirry kohtaan Viipaleasetukset. Teimme juuri tämän 2 jaksoa sitten. Eikö tämä ole hullua? NIMI, KÄYTTÖ KAAVOISSA, ja tiedän, että se on SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,hyvä on? Joten luulen saan sen.
Nyt siirrymme VBA: han täällä, ja muuten, varmista, että olet tallennettu xlsm-tiedostona, ja varmista, että makroturva on asetettu sallimaan makrot. Jos se on tallennettu nimellä xlsx, luota minuun, sinun on mentävä tekemään TIEDOSTO, TALLENNA AS, menetät kaikki työsi, jos jätät sen xlsx-tiedostoksi. Kyllä, 99,9% käyttämistäsi laskentataulukoista on xlsx, mutta tämä makrolla ei toimi. ALT + F11. Selvä, joten tässä on koodi.
Löydämme kolme viipalointivälimuistia, yhden viipalointikohteen ja 3 aluetta. Jokaiselle viipalointivälimuistille asetamme sen nimeksi, jota käytettiin kaavassa, jonka näytin juuri SLICER SETTINGS -valintaikkunassa. Joten meillä on kolme niistä. Haluan tyhjentää kaikki ne varmistaaksemme, että olemme palanneet kaikkeen valintaan. Tätä laskuria käytetään myöhemmin tiedostonimessä.
Hyvä on. Nyt tämä seuraava osio täällä, POISTA OIKEALLE, RAKENNA KOLME STAATTISIA LUETTELOJA KAIKISTA VIIKKAILIJOISTA. Katso ulosottoa nro 2 saadaksesi selville, miksi tämän hulluuden piti tapahtua. Joten aion selvittää, missä seuraava käytettävissä oleva sarake on, mene tavallaan yli 2 viimeisestä sarakkeesta, muista, että voin poistaa tavaraa myöhemmin, ja sitten jokaisen SI: n, viipalointikohdan kohdalla, IN SC1.SLICERITEMS, kirjoitamme leikkauslehden tuon leikkurin kuvatekstin. Kun olemme suorittaneet kaikki nämä viipalointikohteet, selvitä, kuinka monta riviä meillä oli tänään, ja nimeä sitten alue SLICERITEMS1. Toistamme koko asian viipalointivälimuistiin 2, menee yli 1 sarakkeen, SLICERITEMS2 ja SLICERITEMS3.
Haluan näyttää, miltä se näyttää tässä vaiheessa. Joten laitan katkaisupisteen tänne ja suoritamme tämän koodin. Hyvä on. Se oli nopeaa. Aiomme siirtyä VBA: lle, ja kaukana täällä oikealla puolella, saan 3 uutta luetteloa. Nämä luettelot ovat kaikki, mikä on viipalointilaitteessa, ja näet sen olevan SLICERITEMS1, SLICERITEMS2 ja SLICERITEMS3, okei? Pääset eroon lopulta, mutta se antaa meille jotain silmukoita. Takaisin VBA: han.
Hyvä on. Käymme läpi kaikki SLICERITEMS1-kohteet, tyhjennämme suodattimen viipalointivälimuistiin 1 ja käymme sitten läpi yksi kerrallaan jokaisen viipalointikohdan läpi ja katsomme, onko tämä viipalointikohde = tähän CELL1.VALUE, ja jälleen kerran käymme läpi jokaisen arvon. Joten ensimmäistä kertaa se tulee olemaan ANDY ja sitten BETTY ja tiedät, ja niin edelleen.
Se on turhauttavaa. En löytänyt tapaa sammuttaa kaikkia viipaleita kerralla. Yritin jopa tallentaa koodin ja valita yhden osittajan, ja tallennettu koodi palautti 9 viipaletta pois päältä ja kytki yhden viipaleen päälle, okei? Niin turhauttavaa, että en löytänyt mitään parempaa, mutta en löytänyt mitään parempaa.
Joten asetamme ensimmäisen viipalointilaitteen = arvoksi ANDY. Sitten käymme läpi, ja toiselle viipaleelle asetamme sen = ensimmäiseen kohtaan. Määritä kolmannelle viipaleelle = ensimmäinen kohta.
Hyvä on. Sitten täällä täällä, PÄÄTÄ, JOS TÄMÄ ON KELPOINEN YHDISTELMÄ. Minun on selitettävä sinulle, miksi se on tärkeää. Jos me ihmisinä teemme tämän, ANDY, emme valitsisi A52: ta, koska se on selvästi harmaantunut, mutta makro tulee olemaan liian tyhmä ja se valitsee A52: n ja sitten 104, ja se luo tämän tyhjän kääntöpöytä. Joten täällä on tuhat mahdollista yhdistelmää. Tiedän, että raportteja on vain 400. Sitä henkilö kertoi minulle, joten saamme 600 kertaa, missä aiomme luoda PDF-tiedoston tästä (ruma - 04:45) raportista.
Joten aion tarkastella tätä ANALYYSI-välilehdessä - sitä kutsuttiin OPTIONS vuonna 2010 - ja nähdä, mikä on tämän pivot-taulukon nimi, ja haluan nähdä, kuinka monta riviä saamme. Minun tapauksessani, jos saan 2 riviä, tiedän, että se on raportti, jota en halua viedä. Jos saan enemmän kuin 2 riviä, 3, 4, 5, 6, tiedän, että se on raportti, jonka haluan viedä. Sinun on selvitettävä tilanteessasi, mikä se on.
Hyvä on. Joten siksi tarkistamme, onko pivot-taulukko 2 ja että nimi oli siellä nauhassa, .TABLERANGE2.ROWS.COUNT on> 2. Jos se ei ole> 2, emme halua Luo PDF, okei? Joten tämä IF-lausunto tähän END IF: ään asti sanoo, että aiomme luoda vain PDF-tiedostot raporttiyhdistelmille, joilla on arvoja. MYFILENAME, loin kansion nimeltä C: REPORTS. Se on vain tyhjä kansio. C: RAPORTIT. Varmista, että sinulla on kansio ja käytät samaa kansionimeä makrossa. C: RAPORTIT / ja tiedoston nimi tulee olemaan REPORT001.PDF. Nyt laskuri, jonka aloitimme varmuuskopiosta, on 1 FORMAT-sovelluksella, joka vastaa Excelissä laskurin tekstin sanomista, ja 000. Siten saan 001, sitten 002, sitten 003 ja sitten 004. Ne lajittelemme oikein.Jos olisin juuri soittanut tähän REPORT1: een, ja myöhemmin minulla on REPORT10 ja 11, ja myöhemmin REPORT100, ne kaikki lajittelevat yhdessä, kun he eivät kuulu yhteen. Joten, luomalla tiedoston nimen, jos tiedosto on olemassa edellisestä kerrasta, kun suoritimme tämän, aiomme tappaa sen. Toisin sanoen poista se. Tietenkin, jos yrität tappaa tiedoston, jota ei ole, he heittävät virheen. Joten, jos saamme virheen seuraavalle riville, se on hieno. Jatka vain, mutta sitten nollasin virheen tarkistuksen ON ERROR GOTO 0.Tietenkin, jos yrität tappaa tiedoston, jota ei ole, he heittävät virheen. Joten, jos saamme virheen seuraavalle riville, se on hieno. Jatka vain, mutta sitten nollasin virheen tarkistuksen ON ERROR GOTO 0.Tietenkin, jos yrität tappaa tiedoston, jota ei ole, he heittävät virheen. Joten, jos saamme virheen seuraavalle riville, se on hieno. Jatka vain, mutta sitten nollasin virheen tarkistuksen ON ERROR GOTO 0.
Tässä on AKTIIVINEN LOMAKE, VIENTI KIINTEÄMUODOSSA, PDF-tiedostona, siellä on tiedostonimi, kaikki nuo valinnat, ja sitten korotan laskuria, joten seuraavalla kerralla, kun löydämme tietueita, luomme REPORT002.PDF . Viimeistele nuo kolme silmukkaa ja TYHJENNÄ STAATTISET LUETTELOT. Joten muistan, mikä sarake me olimme, kokoa 1 rivi, 3 saraketta, ENTIRECOLUMN.CLEAR ja sitten mukava pieni viestiruutu osoittamaan, että asiat on luotu. Okei. Suoritetaan se.
Hyvä on. Nyt, mitä täällä pitäisi tapahtua, on, jos menemme katsomaan Windowsin Resurssienhallinnassa, siinä se on. Okei. Se luo … kuten joka sekunti saamme 2, 3 tai 4 tai enemmän. Keskeytän tämän ja annan sen käydä. Hyvä on. Siellä me olemme. 326 raporttia on luotu. Se kävi läpi kaikki 1000 mahdollisuutta ja piti vain ne, joissa oli todellinen tulos. Selvä, kello 9:38 - 9:42, 4 minuuttia kaiken tekemiseen, mutta silti nopeammin kuin 400: n tekeminen.
Hyvä on. Joten, se on makrotapa tehdä tämä. Toinen asia, joka hämmästytti minua tässä, että se voi tai ei toimi. On todella vaikeaa sanoa. Otetaan tietomme ja siirrän tiedot upouuteen työkirjaan. SIIRRY TAI KOPIOI, LUO KOPIO UUSIIN KIRJOON, napsauta OK, ja käytämme tässä temppua, jonka opin ensin Szilvia Juhaszilta - hienolta Excel-konsultilta Etelä-Kaliforniassa - ja aiomme lisää KEY-kenttä tähän. AVAIN-kenttä on = ARVOSTELIJA & ANTENNA & KURIN. Kopioimme sen alas ja lisäämme uuden pivot-taulukon. Napsauta OK ja otamme kyseisen kentän, AVAIN-kentän, ja siirrämme sen ylös vanhanaikaisiin SUODATTIMIIN, ja sitten katsotaan. (Hajotetaan pieni raportti täällä - 08:30.) TARKASTELIJAT, ANTENNI, KURSSI ja TULOT.
Alright, now, normally what we would do here is would come open this filter and choose one item from the filter, but the trick from Szilvia is that we can take this pivot table and go to either the ANALYZE tab in ’13 or ’16, or the OPTIONS tab in 2010, open the OPTIONS dropdown, say SHOW REPORT FILTER PAGES, SHOW ALL PAGES OF KEY, and what it's doing right now is it’s inserting a new worksheet for every unique combination of the KEY, probably 300 and some files, alright? Now, how many worksheets can you have in a workbook? Well, that number is different on every computer and it depends on how complicated the workbook is because it's limited by available memory, but here we start on ANDY B37 112. I’m going to press CONTROL and this arrow down to JOE, like that.
The beautiful advantage here is, when I do FILE, EXPORT, CREATE A PDF, and then ALLREPORTS, we’re going to end up with a single PDF with all 326 reports in it. Now, we could have created a single PDF using Adobe Acrobat, select all of these reports, right click, and COMBINE FILES IN ACROBAT, but that requires you have a full version of Acrobat, not just Acrobat Reader.
So, this great trick using SHOW REPORT FILTER PAGES from Szilvia might be a great, great alternative if you have enough memory to create all the versions.
Alright. To learn more about VBA, check out this book Excel 2016 VBA And Macros by Bill Jelen and Tracy Syrstad. That will get you up the VBA learning curve.
Alright. The goal is to loop through all combinations in 3 slicers and generate a PDF for each. Used a little VBA to loop through those slicers. Save as PDF using VBA. The alternate solution there at the end is Szilvia Juhasz’s SHOW REPORT FILTER PAGES and then export the whole thing as PDF.
Hey. I want to thank you for stopping by. We'll see you next time for another netcast from.
Well, this will be an outtake. First time I ran this darn thing, I got a 1000 of them, and every darn one of them was Andy A52 104. I'm like what the heck is going on? Except I didn’t say heck.
Alright. So, here, watch this code. This was the code I had. I said I'm going to go through all of the filters FOR EACH SI IN SC1.SLICERITEMS and then I set it = to FALSE, and then the one that I want, I'll set = to TRUE, right? Sound like a great, great bit of code, alright?
So, here's what happens. The first one is Andy, goes away. Betty goes away. Charlie goes away. Dale. Here, I'll just keep pressing F8, F8, F8, F8. I'm down to the last one. This is JOE. I'm about to set JOE = to FALSE and watch what happens over there in Excel. Bam. Once you turn JOE off, it turns them all back on. I mean, that stinks, Excel, and then I would try and turn, what is it, ANDY back on and turning ANDY back on when everybody else is already on. So, it ran through… it created a 1000 of the PDFs, every stinking one. It was ANDY A52 104. It's funny now. It wasn't then.
Alright. Here’s another outtake. Why did I go to the trouble of building the list, the static list, off to the right hand side so I can loop through that static list? Well, originally, I was looping through all of the items in the slicers themselves and it was causing some wrong results. See, here, Andy A52 112 should be 0, but when I actually ran the loop, ANDY A52 112 is showing up with six rows. I’m like, well, that can't be. So, over here, my code, ALT+F11, I put a thing, if SI1.CAPTION=ANDY, SI2.CAPTION=A52, SI3 CAPTION=112, THEN STOP, right? So let's run this code, then stop.
There we are, and I will come back. We should have ANDY A52 112, but when I look, ANDY, it’s not A52, it’s D33. What the heck is going on, and then I come back here, ALT+F11, and I right-click and say that I want to ADD A WATCH, and when I look at this, it claims that the caption is A52 but, very clearly, it's D33. So, is this a bug or am I just violating some weird rule by looping through a collection of 10 items when the order of those 10 items is constantly being reordered? It seems like that must be the problem. Hence, we went with the static list off to the right.
Ja kolmas vie, okei? Tämä on hullu. Jos haluan tallentaa makron, haluan (kirjoittaa makron - 13:35) valita vain yhden kohteen, selvittää, miten se tehdään käyttämällä KEHITTÄJÄ, TALLENNA MAKRO, HOWTOCHOOSEONEITEMFROMSLICER, napsauta OK ja valitsemme yksinkertaisesti kohde. FLO. Napsauta PYSÄYTÄ TALLENNUS, sitten siirrymme ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, MUOKKAA sitä, ja tosiaan, he tekevät FLO-TOSIN ja sitten kaikki muut FLASE. Se tarkoittaa, että jos minulla olisi leikkuri, jossa on 100 kohdetta, heidän pitäisi laittaa sinne 100 riviä koodia poistaakseen kaiken muun valinnan. Näyttää uskomattoman tehottomalta, mutta olet siellä.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2106.xlsx
