- 毎日のデータ集計作業に時間がかかりすぎる
- 手作業での生データ加工は時間もかかるし、ミスが多くて困っている
- もっと効率的にデータ分析を行いたい
このように、日々のデータ業務に悩みを抱えていませんか？ システムには日々生データが大量に蓄積されますが、データ活用次の加工作業が膨大になり、担当者の大きな負担となっています。
手作業による集計や加工作業は、時間と手間がかかるだけでなく、ヒューマンエラーを引き起こす原因にもなりかねません。
そこでこの記事では、Excelの標準機能でありながら、データの収集・変換・結合といった作業を自動化できる「Power Query（パワークエリ）」の基本的な使い方と手順をサンプルファイルで初心者の方にも分かりやすく解説します。
私がこの機能初めて知って使ったときは、今まで1時間くらいかかっていたデータの加工作業が、生データを取り込んで少し待つだけ、わずか３分で終わるようになりました。
この記事を読んで、Power Queryの強力な機能を理解し、日々のデータ作業を劇的に効率化できるようになります。面倒な手作業から解放され、より付加価値の高い分析業務に時間を使いましょう。
- Excelのデータを作るとき、生データから不要なデータや行を削除したり、計算項目を追加したりしている。
- 複数のExcelからVLOOKUPでデータを統合する作業が大変。
- 毎月同じ作業をするからマクロを組みたいけれど、VBA（プログラミング言語）が分からない。
全てExcelの標準機能である「Power Query（パワークエリ）」を使えば、以下の作業が簡単にできるのです。
- 不要なデータの削除
- 必要な列の追加や計算式の構築
- 書式の統一や置換
- 2つ以上の生データの結合
- VBAを使用せずマクロと同じ作業
一度、Power Queryを作れば、生データを入れるだけで、面倒な作業をエクセルがあっという間にやってくれるので、作業時間を大幅にカットできます。
しかし、Power Queryはくせ者で、通常のエクセルとちょっと使い方が違います。
仕組みを理解していないと便利さが感じられません。
そこで今回は1つずつ丁寧に解説していきますので、ゆっくり読み進めていってください。
この記事で使ったデータがダウンロードできるので、ぜひサンプルを使って実際にパワークエリを動かしてみてください。
ほかにもたくさん在庫管理に役立つフォーマットも入手できます！
なお、この記事ではPower Queryの基本的な使い方に加えて、以下の機能について紹介しています。
- 値の置換
- 文字列の変換
- 複数Excelや複数シートからデータを連結
目次
まずはPower QueryとExcelの違いを説明します。
Power Query（パワークエリ）で作業時間を98%削減
在庫管理セミナーの受講者やコンサル先のクライアントにPower Queryを紹介し、利用を強く進めています。
なぜなら、私自身、Power Queryを使って劇的な効率化を実感したからです。本当に感動しました。
定期的に作っているデータには、ぜひPower Queryを活用することを強くお勧めします。
私自身、毎回60分かかって、
- 最新データをダウンロード
- 不要なデータを削除
- 管理項目を計算やIF関数などで追加
- 項目同士の結合（＆でつなげます）
- VLOOKUP関数でつなげる
ということをしていましたが、Power Queryの導入後は、最新データを読み込むだけなのでわずか1.5分で終了です。
作業時間を97.5%も削減できました。
Power Query（パワークエリ）とは
Power Queryで簡単にできるようになるのは、次の2点です。
- データの整形（計算式を足したりする）
- データクレンジング（集計や分析の時に除外しなければいけない、表記ゆれや、空白、エラーなどを除く処理）
Excelの「取得と変換」の機能はデータベース扱いで、取得されたデータはExcelの外部に持っています。そして、作業結果をExcelに表示しているのです。
そのため、Excelの「取得と変換」は「データ取得とデータ変換」を目的にしているので、表計算する目的を持っていません。
それでは次に、Power Queryでできる作業やPower Queryのできる事、使用する際の注意点、マクロとの違いを紹介します。
Power Queryでできること
Power Queryでは大きく以下、4つの作業が可能です。
- データ接続
- データの変換
- データの結合・分離
- 定期更新
上記4つの作業でどのようなことができるか簡単に紹介します。
データ接続
データ接続とは、パワークエリを使って加工したいデータを取得することです。
データ接続では、エクセルのほかに、以下の形式でデータ取得できます。
- Excel／テキストファイル／CSV／PDF
- SQLサーバー／Microsoft Accessのなどのデータベース
- Web
ここで注意！
Webからのデータ取得はWebスクレイピングといって、Web上で公開されている情報を抽出する技術を意味します。
Webスクレイピングで抽出するデータが著作権法対象となっている場合がありますので、Webからデータ取得する際は、作業前にサイトの注意事項を確かめておきましょう。
データの変換
データの変換では、データ接続で取得したデータを使える状態にするために、変換・置換します。
このとき、元データ（変換・置換前のデータ）が直接書き換えられることは無いのでご安心ください。
この記事では、最も基本的な機能である「値の置換」と「文字列の変換」を紹介していきます。
他にもPower Queryでは色んなデータ整形機能がありますので、他の整形機能の一部を紹介します。
- フィル：空欄を上の値で埋める作業
- 行や列の削除：不要な行や列を削除
- 書式の統一：大文字／小文字／トリミングなど
- 列の分割：あらゆる条件で文字を分割
なお、不要なセルを削除して左側、上側に数値をスライドさせる場合はPower Queryが使えません。
理由は、Excelの最小単位はセルですが、Power Queryの場合はレコード（1行）が最小単位となるからです。
そのため、「1つのセルを削除して、数値が入力されたセルを移動させる」といった考え方はないと認識しましょう。
データの結合・分離
複数ある表やシート、エクセル間のデータを組み合わせて、１つの表にする場合、VLOOKUP関数を使用しているのではないでしょうか？
実は、この作業もPower Queryで手間なく簡単に一括で結合できます。Power Queryが優れている点は、例えば結合したいデータベースが「CSV」「Excel」「アクセス」など混在しても、特別な加工操作は必要なく、手間なく簡単に結合できる点です。しかも、ファイルを開かなくてもVLOOKUP関数のように結合できるのです。
エクセルで同じようなことをする場合は、
- ファイルを開く
- 必要なデータをダウンロード（CSV,アクセス等）
- データをエクセル化する（貼り付ける等）
- VLOOKUP関数でつなげる
といった手間のかかる作業を毎回しなければいけません。
また、不要な列を一括削除する機能もあります。
Power Queryが優れているのが、元データが全て加工前の状態（結合前・削除前）で残っていることです。
Power Queryは、データを使って加工するだけで、元々のデータ自体を上書きしません。
定期更新
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 ダウンロードセンターにアクセスし、ダウンロードボタンをクリックしてインストールしていきます。
なお、今回の解説はoffice365を使った例です。（その他のバージョンの場合は、一部、画面が異なる場合もあります。）
【活用例あり】Power Queryの使い方
今回は以下3つのExcelを使って、1つの表を仕上げていきます。
【使用するExcelファイル】
- 品種マスタ
- 在庫マスタ
- 販売実績マスタ
Power Query練習用のエクセルファイルをダウンロードする
【完成形のエクセル表】
上記の表を作るにあたって、以下の作業手順を解説していきます。
- ExcelからPower Queryを立ち上げる方法
- データの取り込み
- 細かい操作方法
それでは順に解説していきます。
Power Queryの立ち上げ方
まず、空のExcelを開いてPower Queryを立ち上げる準備をします。
『１．Excelのツールバーより「データ」をクリック』します。
次に、一覧表を作るのに必要なExcelを取り込んでいきましょう。
データの取り込み
今回は3つのExcelをデータベースとして取り込みます。
そのため、
『２．「データの取得」をクリック』し、
『３．「ファイルから」→「Excelブックから」を選択』してください。
なお、Excel以外にも下記の様々なデータソースからデータを取得できます。
次に、
『４．取り込みたいExcelを選択し、「インポート」をクリック』してください。
そして、
『５．取り込みたいExcelのシート名を選択』します。
ここでは3シートのデータを取り込むので、「読み込み」をクリックします。
すると、下記の緑色部分が読み込まれました。
※緑色部分のデータは元ソースのExcelと連動しているため、元ソースの行数が増減すれば緑色部分のデータも増減します。
データ取り込み作業を必要なExcelシートすべて行ったら、次は「データ変換」等について解説していきます。
なお、販売実績や在庫実績のように同じ項目で数字だけが変わっている場合、1つのフォルダに月ごとのExcelを保存しておくと便利です。
下記のように1つのフォルダ内にExcelをまとめ、データ取得方法を「ファイルから」→「フォルダから」にすれば、4月〜7月までのデータを1つのデータベースとしてまとめてくれます。
さらに、8月のデータが追加されたらフォルダへ8月のデータを保存します。それだけで4月～8月のデータを1つにまとめてくれるのです。
データ変換・結合の操作方法
データを変換・結合する前に、取り込んだデータベースが正しく表示されているか確かめましょう。
今回、3つのExcelをデータベースとして取り込みましたが、下記のようにヘッダー（項目名）がおかしなことになっています。
そのため、まずはヘッダーのエラーを直していきましょう。
『６．「データ取得」をクリック』し、
『７．「Power Queryエディタの起動」をクリック』します。
すると、下記のエディタが出てきました。
これから下記のエディタを使って変換や結合作業をしていきます。
では、『８．「1行目をヘッダーとして使用」をクリック』してください。
「閉じて読み込む」をクリックすれば、ヘッダーを正しく表示させられます。
実際に作業に入る前に、下記画像でPower Queryエディタの解説をしておきましょう。
「適用したステップ」は行った作業名が記録されます。VBAを書かずに作業名を記録してくれるため、「F2」を押して分かりやすい作業名に変更しましょう。
「クエリ」は取り込んだデータ名を表示します。Excelのシートが「sheet1」となっていた場合は、こちらも「F2」を押して名前変更が可能です。
それでは実際に
- 値の置換
- 文字列の変換
- データの結合
を行っていきます。
値の置換
品種マスタの「ササニシキ」と「ななつぼし」の分類が誤って、もち米で登録されていることに気付きました。
これから、もち米→うるち米 へ変換していきます。
『９．間違いを見つけた列をクリックして選択』（緑色に表示させる）
『１０．「値の置換」をクリック』
『１１．置換したい文字を入力』し、OKをクリック
下記のように、すべて「うるち米」に置換されました。
※この時、元ソースのExcelは変更されません。そのため元ソースのExcelは「もち米」のままですので、注意してください。
文字列の変換
品種マスタの「品種名」と「分類」を結合させ、「あきたこまち｜うるち米」と表示させます。
１２．『「変換」をクリック』
１３．『shiftキーを押しながら、結合させたい項目をクリック』
１４．『「列のマージ」をクリック』
１５．『文字を区切る線を選び、テーブル名（項目名）を新しく入力』する
※区切り線は半角で入力されるため見にくいです。「カスタム」を選択し、全角の記号を使うと良いでしょう。
すると、下記のように変換されました。
このとき、元の「品種名」と「分類」は非表示になります。
それでは次に、「在庫マスタ」「品種マスタ」「販売実績マスタ」を結合させ、一覧表を作っていきます。
データの結合
「品種マスタ」に「在庫マスタ」「販売実績マスタ」の情報を追加していきます。
１６．『結合させたいクエリを選択』（緑色に表示させる）
１７．『「ホーム」より「クエリのマージ」をクリック』
すると、下記のような画面が出ます。
この画面を解説していきます。
下記画像をご覧ください。
赤枠が「品種マスタ」で基礎となるデータになり、青枠がこれから追加する「在庫マスタ」のデータです。
１８．『追加したいテーブルを選択』
１９．『両データに共通する項目をクリックし、緑色に表示』し、OKをクリック
なお、結合の種類で出てくる用語は以下の通りです。
- 左外部：赤枠のレコードがすべて表示されて、一致する青枠のレコードのみを結合して表示
- 右外部：青枠のレコードがすべて表示されて、一致する赤枠のレコードのみを結合して表示
- 完全外部：両テーブルのレコードをすべて表示（共通項目で一致していても、不一致でも表示）
- 内部：両テーブルのレコードで一致するもののみ結合し表示
- 左反：両テーブルで一致しないレコードのうち、赤枠のテーブルのレコードのみ結合して表示
- 右反：両テーブルで一致しないレコードのうち、青枠のテーブルのレコードのみ結合して表示
データの結合は「ベースとなる情報」に「必要な情報」を付け加えていくように作るので、左外部を選択すれば良いでしょう。
出たデータを見てみると項目が「在庫マスタ」となっており、在庫数が表示されていません。
そこで、在庫数を表示させていきます。
２０．『「在庫マスタ」のプルダウンをクリック』
２１．『表示させたい項目を選択』し、OKをクリック
すると、在庫数が表示されました。しかし、テーブル名（項目名）が分かりにくくなっているので、「F2」を押して修正します。
同様の手順で「販売実績マスタ」の「販売数」を結合させました。
すると下記画像のように、同品種コードで複数の販売数があるため、販売数ごとに表示されてしまいます。
これでは品種コードも在庫数も重複してしまいます。
そこで「販売数」は合計し、それ以外のデータは1行で表示していきましょう。
２２．「ホーム」より『「グループ化」をクリック』
次に、「詳細設定」をクリックし、
２３．『グループ化させたい項目を選択』
※今回は「品種コード」から「在庫」までを表示させたかったので、「グループ化の追加」で項目を増やしました。
２４．『集計する項目を入力』
集計する項目は「販売数」です。
現在、日別ごとに表示された販売数を、品種コードごとに合計させます。
このとき、集計結果の列名を「新しい列名」に入力しなければなりません。
上記のように入力すると、無事結果が表示されました。
10行目「ササシグレ」の合計販売数が「nul」で表示されています。もし「0」で表示させたければ、値の置換を利用しましょう。
最後にPower QueryエディターからExcelへデータを取り込む方法は、
２５．『「ホーム」より「閉じて読み込む」をクリック』して完了です。
Excelに出てきた結果は下記のようになります。
Power Queryの作業は以上になります。
1回だとわかりにくいと思いますので、実際に手を動かしながら何度か読み返してみてください。
私自身、Power Queryを使ってみて、本当に感動しました。もっと早く知っておけば・・・と思ったくらいです。
パワークエリを使用する際の注意点
ここでは、Power Queryの使用する際の注意点について紹介します。
特に気を付けておきたいのは以下2点です。
- Power Queryに取り込みたいデータはデータベース形式に直す
- Excel関数が使えない
- 3つ以上のテーブルを結合する場合は複数回の作業が必要
Excel関数が使えない
Power Queryはエクセル関数が使えません。
Power Queryには専用の「M言語」というものが用意されています。
ただし、簡単な操作であればM言語を使わなくても、基本的なPower Queryの機能は使えますので、ご安心ください。
（M言語を使えば、Power Queryでより複雑な処理でデータ整形ができます。）
３つ以上のテーブル結合は複数回の作業が必要
一度に結合できるのは2つまでです。
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はデータの並べ方は、データベース方式と決まっています。
データベース形式とは、上記のような図でもつことです。
- フィールド（エクセルの列）：データの管理項目
- レコード（エクセルの行）：データの追加
エクセルを使っていて、よくある間違いは次のような例です。
NG例：パターンA（マスタ―などに多い例）
- 新規の果物を追加する時、列方向（ヨコ）にデータが増えていく。
- 新しい管理項目を追加する時、行方向（タテ）に項目が増えていく。
NG例：パターンB（入出庫表や在庫管理表に多い例）
- 各品目の毎日の入出庫数は、列方向（ヨコ）にデータを入力していく。
- 項目名が無い。
上記のような場合は、パワークエリを使うことはできません。
ちなみに、在庫管理の現場で多いのはパターンBです。このような形式の場合は、パワークエリは使えません。
また、データの加工や分析もしづらいため、このような形式である場合は、用途は限定されます。
使いやすい、良かれと思ってやっているこのようなエクセルの使い方が、現場の生産性を下げ、デジタル化を阻害している最も大きな原因です。
この他にもデータを使う際には基本的な作法があります。
作法を知らなくてもエクセルでは何とかなりますが、
- データの追加や更新
- 修正
- 他のデータとの連携（結合や分離）
- 担当者の引継ぎ
といったことをしようとしたときに、無駄な労力が必要になります。
できる限り、作業時間を減らすこと、どんな人でも使えるようにすることで、効率化・コストダウンが図れます。
Power Queryの作業効率化を専門家から学ぶ
今回の解説記事をもとにPower Queryを使いこなせば、必ず作業効率化を実現できるでしょう。
自社のデータ分析作業の効率化を専門家から学びたい！という場合は、在庫管理110番が行っているエクセルだけでできる！中小企業のためのデータ分析研修がお勧めです。
研修では、実際にあなたの会社のデータを使うため、作業の効率化に直結します。さらに、データの読み方も学べます。
