A szokásos pivot tábla szűrők az Összes jelentés szűrő megjelenítése oldalakat kínálják, de a szeletelők nem támogatják ezt a funkciót. Ma néhány VBA végigvezeti az összes lehetséges szeletelő kombinációt.
Videót nézni
Videó átirat
Ismerje meg az Excel programot, a Podcast 2106. epizód: Hozzon létre egy PDF-et 3 szeletelő minden kombinációjából.
Milyen nagy kérdésünk van ma. Valaki beírta, meg akarta tudni, hogy lehetséges-e. Jelenleg 3 darabolójuk forgóasztalt futtat. Nem tudom, hogy néz ki a forgóasztal. Ez bizalmas. Nem láthatom, így csak tippelek, igaz? Tehát azt csinálják, hogy mindegyik szeletelőből kiválasztanak egy elemet, majd létrehoznak egy PDF-et, majd mennek és kiválasztják a következő elemet, létrehoznak egy PDF-et, majd a következő elemet és a következő elemet, és akkor képzelje el, 400 szeletelő kombinációjával ez örökké tarthat, és azt mondták, van-e valamilyen módja annak, hogy egy program átmegy és áttekinti az összes lehetőséget?
Azt mondtam, rendben, itt van néhány minősítő kérdés. Első, nem Mac-en vagyunk, igaz? Nem Android, nem Excel az iPhone-hoz. Ez az Excel for Windows. Igen, azt mondták. Nagy. Azt mondtam, hogy a második igazán fontos kérdés az, hogy az egyik elemet egy szeletelőből szeretnénk kiválasztani, majd végül a másik elemet a szeletelőből, majd a másik elemet a szeletelőből. Nincs szükségünk olyan kombinációkra, mint ANDY, aztán ANDY és BETTY, aztán pedig ANDY és CHARLIE, igaz? Ez kint. Csak egy darabot fogok csinálni minden szeletelőből. Igen igen igen. Így fog menni. Tökéletes - mondtam. Tehát itt, mondja el ezt nekem, válassza ki az összes szeletelőt, lépjen a SZELET SZERSZÁMAI, OPCIÓK és a Szeletelő beállítások részhez. Ezt 2 epizóddal ezelőtt csináltuk. Nem őrült ez? FORMULÁKBAN HASZNÁLANDÓ NÉV, és tudom, hogy ez SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,rendben? Szóval, azt hiszem, megvan.
Most itt térünk át a VBA-ra, és mellesleg győződjön meg arról, hogy xlsm-ként van-e mentve, és győződjön meg arról, hogy a makróbiztonsága a makrók engedélyezésére van beállítva. Ha xlsx-ként van elmentve, higgyen nekem, el kell végeznie egy FÁJLT, MENTÉS MINT, elveszíti az összes munkáját, ha xlsx-nek hagyja. Igen, a használt táblázatok 99,9% -a xlsx, de ez a makróval nem fog működni. ALT + F11. Rendben, szóval itt van a kód.
Megtaláljuk három szeletelő gyorsítótárat, egy szeletelő elemet és 3 tartományt. Mindegyik szeletelő gyorsítótár esetében a képletben használt névre fogjuk állítani, amelyet a Szeletelő beállításai párbeszédpanelen mutattam meg. Tehát van hárman. Szeretném tisztázni mindazokat, hogy megbizonyosodjak arról, hogy visszatérünk mindenhez. Ezt a számlálót később a fájlnévben fogjuk használni.
Rendben. Ez a következő szakasz itt, JOBBRA KERÜL, HASZNÁLJA MEG AZ HASZNÁLATI TETTEK HATÁLYOS LISTÁJÁT. Lásd a 2. kivitelt, hogy miért kellett bekövetkeznie ennek az őrületnek. Tehát meg fogom találni, hol van a következő elérhető oszlop, valahogy menj át 2-re az utolsó oszlopból, ne felejtsd el, hogy később törölhessem a cuccot, majd minden egyes SI, szeletelő elemnél az SC1-ben. SLICERITEMS, megírjuk azt a szeletelő feliratot a táblázatba. Ha befejeztük az összes szeletelő elemet, derítse ki, hány sorunk volt ma, majd nevezze el ezt a tartományt SLICERITEMS1 néven. Megismételjük az egészet a 2. szeletelő gyorsítótárnál, átmegyünk 1 oszlopon, a SLICERITEMS2 és a SLICERITEMS3.
Hadd mutassam meg, hogy néz ki ezen a ponton. Tehát ide teszek egy töréspontot, és futtatjuk ezt a kódot. Rendben. Ez gyors volt. Át fogunk váltani a VBA-ra, és itt messze a jobb oldalon 3 új listát fogok kapni. Ezek a listák tartalmazzák mindazokat, amelyek a szeletelőben találhatók, és úgy látja, hogy a neve SLICERITEMS1, SLICERITEMS2 és SLICERITEMS3, rendben? A végén megszabadulunk ettől, de ez ad nekünk valamit, amit át kell hurkolnunk. Vissza a VBA-hoz.
Rendben. Végiglépkedünk a SLICERITEMS1 összes elemén, töröljük a szűrőt az 1. szeletelő gyorsítótárból, majd egyenként átmegyünk minden szeletelő elemen, és megnézzük, hogy ez a szeletelő elem = ehhez tartozik-e. CELL1.VALUE, és ismét végigvezetjük az egyes értékeket. Szóval, először ez lesz ANDY, majd BETTY, és tudod, és így tovább.
Ez frusztráló. Nem találtam semmilyen módot az összes szeletelő egyszerre kikapcsolására. Meg is próbáltam rögzíteni a kódot és kiválasztani egy szeletelőt, és a rögzített kód 9 szeletelőt adott vissza, és kapcsolta be az egy szeletelőt, jól? Olyan frusztráló, hogy ennél jobbat nem találtam, de ennél jobbat sem.
Tehát az első szeletelőt = ANDY-ra állítottuk. Ezután átmegyünk, és a második szeletelőhöz beállítjuk = az első elemre. A harmadik szeletelőnél állítsa be = az első elemre.
Rendben. Aztán itt lent döntse el, hogy ez vajon érvényes kombináció-e. El kell magyaráznom neked, hogy ez miért fontos. Ha mi emberekként ezt csináljuk, ANDY, akkor nem az A52-et választanánk, mert egyértelműen szürkén jelenik meg, de a makró túl hülye lesz, és az A52-et, majd a 104-et választja, és ezt az üreset hozza létre. Pivot tábla. Tehát ezer lehetséges kombináció van itt. Tudom, hogy csak 400 lehetséges jelentés van. Ezt az illető mondta nekem, és így 600-szor fogunk eljutni, ahol elkészítjük ennek a (csúnya - 04:45) jelentésnek a PDF-jét.
Tehát azt fogom csinálni, hogy itt fogok megnézni az ELEMZÉS lapon - amelyet 2010-ben OPTIONS-nek hívtak -, és megnézem, mi ennek a pivot-táblának a neve, és szeretném megnézni, hogy hány sor van kapunk. Az én esetemben, ha 2 sort kapok, tudom, hogy ez egy jelentés, amelyet nem akarok exportálni. Ha 2-nél több sort kapok, 3, 4, 5, 6, akkor tudom, hogy ez egy jelentés, amelyet exportálni akarok. Ki kell találnod a helyzetedben, hogy mi az.
Rendben. Tehát ezért ellenőrizzük, hogy a 2. forgatótábla és ez a név volt-e ott a szalagban, a .TABLERANGE2.ROWS.COUNT értéke> 2. Ha nem> 2, akkor nem akarjuk PDF-t készíteni, rendben? Tehát ez az IF állítás az END IF leírásáig azt mondja, hogy csak az értékkel rendelkező jelentéskombinációkhoz fogjuk létrehozni a PDF-eket. MYFILENAME, létrehoztam egy mappát: C: REPORTS. Ez csak egy üres mappa. C: JELENTÉSEK. Győződjön meg róla, hogy rendelkezik mappával, és ugyanazt a mappanevet használja a makróban. C: REPORTS / és a fájl neve REPORT001.PDF lesz. Most számlálóként inicializáltuk a biztonsági másolatot. 1 van a FORMAT használatával, amely az Excel-ben egyenértékű a számláló szövegének és a 000-nek. Így kapok 001-et, majd 002-t, majd 003-at, majd 004-et. helyesen fog válogatni.Ha most hívtam ezt a JELENTÉST1, majd később lesz egy JELENTÉS10 és 11, később pedig a JELENTÉS 100, akkor mindannyian együtt fognak rendezni, ha nem tartoznak egymáshoz, rendben? Tehát létrehozva a fájl nevét, ha a fájl legutóbb létezett, amikor ezt futtattuk, meg fogjuk ölni. Más szóval törölje. Természetesen, ha megpróbál megölni egy olyan fájlt, amely nincs ott, akkor hibát fog okozni. Tehát, ha hibát kapunk a következő sorban, akkor rendben van. Csak folytassa, de akkor visszaállítottam a hibaellenőrzést az ON ERROR GOTO 0-on.Természetesen, ha megpróbál megölni egy olyan fájlt, amely nincs ott, akkor hibát fog okozni. Tehát, ha hibát kapunk a következő sorban, akkor rendben van. Csak folytassa, de akkor visszaállítottam a hibakeresést az ON ERROR GOTO 0-on.Természetesen, ha megpróbál megölni egy olyan fájlt, amely nincs ott, akkor hibát fog okozni. Tehát, ha hibát kapunk a következő sorban, akkor rendben van. Csak folytassa, de akkor visszaállítottam a hibaellenőrzést az ON ERROR GOTO 0-on.
Itt található az AKTÍV LAP, KIVITEL RÖGZÍTETT FORMÁTUMBAN, PDF formátumban, ott van a fájl neve, mindezek a választások, majd én növelem a számlálót, így így legközelebb, amikor találunk olyat, amely rendelkezik rekordokkal, létrehozzuk a REPORT002.PDF fájlt. . Fejezze be ezt a három hurkot, majd TÖRLJE KI A STATIKUS LISTÁKAT. Szóval, emlékszem, melyik oszlop voltunk, méretezzen át 1 sort, 3 oszlopot, ENTIRECOLUMN.CLEAR, majd ott egy szép kis üzenetdobozt, amely megmutatja, hogy a dolgok létrejöttek. Oké. Futtassuk.
Rendben. Mi történjen itt, ha elmegyünk és megnézzük a Windows Intézőt, ott van. Oké. Létrehozása… mint másodpercenként 2, 3, 4 vagy több. Ezt szüneteltetem és hagyom futni. Rendben. Itt vagyunk. 326 jelentés készült. Végighúzta mind a 1000 lehetőséget, és csak azokat tartotta meg, ahol tényleges eredmény született. Rendben, 9: 38-tól 9: 42-ig, 4 perc mindehhez, de még mindig gyorsabb, mint a 400-at, rendben?
Rendben. Tehát ez a makro módja ennek. A másik dolog, ami itt feltűnt, hogy működhet, vagy nem. Nagyon nehéz megmondani. Vegyük az adatainkat, és át fogom helyezni az adatokat egy teljesen új munkafüzetbe. Költözzön vagy másoljon, hozzon létre másolatot egy új könyvhöz, kattintson az OK gombra, és itt egy olyan trükköt fogunk használni, amelyet először Juhasz Szilviától - egy nagyszerű Excel-tanácsadótól tanultam Dél-Kaliforniában - tanultam, és adjon ide egy KEY mezőt. A KEY mező = FELÜLVIZSGÁLÓ & ANTENNA & Fegyelmezett. Ezt lemásoljuk, és beillesztünk egy új pivot táblázatot. Kattintson az OK gombra, és átvesszük ezt a mezőt, a KEY mezőt, és áthelyezzük a régimódi SZŰRŐK közé, majd nézzük meg. (Hadd oszlassunk el itt egy kis jelentést - 08:30). FELÜLVIZSGÁLÓ, ANTENNA, Fegyelmezettség és BEVÉTELEK.
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.
És a harmadik kivitel, rendben? Ez az az őrült. Ha makrót akarok rögzíteni, ha (makrót akarok írni - 13:35) csak egy elemet választani, akkor találja meg, hogyan lehet ezt a DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER segítségével használni, kattintson az OK gombra, és egyszerűen kiválasztunk egyet tétel. FLO. Kattintson a FELVÉTEL LEÁLLÍTÁSA lehetőségre, majd az ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER elemre megyünk, SZERKESZTÜK, és, bizony, a FLO-t IGAZSÁGBAN, majd mindenki mást villogtatják. Ez azt jelenti, hogy ha van egy szeletelőm, benne 100 tétel, akkor egy 100 sornyi kódot kell beiktatniuk oda, hogy minden mást kijelöljenek. Hihetetlenül hatékonynak tűnik, de ott vagy.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2106.xlsx
