Excel2019の新機能：TEXTJOIN関数の高度な活用テクニック

今回はExcel2019で完全対応となった新関数「TEXTJOIN」の高度な活用テクニックを紹介します。

（※この文章は、ことりちゅんが思いついたアイディアを淡々と書き記した物語です。過度な期待はしないでください。）

f:id:Kotori-ChunChun:20190814033653p:plain

きっかけ
TEXTJOIN関数とは
TEXTJOINの使用例
TEXTJOINの活用テクニック
TEXTJOINの不満
- この記事の内容の注意
参考資料
まとめ

きっかけ

長らくTEXTJOINに関する情報は、こちらのツイートにて蓄積しておりましたが、実装から3年。Excel2019が普及してそろそろ認知度が高まってきたかと思うので記事にすることにしました。

このツリーは、今後も良いアイディアを発見したら付け足していく予定です。

TEXTJOINのことを考えていたら、胸が熱くなりました。

ドキドキが止まらないんです。

もしかして、これは・・・恋？
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年5月21日

ハッシュタグは #TEXTJOIN です！

TEXTJOIN関数とは

タイトルでは見栄えの都合でExcel2019の新機能と書きましたが、実はサブスクリプション契約している一部のExcel2016や、Office 365のExcelでは2016年2月ごろから使えるようになっていました。

公式や各解説サイトでは「2016対応」と記載されていますが、パッケージ版やプリインストール版のExcel 2016では2020年7月現在でも使用できない状態なので、おそらく一生実装されることはないでしょう。

Excel2016で使えている人は皆無です。Excel2019の新機能と言って差し支えありません。

※パッケージ版の2016では、月次アップデート直後だけOffice365化して使えるようになるなど、混乱に見舞われる現象がしばしば発生します。

同時に実装された関数

同じタイミングで以下の関数が実装されています。

いずれも私が喉から手が出るほど欲しかった関数ばかりなので、まだ知らない人は一度使ってみましょう。

IFS関数
SWITCH関数
TEXTJOIN関数
CONCAT関数
MAXIFS関数
MINIFS関数
FORECAST.ETS系関数

TEXTJOINと似たような関数

文字列の連結には

CONCATENATE
CONCAT
文字列結合演算子（&）

もあります。

TEXTJOINはこれらの機能の完全上位互換関数であり、式が長くなるものの全てを実現可能です。

しかしながら、逆の操作を行うSPLIT関数は存在しません。実装を強く願います。

※Google Spreadsheetには、JOIN関数、SPLIT関数、REGEX系関数があります。

TEXTJOINの書式

 =TEXTJOIN(区切り文字,空のセルは無視,テキスト1,...,テキスト252)

※引数が「テキスト252」で終わりなのは、Excel関数の引数は254個が限界のため。

TEXTJOINのパラメータ

パラメータ	概要	定数	配列定数	参照	配列参照	省略時
区切り文字	コンマとかの区切り文字列	○	○	○	○	""
空のセルは無視	TRUE/FALSE	○	×	○	×	TRUE
テキストn	結合対象の文字列	○	○	○	○

TEXTJOINの凄いところ

１つ目はセル範囲（二次元配列参照）を複数連結できることです。

今までは文字列の連結をするときにA1&B1&C1やCONCATENATE(A1,B1,C1)のようにひとつづつ繋ぐ必要がありました。

TEXTJOIN関数であれば、テキストnに配列が使えるのでTEXTJOIN("",TRUE,A1:C1)のようにして、まとめて連結することができます。

それぞれのテキストnに別々に配列を渡す事もできるので、かなり汎用性が高いと言えます。

（ここまでは同時実装のCONCAT関数でも出来ます）

2つ目は、区切り文字を使った連結です。

これはCONCAT関数で出来ないため、TEXTJOIN関数でないと出来ません。

CSVやTSVなどのデータが作れそうな気がしてきますね。

（ここまでは、Google スプレッドシートのJOIN関数でも出来ます）

3つ目は、空欄を無視して連結出来るということです。

歯抜けの表から「データのある所だけを拾って区切り文字で連結する」なんて複雑な事が簡単にできます。

例えばAAA,,,DDDとなりそうなデータでも、AAA,DDDというデータを作ることが出来ます。

4つ目が、区切り文字にも配列が使えるということです。

たぶん、3年経過した現在でも、この仕様はあまり知られていません。

この仕様をマスターすると、従来の数式に革命を起こす驚異的な可能性が見えてきます。

