---topics:文字列結合、textjoinが使えないとき---

其ノ21 飛び飛びに入っている文字列を結合する

ハイ、本日は、飛び飛びのセルに入っている文字を結合する、というのをやりましょう。 どういうものかというと、こういうの↓です※1

kiso21_tobitobi.xlsx
※1 kiso21_tobitobi.xlsx

A欄~E欄まであって、それをカンマでつなぎたい。 これが今日のミッションです。
…実は、B列にすでに答えを入れてあります。 つまり、B列の値がゴール、なのですが、、じゃあここまでどうやってもっていくか。 一緒に考えてみましょう。

この問題、実はExcel2016だとtextjoin関数ってヤツを使うと解決する、らしいんですよ。 「らしい」って言うのは、…ぼくが実はまだExcel2016を使えてないからで、なのでたしかめたわけではないのですが、どうやらそうらしいです。 でも、2016より前のバージョンだと結局textjoinは使えないので、下記の話を一応は知っておいた方がよいでしょう。

とりあえずカンマでつないでみるか…

この作業、どうやってやりますか?
…とりあえず、手作業はちょっと手間がかかりすぎるよなあ。 だから、式を使ってやることになるんだけど、…どうやろうかなあ。

文字列の結合の仕方についてはもうやりましたよね。 セル内容の結合には「&」を使います。 例えば、C2セルとD2セルをくっつけるには「=C2&D2」、あいだにカンマをはさみたい場合は「=C2&","&D2」とカンマをアンドではさんでやります。 ここまでは覚えています?

じゃあとりあえず、今回は結合しなきゃいけないセルが5個あるけど、コイツらを結合してみますか。 作業用の列を新しく挿入して、式を入れてみましょう※2 ※3

式はこんな感じ
※2 =D2&","&E2&","&F2&","&G2&","&H2
カンマをはさみつつ結合された
※3 カンマをはさみつつ結合された

A欄からE欄まで全部結合し、間に「,(カンマ)」はさむわけだから、

=D2&","&E2&","&F2&","&G2&","&H2

となるよね。
アンド カンマ アンド、アンド カンマ アンド、…ってな具合に。 ここまではオケイ?

そいじゃあ、この式をおしりまでコピーすると…※4
…まあ、予想はついてると思うけど…。

「,,,D,E」とかになる
※4 「,,,D,E」とかになる

こうなる。
「,B,C,D,」とか「,,,D,E」とか、先頭がカンマで始まったり、その逆でカンマ終わりだったり、あと、カンマが2つ3つ並んだり。 でも今回は、そういうよけいなカンマはなくしたいんですよ。 間をつなぐカンマは1つ、先頭にもおしりにもよけいなカンマは要らない、としたい。 最初の答えの、B列のように。

じゃあ、どうしよう。

例えば、入れる式を行によって変えていって、「この行はA欄とC欄とE欄が入ってるから、くっつけるのは3つ」とか「この行はB欄しか入っていないから…」とか判断しながら式を入れてくやり方もある。 フィルタをかけて、「A欄に入っていて、B欄にも入っていて、C欄にも…」と抽出しながら、とか。
項目数が少ない場合はそれでもよいんですよ。
2つとか3つとかだったら。
でも今回は結合対象が5つもあるし、組み合わせの数もめっちゃある。
…うーむ。 うむむ。
じゃあ。

…もうちょっと、がんばってみましょうか。 とりあえず、今機械的に結合したヤツ、値貼りしておいてください※5

値貼りした
※5 値貼りした

複数カンマを1コにしたい→置換

ハイ、では。
機械的にカンマで結合したコイツを、なんとか求めるかたちにもっていくことにしましょう。

まず、複数カンマが並んでいるのを何とかしようと思います。
使う機能は置換
Ctrl+Hで置換を呼び出し、,,,(カンマ2つ→カンマ1つ)に置換します※6

カンマ2コを1コに
※6 カンマ2コを1コに

こうすると、カンマが2コ並んでいるところが1コになります※7
あ、そうそう、式を値貼りしておかないと置換がはたらかない(式なので、文字列と見なしてくれない)ので注意。 忘れず値貼りしておいてね。

