Google Spreadsheetsがいつの間にかSheetsという名前に変わっていたことを今日まで知らなかった初心者です。最近必要に迫られてExcelとGoogle Sheetsで数式パズルをやることがあり、今更ながら便利な機能がたくさんあることを知りました。
基本機能
中括弧で囲むと複数列の値を出力できる
例えばセルに ={1,2} と入力すると、そのセルと右隣のセルにそれぞれ 1 2 が出力されます。
値だけでなく数式も指定可能なので ={A1*2,B1*3} のような指定も可能です。
ARRAYFORMULA関数
=ARRAYFORMULA(A1:A5*2) のような指定で、一気に5行分の計算をして出力できます。基本的にオートフィルで数式をコピーしていっても同じことができますが、コピーの手間が省けるほか、行数が多くなると速度的にも有利になります。
B2～B5セルには何も入力していません。
非常に便利なので、多数のサイトで紹介されていますね。ただ、「オートフィルを省略できる」以上の活用方法は意外と紹介されていない印象を受けました。
中間変数用の列を節約したい
検索キー側に数式を適用する場合
例えばこのようなデータがあるとします。A～B列はマスタデータで、D～F列は請求書データのイメージです。
この表で、F列に顧客名を入れたい場合は VLOOKUP を使うと思います。AAAA-01 という文字列から AAA を探すために、一旦G列あたりに SPLIT で展開しても良いのですが、中間変数のように使う列がどんどん増えるのは美しくありません。この場合は第1引数の「検索キー」に数式を当てたいため、単純に詰め込んであげることで対応できます。
=VLOOKUP(INDEX(SPLIT(D2,"-"),0,1),A$2:B,2,FALSE)
※上のスクショではF3～F6列にオートフィルで式をコピーしています。
検索範囲側に数式を適用する場合
本題です。次のような場合を考えてみます。A～C列にマスタデータがあり、E～F列には別のシステムから出力したデータがあります。
学校名をキーに担当者を導出したいのですが、学校名は都道府県単位をまたぐと一意でないため、A列とB列を結合させたものを検索しなくてはいけない、とします。
VLOOKUPの第2引数「範囲」には、値ではなくレンジを指定するため、そのままでは数式を適用できません。しかし、 ARRAYFORMULA を使えば、中間列を作ることなく直接検索できます。
=VLOOKUP(E2,{ARRAYFORMULA(A2:A&"立"&B2:B),C2:C},2,FALSE)
なお、上のスクショではF3列にも式をコピーしていますが、全体をさらにARRAYFORMULAで囲めば、それも不要になります。
=ARRAYFORMULA(VLOOKUP(E2:E3,{ARRAYFORMULA(A2:A&"立"&B2:B),C2:C},2,FALSE))
ARRAYFORMULAは便利！
これまでSheetsの数式は、ちょっと複雑なことをやろうとするとすぐに中間列が増えて鬱陶しいのが気になっていました。ARRAYFORMULAを使えば、少しだけ気持ちよく書けそうです。
ただ、以下の点には気をつけようと思います。
- Excelでは使えないので、あとでエクスポートして使う予定があるなら避けるべき。
- やりすぎるとネストが深くなって読みづらくなるので程々に。
