この一見単純な問題の解決策を見つけるのに本当に苦労しています。ChatGPT の助けも借りましたが、その解決策はどれもうまくいきませんでした。あなたは (今のところ) 機械よりも賢いことを証明できるでしょうか?
隣接するワークシートや、時には数万行もある外部ワークブックを検索しているので、LOOKUPやよりも高速な処理が必要だと感じていますVLOOKUP。そうでないと、処理が停止してしまいます。ただし、LibreOffice 7.4 を使用しているため、動的配列数式や、、などの新しい Excel 関数はXLOOKUPサポートされていませんFILTER。SORT
列 A には、昇順または降順の日付のリストが含まれています。列 B には、並べ替えられていない数値と空白のセルが含まれています。
| あ | 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 の最新の空白でない値 (ゼロを含む) を検索するには、CPU をあまり使用しない数式が必要です。 、、$Z1のバリエーションを試しましたが、すべてゼロ、エラー、または日付を無視した列 B の最初または最後の値が表示されます。LOOKUP(2,1/INDEX MATCHVLOOKUP
何かアイデアはありますか? LibreOffice に何か問題があるのではないかと思い始めています。正常に動作させるために再インストールしなければならなかったのは、今回が初めてではありません。
編集: これまで試した数式のリスト:
{=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$124 ではなく、セル $B$9998 が検索されていると思います。
=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, ""))}
どういうわけか、この関数はセル $Z1 の日付に関係なく、列 B の最後の値ではなく 3 番目の値を返します。
{=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 の最後から 2 番目の値を返します。
{=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)
これは、セル $Z1 の日付に関係なく、常に列 B の最後の空白でないセルを返します。
=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)
インストール後goosepirate による Lox365 拡張機能、私はこの式を を使って試してみました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)