Los filtros de tabla dinámica normales ofrecen las páginas Mostrar todos los filtros de informes, pero los segmentadores no admiten esta funcionalidad. Hoy, algo de VBA para recorrer todas las combinaciones posibles de cortadores.
Ver video
Transcripción del video
Aprenda Excel de, Podcast Episodio 2106: Cree un PDF de cada combinación de 3 rebanadores.
Qué gran pregunta tenemos hoy. Alguien me escribió, quería saber si era posible. En este momento, tienen 3 cortadoras que ejecutan una tabla dinámica. No sé cómo se ve la tabla dinámica. Es confidencial. No se me permite verlo, así que solo estoy adivinando, ¿verdad? Entonces, lo que están haciendo es elegir un elemento de cada rebanador y luego crear un PDF, y luego elegir el siguiente elemento y crear un PDF, y luego el siguiente elemento y el siguiente elemento, y usted puede imagina, con 400 combinaciones de segmentaciones, esto podría llevar una eternidad, y dijeron, ¿hay alguna forma de hacer que un programa recorra y recorra todas las opciones?
Dije, está bien, aquí hay algunas preguntas de calificación. Número uno, no estamos en una Mac, ¿verdad? Ni Android, ni Excel para iPhone. Esto es Excel para Windows. Sí, dijeron. Excelente. Dije, la segunda pregunta realmente importante es que queremos elegir un elemento de una cortadora, y luego finalmente el otro artículo de la cortadora y luego el otro artículo de la cortadora. No necesitamos combinaciones como ANDY, y luego ANDY y BETTY, y luego ANDY y CHARLIE, ¿verdad? Eso está fuera. Solo voy a hacer un elemento de cada cortadora. Si si si. Esa es la forma en que va a ir. Perfecto, dije. Entonces aquí, dígame esto, elija cada cortadora, vaya a HERRAMIENTAS DE LA REBANADORA, OPCIONES, y vaya a CONFIGURACIÓN DE LA REBANADORA. Hicimos esto hace 2 episodios. ¿No es esto una locura? NOMBRE PARA USAR EN FÓRMULAS y sé que es SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,¿bien? Entonces, creo que lo tengo.
Ahora, vamos a cambiar a VBA aquí y, por cierto, asegúrese de que está guardado como xlsm y asegúrese de que su seguridad de macros esté configurada para permitir macros. Si está guardado como xlsx, créame, tiene que ir a hacer un ARCHIVO, GUARDAR COMO, perderá todo su trabajo si lo deja como xlsx. Sí, el 99,9% de las hojas de cálculo que usa son xlsx, pero esta con una macro no funcionará. ALT + F11. Muy bien, aquí está el código.
Vamos a encontrar tres cachés de segmentación, un elemento de segmentación y 3 rangos. Para cada uno de los cachés de la cortadora, vamos a establecerlo con el nombre utilizado en la fórmula que acabo de mostrarle en el cuadro de diálogo CONFIGURACIÓN DE LA REBANADORA. Entonces, tenemos los tres. Quiero borrar todos esos para asegurarme de que volvemos a seleccionar todo. Este contador se utilizará más adelante en el nombre del archivo.
Bien. Ahora, la siguiente sección aquí, HACIA LA DERECHA, CONSTRUYE TRES LISTAS ESTÁTICAS DE TODOS LOS ARTÍCULOS DE LA REBANADORA. Vea el descarte n. ° 2 para ver por qué tuvo que suceder esta locura. Así que voy a averiguar dónde está la siguiente columna disponible, más o menos 2 de la última columna, recuerda eso para poder eliminar las cosas más tarde, y luego, para cada SI, elemento de corte, EN SC1.SLICERITEMS, vamos a escribir ese título de la cortadora de archivos en la hoja de cálculo. Cuando hayamos terminado con todos esos elementos de segmentación, averigüe cuántas filas teníamos hoy y luego nombre ese rango como SLICERITEMS1. Vamos a repetir todo eso para la caché de segmentación 2, pasando por 1 columna, SLICERITEMS2 y SLICERITEMS3.
Déjame mostrarte cómo se ve en este momento. Entonces, pondré un punto de interrupción aquí y ejecutaremos este código. Bien. Eso fue rápido. Vamos a cambiar a VBA, y lejos de aquí, a la derecha, obtendré 3 listas nuevas. Estas listas son todo lo que hay en la cortadora, y ves que se llama SLICERITEMS1, SLICERITEMS2 y SLICERITEMS3, ¿de acuerdo? Nos desharemos de eso al final, pero eso nos da algo para recorrer. Volver a VBA.
Bien. Vamos a recorrer todos los elementos en SLICERITEMS1, borrar el filtro de la caché de la segmentación 1, y luego revisaremos, uno a la vez, cada elemento de la segmentación y veremos si este elemento de la segmentación es igual a esto CELL1.VALUE y, de nuevo, recorremos cada uno de los valores. Entonces, la primera vez, será ANDY y luego BETTY y, ya sabes, y así sucesivamente.
Es frustrante. No pude encontrar ninguna forma de apagar todas las cortadoras a la vez. Incluso intenté grabar el código y elegir una cortadora, y el código grabado devolvía 9 cortadoras y encendía una, ¿de acuerdo? Tan frustrante que no pude encontrar nada mejor que eso, pero no pude encontrar nada mejor que eso.
Entonces, configuramos el primer cortador = en ANDY. Luego pasamos, y para el segundo rebanador, vamos a establecerlo = en el primer elemento. Para el tercer rebanador, configúrelo = en el primer elemento.
Bien. Entonces, aquí abajo, DECIDIR SI ESTA ES UNA COMBINACIÓN VÁLIDA. Tengo que explicarte por qué eso es importante. Si nosotros, como humanos, estamos haciendo esto, ANDY, no elegiríamos A52 porque claramente está en gris, pero la macro será demasiado estúpida y elegirá A52 y luego 104, y creará este vacío. tabla dinámica. Entonces, hay mil combinaciones posibles aquí. Sé que solo hay 400 informes posibles. Eso es lo que me dijo la persona, así que vamos a obtener 600 veces donde vamos a crear un PDF de este (feo - 04:45) informe.
Entonces, lo que voy a hacer es buscar aquí en la pestaña ANALIZAR (se llamaba OPCIONES en 2010) y ver cuál es el nombre de esta tabla dinámica, y quiero ver cuántas filas obtenemos. En mi caso, si obtengo 2 filas, sé que es un informe que no quiero exportar. Si obtengo más de 2 filas, 3, 4, 5, 6, entonces sé que es un informe que quiero exportar. Vas a tener que averiguar en tu situación cuál es.
Bien. Entonces, es por eso que estamos verificando si la tabla dinámica 2 y, ese es el nombre que estaba allí en la cinta, .TABLERANGE2.ROWS.COUNT es> 2. Si no es> 2, no queremos crear un PDF, ¿de acuerdo? Entonces, esta declaración IF hasta este END IF dice que solo vamos a crear los PDF para las combinaciones de informes que tienen valores. MYFILENAME, creé una carpeta llamada C: REPORTS. Es solo una carpeta vacía. C: INFORMES. Asegúrese de tener una carpeta y use el mismo nombre de carpeta en la macro. C: REPORTS / y el nombre del archivo será REPORT001.PDF. Ahora, el contador que inicializamos de nuevo hay 1 usando FORMAT, que es equivalente en Excel a decir el texto del contador y 000. De esa manera, obtendré 001, luego 002, luego 003 y luego 004. Ellos vas a ordenar correctamente.Si acabo de llamar a este REPORT1, y luego tengo un REPORT10 y 11, y luego un REPORT100, todos se ordenarán juntos cuando no vayan juntos, ¿de acuerdo? Entonces, creando el nombre del archivo en caso de que el archivo exista desde la última vez que ejecutamos esto, lo vamos a eliminar. En otras palabras, elimínelo. Por supuesto, si intenta eliminar un archivo que no está allí, generará un error. Entonces, si obtenemos un error en la siguiente línea, está bien. Simplemente continúe, pero luego restablezco el error al verificar ON ERROR GOTO 0.Por supuesto, si intenta eliminar un archivo que no está allí, generará un error. Entonces, si obtenemos un error en la siguiente línea, está bien. Simplemente continúe, pero luego restablezco el error al verificar ON ERROR GOTO 0.Por supuesto, si intenta eliminar un archivo que no está allí, generará un error. Entonces, si obtenemos un error en la siguiente línea, está bien. Simplemente continúe, pero luego restablezco el error al verificar ON ERROR GOTO 0.
Aquí está la HOJA ACTIVA, EXPORTAR COMO FORMATO FIJO, como un PDF, está el nombre del archivo, todas esas opciones, y luego incremento el contador, de esa manera, la próxima vez que encontremos uno que tenga registros, estaremos creando REPORT002.PDF . Termine esos tres bucles y luego BORRE LAS LISTAS ESTÁTICAS. Entonces, recordaré en qué columna estábamos, cambiar el tamaño de 1 fila, 3 columnas, ENTIRECOLUMN.CLEAR, y luego un pequeño cuadro de mensaje para mostrar que se han creado cosas. Bueno. Ejecútelo.
Bien. Ahora, lo que debería estar sucediendo aquí es si vamos y buscamos en el Explorador de Windows, ahí está. Bueno. Está creando … como, cada segundo, obtenemos 2 o 3 o 4 o más. Pausaré esto y lo dejaré correr. Bien. Alli estamos nosotros. Se han creado 326 informes. Recorrió las 1000 posibilidades y solo mantuvo aquellas en las que había un resultado real. Muy bien, desde las 9:38 hasta las 9:42, 4 minutos para hacer todo eso, pero aún más rápido que hacer los 400, ¿de acuerdo?
Bien. Entonces, esa es la forma macro de hacer esto. La otra cosa que me llamó la atención aquí es que puede que funcione o no. Es realmente difícil de decir. Tomemos nuestros datos y los moveré a un nuevo libro de trabajo. MOVER O COPIAR, CREAR UNA COPIA, a un NUEVO LIBRO, haga clic en Aceptar, y vamos a utilizar un truco aquí que aprendí por primera vez de Szilvia Juhasz, una gran consultora de Excel en el sur de California, y vamos a agregue un campo CLAVE aquí. El campo CLAVE es = REVISOR & ANTENA & DISCIPLINA. Lo copiaremos e insertaremos una nueva tabla dinámica. Haga clic en Aceptar, y vamos a tomar ese campo, el campo CLAVE, y moverlo hacia los FILTROS anticuados, y luego veamos. (Disipemos un pequeño informe aquí con - 08:30) REVISOR, ANTENA, DISCIPLINA e INGRESOS, así.
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.
Y el tercer descarte, ¿de acuerdo? Este es el que está loco. Si quiero grabar una macro, si quiero (escribir una macro - 13:35) elegir solo un elemento, averigüe cómo hacerlo usando DESARROLLADOR, REGISTRO MACRO, CÓMO ELEGIR UNITEMFROMSLICER, haga clic en Aceptar, y simplemente elegimos uno articulo. FLO. Haga clic en DETENER GRABACIÓN, luego vamos a ALT + F8, CÓMO ELEGIR UN ARTÍCULO DE LICUADOR, EDITAR eso y, efectivamente, hacen que FLO sea TRUE y luego todos los demás FLASE. Significa que si tuviera una rebanadora con 100 elementos, tendrían que poner 100 líneas de código allí para anular la selección de todo lo demás. Parece increíblemente ineficiente, pero ahí está.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2106.xlsx
