Ocultar y mostrar filtros en tablas dinámicas seleccionadas

En el post anterior prometí subir una macro que no sólo nos permita ocultar los filtros de una tabla dinámica sino también que nos permita sobre que tablas actuar cuando hay más una en la hoja activa.

Supongamos este caso donde tenemos tres tablas (o mejor dicho, reportes) dinámicas en la hoja

Nuestro objetivo es seleccionar qué tablas aparecerán con filtros de campos y cuáles no. Para eso debemos tener la posibilidad de seleccionar sobre qué tablas aplicar la macro.

Este video muestra el funcionamiento de la macro




El cuaderno con los códigos y el userform puede descargarse aquí.

La macro funciona con un Userform y los códigos de sus elementos 

y una sub para disparar el userform

Para utilizar la macro podemos guardarla en el cuadero Personal y crear un atajo en la barra de acceso rápido o usar Alt+F8 - Ejecutar

Ocultar filtros en todas las tablas dinámicas de una hoja

Gustavo, colega en el trabajo, es uno de esos tipos que pueden causarte un otoño capilar prematuro con sus observaciones. En particular porque siempre le encuentra la quinta pata al gato, siempre habrá algún pero.
Así que cuando vio mi macro para mostrar u ocultar filtros de tablas dinámicas, no pudo menos que decirme: "muy bien, ¿pero si hay más de una tabla dinámica en la hoja?
Es bastante común que haya más de una tabla dinámica en una hoja por lo que tendremos que dar una solución al planteo de Gustavo.

Modificar la macro propuesta en la nota anterior para que muestre u oculte todos los filtros de todas las tablas dinámicas en la hoja activa es sencillo. Supongamos esta hoja con dos tablas dinámicas

Para ocultar los filtros de las dos tablas a la vez usamos este código

 Sub ocultar_Filtros_all()  
   Dim ptbl As PivotTable  
   Dim pfld As PivotField  
   For Each ptbl In ActiveSheet.PivotTables  
    For Each pfld In ptbl.PivotFields  
      pfld.EnableItemSelection = False  
    Next pfld  
   Next ptbl  
 End Sub  

Para restaurar los filtros usamos este otro código

 Sub mostrar_Filtros_all()  
 Dim ptbl As PivotTable  
 Dim pfld As PivotField  
   For Each ptbl In ActiveSheet.PivotTables  
    For Each pfld In ptbl.PivotFields  
      pfld.EnableItemSelection = True  
    Next pfld  
   Next ptbl  
 End Sub  

Para que nuestra macro sea realmente útil tenemos que agregar la posibilidad de seleccionar que tablas queremos modificar, lo cual será el tema del próximo post.

Consolidar archivos con Power Query - el caso ".tsv"

En la nota anterior mostramos como consolidar datos de varios archivos .csv (comma separated values) con Power Query. Estos son archivos de texto donde los campos están separados por comas o puntos y comas. Existen también archivos de texto .tsv donde, como en los archivos .csv, cada registro de la tabla es una línea del archivo de texto y cada campo de un registro se separa del siguiente por un carácter de tabulación - que es una forma del formato más general valores delimitador separados.

TSV es un formato de archivo simple ampliamente difundido que a menudo se utiliza para mover datos tabulares entre los diferentes programas informáticos que soportan este formato. Por ejemplo, un archivo de TSV puede ser utilizado para transferir información de un programa de base de datos para una hoja de cálculo. Y este es el caso de  un colega que después de haber leído mi post, vino a consultarme cómo hacer lo mismo con archivos .tsv.

