この記事で分かること
- &・CONCATENATE・CONCAT・TEXTJOIN の違いと、照合キーでは ignore_empty を FALSE、一覧では TRUE にする理由
- 先頭ゼロの消失と日付のシリアル値化を TEXT で直し、COUNTIFS で一致件数を検算する手順
- TEXTJOIN と FILTER で閾値を超えた科目を1セルに列挙する式と、文字数上限の確かめ方
想定読者：子会社や部門から集めた数表を、会計システムの出力など別の表と突き合わせている IB・PE・FAS・FP&A の若手や、経理・経営企画の担当の方
前提知識：SUM や COUNTIF などの関数を使ったことがあること
結論：区切りと空白は TEXTJOIN で決め、数値と日付は TEXT で文字にそろえてから結ぶ
以下は学習用の架空事例です。部品メーカーのP社が、子会社6社（X1社〜X6社）の連結パッケージの値と会計システムの値を10科目で突き合わせます。会社コードは先頭ゼロ付きの4桁、決算期は2026年3月31日、差異の閾値は±50百万円です。
詰まりやすいのは、会社コード・決算期・科目の複数列を1本の「照合キー」にする場面です。空白で区切りが二重に入る、先頭ゼロが消える、日付が46112のような数字になる、のどれでもキーは一致しません。結論は3点です。キーでは TEXTJOIN の ignore_empty を FALSE にして列の位置を保つ（一覧では TRUE）。数値と日付は TEXT 関数で書式を固定した文字にしてから結ぶ。COUNTIFS で一致件数を数えて検算する。
4つの結合方法の違い：範囲・空白・区切り文字・使える Excel
Microsoft サポートによると、TEXTJOIN 関数は複数の範囲や文字列を、指定した区切り記号を挟んで結合します。引数は「区切り記号」「ignore_empty」「text1, text2…」の順で、ignore_empty が TRUE のとき空のセルは無視され、各引数にはセルの範囲も使えます1。CONCAT 関数は範囲も結合できますが、区切り記号と IgnoreEmpty の引数はありません2。CONCATENATE 関数は最大255項目・合計8,192文字までで、CONCAT に置き換えられ、互換性のために残っています3。海外の解説でも、TEXTJOIN の特徴は区切り文字の柔軟さと空のセルの扱いを選べる点とされています4。
| 方法 | 範囲の指定 | 空白セル | 区切り文字 | 使える Excel |
|---|---|---|---|---|
| & 演算子 | 不可（1セルずつ） | 区切りが残る | 自分で書く | 全版（演算子） |
| CONCATENATE | 不可 | 選べない | 自分で書く | 互換用に残る |
| CONCAT | 可（列全体も） | 選べない | 自分で書く | 2019以降・365（注記） |
| TEXTJOIN | 可 | TRUE で無視 | 第1引数 | 2019 以降・365 |
図表1のとおり、範囲をまとめて渡し、区切りと空白の扱いも選べるのは TEXTJOIN だけです。CONCAT のページは適用先に Excel 2016 を挙げる一方、注記では Office 2019 または Microsoft 365 としており、記載が一致しません（2026年10月時点）。共有先の版で動くか確かめてください。
空白セルと ignore_empty：キーでは FALSE、一覧では TRUE
会社コード「0012」、部門（空白）、科目「売上高」の1行を区切り「-」でつなぐと、& では「0012–売上高」、TEXTJOIN で ignore_empty が TRUE なら「0012-売上高」、FALSE なら「0012–売上高」です（編集部の Windows 版 Excel 16.0系・日本語環境で確認）。
TRUE は会社名や科目名を並べる一覧に向きますが、照合キーでは問題が起きます。会社コード・部門コード・補助コードの3列でキーを作るとき、部門が空で補助が「01」の行と、部門が「01」で補助が空の行は別の行です。TRUE では空白が詰められてどちらも「0012|01」になります。FALSE なら「0012||01」と「0012|01|」になり、列の位置が保たれます（図表2の最終行）。なお、区切り記号に数値を指定すると文字列として扱われます5。
使い分けの目安
一覧（読む人向け）：TEXTJOIN("、",TRUE,範囲)
照合キー（別の表と一致させる）：TEXTJOIN("|",FALSE,列1,列2,列3)
数値と日付は TEXT で文字にそろえる：先頭ゼロとシリアル値
会社コード「0012」が、数値の12に「0000」という表示形式を付けただけのセルだったとします。画面には0012と見えても値は12なので、& や TEXTJOIN では「12」としてつながります。日付も内部では通し番号（シリアル値）なので、2026年3月31日は「46112」になります（1899年12月30日からの日数）。海外の解説も、TEXTJOIN で日付のセルを参照するとシリアル値相当の数字が返ると説明しています6。
直し方は、TEXT関数
照合キーの式（A列＝会社コード、B列＝決算期、C列＝科目）
=TEXTJOIN("|",FALSE,TEXT(A2,"0000"),TEXT(B2,"yyyymmdd"),C2)
=TEXT(A2,"0000")&"|"&TEXT(B2,"yyyymmdd")&"|"&C2 （同じ結果になる & 版）
会社コードが最初から文字列の「0012」なら TEXT は不要です。TEXT の結果は文字列で後の計算に使いにくいため、元の値は別のセルに残すのが最善とされています8。キー列は照合専用とし、元の列は変えないでください。
| 症状 | 失敗した出力 | 直した出力 | 直し方 |
|---|---|---|---|
| 先頭ゼロ消失・ 日付のシリアル値化 | 12|46112|売上高 | 0012|20260331|売上高 | TEXT(コード,"0000") TEXT(決算期,"yyyymmdd") |
| 空白で区切りが二重 | 0012–売上高 | 0012-売上高 | TRUE（一覧向け） |
| 別の2行が同じキーに | 0012|01（2行とも） | 0012|01| と 0012||01 | FALSE（キー向け） |
図表2のとおり、失敗した出力は一見正常に見えます。余分なスペースなどの整形はExcelの財務データクリーニング実務に委ねます。
照合キーの設計：並び順・区切り文字・列の固定
設例のキーは「会社コード|決算期|科目」の順、区切りは縦棒「|」です。設計の要点は3つです。
- 並び順は粒度の大きいものから小さいものへ。2つの表で同じ式にそろえます。
- 区切り文字はデータに現れない文字にします。「-」だと、補助コード「A-1」・科目「売上」の行と、補助コード「A」・科目「1-売上」の行が、どちらも「0012-A-1-売上」になります。
- 先頭ゼロのあるコードは TEXT で桁数を固定し、ignore_empty は FALSE にします。
区切り文字の混入は、科目が C2:C61 にあるなら =SUMPRODUCT(LEN(C2:C61)-LEN(SUBSTITUTE(C2:C61,"|",""))) で数えられ、設例では0です。元データをExcelテーブル
COUNTIFS で一致件数を検算する：57件・2件・1件の内訳
設例では、X表（連結パッケージ）が60行、Y表（会計システム）が61行です。X表のキーがD2:D61、Y表のキーがI2:I62なら、X表の各行に =COUNTIFS($I$2:$I$62,D2)、Y表の各行に =COUNTIFS($D$2:$D$61,I2) を入れます。1なら対応、0なら相手の表になく、2以上なら相手の表で重複です。
図表3のとおり、X→Yは1件57・0件2・2件1で計60です。0件はX5社（0058）の売上高と買掛金で、Y表の決算期が2026年3月30日と誤入力されていました。2件はX2社（0027）の売上原価がY表に二重に入っていたものです。Y→Xは1件59・0件2で計61です。
2方向を数えるのは、見つかる問題が違うからです。二重登録はX→Yの2件でしか現れず、Y表にだけある行はY→Xの0件でしか見つかりません。金額を引く手順はINDEX/MATCHとXLOOKUP実践に、検算をまとめる考え方は検算チェックの設計に委ねます。
TEXTJOIN と FILTER で、条件に合う項目を1セルに並べる
次に、差異が閾値を超えた科目を会社ごとに1つのセルへ並べます。図表4は設例の差異（単位：百万円）で、絶対値が50を超えるものが対象です。
| 科目 | X1社 | X2社 | X3社 | X4社 | X5社 | X6社 |
|---|---|---|---|---|---|---|
| 会社コード | 0012 | 0027 | 0031 | 0045 | 0058 | 0063 |
| 売上高 | ▲62 | −18 | 0 | ◇50 | ▲−75 | 4 |
| 売上原価 | ▲−55 | 12 | 0 | 8 | 41 | ▲−53 |
| 販管費 | 3 | 0 | ◇−50 | −7 | 0 | 0 |
| 減価償却費 | 0 | 0 | 1 | 0 | 0 | 0 |
| 支払利息 | 0 | 2 | 0 | 0 | −1 | 0 |
| 受取配当金 | 0 | 0 | 0 | 0 | 0 | 0 |
| 棚卸資産 | ▲58 | −9 | 0 | ▲−62 | 0 | 0 |
| 売掛金 | −3 | 0 | 14 | 0 | ▲90 | −6 |
| 買掛金 | 0 | 0 | 0 | 5 | ▲−66 | 0 |
| 有形固定資産 | 0 | 0 | 0 | 0 | 0 | 0 |
| 超過科目数（>50） | 3 | 0 | 0 | 1 | 3 | 1 |
FILTER は条件に合う行だけを取り出す関数で、3つ目の引数 if_empty に該当がないときの値を指定します。Microsoft サポートには、空の結果が返り得る場合に if_empty を使わないと #CALC! エラーになるとあります9。該当0件の会社があるので「なし」を入れ、負の差異は ABS 関数（絶対値）で符号を外して比べます。FILTER の結果は動的配列
閾値超の科目を列挙する式（科目 A3:A12、X1社の差異 B3:B12）
=TEXTJOIN("、",TRUE,FILTER($A$3:$A$12,ABS(B$3:B$12)>50,"なし"))
この条件で6社を列挙すると、X1社：売上高、売上原価、棚卸資産。X2社・X3社：なし。X4社：棚卸資産。X5社：売上高、売掛金、買掛金。X6社：売上原価。合計は3+0+0+1+3+1＝8科目で、図表4の▲の数と一致します（この結果は、後述の IF 版で実機確認したものです）。
ちょうど50のセルが2つ（X4社の売上高50、X3社の販管費−50）あり、「>=50」にすると10科目に増えます。「超える」か「以上」かは閾値の定義に合わせ、式の横に書いておきます。会社名を並べるなら、売上高の行で =TEXTJOIN("、",TRUE,FILTER($B$1:$G$1,ABS($B$3:$G$3)>50,"なし")) の形になり、該当は「X1社、X5社」です（IF 版で確認）。
FILTER のページの適用先に Excel 2019 は挙げられていません10。その場合は =TEXTJOIN("、",TRUE,IF(ABS(B$3:B$12)>50,$A$3:$A$12,"")) を Ctrl+Shift+Enter で確定する旧来の配列数式
1セルの文字数上限を事前に確かめる
Microsoft サポートの「Excel の仕様と制限」では、1セルの合計文字数の上限は32,767文字です11。TEXTJOIN は結果がこれを超えると #VALUE! を返します12。
超えると結果はエラー値
実務チェックリスト
- キーは ignore_empty＝FALSE、読む人向けの一覧は TRUE にしている。
- 先頭ゼロのある値は TEXT(値,"0000")、日付は TEXT(日付,"yyyymmdd") で文字にし、元の列は変えていない。
- 区切り文字の出現回数が0であることを SUBSTITUTE で確かめた。
- COUNTIFS を両方向に実行し、1件・0件・2件以上の内訳が行数と合う（設例なら57+2+1＝60、59+2＝61）。
- 閾値の条件が「>」か「>=」かを定義どおりに選び、ちょうど閾値のセルを個別に確認した。
よくある質問（F AQ）
数式が空文字（" "）を返すセルは、ignore_empty を TRUE にすると無視されますか。
編集部の Excel
共有先に Excel 2016 を使う人がいる場合は、どうすればよいですか。
TEXTJOIN のページの適用先に Excel 2016 は含まれません。& 演算子でキーを組むのが確実です。
まとめ
4つの結合方法は、範囲の渡し方、空白の扱い、区切り文字、使える版が違います。照合キーでは ignore_empty を FALSE にし、先頭ゼロと日付は TEXT で文字にしてから結びます。設例では COUNTIFS の検算で57件一致・2件不一致・1件重複が見つかりました。条件に合う科目は TEXTJOIN と FILTER（使えない版では IF の配列数式）で1セルに並べ、上限は事前の長さで確かめます。
脚注
- Microsoft サポート「TEXTJOIN 関数」の構文と引数表。ignore_empty が TRUE なら空のセルを無視し、各引数に範囲を指定できる（参考文献 R1）。↩
- Microsoft サポート「CONCAT 関数」冒頭。区切り記号と IgnoreEmpty 引数は提供せず、CONCATENATE を置き換えると記載（参考文献 R2）。↩
- Microsoft サポート「CONCATENATE 関数」。最大255項目・合計8,192文字。「重要」欄に Excel 2016 等で CONCAT に置き換えられたと記載（参考文献 R3）。↩
- Wall Street Oasis「TEXTJOIN Function」の導入部。主な理由として区切り文字の柔軟さと空のセルの扱いを選べる点を挙げる。一般的な概念の参考（参考文献 R4。訳は編集部）。↩
- Microsoft サポート「TEXTJOIN 関数」の引数表。区切り記号に数値が指定された場合は文字列として扱われる（参考文献 R1）。↩
- Wall Street Oasis「TEXTJOIN Function」の Example #3。日付のセルを参照するとシリアル値相当の数字が返ると説明（参考文献 R4。訳は編集部）。↩
- Microsoft サポート「TEXT 関数」の例の表。=TEXT(1234,”0000000″) で先頭にゼロを追加（例: 0001234）（参考文献 R5）。↩
- Microsoft サポート「TEXT 関数」冒頭の「注」。元の値を1つのセルに保持し、別のセルで TEXT 関数を使うのが最善と記載（参考文献 R5）。↩
- Microsoft サポート「FILTER 関数」。空の値が返り得る場合に if_empty を使わないと #CALC! エラーになると記載（参考文献 R6）。↩
- Microsoft サポート「FILTER 関数」冒頭の適用先。Excel 2019 は含まれない（2026年10月22日閲覧時点。参考文献 R6）。↩
- Microsoft サポート「Excel の仕様と制限」。セルが含むことができる合計文字数は32,767文字（参考文献 R7）。↩
- Microsoft サポート「TEXTJOIN 関数」の「解説」。結果が32767文字（セルの制限）を超えると #VALUE! を返す（参考文献 R1）。↩
参考文献（2 026年10月確認）
- R1 Microsoft サポート
「T EXTJOIN 関数」 support.microsoft.com （構文・引数表・解説。脚注1・5・12。閲覧日：2026年10月22日） - R2 Microsoft サポート
「C ONCAT 関数」 support.microsoft.com （冒頭の説明と適用先。脚注2。閲覧日：2026年10月22日） - R3 Microsoft サポート
「C ONCATENATE 関数」 support.microsoft.com （「重要」欄と引数の説明。脚注3。閲覧日：2026年10月22日） - R4 Wall Street Oasis「TEXTJOIN Function」 wallstreetoasis.com （一般的な概念の参考。脚注4・6。閲覧日：2026年10月22日）
- R5 Microsoft サポート
「T EXT 関数」 support.microsoft.com （冒頭の注と例の表。脚注7・8。閲覧日：2026年10月22日） - R6 Microsoft サポート
「F ILTER 関数」 support.microsoft.com （適用先・構文・if_empty の注。脚注9・10。閲覧日：2026年10月22日） - R7 Microsoft サポート
「E xcel の仕様と制限」 support.microsoft.com （ワークシートとブックの仕様の表。脚注11。閲覧日：2026年10月22日）
※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための学習用の架空事例であり、実在の企業・案件とは関係ありません。
