More than 1 year has passed since last update.

雑・Excel入門試論 - 脱VLOOKUPの思考Advent Calendar 2022Day 15

雑・Excel入門試論 - 脱VLOOKUPの思考 15 - Power Query - エクセルのデータを読み込む

Last updated at 2023-01-12Posted at 2022-12-14

Power Queryを使う意味

　我々がエクセルで行う作業では、多くの場合、CSVファイルやエクセル・ファイル、テキスト・ファイルが渡されて、それを整形や加工する作業が大きな比重を占めています。
　Power Queryのコネクタを利用することで、CSVやエクセルファイルだけでなく、Web上のデータや、特別なアドインやODBCの設定無しで多くのデータソースからデータの抽出が可能です。
　また、以下のような、データの変換作業にも強力な力を発揮してくれます。

データクレンジング : データのフィルタリングや不要な行の削除、データの加工、変換、分割など、データをきれいにするためのあらゆる機能があります。
データ統合 : 複数のデータセットを結合するためにVLOOKUPやXLOOKUPを使っていると思いますが、Power Queryでは関数や引数を書くことなく、データの結合やグループ化を視覚的に行うことができます。
データエンリッチメント : 新しい列を追加したり、データセットに対して計算を行うことができます。また、テーブルを動的に作成することもできます。

Power Queryを使用した変換作業では、殆どの作業が数式やコードを書く必要なく、メニューのコマンドで操作が可能です。その操作結果は、１つ１つステップとして記録され、どのような手順で操作を行ったか追跡することができます。
作業の手順が検証可能であることは、データの信頼性を高めるためには大変重要です。

古典的なエクセルの操作で、エクセルシート上で複数ステップで変換を行ったり、データによって違う関数を使用したりすると、その手順の追跡、検証が難しくなります。また、最終的に値で貼り付けを行うと、シート上では追跡、検証が不可能になってしまいます。

　その操作した内容は M言語 で記録されているので、ユーザーはそのコードを学習することで、より便利な機能を手にすることができます。Power Queryは、ノーコード、ローコード、プロコード、いずれの要望にも答えることができます。

クエリオプション

　Power Queryを始める前に、クエリオプションを確認します。

1. グローバル・全般

▼ 2. グローバル・データの読み込み

　「高速データ読み込み」にチェックを入れます。このチェックによって、データの更新中にExcelのユーザーインターフェースがロックされますが、読み込みが高速に行われます。

▼ 3. グローバル・Power Queryエディター

　以下のチェックボックスにはすべてチェックを入れておきましょう。

4. グローバル・セキュリティ

5. グローバル・プライバシー

6. グローバル・地域の設定

　規定では「現在のインストール言語」になっており、Power Queryeエディターで作成されるステップの名前が日本語で作成されます。「常に英語」にすると、ステップ名が英語で作成されます。

7. グローバル・診断

8. 現在のブック・データの読み込み

　Power Queryは、クエリごとに最大1,000行のプレビューデータをキャッシュし、更新を高速化することができます。ただし、1,000行を超えるファイルに対して多数のクエリを作成する場合、プレビューデータの更新に時間がかかる可能性があります。そのような作業を行う場合は、「バックグラウンドでのデータプレビューのダウンロードを許可する」のチェックを外します。

9. 現在のブック・地域の設定

10. 現在のブック・プライバシー

データの読み込み

　Power Queryは、様々なデータソースからのデータを読み込むことができます。

エクセルブック、PDF、テキストファイル、CSV、XML、JSONなど
SQLサーバー、Access、Oracle, SAP HANAなど
Power BI
SharePoint、Exchange、Dynamics 365、 Salesfoceなど
OData、Hadoop、ODBCなど

Excelワークブックからデータを読み込む

データの取得

　作業の元となるデータが定期的に他のシステムから提供されるようなシーンでは、元となるデータの形がエクセルやCSVの形になっていることがよくあります。
　CSVファイルの場合、エクセルに読み込んでから作業を行うことになりますが、エクセルファイルで提供される場合、直接そのエクセルファイルで作業を行うことはやめましょう。元となるデータは、常に参照する形で使用し、元データを書き換える危険は極力避けるようにします。

　そこで、作業用のエクセルにデータを読み込む作業を行うことになりますが、その作業をPower Queryで行います。

　ナビゲーターにシート名、テーブル名が表示され、読み込みたいテーブルを選択し、「データの変換」を押します。

　テーブルのデータがPower Queryエディタに表示されます。

必要な項目の選択

　読み込みを行うと、自動的にクエリステップがいくつか作成されます。

ソース：エクセルブックの読み込み。
ナビゲーション：読み込み対象とするテーブルを選択
昇格されたヘッダー数：1行目のデータをヘッダーとして適用
変更された型：テーブルの最初の200行を検査して、列の型とヘッダーを自動的に検出