La cuestión es que Power Query no cuenta con una función Tsv.Document para extraer datos de este tipo de archivos y la función Csv.Document, que mostramos en el post mencionado, si bien cuenta con un parámetro para indicar el separador a usar ([Delimiter]), pero todos mis intentos de indicar la tabulación como separador (#"tab", "#tab", #(Tab), etc.) terminaron en rotundos fracasos.

La solución fue importar los archivos al editor del Power Query, tal como hicimos con los archivos csv, agregar una única columna con los datos y luego separar esta columna con la funcionalidad Split Column del menú de Power Query.

Veamos el proceso. Después de seleccionar la carpeta que contiene los archivos a consolidar y abrirla en la ventana del Power Query (ver el proceso en el post anterior), agregamos la columa "Datos" creada con la fórmula =Csv.Document([Content])

Esta fórmula crea una columna que expandimos apretando la doble flecha en el encabezado

con este resultado

Eliminamos las columnas Content y Name con lo que nos queda una única columna con todos los datos. La funcionalidad Split Column by Delimiter si tiene el valor Tab como separador

con este resultado

Todo lo que nos queda por hacer es promover la primer fila a los encabezados

y transferir la tabla a una hoja de Excel o crear una conexión a la consulta

Consolidar datos de archivos .csv con Power Query

Este post es la continuación del anterior sobre la consolidación de datos con Power Query.
La misma técnica puede aplicarse si los archivos a integrar están en formato .csv (texto, comma separated values).
La "magia" del proceso son las funciones de extracción de datos del Power Query (el lenguaje "M"). Una reseña sucinta sobre las funciones del Power Query puede verse en esta página. Una lista detallada de toda las funciones "M" (el lenguaje del Power Query) puede obtenerse aquí.

En este post vamos a reveer los pasos para integrar datos de cuadernos guardados en una misma carpeta. Como en el caso anterior, partimos de la base que las tablas de datos en los distintos cuadernos tienen la misma estructura.

Como en el caso de los archivos .xlsx, usamos la opción From Files - From Folders del Power Query

Como en el caso anterior, recibimos una tabla con una fila por cada archivo de la carpeta seleccionada

Eliminamos todas las columnas excepto Content y Name y hacemos el "pase mágico": creamos una columna personalizada con la fórmula =Csv.Document([Content]), y recordemos que a diferencia de las funciones de Excel, debemos respetar las mayúsculas

La fórmula creará una nueva columna (eso es lo que las funciones M hacen)

que procedermos a expandir apretando al doble flecha en el ángulo derecho del encabezamiento de la columna

con este resultado

Lo último que nos queda por hacer es utilizar la primer fila de la tabla como encabezamiento de las columnas

Podemos eliminar la columna "Column1" (Binary) que ya no cumple ninguna función. La segunda columna contienen el nombre del archivo de donde se extrajo la fila. También esta columna puede eliminarse y si queremos dejarla para identificar el origen de los datos, debemos cambiar el nombre.

Finalmente, pasamos la tabla a una hoja del cuaderno Excel o creamos una conexión a la consulta.

Este video muestra todo el proceso

Integrar datos de archivos cerrados con Power Query

Consolidar datos de distintos archivos es una tarea frecuente para todo analista que use Excel. Tal es así que este blog ha tratado el tema desde distintos ángulos: usando MS Query, Access, SQL, Vba (macros) y últimamente con Power Query.

La técnica que mostré en el post anterior puede parecer complicada e incluso intimidante para quien recién se inicia en el uso del Power Query (nueva interfaz, nuevo idioma). Para nuestra fortuna, Miguel Escobar publicó en el pasado una técnica sencilla, que pueden ver en esta nota (y continuada luego en esta otra nota), y que reproduzco aquí en una versión simplificada (parece ser que Miguel eliminó la versión en castellano del post).

Power Query nos permite consolidar datos de distintos archivos sin necesidad de abrirlos, sin tener que programar Vba, sin SQL, en pocos pasos y con facilidad.

En nuestro ejemplo deben cumplirse dos condiciones:

1 - los archivos deben tener la misma estructura (número y tipo de columnas);
2 - los archivos se encuentran en la misma carpeta.

Supongamos tres archivos con las ventas de una empesa imaginaria

Nuestra misión en consolidar los datos de los tres años en una única tabla que luego podremos analizar con tablas dinámicas, mejor aún, con PowerPivot.

Empezamos toda la carpeta a la ventana del Power Query

obteniendo este resultado en la ventana del editor del Power Query

Además de los tres de ventas, la carpeta contiene otros archivos así que filtramos la tabla dejando visibles sólo los archivos con la extensión ".xlsx"

Eliminamos todos los campos en la tabla excepto "Content" (Binary ) y "Name". Una de las tantas cosas buenas del Power Query es que podemos seleccionar las columnas que queremos dejar y elegir la opción Remove Other Columns (eliminar las otras columnas)

muy práctico cuando queremos eliminar la mayor parte de las columnas en el ventana.

Ahora viene el paso crítico en el proceso: vamos a crear una nueva columna, "Datos", definida con la fórmula =Excel.Workbook([Content],true)

¿Qué es esta expresión "=Excel.Workbook([Content],true)"? Power Query tiene su propio lenguaje de funciones (conocido como "M"). La función Excel.Workbook pertenece a la categoría de funciones que permiten acceder a distitnas plataformas de datos y extraerlos en forma de tabla. El valor "true" delsegundo argumento de la función hace que la primer fila de la tabla de datos de la hoja sea usada como encabezamiento de las columnas.

Al escribir la fórmula debemos tomar en cuenta que, a diferencia de las de Excel, en las funciones del lenguaje del Power Query las mayúsculas y minúsculas tienen importancia. Si escribimos =excel.workbook, resultará un error. Siguiendo con nuestro ejemplo, introducimos la fórmula y apretamos OK para crear la nueva columna. Este es el resultado

Ahora expandimos la columna Datos apretando la doble flecha en el lado derecho del encabezado de la columna

Power Query nos ofrece abrir todas las columnas de "Table" pero dejamos sólo la marca en "Data" y apretamos OK

Ahora expandimos la columna "Data"

En este caso vamos a dejar todas las columnas

Los próximos dos pasos son:

# - eliminar las columnas "Content" y  "Name" que, en nuestro caso, no cumplen ya ninguna función;
# - cambiar el tipo de dato de la columna Fecha de "any" (general) a "Date" (fecha)

El última paso es pasar (load) la tabla que hemos creado a una hoja de Excel o crear sólo una conexión

Si elegimos la primer opción (Close and Load), Excel exporta la tabla a una hoja del cuaderno

Excel y el problema de los separadores (definiciones regionales)

En más de una oportunidad a lo largo de la historia de este blog me he topado con el problema de los separadores y las definiciones regionales. En ciertos países se usa la coma para separar los miles  y el punto para los decimales mientras que en otros el uso se invierte.
Lo mismo sucede con los separadores de filas y columnas que usamos en las constantes matriciales. Si nos atenemos a la ayuda de Excel usaremos la coma para crear una constante matricial orientada horizontalmente

y el punto y coma para una orientada verticalmente

Sin embargo, en ciertas definiciones regionales, Excel usa el caracter "\" como separador.

Para saber qué caracteres usa Excel para los distintos separadores podemos usar una macro para exponer los valores de la propiedad Application.International. Esta tabla muestra los índices de los distitntos valores

El código para mostrar los valores en un MessageBox es el siguiente

 Sub mostrar_separadores()  
 Dim strSep As String  
 With Application  
   strSep = "Separador de elementos de matriz alternativo =" & .International(xlAlternateArraySeparator) & vbCrLf  
   strSep = strSep & "Separador de Columna =" & .International(xlColumnSeparator) & vbCrLf  
   strSep = strSep & "Separador Decimal =" & .International(xlDecimalSeparator) & vbCrLf  
   strSep = strSep & "Separador de Lista =" & .International(xlListSeparator) & vbCrLf  
   strSep = strSep & "Separador de Fila =" & .International(xlRowSeparator) & vbCrLf  
   strSep = strSep & "Separador de Miles =" & .International(xlThousandsSeparator) & vbCrLf  
   MsgBox (strSep)  
 End With  
 End Sub  

Al correr el código veremos este mensaje

Las funciones FILA() y COLUMNA()

Las funciones FILA() y COLUMNA() de Excel son de ese tipo de funciones que a primera vista parecen superfluas. FILA() da como resultado el número de fila de una referencia: COLUMNA() hace lo mismo en relación al número de columna de la referencia

Es decir, si ponemos en un celda =FILA(A4) el resultado será 4; si ponemos =FILA(), sin referencia, el resultado sera el número de fila de la celda que ocupa la fórmula.

El verdadero valor de esta funciones aparece cuando queremos crear series (vectores) de númenos enteros sucesivos lo que hacemos con una constante matricial.

En la nota sobre cálculo de vencimientos en día hábil usamos un constante matricial en esta fórmula

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6 span="">

(nótese el uso de "{" y "}" para crear la constante matricial y el ";" para indicar el sentido del vector). Esta notación tiene el problema de las definiciones regionales; en cierta definiciones se usa el "\" en lugar del punto y coma.
La función FILA supera este problema, ya que es independiente de las definiciones regionales:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6 span="">

Pero también esta notación tiene sus problemas ya que si copiamos la fórmula, la referencia cambia. Si ponemos en la celda A2 la fórmula =FILA(1:7), al copiarla a A3 veremos =FILA(2:8).

La solución es usar la función INDIRECTO de esta manera =FILA(INDIRECTO("1:7"))

Nótese que usamos comillas en la expresión "1:7", ya que INDIRECTO funciona con texto, no con valores numéricos.

La función FILAS()

He pasado una semana agitada. Todo empezó con un intimidante correo electrónico del departamento de informática que rezaba: "¡Urgente! Todos los laptops deben pasar una revisación. Traerlos de inmediato al laboratorio de informática". Todo en letras tamaño catástrofe. Sucede que algunos inocentes usuarios en la empresa habían caido en las garras del virus Ransom, ese que encripta los archivos del disco duro y exige rescate para quitar la codificación.
Tendría que haber fotografiado la cara del técnico cuando vio que tenía instalado Windows 10. Pero, ¿quién te autorizó a instalarlo?. Más que una pregunta era una acusación. Poniendo mi mejor cara de inocente le pregunté cuál era el problema. Me farfulló algo que no terminé de entender pero la sentencia fue terminante: te vamos a formatear el disco e instalar de nuevo el Windows 7, como si el disco duro fuera parte de mi organismo, carne de mi carne. Inútiles que fueron mis ruegos y súplicas pasé el resto de la semana reinstalando los programas que fueran despiadamente eliminados de mi laptop.Y toda este historia es para explicar por qué este post será mínimo pero espero que útil.

¿Recuerdan la nota sobre las funciones raramente usadas de Excel? A la lista de la nota, y de otras notas posteriores sobre las funciones CELDA, CARACTER y CODIGO, podemos agregar la función FILAS().

FILAS() da como resultado el número de filas de una referencia a un rango de filas o matriz

La utilidad de esta función es mucho mayor de lo que se desprende de la escueta información y la he utilizado en varias técnicas que aparecen en este blog.

Supongamos este ejemplo, basado en un anállisis que tuve que realiza para el departamento de mercadeo de mi organización. Tenemos una lista de órdenes de clientes en un rango que hemos definido como Tabla

Ahora supongamos que queremos calcular el número de filas en la tabla. Las técnica "tradicional" sería crear un nombre definido que se refiera a una fórmula con la función CONTARA. Pero si una de las celdas del rango esta vacía, el resultado será erróneo. La función FILAS() usada junto con la tabla soluciona este problema

La celda N3 contiene la fórmula =CONTARA(Tabla_Northwind[[#Todo],[Cliente]]) pero como la celda B5 está vacía el resultado es 19.
La celda N4 contiene la fórmula =FILAS(Tabla_Northwind[#Todo]) y dá el resultado correcto.

Si queremos calcular el porcentaje de órdenes de cada país del total de órdenes en la tabla podemos usar la fórmula =FILAS(Tabla_Northwind[#Datos])  como en la celda N2 en la imagen de abajo (y de paso vemos las ventajas del lenguaje estructurado de las tablas)

Las celdas N5 a N9 contienen la fórmula  =CONTAR.SI(Tabla_Northwind[Pais],M5)/FILAS(Tabla_Northwind[#Datos])

Al usar Tabla_Northwind[#Datos] la fila de los encabezados no es tomada en cuenta. Y por supuesto, ya que estamos usando tablas, a medida que agreguemos o eliminemos filas de la tabla, las fórmulas se adaptarán automáticamente.

Una curiosidad, no tan curiosa, es que FILAS() no crea una referencia circular también si la celda que contiene la fórmula es parte del rango de la referencia

Todo lo dicho aquí se aplica, por supuesto, a la función hermana COLUMNAS().

FILAS() y COLUMNAS() tienen dos primas cercanas, FILA() y COLUMNA() de las que hablaré en un futuro post.

Buen fin de semana.