「知っていると得するかもしれないExcelの癖」シリーズ第二回、テーマは「文字列結合について」になります。
Excel 2016バージョン1902を使用して確認しています。
行(横方向)でも列(縦方向)でも同じことになりますが、例えば複数行または列に分かれて入力されている住所をひとつにまとめるような場合に数式を使用することと存じますが、その際に注意しなければならない点があります。
文字列を結合するための数式
今回説明で使用している環境はExcel2016となりOffice365やoffice2019ではありませんのでご注意ください。
また、以下では列方向で例示をしていますが、行方向でも同じ形になります。
B2:B4セルを結合するための数式は以下の2パターンになります。
=B2&B3&B4
=CONCATENATE(B2,B3,B4)
例えば数値を合計するSUM関数の場合はセル範囲(B2:B4)での指定ができるのですが、文字列結合の場合はセル範囲での指定はできません。
関数で指定している範囲内で行追加されると困ること
上図の状態で5行目と6行目の間に1行追加する必要が出ました。
例えば2番目の住所を市町村区と丁目番地に分けたいなどの要件などが考えられます。
6行目を選択し「挿入」でも、５行目をコピーして6行目に「コピーしたセルの挿入」でも結果は同じですが、後者の場合に3行目の数式がどのようになったかを2行目に表記しています。
上図はC3セルにフォーカスがある状態での画面キャプチャーになります。
当然数式としてはセル範囲ではないので、追加した行が数式に自動反映されることはありません。
ここで、やっぱり追加はしないことになったとします。
その時、人間は5行目と6行目は同じだから5行目を選択して行削除したとします。
そうすると、下図のようなエラーが表示されてしまいます。
上図はB3セルにフォーカスがある状態での画面キャプチャーになります。
D列のセル範囲は何事もなく正しい結果を表示していますが、B列、C列は「#REF!」になります。
これは消された行と同じ内容ではありますが、Excel的には数式にセットされていた5行目と、上図の5行目は別物であると判断されて、「いままでのB5とC5が参照できない」として「#REF!」のエラーになってしまいます。
文字列結合の対象行を追加削除しても、影響を受けない数式の設定方法
ここで登場するのが、一般的には馴染みが薄いOFFSET関数になります。
ただしOFFSET関数は優れた機能を持っている訳ではありません。
というかどちらかというと、Excelのセルコントロールから外れた自主独立性が高い機能と言えます。
上図はB3セルにフォーカスを当てた画面キャプチャーです。
これまでのように参照セルに背景色が付くような動きは無くなります。
いままでと同じように5行目を行コピーして、6行目に挿入して見ます。
なおB3セルの数式の末尾に「&OFFSET(B3,4,0)」を追記します。
B3セルには「ABBD」という正しい結果が表示されます。
なお、つぎの説明のために8行目に「@End」を追加しました。
ここで、5行目を削除して見ます。
５行目は消えましたが、代わりに余計な7行目まで表示されてしまいます。
7行目を表示させなくするには、先ほど追加した「&OFFSET(B3,4,0)」をB3セルの式から削除する必要があります。
まとめ
如何でしょうか?
恐らく結合する文字列数が少なく、あまり行の追加・削除が発生しないのであれば、Excelの癖を許容したうえで今まで通りの数式を選択されるのではないか?と思います。
その理由としては、追加・削除の手間はCONCATINATE関数でもOFFSET関数でも同じ手間なのですが、やはり数式に含まれるセルに対するセル参照の機能があるのとないのでは、ある方に軍配が上がることと存じます。
そうなりますと、CONCAT関数が広くExcelユーザに開放されることを望まざるを得ません。
ただ、頻繁に行の存減が想定される文字列結合がある場合には、是非ともOFFSET関数をご検討いただければ幸いです。
以上、最後までご一読いただきありがとうございました。
