Excelで顧客別売上分析を自動化する関数構成

まず押さえる：顧客別売上分析で「自動化」できる範囲とゴール設定
土台づくり：売上データを“関数が効く形”に整える（テーブル化・項目設計）
集計の核：顧客別売上を一発で出す定番関数構成（SUMIFS／COUNTIFS）
分析力を上げる：ランキング・前年差・リピート判定まで広げる（XLOOKUP／UNIQUE／FILTER）
運用で勝つ：更新に強い自動化テンプレ化とミス防止（入力規則・エラー処理・保守）

まず押さえる：顧客別売上分析で「自動化」できる範囲とゴール設定

Excelで顧客別売上分析を「自動化する」と聞くと、全部がボタン一発で回りそうですが、最初に整理すべきはどこまでを自動化の対象にするかです。結論から言うと、自動化できるのは主に「集計・抽出・並べ替え・比較」の領域で、逆に人が決めるべきは「分析の切り口（なんのために見るか）」です。ここが曖昧だと、関数を組んでも使われない表が出来上がります。

顧客別売上分析で、関数だけで自動化しやすい範囲は次のとおりです。

顧客ごとの売上合計（月次・期間指定も含む）
取引回数・購入回数、平均単価などの基礎指標
上位顧客ランキングや特定条件の顧客抽出（例：今月売上が10万円以上）
前年差・前月差などの比較（マスタ参照含む）
更新への追従（データを追加したら集計表も自動で伸びる）

一方で、完全自動化しにくい（やらないほうがいいことが多い）領域もあります。

「どの顧客を重点」と判断する基準作り（社内ルールや事業状況で変わる）
例外処理の連続（データが毎回ぐちゃぐちゃ、顧客名の表記揺れが多すぎる等）
ストーリーのある提案資料化（最終的な解釈・示唆出し）

では、20代の会社員が業務で使える「ゴール設定」はどう置くべきか。おすすめは、“毎月やる作業を、データ貼り付けだけにする”ことです。具体的には、次のゴールを先に宣言しておくと設計がブレません。

ゴール例（実務向け）

売上データを追加したら、顧客別売上表が自動更新される
期間（今月／四半期／任意開始日〜終了日）を変えても、集計が崩れない
上位20社、売上ゼロになった顧客、伸びた顧客が自動で見える
手作業の集計（ピボット作り直し、フィルター→コピー等）をゼロにする

このブログでは、マクロやPower Queryに寄せず、まずは関数構成を軸に「更新に強い」型を作ります。そのため、次章以降で重要になる前提がひとつあります。関数が気持ちよく効くのは、データが同じ列に同じ意味で並び、追加されても形が崩れないときだけです。つまり自動化の成否は、関数のテクニックよりデータの整え方（設計）で決まります。

次章では、売上データを「関数が効く形」に変えるための土台作りとして、テーブル化と項目設計（最低限そろえる列）を固めていきます。

土台づくり：売上データを“関数が効く形”に整える（テーブル化・項目設計）

顧客別売上を関数で自動化するなら、最初にやるべきは「元データを整形して、増えても崩れない器に入れる」ことです。ここができていないと、SUMIFSを組んでも「範囲が足りない」「列がズレた」「月が変わると手直し」になりがち。狙いは1章で決めたとおり、毎月はデータを追加するだけの状態にすることです。

1) 売上データは「Excelテーブル」にする

まず元データ範囲を選択して、Ctrl + T（テーブル化）。ここで得られるメリットは実務で強力です。

データを下に追加しても、集計範囲が自動で伸びる
列名で参照でき、数式が読みやすく壊れにくい（構造化参照）
フィルターや並べ替えが標準装備で、確認作業が速い

テーブル名は後で関数に何度も出てくるので、例として「tblSales」のように意味が分かる名前にしておくと保守がラクです（テーブルデザイン→テーブル名）。

2) 「1行＝1取引」の粒度を守る

関数が効く形の基本は、1行に1つの事実です。よくあるNGは「1行に複数の月が横持ち」「小計行が混ざっている」「顧客別の合計が最初から書かれている」など。集計は後工程で作るので、元データは明細だけに寄せます。

