ピボットテーブルの元データ(データソース)にしたい表で守るべき5つのルール [ピボットテーブル基礎]
AさんAさん

ピボットテーブルを挿入する時にエラーが出たり、挿入できてもピボットテーブル上でうまく集計できないケースがあります・・・。

この場合どうしたら良いですか?

森田森田

それはピボットテーブルの元データの表に原因がありますね。

実は、ピボットテーブルの元データにしたい表には守るべきルールがあります。

今回はそのルールの詳細を解説していきますね。

解説動画:【ピボットテーブル#2】元データが起因でつまずく「あるある」4ケース - 頻出のつまずき理由とその対策を把握する

この記事の内容は下記の動画でも解説しています。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • Excelでデータの集計・分析作業を行うことが多い方
  • ピボットテーブルを実務で使う機会がある方
  • ピボットテーブル挿入時のエラーや挿入後の集計がうまくできなくて困ったことがある方

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

元データが起因でピボットテーブルにつまずく「あるある」4ケース

ピボットテーブルを使う上で、元データ(データソース)にしたい表が起因で集計につまずくケースは4つあります。

【ケース1】ピボットテーブル挿入時にエラーが出る

まず1つ目は、ピボットテーブル挿入時に次のようなエラーが出るケースですね。

そのピボットテーブルのフィールド名は正しくありません。ピボットテーブルレポートを作成するには、ラベルの付いた列でリストとして編成されたデータを使用する必要があります。ピボットテーブルのフィールド名を変更する場合は、フィールドの新しい名前を入力する必要があります。

このエラーの原因は、元データ(データソース)の見出し部分にあります。

  • 見出しに空白セルがある
  • 見出しに結合セルがある

こうした場合に上記エラーが出てしまうため、事前に見出しを必ず入力するようにしましょう。

見出し名は重複しても特段エラーは出ませんが、重複したものは連番が振られます。
(2回目に登場したものは「見出し2」など)
集計時にフィールドの選択誤りがないように分かりやすい見出し名を付けましょう。

【ケース2】データソースの範囲が元データの表全体にならない

続いて2つ目は、元データの表全体がピボットテーブルのデータソースにならないケースです。

具体的には、次のようなイメージですね。

これは、元データの表の中に空白のレコード(行)があることが原因です。

よって、事前に元データの表の空白レコードを削除するか、必要なデータを入力しておきましょう。

【ケース3】ピボットテーブルで集計できるデータが足りない

3つ目は、ピボットテーブル(レポート)で集計できるデータが足りないというケースです。

具体的には、次のようなイメージですね。

これは、元データの表のレコードに結合されたセルがあると発生します。

結合セルの値は、結合されたセルの中で一番左上のセルのものとなり、それ以外のセルは空白扱いになるためです。

よって、事前に元データの表の結合セルを解除し、1セルに1データを入れておきましょう。

【ケース4】ピボットテーブルで集計時に余計なデータが表示される

最後の4つ目は、ピボットテーブル(レポート)で集計時に余計なデータが表示されるというケースです。

具体的には、次のようなイメージですね。

これは、1列(フィールド)に複数種類のデータが混在している時に発生します。

よって、事前に1列あたり1種類のデータの表にしておきましょう。

ピボットテーブルの元データにしたい表で守るべき5つのルール

以上を踏まえ、ピボットテーブルの元データにしたい表は以下のルールを守っておけば、上記のつまずきを避けることができます。

  1. 表の見出しを1行にすること
  2. 表の見出し名をすべて入力しておくこと
  3. 表の中に結合セルがないこと
  4. 表の中に空白レコードをつくらないこと
  5. 1列同一種類のデータにすること

これらのルールを守り、ストレスフリーでピボットテーブルを実務に活用してくださいね!

【解決策】ピボットテーブルの元データ(データソース)は「テーブル」にしておくこと

ちなみに、Excelには上記5つのルールを守りやすくするための機能として「テーブルとして書式設定」というコマンドがあります。

ピボットテーブルの元データにしたい表を作成する際、このコマンドを使って該当の表をテーブル化しておくことで、次のように物理的な制御をかけてもらえます。

  • 自動的に表の1行目が見出し、2行目以下はレコードになる
  • 見出し名を削除しても自動的に「列1」など何かしら値が入る
  • テーブル内でセル結合ができない(セル結合のコマンドが非活性)
  • 仮にテーブル内に空白レコードがあってもピボットテーブルの範囲として認識される

よって、ピボットテーブルの元データは原則テーブル化することをおすすめします。

テーブルのメリットについては、こちらの記事も参考にしてみてください。

既存の表をピボットテーブルの元データに適したものに整形・加工したい場合、Power Query(パワークエリ)という機能が便利です。

さいごに

いかがでしたでしょうか?

「ピボットテーブルは元データが9割」と言っても過言ではないほど、元データの準備が非常に重要なステップです。

そして、この元データが原因で集計につまずき、結果ピボットテーブルに苦手意識を持ってしまっている方も非常に多いと感じます。

なので、本記事を参考に、しっかりと上記の5つのルールを理解し、実務に活かしてくださいね。

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

私自身もピボットテーブル初心者の頃は、「なぜかピボットテーブルが挿入できない・・・」とか「ピボットテーブルで思うように集計できない・・・」など、つまずきまくっていました。

しかし、どんな元データにすれば良いかを理解すると途端にピボットテーブルの心理的なハードルが下がり、サクッと集計できるようになったものです。

ピボットテーブルは元データさえ整っていれば、大量のデータ集計・分析を行う上ではなくてはならない機能なので、しっかり活用できるようにしていきましょう!