えくせるちゅんちゅん

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

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では2019年8月現在でも使用できない状態なので、おそらく一生実装されることはないでしょう。

したがって、2016で使えている人は(たぶん)皆無です。堂々と使えると断言出来るのは2019からだと認識しています。

これはつまり、Excel2019の新機能と言っても過言ではありません。

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


同時に実装された関数

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

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

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


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の活用テクニック

ここからが本題です。


文字列の反転

こちらは、文字列処理奥義集で紹介している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"

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

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


数字だけの抽出

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

INDEXバージョン

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

配列数式バージョン

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

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

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))


数式の作成

数式の作成と言っても、配列定数(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]])

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

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

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


テーブルから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文を作成する方法を模索した


会話の相槌を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)

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


参考資料

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

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の奥深さは伝わりましたでしょうか。

いろいろとTEXTJOINで複雑な数式を例示しましたが、はっきり言って「実用性はあまり無い」です。大半はネタの域を超えていません。

残念ながらここで紹介した多くの操作は、Excel関数より優れた手法が既に確立されてます。

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

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

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

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

一方で、SQLの生成やちょっとした文字列の連結においては、事前に数式を組んでおけば作業に集中できますし、リアルタイムで常に更新してくれるのは有り難いです。

これを見て、なにかの参考になれば幸いです。

以上


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

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

プライバシーポリシー