☆☆☆★★ Excelにあるリストからカンマ区切りの文字列を作る
Excelにあるリストからカンマ区切りの文字列を作る
状況：Excelにとあるリストがあるので結合してカンマ区切りの1つの文字列にしたい。Pythonのリスト作成やSQLでWhere文に使う。
例：Excelのある列に
北海道
青森県
・・・
鹿児島県
沖縄県
となっているのを、(′北海道′,′青森県′,・・・′鹿児島県′,′沖縄県′)という1つの文字列にする
方針
文字列を作ることが目標なのでExcel上で処理することを試みる。そのまま取り込んでリストでループする方法は別途。
解決方法
リストはA列に重複無しとする。
TEXTJOINでカンマ区切りの結合（Excel2016以降）
「=TEXTJOIN(",",TRUE,A:A)」と入力すると、都道府県名がカンマ区切りで結合された文字列になる。このTEXTJOIN関数は見ての通り、第一引数に指定した文字列で、第三引数の範囲内の値を指定した文字列で結合してくれる。第二引数は空白がなければTRUEでもFALSEでも良いが省略不可。
この関数が無かったときは一旦縦と横を入れ替えてからテキストエディタに張り付けしてタブを「′,′」に変換したりしていたが、これは便利。
このままだとPythoneにもSQLにも使えないので少し手を加える
数値ならこれでも良いが文字列の場合はこのままだと当然エラーになるので、シングルクォーテーションで囲わなければいけない。だからといってこのカンマ区切りの文字列をテキストエディタにコピペして、とかやっているとTEXTJOINを使った意味がなくなってしまうのでここはもっと簡単に終わらせたいところ。
そこで今度は 「=TEXTJOIN("′,′",TRUE,A:A)」と入れてみる。区切り文字をカンマだけでなく、「′,′」としている。すると各文字列がシングルクォーテーションで囲まれたカンマ区切りの結合になる。
ついでにあとちょっと補正
後は最初と最後に足りない部分があるので実務上は手作業で書き加えればよいが、せっかくなのでこれもまとめて作ってしまおう。最初と最後に足りない部分をconcat関数でつけたす。
「=CONCAT("(′",TEXTJOIN("′,′",TRUE,A:A),"′)")」と入力すれば完成。あとはPythonでリストに入れるもよし、for文に直接かくも良し、SQLならwhere in のあとにそのまま書けば終り。
発展
結合するだけならconcatでOK
今回はカンマ区切りという前提なのでTEXTJOINを使っているが、区切り文字なしで単に結合するだけだったり、区切り文字を都度変えたいということであればconcat関数を使えばよい。
Excelでやるかは状況次第
この記事では文字列を作ることに焦点を当てたからExcelだけで完結しているけれども、実際にはExcelで作ることにこだわる必要はなくて状況に合わせて使い分ける。
少数なら直接書いた方が早いし、Pythonで値を使ってループさせるならファイルをそのまま取り込む方がいいかもしれない。別のテーブルとjoinしたければテーブルを作って挿入するかもしれないし、毎回作るのは無駄なのでよく使うマスタはいつでも使えるテーブルを作っておこうと思いつくのは自然な流れだ。
アドホックで急ぎな案件だからExcelでさっさと処理してしまおう、なんてこともあるだろう。とにかく手を動かす前に少し考えると余計な手間が減る。
これだけできてもあまり意味がない
データ分析で仕事をするならちょっと手が込んだことをやらないといけない時に応用が効かないとこれだけできてもあまり意味がない。例えば都道府県+市町村区のデータがあったとして
- ある条件に合致する行の値だけが欲しい（競合が出店している市町村区）
- ある条件に合致する行のユニークな値だけ欲しい（競合が出店している市町村区が1つでもある都道府県）
- グループ別にまとめたい（競合が出店しているエリア別の都道府県）
といったことにも対応できるようになっておきたい。これは随時まとめる。
評価
正直これぐらいはできないと厳しいので☆☆☆☆★にするか迷ったけれどもそれだとレベルを上げすぎなきもするので、いくつかの方法を自分で考えて一番良いのを選ぶことが出来る、というのまで含めて★2つ。
参考リンク
TEXTJOIN 関数（Office Support）