導入編ではPower Queryを使ったデータ集計と自動更新の方法を具体的なでチュートリアルで説明しました。
チュートリアルを通じてPower Queryが強力なツールであることを実感いただけたのではないでしょうか？
でも、まだ一通り操作しただけで、自分なりの活用をするにはもう少し知識を広げておく必要がありそうです。
この記事では実務レベルのデータ処理に取り組んでいきます。Power Query の様々な機能を活用して例題に取り組みます。
この記事で取り上げる内容は以下の通りです
【データ取得】
- 複数のファイルからデータを一括で取り出す方法
【データ加工】
- 整形されていないシートからデータの部分だけを取り出す方法
- テキスト文字列を操作して必要なデータを取り出す方法
【データ出力】
- ピボットテーブルを利用して自在に集計する方法
Power Query チュートリアル：例題２
あなたの会社では下の図のような売上管理表を５つの支店で作成しています。本社にいるあなたはこれら５つのファイルを受け取って集計しなければなりません。
さらに、支店ごとの売上や、製品ごとの売上、月、年ごとの売上など、様々なグラフを作成して上司に報告が必要です。
この例題で使うサンプルデータのファイル一式は以下からダウンロードしてご活用下さい。
これはかなり大変そうな作業ですね。でも Power Query の機能をフル活用すれば処理できそうです。順番に処理していきましょう。
データの内容を確認する
まず、作業に入る前にデータを眺めてみましょう。そうすると以下の課題点に気付くかと思います。
- ファイルが複数ある
- 不要なデータ範囲がある
- 項目名が省略されている
- 年月が横に並んでいるので、「理想的なデータの形」になっていない
- 年と月が “YYYYMM” 形式 の文字列になっている
これらの課題に対して、以下のように処理をしていきます。
- フォルダ内の全てのファイルを読み込み
- 各ファイルの頭の不要な行を削除
- データの過不足を補正
- 「年月」を 横並び ⇒ 縦並び 変換
- 年月の文字列から正しく日付を生成
- ピボットテーブルにデータを繋いで集計する
では、さっそく始めましょう。
フォルダ内の全てのファイルを読み込む ①
Excelで空白のブックを開き、まっさらの状態から始めます。
以下のように操作して下さい。
操作手順
- Excelのメニューから「データ」を選択し、「データの取得」→「ファイルから」→「フォルダーから」をクリックします。
- ５つのファイルを保管しているフォルダを指定して「開く」ボタンを押す
- 「結合」→「結合および読み込み」を選択
- 小ウィンドウが出るので、データが入っている「Sheet1」を選択して「ＯＫ」ボタンを押す
- ５つのファイルが自動的に読み込まれて一つのテーブルに出力される
解説
この操作でフォルダ内のファイルを一つずつ開いてデータを集めてくる作業を Power Query が自動で処理しました。
この機能が優れた点は、これが ファイルが100個になっても同じように全て自動で処理してくれる ということです。
それぞれのファイルの頭の不要な行を削除する ②
さて、出てきたデータを見てみると、先ほど気になった 不要なデータが入り込んでしまっている ようです。
これは、各ファイルの Sheet1 からデータを読み取る時に Power Query が切り捨てずに読み込んでしまったものです。
これを修正するには以下の操作をして下さい。
操作手順
- クエリの一覧から「サンプルファイルの変換」を選択してダブルクリック
- Power Queryエディターが開くので、「行の削除」→「上位の行の削除」をクリック
- 消すのは1行だけなので「1」を入力して
- 「1行目をヘッダーとして使用」をクリック
- これで１行目にあったデータが列の名前（ヘッダー）になりました
解説
ここは少し分かりにくいところです。
フォルダ内のファイルを読み込んだ際に、全てのファイルに施すデータ処理が「サンプルファイルの変換」というクエリになります。
従って、ファイルごとのデータ処理に修正が必要な場合はこの「サンプルファイルの変換」というクエリを修正すれば良いのです。
そこで、冒頭の行を削除してから「１行目をヘッダーとして使用」という操作で列名（ここでは「ヘッダー」と呼ばれています）を変更しています。
これらの機能を活用することで、データとして綺麗に整っていないシートからもデータを読み込むことができるのです。
エラーへの対処（「テーブルの列が見つかりませんでした。」）
Power Queryの状態を見てみましょう。クエリ一覧を見ると「PowerQuery_Sample」のクエリでエラーが出ているようです。ダブルクリックして開いてみましょう。
Power Queryエディターを開くと、右側に処理手順（ステップ）が並んでいると思います。
これは上から順にそのデータに対して加えた加工が全て記録されているのです。
どのステップでエラーが起きているか、ステップをさかのぼってクリックしていくと、最後のステップでエラーになっていることが分かります。最後のステップは「データ型の指定」ですが、ここではこの処理は不要なのでこのステップを選択した状態で DELキーを押して削除します。
データの型の指定は通常最後の工程でやれば良いのですが、Power Queryはデータ読み込んだ段階で自動でデータ型の設定をする場合があります。これがエラーを発生させている場合は、一旦削除してしまって構いません。必要なら後からデータ型の指定をします。
エラーが消えて処理されたデータが見えると思います。エラーへの対処をするまでの操作の目的の通り不要な行が消えていることが分かります。
データを修正して過不足を補う ③
問題をクリアしたところで、続いてデータの加工に入ります。
１列目はファイル名ですが店名が含まれているので、不要な部分を削除して店名を抽出します。
それから、記入が省略されているデータも補完しましょう。
以下のように操作して下さい。
操作手順
- メニューから「変換」→「抽出」→「区切り記号の間のテキスト」
- 開始区切り記号に ”_” （アンダースコア）、終了区切り記号に “.”（ピリオド）を入力して「ＯＫ」ボタンを押します
- コントロールキーを押しながら「製品区分」と「シリーズ名」の２列を選択
- メニューから「変換」→「フィル」→「下へ」をクリック
解説
ここでは、まず文字列を区切り記号を使って抽出しました。データを区切る機能は従来のExcelの機能にもありますが、Power Query の方がよりきめ細かく設定が可能です。
続いてデータの記入が省略されている範囲にデータを埋める「フィル」という処理をしました。これも良く使う機能になりますので是非覚えておいて下さい。
「年月」を 横並び ⇒ 縦並び 変換 ④
だんだんデータらしくなってきました。次のステップに入ります。
以下のように操作して下さい。
操作手順
- 年月の列を除いた左４列をシフトキーを押しながら選択します。
- メニューから「変換」→「列のピボット解除」→「その他の列のピボット解除」をクリック
- 列名が「属性」「値」になってしまっているので、列名をそれぞれ「年月」「販売数」に修正します。
解説
1回の操作で横並びだった「年月」が縦並びになります。この機能は「ピボット解除」と言って理想的なデータを作成する上で非常に強力な機能です。
人がデータを入力する場合は、時間軸を横並びにして入力するのが直感的に分かりやすいので、こういう形をしたデータというのは多くあります。しかし、このままではデータとして活用できないので「ピボット解除」の処理が必要なのです。
年月の文字列から正しく日付を生成 ⑤
「年月」のデータはできましたが、実際には正しい日付のデータでは無く “YYYYMM” 形式の文字列になっていました。これを正しい日付のデータに変換していきます。
以下のように操作して下さい。
操作手順
- メニューの「列の追加」→「カスタム列」をクリック
- 出てきた小ウィンドウで新しい列名は仮なので「年月＋」と適当に入力します。
- 「年月」をダブルクリックしてインプットボックスに追加します。
- 続けて &”01″ と入力して「OK」ボタンを押します。
- すると、“YYYYMMDD”形式の文字列 のデータが追加されます。
- 続いて列名の左にある「ABC123」とあるアイコンをクリックしてプルダウンメニューを出して、「日付」を選択します。
- これで “YYYYMMDD” 形式の文字列が日付データに変換されました。
解説
ここでは小ワザを使っていますので、解説します。
「年月」文字列は横幅が短く月別の数値表を作成する上では便利です。しかし、これでは日にちの情報が足りないので「日付」のデータにはなりません。
そこで、YYYYMMの文字列の後ろに “01” （つまり1日）を足してから、データ型を 「日付」に変換した 訳です。
導入編でも解説しましたが、Power Queryでは「データ」を取り扱います。「データ」では、一つの列の中に入るデータの種類が同じである必要があります。
また、特に数値や日付は計算処理をするため、Power Query上でデータの種類をハッキリとさせておく必要があります。
例えば、上記の例のように “YYYYMMDD” の形式の元データがありましたが、これを数値とする場合は、”20230101″ は、”20,230,101″ と約２千万の数値なりますし、これを日付にする場合は、“2023年1月1日” になります。
このデータの種類のことを 「データ型」 と呼びます。データ型を意識してデータを整理することは非常に大切になりますので、覚えておくようにして下さい。
ピボットテーブルにデータを繋いで集計する ⑥
ここまでの操作により、Power Query で複数のファイルから必要なデータを抽出、加工、整形して綺麗なデータを作ることができました。続いてデータ集計を自由自在にできるピボットテーブルを使います。
以下のように操作して下さい。
操作手順
- 販売数の列の「ABC123」のアイコンをクリックして数値を選択。データを数値型にします。
- メニューから「閉じて読み込む」＞「閉じて次に読み込む」をクリックします。
- 出てきた小ウィンドウで、「接続の作成のみ」、「このデータをデータ モデルに追加」を選択して「OK」ボタンを押す
- メニューから「挿入」＞「ピボットテーブル」＞「データモデルから」をクリックします。
- 出てきたダイアログはそのまま「OK」ボタンを押します。
- ピボットテーブルが作成されるので、ウィンドウ右の「ピボットテーブルのフィールド」の「クエリ１」を開きます。（「クエリ１」でない場合でも出てきた項目名を開きます。）
- 「販売数」を「値」へ、「店名」を「行」へ、「年月」を「列」へ、ドラッグ＆ドロップします。
解説
最後は Power Query で作成したデータをピボットテーブルで集計しました。
きちんと整形されたデータがあれば、ピボットテーブルで数回のマウス操作で集計表を作成することができます。
ここでは詳細は解説しませんが、Power Query とピボットテーブル（Power Pivot）の組み合わせは非常に強力なツールになりますので、このチュートリアルを機会により深く学習されることをオススメします。
（⇒ ピボットテーブル関連記事 リンク ）
ここで「データ モデルに追加」とういう操作が登場しました。これは Power Query で整えたデータをもう一つの強力な機能である Power Pivot にバトンタッチするための操作です。
「データモデル」にすると Power Pivot の強力な集計機能を活用できるようになります。DAX言語などのプログラム言語も使えてユーザーのニーズに合わせた複雑な計算も可能です。
これらについては別の記事で紹介していますので、興味があればそちらも参照してみて下さい。（⇒ Power Pivot 関連記事 リンク ）
まとめ
複数のファイルを一気に読み込んだ上で、データを整形して全体の数値を一つの集計表にしてしまうという操作をほとんどマウス操作だけで実現しました。
Power Query の様々な機能と、最後に少しだけ Power Pivot についても解説させていただきました。
ここで構築したファイルは、実務で大いに役立つと思います。というのも、フォルダに保管されたファイルが増えて支店の情報が追加された場合でも、更新をかけるだけで全自動で集計表が作成されるからです。
ここまで学んだ Power Query の機能を活用することで、かなりの業務が楽に速く処理できるようになると思います。
この記事で学んだことをまとめます。
- データ取得：フォルダに保管された複数のファイルからデータをまとめて取り出しました。また、ファイルごとに不要な範囲を取り除いて必要なデータ部分を抽出しました。
- データ加工：抜けていたデータをフィル機能で補完し、「年月」のような文字列データを日付データに変換しました。
- 集計：Power Queryの処理結果を「データモデル」に繋げて、ピボットテーブルを作成して自動で集計表を作成しました。
次回の上級編では Power Query を活用していく上で知っておくべきより深い知識について解説したいと思いますので、興味のある方は是非読み進めてみて下さい。
