CSV形式の文字列を生成する

ここでご紹介させて頂くのは 「複数のセルを参照し1つのセルでCSV形式の文字列を生成する」と言ったものです。

 「そんな事はとっくの昔に出来ているよ！」とツッコミが入るかもしれません(^^;

はい、その通り。作業セルを使えば割と簡単に数式でCSV形式の文字列を作れちゃうのです。
ですが、これはどうでしょう？

「複数のセルを参照し1つのセルで “作業セルを使わず”にCSV形式の文字列を生成する 」

「………………」

「え？作業セルを使わずにCSV形式の文字列を？！」

「で、できねぇ…」

と言いたいところですが、実は

「出来らぁ！」

なんです！

古いExcelはもう使っておらず実際試していないので良くわからないのですが、最新のExcelでは確実に出来るようになっちゃっているのです。

嘘だと思いますよね？なので先ずは実例をお見せします。

はい。↑↑ ↑ がその結果です。
数式はB2セルにしか入っておりません。それでこの結果が返ってくるのです！

では、B2セルにはどのような数式が入っているのか実際に見てみましょう。

こんな感じです。
「TEXTJOIN関数」と「VALUETOTEXT関数」を組み合わせれば割と簡単に作れちゃいます。

さて、この関数を初めて見ると言う方もいらっしゃると思うので一つ一つ解説していくこととします。

なお、ここから先は「TEXTJOIN関数」と「VALUETOTEXT関数」 の簡単な解説となりますので、そんなの既に知ってるよ！って方は次の見出しまで読み飛ばして頂いて結構です。

先ずは「VALUETOTEXT関数」からです。
この関数の引数は「VALUETOTEXT（値，書式）」となります。

引数の“書式”には「0（簡潔）」か「1（正確）」を設定します。と言っても分かりづらいので実例を交えて解説しましょう。

ご覧の通り書式の引数が「0」の場合はそのままの値、「1」の場合は文字列が二重引用符で囲まれております。
まぁ、簡単に言えばそれだけの違いなんですけどね。

上記は分かり易くB1セルの文字列だけ値に指定してみましたが、これをB1セルからF1セルまで纏めて指定するとどうなるでしょうか？
では、早速やってみましょう。

なんと、スピル(※)したではありませんか！

スピルしたことで、B9セルからF9セルに二重引用符で囲まれた文字列が結果として返ってきました。

※スピルにつきましては、最近のExcelで新しく追加された機能となりご存じない方もいらっしゃると思いますが、ブログ記事やYouTube等でより詳しく解説されておられる方がいらっしゃいますのでそちらをご覧ください。

……とまぁ、ここまで解説しておいて言うのもなんですが、この関数は弱点がありまして（弱点というか本来の使用用途ではないため致し方ない）、数値だと二重引用符で囲まれないのです。その結果が以下の通りとなります。

まぁ、とりあえずそんなことは置いておいて……

実はこれよりもっとシンプルな数式があって、その数式であれば数値でも二重引用符で囲めることができます。
それが、こちら↓↓↓

もはや関数すら使用していない、とっても単純な数式です。
ただ、VALUETOTEXTを利用した方が二重引用符で囲む場合と囲まない場合で切替が簡単（引数の書式を0か1で切り替えるだけ）なので、本解説ではVALUETOTEXTを使用することとします。
（後述してあることを先に言ってしまいますが、本編でご紹介する数式は「TEXT関数」を使うので何れにしてもVALUETOTEXTで扱う文字列が全て文字列形式になるので結果的に何の問題もなかったりします）

続いては「TEXTJOIN関数」の解説です。
関数の名前から大体お察し頂けるかと思いますがこの関数は、複数のセルに入っているテキストを繋げる関数となります。

セル間の値を繋げると言うと、なんとなく「CONCAT関数」と同じ役割なのでは？と考えられる方もいらっしゃると思いますが、TEXTJOIN関数はCONCAT関数より指定可能な引数が多く、CSV形式の文字列生成に便利な機能を持っております。

この関数の引数は「TEXTJOIN（区切り文字，空のセルは無視，テキスト１，テキスト２，...）」となります。 

“区切り文字”は読んで字の如く、テキスト同士を繋げる場合の区切り文字を指定します。
カンマ形式のCSVの前提で、ここでは「,（半角カンマ）」を指定していきます。

“空のセルは無視”は「TRUE（空のセルは無視）」か「FALSE（空のセルを含める）」を指定します。（1（TRUE） か、0（FALSE）の指定でもOKです）

“テキスト１”以降は繋げたい値の入ったセルの参照範囲を纏めて指定します。

それだけで簡単にカンマ区切りの文字列が作れてしまうのです。これがTEXTJOIN関数の便利な点です。

では、実際にVALUETOTEXTで二重引用符で囲った文字列をTEXTJOIN関数でカンマ区切りを指定して繋げてみましょう。

とまぁ、こんな感じです。

結果として、B2セルの戻り値はVALUETOTEXT関数とTEXTJOIN関数を組み合わせただけですのでB7セルと同じ戻り値を得られたということになります。

【数値の書式を好みの形式に変換する小技】

さて、ここまでダラダラ解説してきましたが、実は今まで解説してきた関数だけを使った数式では実用に耐え得るには少々簡素過ぎるのです。

ええ、それは分かってますとも。なぜなら、TEXTJOIN関数とVALUETOTEXT関数の組合せだけでは表現しきれない書式があるからです。

例えばCSV形式にしたいセル範囲の書式が日付形式だったり、%形式、時刻形式などの値が含まれていたらこれまで紹介してきた書式は一気に崩壊してしまいます。

実際に今まで紹介してきた数式を使ってそれらの値をCSV形式に変換してみましょう。

はい、この通り。目も当てられません。

％の表現は0.00で表現しても、まぁ、間違いではないです。
ただ、日付と時刻に関しては実用的かと言うとちょっと（いや、かなり？）無理がありますよね。

ここからはExcelの中級者～上級者であればご存じであろう、「CELL関数」と「TEXT関数」の組合せによって解決していく必要があります。

「はい、また新しい関数出てきたよ～」って方はご安心下さい。この後、実例を交えて解説していきます。

「そんなのとっくの昔から知ってっから！」って方は、もう少々ダラダラ解説にお付き合いください。

では、実際に「CELL関数」と「TEXT関数」を使ってどのように書式を変えていくのか？について、作業セルを用いて解説していきましょう。 

ちょっと複雑になってきましたね。

先ずは作業セル①にはご覧の通り「CELL関数」が入っております。
CELL関数は第一引数の“検査の種類”に“format”を指定することで、第二引数の“対象範囲”に指定したセルがどのような書式か判別可能な文字列値を返してくれます。
（CELL関数の引数に関しての詳細は以下にMicrosoftのサポートページ（CELL関数）を貼っておきますのでそちらをご覧ください。）

続いて作業セル②では、CELL関数の戻り値を元にTEXT関数の第二引数に指定する表示形式に変換しています。

SWITCH関数は割と最近追加された関数なので、見慣れない方が多いかと思いますがここでは詳細な解説は割愛します。
要するに、CELL関数の戻り値が、D3であれば"yyyy/m/d"を返し、D9であれば"h:mm"を返す。何れの条件にも当てはまらなければ"G/標準"を返すと言った条件分岐が可能な便利な関数なのです。

作業セル③では、TEXT関数を使って対象文字列を範囲指定し、なおかつ、作業セル②で得られた表示形式を引数とすることで対象文字列の値を任意の文字列形式(※)に変換かけております。
※上記の作業セル②の数式はあくまで一例です。実際、CELL関数で得られる文字列値はもっと多いため、必要に応じ条件分岐を増やす必要があります。

これらを前段で紹介したVALUETOTEXT関数で二重引用符で囲った後、TEXTJOIN関数でカンマ区切りで繋げてしまえばCSV形式の文字列が生成できると言った次第です。

【極意】

さぁ、ここからが本題！
今までの解説の中でもご存じの方は「まぁ、そりゃそうだわ」で終わる話かと思います。

CELL関数とTEXT関数でこんなことが出来ることを知らなかった方は目から鱗かと思います。

でも、これらの関数を持ってしても今までのExcelの常識では「“作業セルを使わず”にCSV形式の文字列を生成する」なんてことは実現できなかったと思います。

ところが最近のExcelではSPILL機能の実装によりそんな数式も実現可能になったのです。
（古いExcelを使っておらず試してみる事が出来ないので、実際に古いExcelで動かしてみたら実現できる可能性もありますのでその際はご容赦ください^^;）

とにかく百聞は一見です。実際に作業セルを一切使わずに「複数のセルを参照し1つのセルで“作業セルを使わず”CSV形式の文字列を生成する」 極意を見て貰いましょう！

はい、ドン！

こんな感じです。

「いやいや、今まで解説してきたCELL関数やらTEXT関数やらを繋げればこんなの簡単に出来るっしょ！」

と突っ込まれそうですが（汗

まぁ、実際そう上手い話しはないのですけどね。と言ってもなかなか伝わらないと思うので実際にやってみましょう。

あ、あれ？？（汗

B8セルと同じ結果になると思ったのに、日付や時刻などの書式が崩れてる？！

はい。実はCELL関数で指定した第二引数の参照はセル範囲を指定した場合でも一番最初のセルの文字列値しか取得できないのです。

と言っても実際に見てみないと分からないと思いますので、CELL関数の参照にセル範囲を指定した場合、どのような結果になるかを実例で解説します。

とまぁ、こんな感じですね。（B11セル参照）

C11セル～F11セルまでスピってくれれば良いのですが、世の中そう簡単に上手くはいきません。

では、前述ではどのようにして「複数のセルを参照し1つのセルで CSV形式の文字列を生成した」のか？の解説となりますが、結果を見て貰った方が早いと思うので早速見て頂きます。

お分かり頂けましたでしょうか？

そう、OK例の赤字下線部を見て頂ければお気づきかもしれませんが、CELL関数の引数の参照を「INDIRECT関数」で直接指定してしまうのです。

「ADDRESS関数」はINDIRECT関数の引数である参照文字列に渡すセル番地文字列を生成するために使っているのですが、「ROW関数」と「COLUMN関数」に対象文字列のセル範囲を指定することで、対象文字列のセル範囲のセル番地文字列を取得することができるというわけです。

結果、INIDIRECTで指定した参照範囲の文字列値をCELL関数が取得できたということですね。

文章では伝わりづらいので、実際にどういう動きをしているかを分解した計算結果で見て頂きましょう。

このような形で、先ずはADDRESS関数で対象文字列のセル番地の文字列を取得しています。

次に、ADDRESS関数で生成したセル番地文字列をINDIRECT関数に突っ込むのですが、INDIRECT関数単体ではエラーになるのでCELL関数と組み合わせて使用する必要があります。

CELL関数の参照に、取得したいセル番地の文字列が入ったセル範囲を指定したINDIRECT関数を引数として渡してあげると、対象文字列の文字列値が返ってくるという絡繰りとなっております。文章で書くとちょっとややこしいですね（汗

如何でしたでしょうか？

CELL関数の参照に、複数のセル番地を指定したINDIRECT関数を渡してあげるとCELL関数がスピってくれると言うのは中々ご存じの方は少ないのではと思います。

さて、仕組みが分かったところで、最後にCELL関数で取得した文字列値について比較的あらゆるパターンを網羅した数式を紹介いたします。

先ずはCSV形式に変換する対象の文字列のサンプルです。

上記のように、CELL関数で取得できる文字列値は色々なパターンが存在します。

今回ご紹介する数式は、CELL関数の戻り値全てのパターンを網羅出来ているわけではありませんが、大抵の実務で利用するパターンは含まれているのではないかと思います。

ちなみに、上の表の文字列値は実際にCELL関数で取得した文字列値を表示しております。
これに対し、表示形式（参考）の値に変換していくのですがこちらの表の内容はXLOOKUPなどの関数で文字列値を表示形式に変換するための表ではなく、あくまで今回ご紹介する数式を使った場合に変換される表示形式の一例です。

実際は小数部の桁数によって文字列値の値が変わったり（値が0.00だと文字列値がF2、値が0.0000だと文字列値がF4）、対象文字列の値が書式設定してあるかないかで、文字列値の内容が変わってくる（上記の表をご覧頂ければお分かりかと思いますが、数値であっても書式が設定されていれば、Fxで返ってくるし、セルに入力したままだとGで返ってきます）ので単純にこの表からXLOOKUPなどで文字列値を表示形式に変換するのは少々困難です。（対象文字列に実際に入るであろうと想定される小数部の桁数に応じた文字列値に適応した変換表が必要となる）

それはさておき、早速こちらの表のA2セルからA22セルまでを作業セルを使わずにCSV形式に変換します。

はい、このような形となります。

数式が気持ち悪いですね（汗

最近増えた「LET関数」も使っており、ここまで来ると最早ただのゴリ押しでしかないので、数式の中身をご理解頂く必要はないです。正直ここまで来るともう初心者の方ではついてこれないと思います（汗
（この数式の肝は前段で紹介したCELL関数とINDIRECT関数の組合せ部分なので）

数式につきましては以下に貼っておきますので、実際に使用して見る際は「A2:A22」の部分（一箇所）をCSV形式にしたい文字列の参照範囲に変えて頂くだけでご利用可能です。

【数式】
=LET(subString,A2:A22,intSubString,INT(subString),formatSubString,CELL("format",INDIRECT(ADDRESS(ROW(subString),COLUMN(subString)))),clipFwFormatSubString,LEFT(formatSubString,1),clipBwFormatSubString,SUBSTITUTE(REPLACE(formatSubString,1,1,""),"-",""),TEXTJOIN(",",0,VALUETOTEXT(TEXT(subString,SWITCH(SWITCH(clipFwFormatSubString,"F",1,"P",1,"C",1,",",1,SWITCH(formatSubString,"D1",2,"D2",2,"D3",2,"D4",2,"D5",2,"D6",3,"D7",3,"D8",3,"D9",3,0)),1,IF(clipBwFormatSubString="0","0","0."&REPT(0,clipBwFormatSubString)),2,"yyyy/m/d",3,"h:mm:ss",IFS(ISBLANK(subString),"",ISNUMBER(subString),IF(intSubString=subString,"0","0."&REPT("0",LEN(SUBSTITUTE(subString,".",""))-LEN(intSubString))),TRUE,""))&IF(clipFwFormatSubString="P","%","")),1)))

ただし、この関数も完璧でないことはご理解の上、利用頂ければと思います。
例えば、値の書式に「*hh:mm:ss」形式(アスタリスク付き)の時刻形式を設定されていると文字列値がGとなるため、上手く変換できません。

また、文字列値がSから始まる表示形式は実務としてはあまり使わないであろうことから考慮しておりません。

実際にそれらを実務で使用している場合やCSVに変換した時の書式を変更したい(日付形式を“yyyy/m/d”ではなく“yyyymmdd”にしたいなど)といった場合は、当該数式を任意にカスタマイズしてご利用頂く必要がございます。

とはいえ、カスタマイズして頂くにしてもどんな数式なのか読めないとどうしようもありません。
折角なのでこちらの数式を分解して解説していきます。（長くなりますので実例は割愛させて頂きます）

まずは、そもそもこの数式で使用している「LET関数」とは何か？ですが、簡単に言うと変数を定義する変数定義部と戻り値を取得するための数式定義部にで構成された、若干、プログラミングに似た関数となります。

LET関数を使わなくても表現は可能ですが、LET関数を使用することで数式が読みやすいのと、何より数式のメンテナンスが楽というメリットがあります。（いやいや、充分読みづれぇわ！と言うツッコミが聴こえ…はい、その通りです汗）

実際に上記の数式を見て頂くとCSVへ変換する対象文字列のセル参照範囲「A2:A22」は一箇所のみの指定となっています。
これは、subString（subject stringの略※ ）という変数にセル参照範囲を入れて、その変数を後続に記載する数式で使えると言った代物です。

※subject stringはJAVAのメソッドとは関係ありません。当方、プログラミングは全くのド素人で変数名の定義はいい加減に決めておりますので何卒ご容赦下さい 。本当は変数名を日本語にすれば良いのかもしれませんけどね。（変数名に日本語を付けることは可能です）
プログラマーみたいにちょっとカッコつけてみたかっただけです。はい、すみません（汗）

なお、変数にはセル参照範囲だけではなく関数を使った数式の計算結果も入れることが出来るので、1つの数式内に何度も同じ関数を使った数式を記述する場合などは、変数に置きかえておくと1回の記述で良いことになります。

プログラミングに詳しい方はこの辺は良くご理解頂けるものかと思います。

LET関数を詳しく解説し始めると膨大になりそうなので、詳細はグーグル先生に聞いて頂くとして、早速、数式を分解して解説していきましょう。

ここからは数式を分解しながら赤字で解説を入れていきます。
ここでは解説していない関数を色々使用しておりますが、割愛させて頂きます。
申し訳ございませんが、解説しきれていない関数についてご存じない方はグーグル先生にお尋ね頂けますと幸いです（汗

先ずは「LET関数」の変数の定義部分から。

=LET(
subString,A2:A22,　→対象文字列のセル参照範囲の値を変数に入れる
intSubString,INT(subString),　→対象文字列の小数部を除いた値を変数に入れる
formatSubString,CELL("format",INDIRECT(ADDRESS(ROW(subString),COLUMN(subString)))),
　→　対象文字列の文字列値を取得（これまで解説してきた内容）
clipFwFormatSubString,LEFT(formatSubString,1)
　→　対象文字列の文字列値の頭1桁を取得（F4の場合はFを取得）
clipBwFormatSubString,SUBSTITUTE(REPLACE(formatSubString,1,1,""),"-",""),
　→　対象文字列の文字列値の頭1桁を覗いた値を取得（F4の場合は4を取得）
　　　（※小数部の桁数取得が目的）

ここまでが変数の定義部分となります。
上記のように一つ一つ分解していけば、この数式でどんなことをやっているのか理解頂き易いかと思います。

続いて、値取得する数式定義部となります。
こちらも一つ一つ分解して見ていきましょう。

TEXTJOIN(",",0,VALUETOTEXT(
　→　二重引用符を付加したテキストをカンマ区切りで繋げる
　　　（上記はこれまで解説してきた内容ですね。VALUETOTEXTの中身はここから解説していきます）

　TEXT(subString,
　　SWITCH(
　　→　対象文字列を任意の表示形式に変換する（TEXT関数）
　　　　変換対象の表示形式はSWITCH関数を用いて条件分岐して決めていきますので後述で解説します。
　　　　(SWITCHの中に更にSWITCHが入ってくるのでちょっと分かりづらいですね^^;）

　　【①TEXT関数の引数（表示形式）に指定したSWITCH関数の条件文の判断で使用する区分を
　　　更にSWITCH関数を使って決定する】 
　　　SWITCH(clipFwFormatSubString ,
　　　　→　文字列値の頭一桁を判断し、区分0～3までを決定
　　　　　　（1:数値形式、2:日付形式、3:時刻形式、0:その他）
　　　　"F",1,"P",1,"C",1,",",1,
　　　　　　→　数値形式("0")にしたい場合は1を返す
　　　　　　　　文字列値の頭一桁が上記以外の場合は更に文字列値全体で判断し、以下の区分を設定
　　　　SWITCH(formatSubString,
　　　　　　"D1",2,"D2",2,"D3",2,"D4",2,"D5",2,
　　　　　　　→　日付形式("yyyy/m/d")にしたい場合は2を返す
　　　　　　"D6",3,"D7",3,"D8",3,"D9",3,
　　　　　　　→　時刻形式("h:mm:ss")にしたい場合は3を返す
　　　　　　0)),
　　　　　　　→　上記以外は0を返す

　　【TEXT関数の引数（表示形式）に指定したSWITCH関数の条件文の記述と結果の指定】
　　　1,IF(clipBwFormatSubString="0","0","0."&REPT(0,clipBwFormatSubString)),
　　　　→　①で決めた区分が1の場合、以下を判断し「数値形式」を返す
　　　　　　　文字列値の頭一桁を除いた値が0だったら（整数値だったら）"0"を返す
　　　　　　　上記以外（小数部有りの場合）は少数部の桁数に応じた数値形式("0.00...")を返す
　　　2,"yyyy/m/d",　　→　区分が2の場合、「日付形式」"yyyy/m/d"を返す
　　　3,"h:mm:ss",　　→　区分が3の場合、「時刻形式」"h:mm:ss"を返す
　　　IFS(
　　　　　→　区分が上記以外の場合は、IFSの中で対象文字列の内容に応じて表示形式を返却

　　　　　ISBLANK(subString),"",　→　対象文字列が空の場合は、""を返す
　　　　　ISNUMBER(subString),　→　対象文字列が数値の場合、以下を判断し数値形式を返す
　　　　　　　IF(intSubString=subString,"0",
　　　　　　　　　　→　小数部を覗いた値と元値が一致している場合、"0"を返す
　　　　　　　　　　　　（対象文字列が整数値であると判断） 
　　　　　　　　　"0."&REPT("0",LEN(SUBSTITUTE(subString,".",""))-LEN(intSubString)))
　　　　　　　　　　→　上記以外の場合、少数部の桁数に応じた数値形式("0.00...")を返す
　　　　　　　　　　　　（対象文字列が少数部有りであると判断）　
　　　　　TRUE,""))　→　上記以外の場合は""を返す。 
　　　　　&IF(clipFwFormatSubString="P","%","")),1)))
　　　　　　→　最後に、文字列値の頭1桁が"P"だったら"%"をつける

非常に長かったですが、ご理解頂けましたでしょうか（無理）

要するにベースの数式は以下なのですが、表示形式をあらゆるパターンに適応させたため、やたらと長い数式となった次第なのです。

=TEXTJOIN(",",0,VALUETOTEXT(TEXT(値,表示形式),1))

他にもCELL関数で取得した文字列値と表示形式の対比表を作って、XLOOKUP関数などで表示形式を取得するといった方法など色々と良いやり方はあると思います。
ただ、今回は「作業セルを使用しない縛り」で行きましたので、こんな膨大な数式になってしまいました。

しかしながら、こんな長ったらしい数式でもLET関数を使用することで、CSVに変換したい文字列の参照範囲を指定する箇所が一箇所だけというのは今までのExcelの常識では考えられないくらい凄いことではないでしょうか？

LET関数を推したいわけではありませんが、今のExcelってマクロ無しでここまで凄いことができるんだ！と言う事を伝えたかったのは否定しません。（何気にSWITCH関数も使い勝手が良いことをご理解頂ければ幸いです）

他にもSPILL機能で増えた関数や普段の実務ではお目にかかる機会が滅多にない、知られざる便利な関数がまだまだ沢山あります。

Excelは日々進化しております。

数年後にはこのような新しい機能や関数が実務で当たり前のように使われていくことになるでしょう。
今までの常識を変えたくない気持ちも理解はできますが、是非ともExcelの新機能を覚えて常識を打ち破る手助けになっていけれ幸甚でございます。

さて次回は、今回作った「CSV形式の文字列をカンマ位置で区切って複数のセルに分解してみよう！～もちろん、作業セルは使わずに～」編です。

「え？作業セルを使わずにCSV形式の文字列の分解を？！ 」

いやいや。

さすがにそれは無理でしょうよ。
さすがにそれは「できねぇ！」と言いたいですよ。

……ん？「できらぁ！」と言いたいじゃなく、「できねぇ！」と言いたい？

つまりそれって、できるってこと！？

結果は次回、乞うご期待です。