本連載の第184回では「Power QueryでExcel作業を超効率化しよう」という話をお伝えしました。今回もこの強力なツール、Power Queryに焦点を当てて、その魅力についてお話します。
データの収集や集計、分析のためにExcelを使っている方にとって、関数やピボットテーブルと並ぶ強力なツールにPower Queryという機能があります。しかし、恐らく「聞いたことがない」という方がほとんどかと思います。かくいう私も今年に入って初めて知りました。
ある日、とあることがきっかけでPower Queryのことを知ったのですが、その時に受けた衝撃はすさまじいものでした。なぜなら、Power QueryがExcelでデータを扱う全ての人にとって、作業を大幅に効率化できる可能性があると感じたからです。
前置きが長くなりましたが、ここから「Power Queryを使うとどのような良いことがあるのか」をお伝えしていきます。なお、前回のコラムでは既に以下３つをお伝えしたので、本稿ではそれらを除く2つの利点について説明します。
前回お伝えしたPower Queryの利点
・複数ファイルのデータを取得して統合できる。
・WebサイトやPDFファイルからもデータを取得できる。
・取得元のデータの追加や削除、更新をボタン一つで反映できる。
1. データを綺麗に整えることができる
売上や商品などのデータを扱う際に、元のデータのままでは不都合があるというケースはよくあります。たとえば「氏名」について「日本 太郎」のように名字と名前が1つのセルに入っているのを「日本」「太郎」と別々の列に分離させなければならないときや、元データの行と列を入れ替えなければならないときなどです。
もちろん、このような操作は関数や従来のExcelの機能を使っても実現できます。先ほどのように氏名の間にスペースが入っているデータを分離させるのであれば、LEFT関数やRIGHT関数、FIND関数を組み合わせれば可能ではあります。しかし、関数に不慣れな人にとってはハードルが高い上に、元の列を残しておきつつ新たな列を追加しなければならず、表のサイズが大きくなっていまいます。
その一方、Power Queryでは「列の分割」という機能から「区切り記号による分割」を選び、区切り記号としてスペースを指定すれば簡単にスペースの前後の値に列を分けることが可能です。もちろん、コロンやコンマ、或いは自分で指定した記号などで区切ることも簡単にできます。
また、行と列を入れ替える操作についてですが、こちらもTranspose関数を使うことで可能ではあります。しかし、Transpose関数では元データの列数と行数に変更が生じた場合に対応しきれませんし、そもそもマニアック過ぎて扱える人も少ないのではないでしょうか。
関数を使わなくてもコピー＆ペーストする際に「形式を選択して貼り付け」→「行列を入れ替える」という操作でも対応はできますが、やはりTranspose関数と同様、貼り付けた後に表のサイズに変更があった場合は対応できないので、その場合には操作をやり直す必要があります。
それに対して、Power Queryでは「入れ替え」というボタンを押すだけで行と列を瞬時に入れ替えることが可能です。操作はたったそれだけです。また、元データの行数や列数に変更が生じた場合でも、Excelに読み込んだデータ上で「右クリック→更新」と操作するだけで全ての変更が自動的に反映されます。
これらはあくまでも例ですが、他にもデータを綺麗にするための機能が備わっているのでぜひ一度、ご自身の目で確かめてみてください。
2. 複数のデータを繋げて1つのデータに統合できる
さて、Power Queryの利点としてもう１つご紹介したいのはこちらの機能です。仮に手元に「顧客マスタ」と「商品マスタ」、そして「取引データ」という3つのエクセルファイルがあったとします。取引データには毎日の商品の売買取引が記録されており、その中に「顧客ID」と「商品ID」が含まれていたとします。
「顧客マスタ」には「顧客ID」に加えて「氏名」、「住所」、「メールアドレス」、「電話番号」が入っており、「商品マスタ」には「商品ID」と「商品名」、「商品カテゴリ」、「価格」が入っていたとします。
これらのデータを元に、「直近1カ月分の商品カテゴリAの、東京都での売上データを集計」しようとしたとします。そうすると、これまでなら「取引データ」ファイルを開き、「商品ID」をキーにして「商品マスタ」から「商品カテゴリ」を取得して表示、「顧客ID」をキーにして「顧客マスタ」から「住所」を取得・表示した上で、ピボットテーブルや関数で集計する、というやり方をするのではないでしょうか。もちろんこの方法でも可能ではありますが、「取引データ」は日々増えていくものなので関数で対応しようとしても限界があります。
しかし、Power Queryでは、「商品ID」や「顧客ID」をキーにして他のファイルからデータを取得してくることが簡単にできます。しかも、それが直感的に可能です。また、「取引データ」が増え続けても、「更新」ボタンを押せば即座に最新データを表示させることが可能です。
また、Power Queryで取得するデータを選択したり、データ同士を紐づけたりするときは元データそのものに変更を加えるわけではないので、誤って元データを削除したり変更したりするミスも防げます。
ここまでで、Power Queryについての利点を紹介してきましたが、いかがでしたでしょうか。ぜひご自身の業務でも使えないか、試してみていただければ幸いです。
