転生したらSpreadsheetだった件

第42話 ARRAYFORMULA私的ベストプラクティス

「よし、これで心おきなく解説できるな」

　意気揚々とサイトウは壁に関数を書きながら説明しはじめた。

=ARRAYFORMULA(LEFT(A2:A20, 5))

「さっきの関数の組み合わせだがな、ユーザーさんがそもそもやりたいことはこんな感じだろう。A列の値にLEFT関数を適用して何文字でも構わないんだが5文字を切り出す。この式で困るケースがあるとしたらなんだ？」

「処理したい値が20行以上つづいたときですよね」

=ARRAYFORMULA(LEFT(A2:A, 5))

「僕ならこういう感じで書きます。IFERRORとかつけてもいいのですが、LEFTなら空白でエラーってこともないので気にしなくていいですね」

「そうだな。まあARRAYFORMULAを使う上で範囲の動的指定とどう向き合うかは重要だ。このユーザーさんは処理対象の行を正確に指定したかったんだろう。そのうえで処理したい行数の変化に対応するためこう書く」

=ARRAYFORMULA(LEFT(INDIRECT("A2:A"&COUNTA(A:A)), 5))

「なるほど。COUNTAで処理したい値の数を数えて、INDIRECTでそれを文字列からセル範囲に変換し、ARRAYFORMULAの処理範囲として指定する」

「そういうこった。最後にROWだ」

=ARRAYFORMULA(LEFT(INDIRECT("A"&ROW(A2)&":A"&COUNTA(A:A)),5))

「INDIRECTの弱点は行追加などの変更に弱くなることだ。見出し行を2行にふやされたりするとズレが発生する。そこでROWで処理対象の開始行を捕捉しておくわけよ」

「なるほど。それであの鵺みたいな組み合わせがよくあるんですね。でもこれだと列がずれたときに対処できないからCOLUMNを使って列も動的に記述したほうがいいんじゃないですか？」

「そうだな。列も考慮するならもっと別の書き方がある」

=ARRAYFORMULA(LEFT(INDIRECT(CELL("address",A2)&":"&ADDRESS(COUNTA(A:A),COLUMN(A2))),5))

「CELLはセルの情報を取得する関数でセルを文字列に変換するにも使えるのでこれで開始行をおさえる。最終行のほうはCOUNTAで行番号を作って、ADDRESSで合成する。これで範囲の文字列が出来上がるわけだな」

　正直CELLは使ったことがなかった。ARRAYFORMULAのようなExcelにない関数だけでなく、まだまだおれには知らない関数がたくさんある。生きてる間は他のExcel職人が作ったシートなんて見る機会はなかったが、こうやってworkerとしてクエストをこなしていると自分では思いもよらなかった関数の組み合わせや使い方に出会う。

　いかに自分の生きていた世界が狭かったのかを理解した。関数と戦う身体能力だけではなく、知識を増やしていかねばならない。いつまでもサイトウに頼りきりというわけにはいかないのだ。

※今回の関数

CELL https://support.google.com/docs/answer/3267071

ADDRESS https://support.google.com/docs/answer/3093308

***************************

Twitterで指摘いただいて、ARRAY_CONSTRAINを使えばもっとシンプルに記述できるということに気付かされました。

=ARRAYFORMULA(LEFT(ARRAY_CONSTRAIN(A2:A,COUNTA(A2:A),1),5))

https://twitter.com/kayo_818/status/1260917561463410688

負債をばら撒いてしまったなぁという後悔に駆られつつ、「アウトプットをする人間にすべての知見が集中していく」という言葉に慰められています。

ARRAY_CONSTRAIN https://support.google.com/docs/answer/3267036?hl=ja