Vamos a ver un ejemplo, con el modelo de Contoso, en la hoja ventas.
¿Qué queremos?
Si nos fijamos en el modelo, en la hoja ventas, tenemos diversas columnas, la primera de ellas, es IDTienda.
Queremos seleccionar un IDTienda, y, que nos devuelva el valor del resto de encabezados, es decir, desde IDProducto hasta Total.
Realmente, no es lo que queremos, lo que queremos es que seleccionemos un encabezado, y, nos devuelva las coincidencias, desde ese encabezado hasta el final.
Vamos a empezar, creando una lista única de IDTiendas, lo vamos a hacer, por ejemplo, en la celda Z2, por lo que escribimos el signo igual, seguido de la función UNICOS, abrimos un paréntesis, seleccionamos la columna IDTienda, y, cerramos paréntesis.
=UNICOS(TVentas[IDTienda])
Por último, ordenamos estos valores, con la función ORDENAR, y, los argumentos predeterminados.
=ORDENAR(UNICOS(TVentas[IDTienda]))
Vamos a crear una validación de datos, para poder seleccionar un IDTienda, lo haremos en la celda Q2, para ello, teniendo seleccionada la celda, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, donde desplegamos permitir, y, seleccionamos lista.
En la ventana, origen, escribimos la celda Z2, donde comienza el primer ID, seguido del operador de rango derramado (#), con esto conseguimos, que, si se añaden, o, eliminan registros, la lista siempre este actualizada.
Aceptamos, y, seleccionamos un ID.
Pues, empecemos.
El modelo está en formato de tabla, y, esta se llama TVentas, la T, es para hacer referencia que es una tabla.
Lo primero que vamos a hacer, es buscar el IDTienda, que hemos seleccionado, y, que nos devuelva la segunda columna, lo haremos en la celda Q3.
Para ello, escribimos el signo igual, ponemos la función BUSCARV, y, abrimos un paréntesis.
=BUSCARV(
Como argumento valor buscado, es la celda Q2, donde tenemos la validación.
=BUSCARV(Q2;
Como argumento matriz_tabla, pues, ponemos la tabla TVentas.
=BUSCARV(Q2;TVentas;
Indicamos que nos devuelve la segunda columna, y, en tipo de combinación, seleccionamos exacta.
Cerramos paréntesis, y, aceptamos.
=BUSCARV(Q2;TVentas;2;FALSO)
Obtenemos el IDProducto, correspondiente al IDTienda, que hemos seleccionado.
Y, ¿si quiero que me devuelva la columna 2, y, la columna 3?
Entonces, deberíamos de hacer uso de una constante de matriz, la cual debemos de poner los valores entre llaves, y, nos crea una matriz desbordada, con los valores que le indiquemos.
Por ejemplo, si en una celda escribo, ={1;2;3}, obtengo una matriz desbordada, en vertical, con los números 1, 2, y, 3.
Si en una celda escribo, ={1\2\3}, obtengo una matriz desbordada, en horizontal, con los números 1, 2, y, 3.
Pues, vamos a modificar el argumento, numero de columna, de la función BUSCARV, y, ponemos la siguiente constante de matriz, {2;3}, es decir, que nos devuelva las columnas 2 y 3, en vertical.
=BUSCARV(Q2;TVentas;{2;3};FALSO)
Aceptamos, y, vemos como resultado, una matriz desbordada, en vertical, con las columnas 2 y 3.
Si quisiera que me devolviera todas las columnas, pues, dentro de la constante de matriz, debería de poner desde el número 2, hasta el número 14, que es el último encabezado, lo cual sería una “tarea tediosa”, además, de no ser dinámico, porque si aumentan, o, disminuyen los encabezados, no se actualizaría, pero el caso, es que quiero que me devuelva todos los encabezados.
¿Cómo lo hacemos?
Tenemos en Excel, una función matricial, llamada SECUENCIA, que nos genera una matriz desbordada, con el número de filas, y, de columnas, que le indiquemos.
Si en una celda, escribo, =SECUENCIA(5;2), obtengo una matriz desbordada, de cinco filas, y, dos columnas.
A ver, si la podemos usar como argumento, numero de columna, de la función BUSCARV.
Voy a cambiar el argumento, numero de columna de la función BUSCARV, por SECUENCIA(14), donde solo uso el argumento filas, para que me genere una matriz en vertical, empezando desde el numero 1, hasta el número 14.
=BUSCARV(Q2;TVentas;SECUENCIA(14);FALSO)
Aceptamos, y, vemos como nos ha rescatado todas las columnas.
Pero ¿Qué ocurre si se eliminan, o, añaden encabezados?
Pues, que siempre nos dará hasta el encabezado número 14, independientemente, del número de encabezados que haya.
Esto quiere decir, que no está bien, no podemos dejarlo así, sin tener en cuenta este problema.
Tenemos una función llamada CONTARA, que nos cuenta el número de celdas alfanuméricas que hay, dentro de un rango.
Voy a usar la función CONTARA, para contar el número de encabezados, lo haremos en la celda T2.
=CONTARA(TVentas[#Encabezados])
Vemos que nos devuelve 14, el número de encabezados que hay, hasta el momento, ya sabemos que, al estar en formato de tabla, si añadimos, o, quitamos registros, se actualizarán de forma automática.
Como argumento filas, de la función SECUENCIA, hemos puesto 14, pues vamos a cambiarlo, por la función CONTARA.
=BUSCARV(Q2;TVentas;SECUENCIA(T2);FALSO)
Vemos que obtenemos los mismos resultados, pero ahora, si es dinámico, porque siempre va a contar el número de encabezados que hay.
Lo siguiente que quiero, es no ver todos los encabezados, sino, elegir un encabezado, y, que me devuelva desde ese encabezado hasta el final.
Vamos a crear otra validación de datos, como sabemos, para elegir un encabezado.
Seleccionamos un encabezado.
Entonces, en este caso, quiero que me busque el IDTienda 5, y, que me devuelva a partir de IDCliente, encabezado incluido.
Tenemos otra función llamada COINCIDIR, que nos devuelve la posición de un elemento dentro de un rango, pues, voy a usar la función COINCIDIR, para que me devuelva la posición del encabezado seleccionado, con una coincidencia exacta, lo haremos en la celda S2.
=COINCIDIR(R2;TVentas[#Encabezados];0)
Nos devuelve la posición 5, quiere decir, que debe de devolver desde el encabezado número 5, hasta el último encabezado.
Bien, se dónde empieza, pero teniendo en cuenta que los encabezados, pueden cambiar, ¿Cómo lo decimos hasta donde debe de llegar?
La función CONTARA, nos da un recuento de las celdas alfanuméricas dentro de un rango, en este caso nos devuelve 14.
Si restamos, el resultado de CONTARA, menos el resultado de COINCIDIR nos devuelve 9, lo haremos en la celda U2.
Si contamos, desde el encabezado siguiente al que hemos elegido hasta el último encabezado, veremos que es 9.
Podíamos decir, que ya tengo el argumento número de columna, de la función BUSCARV, pero queremos, que el encabezado que seleccione este incluido, por lo que tenemos que sumar 1 a la resta anterior.
=(T2-S2)+1
Tenemos que modificar la función SECUENCIA, el primer argumento es filas, es decir, de cuantas filas va a estas compuesta la matriz, pues, eso lo obtenemos con la función resta de la función CONTARA, menos COINCIDIR, sumándole 1.
= SECUENCIA(U2
El siguiente argumento de la funcion SECUENCIA es columnas, pues, ponemos 1.
El siguiente argumento es inicio, es decir, donde debe de empezar a contar, pues eso, ya sabemos que lo decide la función COINCIDIR.
=SECUENCIA(U2;1;S2)
Pues, esta es la función, que debemos de poner, como argumento número de columna, de la función BUSCARV, quedaría:
=BUSCARV(Q2;TVentas; SECUENCIA(U2;1;S2);FALSO)
Probemos, elijo IDTienda 11, y, el encabezado precio neto, vemos que nos devuelve desde precio neto hasta el último encabezado.
Lo siguiente, seria poner al lado del valor rescatado, el nombre del encabezado, cosa que nos va a resultar fácil, porque ya tenemos el número de columna, que nos lo devuelve la función SECUENCIA, pues, voy a usar la función INDICE, donde como argumento matriz, voy a seleccionar los encabezados, como argumento número de fila, ponemos 1, y, como argumento número de columna, ponemos la función SECUENCIA, que acabamos de usar, por supuesto, seria dinámico.
=INDICE(TVentas[#Encabezados];1;SECUENCIA(U2;1;S2;1))
Ya tenemos también, los encabezados.
Tenemos el modelo que suelo usar, habitualmente, que es ventas de productos en diferentes centros comerciales, provincias, y, diferentes fechas.
Lo primero que queremos calcular, es el total para cada último mes de cada trimestre, es decir, un año tiene cuatro trimestres, marzo, junio, septiembre, y, diciembre, pues el total, para cada uno de estos meses.
Este modelo está en formato de tabla, y, se llama datos.
Pues, empecemos…
En la celda G2, vamos a usar la función MES, para extraer el mes de cada fecha, para ello, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=MES(
Como argumento, seleccionamos la columna fecha, cerramos paréntesis, y, aceptamos.
=MES(Datos[Fecha])
Tenemos una columna con el número de mes.
Disponemos de una función, llamada RESIDUO, que nos calcula el resto de una división, donde, nos devuelve cero si el resto es exacto, y, uno, si no lo es.
Por ejemplo, si en una celda, escribo =RESIDUO(1;3), me devuelve el valor 1, porque la división no es exacta, pero, si ponga la siguiente expresión =RESIDUO(3;3), vemos que nos devuelve cero, porque la división es exacta.
Quiere decir, que puedo usar la función RESIDUO, para calcular el último trimestre, antes, de la función MES, usamos la función RESIDUO, tiene dos argumentos, que son número, y, numero divisor, el primer argumento es el numero a dividir, y, el segundo argumento, es por el número que vamos a dividir, pues, el argumento número, va a ser la función MES, y, en el argumento número divisor, va a ser 3.
Es decir, va a dividir cada número de mes, entre 3, solo nos va a devolver el valor cero, cuando la división sea exacta, y, será exacta, cuando se divida 3 entre 3, o, 6 entre 3, o, 9 entre 3, o, 12 entre 3, es decir, el mes de cada trimestre.
Probemos.
=RESIDUO(MES(Datos[Fecha]);3)
Aceptamos, y, vemos que donde el mes es tres (marzo), la función RESIDUO, devuelve cero.
Lo mismo pasa para el mes de junio.
El mes de septiembre.
Y, el mes de diciembre.
Estamos trabajando con el año 2017.
Lo siguiente que vamos a hacer, es filtrar la columna de total, siempre, que el resultado de RESIDUO sea cero, para ello, vamos a usar la función matricial FILTRAR.
Bien, pues, antes de la funcion RESIDUO, ponemos la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Como argumento, array, seleccionamos la columna de total.
=FILTRAR(Datos[Total];
Como argumento include, vamos a abrir un paréntesis, donde es la función RESIDUO, pero lo igualamos a cero, para que nos devuelva solo los totales, del último mes de cada trimestre, cerramos paréntesis de la condición, cerramos paréntesis de la función FILTRAR, y, aceptamos.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0))
Tenemos una matriz desbordada con los totales del último mes para cada trimestre.
Pero, queremos el último mes del primer trimestre, es decir, del mes de marzo.
Para ello, vamos a modificar la función FILTRAR, y, después del cierre de paréntesis de la primera condición, ponemos el símbolo de asterisco, que es igual que operador lógico Y, es decir, nos va a devolver VERDADERO, si todas las condiciones se cumplen.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*
Abrimos un paréntesis, y, vamos a poner la segunda condición, donde usamos la función MES en la columna de fecha, y, la igualamos a 3, para que nos devuelva solo los totales para el mes de marzo.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3))
Tenemos una matriz desbordada, solo con los valores del mes de marzo, pero, queremos el total, por lo que después del signo igual, usamos la función SUMA.
=SUMA(FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3)))
Ahora debemos de copiar hacia la derecha, por lo que voy a seleccionar la función, pulso CTRL más C, para copiar, selecciono las celdas H2, I2, y, J2, botón alternativo del ratón, y, seleccionamos pegar como fórmula, dentro de pegado especial.
En cada función, cambiamos el número de mes, por el que corresponda.
Ya tenemos el total del último mes de cada trimestre.
Mi pregunta es, ¿puedo hacerlo de otra forma?
Pues vamos a verlo.
En la celda G6, abro un paréntesis, uso la función MES en la columna de fecha, cierro paréntesis de la función MES, y, del primer paréntesis, y, aceptamos.
=(MES(Datos[Fecha]))
Obtenemos una matriz desbordada con el número de mes de cada fecha.
Pero, quiero que sea el mes 3, es decir, el último mes del primer trimestre, pues al final de la función, escribo el signo igual, y, el número 3.
=(MES(Datos[Fecha])=3)
Obtenemos una matriz desbordada con VERDADEROS, y, FALSOS, VERDADERO donde el número de mes es 3, y, FALSO, donde no lo es.
Y, si, después del cierre del último paréntesis, usamos el símbolo de asterisco, que ya sabemos que nos devolverá VERDADERO, si todas las condiciones se cumplen, donde selecciono la columna de total, y, cierro paréntesis.
=(MES(Datos[Fecha])=3)*(Datos[Total])
Pues, obtenemos una matriz desbordada con los valores del último mes del primer trimestre, y, el valor cera donde no lo es.
Igual que antes, como queremos el total, vamos a hacer uso de la función SUMA.
=SUMA((MES(Datos[Fecha])=3)*(Datos[Total]))
Podemos ver que obtenemos el mismo resultado.
Copiamos hacia la derecha, y, vemos que todos los resultados son iguales, lo único que debemos de cambiar, igual que antes, es el número de mes.
Esta fue la primera función que usamos:
=SUMA(FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3)))
Esta la segunda:
=SUMA((MES(Datos[Fecha])=3)*(Datos[Total]))
Entre las dos, la segunda expresión es mucho más corta, y, tenemos los mismos resultados, por lo que volvemos a recordar, que, en Excel, lo mismo se puede hacer de diferentes maneras.
Pero, todavía podemos hacerlo, de otra manera, seria con el condicional SI, donde voy a preguntar que si el mes de la columna fecha, es igual a 3, que me devuelva el resultado de la columna de total, en caso contrario, que me devuelva un texto en blanco, lo haremos en la celda G10.
=SI(MES(Datos[Fecha])=3;Datos[Total];"")
Obtenemos una matriz desbordada con los totales, solo para el mes de marzo.
Pero, como queremos el total, usamos, de nuevo, la función SUMA.
=SUMA(SI(MES(Datos[Fecha])=3;Datos[Total];""))
Vemos que obtenemos el mismo resultado del último mes del primer trimestre.
Volvemos a copiar hacia la derecha, y, seguimos teniendo los mismos resultados.
Vamos a ver un ejemplo de las funciones FILTRAR, TEXTOANTES, y, TEXTODESPUES.
Tenemos el siguiente modelo de datos, donde vemos que la provincia, el centro comercial, y, el producto, están unidos, separados por un delimitador, que es la coma.
Nos piden, tal cual está el modelo, sin tocar nada, que creemos una lista de provincias únicas, y, calcular los totales para cada provincia.
Vemos que en la columna A, tenemos unido provincia, centro, y, producto, por lo que debemos de quedarnos solo con la provincia, este sería el primer paso.
Primero, debemos de extraer la provincia, para ello, vamos a usar la función TEXTOANTES, lo haremos en la celda H2, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
Decir, que el modelo está en formato de tabla.
=TEXTOANTES (
El primer argumento es texto, pues seleccionamos la columna provincia/centro/producto.
=TEXTOANTES(Datos5[Provincia/Centro/Producto]
Punto y coma, el siguiente argumento es delimitador, pues entre comillas dobles, ponemos la coma, cerramos paréntesis, y, aceptamos.
=TEXTOANTES(Datos5[Provincia/Centro/Producto];",")
Tenemos una matriz desbordada, con todas las provincias.
Pero, estas provincias se repiten, y, queremos provincias únicas, por lo que después del signo igual, usamos la función UNICOS.
=UNICOS(TEXTOANTES(Datos5[Provincia/Centro/Producto];","))
También, la vamos a ordenar, por lo que vamos a usar la función ORDENAR, con los argumentos predeterminados.
=ORDENAR(UNICOS(TEXTOANTES(Datos5[Provincia/Centro/Producto];",")))
Ya tenemos las provincias únicas, y, ordenadas.
Lo siguiente es calcular el total para cada provincia.
Lo haremos en la celda I2, voy a usar la función FILTRAR, para que me filtre la columna total, por la primera provincia, para ello, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=FILTRAR(
Como primer argumento, que es array, seleccionamos la columna de total.
=FILTRAR(Datos5[Total]
El siguiente argumento es include, donde debemos de volver a usar la función TEXTOANTES, e, igualarlo al valor de la celda H2, que es la primera provincia.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(Datos5[Total];TEXTOANTES(Datos5[Provincia/Centro/Producto];",")=H2)
Tenemos una matriz desbordada con los totales para la primera provincia, que es Albacete.
Pero, como queremos los totales, después del signo igual, usamos la función SUMA.
=SUMA(FILTRAR(Datos5[Total];TEXTOANTES(Datos5[Provincia/Centro/Producto];",")=H2))
Ya tenemos el total para la primera provincia.
Arrastramos, porque no tenemos que fijar nada.
Ya tenemos los totales para cada provincia.
Lo siguiente que queremos hacer, es calcular el total para cada provincia, y, para cada producto.
La lista de provincias únicas, ya la tenemos, vamos a crear la lista de productos únicos, en la celda L1, pero en este caso, vamos a usar la función TEXTODESPUES.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=TEXTODESPUES(
El primer argumento es texto, pues seleccionamos la columna provincia/centro/producto.
=TEXTODESPUES(Datos5[Provincia/Centro/Producto]
Punto y coma, el siguiente argumento es, delimitador, pues entre comillas dobles, ponemos la coma.
=TEXTODESPUES(Datos5[Provincia/Centro/Producto];","
Punto y coma, el siguiente argumento es, a partir de que delimitador tengo que extraer, en este caso, a partir del delimitador 2, después del primer delimitador, tenemos centro, y, después del segundo delimitador, tenemos producto, por eso, ponemos el número 2.
Cerramos paréntesis, y, aceptamos.
=TEXTODESPUES(Datos5[Provincia/Centro/Producto];",";2)
Tenemos una matriz desbordada con los productos, pero igual que antes, se repiten, por lo volvemos a hacer uso de la función UNICOS.
=UNICOS(TEXTODESPUES(Datos5[Provincia/Centro/Producto];",";2))
Pero, lo quiero en horizontal, voy a hacer uso de la función TRANSPONER.
=TRANSPONER(UNICOS(TEXTODESPUES(Datos5[Provincia/Centro/Producto];",";2)))
Y ya lo tenemos.
Ahora, tenemos que calcular los totales, para ello, voy a hacer uso de la función FILTRAR, de nuevo, pero en este caso, debemos de especificar dos condiciones.
La función FILTRAR, hasta el primer argumento, es igual que la anterior, con la diferencia que la condición la debemos de encerrar entre paréntesis.
=FILTRAR(Datos[Total];(TEXTOANTES(Datos[Provincia/Centro/Producto];",")=K2)
Ahora, ponemos el símbolo de multiplicación (*), ya sabemos que es igual que el operador lógico Y, es decir, nos devuelve VERDADERO, si todas las condiciones que pongamos se cumplen.
Abrimos un paréntesis, para poner la siguiente condición, donde vamos a usar la sintaxis anterior de la función TEXTODESPUES, para extraer cada producto, y, lo igualamos al valor de la celda L1, donde se encuentra el primer producto.
Cerramos paréntesis de la condición, y, paréntesis de la función FILTRAR.
=FILTRAR(Datos[Total];(TEXTOANTES(Datos[Provincia/Centro/Producto];",")=K2)*(TEXTODESPUES(Datos[Provincia/Centro/Producto];",";2)=L1)))
Tenemos una matriz desbordada con los totales para la provincia de Albacete, del producto aspiradora.
Como queremos el total, después del signo igual, usamos la función SUMA.
=SUMA(FILTRAR(Datos[Total];(TEXTOANTES(Datos[Provincia/Centro/Producto];",")=K2)*(TEXTODESPUES(Datos[Provincia/Centro/Producto];",";2)=L1)))
Ahora, debemos de copiar tanto hacia abajo, como hacia la derecha, por lo que debemos de fijar la columna de K2, para que, al copiar hacia la derecha, la columna no se actualice, y, la fila de L1, para que, al copiar hacia abajo, la fila no se actualice.
=SUMA(FILTRAR(Datos[Total];(TEXTOANTES(Datos[Provincia/Centro/Producto];",")=$K2)*(TEXTODESPUES(Datos[Provincia/Centro/Producto];",";2)=L$1)))
Seleccionamos la celda con la función, pulsamos CTRL más C, para copiar, seleccionamos todo el rango, y, pulsamos CTRL más V, para pegar.
Ya tenemos todos los totales, pero vemos que hay sitios, donde no ha habido ventas, y, nos devuelve error, pues vamos a hacer uso de la función SI.ERROR, y, le decimos que si devuelve un error, que ponga un texto en blanco.
=SI.ERROR(SUMA(FILTRAR(Datos[Total];(TEXTOANTES(Datos[Provincia/Centro/Producto];",")=$K2)*(TEXTODESPUES(Datos[Provincia/Centro/Producto];",";2)=L$1)));"")
Ya tenemos hecho nuestro cuadro.
Vamos a ver un ejemplo, donde vamos a usar, de momento, la función FILTRAR.
Tenemos el modelo que suelo usar para pruebas, ventas de productos en diferentes centros comerciales, provincias, y, fechas, pero vemos que está separado, en la columna A, tenemos las provincias, y, a partir de la columna C, el resto de las columnas.
Vamos a filtrar el modelo, a partir de la columna C, donde vamos a decidir que columnas queremos ver, este modelo, a partir de la columna C, consta de 5 columnas, y, solo quiero ver la primera columna, es decir, la columna de centro comercial.
En una celda, escribimos el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Seleccionamos el rango desde C2 a G27, que es el argumento array.
=FILTRAR(C2:G27
Punto y coma, como argumento include, vamos a abrir unas llaves, como tenemos los encabezados en horizontal, debemos de usar la barra invertida en la constante de matriz (\), pues, voy a ir poniendo cero donde no quiero que aparezca la columna, y, 1 donde quiero que aparezca, en este caso, solo quiero la columna de centro de comercial, quedaría como sigue:
=FILTRAR(C2:G27;{1\0\0\0\0
Cerramos la llave de la constante de matriz, y, el paréntesis de la función.
=FILTRAR(C2:G27;{1\0\0\0\0})
Aceptamos, y, tenemos una matriz desbordada solo con los centros comerciales.
Pero, el ejercicio consiste en seleccionar una provincia, y, que nos devuelva la columna, o, columnas que queremos.
Lo primero que voy a hacer, es crear una lista de provincias, para ello, estas provincias no se repiten, para ello, voy a crear una validación de datos, para crear una lista desplegable, para seleccionar una provincia, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, donde desplegamos permitir, y, elegimos lista.
En la ventana origen, seleccionamos las provincias.
Aceptamos, y, seleccionamos una provincia.
Para este caso, quiero que me devuelva las columnas de cantidad, precio, y, total.
La expresión anterior, es decir, la función FILTRAR, va a ser el argumento matriz devuelta de la función BUSCARX.
En la celda donde escribimos la función FILTRAR, después del signo igual, ponemos la función BUSCARX, y, abrimos un paréntesis.
=BUSCARX(
Como argumento valor buscado, seleccionamos la provincia que hemos seleccionado.
=BUSCARX(I2;
Como argumento matriz de busqueda, seleccionamos la matriz donde debe de buscar la provincia.
=BUSCARX(I2;A2:A27;
Como argumento matriz devuelta, es la función FILTRAR, que hemos usado anteriormente.
Cerramos paréntesis y aceptamos.
=BUSCARX(I2;A2:A27;FILTRAR(C2:G27;{0\0\1\1\1}))
Tenemos la cantidad, el precio, y, el total de la provincia seleccionada.
Pero, me gustaría ver también los encabezados, pues en la celda superior a esta, vamos a volver a usar la función FILTRAR, va a ser igual que la anterior, lo único que va a cambiar es el argumento array, que ahora, va a ser los encabezados, quedaría:
=FILTRAR(C1:G1;{0\0\1\1\1})
Ya tenemos también, los encabezados dinámicos.
El problema, es cuando queremos agregar, o, quitar una columna, debemos de hacerlo en las funciones, pero podemos crear una especie de contador con ceros, y, unos, haciendo referencia a las columnas que queremos, o no queremos.
Vamos a ir a cada expresión, y, sustituimos la constante de matriz en ambas expresiones, y, seleccionamos el rengo donde hemos puesto el contador.
=FILTRAR(C1:G1;K1:O1)
=BUSCARX(I2;A2:A27;FILTRAR(C2:G27;K1:O1))
Tenemos los mismos resultados, ahora, cuando queremos seleccionar columnas, solo debemos de cambiarla en el contador.
En el siguiente ejemplo, tenemos una serie de nombres junto con su primer, y, segundo apellido, su teléfono, y, su ID correspondiente.
Queremos elegir un nombre, primer y segundo apellido, y, que nos devuelva su ID, para este ejercicio, usaremos la función BUSCARX.
Vamos a crear un primer desplegable en la celda G2, para seleccionar un nombre, en el modelo los nombres se repiten, por lo que debemos de crear una lista única, para ello, con una celda dentro del modelo, vamos a la pestaña de datos, dentro del grupo ordenar y filtrar, hacemos clic en avanzadas.
Se abre la ventana filtro avanzado, en la ventana rango de la lista, seleccionamos la columna B.
Marcamos copiar a otro lugar.
En la ventana copiar a, seleccionamos la celda donde vamos a ponerla.
Marcamos la casilla solo registros únicos, y, aceptamos.
Ya tenemos los nombres únicos, junto con el encabezado.
También, podríamos hacerlo con la función ORDENAR, y, UNICOS, de la siguiente manera:
=ORDENAR(UNICOS(B2:B14))
Pues, seleccionamos la celda G2, 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, y, en la ventana origen, en mi caso, la lista única la he realizado con la función UNICOS, y, ORDENAR, por lo que seleccione la celda L1, y, añado el operador de rango derramado (#), de esta manera, nos aseguramos de que el rango sea dinámico.
Aceptamos.
La segunda lista se debe de rellenar con los primeros apellidos del nombre seleccionado, para ello, voy a usar el condicional SI, donde voy a preguntar si en el rango B2:B14, es igual al valor de la celda G2, que me devuelva la misma posición, pero del rango C2:C14, en caso contrario, que ponga un texto en blanco, lo haremos en la celda M2.
=SI(B2:B14=G2;C2:C14;””)
Aceptamos, y, tenemos una matriz desbordada con los apellidos correspondiente al nombre seleccionado, y, donde no hay coincidencia, pone un texto en blanco.
También, podría hacerlo con la función FILTRAR, donde como argumento array, selecciono la columna apellido1, y, como argumento include, selecciono la columna nombre, y, lo igualo al nombre seleccionado.
=FILTRAR(C2:C14;B2:B14=G2)
Tenemos una matriz desbordada, con los apellidos que corresponden al nombre seleccionado, la diferencia con la expresión, donde hemos usado el condicional SI, es que la función FILTRAR, solo devuelve los valores donde hay coincidencias, por lo que no devuelve celdas en blanco.
Debido a la actualización de validación de datos, cuando creamos una lista donde se incluyen valores en blanco, estos son ignorados, por lo que podemos usar cualquiera de las funciones, usadas anteriormente, ahora, voy a crear directamente la lista, para ello, en la celda H2, 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, y, en la ventana origen, voy a usar la expresión con el condicional SI, pongo la celda M1, seguido del operador de rango derramado.
Aceptamos.
Vamos a por la tercera lista, que es el apellido 2, para este caso, debe de coincidir tanto el nombre, como el apellido 1, lo haremos en la celda N1.
Si usamos el condicional SI, seria anidado, porque debemos de realizar dos preguntas, como sigue:
=SI(B2:B14=G2;SI(C2:C14=H2;D2:D14;"");"")
Obteniendo una matriz desbordada con el o los apellidos donde hay coincidencia, y, una celda en blanco, donde no la hay.
Ahora, lo hacemos, pero con la función FILTRAR, donde como argumento array, seleccionamos la columna de apellido 2, como argumento include, entre paréntesis, ponemos la primera condición, que es que el nombre de la columna nombre, sea igual al seleccionado, y, la siguiente condición, es que el apellido 1, de la columna apellido 1, sea igual al apellido 1 seleccionado, todo ello separado por el símbolo de asterisco.
=FILTRAR(D2:D14;(B2:B14=G2)*(C2:C14=H2))
Aceptamos, y, tenemos una matriz desbordada con los apellidos que corresponden, pero sin espacios en blanco.
Ya tenemos los datos para poder buscarlo, que lo haremos con la función BUSCARX.
Bien, en la celda J2, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=BUSCARX(
Como argumento valor buscado, concatenamos el nombre, apellido1, y, apellido2.
=BUSCARX(G2&H2&I2;
Como argumento matriz de búsqueda, seleccionamos la columna de nombre, la concatenamos con la columna de apellido 1, y, concatenamos con la columna de apellido 2.
=BUSCARX(G2&H2&I2;B2:B14&C2:C14&D2:D14;
Y, como argumento matriz devuelta, seleccionamos la columna de ID.
Cerramos paréntesis, y, aceptamos.
=BUSCARX(G2&H2&I2;B2:B14&C2:C14&D2:D14;A2:A14)
Obtenemos el ID de la persona que hemos seleccionado.
También, podría hacerlo con la función FILTRAR en la celda J3, pero concatenando.
=FILTRAR(A2:A14;(B2:B14&C2:C14&D2:D14=G2&H2&I2))
Obtenemos el mismo resultado.
Miguel Angel Franco
Comments