Para el siguiente ejemplo, vamos a trabajar con el modelo que suelo usar habitualmente, pero mas corto y con algunas modificaciones.
Vemos que tenemos la provincia de Granada, siguiendo el orden, la siguiente provincia es Huelva, pues, queremos crear un informe donde no aparezca Granada si la siguiente, siguiendo el orden, es Huelva.
Vamos a empezar el ejercicio, y, lo vamos viendo paso a paso.
Lo primero es crear una lista de provincias únicas, y, ordenarlas, de esa manera, sabemos el orden de las provincias, para ello, vamos a usar la función ORDENAR, y, UNICOS.
=ORDENAR(UNICOS(Ventas[Provincia]))
Ya tenemos las provincias ordenadas, podemos ver que después de Granada, viene Huelva.
Lo siguiente es hacer parejas en horizontal, para ello, con el condicional SI voy a preguntar que si el valor de B2, vemos que cuando hacemos clic en la celda, aparece Ventas[@Provincia], quiere decir que va a actuar en cada provincia es igual al valor de la celda I2 junto con el operador de rango derramado (#), para que seleccione todas las provincias (prueba lógica).
=SI(Ventas[@Provincia]=I2#
Punto y coma, como argumento valor si verdadero, debe de devolverme la fila de I2#, lo concatenamos con un espacio, y, lo concatenamos con la fila de I2# mas 1.
=SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1
Punto y coma, como argumento valor si falso, que ponga un texto en blanco.
Cerramos paréntesis.
=SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;"")
Aceptamos, y, tenemos una matriz desbordada con blanco donde no hay coincidencia, y, los números de filas donde hay coincidencia.
Vamos a concatenar con la función CONCAT, que ignora los espacios en blanco, pero si el valor esta en formato de número, lo pasa a formato de texto.
=CONCAT(SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;""))
Vemos como se ha alineado a la izquierda.
Ahora, dividimos el texto, porque debemos de trabajar con cada fila, para ello, vamos a usar la función DIVIDIRTEXTO, donde como argumento texto es la expresión anterior.
=DIVIDIRTEXTO(CONCAT(SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;""))
Punto y coma, como argumento delimitador de columna, ponemos un espacio.
Cerramos paréntesis.
=DIVIDIRTEXTO(CONCAT(SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;""));" ")
Aceptamos, y, ya tenemos los números separados en una matriz desbordada en horizontal.
Vamos a usar la función ABS que devuelve el valor absoluto de un valor, si es número, lo pasara a formato de número.
=ABS(DIVIDIRTEXTO(CONCAT(SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;""));" "))
Vemos como han quedado alineados a la derecha.
Lo siguiente e usar la función INDICE, donde como argumento matriz es la columna I, donde tenemos las provincias únicas ordenadas.
=INDICE(I:I
Punto y coma, como argumento numero de fila es la expresión anterior, y, omitimos el argumento numero de columna, y, de forma predeterminada va a poner 1 columna.
Cerramos paréntesis.
=INDICE(I:I;ABS(DIVIDIRTEXTO(CONCAT(SI(Ventas[@Provincia]=$I$2#;FILA($I$2#)&" "&FILA($I$2#)+1;""));" ")))
Aceptamos, y, ya tenemos la pareja a buscar.
Ahora, vamos a preguntar que, si en la columna provincia se encuentra la primera provincia, que ponga 1, pero si además, se encuentra la segunda provincia, que también ponga 1, para ello, usamos el condicional SI.
Vamos a preguntar que si la columna de provincia es igual a la primera columna de la matriz desbordada donde tenemos la pareja de provincias, por lo que debemos de usar la función INDICE, omitir el argumento numero de fila, y, como argumento numero de columna, ponemos 1 (prueba lógica).
=SI(Ventas[Provincia]=INDICE(K2#;;1)
Punto y coma, como argumento valor si verdadero, ponemos 1.
=SI(Ventas[Provincia]=INDICE(K2#;;1);1
Punto y coma, como argumento valor si falso, vamos a usar otro condicional SI, para preguntar que, si no es la primera provincia, vamos a preguntar por la segunda provincia, de nuevo, debemos de usar la función INDICE, pero en este caso, que nos devuelva la columna 2.
=SI(Ventas[Provincia]=INDICE(K2#;;1);1;SI(Ventas[Provincia]=INDICE(K2#;;2)
Punto y coma, como argumento valor si verdadero del segundo SI, que de nuevo ponga 1.
=SI(Ventas[Provincia]=INDICE(K2#;;1);1;SI(Ventas[Provincia]=INDICE(K2#;;2);1
Punto y coma, como argumento valor si falso del segundo SI, debe de poner 0.
Cerramos paréntesis.
=SI(Ventas[Provincia]=INDICE(K2#;;1);1;SI(Ventas[Provincia]=INDICE(K2#;;2);1;0))
Aceptamos, y, tenemos una matriz desbordada en vertical, donde vemos 1 donde hay coincidencia, y, 0 donde no la hay, pero donde la provincia es Granada, y, la siguiente es Huelva, tenemos una pareja de 1.
Pues, estos 0 y 1 va a ser el argumento include de la función FILTRAR que vamos a usar, para obtener nuestro informe.
Vamos a realizar el filtro en una hoja nueva, por lo que añadimos una hoja nueva, en la celda B2, ponemos la función FILTRAR, como argumento array, es la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, es la expresión anterior, siempre que el resultado de dicha expresión sea igual a 0.
Cerramos paréntesis.
=FILTRAR(Ventas;Ejemplo!M2#=0)
Aceptamos, y, tenemos nuestro modelo sin las provincias de Granada y Huelva.
Como hemos hecho en videos anteriores nos traemos los encabezados.
=APILARV(Ventas[#Encabezados];FILTRAR(Ventas;Ejemplo!M2#=0))
Y ponemos los bordes dinámicos.
Una forma de hacerlo, algo mas corto, es con la función FILTRAR, donde como argumento array, es la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, debemos de poner dos condiciones, pues abrimos un paréntesis para poner la primera condición, dicha condición es que la columna provincia sea diferente al valor de K2 de la hoja ejemplo.
=FILTRAR(Ventas;(Ventas[Provincia]<>Ejemplo!K2)
Ponemos el símbolo de asterisco, abrimos otro paréntesis, y, la siguiente condición que la columna provincia sea diferente al valor de la celda L2 de la hoja ejemplo.
Cerramos paréntesis.
=FILTRAR(Ventas;(Ventas[Provincia]<>Ejemplo!K2)*(Ventas[Provincia]<>Ejemplo!L2))
Tenemos los mismos resultados.
Miguel Angel Franco Garcia
Comments