【Excel関数】表の横項目を「／」を挟んで結合させようとしたら意外に大変だった、というおはなし

ある表の、同年の項目1から項目5までの文字列を、「／」を挟んで結合させたい案件が発生した。
序でに内容のない項目（つまりは空セル）は省いて結合させたい。
具体的にはこんな感じ。

表1

コレ、以外にややこしい事になってしまった。
マクロは使わず（マクロが出来ない、が正解なのだが）、関数のみでどうにか出来ないか悪戦苦闘したからだが。
ややこしすぎて、どうやったか忘れそうなので、備忘録としてまとめてみた。

条件は、

• 文字列を「／」を挟んで結合させたい
• 空セルは省く
• どの項目が空セルになるかはランダム

以上の条件全てを満たす関数を組みたいのだが、懸念点は以下の通りである。

• 途中に空セルがある場合「／」が「／／」や「／／／」と重なる　例）2017：「東京／大阪／／／奈良」
• 最後が空セルの場合、最後に「／」が来てしまう　例）2016：「東京／／京都／神戸／」
• 先頭が空セルの場合、先頭に「／」来てしまう　例）2015：「／大阪／／神戸／奈良」

もう何とも意図したものに出来ないのである。

ということで、地道に1づつ解説していく。

まず最初に、

• 文字列を「／」を挟んで結合させたい

を実装する関数を考えてみる。

セルB7に結合した文字列を表示するものとする。

素直に文字列を結合すると、

B7=B2&"／"&C2&"／"&D2&"／"&E2&"／"&F2（B7=CONCATENATE(B2,"／",C2,"／",D2,"／",E2,"／",F2)に同じ）

結果：「東京／大阪／／／奈良」

この場合、上記懸念点で記した通り、空セルがある場合は「／」が余計に現れるのである。
今回作成する関数はひじょ〜に長いので、通常文字列を結合させる時に使用するCONCATENATE関数ではなく、「&」を使った文字列結合を採用する。

次に、空セルの場合は省き、そうでない場合は文字列を表示したい時、これは条件分岐のIF関数で、

B7=IF(B2="","",B2)

結果：「東京」

「B2=""」は「セルB2が空セルである場合」を意味し、その条件を満たす時はセルB7に「""」を入れ（何の文字列も入れないことを意味する）、
そうでない場合（セルB2に何らかの文字列が入っている場合）、B2の文字列を表示する、という意味。

この時、「／」はどのような方法で挟み込めば良いか？
空セルの場合は「／」も不要なので、上記の条件分岐の関数のセルに「／」を付属させれば良い。
では、どこが良いか？
セルの前？
それともセルの後？

セルの前に「／」を付属させると、先頭に「／」が来る可能性が出てくるので、これはNGなのがわかる。
ということで、セルの後に「／」を付属させてみる。
上記の条件分岐の関数を改良すると、

B12=IF(B2="","",B2&"／")

結果：「東京／」

以上から、改めて文字列を結合する。

B17=B12&C12&D12&E12&F12

結果：「東京／大阪／奈良／」

これで空セルは省け、且つ余分な「／」もない結果が得られた、よかったよかった………、

アレ？！
えっと、文字列の最後に「／」がありますねぇ〜。

これが最後！
この余分な「／」の削除方法があるんです。
LEFT関数とLEN関数の合わせ技を使うのである。

LEFT(文字列,[文字数])
定義
文字列の先頭（左端）から指定された数の文字列を取得。今回は文字列最後の「／」の直前までの文字列を取得
文字列
取り出す文字を含む文字列を指定。今回はセルB17を指定
文字数
省略可。取り出す文字数を指定。今回はLEN関数で文字数を指定（以下を見よ）

LEFT関数内の「文字数」に、LEN関数を使用するのがミソ。

LEN(文字列)
定義
文字列の文字数を返す。
文字列
指定した文字列の文字数を取得。今回はセルB17から最後の「／」を引いた文字数を取得

しかるに、取得したい文字数は、

（B17の文字数）-（「／」の文字数）=LEN(B17)-1

以上より、

B22=LEFT(B17,LEN(B17)-1)

結果：「東京／大阪／奈良」

これで、ランダムに現れる空セルを省きつつ、余分な「／」も出ずに文字列を結合させることが出来た。

因みに、上記で作成した関数をまとめて1つにすることも可能。
こんな感じになる。

B22=LEFT(IF(B2="","",B2&"／")&IF(C2="","",C2&"／")&IF(D2="","",D2&"／")&IF(E2="","",E2&"／")↲
&IF(F2="","",F2&"／"),LEN(IF(B2="","",B2&"／")&IF(C2="","",C2&"／")&IF(D2="","",D2&"／")↲
&IF(E2="","",E2&"／")&IF(F2="","",F2&"／"))-1)…①

先に、ひじょ〜に長くなるといったが、実はこのことである。

実際の案件では、不特定の人間が各々入力してもらうもののため、
人によってはセル内改行や、スペースを入れてしまうケースが多い。
それらを一々手作業で削除するのは面倒かつ事故発生の可能性もあるので、
それに対処する関数を上記の関数にかませている。

実際の案件の関数の最終形は以下の通り。

CLEAN(SUBSTITUTE(SUBSTITUTE(B22,"　","")," ",""))

（本来はB22の箇所に①の関数が挿入されるが、判りづらくなるので上記の表記に留めた）
CLEAN関数とSUBSTITUTE関数を使って、セル内改行や、スペースを削除している。
（コレに関しては別のブログでサクッと書く予定）

最後に、残念なお知らせ（いや良い知らせか）

上記で長々と説明した事柄は、実は、
Excel 2016から、TEXTJOIN関数でアッサリスッキリ出来ちゃうのです！

TEXTJOIN(区切り記号,ignore_empty,文字列1,[文字列2],...)
区切り記号
今回の例では「／」
ignore_empty
空セルを無視するか否か。空セルを無視＝TRUE、空セルを無視しない＝FALSE
文字列1,[文字列2],...
結合する文字列。今回はセル「B2」から「F2」

（詳細は、「TEXTJOIN 関数 - Office サポート」）

ということで、TEXTJOIN関数でB22を表現すると、

B22=TEXTJOIN("／",TRUE,B2:F2)

こんな簡単に表現できるのである。

バージョンアップしたい……。

謝辞

この案件の解決方法は、

「条件に合うセルの値のみ、「CONCATENATE関数」で結合したいです... - Yahoo!知恵袋」

でのベストアンサーに選ばれた回答のkei_jun_kuuさんから多大なる教示を受けました。
ここに感謝の意を表したいと思います。深謝。

---