En el siguiente ejemplo, vamos a trabajar con el modelo que suelo usar habitualmente, con la diferencia de que hay una columna en blanco.
Debemos de crear el mismo informe, pero si hay columnas en blanco, deben de ser omitidas.
El modelo está en formato de tabla, y, se llama ventas.
Vamos a ver dos formas de hacerlo, una mas larga que la otra.
Veamos la forma mas larga, en la celda J2, voy a traerme los encabezados, para ello, uso la siguiente sintaxis:
=Ventas[#Encabezados]
Con la función INDIRECTO, voy a traerme la columna de la celda J2, es decir, la columna fecha, para ello, después de la apertura de paréntesis, entre comillas dobles, ponemos el nombre de la tabla y una apertura de corchete, y, cerramos paréntesis.
=INDIRECTO("Ventas["
Concatenamos con el valor de la celda J2.
=INDIRECTO("Ventas["&J2
Concatenamos con un cierre de corchete, entre comillas dobles.
Cerramos paréntesis.
=INDIRECTO("Ventas["&J2&"]")
Para dirigirme a los datos de la columna fecha, debo de poner el nombre de la tabla, y, entre corchetes el nombre de la columna a recuperar, como sigue:
Ventas[Fecha]
Pues este es el resultado de la función INDIRECTO.
Aceptamos.
Obtenemos la columna fecha de la tabla ventas.
Después del signo igual, voy a usar la función ESBLANCO, que me devolverá VERDADERO si toda la columna fecha es blanco, y, FALSO si no lo es.
=ESBLANCO(INDIRECTO("Ventas["&J2&"]"))
Obtenemos FALSO para toda la columna.
Ahora voy a preguntar que, si el resultado de la función ESBLANCO es iguala FALSO, que ponga 1, en caso contrario, que ponga 0.
=SI(ESBLANCO(INDIRECTO("Ventas["&J2&"]"))=FALSO;1;0)
En este caso, como la columna fecha devuelve FALSO nos pone 1.
Arrastramos hacia la derecha.
Obtenemos 1 donde ESBLANCO devuelve FALSO, y, 0 donde devuelve VERDADERO.
Vemos que para la columna Columna1, devuelve cero, pues, esta expresión es el argumento include de la función FILTRAR que vamos a usar.
En la celda J6, ponemos la función FILTRAR, como argumento array, ponemos la tabla ventas, abrimos un corchete y seleccionamos todo, para que nos traiga también los encabezados.
=FILTRAR(Ventas[#Todo]
Punto y coma, como argumento include, seleccionamos los 1 y 0 que hemos obtenido.
=FILTRAR(Ventas[#Todo];J1:Q1)
Aceptamos, y, vemos que tenemos la tabla ventas sin la o las columnas vacías.
He añadido una nueva columna, y, nos devuelve un error.
En los encabezados tenemos una nueva columna, que es columna2.
En la función de la celda K1:
=UNICOS(SI(ESBLANCO(INDIRECTO("Ventas["&K2&"]"))=FALSO;1;0))
La arrastramos hasta el ultimo encabezado, en ese momento, por lo que nos queda que arrastrar hacia la derecha una vez más.
Una forma de arreglarlo es usando el condicional SI, y, preguntando si el valor de la celda K2 es diferente a blanco.
=SI(K2<>""
Punto y coma, como argumento valor si verdadero, es la expresión anterior.
=SI(K2<>"";UNICOS(SI(ESBLANCO(INDIRECTO("Ventas["&K2&"]"))=FALSO;1;0))
Punto y coma, como argumento valor si falso, ponemos un texto en blanco.
Cerramos paréntesis.
=SI(K2<>"";UNICOS(SI(ESBLANCO(INDIRECTO("Ventas["&K2&"]"))=FALSO;1;0));"")
Aceptamos, y, la copiamos hacia la derecha una cantidad mayor al número de encabezados.
Pero, mejor seria hacerlo de forma dinámica, y, no tener que arrastrar, para ello, con la siguiente expresión, =Ventas, me traigo los datos de la tabla ventas.
Pregunto si es blanco la tabla ventas.
=ESBLANCO(Ventas)
Obtengo una matriz desbordada con VERDADERO donde es blanco y FALSO donde no lo es.
Me quedo con los valores únicos.
=UNICOS(ESBLANCO(Ventas))
Ahora, pregunto que, si el resultado de la matriz desbordada es igual a FALSO que ponga 1, en caso contrario, que ponga 0.
=SI(UNICOS(ESBLANCO(Ventas))=FALSO;1;0)
Pues, ya tenemos nuestros unos y ceros de forma dinámica.
Sustituimos los unos y ceros anteriores por estos.
Vemos que la tabla a devolver también nos devuelve error, hemos usado la función FILTRAR para obtener la tabla:
=FILTRAR(Ventas[#Todo];K1:R1)
Tenemos el mismo problema que antes, ahora, debe de ir hasta S1, pero queremos que sea de forma dinámica, que no tengamos que arrastrar.
En la celda K2, comienza los encabezados que trajimos con la función con la función =Ventas[#Encabezados], y, acaba en S2.
En estos encabezados, ya tenemos la nueva columna.
Quiero conseguir columna del ultimo encabezado, si uso la función COLUMNA, y, como argumento pongo la celda K2 junto con el operador de rango derramado, y, cierro paréntesis.
=COLUMNA(K2#)
Obtengo una matriz desbordada en horizontal con el numero de columna de cada encabezado.
Pero me interesa, solo, la ultima columna, por lo que voy a usar la función MAX.
=MAX(COLUMNA(K2#))
Obtengo el número 19.
Voy a hacer lo mismo para obtener la fila, pero con la función FILA, en este caso, no voy a usar el operador de rango derramado, porque necesito la primera fila, pero debemos de restar 1, porque los números los tenemos en la fila 1.
=FILA(K2)-1
Obtengo el número 2.
Ahora, voy a usar la función DIRECCION, que dándole un numero de fila, y, un numero de columna, nos lo convierte en referencia, el argumento fila es la función FILA que hemos usado, y, el argumento columna, es la función COLUMNA usada anteriormente.
=DIRECCION(FILA(K2#)-1;MAX(COLUMNA(K2#)))
Si uso esta función como argumento referencia de la función INDIRECTO, obtendré los 1 y 0.
Seleccionamos y copiamos la función.
Vamos a la función FILTRAR.
=FILTRAR(Ventas[#Todo];K1:R1))
Debemos de modificar el argumento include, donde vamos a usar la función INDIRECTO, el argumento referencia, entre comillas dobles, ponemos K1: y concatenamos con la función DIRECCION.
=FILTRAR(Ventas[#Todo];INDIRECTO("k1:" &DIRECCION(FILA(K2#)-1;MAX(COLUMNA(K2#)))))
Aceptamos, y, vemos como ahora tenemos el modelo sin las columnas en blanco.
He eliminado una de las columnas en blanco, y, me vuelve a aparecer el error, pero el error esta en la función FILTRAR, en el argumento include, hemos puesto entre comillas dobles “K1:”, pero cuando añadimos o quitamos columnas, la celda K1, donde empieza el encabezado cambia, y, la tenemos fija, por lo que debemos de hacerla dinámica.
=FILTRAR(Ventas[#Todo];INDIRECTO("k1:" &DIRECCION(FILA(J2#)-1;MAX(COLUMNA(J2#)))))
Es igual que hemos hecho para calcular la ultima columna, pero necesitamos la primera fila, y, primera columna, para ello, volvemos a usar la función COLUMNA, y, usamos la función MIN para obtener la primera columna.
=MIN(COLUMNA(J2#))
Nos devuelve 10.
Con la siguiente expresión, obtenemos la primera fila.
=FILA(Ventas[#Encabezados])
No obtenemos una matriz desbordada, solo la primera fila.
Volvemos a usar la función DIRECCION.
=DIRECCION(FILA(Ventas[#Encabezados]);MIN(COLUMNA(J2#)))
Obtenemos la referencia J1 en absoluto.
Seleccionamos y copiamos la función, vamos a la función FILTRAR, sustituimos K1 por la función anterior, concatenamos con los puntos y concatenamos con la función DIRECCION donde hemos obtenido la ultima referencia.
=FILTRAR(Ventas[#Todo];INDIRECTO(DIRECCION(FILA(Ventas[#Encabezados]);MIN(COLUMNA(J2#))) & ":" &DIRECCION(FILA(J2#)-1;MAX(COLUMNA(J2#)))))
Aceptamos, y, tenemos nuestro modelo sin las columnas en blanco, si probamos a quitar y poner columnas, veremos como ya no tenemos problemas.
Una forma mas sencilla donde usaremos una única función es la siguiente.
Volvemos a usar la función FILTRAR, donde como argumento array, es toda la tabla ventas, incluido los encabezados.
=FILTRAR(Ventas[#Todo]
Punto y coma, como argumento include, preguntamos si no es blanco la tabla ventas, y, nos quedamos con los valores únicos.
Cerramos paréntesis.
=FILTRAR(Ventas[#Todo];UNICOS(NO(ESBLANCO(Ventas))))
Pues, ya lo tenemos.
Creo que esta última opción es la mas oportuna, pero en el primer ejemplo, hemos aprendido mucho.
Pero y si en vez de tener columnas vacías, tenemos filas vacías.
Bien, voy a preguntar si es blanco la tabla ventas2.
=ESBLANCO(Ventas2)
Obtenemos una matriz desbordada con FALSO donde no es blanco y VERDADERO donde lo es.
Pregunto si es blanco la tabla ventas, que ponga 1, en caso contrario, que ponga 0.
=SI(ESBLANCO(Ventas2)=FALSO;1;0)
Obtenemos una matriz desbordada con 1 donde es FALSO y 0 donde es VERDADERO, este resultado lo obtenemos para columnas.
Con la función INDICE voy a quedarme con la primera columna, por lo que omito el argumento número de fila.
=INDICE(SI(ESBLANCO(Ventas24)=FALSO;1;0);;1)
Pues esta columna con ceros y unos es el argumento include, de la función FILTRAR que vamos a usar.
=FILTRAR(Ventas2[#Todo];INDICE(SI(ESBLANCO(Ventas2[#Todo])=FALSO;1;0);;1))
Pues ya tenemos nuestro modelo sin las filas vacías.
留言