エクセルで文字列の置き換え自由自在-基本からsubstitute関数まで-

    エクセルは、表を中心とした資料の作成だけでなく、顧客名簿のようにデータを整理した簡単なデータベースとして利用されることも多いです。こうして整理したデータは、企業名や市町村名が変更されることに伴い、ある日一括して変更しなければならないといった事案はよくあります。

    また、いろいろなデータをまとめた時に、集めた情報の「揺らぎ」が問題になることも多いです。お客さんの名前をまとめてみたら、苗字と名前の区切りで全角の空白と半角の空白が入り交じっていたためどちらかに統一したいといった事案も実際よくあります。

    数件程度ならともかく、多量のデータを手作業で修正し変更するのは大変ですが、エクセルには強力な置換機能が備わっています。その使い方とsubstitute 関数を用いた、より応用範囲が広い置換のやり方をまとめてみました。

     



    ■基本的な置換のやり方をおさえよう

    企業が合併したり、ブランドの変更で名称が変更されること、最近では珍しくありません。そんなときに、エクセルで作っていた顧客データのうち該当する企業の名前をあるタイミングで一括して変えてしまいたいといったこともあるでしょう。
    エクセルには、強力な検索/置換の機能が用意されていますので、決まり切ったパターンであれば簡単に一括で置換することが可能です。
    具体的には、変更したいエクセルのファイルを開き、「ホーム」タブにある「検索と置換」をクリックし、さらに「置換(R) 」を選択します。すると、検索と置換ダイアログボックスが表示されます。
    例えば、「株式会社ABC」が「CDE株式会社」に変わるといった場合には、「検索する文字列(N) 」に「株式会社ABC」、続いて「置換後の文字列(E) 」に「CDE株式会社」を入力します。
    ここで、すぐに「すべて置換(A)」ボタンをクリックしたくなりますが、その前にデータの中に該当するものがどれくらいあるのか、「すべて検索」をクリックして確認しておいた方がよいでしょう。

    この図のように、企業名の中には「株式会社ABC◎◎」といったように、「株式会社ABC」が名前に含まれているだけの場合もあります。このまま一括で置換してしまうと、このパターンの場合「CDE株式会社◎◎」のようになってしまうので注意が必要です。 「株式会社ABC」だけを一括置換したい場合には、置換のオプションで指定することが可能です。 まず、置換と検索ダイアログボックスにある「オプション(T)」ボタンをクリックして詳細な設定ができるようにしておきましょう。そして「セル内容が完全に同一であるものを検索する(O) 」をチェックします。これで、完全に同じ物だけが検索対象となります。

    あとは「すべて置換(A) 」ボタンをクリックすれば置換が行われます。

    ■substitute関数を使って置換の作業を自動化しよう

    このように、エクセル標準の「検索と置換」を使うことで単純なパターンの置換を一括して行うことができますが、いちいち手作業でやらなければならないのが難点です。

    例えば、他の人が作ったエクセルのファイルから住所のデータを定期的に集め、まとめて一覧にして使いたいといった場合を考えてみましょう。ようやくまとめてみたものの、いろいろな人が入力したエクセルのファイルでは、住所の番地の表記の仕方、郵便番号の形式などがバラバラだったという経験はないでしょうか。

    住所の区切りに使われていた文字が全角の「ー」と半角の「ー」が混ざっている、全角のハイフン「−」だったり、全角の長音「—」だったり、ぱっと見区別がつきにくいものもあります。
    区別がつかないのだから、別によいというわけにもいきません。その住所のデータを別のシステムに利用しようとすると、長音の場合はエラーになって登録できないなどということもありえます。統一できるものは無難なものに揃えておいた方がデータとしては扱い易くなります。

    このように、複数のパターンを1つにまとめる作業を、しかも定期的に行わなければならない場合、そのたびに手作業で「検索と置換」を使っていては効率がよくありません。
    このような場合に便利なのが「substitute関数」を使う方法です。subsititute関数は、次のような形式で利用できます。

    =subsititute(対象の文字列又はセル,検索文字列,置換後の文字列,置換対象)

    最後の置換対象が、少しわかりづらいので補足すると、対象の文字列の中に該当するものが複数あった場合、全て置換したければ省略できます。もし最初にでてきたものだけ置換したければ、数字の1を指定するという使い方です。

    先ほどの住所の例で、半角の「-」(マイナス)を全角の「−」(ハイフン)に全て置換したければ、

    =subsititute(住所のセル,”-“,”−”)

    のように使います。

    ところが、これだけでは1つの組合せが処理できるだけです。さらに加えて全角の「ー」を全角の「ー」(ハイフン)に置換しておきたい場合には、どうすればよいのでしょう。

    実は、subsititute関数は、

    =subsititute((subsititute(住所のセル,”-“,”−”),”ー”,”−”)

    のように複数繰り返して記述することができます。このような書き方を、関数の入れ子(ネスト)のように言うこともあります。subsititute関数を入れ子にして、必要なだけのパターンを処理することで手作業で置換する手間を省くことが可能です。

    この例では、2つですんでいますが、処理したい内容によってはもっと多くの入れ子が必要になるかもしれません。エクセルにはバージョンごとに入れ子の数の上限および数式自体の長さにも一定の制限がありますので、それを超えないように注意が必要です。

    とはいえ、どちらもそれなりの大きい数ですので、エクセルのバージョンが2010以降であればあまり問題にはならないでしょう。一方で、あまりに数式が複雑になってしまうと人間の方が対応しきれないので、エクセル以外の別の方法を検討した方がいいかもしれません。

    エクセルの各種制限については、マイクロソフトの公式サイトにまとまっているので参考にして下さい。

    エクセルには置換の他にも検索などデータを処理する機能が豊富に用意されています。上手に活用することで業務の効率化に役立てたいものです。

    この記事が気に入ったら
    いいね ! しよう



     トイロハのおすすめ記事