Ich habe wirklich Mühe, eine Lösung für dieses täuschend einfache Problem zu finden. Ich habe sogar die Hilfe von ChatGPT in Anspruch genommen, aber keine der Lösungen hat funktioniert. Vielleicht können Sie beweisen, dass Sie (vorerst) schlauer sind als eine Maschine?
Ich durchsuche benachbarte Arbeitsblätter und externe Arbeitsmappen mit manchmal Zehntausenden von Zeilen und stelle fest, dass ich etwas Schnelleres als LOOKUPoder brauche VLOOKUP, sonst kommt alles zum Stillstand. Ich verwende jedoch LibreOffice 7.4, daher werden dynamische Arrayformeln und einige neuere Excel-Funktionen wie XLOOKUP, FILTER, und SORTnicht unterstützt.
Spalte A enthält eine Liste von Daten in aufsteigender oder absteigender Reihenfolge. Spalte B enthält nicht sortierte Zahlen und leere Zellen.
| 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 |
Ich benötige eine Formel, die wenig CPU-Intensiv ist, um den letzten nicht leeren Wert in Spalte B (einschließlich Nullen) zu finden, wobei der Nachschlagewert ein Datum ist, das nicht nach dem in Zelle liegt $Z1. Ich habe Variationen von LOOKUP(2,1/, INDEX MATCH, ausprobiert VLOOKUP, aber entweder erhalte ich nur Nullen, einen Fehler oder den ersten oder letzten Wert in Spalte B, wobei das Datum ignoriert wird.
Irgendwelche Ideen? Ich frage mich langsam, ob mit LibreOffice etwas nicht stimmt. Es wäre nicht das erste Mal, dass ich es neu installieren muss, damit es funktioniert.
EDIT: Eine Liste der Formeln, die ich bisher ausprobiert habe:
{=INDEX($B$2:$B$9999,MAX(IF($A$2:$A$9999<=$Z1,ROW($A$2:$A$9999)-MIN(ROW($A$2:$A$9999))+1)))}
Es gibt einen Wert von 0 zurück. Ich glaube, es wird die Zelle $B$9998 gefunden und nicht die letzte nicht leere Zelle, die $B$124 ist.
=LOOKUP(2,1/($A$2:$A$9999<=$Z1),$B$2:$B$9999)
Es gibt eine leere Zeichenfolge zurück. Ich habe zuvor eine andere Version davon verwendet:
=LOOKUP(2,1/($Z1>=$A$2:$A$9999)/$B$2:$B$9999,$B$2:$B$9999)
Es funktioniert, unterstützt jedoch keine Zellen mit einem Wert von 0.
{=MAX(IF($A$2:$A$9999<=$Z1, $B$2:$B$9999, ""))}
Aus irgendeinem Grund gibt dieser den dritten Wert in Spalte B zurück, nicht den letzten – unabhängig vom Datum in Zelle $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)}
Dieses betrachtet die Zelle $B$9998 und gibt einen Wert von 0 zurück, anstatt die letzte nicht leere Zelle zu betrachten.
{=INDIRECT("'Sheet 2'.B"&SUMPRODUCT(MAX(('Sheet 2'.A:A<=$Z1)*ROW('Sheet 2'.A:A))))}
Dieser gibt einen Wert von 0 zurück.
{=INDEX('Sheet 2'.B:B, MAX(IF('Sheet 2'.A:A<=$Z1, ROW('Sheet 2'.A:A))))}
Dieser gibt einen Wert von 0 zurück.
{=INDEX($B$2:$B$9999,MAX(IF($A2:$A$9999<=$Z1,ROW($B$2:$B$9999)-MIN(ROW($B$2:$B$9999))+1)))}
Dieser gibt einen Wert von 0 zurück.
{=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)}
Etwas überraschend gibt dieser den vorletzten Wert in Spalte B zurück.
{=INDEX($B$2:$B$9999, MAX(IF($B$2:$B$9999<>"", ROW($B$2:$B$9999)-ROW($B$2)+1, 0)))}
Dasgibt den Wert der letzten nicht leeren Zelle zurückin Spalte B, aber ich konnte dann nicht herausfinden, wie ich es dazu bringen konnte, das Datum in $Z1 zu berücksichtigen. (Wie Sie wahrscheinlich merken, war ich dabei sehr müde.)
=INDEX($B$2:$B$9999, MATCH(2, 1/($A$2:$A$9999<=$Z1), 1))
Dieses betrachtet Zelle $B$9998 und gibt einen Wert von 0 zurück.
=IF($A5>=$'Savings chart'.$A$2:$A$9999,LOOKUP(9.999999E+306,$'Savings chart'.$B$2:$B$9999),0)
Dieser gibt immer die letzte nicht leere Zelle in Spalte B zurück, unabhängig vom Datum in Zelle $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)
Nach der Installation desLox365-Erweiterung von goosepirateIch habe diese Formel mit ausprobiert XLOOKUP. Es ergab einFehlercode Err:504. An diesem Punkt gab ich auf und ging zu Bett.
Hier ist ein Beispiel der Daten, die ich verwendet habe:
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
Antwort1
Die Formel, mit der ich begonnen habe, war:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/$'Sheet 1'.B$2:B$10000,$'Sheet 1'.B$2:B$10000)
Ich konnte das gewünschte Ergebnis erzielen, indem ich einen „nicht leer“-Filter wie folgt hinzugefügt habe:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/($'Sheet 1'.B$2:B$10000<>""),$'Sheet 1'.B$2:B$10000)