商品カテゴリ別の売上をExcelで集計する方法｜売上明細と商品マスタを結合

「商品カテゴリごとの売上を出してほしい」と頼まれたものの、手元の売上明細には商品コード・数量・単価しかない。カテゴリ名は別シートの商品マスタにある――。このような場合、必要なのは売上明細にカテゴリをひも付け、カテゴリ単位で売上金額を合計することです。

この記事では、Excelの標準機能で進める方法と、SQLを書ける人が JOIN・GROUP BY で集計する方法を、同じ架空データで説明します。SQL用のデータベースを新たに用意するかどうかも含め、作業に合う方法を選べるようにします。

なぜ売上明細だけでは「カテゴリ別売上」が出せないのか

売上明細には通常、「何を・いくつ・いくらで売ったか」が記録されています。一方、商品ジャンルやカテゴリは商品マスタに管理されている場合があります。

• 売上明細：商品コード、数量、販売単価
• 商品マスタ：商品コード、カテゴリ
• 欲しい結果：カテゴリごとの 数量 × 販売単価 の合計

つまり、商品コードを共通キーとして2つの表を結び付ける必要があります。最初から売上明細にカテゴリ列を手入力する方法もありますが、明細が増えるほど転記漏れや分類の揺れを招きます。

例：同じExcelブックの2シートを使う

ここでは1つの .xlsx ファイルに「売上明細」と「商品マスタ」の2シートがあるとします。両シートとも1行目が列名、2行目以降がデータです。

売上明細シート（架空データ）

商品マスタシート（架空データ）

求める結果は以下のとおりです。

※この例では「売上金額＝数量×単価」と定義し、返品・値引き・税額は扱いません。図は処理の概念図であり、アプリケーションの実画面ではありません。

集計前に確認すべき4つのこと

JOIN 自体は簡単でも、元データの状態次第で数字を間違えます。集計前に次を確認してください。

1. 商品コードの型と表記：片方だけ数値型になっていないか、先頭のゼロや余分な空白が消えていないか。
2. 商品マスタの商品コードの一意性：同じ商品コードがマスタに2行あると、結合で売上明細が重複し、金額が水増しされるおそれがあります。
3. マスタ未登録の商品コード：売上明細にあるのにマスタにないコードを、集計から黙って落とさないこと。
4. 売上の定義と期間：集計したいのは税込・税抜、値引き前・後、返品込み・除外のどれか。対象期間をどの日付列で絞るか。

とくに実務では「結合できたか」よりも「数字を落としたり、二重計上したりしていないか」を最後に確かめることが重要です。

方法1：ExcelのXLOOKUPとピボットテーブルで集計する

SQLを使わなくても、Excelの検索関数とピボットテーブルで実現できます。明細の列数や件数が管理しやすく、完成した表をExcel上で編集するチームに向いています。

上の例で、「売上明細」シートのA～C列に商品コード・数量・単価を置き、D列を「カテゴリ」、E列を「売上金額」とします。「商品マスタ」シートはA列が商品コード、B列がカテゴリです。

1. 「売上明細」のD2セルに次の式を入れ、最終行までコピーします。

   =XLOOKUP(A2,商品マスタ!$A$2:$A$4,商品マスタ!$B$2:$B$4,"未登録",0)
   

   見つからない商品コードは「未登録」と表示します。実データでは商品マスタの参照範囲を全件に広げてください。

2. E2セルに =B2*C2 を入力し、最終行までコピーします。

3. 「売上明細」の表を選び、挿入 → ピボットテーブルを作成します。

4. 「カテゴリ」を行、「売上金額」を値に配置し、集計方法が「合計」になっていることを確認します。

この例では文具15,000円、家電15,000円、雑貨4,000円になります。「未登録」が出たら、その金額をその他に混ぜずに商品マスタを確認します。

※XLOOKUPの利用可否はExcelのバージョンによります。利用できない環境ではVLOOKUPなどの関数、または次のPower Queryを検討してください。

方法2：Power Queryで結合・集計する

同じ種類の表を毎月取り込み、決められた手順で更新するなら、Excelに標準搭載されるPower Queryも有力です。

1. 2シートの各データ範囲をExcelの「テーブル」に変換します。
2. データ → テーブルまたは範囲からの操作で、それぞれをPower Queryへ取り込みます。
3. 売上明細を基準に、商品コード列を指定して商品マスタのクエリをマージします。未登録コードも検知できるよう、結合種類は左外部結合を選びます。
4. マージした商品マスタから「カテゴリ」を展開します。
5. カスタム列で「数量×単価」の売上金額を計算し、カテゴリ列をキーにグループ化して売上金額を合計します。
6. 結果をExcelへ読み込み、元の明細合計・未登録件数と照合します。

