Poner numero y celda superior

Tenemos el siguiente modelo:

Debemos de crear un modelo, y, poner la celda que contiene un numero y la celda superior.

Lo vamos a realizar de dos modos.

La primera forma que vamos a realizarlo es en una columna poner los productos, y, en otra columna las cantidades.

En la celda F2, usamos LET, creamos una variable, la llamamos números, usamos el condicional SI, preguntamos si es número el rango A2:D13.

=LET(numeros;SI(ESNUMERO(A2:D13)

Si lo es, que nos devuelva la fila concatenada con un espacio y la columna del rango A2:D13, en caso contrario, que devuelva un texto en blanco.

Probamos variable.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");numeros)

Tenemos una matriz desbordada con la fila y columna donde hay coincidencia, y, blanco donde no la hay.

Creamos otra variable, la llamamos productos, hacemos la misma pregunta que para si es número, lo único que debemos de cambiar es restar 1 a la fila.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");productos)

Obtenemos la misma matriz desbordada, pero con el numero de fila y columna de los productos.

Creamos otra variable, la llamamos solo_productos, usamos INDICE como argumento matriz, seleccionamos las columnas A:D, como argumento numero de fila, usamos IZQUIERDA, como argumento texto, seleccionamos la variable productos, como argumento numero de caracteres, ponemos 2, porque hay filas con dos dígitos, como argumento numero de columna, usamos DERECHA, como argumento texto, ponemos la variable productos, e, ignoramos el argumento número de caracteres.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_productos;INDICE(A:D;IZQUIERDA(productos;2);DERECHA(productos));solo_productos)

Tenemos una matriz desbordada con los productos, y, error donde no hay coincidencia.

Usamos ENCOL e ignoramos blancos y errores.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_productos;ENCOL(INDICE(A:D;IZQUIERDA(productos;2);DERECHA(productos));3);solo_productos)

Ya tenemos los productos.

Creamos otra variable, la llamamos solo_numeros, es la misma expresión que para la variable solo_productos, lo único que cambia es el argumento texto de IZQUIERDA y DERECHA por la variable números.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_productos;ENCOL(INDICE(A:D;IZQUIERDA(productos;2);DERECHA(productos));3);solo_numeros;ENCOL(INDICE(A:D;IZQUIERDA(numeros;2);DERECHA(numeros));3);solo_numeros)

Tenemos las cantidades.

Usamos el argumento calculo de LET, ponemos APILARH, como argumento matriz1, ponemos la variable solo_productos, como argumento matriz2, ponemos la variable solo_numeros.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_productos;ENCOL(INDICE(A:D;IZQUIERDA(productos;2);DERECHA(productos));3);solo_numeros;ENCOL(INDICE(A:D;IZQUIERDA(numeros;2);DERECHA(numeros));3);APILARH(solo_productos;solo_numeros))

Aceptamos y ya tenemos resuelto el primer modo.

El segundo modo consiste en respetar la estructura del modelo de datos.

Usamos LET, la variable números, y, productos son las mismas que para el ejemplo anterior.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"")

Creamos otra variable, la llamamos solo_numeros, usamos el condicional SI, preguntamos si es numero el rango A2:D13, en ese caso, que devuelva el rango A2:D13, en caso contrario, que devuelva un texto en blanco.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_numeros)

Tenemos una matriz desbordada con las cantidades y blanco donde no hay coincidencia.

Vemos en el modelo que cada producto tiene una longitud mayor a 3, pues, vamos a crear otra variable, la llamamos solo_productos, preguntamos si la longitud del rango A2:D13 es mayor a 3.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_productos;SI(LARGO(A2:D13)>3

En ese caso, que nos devuelva el rango A2:D13, en caso contrario, que devuelva un texto en blanco.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_productos;SI(LARGO(A2:D13)>3;A2:D13;"");solo_productos)

Tenemos una matriz desbordada con los productos, y, blanco donde no hay coincidencia.

Creamos otra variable, la llamamos resul, preguntamos si la variable solo_productos es diferente a blanco, en ese caso, que devuelva la variable solo_productos, en caso contrario, que devuelva la variable, solo_numeros.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_productos;SI(LARGO(A2:D13)>3;A2:D13;"");resul;SI(solo_productos<>"";solo_productos;solo_numeros);resul)

Pues, ya lo tenemos.

Vemos que hay filas completas en blancos, pues, vamos a quitarlas, para ello, creamos otra variable, la llamamos recuento, usamos BYROW, como argumento array, usamos LARGO, como argumento de LARGO ponemos la variable resul, como argumento función, ponemos LAMBDA, creamos una variable, y, sumamos dicha variable.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_productos;SI(LARGO(A2:D13)>3;A2:D13;"");resul;SI(solo_productos<>"";solo_productos;solo_numeros);recuento;BYROW(LARGO(resul);LAMBDA(x;SUMA(x)));recuento)

Tenemos una matriz desbordada con la longitud de cada cadena.

Donde tenemos cero, quiere decir que la fila completa está vacía, vamos a usar el argumento calculo de LET, filtramos la variable resul, siempre que la variable recuento sea diferente a 0.

=LET(numeros;SI(ESNUMERO(A2:D13);FILA(A2:D13)&" "&COLUMNA(A2:D13);"");productos;SI(ESNUMERO(A2:D13);FILA(A2:D13)-1&" "&COLUMNA(A2:D13);"");solo_numeros;SI(ESNUMERO(A2:D13);A2:D13;"");solo_productos;SI(LARGO(A2:D13)>3;A2:D13;"");resul;SI(solo_productos<>"";solo_productos;solo_numeros);recuento;BYROW(LARGO(resul);LAMBDA(x;SUMA(x)));FILTRAR(resul;recuento<>0))

Aceptamos y ya tenemos el segundo modo.

Miguel Angel Franco