Я действительно борюсь, чтобы найти решение этой обманчиво простой проблемы. Я даже заручился помощью ChatGPT, но ни одно из его решений не сработало. Может быть, вы сможете доказать, что вы умнее машины (пока)?
Я ищу соседние листы и внешние книги, иногда содержащие десятки тысяч строк, поэтому я нахожу, что мне нужно что-то более быстрое, чем LOOKUPили VLOOKUP, иначе все застопорится. Однако я использую LibreOffice 7.4, поэтому динамические формулы массива и некоторые новые функции Excel, такие как XLOOKUP, FILTERи , SORTне поддерживаются.
Столбец A содержит список дат в порядке возрастания или убывания. Столбец B содержит неотсортированные числа и пустые ячейки.
| А | Б |
|---|---|
| 01/05/23 | 1234.10 |
| 02/05/23 | 134.25 |
| 05/05/23 | 5123.45 |
| 08/05/23 | |
| 10/05/23 | 0.00 |
Нужна формула, не требующая интенсивного использования ЦП, для поиска последнего непустого значения в столбце B (включая нули), где искомое значение — это дата не позднее, чем в ячейке $Z1. Я пробовал вариации на LOOKUP(2,1/, INDEX MATCH, VLOOKUP, но либо получаю все нули, либо ошибку, либо первое или последнее значение в столбце B, игнорируя дату.
Есть идеи? Я начинаю подозревать, что с LibreOffice что-то не так. Мне не в первый раз пришлось его переустанавливать, чтобы он начал вести себя нормально.
EDIT: Список формул, которые я уже опробовал:
{=INDEX($B$2:$B$9999,MAX(IF($A$2:$A$9999<=$Z1,ROW($A$2:$A$9999)-MIN(ROW($A$2:$A$9999))+1)))}
Он возвращает значение 0. Я думаю, что он ищет ячейку $B$9998, а не последнюю непустую ячейку, которая равна $B$124.
=LOOKUP(2,1/($A$2:$A$9999<=$Z1),$B$2:$B$9999)
Возвращает пустую строку. Я уже использовал другую версию этого:
=LOOKUP(2,1/($Z1>=$A$2:$A$9999)/$B$2:$B$9999,$B$2:$B$9999)
Это работает, за исключением того, что не поддерживает ячейки со значением 0.
{=MAX(IF($A$2:$A$9999<=$Z1, $B$2:$B$9999, ""))}
Каким-то образом этот код возвращает третье значение в столбце B, а не последнее — независимо от даты в ячейке $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)}
Этот код просматривает ячейку $B$9998 и возвращает значение 0, а не просматривает последнюю непустую ячейку.
{=INDIRECT("'Sheet 2'.B"&SUMPRODUCT(MAX(('Sheet 2'.A:A<=$Z1)*ROW('Sheet 2'.A:A))))}
Этот возвращает значение 0.
{=INDEX('Sheet 2'.B:B, MAX(IF('Sheet 2'.A:A<=$Z1, ROW('Sheet 2'.A:A))))}
Этот возвращает значение 0.
{=INDEX($B$2:$B$9999,MAX(IF($A2:$A$9999<=$Z1,ROW($B$2:$B$9999)-MIN(ROW($B$2:$B$9999))+1)))}
Этот возвращает значение 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)}
Несколько удивительно, что этот метод возвращает предпоследнее значение в столбце B.
{=INDEX($B$2:$B$9999, MAX(IF($B$2:$B$9999<>"", ROW($B$2:$B$9999)-ROW($B$2)+1, 0)))}
Этотвозвращает значение последней непустой ячейкив столбце B, но я не смог тогда понять, как заставить его учитывать дату в $Z1. (Как вы, вероятно, заметили, я очень устал, делая это.)
=INDEX($B$2:$B$9999, MATCH(2, 1/($A$2:$A$9999<=$Z1), 1))
Этот код просматривает ячейку $B$9998 и возвращает значение 0.
=IF($A5>=$'Savings chart'.$A$2:$A$9999,LOOKUP(9.999999E+306,$'Savings chart'.$B$2:$B$9999),0)
Этот метод всегда возвращает последнюю непустую ячейку в столбце B, независимо от даты в ячейке $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)
После установкиРасширение Lox365 от goosepirate, Я попробовал эту формулу, используя XLOOKUP. Она далакод ошибки Err:504. В этот момент я сдался и пошёл спать.
Вот пример данных, которые я использовал:
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
решение1
Формула, с которой я начал, была следующей:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/$'Sheet 1'.B$2:B$10000,$'Sheet 1'.B$2:B$10000)
Мне удалось получить нужный мне результат, добавив фильтр «непустое» следующим образом:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/($'Sheet 1'.B$2:B$10000<>""),$'Sheet 1'.B$2:B$10000)