　すべてのクエリの最初のステップは「ソース」と表示されます。「適用したステップ」ウィンドウでこれを選択すると、Power Queryが抽出した生データがプレビュー表示されます。
　「変更された型」は、最初の200個の値から、日時、整数、10進数、テキストのいずれかの型を判断します。型の種類は他にもありますが、この4種類の型がPower Queryの基本の型となります。一旦「変更された型」を削除し、自動検出でなく、後で自分で行うこととします。

「適用したステップ」に表示されているステップを選択して、ステップの削除、追加を行うことができます。
また、Power Queryはデータをコピーして動作しているため、データソースを汚すことがありません。一番最初のステップである「ソース」以外を削除してしまえば、最初の状態に戻ることができます。

　まず最初に行うのは、必要な項目の選択です。読み込まれたデータをすべて使用する場合はそのままでいいのですが、必要のない項目があった場合は、列の選択でチェックを外して削除します。

項目名の変更

　最初の項目名は、エクセルのひょうの1行目のデータをもとに作成されていますので、必要なら適宜変更します。変更するには、項目名をダブルクリックして行います。

データ型の決定

　先程「変更された型」のステップを削除したので、項目名の左にあるアイコンが「ABC123」となっていると思います。

これは、Any型と言われるもので、なんでもOKの型です。データ処理を行っていくのに、項目の型を決めておくことは重要なので、変更します。
　「ABC123」となっているところをクリックすると、選択できる型の種類がドロップダウンで表示されます。このデータでは"Sales Price"がドルになっているので、小数点以下2桁が有効な通貨型を選択します。小数点以下を扱わない場合は、整数で問題ありません。小数点以下の計算が必要な場合は、浮動小数点を扱う10進数を選択します。

　変更すると、項目名の左にそれぞれの型を示すアイコンが表示されます。また、左側のクエリステップに「変更された型」のステップが追加されます。

　データの型の決定を決定し、期待するデータに揃えておくことは、データ処理の基本です。もっといい方法があるかもしれませんが、意識して作業を行うことを心がけてください。

「閉じて読み込む」

　ここで、一旦「閉じて読み込む」を押し、エクセルシートにデータを読み込んでみます。

クエリの編集

　作成したクエリを編集するには、「データ」の「クエリと接続」を選択すると、右側に先ほど作成したクエリが表示されます。

　表示されたクエリ名を右クリックし、「編集」を選択すれば、Power Queryエディタが表示されます。

　あるいは、「データ」から「データの取得」を開き「Power Queryエディターの起動」を選択します。

ソースの変更

　違うエクセルファイルを読み込みたい場合は、Power Queryエディタのステップ「ソース」の名前をダブルクリックするか、名前の右にある歯車のアイコンをクリックしてダイアログを表示させます。その中のファイルパスを変更すれば、違うファイルを読み込むことができます。

CSVの読み込み

　CSVの読み込みには、「テキストまたはCSVから」を使用します。

エクセルシートのデータを読み込む

　元となるデータを現在開いているエクセルブックのシートを使いたい場合、テーブル内のデータを右クリックし、「テーブルまたは範囲からデータを取得」を選択します。

　Power Queryエディタがデータが読み込まれた状態で開きます。

フォルダの中の複数のエクセルファイル（または複数のCSVファイル）を読み込む

　毎回ファイル名を指定するのではなく、決められたファイルに入っているものを読み込むようにしたい場合は、「フォルダーから」を使用します。加えて、フォルダーに複数のエクセルファイルがあれば、まとめて読み込むこともできます。

　下図のアイコンをクリックし、ContentsのBinaryを展開します。

　後は、先程のエクセルファイル（またはCSVファイル）と同様に選択していきます。

　データの読み込みができると、左側のクエリペインには複数のファイルからなる「ヘルパークエリ」が表示され、右側のステップは新たなステップが記録されています。
　1つ1つのファイルの変換は、ヘルパークエリの「サンプルファイルの変換」で定義されています。エクセルファイルからデータを取り込む際の操作を行うには、このクエリを修正していきます。修正は自動的に「ファイルの変換」に反映し、「新しいフォルダー」クエリから呼び出されます。

フォルダの中のファイルは、全て同じ形式のファイルが入っていることが前提です。違う形式が混じっている場合、正しく読み込みできません。

データの更新

　もとのデータファイルの中身が変更になった場合、「すべて更新」を押して再読込を行うと、定義されたステップが変更されたデータに再度適用されます。

従来は、データを入れ替えて計算しなおすには、全て手作業でやり直すか、古いワークシートに新たなデータを貼り付けて調整を行う作業が必要でした。Power Queryではそのような再作業の必要はなく、パラメータを書き換えて「すべて更新」ボタンを押すだけで完了します。
ただ早いだけでなく、エラーの原因となる人的要因を排除できることがとても重要です。

ポータビリティを高めるた、フォルダーのパスのような固有の値はパラメータに登録しておくことをお勧めします。

コンテンツ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up