そんなことを言われても、いまいちイメージが浮かばないと思いますが、まあ単一セルでCSVが作成出来るとでもお考えください。

TEXTJOINの使用例

まずは極々普通なTEXTJOIN関数の使用事例の紹介です。

単純な連結

＼	A	B	C	D	E
1	コードA	コードB	コードC	コードD	TEXTJOIN後
2	1234	4478	2345	7889	1234-4478-2345-7889

=TEXTJOIN("-",FALSE,A2:D2)

空欄を省略して連結1

＼	A	B	C	D	E
1	部	課	係	名	TEXTJOIN後
2	ほげ部	ほげ課	ほげ係	日本太郎	ほげ部-ほげ課-ほげ係-日本太郎
3	ふが部	ふが課		和和花子	ふが部-ふが課-和和花子
4	ぴよ部		ぴよ係	東京四郎	ぴよ部-ぴよ係-東京四郎

=TEXTJOIN("-",TRUE,A2:D2)
=TEXTJOIN("-",TRUE,A3:D3)
=TEXTJOIN("-",TRUE,A4:D4)

空欄を省略して連結2

＼	A	B	C	D
1	あああ			おおお
2		いいい
3	ううう		えええ
4
5	TEXTJOIN後	あああ,おおお,いいい,ううう,えええ

=TEXTJOIN(",",TRUE,A1:D3)

これを見て分かるように、二次元配列を渡した時はA1,B1,C1,D1,A2,B2,C2,D2,A3....とテーブルでTABキーを押した時と同じように処理されます。

横方向に並んだデータを交互に連結

＼	A	B	C	D
1	あああ	いいい	ううう	えええ
2	aaa	bbb	ccc	ddd
3
4	TEXTJOIN後	あああaaaいいいbbbうううcccえええddd

=TEXTJOIN("",FALSE,A1:A2,B1:B2,C1:C2,D1:D2)

ただし、この方法では列数が増えれば増えるほど引数を増やさなければならず、結構面倒だと思います。

データが二行しか無い場面でしか使えませんが、区切り文字に配列が使える事を生かしてこんな書き方もできます。

=TEXTJOIN(A2:D2,FALSE,A1:D1,"")

なんとか多数の行に対応した書き方は出来ないものかと考えていたら、TRANSPOSE関数を使えば実現できました。

ただし、配列数式とする必要があるため、CSEで確定が必要です。

{=TEXTJOIN("",FALSE,TRANSPOSE(A1:D2))}

ちなみに、配列数式はINDEX(式,0)で代用できるはずだ、と思ったのですが・・・。結果は惨敗です。

=TEXTJOIN("",FALSE,INDEX(TRANSPOSE(B41:E42),0))
=#VALUE!

=INDEX(TEXTJOIN("",FALSE,TRANSPOSE(B41:E42)),0)
=#VALUE!

原因がわかりませんが、ヘルプを見る限り、そういう仕様なのでしょう。

※上手い方法があれば教えて下さい。

TRANSPOSE 関数

手順 4: 最後に CTRL + SHIFT + ENTER を押す

CTRL + SHIFT + ENTER を押します。それはなぜでしょうか。それは TRANSPOSE 関数が配列数式でのみ使用されるためであり、最後は配列数式で終える必要があります。配列数式とは要するに、1 つ以上のセルに適用される数式です。手順 1 で複数のセルを選択しているので、式は複数のセルに適用されることになります。 CTRL + SHIFT + ENTER を押すと、次のような結果になります。

複数の区切り文字で連結

区切り文字を配列定数にすることで、複数の区切り文字を巡回させることが出来ます。

＼	A	B	C
1	あ	い	う
2	え	お	か
3	き	く	け
4
5	TEXTJOIN例1	あ\tい\nう\tえ\nお\tか\nき\tく\nけ
6	TEXTJOIN例2	あ\tい\tう\nえ\tお\tか\nき\tく\tけ

=TEXTJOIN({"\t","\n"},FALSE,A1:C3)
=TEXTJOIN({"\t","\t","\n"},FALSE,A1:C3)

これを上手く使えばCSVの作成などへの応用が出来ます。

TEXTJOINの活用テクニック

ここからが本題です。

結合要素に合わせて区切り文字を変化

例えば文字数が奇数なら奇、偶数なら偶を区切り文字として表示してみたりできます。

＼	A	B	C	D
1	あ	あいう	あい	あいうえお