3) 最低限そろえる列（項目設計）

顧客別売上分析でまず必要になる列は、次のセットです。迷ったらこの形に寄せると、3章以降のSUMIFS/COUNTIFSが素直に書けます。

日付（取引日／売上日）
顧客ID（できれば数値や固定コード）
顧客名（表示用。集計キーはID推奨）
売上金額（税抜/税込の定義を固定）
商品/カテゴリ（任意：後の切り口拡張用）
担当者/拠点（任意：社内の分析用）

ポイントは、集計キーを顧客名ではなく顧客IDに寄せること。名前は「株式会社A」「(株)A」など表記揺れが起きやすく、後で一致条件が崩れます。顧客IDが無い場合は、まずID列を作る価値があります（既存の顧客台帳があるならそこに合わせる）。

4) データ品質を落とす“地雷”を先に潰す

自動化が途中で止まる原因は、だいたいこの3つです。

日付が文字列（見た目は日付でも、関数条件に引っかからない）
金額が文字（「\」「,」付きで数値になっていない）
空白や全角スペース混入（顧客ID/名の一致がズレる）

テーブル化したら、日付列は日付形式、金額列は数値形式に統一。顧客ID/名は余計な空白が入らない運用（コピー元の癖）も意識しておくと、後の集計が安定します。

ここまでの土台ができると、次章の「顧客別売上を一発で出す」関数構成は、範囲指定に悩まない状態で組めます。次はSUMIFS/COUNTIFSで、顧客別の売上・回数を“更新に強く”自動集計していきます。

集計の核：顧客別売上を一発で出す定番関数構成（SUMIFS／COUNTIFS）

土台（tblSales）ができたら、いよいよ自動化の本丸です。ここで作るのは、「顧客ごとの売上合計」と「取引回数」を、条件つきで一発集計できる形。ピボットの作り直しや範囲ズレから解放される“定番の型”なので、まずはこの構成をコピペできるレベルまで固めましょう。

1) 集計表側のレイアウト（先に「入力欄」を分ける）

おすすめは、集計表の上部に期間指定を置き、下に顧客別の一覧を作る形です。

開始日：セルB2
終了日：セルC2
顧客ID：列A（A5から下に並ぶ想定）
売上合計：列B
取引回数：列C

ここでのコツは、期間条件を別セルにしておくこと。毎月の運用が「B2とC2を変えるだけ」になります。

2) 売上合計はSUMIFSで“顧客×期間”をまとめて拾う

顧客ID（A5）をキーに、tblSalesの売上金額を合計します。期間も同時に絞ると、月次・四半期・任意期間にそのまま流用できます。

=SUMIFS(tblSales[売上金額],
       tblSales[顧客ID], $A5,
       tblSales[日付], ">="&$B$2,
       tblSales[日付], "<="&$C$2)

ポイントは2つです。

条件の日付は">="&$B$2のように、記号とセルを連結する（SUMIFSの作法）
参照はテーブルの構造化参照にする（行が増えても範囲が自動追従）

「今月分だけ出したい」なら、B2に月初、C2に月末を入れるだけでOK。これが“貼り付け→自動更新”の中心になります。

3) 取引回数（購入回数）はCOUNTIFSで同じ条件を数える

売上だけだと「大口1回」なのか「小口を何回も」なのかが見えません。そこで取引回数も同じ条件で数えます。件数のカウントはCOUNTIFSが定番です。

=COUNTIFS(tblSales[顧客ID], $A5,
          tblSales[日付], ">="&$B$2,
          tblSales[日付], "<="&$C$2)

SUMIFSとCOUNTIFSは条件の書き方を揃えるのがコツ。後で「売上は合ってるのに回数がズレる」事故を防げます。

4) “0円/0回”を見やすくする（表示のひと工夫）

顧客別集計は、未取引の顧客が混ざると見づらくなります。まずは見た目だけでも整えておくと、現場で使われやすいです。

売上合計は0なら空欄にする：
=IF(B5=0,"",B5) のように表示列を分ける（元の集計式は残す）
回数も同様に、0回は空欄表示にする

