「Excelの関数やピボットテーブルを使ったデータの操作を覚えたが、まだまだ不便なことが多い。もっと便利な方法を知りたい」
Excelの中級者レベルの方は、日頃このように感じながら仕事をしているのではないでしょうか？
2020年時点で、比較的新しいExcelの機能である「PowerQuery」について紹介します。Excel2016以降のバージョンを対象としています。
この記事を書いている私は、約1年前にPowerQueryの学習と実務での活用を進めてきました。実際に手を動かしてきて、いろんな気付きがありました。貴重な体験が出来たので、忘れないためにここに記しておきたいと思います。
まだまだ習得できていない機能もありますが、ここではPowerQueryを使ったことのない人向けに、大まかな内容を知って頂くことを目的としました。
PowerQueryの３つの特長
PowerQueryの特長は以下の通りです。
- 外部から様々なデータを取得できる
- 様々なデータの加工を行える
- データ集計やグラフ機能との相性が抜群
外部から様々なデータを取得できる
PowerQuery機能はデータの取得から始まります。
データタブの「データの取得と変換」というメニューがありますが、取得したいデータの種類を選べます。
ファイルやデータベース、WEBやクラウドシステムなど、とにかく様々な種類のデータを取得できます。世の中にある全てのデータを取り込めるのではないかというくらいです。
基幹システムなどで特定の集計を行いたいけれども、
- 紙で一旦レポートを印刷しないといけない
- コピペやデータの再入力をしないといけない
という、無駄な作業を強いられて、痒いところに手が届かない思いをされたことがどこかであると思います。
データの取得を活用することで、このような無駄な作業から解放されます。
私の場合は、カレントブックのデータを取り込むための「デーブルまたは範囲から」と、「Accessデータベース」からの取得を主に使っています。
その他の基幹システムのデータが加工しやすいものであれば、直接データベースサーバーから取得するのもアリです。
様々なデータの加工を行える
データを取得した時にPowerQueryエディタというウィンドウが必ず開きますが、ここで様々なデータの加工を行えます。
- 複数のデータを連結して新しい表を作る
- 空白行や不要な列を削除する
- 特定の値を探して別のデータに自動変換する
- 横持ちのデータを縦持ちに変換する
他にもいろいろな加工を行えますが、従来であればプログラミングやデータベース言語などが必要であったものが、分かり易いメニュー画面から選んで実行できます。
データ集計やグラフ機能との相性が抜群
データを有効に活用するためには、加工されたデータを分かり易く集計したり、グラフなどで視覚化しないといけません。
PoewrQueryで加工されたデータは、PowerPivot(パワーピボット)で集計や視覚化を簡単に行うことが出来ます。PowerPivotは、従来のピボットテーブルやピボットグラフを更に強化したものです。
PowerQueryとPowerPivotは相性抜群で、ほとんどがセットで使われます。
スライサーを設置すれば、ワンクリックでデータを簡単に切り替えて様々な分析に活用することが出来ます。
更に、PowerPivotを印刷などのレポートに特化した「PowerView」というものもありますが、Excelのバージョンによって入手する方法が異なるようです。
PowerQueryの問題点は、入力データを変えたら更新作業を行わないと出力データが変わらないことです。パソコンのスペックによっては、更新に時間がかかる場合もあるでしょう。
しかし、更新の問題点を差し引いても自前で様々なデータ加工が簡単に出来るメリットは大きいので、PowerQueryを積極的に使う価値はあります。
PowerQueryを設定してみる
試しにカレントブックからデータを取得して、PowerQueryを設定してみましょう。
データテーブルを右クリックし、「テーブルまたは範囲からデータを取得する」を選びます。
PowerQueryエディタが起動します。
エディタをそのまま閉じます。警告表示は「保持」をクリックします。
新しいシートにテーブルが作成されます。「クエリと接続」で、新しいクエリが作成されています。
新しく作られたクエリを右クリックし、「読み込み先」を選びます。
「接続の作成のみ」を選び、「このデータをデータモデルに追加する」にチェックを入れます。PowerQueryを設定するときにここが一番重要な設定になります。必ずチェックを入れるようにしてください。
警告表示が出ますが、そのままOKをクリックします。
シートのテーブルが削除されましたが、クエリは残っています。
今回加工したクエリからピボットテーブルを作ることが出来ます。
ピボットテーブのフィールドでは、テーブルとは違うアイコンが表示されています。
これがクエリのアイコンです。
以下は設定を表示するために覚えておくべき手順です。
クエリを右クリックして編集を選ぶと、そのクエリのPowerQueryエディタを表示することができます。
また、データタブの「クエリと接続」をクリックすることで、右側の「クエリの接続」の表示を出したり消したりできます。
PowerQueryエディターの操作方法について
PowerQueryエディタは、Excelの機能とは違うので操作に戸惑うことがあるかもしれません。
しかし、メニュー画面はExcelと同じようなリボンで操作が出来ます。ほとんどのデータの操作については、リボン内で行うことが出来ます。
リボン内のタブは５つありますが、データの操作に使うのは「ホーム」「変換」「列の追加」の3つです。
「列の追加」では、関数を使って新しい列を設けることが出来ます。
四則演算の場合は、Excelワークシートと同じように計算を行うことができますが、その他はPowerQuery専用の関数を使います。Excelワークシートとは関数が異なるので注意が必要です。
PowerQueryエディターの右端に「クエリの設定」というメニューがあります。
「適用したステップ」では、データの処理内容が順番に表示されています。順番を入れ替えて調整することも出来ます。
ホームタブから「詳細エディタ」をクリックしてみます。
「let」から始まる式が表示されますが、これは「適用したステップ」と連動しています。
リボンの操作では行えない処理を、この詳細エディタで行うことが出来ます。
詳細エディタは「Power Query M 」という言語で書かれています。
マイクロソフトの公式サイトにリファレンスがありますので、詳細エディタで直接編集したい方にとっては必見のページです。
Power Query M 数式言語のリファレンス - PowerQuery M | Microsoft Docs
カレントブックでもPowerQueryを使うメリット
本記事ではカレント(今使用してる)ブックでPowerQueryを設定しましたが、外部からのデータについても同じように取得した直後にエディタが開き、クエリが作成されます。
ここで、「外部のデータをつなげて集計する価値はありそうだけども、カレントブックでもPowerQueryを設定する意味はあるのかな？」という疑問を抱える方もいらっしゃるかもしれません。
PowerQueryを使うことで、複数の表の連結やグループ化など、Excelワークシート関数では手間のかかる処理を簡単に行うことが出来ます。
ピボットテーブルでのカスタマイズ性も大きく高まります。
ワークシート関数では行や列がズレて困ったこともあるとは思いますが、そのようなリスクを減らすこともできますので、カレントブックでもPowerQueryを使う価値は十分にあります。
まとめ
このように、PowerQueryを知ることで、今までのExcelとは違った使い方を行うことが出来ます。
ここでは、クエリの作り方やメニューについて簡単に触れた程度でしたが、マスターするための一番の方法は、書籍を購入して順番に手を動かしてみることです。
様々な書籍がありますが、一番のおススメは「Excelパワーピボット 7つの7つのステップでデータ集計・分析を「自動化」する本」です。
データの取り込みから出力データの作成までが分かり易く書かれています。サンプルファイルもダウンロード出来ますので、パワークエリ・パワーピボットで作ったダッシュボードを操作する感覚が分かります。
発売から1年経過した2020年9月現在でも売れ筋の本のようです。私も、スライサー自体をこの本で知ることが出来ましたし、食わず嫌いだったピボットグラフを使いこなせるようになりました。
こちらにデータ分析を行う人向けのおススメ書籍をまとめましたので、ご覧ください。
最後に、著者の鷹尾祥さんが、本のなかでパワークエリの機能について記載されている文を抜粋してこの記事を終わります。
このパワークエリとパワーピボットを組み合わせれば、従来ソフトウエアとハードウエアの両面からIT部門の支援を得なければ成し遂げられなかったレポートが、もはやExcelファイル1つで誰でも作ることができるからです。
最後まで読んで頂きありがとうございました。
(2021年2月21日追記)
2021年2月19日に「Excelパワークエリ データ収集・整形を自由自在にする本」が発売されました。
よりパワークエリに特化して書かれた良書になっています。
こちらの記事で書評を書いていますので、よかったらご覧ください。