=TEXTJOIN(IF(MOD(LEN(A1:D1),2)=0,"偶","奇"),,A1:D1)
あ奇あいう奇あい偶あいうえお

日付の連結

日付はシリアル値になるので、TEXT関数をかませる必要がある。

空欄を省略しつつ連結するならこうすると良い。

はけた様のツイート参考

＼	A	B	C	D	E
1	祝日1	祝日2	祝日3	祝日4	合成文字列
2	4月29日				2020/04/29
3	5月3日	5月4日	5月5日	5月6日	2020/05/03,2020/05/04,2020/05/05,2020/05/06
4
5	7月23日	7月24日			2020/07/23,2020/07/24

=TEXTJOIN(",",TRUE,TEXT(A2:D2,"yyyy/mm/dd;;"))

文字列の反転

こちらは、文字列処理奥義集で紹介しているCONCAT関数でも出来る数式です。

=TEXTJOIN("",TRUE,INDEX(MID("あいうえお",1+LEN("あいうえお")-ROW(INDIRECT("1:"&LEN("あいうえお"))),1),))

あいうえお
↓
おえういあ

反転することによって、今まで不可能だった数々の文字列処理が可能となります。

文字列への文字挿入

文字列abcdを一文字つづの配列に分解して、区切り文字,を挿入して連結します。

=TEXTJOIN(",",TRUE,INDEX(IFERROR(MID("abcd",{0,1,2,3,4},1),""),0))

abcd
↓
a,b,c,d

式の前後と区切り文字にダブルクォーテーションを付与すれば、文字列化もできます。

=""""&TEXTJOIN(""",""",TRUE,INDEX(IFERROR(MID("abcd",{0,1,2,3,4},1),""),0))&""""

abcd
↓
"a","b","c","d"

プログラミングで使う配列定数の生成なんかで重宝しそうですね。

※この例は固定長配列なので、実用するには動的配列にする必要があります。

ダブルクォーテーション付きCSVレコードの作成

上の例をセルで行うとこうなります。

reimeさんのツイートより

＼	A	B	C	D	E	F
1	11	22	33	44	55	"11","22","33","44","55"
2	1	123	456789		0	"1","123","456789","","0"
3	あ	い	う	え	お	"あ","い","う","え","お"
4	abc	def	gh	i	j	"abc","def","gh","i","j"
5	や		ゆ		よ	"や","","ゆ","","よ"

=""""&TEXTJOIN(""",""",FALSE,A1:E1 )&""""

シングルクォーテーションやパーセントで挟む場合

シングルクォーテーションなら文字を重ねなくて良いので簡単になります。

＼	A	B	C	D	E	F
1	11	22	33	44	55	'11','22','33','44','55'
2	1	123	456789		0	'1','123','456789','','0'
3	あ	い	う	え	お	'あ','い','う','え','お'
4	abc	def	gh	i	j	'abc','def','gh','i','j'
5	や		ゆ		よ	'や','','ゆ','','よ'

="'"&TEXTJOIN("','",FALSE,A1:E1 )&"'"
="%"&TEXTJOIN("%,%",FALSE,A1:E1 )&"%"

パーセントエンコーディングしたものをカンマ区切りで結合

パーセントエンコーディングしたい場合はENCODEURL関数を組み合わせます。

＼	A	B	C	D	E	F
1	11	22	33	44	55	11,22,33,44,55
2	1	123	456789		0	1,123,456789,,0
3	あ	い	う	え	お	%E3%81%82,%E3%81%84,%E3%81%86,%E3%81%88,%E3%81%8A
4	abc	def	gh	i	j	abc,def,gh,i,j
5	や		ゆ		よ	%E3%82%84,,%E3%82%86,,%E3%82%88

=TEXTJOIN(",",FALSE,ENCODEURL(A1:E1) )

※筆者は利用場面が少ないため、このフォーマットで使われるものか分かりません。

数字だけの抽出

＼	A	B	C
1	abc123def456	123456	←INDEXバージョン
2	abc123def456	123456	←配列数式バージョン
3	abc123def456	123456	←Office365バージョン

INDEXバージョン(Excel2019以前対応)

=TEXTJOIN("",TRUE,INDEX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),,0))

配列数式バージョン(Excel2019以前対応)

{=TEXTJOIN("",TRUE,INDEX(IFERROR(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""),0))}

Office365専用(2020/1スピルアップデート後)バージョン

=TEXTJOIN("",TRUE,IFERROR((--MID(A3,SEQUENCE(LEN(A3)),1)),""))

