【Googleスプレッドシート】顧客別の購入金額集計!CRM風の分析テンプレート

顧客ごとの購入金額を集計したい場面は多いのではないでしょうか。例えば、売上データからどの顧客が最も購入しているのかを把握することで、営業戦略に活かせます。本記事では、Googleスプレッドシートを使って顧客別の購入金額を集計するCRM風の分析テンプレートの作成手順を解説します。このテンプレートを使えば、データを更新するだけで自動的に集計結果が反映されるようになります。

顧客別購入金額集計テンプレートの概要

このテンプレートは、元データとなるトランザクションシートと、集計結果を表示する分析シートの2枚で構成します。元データには「顧客名」「購入日」「金額」の3列を最低限用意します。分析シートでは、顧客ごとの合計金額を自動計算し、さらに並び替えや条件付き書式で視覚化します。このテンプレートの最大のメリットは、新しいデータを追加するたびに集計が自動更新される点です。手作業での集計作業から解放され、常に最新の売上状況を把握できます。

テンプレートの作成手順

それでは、具体的な手順を説明します。以下の操作を順番に行ってください。

1. 元データシートの準備
   新しいスプレッドシートを作成し、1枚目のシート名を「トランザクション」に変更します。A列に「顧客名」、B列に「購入日」、C列に「金額」という見出しを入力し、その下に実際のデータを入力します。日付は日付形式、金額は数値形式で統一してください。
2. 分析シートの作成
   2枚目のシートを追加し、シート名を「分析」に変更します。このシートに集計結果を表示します。A1セルに「顧客名」、B1セルに「合計金額」という見出しを入力します。
3. 顧客一覧の自動抽出
   A2セルに次の数式を入力します。=UNIQUE(トランザクション!A2:A) この数式は、トランザクションシートのA列から重複しない顧客名を自動でリストアップします。データが増えても自動で範囲が拡大されます。
4. SUMIF関数で金額を合計
   B2セルに次の数式を入力します。=SUMIF(トランザクション!A:A, A2, トランザクション!C:C) この数式は、トランザクションシートのA列がA2の顧客名と一致する行のC列の金額を合計します。この数式を、A列に顧客名がある行までオートフィルでコピーします。
5. 並び替えとランキング表示
   分析シートのA1セルを選択し、メニューから「データ」→「フィルタを作成」をクリックします。次にB列のフィルタアイコンをクリックし、「降順」を選択して合計金額が多い順に並び替えます。さらに、C列に「ランク」という見出しを追加し、C2セルに=RANK(B2, B:B, 0)と入力して順位を表示します。
6. 条件付き書式で上位を強調
   分析シートのA1からC列の最終行までを選択し、メニューから「書式」→「条件付き書式」をクリックします。ルールとして「カスタム数式」を選び、=$C2<=3と入力します。書式スタイルで背景色を黄色などに設定します。これで上位3位の顧客が強調表示されます。

テンプレート活用の注意点

元データの範囲が変わったときの対処法

SUMIF関数やUNIQUE関数は、列全体を参照しているため、データを追加しても自動的に対応します。ただし、意図的に範囲を限定している場合は、数式の参照範囲を更新する必要があります。例えば、SUMIF(トランザクション!A1:A100, A2, トランザクション!C1:C100)のように指定している場合は、新しいデータを追加したら範囲を拡大してください。

空白セルがある場合のエラー回避

元データの顧客名や金額に空白セルが含まれていると、集計結果に0やエラーが表示されることがあります。これを防ぐには、SUMIFの代わりにQUERY関数を使う方法があります。例えば、=QUERY(トランザクション!A:C, "select A, sum(C) where A is not null group by A label sum(C) '' ", 1)と入力すれば、空白を除外して集計できます。また、データバリデーションを設定して顧客名の入力をリストから選択できるようにすると、空白や誤入力を防げます。

新しい顧客が追加されたときの自動更新

UNIQUE関数とSUMIF関数の組み合わせでは、新しい顧客が追加されると自動的に顧客一覧に追加されます。しかし、オートフィルでSUMIFをコピーした範囲が固定されている場合は、新しい行に数式が自動でコピーされません。その場合は、最初からARRAYFORMULAを使って一つの数式で解決する方法があります。分析シートのA2セルに=UNIQUE(トランザクション!A2:A)、B2セルに=ARRAYFORMULA(IF(A2:A="", "", SUMIF(トランザクション!A:A, A2:A, トランザクション!C:C)))と入力すれば、新しい顧客が追加されても自動で計算されます。

SUMIFとQUERYの比較

顧客別集計にはSUMIFとQUERYのどちらも使えます。それぞれの特徴を比較表にまとめました。

このように、QUERY関数はより高度な集計や並び替えを一つの数式で完結できるため、複雑な要件に向いています。一方、SUMIFはシンプルで学習コストが低いため、基本的な集計には十分です。

まとめ

本記事では、Googleスプレッドシートを使った顧客別購入金額集計テンプレートの作成方法を解説しました。SUMIF関数とUNIQUE関数の組み合わせで、元データを追加するだけで自動更新される集計表が作れます。さらに、QUERY関数を使えばより柔軟な分析が可能です。次は、このテンプレートにグラフを追加したり、ピボットテーブルで月別の集計を行ったりして、分析の幅を広げてみてください。