スプレッドシートは、簡単に共有ができて、全員で編集ができるため、複数人で数値を確認する場合には非常にオススメなツールです。
管理職ともなると数値集計にも毎日目を光らせる必要がありますが、スプレッドシートを駆使すれば毎日の数値集計を自動化することが可能になります。
本記事では、スプレッドシートを使って数値の集計を自動化させるための3ステップと、数値集計の際に特に活用できる10の数式・機能について動画付きでご紹介します。
目次
スプレッドシートでの集計の自動化を考えるステップ
スプレッドシートでの数値集計作業を自動化するためには、以下の流れで考えて実装するのがオススメです。
集計したい数値のタイプを明らかにしよう
まずは、集計を自動化したい数値は以下のいずれのタイプなのかをはっきりさせましょう。
- 日々更新、蓄積されていくデータ（例：活動量や出勤日数など）
- ある一時点の大量のデータ（例：見込み客情報分析や顧客情報分析など）
いずれかのタイプに分ける理由は、それによって使用する数式が異なってくるからです。具体的には、以下のように違います。
日々更新、蓄積されていくデータを集計する場合：SUM、AVERAGE、ARRAYFORMULA など
ある一時点の大量のデータを集計、分析したい場合：COUNTIF、COUNTIFS、COUNTA、IF、IFS、IMPORTRANGE など
目的によって武器を使い分ける必要がある、ということです。
データの更新（インプット）方法を決定・固定しよう
集計したい数値の元となる各データは、入力箇所と方法を固定しましょう。
入力のたびにデータの場所が変わってしまっていたら、せっかく作成した数値集計の自動化のための数式が役に立たなくなるからです。
たとえば、自部門のメンバーの業務状況を把握したいのであれば、部下に入力させる場所を「決まったセル」に固定させる必要があります。
図のように表を先に作ってしまって、部下に入力させる場所を固定する方法でもOKですし、入力してもらう方法をGoogleフォームに固定するのでもOKです。
計算したい数値を導く計算式を先に入れておこう
データの更新方法が決定したのなら、あとは更新されるデータが計算されるようあらかじめ計算式を入れておくだけです。
以下の画像でいうと、担当者3名の合計訪問数と平均訪問数の欄に、それぞれ先にSUMとAVERAGEを入れておくだけでOKです。
先に数式を入れておくと、右側の入力欄に数値を入れておくだけで動画のように勝手に計算されていきます。
数値管理を始める前に「どの数字を知りたいのか」という数値を集計する目的さえしっかりしていれば、あとはその準備をするだけで、いとも簡単に数値集計を自動化できちゃいます。
スプレッドシートの集計を自動化するために使える数式・機能一覧
スプレッドシートで数値集計を自動化する際に活用できる数式や機能には、以下のようなものがあります。
- SUM（合計）
- AVERAGE（平均）
- COUNTIF（単一条件でのカウント）
- COUNTIFS（複数条件でのカウント）
- COUNTA（データ数のカウント）
- IF（条件により表示を変える）
- IFS（複数条件により表示を変える）
- IMPORTRANGE（よそから持ってくる）
- ARRAYFORMULA（入力をサボる）
- ピボットテーブル（いい感じにまとまる）
SUM
SUM関数は、範囲内の合計数値を計算する数式です。以下のような数式になります。
＝SUM（範囲）
※範囲は「A1:A15」のような「ここから：ここまで」という表し方をします。
範囲はわざわざ打ち込まなくとも、動画のようにクリックしながら範囲を囲むだけでもOKです。
活用できるシーンは、SUMの範囲内に入力する欄を設けておいて、入力した瞬間に自動計算がされるようにしておく場合が多いです。動画のような毎日変動する訪問数（活動量）などには非常に活用しやすいです。
=sum(D3:H3)おまけ
ちなみに、さとうさんとすずきさんの合計数値も計算できるようSUMを追加したい場合、オートフィルを活用するのが便利です。
オートフィルとは、セルを選択したときに見える右下の青い■をクリックしながら引っ張ると、同じような結果を表示してくれる便利機能です。動画の通りにやってみると、数式を入れる手間が省けます。
AVERAGE
AVERAGEは範囲内の平均数値を計算する数式です。以下のような数式になります。
＝AVERAGE（範囲）
※範囲は「A1:A15」のような「ここから：ここまで」という表し方をします。
やっていることはSUMと同じで、最初にSUMと入力するかAVERAGEと入力するかのどちらかです。
=average(D3:H3)おまけ
もちろんオートフィルも可能です。
COUNTIF
COUNTIFは範囲内で1つの条件に当てはまるものの個数を計算する数式です。以下のような数式になります。
＝COUNTIF（範囲，条件）
※範囲内にある、条件に合致するものが何個あるか？ を調べるときに活用します。
動画では、見込み企業リストの中に所在地が「宮城県」の企業は何社あるか調べるために、数式を入れています。
条件が数値ならそのまま入力し、「宮城県」などの名前の場合は「”宮城県”」のように「””」で名前をくくる必要があります。
=countif(B3:B12,"宮城県")COUNTIFS
COUNTIFSは複数の条件を同時に満たすデータの数を計算する数式です。以下のような数式になります。
＝COUNTIFS（範囲1，条件1，範囲2，条件2……）
※条件の数はいくらでも追加可能です。
※条件1かつ条件2に当てはまるデータの数を計算します。「または」ではありません。
動画では、見込み企業リストのうち所在地が「宮城県」かつ従業員数が「100名以上」という条件でカウントします。
COUNTIFSやCOUNTIFで「100名以上」のようなある数値以上または以下を表現しようとする場合は、「＜」や「＞」を「””」で囲み、「＆」でつなぐ必要がありますので、注意してください。
=countifs(B3:B12,"宮城県",D3:D12,">="&100)COUTA
COUNTAは範囲内にあるデータの個数を計算する数式です。以下のような数式になります。
＝COUNTA（範囲）
※範囲は「A1:A15」のような「ここから：ここまで」という表し方をします。
動画では、表の中にそもそも見込み企業である会社は何社あるのかを計算しています。
COUNTAは範囲を入力するだけで計算してくれるので、簡単な数式のひとつです。
=counta(A3:A12)IF
IFは、「Aという条件に当てはまったらBと表示させる」という、条件によって表示の仕方を変える数式です。以下のような数式になります。
＝IF（条件，条件に当てはまった場合の表示，そうじゃない場合の表示）
IFはSUMと並ぶくらい数式として有名ですが、IFを使いこなせたら非常に多くのことを数式で行うことができます。
動画では、リスト内にある企業のうちエリアが「東北」なら「近い」と表示させるような数式を紹介しています。
条件に合致していないときの表示として「””」と入力すると、当てはまらないデータには空白で表示されるようにすることができます。
=if(C3="東北","近い","")IFS
IFSは複数の条件を1つずつ当てはめていき、当てはまったところの結果を表示させる数式です。以下のような数式になります。
＝IFS（条件1，条件1のときに出す結果1，条件2，条件2のときに出す結果2……）
※条件は127個まで設定可能です。
※条件1と条件2を同時に満たしている場合は、結果1が優先的に表示されます。
動画では、見込み企業のうちエリアが「東北」なら「近い」、「関東」なら「遠い」、「北海道」なら「寒い」と表示させるようにしました。
ちなみに、「いずれの条件にも当てはまらないときに空白にする」という挙動にするためには、最後の条件の後に「TRUE,””」と付け加えればOKです。
=ifs(C3="東北","近い",C3="関東","遠い",C3="北海道","寒い")IMPORTRANGE
IMPORTRANGEは、ほかのスプレッドシートのデータを参照する数式です。Excelでは他のファイルのデータを表示させることはできませんが、スプレッドシートはこの数式を使用することで可能となります。以下のような数式となります。
＝IMPORTRANGE（シートID，シート名！場所）
※シートIDとは、スプレッドシートのURL内にあるhttps://docs.google.com/spreadsheets/d/ココ/editのことです。
動画では、別のスプレッドシートに作成していた見込み企業リストの企業数を訪問管理表に表示させるようにしています。
IMPORTRANGEは以下のような場面で非常に有効です。
1つのシートにほかのスプレッドシートの数字をまとめられるので、「あのデータどこ行ったっけ？」と探す時間を省けます。
見たいものだけIMPORTRANGEで引っ張ることで、見る専用のスプレッドシートを軽くできます。ロードタイムに悩まされることもありません。
ほかにも、大きなデータのリストが2つあってVLOOKUPを使用したいときに、1つのスプレッドシートに2つのデータをまとめずとも、IMPORTRANGEで検索することも可能です。
=importrange("1yv3CWFq_7_nh4dBlQzQg4FUNgVysF-rFL4o-QPcaaIc","シート1!G9")ARRAYFORMULA
ARRAYFORMULAは、オートフィルの変わりになるような数式です。以下のような数式になります。
＝ARRAYFORMULA（オートフィルしたい数式）
※オートフィルしたい数式を先に入れて、ARRAYFORMULAで囲む みたいな覚え方が分かりやすいです。
百聞は一見にしかずなので、以下動画をご覧ください。
もともとIFの数式を入力していたセルですが、ARRAYFORMULAで囲み、C3と入力していた部分をC3:C12に変更しています。
「C3が東北だったら近い」を「C3からC12のそれぞれが東北だったら近い」というような数式に変えることで、全て一発で結果を出す、という仕組みです。
ARRAYFORMULAを使用しない場合に●▶︎■株式会社まで同じ数式を入れようとすると、オートフィルで12行目まで引っ張る必要があります。今は10社なのでオートフィルでもできますが、これが1,000社とかになってくるとオートフィルで引っ張るのも時間がかかります。
これを一発で解消してくれるのがARRAYFORMULAである、ということです。
また、オートフィルよりもARRAYFORMULAの方が数式も1つで済むのでスプレッドシート自体が軽くなって操作もしやすくなります。
ピボットテーブル
ピボットテーブルは、数式を使わずにデータ個数のカウントや合計数値を計算してくれる便利機能です。
上部にある「データ」を選択して、ピボットテーブルを選択します。その後、データ範囲を選択すればピボットテーブルが作成できます。
Excelのピボットテーブルと違い、スプレッドシートのピボットテーブルは自動更新なので、先に範囲さえ固定しておけば勝手に新しい数値に変わってくれます。
数式を打ち込むことすら面倒、という場合はオススメな機能です。
例えばどんなものを自動化できるか
今回ご紹介したスプレッドシートの数式や機能を活用して、自動化できそうな数値集計の例をいくつかあげてみます。
- 売上、仕入管理
- 従業員の勤務日数
- 経費精算
- 部署メンバーの活動量管理表
- 顧客リストの最新状況
- アプローチすべき見込み企業数
- 料理のレシピのカロリー数
- 家計簿 etc.
要するに、大体のことは自動化できちゃいます。
数値を集計する作業は工夫次第でいくらでも自動化ができます。ぜひがんばってみてください。
報告が必要ならGoogle App Scriptを活用しよう
スプレッドシートを活用して自動で集計した数値を、だれかに報告する必要がある場合は、Google App Script（GAS）を活用しましょう。
定期的に発動させるような条件設定をすることも可能ですし、「ボタンを押したら送信する」のように設定しておくことも可能です。
定期的な報告をGASを活用して自動化することを詳細に説明している記事もありますので、興味があればご確認ください。
さいごに
今回はスプレッドシートでの数値集計を自動化させる流れと、そのために使える数式や機能についてご紹介しました。大切なことは、どんな数値を取りたいのかという目的からスタートして、それを実現する手段として数式を活用することです。
一度組んでしまえば自動で数値集計ができるため、忙しい管理職の人はもちろん、リストを扱う人には非常にオススメです。ぜひお試しください。