文字列処理の数式を読み慣れた人にはおなじみの、文字列を一文字毎に分解して数値判定を行い、数字のみを連結しているだけです。

f:id:Kotori-ChunChun:20190813180004p:plain

ご覧の通り、様々な文字列処理式に応用できることが考えられます。

数字だけを抽出してハイフン区切り

上記の区切り文字をハイフンに変えれば、ハイフン区切りの数値が作成できます。

何に使うのか想像つきませんが・・。

＼	A	B	C
1	abc123def456	1-2-3-4-5-6	←INDEXバージョン

=TEXTJOIN("-",TRUE,INDEX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),,0))

数値の集まりごとにハイフン区切り

2020/3/23 Twitterにて

＼	A	B
1	ab123cd45ef67	123-45-67

※2020/7時点では未実装のLET関数を使用した例です。

=TEXTJOIN("-",,LET(
  _s_, A1,
  _ren_,SEQUENCE(LEN("★"&_s_)),
  _mml_,MMULT(ISNUMBER(-MID("★"&_s_,_ren_+{0,1},1))*1,{1;2}),
  _m1_,FILTER(_mml_*_ren_,_mml_=1),
  _m2_,FILTER(_mml_*_ren_/2,_mml_=2),
  MID(_s_,_m2_, _m1_ - _m2_)
))

※次の式のほうが現実的です。

=SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(MID(A1,SEQUENCE(LEN(A1)),1)*1)*1,MID(A1,SEQUENCE(LEN(A1)),1)," ")))," ","-")

算数の計算式の作成

数式の作成と言っても、配列定数（1～10）の各要素に対して配列定数（+、+、-）を順番に挿入して連結してみたにすぎず、このままでは実用性は皆無です。

数式

=TEXTJOIN({"+","+","-"},TRUE,{1,2,3,4,5,6,7,8,9,10})

結果

1+2+3-4+5+6-7+8+9-10

英字のA～Zと数字の0～9を交互に連結

これも実用性があるかは謎ですが、とても面白い試みだと思いませんか？

数式

=TEXTJOIN(INDEX(ROW(INDIRECT("1:10"))-1,0),TRUE,INDEX(CHAR(CODE("A")+ROW(INDIRECT("1:26"))-1),0))

結果

A0B1C2D3E4F5G6H7I8J9K0L1M2N3O4P5Q6R7S8T9U0V1W2X3Y4Z

文字列をUNICODEの16進数コンマ区切り文字列に変換

佐藤嘉浩(Excelの魔法使い・スピルのひと)@yosatonetさんのツイートより

文字列をUNICODE番号に変換して16進数に変換して1データにするやつ！

パケットのホンモノっぽいダミーデータ流すのに使えるかも！

使えないかも！！

＼	A	B
1	あいうえお	3042,3044,3046,3048,304A
2	おはようございます！	304A,306F,3088,3046,3054,3056,3044,307E,3059,FF01
3	こんばんわ	3053,3093,3070,3093,308F
4	ABCDE	0041,0042,0043,0044,0045
5	12345	0031,0032,0033,0034,0035

SEQENCE版

=TEXTJOIN(",",FALSE,RIGHT("0000"&DEC2HEX(UNICODE(MID(A1,SEQUENCE(LEN(A1),1),1))),4))

※2019/8/13時点のExcel2019やOffice365ではSEQENCE関数が未実装なので使えません。

※SEQUENCE(LEN(A1),1)はROW(INDIRECT("1:"&LEN(A1)))で置き換える事ができます。

配列数式版

{=TEXTJOIN(",",FALSE,RIGHT("0000"&DEC2HEX(UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),4))}

INDEX版

=TEXTJOIN(",",FALSE,INDEX(
  RIGHT("0000"&DEC2HEX(UNICODE(
    MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
  )),4),
0))

※厳密な動作検証は行っていません。

これでエクセルバイナリエディタとか作れるかもしれませんね。

テーブルからアウトラインの作成

ここで言うアウトラインは、Wordで良く使う見出しの連番の部分のことです。

f:id:Kotori-ChunChun:20190813201214p:plain

サンプルデータ[TBL_OUTLINE]

