【Excel】集計しやすい表の作り方 | データの正規化と縦持ちへの整理

【Excel】集計しやすい表の作り方 | データの正規化と縦持ちへの整理
🛡️ 超解決

毎月列を増やして作った売上表は、目で比較するには便利です。しかし、店舗別の年間集計や別年度との比較を始めると、範囲を直したり、複数の表を貼り合わせたりする手間が増えます。集計を繰り返すなら、入力・保存用のデータと、見せるための帳票を分けて設計すると扱いやすくなります。

Excelで「データの正規化」と呼ばれる作業には、表の整形や縦持ちへの変換が含まれることがあります。ここでは言葉の違いを押さえたうえで、ピボットテーブルやPower Queryで集計しやすい元表に整える手順を紹介します。

ADVERTISEMENT

1. 正規化と「集計しやすい表」は同じ意味ではない

データベースの正規化は、同じ情報の重複や更新時の不整合を減らすため、データを関連する表へ整理する考え方です。例えば顧客名・住所を取引のたびに複写する代わりに、顧客マスターと取引表を顧客IDで結ぶ設計が該当します。

一方、Excelで月別の列を「月」と「売上」の2列に組み替える処理は、横持ちから縦持ちへの整形です。それだけでデータベースの正規化が完成するわけではありません。目的は、集計に必要な項目を同じ列へそろえることだと理解してください。

横持ちの帳票をすべて否定する必要もありません。1~12月を並べた表は月の比較に向いています。元データは追加しやすい形で保管し、ピボットテーブルなどで見やすい横持ち表を作ると、集計のしやすさと読みやすさを両立できます。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. 最初に「1行が何を表すか」を決める

表を整える前に、1行の単位を文章にします。「1件の注文」「1店舗の1か月の売上」「1注文の1商品明細」では必要な列も重複の意味も違います。ここを決めずに重複行を削除すると、同じ商品を2回注文した正当な取引まで消すおそれがあります。

月次の店舗別売上なら、店舗ID・対象月・売上を基本項目にします。商品ごとの内訳も扱うなら商品IDが必要です。注文の明細なら、注文番号だけでは複数行になるため、明細番号なども含めて1行を識別します。行番号は並べ替えで位置が変わるので、業務上の識別子の代わりにはなりません。

項目 決めておきたいこと
店舗ID 店舗名変更後も同じ店舗として集計するか
対象月 年月まで持たせ、異なる年の1月を混ぜない
売上 税込・税抜、円・千円などの単位を統一する
状態 未報告・確定・取消などを金額と別に記録する

見出しは1行にし、同じ列名の重複や空欄をなくします。表の途中に空白行、繰り返しの見出し、小計行を挟まず、小計は別の集計表で作る方が二重計上を防げます。

3. セル結合・空白・データ型を整理する

店舗名のセルを縦に結合した表では、結合を解除しても全行に店舗名が入るわけではありません。通常、値は左上のセルに残り、下のセルは空になります。元表のまとまりを確認して、該当する行に店舗名を補います。空白を機械的に直前の値で埋めると、区切り行や新しい分類まで誤って埋めるため、先に表の範囲を整理しましょう。

空白と0も分けます。「未報告で金額が不明」と「報告済みで売上0円」は異なります。未報告を0に置き換えると、平均値や達成率の意味が変わります。状態列に未報告と記録するなど、集計時に区別できる形にします。

また、売上列の「1,000円」は単なる文字列として保存されている可能性があります。計算用の値は数値にし、円記号は表示形式で付ける方法が適しています。顧客コード「00123」は金額ではないため、先頭の0を残す文字列として扱います。Power Queryで型を変更する際も、すべて数値にそろえず、識別子・日付・金額の用途に応じて決めてください。

4. 月が列に並んだ表をPower Queryで縦持ちにする

