Power Query を使った重複行の削除

重複行の削除もしくはユニークなデータのリスト作成も実務で Excel を使うユーザーにとっては往々にして直面する課題である。

いくつかある重複行の削除

現在、重複行の削除で Excel 2007 以降に追加された データ タブ － データ ツールにある「重複行の削除」がもっとも紹介されている機能だろう。

表の重複している項目を削除する（Microsoft atLife）
http://www.microsoft.com/ja-jp/atlife/tips/archive/office/tips/002.aspx


たしかにこの機能により重複行を削除し、ユニークデータのリストの作成が可能なのだが、元データが更新されれば、同じ作業をし直さなければならず、この機能を活用するシーンは私自身はあまりない。正直、単純で汎用性が乏しく実務では応用した活用が難しいのだ。


一方、ピボットテーブルを使うことで元のデータが更新されても重複行を削除した表/リスト作成が可能だ。この方法であれば、元データが更新されてもピボットテーブルの「更新」をすることで対応できる。

ただし、注意したいのは、ピボットテーブル レポートのピボットテーブルは構造化参照可能なテーブルではない。ユニークデータリストを作って終わり、ではなく、そこから何らかの集計・計算・データ利用において、構造化参照によるテーブルの利用ができないため、他への再利用が難しい。テーブルではないことから、データの増減が発生したときの構造化参照テーブルのメリットも使えない。

上記の例ではデータカウントを取ることが目的ではない。逆にピボットテーブルなのでカウントの取得は簡単だ。しかし、このブログでも再三紹介しているように、現在そして今後 Excel はテーブル機能をベースにして拡張、新機能が追加されている。なるべくテーブルを使った課題解決方法を手にしておきたいところだ。

参考までに、もちろん、VBA を使った重複行削除のテクニックもある。

重複行を削除する（OfficeTANAKA）
http://officetanaka.net/excel/vba/tips/tips14.htm

VBA を使えば、重複行削除をしたユニークデータのリストを作成し、それをテーブルに変換することができる。

ただし今では VBA プログラミングをせずに「機能」だけで上記を実現できる。 それが Power Query だ。

Power Query を使った重複行の削除

Power Query については以前にも一部の機能を紹介しているが、そこでも述べたように Power Query はサーバーやクラウドからデータを Excel に取り込むことだけを目的としたアドインではない。Excel のテーブルもデータ ソースとして指定が可能だ。

そして、Power Query のクエリ エディターの「列の削減」で「重複部分の削除」の機能があるのだ。
これを使うことで、「重複行の削除」と同様のことができる。

もちろん、Power Query で取り込んだデータは Excel のテーブルとなる。構造化参照可能なユニークデータの「テーブル」となる。これでピボットテーブルの重複行削除でできなかったことが可能になる。

Power Query が Excel をデータ ソースに変える

しかし、本当に重要なのは、重複行の削除ができることではない。

データを Excel のテーブルとして持ち、Power Query を使うことで、Excel をまるで「データベース」のように扱うことができる、ということが重要なのだ。これが Power Query をすべての Excel ユーザーに勧めたい理由である。 重複行の削除はほんの一例でしかない。

蓄積されたデータの中から必要なデータを抜き出し、それを加工したい、分析したい、集計したい、というニーズは Excel ユーザーにとっては「通常」のことだと思う。

そしてその作業を「繰り返して」はいないだろうか。更新されたデータ（ワークシート、テーブルといったデータの固まり）を対象に、同じ条件で抜き出す、加工する、レポートを作る、といったことだ。

手作業でデータを抜き出すのであればオートフィルターを使っているだろう。そこから絞り込んだデータを他のワークシートやブックにコピーしていないだろうか。

実践ワークシート協会の VBA セミナー スタンダードを受けた受講者であれば、それら一連の作業を VBA で行うことができるだろう。

Power Query を使うことで、Excel のテーブルをデータ ソースとして指定し、抽出するための複数条件をクエリ エディターを使って設定し、結果を Excel のテーブルとして出力する、それらすべての設定を「クエリ」として保存し、再利用が可能なのだ。

そして、Power Query は条件を設定し抽出するだけではない。カスタム列の追加も可能なのだ。ピボット テーブルの集計フィールド、Power Pivot の集計列と同じだ。データ型の変換までできる。

以下のアニメーション GIF では、Data2 列の数値を文字列変換し、ゼロパディングで 1 を 001 にして Data 列の文字列と結合させ、文字列の Data3 を数値に変換している。列の順番も変更可能だ。このようなデータの操作・加工も Power Query で可能で、ある意味、元のテーブルからまったく別のテーブルを作っているようなものだ。

Power Query のクエリ エディタの式で使える関数はワークシート関数でもなく、Power Pivot の DAX 関数でもない。しかし、マイクロソフトが公開している記事を参考にしながら Excel のワークシート関数の知識で試してみればその使い方はわかると思う。重要なのは全く別のものでなく、かぶっていることが多い、だから、Excel ユーザーであれば「わかる」だろう、想像してみよう、ということだ。

上記のアニメーション GIF でも、関数の Number.ToText の引数として、"000" を入れたのはワークシート関数の TEXT 関数の知識からだ。それで期待通りの動きになるのはさすがマイクロソフトと言わざるを得ないだろう。

少しでもこの情報が参考になれば幸いである。