jueves, 22 de septiembre de 2022

Power Pivot: Tablas como VAR

Hoy veremos como en Power Pivot-DAX podemos asignar tablas como variables, y cómo trabajar con dichas tablas.

Tiempo atrás publiqué algunos conceptos importantes sobre la definición de variables (con VAR) que deberías releer ;-)

En la entrada de hoy veremos un par de ejemplos de cómo conseguir crear nuestras 'tablas temporales' (cargadas como variables), y trabajar sobre ellas como con 'cualquier otra tabla física' (con las limitaciones que en sí conlleva este método.).

Partiremos de un conjunto de tres tablas en una hoja de cálculo ('TblDATOS','TblPRECIOS' y 'TblDESC'). Tablas que habremos cargado al Modelo de datos (desde la Ficha Power Pivot > botón Agregar al modelo de datos).
Para a continuación relacionar entre sí, desde la ventana de Vista de diagrama ya dentro del Editor de Power Pivot:
Power Pivot: Tablas como VAR

Fundamental siempre definir las relaciones existentes correctamente!!.

Crearemos a continuación algunos ejemplos similares donde conseguimos segmentar y calcular únicamente las ventas brutas del vendedor 'JUAN' a los clientes definidos como 'VIP'.
Hasta llegar a un informe de tabla dinámica como el que sigue:
Power Pivot: Tablas como VAR


Primer ejemplo. Añadimos una medida con la siguiente fórmula:
 PrimerIntento:=
VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan" )
RETURN
SUMX(filtroVIP_Juan; [Unidades vendidas]*RELATED(TblPRECIOS[precio]))

Power Pivot: Tablas como VAR


En la primera asignación de variable 'filtroVIP_Juan' con VAR, cargamos la tabla devuelta con FILTER. Esta función nos devuelve como resultado una tabla completa, con las filas resultantes de aplicar el filtro sencillo o múltiple.
Posteriormente iteramos con SUMX las filas de nuestra 'tabla temporal', multiplicando las Unidades por sus precios relacionados, acumulando dicho producto.

Un caso sencillo 👍

Vamos con un segundo ejemplo un poco más elaborado...
Creamos una nueva medida con la fórmula:
testVAR:=
VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan")	--22405 reg
VAR FIN=NATURALINNERJOIN(filtroVIP_Juan;TblPRECIOS)		--22405 reg

VAR Vtas_EUR=SUMX(FIN;[Unidades vendidas]*[precio])		--recorremos las 22405 filas
RETURN
Vtas_EUR

Power Pivot: Tablas como VAR

Analicemos la fórmula.
La primera asignación de variable 'filtroVIP_Juan' con VAR cumple las mismas expectativas que el caso anterior...
En este caso, hacemos uso de la tabla temporal para con la función NATURALINNERJOIN, obtener una nueva tabla.
VAR FIN=NATURALINNERJOIN(filtroVIP_Juan;TblPRECIOS)
NATURALINNERJOIN que devuelve una nueva tabla resultante de'combinar' la primera (tabla izquierda) con la segunda (tabla derecha), incluyendo solo filas para las cuales los valores en las columnas comunes especificadas están presentes en ambas tablas.
La tabla devuelta tendrá las columnas comunes de la tabla de la izquierda y otras columnas de ambas tablas.
Obtenemos una tabla con las columnas necesarias para aplicar el siguiente cálculo:
VAR Vtas_EUR=SUMX(FIN;[Unidades vendidas]*[precio])
donde de nuevo hacemos referencia a una tabla temporal... y como expresión, que itera por esa 'tabla', utilizamos campos de ésta!!.

Último ejemplo, con el mismo objetivo de cálculo.
Nueva medida con fórmula:
TESTvar3:=VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan")		--22405 reg
VAR tbl=SUMMARIZE(filtroVIP_Juan;  TblDATOS[artículo];TblPRECIOS[precio])	--10 reg
VAR Vtas_EUR=SUMX(tbl;
			SUMX(CALCULATETABLE(TblDATOS;TblDATOS[Tipo venta]="VIP" ; TblDATOS[Vendedor]="Juan");
			TblDATOS[Unidades vendidas]*tblPRECIOS[precio]))
//22405 x 10 = 224.050 iteraciones/cálculos
RETURN
	Vtas_EUR

Power Pivot: Tablas como VAR


