- Excelのデータ集計をするときに書式を整え直したり、複数のExcelからVLOOKUPでデータを持ってくる作業が大変
- 毎月同じ作業をするからマクロを組みたいけれど、VBA(プログラミング言語)が分からない
- 書式の統一や置換
- データの結合
- VBAを使用せずマクロと同じ作業
- 値の置換
- 文字列の変換
- 複数Excelや複数シートからデータを連結
もくじ
Power Query(パワークエリ)で作業時間を98%削減
在庫管理セミナーの受講者やコンサル先のクライアントにPower Queryを紹介し、利用を強く進めています。 なぜなら、私自身、Power Queryを使って劇的な効率化を実感したからです。本当に感動しました。 定期的に作っているデータには、ぜひPower Queryを活用することを強くお勧めします。 私自身、毎回60分かかって、- 最新データをダウンロード
- 不要なデータを削除
- 管理項目を計算やIF関数などで追加
- 項目同士の結合(&でつなげます)
- VLOOKUP関数でつなげる
Power Query(パワークエリ)とは
Power Queryで簡単にできるようになるのは、次の2点です。- データの整形(計算式を足したりする)
- データクレンジング(集計や分析の時に除外しなければいけない、表記ゆれや、空白、エラーなどを除く処理)
Power Queryでできること
Power Queryでは大きく以下、4つの作業が可能です。- データ接続
- データの変換
- データの結合・分離
- 定期更新
データ接続
データ接続とは、パワークエリを使って加工したいデータを取得することです。 データ接続では、エクセルのほかに、以下の形式でデータ取得できます。- Excel/テキストファイル/CSV/PDF
- SQLサーバー/Microsoft Accessのなどのデータベース
- Web
データの変換
データの変換では、データ接続で取得したデータを使える状態にするために、変換・置換します。 このとき、元データ(変換・置換前のデータ)が直接書き換えられることは無いのでご安心ください。 この記事では、最も基本的な機能である「値の置換」と「文字列の変換」を紹介していきます。 他にもPower Queryでは色んなデータ整形機能がありますので、他の整形機能の一部を紹介します。- フィル:空欄を上の値で埋める作業
- 行や列の削除:不要な行や列を削除
- 書式の統一:大文字/小文字/トリミングなど
- 列の分割:あらゆる条件で文字を分割

データの結合・分離
複数ある表やシート、エクセル間のデータを組み合わせて、1つの表にする場合、VLOOKUP関数を使用しているのではないでしょうか?
実は、この作業もPower Queryで手間なく簡単に一括で結合できます。Power Queryが優れている点は、例えば結合したいデータベースが「CSV」「Excel」「アクセス」など混在しても、特別な加工操作は必要なく、手間なく簡単に結合できる点です。しかも、ファイルを開かなくてもVLOOKUP関数のように結合できるのです。エクセルで同じようなことをする場合は、
- ファイルを開く
- 必要なデータをダウンロード(CSV,アクセス等)
- データをエクセル化する(貼り付ける等)
- VLOOKUP関数でつなげる
定期更新
Power Queryはデータベースなので、元データの情報が追加されると自動更新される仕組みです。 また、元データの内容が変更されると、Power QueryからExcelへ表示した結果も変更される仕組みとなっています。 例えば、毎月の実績資料を別Excelで作って、VLOOKUP関数で1つのシートにまとめている・・・ といったような作業はPower Queryを使えば自動化できます。 あっという間に終わり、劇的に生産性があがります。
Power Queryが利用できるExcelのバージョン
Power Queryは2016年からExcelに実装された機能のため、お使いのExcelバージョンにPower Queryが対応しているか確かめておきましょう。
◆Windows|Excel 2016以降で使用する場合:Power Query実装済み
◆Windows|Excel 2013と 2010で使用する場合:Microsoft ダウンロードセンターでインストール必要。 ※2019年のバージョンを最終とし、更新されません。しかし古いバージョンでよければインストール&使用可能です。新しいPower Queryを使用したい場合はExcel 2016以降にアップグレードしてください。
◆Mac|Excel 2016と 2019で使用する場合:Power Query実装なし
◆Mac|Excel Microsoft 365で使用する場合:Power Query実装あり
Microsoft ダウンロードセンターにアクセスし、ダウンロードボタンをクリックしてインストールしていきます。
【活用例あり】Power Queryの使い方
今回は以下3つのExcelを使って、1つの表を仕上げていきます。 【使用するExcelファイル】- 品種マスタ
- 在庫マスタ
- 販売実績マスタ

- ExcelからPower Queryを立ち上げる方法
- データの取り込み
- 細かい操作方法
Power Queryの立ち上げ方
まず、空のExcelを開いてPower Queryを立ち上げる準備をします。 『1.Excelのツールバーより「データ」をクリック』します。
データの取り込み
今回は3つのExcelをデータベースとして取り込みます。 そのため、 『2.「データの取得」をクリック』し、 『3.「ファイルから」→「Excelブックから」を選択』してください。





データ変換・結合の操作方法
データを変換・結合する前に、取り込んだデータベースが正しく表示されているか確かめましょう。 今回、3つのExcelをデータベースとして取り込みましたが、下記のようにヘッダー(項目名)がおかしなことになっています。



- 値の置換
- 文字列の変換
- データの結合
値の置換
品種マスタの「ササニシキ」と「ななつぼし」の分類が誤って、もち米で登録されていることに気付きました。


文字列の変換
品種マスタの「品種名」と「分類」を結合させ、「あきたこまち|うるち米」と表示させます。 12.『「変換」をクリック』 13.『shiftキーを押しながら、結合させたい項目をクリック』 14.『「列のマージ」をクリック』 15.『文字を区切る線を選び、テーブル名(項目名)を新しく入力』する ※区切り線は半角で入力されるため見にくいです。「カスタム」を選択し、全角の記号を使うと良いでしょう。

