氏名データから「氏」のデータだけ抽出したいのですが、苗字の長さが2文字の人と3文字の人がいるので、氏名の間の「スペース」を基準に抽出したいです。
Power Query(パワークエリ)の場合、どうしたら良いですかね?
なるほど。それは「区切り記号」というやつですね。
その場合、Power Queryエディター上の「抽出」というコマンドが有効ですよ!
具体的な使い方について解説していきますね。
解説動画:【パワークエリ#6】データクレンジング基本テクニック13選 – 元データを綺麗にするための不備修正の作業を自動化する
この記事の内容は下記の動画でも解説しています。
コメント欄の「抽出」の時間の部分をクリックすると抽出の解説へジャンプできますよ!
はじめに
本題に入る前に、この記事がおすすめな方を挙げてみます。
- 指定した区切り記号で各データからの抽出作業を自動化したい方
- データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
- Power Query(パワークエリ)の使い方を習得したい方
前提条件
この記事のテクニックを使うためには、以下の条件を満たす必要があります。
- ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
- Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
- Power Query(パワークエリ)の概要を理解していること
→まず、以下の記事で概要を把握することをおすすめします。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでブック内、あるいは別ファイルのデ …
既存データの一部分のみ切り出したい場合は「抽出」が有効
実務では、作業内容によって元データの一部の文字が必要なケースがあります。
たとえば、番地まで入っている住所データのうち、都道府県情報だけほしいなどですね。
この場合、任意の部分を抜き取る作業をデータ抽出といいます。
データ抽出を行う際、次の2通りの方法があります。
- 文字数を基準に抽出する
- 特定の記号を基準に抽出する
本記事では2つ目の方法の解説です。(1つ目の方法は下記記事参照)
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 指定した文字数で各データからの抽出作業を自動 …
目印となる記号がある場合に区切り記号を基準に抽出する
次のような場合に、区切り記号を基準とした抽出が有効です。
ちなみに、区切り記号とは、文字通りデータとデータを区切る記号のことです。
代表的なものは以下が挙げられます。
- コロン(:)
- コンマ/カンマ(,)
- セミコロン(;)
- スペース( )
- タブ( )
- スラッシュ(/)
なお、上記に限らず特定の文字でも「目印」になれば、ある意味区切り記号となり得ます。(たとえば住所であれば都道府県など)
ちなみに、Excelワークシートで区切り記号を基準とした抽出を行う方法は以下の2パターンが主流です。
- 置換+ワイルドカードの組み合わせ
- LEFT・RIGHT・MID関数+FIND関数の組み合わせ
文字列関数の組み合わせでオーソドックスなものとして住所情報を分割することがあげられます。 たとえば、 都道府県 …
ただし、他のデータ整形作業とセットで行うなら、Power Query(パワークエリ)で行った方が、一連の作業手順を記録できて自動化できますよ!
では、Power Query(パワークエリ)での区切り記号を基準とした抽出の手順を確認していきましょう。
Power Query(パワークエリ)での区切り記号を基準とした抽出の手順
今回の前提として、抽出を行う対象テーブルはブック内にあり、事前にPower Queryエディターに取り込んでいる状態からスタートするとします。(ブック内のデータ取得について復習したい方は下記記事を参照)
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 同じブック内の表データ(テーブルまたはセル範 …
今回の区切り記号のスペース( )の前にある「氏」のデータを抽出するケースを例に解説していきます。
抽出は以下の2ステップとなります。
【STEP1】リボン「変換」タブから「抽出」をクリック
まず、抽出元の列を選択(①)します。
これは、通常のワークシート上の操作と同じ感覚で問題ありません。
続いて、リボン「変換」タブをクリック(②)してください。
そして、「抽出」をクリック(③)し、「区切り記号の前のテキスト」をクリック(④)してください。
手順④は、区切り記号の後ろの文字を抽出したい場合は「区切り記号の後のテキスト」、2つの区切り記号の間の文字を抽出したい場合は「区切り記号の間のテキスト」を選択すれば良いですよ!
【STEP2】「区切り記号の前のテキスト」ダイアログで任意の区切り記号を指定
「区切り記号の前のテキスト」ダイアログが起動します。
ダイアログ内へ任意の区切り記号を入力(⑤)したら、「OK」ボタンをクリック(⑥)してください。
手順⑤は、半角と全角の違いで別文字扱いとなり、うまく抽出できない原因になりますので、半角/全角の入力間違いに気を付けましょう。
手順⑤は、手順④で「区切り記号の間のテキスト」を選択した場合、2種類の区切り記号を入力する必要があります。
「プレビュー」ウィンドウ上で抽出が確認できたらOK!
「プレビュー」ウィンドウを見ると、無事手順①で指定した列の各データが、区切り記号のスペース( )の前にあった「氏」の部分の文字へ上書きされていますね!
あとは、その他の処理を行い、任意の方法でデータを出力すれば完了です。
【参考】抽出した文字を別の列に表示することも可能!
リボン「変換」タブの「抽出」コマンドでは、抽出元の列を上書きしてしまう仕様ですが、別の列に抽出結果を表示したい場合もあると思います。
その場合は、リボン「列の追加」タブの「抽出」コマンドを使いましょう。
上記の手順②をリボン「列の追加」タブに置き換えるだけで、他の手順はまったく同じです。
ケースバイケースで使い分けてくださいね。
【参考】抽出条件を変更したい場合は
もし、あとで抽出条件を変更したい場合は、「クエリの設定」ウィンドウの任意のステップをダブルクリック、もしくは歯車マークをクリックしましょう。
すると、再度STEP2の「区切り記号の前のテキスト」ダイアログ(「区切り記号の後のテキスト」ダイアログ/「区切り記号の間のテキスト」ダイアログ)が区切り記号の入力された状態で起動します。
あとは、区切り記号を入力し直して、再度「OK」ボタンをクリックすれば良いですね。
サンプルファイルで練習しよう!
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_PowerQueryデータ整形_抽出_区切り記号.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)
ファイルを開いたら、区切り記号での抽出を行う前に、Power Queryエディターを起動させてくださいね。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリックしてください。
- リボン「データ」タブをクリック
- 「クエリと接続」をクリック
- 「社員マスタ(抽出_区切り記号)」クエリをダブルクリック
ここまで準備ができたら、次の手順を実施してください。(今までの解説のまとめです)
- 抽出元の列を選択
- リボン「変換」タブをクリック
- 「抽出」をクリック
- 「区切り記号の前のテキスト」をクリック
- 任意の区切り記号を入力
- 「OK」ボタンをクリック
本記事の解説と同じ結果になればOKです!
さいごに
いかがでしたでしょうか?
区切り記号での抽出はワークシート上では応用テクニックですが、Power Query(パワークエリ)では文字数とほぼ同じ難易度で設定できるのが良いですね。
区切り記号での抽出は、実務でのデータ集計や分析でも応用範囲は広いため、ぜひ覚えていただくことをおすすめします。
なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします!
特典として、Excelワークシート関数一覧(計22スライド)やExcelショートカットキー一覧(計25スライド)等をプレゼント!
ご参考になれば幸いですm(_ _)m
今回の区切り記号での抽出のように、ワークシートでは応用テクニックでもPower Query(パワークエリ)なら標準機能になっているケースがあります。
同じことをするにも、より敷居が下がってくれるのでありがたいですね。
今回のテクニックなど活用できそうだなと思ったら、ぜひともPower Query(パワークエリ)を実務に取り入れてみてください。