＼	A	B	C	D	E	F	G
1	区	町1	町2	町3	町4	町5	アウトライン
2	千代田区	飯田橋	一番町	岩本町			1.千代田区飯田橋 2.千代田区一番町 3.千代田区岩本町
3	港区	芝公園	芝大門				1.港区芝公園 2.港区芝大門
4	江東区	枝川	越中島	扇橋	大島		1.江東区枝川 2.江東区越中島 3.江東区扇橋 4.江東区大島
5	品川区	小山台	戸越	中延	西大井	西五反田	1.品川区小山台 2.品川区戸越 3.品川区中延 4.品川区西大井 5.品川区西五反田

A1数式（G2の式）

="1."&A2&" "&TEXTJOIN(
  INDEX(CHAR(10)&1+ROW(INDIRECT("1:"&COLUMNS($B2:$F2)))&"."&A2&" ",0),
  TRUE,
  $B2:$F2)

構造化参照式

="1."&[@区]&" "&TEXTJOIN(
  INDEX(CHAR(10)&1+ROW(INDIRECT("1:"&COLUMNS(TBL_OUTLINE[@[町1]:[町5]])))&"."&[@区]&" ",0),
  TRUE,
  TBL_OUTLINE[@[町1]:[町5]])

この式の面白い所は区切り文字の方を変化させることで、連番を付与しているところです。

もう少し頑張れば、複数段階のレベル分けをしたアウトラインも作れそうです。

表データから配布資料を作る時に、すごく使えそうな気がします。

全ての文字を濁音にする（適当ネタ）

2020/5/3 #Excelクイズより

=TEXTJOIN("゛",TRUE,MID(A1,SEQUENCE(1,LEN(A1)),1) )&"゛"

A~Zの単語一覧から、EXCELに相当する単語をスペースで繋いで表示する数式

＼	B	D	E
1	Alfa	EXCEL	Echo X-ray Charlie Echo Lima
2	Bravo	↑入力値	↑数式
3	Charlie
4	Delta
5	Echo
6	Foxtrot
7	Golf
8	Hotel
9	India
10	Juliett
11	Kilo
12	Lima
13	Mike
14	November
15	Oscar
16	Papa
17	Quebec
18	Romeo
19	Sierra
20	Tango
21	Uniform
22	Victor
23	Whiskey
24	X-ray
25	Yankee
26	Zulu

=TEXTJOIN(" ",,
  INDEX($B$1:$B$26,
    CODE(MID(D1,SEQUENCE(LEN(D1)),1))-64))

固定長文字列ファイルデータ

同様の方法で文字数に合わせて、区切り文字のスペースの個数を変化させれば、昔なつかし固定長ファイルの出来上がりです。

＼	A	B	C	D	E
1	1	23	56	789	1 23 56 789
2	23	54	5	765	23 54 5 765
3	444	6	767	789	444 6 767 789
4	7	675	456	789	7 675 456 789

E1
=TEXTJOIN(REPT(" ",(4-LEN(A1:D1))),,A1:D1)
1  23 456 789

テーブルからCSVの作成

f:id:Kotori-ChunChun:20190813120848p:plain

＼	A	B	C	D	E	F
1	地名	最高気温	最低気温	最大風速	風向	最大瞬間風速
2	札幌	17.1	12.8	14.1	南南東	23
3	稚内	18.2	11.5	8.2	南南東	14
4	北見枝幸	19.6	12.4	9.1	南東	16
5	旭川	18.7	14.2	9.7	南	16
6	留萌	18.8	12	11.2	南南東	20
7	羽幌	19	12.4	10.9	南	18

配列定数

絶対参照