「0も重要だから消したくない」場合は空欄にせず、条件付き書式で薄くするのもアリです。ここは運用者の好みでOK。

5) よくある詰まりどころ（最短で潰すチェック）

日付条件が効かない：tblSales[日付]が文字列の可能性（2章の地雷）。まずセルの表示ではなく、実データが日付型か確認
顧客で拾えない：顧客名を条件にして表記揺れが発生。キーは顧客IDに寄せる
金額が合わない：売上金額列に文字の「,」や「￥」が混ざって数値化できていないケース

この3章のゴールは、SUMIFSで売上、COUNTIFSで回数を、期間つきで同じロジックで回すこと。ここまで作れれば、次章のUNIQUE/FILTERで顧客一覧を自動生成したり、XLOOKUPで前年差を引っ張ったり、ランキングに広げる準備が整います。

分析力を上げる：ランキング・前年差・リピート判定まで広げる（XLOOKUP／UNIQUE／FILTER）

3章で「顧客×期間」の売上・回数が自動集計できたら、次は“見たい顧客だけを自動で浮かび上がらせる”段階です。ここで効くのが、UNIQUEで顧客一覧を自動生成し、FILTERで条件抽出、XLOOKUPで別期間の数値を参照して比較する流れ。手作業の並べ替えやコピペランキングから卒業できます。

1) 顧客一覧をUNIQUEで自動生成（まず「行を作る」を自動化）

集計表の顧客ID（例：A5）を手入力で並べるのは、運用が長くなるほど事故ります。そこで、元データ（tblSales）から顧客IDを自動で作ります。

=SORT(UNIQUE(tblSales[顧客ID]))

これで新しい顧客が取引に登場しても、一覧が自動で増えます。顧客名を表示したい場合は、顧客マスタ（例：tblCustomer）を用意してXLOOKUPで引っ張るのが安全です（表記揺れ対策）。

=XLOOKUP([@顧客ID], tblCustomer[顧客ID], tblCustomer[顧客名], "")

2) ランキングは「抽出→並べ替え」をFILTER＋SORTで固定化

「上位20社だけ見たい」を毎回フィルターして並べ替えるのはムダなので、ランキング表を別枠で自動生成します。前提として、集計表に「売上合計」列（例：B5#に連動している想定）があるとします。

売上が0より大きい顧客だけを抽出して、売上降順に並び替えます。

=SORT(
  FILTER(A5:C200, B5:B200>0),
  2, -1
)

ポイントは「0円を除外」→「売上列で降順」の順番。これで“動くランキング”が完成します。上位N社に絞るなら、上からN行だけ取り出す形にします（環境によってはTAKEが使えます）。

3) 前年差（YoY）は「別期間の売上」をXLOOKUPで参照して引き算

前年差は、考え方としてはシンプルで「今年の売上」−「去年の同期間売上」。コツは、「去年の同期間売上」を別列でSUMIFSし、顧客IDで突合することです。

例えば、集計表に「前年売上」の列を作り、期間だけ1年ずらしたSUMIFSを書きます。

=SUMIFS(tblSales[売上金額],
       tblSales[顧客ID], $A5,
       tblSales[日付], ">="&EDATE($B$2,-12),
       tblSales[日付], "<="&EDATE($C$2,-12))

そして前年差は引き算。

=B5 - D5

もし「前年売上」を別シートに作る場合は、XLOOKUPで「顧客ID→前年売上」を参照してもOKです。

=B5 - XLOOKUP($A5, 前年表[顧客ID], 前年表[売上合計], 0)

4) リピート判定は「回数」か「購入間隔」で割り切る

20代の現場実務だと、リピート判定はまずシンプルに運用できる定義が勝ちです。おすすめは次のどちらか。

期間内の取引回数が2回以上ならリピート（COUNTIFSベース）
直近購入日が一定日数以内ならアクティブ（MAX/FILTERベース）

前者（回数ベース）なら、3章の取引回数（C列）がそのまま使えます。

=IF(C5>=2,"リピート","単発")

後者（直近購入日）をやるなら、顧客の取引日だけをFILTERで抜いて最大値を取ります。

