エクセルを使い始めたころだと、たくさんのツールや関数があるため、すぐに完璧に覚えることはとても難しいですよね。
そんなときは、使いたい内容に合わせて調べ、そのつどどのシーンにはどの関数やツールが必要かをイメージできれば、問題なくエクセルを習得することができます。
この記事で紹介するエクセルの関数は【SUBSTITUTE関数】です。
SUBSTITUTE関数とは、もともと作成されたエクセルのデータにある文字列セルの一部を書き換える関数のことを指します。
「検索と置換」を使う方法もありますが、SUBSTITUTE関数にはどのような違いがあるのか、さらにエラーやおすすめのツールについてもあわせて解説します。
目次
SUBSTITUTE関数とは？
SUBSTITUTE(サブスティテュート)関数とは、文字列セルにある文字を別の文字へ書き換える方法です。
似たような方法として【検索と置換】の方法がありますが、この方法では元のデータがすべて書き換えられてしまいます。
元のデータを維持しつつ、書き換えたい文字がセル内に複数ある場合はSUBSTITUTE関数を使うと良いでしょう。
h2 SUBSTITUTE関数の使い方と書式
SUBSTITUTE関数の書式は以下になります。
| =SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象となる文字]) |
SUBSTITUTE関数は最大で4つの引数(ひきすう)を使います。
引数(ひきすう)とは、エクセルで関数を使用する際に必要な情報として入力する文字や数字のことです。
それぞれの意味を解説します。
第1引数【文字列】
SUBSTITUTE関数の最初の引数である【文字列】は記入必須項目であり、セル内のテキストや置換するテキストが含まれるセル参照を指定する役割があります。
文字列を指定する場合は【”○○”】のように【””】(ダブルクォーテーション)で囲って指定しなければなりません。
第2引数【検索文字列】
2番目に使用する引数は【検索文字列】と呼びます。
検索文字列も入力必須で、第1引数の文字列から検索する文字列を指定する際に持ちいります。
第3引数【置換文字列】
3番目に使用する引数は【置換文字列】と呼びます。
置換文字列も入力必須の引数です。
第1引数の文字列から検索した文字列を書き換えるための文字列を入力・指定して使います。
第4引数【置換対象となる文字】
4番目の引数は【置換対象となる文字列】です。
この引数は使用する内容によって任意で入力するものであり、必須項目ではありません。
文字列のなかに複数の検索文字列が対象となったとき、何番目の文字列を置き換えるかを数字で指定するために使用します。
指定しない場合はすべての検索文字列が置換文字列に書き換わります。
SUBSTITUTE関数の基本的な使い方
たとえばこのエクセルの場合だと【東京都渋谷区渋谷】と住所が間違っています。
このような状態のときは、C3のセル内にこのような関数を作成します。
| =SUBSTITUTE(B3,”渋谷”,”神宮前”,2) |
B3のセル内にある1つめの【渋谷】を残し、2つめの【渋谷】を神宮前に書き換えるよう、4つめの引数に「2番目」という意味の【2】を記入し【Enter】を押します。
B3の住所が書き換えられたことがわかります。
このように、セルにある一部分を修正したい場合は、SUBSTITUTE関数で文字列を指定して書き換えれば、同じ間違いが複数あっても瞬時に書き換えられます。
関数の挿入を使ってSUBSTITUTE関数を使う方法
エクセルでは関数を手入力して引数をそれぞれ入力していくパターンとは別に、【関数の挿入】という独自ツールを用いて、それぞれ入力していく方法の2パターンがあります。
手入力で入力する場合、関数に必要な引数の意味や項目を覚えておく必要がありますが、関数の挿入を使うことで、どこになにを入れるべきなのかを案内してもらいながら入力し、関数を作成することができます。
また、関数を作成する際、英単語を入力する必要があります。
しかし、スペルを思い出せないシーンだと、そのまま関数を作成してしまえば、エラーになってうまく参照することができないといった不具合を起こしてしまいかねません。
このように、エクセル初心者でも、エクセル上級者でも、さまざまなシーンで活躍するツールですから、関数の挿入を使ったエクセル作成法も一緒に覚えておくと良いでしょう。
【関数の挿入】は、エクセルを開き関数を入力する窓の横に、【fx】と書かれた記号があるのが確認できるかと思います。
この部分をクリックすることで、関数の挿入を行うことができます。
では実際に関数の挿入を使って関数を作成してみましょう。
エクセルを開き、関数の挿入をクリックします。
すると関数の挿入ダイアログボックスが開き、【何がしたいかを簡単に入力して、[検索開始]をクリックしてください】と文書が表示されています。
この文書を削除し、【SUB】と入力し、【検索開始】をクリックしましょう。
すると【SUB】を含んだ関数が一覧表示されるので、今回使用する【SUBSTITUTE】を選択し、【OK】を押します。
関数の引数ダイアログボックスが開いたら、全ての項目に引数となるセル数値や文字列を入力していきましょう。
今回は、基本的な使い方でも使用したように、
東京都渋谷区渋谷
↓
東京都渋谷区神宮前
へと変更したいので、
まずは入力間違いしているセルを選択しましょう。
上記のデータを確認すると入力ミスをしているセルは【B3】のセルです。
そのため、【文字列】部分に入力する数値は【B3】となります。
つづいて【検索文字列】です。
今回の場合、渋谷を修正し、神宮前と変更したいので、ここでは【渋谷】を入力します。
また、修正したい住所では渋谷を2つ使用しているため、後述しますが、その部分も、引数を使って2つめの渋谷を変更するように指示します。
【置換文字列】には、変更後の文字列を記入します。
今回は【神宮前】と変更したいので、【神宮前】と入力しましょう。
最後に【置換対象】です。上述したように、変更したい住所のなかには【渋谷】が2つ入っています。
変更したい渋谷は【2】つめの部分なので【2】を入力します。
間違いなく入力できたら最後に【OK】をクリックしましょう。
関数が手入力したときと同様、
| =SUBSTITUTE(B3,”渋谷”,”神宮前”,2) |
と作成され、無事、2つめの【渋谷】を【神宮前】に変更することができました。
ひとつ注意したいのが、エクセルで関数を作成する際、文字列には必ず【””】(ダブルクォーテーション)を使い、【,】で区切る必要があります。
手入力で行う際は、ご自身で関数を作成する際にそれぞれ入力しなければなりませんが、関数の挿入を使った場合は自動的に【””】も【,】も入った状態で作成してくれます。
そのため、このクセが付いてしまうと実際に手入力で関数を作成した際に、エラーを起こしす可能性や、数値や文字列が正常に反映しないといった不具合を起こす可能性があります。
関数の挿入を使う際も、文字列を挿入する場合は【””】を使うクセをつけるようにしましょう。
また、関数が完成したあとは、各引数のあとには【,】を含め、どのような演算子を使用しているのかをチェックするとインプットしやすいのでこの2項目を心掛けてくださいね。
SUBSTITUTE関数の応用
SUBSTITUTE関数をの使い方をマスターできれば、あらゆるセル内の置換をより効率よく行うことができるようになります。
ネストで複数の文字列を同時に置換させる方法
上記のようにそれぞれ千代田区と渋谷区と入力されたエクセルがあるとします。
しかし住所を間違えており、千代田区を新宿区、渋谷区を江戸川区へと書き換えたいとします。
この場合はC3セル内に、
| =SUBSTITUTE(SUBSTITUTE(B3,”千代田”,”新宿”),”渋谷”,”江戸川”) |
と入力します。
の関数を解説すると、【千代田】を【新宿】を入れ子にして書き換えたのち、さらに書き換えた文字列をSUBSTITUTE関数の引数にして【渋谷】を【江戸川】へと書き換えるという意味になります。
【Enter】を押すと千代田区が新宿区へと置換されたことがわかります。
ではそのままC3のセルの右下角にマウスを合わせ【オートフィル】を用いてC4のセルまで関数ドラッグし自動入力させます。
オートフィルを用いたことでC4セルにも、
| =SUBSTITUTE(SUBSTITUTE(B4,”千代田”,”新宿”),”渋谷”,”江戸川”) |
とB4を指定した関数が自動で入力され渋谷を江戸川へ書き換えることができました。
空白を削除する方法
セル内にあるデータには、数字などの文字だけでなく、なかには空白(スペース)が混ざっていることがあります。
この空白も、SUBSTITUTE関数を使って一括削除することが可能です。
B3のセルの住所に【 】が入っているのがわかります。
これをSUBSTITUTE関数を使って「空白がない状態」に書き換えてみます。
その際関数の書式は以下のようになります。
| =SUBSTITUTE(B3,” ”,””) |
C3のセルに上記の関数を入力し【Enter】を押します。
無事【 】を除いた住所に書き換えることができました。
同じように【 】(半角スペース)を置換したい場合は【” “】と記入することで同じように書き換えることができます。
改行を削除する方法
上記の空白と同じような方法を用いて改行を削除することも可能です。
その場合の関数の書式は以下のようになります。
| =SUBSTITUTE(B3,CHAR(10),””) |
上のエクセルのように、住所内に改行があったとしましょう。打ち間違いなどをしたことにより気づけないこともありますが、意外とこのような入力ミスをしてしまうことも少なくありません。
このような場合も、上記のように関数を用いればすぐに削除することができます。
C3のセルに上記の関数を入力し【Enter】を押します。
改行のない文字列へと書き換えることができました。
たとえばこのような状態のセルが複数あるとします。
このような状態のときも【オートフィル】を用いることですべて一瞬で同じ条件で書き換えることができます。
複数のセルに改行や空白が入っていても、オートフィルを用いることでどの行も一気に書き換えることができます。
ただし、書き換えるためには書き換えるべくセルの横にあらかじめ書き換え専用のセルを用意しなければなりません。
(SUBSTITUTE関数を使う場合は、あらかじめその隣に書き換え専用のセルを用意する、という意味です。)
列の挿入方法は、追加したいセルの1番上で右クリックを押し【挿入】を押すことで列を挿入することができます。
SUBSTITUTE関数におけるエラーの理由と解決方法
関数をうまく作成したはずなのに、なぜかうまく表示されないときや、エラーとなってしまうことがあります。
ここではSUBSTITUTE関数における主なエラーの理由と、その解決方法について解説します。
指定した文字列で変更されない
この画像を見てみましょう。
一見、関数はしっかり入っているように見えますが、置換後の文字列が置換前の文字列と全く変わっていません。
関数をよく見てみると、【”渋谷”】と【”神宮前”】の間に置くべき【,】が抜けています。
文字列を羅列、または文字列の後に数値などを入力する場合は【,】をはじめ、関数にふさわしい演算子を入力しなければうまく関数が作動しません。
この場合は、文字列と文字列の間に【,】を入れることで解決します。
【#NAME?】になるエラーとその解決方法
関数を作成し終えた際、【＃NAME?】となってしまう可能性としては、関数のスペルの入力間違いが考えられます。
SUBSTITUTEのスペルを確認してみましょう。
最後の【E】が【O】になっています。
#NAME?エラー担った場合は、関数のスペルを今一度確認してみましょう。
関数を入力したのに変更後空白になる場合の理由と解決方法
関数は問題なく入力されているのに、なぜか変更後のセルが空白になってしまうことがあります。
この場合第1引数である【文字列】として選んだセルが、空白のセルを選んでいる可能性が考えられます。
上記の画像でいうと、文字列を入力したセルはB3であるのに対し、第1引数で入力したセルは空白の【E4】となっています。
そのため、空白のセルができてしまうということなのです。
この場合は、第1引数のセル選択部分を確認すると解決できますよ。
まとめ
SUBSTITUTE関数を覚えることで、わざわざ検索と置換を行わずとも、書き換えたい文字や空白、改行などを的確に書き換えることができるようになります。
オートフィルを使用すれば、検索を行わずとも、一瞬で書き換えてくれるため、手間も省け、その分効率よく作業を進めることができます。
オフィスシーンのみならず、自宅でのちょっとしたエクセル作業でも活躍する関数ですから、ぜひこの機会にSUBSTITUTE関数をマスターしておきましょう。
