Normalizar modelo

Tenemos el siguiente modelo:

Vemos que unidos aparecen dos nombres, y, tres nombres.

Debemos de construir un modelo, donde en la primera columna, aparezcan los nombres únicos, teniendo en cuenta, que donde aparece más de un nombre en una celda, se cuentan como nombres independientes.

En la segunda columna, deben de aparecer las ordenes que corresponde con cada nombre, si un nombre aparece más de una vez, deben de aparecer dichas órdenes.

Después, deben de aparecer tantas columnas como meses haya con el total de cada mes y cada nombre.

Por último, añadimos una última columna llamada total, donde sumamos las cantidades por meses por nombres.

Empecemos…

En la celda H2, ponemos LET, creamos una variable, la llamamos primera_coin, usamos ENCONTRAR, como argumento texto buscado, entre comillas dobles, ponemos la barra inclinada, como argumento dentro del texto, seleccionamos el rango C3:C15, probamos variable.

=LET(primera_coin;ENCONTRAR("/";C3:C15);primera_coin)

Obtenemos una matriz desbordada con la posición de la barra inclinada, y, error donde no hay coincidencia.

Creamos otra variable, la llamamos segunda_coin, vamos a encontrar la posición del segundo guion, volvemos a usar ENCONTRAR, como argumento texto buscado, entre comillas dobles, ponemos la barra invertida, como argumento dentro del texto, seleccionamos el rango C3:C15, como argumento numero inicial, usamos de nuevo, ENCONTRAR, volvemos a buscar la barra inclinada y sumamos 1.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);segunda_coin)

Obtenemos una matriz desbordada con la posición del segundo guion donde lo haya, y, error donde no hay coincidencia.

Creamos otra variable, la llamamos extraer1, usamos EXTRAE, como argumento texto, seleccionamos el rango C3:C15, como argumento posición inicial, ponemos 1, como argumento número de caracteres ponemos la variable primera_coin, y, restamos 1.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;EXTRAE(C3:C15;1;primera_coin-1);extrae1)

Obtenemos una matriz desbordada con el primer nombre donde hay mas de un nombre, y, error donde no hay coincidencia.

Usamos la función SI.ERROR, en el argumento valor si error, seleccionamos el rango C3:C15.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;primera_coin-1);C3:C15);extrae1)

Obtenemos el primer nombre.

Creamos otra variable, la llamamos extrae2, usamos EXTRAE, como argumento texto, seleccionamos el rango C3:C15, como argumento posición inicial, ponemos la variable primera_coin, y, sumamos 1, como argumento número de caracteres, usamos LARGO, como argumento de LARGO, seleccionamos el rango C3:C15, y, restamos la variable primera_coin.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);extrae2)

Tenemos una matriz desbordada con el segundo y tercer nombre donde haya.

Usamos la función DIVIDIRTEXTO, como argumento texto, es la expresión anterior, como argumento delimitador de columna, entre comillas dobles, ponemos la barra inclinada.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;DIVIDIRTEXTO(EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);"/");extrae2)

Ya tenemos el segundo nombre.

Creamos otra variable, la llamamos extrae3, usamos EXTRAE, como argumento texto, seleccionamos el rango C3:C15, como argumento posición inicial, ponemos la variable segunda_coin y sumamos 1, como argumento numero de caracteres, usamos LARGO, como argumento de LARGO, seleccionamos el rango C3:C15, y, restamos la variable segunda_coin.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;DIVIDIRTEXTO(EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);"/");extrae3;EXTRAE(C3:C15;segunda_coin+1;LARGO(C3:C15)-segunda_coin);extrae3)

Tenemos una matriz desbordada con el tercer nombre.

Creamos otra variable la llamamos unión, usamos APILARV, como argumento matriz1, ponemos la variable extrae1, como argumento matriz2, ponemos la variable extrae2, como argumento matriz3, ponemos la variable extrae3.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;DIVIDIRTEXTO(EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);"/");extrae3;EXTRAE(C3:C15;segunda_coin+1;LARGO(C3:C15)-segunda_coin);union;APILARV(extrae1;extrae2;extrae3);union)

Tenemos una matriz desbordada con la unión de las tres variables, y, error, donde no había coincidencia.

Usamos ENCOL, y, como argumento ignorar, ponemos 3, donde ignoramos celdas vacías y errores.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;DIVIDIRTEXTO(EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);"/");extrae3;EXTRAE(C3:C15;segunda_coin+1;LARGO(C3:C15)-segunda_coin);union;ENCOL(APILARV(extrae1;extrae2;extrae3);3);union)

Nos quedamos con los valores únicos.

