スポンサーリンク
はじめに
ExcelのPowerQueryでは、PowerQueryエディターの中にて、「文字列○○を含む」「数値が○以上」などといった絞り込み条件を設定しておくことができる。
下図のように、通常のExcelのオートフィルタと同じ要領で設定できるわけだ。
ただそれは基本的に、一度設定した絞り込み条件を固定で設定しっぱなしにするものであり、状況に応じて気紛れに条件を変更しまくるのには適さない。
条件変更するにせよ、エディターを開いてからあれこれ操作するようなことは、PowerQueryについての予備知識がない一般ユーザに対してさせるべきではない。
抽出条件のテーブルを作っておく
今回の処理目標
では今回のサンプルとして、下図のようなテーブルにおいて
という処理をできるようにしてみる。
抽出条件用のテーブルを作成
ではまず、絞り込む文字列変数hogeを、別途用意したシートに入力する。
A1セルには「語句」などと書いた見出しを用意。この見出しは、見出しとして特別扱いするため、左右中央揃えのセル配置にしておこう。
次にA2セルには、文字列変数hogeのサンプルとして”田“とでも入れておく。
そしてショートカットキーCtrl+Tで、このセル範囲をテーブルに変換する。
作ったテーブルは、最初は「テーブル1」とかいう名前になっているが、これを必ず変更しておこう。
PowerQueryでテーブルを取り込んだ時、テーブル名がそのままクエリの名前になるからだ。
それを抜きにしても、テーブルとかシートの名前はすぐ変更しておくべきであるし。
今回は「抽出語句」というテーブル名称にしておく。
テーブルをPowerQueryに取込み
このテーブル「抽出語句」をPowerQueryに取り込む。
テーブルにカーソルを合わせておいた状態で
リボン「データ」タブ
→「テーブルまたは範囲から」
とすれば即座に取り込める。
これで、「抽出語句」という名前のクエリがPowerQueryの中に追加される。
これを後から使う。
クエリを作成
今回、元データとして使用する個人情報データサンプルファイルを、こちらに用意した。
同ファイルを自PCに保存してから、通常のPowerQueryの手順どおり
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」
として開く。
次に出てきた画面の左端から、シート名「データ」を選んで「データの変換」を押す。
ではここで、「名前」列を文字列絞り込みする操作を、実際にやってみる。
「名前」列の右端にある四角ボタンを押して「テキストフィルター」→「指定の値を含む」としよう。
そして、
文字列「田」を含む
条件を指定し「OK」を押す。
クエリを関数に変換
いま、
文字列「田」を含む
条件でフィルタリングするクエリ「データ」が作られたわけだが、そのクエリをPowerQueryの関数というやつに変換しよう。
クエリ「データ」を右クリックして「関数の作成」をクリックする。
PowerQueryには、元々用意された関数が沢山あるわけだけど、オリジナルの関数を追加するに当たり、クエリから変換する手段を使うわけだ。
というか、クエリから変換する以外のやり方でオリジナル関数を追加できるものなのかは、よく分からない。
クエリを関数に変換してどうなるのかって、正確な説明の自信がないけど、今回の例で言う文字列変数hogeみたいな変数をやり取りして使えるようになるということで良いかと思う。
「関数の作成」を押したら、「パラメーターがありません」とかいう警告メッセージが出るが、そのまま「作成」を押す。
関数の名前を付ける画面が出てくる。
今回はひとまず「名前フィルタ」という関数名にして「OK」を押そう。
クエリ「データ」から関数を作成したが、元々あったクエリ「データ」は削除してしまっても構わない。
作った関数は、クエリ「データ」の内容を忠実に受け継いでいるし。
詳細エディター
作成した関数「名前フィルタ」をクリックした状態で「詳細エディター」を開く。
ここでもなんか警告メッセージが出るけど、そのまま「OK」を押す。
詳細エディターには、「let」から始まる、こういうソースコードが表示されるはず。
変数の宣言
そして「let」の次に、図①の箇所で
ソース = () => let
とか書かれている。
括弧()の中身が空けられてて、矢印マーク(移動記号という) => が続いている。
この、中身が空いた括弧()の中に、文字列変数hogeを書き入れる。
文字列変数ということは、textという型の変数になるのだけど、それを
hoge as text
という形で書き込む。
これで、hogeという文字列型の変数を宣言するわけだ。
ちなみに、クエリを関数に変換したわけだが、変換前のクエリを詳細エディターで開いた場合は
ソース = () => let
の部分が無い。
要は関数ってことで、この中身を空けた括弧()に何か値を入れさせようということになったわけだ。
なお変数を複数使いたい場合は、
hoge as text , hoge02 as number
のように半角カンマで区切って列挙する。
変数を抽出条件に書き込み
次に、図②の箇所には、
文字列”田“を含む
という条件が何やら書かれているけど、この”田“も変数hogeに書き換えよう。
関数を使って語句抽出
では関数を作ったわけだけど、ここで最初にテーブルから作成した抽出条件のクエリ「抽出語句」を、左端の画面で選択する。
抽出条件のクエリの方を選択するというこれが、なかなか感覚的に馴染みにくいところなのだけど。
この「抽出語句」クエリに、元データを追加するのがミソだ。そのために
リボン「列の追加」タブ
→「カスタム関数の呼び出し」
とする。
出てきた画面で、列名の枠は放っておいて、下の「関数クエリ」欄をクリックすれば、先ほど作成した関数「名前フィルタ」を選べる。
そして一番下で、変数hogeにセットする項目を選ぶ。
今回は「抽出語句」テーブルの「語句」列をセットするわけなので、左の選択肢からは「列名」を、その右側の選択肢から「語句」を選ぼう。
そこまでセットしたら「OK」を押す。
項目を展開
「語句」列の右に、折りたたまれた「名前フィルタ」の列が追加される。
「名前フィルタ」列の右端の四角ボタンを押す。
「元の列名をプレフィックスとして使用します」のチェックは外して「OK」を押して、データを展開する。
これで、文字列”田“を含むデータのみが出てくる。
リボン「ホーム」タブ
→閉じて読み込む
でExcelシートに出力しよう。
抽出条件を変更してみる
ではテーブル「抽出語句」を作成していたExcelシートに戻り、今度はセルに「子」と書き入れてみよう。
そして
リボン「データ」タブ→「すべて更新」
とするか、
ショートカットキーCtrl+Alt+F5
で、データを更新してみよう。
すると、データが、文字列”子“を含むもののみに更新される。
これで、セルに抽出条件を入力することでPowerQueryの結果を更新できるようになった。
複数条件の指定
では次に、「語句」列に「子」「田」「本」と3つ条件を縦に並べて書いてみる。
そしてCtrl+Shift+F5 でデータ更新する。
すると、「子」「田」「本」それぞれの文字列を含むデータが全て出力される。
OR条件というやつで、複数条件を「または」条件で判定するわけだ。
この例では、名前「浜本 美智子」などは、「子」「本」の文字を両方含んでいて、2つの検索条件に合致するので、2回データ中に出現する。
そういう重複出現を防ぎたいなら、PowerQueryの画面で
リボン「ホーム」タブ→「重複の削除」
としておくと良いだろう。
スポンサーリンク
