Para el siguiente ejemplo, tenemos el modelo que suelo usar habitualmente, pero tenemos diversas columnas vacías.
Debemos de crear un informe, pero sin esas columnas vacías.
Primero, vamos a preguntar con el condicional SI, que, si la tabla ventas es diferente a blanco, que devuelva la fila de la tabla ventas, en caso contrario, debe de poner un texto en blanco.
=SI(Ventas<>"";FILA(Ventas);"")
Aceptamos, y, tenemos una matriz desbordada con el numero de filas y columnas del modelo, con las filas donde hay coincidencia, y, blanco donde no la hay.
Como tenemos siete columnas, tenemos siete columnas repetidas.
Vamos a trabajar con la primera fila, por lo que vamos a usar la función INDICE, donde como argumento array es la expresión anterior, omitimos el argumento numero de fila, y, como argumento numero de columna, ponemos 1.
=INDICE(SI(Ventas<>"";FILA(Ventas);"");;1)
Aceptamos.
Filtramos la expresión anterior, siempre que dicha expresión, sea diferente a blanco.
=FILTRAR(INDICE(SI(Ventas<>"";FILA(Ventas);"");;1);INDICE(SI(Ventas<>"";FILA(Ventas);"");;1)<>"")
Usamos la función LET, para almacenar en una variable la expresión INDICE, y, la vamos sustituyendo.
=LET(a;INDICE(SI(Ventas<>””;FILA(Ventas);””);;1);FILTRAR(a;a<>””))
Nos queda rescatar los registros, para ello, volvemos a usar la función INDICE, donde como argumento array, seleccionamos las columnas A:G.
=INDICE(A:G
Punto y coma, como argumento numero de fila, es la función LET.
=INDICE(A:G; LET(a;INDICE(SI(Ventas<>"";FILA(Ventas);"");;1);FILTRAR(a;a<>""))
Punto y coma, como argumento numero de columnas, usamos la función SECUENCIA, donde omitimos el argumento filas, y, como argumento columnas, ponemos 7, pues 7 columnas son de las que disponemos.
Cerramos paréntesis.
=INDICE(A:G;LET(a;INDICE(SI(Ventas<>"";FILA(Ventas);"");;1);FILTRAR(a;a<>""));SECUENCIA(;7))
Aceptamos, y, ya tenemos nuestro informe sin filas vacías.
Para traernos el encabezado, vamos a usar la función APILARV (VSTOCK), donde como argumento matriz1, nos traemos los encabezados.
=APILARV(Ventas[#Encabezados]
Punto y coma, como argumento matriz2, es la expresión anterior.
Cerramos paréntesis.
=APILARV(Ventas[#Encabezados];INDICE(A:G;LET(a;INDICE(SI(Ventas<>"";FILA(Ventas);"");;1);FILTRAR(a;a<>""));SECUENCIA(;7)))
Aceptamos, y, ya lo tenemos.
Ahora, tenemos el mismo modelo, con filas vacías, pero también, tenemos la columna cantidad vacía.
Debemos de realizar el mismo informe.
Igual que antes, voy a preguntar que, si ventas es diferente a blanco, en este caso, que devuelva los encabezados, y, omitimos el argumento valor si falso, por lo que, si no hay coincidencia, devolverá FALSO.
=SI(Ventas2<>"";Ventas2[#Encabezados])
Obtenemos una matriz desbordada con el mismo numero de filas que el modelo y de columnas, pero todas con el mismo valor.
Nos quedamos con los valores únicos.
=UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados]))
Filtramos la expresión anterior, siempre que dicha expresión sea diferente a FALSO, pero si usamos la función FILTRAR y tenemos los datos en columnas, nos devolverá un error, por lo que debemos de poner los datos en vertical, es decir, en una sola columna, para ello, delante del signo igual, usamos la función ENCOL.
=ENCOL(UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados])))
Creamos una función LET y almacenamos la expresión anterior en una variable.
=LET(a;ENCOL(UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados])))
Filtramos la expresión anterior, siempre que sea diferente a FALSO.
=LET(a;ENCOL(UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados])));FILTRAR(a;a<>FALSO))
Ya tenemos los encabezados a usar en vertical.
Transponemos.
=TRANSPONER(LET(a;ENCOL(UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados])));FILTRAR(a;a<>FALSO)))
Vemos que no se encuentra el encabezado cantidad, estos son los encabezados a recuperar.
Concatenamos entre comillas dobles, el nombre de la tabla, y, una apertura de paréntesis, concatenamos con la expresión anterior, y, concatenamos entre comillas dobles, con un cierre de corchete.
="Ventas2[" & TRANSPONER(LET(a;ENCOL(UNICOS(SI(Ventas2<>"";Ventas2[#Encabezados])));FILTRAR(a;a<>FALSO))) & "]"
Ya tenemos la sintaxis de cada encabezado para poder ser recuperados.
En la celda siguiente, usamos la función INDIRECTO, y, como argumento es la celda I2, que es el primer encabezado a recuperar.
=INDIRECTO(I2)
Tenemos una matriz desbordada con las fechas, pero nos falta anular las filas en blanco, vemos como las filas en blanco aparecen como cero (00/01/1900).
Lo único que tenemos que hacer es filtrar la expresión anterior, siempre que dicha expresión sea diferente a cero.
=FILTRAR(INDIRECTO(I2);INDIRECTO(I2)<>0)
Aceptamos y ya lo tenemos.
Arrastramos hacia la derecha, y, ya tenemos nuestro informe.
Lo siguiente, es crear el mismo informe, pero en este caso, tenemos solo celdas vacías.
Debemos de crear el mismo informe, pero debemos de eliminar cada fila donde haya una celda vacía.
Empecemos…
Vamos a preguntar que, si ventas3 es igual a blanco, que devuelva la fila, en caso contrario, que devuelva un texto en blanco.
=SI(Ventas3="";FILA(Ventas3);"")
Obtenemos una matriz desbordada con blanco donde no hay coincidencia, y, el numero de fila donde hay coincidencia.
Ahora, debo de filtrar, y, dejar solo los números, si usamos la función FILTRAR para filtrar la expresión anterior, siempre que sea distinto a blanco.
=FILTRAR(SI(Ventas3="";FILA(Ventas3);"");SI(Ventas3="";FILA(Ventas3);"")<>"")
Obtenemos el error VALOR.
Este problema surge porque no podemos filtrar varios valores a la vez, vemos que tenemos valores en filas y columnas, por lo que no puede filtrar por todos los valores de una columna, para poder filtrar, debemos de tener los datos en una sola columna.
Para poner los datos en una única columna, vamos a usar la función ENCOL.
=ENCOL(SI(Ventas3="";FILA(Ventas3);""))
Ahora, si podemos filtrar, pero, primero usamos LET y en una variable almacenamos la función expresión anterior, porque la vamos a usar dos veces.
=LET(a;ENCOL(SI(Ventas3="";FILA(Ventas3);""))
Ahora, filtramos.
=LET(a;ENCOL(SI(Ventas3="";FILA(Ventas3);""));FILTRAR(a;a<>""))
Ya tenemos las filas de los registros que no deseamos.
Ahora, usamos la función APILARV (VSTOCK), donde como argumento matriz1 son las filas de la tabla ventas3.
=APILARV(FILA(Ventas3)
Punto y coma, como argumento matriz2, en la función LET.
Cerramos paréntesis.
=APILARV(FILA(Ventas3);LET(a;ENCOL(SI(Ventas3="";FILA(Ventas3);""));FILTRAR(a;a<>"")))
Ordenamos.
=ORDENAR(APILARV(FILA(Ventas4);LET(a;ENCOL(SI(Ventas4="";FILA(Ventas4);""));FILTRAR(a;a<>""))))
Obtenemos la matriz desbordada ordenada, donde vemos que aparecen filas repetidas, que son las filas que no deben de aparecer.
En la celda contigua, usamos CONTAR.SI, donde como argumento rango es la matriz desbordada obtenida anteriormente, así como el argumento criterio.
=CONTAR.SI(I2#;I2#)
Obtenemos una matriz desbordada con las veces que aparece cada cantidad, vemos que donde la cantidad aparece mas de una vez, aparece el número de veces que aparece.
Debemos de quedarnos con los valores que son iguales a 1, por lo que filtramos.
=FILTRAR(H2#;CONTAR.SI(H2#;H2#)=1)
Ya tenemos las filas a recuperar de la tabla.
Dichas filas la vamos a recuperar con la función INDICE, donde como argumento matriz, seleccionamos las columnas desde A a F.
=INDICE(A:F
Punto y coma, como argumento numero de fila es la matriz desbordada obtenida.
=INDICE(A:F;I2#
Punto y coma, como argumento numero de columna, podemos poner directamente 6, o, calcularlo por si los datos aumentan o disminuyen, cosa que hacemos con la función SECUENCIA, donde omitimos el argumento filas, y, como argumento columnas, es la función CONTARA en los encabezados.
Cerramos paréntesis y aceptamos.
=INDICE(A:F;I2#;SECUENCIA(;CONTARA(Ventas3[#Encabezados])))
Ya tenemos nuestro modelo sin las celdas vacías.
Nos traemos los encabezados con APILARV (VSTACK) como hemos hecho en videos anteriores.
=APILARV(Ventas3[#Encabezados];INDICE(A:F;I2#;SECUENCIA(;CONTARA(Ventas3[#Encabezados]))))
Miguel Angel Franco Garcia
Comentários