Ejemplos5

Para este ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente. Dicho modelo esta en formato de tabla.

Lo primero, que quiero, es poner una letra, por ejemplo, la letra “s”, y, que me devuelva todas las provincias que contengan dicha letra.

Voy a usar la función EXTRAE, para encontrar la letra en la columna provincia, pero la pregunta, en el argumento posición inicial de la función EXTRAE, ¿Cuántos son los caracteres que he de extraer?

Pues, para ello, en la cela I11, vamos a hacer uso de la función SECUENCIA, junto con la función LARGO, lo vamos a hacer en una celda aparte, para ver que función correctamente.

Entonces, escribo el signo igual, seguido de la función SECUENCIA, y, abro un paréntesis.

=SECUENCIA(

Como argumento filas, ponemos 1.

=SECUENCIA(1;

Como argumento columnas, usamos la función LARGO aplicada a la celda B2.

=SECUENCIA(1;LARGO(B2);

Omitimos el argumento inicio, y, el argumento paso.

Cerramos paréntesis, y, aceptamos.

=SECUENCIA(1;LARGO(B2))

Tenemos una matriz desbordada empezando desde el número 1, hasta la última posición de la letra, en este caso, la provincia es Granada.

Vamos a usar la función SECUENCIA como el argumento posición inicial de la función EXTRAE.

Escribimos el signo igual, seguido de la función EXTRAE, y, abrimos un paréntesis.

=EXTRAE(

Como argumento texto, señalamos la columna de provincia.

=EXTRAE(Table1[Provincia];

Como argumento posición inicial, ponemos la función SECUENCIA, pero, debemos de modificar el argumento columnas de la funcion SECUENCIA, debemos de usar la longitud máxima de la columna provincia, es decir, la provincia con la longitud mayor, para ello, en el argumento de LARGO, seleccionamos la columna de provincia, y, antes de LARGO, usamos la funcion MAX, para extraer la provincia de mayor longitud.

=EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]))

Como argumento número de caracteres, ponemos 1, cerramos paréntesis, y, aceptamos.

=EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)

Obtenemos una matriz desbordada, con las provincias, pero, en cada celda aparece cada letra de la provincia.

Lo siguiente va a ser preguntar, con el condicional SI, que si una de las letras, es igual, por ejemplo, a la letra “s”, que me devuelva la fila, en caso contrario, que devuelva un texto en blanco.

=SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"")

Tenemos una matriz desbordada con las filas donde la letra “s”, y, blanco donde no hay coincidencia, pero esos espacios en blanco nos van a estorbar, porque queremos que aparezcan los números de filas de forma secuencial, sin espacios en blanco.

Para solventar este problema, vamos a usar la función K.ESIMO.MENOR, para ir obteniendo cada valor menor, pero lo haremos a través de la función AGREGAR, donde podemos ignorar celdas vacías, que es lo que nos interesa.

Pues, escribimos el signo igual, seguido de la función AGREGAR, y, abrimos un paréntesis, se abre una ventana para elegir la operación, pues, elegimos K.ESIMO.MENOR.

Punto y coma, en la siguiente ventana, señalamos omitir filas ocultas.

Punto y coma, el argumento matriz, es la función anterior.

=AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;LARGO(B2);1);1)="s";FILA(Table1[Provincia]);"")

Punto y coma, como argumento K de la función K.ESIMO.MENOR, vamos a poner el total de filas, así no tendremos problemas, pero, lo haremos con la función SECUENCIA, para que nos genere una matriz en vertical desde el numero 1 hasta la última celda ocupada, para ello, usamos la funcion CONTARA, y, como argumento seleccionamos la columna de provincia, aunque, podemos seleccionar cualquier otra columna.

Cerramos paréntesis, y, aceptamos.

=AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia])))

Pues, ya tenemos los números de filas de las provincias que contienen la letra “s”.

Lo siguiente es usar la función INDICE, para extraer el valor de la columna provincia, donde la expresión anterior, es el argumento número de fila.

=INDICE(B:B;AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia]))))

Obtenemos una matriz desbordada con las provincias que contienen la letra “s”, pero, después de la ultima provincia, tenemos errores, pues, vamos a usar la funcion LET, creamos una variable, y, almacenamos la expresión anterior.

=LET(a;INDICE(B:B;AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia]))))

Como argumento calculo de LET, filtramos la variable “a”, siempre que dicha variable no devuelva un error, por lo que usamos la funcion NO, y, como argumento usamos la funcion ESERROR, y, como argumento ponemos la variable “a”.

