Googleスプレッドシートでよく使う関数11個
事業の状況をタイムリーに把握するために経営・事業ダッシュボードを構築・運用することは重要ですが、敷居が高いと感じる方も多いかもしれません。そのような場合、おそらく多くの方に馴染みがあるGoogleスプレッドシートを使ってダッシュボードを構築することを検討するのは、低コストで良い選択肢の1つです。データをうまく整理し可視化することで、これにより、より迅速かつ正確な意思決定ができる基盤を築くことができます。
この記事では、私が過去にGoogleスプレッドシートで多数の経営・事業ダッシュボードを作成してきた経験から、特に役立つと感じた関数を11個ピックアップして紹介します。今回取り上げる関数は11種類ですので、これらの関数を覚えるだけで、Googleスプレッドシートを活用したダッシュボード作成が格段にスムーズになるでしょう。
IF
IF関数は、指定した条件が真（TRUE）か偽（FALSE）かに基づいて、二つの異なる結果を返す関数です。
この式は、セルA1の値が10より大きい場合に"Yes"を、そうでない場合に"No"を返します。
ダッシュボードでの具体的な活用シーンとしては、
- 昨対比（YoY）を月別に見たい
- 実績が確定していない数字は空白にしたい（例えば今日が9/15だとした場合、9月の月次の数字は確定していないためYoYの数字を見るのは8月以前までにしたい）
といった場合に、初月〜8月の場合はYoYを計算し、9月〜の場合は空白にする、みたいな計算式が組めます。
IFERROR
IFERROR関数は、指定した式の計算結果がエラー（例：#DIV/0!, #N/A, #NAME? など）である場合に、代わりの値を返す関数です。
この式は、セルA1をセルB1で割る計算を行います。もしB1の値が0であるためにエラーが発生する場合、"Error"というテキストを返します。
ダッシュボードでは、「#DIV/0!」などが出て全体の計算結果がエラーになるのを避けるため、
とだけ記載し、もしエラーの場合はそのセルが何も表示されないようにしたりします。IFERRORを付けておくと安心な場面があるので、覚えておいて損はないでしょう。
COUNTIF
COUNTIF関数は、特定の条件に一致するセルの数をカウントするために使用されます。
例：A列に1, 2, 3, 4, 5といった数値が入っている場合、
は「3」という結果を返します。
条件が1つしか設定できないため、実際にダッシュボードを作る際には、使う頻度はあまり高くありません。
COUNTIFS
COUNTIFS関数は、複数の条件に一致するセルの数をカウントするために使います。
例：A列に1, 2, 3, 4, 5といった数値が、B列に"Apple", "Banana", "Apple", "Cherry", "Apple"といったテキストが入っている場合、
は「2」という結果を返します。条件に一致するセル（A列で3、5とB列で"Apple"）が2組あるからです。
ダッシュボードを作る時、COUNTIFSはよく使います。なぜならダッシュボードを作る時に、
- 昨対比（YoY）を見る時に今年と昨年のデータを指定する必要があり、まず年（Year）で指定する
- 事業の状況を見る時は少なくとも月別には見るので、月（Month）で指定する
ため、2つ以上の条件に合致する項目数を計算するケースが多々あるためです。
実際には商品カテゴリや商品名などでも条件指定することがあるので、COUNTIFSで指定する条件が3つ4つになることもあります。そうなると数式が長くなりますが、「年（Year）が2023で、月（Month）が9で、商品カテゴリがXXで・・・」といったように一つずつ落ち着いて数式を書いていけばそこまで難しくありません。
SUMIF
SUMIF関数は、指定した条件に一致するセルの値を合計するための関数です。
は、A列内の値が100より大きい場合、B列に対応する売上数を合計します。
COUNTIF / COUNTIFSでご説明したのと同様、複数条件で絞ることが多いため、SUMIFを使う頻度はあまり高くありません。次に紹介するSUMIFSが高頻度で使用します。
SUMIFS
SUMIFS関数は、複数の条件に一致するセルの値を合計するための関数です。
A列に日付、B列に商品名、C列に売上数が記録されている場合、
は、日付が2021年1月1日より後で商品名が"Apple"であるセルに対応する売上数を合計します。
売上や費用や販売数量など、さまざまな数字の合計をするのに便利な関数です。絞る条件は、例えば
- 年（Year)
- 月（Year）
- 店舗名
- 商品カテゴリ/商品
- 流入経路
など、とにかく色んな集計で使います。
IMPORTRANGE
IMPORTRANGE関数は、別のGoogleスプレッドシートからデータをインポートするために使用されます。
この式は、指定されたURL（またはID）のスプレッドシートの「Sheet1」におけるA1からA10までのセル範囲のデータをインポートします。この関数は、別のスプレッドシートに保存されているデータを簡単に取り込む場合や、複数のスプレッドシートを連携させる際に非常に有用です。
ダッシュボードでは、別チームが管理しているスプレッドシートがあり、そのスプレッドシートのデータをダッシュボードに持っていきたい時などに使います。そうすると2箇所に入力する必要がなく、元のスプレッドシートが更新されればIMPORTRANGE関数によりダッシュボードにも自動で更新されます。
色んなスプレッドシートで入力作業をして、一つのスプレッドシートにIMPORTRANGEで飛ばす、みたいな活用方法もあります。IMPORTRANGEが好きな人はつい多用してしまうくらい癖になる関数の1つです。
ARRAYFORMULA
ARRAYFORMULA関数は、単一の式や関数を一連の値またはセル範囲に対して適用することができます。これにより、多数のセルにわたって同じ計算を繰り返す場合に、その計算を単一の式でまとめることができます。
この式は、A1からA5までの各セルの値が5より大きいかどうかを評価し、それに応じて"大"または"小"という文字列を出力します。
ARRAYFORMULA関数の良いところを一言で言うと、主に多数の行に対して同じような数式を入れる場面で、1つのセルに数式を入れるだけであとは全部自動で計算してくれるところです。つまり、売上データなどで毎月・毎日データを追加していく場合、数式をあらかじめ下の方まで入れておいたり、どこまで数式を入れたか確認したり、といった手間が省けます。また、数式漏れによる集計ミスも防げます。
少し注意点を挙げるとすると、ARRAYFORMULA関数を知らない人が見た時、数式が何も入っていないところに数字が表示されて混乱させてしまう可能性があります。そんな時はぜひARRAYFORMULA関数について教えてあげると親切です。
VLOOKUP
VLOOKUP（Vertical Lookup）関数は、指定した値を最初の列で検索し、その値が含まれる行の指定した列にある値を返す関数です。
この式は、A1からA5の範囲内で商品Aという値を検索し、該当する行のB列の値を返します。検索は厳密に行われます（FALSE指定）。
売上や販売数の集計など場合はSUMIFSをよく使うため、VLOOKUPの知名度の割にはそこまで使わないかもしれません。使用シーンは、例えば商品Aの単価を商品マスタから引っ張ってきたい、といった場面です。
HLOOKUP
HLOOKUP（Horizontal Lookup）関数は、指定した値を最初の行で検索し、その値が含まれる列の指定した行にある値を返す関数です。
この式は、A1からG1の範囲内で1月という値を検索し、該当する列の3行目の値を返します。検索は厳密に行われます（FALSE指定）。
HLOOKUP関数は、データが水平方向に配置されている場合に特に役立ちます。例えば、時系列データや、項目が横方向に並べられている場合などに使用します。
使用頻度はVLOOKUPよりもさらに少ないですが、たまに使えると便利な場面があるので知っておいて損はありません。
INDEX MATCH MATCH
そして最後にINDEX MATCH MATCHです。
INDEXとMATCH関数を組み合わせる（多くの場合はINDEXとMATCHとMATCHを組み合わせる）ことで、VLOOKUPやHLOOKUPの制限を克服し、より柔軟な検索と参照が可能になります。特に、INDEX MATCH MATCHの組み合わせは、2次元の表からデータを検索する際に有用です。
この式は、A1:C5の範囲内で、行方向に"Apple"と一致する行と、列方向に"Jan"と一致する列の交点にある値を返します。
数式が長くなりがちでぱっと見わかりづらいですが、SUMIFSやVLOOKUP、HLOOKUPではどうしても集計できないときに役に立ちます。INDEX MATCH MATCHが使えると、細々としたシート作成作業が大幅に効率的になります。
最後に
今回紹介した関数はいずれも、実際にご自身で触って数式を作ってみることで理解度が上がります。ぜひ試してみてください。
経営・事業ダッシュボード構築・運用のご相談は、こちらからお気軽にお問い合わせください。