手順をクエリとして保持できるため、定型的な集計との相性がよい方法です。ただし、マスタの重複や結合先の欠落はPower Queryでも自動的には解決されません。

方法3：SQLでJOINし、カテゴリ別の売上をGROUP BYする

SQLを既に書ける人なら、列のひも付けと集計内容をSQLで明示した方が考えやすい場合があります。

今回の2シートをそれぞれ 売上明細・商品マスタ というテーブルとして扱える環境では、次のように記述できます。

SELECT
    COALESCE(m."カテゴリ", '未登録') AS "カテゴリ",
    SUM(d."数量" * d."単価") AS "売上金額"
FROM "売上明細" AS d
LEFT JOIN "商品マスタ" AS m
    ON d."商品コード" = m."商品コード"
GROUP BY COALESCE(m."カテゴリ", '未登録')
ORDER BY "売上金額" DESC, "カテゴリ";

このSQLでは、まず商品コードで明細とマスタを結び、次にカテゴリごとの金額を合計します。LEFT JOIN を使っているため、マスタ未登録の商品コードがあっても、その売上行を集計から取りこぼさず「未登録」として確認できます。ただし、マスタに商品コードの重複があると金額が増えてしまう点は変わりません。

この架空データなら、結果は次の3行です（表示順はSQLの並べ替え条件によります）。

マスタ未登録コードを別に一覧化してから集計したい場合は、次のSQLでも確認できます。

SELECT
    d."商品コード",
    COUNT(*) AS "明細行数"
FROM "売上明細" AS d
LEFT JOIN "商品マスタ" AS m
    ON d."商品コード" = m."商品コード"
WHERE m."商品コード" IS NULL
GROUP BY d."商品コード";

実際の月次分析では、さらに売上日で期間を絞ったり、商品カテゴリと取引先区分の2軸で集計したりできます。日付の条件は、取り込んだ列のデータ型を確認してから追加してください。

どの方法を選ぶべきか

大事なのは、JOIN ができるツールを選ぶことではなく、欲しい売上の定義に対して、短い手順で再現でき、数字を検算できる方法を選ぶことです。

SQL環境を新しく用意したくない場合

「SQLは書ける。でも、今回のExcel集計のためだけにデータベースを準備したくない」という人には、Small Work Labの表データSQLツールも選択肢です。

このWindows向けツールは、1つの .xlsx ブックの各シートを1つのSQLテーブルとして扱い、DuckDB SQLの対応範囲で SELECT・JOIN・GROUP BY などを実行できます。元ブックを直接上書きせず、結果を別の .xlsx ファイルとして保存できます。SQLを自動生成する製品ではないので、SQLを自分で書ける人向けです。

対応はWindows 11（64bit）の .xlsx です。別々のExcelブックを自動で横断結合する機能、CSVの直接読み込み、外部データベース接続、SQLの自動作成、定期自動実行は対象外です。

→ 表データSQLツールの機能と対応範囲を見る

SQLでExcelを扱う考え方全般は、既存の記事「ExcelをSQLで加工する方法」でも説明しています。

よくある質問

売上明細と商品マスタが別々のExcelファイルでも集計できますか？

Excelの標準機能やPower Queryでは、両ファイルを読み込んで結合する方法があります。ただし、上記の表データSQLツールの対象は同じ .xlsx ブックにある複数シートです。別ファイルのまま自動結合する機能はありません。

「商品別売上」と「カテゴリ別売上」は何が違いますか？

商品別売上はP001、P002など個々の商品コード単位の集計です。カテゴリ別売上は、商品マスタに記録した文具・家電などの分類で商品をまとめた集計です。今回のように明細にカテゴリがない場合、後者はマスタとの照合が必要です。

集計結果の合計が元の売上明細と一致しないのはなぜですか？

よくある原因は、商品マスタのキー重複、未登録コードの除外、対象期間のずれ、返品・値引き・税の扱いの違いです。まず明細の売上合計 → 結合後の行数 → カテゴリ別の合計の順に確認すると、どこで差が生じたかを切り分けやすくなります。

参考資料（操作方法）

• Microsoftサポート：XLOOKUP関数
• Microsoftサポート：クエリをマージする（Power Query）
• Microsoftサポート：データの行をグループ化する（Power Query）