=LET(a;INDICE(B:B;AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia]))));FILTRAR(a;NO(ESERROR(a))))

Nos quedamos con los valores unicos.

=LET(a;INDICE(B:B;AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)="s";FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia]))));UNICOS(FILTRAR(a;NO(ESERROR(a)))))

Lo siguiente es calcular el total para cada provincia, para ello, vamos a usar la función SUMAR.SI, como argumento rango, seleccionamos la columna de columnas, como argumento criterio, seleccionamos las provincias únicas, y, como argumento rango de suma, seleccionamos la columna de total.

=SUMAR.SI(Table1[Provincia];I11#;Table1[Total])

Tenemos el total para cada provincia.

Lo siguiente va a ser calcular el total mayor vendido para cada provincia, para ello, vamos a usar la función MAX.SI.CONJUNTO.

El primer argumento es rango máximo, que será la columna de total.

=MAX.SI.CONJUNTO(Table1[Total];

El siguiente argumento es rango criterios, que es la columna de provincias.

=MAX.SI.CONJUNTO(Table1[Total];Table1[Provincia];

El ultimo argumento es criterio, donde escribimos la celda donde se encuentra la primera provincia, seguido del operador de rango derramado (#), para que así sea dinámico.

Cerramos paréntesis, y, aceptamos.

=MAX.SI.CONJUNTO(Table1[Total];Table1[Provincia];I11#)

Ya tenemos el total de cada provincia, si ponemos otra letra, veremos como también aparecen los totales, gracias al operador de rango derramado.

Lo siguiente que queremos es que nos devuelva la fecha de ese total.

En este caso voy a usar la función BUSCARX, donde el argumento valor buscado, será la celda K11, junto con el operador de rango derramado, el argumento matriz donde buscar, es la columna de total, y, como argumento matriz devuelta, es la columna de fecha.

Sería como sigue:

=BUSCARX(K11#;Table1[Total];Table1[Fecha])

Ya lo tenemos.

Vamos al argumento prueba lógica del condicional SI, después del signo igual, cambiamos la letra “s” por la celda J8.

=LET(a;INDICE(B:B;AGREGAR(15;5;SI(EXTRAE(Table1[Provincia];SECUENCIA(1;MAX(LARGO(Table1[Provincia]));1);1)=J8;FILA(Table1[Provincia]);"");SECUENCIA(CONTARA(Table1[Provincia]))));UNICOS(FILTRAR(a;NO(ESERROR(a)))))

Seguimos trabajando con el modelo que uso habitualmente.

Queremos calcular los totales para cada provincia, y, cada centro comercial.

Lo primero que voy a hacer es unir las columnas provincia, y, centro con la función APILARH, este ejemplo, volveré a hacerlo con la versión en inglés, para poder usar las nuevas funciones.

En la celda F2, escribimos el signo igual, seguido de la función APILARH, y, abrimos un paréntesis.

=APILARH(

Como argumento matriz1, seleccionamos la columna de provincia.

=APILARH(Table1[Provincia]

Como argumento matriz2, seleccionamos la columna de centro.

Cerramos paréntesis y aceptamos.

=APILARH(Table1[Provincia];Table1[Centro])

Tenemos una matriz desbordada de dos columnas, que son provincia, y, centro.

Vamos a calcular el total para cada provincia, y, centro, para ello, vamos a usar la funcion SUMAR.SI.CONJUNTO, como argumento rango de suma seleccionamos la columna de total del modelo, como argumento rango criterios1, seleccionamos la columna de provincia, como argumento criterio1, usamos la funcion TOMAR, como argumento matriz, seleccionamos la celda A2 junto con el operador de rango derramado de la hoja ejemplo, omitimos el argumento filas, como argumento columnas, ponemos 1, vamos a tomar la primera columna de la matriz desbordada (A2#).

=SUMAR.SI.CONJUNTO(Table1[Total];Table1[Provincia];TOMAR(EJEMPLO!A2#;;1)

Como argumento rango criterios2, seleccionamos la columna de centro, como argumento criterio2, volvemos a usar la funcion TOMAR, como argumento matriz, seleccionamos la celda A2 junto con el operador de rango derramado, omitimos el argumento filas, como argumento columnas, ponemos -1, es decir, vamos a tomar la ultima columna de la matriz desbordada (A2#).

=SUMAR.SI.CONJUNTO(Table1[Total];Table1[Provincia];TOMAR(EJEMPLO!A2#;;1);Table1[Centro];TOMAR(A2#;;-1))

Ya tenemos el total por provincia, y, centro.

Vamos a ver un ejemplo, de cómo podemos obtener los valores que se duplican dentro de un modelo.

Trabajamos con el modelo que suelo trabajar, pero con menos filas, donde hay filas que se repiten, por ejemplo, la siguiente fila, se repite dos veces más, al igual, que otras provincias.

Lo vamos a realizar en varios pasos.

En una celda, tenemos que preguntar si en el rango A2:A2, es igual al valor de A2, donde fijamos el primer A2, quedaría, $A$2:A2=A2.

Si miramos el modelo, el valor de la referencia A2, es Granada, estamos comparando Granada consigo mismo, por lo que nos devuelve VERDADERO.

En la misma celda, usamos la función CONTAR.SI.CONJUNTO, como argumento rango criterios1, es $A$2:A2, como argumento criterio1, seleccionamos la celda A2.

=CONTAR.SI.CONJUNTO($A$2:A2;A2)

Nos devuelve 1, porque de momento aparece una vez, arrastramos, y, vemos las veces que aparece cada provincia, fijémonos en la segunda función (F3).

=CONTAR.SI.CONJUNTO($A$2:A3;A3)

En este caso, va a contar desde el rango A2:A3, que son las provincias de Granada, y, Madrid, y, pregunta si algún valor es igual al de la celda A3, que es Madrid, como hay una coincidencia, también, nos devuelve 1.

Ahora, miremos la siguiente expresión (F11), siendo su resultado el número 3.

=CONTAR.SI.CONJUNTO($A$2:A11;A11)

El valor de la celda A11, es Granada, entonces, está contando las veces que aparece Granada en el rango A2:A11, que son tres.

Pues esto es lo que hace con el resto de las provincias.

Pero, para encontrar un registro duplicado, debemos de comparar cada celda consigo mismo, es decir, lo que hemos hecho anteriormente, debemos de hacerlo con B2, C2, y, D2, por lo que debemos de añadir mas condiciones a la funcion CONTAR.SI.CONJUNTO.

Como argumento rango criterios2, seleccionamos B2:B2, y, fijamos la primera referencia, como argumento criterio2, ponemos la celda B2.

=CONTAR.SI.CONJUNTO($A$2:A2;A2;$B$2:B2;B2

Como argumento rango criterios3, ponemos $C$2:C2, como argumento criterio3, ponemos la celda C2.

=CONTAR.SI.CONJUNTO($A$2:A2;A2;$B$2:B2;B2;$C$2:C2;C2

Como argumento rango criterios4, ponemos $D$2:D2, como argumento criterio4, ponemos la celda D2, cerramos paréntesis.

=CONTAR.SI.CONJUNTO($A$2:A2;A2;$B$2:B2;B2;$C$2:C2;C2;$D$2:D2;D2)

Aceptamos, arrastramos, y, vemos las veces que aparece cada registro.

El que aparece, una sola vez, es un registro que no se repite, por lo que en la celda de al lado, voy a preguntar que si el resultado de la función CONTAR.SI.CONJUNTO, es mayor a 1, me devuelva la fila, en caso contrario, voy a poner un texto en blanco.

Lo haremos de forma matricial en la celda G2.

=SI(F2:F25>1;FILA(F2:F25);"")

Obtenemos una matriz desbordada, con las filas donde hay coincidencias, y, blanco, donde no la hay.

Necesito ordenar estas filas de menor a mayor, para anulas los blancos, para ello, voy a usar la función K.ESIMO.MENOR, esta función tiene dos argumentos, el primero de ellos, es el rango con el que vamos a trabajar, y, el segundo argumento, es el número menor que queremos que nos devuelva, el primero, segundo…

La pregunta es ¿Cuántos números me tiene que devolver?, porque estos valores pueden cambiar.

En la celda H2, voy a contar los números de dicha matriz, para ello, voy a usar la función CONTAR.

=CONTAR(G2#)

Nos devuelve 6, quiere decir que la función K.ESIMO.MENOR, tiene que devolver los 6 primeros números, pero, el resultado de CONTAR, lo debo de convertir en una matriz de seis números, empezando desde el número 1, para usarlo, en el argumento K de la función K.ESIMO.MENOR.

Para ello, después del signo igual, voy a escribir la función SECUENCIA, y, abro un paréntesis, solo voy a usar el argumento fila, que es la función CONTAR.

=SECUENCIA(CONTAR(G2#))

Tenemos una matriz desbordada desde el numero 1 hasta el número 6.

Ya tenemos el argumento K, la función completa quedaría:

=K.ESIMO.MENOR(G2#;SECUENCIA(CONTAR(G2#)))

Tenemos los números de filas ordenados de menos a mayor.

Voy a usar la función INDICE, para traerme estas filas del modelo.

El argumento array, son las columnas desde A hasta D.

=INDICE(A:D

Como argumento número de fila, es la función K.ESIMO.MENOR.

=INDICE(A:D;K.ESIMO.MENOR(G2#;H2#)

Para el argumento número de columna, solo podemos especificar una columna, pero tenemos que especificar que nos devuelva las cuatro columnas, por lo que voy a usar una constante de matriz, como sigue:

=INDICE(A:D;K.ESIMO.MENOR(G2#;H2#);{1\2\3\4})

Tenemos una matriz desbordada con las filas.

Pero, aun se repiten, por lo que voy a hacer uso de la función UNICOS.

=UNICOS(INDICE(A:D;K.ESIMO.MENOR(G2#;SECUENCIA(CONTAR(G2#)));{1\2\3\4}))

Ya tenemos los registros que se repiten dentro del modelo.

En el siguiente ejemplo, tenemos unas fechas del mes de enero, del año 2017, y, 2018.

Las ventas del mes de enero del año 2017 llegan hasta el día 18, y, las ventas del mes de enero del año 2018 llegan hasta el día 21.

Quiero comparar los días del mes de enero, es decir, hasta el día 18, con los días del mes de enero del año 2018, pero también, hasta el día 18.

Lo primero que voy a hacer, es extraer el último día de venta del mes de enero del año 2017, pues no lo sabemos.

Para ello, voy a preguntar en la celda E2, que, si el año del modelo es igual a 2017, que me devuelva la columna de fecha, en caso, contrario, que me devuelva un texto en blanco.

Usare la función TEXTO, para extraer el año de la columna fecha, pero el resultado de la función TEXTO, es texto, por lo que delante de la función TEXTO, usare la función VALOR, para que me devuelva el resultado en formato de número.

=SI(VALOR(TEXTO(Tabla2[Fecha];"aaaa"))=2017;Tabla2[Fecha];"")

Obtenemos una matriz desabordada con las fechas del año 2017, en este caso, solo tenemos las ventas del mes de enero.

Ahora, me voy a quedar con la fecha máxima, con la función MAX, de esta manera, este será el último día para buscar en el año 2018.

=MAX(SI(VALOR(TEXTO(Tabla2[Fecha];"aaaa"))=2017;Tabla2[Fecha];""))

Obtenemos el día 18 de enero de 2017.

Voy a calcular el total en la celda F2, para ello, escribo el signo igual, abro un paréntesis, donde pregunto, si las fechas de la columna total, es menor al 18 de enero de 2017.

=(Tabla2[Fecha]<=D2)

Obtengo una matriz desbordada con VERDADERO, donde se cumple la condición, y, FALSO, donde no se cumple.

Pongo el símbolo de asterisco, vuelvo a abrir un paréntesis, y pongo la columna de total, quiere decir que donde pone VERDADERO, pondrá el valor de la columna total.

=(Tabla2[Fecha]<=D2)*(Tabla2[Total])

Obtenemos una matriz desbordada con los totales.

Usamos la función SUMA, para obtener el total.

=SUMA((Tabla2[Fecha]<=D2)*(Tabla2[Total]))

Ahora, vamos por el año 2018 en la celda E3, vamos a verificar si la fecha 18/01/2018 se encuentra en el modelo, lo primero que vamos a hacer es crear la fecha, para ello, usamos la funcion FECHA, como argumento año, usamos la funcion AÑO, como argumento seleccionamos la celda E2 y sumamos 1, como argumento mes, usamos la funcion MES, como argumento ponemos la celda E2, como argumento día, ponemos la funcion DIA, como argumento ponemos la celda E2.

=FECHA(AÑO(E2)+1;MES(E2);DIA(E2))

Ya tenemos la fecha a buscar, para ello, usamos la funcion BUSCARV, como argumento valor buscado, es la funcion FECHA, como argumento matriz_tabla, seleccionamos la columna de fecha, como argumento número de columna, ponemos 1, como tipo de coincidencia, ponemos aproximada.

=BUSCARV(FECHA(AÑO(E2)+1;MES(E2);DIA(E2));Tabla2[Fecha];1;VERDADERO)

Nos devuelve la fecha 17/01/2018, porque la fecha 18/01/2018 no existe.

En la celda F3, escribo el signo igual, abro un paréntesis, y, pregunto con la función DIA, si el día de la columna fecha, es igual, o, menor al día de la fecha obtenida anteriormente.

=(DIA(Tabla2[Fecha])<=DIA(E2)

Obtengo una matriz desbordada, con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

La siguiente condición para añadir, es que el año de la columna fecha, la cual, usaremos la función AÑO, debe de ser igual al año de la fecha obtenida.

=(DIA(Tabla2[Fecha])<=DIA(E2))*(AÑO(Tabla2[Fecha])=AÑO(E3))

Obtenemos una matriz desbordada, donde vemos que pone 1, donde hay coincidencia, que son las fechas desde el día 1 de enero del 2018 al día 17 de enero del 2018.

Vamos a añadirle que nos devuelva la columna de total, y, usamos la función SUMA, para obtener el total.

=SUMA((DIA(Tabla2[Fecha])<=DIA(E2))*(AÑO(Tabla2[Fecha])=AÑO(E3))*(Tabla2[Total]))

Y ya lo tenemos.

Vemos que desde el día 1 de enero hasta el día 17 de enero del año 2018, se ha vendido más que para el periodo de 2017.

Tenemos los datos de todo el año 2017, donde, vamos a crear un cuadro con los nombres de los meses, para ello, en la celda E1, voy a usar la función TEXTO, para extraer el nombre del mes de la columna fecha.

=TEXTO(Table16[Fecha];"mmmm"))

Voy a quedarme con los valores únicos, por lo que uso la función UNICOS.

=UNICOS(TEXTO(Table16[Fecha];"mmmm")))

Por último, transpongo con la función TRANSPONER.

=TRANSPONER(UNICOS(TEXTO(Table16[Fecha];"mmmm"))))

Vamos a calcular en la celda E2, cual fue el último día de venta de cada mes, para ello, voy a preguntar si el mes de la columna fecha, es igual al valor de la celda E1, es decir, enero, que me devuelva el día de la columna fecha.

=SI(TEXTO(Table1[Fecha];"mmmm")=E1;DIA(Table1[Fecha])

Obtengo una matriz desbordada con los días del mes de enero, y, FALSO donde no hay coincidencia.

Pues, ahora me quedo con el día máximo, para ello, uso la función MAX.

=MAX(SI(TEXTO(Table1[Fecha];"mmmm")=E1;DIA(Table1[Fecha]);""))

Ya tengo el día máximo para el mes de enero.

Seleccionamos la función, pulsamos CTRL más C para copiar, seleccionamos desde F2 a P2, botón alternativo del ratón, y, seleccionamos pegar formula.

Ya tenemos el último día de venta para cada mes.

Ahora, vamos a poner un estado para cada mes, donde si no se ha realizado ventas hasta el último día del mes, que ponga incompleto, en caso contrario, que ponga completo, lo haremos en la celda E3.

Si uso la función SECUENCIA, donde uso el argumento columnas, donde pongo 12, y, como inicio pongo 1.

=SECUENCIA(;12;1)

Me crea una matriz desbordada de doce columnas, empezando desde el número 1, hasta el número 12.

Ahora, voy a usar la función FECHA, donde como argumento año, pongo 2017, como argumento mes, es la función SECUENCIA anterior, y, como argumento día, ponemos 1.

=FECHA(2017;SECUENCIA(;12;1);1)

Obtengo una matriz desbordada con el día 1 de cada mes.

Ahora, con la función FIN.MES, voy a traerme el último día de cada mes, esta función tiene dos argumentos, el primero de ellos es la fecha con la que vamos a trabajar, y, el segundo argumento son los meses para adelantar, o, retrasar, en este caso, como voy a trabajar con la fecha de una celda, ponemos cero.

=FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0)

Tenemos el último día de cada mes.

Ahora con la función DIA, me quedo con el día de cada fecha.

=DIA(FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0))

Ahora, resto estos días con la última fecha de venta de cada mes.

=DIA(FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0))-E2:P2

Vemos la diferencia de días, y, cero donde se ha realizado una venta, el último día del mes.

Ahora, preguntamos que, si la diferencia de días es cero, que ponga completo, en caso contrario, que ponga incompleto.

=SI(DIA(FIN.MES(FECHA(2017;SECUENCIA(;12;1);1);0))-E2:P2=0;"Completo";"Incompleto")

Lo siguiente es calcular el total para cada mes en la celda E4, para ello, voy a preguntar si el mes de la columna fecha es igual al valor de la celda E1, es decir, enero, que me devuelva la columna total, en caso contrario, que me devuelva un texto en blanco, y, sumamos el resultado, lo vamos a hacer con la función FILTRAR, como argumento array, seleccionamos la columna de total, como argumento include, nos traemos el mes de la columna fecha, para ello, usamos la funcion TEXTO, como argumento valor, seleccionamos la columna de fecha, como argumento formato, entre comillas dobles, ponemos cuatro “m”, e, igualamos al  valor de la celda E1.

Antes de la funcion FILTRAR, ponemos la funcion SUMA.

=SUMA(FILTRAR(Table16[Total];TEXTO(Table16[Fecha];"mmmm")=E1))

Igual que antes, seleccionamos la función, pulsamos CTRL más C, para copiar, seleccionamos las celdas de los siguientes meses, botón alternativo de ratón, y, seleccionamos pegar formula.

Lo siguiente va a ser crear dos matrices, una donde deben de aparecer los meses completos, y, otra los meses no completos junto con los totales.

Para los meses completos, en la celda D7, voy a usar la función FILTRAR, para que me filtre desde E1 a P1, si el valor del rango E3:P3, es igual al valor de la celda K3 (Completo).

=FILTRAR(E1#;E3#=K3)

Obtenemos una matriz desbordada en horizontal, con los meses, pues usamos TRANSPONER, para ponerlos en vertical.

=TRANSPONER(FILTRAR(E1#;E3#=K3))

Ahora, vamos a calcular los totales, para ello, voy a usar la función BUSCARX, donde el argumento valor buscado, son los meses que hemos obtenido, como matriz de búsqueda, es desde E1 a P1, y, como matriz devuelta, es desde E4 a P4.

=BUSCARX(D7#;E1#;E4:P4)

Ya tenemos los totales.

Para los meses no completos, son las mismas funciones, pero en la función FILTRAR, cambiamos completo, por incompleto.

=TRANSPONER(FILTRAR(E1#;E3#=E3))

Fórmula para el total.

=BUSCARX(D16#;E1#;E4:P4)

En el siguiente ejemplo, tenemos 10 hojas con provincias, queremos crear una lista de provincias, en una hoja nueva, el problema que se presenta es que, aunque, la estructura de todas las hojas son las mismas, la columna de provincias en la hoja de Cádiz se encuentra en otra posición, por ejemplo, en la hoja de Albacete, la columna de provincia se encuentra en la columna 2.

En la hoja de Cádiz, la columna provincia, se encuentra en la columna 3.

En las demás hojas, la columna provincia se encuentra en la columna 2.

Para este ejemplo, debemos de usar referencias 3D.

Si en una celda, escribo el signo igual, señalo la hoja de Albacete, dejo pulsado la tecla SHIFT, o, MAYUSC, y, señalo la ultima hoja, es decir, Cáceres, ahora, señalo la columna de provincia, desde B2 a B100, y, acepto.

=Albacete:Caceres!B2:B100

Me devuelve un error, no es capaz de devolverme la columna B de todas las hojas, pero, podemos usar la función APILARV, y, si nos dará los valores de la columna B de todas las hojas, como sigue:

=APILARV(Albacete:Caceres!B2:B100)

Ahora, con la función UNICOS, nos quedamos con los valores únicos.

=UNICOS(APILARV(Albacete:Caceres!B2:B100))

Tenemos una matriz de valores únicos, pero podemos ver que tenemos provincias, y, productos, porque la columna B en la hoja de Cádiz, es la de productos.

Vamos a intentar solventar este problema.

Voy a apilar en horizontal con APILARH la fila 1, y, 2, desde la columna A hasta la columna G, de todas, las hojas.

=APILARH(Albacete:Caceres!A1:G2)

Tenemos una matriz desbordada con las dos primeras filas de todas las hojas.

Ahora, voy a filtrar la matriz desbordada anterior, siempre que la primera fila sea igual a provincia.

=FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia")

Tenemos una matriz desbordada en horizontal, pero solo los encabezados de columnas.

Ahora, con la función INDICE, le voy a decir que me devuelva la fila 2.

=INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2)

Ya tenemos las provincias, aunque estén colocadas en otras columnas.

Si la queremos en vertical, pues, transponemos.

=TRANSPONER(INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2))

Por último, con la función APILARV, le voy a añadir el encabezado provincias, quiere decir que será dinámico.

=APILARV("Provincias";TRANSPONER(INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2)))

Miguel Angel Franco