=LET(primera_coin;ENCONTRAR("/";C3:C15);segunda_coin;ENCONTRAR("/";C3:C15;ENCONTRAR("/";C3:C15)+1);extrae1;SI.ERROR(EXTRAE(C3:C15;1;ENCONTRAR("/";C3:C15)-1);C3:C15);extrae2;DIVIDIRTEXTO(EXTRAE(C3:C15;primera_coin+1;LARGO(C3:C15)-primera_coin);"/");extrae3;EXTRAE(C3:C15;segunda_coin+1;LARGO(C3:C15)-segunda_coin);union;UNICOS(ENCOL(APILARV(extrae1;extrae2;extrae3);3));union)

Ya tenemos los nombres con los que vamos a trabajar.

Bien, todo este trabajo lo podemos reducir a una única expresión, usando la función DIVIDIRTEXTO, donde como argumento texto, seleccionamos el rango C3:C15, como argumento delimitador de columna, entre comillas dobles, ponemos la barra inclinada.

=DIVIDIRTEXTO(C3:C15;"/")

Nos quedamos con los valores únicos.

=UNICOS(DIVIDIRTEXTO(C3:C15;"/"))

Tenemos los mismos resultados.

¿Por qué podemos usar DIVIDIRTEXTO?

Cuando usamos DIVIDIRTEXTO de forma matricial, solo nos separa la primera cadena, pero, las cadenas donde aparece más de un nombre son los mismos nombres que aparecen como primer nombre, como puede ser Lily/Susan, aparece Lily como primer nombre, y, Susan, también, como primer nombre.

Lo siguiente es extraer las órdenes para cada nombre, lo haremos en la celda J3.

Usamos de nuevo, ENCONTRAR, como argumento texto buscado, ponemos la celda H3, que es el primer nombre, como argumento dentro del texto, seleccionamos el rango C3:C15, y, fijamos.

=ENCONTRAR(H3;$C$3:$C$15)

Tenemos una matriz desbordada con la posición de la primera letra del nombre, y, error donde no hay coincidencia.

Preguntamos si es número.

=ESNUMERO(ENCONTRAR(H3;$C$3:$C$15))

Obtenemos VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Filtramos el rango B3:B15, como argumento include de FILTRAR es la expresión anterior.

=FILTRAR($B$3:$B$15;ESNUMERO(ENCONTRAR(H3;$C$3:$C$15)))

Tenemos las órdenes para el primer nombre.

Usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, y, como argumento texto1, es la expresión anterior.

=UNIRCADENAS(",";VERDADERO;ENFILA(FILTRAR($B$3:$B$15;ESNUMERO(ENCONTRAR(H3;$C$3:$C$15)))))

Arrastramos, y, tenemos la orden, u, órdenes para cada nombre.

Podemos hacerlo de forma matricial, para ello, usamos LET, creamos una variable, transponemos el rango B3:C15, y, probamos variable.

=LET(a;TRANSPONER(B3:C15);a)

Como argumento cálculo de LET, usamos BYROW, como argumento matriz, usamos el condicional SI, como argumento prueba lógica, usamos ESNUMERO, como argumento de ESNUMERO, usamos ENCONTRAR, como argumento texto buscado, seleccionaos la celda H3, junto con el operador de rango derramado, como argumento dentro del texto, usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento número de fila, ponemos 2, como argumento número de columna, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 13, que son los nombres que hay, es decir, vamos a buscar vamos a buscar cada nombre en la primera fila de la variable “a”.

