Un tema que siempre me ha interesado en Excel es poder anexar un número de tablas indeterminado con funciones.. si además, podía incluir un encabezado y una fila de totales (todo formulado), y además pudiera elegir las columnas a incorporar en el anexado final, uno de mis sueños se hubiera cumplido...
Pues tras el 'lanzamiento de las catorce', jugando con ellas, conseguí una de esas fórmulas 'largas' que no te dejará indiferente.
Aún siendo consciente de que puede mejorarse, he optado por emplear prioritariamente alguna de las catorce nuevas funciones, frente a otras igual de válidas... pero como siempre quedo abierto a cualquier corrección o mejora ;-)
Imagina un número de tablas indeterminado... (para el ejemplo trabajaremos con tres tablas: TblES, TblDE y TblIT).
Y que tenemos un listado (llamado 'anexadas') con los nombres de las tablas que queremos anexar... Y en una segunda tabla una lista ('TblOrden') con los campos que deseamos mostrar en el listado final.
Una sola función que recopila la mayoría de las nuevas catorce nos permite obtener el resultado de la imagen!!.
En J2 escribimos:
Un poco larga, sin duda, pero notemos que realiza un trabajo portentoso... comienza añadiendo un encabezado, seguido de una secuencia de filas, de acuerdo a los campos seleccionados, para N tablas diferentes...!!!
para terminar incorporando una última fila de totales al final de nuestro resultado.
Salvaje :OO
Si repasamos la fórmula en custión obtenemos un listado de las funciones empleadas:
clásicas: INDIRECTO, LARGO, CONTARA, SUMA, COINCIDIR, TRANSPONER, REPETIR o EXTRAE
No creo que ninguna necesite explicación... quizá INDIRECTO que nos permite interpretar cadenas de texto propuestas como referencias (Tablas en nuestro ejemplo).
funciones 'desbordadas' y LAMBDA: LET, MAP, MATRIZATEXTO o UNIRCADENAS
Especial atención a MATRIZATEXTO que permite devolve en modo secuencia de caracteres una matriz...
y dentro del grupo de las catorce nuevas: ENFILA, EXCLUIR, ELEGIRCOLS, ELEGIRFILAS, AJUSTARFILAS, DIVIDIRTEXTO, APILARH y APILARV
No están las catorce.. por si vas a contar jeje... pero casi ;-)
Qué hacemos con nuestra fórmula.
En primer lugar obtenemos la fila de encabezados a partir de la tabla 'TblOrden' que contiene precisamente eso, los campos que queremos mostrar:
LET(encab;TRANSPONER(TblOrden);
La segunda línea es la más compleja, ya que se encarga de anexar las columnas indicadas de todas las tablas listadas. Dentro de la función LET:
datos;UNIRCADENAS(";";;MAP(anexadas;LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO(EXCLUIR(ELEGIRCOLS(INDIRECTO(tbl&"[#Todo]");COINCIDIR(TblOrden;ELEGIRFILAS(INDIRECTO(tbl&"[#Todo]");1);0));1));1))));
Lo más profunda de esta parte de la fórmula:
ELEGIRCOLS(INDIRECTO(tbl&"[#Todo]");COINCIDIR(TblOrden;ELEGIRFILAS(INDIRECTO(tbl&"[#Todo]");1);0))
trabajando sobre cada Tabla de información recupera la primera fila, que con COINCIDIR junto con ELEGIRCOLS nos devuelve precisamente las columnas deseadas... (en el orden adecuada, además), dejando fuera las NO coincidentes.
EXCLUIR sobre ese resultado deja fuera la primera fila que contiene los encabezados... y que no necesitamos.
Esa matriz, ya sin encabezado y solo con las columnas deseadas, la convertimos en una secuencia de elementos con MATRIZATEXTO, lo que en conjunto con ENFILA, y bajo el contexto de la función LAMBDA de MAP, tenemos la secuencia de datos para cada tabla a anexar...
Secuencia que terminamos concatenando con UNIRCADENAS.
Retorcido y complicado... y seguro con una resolución más directa.
Las siguientes etapas son más sencillas.
El siguiente paso dentro de LET es:
calc;AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));CONTARA(encab);"-");
donde nos aprovechamos de DIVIDIRTEXTO para limpiar los valores retornados...
OJO, ya que la función de texto devuelve valores de texto!!
Con la matriz de tablas anexadas 'limpia' de espacios sobrantes... continuamos dentro de LET para, en el siguiente paso, obtener la suma total que en su momento añadiremos en la última fila:
calcTotal;SUMA(--ELEGIRCOLS(calc;3));
de la matriz de datos anterior, nos quedamos con la columna tres, indicada a mano!, que nos dice la posición de la columna 'Uds' a sumar.
Podríamos haber automatizado este valor... pero me pareció excesivo y que no aportaba demasiado valor.
Y en el penúltimo paso montamos la fila de totales:
filaTotal;APILARH(DIVIDIRTEXTO("Total"&REPETIR("\";CONTARA(TblOrden)-2);"\");calcTotal);
donde APILARH nos ayuda a obtener una matriz/vector fila con los datos que necesitamos...
Un 'Total' en la primera columna, n-2 columnas vacías (REPETIR se encarga de eso..), y finalmente, el dato de suma anteriormente calculado.
Solo nos queda unir todo lo anterior. Lo hacemos con APILARV:
APILARV(encab;calc;filaTotal)
donde unimos tres matrices de igual número de columnas...
Pues tras el 'lanzamiento de las catorce', jugando con ellas, conseguí una de esas fórmulas 'largas' que no te dejará indiferente.
Aún siendo consciente de que puede mejorarse, he optado por emplear prioritariamente alguna de las catorce nuevas funciones, frente a otras igual de válidas... pero como siempre quedo abierto a cualquier corrección o mejora ;-)
Imagina un número de tablas indeterminado... (para el ejemplo trabajaremos con tres tablas: TblES, TblDE y TblIT).
Y que tenemos un listado (llamado 'anexadas') con los nombres de las tablas que queremos anexar... Y en una segunda tabla una lista ('TblOrden') con los campos que deseamos mostrar en el listado final.
Una sola función que recopila la mayoría de las nuevas catorce nos permite obtener el resultado de la imagen!!.
En J2 escribimos:
=LET(encab;TRANSPONER(TblOrden);
datos;UNIRCADENAS(";";;MAP(anexadas;
LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO(
EXCLUIR(ELEGIRCOLS(INDIRECTO(tbl&"[#Todo]");
COINCIDIR(TblOrden;ELEGIRFILAS(INDIRECTO(tbl&"[#Todo]");1);0));1));1))));
calc;AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));CONTARA(encab);"-");
calcTotal;SUMA(--ELEGIRCOLS(calc;3));
filaTotal;APILARH(DIVIDIRTEXTO("Total"&REPETIR("\";CONTARA(TblOrden)-2);"\");calcTotal);
APILARV(encab;calc;filaTotal))Un poco larga, sin duda, pero notemos que realiza un trabajo portentoso... comienza añadiendo un encabezado, seguido de una secuencia de filas, de acuerdo a los campos seleccionados, para N tablas diferentes...!!!
para terminar incorporando una última fila de totales al final de nuestro resultado.
Salvaje :OO
Si repasamos la fórmula en custión obtenemos un listado de las funciones empleadas:
clásicas: INDIRECTO, LARGO, CONTARA, SUMA, COINCIDIR, TRANSPONER, REPETIR o EXTRAE
No creo que ninguna necesite explicación... quizá INDIRECTO que nos permite interpretar cadenas de texto propuestas como referencias (Tablas en nuestro ejemplo).
funciones 'desbordadas' y LAMBDA: LET, MAP, MATRIZATEXTO o UNIRCADENAS
Especial atención a MATRIZATEXTO que permite devolve en modo secuencia de caracteres una matriz...
y dentro del grupo de las catorce nuevas: ENFILA, EXCLUIR, ELEGIRCOLS, ELEGIRFILAS, AJUSTARFILAS, DIVIDIRTEXTO, APILARH y APILARV
No están las catorce.. por si vas a contar jeje... pero casi ;-)
Qué hacemos con nuestra fórmula.
En primer lugar obtenemos la fila de encabezados a partir de la tabla 'TblOrden' que contiene precisamente eso, los campos que queremos mostrar:
LET(encab;TRANSPONER(TblOrden);
La segunda línea es la más compleja, ya que se encarga de anexar las columnas indicadas de todas las tablas listadas. Dentro de la función LET:
datos;UNIRCADENAS(";";;MAP(anexadas;LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO(EXCLUIR(ELEGIRCOLS(INDIRECTO(tbl&"[#Todo]");COINCIDIR(TblOrden;ELEGIRFILAS(INDIRECTO(tbl&"[#Todo]");1);0));1));1))));
Lo más profunda de esta parte de la fórmula:
ELEGIRCOLS(INDIRECTO(tbl&"[#Todo]");COINCIDIR(TblOrden;ELEGIRFILAS(INDIRECTO(tbl&"[#Todo]");1);0))
trabajando sobre cada Tabla de información recupera la primera fila, que con COINCIDIR junto con ELEGIRCOLS nos devuelve precisamente las columnas deseadas... (en el orden adecuada, además), dejando fuera las NO coincidentes.
EXCLUIR sobre ese resultado deja fuera la primera fila que contiene los encabezados... y que no necesitamos.
Esa matriz, ya sin encabezado y solo con las columnas deseadas, la convertimos en una secuencia de elementos con MATRIZATEXTO, lo que en conjunto con ENFILA, y bajo el contexto de la función LAMBDA de MAP, tenemos la secuencia de datos para cada tabla a anexar...
Secuencia que terminamos concatenando con UNIRCADENAS.
Retorcido y complicado... y seguro con una resolución más directa.
Las siguientes etapas son más sencillas.
El siguiente paso dentro de LET es:
calc;AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));CONTARA(encab);"-");
donde nos aprovechamos de DIVIDIRTEXTO para limpiar los valores retornados...
OJO, ya que la función de texto devuelve valores de texto!!
Con la matriz de tablas anexadas 'limpia' de espacios sobrantes... continuamos dentro de LET para, en el siguiente paso, obtener la suma total que en su momento añadiremos en la última fila:
calcTotal;SUMA(--ELEGIRCOLS(calc;3));
de la matriz de datos anterior, nos quedamos con la columna tres, indicada a mano!, que nos dice la posición de la columna 'Uds' a sumar.
Podríamos haber automatizado este valor... pero me pareció excesivo y que no aportaba demasiado valor.
Y en el penúltimo paso montamos la fila de totales:
filaTotal;APILARH(DIVIDIRTEXTO("Total"&REPETIR("\";CONTARA(TblOrden)-2);"\");calcTotal);
donde APILARH nos ayuda a obtener una matriz/vector fila con los datos que necesitamos...
Un 'Total' en la primera columna, n-2 columnas vacías (REPETIR se encarga de eso..), y finalmente, el dato de suma anteriormente calculado.
Solo nos queda unir todo lo anterior. Lo hacemos con APILARV:
APILARV(encab;calc;filaTotal)
donde unimos tres matrices de igual número de columnas...
