【中級編】【PowerQuery】IF文・関数を駆使して自在に文字を抽出しよう!

文字を抽出してみよう!

抜出し方を整理する

かじむー

まずはデータの状態を見て考え方を整理しよう!

このカテゴリ列から最下層のカテゴリを抜き出したい!
「お菓子_洋菓子」なら「洋菓子」
「食品_乳製品-チーズ」なら「チーズ」

ただしこのカテゴリには以下の様な
2つの特徴があります。

パターン①:カテゴリが2階層(大・中)の場合
アンダーバーで区切る(例:お菓子_洋菓子)
パターン②:カテゴリが3階層(大・中・小)の場合
3階層目をハイフンで区切る(例:食品_乳製品チーズ)

考え方を整理する

●パターン②(食品_乳製品チーズ)について

もしカテゴリ列に「 – 」を含むなら
「 – 」直後の文字から最後までを抜き出す

●パターン①(お菓子_洋菓子)について

もしカテゴリ列に「 – 」を含まないなら
「 _ 」直後の文字から最後までを抜き出す

IF文とText.Containsを使って場合分け

Text.Containsという関数は
特定の文字を含むか?含まないか?を判定できる関数です。

カテゴリ列に “-” 含むかどうかを判定するには
以下の様な書き方をします!

PowerQuery関数
=Text.Contains([カテゴリ], "-")

そうすると
含むものは「True
含まないものは「False
と返ってきます!

という事は
Text.Contains関数条件式そのものなので

IF文を使って
「 – 」含む場合は “yes”
「 – 」含まない場合は “no”
という処理を書いてみると・・・

PowerQuery関数
=if Text.Contains([カテゴリ], "-") then "yes" else "no"

きちんと “yes” “no” と出力されましたね!

では “yes” の部分をきちんと作っていきましょう!

Text.Endで文末から文字抽出

Text.Endという関数は
指定した文字数を文末から抜き出す関数です。

例えば
カテゴリ列の後ろから3文字だけを抜き出す場合は

PowerQuery関数
=Text.End([カテゴリ], 3)

このように書きます!

今 “yes” の部分を作りこんでいるので
この式を “yes” の部分に入れ込むと・・・

カテゴリ列に「 – 」を含む場合
カテゴリ列の後ろから3文字抜き出せましたね!

でも「3」と固定してしまうと
「食品_精肉-鶏肉」の場合
「-鶏肉」と抜き出してしまうので・・・

抜き出す文字数
変動的に出せるように作りこむ必要があります!

考え方は以下3つのStepです!
① 全体の文字数を算出 (食品_精肉-鶏肉8文字)
② 「 – 」も文字位置を特定 (食品_精肉鶏肉 ← 6文字目)
③ ①-② (食品_精肉-鶏肉8 – 6 ) = 2文字

Text.Lengthで文字数を取得

Text.Lengthという関数は
文字数を取得できる関数です。

カテゴリ列の文字数を取得する場合は

PowerQuery関数
=Text.Length([カテゴリ])

と書いてあげます!

Text.PositionOfで文字の位置を特定

Text.PositionOfという関数は
指定した文字の位置を取得できる関数です。

カテゴリ列に対して
「 – 」の位置がどこか取得する場合は

PowerQuery関数
=Text.PositionOf([カテゴリ], "-") + 1

最後の「 +1 」について!

Text.PositionOf は
「1」文字目を「0」
「2」文字目を「1」
と数えるので「+1」をする事で帳尻を合わせます!

と言うことでそろそろ仕上げます!

いまカテゴリ列に「 – 」を含む場合 “yes” と出しているので
ここを先ほど作った Text.End・Text.Length・Text.PositionOf を
組み合わせると・・・

PowerQuery関数
=if Text.Contains([カテゴリ], "-") then
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "-")-1)
else
"no"

「カスタム」という列にきちんと出力されてますね!

では最後 “no” の部分を作りこみます!

上記の「”-” 直後から最後まで抜き出す」と言う処理の
“-” が “_” に置き換わっただけなので・・・

PowerQuery関数
=if Text.Contains([カテゴリ], "-") then
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "-")-1)
else
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "_")-1)

と書けば
欲しかった結果を一発で得る事が出来ました!

【上級者向け】もっとカッコよく抜き出すテクニック

今回のまとめ

今回は

・IF文
・PowerQueryの関数

について学びました!

関数については
文字抽出に使える関数を紹介しました!

なので関数はまだまだ沢山存在します!

全てを使いこなす必要はありませんが
知ってれば知ってるほど
出来ることが多くなります!

皆さんもPowerQueryを習得して
Excelマスターを目指しましょう!

1 2