Otros dos intentos más con medidas...
Otro1:=VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan" )
RETURN
SUMX(filtroVIP_Juan; [Unidades vendidas]*RELATED(TblPRECIOS[precio]))


Otro2:=VAR filtroVIP_Juan=CALCULATETABLE(TblDATOS;TblDATOS[Tipo venta]="VIP" ; TblDATOS[Vendedor]="Juan")	--22405 reg
RETURN
SUMX(ADDCOLUMNS(filtroVIP_Juan; "Precio"; RELATED(TblPRECIOS[precio]));
			[unidades vendidas]*[Precio])


Como se puede comprobar, la versatilidad de esta tablas temporales es muy alta.. pero OJO!!!, no siempre podrás utilizarlas...
Funciones como ALL, ALLEXCEPT, ... requieren de 'tablas reales-existentes' para su correcto funcionamiento.

Un ejemplo de asignación de tabla como variable fallido:
Fallido:=VAR tbl=FILTER(TblDATOS; TblDATOS[Tipo venta]="VIP")
VAR FIN = CALCULATE([Valor_Eur]; ALLEXCEPT(tbl; tbl[Vendedor]))
//VAR FIN2=CALCULATE([Valor_Eur];ALLEXCEPT(TblDATOS;TblDATOS[Tipo venta]))	--sí funciona al usar Tablas existentes
RETURN
FIN


martes, 20 de septiembre de 2022

Power Pivot: Optimizar tiempos de cálculo

Hoy veremos cómo el complemento DAX Studio nos puede ayudar a entender y luego mejorar nuestros tiempos de cálculo respecto de las medidas y/o columnas calculadas.
Repasaremos dentro del complemento dos conceptos comentados en una entrada anterior: SE y FE (motor de almacenamiento y motor de procesamiento).

Para mostrar los diferente tiempos empleados, partiremos de un ejemplo sencillo con tres tablas:
TblDATOS (campos: 'Vendedor', 'artículo', 'Tipo venta' y 'Unidades vendidas') y 200.000 registros
TblPRECIOS (campos: 'artículo', 'precio' y 'Descuento_ART') y 15 registros
TblDESC_VIP (campos: 'Tipo' y 'Descuento_VIP') y 3 registros
Power Pivot: Optimizar tiempos de cálculo

Esto es, clásico ejemplo para calcular nuestras ventas netas.
Comenzaremos Agregando las tres tablas al Modelo de datos de Power Pivot en nuestro libro de Excel.
Desde la Ficha de Power Pivot y presionando el botón 'Agregar al modelo de datos'.

Ya dentro del editor de Power Pivot, accederemos a la Vista de Diagrama donde arrastraremos los campos comunes entre las tres tablas, creando así las relaciones necesarias:
Power Pivot: Optimizar tiempos de cálculo


Vamos con los cálculos y sus medidas de tiempo...

Empezaremos con una columna calculada que nos devuelva fila a fila el cálculo requerido:
unidades x precio x (1-descuento artículo) x (1 - descuento vip)

Veamos este caso dentro del complemento DAX Studio.
Obvio que ya lo tienes descargado e instalado y disponible en la Ficha complementos de Excel.
Power Pivot: Optimizar tiempos de cálculo


Al presionar el botón de DAX Studio se nos abrirá el enlace de conexión a nuestro libro de trabajo:
Power Pivot: Optimizar tiempos de cálculo


Y se nos abrirá una ventana de código donde escribir nuestra estructura de DAX...

En la ventana escribiremos lo siguiente para replicar el efecto de crear una columna calculada en el modelo de datos:
EVALUATE
//añadimos una nueva columna a la TblDATOS
ADDCOLUMNS(TblDATOS,
		"Precio_descontado",	-- como producto de las unidades x precio x descto_vip x desc_art
		TblDATOS[Unidades vendidas]*
		RELATED(TblPRECIOS[precio])*
		(1-RELATED(TblDESC[Descuento_VIP]))*
		(1-RELATED(TblPRECIOS[Descuento_ART])))

Power Pivot: Optimizar tiempos de cálculo

