Informe sin errores

Para este ejemplo, tenemos el modelo que suelo usar habitualmente, pero, por error en la columna centro, se ha repetido en algunos centros la misma palabra.

Debemos de crear un nuevo reporte, o, informe sin esos registros donde una palaba aparece repetida.

El modelo esta en formato de tabla, y, se llama Ventas.

Como dentro de una tabla, no podemos usar funciones matriciales, la vamos a realizar en un columna aparte, , después la movemos a la tabla.

En la celda I2, vamos a usar la función APILARH, donde como argumento matriz1, usamos la función ENCOL, para obtener los resultados en filas, como argumento matriz de ENCOL, usamos la función DIVIDIRTEXTO, donde como argumento texto es la columna C2, como argumento delimitador de columna, ponemos un espacio, cerramos paréntesis.

=APILARH(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" "))

Como argumento matriz2, es la misma expresión que la anterior, pero, usamos la función UNICOS.

Cerramos paréntesis.

=APILARH(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" "));UNICOS(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" "))))

Aceptamos, tenemos una matriz desbordada de dos columnas, en la primera columna, tenemos cada cadena de la celda C2, en la segunda columna, tenemos los valores únicos de las cadenas de la celda C2.

Vamos a usar LET, creamos una variable, y, almacenamos la expresión anterior.

Probamos la variable.

=LET(a;SI.ERROR(APILARH(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" "));UNICOS(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" "))));"");a)

Vamos a crear otra variable, donde vamos a buscar la primera columna de la variable “a”, en la segunda columna de la variable “a”.

=LET(a;SI.ERROR(APILARH(ORDENAR(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")));ORDENAR(UNICOS(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")))));"");b;ENCONTRAR(INDICE(a;;1);INDICE(a;;2))

Probamos la variable.

=LET(a;SI.ERROR(APILARH(ORDENAR(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")));ORDENAR(UNICOS(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")))));"");b;ENCONTRAR(INDICE(a;;1);INDICE(a;;2));b)

Obtenemos una matriz desbordada en vertical con 1 donde hay coincidencia, y, error donde no la hay.

¿Cómo salen estos valores?

Bien, estos son los valores para comparar.

Compara el primer valor de la primera columna con el primer valor de la segunda columna, el segundo valor de la primera columna con el segundo valor de la segunda columna, y, el tercer valor de la primera columna con el tercer valor de la segunda columna.

Como en la tercera columna no hay valores a comparar, porque el tercer valor de la segunda columna esa vacío.

Como argumento calculo de LET, vamos a filtrar la variable “b”, si la variable “b” devuelve un error, pero, como puede que nos devuelva más de un valor vamos a usar la función ENFILA en la variable “b”, para que los resultados nos lo de en filas, y, así podemos arrastrar.

=LET(a;SI.ERROR(APILARH(ORDENAR(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")));ORDENAR(UNICOS(ENCOL(DIVIDIRTEXTO(Ventas[@Centro];" ")))));"");b;ENCONTRAR(INDICE(a;;1);INDICE(a;;2));FILTRAR(ENFILA(b);ESERROR(ENFILA(b));""))

Para la celda C2, obteneos un error, quiere decir que hay una palabra repetida.

Arrastramos, obtenemos error donde hay una palabra repetida, y, blanco donde no lo hay.

Movemos todas las expresiones a H2, para que forme parte de la tabla, en algunas celdas vemos un error de desbordamiento, porque devuelve más de un valor, y, una tabla no puede desbordad, pero este error de desbordamiento es lo mismo que un error.

Cambiamos el nombre al encabezado.

En una celda, usamos la función FILTRAR, como argumento array, es la tabla Ventas, como argumento include, ponemos la función NO, como argumento de NO, ponemos la función ESERROR, como argumento de ESERROR, ponemos la columna que hemos añadido.

Cerramos paréntesis.

Es decir, queremos que nos filtre la tabla Ventas, siempre que la nueva columna no sea un error.

=FILTRAR(Ventas;NO(ESERROR(Ventas[Filtro])))

Pero, no queremos que aparezca esa columna que hemos añadido, por lo que modificamos el argumento array de FILTRAR, y, ponemos:

=FILTRAR(Ventas[[Fecha]:[Total]];NO(ESERROR(Ventas[Filtro])))

Aceptamos, y, tenemos un informe sin esos registros donde aparecen palabras repetidas en la misma cadena.

Vamos a traernos los encabezados, pero, lo vamos a realizar de forma dinamica, de manera, que si aumentan o disminuyen los encabezados se actualicen.

En una celda aparte, voy a realizar la expresión, que después uniremos a la expresión anterior.

Con la siguiente expresión:

=Ventas[#Encabezados])

Nos traemos los encabezados.

Ahora, contamos los encabezados.

=CONTARA(Ventas[#Encabezados])

Restamos 1, que son los encabezados que necesitamos.

=CONTARA(Ventas[#Encabezados])-1

Usamos la función SECUENCIA, solo usamos el argumento columnas, que es la expresión anterior.

=SECUENCIA(;CONTARA(Ventas[#Encabezados])-1)

Pero los números de encabezados a rescatar deben de ser 1, por lo que volvemos a la función SECUENCIA, añadimos el argumento inicio, ponemos 1, y, como argumento paso, ponemos 0.

=SECUENCIA(;CONTARA(Ventas[#Encabezados])-1;1;0)

Como tenemos 8 encabezados, y, hemos conseguido siete unos, debemos de conseguir un último número que debe de ser cero, que es el encabezado que no debe de aparecer.

Para ello, usamos APILARH, como argumento matriz1, es la expresión anterior, como argumento matriz2, ponemos 0.

=APILARH(SECUENCIA(;CONTARA(Ventas[#Encabezados])-1;1;0);0)

Ahora después del signo igual, ponemos la función FILTRAR, como argumento array son los encabezados, y, como argumento include, es la función APILARH.

=FILTRAR(Ventas3[#Encabezados];APILARH(SECUENCIA(;CONTARA(Ventas3[#Encabezados])-1;1;0);0))

Seleccionamos y copiamos la expresión.

Vamos a la primera expresión, después del signo igual, ponemos la función APILARV, como argumento matriz1, son los encabezados, y, como argumento matriz2, es la expresión que ya tenemos.

=APILARV(FILTRAR(Ventas3[#Encabezados];APILARH(SECUENCIA(;CONTARA(Ventas3[#Encabezados])-1;1;0);0));FILTRAR(Ventas3[[Fecha]:[Total]];NO(ESERROR(Ventas3[Filtro]))))

Pues ya lo tenemos.

Si queremos poner un formato a filas alternas, seleccionamos todo el modelo, desplegamos formato condicional, y, seleccionamos nueva regla.

Seleccionamos la ultima opción, en la ventana dar formato, ponemos:

Damos un formato, y, aceptamos.

Damos formato al encabezado.

Miguel Angel Franco