Excel・スプレッドシートで実践する：関数・ピボット・分析ツール｜マーケターのためのデータ分析・統計入門 第9回

関数で要約表をつくる

第2回で学んだ「要約表」を、関数で作ってみましょう。架空のA社の顧客1,000人の年間購入金額が、D列の2行目から1001行目に入っているとします。

これらの関数は、ExcelとGoogle スプレッドシートのどちらでも同じ名前で使えます（Google スプレッドシートの関数リストでも、AVERAGE、MEDIAN、QUARTILE.INC、STDEV.S などが統計関数として掲載されています）。四分位の計算方法はソフトや関数によって少しずつ違うので、ほかのツールの結果とわずかにずれても慌てる必要はありません。

条件つきの集計

「新規客だけの平均」「検索から来た顧客の人数」のように、条件をつけて集計したいときは、AVERAGEIFS や COUNTIFS などの関数が便利です。たとえば、C列に「新規」「既存」の区分があるとき、新規客の平均購入金額は次のように出せます。

=AVERAGEIFS(D2:D1001, C2:C1001, “新規”)

ただし、条件の組み合わせが多くなると式が複雑になります。そこで登場するのが、次のピボットテーブルです。

ピボットテーブルでセグメント別に集計する

ピボットテーブルは、明細データから、行・列・値を選ぶだけでクロス集計表を作る機能です。第7回で学んだセグメント分析の多くは、ピボットテーブルで作れます。

Excel（Windows版）での基本の手順は次のとおりです。

1. 明細データの中のセルを1つ選ぶ
2. 「挿入」タブの「ピボットテーブル」を選ぶ
3. 置き場所（新しいワークシートなど）を選んで「OK」
4. 右側のフィールドの一覧から、項目を「行」「列」「値」「フィルター」の欄にドラッグする

Google スプレッドシートでも、「挿入」メニューの「ピボットテーブル」から同じように作れます。図3の例では、行に「流入元」、列に「新規／既存」、値に「金額の合計」を入れるだけで、「メール経由の売上はほとんどが既存客から」「広告経由は新規客の割合が高い」といった違いが一目で分かります。

ピボットテーブルの「値」は、合計だけでなく、個数（注文数）、平均、最大・最小などに切り替えられます。「合計」と「個数」を並べれば、セグメントごとの客単価も計算できます。第7回のコホート表も、行に「初回購入月」、列に「経過月数」、値に「顧客IDの個数」を入れれば作れます（経過月数の列は、加工のシートで事前に計算しておきます）。

相関・回帰・A/Bテストを関数で出す

第4回の相関、第8回の回帰も、関数1つで出せます。架空のA社の24か月分のデータで、A列に売上、B列に広告費、C列に繁忙期（1か0）が入っているとします。

• =CORREL(B2:B25, A2:A25)：相関係数（第4回）
• =SLOPE(A2:A25, B2:B25)、=INTERCEPT(A2:A25, B2:B25)：回帰直線の傾きと切片（第8回）
• =RSQ(A2:A25, B2:B25)：決定係数
• =LINEST(A2:A25, B2:C25, TRUE, TRUE)：重回帰分析。係数や標準誤差、決定係数などが表の形でまとめて返ってくる
• =FORECAST(100, A2:A25, B2:B25)：広告費100万円のときの売上の予測

SLOPEやINTERCEPTなど多くの関数では、引数の順番が「結果（y）が先、要因（x）が後」になっている点に注意しましょう。また、LINESTの結果は、係数が「要因の列の並びと逆の順番」で返ってきます（B列・C列の順に並べた場合、先頭が繁忙期の係数、次が広告費の係数、最後が切片）。使う前にヘルプで出力の並び方を確かめておくと安心です。

A/Bテストの判定を計算する

第5回のA/Bテスト（Aは1万訪問で200件、Bは1万訪問で250件）のp値も、表計算ソフトで計算できます。

NORMSDIST関数は、ExcelでもGoogle スプレッドシートでも使える標準正規分布の関数です。ただし、この計算は訪問数が十分に多いときの近似です。購入数が数件しかないような小さなデータでは、A/Bテストツールや統計ソフトの正確な方法を使いましょう。