データの結合
「品種マスタ」に「在庫マスタ」「販売実績マスタ」の情報を追加していきます。 16.『結合させたいクエリを選択』(緑色に表示させる) 17.『「ホーム」より「クエリのマージ」をクリック』


- 左外部:赤枠のレコードがすべて表示されて、一致する青枠のレコードのみを結合して表示
- 右外部:青枠のレコードがすべて表示されて、一致する赤枠のレコードのみを結合して表示
- 完全外部:両テーブルのレコードをすべて表示(共通項目で一致していても、不一致でも表示)
- 内部:両テーブルのレコードで一致するもののみ結合し表示
- 左反:両テーブルで一致しないレコードのうち、赤枠のテーブルのレコードのみ結合して表示
- 右反:両テーブルで一致しないレコードのうち、青枠のテーブルのレコードのみ結合して表示









パワークエリを使用する際の注意点
ここでは、Power Queryの使用する際の注意点について紹介します。 特に気を付けておきたいのは以下2点です。- Power Queryに取り込みたいデータはデータベース形式に直す
- Excel関数が使えない
- 3つ以上のテーブルを結合する場合は複数回の作業が必要
マクロ(VBA)との違い
「ExcelとPower Queryが違うというのであれば、マクロ(VBA)とは違うのか?」と疑問に持つ方がいらっしゃると思います。 結論からいうと、Power Queryはマクロ(VBA)と同様の処理ができます。 Power Queryを導入するメリットは、マクロで必要なVBA言語を使わなくても良い点です。 マクロは修正する際に、作った本人しか分からないという不具合が良く起こります。 しかし、Power Queryであれば、基本操作ができれば誰でも修正可能といったメリットがあります。 ※マクロ(VBA)にしかできない加工もあるので使い分けが必要です。
Power Queryを効率よく使う時のポイント
Power Queryの具体的な使い方を解説しましたが、効率よくPower Queryを使うには3つのポイントがあります。 Excelを使う上では当たり前の内容ですが、実際にExcelを正しく使えていない人は多いです。 これから解説する内容は仕事の効率化にも関わりますので、ぜひ参考にしてみてください。データはなるべくきれいに入力しておく
セルの書式は列ごとに統一させて入力しましょう。コピペ(ctrl + V)で値を貼り付けたとき、数値で貼り付けるべきところが文字列となっている場合があります。 数値と文字列が混在しているとExcelは計算できません。 複数人で1つのExcelを使用する場合は、コピペの仕方も周知し、入力するデータはなるべくきれいにしておきましょう。作業工数を減らすためにはデータ入力のルールを作る
手作業や関数を使用してデータを整えていた状況からPower Queryに変えるだけでも作業工数を減らせます。 しかし、さらに作業工数を減らすにはデータの入力ルールを作るのが効果的です。 ルール通りにデータが入力されていれば、Power Queryの作業も1つや2つ減らせる可能性があるのです。Excelを正しく使う
Excelは自由度の高い表計算ソフトです。 使い方に規制が無いので会社や個人で使いやすいように独自のExcelの使い方があるかもしれません。 しかし、効率的に業務を行いたければ、エクセルを充分に生かせるように、正しい使い方を知っておく必要があります。 よく企業からお聞きするのは、独自のやり方で作られたExcelを新しい担当が使いこなせず、在庫管理において欠品や納期遅延を起こしているという話です。 関数やマクロで上手く作られたExcelは、個人の使い方や考え方が強く反映されていて、ルールがありません。 エクセルが得意ないわゆる「エクセル職人」は、機能を使いこなせるため上手に加工して処理します。 そのため、その人以外だとさっぱり理解ができず、使いこなすには、新しい担当にも相応のExcel能力が求められます。 もし、「引継ぎ後に上手く使いこなせない」といった状況になっているのなら、エクセルの正しい使い方を学んで、今回解説したPower Queryをぜひ使ってみてください。 Power Queryのデータの並べ方はデータベース形式 エクセルで最優先で実践していただきたいのは、データの並べ方です。 エクセルは、行や列にどのようにデータを配置しても良いですが、データベース形式で並べるとエクセル職人がやるような高度な工夫や処理をしなくても、 エクセルの基本機能が生かしやすくなります。 特に、今回解説したPower Queryはデータの並べ方は、データベース方式と決まっています。
- フィールド(エクセルの列):データの管理項目
- レコード(エクセルの行):データの追加

- 新規の果物を追加する時、列方向(ヨコ)にデータが増えていく。
- 新しい管理項目を追加する時、行方向(タテ)に項目が増えていく。
- 各品目の毎日の入出庫数は、列方向(ヨコ)にデータを入力していく。
- 項目名が無い。
- データの追加や更新
- 修正
- 他のデータとの連携(結合や分離)
- 担当者の引継ぎ
Power Queryは作業効率化できるが生産性向上できる訳ではない
勘違いしないでいただきたいのは、Power Queryは作業効率化のために行う機能になります。 生産性向上とは、使った時間で価値を生み出していくことを表し、時短・効率化とは異なるのです。 ときどき勘違いされている企業の方は、DX(デジタルトランスフォーメーション)を今の業務をデジタルに置き換えることだと思われています。 それでは「導入したシステムをうまく使いこなせず、システムに踊らされて時間がかかるばかり」といったことにもなりかねません。 当社では、御社が抱える在庫管理トラブルを解決するために必要な知識と考え方の伝授、在庫管理DX化の手順をお伝えするセミナーを開催しています。 時代の変化が速い現代で、企業を生き残らせたいと思われる方は、ぜひご参加ください。
\在庫適正化!脱属人化!/
PowerQuery練習用ファイル
【無料ダウンロード】