今回の記事では
特定の文字列を検索して目的の文字に置き換えられる関数ってあるの？
同じ間違いをしている文字列を一気に置き換えられたら便利なのに
文字列の中に含まれている一文字を置き換える関数があるなら知りたい
一文字だけ調べていちいち修正するのは面倒だから何とかならない？
こういった疑問に答えます
✔文字列を置換する関数のテーマ
①文字列を置換するならSUBSTITUTE関数でサクッと変換できます
②文字列の一部を置換するならREPLACE関数を使うのが最適解です
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「文字列を置換する関数」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
文字列を置換するならSUBSTITUTE関数でサクッと変換できます
文字列を置換するならSUBSTITUTE関数を使えば簡単に置き換えられます
なぜなら、SUBSTITUTE関数は指定文字列の置換に特化した関数だからです
では、SUBSTITUTE(サブスティチュート)関数の使い方について見ていきましょう
今回は「勘定科目の置き換え」や「１文字置き換え遊び」を例に使い方を解説してきます
こんな風に勘定科目をSUBSTITUTE関数を使って置換(ちかん)していきます
ちなみに置換とは「置き換える」と同じ意味なので言い方が難しい場合は「置き換えるための作業をするんだんな」と覚えることをオススメします
まず、SUBSTITUTE関数の基本的な使い方は「SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])」と設定します
但し、「置換対象」は設定しなくても問題無いので今回は設定しません
また、SUBSTITUTE関数は原則１つの項目を置換してくれる関数なので修正が複数ある場合には置換機能を使った方が早い場合があるということを覚えておきましょう
今回はそれぞれの勘定科目を修正していきます
修正する項目は「ソフトウェア→消耗品費」「交通費→車両費」「宣伝広告費→広告宣伝費」です
但し、SUBSTITUTE関数は「検索文字列１つ」「置換文字列１つ」を交換する関数なので同時に複数の置換は出来ません
今回は「ソフトウェア→消耗品費」のパターンを変換してみましょう
SUBSTITUTE関数の設定内容は「SUBSTITUTE(B5,$F$5,$G$5)」なのでこれを日本語にしてみましょう
「SUBSTITUTE(B5セルの内容を置換してね,F5セルの文字列を,G5セルの文字列に)」という意味です
分かってしまうと簡単ですね
こんな風に無事、置換されています
実務でもよくあることですが、こんな風に勘定科目がバラバラになってしまうので、置換関数を上手く使ってあげるとキレイに修正してくれます
但し、このままだと他の科目はそのままになってしまいますね
SUBSTITUTE関数をネストすることで他の科目も修正できるとは思いますが、設定項目数が多いのであまりオススメできません
そんな時は、Excelの置換機能を使った方が簡単に修正できるのでその方法も見ていきましょう
まずは、置換したい範囲を選択しておきます
置換機能を限定した範囲で使うためには前もって範囲選択しておくことが重要です
範囲選択していないとシート全部が置換する範囲になってしまうので注意が必要です
「ホームタブ」の「編集グループにある検索と選択」をクリックして「置換」をクリック
「検索と置換ダイアログ」が表示されるので
「検索する文字列と置換する文字列」をそれぞれ設定します
今回の場合は「検索するのは交通費」で「置換するのは車両費」なので設定はこんな風になります
「すべて置換」ボタンをクリックすると一気に置換してくれます
置換が終わると何件分置換してくれたのか知らせてくれるので「OK」ボタンをクリック
置換ダイアログを閉じて結果を見てみるとちゃんと置き換えてくれています
こんな風にいくつも置換したい項目がある時は、関数よりも置換機能を活用した方が効率的ですね
範囲を気にせずサクッと置換したい場合は、「SUBSTITUTE関数」を活用しましょう
複数の置換が必要な場合は、範囲を指定して「置換機能」を活用した方がスムースに作業が進みます
繰り返しになりますが、文字列を置換するならSUBSTITUTE関数を使えば簡単に置き換えられます
文字列の一部を置換するならREPLACE関数を使うのが最適解です
文字列の一文字など一部分を置換する場合はREPLACE関数を使うのが正解です
なぜなら、REPLACE(リプレース)関数は文字の位置を特定して置換してくれる関数だからです
一文字置き換えゲーム
今回は一文字入れ替えて別の言葉にする「一文字置き換えゲーム」の作り方を通して解説していきます
このゲームは、「置き換え前の文字の1文字」を変えて「他の言葉にする」ゲームです
例えば、「キック→コック」のように1文字だけ変えることで別の言葉にするというルールです
一文字を変えたい時は「REPLACE関数」を使います
REPLACE関数の基本的な使い方は、「=REPLACE(文字列,開始位置,文字数,置換文字)」です
今回の式は「=REPLACE(A5,1,1,B5)」なのでコレを日本語にしてみましょう
「=REPLACE(A5セルの文字列の,1文字目を,1文字分,B5セルの文字に置換してね)」という意味になります
要は一番左の文字を指定された文字に置換するということです
そのため、ちゃんと置換できている文字と置換に失敗している文字がありますね
ではどうすれば他の文字もちゃんと置換できるか考えてみましょう
よく考えてみると問題は「開始位置」にあると思いませんか？
今指定している内容は、左端から1文字(1文字目)を置換するという設定です
つまり、開始位置を柔軟に特定出来れば問題解決できそうですよね
そこで開始位置を関数を活用して設定してみましょう
開始位置の特定にはFIND(ファインド)関数を使いましょう
FIND関数はの基本的な使い方は、「=FIND(検索文字列,対象,[開始位置])」です
今回の「=FIND(B5,E5,1)」を日本語にしてみると「=FIND(B5セルの文字列を探して,E5セルの文字列の中で,1文字目から調べて見つけたら位置を表示してね)」
という意味になります
つまり、FAIND関数は「調べたい文字が、検索対象の文字列の何番目にあるか調べてくれる」関数です
FIND関数の詳しい使い方は、「FIND関数で知りたい文字が含まれているか分かる」などの記事で解説しているのでそちらを参考にしてみて下さい
この数字をREPLACE関数の設定に組み込むとこんな感じになります
設定下内容がこんな感じです
変更したのは開始位置だけなのでほぼ変わらないですが、これで柔軟な検索ができるようになったので全問正解です
最後の仕上げは「REPLACE関数とFIND関数」をネストさせると更にスマートです
これで関数だけで柔軟に文字を置換することが出来ます
1列増やしたくない場合は活用してみましょう
虫食い問題の作り方
おまけで虫食い問題の作り方を紹介します
REPLACE関数の設定を少しだけ変えることで置換の文字を「□の虫食い問題」にすることも出来ます
設定方法は、最後の置換文字に「□」という文字を設定するだけなので虫食い問題を作りたい時などにとっても便利です
遊びがてら作ってみると練習になりますよ
最後にもう一つだけおまけですが実は、SUBSTITUTE関数でも同じようなことが出来ます
こんな風にSUBSTITUTE関数でも「検索文字」を設定してあげるだけで同じことが出来ますが、ただ「アイアン→アイコン」のように「ア」が2回出てくるような場合は間違った結果になってしまうので、1文字置換の場合はREPLACE関数一択です
繰り返しになりますが、文字列の一文字など一部分を置換する場合はREPLACE関数を使うのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す