エクセル初心者
エクセルのパワークエリって何かな？使い方を教えてほしいな！
エクセルのパワークエリは、変換ミスせずにデータを読み込んでくれて、複数のデータを一つにまとめられる便利な機能だよ！
Dr.オフィス
今回は、操作が少し難しいけれど、慣れると便利なエクセルのパワークエリについて解説します。
パワークエリを使いこなせるようになれば、別々に保存していたデータを一つにしたり、元データをいじらなくても綺麗に整えた表を提示できるようになりますよ。
- データの「接続や取得」「変換や整理や組合わせ」「出力」ができる
- 「更新」も可能！
マイクロソフトオフィススペシャリストの資格を持つ私が、役立つパワークエリの使い方について解説しつつ、具体的な活用法も紹介するよ。
Dr.オフィス
エクセルのパワークエリは使い慣れると複数のエクセルファイルを一つにまとめ、表にするといった作業もできます。
数多くのデータをまとめるのに苦労している方には、大変ありがたい機能になるので、使い方をマスターしていきましょう！
※本記事は『OS：Windows11』画像は『Excelのバージョン：2021』を使用しています。
目次
エクセルのパワークエリとは
パワークエリは「データ」タブを開くと表示される「デパワークエリでCSVータの取得と変換」と「クエリと接続」機能になります。
パワークエリを用いて出来ることは以下の4つです。
- データの接続・取得
- 取得したデータの変換・整形・結合
- 変換・整形・結合したデータの出力
- 出力したデータの更新
接続や取得、整形や変換と言われても、ちょっとわかりにくいですよね。今回は具体的にどういった使い方が出来るのかを紹介していきます。
パワークエリは古いエクセルでマクロやVBAでやっていたことを、一つの機能に落とし込んで使えるようにしてくれるんだよ！
Dr.オフィス
パワークエリでCSVやPDFファイルの取り込みができる
パワークエリで出来ることの代表例が、CSVファイルやPDFファイル、さらにはWEBデータを簡単に取り込めることです。
大量にあるエクセルデータを保存するときは、エクセルそのままのデータではなく他の人達とやり取りするときに使いやすいCSVファイルにすることもあります。
パワークエリは、このCSVファイルを綺麗に落とし込めることができます。
パワークエリを使ってCSVファイルを文字化けさせず、しっかりと開ける方法を解説するから安心してね！
ちなみに、PDFファイルも取り込めるけど、取り込むにはMicrosoft 365かエクセル2021じゃないといけないから、注意してね！
Dr.オフィス
パワークエリは簡単に複数ファイルの結合が可能！
パワークエリを使えば、データとしてバラバラに保存していたものを一つに結合させることが出来ます。
例えば、支社や支部や部署がいくつかあり、それぞれで顧客情報や売上げ情報をバラバラに管理していたものを、パワークエリがあれば簡単に1つにまとめられます。
また、結合させるファイルに同じ「ID」や「商品番号」を使っていると、パワークエリの「マージ（結合）」機能を使えば異なるテーブルになっているエクセルでもまとめることもできます。
パワークエリを使って複数エクセルを取り込んだ後は、元データを変更しても取り込んだデータを更新するとデータ連携で同じように更新させることも可能なのでとっても便利ですよ！
パワークエリの「マージ」機能はちょっと難しいから、この記事ではシンプルに複数記事を1つにまとめる方法を紹介するね！
Dr.オフィス
パワークエリのデメリット
便利なパワークエリですが、デメリットやちょっとした問題点についても解説していきましょう。
デメリットは簡単にまとめると以下の通りです。
- パワークエリエディターを開いていると全てのエクセルでワークシート作業ができなくなる
- 3つ以上のテーブルの同時結合ができず、2つの結合を繰り返すことになる
- エクセルと関数処理の仕方が違うので、使い慣れたエクセル関数が使えない
パワークエリは便利ではあるけど、使っていると「う～ん･･･」って思ってしまう部分は確かにあるよ！
Dr.オフィス
全てのエクセルでワークシート作業ができなくなる
これがパワークエリを使う上で一番のデメリットになっている人が多いです。
パワークエリを操作するには「パワークエリエディター」を開かないといけないのですが、これを開いていると今使っているエクセルのワークシートも、別に開いているエクセルのワークシートでも作業が出来なくなります。
別のエクセルの開いている画面を見ることはできるけど、何の操作も受け付けない状態になってるよ！
Dr.オフィス
そのため、いくつかの仕事や作業を並行して行っている場合、他のエクセルを操作するためにはいちいちパワークエリエディターを閉じないといけません。
これは何度も繰り返すことになると人によってはとても面倒に感じてしまいます。
異なる3つ以上のテーブルの同時結合ができない
パワークエリは結合させたいファイルに同じ「ID」や「商品番号」を使っている場合「マージ（結合）」機能で結合できます。
ただし、この異なるファイルを用いた結合作業ができるテーブルは2つまでです。
3つ以上は同時に結合させることが不可能なので、大量にあるデータでテーブルの結合が必要になると、2つの結合をコツコツ続けないといけません。
エクセルと関数処理の仕方が違う
パワークエリはエクセルとは違う言語の「M言語」を使っているので、使い慣れた「SUM」関数や「VLOOKUP」関数といったエクセル関数がパワークエリエディター上では使えません。
パワークエリエディター上で新たに計算式を作りたいときはM言語にマッチした関数を用意してあげましょう。
パワークエリエディター上での作業は、リボン上の機能でほぼほぼ完結しているから、計算式を後付けで埋め込むことはあんまりないよ！
人によっては、マウスのクリックだけで作業が終わるから安心してね！
Dr.オフィス
パワークエリとマクロやVBAの違い
パワークエリと似た機能にはマクロやVBAがあります。
パワークエリがマクロやVBAとの違う点は「プログラミング言語の知識」が必要ないということです。
マクロは特定の動作を自動で動くようにさせる機能、VBAはそのマクロを問題なく動くようにさせるプログラミング言語だよ！
つまり、マクロを動かすにはVBAを動かすためのプログラミング言語知識が必要だったんだよね！
Dr.オフィス
パワークエリはパワークエリエディター上でVBAでプログラムさせないとできないような処理が簡単にできてしまいます。
パワークエリをより使いこなしたいのであれば「M言語」をマスターしないといけませんが、通常の編集作業なら「M言語」知識もいらないので、マクロを動かすよりも簡単です。
ただし、VBAやマクロじゃないとできない処理も存あるので、場合によって使いわけましょう。
エクセルのパワークエリを実際に使ってみよう！
それでは具体的にエクセルのパワークエリを使った、CSVファイルなどのデータを正しく読み込む方法から学んでいきましょう！
複数ファイルの結合方法についても解説していきます。
CSVやPDFファイルをのデータを読み込む方法
- STEP
新規ブックをエクセルで作成する
ファイルをクリック 最初に「ファイル」をクリックしてください。
- STEP
空白のブックを選択する
空白のブックをクリック こちらの画面が表示されたら、空白のブックをクリックしてください。
- STEP
「データ」タブの「データの取得」から取得方法をクリック
CSV読み込み - 「データ」タブを開く
- 「データの取得」をクリックする
- 取得方法を確認されるので今回は「ファイルから」にポイントを合わせる
- 表示された中から「テキストまたはCSVから」をクリックする
対象のファイル形式が異なるときは、それにあわせた形式を選んでね！
Dr.オフィス
- STEP
該当のファイルを選び「インポート」をクリック
CSVファイルインポート - 開きたいデータの保存場所を見つけて対象物をクリックする
- 「インポート」をクリックする
- STEP
プレビュー画面が表示されるので「データ変換」をクリック
CSVデータ変換 - どのように表示されるのかをチェックする
- 「データの変換」をクリック
「データの変換」を使うと自動判定機能が働きその後の作業が楽になるから、「読み込み」ではなくこっちを使ってね！
Dr.オフィス
- STEP
Power Queryエディターが開かれるので、必要な変換や整理作業を行う
修正必要箇所確認 まずは、パワークエリの画面で修正や整形が必要な箇所があるのかを確認してください。
修正がいるかどうかでやるべき事やかかる時間が変わるので、自分に合わせた作業を行ってください。
特に、修正内容がないという人は、以下に解説している表記の修正や列削除っていう作業は不要だよ！！
Dr.オフィス
- STEP
電話番号の表記修正
ここでは住所録のCSVデータから、電話番号の表記修正と余計な表示を消すという作業について解説していきます。
まずは、電話番号の表記修正からしていきましょう。
電話番号表示修正 - 「電話番号」をクリックして列を選択
- 「ホーム」タブをクリックする
- 「データ型：変数」をクリックする
- 「テキスト」をクリックする
ここまでの作業をすると次のポップアップが出てきます。
- STEP
「列タイプの変更」について
テキスト変換ポップアップ 「列タイプの変換」について問われるので、「現在のものを置換」をクリックして完了です。
電話番号がきちんと「0」から表示されているのかを確認してください。
- STEP
不要な列の削除
次は、不要な列の削除を行い表を整理します。
列削除 - 「ホーム」タブになっているか確認する
- 不要な列データがあるヘッダーをCtrlを押しながらクリックして全部選ぶ
- 「列の削除」をクリックする
- 新たに表示された「列の削除」をクリックする
これで余計な列の表示を消えました。
- STEP
名前の変更
右側の「適用したステップ」の項目にある「削除した列」を後から見てもわかるように名前を変更していきます。
削除された列名前変更 - 「削除された列」を右クリックする
- 「名前の変更」をクリックして、先ほどの作業内容をわかりやすく記載する
- STEP
住所のヘッダー表記を修正
次は住所の部分のヘッダー表記がおかしくなっているので、そちらを修正します。
住所ヘッダー修正 それぞれのヘッダーをダブルクリックして、左から「都道府県」「市区町村」「字」「番地」「アパート・マンション」に変更していきましょう。
- STEP
パワークエリエディター上での作業完了
作業が終了すると、画面右側の「適用したステップ」内に「名前が変更された列」という作業内容が表示されます。
名前が変更された列 こちらも、このままでは先ほどの「削除された列」と同様、作業内容がわからないので同じように「住所部分のヘッダー変更」に変えておきましょう。
これでパワークエリエディター上での作業は終了となります。
- STEP
「ホーム」タブの「閉じて読み込む」をクリックし「閉じて次に読み込む」をクリック
閉じて読み込む - 「ホーム」タブにある「閉じて読み込む」をクリックする
- 「閉じて次に読み込む」をクリックする
- STEP
「データのインポート」が表示されるので、自分が出力したい形にしてから「OK」をクリック
データのインポート - データの表示方法を4つから選ぶ（画像では「テーブル」）
- データを返す先を選択する（画像ではエクセルの左上から表示させたいので「=＄A＄1」）
- 「OK」をクリック
- STEP
データの取り出し完了
ここまでの作業が完了すると、CSVの住所録からデータが綺麗に取り出せます。
抽出後の住所録 画像のように、連番が消えて、電話番号が「0」から表示されるようになり、住所部分のヘッダーも変わっていたのなら、成功です！
複数ファイルを結合する方法
次は、複数あるファイルを1つのエクセルに結合する方法について解説します。
今回は売上げ情報を例に解説していきます。
テンプレートが用意された売り上げのエクセルデータを使用し、各店舗ごとにデータが入力されている状態です。
このエクセルデータを本社社員A君が、1つのデータとしてまとめるという操作を行います。
売上げ情報はテンプレートが用意されたエクセルを使っていて、そこにお店ごとにデータが入力されているって考えよう！
これを本社社員A君が、1つのデータとしてまとめたいって状況だね！
Dr.オフィス
- STEP
新規ブックをエクセルで作成する
ファイルをクリック 最初に「ファイル」をクリックしてください。
- STEP
空白のブックを選択する
空白のブックをクリック こちらの画面が表示されたら、空白のブックをクリックしてください。
- STEP
「データ」タブの「データの取得」から取得方法をクリック
ファイルから読み込み - 「データ」タブを開く
- 「データの取得」をクリックする
- 取得方法を確認されるので今回は「ファイルから」にポイントを合わせる
- 表示された中から「フォルダから」をクリックする
フォルダの中には社員A君が受け取った、支店事の売上げデータが入っているって考えてね！
Dr.オフィス
- STEP
該当のフォルダを選び「開く」をクリック
フォルダ選択 - 結合させたいエクセルが入ったファイルをクリックする（画像では「支店売上げ」）
- 「開く」をクリックする
- STEP
フォルダ内での選択されたデータが表示されるので「変換」をクリック
データ結合 - 「結合」をクリック
- 「データの結合と変換」をクリックする
- STEP
Power Queryエディターが開かれるので、必要な変換や整理作業を行う
変換作業を行うとこの画像が表示されるので、以下のように操作してください。
ファイル結合スタート - 「sheet1」をクリック（結合先のシート名で変わる）
- 「OK」をクリックする
これで、本格的な結合作業が始まるので、どのように表示されるのかを見てください。
ここからは、データの中身によって作業内容が異なってくるので、あくまでも1例となります。
結合されたデータを見て、特に問題が無いと感じた人は、以下に解説している作業は不要だよ！！
Dr.オフィス
- STEP
ファイル内容の確認
結合確認 まずは、上の画像にあるようにファイル名がきちんと表示されているか、複数ファイルがきちんと結合出来ているかを確認しましょう。
エラーが起きていると、この列の表示がなかったりファイル名がここで表示されないといった状態になります。
- STEP
データ名の修正
「東京.xlsx」や「大阪.xlsx」というデータ名をどこから引っ張ってきたデータなのか、それを表す列として活用したいので修正していきます。
データファイル修正 - 「Source.Name」をクリックして列を選択する
- 「ホーム」タブを開く
- 「値の変換」をクリックする
- 「検索する値」で不要な文字の「.xlsx」と入力する
- 「置換後」は空白にする
- 「OK」をクリックする
この作業で、「Source.Name」の列に表示されていた「.xlsx」が空白に置換され、そして消去されました。
- STEP
列の名前の変更
次は列の名前を変更して、わかりやすいものにしましょう。
データ列名変更 上の画像の赤い四角で囲まれた場所をクリックして、変更したい名称に変えてください。
ここでは支店名に変更するよ！
Dr.オフィス
- STEP
列の移動
支店列移動 列名を変更したら、その列を表示させたい場所にドラッグ＆ドロップで一番後ろに移動させます。
後は、他のデータを今までまとめてきたように、必要に応じて削除したり列名を変更してください。
- STEP
ステップ名称の修正
パワークエリ作業終了 後でどんな作業をやったのか見てわかるように「適用したステップ」に表示されるステップ名称の修正もしておくと、確認や修正が簡単になりますよ。
最終確認が終わったら、「Power Queryエディター」での作業は終了です。
- STEP
「ホーム」タブの「閉じて読み込む」をクリックし「閉じて次に読み込む」をクリック
パワークエリ終了 - 「ホーム」タブにある「閉じて読み込む」をクリックする
- 「閉じて次に読み込む」をクリックする
- STEP
「データのインポート」が表示されるので、自分が出力したい形にしてから「OK」をクリック
データのインポート - データの表示方法を4つから選ぶ（画像では「テーブル」）
- データを返す先を選択する（画像ではエクセルの左上から表示させたいので「=＄A＄1」）
- 「OK」をクリック
- STEP
データの完成
ここまでの作業が完了すると、複数あった売上げデータが一つに綺麗にまとめられて、とても見やすいエクセルに仕上がります。
エクセルファイル結合結果 東京以外の支店のデータもきちんとまとまっており、フィルターを使えば簡単に抽出出来るようになっています。
今回は実例で3つのデータを結合しました！
これが10個や100個と膨大でも作業内容は一緒ですね。
大量にデータがあっても、この作業で1つにまとめてフィルターを使った一発確認をすれば楽勝だ！Dr.オフィス
パワークエリで作ったテーブルを自動更新するには
パワークエリで作ったテーブルを自動更新する方法についても解説します。
使うデータは前回作成した売上げデータで、こちらをファイルを開いたタイミングで自動的に更新させるようにしますね！
- STEP
ファイルを開く
ファイルを開く まずは、目的のファイルを開きましょう。
こちらは社員A君が前回作成したエクセルになります。
- STEP
セルをクリックしタブを選ぶ
セルをクリック - パワークエリで作成した表をどこでもいいのでクリックする
- データタブを開いておく
- STEP
接続のプロパティをクリック
接続のプロパティ - 「すべてを更新」をクリックする
- 「接続のプロパティ」をクリックする
- STEP
「ファイルを開くときにデータを更新する」にチェックを入れる
「ファイルを開くときにデータを更新する」にチェック - クエリプロパティ画面が表示されるので、「ファイルを開くときにデータを更新する」にチェックを入れる
- 「OK」をクリックする
これでファイルを開くたびに最新データになってくれるよ！
社員A君が使った売上げデータが社員全員がアクセスできる共有データで、頻繁に更新されてても、これなら安心だね！Dr.オフィス
エクセルのパワークエリに関するQ＆A
Q
エクセルのパワークエリで作ったテーブルを自動更新にしたいんだけど、方法は？
A
Excelを開いたときに自動的に、元となったデータをチェックするように切り替えれば簡単にできます！
くわしくは≫パワークエリで作ったテーブルを自動更新するにはを参考にしてください。
Q
「エクセル2013」ではパワークエリは使えないの？
A
エクセル2013ではまだ標準装備されていないので、外部ツールとしてダウンロードしないと使えません！
マイクロソフトの公式ページからダウンロードして、インストールすれば使えるようになります！
Q
エクセルのパワークエリではPDFファイルは取り込めないの？
A
エクセル2021とMicrosoft 365のエクセルなら、PDFファイルでもパワークエリに取り込んで、表として表示させることが出来ます！
それ以前のバージョンだとテキストデータとして読み込むことは可能ですが、表をそのまま取り入れるのは難しいでしょう。
エクセルのパワークエリを使って業務を効率化しよう！
今回はエクセルのパワークエリについて、CSVからのデータ変換や複数ファイルの結合まで解説しました。
エクセルのパワークエリは使ったことがない人にとって難しい操作が多いですが、慣れるとCSVファイルの変換も簡単にできて効率的に作業を行えます。
最後に、エクセルのパワークエリの簡単な使い方についておさらいします！
- 新規ブックをエクセルで作成しておく
- 「データ」タブの「データの取得」から取得方法をクリック
- 該当のファイルを選び「インポート」をクリック
- プレビュー画面が表示されるので「データ変換」をクリック
- Power Queryエディターが開かれるので、必要な変換や整理作業を行う
- 「ホーム」タブの「閉じて読み込む」をクリックし「閉じて次に読み込む」をクリック
- 「データのインポート」が表示されるので、自分が出力したい形にしてから「OK」をクリック
元々はエクセルの拡張機能だったのに多くのユーザーに使われ、標準機能になったパワークエリは使いこなせたら便利な機能の1つです。
今回ご紹介した機能をマスターして、大量のエクセルファイルを同時に開いて行う面倒な作業や、CSVファイルを見てうまく変換できずにイライラするといった過去の自分から卒業してください！
CSVファイルのダウンロードで文字化けを起こす場合は、こちら≫エクセルでCSVファイルを文字化けさせずに開く方法を徹底解説！で解決できます。ぜひ参考にしてみてください。