導入編のチュートリアルでは Power Query を使ったデータ集計と自動更新を一通り操作しました。
チュートリアルを通じて Power Query が強力なツールであることを実感いただけたのではないでしょうか？
でも、まだ一通り操作しただけで、自分なりの活用をするにはもう少し知識を身に付けておく必要がありそうです。
この記事では実践的なデータ処理に取り組んでいきます。Power Query の様々な機能を活用して例題に取り組みます。
この記事で取り上げる内容は以下の通りです
【データ取得】
- 複数のファイルから データを取り出す
【データ加工】
- 整形されていないシート からデータを取り出す
- テキスト文字列を操作 してデータを取り出す
【データ出力】
- ピボットテーブルを利用 して自在に集計する
Power Query チュートリアル：例題２
あなたの会社では下の図のような 売上管理表を５つの支店で作成 しています。本社にいるあなたはこれら ５つのファイルのデータを集計 しなければなりません。
さらに、次のような 様々なグラフを作成 して上司に報告が必要です。
- 支店ごとの売上
- 製品ごとの売上
- 月、年ごとの売上
この例題で使うサンプルデータのファイル一式は以下からダウンロードできますので、よろしければご活用下さい。
これはかなり大変そうな作業ですね。でも Power Query の機能を使えば次々と処理できてしまいます。
手順を順番にみていきましょう。
データの内容を確認する
まず、作業に入る前にデータを眺めてみましょう。そうすると以下の 課題点 に気付くかと思います。
- ファイルが複数 ある
- 不要なデータ範囲 がある
- 項目名の記入が省略 されている
- 年月が縦並びでなく 横並びになっていて、「理想的なデータの形」になっていない
- 年と月が “YYYYMM” 形式 の文字列になっている
これらの課題に対して、それぞれ以下のように処理をしていきます。
- フォルダ内の 全てのファイルを読み込み
- 各ファイルの頭の 不要な行を削除
- データの不足を補完
- 「年月」を 横並び ⇒ 縦並び 変換
- 年月の文字列から 正しく日付を生成
- 最後に ピボットテーブル にデータを繋いで集計する
では、さっそく始めましょう。
フォルダ内の全てのファイルを読み込む ①
Excelで空白のブックを開き、まっさらの状態から始めます。
以下のように操作して下さい。
操作手順
- Excelのメニューから「データ」を選択し、「データの取得」→「ファイルから」→「フォルダーから」をクリックします。
- ５つのファイルを保管している フォルダを指定して「開く」ボタンを押す
- 「結合」→「結合および読み込み」を選択
- 小ウィンドウが出るので、データが入っている「Sheet1」を選択 して「ＯＫ」ボタンを押す
- ５つのファイルが自動的に読み込まれて 一つのテーブルに出力される
解説
この操作で フォルダ内のファイルを１つずつ開いてデータを集めてくる作業 を Power Query が自動で処理 してくれました。
この機能が優れた点は、これが ファイルが100個になっても 同じように 全て自動で処理してくれる ということです。
それぞれのファイルの頭の不要な行を削除する ②
さて、出てきたデータを見てみると、先ほど気になった 不要なデータが入り込んでしまっている ようです。
これは、各ファイルの Sheet1 からデータを読み取る時に Power Query が切り捨てずに読み込んでしまったものです。
これを修正するには以下の操作をして下さい。
操作手順
- Excel のメニューから「データ」→「クエリと接続」をクリックしてクエリ一覧を開く
- クエリの一覧から「サンプルファイルの変換」を選択してダブルクリック
- Power Queryエディターが開くので、「行の削除」→「上位の行の削除」をクリック
- 消すのは1行だけなので「1」を入力して
- 「1行目をヘッダーとして使用」をクリック
- これで１行目にあったデータが列の名前（ヘッダー）になりました
解説
ここは少し分かりにくいところです。
フォルダ内のファイルを読み込む際に、ファイルごとに施すデータ処理は「サンプルファイルの変換」というクエリ になっています。
ファイルごとのデータ処理に修正が必要な場合はこの「サンプルファイルの変換」というクエリを修正すれば良いのです。
そこで、このクエリを編集して、
- 冒頭の行を削除
- １行目の内容を列名に変換
という処理を追加しました。
これらの機能を活用することで、データとして綺麗に整っていないシートからもデータを読み込むことができるのです。
エラーへの対処（「テーブルの列が見つかりませんでした。」）
Power Queryの状態を見てみましょう。クエリ一覧を見ると「PowerQuery_Sample」のクエリでエラーが出ている ようです。
ダブルクリックして開いてみましょう。
Power Queryエディターを開くと、右側に「適用したステップ」が表示されています。
これは データ加工の操作手順が上から順に記録 されたものです。
ステップ名をクリックするとそのステップでのデータ処理の状態がプレビューに表示されます。
またそのステップでエラーが発生しているとエラー表示もされます。
Power Query エディターを開くと最後のステップが選択された状態ですが、どうやらその最後のステップでエラーになっている ことが分かります。
「売上管理表 東京本店」という列が無いというエラーです。
この「売上管理表 東京本店」とは、フォルダを読み込む操作の際に Power Query が自動で設定した列名 のようです。
列名は「サンプルファイルの変換」のクエリを編集して変更している ので、ここで不一致を起こしたようです。
最後のステップは「データ型の指定」ですが、ここではこの処理は不要なので このステップを選択 した状態で DELキーを押して削除 します。
今回の例題では データ型の指定はピボット解除した後で行います ので、このステップは一旦削除します。
エラーが消えて処理されたデータが見えると思います。エラーへの対処をするまでの操作の目的の通り 不要な行が消えている ことが分かります。
データを修正して不足を補う ③
問題をクリアしたところで、続いてデータの加工に入ります。
１列目はファイル名ですが 店名が含まれているので、不要な部分を削除して店名を抽出します。
それから、記入が省略されているデータを補完 しましょう。
以下のように操作して下さい。
操作手順
- メニューから「変換」→「抽出」→「区切り記号の間のテキスト」
- 開始区切り記号 に ”_” （アンダースコア）、終了区切り記号 に “.”（ピリオド）を入力して「ＯＫ」ボタンを押します
- コントロールキーを押しながら「製品区分」と「シリーズ名」の２列を選択
- メニューから「変換」→「フィル」→「下へ」をクリック
解説
ここでは、まず 文字列を区切り記号を使って抽出 しました。データを区切る機能は従来の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 Query エディターのメニューの「閉じて次に読み込む」が押せなく なってしまいます。
「閉じて次に読み込む」をクリックすると「データのインポート」設定ができるのですが、これは別の方法でも可能です。
「データのインポート」設定ウィンドウを出す手順は以下のようになります。試してみて下さい。
- Power Query エディターを閉じる。
- 編集内容を残す場合は「保存」、そうで無い場合は「破棄」のボタンを押す。
- Excel のメニューから「データ」 > 「クエリと接続」 をクリック
- ウィンドウ右にサイドウィンドウが出てクエリ一覧が表示される
- 対象のクエリを右クリック
- メニューが出るので 「読み込み先…」をクリック
- 「データのインポート」設定ウィンドウが出る
データモデルについて
ここで「データ モデルに追加」とういう操作が登場しました。これは Power Query で整えたデータをもう一つの強力な機能である Power Pivot にバトンタッチするための操作 です。
「データモデル」にすると Power Pivot の強力な集計機能を活用できるようになります。
Power Pivot ではこれまでのピボットテーブルではできなかったような様々な計算ができるようになります。更に、DAX言語というプログラム言語も利用することができ、複雑な計算も自在にカスタマイズすることができます。
これらについては以下の記事で紹介していますので、興味があればこちらも参照してみて下さい。
Power Query のエラー対処方
このチュートリアルの中でもありましたように、Power Query でデータを処理する際、エラーに遭遇することがあります。エラーを解消しないとデータ処理が完了できない場合もありますので、エラーに対処する知識が必要です。そこで、続いてエラー対処方について紹介したいと思います。
エラー発生を把握する
まずPower Query でエラーが発生した場合のエラー表示を知っておく必要があります。エラーは大きく分けて２つ種類があります。
処理が止まるエラー
Power Query の処理コマンドが実行できないと、そこで処理が止まってしまいます。この場合、ダイアログが出て図のように「クエリと接続」のサイドウィンドウにエラーの表示が出ます。
この表示を確認したら Power Query エディター でエラーが発生している処理手順の箇所を修正するなどの対策が必要です。
処理は進むが、処理できていないデータがあるエラー
一見、Power Query の処理は無事終わったように見えるのですが、実は処理できていないデータがある場合があります。
その場合は、下の図のような表示になります。この場合は3個のデータが何らかの問題で処理できなかったものと考えられます。
この「３個のエラーです。」という箇所をダブルクリックすると図のようにエラーとなっているデータのみを出力する一時的なクエリが自動作成されて Power Query エディターが開きます。
また、エラーが発生しているクエリを Power Query エディターで開くと図のようにエラーが発生している列が通常と異なる表示になっています。
処理が止まるエラーの箇所を特定する
処理が止まってしまうエラーが発生すると Power Query は機能していな状態になりますので、とにかく対策が必要です。
最初にエラーが発生している箇所の特定が必要です。このために、まずクエリ名の部分をダブルクリックして Power Query エディターを開いて確認します。
図のように「エラーに移動する」ボタン が表示されている場合はこれを押します。するとエラーが発生している処理手順にに移動します。
よくあるエラー「テーブルの列 ‘***’ が見つかりませんでした。」の対策
Power Query の処理を作成しているとよく「テーブルの列 ‘***’ が見つかりませんでした。」というエラーに遭遇します。ちなみに、’****’の部分は具体的な列の名前になります。
このエラーの原因と対策の例を挙げてみます。
データソースが変更されて列が無くなった
これは読み込むデータの方が変更されて、Power Query で処理する列が無くなってしまったケースです。
データを修復して Power Query の処理と整合させるのが最も単純な対策 となります。
データソースの変動に強い Power Query を組む
しかし、他にも データソース の変動に強い Power Query を組む という対策があります。
このチュートリアルの中で 「年月」を 横並び ⇒ 縦並び 変換 ④ という処理手順がありましたがここはこの対策をした部分になります。
ピボットの解除には ピボッット解除する列を指定してピボット解除 する方法 と、ピボット解除しない列を指定してその他の列をピボット解除する方法 の２つがあります。
ピボット解除する場合は、後者の ピボット解除しない列を指定してその他の列をピボット解除する方法 の方が データソースの変動に強い方法となります。
なぜなら、横並びのデータは更に横にデータが延長されるケースが多いのですがピボット解除する列を指定してしまうと、後から追加された列はピボット解除される列に含まれなくなってしまう からです。
以上から、このチュートリアルの 「年月」を 横並び ⇒ 縦並び 変換 ④ の手順のように「その他の列のピボット解除」を使うのはオススメできる Power Query の組み方になります。
処理は進むが、処理できていないデータがある場合の対策
エラー行の削除
エラーを含む行を削除することで、クエリの処理を続行できます。これは、エラーが少数であり、データの損失が許容できる場合に有効です。
手順:
- エラーセルの列を右クリック します。
- 「エラーの削除」を選択します。
エラーの置換
エラーセルを特定の値（例：0、NULL、特定の文字列）で置換することで、エラーを処理できます。
手順:
- エラーセルの列を右クリック します。
- 「エラーの置換」を選択します。
- 置換する値を入力します。
条件付き変換
条件付き変換を使用して、エラーを特定の条件に基づいて処理できます。これにより、より柔軟なエラーハンドリングが可能になります。
手順:
- 「追加」タブをクリック します。
- 「条件付き列」を選択します。
- 条件を設定し、エラーに対する適切なアクションを指定します。
エラーの対処法まとめ
エラー発生を把握して、エラー箇所を特定、対策する方法を紹介しました。また、データソースの変動に強い Power Query の組み方についても事例を紹介しました。
また、データの中にエラーとなるデータが含まれていた場合は、削除したり変換したりする方法も紹介しました。
Power Query のエラー発生の原因を良く知ることでエラーが起こりにくい実用的な仕組みの構築が可能になりますので、是非経験を踏んでスキルを身に付けて下さい。
まとめ
チュートリアルでは 複数のファイルを一気に読み込んだ上で、データを整形して一つの集計表にしてしまうという処理をほとんど マウス操作だけで実現 しました。
ここで構築したファイルは、実務で大いに役立つと思います。というのも、フォルダに保管されたファイルが増えて支店の情報が追加された場合でも、次回は更新の操作だけで集計表が自動で更新される からです。
チュートリアルで学んだことをまとめます。
- データ取得：フォルダに保管された 複数のファイルから データをまとめて取り出しました。また、ファイルごとに不要な範囲を取り除いて必要なデータ部分を抽出しました。
- データ加工：抜けていたデータをフィル機能で補完 し、「年月」のような 文字列データを日付データに変換 しました。
- 集計：Power Queryの処理結果を「データモデル」に繋げて、ピボットテーブルを作成して自動で集計表を作成 しました。
また、後半では Power Query のエラー対処方を解説しました。Power Query で発生するエラーを知ることでより実用的な仕組みの構築が可能となりますので、ご自分でも Power Query をさわってみて経験を積んでみて下さい。
次回の上級編では Power Query を活用していく上で知っておくべきより深い知識について解説したいと思いますので、興味のある方は是非読み進めてみて下さい。
