えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

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

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

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

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


きっかけ

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

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

ハッシュタグは #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の凄いところ

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

今までは文字列の連結をするときにA1&B1&C1CONCATENATE(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に相当する単語をスペースで繋いで表示する数式

A B C 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の構文を作成

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

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


絞り込みの式とか

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


追加のSQLとか

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


和集合のSQLとか?

SELECT ユーザー名, 年齢, 住所 FROM ユーザー1
 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)

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

↓元ネタ

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

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

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

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

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


TEXTJOINの不満

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

  • なんで、Googleスプレッドシートとの互換性(JOIN関数)を良くしてくれなかったのか。

  • なんで、Excel2016に完全搭載してくれなかったのか。

  • なんで、区切り文字の引数を1つだけにしたのか。2つあれば二次元配列の取り回しが楽になったのに。

  • 配列同士の連結がしたかった。

    • たとえば、「悪いのは君じゃない」ネタ表の例ですが、本来ならばA列には「A.」B列には「B.」を先頭に付与して、発言者を分かるようにしてから連結したいわけです。

    • それをやろうとして以下のように書いても、それは出来ません。

    • =TEXTJOIN("B."&B1:B10,FALSE,"A."&A1:A10)

    • 実現はできますが、配列数式として確定する必要があります。


この記事の内容の注意

一部、とても複雑な数式を例示しましたが、「実用性はあまり無い」です。ネタも多分に含まれているのでご注意ください。

特にフルCSV作成なんかは、Excel関数より優れた手法が既に確立されてます。

CSVなら名前を付けて保存しても良いですし、メモ帳コピペでも良いはずです。

フォーマットにこだわるならVBAで複雑な判定をしたほうが、ちゃんと例外対応できるので安心できます。

わざわざ数式を使ってまでCSV等を作るのは、かなりイレギュラーな用途ではないかと推測します。

また、仮に数式を使うにしても、単一セルに複雑な式を押し込むよりも、レコードごとの連結をして、その後に全レコードの連結をすればCSVになりますから、そのほうが可読性は高いでしょう。(もはやロマンですね)


参考資料

定数、配列定数、参照、配列参照という表現を使っていますが、よく分からない人は以前執筆した下記の記事の序盤を御覧ください。

www.excel-chunchun.com


途中で出現した難解な文字列処理の数式は、下記の記事にて紹介しています。

www.excel-chunchun.com


Microsoft公式

support.office.com - TEXTJOIN関数


未対応のバージョンの古いExcelのためのVBAによる互換関数

経理・会計事務所向けエクセルスピードアップ講座 - textjoin関数の使い方とExcel2013以前の古いエクセルで使う方法


furyutei様が極めて完成度の高いアドインを公開されていますので、本格的に使う場合はこちらを導入すると良さそうです。

furyutei/Excel2016Func


TEXTJOINで配列が使えるということを、ここの記事を読んで知りました。素敵です。

できるネット - TEXTJOIN関数の使い方。区切り記号を挿入しながら複数の文字列を連結する


TEXTJOIN/CONCATが無い頃は、こんな方法で連結するのが一般的でした。今でもよくお世話になります。

わえなび ワード&エクセル問題集 - 【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う


まとめ

TEXTJOINの奥深さは伝わりましたでしょうか。

中には別の機能のほうが良いものもありますが、Excel関数は事前に数式を組んでおけばリアルタイムに更新してくれる点や、データの入力編集が簡単という点にアドバンテージがあります。

ちょっとした文字列の連結においては手軽にできて便利です。帳票作成、CSV作成、SQL生成、VBA作成などでは欠かせない存在となりつつあります。

Excel2019を買うかOffice365を契約しないと使えませんが是非活用していきましょう!


↓ というわけで、良い子の皆は Office365を契約してTEXTJOINしよう。

↓ 買い切り版でも2019ならTEXTJOINできます!!!

以上


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)

プライバシーポリシー