29件置換した
※7 29件置換した

うん、置換できた。
でも、まだカンマ2つのところ、ありますよね。
なのでこれを、「置換対象が見つからないからもう置換できないよ」ってところまで繰り返し置換します※8
今回のケースだとおそらく、3回目で全部カンマ1コに置き換えることができるのでは。

置換対象が見つからない=カンマはすべて1コになった、ということ
※8 置換対象が見つからない=カンマはすべて1コになった、ということ

ハイ、これで、カンマ連続しちゃう問題は解決です。 カンマが複数続いてるところ、なくなりましたよね。

カンマはじまり→midで2文字目以降をもってくる

次に、先頭カンマとカンマ終わりを何とかしましょう。
先に、先頭カンマから。
まず、作業用の列をとなりに作ってください。 ここに式をいれていきますゆえ。
そしたら、カンマで始まるところをフィルタで抽出していきます。 「フィルタ」→「テキストフィルター」→「指定の値で始まる」とし、始まりの文字に「,(カンマ)」を指定します※9 ※10。 そうすると、カンマ始まりのものを抽出できます※11

テキストフィルターで…
※9 テキストフィルターで…
「カンマではじまる」を指定
※10 「カンマではじまる」を指定
カンマではじまるのは12コ
※11 カンマではじまるのは12コ

そうしたら後は、最初のカンマは要らない2文字目以降を抜き出せばよいから、mid関数を使って「=mid(D8,2,len(D8))」と入れてやります※12
要するに、「2文字目からうしろ、全部の文字抜き出すぞ」、と。

2文字目からうしろを抜き出す 使用するのはmid
※12 2文字目からうしろを抜き出す 使用するのはmid

mid関数は以下のように使います。 覚えてますよね? 不安な方は其ノ3まで戻りましょう。

・left関数 =left(取り出す元となるセル , から何文字分?)
・right関数 =right(取り出す元となるセル , から何文字分?)
・mid関数 =mid(取り出す元となるセル , 何文字目からスタート? , 何文字取り出す?)

上記mid関数内のlen関数のところですが、厳密には「=mid(D8,2,len(D8)-1)とマイナス1するのが正解なのですが(全文字数 - 最初の1文字)、別に元の文字数より多く指定しちゃってもエラーになったりはしないので問題ありません。
また、このmid関数のところは、right関数を使って「=right(D8,len(D8)-1)としてもOKです。 要は、最初の文字の手前(その分がマイナス1)まで、右から文字列引っ張る、ということ。

後はこの式をコピーしてやります。 先頭のカンマが消えていること、確認しておいてください※13

先頭のカンマが消えた
※13 先頭のカンマが消えた

ハイ、まだ終わりじゃないですよ。
先頭カンマのところが解決したら、一度フィルタを元に戻して※14式の入っていないところ、つまり空白のところだけにします※15
カンマはじまりじゃないところは、とりあえずそのままでよいですもんね。 なので、その処理をします。

絞り込んでいたフィルタを「すべて」に戻す
※14 絞り込んでいたフィルタを「すべて」に戻す
作業列の方に移り、今度は「空白」だけをフィルタ
※15 作業列の方に移り、今度は「空白」だけをフィルタ

作業列が「空白」のところはカンマはじまりじゃないので、そこはとなりとイコール、とします※16 ※17。 イコール式を入れてあげてください。

イコール式で「となりとイコール」とする
※16 イコール式で「となりとイコール」とする
式をコピー
※17 式をコピー

これでフィルタを戻せばOKです※18。 値貼りしておいてください。

これで、カンマはじまりのところはなくなった 値貼りしておこう
※18 これで、カンマはじまりのところはなくなった 値貼りしておこう

これで、先頭カンマ問題は解決しました。

カンマ終わり→leftを使う

では次に、おしりのカンマ問題。
こちらも、先頭カンマのときと考え方は一緒です※19~24。 カンマ終わりのところを抽出して、今度はleft関数で「全文字数-1」を抜き出してやります。

