分析が7倍捗るGoogleスプレッドシートの神関数7選【2020年12月最新】

こんにちは！
Tokyo Otaku Mode（TOM）でマーケティングやメディアの運営を担当している清水です。

普段の業務で表計算ソフトとしてGoogleスプレッドシートを使っています。
以前はエクセルをメインに使っていたのですが、TOMに入社してからスプレッドシートに乗り換えて、すごく便利な関数がたくさんある！と気が付いたので、僕自身、分析業務で使っている関数についてシェアしたいと思います。

紹介する関数は実際にどのような使われているのか、サンプルのスプレッドシートも作ってみたので、ぜひ確認してみてください！

QUERY関数

QUERY関数とは「Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行する」関数です。

Googleの公式の解説ページを見ると、さまざまなことができそうですが、僕自身は別々のシートに分かれた月ごとのデータを、必要なカラムのみ抽出して一つのシートにとりまとめるために使っています。

たとえば、以下のようなデータを月ごとに抽出できるとします。

分析するときに、「日付」「商品」「売上」の数値のみ必要で、一つのシートにすべてのデータを取りまとめたいとします。

別々に分かれていた月ごとのデータを一つのシートに取りまとめるために、手動でベタ貼りしていっても良いのですが、手動での作業はミスが発生しやすいです。必要なカラムのみ、自動で取りまとめができるようにQUERY関数を使うと下の画像のようになります。

サンプルのシートはこちら

=QUERY({
QUERY('シート名A'!A:E,"select A,B,E");
QUERY('シート名B'!A:E,"select A,B,E");
QUERY('シート名C'!A:E,"select A,B,E")},
"where Col1 is not null order by Col1")

QUERY関数の中に{}でQUERY関数を挿入し、それぞれの関数内でシート名とその範囲を選択、select~にて抽出する列を選択します。大元のQUERY関数後半部に"where Col1 is not null order by Col1"と入力することで、月ごとのデータを、必要なカラムのみ抽出して一つのシートにとりまとめることができます。

QUERY関数は他にも色々なことができるので、興味があるかたはGoogle Visualization API のクエリ言語を確認してみてください！

ARRAYFORMULA関数

スプレッドシートについて調べているとよく出てくる関数ではあるのですが、とても便利なのでこちらでも紹介させてください。

Googleのこの関数を説明するページでは「配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。」と書かれています。
（個人的によくわからないので）かんたんに言うと「同じような数式を、複数行に渡って入力する必要がなくなる」関数です。画像のようにB2セルに一つの数式を入力することで、B6セルまで同様の法則で計算結果を表示してくれます。

サンプルのシートはこちら

たとえば、僕はさまざまなデータ分析のため、大量のデータの整形を行っています。その際、何千行に渡ってVLOOKUP関数やIF関数などを入力します。試行錯誤も発生するので、その修正をたった一つの関数で列全体に反映させることができARRAYFORMULAはとても便利な関数です。

INDIRECT関数

INDIRECT関数とは、文字列で指定したセルの値を参照する関数です。

この関数は、VLOOKUPなどと掛け合わせて、多数のシートからデータを抽出したいときに効果を発揮します。

例えば、シートごとに月ごとに売上データがまとまっているとします。

これらのデータを、商品別×月次にまとめたいとした場合、以下のように数式を入力します。

サンプルのシートはこちら

=vlookup(B$1,indirect(シート名&"!B:E"),columns($B:$E),0)

今回は、シート名と対応する行頭をA列に持ってきているので、INDIRECT関数でindirect($A2&"!B:E")と指定してあげることで、それぞれのシート名の対応する範囲を参照させることができます。

また余談ですが、VLOOKUP関数内の3つ目の項目で使われているCOLUMNS関数についてです。これは指定範囲内でカラムが何列あるかを数値で戻してくれるものです。VLOOKUPは1つ目の項目で指定したキーをフックに、何行目の数値を戻すかを数値で指定する必要がありますが、COLUMNS関数を使うことで列数を数えることなく、また、参照先の表を修正した際にも自動でそれを考慮した数値が反映されるようになるので、こちらもおすすめの関数です。

INDEX関数 × MATCH関数

VLOOKUP関数を使ってある数値を引っ張ってきたいときに、その列がキーとなる列より左側で確認できない！となったこと、誰しも一度は経験があるのではないでしょうか。

そんな状況を解決してくれるのが、 INDEX関数とMATCH関数の組み合わせです。

サンプルのシートはこちら

たとえばこちらのように「売れた個数をキーにして、商品名を抽出したい」ときに、VLOOKUP関数を使うことはできません。
そんなときに

=index(参照範囲,match(検索する値,検索したい列,0),左から数えて何列目か)

のように入力することで、値の抽出が可能です。

列の位置を気軽に変えることができない場合などにぜひ使ってみてください！

UNIQUE関数

該当の列から、重複した値が入っているセルを削除して整列したい！と思ったことはないでしょうか。範囲を指定して「重複を削除」や、ピボットテーブルの作成などでも解決可能ですが、都度の作業がなかなか面倒かと思います。

そんなときに使用するのがUNIQUE関数です。

サンプルのシートはこちら

上の画像のように、UNIQUE関数でD列を指定することで、重複が削除されてA4セルまで自動で入力されました。
今後はD～F列のデータを更新することで、自動でA列にて重複が削除された値が並んで行きますので、スプレッドシートの更新手数の削減が期待できます。

FILTER関数

FILTER関数とは、指定範囲内で条件に合致する行か列を返す関数のことです。

たとえば、売上が200以下の商品に絞って実績を確認したい場合、下の画像のように数式を入力します。

サンプルのシートはこちら

すると、A2セルに数式を一つ入力するだけで、指定範囲内の売上が200以下の行のみが全て自動で入力されます。（余談ですが、エクセルやスプレッドシートでは「小なりイコール」を「<=」と表現します。）
こちらの関数も、E～G列のデータを更新するだけで、自動でA～C列が更新されていくので、スプレッドシートの更新手数を減らすことができます。

また、先程紹介したUnique関数と組み合わせて

=unique(filter(指定範囲, 条件))

とすることで、条件を指定した上で、重複を削除して並べることもできます。
こちらもとても便利なので、ぜひ使ってみてください。

SPARKLINE関数

最後に紹介するのが、SPARKLINE関数です。
この関数は、指定の範囲の数値の動きを、該当のセル内で線グラフとしてくれるものです。

サンプルのシートはこちら

このように、数値の動きを線グラフで表してくれるので、各範囲内で数値がどのような動きをしているのか、直感的に確認することができます。
※各グラフ間で相対的ではないので、参照範囲が改善傾向なのか悪化傾向なのか、トレンドラインを確認するために使用するのが良いと思います。

特に、確認するべき行や列が多岐にわたっている場合に効果を発揮する関数だと思います。

まとめ

今回は中級者～上級者向けの、Google スプレッドシートやエクセルで使用できる便利な関数を紹介しました。
他にも役立つ関数はたくさんあると思うので、みなさんがよく使う便利な関数も教えていただけると嬉しいです！

このブログは？
TOMではFacebookなどSNSを通じた海外への情報発信と、海外に向けてアニメグッズを販売する越境EC事業などを行っています。このブログを通して、海外・越境でぶつかるハードル（言語、通貨、配送、関税、法律などの違い）をどう乗り越えてきたかを余すことなく伝えていきます。よかったらフォローしてくださいね！