富裕層・投資家の「今と先」を伝えるキュレーションサイト

【エクセル関数】バラバラな表記を一発で統一するSUBSTITUTE関数

問題:次の表組の「会社名」欄に入力されている「㈱」、「(株)」、「(株)」をすべて「株式会社」に置き換えた文字列を、「会社名(表記統一)」欄に自動入力させよ(環境依存文字「㈱」は、使用している端末によっては表示されない場合もあります)。 難易度:☆☆☆☆★ 実用度:☆☆☆☆★ 目標ステップ数:8 解答: 指定した文字列の検索・置換をするには「SUBSTITUTE(サブスティチュート)」関数を用いる。SUBSTITUTE関数は、基本的には1つの検索・置換しか行えないが、入れ子構造にすることで複数の文字列をまとめて検索・置換できるようになる。この入れ子構造を使うことが、今回の問題のポイントだ。では、標準解答の手順を見ていこう。 ●STEP1 数式を入力したいセル(ここではB2)を選択し、「数式バー」に「=SUB」と入力。表示される関数の候補から「SUBSTITUTE」をクリック。 ●STEP2 「=SUBSTITUTE(」と、関数の一部が自動入力されるので、「(」の後に続けて「SUB」と入力し、表示される関数の候補から「SUBSTITUTE」をクリック。 ●STEP3 「=SUBSTITUTE(SUBSTITUTE(」となったら、さらに「(」の後に続けて「SUB」と入力し、表示される関数の候補から「SUBSTITUTE」をクリック。これで「数式バー」には、「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(」と入力されている状態になる。 ●STEP4 検索したい範囲(ここではA2)をクリックして選択。 ●STEP5 続けて「,"㈱","株式会社"),」と入力(記号はすべて半角)。ここまでで「数式バー」内の数式は「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"㈱","株式会社"),」となっている。 ●STEP6 さらに続けて「(株)","株式会社"),」を追加入力。ここまでで「数式バー」内の数式は「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"㈱","株式会社"),"(株)","株式会社"),」となっている。 ●STEP7 最後に「"(株)","株式会社")」と入力。数式全体が「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"㈱","株式会社"),"(株)","株式会社"),"(株)","株式会社")」となっているのを確認したらEnterキーを押す。 ●STEP8 選択したセルに計算結果が表示される。選択範囲の右下にポインタをあわせダブルクリックすると、オートフィル機能が働き、表組の空欄が埋まる。 【今回のまとめ】 今回用いたSUBSTITUTE関数は、「=SUBSTITUTE (検索範囲,検索文字列,置換文字列)」という基本構文となる。「検索範囲」には、セル範囲のほか文字列を直接指定することもできる。 今回の問題では、SUBSTITUTE関数の入れ子構造を使っているため、複雑な印象を受けるかもしれないが、数式を分解してみれば、さほど難しいものではない。 SUBSTITUTE関数の入れ子構造は、数式の中央にある関数が核となる、と考えればわかりやすい。今回の場合は「SUBSTITUTE(A2,"㈱","株式会社")」の部分だ。ここでは、セルA2に含まれる文字列で、「㈱」を検索し「株式会社」に置換している。 この数式を核として、入れ子をひとつ増やした状態が「SUBSTITUTE(SUBSTITUTE(A2,"㈱","株式会社"),"(株)","株式会社")」だ。核にあたる部分で「検索範囲」を指定しているので、入れ子の外側では「検索範囲」を指定する必要はないが、「置換文字列」を省略することはできない点に注意が必要。この場合は、「㈱」→「株式会社」と「(株)」→「株式会社」という2つの検索・置換を同時に行っているわけだ。 そして最後の入れ子を増やした状態が「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A15,"㈱","株式会社"),"(株)","株式会社"),"(株)","株式会社")」。書体によっては、わかりにくいかもしれないが、「(株)」と「(株)」では、カッコの全角・半角が異なっている。つまり、数式全体ではセルA2を対象に、「㈱」→「株式会社」、「(株)」→「株式会社」、「(株)」→「株式会社」という3つの検索・置換を同時に行っていることになる。ちなみに、3つ以上の入れ子構造をつくることも可能だ。 表記の統一にSUBSTITUTE関数を使う場合は、このように入れ子構造を活用する場合が多い。検索・置換は、関数を使わなくても行えるが、その場合は1回ずつしか検索・置換ができないのが面倒なところ。今回の問題のように、複数の表記ゆれが想定される場合は、SUBSTITUTE関数を活用することで、より効率よくデータの整理ができるだろう。