導入編では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 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 を活用していく上で知っておくべきより深い知識について解説したいと思いますので、興味のある方は是非読み進めてみて下さい。
