MENU
  1. ホーム
  2. PowerQuery
  3. PivotTableやPivotChartの同じ値が重複する問題を解決するデータ変換テクニック

PivotTableやPivotChartの同じ値が重複する問題を解決するデータ変換テクニック

PowerQuery

アンケート結果などをExcelのPivotTableやPivotChartで分析したいのに、なぜか同じ数値がすべての行に並んでしまう……。こうした経験はありませんか？ 実は、データ構造をほんの少し工夫するだけで問題が解決し、より正確で自由度の高い集計・可視化が可能になります。今回は、この重複表示が起こる原因と、その解消方法を丁寧に解説していきます。読み進めれば、Power Queryを使ったアンピボット（列のピボット解除）や、回答選択肢の並び替えのコツなど、いま知っておきたい実用的なポイントがきっと見つかるはずです。では、早速スタートしましょう。

目次
  1. PivotTableやPivotChartで同じ値が重複表示される理由
  2. よくあるアンケート結果のデータ構造例
  3. データ変換の鍵：アンピボット（Unpivot）
    1. アンピボットとは何か？
    2. Power Queryを使った手順例
  4. 回答選択肢の順序をカスタマイズする方法
    1. 並び順を一括で管理するには別テーブル化が便利
  5. Mac版ExcelでのPower Queryの制限に注意
  6. PivotTable作成後におすすめの工夫
    1. スライサーやタイムラインの活用
    2. 不要な列や表記を削除する
  7. Power BIなど別ツールでの可視化も視野に入れよう
  8. 実践例：Power Queryコードのイメージ
    1. M言語でのアンピボット例
  9. まとめ：データはフラットに整形してからPivotTableを作ろう

PivotTableやPivotChartで同じ値が重複表示される理由

Excelでアンケートなどの結果を集計する際、下記のような状態に陥ることがあります。

  • PivotTableを作成したのに、回答数や集計値がすべて同じ行に表示されてしまう
  • 本来は質問A、質問B、質問Cと分かれているはずなのに、どの質問を行ラベルや列ラベルに設定しても同じ結果が表示される
  • グラフ（PivotChart）でも、すべてのカテゴリーが同じ数値に見えてしまう

これは多くの場合、データがすでにピボットされた形式（「横持ち」の形式）であることが原因です。ピボット済みのデータとは、たとえばアンケートの回答項目が横方向に展開されていたり、質問が行方向に設定されていたりと、本来集計用に必要な「各行が1レコードを表す」スタイルからかけ離れた配置になっている状態を指します。

よくあるアンケート結果のデータ構造例

アンケートの結果データは、下表のように横並びで表現されている場合がよくあります。

Q1_非常に満足Q1_満足Q1_普通Q1_不満Q1_非常に不満Q2_非常に満足Q2_満足Q2_普通Q2_不満Q2_非常に不満
20代51082149730
30代12155101114601

このように、質問ごとに列がわかれていて、回答選択肢ごとにも列がある――という状態は、人間が見る分にはわかりやすいかもしれません。しかし、PivotTableで扱う場合は「1行1レコード」という形式にまとめる必要があります。つまり、

  • 「どの質問か」
  • 「回答項目はどれか」
  • 「実際の回答数はいくつか」

という情報を、行単位で表現できるよう変換する必要があるわけです。

データ変換の鍵：アンピボット（Unpivot）

アンピボットとは何か？

アンピボット（Unpivot）は、Power Queryやデータベース操作の世界でよく使われる用語です。英語では「Unpivot columns」や「アンピボット列」と呼ばれ、列見出しとして横に並んでいる情報を行方向に整理し直す操作を指します。先ほどの例では、Q1～Q2など質問ごとに「非常に満足」「満足」「普通」……といった列が横に並んでいましたが、それを下記のように「Question」「Answer」「Count」といった列見出しに統合するイメージです。

AgeQuestionAnswerCount
20代Q1非常に満足5
20代Q1満足10
20代Q1普通8
20代Q1不満2
20代Q1非常に不満1
20代Q2非常に満足4
20代Q2満足9
20代Q2普通7
20代Q2不満3
20代Q2非常に不満0
30代Q1非常に満足12
30代Q1満足15
30代Q1普通5
30代Q1不満1
30代Q1非常に不満0
30代Q2非常に満足11
30代Q2満足14
30代Q2普通6
30代Q2不満0
30代Q2非常に不満1

このように変換しておくと、PivotTableのフィールド設定で「Question」「Answer」「Age」などを自由に行や列、値に配置できるようになり、正しい集計が可能になります。

