Excel(エクセル)では文字や数値を置き換えて別のものにしたい場合もあるかもしれません。1つずつ修正すると時間がかかりますから、Excel内でまとめて置換することで別の数字などに置き換えることができると便利ですよね。今回は、エクセルで数字などを置き換える際の関数を使ったやり方や数式と、置換できない原因や解決策として考えられることをご紹介します。
置換とは、エクセルシートにある特定の数字や文字を別のもので表記することです。関数を使って置き換えをする方法もありますが、エクセル上にある機能を使うことでも可能です。今回の例では、表に書かれている年代をエクセルの置き換えで20代→20などと数字のみにする作業を考えてみましょう。
①Excelシートを開き、上側にあるタブのうち『ホーム』を押します。その後、右端に出てくる「編集」内にある虫眼鏡マークの『検索と選択』をクリックしましょう。
②上から2番目に出てくる『置換(R)』を押します。ちなみに、置き換えにはショートカットもあり、キーボードの『Ctrl』と『F』を同時に押すことで、ここまでの操作を一気に行うことも可能です。
③エクセルシート上に「検索と置換』と書かれたものが出てきました。このうち『置換(P)』の方を選びます。置き換えたい文字や数字を『検索する文字列(N)：』と書かれたすぐ右の空欄に、置き換えた後に表示したい文字列や数字を『置換後の文字列(E)：』と書かれたすぐ右の空欄に入力しましょう。
今回の例では、年代の●●代の「代」を消す作業をするため、 『検索する文字列(N)：』 に「代」・ 『置換後の文字列(E)：』 には何も入力しませんでした。このように、単に一部の文字を消したい場合には 『置換後の文字列(E)：』 には何も書かないことも可能です。
④『すべて置換(A)』をクリックします。こうすることで、『検索する文字列(N)：』に入力した文字列や数字をExcel内で探し出し、『置換後の文字列(E)：』で入力した内容に自動的に書き換えられるのです。ちなみに、その隣の『置換(R)』をクリックすると対象となるセルが一つずつ置き換えられます。チェックしながら変えたい場合にはこちら、複数まとめて行いたいのであれば『すべて置換(A)』の方が効率的でしょう。例では、●●代が数字のみになっていて、「6件を置換しました」と表示されました。
エクセルでは数字を記号に置き換えることもできます。先ほどと同じ手順で、「置換と検索」から『検索する文字列(N)：』と『置換後の文字列(E)：』を入力しましょう。エクセルには文字列と書かれていますが、数値や記号でも問題なく反応します。例では、20という数字を++に置き換えてみました。
エクセルで数字などの置き換えは関数を使う方法もあります。SUBSTITUTE関数(サブスティチュート関数)と呼ばれる機能です。特に、元の文字列を残しておきたい場合や、置換したい文字列が複数ある状況で役立ちます。エクセルで関数を使って置換する際の数式や手順について、セル上の”m”の文字を”男”に変える作業を例にみていきましょう。
まずは、置換後にその内容を表示したいエクセルのセルをクリックします。同じ場所に表示したい場合には、作業後にコピーして貼り付けが必要です。書式は、『=SUBTITUTE(』とまず入力しましょう。いずれも『shift』キーを押しながらそれぞれのアルファベットや記号が書かれているキーを押していきます。その後クリックするのが、置換したい文字列が書かれているセルです。
その後、カンマ『,』を入力して、エクセルで置き換えたい数字や文字列を入力します。セル全体の内容ではなく、そのうちの一部であっても問題ありません。文字列の場合には両端を””で囲むことを忘れないようにしましょう。(“”は『shift』+『2』の同時押し)
さらに、カンマ『,』を入力して、エクセルで置き換えた後に消したものの代わりに表示する数字や文字列を入力します。今回の例では、mの文字を男に変えたいので、ここまでで『=SUBSTITUTE(セル番地,”m”,”男”』になりました。
最後に『)』を入力して『Enter』キーを押すと、指定したセルに置換後の内容が表示されます。()の2つ目に入力した置き換えたい内容は部分一致でも反応し、指定した部分だけが置き換えた後の内容に変わって既存の文字列や数値などとつながっている点がポイントです。この方法を利用すると、エクセルの置き換えで特定の数字のみを変えたい場合に簡単に変えられますので、あわせて押さえておくと良いでしょう。
SUBSTITUTE関数の数式の最後にある「置換対象」は入力しなくても数式は成立しますが、知っておくと便利かもしれません。エクセルでの「置換対象」とはセル内の一部(何番目)の文字だけを置き換えるように指定する機能です。たとえば、ABCCEABCDEをABCDEABCDEにしたい場合、Cのうち2番目ののみをDに変える必要がありますが、単なる置換ではCが全部Dに変わってしまいます。このときにサブスティチュート関数の「置換対象」に2を入力すると、1・3番目のCはそのままで2番目だけをDにできます。ごく一部の文字だけを変えたい場合に便利な機能ですから、あわせて使いこなしましょう。
置換できない原因の一つに、エクセルが対象のデータを認識できていないことが挙げられます。スペルミス、大文字小文字や全角と半角の違いなどがあるかもしれません。解決策は、「検索」機能を使って置き換えたい文字がヒットするかを確かめることです。最初の見出しの③「検索と置換」で『検索』を選び、置き換えたい文字列を検索することで全て認識されているかを調べてみましょう。
置換してほしくない部分までエクセルが変えてしまうことで思ったようにできない場合もあるでしょう。関数を使うことでエクセルの置換をセルの一部のみに適用できることを先ほどご紹介しましたが、冒頭で触れた「検索と置換」の機能ではうまくできません。特に文字の置き換えをする場合には関数も対象となるため、計算式が成立しなくなる原因にもなります。置換を1セルずつ行う(『置換(P)』を使う)ことでどの部分が変わったかを確かめ、変わってほしくない部分に影響があれば「置換対象」を使って細かく設定し直しましょう。
Excelでの置換の対象は数字や文字であり、ここには数式も含まれます。しかし、エクセル上の数字が数式を使った計算結果である場合には置換できない仕様です。たとえば、画像のように指定したセルを置換しようとしたところ、エクセル上にエラーが出ました。この数字が、SUM関数によって導き出された計算結果だからです。
算出した値を変えたい場合には、元々の計算式を変える・別の場所にコピーして改めて置換する・関数を使って置き換える、のいずれかを行いましょう。 別の場所にコピーして改めて置換する場合には、特殊な貼り付けを行う必要があります。単に、『Ctrl』+『C』でコピーして『Ctrl』+『V』で貼り付けると数式として入力される関数までコピーされるためです。 『Ctrl』+『Alt』+『V』とオルトキーも同時に押すと 「形式を選択して貼り付け」という画面が出てきますので、『値(V)』を選択してから『OK』をクリックしましょう。
エクセルで置換できない場合として、文字や記号を新たに挿入するときが挙げられます。Excelにある「検索と置換」やSUBSTITUTE関数は、代わりに消す文字や数字を探し出せることで機能するからです。もし、特定の位置に挿入する場合には、REPLACE関数を使うことができます。REPLACE関数は、指定した位置の内容を置き換える関数ですが、SUBSTITUTE関数との大きな違いは、開始位置の指定があることです。REPLACE関数の数式は、『=REPLACE(文字列,開始位置,文字数,置換後の文字列)』であり、左から数えて[開始位置]番目より[文字数]分だけの文字や数字が指定した内容に変わります。
この関数を応用することで、挿入が可能です。文字数を0にすると、消す内容はありません。他の要素は同じように指定すると、[開始位置]番目のところに[置換後の文字列]がそのまま追加される形になります。例では、6番目に-を挿入しました。
今回は、Excelで数字などを置換する方法をご紹介しました。置換できない場合には、エクセルがうまく反応できない文字や数字にしている可能性がありますので、解決策を順番に試しながら探るのが良いでしょう。最後までお読みいただきありがとうございました。