Realmente estoy luchando por encontrar una solución a este problema aparentemente simple. Incluso pedí la ayuda de ChatGPT, pero ninguna de sus soluciones funcionó. ¿Quizás puedas demostrar que eres más inteligente que una máquina (por ahora)?
Estoy buscando hojas de trabajo vecinas y libros de trabajo externos que a veces tienen decenas de miles de filas, por lo que descubro que necesito algo más rápido que LOOKUPo VLOOKUP, o las cosas se detienen. Sin embargo, estoy usando LibreOffice 7.4, por lo que las fórmulas de matriz dinámica y algunas funciones más nuevas de Excel como XLOOKUP, FILTERy SORTno son compatibles.
La columna A contiene una lista de fechas en orden ascendente o descendente. La columna B contiene números no ordenados y celdas en blanco.
| A | B |
|---|---|
| 05/01/23 | 1234.10 |
| 05/02/23 | 134,25 |
| 05/05/23 | 5123.45 |
| 05/08/23 | |
| 05/10/23 | 0.00 |
Se necesita una fórmula que no requiera un uso intensivo de CPU para encontrar el último valor que no esté en blanco en la columna B (incluidos los ceros) donde el valor de búsqueda es una fecha no posterior a la de la celda $Z1. Probé variaciones de LOOKUP(2,1/, INDEX MATCH, VLOOKUPpero obtengo todo ceros, un error o el primer o último valor en la columna B, ignorando la fecha.
¿Algunas ideas? Estoy empezando a preguntarme si hay algún problema con LibreOffice. No sería la primera vez que tengo que reinstalarlo para que empiece a comportarse solo.
EDITAR: Una lista de fórmulas que he probado hasta ahora:
{=INDEX($B$2:$B$9999,MAX(IF($A$2:$A$9999<=$Z1,ROW($A$2:$A$9999)-MIN(ROW($A$2:$A$9999))+1)))}
Devuelve un valor de 0. Creo que está buscando la celda $B$9998, en lugar de la última celda que no está en blanco, que es $B$124.
=LOOKUP(2,1/($A$2:$A$9999<=$Z1),$B$2:$B$9999)
Devuelve una cadena vacía. He usado una versión diferente de esto antes:
=LOOKUP(2,1/($Z1>=$A$2:$A$9999)/$B$2:$B$9999,$B$2:$B$9999)
Funciona, excepto que no admite celdas con un valor de 0.
{=MAX(IF($A$2:$A$9999<=$Z1, $B$2:$B$9999, ""))}
De alguna manera, éste devuelve el tercer valor en la columna B, no el último, independientemente de la fecha en la celda $Z1.
{=OFFSET($'Savings chart'.$B$2,MAX(IF($'Savings chart'.$A$2:$A$9999<=$A38,ROW($'Savings chart'.$A$2:$A$9999)-ROW($A$2)+1,0))-1,0)}
Este mira la celda $B$9998 y devuelve un valor de 0, en lugar de mirar la última celda que no está en blanco.
{=INDIRECT("'Sheet 2'.B"&SUMPRODUCT(MAX(('Sheet 2'.A:A<=$Z1)*ROW('Sheet 2'.A:A))))}
Este devuelve un valor de 0.
{=INDEX('Sheet 2'.B:B, MAX(IF('Sheet 2'.A:A<=$Z1, ROW('Sheet 2'.A:A))))}
Este devuelve un valor de 0.
{=INDEX($B$2:$B$9999,MAX(IF($A2:$A$9999<=$Z1,ROW($B$2:$B$9999)-MIN(ROW($B$2:$B$9999))+1)))}
Este devuelve un valor de 0.
{=OFFSET('Savings chart'.$B$1, MAX(IF('Savings chart'.$B$2:$B$9999<>"", ROW('Savings chart'.$B$2:$B$9999)-ROW('Savings chart'.$B$2)+1, 0))-1, 0)}
Sorprendentemente, este devuelve el penúltimo valor de la columna B.
{=INDEX($B$2:$B$9999, MAX(IF($B$2:$B$9999<>"", ROW($B$2:$B$9999)-ROW($B$2)+1, 0)))}
Estedevuelve el valor de la última celda que no está en blancoen la columna B, pero no pude encontrar la manera de hacer que tenga en cuenta la fecha en $Z1. (Como probablemente podrás ver, estaba muy cansado mientras hacía esto).
=INDEX($B$2:$B$9999, MATCH(2, 1/($A$2:$A$9999<=$Z1), 1))
Éste mira la celda $B$9998 y devuelve un valor de 0.
=IF($A5>=$'Savings chart'.$A$2:$A$9999,LOOKUP(9.999999E+306,$'Savings chart'.$B$2:$B$9999),0)
Este siempre devuelve la última celda que no está en blanco en la columna B, independientemente de la fecha en la celda $Z1.
=XLOOKUP($'Savings chart'.$A$2:$B$9999,($A2>=$'Savings chart'.$A$2:$A$9999)*($'Savings chart'.$B$2:$B$9999<>""),$'Savings chart'.$B$2:$B$9999)
Después de instalar elExtensión Lox365 de goosepirate, Probé esta fórmula usando XLOOKUP. Produjo uncódigo de error Err:504. En ese momento me di por vencido y me fui a la cama.
Aquí hay una muestra de datos que he estado usando:
Date Data
1 Jan 23 490.44
1 Jan 23
1 Jan 23
2 Jan 23
3 Jan 23
4 Jan 23
4 Jan 23
5 Jan 23
6 Jan 23
7 Jan 23
8 Jan 23
9 Jan 23
11 Jan 23
11 Jan 23
11 Jan 23
11 Jan 23
11 Jan 23
12 Jan 23
12 Jan 23
13 Jan 23
14 Jan 23
16 Jan 23
16 Jan 23
17 Jan 23
18 Jan 23
18 Jan 23
19 Jan 23
20 Jan 23 493.44
21 Jan 23
21 Jan 23
22 Jan 23
22 Jan 23
23 Jan 23 499.44
24 Jan 23
24 Jan 23
24 Jan 23
24 Jan 23
25 Jan 23 484.81
25 Jan 23
25 Jan 23
25 Jan 23
25 Jan 23
25 Jan 23
26 Jan 23
26 Jan 23
26 Jan 23
27 Jan 23
27 Jan 23
27 Jan 23
28 Jan 23
29 Jan 23
31 Jan 23
31 Jan 23
1 Feb 23
1 Feb 23
1 Feb 23
3 Feb 23
3 Feb 23
4 Feb 23
5 Feb 23
5 Feb 23
5 Feb 23
6 Feb 23
6 Feb 23
6 Feb 23
6 Feb 23
6 Feb 23
7 Feb 23
7 Feb 23
7 Feb 23
7 Feb 23
8 Feb 23
8 Feb 23
9 Feb 23
9 Feb 23
9 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
10 Feb 23
11 Feb 23
11 Feb 23
11 Feb 23
11 Feb 23
11 Feb 23
12 Feb 23
12 Feb 23
12 Feb 23
13 Feb 23
13 Feb 23
13 Feb 23
13 Feb 23
14 Feb 23
15 Feb 23
15 Feb 23
16 Feb 23
16 Feb 23
17 Feb 23
17 Feb 23
17 Feb 23
17 Feb 23
17 Feb 23
17 Feb 23
17 Feb 23
18 Feb 23
18 Feb 23
19 Feb 23
20 Feb 23 487.81
20 Feb 23
20 Feb 23
20 Feb 23
21 Feb 23
21 Feb 23
22 Feb 23
22 Feb 23
22 Feb 23
22 Feb 23
23 Feb 23 493.81
24 Feb 23 477.81
Respuesta1
La fórmula con la que comencé fue:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/$'Sheet 1'.B$2:B$10000,$'Sheet 1'.B$2:B$10000)
Pude obtener el resultado que necesitaba agregando un filtro "no en blanco" como este:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/($'Sheet 1'.B$2:B$10000<>""),$'Sheet 1'.B$2:B$10000)