Power Queryを使った手順例

実際にExcelでアンピボットを行う場合、Power Queryを使うのが最も簡単です。大まかな流れは以下のとおりです。

  1. データをPower Queryに読み込む
  • [データ]タブ → [データの取得] → [ファイルから] → [Excelブック] などを選択して、分析対象のワークシートもしくはテーブルを読み込みます。
  1. アンケートの回答列をまとめて選択する
  • Power Queryエディターが開いたら、横に並んでいるQ1_～Q2_～などの列をShiftキーやCtrlキーで複数選択します。
  1. 右クリック → [アンピボット]
  • 選択した列を右クリックし、「列のピボット解除」または「アンピボット」を選びます。これで、列見出しとして横に並んでいた情報が一つの「属性」列と「値」列にまとめられます。
  1. 列のリネームやデータ型の設定
  • 新しく作成された列の名前が「属性」「値」のままだとわかりにくいので、「QuestionAndAnswer」「Count」などに名前を変更します。
  • さらに「QuestionAndAnswer」列の文字列から、質問番号と回答選択肢を分割する必要がある場合は、列分割を行います（例：「区切り記号_」で分割すると、「Q1」「非常に満足」などに分離できます）。
  • 数値の列は「小数/整数」など適切なデータ型に設定します。
  1. 変換結果をシートに読み込む
  • 最後に[ホーム]タブ → [閉じて読み込む]をクリックして、変換後のデータをExcelシートとして出力します。そのシートをもとにPivotTableを作成すると、期待どおりの集計ができるようになります。

回答選択肢の順序をカスタマイズする方法

並び順を一括で管理するには別テーブル化が便利

アンケートには「非常に満足→満足→普通→不満→非常に不満」など、論理的・感覚的に適切な並び順があります。ところが、デフォルトの並び替えでは文字コード順（英語のアルファベット順や五十音順）になってしまうことが多いです。この場合は以下の方法が効果的です。

  1. 回答選択肢とその表示順序を格納したテーブルを用意
  • たとえば「Answer」「Order」という2列を持つテーブルを用意し、「非常に満足」「満足」「普通」「不満」「非常に不満」のようにAnswerを記載し、Order列に1,2,3,4,5といった数値を割り当てます。
  1. Data Model（データモデル）で関連付け
  • Excelでは、[データ]タブ → [データツール] → [関係の管理] を使って、アンケート回答のメインテーブルの「Answer」列と、回答選択肢テーブルの「Answer」列を関連付けます。
  1. 順序列を使って並び替え
  • PivotTableフィールドの設定画面やPowerPivotのデザイン画面で「表示順序を別の列で指定」することで、「Answer」を「Order」列に従って並び替えるよう設定できます。

こうすることで、データが増減したり、新しい回答選択肢が加わったときにも、あらかじめ決めた順番通りに並び替えが行いやすくなります。

Mac版ExcelでのPower Queryの制限に注意

ExcelのPower QueryはWindows版が最も機能豊富ですが、Mac版では機能が制限されている場合があります。もしMac版Excelを使っている場合、アンピボットを含む複雑なデータ変換がうまくできない可能性があります。対策としては、以下のような方法があります。

  • Windows環境のExcelを使う
    Boot Campや仮想マシン（Parallels、VMwareなど）を利用し、Windows版ExcelをインストールしてPower Queryをフル活用する。
  • オンライン版Excelを活用する
    Office 365のサブスクリプションがある場合は、Webブラウザ経由で最新機能を利用できることがある。
  • 外部ツールでのデータ変換
    PythonやR、あるいは他のETLツールを使ってデータを加工し、アンピボットした結果をExcelに読み込む。

状況に合わせて最適な方法を選択し、Mac版のExcelのみで作業が完結しない場合も、うまく組み合わせて柔軟に対処しましょう。

PivotTable作成後におすすめの工夫

スライサーやタイムラインの活用

集計軸が増えてくると、表やグラフが複雑になりがちです。そんなときは、PivotTableにスライサーを挿入してみるのがおすすめです。スライサーを使うと、クリック操作で特定のAge（年代）や回答を瞬時にフィルタリングできるため、分析やプレゼンが格段にやりやすくなります。スライサーのほかに、日付データがある場合は「タイムライン」機能を使って期間で絞り込むことも可能です。

不要な列や表記を削除する