=LET(a;TRANSPONER(B3:C15);BYROW(SI(ESNUMERO(ENCONTRAR(H3#;INDICE(a;2;SECUENCIA(;13))))

Como argumento valor si verdadero, volvemos a usar INDICE, como argumento matriz es la variable “a”, como argumento número de fila, ponemos 1, como argumento número de columna, volvemos a usar SECUENCIA, omitimos el argumento filas, como argumento columnas, usamos SECUENCIA, ignoramos el argumento filas, como argumento columnas, ponemos 13, como argumento valor si falso, ponemos un texto en blanco.

=LET(a;TRANSPONER(B3:C15);BYROW(SI(ESNUMERO(ENCONTRAR(H3#;INDICE(a;2;SECUENCIA(;13))));INDICE(a;1;SECUENCIA(;13));"")

Como argumento función de BYROW, ponemos LAMBDA, creamos una variable, como argumento calculo, usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”.

=LET(a;TRANSPONER(B3:C15);BYROW(SI(ESNUMERO(ENCONTRAR(H3#;INDICE(a;2;SECUENCIA(;13))));INDICE(a;1;SECUENCIA(;13));"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))))

Aceptamos, y, ya lo tenemos.

Ahora, vamos por las cantidades por nombre y mes.

Pero, antes vamos a crear el encabezado, lo haremos en la celda H2, primero vamos a crear los meses, para ello, usamos APILARH, como argumento matriz1, usamos TEXTO, como argumento valor seleccionamos el rango D3:D15, como argumento formato, entre comillas dobles, ponemos “mmm”, para que nos devuelva las iniciales del mes, como argumento matriz2, usamos la función MES, como argumento seleccionamos el rango D3:D15.

Tenemos una matriz desbordada de dos columnas, en la primera de ellas, tenemos las iniciales del mes, y, en la segunda de ellas, el número de mes, esto lo hacemos, para poder ordenar por meses.

Ordenamos por la segunda columna.

=ORDENAR(APILARH(TEXTO(D3:D15;"mmm");MES(D3:D15));2)

Nos quedamos con los valores únicos.

=UNICOS(ORDENAR(APILARH(TEXTO(D3:D15;"mmm");MES(D3:D15));2))

Usamos INDICE, y, nos quedamos con la primera columna.

=INDICE(UNICOS(ORDENAR(APILARH(TEXTO(D3:D15;"mmm");MES(D3:D15));2));;1)

Transponemos.

=TRANSPONER(INDICE(UNICOS(ORDENAR(APILARH(TEXTO(D3:D15;"mmm");MES(D3:D15));2));;1))

Ya tenemos los meses únicos dentro del modelo.

Después del signo igual, ponemos APILARH, como argumento matriz1, entre comillas dobles, ponemos Nombre, como argumento matriz2, entre comillas dobles, ponemos DIVIDIRTEXTO, como argumento matriz3, ponemos Orden, como argumento matriz4, entre comillas dobles ponemos Orden matricial, como argumento matriz5, es la expresión anterior, y, como argumento matriz6, entre comillas dobles, ponemos Total.

=APILARH("Nombre";"DIVIDIRTEXTO";"Orden";"Orden matricial";TRANSPONER(INDICE(UNICOS(ORDENAR(APILARH(TEXTO(D3:D15;"mmm");MES(D3:D15));2));;1));"Total")

Aceptamos, y, ya tenemos el encabezado.

En la celda L3, vamos a calcular la cantidad de cada nombre para el mes de mayo.

Usamos el condicional SI, usamos TEXTO, como argumento valor seleccionamos el rango D3:D15, lo fijamos, como argumento formato, entre comillas dobles, ponemos “mmm”, e, igualamos a L2, fijando la fila.

=SI(TEXTO($D$3:$D$15;"mmm")=L$2

Como argumento valor si verdadero, usamos otro condicional SI, como argumento valor si verdadero, usamos ENCONTRAR, como argumento valor buscado, ponemos la celda H3 junto con el operador de rango derramado, como argumento dentro del texto, seleccionaos el rango C3:C15, y, fijamos.

=SI(TEXTO($D$3:$D$15;"mmm")=L$2;SI(ENCONTRAR(H3#;$C$3:$C$15)

Como argumento valor si verdadero, seleccionamos el rango E3:E15, y, fijamos, como argumento valor si falso, ponemos un error.

Como argumento valor si falso del primer SI, ponemos otro error.

Obtenemos una matriz desbordada con la cantidad o cantidades para el primer nombre y el mes de mayo, y, error donde no hay coincidencia.

Usamos ENCOL, e, ignoramos blancos y errores.

=ENCOL(SI(TEXTO($D$3:$D$15;"mmm")=L$2;SI(ENCONTRAR($H3;$C$3:$C$15);$E$3:$E$15;NOD());NOD());3)

Sumamos los resultados, porque hay nombres que tiene más de un valor.

=SUMA(ENCOL(SI(TEXTO($D$3:$D$15;"mmm")=L$2;SI(ENCONTRAR($H3;$C$3:$C$15);$E$3:$E$15;NOD());NOD());3))

Al arrastrar puede que algunas celdas nos devuelva error, por lo que vamos a usar SI.ERROR, y, ponemos un texto en blanco.

=SI.ERROR(SUMA(ENCOL(SI(TEXTO($D$3:$D$15;"mmm")=L$2;SI(ENCONTRAR($H3;$C$3:$C$15);$E$3:$E$15;NOD());NOD());3));"")

Copiamos la expresión, seleccionamos donde vamos a pegar, y, pulsamos CTRL mas V para pegar, y, ya lo tenemos.

En la celda O2, vamos a calcular la suma de cada columna, para ello, sumamos el rango L3:N3, y, arrastramos.

=SUMA(L3:N3)

Miguel Angel Franco