Excel：複数の値を一度に見つけて置き換えます

このチュートリアルでは、複数の単語、文字列、または個々の文字を見つけて交換するいくつかの方法を調べて、ニーズに最適な単語を選択できます。

通常、人々はどのようにしてExcelで検索しますか？ほとんどの場合、単一の値で正常に機能するFind＆Perpectの機能を使用します。しかし、交換する数十個または数百のアイテムさえある場合はどうでしょうか？確かに、誰もこれらすべての代替品を手動で1つずつ作り、データが変更されたときにもう一度やり直したいとは思わないでしょう。幸いなことに、Excelで質量交換を行うためのいくつかの効果的な方法があり、それらのそれぞれを詳細に調査するつもりです。

• 複数の値を置き換えるためのネストされた代替式
• xlookup式を検索します。複数の単語を検索します
• 再帰的なラムダ関数を使用したマルチレプレース
• Mass Find and udfに置き換えます
• 複数の値を見つけて置き換えるためのバルク複製マクロ
• サブストリングツールを使用して複数の検索と交換

ネストされた代替物に複数の値を見つけて置き換えます

Excelで複数のエントリを見つけて交換する最も簡単な方法は、代替機能を使用することです。

フォーミュラのロジックは非常に単純です。古い値を新しい値に置き換えるために、いくつかの個別の関数を書きます。そして、あなたはそれらの機能を互いにネストし、その後の各代替物が以前の代替物の出力を使用して次の値を探すようにします。

代替（代替（Text（ Text 、 Old_Text1 、 New_Text1 ）、 Old_Text2 、 New_Text2 ）、 Old_Text3 、 New_Text3 ））

A2：A10の場所のリストでは、短縮された国名（ FR 、英国、米国など）をフルネームに置き換えると仮定します。

それを実行するには、D2：D4の古い値とE2：E4の新しい値を入力してください。以下のスクリーンショットに示すように。次に、以下の式をB2に入れてEnterを押します。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

…そして、あなたはすべての交換を一度に行うことになります：

上記のアプローチは、動的配列をサポートするExcel 365でのみ機能します。

Excel 2019、Excel 2016以前のDynamic Pre-Dynamicバージョンでは、最上部のセル（B2）に式を書く必要があります。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

この場合、交換値を絶対セル参照でロックするように注意してください。そうすれば、式をコピーしてもシフトしないことに注意してください。

注記。代替機能はケースに敏感です。つまり、元のデータに表示されるのと同じ文字ケースに古い値（ old_text ）を入力する必要があります。

おそらく簡単に簡単には、この方法には重要な欠点があります。交換するための数十個のアイテムがある場合、ネストされた機能の管理が非常に困難になります。

利点：実装しやすい;すべてのExcelバージョンでサポートされています

欠点：限られた数の検索/交換値に使用するのが最適です

複数のエントリをXlookupで検索して交換します

セルのコンテンツ全体をその部分ではなく置き換えようとしている状況では、Xlookup関数は役立ちます。

列Aの国のリストがあり、すべての略語を対応するフルネームに置き換えることを目指しているとしましょう。前の例のように、別の列（それぞれdとe）に「検索」と「置き換える」アイテムを入力することから始めてから、B2にこの式を入力します。

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Excel言語から人間の言語に翻訳されています。ここにフォーミュラが行うことがあります。

d2：d4（lookup_array）でA2値（lookup_value）を検索し、e2：e4（return_array）から一致を返します。見つからない場合は、A2から元の値を引いてください。

塗りつぶしハンドルをダブルクリックして、フォーミュラを下のセルにコピーすると、結果が待ち続けません。

Xlookup関数はExcel 365でのみ利用可能であるため、上記の式は以前のバージョンでは機能しません。ただし、この動作をIferrorまたはIfnaとvlookupの組み合わせで簡単に模倣できます。

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

注記。代替品とは異なり、Xlookupおよびvlookup関数はケースに敏感ではありません。つまり、文字ケースを無視するルックアップ値を検索します。たとえば、私たちの式はFRとFRの両方をフランスに置き換えます。

