文字を抽出してみよう!
抜出し方を整理する
かじむー
このカテゴリ列から最下層のカテゴリを抜き出したい!
「お菓子_洋菓子」なら「洋菓子」
「食品_乳製品-チーズ」なら「チーズ」
ただしこのカテゴリには以下の様な
2つの特徴があります。
●パターン①:カテゴリが2階層(大・中)の場合
┗ アンダーバーで区切る(例:お菓子_洋菓子)
●パターン②:カテゴリが3階層(大・中・小)の場合
┗ 3階層目をハイフンで区切る(例:食品_乳製品–チーズ)
●パターン②(食品_乳製品–チーズ)について
もしカテゴリ列に「 – 」を含むなら
「 – 」直後の文字から最後までを抜き出す
●パターン①(お菓子_洋菓子)について
もしカテゴリ列に「 – 」を含まないなら
「 _ 」直後の文字から最後までを抜き出す
IF文とText.Containsを使って場合分け
Text.Containsという関数は
特定の文字を含むか?含まないか?を判定できる関数です。
カテゴリ列に “-” 含むかどうかを判定するには
以下の様な書き方をします!
=Text.Contains([カテゴリ], "-")
そうすると
含むものは「True」
含まないものは「False」
と返ってきます!
という事は
Text.Contains関数は条件式そのものなので
IF文を使って
「 – 」含む場合は “yes”
「 – 」含まない場合は “no”
という処理を書いてみると・・・
=if Text.Contains([カテゴリ], "-") then "yes" else "no"
きちんと “yes” “no” と出力されましたね!
では “yes” の部分をきちんと作っていきましょう!
Text.Endで文末から文字抽出
Text.Endという関数は
指定した文字数を文末から抜き出す関数です。
例えば
カテゴリ列の後ろから3文字だけを抜き出す場合は
=Text.End([カテゴリ], 3)
このように書きます!
今 “yes” の部分を作りこんでいるので
この式を “yes” の部分に入れ込むと・・・
カテゴリ列に「 – 」を含む場合
カテゴリ列の後ろから3文字抜き出せましたね!
でも「3」と固定してしまうと
「食品_精肉-鶏肉」の場合
「-鶏肉」と抜き出してしまうので・・・
抜き出す文字数を
変動的に出せるように作りこむ必要があります!
考え方は以下3つのStepです!
① 全体の文字数を算出 (食品_精肉-鶏肉 ← 8文字)
② 「 – 」も文字位置を特定 (食品_精肉–鶏肉 ← 6文字目)
③ ①-② (食品_精肉-鶏肉 ← 8 – 6 ) = 2文字
Text.Lengthで文字数を取得
Text.Lengthという関数は
文字数を取得できる関数です。
カテゴリ列の文字数を取得する場合は
=Text.Length([カテゴリ])
と書いてあげます!
Text.PositionOfで文字の位置を特定
Text.PositionOfという関数は
指定した文字の位置を取得できる関数です。
カテゴリ列に対して
「 – 」の位置がどこか取得する場合は
=Text.PositionOf([カテゴリ], "-") + 1
最後の「 +1 」について!
Text.PositionOf は
「1」文字目を「0」
「2」文字目を「1」
と数えるので「+1」をする事で帳尻を合わせます!
と言うことでそろそろ仕上げます!
いまカテゴリ列に「 – 」を含む場合 “yes” と出しているので
ここを先ほど作った Text.End・Text.Length・Text.PositionOf を
組み合わせると・・・
=if Text.Contains([カテゴリ], "-") then
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "-")-1)
else
"no"
「カスタム」という列にきちんと出力されてますね!
では最後 “no” の部分を作りこみます!
上記の「”-” 直後から最後まで抜き出す」と言う処理の
“-” が “_” に置き換わっただけなので・・・
=if Text.Contains([カテゴリ], "-") then
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "-")-1)
else
Text.End([カテゴリ], Text.Length([カテゴリ]) - Text.PositionOf([カテゴリ], "_")-1)
と書けば
欲しかった結果を一発で得る事が出来ました!
今回のまとめ
今回は
・IF文
・PowerQueryの関数
について学びました!
関数については
文字抽出に使える関数を紹介しました!
なので関数はまだまだ沢山存在します!
全てを使いこなす必要はありませんが
知ってれば知ってるほど
出来ることが多くなります!
皆さんもPowerQueryを習得して
Excelマスターを目指しましょう!