=TEXTJOIN({",",",",",",",",",","
"},FALSE,$A$1:$F$7)

構造化参照

=TEXTJOIN({",",",",",",",",",","
"},FALSE,TBL_TENKI[#すべて])

ここで区切り文字に指定している配列定数には数式を使えないという制限があるため、CHAR(10)の変わりに内部改行（Alt+Enter)を使わなければなりません。

数式のうち、ダブルクォーテーションで囲われた内部改行は文字列の一部として解釈するため、数式が見づらくなりますがこのように実現する方法もあります。

これを知らない人は、謎の数式エラーで混乱するかもしれません。

f:id:Kotori-ChunChun:20190814101656p:plain

動的配列

テーブル全体を示すセル範囲を3回指定しなければならないのが難点だが、行列の増減に対応しているので非常に扱いやすい。

絶対参照

=TEXTJOIN(
  INDEX(MID(REPT(",",COLUMNS($A$1:$F$7)-1)&CHAR(10),
    ROW(INDIRECT("1:"&COLUMNS($A$1:$F$7))),1),0),
  FALSE,$A$1:$F$7)

構造化参照[TBL_TENKI]

=TEXTJOIN(
  INDEX(MID(REPT(",",COLUMNS(TBL_TENKI[#すべて])-1)&CHAR(10),
    ROW(INDIRECT("1:"&COLUMNS(TBL_TENKI[#すべて]))),1),0),
  FALSE,TBL_TENKI[#すべて])

出力結果

地名,最高気温,最低気温,最大風速,風向,最大瞬間風速
札幌,17.1,12.8,14.1,南南東,23.2
稚内,18.2,11.5,8.2,南南東,14.4
北見枝幸,19.6,12.4,9.1,南東,15.8
旭川,18.7,14.2,9.7,南,16.4
留萌,18.8,12,11.2,南南東,19.7
羽幌,19,12.4,10.9,南,18

問題点

ただ、TEXTJOINを使ったCSV作成には問題があり、内部改行を含んでいるセルが含まれる場合にダブルクォーテーションが付与されません。

CSVの改行コードやダブルクォーテーションの取り扱いについては仕様が色々あるので詳しくは言及しませんが、ダブルクォーテーション無しというのは解読側にとって優しくないフォーマットなのではないかと思います。

＼	A	B	C
1	地名	最高気温	最低気温
2	札幌	17.1	12.8
3	稚内	18.2	11.5
4	北見枝幸	19.6	12.4
5	旭川	18.7	14.2

TEXTJOINで連結した結果

地名,最高気温,最低気温
札幌,17.1,12.8
稚内,18.2,11.5
北見
枝幸,19.6,12.4
旭川,18.7,14.2

テーブルをメモ帳にコピペして、タブをコンマに置換した結果

地名,最高気温,最低気温
札幌,17.1,12.8
稚内,18.2,11.5
"北見
枝幸",19.6,12.4
旭川,18.7,14.2

この性質を考えると、この方法が使える場面は限られるかもしれません。

全てのデータにダブルクォーテーションを付けることが出来れば、なんとか使えるCSVになりそうな気がします。

HTMLのTABLEタグを作成

手動で作成するとなると大変面倒な<TABLE>タグですが、これを自動生成することも可能となりました。

式がとても読みづらくなっただけで、基本的な考え方はCSVと変わりません。

入力値

＼	A	B
1	区	町1
2	千代田区	飯田橋
3	港区	芝公園
4	江東区	枝川
5	品川区	小山台

数式（固定表）

="<table>"&CHAR(10)&
  "  <tr>"&CHAR(10)&
    "    <td>"&
       TEXTJOIN({"</td>
    <td>","</td>
  </tr>
  <tr>
    <td>"},FALSE,A1:B5)&
    "</td>"&CHAR(10)&
  "  </tr>"&CHAR(10)&
"</table>"

結果

<table>
  <tr>
    <td>区</td>
    <td>町1</td>
  </tr>
  <tr>
    <td>千代田区</td>
    <td>飯田橋</td>
  </tr>
  <tr>
    <td>港区</td>
    <td>芝公園</td>
  </tr>
  <tr>
    <td>江東区</td>
    <td>枝川</td>
  </tr>
  <tr>
    <td>品川区</td>
    <td>小山台</td>
  </tr>
</table>

しかし動的な表に対応させるとなると、急激に難解な数式になります。

TEXTJOINの前に増えたもの

"<table>"&CHAR(10)&
  "  <tr>"&CHAR(10)&
    "    <td>"&

TEXTJOINの後に増えたもの

&
    "</td>"&CHAR(10)&
  "  </tr>"&CHAR(10)&
"</table>"

TEXTJOINの区切り文字を変化HTMLタグに置き換えたもの

{"</td>
    <td>","</td>
  </tr>
  <tr>
    <td>"}

ここでも、区切り文字に指定する配列定数で数式は使えないという制限が障害となるため、CHAR(10)の変わりにAlt+Enterで対応しています。読みづらいですね。

この区切り文字の生成がちょっとした鬼門で、CSVで以下のようにしていた数式が爆発します。（この式はMID(",,,,,\n",n文字目,1文字)という形で区切り文字を抽出する式です）

  INDEX(MID(REPT(",",COLUMNS(TBL_TENKI[#すべて])-1)&CHAR(10),
    ROW(INDIRECT("1:"&COLUMNS(TBL_TENKI[#すべて]))),1),0)

CSVとHTMLの区切り文字の違いは次のようになります。

＼	列区切り	行区切り
CSV	`(前の項目),(次の項目)`	`(前の項目)Alt+Enter(次の項目)`
HTML	`(前の項目)</td><td>(次の項目)`	`(前の項目)</td></tr><tr><td>(次の項目)`

このように区切りの文字数が不定となるため、MID関数で抽出することは不可能です。

引数には配列を返さなければならないので、一時は黒魔術SPLIT互換関数が必要かと思われました。

=INDEX(SUBSTITUTE(
  MID(
    SUBSTITUTE(_s_,_f_, REPT("★",LEN(_s_))),
    ROW(INDIRECT("A1:A"&LEN(_s_)))*LEN(_s_)-(LEN(_s_)-1),
    LEN(_s_)
  ),
  "★",
  ""
),_idx_)

が、この式を使うと_s_の出現回数があまりにも多く、</td>～<td>と</td></tr><tr><td>を生成する式

=REPT("</td>"&CHAR(10)&"    <td>",COLUMNS(TBL_TENKI[#すべて])-1)&
"</td>"&CHAR(10)&"  </tr>"&CHAR(10)&"  <tr>"&CHAR(10)&"    <td>"

を6回も書く必要が出てきます。これはスマートでは無い。というわけで・・・

</td>の後に半角スペースを追加して、区切り文字を固定長にしました。（29文字）

＼	列区切り	行区切り
CSV	`(前の項目),(次の項目)`	`(前の項目)Alt+Enter(次の項目)`
HTML	`(前の項目)</td><td>(次の項目)`	`(前の項目)</td></tr><tr><td>(次の項目)`
HTML改	`(前の項目)</td> <td>(次の項目)`	`(前の項目)</td></tr><tr><td>(次の項目)`

これにより、従来のMID関数と同じ考え方で実装することに成功しました。

INDEX(MID(
REPT("</td>               "&CHAR(10)&"    <td>",COLUMNS(TBL_TENKI[#すべて])-1)&
"</td>"&CHAR(10)&"  </tr>"&CHAR(10)&"  <tr>"&CHAR(10)&"    <td>",
ROW(INDIRECT("1:"&COLUMNS(TBL_TENKI[#すべて])))*29-28,29),0),

構造化参照数式[TBL_TENKI]

TBL_TENKIの部分がテーブル名になるので、3箇所の変更が必要です。

="<table>"&CHAR(10)&
  "  <tr>"&CHAR(10)&
    "    <td>"&TEXTJOIN(
      INDEX(MID(
        REPT("</td>               "&CHAR(10)&"    <td>",COLUMNS(TBL_TENKI[#すべて])-1)&
          "</td>"&CHAR(10)&"  </tr>"&CHAR(10)&"  <tr>"&CHAR(10)&"    <td>",
        ROW(INDIRECT("1:"&COLUMNS(TBL_TENKI[#すべて])))*29-28,29),0),
      FALSE,TBL_TENKI[#すべて])&
    "</td>"&CHAR(10)&
  "  </tr>"&CHAR(10)&
"</table>"

簡略化に成功したとは言え、なかなかにカオスですね。

Markdownの表を作成

数式は割愛しますが、Markdownの表を作ることも難しくはないでしょう。

| ＼   | A        | B      |
| ---- | -------- | ------ |
| 1    | 区       | 町1    |
| 2    | 千代田区 | 飯田橋 |
| 3    | 港区     | 芝公園 |
| 4    | 江東区   | 枝川   |
| 5    | 品川区   | 小山台 |

（気が向いたら追記予定）

SQLの構文を作成

EXCELでSQLの構文を作ることってありますよね。

ないです？いや、ありますって。

絞り込みの式とか

SELECT *
  FROM テーブル名
WHERE カラム名 NOT IN (値1,値2,値3,・・・)

追加のSQLとか

INSERT INTO テーブル名
     VALUES ('1', 'コトリ', 'ちゅんちゅん', '2019/08/14' ,'2019/08/15')

和集合のSQLとか？

SELECT ユーザー名, 年齢, 住所　FROM　ユーザー１
 UNION
SELECT ユーザー名, 年齢, 住所　FROM　ユーザー2

数式のサンプルは省略します。

型によるシングルクォーテーションの追加とか、日付のキャストとか、NULLや空欄の扱いをどうするかが要注意ですかね。

参考

【SQL】ExcelでできるだけスマートにINSERT文を作成する方法を模索した

FizzBuzz

みんな大好きFizzBuzzも余裕です！

=TEXTJOIN(" ",,CHOOSE((MOD(ROW(1:100),3)=0)+(MOD(ROW(1:100),5)=0)*2+1,ROW(1:100),"Fizz","Buzz","FizzBuzz"))

1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fizz 13 14 FizzBuzz 16 17 Fizz 19 Buzz Fizz 22 23 Fizz Buzz 26 Fizz 28 29 FizzBuzz 31 32 Fizz 34 Buzz Fizz 37 38 Fizz Buzz 41 Fizz 43 44 FizzBuzz 46 47 Fizz 49 Buzz Fizz 52 53 Fizz Buzz 56 Fizz 58 59 FizzBuzz 61 62 Fizz 64 Buzz Fizz 67 68 Fizz Buzz 71 Fizz 73 74 FizzBuzz 76 77 Fizz 79 Buzz Fizz 82 83 Fizz Buzz 86 Fizz 88 89 FizzBuzz 91 92 Fizz 94 Buzz Fizz 97 98 Fizz Buzz

Days of the Week Songの歌詞を少ない文字数の数式で表現

遊牧家族様の #エクセルクイズツイートより

Sunday, Monday, Tuesday, Wednesday, Thursday, Thursday, Friday, Saturday, Sunday comes again

式

=TEXTJOIN(", ",,TEXT({1,2,3,4,5,5,6,7,8},"dddd"))&" comes again"

会話の相槌を3パターンのみで乗り切る

※マイナーなネタでごめんなさい。

＼	A	B
1	質問	相槌
2	もしかして、欲しいなぁって思ってるんじゃないでしょうね？	なるほど
3	よろしければ私がお作りさせていただきますが？	すごいな
4	ちょ、ちょっと！この子本当に作るわよ？	悪いのは君じゃない
5	風見さん！私にサメさん作らせてください！
6	しかも、風見さん用にラインストーンをつけて、さらに可愛くキラキラにさせていただきます！だから毎日しっかり着用してくださいませんか？
7	このサメさんにはラインストーンが似合う！風見さんもそう思っていただけたんですね？
8	それでは、決定事項とさせていただきます！
9	クオリティが低くならないように努力します！
10	えへへっ♪
11
12	もしかして、欲しいなぁって思ってるんじゃないでしょうね？なるほどよろしければ私がお作りさせていただきますが？すごいなちょ、ちょっと！この子本当に作るわよ？悪いのは君じゃない風見さん！私にサメさん作らせてください！なるほどしかも、風見さん用にラインストーンをつけて、さらに可愛くキラキラにさせていただきます！だから毎日しっかり着用してくださいませんか？すごいなこのサメさんにはラインストーンが似合う！風見さんもそう思っていただけたんですね？悪いのは君じゃないそれでは、決定事項とさせていただきます！なるほどクオリティが低くならないように努力します！すごいなえへへっ♪	←TEXTJOIN後

=TEXTJOIN(B2:B3,FALSE,A2:A10)

という感じで連結もできます。

↓元ネタ

ニコニコ大百科 - 悪いのは君じゃない

もうこれ以上は付き合いきれないと感じた時は「なるほど」「すごいな」「悪いのはきみじゃない」この３つで対応することにしている。多くの場合、それで十分会話は成立するのだ。

実際、俺は過去にこのスリーワードで四時間ほどの女性との会話を乗り切ったことだってある。

本来なら発言の前にA.もしかして～～～～、B.なるほどのようにA.``B.を追記したいのだが、残念ながら配列に一括で付け足すようなことは出来なかったのが悔やまれます。

~~※配列数式にすれば出来るような気がしますが、調査中に力尽きました。~~

TEXTJOINの不満

ここまで褒め称えておいてなんですが、不満もあります。

なんで、Google スプレッドシートとの互換性（JOIN関数）を良くしてくれなかったのか。
なんで、Excel2016に完全搭載してくれなかったのか。
なんで、区切り文字の引数を1つだけにしたのか。2つあれば二次元配列の取り回しが楽になったのに。
配列同士の連結がしたかった。
- たとえば、「悪いのは君じゃない」ネタ表の例ですが、本来ならばA列には「A.」B列には「B.」を先頭に付与して、発言者を分かるようにしてから連結したいわけです。
- それをやろうとして以下のように書いても、それは出来ません。
- =TEXTJOIN("B."&B1:B10,FALSE,"A."&A1:A10)
- 実現はできますが、配列数式として確定する必要があります。