利点：通常の機能の異常な使用。すべてのExcelバージョンで機能します

欠点：細胞レベルで動作し、細胞の内容の一部を置き換えることはできません

再帰的なラムダ関数を使用した複数の交換

Microsoft 365の加入者の場合、Excelは、従来のフォーミュラ言語を使用してカスタム関数を作成できる特別な機能を提供します。うん、私はラムダについて話している。この方法の美しさは、非常に長く複雑な式を非常にコンパクトでシンプルな式に変換できることです。さらに、Excelには存在しない独自の機能を作成できます。これは、VBAでのみ可能なものでした。

カスタムLambda関数の作成と使用に関する詳細情報については、このチュートリアル：ExcelでLambda関数の書き方をご覧ください。ここでは、いくつかの実用的な例について説明します。

利点：結果は、交換用のペアの数に関係なく、エレガントで驚くほど簡単な機能です

欠点：Excel 365でのみ利用可能。ワークブック固有であり、さまざまなワークブック間で再利用することはできません

例1。複数の単語 /文字列を一度に検索して交換します

複数の単語またはテキストを一度に置き換えるために、これらのフォームのいずれかをとることができるマルチリープレースという名前のカスタムラムダ関数を作成しました。

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

または

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

どちらも自分自身を呼ぶ再帰機能です。違いは、出口ポイントがどのように確立されるかにのみです。

最初の式では、IF関数は古いリストが空白でないかどうかをチェックします（古い ""）。 trueの場合、マルチリープレース関数が呼び出されます。 falseの場合、関数は現在のフォームと出口をテキストに返します。

2番目の式では、逆ロジックを使用します。古いものが空白の場合（古い= ""）、テキストを返して終了します。それ以外の場合は、 MultirePlaceに電話してください。

最も難しい部分が達成されます！あなたがするために残されているのは、以下のスクリーンショットに示すように、名前マネージャーのマルチリープレース関数に名前を付けることです。詳細なガイドラインについては、Lambda関数の名前を付ける方法をご覧ください。

関数が名前を取得したら、他の組み込み関数と同じように使用できます。

エンドユーザーの観点から、選択した2つの式のバリエーションのいずれかが、これと同じくらい簡単です。

MultiReplace（テキスト、古い、新しい）

どこ：

• テキスト- ソースデータ
• 古い- 見つける値
• 新しい- 置き換える値

前の例をもう少し進めると、国の略語だけでなく、国家の略語も置き換えましょう。このために、D2で始まる列Dの略語（古い値）とE2で始まる列Eのフルネーム（新しい値）を入力します。

B2では、マルチリープレース関数を入力してください。

=MultiReplace(A2:A10, D2, E2)

Enterを押して結果を楽しんでください:)

この式の仕組み

フォーミュラを理解する手がかりは、再帰を理解することです。これは複雑に聞こえるかもしれませんが、原則は非常に簡単です。反復ごとに、再帰関数は大きな問題の小さなインスタンスを1つ解決します。私たちの場合、マルチリープレース関数は古い値と新しい値を介してループし、各ループで1つの交換を実行します。

