Googleスプレッドシートを使い、リストの作成やプロジェクト管理、事業計画書・報告書の作成を行う人は多くいます。仕事の生産性を上げる上で欠かせないツールとなりました。ある程度使いこなせるようになったものの、関数を上手く組むことができないと悩む人は少なくありません。
先輩や同僚に関数の組み方を聞いても、そもそも基本的なことが身についていないから何を言っているのかわからない。そんな経験があるかもしれません。この記事では、関数の基本的な作り方、考え方から実践で役立つ関数を解説します。
実務を想定してどのように関数を使えば良いのかも併せて説明します。
初心者にとって、Googleスプレッドシートの関数はとっつきにくいと感じるかもしれません。それは、関数によって何が実現でき、どのような仕組み（ロジック）になっているのかが分からないためです。
関数はデータの処理速度を飛躍的に向上できます。想像以上に生産性が上がります。
関数の基本を抑え、営業リストや事業計画書、報告書の作成に役立てましょう。
Googleスプレッドシートで関数を作る際は任意のセルで「=」を入力します。すべて半角で入力してください。全角で入力するとエラーになります。
関数に慣れてくると、あちこちのセルに計算式を入れて計算結果を見ようとすることがあります。Googleスプレッドシートや関数を使いこなすという側面では決して悪いことではありませんが、できる限りスプレッドシートを整理し、第三者にもわかる形で作成することが重要です。
関数によって重要な発見をしたものの、どのような計算をしたのか忘れてしまった、どこのセルを参照したのか分からなくなってしまったということが頻繁に起こるからです。スプレッドシートを使う際は、何をゴールとしているのかを明確にし、答えを導くために数字や項目の整理をし、計算式を入れるのが良いでしょう。
計算したいセルを選択し、足し算は「+」、引き算は「-」、掛け算は「*」、割り算は「/」を入力します。
「足し算」・「引き算」と「掛け算」・「割り算」を組み合わせる際は、計算の基礎で習う通り、「掛け算」と「割り算」が先に計算されます。「足し算」や「引き算」を先に計算したい場合は（ ）を使ってください。
下の図でりんごとバナナの合計販売額を出す場合は、「（ ）あり」が正しい値です。ありとなしで数字に大きなズレが生じていることに注意してください。
もちろん、セル内に直接数字を入力して計算することもできます。
しかし、ビジネスで使う報告書や事業計画書などにおいて、セル内に直接数字を打ち込むことはおすすめしません。理由は生産性の向上と、第三者にもわかりやすくするためです。
例えば上のケースだと単価は137円ですが、この金額は常に一定とは限りません。単価が140円になった場合、C5の単価を140に入力しなおし、更にC6の計算式を修正する必要があります。2度手間です。
また、このGoogleスプレッドシートを別の人と共有していた場合、計算式はセルを参照しているものと勘違いし、C5の単価を入力し直せば自動的に計算式の値は正しいものになると考えているかもしれません。その場合、誤った数字が表示されてしまいます。
次にGoogleスプレッドシートの基本的な関数を紹介します。使い始めは覚えるのが大変ですが、自然に関数が出てくるようになると事業計画や商品の需要予測、商圏分析などの複雑な処理をすることができるようになります。
使えそうだと思った関数は、パソコンなどのメモに入れておくことをおすすめします。
数値の合計を求める関数です。「=」の後に「sum」と入力します。ローマ字は大文字でも小文字でも構いません。次に矢印キーを押して指定のセルを決めたらシフトキーを押し、合計値を求めたいセルまで動かします。最後に「）」を入力すると自動的に計算されます。
SUM関数に限りませんが、関数を入力する際は計算したい値がすべて含まれているか必ず確認するようにしてください。
同じ関数を横並びで複数入力する場合は、それぞれのセルに関数を一つひとつ入れる必要はありません。起点となるセルに関数を入力後、セル右下に表示される青い点にマウスのポインターを合わせ、上下左右に動かしてください。これをオートフィルと言います。
下の例の場合は右（→）に動かします。
オートフィルは基本的にすべての関数に適用可能です。
平均値を求める関数です。販売数や販売価格、点数などの平均値を瞬時に求めることができます。
AVERAGE関数は平均値を求める便利な関数ですが、異常値があった場合には必ずしも妥当な結果を出しません。例えば、100点の人が1人、0点の人が3人いると平均値は25点になります。
平均値を出す際は、データの分布にばらつきが生じていないかチェックすることも重要です。
SUMPRODUCTは範囲同士の掛け算ができる関数です。複数行の掛け算の結果を一つのセルに表すことができます。
下の図の例では、商品数と単価をすべて掛け合わせた数をC9に表しています。つまり、商品ごとの数と単価をかけた結果がE3-E7に出ていますが、この数字の合計額がSUMPRODUCTで計算されたC9の答えということです。
SUMPRODUCTのように複数の行や列を指定する場合は、セルの範囲を指定した後、カンマ「,」を入力し、別の範囲を指定します。
SUMPRODUCTは請求書や販売管理、営業成績表の作成など、使い勝手の良い関数です。
SUM関数の応用編です。
条件を指定して合計値を求めることができます。下の図では、日曜日の販売数を求めています。
「=SUMIF」を入力した後、検索の対象となるセルの範囲を選択します。次に検索する条件を指定します。今回の例の場合は各曜日の中から日曜日を選んでいますので、カンマ「,」を入れた後に「”日”」と入力しています。
再びカンマ「,」を入れ、合計したい値が入っているセルの範囲を指定します。
条件を直接指定する場合は、必ず「” ”」を入れてください。ただし、基本的には下のようにリストを作成しておき、セルで選択した方が整理しやすいです。
上のような表を作成すれば、曜日ごとに「”日”」「”月”」などと入力する手間が省けます。セルごとに関数を組む必要がなく、オートフィルで計算式を自動的に作成できます。
縦移動のオートフィルで計算式を作成する場合に気をつけたいのが、選択するセルが自動的にズレてしまうことです。それを防止するため、選択するセルの列に当たるアルファベットに「$」を入れてください。これを入れることでズレが防止できます。
選択する範囲のデータの個数を求める関数です。
下の図では男女を合わせた全体の人数を表しています。
例えば、商品リストを作成する際、扱っている商品がリスト内にすべて入っているか確かめる際などに使われます。
次で説明するCOUNTIFとセットで覚えると便利です。
COUNTAの全く反対の値を求めるのがCOUNTBLANKです。選択したセルの範囲の中で値の入っていないセルの数を求めます。入力されていない項目がいくつかるのかを調べるときなどに使います。
指定した条件と一致するデータの数を求める関数です。
COUNTIFと入力した後、セルの範囲を指定して検索条件を入力します。
上の図の場合は、男性と女性の人数を数えています。検索条件はSUMIFと同様です。検索条件の一部が一致している場合（今回の場合は「男」「女」）は、軸となる任意の言葉や数字の前後に「*」を入れます（「*男*」「*女*」）。このようにすることで、前後に何らかの言葉や数字が入っていても、指定したデータの個数が求められます。
行番号を求める関数です。ROWを入力した後にセル範囲を入れれば、行番号が求められます。
この関数が最も活躍するのは、通し番号をふる必要があるときです。下の図の場合、B3に「=ROW()-2」と入力すれば「1」という値が求められます。そのままオートフィルで下に移動することで、通し番号になります。
行の場合はCOLUMNです。ROWやCOLUMNは複雑な関数を組む際に極めて役に立つ関数ですが、ここでは簡単な説明に留めます。
任意の範囲の中から条件に合致したデータを抽出する関数です。
例えば、学生のテストの点数を集計する際、60点以上、80点以上、30点以下の学生を関数で瞬時に抽出することができます。
セルの範囲を指定し、抽出したいセル範囲と計算式を入力します。
下の図では、販売数が300以上の商品を抽出しています。
抽出する条件は、「=：等しい」「>=：以上」「<=：以下」です。不等式の後に入れている＝を外せば、指定した数字を含まない数を抽出できます。
FILTER関数は数字だけを抽出するよりも、それに付随する要素（上の図の場合は商品名）も一緒に指定してください。
行と列を入れ替える関数です。Googleスプレッドシートには、行列を入れ替えて貼り付けすることができます。しかし、関数を使うことにより、縦に並んだデータが増えると同時に横に配列することが可能です。
最大値と最小値を求める関数です。
レストランを経営していて、最も売れているメニュー、売れていないメニューを把握するなど、営業戦略を立てる際によく使われます。
ここまでの関数を使いこなせるようになっていれば、基本的な報告書やリスト、資料の作成はできるはずです。
ここからは応用的な関数を紹介します。複雑なように感じますが、非常に利便性の高いものです。ビジネスの第一線で活躍するコンサルタントの利用頻度が高いものをピックアップしました。
Googleスプレッドシートを使ったデータの統合で、頻繁に活用される鉄板の関数です。
縦方向に並んだデータに対応する値を抽出する関数です。リストの中から住所や個数、値段、点数などを取り出すことができます。
下の図では、Aという製品群をまとめたリスト①と、製品全ての個数が記されたリスト②が並んでいます。リスト②の中からAの個数のみをリスト①に移したいと考えていたとします。
VLOOKUP関数を使うと、瞬時に値を移すことができます。
VLOOKUPは「検索値」「範囲」「列番号」「検索の型」の順に入力します。上の図の場合（計算式は便宜的にセルC9に入っていますが、C4に入力されていると考えてください）、個数を求めたい製品番号は「A-1」です。
従って、B4が「検索値」になります。「範囲」は個数が入力されているデータソースのことです。この場合はリスト②がそれに該当します。「範囲」を選択する際は、該当するセルをすべて選択します。
上の図の場合、リストはA-1からA-5まで順番に並んでいます。このような場合はオートフィルで自動的にすべてのセルの値を求めることができます。そのため、「範囲」のアルファベットはズレを防止するために「$」で囲みます。
「列番号」はリストの「何列目」の数字を参照するのかを指定するものです。今回は製品番号の隣の個数を抽出しようとしているので、2番目の「2」と入力しています。
最後の「検索の型」は、検索する値が見つからない場合の処理の仕方を指定するものです。「TRUE」または「FALSE」のどちらかを入力します。
「TRUE」は検索値を超えない最大値を該当するデータとして抽出します。「FALSE」は完全に一致しない場合はエラーになります。上のようなリストを作成する場合、完全一致の値を入力しなければ、意味がありません。報告書やリストの作成でVLOOKUP関数を使用する場合は、基本的に「FALSE」で処理すると覚えて構いません。
条件を指定する関数です。
IFは「論理式」「値が真の場合」「値が偽の場合」の順に入力します。
下の図は子供のリストと体温が記されたリストから、判定基準に従って「登校」できる子供を割り出そうとするものです。
この場合は「登校」できる子供を抽出するものですので、子供1の「論理式」は判定するためのセル（体温）であるC4が、判定基準である36.9以下かどうかです。従って、「C4<=F4」となります。
このリストも縦に並んでおり、オートフィルで全員分を瞬時に埋めたいので、判定基準であるF4がズレないよう「$」でアルファベットを囲みます。
「値が真の場合」は、「登校」を入れたいので、「H4」と入力して論理式と同じく「$」で囲みます。
「値が偽の場合」は何も入れないことにします。空白にする場合は「””」と入力してください。これはIF関数に限らず、すべての関数で共通するルールです。
IF関数の更に応用版にIFS関数があります。先ほどの例だと、「登校」以外は空白ではなく、それぞれ起こすべき行動が一度に入った方が合理的です。IFSは複数の条件を指定することができます。
「論理式1」「値が真の場合1」「論理式2」「値が真の場合2」……という順番で入力します。ここでは複雑になるので詳細は省きますが、IFSによって関数の幅が広げられることを覚えてください。
他のシートから指定した範囲のデータを読み込むことができる関数です。Googleスプレッドシートはチームで共同して作業を行うことがありますが、他のプロジェクトシートや報告書などから、リストやデータを参照することができます。
Excelにはなく、Googleスプレッドシートオリジナルの関数です。
「”スプレッドシートキー”」「”シート名!範囲の文字列”」を入力します。先ほどの子供の体温で判別したリストを別のシートに移管したいと考えていたとします。
IMPORTRANGEの「”スプレッドシートキー”」は、参照元となるファイルのURLの「https://docs.google.com/spreadsheets/d」から下に該当します。この部分をコピーしておきます。
新しいシートにIMPORTRANGEの関数を入力した後、「”」を入れた後にペーストしてください。その後「”」を入れてカンマ「,」を入力します。次に参照するシート名とセル範囲を指定します。シート名は「子供リスト」としました。セル範囲はB3からD13です。
「”シート名!範囲の文字列”」は、「”」を入力した後に「子供リスト」と記述し、「!」を入れます。そのままセルの範囲である「B3:D13」を入力してください。
自動的にファイルを跨いでデータを参照できるはずです。
IMPORTRANGEを使うことで、ファイルの分断が防げます。値が変更になった場合は、更新することで最新の情報になります。
中央値を求める関数です。中央値とは、データを大きい順に並べたときにちょうど真ん中にくる値です。
例えば、2019年の厚生労働省の国民生活基礎調査において、1世帯当たりの平均所得は547.5万円ですが、中央値は427万円です。平均所得というと、所得の平均的な水準（ちょうど中間の値）のように感じますが、実際は国民の半分が427万円以下ということを示しています。
日報など数多くの数字が並ぶものを集計する場合、合計、平均値、中央値に分けて出すと良いかもしれません。
なお、関数の入力の方法はAVERAGEと同じです。
2つのセル範囲の相関関係があるかどうかを判定するのに役立つ関数です。必ず2つのセル範囲を指定します。
例えば、とある駅前のお店の日商は、駅の乗降客数と相関関係があるのではないかと考えたとします。そのような場合にCORREL関数を使うと、その仮説が正しいのかどうかが検証できます。
値は「-0.149」でした。
CORREL関数は＋1に近いほど相関性が強く、-1に近いほど負の相関性が強いと言われています。つまり、今回の場合は正も負も相関性がないという判断になります。
一般的に0.5くらいから何らかの相関関係があるのではないかと判断されるようですが、サンプル数や調査項目によって異なるために断言はできません。何度も繰り返し調査・検証することによって相関のあるなしが判断できるようになります。
回帰直線を使って予測値を出す関数です。
例えば、過去の事業年度の売上高をもとに予測値を求めることができます。
「予測に使うxの値」「yの範囲」「xの範囲」の順に入力します。下の図は2021年度の売上高を予測するものです。
年度は横軸になるので、xの範囲となります。売上高はyの範囲です。FORECAST関数を使う際は、事前にCORRELで相関性があるかどうかを検証してください。
上の例の場合、CORREL関数で返ってきた値は7.54でした。そこそこの精度で予測ができそうです。
TREND関数はFORECAST関数の応用です。複数の重回帰を使って既存の値から将来の値を予測することができます。
重回帰分析はやや概念そのものが複雑ですので、ここではこの関数を使って何年か先までの予測値を出すまでに留めます。先ほどのFORECASTでは、単年度の予測を出しましたが、TREND関数によって数年先の予測を出すことができます。
「yの範囲」「xの範囲」「予測したいxの範囲」の順です。
Googleスプレッドシートに記載された英語を日本語に翻訳してくれる便利な関数です。
「翻訳したいテキスト」「”翻訳前の言語のコード”」「”翻訳したい言語のコード”」を入力します。
言語コードを入力する際は必ず「” ”」を入れてください。
【主な言語コード】
IFERRORはエラーを表示させないようにする関数です。別の文字列などに置き換えることができます。VLOOKUP関数などを使った場合、参照する値がない場合にエラーが表示されます。それを回避することができます。
あらかじめエラーが出る可能性のあるセルに、「値」「エラーの場合の値」を入れます。値がエラーでない場合はそのまま表示されます。エラーが出た場合は「エラーの場合の値」が表示されます。
配列数式の結果を複数のセルに反映するGoogleスプレッドシート独自の関数です。先ほどROW関数を使って通し番号をふる方法を紹介しましたが、ARRAYFORMULAを使うとオートフィルを使うことなく瞬時に番号をふることが可能です。
最後は、実際に仕事で使いそうなシーンを想定し、関数で作業の効率化を図ってみます。「目で見て数える」「リストの数字を拾ってコピペする」といった面倒な作業から解放されます。
リストがぐちゃぐちゃで、製品などが重複しているケースがありませんか？リストに重複している製品にチェックをつけたいと考えていたとしましょう。関数は以下の通りです。
=IF(COUNTIF(セルの範囲,検索条件)>1,”★”,””)
上の図では、製品1の重複チェックをしています。重複チェック関数は、詳細を把握するよりも、数式をメモ帳などにコピーしておき、すぐに貼り付けられるようにしておくと便利です。
セルの範囲を指定してオートフィルで動かす場合、「$」を入れることを忘れないようにしてください。
Webサイトの報告書を作成しているとします。記事の管理シートには、記事のURLが記載されています。しかし、アナリティクスから抽出したデータは、ドメインが記載されていない状態でした。
VLOOKUPでPV数を瞬時に抽出したいところですが、この場合は一つひとつ見比べながら数字をコピペしなければならないのでしょうか？
やり方はいくつかありますが、便利な関数にRIGHT（LEFT）関数があります。指定した文字数を取り出す関数です。
下の図の左のリストのような記事管理表があったとします。D列の記事URLを軸として、右側のアナリティクスのデータをVLOOKUPで抽出することはできません。
そこでE列にRIGHT関数を使い、ドメイン以下の文字列を抽出します。E列を使えばVLOOKUPでPV数を引っ張ることができます。
VLOOKUPを更に応用したパターンです。
先ほどのPV数の報告書において、複数の月の過去データがあってVLOOKで各月のPV数を入力したいと考えていたとします。通常であれば、各月にVLOOKUPの計算式を入力しますが、オートフィルで瞬時に過去分を入力する方法があります。
参照するデータソースの範囲を指定した後に、COLUMN関数を入れます。COLUMN関数は列番号を求める関数でした。この関数を使うことで、オートフィルで動かすのと同時に行番号が横にスライドするのです。
VLOOKUPとCOLUMNの組み合わせは使い勝手がよく、大量のデータを処理しなければならないときに重宝します。
Googleスプレッドシートの関数を使いこなすと生産性と正確性が上がり、成果に差が出るのは間違いありません。
しかし、数多くの関数を覚えて報告書に落とし込むのは時間がかかります。WebサイトやSNSの数値成果を報告書にまとめている会社は多いはずです。しかし、漫然と数字を並べているだけで、それが何を意味するのかまでは分析しきれないのではないでしょうか？
Webの報告書や売上・集客データなどは、クロス集計して傾向を見出し、再現性を高めるPDCAを回して初めて成果に繋がります。その際には高度な計算式や統計技術が欠かせません。
INFLUはWebマーケティングのコンサルティングを専門で行う会社です。組織の課題をヒアリングし、最適な戦略構築を提案できます。過去のデータを提供していただければ、それをもとに傾向を割り出し、課題解決に向けた精度の高い方針や戦略を打ち出すことができます。ぜひ、ご相談ください。