この記事の監修者に相談できます

監修者の田中磨生が代表を務める株式会社Abinで、ご相談をお受けしています。

相談無料ツギノテ編集部に相談する→

Excelの「分析ツール」を使う

Excelのデスクトップ版には、「分析ツール」というアドインが用意されています。読み込むと、「データ」タブの「データ分析」から、基本統計量、相関、回帰分析、t検定、ヒストグラム、移動平均などを、ダイアログで範囲を指定するだけで実行できます。

Windows版での読み込みの手順は、Microsoftのヘルプによると次のとおりです。

1. 「ファイル」→「オプション」→「アドイン」を開く
2. 「管理」で「Excel アドイン」を選び、「設定」を押す
3. 「分析ツール」にチェックを入れて「OK」を押す

たとえば「回帰分析」を使うと、係数・標準誤差・t値・p値・95％信頼区間・決定係数・残差がひとまとめに出力されます。第8回の「もう一歩深く」で紹介した係数の信頼区間も、ここで確かめられます。関数で1つずつ出すより速く、出力の見方も一覧で分かるので、回帰分析にはこちらがおすすめです。

あとでやり直せるシートを作る

最後に、表計算ソフトで分析するときにいちばん大切な習慣を紹介します。それは「あとで自分や他人がやり直せるように作る」ことです。

1. シートを役割で分ける：「元データ」「加工」「集計」「グラフ・結論」のようにシートを分け、元データは取り込んだまま触らない
2. 手で数字を打ち換えない：おかしな値を見つけたら、元データの取得からやり直すか、加工のシートの式で直す。集計結果のセルを直接書き換えると、来月同じ作業をしたときに再現できない
3. 定義をメモする：「新規客＝初回購入の顧客」「期間＝2026年4月1日〜30日、キャンセルを除く」など、指標の定義と期間をシートの先頭に書く
4. 数字は式で出す：別のシートから値をコピーして貼るのではなく、元から計算する式にしておく

この習慣があれば、上司から「先月と同じ分析を今月もやって」と頼まれたときに、元データを差し替えるだけで済みます。毎月同じ分析をくり返すようになったら、BIツールで自動化する段階です。この講座の姉妹編BIとダッシュボード設計（全6回）で詳しく扱うほか、ツールの比較はBIツールおすすめ比較で紹介しています。

この回のまとめ

• 分析の土台は「1行＝1件」の明細データ。見出しは1行、1列に1項目、数値は数値で、小計や色で意味を持たせない。
• 代表値・ばらつきはAVERAGE、MEDIAN、QUARTILE.INC、STDEV.S などの関数で出す。条件つきはAVERAGEIFS・COUNTIFS。
• ピボットテーブルで、行・列・値を選ぶだけでセグメント別の集計ができる。
• 相関・回帰はCORREL、SLOPE、INTERCEPT、RSQ、LINEST。引数の順番に注意。回帰分析はExcelの「分析ツール」も便利。
• シートは役割で分け、手で数字を打ち換えず、定義をメモし、式で計算する。

確認テスト

次回予告

次回はいよいよ最終回です。どれだけ正しい分析をしても、相手に伝わらなければ判断は変わりません。結論から話すレポートの型、数字の見せ方、そして「次の一手」の決め方を学び、この講座で学んだことを1枚のレポートにまとめる方法を紹介します。

参考資料

• Microsoft サポート「ピボットテーブルを作成してワークシート データを分析する」（確認日：2026年11月12日）https://support.microsoft.com/ja-JP/Excel/get-started/create-a-pivottable-to-analyze-worksheet-data
• Microsoft サポート「分析ツールを使用して複雑なデータ分析を実行する」（確認日：2026年11月12日）https://support.microsoft.com/ja-JP/excel/use-the-analysis-toolpak-to-perform-complex-data-analysis
• Google ドキュメント エディタ ヘルプ「Google スプレッドシートの関数リスト」（確認日：2026年11月12日）https://support.google.com/docs/table/25273?hl=ja