MultiReplace (SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

ネストされた代替機能と同様に、以前の代替の結果は、次の代替品のテキストパラメーターになります。言い換えれば、それ以降のマルチリープレースのコールで、代替機能は元のテキスト文字列ではなく、前の呼び出しの出力を処理します。

古いリストのすべてのアイテムを処理するために、最上部のセルから始め、オフセット関数を使用して、各インタラクションで1列を下に移動します。

OFFSET(old, 1, 0)

新しいリストについても同じことが行われます。

OFFSET(new, 1, 0)

重要なことは、再帰的な呼び出しが永遠に進むのを防ぐために出口のポイントを提供することです。 IF関数の助けを借りて行われます -古いセルが空の場合、関数は現在のフォームと出口をテキスト返します。

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

または

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

例2。Excelの複数の文字を交換します

原則として、前の例で説明されているマルチリプレース関数は、個々の文字も処理できます。これは、上記のスクリーンショットの略語やフルネームとまったく同じ古いキャラクターと新しい文字が別のセルに入力されていることを条件としています。

1つのセルに古い文字と別のセルに新しい文字を入力するか、式に直接入力する場合は、これらの式のいずれかを使用して、別のカスタム関数という名前の別のカスタム関数を作成できます。

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

または

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

いつものように、新しいLambda関数に名前を付けてください：

交換する関数 "title =" name a custom applerteChars関数 ">

そして、あなたの新しいカスタム関数は使用する準備ができています：

交換（Text、Old_Chars、New_Chars）

どこ：

• テキスト- 元の文字列
• 古い- 検索するキャラクター
• 新しい- 置き換えるキャラクター

フィールドテストを提供するために、インポートされたデータでしばしば実行されることを実行しましょう。スマート引用とスマートアポストロフィをまっすぐな引用とストレートアポストロフィで置き換えてください。

まず、D2でスマート引用とスマートアポストロフィを入力し、E2でストレート引用とストレートアポストロフィを入力し、キャラクターをスペースで分離して読みやすくします。 （両方のセルで同じデリミッターを使用しているため、結果に影響はありません。Excelはスペースをスペースに置き換えるだけです。）

その後、この式をB2に入力します。

=ReplaceChars(A2:A4, D2, E2)

そして、私たちが探していた結果を正確に取得します：

また、式に文字を直接入力することもできます。私たちの場合、このようなまっすぐな引用を「複製」することを忘れないでください。

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

この式の仕組み

交換用の機能は、 old_charsとnew_charsの文字列を循環し、左側の最初の文字から一度に1つの交換を行います。この部分は、代替機能によって行われます。

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

各反復で、右の関数は、 old_charsとnew_chars文字列の両方の左から1つの文字を取り除いているため、左は次の文字のペアを配置するために取得できます。

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

各再帰呼び出しの前に、IF関数はOld_Chars文字列を評価します。空でない場合、関数はそれ自体を呼び出します。最後の文字が交換されるとすぐに、反復プロセスが終了すると、式はテキストを返し、現在のフォームと出口を返します。

注記。コアフォーミュラで使用される代替機能は大文字と小文字が区別されるため、ラムダ（マルチリープレースと交換用の両方）の両方が大文字と小文字の文字を異なる文字として扱います。

Mass Find and udfに置き換えます

ExcelでLambda関数が利用できない場合は、VBAを使用して従来の方法でユーザー定義の関数を書くことができます。

MassReplaceによると、UDFをLambda定義のマルチリープレース関数と区別するために、違った名前を付けます。関数のコードは次のとおりです。

function massReplace（inputrng as range、findrng as sandrng as rangeerng as range） as variant （） dim arres （） 'array（）' array （ ） ' array ' array 'array dim arsearchReplace（）、 string ' array 'asray' asray 'array' asray 'asray' asray 'asray' asried find/findペア、一時的な文字列dimind iinputcurrow、iinputcurcol、cntinputrows、cntinputcolsソース範囲の現在の行の長いインデックス、ソース範囲の現在の列のインデックス、列のカウント、cntinputrowsのカウント= inputrng.rows.count cntinputcols = inputrng.columns.contfutcols . arres（1からcntinputrows、1からcntinputcols） redim arsearchReplace （1からcntfindrows 、1から2 ） arsearchReplace（ifindcurrow、2）= antalenterng.cells（ifindcurrow、1）.value next 'iinputcurrow = 1のソース範囲での検索と交換iinputcurcol = 1からcntinputcols stmp = inputrng.cells（iinputcurrow、iinputrng）.celpurw、iinputrng （各セルのペアを検索/交換ifindcurrow = 1からcntfindrows stmp =置き換え（stmp、arsearchreplace（ifindcurow、1 ） 、 arsearchReplace（ifindcurow、2））次

Lambda定義の機能と同様に、UDFはワークブック全体です。つまり、 MassReplace関数は、コードを挿入したワークブックでのみ機能します。これを正しく行う方法がわからない場合は、ExcelにVBAコードを挿入する方法で説明されている手順に従ってください。

コードがワークブックに追加されると、機能は式Intellisenseに表示されます - 関数の名前のみが引数ではありません！しかし、構文を覚えておくのは大したことではないと思います。

MassReplace（input_range、find_range、lated_range）

どこ：

• input_range-値を置き換えるソース範囲。
• find_range-検索するキャラクター、文字列、または単語。
• 置き換え- 置換する文字、文字列、または単語。

Excel 365では、動的配列のサポートにより、これは通常の式として機能します。これは、上部セル（B2）に入力する必要があります。

=MassReplace(A2:A10, D2:D4, E2:E4)

ダイナミック以前のExcelでは、これは古いスタイルのCSEアレイ式として機能します。ソース範囲全体（B2：B10）を選択し、式を入力し、 CTRLシフトEnterキーを同時に押して、それを完了します。

利点：Excel 2019、Excel 2016および以前のバージョンのカスタムラムダ関数のまともな代替

欠点：ワークブックはマクロ対応の.xlsmファイルとして保存する必要があります

VBAマクロにExcelに置き換えます

マクロで一般的なタスクを自動化するのが好きなら、次のVBAコードを使用して、範囲内の複数の値を見つけて置き換えることができます。

sub bulkreplace（） dim rng as range、sourcerng as saurcerng as resperng as range as error resume next sourcerng = application.inputbox（ " source data：" 、 " bulk facty "、application.address、 type ： = 8） err.clear sourcerngが何もありませんantalenterngはapplication.screenupdating = false = false = false infallerng.columns （1）.cells sourcerng.replace what：= rng.value、fallingment：= rng.offset（0、1）.value next application.screenupdating = true end if end end sub sub sub sub

マクロをすぐに利用するには、コードを含むサンプルワークブックをダウンロードできます。または、自分のワークブックにコードを挿入することもできます。

マクロの使用方法

マクロを実行する前に、以下の画像に示すように、古い値と新しい値を2つの隣接する列に入力します（C2：D4）。

次に、ソースデータを選択し、 Alt F8を押し、 BulkReplace Macroを選択し、 [実行]をクリックします。

ソースレイジが事前に選択されているため、参照を確認し、[OK]をクリックします。

その後、交換範囲を選択し、[OK]をクリックします。

終わり！

利点：一度セットアップ、いつでも再利用

欠点：マクロは、すべてのデータ変更で実行する必要があります

Excelの複数の検索と交換は、サブストリングツールに置き換えます

最初の例では、ネストされた代替品がExcelの複数の値を置き換える最も簡単な方法であると述べました。私は間違っていたことを認めます。私たちの究極のスイートは物事をさらに簡単にします！

ワークシートで質量を置き換えるには、 [EbyBitsデータ]タブに移動し、 [サブストリング]ツール> [サブストリングの交換]をクリックします。

[サブストリングの交換]ダイアログボックスが表示され、ソース範囲とサブストリングの範囲を定義するように依頼します。

選択した2つの範囲で、 [交換]ボタンをクリックして、元のデータの右側に挿入された新しい列の結果を見つけます。うん、それはとても簡単です！

ヒント。 [交換]をクリックする前に、考慮すべき重要なことが1つあります。ケースに敏感なボックスです。大文字と小文字を異なる文字として処理する場合は、必ず選択してください。この例では、大文字の文字列を交換し、「FR」、「UK」、または「AK」などのサブストリングをそのままにしておきたいので、このオプションにチェックマークを付けます。

他のバルク操作を文字列で実行できることを知りたい場合は、究極のスイートに含まれる他のサブストリングツールをチェックしてください。またはさらに良いことに、以下の評価バージョンをダウンロードして、試してみてください！

それは、Excelで複数の単語とキャラクターを一度に見つけて置き換える方法です。お読みいただきありがとうございます。来週のブログでお会いしましょう！

利用可能なダウンロード

Excel（.xlsmファイル）究極のスイート14日間の完全官能バージョン（.exeファイル）の複数の検索と交換

以上がExcel：複数の値を一度に見つけて置き換えますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。