- Excelのデータ集計をするときに書式を整え直したり、複数のExcelからVLOOKUPでデータを持ってくる作業が大変
- 毎月同じ作業をするからマクロを組みたいけれど、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では色んなデータ整形機能がありますので、他の整形機能の一部を紹介します。- フィル：空欄を上の値で埋める作業
- 行や列の削除：不要な行や列を削除
- 書式の統一：大文字／小文字／トリミングなど
- 列の分割：あらゆる条件で文字を分割
データの結合・分離
複数ある表やシート、エクセル間のデータを組み合わせて、１つの表にする場合、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 ダウンロードセンターにアクセスし、ダウンロードボタンをクリックしてインストールしていきます。
なお、今回の解説はoffice365を使った例です。（その他のバージョンの場合は、一部、画面が異なる場合もあります。）
【活用例あり】Power Queryの使い方
今回は以下3つのExcelを使って、1つの表を仕上げていきます。 【使用するExcelファイル】- 品種マスタ
- 在庫マスタ
- 販売実績マスタ
上記の表を作るにあたって、以下の作業手順を解説していきます。
- 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つ以上のテーブルを結合する場合は複数回の作業が必要
マクロ（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（マスタ―などに多い例）
- 新規の果物を追加する時、列方向（ヨコ）にデータが増えていく。
- 新しい管理項目を追加する時、行方向（タテ）に項目が増えていく。
- 各品目の毎日の入出庫数は、列方向（ヨコ）にデータを入力していく。
- 項目名が無い。
- データの追加や更新
- 修正
- 他のデータとの連携（結合や分離）
- 担当者の引継ぎ
Power Queryは作業効率化できるが生産性向上できる訳ではない
勘違いしないでいただきたいのは、Power Queryは作業効率化のために行う機能になります。 生産性向上とは、使った時間で価値を生み出していくことを表し、時短・効率化とは異なるのです。 ときどき勘違いされている企業の方は、DX（デジタルトランスフォーメーション）を今の業務をデジタルに置き換えることだと思われています。 それでは「導入したシステムをうまく使いこなせず、システムに踊らされて時間がかかるばかり」といったことにもなりかねません。 当社では、御社が抱える在庫管理トラブルを解決するために必要な知識と考え方の伝授、在庫管理DX化の手順をお伝えするセミナーを開催しています。 時代の変化が速い現代で、企業を生き残らせたいと思われる方は、ぜひご参加ください。
＼在庫適正化！脱属人化！／
PowerQuery練習用ファイル
【無料ダウンロード】