スポンサーリンク
はじめに
前回は、PowerQueryで複数の列を、区切り文字も使って結合(マージ)する処理を取り上げた。
今回は逆に、区切り文字などによって列を分割する処理を扱う。
と言っても、正直なところ私は、実務でこの処理をやる機会がなくて全然使ってないのだけどね。
思ったより長くなったので記事を2つに分けた。
これを使う機会が多い職種とかも、あるのかな？
区切り記号による分割
まず、下図の列「生年月日」を、記号「/」を区切り記号として分割してみる。
リボン「ホーム」タブ
→列の分割
→区切り記号による分割
としよう。
列の結合のときと同様、区切り記号を選ばせる画面が出てくる。
「コンマ」などあらかじめ用意された区切り記号を選ぶこともできて、今回はその中には無い「/」記号を入れるので、上の選択肢で「カスタム」を選んでから下の枠に「/」記号を入れよう。
その下の「分割」というところで3つ選択肢があり、これらの大体の意味は分かると思うけど、普通は一番下の「区切り記号の出現ごと」を選べば良いだろう。
そして「OK」を押す。
そうすると、「/」記号で区切られて、列「生年月日」が、年・月・日の3つの列に分割される。
列の名前はF2キーで変更しておこう。
月・日について、「01」など先頭に0の文字を付けた文字列になってほしいかもしれないが、勝手に数値型に自動変換されてしまっている。
文字列の型にしたい場合は、以前に取り上げたように、その自動型変換のステップを削除しておこう。
文字数による分割
では次に、「○文字目で分割する」というやり方。
「列の分割」メニューから「文字数による分割」を選ぶ。
次の画面で、何文字で分割するか、文字数を入力する。
また、その下の「分割」欄では通常は「繰り返し」を選べば良いだろう。
そして「OK」を押す。
ちょっと見にくいし、文字数が少ない場合は右端がnullとかなってるけど、とにかく2文字ごとで分割された。
今のは「分割」欄で「繰り返し」を選んだが、次は「できるだけ左側で1回」を選んでみる。
この場合、「最初の左2文字とそれ以降」という分割がなされる。
位置による分割
次の例。
「列の分割」メニューから「位置」を選んでみる。
なんかさっきの「文字数による分割」みたいに文字数を入力する欄が出てくるが、この「位置」欄に数字を入れ「OK」を押す。
文字数に「2」を入力したけど、その場合、左の2文字目までは削除されて、3文字目以降だけが残される。
先ほどの「文字数による分割」の場合は、左の2文字目までの部分も残されたけど、それが残らないということだ。
次は「位置」欄に、カンマ区切りで「2,5」と入れてみる。
すると、
- 3(=2+1)文字目～5文字目
- 6(=5+1)文字目以降
というように文字列が残った。
「090aaaabbbb」みたいな電話番号などを区切るのに、良いかもしれない。
詳細設定(1)分割後の列数変化に対応
文字列分割で3個に分かれるようにUIで分割すると
— Yt-olt (@olt_yt) August 11, 2020
コード的には3列作ってそこに文字を入れるような処理になるので
枠のない文字は
位置による分割の「123」みたいに闇に消えていくんです
テーブル取り込みで「aaaaaa」を文字数分割2にして
後ろに文字を書き加えたりするとわかりますよ pic.twitter.com/8SDXJ3gC6s
※TwitterでYt-oltさんからご教示いただいたことを元に書いてみました。
上記で紹介した、区切り記号や文字数による分割のコマンドでは、「詳細設定オプション」というメニューを展開できる。
あまりこれを使うこともないとは思うが、ちょっと紹介。
まず「区切り記号ごと」「一定文字数ごと」で分割する場合は、分割数が何回になるかはデータにより不定だ。
たとえば下図では、区切り記号「、」で項目「会社」を区切り分割するわけだが、この場合は3列ずつに分割されることになる。
分割後、3列ずつに分かれたデータがこれだ。
さてPowerQueryとは、元データがあってそれを読み込むものであり、元データに変更があったら当然それが反映されるものだ。
では上記の例で、元データの会社名に変更を加えて、下図のように3列では収まらなくなったらどうなるか。
A社～ZZ社まで会社名が5社になったが、これはこのままでは、PowerQueryの分割処理に反映されない。
つまりPowerQueryの中では3列に分かれたままで、1列目にあるべきE社・ZZ社が見当たらない。
これは、「3列に分割する」ものだと自動的に処理が決め込まれているからだ。
ではこれを、後から設定変更するにはどうするか。
右端の「ステップ」の画面から、分割処理したステップを見つけて、その右端にある歯車のマークをクリックしよう。
PowerQueryのステップは、この歯車のマークで、後から設定変更できるものが数多くある。
そして設定画面が出てくるので、そこの「詳細設定オプション」を押すと展開されるメニューで「分割後の列数」というのがある。
ここに、自動的に「3」が設定されているけど、これを今回増えた列数に合わせて「5」に書き換え「OK」を押す。
これで、列が5つに分割され、先ほど表示されていなかったE社・ZZ社も表示された。
ただこれ、何列に増えても完全自動でそれに対応するってことまではできない(はず)。
だから何列まで増えるか予測できない場合は、とりあえず十分に多い列数を入れておくということで良いかと思う。
詳細設定(2)行で分割
いま紹介した「詳細設定オプション」の中で「分割の方向」というやつが通常は「列」になっているのだが、これを「行」にしたらどうなるか。
下図の例では、項目「会社」が「、」記号で区切られていて、その記号で分割し分割の方向に「行」を指定してみる。
結果として、会社名が縦に並べられ、他の項目「売上」「粗利」は縦に複製された。
この例は、もともと項目「会社」を「、」記号区切りで書いてるのがよろしくないクソExcelなわけで、実務でこういうのが無くもないので、そういう時に使える技かもしれない。
スポンサーリンク
