現在のビジネスにおいて、データが持つ重要性は大きいです。しかし、Excelで大量のデータの整理や変換を行うのは、相応の時間と労力が必要となるでしょう。その際にExcelに搭載されている機能「パワークエリ」を活用すれば、データの取得や変換などを簡単かつ効率的に行えます。
今回の記事では、パワークエリの概要や他のツールとの違い、自動化の方法などを解説します。
Power Query（パワークエリ）とは
パワークエリは、Microsoftが開発したデータ処理ツールです。Excel2016から標準搭載された機能の一つで、さまざまなデータの取得・接続や取得したデータの加工・整形などを自由に行えます。
パワークエリが搭載される前は、データの加工・整形に多くの時間を費やさなければなりませんでしたが、パワークエリであれば、データを取得するだけで簡単にデータの加工・整形が可能です。そのため、パワークエリを導入すれば大幅な業務改善が期待できるでしょう。定型的な内容は自動化できるため、作業時間の大幅な短縮も可能です。
パワークエリと他のツールの違い
以下に挙げる機能やツールは、パワークエリと似た役割をもっています。
- マクロ・VBA
- パワーピボット
- ACCESS
パワークエリとこれらの機能・ツールにはどのような違いがあるのか、詳しく見ていきましょう。
パワークエリとマクロ・VBAの違い
マクロは特定の動作を自動化できる機能、VBAはマクロを作成する際に使用するプログラミング言語です。VBAを使用すれば、ExcelやPowerPointなどのアプリケーションの機能を拡張できます。パワークエリとマクロは同様の処理が可能なため混同されがちですが、両者の大きな違いはVBAが必要かどうかです。
マクロはVBAで処理を記述しなくてはなりませんが、パワークエリはVBAが必要ないため、VBAの知識がなくとも操作できます。ただし、パワークエリでより高度な処理を行いたい場合は、M言語の習得が必要です。M言語を習得しておけば、構文やその他のルールがわかるようになるため、パワークエリに関するスキルの上達につながります。
また、マクロ・VBAにしかできない加工もあるため、両者の特性を理解して適切に使い分けることが理想です。
パワークエリとパワーピボットの違い
パワーピボットは、Excelで使用できるデータ分析ツールです。Excelにインポートしたデータをもとに、クロス集計表の作成やデータモデルの拡張、グラフの作成などを行えます。
パワークエリとパワーピボットは基本的に組み合わせて使用します。パワークエリで抽出・加工したデータをもとに、パワーピボットでテーブルやグラフを作成していくようなイメージです。パワークエリで行えるのはデータの加工までで、加工したデータの集計・分析にはパワーピボットが必要だということを把握しておきましょう。
パワークエリとACCESSの違い
ACCESSは、データベース管理ソフトです。データの入力や更新、検索などの機能があり、複数のテーブルを組み合わせて一つのデータを表現する、「リレーショナルデータベース」方式のデータベースを扱っています。一方、パワークエリはExcelやPower BIに内蔵されているデータ抽出・変換ツールです。
以上のようにACCESSはデータベースの作成・管理に長けたソフトのため、データの加工・整形を得意とするパワークエリとは使用用途が異なります。業務で使用する際は、パワークエリとACCESSのどちらが現在の業務に適しているのかを、判断できるようにしておきましょう。
パワークエリが主にできること
さまざまな機能を持つパワークエリを活用すれば、以下に挙げるような作業を行えます。
- データ変換の自動化ができる
- ExcelやCSV以外からもデータを取り込める
- 複数データの結合ができる
- 取り込んだデータの整形や加工ができる
それぞれ詳しく解説します。
データ変換の自動化ができる
データ変換を自動で行える点は、パワークエリの大きなメリットといえるでしょう。後述するデータの更新設定を行えば、元データに情報が追加されたときにパワークエリを更新するだけで、自動でデータ変換処理が実施されます。パワークエリはデータ変換処理を「クエリ」として記録しており、データが更新されたらその処理を自動で実行するような仕様となっているためです。
また、値の変換や文字列の変換なども自動化可能です。パワークエリ側でデータを変換したとしても、元データが書き換えられることはありません。
ExcelやCSV以外からもデータを取り込める
パワークエリが取り込めるデータはExcelファイルやCSVファイルだけではありません。ACCESSをはじめとするデータベースソフトや、PDFファイルからもデータを取得できます。Web上のデータや画像データも取り込めるため、業務の効率化に大いに役立つでしょう。
取得したデータはExcel形式に変換されるため、データの集計やグラフ化などが可能です。
複数データの結合ができる
パワークエリを使用すれば、複数のファイルからデータを取り込み、組み合わせられます。Excelでも同様の操作は可能ですが、Excelで複数データを結合するには「VLOOKUP」関数を使わなければなりません。とくにCSVファイルやACCESSファイルなど、違う形式のファイルを結合したい場合は、それぞれのファイルをExcel形式にしてからVLOOKUP関数を使う必要があるため、手間がかかります。
しかし、パワークエリならこのような操作やVLOOKUP関数を使うことなく、簡単に複数のデータを結合可能です。元データは結合前の状態で残っているため、誤って元データを削除したり、書き換えてしまったりといった事態も起こりません。
取り込んだデータの整形や加工ができる
パワークエリは、取り込んだデータの整形・加工して目的に応じた形に整理できます。データの整形や加工自体はExcelでも行えますが、パワークエリにはデータの整形や加工に特化した機能が備わっているため、VBAや関数で処理するよりも簡単です。
関数を記述するのではなく、マウスを使った操作のみで整形・加工処理が完結するため、VBAや関数の知識をほとんど必要としない点も大きなメリットといえるでしょう。
パワークエリで自動化する基本的な方法
パワークエリを使用すれば、データの更新や集計などの操作の自動化が可能です。次の章ではワークエリで自動化する基本的な方法として、取り込んだデータの更新を自動化するための設定方法を紹介します。
1.データを用意しパワークエリに取り込む
まずは、パワークエリに取り込みたいデータを指定しますパワークエリはさまざまなデータを取り込めますが、今回はExcelファイルを取り込む場合の手順を紹介します。
Excelファイルを取得するには、「データ」タブを開き「データの取得」をクリックします。データの取得先の選択肢が表示されるため、「ファイルから」→「Excelブックから」の順にクリックします。
次にExcelブックの一覧が表示されるため、取り込みたいExcelブックを選択して「インポート」をクリックします。
インポートをクリックすると選択したExcelブックのシートが一覧表示されるため、任意のシートを選択して「データの変換」をクリックします。
ここまでの操作を行うとパワークエリエディターが起動するため、「閉じて読み込む」をクリックすると、選択したシートをテーブルとして取得可能です。
2.取り込んだデータを整形する
取り込んだデータの整形はパワークエリエディターで行えます。データの変換や削除など、さまざまな編集が可能です。それぞれ手順が異なりますが、今回は表示したい列・行の選択および列・行を削除する手順を紹介します。
特定の列のみ表示したい場合は、「列の選択」をクリックします。
列の一覧が表示されるため、表示したい列をチェックして「OK」をクリックします。
以上の操作を行うと、チェックを入れた列のみが表示されます。
列を削除したい場合は、削除したい列を選択した状態で「列の削除」をクリックします。
特定の行を削除したい場合は、「行の削除」をクリックします。この操作では削除したい行の範囲を指定可能です。今回は「上位の行の削除」をクリックします。
次に、削除する行の数を入力し、「OK」をクリックします。
上から数えて指定した行数分のデータが削除されます。行を保持したい場合は、削除したい行を選択した状態で「行の保持」をクリックします。整形したデータは、「閉じて読み込む」をクリックすればExcelに出力されます。
3.整形したデータをExcelに出力する
読み込み先を変更したい場合は、「データ」タブの「クエリの表示」をクリックします。
変更したいクエリを右クリックし、「読み込み先…」をクリックします。
「テーブル」や「ピボットテーブル レポート」などの中から任意の読み込み先を選びます。
4.データの更新設定を行う
データの更新は「データ」タブから行えます。「すべて更新」→「接続のプロパティ」の順にクリックします。
クエリプロパティが開かれるため、「ファイルを開くときにデータを更新する」を選択します。以上の操作によって、ファイルが開かれたときにクエリが自動で更新されるようになります。関数を組み直す必要なく自動でデータが更新されるため、非常に便利です。
パワークエリを使う際の注意点
パワークエリは便利な機能を多数備えていますが、使用する際はいくつか注意点が存在します。
- Excelと同じ関数は使えない
- 3つ以上のテーブルを同時に結合はできない
- エディターを開いている場合にワークシート上の作業ができない
各注意点について、詳しく解説します。
Excelと同じ関数は使えない
先述したようにパワークエリの基本的な機能はマウス操作のみで行えますが、複雑な処理を行いたい場合はM言語を使用します。M言語はExcelで使われている関数とは違う言語のため、「SUM」や「COUNTIF」などのExcel関数は使用できません。
そのため、セル単位での操作や別シートの参照など、パワークエリのメニューにない処理を行いたい場合は、M言語の学習が必要です。M言語はPower BIにも使われているため、学習しておけばPower BIを活用する際にも役立つでしょう。
3つ以上のテーブルを同時に結合はできない
パワークエリは複数のテーブルからデータを抽出し、結合できますが、一度に結合できるテーブルは2つまでです。そのため、3つ以上のテーブルを結合する場合は、クエリのマージを複数回行わなくてはなりません。
ACCESSなら3つ以上のテーブルを結合できるため、2つのテーブル間でしか結合できない点はパワークエリのデメリットと言えます。
エディターを開いている場合にワークシート上の作業ができない
パワークエリは、エディターを開いている間、ワークシート上や別Excelファイルでの作業ができません。そのため、別のExcelファイルで操作をしたい場合は、都度パワークエリエディターを閉じる必要があります。
それほど大きなデメリットではありませんが、場合によってはエディターを立ち上げる、閉じるという作業を何度も繰り返すことになるでしょう。その結果、作業の保存し忘れといったミスにつながる可能性がある点は注意が必要です。
業務の自動化・効率化にはCELFもおすすめ
パワークエリを活用すれば、値の変換や文字列の変換などを自動化できます。これらの作業を手作業で行うのは相応の時間や労力が必要となるため、自動化のメリットは大きいです。ただし、パワークエリには少なからず注意点も存在するため、パワークエリを使う際は注意点をしっかりと把握しておく必要があるでしょう。
パワークエリを使用したいものの、今回挙げた注意点がどうしてもネックになる場合は、業務システム制作ツール「CELF」もおすすめです。CELFはExcelと同じ操作性で業務アプリを開発でき、開発したアプリを使用すれば業務の自動化・効率化を実現できます。先述した「Excelと同じ関数は使えない」、「3つ以上のテーブルを同時に結合できない」というパワークエリの注意点も、CELFなら解消可能です。
パワークエリの導入が難しい、またはパワークエリでは解消できない課題がある場合は、ぜひCELFの導入をご検討ください。