Activaremos el Server Timings en la Ficha 'Home' dentro de DAX y a continuación ejecutaremos la consulta creada (también puedes presionar F5).
Esto iniciará unos contadores de tiempos y algunas estadísticas sobre la ejecución de la consulta.
Una vez finalizado el proceso no olvides presionar de nuevo el botón de Server Timings con el fin de parar los contadores...
En este caso, nuestra consulta de creación de columna calculada, nos reporta estos datos:
Power Pivot: Optimizar tiempos de cálculo


Veamos los datos...
Tiempo total en milisegundos: 5.203 ms (esto son 5,2 segundos... lo que no está nada mal... recuerda son 200.000 registros calculando datos a partir de otras dos tablas!!).
Además nos dice cuál ha sido el consumo de motores (SE y FE). En este caso todo ha sido motor de procesamiento!!.
Pero espera... todos los manuales nos dicen que crear columnas consume más motor de almacenamiento??.
Bueno, recuerda lo que decíamos en un artículo previo, mejor compruébalo... no des nada por sentado ;-)
También interesante (si conoces algo de SQL) es que esta ventana nos ofrece el código SQL que hay detrás de nuestra consulta.... pero eso es otra guerra.

Como resumen nos quedamos que tardamos 5,2 segundos en crear una nueva columna con 200.000 nuevos cálculos.

Y qué pasa si creamos una medida que replique el cálculo de esa columna??. Veámoslo.
En DAX añadimos una nueva ventana para otra query, donde añadiremos el siguiente código DAX:
DEFINE --definimos el cálculo de una medida
MEASURE TblDATOS[PrecioDescontado1]=	--con una doble iteración
SUMX(TblDESC,		--primero recorremos la tblDESC
	SUMX(TblPRECIOS,	--y para cada valor de la tblDESC, recorremos cada fila de tblPRECIOS
		VAR descVIP=TblDESC[Descuento_VIP]
		VAR descART=TblPRECIOS[Descuento_ART]
		VAR EUR=[Valor_Eur]		--además en cada ocasión exigimos se ejecute otra medida (con sus itereaciones)
		RETURN
		EUR*(1-descVIP)*(1-descART)	)	)
		
EVALUATE
//creamos un resumen simple donde se opera con nuestra medida
//se trata de no interferir demasiado en el cálculo
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [PrecioDescontado1])

Power Pivot: Optimizar tiempos de cálculo


Repetimos el proceso para conocer las estadíticas de esta consulta... Y obtenemos lo siguiente:
Power Pivot: Optimizar tiempos de cálculo

Veamos los datos...
Tiempo total en milisegundos: 11 ms (sin comentarios :OO ).
Consumo de motores (FE: 4 ms y SE:7 ms )
Poco ahi que decir... sin embargo, el procedimiento empleado no parece el más óptimo, si nuestras tablas de dimensiones fueran de mayor envergadura, ya que dobles iteraciones nunca son buenas, puesto que exigen cálculos y repeticiones exponenciales, por ejemplo, un doble bucle de 200.000 registros, sobre una segunda tabla de 1.000 registros, exigiría 200.000 x 1.000 = 200.000.000 de cálculos!!

Un truco que aprendí de mi colega Alberto Ferrari (de SQLBI) viendo alguno de sus vídeos, es resumir a valores únicos los datos, para evitar una 'granularidad' alta (que haya muchos datos diferentes a evaluar).
Podríamos entonces emplear SUMMARIZE para tener una 'tabla reducida' a partir de la cual trabajar.
Vamonos de nuevo a DAX y creamos una nueva ventana de código, donde insertaremos un nuevo código.
La idea es la misma de antes... primero definimos una medida, para luego aplicarla en forma de resumen.
El código DAX en esta ocasión es:
DEFINE MEASURE 
TblDATOS[Importe_descontado]=		--definimos el cálculo de una medida
SUMX(
	ADDCOLUMNS(
			//summarize nos devuelve una tabla de tres columnas, con las combinaciones únicas
			//generadas entre los elementos únicos de esas columnas
			//en lugar de trabajar sobre los 200.000 registros!!
			SUMMARIZE(TblDATOS, TblDESC[Descuento_VIP],TblPRECIOS[Descuento_ART],TblDATOS[Vendedor]), 
				"IMPORTE",[Valor_Eur]),	-- al resultado anterior le añadimos una columna con la medida Valor_eur
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))	--sobre la tabla anterior aplicamos una iteración
														--con SUMX acumulando valores con la expresión indicada

EVALUATE
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [Importe_descontado])

