ARRAYFORMULA + セル参照の抜け道

ARRAYFORMULA + INDIRECTしかり、ARRAYFORMULA + INDEXしかり、繰り返し処理の中で他のセルの値を参照しようとすると、INDIRECT関数もINDEX関数も返り値が配列形式になっていることが原因で、ARRAYFORMULA関数だと上手く処理することができない。

なんか抜け道ないかなと色々と探していて、やっと見つけた。
なるほどVLOOKUP + SEQUENCE、、、

（Gスプレッドシート）INDEX+ARRAYFORMULA関数の代替手段 - いきなり答える備忘録

Chapter 1: 必要になった状況

各行の中で歯抜けになっているスプレッドシートの列の中から特定列のみを抽出してこないといけないという状況があった。
雑な例を示すとしたら以下のような列になっていた。"need"となっている値のみを抽出して要素の合計値を算出したい。（need = 数値）

...| head | ...
---------------
...| need | ...
...|      | ...
...| aaaa | ...
...| bbbb | ...
...| need | ...
...|      | ...
...| aaaa | ...
...| bbbb | ...

Chapter 2: 試行錯誤の巻

1. INDIRECT関数で引っ張る想定でセル名一覧を生成してみた

"need"の値が出現する規則は決まっていて、need -> 空行 -> aaaa -> bbbb -> 次の"need"…という規則になっていた。
つまり、needが設定されているセル名を生成できれば、INDIRECT関数で出力できるのではないかと考えた。

セル名の一覧を取得するために一番最初に作成した関数が以下のような感じ。A列に並んでいる対象行の数をカウントし、行数分のセル名リストを作成。2行目 + 4行が次の対象行になるので、2 -> 6 -> 10 -> 14とカウントアップしていくことで、needの値だけを取得できるだろう、、、という希望的観測を持った。

=arrayformula("B" & 2 + 4 * arrayformula(row(indirect("A1:A" & counta(A:A)/2)) -1))

2. INDIRECT関数もINDEX関数も使えない壁に衝突

想定通りに以下のようなリストが生成できたので、INDIRECT関数を設定していくことにした。

B2
B6
B10
B14

INDIRECT関数を追加すると次のような関数式になる。

=arrayformula(indirect("B" & 2 + 4 * arrayformula(row(indirect("A1:A" & counta(A:A)/2)) -1)))

結果どのような出力になったかというと

need

以上である。
1行分の値を取得することしかできない結果となった。理由は明白でINDIRECT関数の出力結果は配列形式になっているため、ARRAYFORMULA関数の中で実行すると1行分しか処理を出力できないくなってしまう。

これはセル名ではなく、行番号と列番号を指定して取得するINDEX関数でも同じ結果となる。

3. [index arrayformula]で検索して見つけたvlookup + sequence

SEQUENCE関数を使って最初にやることは、ベースとなるテーブルの作成だ。行番号と対象列だけのテーブルを作成することで、VLOOKUP関数を利用した繰り返し処理ができるようになる。

/// 設定する関数 ///
={
  sequence(counta(A:A)),indirect("B1:B" & counta(A:A))
}

/// 出力結果 ///
| 1 | head |
------------
| 2 | need | 
| 3 |      | 
| 4 | aaaa | 
| 5 | bbbb | 
| 6 | need | 
| 7 |      | 
| 8 | aaaa | 
| 9 | bbbb | 
| 10| need |
  .
  .
  .

生成されたテーブルに対して、対象行番号でVLOOKUPすることでARRAYFORMULA関数の中でもINDEX/INDIRECT関数みたい挙動を設定することができるという訳らしい。なんちゅう発想力。。。すごい。

ARRAYFORMULA関数の中に組み込んでみるとこんな感じ。
VLOOKUPの検索キーとなる部分が繰り返し対象の値で、2 -> 6 -> 10 -> 14という対象行番号のリストになっている。

arrayformula(
  vlookup(
    arrayformula(2 + 4 * arrayformula(sequence(counta(A:A)/2) -1)),
    {sequence(counta(A:A)),indirect("B1:B" & counta(B:B))},
    2
  )
)

Chapter 3: 何かしら解決策は必ずある

INDIRECT関数について調べていると「セル名を指定して値を引っ張るときはINDIRECTを使う他ないが、ARRAYFORMULAでは使えないから解決法は存在しない」みたいな記事が結構多くて、「詰んでるのかぁ」とか思っていたけど、なんやかんや詰んではいなかった。

たぶん針の糸を通すような解決策ではあるんだろうけど、「詰んでると思ってもなんとかなるんだな」と改めて学んだ。

↓「参考になった！」「便利！」と思った方は、ぜひご支援ください❤️↓