Excelでfind

Excelで任意の長さの連続したデータをスピルとして取得したいとき、ありますよね。=A1:A100のように書いてしまうと、たとえば20個しかデータがない時に余計なセルまでスピル範囲に含まれてしまうし、120個データがあった時に取りこぼす。データ長に合わせて自動的にスピル範囲を調整してほしい！　ではどうするか？

範囲が不連続のとき

データが連続しておらず、値があるセルだけを取り出したいのであれば、FILTERとISBLANKを使えば良い。これは多くの人が用いている素直なやり方だろう。

=FILTER(A:A,NOT(ISBLANK(A:A)))

これはMatlabでいうところの論理インデックスとして使える。NOT(ISBLANK(A:A))の代わりにA:A>0で抽出することもできる。

Matlabでいうところのfind()、つまり「TRUEとなる行番号を取得」したいならこれ。SEQUENCE(N,,M,L)はMatlabのM:L:M+L*(N-1)と等価。

=FILTER(SEQUENCE(100),NOT(ISBLANK(A1:A100)))

SEQUENCEと入力範囲の関係を調整するのが面倒ならLETで。iの範囲を設定するだけで処理してくれる。

=LET(
    i, A5:A100,
    l_min, MIN(ROW(i)),
    l, ROWS(i),
    FILTER(SEQUENCE(l,,l_min), NOT(ISBLANK(i)))
)

範囲が連続しているとき

1行目からデータが連続して格納されているならばこれでいい。

=INDEX(A:A,SEQUENCE(COUNTA(A:A)))

1行目がヘッダだったら？　A:Aを範囲に置き換えればいい。確実にこれ以上データをいれることはない、という範囲を設定すればいいし、最悪は重くなるかもしれないけど範囲をA2:A1048576とかにすれば確実に取りこぼしがない。

=INDEX(A2:A10000,SEQUENCE(COUNTA(A2:A10000)))

あるいはSEQUENCEの方で範囲を調整しても構わない。

=INDEX(A:A,SEQUENCE(COUNTA(A:A)-1,,2))

上の例と同じように、LETを使えば入力範囲iとヘッダ行数n_headerを設定すればいいようにできる。

=LET(
    i, A:A,
    n_header, 2,
    INDEX(i,SEQUENCE(COUNTA(i)-n_header, , 1+n_header))
)

一般に適用される注意点として、INDEXの代わりにINDIRECTやOFFSETを使わない方がいいということがある。INDIRECTやOFFSETは揮発性関数に分類される。揮発性関数は引数が変化していなくても常に再計算の対象となる。ということはつまり、全然関係ないセルを編集したときにも余計な処理が走るということ。普通の使い方をする分には問題がないけれど、Excelを濫用する場合には問題となりうるのだ。動作が重くて使い物にならなくなる。そんな処理をExcelにさせるなって？　本当にそのとおり。