Aさん 手入力していくデータで「りんご」と「アップル」など、同義語で表記ゆれがある場合、いちいち置換などで直すのが面倒です・・・。
こうした場合、もっと楽に修正することができないですかね？
森田 その場合は、関数の「SUBSTITUTE」を活用すると良いですよ！
では、SUBSTITUTEの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要については以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
元データの同義語の表記を統一したい場合は「SUBSTITUTE」が有効
手入力するデータは「表記ゆれ」が起きてしまうもの。
表記ゆれとは、人から見れば実質同じデータなのに、PC（Excel）から見ると別の表記になってしまっていることを指します。
その一例が、同義語です。
同義語とは、「りんご」と「アップル」など、同じ意味だが表記が異なる言葉のことです。
同義語での表記ゆれがあると、これをキーにした場合の検索や集計ができない、あるいは結果が狂うというリスクがあります。
よって、事前に同義語は表記を統一することが必要です。
こんな場合、関数の「SUBSTITUTE」を使うことで、文字列中の指定の文字を新しい文字への置換を自動化し、同義語の表記を統一することが可能となります。
ちなみに、SUBSTITUTEは「サブスティチュート」と呼びます。
よって、同義語の表記を統一したい場合はSUBSTITUTEを使用していきましょう。
文字列の位置を基準に置換したい場合は「REPLACE」を使用してください（後日記事にまとめる予定）。
SUBSTITUTEの構文
SUBSTITUTEの構文は以下の通りです。
=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
文字列中の指定した文字を新しい文字で置き換えます。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 文字列 | ○ | 文字列 | 置換対象のセル（値が文字列）を指定します。 |
| 検索文字列 | ○ | 文字列 | 「文字列」の中から置換対象の文字を指定します。 ※置換前の文字 |
| 置換文字列 | ○ | 文字列 | 「検索文字列」を置き換える新しい文字を指定します。 ※置換後の文字 |
| 置換対象 | － | 文字列 ※実際は「数値」 |
「文字列」中に複数の「検索文字列」がある場合、何番目のものを置き換えるか指定します。 |
文字列に引数「検索文字列」が含まれない場合は、引数「文字列」に指定した値がそのまま表示されます。
引数「置換対象」を省略すると、すべての引数「検索文字列」が置換されます。
通常の「置換」機能と異なり、引数「検索文字列」にワイルドカードを指定することはできません（「*」や「?」がそのまま文字列として認識される）。
【参考】「検索と置換」ダイアログと関連付けて覚えることがおすすめ
SUBSTITUTEの引数「検索文字列」・「置換文字列」については、「置換」機能で起動する「検索と置換」ダイアログと関連付けて覚えることがおすすめです。
同じ置換なので、必要な情報は一緒だと分かります。
【参考】SUBSTITUTEは「文字列操作関数」
あくまで参考情報となりますが、SUBSTITUTEはリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。
実際にSUBSTITUTEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
SUBSTITUTEの使用結果イメージ
SUBSTITUTEで同義語の表記を統一したイメージは以下の通りです。
今回は「商品名」列の「アップル」を「りんご」へ置換しました。
なお、SUBSTITUTEは原則一つの関数で一つのセルのみが変換対象です。
ベースの数式をセットしたら、他のセルへペーストしましょう。
また、SUBSTITUTEは一つの関数で一つの文字しか置換できません。
もし、複数の文字を置換したい場合は、その種類の数だけSUBSTITUTEが必要となります。
1つ目のSUBSTITUTEの戻り値を2つ目のSUBSTITUTEの対象（引数「文字列」）にするイメージです。
置換対象の文字の種類が多い場合は、置換対象の文字の一覧となる表を用意し、VLOOKUPで置換前の文字をキーに置換後の文字を転記した方が便利です（詳細は以下の記事を参照）。
はじめに 本題に入る前に、この記事がおすすめな人を挙げてみます。 データの転記・集約作業が多い人 エクセルを使 …
【注意】SUBSTITUTEの対象が数値や日付/時刻でも戻り値は「文字列」になってしまう
SUBSTITUTEの引数「文字列」に指定したセルの値が数値や日付/時刻であったとしても、戻り値のデータ型は「文字列」となってしまいます。
一例として、数値の「100」のうち、「1」→「2」へ置換してみたものが以下です。
基本的にSUBSTITUTEは文字列を対象に使うことが一般的ですが、万が一数値や日付/時刻を対象にする際はご注意ください。
データ型を「数値」にしたい場合は「VALUE」、「日付」にしたい場合は「DATEVALUE」、「時刻」にしたい場合は「TIMEVALUE」をそれぞれ併用しましょう（後日記事にまとめる予定）。
【参考】引数「置換対象」の活用イメージ
実務で使うケースはレアですが、引数「置換対象」まで指定するとどうなるかを見てみましょう。
一例として、「iPhone11」のうち「1」→「2」へ置換する際、引数「置換対象」の有無でどう変わるか、以下をご覧ください。
実際は「11」→「12」で置換すれば良いですが、実務で置換したい対象の文字が文字列中の2番目以降にある場合に活用してください。
SUBSTITUTEの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はH2セル - 「=su」と入力
- サジェストから「SUBSTITUTE」を選択し、「Tab」キーで確定
- 置換対象のセルを選択
※今回はD2セル - コンマ（,）を入力
- 置換前の文字を入力
- コンマ（,）を入力
- 置換後の文字を入力
- 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はH3~H21セルへペースト
手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑩は不要（全レコードへ数式が自動的にコピーされる）。
手順⑥⑧で空白（ブランク）を指定したい場合はダブルクォーテーション（”）を2つ入力すること。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_SUBSTITUTE.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はH2セル - 「=su」と入力
- サジェストから「SUBSTITUTE」を選択し、「Tab」キーで確定
- 置換対象のセルを選択
※今回はD2セル - コンマ（,）を入力
- 置換前の文字を入力
- コンマ（,）を入力
- 置換後の文字を入力
- 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はH3~H21セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
SUBSTITUTEは表記ゆれを修正する際に役立つ関数のうちの一つです。
定期的に同義語での表記ゆれを統一する機会があるなら、ぜひ覚えておいた方が良いですね。
なお、SUBSTITUTE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 SUBSTITUTEとセットで覚えておいた方が良い関数は、文字列の位置を基準に置換できるREPLACEです。
また、SUBSTITUTEは他の文字列操作関数と比べて引数が多いですが、「置換」機能をイメージすれば理解しやすいですよ！
