Excel管理表でピボットテーブルが正しく作れない原因。データ形式の表に整える手順

導入

売上管理表でピボットを作ろうとしたら「フィールドが認識できません」とエラーが出る。範囲を選び直したら、見出し行が2段になっている部分が原因でフィールド名が空欄になり、結局ピボットが組み立てられなかった――こんな場面はありませんか。

これはピボットテーブル機能の不具合ではなく、表が「見やすさ優先」のレイアウトになっていてデータ形式になっていないことが原因です。本記事では、1行1データ・1列1項目・見出し1行の3条件に整えて、ピボットが安定して動く表に整える手順をまとめます。

ピボットは「整ったデータ」を前提にしています。整える側に時間をかければ、集計はピボットで5分で出来るようになります。

担当者の作業が遅いのではなく、ピボットが要求する「データ形式」と表のレイアウトが噛み合っていないことが原因です。見直しは、見出し・結合・1行1データ・1列1項目を順に整えるところから始めます。

直す前 — ピボットが認識できない表:

売上情報			顧客情報
月	金額	区分	顧客名／連絡先	担当者1／担当者2
2024-04	120,000	新規	山田商事／03-1234	田中／鈴木

直した後 — ピボット対応のデータ形式:

計上月	売上金額	売上区分	顧客名	顧客連絡先	担当者ID	担当者役割
2024-04	120,000	新規	山田商事	03-1234	U-001	主担当
2024-04	120,000	新規	山田商事	03-1234	U-002	副担当

見出し1行・1列1項目・1行1データ（担当が複数なら行を分ける）の3条件を満たし、ピボットで「行＝月、列＝担当者、値＝合計金額」がすぐ組めます。

複数段の見出しを1行にまとめます。

操作: A1〜列見出しを直接書き換える。「売上情報＞月」「売上情報＞金額」のような階層は、列名の中に含めて「計上月」「売上金額」のように展開する。

記入例:

ピボットは結合セルを正しく認識できません。

操作: 該当範囲を選択し、「ホーム」→「セルを結合して中央揃え」をクリックして解除する。空欄になったセルには元の値を「ジャンプ→空白セル」→「直上参照→Ctrl+Enter」で一括で埋める。

記入例:

解除前	解除後
A3〜A5 結合「2024-04」	A3「2024-04」／A4「2024-04」／A5「2024-04」

✗悪い例: 結合解除のまま空欄を放置 → ピボットで「(blank)」項目が出る ◎良い例: 空欄を上のセルの値で埋め、すべての行に値を持たせる

1行に複数件入っているセルを、別々の行に展開します。

操作: 「担当者1／担当者2」のように区切り文字で入っているセルを、Power Query→「列の分割」→「列のピボット解除」で行展開する。少量なら手作業で行を増やしてもよい。

記入例:

操作前	操作後
240401／山田商事／田中・鈴木	240401-1／山田商事／田中、240401-2／山田商事／鈴木の2行

1セルに複数の情報が入っている列を、別々の列に分けます。

操作: 「顧客名／連絡先」のような複合列を、「データ」→「区切り位置」で分割する。区切り文字は「／」「、」など事前に決める。

記入例:

操作前	操作後
山田商事／03-1234	顧客名「山田商事」／顧客連絡先「03-1234」

ピボットの参照範囲がデータ追加に追従するように、表をテーブル化します。

操作: データ範囲を選択→Ctrl+T（または「挿入」→「テーブル」）。「先頭行をテーブルの見出しとして使用する」にチェック。テーブル名を「売上テーブル」のように分かりやすく変更する。ピボットを作成する時、範囲指定で「売上テーブル」と入力すれば、新規行追加も自動反映される。

記入例:

ピボットテーブルが正しく作れない原因は、表が見やすさ優先のレイアウトでデータ形式になっていないことです。見出し1行・1列1項目・1行1データ・結合解除・テーブル化の5点を整えれば、ピボットは安定して動きます。

次にやることは、対象ファイルで「見出し行が何行になっているか」を数えることです。2行以上なら最初に1行化するのが最大の効果です。あわせて、見出し1行化の詳細は見出しを1行に整える手順、グラフ作成前の整理はグラフ用データに整える手順も参考になります。