CONCAT関数・TEXTJOIN関数☆
姓と名が2つのセルの入ってるとか、住所の都道府県と市区町村とそのあとがバラバラのセルに入ってるとか、よくありますよね。それを1つのセルにくっつける必要があるかもしれません。そのときに文字同士をくっつけるのがCONCAT関数です。
電話番号なんかだと、市外局番、市内局番、最後のライン番号に分かれてますけど、その間って「-（ハイフン）」でつながると思うんですけど、それみたいになんかの文字でくっつけるのがTEXTJOIN関数です。
その2つの文字同士をくっつける文字列結合関数のお話をします。
CONCAT関数やTEXTJOIN関数は、Excel2016の途中から使えるようになっています。
文字列を結合しよう
Excelで、文字を結合するというのは実は関数じゃなくてもできます。
足し算をするように「&」でくっつけたい文字やセル同士をつなげばいいのです。
=A2&"様（"&B2&"回目の来店）"
という計算式にして、セルA2に「さとう」、セルB2に「3」と入力すれば、その文字をつないで、「さとう様（3回目の来店）」と表示されます。
セル参照はそのままでよくて、なんていう文字か指定するところは””で囲むのポイントです。結構簡単にこんなことができます。ちょっと面白いですよね。
ただ文字の結合って注意しなきゃいけないことがあって、上の例と同じように次に式で文字列結合してるんですけど。
=A2&"までに"&B2&"をお振込みください"
出来上がった文章を見ると、「\15,000」が「15000」ってなってるし、「8月31日」の日付なんて「45535」っていうのに変わっています。
セル参照したものって、そのセルに表示されているものじゃなくて、そのセルの値の正体で書かれてしまうんですね。「\15,000」っていうのはExcelでは「15000」っていうただの数字だし、「8月31日」って2024年の8月31日なんですけどそれはExcelとしては1900年1月1日を1日目とした日数が正体なので45535日目だから45535って出ちゃうんですよね。
これちょっといやなとこです。
これをうまくやるには、表示形式で表した文字列に変換するTEXT関数で、その表示に合わせた文字列に変更してから文字列結合する必要があります。
=TEXT(A2,"m月d日")&"までに"&TEXT(B2,"¥#,##0″)&"をお振込みください"
セルA2の表示形式は「m月d日」、セルB2の表示形式は「¥#,##0」なのでそれをTEXT関数の中に入れて変換して文字列結合してます。メンドイ。
こういうことが起きることだけ覚えておいて、注意してくださいね！
CONCAT関数
CONCAT関数は単純に文字をくっつける関数です。
=CONCAT(テキスト1,テキスト2,…)
テキストには、セル、セル範囲、文字列が指定できて、全部で254個まで入ります。
以下のようなスタッフごとの出勤日の表があって、I列にお客様向けのメッセージを表示するのですが、セルI2の計算式は次の通りです。
=CONCAT(“今月、",A2,"は",B2:H2,"に出勤しています")
「今月、」という文字、セルA2のスタッフ名、「は」という文字、B2からH2のセル範囲に入力されている出勤している曜日、「に出勤しています」という文字を組みあわせ、文章を作成しています。
文字列、1つのセル、セル範囲が使われていますね。特にB2からH2のセル範囲の中には入力されているセルと入力されていないセルがあります。入力されている文字だけ見えていますが、文字列結合は範囲全体のセルに対して行われていて、入力されていないところは空白を結合しているから見えていないだけなんですよね。
計算式は&で全部くっつけるよりはわかりやすくなってると思います。
TEXTJOIN関数
TEXTJOIN関数は、単純に指定された文字を結合するんじゃなくて、文字の間に、指定した文字を入れる関数です。
=TEXTJOIN(区切り文字,空のセルは無視,テキスト1,テキスト2…)
TEXTJOIN関数ではCONCAT関数で指定するものより2つ指定するものが増えています。
区切り文字は、結合するもの同士の間に入れる文字です。
空のセルは無視は、指定するものの中にセルやセル範囲を指定したときに、そのセルが空白だったら結合をやめるかどうかを指定します。TRUEとFALSEで指定してTRUEにすると結合をやめます。これは実際に動作を見たほうがわかりやすいですね。
テキストはCONCAT関数と同じです。
TEXTJOIN関数で読点「、」を結合するものの間に入れてみました。
=TEXTJOIN(“、",TRUE,"今月",A2,"は",B2:H2,"に出勤しています")
人の名前の後にも読点が入ってしまったので文章が少しへんな感じがしますが、こんな風に動作します。
セル範囲の間にも読点が入ります。
人の名前の後ろに読点を入れないこともできそうですね。セルA2と「は」の文字を1つにまとめればいいので、「A2＆”は”」にすれば間に区切りがなくなります。
=TEXTJOIN(“、",TRUE,"今月",A2&"は",B2:H2,"に出勤しています")
では、ここで、空のセルは無視を「FALSE」にして無視しないようにしてみます。
読点がめっちゃ増えました。どこに増えたかというと空白が入ってるB2からH2までのセル範囲に入っています。空のセルを無視しないと、空白セルの分も区切り文字が入ってしまうので、このようになります。
空のセルを無視したほうがいいような感じもしますが、文字の左側から順に読点を数えていけば、曜日ごとに空欄かわかるというメリットがあります。
集計データのときのCONCAT関数やTEXTJOIN関数の使い方
集計データの場合は、文字を結合することでメッセージ的な文字を作るというよりも、検索のためのキーワードを作るということで使うかもしれません。
VLOOKUP関数やXLOOKUP関数でほかの表からデータを持ってくるときに活躍すると思うんですよね。
この技はVLOOKUP関数ではできないので、XLOOKUP関数で行う方法なのですが、XLOOKUP関数って検索値と検索値を探す検索範囲って1列しか指定できないんですよね。
もしそれを3列で一致したらその値を持ってきたいときってあると思うんですよ。
XLOOKUP関数の使い方は以下の記事を参照して下さい。
きじ
左がある学習塾の生徒一覧で生徒名とどこ中の何年何組かがA列からD列に分けて入っていて、生徒それぞれの学校の担任名の一覧を作りたいんですね。
右の表がどこ中の何年何組の担任が誰っていうのがG列からJ列まで入ってます。
左の塾の生徒一覧の中の学校の担任名を調べるには、右の表を調べればわかるんですけど、Excelでそれを調べることが実はできなくて、一番近いのがXLOOKUP関数なんですよね。でもXLOOKUP関数は1つの検索値を1列の中で探す関数なのですが今回は、中学校、学年、クラスの3つの検索値を、同じ3つの列の中から探します。
そこで、検索範囲を新たに作ると探せるようになります。それがK列の中学校、学年、クラスが結合されたセルで、セルK2の計算式は次の通りです。
=CONCAT(G2:I2)
それを作っておけばXLOOKUP関数で検索ができるようになります。
セルE2に入るXLOOKUP関数は、B2からD2までの中学校、学年、クラスを結合したものをK列から探すようにすればいいと思います。その計算式は次の通りです。
=XLOOKUP(CONCAT(B2:D2),$K$2:$K$19,$J$2:$J$19)
B2からD2のセル範囲をCONCAT関数で結合して、その値でK列を検索して、それに対応したJ列を出せば担任名が求まるんですよね。絶対参照にしていれば、オートフィルすればすべての担任が入ります。
今回は中学校、学年、クラスが1文字ごとの単純なパターンだったのでCONCAT関数で大丈夫だったんですけど、例えば姓が「佐藤」名が「吉雄」ってなってるときって、結合すると「藤吉」が間違って検索されちゃうんですよね。それを防ぐにはCONCAT関数を使わないでTEXTJOIN関数で「-」とかの区切り文字で区切るといけるんです。
そのときの、セルK2の計算式は次の通りです。
=TEXTJOIN(“-“,TRUE,G2:I2)
そして、セルE2の計算式は次の通りです。
=XLOOKUP(TEXTJOIN(“-“,TRUE,B2:D2),$K$2:$K$19,$J$2:$J$19)
CONCAT関数のXLOOKUP関数の新しいExcelで使えるものなので、使えないExcelの場合は、CONCAT関数は&で文字列結合し、XLOOKUP関数はINDEX関数とMATCH関数の組み合わせで作成することになります。
かなり計算式は長くなります。
そのときのセルK2の計算式は次の通りです。
=G2&H2&I2
そして、セルE2の計算式は次の通りです。
=INDEX($J$2:$J$19,MATCH(B2&C2&D2,$K$2:$K$19,0))
最新のExcelでするときも、そうでないときも、計算式はレベルが一つ上になって大変かもしれませんが、データ分析するときに役立つ方法ですので、これはぜひマスターしてください。
計算式を複雑にしないためにも、CONCAT関数やTEXTJOIN関数はテーブル内で使われると効果的ですね。
まとめ
文字列を結合するCONCAT関数とTEXTJOIN関数を紹介しました。
文字列結合関数を使ってメッセージを簡単に作れるというのは、便利だと思います。
もちろんそういう使い方も大事ですけど、やっぱりデータを集計する際に応用するのがこの関数の本当に力を発揮することなのかなって思います。
繰り返しになるのですが、データ分析での使い方はマスターすべき使い方ですので、ぜひ、繰り返し練習してみてくださいね。
関連関数
XLOOKUP関数：ほかの一覧表で一致したものに対する指定した範囲のデータを求めます
LEFT関数：左から指定した文字数の文字列を取り出します
RIGHT関数：右から指定した文字数の文字列を取り出します
MID関数：指定した何文字目から指定した文字数の文字列を取り出します