文字列を分割する2つの方法
下図のようなデータがあったとします。
この中から「名前」だけを抜き出すとか、「記号」と「名前」と「数値」に分割するとかの話です。
Power Queryエディタでは、2つの方法があります。
[変換]タブ[テキストの列]グループにある「列の分割」と「抽出」です。ちなみに、「列の分割」は[ホーム]タブの[変換]グループにも同じボタンがあります。どちらでも同じです。それぞれのボタンをクリックすると、次のようなメニューが開きます。
何となく、似たような項目が並んでいますね。
ややこしいことに、この「抽出」ボタンは、リボンの中にもうひとつ存在します。
もうひとつの「抽出」ボタンは、[列の追加]タブ[テキストから]グループにあります。2つの「抽出」ボタンですが、これは動作が異なります。
列の分割
「列の分割」からいきましょう。
「列の分割」は、その名のとおり「列内のデータを、別の(複数の)列に分割する」機能です。あくまで目的は「列を分割」することです。最も一般的な「区切り記号による分割」をやってみましょう。
上図のように、データがハイフン(-)で区切られています。この状態で「列の分割」-「区切り記号による分割」をクリックします。実行すると、下図の画面が開きます。
「区切り記号を選択するか入力してください」の下にあるボックスで「--カスタム--」が選択されています。ここには、区切り記号として使用されることの多い「コンマ」や「タブ」などがあらかじめ用意されており、それを選択するか「--カスタム--」を選択して、任意の記号を入力するかを選べます。今回の区切り記号である「ハイフン(-)」はリストに用意されていませんので、「--カスタム--」が選択され、その下のボックスに、自動的にハイフン(-)が入力されています。ですから、今回はこのままでいいです。
「詳細設定」をクリックすると、データを"行方向"へ分割することも可能です。詳しくは「行方向へ分割する」をご覧ください。
[OK]ボタンをクリックすると、次のようになります。
Excelユーザーでしたら、どういう動作かはイメージしやすいでしょう。Excelの標準機能である「区切り位置」と同じです。いずれにしても、この「列の分割」は、その名のとおり「1列を3列に分割」しました。つまり、区切り記号の数だけ列が増えたんですね。ここ、後で重要になってきますので、よく覚えておいてください。
2つめの選択肢である「文字数による分割」を、やってみましょう。
今度は、上図のような元データです。記号と名前、それと数値が2文字ずつあります。実行すると、次のように分割されます。
半角と全角は、どちらも同じように"1文字"とみなすようですね。
今のは、すべて同じ"2文字"で分割しましたが、分割したい文字数が異なる場合は、次の「位置」を使います。
これの指定は分かりにくいです。文字数を区切る文字位置の数値を、カンマで区切って指定するのですが、上図のように「記号 = 2文字」「名前 = 3文字」「数値 = 4文字」で区切りたいときは「0,2,5」のように指定します。ここで指定する位置(数値)は、要するに「その位置の後ろで区切りますよ」という意味です。あるいは「区切った結果は、その位置の次から始まりますよ」でしょうか。言葉で表すと難しいですが、そういうイメージです。
ちなみに、今回指定した「0,2,5」のうち「最初の0って、いらねーんじゃね？」と思う方もいるでしょう。いや、私もそう思いましたから。で、やってみました。
結果は、下図のようになります。ガックシです。
さて、残りの「小文字から大文字による分割」「大文字から小文字による分割」「数値から数値以外による分割」「数値以外から数値による分割」は、それぞれ変化する位置で分割します。ちなみに、この4つは、メニューをクリックすると、設定画面が開かず、いきなり分割が行われます。まあ、いいんですけど。だったら上の3つは「区切り記号による分割...」とか「位置...」みたく表記しろよって。自分で決めた表記のルールを守れよ＞Microsoft。ここでは最後の「数値以外から数値による分割」だけやってみます。
これが、こうなります。
[変換]タブの抽出
「抽出」は、[変換]タブと[列の追加]タブにあります。両者は動作が異なります。まずは[変換]タブの抽出です。
どれも、何となく予想ができますね。おそらく、予想どおりだと思います。なので、ここまでやってきたようなケースで、一番最後の「区切り記号の間のテキスト」から、やってみましょう。
注目すべきは、「開始区切り記号」と「終了区切り記号」を別々に指定できるところです。これは、Excelの標準機能「区切り位置」でもできませんし、ワークシート関数やVBAでやるにしても、ちょっと面倒くさいです。今回は、どちらもハイフン(-)を指定しました。さて、実行すると、下図のようになります。
ここです、「列の分割」との違いは。まあ、当たり前っちゃ当たり前なんですけど、おそらく多くのExcelユーザーが、このへんを混同して、いったいどっちを使えばいいんだい？と悩むのではないかと。
「列の分割」というのは、あくまで「列を分割」するのが目的です。区切り記号などで文字列を分割しますが、その分割した結果を、別の(複数の)列にして、分割した数だけの"列"を追加します。そういう機能です。対して「抽出」は、指定した条件、たとえば今回だったら「ハイフン(-)とハイフン(-)に囲まれている文字列」という条件に従って、元の文字列の中から、特定の文字列だけを抽出し、元の文字列を、その抽出した文字列に置き換えます。分割されたすべてが表示されるわけではありません。たとえば、今回のケースに限れば、最初に読み込んだ列を「重複する列」で3つにして、それぞれを「抽出」すれば、「列の分割」と同じことが可能です。
でも、だったら最初から「列の分割」をすればいいです。逆に、「列の分割」で分割してから、真ん中の"名前"以外の列を削除すれば、ハイフン(-)で囲まれた名前だけを取り出せます。でも、だったら最初から「抽出」すればいいです。このように、最終的に自分が何をしたいのか、どういう結果が欲しいのかで、両者は使い分けるべきです。繰り返しますが、当たり前っちゃ、当たり前です。
[列の追加]タブの抽出
ここまでをまとめると、簡単に言えば「列の分割」は新しい列が追加されて、「抽出」は新しい列が追加されません。でも、[列の追加]タブの「抽出」は、その中間です。指定した条件で文字列を抽出して(そこまでは同じ)、その抽出した文字列を"新しい列"として追加します。
これはこれで、使う機会がありそうです。
「取得と変換」の"変換"では、データの中から特定の部分を抜き出す機会も多いです。そのとき、どの機能を使うのがベストか、よく考えてくださいね。