Excelで複数のブック・シートの値を一括検索・置換する3つの方法

データセットの修正が必要になる場面は多々あります。しかし、複数のワークシートやワークブックを扱う場合、この作業は非常に手間がかかります。Excelの「検索と置換」機能やVBAコードを正しく使いこなせば、こうした面倒な作業も簡単に行えるようになります。本記事では、「検索と置換」ツールとVBAマクロを使って、複数のシートやファイルにまたがる値を検索・置換する方法をご紹介します。

練習用ワークブックのダウンロード

無料の練習用Excelワークブックはこちらからダウンロードできます。

Excelで複数のワークシート・ワークブックの値を検索・置換する3つの方法

以下のセクションでは、「検索と置換」機能とVBAマクロを使用して、複数のExcelシートやファイル内の特定の値を検索・置換する方法を解説します。

1. 1つのExcelファイル内の複数ワークシートで値を検索・置換する

開いているワークブック内のすべてのワークシートから特定の値を検索し、まとめて任意の値に置き換えることができます。Excelの「検索と置換」ツールを使った具体的な手順を見ていきましょう。

下の画像は、Oneという名前のExcelシートです。

同じワークブックには、Twoという別のシートもあります。

ここでは、Excelの「検索と置換」機能を使って、特定の単語を別の単語に置き換えます。今回は、「Ambrose」という単語を「Moxley」に置き換えてみましょう。

手順:

• ホームタブから検索と選択 → 置換...を選択します。

• すると、「検索と置換」ダイアログボックスが表示されます。オプション>>ボタン(下図参照)をクリックします。

• ダイアログボックスに新しいオプションが表示されます。検索する文字列欄に、検索したい単語(ここでは「Ambrose」)を入力します。
• 次に、置換後の文字列欄に、置き換え後の単語(ここでは「Moxley」)を入力します。
• その後、対象のドロップダウンリストからブックを選択します。これにより、そのワークブック内のすべてのシートから指定した単語が検索され、すべて置換されます。
• 最後にすべて置換をクリックします。

実行された置換内容を知らせる確認メッセージが表示されます。今回のワークブックには2つのワークシートしかないため、2件の置換が行われました。シート数が多い場合は、それだけ多くの置換が実行されます。

それでは、実際に置換が行われたか確認してみましょう。

上記と下記の両方の画像をご覧ください。

両方のシートの「Ambrose」という単語が、「Moxley」に置き換えられました。

関連記事: VBAを使ってExcelで複数の値を検索・置換する方法(3つの例)

2. 複数のワークブックの複数シートで値を検索・置換する

ご存じでしょうか。あるワークシートで「検索と置換」操作を実行した後、「検索と置換」ダイアログボックスは手動で閉じない限り開いたままになります。この特性を活かせば、複数のワークブックの値を効率的に置換できます。

次の画像をご覧ください。2つのExcelワークブックを開いています。1つ目はFind and Replace Value(下図参照)です。

もう1つはTranspose Multiple Columns(下図参照)です。

前のセクションで説明したように、あるワークシートで置換を行った後も、「検索と置換」ダイアログボックスは開いたままになっていることに気づくでしょう。

• あとは、閉じるボタンをクリックせずに、次のワークブックに切り替えてすべて置換を押すだけです。そうすると、そのワークブック内のすべてのワークシートからも該当する値が検索され、指定した値に置き換えられます。

この操作は、必要な数のワークブックに対して繰り返し実行できます。

関連記事: Excelで数式を使って検索・置換する方法(4つの例)

関連記事:

• Excelの数式内のテキストを置換する7つの簡単な方法
• Excelでアスタリスク(*)を検索・置換する方法
• Excel VBAで範囲内のテキストを検索・置換する方法(マクロとユーザーフォーム)
• Excelの列で検索・置換する6つの方法
• Excelで選択範囲内を検索・置換する7つの方法

3. VBAを使って開いているワークブックの値を検索・置換する

前のセクションで説明した操作は、すべてVBAコードだけで実現できます。上級者のExcelユーザーなら、このセクションは必見です。VBAの活用は、Excelであらゆる操作を実行する際に最も効果的で、迅速かつ安全な方法です。ワークブック内のすべてのワークシートから値を検索して置換することができます。VBAマクロでの実装方法を見ていきましょう。

手順:

• まず、キーボードでAlt + F11を押すか、開発 → Visual Basicタブに移動して、Visual Basic Editor(VBE)を開きます。

• 次に、表示されたコードウィンドウで、メニューバーから挿入 → 標準モジュールをクリックします。

• そして、以下のコードをコピーしてコードウィンドウに貼り付けます。

Sub MultipleFindAndReplace()
Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long
OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")
  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i
End Sub

これでコードを実行する準備が整いました。

このコードは、「John, Roman, Dean, Seth, Finn」という配列の値を、「Ben, Alex, Joe, Chris, Josh」という値に置き換えます。

• キーボードのF5キーを押すか、メニューバーから実行 → Sub/ユーザーフォームの実行を選択します。サブメニューバーの小さな実行アイコンをクリックしてマクロを実行することもできます。

コード実行後の結果は、下の画像をご覧ください。

コードで置換対象として指定したすべての値(「John, Roman, Dean, Seth, Finn」)が、「Ben, Alex, Joe, Chris, Josh」に正常に置き換えられました。

このコードは、複数のワークブックの複数のワークシートから複数の値を検索・置換する場合にも使用できます。

• 1つのワークブックでマクロを実行したら、次は別のワークブックに切り替えます。そこで開発 → マクロに移動します。
• マクロダイアログボックスが表示されるので、マクロ名を選択して「実行」をクリックします。

後から開いたワークブックにも同じ結果が適用されているのがわかります。コードに入力した検索・置換の値が、該当する単語を正しく検出し、置き換えてくれます。

VBAコードの解説

Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long

変数を定義しています。

OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")

検索する値をOldValue変数に、置換後の値をNewValue変数に設定します。

  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i

このコード部分は、まず配列リストの各項目をループ処理します。次に、ActiveWorkbook内の各ワークシートをループ処理し、一致するものが見つかった場合にOldValueをNewValueに置き換えます。1つのシートでの反復処理が完了すると、次のシートに移動して再びループを実行します。

関連記事: Excelで複数の値を検索・置換する6つのクイックな方法

まとめ

本記事では、「検索と置換」ツールとVBAマクロを使用して、Excelで複数のシートやファイルにまたがる値を検索・置換するさまざまな方法をご紹介しました。この記事が皆様のお役に立てば幸いです。トピックに関するご質問があれば、お気軽にお問い合わせください。

関連記事

• Excelのデータクリーンアップ手法: セルへのテキスト追加
• Excelで数式の前にテキストを追加する4つの簡単な方法
• ワイルドカードを使ったExcelでの値の検索・置換
• Excel VBAでSubstitute関数を使う方法(3つの例)
• Excelで特殊文字を置換する6つの方法