置換)●はじめに今回説明するのは、文字列を置換する機能のReplace関数の利用方法を説明いたします。置換開始位置や置換や該当した置換の回数を指定する事も引数を設定する事で、利用する事ができます。実際に文字列等を変換する時は、その置換目的に合わせて使い分けて下さい。それでは、サンプルプログラムを交えて順番に説明いたします。Replace関数を利用するには、下記の通りに設定を行います。“エクセル”)※セル「A1」の【EXCEL】の文字列を【エクセル】に置換します。compare:=vbTextCompare)※セル「A1」の空白文字の大文字スペース・小文字スペース共に削除します。“SUBTOTAL(101,”)※セル「D3」に登録されている関数を置換(SUM関数⇒SUBTOTAL関数)します。※SUM関数では、合計値を計算・SUBTOTAL関数では、平均値を計算しています。●Replace関数とReplaceメゾットの違いについて・Replace関数:変数やセル値の指定文字列の一部を置換します。・Replaceメゾット:指定範囲のセルの文字列の一部を置換します。(A列全部やA1:A1000などのセル範囲を指定する場合は使いやすい)Replace関数を使って複数文字列を置換します。(サンプル1)下記のサンプルプログラムは、複数文字列を一括に置換します。A列に文字列としてExcel・Word・Outlookが複数登録されています。この文字列に対して、エクセル・ワード・アウトルックへ一括変換します。【プログラムの流れ】1A列の文字列に対して、置換対象の文字列へ置換します。'Replace関数を使って複数文字列を置換します。'Replace関数を使って複数文字列を置換します。'A列の最終行を取得します。'Replace関数を使って複数文字列を置換します。'A列の最終行を取得します。※プログラム実行後、A列の文字列が指定した文字列へ置換されました。(画面クリックして拡大)Replace関数を利用して、指定した文字列を除いて削除(置換)します。(サンプル2)下記のサンプルプログラムは、A列に東京都の住所一覧が表示されています。この住所一覧に表示されている【東京都】部分のみ一括削除(置換)します。【プログラムの流れ】1A列の文字列として登録されている住所に対して、【東京都】のみ削除(置換)します。'指定した文字列を削除(置換)します。'指定した文字列を削除(置換)します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'A列に登録されている東京都⇒削除します。'指定した文字列を削除(置換)します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'A列に登録されている東京都⇒削除します。※プログラム実行後、A列に登録されている住所内の【東京都】部分のみ削除されました。(画面クリックして拡大)Replace関数を利用して、文字列内の空白文字(全角・半角)を削除(置換)します。(サンプル3)下記のサンプルプログラムは、B列に登録されている【氏名】とC列に登録されている【住所】に空白文字がバラバラに挿入されています。)を削除(取り除く)するサンプルプログラムです。【プログラムの流れ】1B列の文字列として登録されている【氏名】に対して、空白文字のみ削除(置換)します。2C列の文字列として登録されている【住所】に対して、空白文字のみ削除(置換)します。'空白文字(全角・半角)を削除します。'空白文字(全角・半角)を削除します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'B列の空白を順番に削除します。'C列の空白を順番に削除します。'空白文字(全角・半角)を削除します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'B列の空白を順番に削除します。'C列の空白を順番に削除します。※プログラム実行後、B列の「氏名」とC列の「住所」部分に挿入されている空白文字を一括で削除されました。(画面クリックして拡大)Replace関数を利用して、文字列内の改行を一括削除します。(サンプル4)下記のサンプルプログラムは、指定した列の文字列内の改行を一括削除を行うサンプルプログラムです。C列に【メールアドレス】が登録されています。このC列に登録されているメールアドレスは、部分的に改行されています。このC列の改行を全て取り除くサンプルプログラムです。【プログラムの流れ】1C列(メールアドレス)の文字列内の改行を一括削除します。'文字列内の改行を一括削除します。'文字列内の改行を一括削除します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'C列の改行を全て削除します。'文字列内の改行を一括削除します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'C列の改行を全て削除します。※プログラム実行後、指定したC列「メールアドレス」内の改行が全て削除する事が出来ました。(画面クリックして拡大)Replace関数を利用して、置換リストを作成し置換リストに該当するコードを文字列データに置換処理を行います。(サンプル5)下記のサンプルプログラムは、同じシートに参照元データと置換リストを作成して、参照元データ(A列)のコードと置換リストのコード(G列)に該当するコードを文字列に変換を行います。今回のサンプルプログラムでは、参照元データ(A列)には、銀行コード・支店コードが数字で登録されています。この数値を元に、置換リスト(G列)と照合して数値が一致する銀行名・支店名を別セル(B列)に返します。【プログラムの流れ】1A列とG列の最終行を取得します。2A列【銀行コード・支店コード・口座番号】の最終行まで繰り返します。3A列のコード内容をB列に転記します。4B列のコードとG列のコードが一致するコードと文字列をB列に置換します。5コードと文字列を置換した場合は、銀行コードの置換ループから抜ける。6A列の最終行まで繰り返します。(3へ戻る)【プログラム実行条件】1銀行コードと支店コードは、7桁の数字で登録・口座番号も7桁の数字で登録します。A列のデータは、上記の条件で作成してください。G列の銀行コード・支店コードも一致できるようにコードを作成して下さい。'置換リストを作成して、置換リストに該当するコードを文字列データに置換処理を行います。'置換リストを作成して、置換リストに該当するコードを文字列データに置換処理を行います。'A列の最終行を取得します。'G列の最終行を取得します。'置換リストを作成して、置換リストに該当するコードを文字列データに置換処理を行います。'A列の最終行を取得します。'G列の最終行を取得します。※プログラム実行後、A列の銀行コード・支店コードを元に、コードを一致する銀行名・支店名をB列に置き換えました。(画面クリックして拡大)Replace関数を利用して、セルに設定している関数を別の関数へ置換処理を行います。(サンプル6)下記のサンプルプログラムは、EXCELで作成した表で、誤って違う関数を設定してしまった場合に、正しい関数へ置換する(修正)サンプルプログラムです。してG列には、【平均値】・H列には「最大値」・I列には「最小値」として作成するはずでしたが、誤って全てSUM関数(4月~6月までの合計値)を登録されています。これをReplace関数を使い、平均値は、AVERAGE関数・最大値は、MAX関数・最小値は、MIN関数へ置換(関数を訂正)します。【プログラムの流れ】1A列の最終行を取得します。2登録されている4行目のセル位置からA列で取得した最終行まで繰り返します。3G列のSUM関数をAVERAGE関数へ置換・H列のSUM関数をMAX関数へ置換・I列のSUM関数をMIN関数へ置換(誤っているSUM関数を正しい関数へ修正します。)4A列の最終行まで繰り返します。'誤って設定したEXCEL関数を正しい関数へ置換します。'誤って設定したEXCEL関数を正しい関数へ置換します。'A列の最終行を取得します。'A列の最終行まで繰り返します。'誤って設定したEXCEL関数を正しい関数へ置換します。'A列の最終行を取得します。'A列の最終行まで繰り返します。※プログラム実行後、G列~I列までに登録されていたSUM関数からG列は、【平均値】AVERAGE関数・H列は、【最大値】MAX関数・I列は、【最小値】MIN関数に置換され正しい結果が表示されました。(画面クリックして拡大)最後まで、ご覧いただきまして誠に有難うございました。VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。指定した行列に小計を追加する。指定した表を自動的に背景色をゼブラ模様(縞模様)にする。Functionプロシージャを使いこなす。便利なVBAコード・サンプルプログラム一覧・覚えると便利・効率が上がる。エラーかどうがを判定します。