Power Pivot: Optimizar tiempos de cálculo


Repetimos una vez más el proceso de controlar tiempos con el botón Server Timings y vemos lo siguiente:
Power Pivot: Optimizar tiempos de cálculo

O sea tenemos los siguientes valores:
Tiempo total en milisegundos: 3 ms (:OOOOO cómooooo ).
Consumo de motores (FE: 3 ms y SE:3 ms )

Bueno, no nos debería sorprender... era lo esperado. Al evitar esos dobles-triples bucles, los tiempos deben ser menores.. si además reducimos los datos sobre los que trabajar, aún más...
Aún así, brutal la enseñanza de Alberto Ferrari.. gracias!!.

Solo hemos visto algunos de los aspecto relevantes de esas estadísticas, pero hay otros puntos igualmente importantes, como el número de subconsultas que se generan en los procesos de cálculo (SE queries), o la caché, etc...

Ya comprobado cual es la mejor opción, nos iremos al editor de Power Pivot e implantaremos la medida elegida.
En el área de cálculo, añadimos la medida:
Precio_descontado2:=SUMX(ADDCOLUMNS(SUMMARIZE(TblDATOS; TblDESC[Descuento_VIP];TblPRECIOS[Descuento_ART];TblDATOS[Vendedor]); 
				"IMPORTE";[Valor_Eur]);
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))

Power Pivot: Optimizar tiempos de cálculo


Ya podríamos usar en nuestra hoja de cálculo, una tabla dinámica asociada al modelo de datos, que mostrara el cálculo que queríamos para cada vendedor...
Power Pivot: Optimizar tiempos de cálculo

jueves, 15 de septiembre de 2022

Power Pivot: Columnas calculadas o Medidas

Si algo caracteriza el trabajo con el modelo de datos-DAX (bien en Power BI bien en Power Pivot) es la eterna cuestión de qué manera de trabajar es la recomendada: Columnas calculadas o Medidas.

La respuesta rápida, y habitual, es mejor las medidas... pero como todo en la vida, no hay certezas absolutas.

Si bien es cierto que las medidas , puesto que no se almacenan en memoria, generalmente son más rápidas, es importante conocer y evaluar el equilibrio entre utilizar el 'almacenamiento en memoria' o el motor de procesamiento' que consumimos al utilizar una medida o una columna calculada.

Estos dos conceptos son interesantes, y te recomendaría echaras un vistazo a este gran video.

