ピボットテーブルで作成したような集計表から、リスト形式の元表を作成する方法(Excel)

2024年1月12日 09:24

1.はじめに

こんにちは。株式会社SHIFT 技術統括部の力石です。主にお客さまのアプリケーション開発プロジェクトで、性能改善、品質向上などの非機能関連のコンサルティングをしています。

データ分析をする際に、集計したデータをもらったが違う切り口で分析をしたいということがあります。ピボットテーブルを使用して分析する場合、リスト形式のデータを入手する必要がありますが、簡単に集計表からリスト形式を作成することが可能です。
今回はその作成方法をお話しします。

ピボットテーブルで作成したような集計表から、リスト形式の元表が簡単にできるようになります。

入手した集計表に対し、「切り口を変えて再集計をおこないたいが元表が入手できない」という事情があったとします。

例えば以下の表は、店舗ごと日付ごとの売り上げ集計表となっています。これを月毎の集計表に作り替えたいという要望です。

Excelの関数を駆使すれば日付より月を求めて、月ごとの集計をすることは可能ですが、関数を書くなど多くの作業手順が必要となります。例えば期間が10年、店舗数が1000など対象となる数が多くなりますと、作業時間はもっと増えてきます。

このページで説明している方法を使用しますと、データ数が多くなっても作業時間は変わらず数分で実現可能です。

集計表から、ピボットテーブル機能を使用しピボットテーブルを作成します。そこからリスト形式の元表を作成します。

元表さえ出来れば、新たな切り口で分析をすることが可能となります。

ピボットテーブルを作成する方法は2つあります。

2つ目は、ALT→D→Pで「ピボットテーブルピボットグラフウィザード」を起動する方法です。
今回はこちらの方法を使用し作業します。

1.ALT→D→Pを入力すると以下の「ピボットテーブルピボットグラフウィザード 1/3」画面が立ち上があるので以下の内容を入力します。

[複数のワークシート範囲]を選択
※対象となる集計表は1つですが、「複数のワークシート範囲」を選択するのがポイントです。これを選択すると集計表をピボットテーブル化できます。
[ピボットテーブル]を選択
[次へ]ボタンをクリック

2.「ピボットテーブルピボットグラフウィザード 2a/3」画面が立ち上があるので以下の内容を入力します。

3.「ピボットテーブルピボットグラフウィザード 2b/3」画面が立ち上があるので以下の内容を入力します。

4.「ピボットテーブルピボットグラフウィザード 3/3」画面が立ち上があるので以下の内容を入力します。

5.新規のワークシートが作成され、集計表がピボットテーブルで表示されます。

6.ピボットテーブルの一番右下に総計が表示されています。

7.右下の総計セルをダブルクリックしますと、ピボットテーブルの作成元ネタがリスト形式で新規シートに作成されます。
これまでの作業で、ピボットテーブルで作成したような集計表からリスト形式の元表の作成が出来ました。

8.ここからは、使用例にあげた月単位で集計する方法を説明します。
ヘッダー行を「日付」、「店舗」、「売上」に変更します。
D列に年月のカラムを作成し、関数を使用しYYYY/MM形式で表示します

9.表全体を選択し、[挿入]→[ピボットテーブル]→[テーブルまたは範囲から]でピボットテーブルを作成します。

1から9番前の手順で日毎の集計を月毎の集計に変えることが出来ました。
作業手順を覚えていれば2分程度で出来るはずです。関数を駆使して作成するより格段に速く出来るはずです。

【このほかの記事】

執筆者プロフィール：力石知也
大手SIなどでアプリケーション開発からインフラ関連まで幅広く約30年経験。現在は性能改善、品質向上などの非機能関連のコンサルティングを実施。

お問合せはお気軽に

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの採用情報はこちら

PHOTO：UnsplashのTy Feague