Googleスプレッドシートは、業務効率化に使える機能が豊富です。
前回の基本編では、便利な関数や条件付き書式の使用例をお伝えしましたが、ちょっともの足りない、さらに深めたい…という方もいるんじゃないでしょうか。
そこで今回は、もう少しだけ突っ込んだ内容をご紹介します。配列や Google Apps Script（GAS）といった中級レベルの活用法です。
1.VLOOKUPを配列で進化させる
.png?w=640)
値を参照するのにVLOOKUPは便利ですよね。
VLOOKUPでは、商品名をキーにして、表から指定した属性（価格や売上など）を引っ張ってこれます。
しかし、便利なこの関数には、普通のやり方ではキーより左側の列のデータが得られない、との弱点があります。上の表では、商品名から商品コードが引っ張ってこれません（ExcelではXLOOKUPというパワフルな関数で克服しています）。
VLOOKUP以外の関数を使ってこれを実現する方法もありますが、今回はより直感的な、VLOOKUPと配列を組み合わせた方法をお伝えしたいと思います。
配列について理解しよう
.png?w=640)
まず、配列について簡単に説明すると、"{}"でくくった値をデータのグループとして扱うものです。
={1,2,3;4,5,6,}
配列では、セミコロンやカンマで区切りを入れて列や行といった範囲を指定することもできます。
={A1:A2,B1:B2,C1:C2}
.png?w=640)
A列、B列を配列で入れ替える
.png?w=640)
今回、「H24」の商品名をキーにして、B列から「商品b」を探し、A列のデータが「I24」に表示されるようにしました。
本来のVLOOKUPであれば、B列より左にあるA列のデータは読み込めません。しかし、配列でAとBの列を、以下のようにBからAの順番で記述することで、参照できるようにしました。
={B2:B20,A2:A20}
上の記述を利用すると、VLOOKUPを使ってA列を持ってこれるようになります。最終的に、「I24」に以下の数式を入力して完成です。
=VLOOKUP(H24,{B2:B20,A2:A20},2,FALSE)
これで、H24の「商品b」をキーにして、「A20」の商品コードを表示できるようになりました。
ついでにIF文で、H24セルが空白のときもエラーが表示されないようにしておくのもいいでしょう。
2.ARRAYFORMULA関数で配列を利用して計算できる
.png?w=640)
さて、商品名から商品コードを引っ張ってくるという少々マニアックな例で配列を説明してきましたが、配列を使うとほかにもさまざまなことができます。
たとえば、売上の合計を求めるのに便利です。
販売個数と単価を掛け合わせて、SUM関数で合計を出す作業は、一つの配列で計算できます。
それが、ARRAYFORMULAです。
以下のようなシンプルな記述でこれが実現できます。
=SUM(ARRAYFORMULA(D2:D20*F2:F20))
2つの縦列の合計が、これで簡単に出せます。
2-2.ARRAYFORMULAはセルへの数式入力の手間も省く
.png?w=640)
もう1つARRAYFORMULAの利用例を挙げておきます。
「利益÷売上」で求められる利益率を、ARRAYFORMULAによって記述すると以下のようになり、1つのセルへの記述で済みます（表示形式は"パーセント"にしています）。
=ARRAYFORMULA(I2:I20/H2:H20)
もちろん、同じセルにI2/H2を記述してオートフィルでもいいのですが、商品の行が追加されることを考えると、数式をどこまでコピーしていいのか…といった問題が出てきますよね。
ARRAYFORMULAを使った上記の数式を少し変更すれば、この問題は解決します。以下のようなものです。
=ARRAYFORMULA(IF(A2:A<>"",I2:I/H2:H,""))
少しややこしいかもしれませんが、まず"I2:I"といった最終行を指定しない方法で、範囲の下限をとっぱらっています。
そのうえで、IF文によって、商品入力のないセル（ここではA列の商品コードの入力有無で判定）にもエラーが表示されないようにしました。
3.カスタムファンクションを呼び出してさらなる業務効率化を
.png?w=640)
最後は、 GASという、便利なプログラミング機能に触れておこうと思います。
本来Gmailを操作したり、Webページからデータを取ってきたりと、外部機能との連携にも利用できるこの機能ですが、複雑なことをしようとするとやはり前提知識が必要になってきますので、ここではすこぶる簡単な例（メールアドレスの記述からドメイン名を抜き出す）を取り上げます。
GASでは自分だけのオリジナル関数（カスタムファンクション）が作れて、業務でよく使う関数を登録しておくと効率化できますよ。
.png?w=640)
カスタムファンクションを作るには、メニューバー「ツール」の「スクリプトエディタ」を立ち上げます。ここで、substring()メソッドと lastIndexOf() メソッドで、email（変数）に渡された文字列の、@以下を抜き出して返す…といった内容のMyFunc関数を作りました。
このMyFuncをGoogleスプレッドシートで使用する際は、結果を返すセルに以下のように記述すればOKです。これで完成です。
=MyFunc(E3)
今回は配列やGASの世界を垣間見ていただきました。
特にGASは、ご自身の業務をダイレクトに効率化できる使用法がきっと見つかると思いますので、ご興味のある方、どんどん深めていっていただければと思います。
あわせて読みたい