Estos dos motores SE (motor de almacenamiento) y FE (motor de procesamiento o formulación), que se pueden analizar fácilmente con ese magnifico complemente DAX Studio (https://daxstudio.org/), serán la clave y la respuesta real, en cada caso concreto de qué es más óptimo.
No voy a intentar definir o profundizar por ahora más en este tema... Nos quedamos con la idea que existen dos motores, y cada uno sirve y se emplea para una cosa...

Grosso modo, las columnas calculadas son buenas porque exigen poca memoria (FE) cuando generamos nuestras consultas o informes. Sin embargo, por contra, ocupan más espacio de almacenamiento (SE) en la base de datos.

De otra parte, las medidas se crean, cada vez, en el instante que se ejecuta la consulta (más FE), por lo que no quedan almacenadas en la base de datos (menos SE).

La recomendación general sería por tanto:
Medidas - cuando necesitemos calcular agregados/acumulados, i.e., cálculos más complejos.
columnas calculadas - cuando tengamos que evaluar información por filas, i.e., procesos más rutinarios.

Veamos un ejemplo comparando un cálculo similar realizado en una columna calculada, y otro mediante una medida.
Disponemos de tres tablas en nuestra hoja de cálculo
Power Pivot: Columnas calculadas o Medidas

Cargaremos directamente al Modelo de datos las tres tablas (podemos usar desde la ficha Power Pivot el botón Agregr al modelo de datos).
Una vez en el modelo procedemos, desde la vista de diagrama a relacionar la tabla principal (tabla de hechos), con la tabla auxiliar (o dimensiones) de precios mediante el campo común de 'artículo'
Power Pivot: Columnas calculadas o Medidas

La tabla de categorías según el escalado de unidades vendidas NO la relacionaremos... optando por realizar una búsqueda por aproximación dentro del modelo
Para esto, siguiendo recomendaciones previas, y ya que requerimos evaluar fila a fila el número de unidades vendidas, crearemos una columna calculada con la siguiente fórmula:
=CALCULATE(MIN(TblCAT[Categoría]);FILTER(TblCAT;TblCAT[de]<=TblDATOS[Ventas_Año(UDS)] && TblDATOS[VIP]=""))

con la fórmula recuperamos la categoría mínima (fíjate que están ordenadas en la TblCAT en descendente!!; si estuvieran en ascendente usaríamos MAX).
Recuperaremos la categoría mínima cuando se den al mismo tiempo las dos condiciones expresadas con la función FILTER. Lo que nos devuelve:
Power Pivot: Columnas calculadas o Medidas


El siguiente cálculo lo haremos empleando una medida, ya que responderá a un agregado por suma del valor resultante de multiplicar unidades por precios. Nuestra fórmula:
Valor_Eur:=SUMX(TblDATOS;TblDATOS[Ventas_Año(UDS)]*RELATED(TblPRECIOS[precio]))

clásica fórmula de SUMX que se apoya con RELATED en datos de otra tabla previamente relacionada!!

Con los cálculos y relaciones ya ejecutadas, llegamos al momento de comparación.
Vamos a realizar un cálculo con el que obtener, en nuestro informe de tabla dinámica, por cada categoría y producto el acumulado total de cada categoría... lo vemos en la imagen siguiente:
Power Pivot: Columnas calculadas o Medidas

Veamos el cálculo correspondiente a la columna calculada, que llamaré 'Acum_Ventas_byCAT':
=VAR ventas=CALCULATE([Valor_Eur];ALLEXCEPT(TblDATOS;TblDATOS[Categoría]))
RETURN
IF(TblDATOS[Categoría]="";0;ventas)

Power Pivot: Columnas calculadas o Medidas

La fórmula opera sobre la medida anterior creada, obviando los posibles filtros aplicados a excepción de los aplicados sobre el campo CATEGORIA. Es decir, opera (suma agregada que nos devolvía la medida 'Valor_eur') fila por fila y retorna el dato acumulado correspondiente a la CATEGORIA de esa fila...

De otra parte replicamos el cálculo similar al anterior pero en forma de medida. La medida la llamaré: 'Suma_por_Categoria':
Suma_por_Categoria:=CALCULATE(
              SUMX( FILTER(TblDATOS;[Categoría]<>"");[Valor_Eur]);
              ALLEXCEPT( TblDATOS;TblDATOS[Categoría] ) )


donde de forma similar trabajamos sobre aquellos elementos filtrados donde exista CATEGORIA, acumulando su 'Valor_eur', con la misma excepción de filtros... esto es manteniendo aplicado la CATEGORIA, para que precisamente acumule por ese concepto.

Podemos crear nuestro informe de tabla dinámica de acuerdo a la imagen siguiente:
Power Pivot: Columnas calculadas o Medidas

Observamos como a nivel de combinación Artículo y Categoría obtenemos iguales resultados, pero a nivel de subtotal por categoría la cosa cambia.
La columna calculada acumula sus parciales, como cualquier Subtotal...
Mientras que la medida devuelve el mismo dato que sus parciales... como haría un campo calculado estándar.

No nos importa para el caso... si empleando DAX Studio realizaramos un análisis de rendimiento veríamos con sorpresa que en este caso es más eficiente la columna calculada!!
Ya veremos el proceso en otro artículo.

Esto es muy interesante por que demuestra que dependiendo de la tipología del cálculo a realizar es más óptimo un motor u otro (SE frente a FE), es decir, columnas calculadas versus medidas.

martes, 13 de septiembre de 2022

Nombres definidos ocultos

Hoy hablaremos de un problema con el que me he enfrentado recientemente, donde cuando trataba de guardar hojas de un libro Excel me advertía de la existencia de Funciones-macro 4.0 (que no eran visibles y desde luego yo no había creado!!!)
Nombres definidos ocultos


Un colega, Charles Willians, me recomendó usar el complemento de Jan Karel Pieterse, en el cual colaboró.
Su complemento/Add-in 'Name manager' lo puedes encontrar en: https://jkp-ads.com/excel-name-manager.asp

Pero sobre todo me dió la pista por donde empezar... 'busca nombres definidos ocultos'.
Me acordé entonces de una publicación de hace muchos años atrás (Ocultar y Mostrar los nombres definidos) que resolvería el problema.

Así pues añadí la macro siguiente en un módulo del libro 'problemático':
Sub Localiza_Nombres_Ocultos()
Dim ndNombreDef As Name

For Each ndNombreDef In ThisWorkbook.Names
    If ndNombreDef.Visible = False Then
        If InStr(1, ndNombreDef.Name, "_xl") > 0 Then
            Debug.Print ndNombreDef.Name & "_" & ndNombreDef.Value
            ndNombreDef.Visible = True   ' para hacerlo visible...
         End If
    End If
Next ndNombreDef

End Sub


La sorpresa es el listado de 'nombres ocultos' que aparecieron listados!!
_xlfn._xlws.FILTER_=#NAME?
_xlfn.ANCHORARRAY_=#NAME?
_xlfn.CONCAT_=#NAME?
_xlfn.COUNTIFS_=#NAME?
_xlfn.IFERROR_=#NAME?
_xlfn.SUMIFS_=#NAME?
_xlfn.TEXTJOIN_=#NAME?
_xlfn.UNIQUE_=#NAME?
_xlpm.miFuncion1_=#NAME?
_xlpm.miFuncion2_=#NAME?
_xlpm.miFuncion3_=#NAME?

¿Qué son esos prefijos (_xlfn. _xlws o _xlpm)?.

El primero: _xlfn es el clásico prefijo que aparece cuando una función está trabajando en una versión previa, donde no existía.
Por ejemplo, si habrimos la función SUMAR.SI.CONJUNTO (SUMIFS) en una versión de Excel 2007, Excel mostrará en las celdas donde aparezcan dichas funciones, ese prefijo... además del valor resultado.

El segundo prefijo que vemos es _xlws que identifica algunas de las nuevas/últimas funciones lanzadas por Microsoft (FILTRAR, ORDENAR, ....), y con idéntico significado.

El tercer prefijo que aparece es _xlpm que nos refiere a las funciones LAMBDA, que hayamos podido generar...

Por todo lo leído en diferente documentación, parece ser este fallo se puede producir al abrir el archivo de trabajo con versiones anteriores.

Por suerte, una vez identificado ese listado, y lanzada la macro anterior, ya tendríamos a nuestra disposición en el Adminstrador de nombres definidos dichos nombres que deberemos eliminar.
Una vez borrados el problema desaparece!!!

También podríamos haber añadido en la programación de la macro la acción .Delete sobre el objeto ndNombreDef.

Cómo exactamente ha ocurrido esto y por que los identifica como funciones macro 4.0 es dificil de entender... pero al menos tenemos una solución (al menos en mi caso sirvió).

Un punto importante para comenzar a revisar un fichero, cuando algo empieza a fallar en él es revisar el Inspector de documentos.
Accederemos a esta herramienta desde la ficha Archivo > Información > Inspeccionar libro
Nombres definidos ocultos

Observamos un listado de utilidades, herramientas, funciones, etc. usadas en el libro presente!!!
Por ejemplo, fíjate en la imagen anterior (corresponde en parte al fichero problemático del ejemplo), como aparece listado 'Nombres ocultos'.

Sin duda una primera aproximación al problema... pero, si además presionamos el botón desplegable de 'Comprobar si hay problemas' > 'Inspeccionar documento', nos surgirá una ventana donde optar por analizar diferentes puntos de un libro:
Tablas dinámicas y segmentaciones
Gráficos dinámicos
Fórmulas de cubo
Escalas de tiempo (en caché)
Datos XML personalizados
Comentarios y anotaciones
Propiedades del documento e información personal
Encabezados y pies de página
Filas y columnas ocultas
Nombres y hojas de cálculo ocultos
Contenido invisible
Funciones de datos y vínculos externos
Encuestas de Excel
Propiedades de la hoja de cálculo personalizadas
(entre otras...)
Nombres definidos ocultos


Al Aceptar (botón Inspeccionar) los términos de ese listado del Inspector obtenemos unos resultados o validaciones en dicha ventana del Inspector del libro, donde decidiremos qué hacer:
Nombres definidos ocultos


Una herramienta muy útil y sobre todo sencilla, que nos permite empezar a buscar problemas...

jueves, 8 de septiembre de 2022

Anexar todas las tablas de un libro con funciones

Un nuevo ejemplo de potencial de Excel es la respuesta, empleando solo funciones, a anexar todas las tablas de un mismo libro de manera automática.

Una posible solución a este problema es emplear Power Query, como ya se publicó en este artículo del blog.

Hoy conseguiremos el mismo resultado pero empleando algunas de 'catorce funciones':
AJUSTARFILAS, DIVIDIRTEXTO o ENFILA
Junto a otras igual de potentes como:
LET, UNIRCADENAS, MAP o LAMBDA

Te recomiendo leas previamente, antes de continuar, el artículo siguiente, donde se desarrolla un ejercicio que nos servirá para alcanzar nuestra meta.

Adicionalmente deberás entender el uso de las funciones macro 4.0 (leer artículo).

El uso de una función macro 4.0 particular es fundamental.
La función NAMES - NOMBRES(nombre_documento/archivo; tipo; criterio)
La función devuelve un vector, ordenado alfabéticamente, con los nombres definidos incluidos en el libro de trabajo descrito...
Sabiendo que:
-si omitimos el primer argumento, trabajaremos sobre el libro activo.
-el argumento tipo admite tres valores:
1-solo lista 'nombres normales'
2-nombres definidos ocultos
3-todos los nombres
-el tercer argumento de criterio, que permite el uso de comodines, permite identificar los nombres a recuperar

Llegados a este punto es importante resaltar un aspecto clave...
La función macr 4.0 'NOMBRES' trabaja exclusivamente sobre nombres definidos, NO sobre tablas (tiene sentido por que las Tablas aparecieron mucho después de la existenca/descatalogación de las macro 4.0)...
Asi que para que el trabajo se realice correctamente, a cada tabla habrá que asignarle un nombre definido!!
Un pequeño precio a pagar...

Vamos con el ejercicio...Partimos de dos tablas iniciales: TblUNO y TblDOS, a las cuales he asignado sendos nombres definidos: datosUNO y datosDOS respectivamente...
Notemos el patrón tomado a la hora de asignar nombres definidos, incluyendo un prefijo igual en ambas tablas (esto es clave).
Anexar todas las tablas de un libro con funciones


Todo comienza creando una función LAMBDA personalizada que trabaje con la función macro 4.0 'NOMBRES'. Por tanto abrimos nuestro Administrador de nombres definidos e incluimos la siguiente fórmula, renombrándola como 'fxNAMES':
=LAMBDA(criterio;NOMBRES(;3;criterio))
Anexar todas las tablas de un libro con funciones


Si utilizamos esta función directamente en una celda, por ejemplo en H2 y escribimos:
=TRANSPONER(fxNAMES("datos*"))&T(AHORA())
tendríamos un vector vertical 'volátil' con los nombres definidos en el libro activo que comiencen por el prefijo 'datos'...

Comprobado el resultado de esta función, ya podemos montar nuestra fórmula en E2 para obtener una matriz resultante de anexar las tablas-nombres definidos deseados:
=LET(rngs;TRANSPONER(fxNAMES("datos*"))&T(AHORA());
datos;UNIRCADENAS(";";;MAP(rngs;
                           LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO( INDIRECTO(tbl);0) ))));

AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));2;"-"))

Versión reducida de la comentada en el artículo arriba indicado

La fórmula recorre los elementos obtenidos con nuestra función macro 4.0 (nombres definidos que empiecen por 'datos') para descomponerlos en sus distintos elementos con MATRIZATEXTO, trasladarlo a un vector fila (con la función ENFILA).
La función MAP se encarga de recorrer los distintos vectores obtenidos con ENFILA (un vector por cada nombre definido).
Y UNIRCADENAS nos permite concatenar en un único vector todos los elementos... Será finalmente DIVIDIRTEXTO quien 'separe' esa cadena de texto en un nuevo y único vector que AJUSTARFILAS devolverá en una matriz de dos columnas.

La fórmula se podría completar incluyendo encabezados o incluso fila de totales. Te remito al artículo anterior.

Puedes comprobar que al añadir nuevas tablas: 'TblTRES' y 'TblCUATRO', habiéndoles asignados nombres definidos: datosTRES y datosCUATRO, automáticamente aparecerán anexados!!
Anexar todas las tablas de un libro con funciones


Increible solución sin salir del entorno de la hoja de cálculo!!