Power Query ピボット解除でデータ整形② 分析用テーブルの作成 

業務改善コンサルティング会社がお届けする、今回はExcelのPower Queryの第四十四回目です。

 

前回記事（Power Query ピボット解除でデータ整形① テーブルの準備）のテーブル設定を踏まえて、今回はPower Queryでのピボット解除の具体的な手順を詳しく見ていきます。データをより詳細に分析するために必要な形式に変更し、実際の分析に適したテーブルを作成する方法を説明します。 

 

【実務でよく使用される表】 

実務では、下図の「在庫管理」のような「①会社名、②年別在庫数、③合計」の形式で管理するケースが多いと思います。ただし、このままだとピボットテーブルでデータ分析することはできません。 

【データ分析に向いたテーブル】 

下図の「①会社名、②年、③在庫数」がそれぞれ個別にデータが入力された形式ですと、ピボットテーブルでデータ分析が可能になります。 

【データ分析に向いたテーブルの作成】 

・テーブルのインポート 

・ピボット解除：パターン１ 

・ピボット解除：パターン２ 

・データの整理：必要データのみでフィルターをかける 

・データの整理：不要なデータを事前に削除する 

・データのインポート 

 

■テーブルのインポート 

1.前回記事で作成した、データテーブルをPower Queryにインポートします。データタブ ＞ テーブルまたは範囲からをクリックします。 

2.ヘッダーの設定を行います。 ホームタブ ＞ 1行目をヘッダーとして使用をクリックします。 

3.1行目がヘッダーに設定されました。 

 

次は、ピボット解除でデータを整形しましょう！ 

 

 

■ピボット解除：パターン１ 

1.会社名の列を選択し、変換タブ ＞ 列のピボット解除の▼ ＞ その他の列のピボット解除をクリックします。 

2.一つ一つのデータに分解され、求めたいデータテーブルの形式ができました。 

 

 

■ピボット解除：パターン２ 

1.基準となる会社名”以外”の列を選択します。変換タブ ＞ 列のピボット解除▼ ＞ (今回は)列のピボット解除 をクリックします。 

 

2.今回も「パターン１」と同じように一つ一つのデータに分解され、求めていたデータテーブルの形式ができました。 

※パターン１と２はどちらも同じ結果になるのでどちら使用してもOKです 

 

■データの整理：必要データのみでフィルターをかける 

1.属性列のデータを確認すると、“合計”という値が含まれています。こちらはピボット解除する前の合計列の”合計”という値が残ってしまっている状態です。不要なデータは削除しましょう！ 

2.属性「▼」をクリック ＞ 合計のチェックを外す ＞ OKをクリックします。 

3.“合計”以外のみの値でフィルターされ、必要データのみが残りました。 

■データの整理：不要なデータを事前に削除する 

1.事前に合計列を削除することもできます。 適用したステップ ＞ 変更された型1 ＞ 合計列を選択しピボット解除前まで戻ります。 

2.この時点で合計を削除しましょう。合計列を右クリック ＞ 削除をクリックします。 

3.ステップの挿入では、挿入をクリックし進めます。 

4.それでは後続作業にどう影響しているか確認していきましょう。適用したステップ ＞ ピボット解除された列 をクリックし確認します。 

5.この時点で属性列から”合計”の値が残っていないことが確認できます。 

6.最後にヘッダー名の属性は「年」へ、値は「在庫数」へ修正しましょう。 

 

 

■データのインポート 

データが完成したので、Excelにデータをインポートしましょう！ 

1.ホームタブ ＞ 閉じて読み込む ＞ 閉じて次に読み込む をクリックします。 

2.既存のワークシート ＞ I5セルを選択 ＞ OKをクリックします。 

3.在庫管理表のデータ(A)から、データ(B)として取り出すことができました。これにより(B)のデータでピボットテーブルの分析をすることが可能になりました。また、元々の表(A)もそのまま使える状態です。 

 

この記事を通じて、Power Queryでピボット解除を行い、分析用のテーブルを効率的に作成する方法を学びました。正しくデータを整形することで、より詳細かつ有用なデータ分析が可能になります。これらのスキルを活用して、日々のデータ処理をより効果的に進め、洞察を深めていきましょう。