複数の Excel ファイルの値を検索して置換する方法 (3 つの方法)

何らかの理由でデータセットを変更する必要がある場合があります。また、複数のワークシートやワークブックを扱っている場合、これを行うのは非常に困難です。しかし、Excel の検索と置換の正しい使い方を知っていれば、 ツールと VBA コードを作成すると、この難しい作業が非常に簡単になります。この記事では、複数のシートやファイルで値を検索して置き換える方法を紹介します 検索と置換を使用した Excel の ツールと VBA マクロ .

ワークブックをダウンロード

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

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

次のセクションでは、複数の Excel シートやファイルで特定の値を検索して置き換える方法を学習します。 検索と置換で 機能と VBA を使用 Excel のマクロ。

1. Excel ファイル内の複数のワークシートの値を検索して置換

既存のワークブックのすべてのワークシートから特定の値を見つけて、それらを一度に任意の値に置き換えることができます あなたがしたいこと。 Excel の 検索と置換 でそれを行う方法をお見せしましょう

次の画像では、One という名前の Excel シートがあることがわかります。 .

同じワークブックに、Two という名前の別のシートがあります .

ここでやろうとしていることは、特定の単語を見つけて、検索と置換で別の単語に置き換えることです エクセルの特徴。この場合、「Ambrose」 という単語を置き換えます。 「モクスリー」と .

手順:

• ホームから タブで、検索と選択 -> 置換 を選択します …

• それに続いて、検索と置換 ポップアップボックスが表示されます。 オプション>>をクリックします (下の画像を参照)

• その後、新しいオプションがポップアップ ボックスに表示されます。 探したい単語を書いてください (私たちの場合、それは 「アンブローズ」 です ) 検索対象
• 次に、置き換えたい単語を書きます の前の単語 (単語は “Moxley” です) 私たちの場合) Replace with
• その後、[以内] の横にあるドロップダウン リストから 、ワークブックを選択 .これにより、そのワークブックのすべてのシートで指定された単語が検索され、すべてが置き換えられます。
• 最後に、[すべて置換] をクリックします。 .

確認ボックスが表示されます 発生したすべての交換を記載します。ワークブックにはワークシートが 2 つしかないため、このケースでは 2 つの置換のみが発生しました。より多くのシートがある場合、より多くの置換が行われます。

では実際に入れ替わりがあったのかチェックしてみましょう!

上の画像と次の画像の両方を検討してください。

「アンブローズ」という言葉 両方のシートの 「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. Excel で開いているワークブックの値を検索して置換する VBA を埋め込む

VBA だけで、前のセクションで説明したすべてのことを実行できます。 コード。 Excel の上級ユーザー向けのセクションです。 VBA の実装 は、Excel で操作を実行するための最も効果的、迅速、かつ安全な方法です。 ワークブックのすべてのワークシートから値を検索して置き換えることができます エクセルで。 VBA でそれを行う方法を見てみましょう マクロ。

手順:

• 最初に Alt + F11 を押します キーボードで、またはタブ Developer -> Visual Basic に移動します Visual Basic Editor を開く .

• 次に、ポップアップ コード ウィンドウで、メニュー バーから [挿入] -> [モジュール] をクリックします。 .

• 次に、コピー 次のコードと貼り付け それをコード ウィンドウに表示します。

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」を置き換えます 値 「ベン、アレックス、ジョー、クリス、ジョシュ」 .

• では、 F5 を押してください キーボードまたはメニュー バーから Run -> Run Sub/UserForm を選択します . スモール ラン アイコンをクリックすることもできます。 サブメニュー バーでマクロを実行します。

コードの実行後、下の画像を見て結果を確認してください。

すべての値 (「John、Roman、Dean、Seth、Finn」 ) を値 (“Ben, Alex, Joe, Chris, Josh”) に置き換えるためにコードに入力しました ) が正常に置き換えられました。

このコードを実行して、複数のワークブック内の複数のワークシートから複数の値を見つけて置き換えることもできます .

• 1 つのワークブックでマクロを実行した後、もう 1 つのワークブックにカーソルを合わせる .そこから Developer -> Macros に移動します .
• マクロ ポップアップボックスが表示されます。 マクロ名を選択して [実行] をクリックします .

後で開いたブックにも同じ結果が適用されることがわかります。コードに入力した検索と置換の値は、それらの単語を検索して置換するという仕事を正常に実行します。

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 つの簡単な方法)

結論

結論として、この記事では、複数のシートやファイルで値を検索して置き換える方法について、さまざまな方法を紹介しました。 検索と置換を使用した Excel の ツールと VBA マクロ .この記事があなたにとって非常に有益であることを願っています。このトピックに関する質問はお気軽にどうぞ。

関連記事

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