アンピボット前のデータには集計に不要な列（合計列や「行見出し」など）が含まれていることがあります。PivotTableを作った後に混乱しないよう、Power Queryの段階で不要な列を削除しておくのがおすすめです。また、データ型が不明なままだと集計値がテキスト扱いになるなどの不具合が起きるので、変換時にきちんと「数値」「日付」「文字列」を使い分けるようにしましょう。

Power BIなど別ツールでの可視化も視野に入れよう

ExcelのPivotTableやPivotChartだけでなく、Power BIや他のBIツールを使うと、さらにインタラクティブで洗練されたビジュアルを作成できます。これらのBIツールでも基本的な考え方は同じで、データはできるだけフラット（縦持ち）な形式に整形するという原則が重要です。アンピボットを行ってから読み込むことで、Power BIでもスムーズに分析が行えます。

実践例：Power Queryコードのイメージ

M言語でのアンピボット例

以下に、Power Queryエディターの「詳細エディター」を開いたときのM言語コード例の一部を示します。実際には読み込むデータや列名によって異なりますが、参考にしてください。

let
    Source = Excel.Workbook(File.Contents("C:\User\Desktop\SurveyData.xlsx"), null, true),
    Data_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"Age", type text}, 
            {"Q1_非常に満足", Int64.Type},
            {"Q1_満足", Int64.Type},
            {"Q1_普通", Int64.Type},
            {"Q1_不満", Int64.Type},
            {"Q1_非常に不満", Int64.Type},
            {"Q2_非常に満足", Int64.Type},
            {"Q2_満足", Int64.Type},
            {"Q2_普通", Int64.Type},
            {"Q2_不満", Int64.Type},
            {"Q2_非常に不満", Int64.Type}
        }
    ),
    UnpivotedData = Table.UnpivotOtherColumns(
        ChangedTypes,
        {"Age"},
        "QuestionAnswer",
        "Count"
    ),
    SplitColumn = Table.SplitColumn(
        UnpivotedData,
        "QuestionAnswer",
        Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),
        {"Question", "Answer"}
    ),
    FinalTable = Table.TransformColumnTypes(
        SplitColumn,
        {{"Question", type text}, {"Answer", type text}, {"Count", Int64.Type}}
    )
in
    FinalTable

ポイントとしては、

  • Table.UnpivotOtherColumns（アンピボット）で、Age以外の列をすべて「QuestionAnswer」「Count」にまとめている
  • Table.SplitColumn で QuestionAnswer 列を _ 区切りで分割して、「Question」と「Answer」の2列にしている
  • type text や Int64.Type など適切な型指定を行っている
    といった点が挙げられます。

まとめ：データはフラットに整形してからPivotTableを作ろう

PivotTableやPivotChartで同じ値が重複して表示される場合、そのデータはすでにピボット化されている可能性が高いです。そのままでは正しい集計ができないため、以下の流れを意識してみてください。

  1. Power Queryなどでアンピボット
  • 列を行に変換し、「どの質問」「どの回答」「回答数はいくつか」の形へ整理する。
  1. 回答選択肢の順序管理
  • 必要に応じて、別テーブルに回答順序を設定し、Data Modelで関連付けする。
  1. 不要な列や表記を削除、データ型の設定を厳密に
  • 不要列はカットし、文字列・数値を正しく指定し、整った形でシートに読み込む。
  1. PivotTableを作成後、スライサーや並び替えで見やすい表・グラフに
  • 回答選択肢の並び替えやスライサーの活用を行うと、より明瞭な分析ができる。

アンケート結果や顧客データなど、あらゆる集計シーンに応用できるテクニックなので、ぜひマスターして自由自在なデータ分析を楽しんでください。もしMac版Excelで機能が足りない場合は、Windows環境の利用やオンラインサービス、外部ツールとの連携なども検討してみましょう。

PowerQuery
Excel PIVOT PowerQuery

コメント

コメントする

目次
  1. PivotTableやPivotChartで同じ値が重複表示される理由
  2. よくあるアンケート結果のデータ構造例
  3. データ変換の鍵：アンピボット（Unpivot）
    1. アンピボットとは何か？
    2. Power Queryを使った手順例
  4. 回答選択肢の順序をカスタマイズする方法
    1. 並び順を一括で管理するには別テーブル化が便利
  5. Mac版ExcelでのPower Queryの制限に注意
  6. PivotTable作成後におすすめの工夫
    1. スライサーやタイムラインの活用
    2. 不要な列や表記を削除する
  7. Power BIなど別ツールでの可視化も視野に入れよう
  8. 実践例：Power Queryコードのイメージ
    1. M言語でのアンピボット例
  9. まとめ：データはフラットに整形してからPivotTableを作ろう