Estructurar modelo

Tenemos el siguiente modelo.

Vemos que no está bien estructurado.

Nos solicitan estructurar el modelo como sigue:

Empecemos…

En la celda E2, ponemos la función LET, creamos una variable, la llamaremos fecha, usamos el condicional SI, preguntamos que si el rango A1:A23 es igual a Fecha, que nos devuelva la fila, en caso contrario, que nos devuelva un error.

Probamos variable.

=LET(Fecha;SI(A1:A23=”Fecha”;FILA(A1:A23)+1;NOD());Fecha)

Obtenemos una matriz desbordada con la fila donde hay coincidencia, y, error donde no la hay.

Usamos la función ENCOL, como argumento matriz, es la expresión anterior, como argumento ignorar, ponemos 3, es decir, ignorar blancos y errores.

=LET(Fecha;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3);Fecha)

Usamos la función INDICE, como argumento matriz, seleccionamos la columna A, como argumento número de fila es la expresión anterior, ignoramos el argumento número de columna.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));Fecha)

Obtenemos las fechas.

Creamos otra variable, la llamaremos PR_numero, la expresión a usar es la misma que para la expresión anterior, pero debemos de cambiar algunos rangos, primero, preguntamos si empezando por la izquierda del rango C1:C23, tres caracteres, es igual a PR_, que nos devuelva la fila, en caso contrario, que nos devuelva un error, probamos variable.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());PR_numero)

Igual que antes, obtenemos una matriz desbordada con el número de fila donde hay coincidencia, y, error donde no hay coincidencia.

Quitamos el error, como antes, con la función ENCOL.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3);PR_numero)

Obtenemos los números de filas a rescatar de la columna C, ponemos la función INDICE, como argumento matriz, seleccionamos la columna C, como argumento número de fila, es la expresión anterior, ignoramos el argumento número de columna.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;INDICE(C:C;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3));PR_numero)

Obtenemos los productos.

Creamos otra variable, la llamaremos proveedor, la expresión es la misma que la anterior, pero, voy a poner en negrita lo que debemos de cambiar, y, poner.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;INDICE(C:C;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3));proveedor;INDICE(B:B;ENCOL(SI(DERECHA(B1:B23;4)="Corp";FILA(B1:B23);NOD());3));proveedor)

Obtenemos los proveedores.

Creamos otra variable, la llamaremos valor, pongo en negrita lo que debemos de cambiar.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;INDICE(C:C;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3));proveedor;INDICE(B:B;ENCOL(SI(DERECHA(B1:B23;4)="Corp";FILA(B1:B23);NOD());3));valor;ENCOL(SI(A1:A23="Cantidad";C1:C23;NOD());3);valor)

Nos devuelve las cantidades.

Sumamos los impuestos.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;INDICE(C:C;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3));proveedor;INDICE(B:B;ENCOL(SI(DERECHA(B1:B23;4)="Corp";FILA(B1:B23);NOD());3));valor;ENCOL(SI(A1:A23="Cantidad";C1:C23;NOD());3)+ENCOL(SI(A1:A23="Impuestos";C1:C23;NOD());3);valor)

Obtenemos la cantidad total.

Como argumento cálculo de LET, ponemos la función APILARH, como argumento matriz1, ponemos la variable Fecha, como argumento matriz2, ponemos la variable PR_numero, como argumento matriz3, ponemos la variable proveedor, como argumento matriz4, ponemos la variable valor.

=LET(Fecha;INDICE(A:A;ENCOL(SI(A1:A23="Fecha";FILA(A1:A23)+1;NOD());3));PR_numero;INDICE(C:C;ENCOL(SI(IZQUIERDA(C1:C23;3)="PR_";FILA(C1:C23);NOD());3));proveedor;INDICE(B:B;ENCOL(SI(DERECHA(B1:B23;4)="Corp";FILA(B1:B23);NOD());3));valor;ENCOL(SI(A1:A23="Cantidad";C1:C23;NOD());3)+ENCOL(SI(A1:A23="Impuestos";C1:C23;NOD());3);APILARH(Fecha;PR_numero;proveedor;valor))

Aceptamos, y, ya tenemos nuestro ejemplo resuelto.

Ponemos un encabezado, y, damos formato.

Miguel Angel Franco