元表が「店舗、1月、2月」の3列で、A店が100・120、B店が80・90だとします。縦持ちにすると「店舗、月、売上」の3列に、A店1月100、A店2月120、B店1月80、B店2月90の4行を置けます。元の4つの売上を並べ直す処理であり、合計390は変わりません。

  1. 元ファイルのコピーを残し、元表の見出しと値を確認します。
  2. 表内のセルを選び、「データ」→「テーブルまたは範囲から」でPower Queryエディターを開きます。範囲をテーブルにする確認では見出しの有無を確かめます。
  3. 店舗列を選び、列のピボット解除のメニューから「その他の列のピボット解除」を選びます。
  4. 作成された「属性」列を「月」、「値」列を「売上」に変更します。店舗は文字列、売上は用途に合う数値型にします。
  5. 「閉じて読み込む」の読み込み先で新しいワークシートを選び、元表とは別に結果を置きます。

年が複数あるデータなら、月だけでは識別できません。元表に年の列を持たせるか、「2026年1月」のような年月を扱い、最終的な集計で別年度を混ぜないようにします。

A店の1月100と2月120、B店の1月80と2月90を、店舗・月・売上の4行に変換し合計390を保つ例
月を列名から項目の値へ移すと、同じ3列で月別・店舗別に集計できます。

5. 「その他の列」を選ぶ前に固定する項目を点検する

店舗だけを残して「その他の列のピボット解除」を選ぶと、店舗以外の列がすべて変換対象です。元表に担当者や備考の列があるなら、それらまで売上のように縦へ並んでしまいます。残す識別列を複数選んでから操作するか、1月・2月など変換したい列を明示的に選んで「列のピボット解除」を使います。

「その他の列」の方式は、後から月の列を増やす運用で便利ですが、新しい備考列なども対象に入り得ます。「月を追加する人」と「集計を更新する人」が別の場合は、追加してよい列を決めておきましょう。列名変更やデータ型の変更は、保存済みのクエリ手順に影響することがあります。

縦持ちの結果に手で売上を書き足す運用も避けます。次の更新でクエリの結果に置き換わるため、修正は原則として元表で行い、再度読み込みます。補足情報が必要なら別の入力表に持たせ、安定したIDで結び付ける設計にします。

6. 件数と合計を確かめてから集計に使う

変換結果が表示されたら、まず数行を元表と照合します。A店の1月100が別店舗や別月へずれていないか、売上が文字列のままになっていないかを見ます。例のように2店舗×2か月すべてに値がある表なら、出力は4行、売上の合計は390です。欠損を含む実データでは、同じ計算で件数が一致するとは限らないので、元の空欄がどう扱われたかも確認します。

次に縦持ち表からピボットテーブルを作り、行に店舗、列に月、値に売上を配置します。値が「合計」ではなく「個数」になった場合は、売上列に文字列や型の混在がないかを調べます。集計方法だけ合計へ変えて終わらず、変換元の値を見直してください。

店舗ごとの小計と全体の総計を、元の帳票の正しい集計と照合します。既存の合計行まで元データに入れていないかも確認します。全体の合計が一致しても店舗の割り当てが誤っている場合があるため、合計だけの検算では不十分です。

7. 元表を残し、更新できる仕組みにする

Power Queryの変換は、元表を編集する処理と、読み込んだ結果を更新する処理に分かれます。元表へ新しい月や取引を追加したらクエリを更新し、その後にピボットテーブル側も最新の結果を集計しているか確かめます。更新の完了前に古い集計を提出しないよう、対象年月と件数を確認する習慣を付けましょう。

間違えた場合は、コピーを残した元表からやり直せます。クエリの「適用したステップ」も処理を見直す手掛かりになりますが、途中のステップ削除は後続の処理に影響します。編集前にブックを保存し、修正後に同じ検算を行ってください。

見やすさだけで入力表を作るより、1行の単位、列の型、欠損の意味をそろえることが先です。集計用の元表が安定すれば、提出用の見た目は必要に応じて作り替えられます。

公式資料: 列のピボット解除 / ピボットテーブルの作成 / データベース正規化の基礎

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】