仕事がススむ関数(14)住所データなどの文字結合に便利な、CONCAT/TEXTJOIN関数

投稿日 2023年3月27日
著者 root

せるワザ

顧客情報や取引先/競合企業などを、「住所録」として保管している職場は多いのではないでしょうか。
CRM(Customer Relationship Management/顧客関係性マネジメント)ツールや、GIS(Geographic Information System/地理情報)システムに取りこんで、事前マーケティングや、結果分析に応用する機会もふえたからです。

これらのシステムは、多くの場合、住所を国、都道府県、区市町村、それ以降の住所(番地)、建物名などにくぎって保管するデータベース構造になっています(人名も、姓と名+かなを切り分けて保存しますよね)。

都道府県単位や区市町村単位でフィルタリングするニーズに対応するためです。

今回は、たとえばこのような住所を結合し、再活用することを想定して、Excelの文字列結合に便利な機能をご紹介します。

もっともシンプルなのは、”&”記号か、”CONCATENATE”関数を使うこと

Excelで、複数セルにふくまれた文字列を単純に結合するためには、以前から”&”記号が用いられてきました。

また、結合を指示するExcel関数として「CONCATENATE(連結する)」もありますが、これらの使い方は、ほぼ同じです。

数十件の文字列を結合する時などに、”&”をくりかえし入力しなくて済むくらいしか、わざわざCONCATENATE関数を使用するメリットはありませんでした。

しかし、文字列結合の関数も進化しています。

Excel 2016以降に追加されたCONCAT/TEXTJOIN関数

Excel2016以降に追加された新関数にCONCATとTEXTJOINがあります。

CONCATENATE関数とCONCAT関数のちがいは、CONCATでは文字列結合の対象を、「範囲」指定できるようになったことです。

これなら、結合対象の文字列が10あっても、20あっても、その範囲を指定するだけなので、ラクに作業できますね。

同じくExcel2016以降に登場したTEXTJOIN関数は、CONCATとことなる機能をもっています。

<TEXTJOIN関数の引数>
=TEXTJOIN(区切り記号, 空の文字列を無視, 文字列1, 文字列2, …, 文字列252)

CONCATのように、結合対象を「範囲」で指定することはできず、最大252項目の文字列を1つずつひろい出さなければなりません。しかし、これまでの文字列操作関数ともっとも違う点は、「区切り記号」を指定できるということです。

(2つめの引数「空の文字列を無視」は、TRUEの場合には、文字どおり空の文字列を無視します。FALSEの場合には、空の文字列も連結対象とし、必ず区切り文字を挿入します。)

都道府県と区市町村の間などに、それぞれ区切り文字”,”(カンマ)が挿入されています。また、関数式の2つめの引数(「空の文字列を無視」)をFALSEとしているため、上の例では「丁目」にあたる情報(新琴似町と393番地の間)も「空白セル」として結合し、前後に”,”(カンマ)が購入されています。

区切り文字をふくむ文字列挿入が役立つ場面として、どんなことが考えられるでしょうか?

まず思いつくのが、英文表記の住所録管理です。

他のデータベース・ソフトなどからエクスポートしてきた表(テーブル)を元に宛名リストを作成する場合、自動的に”,”(カンマ)を挿入してもらえれば、手動入力でタイプミスをおこすことがなくなります。

区切り文字を入れておく必要は、英文表記にかぎることではありません。

たとえば、和文表記の住所録に区切り文字を入れてみましょうか。

都道府県と区市町村の間に”,”(カンマ)があった方がよい場面、思いつきませんか?

そうです! この情報を更新(クリーニング)して、別のソフトウェアへインポートする時にも、正確なcsvファイルが簡単に作れるのです。

とくに、都道府県名は3文字県と4文字県とが混在し、東京都/京都府/大阪府/北海道(末尾が「県」でないレコード)もあるので、全住所データから正確に切りはなすには、複雑な入れ子関数を用意しなければなりません。

住所にかぎらず、お客様フルネームを改姓・改名クリーニングしてインポートしなおす場合にも、”,”(カンマ)はぜひ挿入しておきたい情報です。

姓名情報は、2文字の人もいれば3文字、4文字の人もいます。漢字ばかりでなく、カタカナやアルファベットのお名前をもつ方もたくさんいます。顧客件数が多いほど、手動で区切り位置をまちがえて、「お名前誤表記」の信頼失墜は絶対にさけなければいけません。区切り文字は、このようなリスクを軽減してくれる特効薬でもあるのです。