=MAX(FILTER(tblSales[日付], tblSales[顧客ID]=$A5))

この「直近日」と今日の日付（TODAY）や終了日（C2）を比べれば、休眠判定も作れます。

=IF($C$2-直近購入日>=90,"休眠","アクティブ")

ここまで作ると、単なる集計表ではなく、「上位顧客」「伸びた顧客」「離れそうな顧客」が自動で見える分析表になります。次章では、この仕組みを“毎月貼り付けるだけで壊れない”テンプレにして、入力ミスやエラーで止まらない運用に落とし込みます。

運用で勝つ：更新に強い自動化テンプレ化とミス防止（入力規則・エラー処理・保守）

ここまでの関数構成ができても、実務で詰まるのはたいてい「更新した瞬間にどこかが壊れる」問題です。運用で勝つコツは、関数を増やすより先に、入力ルールとエラーの逃げ道を用意して「テンプレとして回せる状態」にすること。ゴールは1章で置いたとおり、毎月やることをデータ追加＋期間変更だけに寄せる設計です。

1) 入力は「触っていいセル」だけにする（事故の9割を先に潰す）

まず、集計シートで人が触る場所を最小化します。基本は期間（開始日B2／終了日C2）と、必要なら「担当者」「カテゴリ」などの追加条件セルだけ。その他のセルは編集しない前提にします。

入力セルは背景色を変えて入力欄だと一目で分かるようにする
数式セルはシート保護（必要最低限）で上書きを防ぐ

2) データの入力規則で「間違った値を入れられない」状態にする

運用が回りだすと、地味に効いてくるのが入力規則です。特に日付と顧客IDはここで守ると、SUMIFS/COUNTIFSのズレが激減します。

開始日・終了日は「日付」以外を弾く（データの入力規則）
終了日は開始日以上に制限（例：=C2>=B2 を条件にする）
顧客IDを手入力する運用が残るなら、顧客マスタ（tblCustomer）からリスト選択にする

「入力者が頑張る」ではなく、入力できない仕組みに寄せるのがテンプレ化の第一歩です。

3) エラー処理は“隠す”より“止めない”を優先（IFERRORの使いどころ）

自動化テンプレで重要なのは、#N/Aや#CALC!が出ても全体が崩れず回り続けること。例えば、XLOOKUPで顧客名を引く箇所は、見つからない時に空欄にしておくと表が壊れません。

=IFERROR(
  XLOOKUP([@顧客ID], tblCustomer[顧客ID], tblCustomer[顧客名]),
  ""
)

FILTERも同様で、該当なしのときにエラーになりやすいので、空を返す設計にします。

=IFERROR(
  FILTER(集計表範囲, 売上範囲>0),
  ""
)

ただし、売上合計（SUMIFS）まで何でもIFERRORで包むと、本当の異常（列名変更、参照切れ）まで見えなくなるので注意。エラー処理は「起こり得る想定内（該当なし）」に絞るのが保守的に強いです。

4) 壊れにくい保守ルール：列名を固定し、参照を“テーブル名”で統一する

更新で壊れる原因の多くは、列の追加・名称変更・コピペでの列ズレです。対策はシンプルで、テーブル（tblSales）を正にすること。

元データは必ず tblSales に追加（別範囲に貼らない）
列名（例：日付/顧客ID/売上金額）は途中で変えない運用ルールにする
数式はA:Aのような列参照ではなく、tblSales[売上金額]のように構造化参照を徹底

5) テンプレの完成形：毎月の作業手順を“3ステップ”に固定する

最後に、運用手順を文章で固定すると「作った人しか回せない」問題を避けられます。おすすめの定型はこの3つです。

新しい明細を tblSales の最下行に追加（貼り付け）
B2（開始日）とC2（終了日）を当月に更新
ランキング・前年差・リピート判定が自動更新されているか、チェック項目（件数/合計）だけ確認

ここまで整えると、関数の仕組みは「作る」から「回す」にフェーズが変わります。つまり、分析のためのExcelが毎月の業務を軽くする道具として機能し始めます。