作業列を挿入し、テキストフィルターで…
※19 作業列を挿入し、テキストフィルターで…
カンマ で終わる と指定
※20 カンマ で終わる と指定
今度はleft関数を使う
※21 今度はleft関数を使う
おしりのカンマがなくなる
※22 おしりのカンマがなくなる
それ以外のところはそのまま イコールでもってくる
※23 それ以外のところはそのまま イコールでもってくる
ハイ、できた
※24 ハイ、できた

こんな感じです。 よけいなカンマを取り除いたかたちで、A、B、C、D、E、を結合することができました。 飛び飛びのセル内容も、こういう手順をとれば結合できるんですね。 つまり、

・「置換」でカンマ2コ→1コに
・カンマはじまりをmid関数で処理
・カンマ終わりをleft関数で処理

という具合です。
ガッテンしていただけましたでしょうか。

…もうちょっと効率良いやり方ないのかな [trimした後空白を置換]

…うーん、なんとか、できた、けど、…もうちょっと楽なやり方、ないのかな…。

うん、ですよね。
じゃあ、もう1回やってみましょう。 さっきまで作業に使った列を削除して、初期状態に戻してください※25

初期状態に戻す
※25 初期状態に戻す

で、またA~Eまで結合するんだけど、今度は間にはさむ文字を半角スペース(空白)にします。 つまり、" "と、ダブルクォーテーション(チョンチョン)で半角スペースをはさんで、

=D2&" "&E2&" "&F2&" "&G2&" "&H2

と式を入れてやります※26

半角スペースをあいだにはさんで結合する
※26 半角スペースをあいだにはさんで結合する

「結合するのはカンマなのに、なぜ半角スペース?」と思う方もいらっしゃるかと思いますが、これにはワケがあるのです。 その点は後々処理しますので、とりあえず半角スペースでくっつけといてください。
じゃあ、式を入れたら、式をおしりまでコピーして、値貼り※27

式をおしりまでコピーし、値貼り
※27 式をおしりまでコピーし、値貼り

半角スペースで結合できたら、もう1列追加して、今度はtrimって関数を使います※28
trim関数は、

=trim(対象セル)

って感じで指定し、対象文字列の単語間のスペースを1つずつ残して、不要なスペースは削除するというはたらきをします。
ちょっと、画面見ながら、ついてきてください。

trim関数で無駄なスペースをトル
※28 trim関数で無駄なスペースをトル

式を入れたら、おしりまでコピーしてください※29。 trimの作用がわかります。

よけいなスペースがなくなった 先頭のスペースもおしりのスペースも消えた
※29 よけいなスペースがなくなった 先頭のスペースもおしりのスペースも消えた

ね?
先頭のスペースは消えた、(実はおしりのスペースも消えてる、)そして文字間の連続スペースも1コになってる。

上で、trim関数について、「単語間のスペースを1つずつ残して、不要なスペースは削除する」と説明しました。
このはたらき、より詳細には、

・スペースが何個も続いている場合、文字間のスペースは1つだけにする
・スペースはすべて半角にする
・文字列先頭がスペースのとき(スペースはじまりのとき)は、それをトル
・スペース終わりの場合もトル

となります。
こういう汚いスペースの使い方、手入力の際にしばしば見受けられますね。 trimはそれを整理してくれるんです。 で、今回は、それを応用しよう、というワケです。

ということで、trimでスペースを整理することができました。
そうしたら、…あとやることわかります?
置換を使って、半角スペース→カンマに換えてやればよい※30
あ、置換する前に値貼り、しておいてね。

半スペをカンマに置換
※30 半スペをカンマに置換

ハイ、できあがり※31。 左の答えと同じになったかと思います。
こっちの方が、手数少なくて済みますよね。

これで求めるものになった
※31 これで求めるものになった

ということで、飛び飛びの文字の結合、やってみました。 もっと効率のよいやり方もあるかもしれませんが、ひとまずこれでくっつけることができます。
この作業が直接仕事の役に立つかどうかは人それぞれかもしれません。 けど、「自分の仕事と関係ないよ」って人でも、考え方を知っておくってのは無駄じゃないと思いますので、そんなふうにとらえていただけるとよいかな、と。
というわけで、シーユーアゲン!

Copyright(C)森田表計算