Tenemos el modelo con el que suelo trabajar, ventas de productos, en diferentes centros comerciales, provincias, y, fechas.
Queremos saber, cuando fue la primera venta de cada mes.
Vamos a crear el cuadro, vamos a extraer cada mes de cada fecha en vertical, para ello, voy a hacer uso de la función TEXTO, de forma matricial.
En la celda I3, escribimos el signo igual, seguido de la función TEXTO, y, abrimos un paréntesis.
=TEXTO (
Como argumento valor, seleccionamos la columna de fecha.
=TEXTO(Tabla1[Fecha];
Como argumento formato, ponemos cuatro MMMM.
=TEXTO(Tabla1[Fecha];"mmmm"
Cerramos paréntesis, y, aceptamos.
=TEXTO(Tabla1[Fecha];"mmmm")
Tenemos una matriz desbordada, con todos los meses, pero repetidos, por lo que después del signo igual, usamos la función UNICOS.
=UNICOS(TEXTO(Tabla1[Fecha];"mmmm"))
Ahora, tenemos los meses únicos, desde enero a diciembre.
Lo siguiente va a ser construir los años únicos, pero en horizontal, para ello, en la celda J2, escribimos de nuevo el signo igual, seguido de la función TEXTO, y, abrimos un paréntesis.
=TEXTO (
Como argumento valor, volvemos a seleccionar la columna de fechas.
=TEXTO(Tabla1[Fecha];
Como argumento formato, entre comillas dobles, ponemos cuatro AAAA.
=TEXTO(Tabla1[Fecha];"AAAA"
Cerramos paréntesis, y, aceptamos.
=TEXTO(Tabla1[Fecha];"AAAA")
Tenemos una matriz desbordada con los años en vertical, pero repetidos, pues después del signo igual, usamos la función UNICOS.
=UNICOS(TEXTO(Tabla1[Fecha];"AAAA"))
Ahora, usamos la función TRANSPONER, para colocar los años en horizontal.
=TRANSPONER(UNICOS(TEXTO(Tabla1[Fecha];"AAAA")))
Ya tenemos realizado el cuadro.
Pues, empecemos.
Primero, en la celda J3, voy a preguntar si el mes de la columna fecha, es igual al valor de la celda I3, en ese caso, que me devuelva la columna fecha.
=SI(TEXTO(Tabla1[Fecha];"mmmm")=I3;Tabla1[Fecha])
Obtenemos una matriz desbordada, con las fechas que corresponden al mes de enero, pero de todos los años, y, queremos los meses de enero del primer año, que es el año 2017, por lo que debemos de poner una segunda condición, dentro del SI, entonces, después del argumento prueba lógica, vamos a poner esta segunda condición, y, en caso de no haber coincidencia, que ponga un texto en blanco.
=SI(TEXTO(Tabla1[Fecha];"mmmm")=I3;SI(TEXTO(Tabla1[Fecha];"aaaa")=J2;Tabla1[Fecha];"");""))
Aceptamos, y, tenemos las fechas de los meses de enero del año 2017.
Ahora, después del signo igual, vamos a usar la función MIN, para quedarnos con la fecha mínima, y, tenemos la primera venta para el mes de enero, del año 2017.
Ahora, vamos a arrastrar tanto hacia abajo, como hacia la derecha, pero antes, debemos de fijar la columna de la referencia I3, para que, al copiar hacia la derecha, la columna no se actualice, y, la fila de la referencia J2, para que, al copiar hacia abajo, la columna no varie.
=MIN(SI(TEXTO(Tabla1[Fecha];"mmmm")=$I3;SI(TEXTO(Tabla1[Fecha];"aaaa")=J$2;Tabla1[Fecha];"");""))
Seleccionamos la función, pulsamos CTRL mas C, para copiar, seleccionamos todo el rango, y, pulsamos CTRL mas V, para pegar, y, tenemos la primera venta para cada mes de cada año.
Ahora, lo vamos a realizar con la función FILTRAR en la hoja ejemplo2, donde volvemos a traernos los meses únicos en la celda I3, y, los años con los que trabajar en la celda J2.
Este modelo va desde el año 2017 al año 2019.
Vamos a filtrar la columna fecha, por las mismas condiciones usadas anteriormente, pero, para este caso, queremos que nos devuelva la columna fecha, producto, y, total, lo vamos a hacer para los años 2017, 2018, y, 2019.
Como he dicho, FILTRAR, va a tener las mismas condiciones usadas anteriormente, cada condición debe de ir entre paréntesis, separado por el símbolo de asterisco, y, nos va a devolver todas las columnas.
En una celda, ponemos la función FILTRAR como argumento array, es la tabla, como argumento include abrimos un paréntesis para una primera condición, usamos la función TEXTO y nos traemos el mes de la columna fecha, e, igualamos al valor de la celda I3, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, nos traemos el año de la columna fecha e igualamos al valor de la celda J2, cerramos paréntesis de la condición, y, de FILTRAR.
=FILTRAR(Tabla1;(TEXTO(Tabla1[Fecha];"mmmm")=I3)*(TEXTO(Tabla1[Fecha];"aaaa")=J2))
Voy a volver a usar la función FILTRAR, para filtrar la expresión anterior, donde como argumento include, voy a usar una constante de matriz, para indicar que columnas debe de devolver, cero significa que no debe de aparecer, y, 1 que si debe de aparecer.
=FILTRAR(FILTRAR(Tabla1;(TEXTO(Tabla1[Fecha];"mmmm")=$I3)*(TEXTO(Tabla1[Fecha];"aaaa")=J$2));{1\0\0\1\0\0\1})
Ahora, debemos de quedarnos con la fecha mínima, como las fechas están ordenadas, voy a usar la función INDICE, para que me devuelva la primera fila.
=INDICE(FILTRAR(FILTRAR(Tabla1;(TEXTO(Tabla1[Fecha];"mmmm")=$I3)*(TEXTO(Tabla1[Fecha];"aaaa")=J$2));{1\0\0\1\0\0\1});1)
Ya lo tenemos, lo siguiente es copiar la función, y, pegarla.
Colocar productos en su columna
En el siguiente ejemplo, tenemos la venta de productos, en diferentes centros, provincias, y, fechas, pero, este archivo, nos lo han enviado, y, por error, los productos lo han puesto a continuación del último centro comercial, no en la columna de producto, el ejemplo va a consistir, en traernos los productos a la columna de producto.
Todas las provincias corresponden a Sevilla.
Vamos a realizarlo de diferentes formas.
La manera más simple de hacerlo es con la función FILTRAR, para ello, en la celda D2 de la hoja ejemplo1, escribo el signo igual, seguido de la función FILTRAR, y, abrimos un
paréntesis.
=FILTRAR(
Como argumento array, seleccionamos la columna de centro, donde también, tenemos los productos.
=FILTRAR(C2:C377;
Como argumento include, seleccionamos la columna de fecha, y, la igualamos a blanco.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(C2:C377;A2:A189="")
Nos devuelve un error.
Este error es debido a que la longitud de la columna C, es diferente a la longitud de la columna A, por lo que vamos a poner el mismo número de filas de la columna C, a la columna A.
=FILTRAR(C2:C377;A2:A377="")
Y ya tenemos los productos en su sitio.
Para eliminar esos productos de más de la columna de centro, primero, debemos de copiar la función FILTRAR, y, pegarla como valores, para que desaparezcan las funciones, y, ya podemos borrar los productos de la columna centro.
Como he dicho esta es la manera más rápida y simple, vamos a ver otra manera de hacerlo en la hoja ejemplo2, en la celda D2.
Si bajamos hasta donde aparece el ultimo centro, y, aparece el primer producto, podemos ver que empieza en la fila 190.
Quiere decir que he empezar a recupera datos desde la fila 190, para llegar hasta la fila 190, puedo usar la función CONTARA, para que me cuente las celdas alfanuméricas en toda la columna A.
=CONTARA(A:A)
Me devuelve 189, que donde aparece el ultimo supermercado.
Pues, le sumo 1, y, ya tengo la fila de inicio.
=CONTARA(A:A)+1
Lo siguiente que he de saber es hasta donde debe de llegar, sabemos que empieza en la fila 190, pero ¿dónde debe de acabar?, si bajamos hasta la última fila ocupada de la columna centro, vemos que la última fila es la 377.
¿Cómo llegamos hasta dicha fila?
Bien, si uso la función CONTARA, para que me dé un recuento de la columna C.
=CONTARA(C:C)
Obtengo el valor 377, que son las filas ocupadas alfanuméricas de la columna C.
Si vuelvo a usar CONTARA, pero para la columna A.
=CONTARA(A:A)
Obtengo 189, que son las fechas que hay.
Si resto la cantidad mayor, menos la cantidad menor, obtengo 188, quiere decir que hay 188 productos que he de subir.
Ya conocemos la función SECUENCIA, pues vamos a crear una matriz, donde debe debemos de indicar de cuantas filas compondrá la matriz, pues va ser la resta CONTARA(C:C), menos CONTARA(A:A), que es el argumento filas, es decir:
=SECUENCIA(CONTARA(C:C)-CONTARA(A:A)
El argumento columnas, lo voy a omitir, por lo que escribo punto y coma.
=SECUENCIA(CONTARA(A:A)+1;;
El argumento inicio, va a ser la función CONTARA(A:A), mas 1.
Cerramos paréntesis, y, aceptamos.
=SECUENCIA(CONTARA(C:C)-CONTARA(A:A);;CONTARA(A:A)+1)
Tenemos una matriz desbordada en vertical, empezando por 190 hasta 377, que es donde se encuentra el ultimo producto en la columna de centro.
Ahora, con la función INDICE, vamos a indicar que en la columna de centro, vaya al número de fila, devuelto por la función SECUENCIA.
=INDICE(C:C;SECUENCIA(CONTARA(C:C)-CONTARA(A:A);;CONTARA(A:A)+1))
Y ya lo tenemos.
Igual que antes, si queremos eliminar los productos en la columna de centro, debemos de copiar la función, y, pegarla como valores.
Encontrar meses de más ventas
Seguimos trabajando con el modelo que suelo trabajar habitualmente, pero tenemos las ventas de productos para el año 2021, y, queremos elegir una provincia, y, que nos muestre cual fue el mes, o, meses de más ventas.
Este modelo está en formato de tabla.
Primero, vamos a crear una lista desplegable de las provincias, para ello, vamos a usar la función ORDENAR, y, UNICOS, para traernos las provincias únicas ordenadas.
Lo hare en la celda M2.
=ORDENAR(UNICOS(Table1[Provincia]))
En la celda I2, voy a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana de origen, seleccionamos M2, junto con el operador de rango derramado (#), con esto nos aseguramos de que esta lista desplegable, siempre estará actualizada.
Aceptamos.
Seleccionamos una provincia.
En la celda J2, escribo el signo igual, abro un paréntesis, selecciono la columna de provincia, y, pregunto si es igual a la provincia que hemos seleccionado, y, cerramos paréntesis.
=(Table1[Provincia]=I2)
Escribe el símbolo de asterisco, abro otro paréntesis, y, le digo que me devuelva la fila de la columna provincia, siempre que haya coincidencia.
=(Table1[Provincia]=I2)*(FILA(Table1[Provincia]))
Obtengo una matriz desbordada con el número de fila donde hay coincidencia, y, cero donde no la hay.
Para obtener la matriz solo con los números mayores a cero, voy a usar la función FILTRAR, para filtrar la expresión anterior, siempre que dicha expresión, sea mayor a cero.
=FILTRAR((Table1[Provincia]=I2)*(FILA(Table1[Provincia]));(Table1[Provincia]=I2)*(FILA(Table1[Provincia]))>0)
Obtengo una matriz desbordada solo con los números que son mayores a cero.
Como uso dos veces la misma expresión, puedo usar la función LET, donde creo una variable, le asigno la expresión (Table1[Provincia]=I2)*(FILA(Table1[Provincia])), y, después lo sustituyo, como sigue:
=LET(a;(Table1[Provincia]=I2)*(FILA(Table1[Provincia]));FILTRAR(a;a>0))
Ahora, con la función INDICE, voy a traerme las fechas que corresponden con dichos números de filas.
=INDICE(A:A;LET(a;(Table1[Provincia]=I2)*(FILA(Table1[Provincia]));FILTRAR(a;a>0)))
Tenemos las fechas de ventas, de la provincia seleccionada.
En la celda K2, con la función MES, me voy a traer el número de mes, de las fechas obtenidas.
=MES(J2#)
Obtengo una matriz desbordada con los números de meses, vemos que cada número de mes aparece más de una vez, porque hubo diferentes ventas en cada mes.
Lo siguiente que necesito saber, son las veces que aparece, o, se repite cada mes, para ello, voy a usar la función FRECUENCIA, esta función tiene dos argumentos, que son datos, y, grupos, pues, para ambos argumentos voy a usar MES(J2#).
=FRECUENCIA(MES(J2#);MES(J2#))
Obtengo una matriz desbordada con las veces que aparece o se repite cada mes.
Con la función MAX, voy a extraer el valor máximo del resultado de la función FRECUENCIA.
=MAX(FRECUENCIA(MES(J2#);MES(J2#)))
Me dice que el mes que más aparece es de cinco veces.
Ahora, compara el valor máximo de FRECUENCIA, con la función FRECUENCIA.
=FRECUENCIA(MES(J2#);MES(J2#))=MAX(FRECUENCIA(MES(J2#);MES(J2#)))
Obtengo una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Como se repite la función FRECUENCIA, usare la función LET, para asignar a una variable, la función FRECUENCIA.
=LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a))
Ahora, con el condicional SI, pregunto que, si FRECUENCIA es igual al valor máximo de FRECUENCIA, que me devuelva el nombre del mes de J2, con la función TEXTO, en caso contrario, que ponga un texto en blanco.
=SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;”mmmm”);””)
Vemos que, para Sevilla, nos devuelve el mes de diciembre, pero es una matriz desbordada, donde si no hay coincidencia, devuelve blanco, y, quiero quedarme solo con el o los datos, para ello, voy a usar la función UNIRCADENAS, donde como delimitador, usare la coma, ignorare las celdas vacías, y, las celdas a unir es J2#.
=UNIRCADENAS(",";VERDADERO;SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;"mmmm");""))
Elijo la provincia de Jaén, y, vemos que aparecen dos meses, separados por coma.
Ahora, uso la función DIVIDIRTEXTO, donde como argumento texto, es la expresión anterior, salto el argumento delimitador de columna, y, como delimitador de fila, pongo la coma.
=DIVIDIRTEXTO(UNIRCADENAS(",";VERDADERO;SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;"mmmm");""));;",")
Obtengo una matriz desbordada en vertical, solo con los meses, sin celdas en blanco.
Recuperar encabezados de tablas seleccionadas
En el siguiente ejemplo, vamos a trabajar con el modelo CONTOSO, donde tenemos varias tablas, que son:
· Ventas
· Tienda
· Promoción
· Cliente
· SubCategoriaProducto
· CategoriaProducto
· Producto
· Canal
En una hoja nueva, queremos seleccionar un producto, y, elegir que encabezados de la tabla ventas, queremos que nos devuelva.
Todos los modelos están en formato de tabla, y, cada tabla tiene el nombre de la hoja.
Vamos a ir haciéndolo, y, lo iremos comprendiendo.
En una hoja nueva, voy a traerme los encabezados de la tabla ventas en la celda C1.
=Ventas[#Encabezados]
De todos los encabezados, para este ejemplo, voy a trabajar con los encabezados IDTienda, IDProducto, IDPromocion; IDCanal, e, IDCliente.
En estas tablas, el campo a recuperar se llama Tienda, Producto, Promocion, Canal, y, Cliente.
Si nos fijamos en los ID´s, si quito la palabra ID, me queda el nombre de la columna, entonces, si en la siguiente fila a la anterior (C2), uso la función HALLAR, que no distingue entre mayúsculas, y, minúsculas, para encontrar la posición de la letra D en cada encabezado, como argumento texto buscado, entre comillas dobles, ponemos “d”, como argumento dentro del texto, seleccionamos la celda C1 más el operador de rango derramado.
=HALLAR("d";C1#)
Obtengo una matriz desbordada con la posición de la palabra D, donde se encuentre, y, error donde no este.
Si a la longitud de cada cadena, le resto hasta la letra D, me quedan los caracteres a extraer, pero decir, que a la expresión anterior, hay que sumarle 1, que es donde debe de empezar a extraer, ponemos la función EXTRAE, como argumento texto, ponemos la celda C1 mas el operador de rango derramado, como argumento posición inicial, es la función HALLAR más 1, como argumento numero de caracteres, usamos la función LARGO, como argumento ponemos la celda C1 más el operador de rango derramado, y, restamos con la función HALLAR.
=EXTRAE(C1#;HALLAR("d";C1#)+1;LARGO(C1#)-HALLAR("d";C1#))
Obtenemos el nombre de la columna de cada tabla, la que necesitamos, y, error donde no hay coincidencia, pero no debemos de preocuparnos, porque vamos a trabajar con los primeros cinco encabezados.
Lo siguiente en la celda C3, vamos a concatenar C2, entre comillas dobles, pongo un corchete de apertura, concatenado con C1, y, concatenado, entre comillas dobles, con un paréntesis de cierre, de esta forma, tendremos el nombre de la tabla y la columna con la que trabajar.
=C2 & "[" &C1 & "]"
Arrastramos hacia la derecha.
El campo que, una vez realizada la búsqueda, se llama igual que la tabla, es decir, de la tabla tienda, vamos a recuperar la columna tienda, igual, para las demás hojas.
En la celda C4, con la función SUSTITUIR, de la celda C3, voy a sustituir la palabra ID por nada, y, me quedara el nombre de la tabla, y, la columna, como argumento texto, ponemos la celda C3, como argumento texto antiguo, entre comillas dobles, ponemos “ID”, como argumento texto nuevo, ponemos dobles comillas dobles.
=SUSTITUIR(C3;"ID";"")
Arrastramos hacia la derecha.
Quiere decir, que tengo los ID para trabajar en la columna ventas, los nombres de las tablas, el nombre de la tabla, y, la columna a buscar, y, el nombre de la tabla, y, la columna a devolver.
Si como argumento texto, en vez de poner C3, ponemos C3:G3, tendremos de una vez, cada nombre de tabla junto con la columna a recuperar.
Ahora, en la celda C8, vamos a crear una lista desplegable, para elegir un producto, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana origen, vamos a la hoja de productos, y, seleccionaos los productos.
Aceptamos.
Elegimos un producto.
Voy a añadir una línea con ceros y unos, debajo de cada línea, uno es el encabezado que quiero ver, y, cero el que no quiero ver, lo haremos de forma matricial, para ello, en la celda C5, usamos el condicional SI, como argumento prueba lógica, seleccionamos la celda C1 junto con el operador de rango derramado, igualamos a C1, como argumento valor si verdadero, ponemos 1, como argumento valor si falso, ponemos otro SI, como argumento prueba lógica, seleccionamos la celda C1 junto con el operador de rango derramado, igualamos a la celda F1, como argumento valor si verdadero, ponemos 1, como argumento valor si falso del segundo SI, ponemos 0, omitimos el argumento valor si falso del primer SI.
=SI(C1#=C1;1;SI(C1#=F1;1;0))
Estos ceros, y, unos hacen referencia a los encabezados que vamos a recuperar, en la celda B10, con la función FILTRAR, voy a filtrar los nombres de tablas (C2#), cuyo valor sea 1, solo tengo puesto el valor 1, en tienda, y, canal, el resto son ceros.
=FILTRAR(C2#;C5:P5)
Lo siguiente que voy a hacer en la celda B11, es encontrar el ID de producto, del producto seleccionado, para ello, voy a usar la función BUSCARX, donde como primer argumento, valor buscado, es el producto seleccionado, como argumento matriz de búsqueda, es la columna B de la hoja de productos, y, como argumento matriz devuelta, es la columna A de la hoja de productos.
=BUSCARX(C8;Producto!B:B;Producto!A:A)
Obtenemos el ID 3.
Lo siguiente que voy a hacer es filtrar la tabla ventas, cuyo ID de producto sea igual al devuelto por la función BUSCARX, como argumento array, ponemos la tabla ventas, como argumento include, ponemos la columna IDproducto de la tabla ventas e igualamos a la función BUSCARX.
=FILTRAR(Ventas;Ventas[IDProducto]=BUSCARX(Hoja3!C8;Producto!B:B;Producto!A:A))
Obtengo una matriz desbordada cuyo id producto es igual al producto seleccionado.
Pero, solo deben de aparecer los datos de los encabezados que hemos seleccionado, por lo que, de nuevo, filtro esta función, y, como argumento include, uso el contador que he creado.
=FILTRAR(FILTRAR(Ventas;Ventas[IDProducto]=BUSCARX(Hoja3!C8;Producto!B:B;Producto!A:A));C5#)
Ya tenemos solo los datos de los encabezados seleccionados.
Lo siguiente es recuperar los datos de los ID que tenemos en pantalla.
Como tenemos tienda, y, canal, tengo que trabajar con la columna IDTienda de la tabla tienda, y, lo mismo para canal, entonces, si uso la función BUSCARX en la celda H11, donde como argumento valor buscado es el valor de la celda B10, que es el primer encabezado a recuperar, como argumento matriz de búsqueda, señalamos desde C2 a G2, y, como argumento matriz devuelta, selecciono desde C4 a G4.
=BUSCARX(B10;C2:G2;C4:G4)
Obtengo el nombre de la tabla, y, una de las columnas con que trabajar, por supuesto, es dinámico, en este caso, obtengo:
Para obtener los datos de dicho encabezado, voy a usar la función INDIRECTO.
=INDIRECTO(BUSCARX(B10;C2:G2;C4:G4))
Obtengo una matriz desbordada con los valores de la columna tienda de la tabla tienda.
Ahora, con otro BUSCARX, vuelvo a buscar el valor de la celda B10, tienda en este caso, en la matriz C2:G2, y, que me devuelva la matriz C3:G3.
=BUSCARX(B10;C2:G2;C3:G3)
En este caso, obtengo el encabezado IDTienda, la otra columna con la que debemos de trabajar, igual que antes, para recuperar los valores, vuelvo a usar INDIRECTO.
=INDIRECTO(BUSCARX(B10;C2:G2;C3:G3))
Ahora, con la función FILTRAR, voy a recuperar el nombre de la tienda, donde como argumento array, será el primer BUSCARX, es decir, el nombre de la tienda, y, como argumento include, es el segundo BUSCARX, que lo igualamos con el valor de la celda B11, que es el primer IDTienda.
=FILTRAR(INDIRECTO(BUSCARX(B10;C2:G2;C4:G4));INDIRECTO(BUSCARX(B10;C2:G2;C3:G3))=B11)
Obtengo el nombre de la tienda del primer IDTienda.
Ahora, he de arrastrar hacia abajo, pero ¿cuántas veces he de arrastrar?, como tenemos una matriz desbordada, en la celda G11, uso la función CONTARA, y, como argumento pongo la celda B11, junto con el operador de rango derramado (#), me cuenta las celdas alfanuméricas de toda la matriz.
=CONTARA(B11#)
Para este ejemplo, me devuelve 8, pero no son ocho veces lo que he de arrastrar, si divido 8 entre el número de columnas, si me dará las filas de cada columna de la matriz, para ello, voy a usar la función COLUMNAS, quedando la sintaxis, de la siguiente manera:
=CONTARA(B11#)/COLUMNAS(B11#)
Ahora, me devuelve 4, que son las filas de cada columna dentro de la matriz.
Ahora, voy a usar la función SECUENCIA, para que genere una matriz de cuatro números, en este caso, el argumento filas, va a ser la función anterior, empezando desde el número 1.
=SECUENCIA(CONTARA(B11#)/COLUMNAS(B11#))
Tenemos una matriz desbordada, en este caso, desde el numero 1 al número 4.
Pues son cuatro veces lo que tengo que arrastrar, pero antes de arrastrar he de fijar algunas referencias, la celda B10, solo he de fijar la fila, para que cuando copiemos hacia abajo no se actualice, pero cuando copiemos hacia la derecha la columna si se debe de actualizar, ponemos la fila en absoluta, los argumentos matriz de búsqueda, y, matriz devuelta de los dos BUSCARX, y, la referencia B11, la dejamos en relativa, para que se actualice al copiar hacia abajo, y, cuando copiemos hacia la derecha, se actualice la columna.
=FILTRAR(INDIRECTO(BUSCARX(B$10;$C$2:$G$2;$C$4:$G$4));INDIRECTO(BUSCARX(B$10;$C$2:$G$2;$C$3:$G$3))=B11)
Pues, arrastramos cuatro veces hacia abajo.
Arrastro una vez hacia la derecha, y, hacia abajo, tenemos recuperado los datos para cada canal.
Crear una copia de tabla, cambiando los ID por sus nombres
Seguimos trabajando con el modelo de CONTOSO, vemos que en la tabla ventas, solo tenemos los ID, no los nombres, queremos crear una copia de la tabla ventas, pero cambiando los ID por sus nombres.
En una hoja nueva, en la celda A1, voy a traer los encabezados de la tabla ventas.
=Ventas[#Encabezados]
Los ID que debemos de rescatar son los de tienda, producto, promoción, canal, y, cliente.
Con la función SUSTITUIR, voy a sustituir la palabra ID por nada.
=SUSTITUIR(Ventas[#Encabezados];"ID";"")
Pero, ocurre que IDVenta, no debemos de sustituirlo, porque no lleva asociado ningún dato, entonces, voy a usar el condicional SI, para preguntar que, si uno de los encabezados es igual a IDVenta, que lo deje, en caso contrario, que sustituya la palabra ID.
=SI(Ventas[#Encabezados]="IDVenta";"IDVenta";SUSTITUIR(Ventas[#Encabezados];"ID";""))
Problema solventado, seguimos…
Como dije anteriormente, vamos a preparar las columnas con las que vamos a trabajar.
Lo hare a partir de la celda AE1.
Vuelvo a traerme los encabezados.
=Ventas[#Encabezados]
Voy a concatenar la palabra Ventas, con una apertura de corchete, con los encabezados, y, con un cierre de corchete.
="Ventas" & "[" & Ventas[#Encabezados] & "]"
Ya tengo el nombre de tabla y de encabezado.
Voy a necesitar el nombre de cada tabla, y, el ID de cada tabla, lo haremos en la celda AE2.
Por lo que me voy a traer de nuevo los encabezados, pero sustituyo la palabra ID por nada, para ello, uso la función SUSTITUIR, como argumento texto, son los encabezados de la tabla ventas, como argumento texto antiguo, entre comillas dobles, ponemos “ID”, como argumento texto nuevo, ponemos dobles comillas dobles, cerramos paréntesis, concateno con un corchete de apertura, vuelvo a concatenar con los encabezados, concateno con un corchete de cierre, y, acepto.
=SUSTITUIR(Ventas[#Encabezados];"ID";"") & "[" & Ventas[#Encabezados] & "]"
Tenemos el nombre de cada tabla con su ID.
Pero, también voy a necesitar el encabezado con el nombre de cada ID, pues, voy a sustituir la palabra ID de la matriz AE2, en la celda AE3, usamos SUSTITUIR, como argumento texto, ponemos la celda AE2 junto con el operador de rango derramado, como argumento texto antiguo, entre comillas dobles, ponemos “ID”, como texto nuevo, ponemos dobles comillas dobles.
=SUSTITUIR(AE2#;"ID";"")
Como vamos a trabajar con las primeras cinco columnas, a partir de la celda AE3, voy a poner 1, en los encabezados que quiero, que son los cinco primeros, y, 0 en los demás, lo haremos de forma matricial, para ello, en la celda AE4, ponemos el condicional SI, como argumento prueba lógica, ponemos la celda AE1 junto con el operador de rango derramado, igualamos al rango AE1:AI1, que son los cinco primeros encabezados, como argumento valor si verdadero, ponemos 1. omitimos el argumento valor si falso.
=SI(AE1#=AE1:AI1;1;0)
Obtenemos 1 para los primeros cinco encabezados, y, error para el resto.
Usamos la función SI.ERROR, como argumento valor es el condicional SI, como argumento valor si error, ponemos 0.
=SI.ERROR(SI(AE1#=AE1:AI1;1;0);0)
Tenemos 0 donde teníamos error.
Ahora, en la celda AE5, filtro AE2#, y, como argumento include, selecciono los ceros, y, unos.
=FILTRAR(AE3#;AE4#)
Volvemos a donde colocamos los encabezados al principio del ejercicio.
En la celda A2, uso la función INDIRECTO de la celda AE1, que es el encabezado tienda de la tabla tienda.
=INDIRECTO(AE1)
Obtengo una matriz desbordada con todos los nombres de tienda de la tabla tienda.
Lo siguiente es buscar cada IDTienda de la tabla ventas, y, que nos devuelva el nombre de la tienda, por lo que tengo que buscar cada IDTienda de la hoja ventas, en cada IDTienda de la hoja tienda, y, que me devuelva el nombre, para ello, voy a hacer uso de la función BUSCARX.
Como argumento valor buscado, será INDIRECTO de la celda AE1, que son los IDTienda de la tabla ventas.
=BUSCARX(INDIRECTO(AE1)
El argumento matriz de búsqueda es INDIRECTO de la celda AE2, es decir, la columna IDTienda de la tabla tienda.
=BUSCARX(INDIRECTO(AE1);INDIRECTO(AE2)
Y, el argumento matriz devuelta, será INDIRECTO de la celda AE5, es decir, el nombre de la tienda.
=BUSCARX(INDIRECTO(AE1);INDIRECTO(AE2);INDIRECTO(AE5)
Cerramos paréntesis y aceptamos.
Ya tenemos los nombres de las tiendas a los que corresponde cada ID de la tabla ventas.
Arrastramos hacia la derecha hasta E2, y, también tenemos los nombres de los demás encabezados.
Nos queda rellenar el resto de los encabezados.
Como ya los tenemos, volvemos a concatenar el nombre de la tabla ventas con el encabezado en la celda F2.
=INDIRECTO("Ventas" & "[" & F1 & "]")
Arrastramos hacia la derecha, y, ya lo tenemos.
Hemos creado una copia de la tabla ventas, pero con los nombres de los ID correspondientes.
Crear una lista desplegable por tres columnas
Trabajamos con el modelo de ventas de productos en diferentes centros comerciales, provincias, y, fechas.
Vamos a crear una única lista desplegable, que debe de ser rellena por provincia, centro, o, producto.
Necesitamos tener activa la pestaña de programador, o, desarrollador si tenemos la versión 2013, para activarla, para ello, desplegamos la pestaña de archivo, y, seleccionamos opciones.
Se abre la ventana de opciones de Excel, en la ventana de la izquierda, hacemos clic en personalizar cinta de opciones.
En la ventana, pestañas principales, bajamos hasta desarrollador, o, programador, marcamos la casilla, y, aceptamos.
Ya debemos de tener la pestaña en la cinta de opciones, pues, hacemos clic en dicha pestaña.
Dentro del grupo controles, desplegamos insertar, y, seleccionamos botón de opción.
Lo colocamos dentro de la celda I2, borramos el texto del botón de comando.
Hacemos clic con botón secundario del ratón sobre el botón de comando, y, seleccionamos formato de objeto.
En la ventana vincular con la celda, seleccionamos una celda, donde poner el número de botón de comando pulsado.
Aceptamos.
Realizamos la misma operación anterior, e, insertamos dos botones de comandos más, lo colocamos al lado del primero, en este caso, no hace ir a formato de control, cuando ponemos varios botones de comandos, al primero se le asigna el número 1, al segundo el número 2, y, así sucesivamente.
Dicho valor es colocado en la celda que hemos vinculado.
Vamos a la pestaña de inicio, dentro del grupo edición, desplegamos buscar y seleccionar, y, seleccionamos, seleccionar objetos, con esto, cuando marquemos una zona, solo los objetos quedaran seleccionados.
Ahora, seleccionamos los tres botones de comandos con el ratón.
Vamos a la pestaña de formato de forma, desplegamos alinear, y, seleccionamos alinear en la parte inferior.
Volvemos a desplegar alinear, y, seleccionamos distribuir horizontalmente.
Ya tenemos los tres botones bien alineados.
Si hacemos clic en uno de los botones, veremos como en la celda vinculada, pone el número que le corresponda.
Cuando hagamos clic en el primer botón, quiero que se carguen las provincias únicas, y, ordenadas.
Cuando haga clic en el segundo botón, quiero que se carguen los centros comerciales únicos.
Cuando haga clic en el tercer botón, se deben de cargar los productos únicos, pero en el mismo rango.
En la celda I1, debe de aparecer el encabezado del botón que seleccionemos, si hago clic en el primer botón en la celda I1, debe de aparecer provincia.
En la celda S1, es donde he vinculado el valor de los botones, entonces, en la celda I1, debo de preguntar que si el valor de la celda S1, es igual a 1, debe de poner provincia, si es igual a 2, debe de poner centro, y, si es igual a 3, debe de poner producto.
Lo hare con el condicional SI anidado.
=SI(S1=1;"Provincia";SI(S1=2;"Centro";SI(S1=3;"Producto";"")))
Vemos que como tengo señalado el primer botón, en la celda I1, aparece provincia.
En la celda S2, es donde deben de aparecer los valores del botón seleccionado.
De nuevo, voy a usar el condicional SI anidado, donde pregunto que si el valor de la celda S1, es igual a 1, aplico la función ORDENAR y UNICOS en la columna de provincia, si es igual a 2, aplico la función UNICOS, en la columna centro, y, si es igual a 3, aplico la función UNICOS, a la columna de productos.
=SI(S1=1;ORDENAR(UNICOS(Datos[Provincia]));SI(S1=2;UNICOS(Datos[Centro]);SI(S1=3;UNICOS(Datos[Producto]);"")))
Como he seleccionado el primero botón, podemos ver las provincias ordenadas, si selecciono otro botón, vemos como el valor de la celda I1, cambia al valor del botón que corresponde, y, como la lista queda rellenada, por ejemplo, si hemos elegido el segundo botón, con los centros comerciales únicos.
En la celda I3, voy a crear la lista desplegable, con los valores de la celda S2, donde usare el operador de rango derramado (#), para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana de origen, ponemos la celda S2, seguido del operador de rango derramado (#).
Aceptamos.
Tengo seleccionado el segundo botón, es decir, el centro comercial, si desplegamos la lista, veremos cómo se ha rellenado con los centros comerciales únicos.
Lo siguiente va a ser filtrar por el botón que hemos seleccionado, para ello, usare la función FILTRAR, donde como argumento array, es la tabla, que se llama datos, y, como argumento include, debemos de convertir el valor de la celda I1, en una referencia de columna, es decir, [Centro].
Vamos a ver antes, como convertir en valor de la celda I1, en una referencia estructurara, por ejemplo, si tengo seleccionado el botón de centro, deberá de aparecer el nombre de la tabla, y, entre corchetes, el nombre del encabezado, es decir, Datos[Centro].
Quiere decir que he de concatenar el nombre de la tabla, Datos, con una apertura de corchete, con el valor de la celda I1, y, concatenado con un cierre de corchete, lo haremos, por ejemplo, en la celda I6.
Es decir:
="Datos" & "[" & I1 & "]"
Y, obtenemos:
Pero, para recuperar los valores de la columna debemos de usar la función INDIRECTO, como sigue:
=INDIRECTO("Datos" & "[" & I1 & "]")
Pues vamos a filtrar la tabla datos, siempre que la columna devuelta por INDIRECTO sea igual al valor seleccionado.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)
Ya tenemos filtrado la tabla datos, por el elemento seleccionado.
Podemos probar cambiando de botón.
En el modelo los años van del año 2017 al año 2021, ahora, voy a insertar dos casillas de verificación, voy a realizar el ejemplo con los dos primeros años, para el resto de los años, es repetir la operación.
Vamos de nuevo a la pestaña de programador, o, desarrollador, dentro del grupo controles, desplegamos insertar, y, seleccionamos casilla dentro de controles de formulario.
La colocamos dentro de la celda J2, insertamos otra casilla, y, la insertamos en la celda J3.
Hacemos clic con botón alternativo de ratón, y, seleccionamos formato de control.
En este caso, lo voy a vincular con la celda T1, y, aceptamos.
En este caso, cuando la casilla este activada, nos devolverá VERDADERO, en caso contrario, devolverá FALSO.
Como voy a trabajar con los dos primeros años, en la celda J2, voy a preguntar si el valor de la celda T1 es igual a VERDADERO, en ese caso, me traigo los años únicos de la columna fecha, transpongo los años, y, con la función INDICE, le digo que me devuelva la primera fila, y, primera columna, lo cual me devolverá el primer año.
=INDICE(TRANSPONER(UNICOS(AÑO(Datos[Fecha])));1;1)
Ahora, usamos el condicional SI después del signo igual, como argumento prueba lógica, seleccionamos la celda T1 e igualamos a VERDADERO, aunque no hace falta igualarlo, porque de forma predeterminada compara con VERDADERO, como argumento valor si verdadero es la función INDICE, como argumento valor si falso, ponemos blanco.
Si activamos la casilla vemos como aparece el año 2017, y, si no está activada no aparece nada.
Ahora, en la celda J3, uso la misma función anterior, pero cambio T1, por T2, porque aquí debemos de vincular cada casilla con una celda, y, en la función INDICE, le digo que me devuelva la columna 2.
=SI($T$2=VERDADERO;INDICE(TRANSPONER(UNICOS(AÑO(Datos[Fecha])));1;2);"")
Funciona igual que para el año 2017.
Lo siguiente es que, si marcamos un año, el modelo debe de quedar filtrado también por el año seleccionado, si selecciono las dos casillas, debe de quedar filtrado por los dos años, y, si no selecciono ninguna casilla, que devuelva todos los datos.
Volvemos a la función (I6) FILTRAR, borramos le cierre de paréntesis, encerramos entre paréntesis, la primera condición.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)
Ponemos el símbolo de asterisco, abrimos un paréntesis para poner la siguiente condición, que es que el año de la columna fecha, donde usare la función VALOR, junto con la función TEXTO, debe de ser igual al valor de la celda J2, es decir, a 2017, y, cerramos paréntesis.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)
Ahora, pongo el símbolo de más (+), que es igual al operador lógico O, es decir, que devuelve VERDADERO, si una de las condiciones se cumple, abro otro paréntesis, de nuevo uso la función VALOR, junto con la función TEXTO, pero debe de ser igual al valor de la celda J3, es decir, a 2018.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3)
Y, para el argumento si vacío, filtramos todo el modelo.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3);FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3);""))
Antes de seguir, las dos condiciones que hemos puesto con el signo más (+), la vamos a encerrar entre paréntesis, en caso contrario, no nos dará los resultados correctos, cuando tengamos marcado el año 2018, y, cambiemos de encabezado.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*((VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3));FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3);""))
Aceptamos, si no tenemos marcada ninguna casilla, aparecen todos los datos, si seleccionamos el año 2017, aparecerá por el botón seleccionado y el año 2017, si marcamos también el año 2018, aparecerán por los años 2017 y 2018, si marcamos solo el año 2018, aparecerá solo por el año 2018, y, si no marcamos ninguna casilla, aparecerán todos los datos.
Filtrar por provincia, centro, producto, y, año
Tenemos el ejemplo del video anterior, pero lo vamos a modificar.
De momento, voy a borrar las casillas y los años.
Esta parte no será modificada.
Muevo las botones de opción a la celda I3, y, la lista desplegable a la celda I2.
En la celda U1, voy a poner los encabezados.
=Datos[#Encabezados]
Igual que antes, vamos a trabajar con las columnas provincias, centro, y, productos, vemos que son columnas contiguas.
En la celda I1, igual que antes, vamos a elegir el encabezado por el que filtrar, cuyos datos se derraman a partir de la celda S2.
Pero, lo que quiero es que en las celdas J1, y, K1, aparezcan los dos encabezados siguientes, es decir, si hemos seleccionado centro, pues, en las celdas J1 y K1, deben de aparecer provincia, y, producto.
Me voy a traer de nuevo los encabezados en la celda J1.
=Ventas[#Encabezados]
Ahora, filtro los encabezados que sean diferentes al valor de la celda I1, por lo que usamos la función FILTRAR, como argumento array, son los encabezados, como argumento include, vuelve a ser los encabezados siempre que sean diferentes al valor de la celda I1, lo haremos en la celda J1.
=FILTRAR(Ventas[#Encabezados];Ventas[#Encabezados]<>I1)
Como usamos dos veces la misma expresión, usamos LET, creamos una variable, almacenamos la expresión.
=LET(a;Ventas[#Encabezados]
Como argumento cálculo de LET, filtramos la variable “a”, siempre que dicha variable sea diferente al valor de la celda I1.
=LET(a;Ventas[#Encabezados];FILTRAR(a;a<>I1))
Tenemos todos los encabezados.
Como hemos dicho anteriormente, en la posición 2 y 3, siempre se encontrarán los encabezados que vamos a necesitar, por lo que voy a poner 1 en el encabezado que quiero ver, y, 0 en el que no quiero ver, en la fila siguiente a la celda U1.
En la celda J1, volvemos a filtrar, como argumento array, es la función FILTRAR anterior, como argumento include, seleccionamos el rango U2:Z2, que son los ceros y unos.
=LET(a;Ventas[#Encabezados];FILTRAR(FILTRAR(a;a<>I1);U2:Z2))
Ya tenemos los encabezados que necesitamos.
Hacemos lo mismo para los encabezados de la celda U1.
Si vamos cambiando, veremos cómo se actualizan los encabezados.
Lo siguiente es crear un desplegable en las celdas J2 y K2, con los elementos del encabezado, empezamos en la celda J2.
Vamos a la pestaña datos, dentro del grupo, herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana origen, voy a usar la misma función, usada anteriormente, para referirme a una columna de la tabla.
Aceptamos.
Si desplegamos, veremos que se ha rellenado con los valores de del encabezado de J1.
En la celda K2, creamos otra lista desplegable, con la misma función, pero se cambia J1 por K1.
Ahora, vamos a filtrar por los valores seleccionados.
Lo siguiente que voy a hacer es preparar los nombres de columnas, para ello, en la celda U5, voy a seleccionar desde I1 a K1.
=I1:K1
Ahora, después del signo igual, entre comillas dobles, pongo el nombre de la tabla, que es ventas, seguido de una apertura de corchetes, concateno con el rango anterior (I1:K1), y, concateno con un corchete de cierre, entre comillas dobles.
="Ventas[" & I1:K1 & "]"
Tenemos una matriz desbordada en horizontal con el nombre de la tabla, y, nombre de encabezado, entre corchetes.
Lo siguiente es filtrar el modelo por estas condiciones, lo haremos en la celda J6, para ello, escribo el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Como argumento array, ponemos el nombre de la tabla, ventas.
=FILTRAR(Ventas
Como argumento include, abrimos un paréntesis, para poner la primera condición, que es:
=FILTRAR(Datos;(INDIRECTO(U5)=I2)
Ponemos el símbolo de asterisco, abrimos otro paréntesis, y, ponemos la segunda condición.
=FILTRAR(Datos;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)
Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, para poner la tercera condición.
=FILTRAR(Ventas;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)*(INDIRECTO(W5)=K2)
Como argumento, si vacío, ponemos un texto en blanco, y, cerramos paréntesis.
=FILTRAR(Ventas;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)*(INDIRECTO(W5)=K2);"")
Aceptamos, y, vemos el modelo filtrado por las condiciones especificadas.
Podemos ir cambiando, y, veremos cómo se filtra correctamente.
Ahora, vamos a la función FILTRAR, y, seleccionamos las tres condiciones.
Fijamos todas las referencias.
Pulsamos CTRL más C para copiar, y, pulsamos la tecla de Escape.
Voy a crear un nombre de rango con las tres condiciones, para ello, vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.
En la ventana se refiera a, pegamos la expresion, y, aceptamos.
Sustituimos las tres condiciones por el nombre de rango, y, vemos que todo sigue igual.
=FILTRAR(Ventas;criterios;"")
Lo siguiente va a ser trabajar con la columna fecha filtrada, para quedarnos con los años.
La pregunta es, ¿Cuántas fechas tenemos filtradas?
Si uso la función CONTARA, me devuelve un recuento de todas las celdas ocupadas alfanuméricas.
Lo hare en la celda U9.
=CONTARA(I5#)
Me devuelve 42.
Pero, si divido la función CONTARA, entre el número de columnas, con la función COLUMNAS, obtendré el número de filas de cada columna.
=CONTARA(I5#)/COLUMNAS(I5#)
Me devuelve 6, es decir, cada columna está compuesta de seis filas.
Con la función SECUENCIA, voy a crear una matriz desbordada en vertical donde solo usare el argumento filas, que será la expresión anterior.
=SECUENCIA(CONTARA(I5#)/COLUMNAS(I5#))
El problema es que no podemos extraer los años de la matriz desbordada (I5#), porque al filtrar por un año, esta matriz con los años cambiará, y, devolverá un error, por lo que debemos de hacerlo con el modelo ventas.
Entonces, voy a filtrar la columna fecha de la tabla ventas, por el nombre de rango criterios.
=FILTRAR(Ventas[Fecha];criterios;"")
Obtengo las mismas fechas que la matriz desbordada, la única diferencia, es que solo nos hemos traído la columna fecha.
Voy a usar la función Año, para quedarme con los años.
=AÑO(FILTRAR(Ventas[Fecha];criterios;""))
Me quedo con los valores únicos.
=UNICOS(AÑO(FILTRAR(Ventas[Fecha];criterios;"")))
Lo siguiente que voy a hacer es usar la función APILARV (VSTACK), donde como argumento matriz1, va a ser el texto no selección, y, como argumento matriz2, será la expresión anterior.
=APILARV("No selección";UNICOS(AÑO(FILTRAR(Ventas[Fecha];criterios;""))))
En la celda L2, voy a crear una lista desplegable, con los años.
Lo siguiente es filtrar el modelo que ya tenemos filtrado por el año que seleccionemos, pero si seleccionamos no selección, deben de aparecer todos los registros, la expresión FILTRAR(Ventas;criterios;""), para este ejemplo, la vamos a usar varias veces, porque siempre tenemos que trabajar con el modelo filtrado, nunca con la matriz desbordada obtenida (I5#), primero lo vamos a hacer repitiendo la expresión anterior.
Volvemos a la expresión, voy a usar el condicional SI, para preguntar por el valor de la celda L2, si es igual, o no, a no selección.
=SI(L2<>"No selección"
Si es diferente a no selección, tiene que filtrar la tabla ventas por los criterios, es decir, primero filtramos la tabla ventas por los criterios, y, después filtramos este resultado, donde vamos a aplicar los criterios, entonces filtrar la tabla ventas por los criterios, es el argumento array del primer filtrar.
=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"")
En el argumento include, extraemos el año con la función INDICE, la ponemos antes de la función AÑO, cuyo argumento matriz es filtrar de nuevo la tabla ventas por los criterios, y nos devuelva el número de filas devuelto por la función SECUENCIA, y, una columna.
AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))
Y este matriz desbordada debe de ser igual al valor de la celda L2.
=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"");AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))=L2)
En el argumento valor si falso del condicional SI, pues que nos filtre la tabla completa, porque ha sido seleccionado no selección.
=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"");AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))=L2);FILTRAR(Ventas;criterios))
Si seleccionamos un año, veremos cómo queda filtrado por dicho año.
Si seleccionamos no selección, vemos como se filtra el modelo por todos los años.
Entonces, como usamos varias la expresión FILTRAR(Ventas;criterios;""), voy a usar la función LET, donde creo una variable con dicha expresión, y, la sustituyo.
=LET(a;FILTRAR(Ventas;criterios;"");SI(L2<>"No selección";FILTRAR(a;AÑO(INDICE(a;SECUENCIA(CONTARA(a)/COLUMNAS(a));1))=L2);a))
Vemos que ha quedado reducida la expresión.
Miguel Angel Franco
Comments