3 jun3 Min. de lectura

Tabla resultados a partir de tabla de problemas

Para el siguiente ejemplo, tenemos unos continentes, unos años, y, unas ventas correspondientes para cada continente y año.

Debemos de crear un reporte, o, informe donde en la primera columna deben de aparecer todos los continentes, pero un continente por año, aunque no haya tenido ventas, si no ha tenido ventas, ponemos el valor de 0, por ejemplo, para el año 2011, solo tenemos los continentes Europe, y, Asia con ventas, pero deben de aparecer todos los continentes.

El modelo debe de quedar como sigue:

Empecemos…

En la celda E2, usamos LET, creamos una variable, nos traemos los valores únicos del rango A2:A12, creamos otra variable, nos traemos los valores únicos del rango B2:B12.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12)

Creamos otra variable, usamos la función INDICE, como argumento matriz, ponemos la variable “a”, como argumento número de fila, usamos la función SECUENCIA, como argumento filas, ponemos 5, como argumento numero de columna, volvemos a usar la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 4.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12);c;INDICE(a;SECUENCIA(5);SECUENCIA(;4));c)

¿Por qué en el argumento numero de fila, en la función SECUENCIA, ponemos 5?

Porque 5 son los continentes que deben de aparecer por años.

¿Por qué en el argumento número de columna, en la función SECUENCIA, ponemos 4?

Porque son los años que tenemos.

Tenemos una matriz desbordada de cinco filas, y, cuatro columnas, donde en la primera columna, tenemos los continentes, en el resto de las celdas, tenemos error.

Donde aparece error, deben de aparecer los continentes de la primera columna, vamos a crear otra variable, usamos la función SI.ERROR, como argumento valor, ponemos la variable “c”, como argumento valor si error, tomamos la primera columna de la variable “c”.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12);c;INDICE(a;SECUENCIA(5);SECUENCIA(;4));d;SI.ERROR(c;TOMAR(c;;1));d)

Aceptamos, y, donde había error, aparece el continente de la misma fila.

Creamos otra variable, usamos la función INDICE, como argumento matriz, ponemos la variable “b”, como argumento numero de fila, usamos la función SECUENCIA, como argumento filas, ponemos 4, como argumento numero de columna, volvemos a usar la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 4.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12);c;INDICE(a;SECUENCIA(5);SECUENCIA(;4));d;SI.ERROR(c;TOMAR(c;;1));e;INDICE(b;SECUENCIA(4);SECUENCIA(;4));e)

Tenemos una matriz desbordada de cuatro filas y cuatro columnas, donde en la primera columna, tenemos los años únicos, en el resto de las celdas, tenemos error.

Creamos otra variable, usamos la función SI.ERROR, como argumento valor, ponemos la variable “e”, como argumento valor si error, tomamos la primera columna de la variable “e”, después, transponemos los resultados, para que los años aparezcan en el orden correcto.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12);c;INDICE(a;SECUENCIA(5);SECUENCIA(;4));d;SI.ERROR(c;TOMAR(c;;1));e;INDICE(b;SECUENCIA(4);SECUENCIA(;4));f;TRANSPONER(SI.ERROR(e;TOMAR(e;;1)));f)

Donde había error, aparece el año de la fila correspondiente.

Pero, debemos de añadir una fila más, porque hay cinco continentes, y, tenemos solo cuatro filas de años, pues, creamos otra variable, usamos la función APILARV, como argumento matriz1, ponemos la variable “f”, como argumento matriz2, tomamos la primera columna de la variable “f”.

Ya tenemos las cinco filas con los años.

Lo siguiente va a ser formar el modelo, donde en la primera columna en vertical deben de aparecer los continentes, y, en la segunda columna en vertical, deben de aparecer los años, para ello, creamos otra variable, usamos la función APILARV, como argumento matriz1, usamos la función ENCOL, como argumento matriz, transponemos la variable “d”.

Si probamos en este momento, tenemos los continentes en vertical.

Añadimos el argumento matriz2 de APILARV, volvemos a usar ENCOL, y, transponemos la variable “g”.

Ya tenemos el modelo.

Creamos otra variable, y, transponemos el rango A2:C12.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos si la primera columna de la variable “h” es igual a la primera fila de la variable “i”.

=LET(a;UNICOS(A2:A12);b;UNICOS(B2:B12);c;INDICE(a;SECUENCIA(5);SECUENCIA(;4));d;SI.ERROR(c;TOMAR(c;;1));e;INDICE(b;SECUENCIA(4);SECUENCIA(;4));f;TRANSPONER(SI.ERROR(e;TOMAR(e;;1)));g;APILARV(f;TOMAR(f;1));h;APILARH(ENCOL(TRANSPONER(d));ENCOL(TRANSPONER(g)));i;TRANSPONER(A2:C12);j;SI(TOMAR(h;;1)=TOMAR(i;1)

Como argumento valor si verdadero, preguntamos si la ultima columna de la variable “h” es igual a la segunda fila de la variable “i”.

Como argumento valor si verdadero del segundo condicional SI, tomamos la tercera fila de la variable “i”.

Como argumento valor si falso de los dos condicionales, ponemos blanco.

Aceptamos, y, tenemos la venta para cada continente.

Creamos otra variable, usamos la función BYROW, como argumento array, ponemos la variable “j”, como argumento función, ponemos LAMBDA, declaramos una variable, con la función K.ESIMO.MAYOR nos traemos el primer valor de la variable “x”.

Ya tenemos las ventas en vertical.

Usamos el argumento calculo de LET, usamos la función APILARH, como argumento matriz1, ponemos la variable “h”, como argumento matriz2, ponemos la variable “k”.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Tabla resultados a partir de tabla de problemas

Entradas Recientes

Comments