root 
業務上、ある特定の文字列を抜き出したり、一括して別の文字列に置換することはよくあります。その際に利用されるのが、Wordなど他のMS Officeソフトでも利用可能な「検索と置換」機能。
Excel2019の場合、画面右上にある検索窓のプルダウン・リストから選択して表示させるダイアログボックスです。
WordやPowerpointもよく使うユーザーでしたら、似た操作で、目的の置換を簡単に終えることができ、とても便利です。
しかし、この操作で置換をすると、置換前の文字列はシートに残らないので、Excelの場合には、ぜひ関数を使って置換する習慣をつけましょう。
なぜなら、「時間がたってからの繰り返し作業」「他人との共同作業」では、置換前後の文字列を比較する必要があるからです。
置換に使えるExcelの関数には、REPLACEとSUBSTITUTEなどがあります。
［構文］ REPLACE(文字列, 開始位置, 文字数, 置換文字列) SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象]) ※SUBSTITUTEの[置換対象]は省略可能
REPLACE 関数は、文字列に含まれる、指定された文字数の文字を別の文字に置き換えます。それに対して、SUBSTITUTE 関数は、文字列に含まれる、指定された文字列そのものを、全く別の文字列に置き換えます。
「検索と置換」ダイアログボックスの機能に近く、置換前後の文字列を比較できるのはSUBSTITUTE 関数の方だと言えるでしょう。
ここで注意しておきたいことは、REPLACEもSUBSTITUTEも、ともに「文字列操作」のための関数であるという点です。
関数を使えば、文字列からデータ計算用の数値を取り出すことが可能
SUBSTITUTE関数の代表的な利用方法としては、たとえばセル内に文字列として入力された「1,500円」「3,800円」などのデータから、数値としての1500、3800を取り出して、計算に利用する場合などがあげられます。
この操作で注意する点は、”円”が削除されただけの関数返り値もまた「文字列」で、そのままでは計算に使用できないということです。
文字列の特徴どおり、返り値もセルの中で左詰めになっていますよね?
文字列を数値に変換するVALUE関数と併用することで、目的の結果を得ることができます。
関数なら「差し込み」パーソナル文面の作成も可能になります
置換前の文字列を保存できるということは、ビジネス文書への応用範囲がとても広がる、ということでもあります。
以前にも、Excel VBAを活用して差し込み印刷という事例をご紹介しましたが、同じように、顧客や商品別にカスタマイズした文書、電子メール文案を作成する上で、置換前の履歴が残るSUBSTITUTE関数を活用すれば応用範囲が広がるのです。
1つ事例をみてみましょう。
受注台帳(実際には、別シートに数十件、数百件単位で受注履歴が存在するものとします)から、出荷手配のすんだ案件について、顧客に電子メールで通知文を送るとしましょう。
「顧客氏名」+「受注商品」+「出荷日付」は、セットとなる情報なので、手作業で転記して、どれか1件でも取り違えると、連絡ミスが生じます。できれば、一斉に同一レコードから差し込みできるようにしたいものです。
上記のシートでは、セルA7に差し込み前の文案を用意し、セルA10でSUBSTITUTE関数を使って<name>、<item>、<date>を受注台帳のレコードから差し込むようにしています。
セルA10の計算式はこのようになります。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$7,"<name>",A2),"<item>",B2),"<date>",C2)
3つのSUBSTITUTEをネスト(入れ子)にして、受注台帳の「氏名」「商品」「日付」を、同一の行からいっせいに差し込むようにしています。
差込前の文案のみ、セル番地(A7)に$マークをつけて絶対参照としておけば、オートフィルで下方へ同じ計算式をコピーするだけで、受注台帳の2行目、3行目が自動的に差し込まれ、別宛名へ、文案通りのメール文面を生成できます。
ここでも注意すべきは、もし台帳の「日付」が数値データになっていたら、差込前に文字列に変換しておくことです。
もし受注台帳の日付を数値データのまま、文字列操作関数であるSUBSTITUTE関数で置換してしまうと、差し込んだ文章の日付が数値(日付のシリアル値)で表示されます。
「商品は、44652に出荷予定です。」というメールを顧客が受け取ったら、折り返しの問い合わせが激増してしまいますね。ご注意ください。
まとめ:ビジネスで履歴を残すことは重要、置換にはSUBSTITUTE関数を活用しましょう
この事例のように、別シート(台帳など)で過去の履歴を管理しながら、文字列を置換して別文書を作成するには、SUBSTITUTE関数を利用するのが便利です。
普段は、別のOfficeソフトで「検索と置換」ダイアログボックスの利用に慣れている方も、せっかく「関数」という便利な機能があるのですから、Excelブックでの置換には、ぜひSUBSTITUTEを使う習慣をつけておきましょう。
また、SUBSTITUTEは「文字列」を扱う関数であるということを意識して、この関数で置換を行う前には、置換対象が文字列かどうかに気をつけてください。その点だけ注意しておけば、オートフィルによる連続操作にも対応できますので、1回ずつダイアログボックスを呼び出す「検索と置換」機能よりも、ずっとラクに大量の置換作業ができますよ。
