たとえば、住所や会社名など、さまざまな程度の「率直さ」で初期データが書き込まれているリストがあるとします。
 |  |
同じ都市または会社が雑多なバリエーションでここに存在することがはっきりとわかります。これは、将来これらのテーブルを操作するときに明らかに多くの問題を引き起こします。 そして、少し考えてみれば、似たような仕事の例は他の地域にもたくさんあるのです。
このような曲がったデータが定期的に届くと想像してください。つまり、これは「手動で修正して忘れる」という XNUMX 回限りの話ではなく、多数のセルで定期的に発生する問題です。
何をすべきか？ 「検索と置換」ボックスを使用するか、または Ctrlキー+H?
このような状況で最初に頭に浮かぶのは、次のように、誤ったオプションと正しいオプションを一致させる事前にコンパイルされた参考書に従って一括置換を行うことです。
残念なことに、このようなタスクが明らかに蔓延しているため、Microsoft Excel にはそれを解決するための簡単な組み込みメソッドがありません。 まず、VBA または Power Query のマクロの形式で「重砲」を使用せずに、式を使用してこれを行う方法を考えてみましょう。
ケース 1. 一括フル交換
比較的単純なケースから始めましょう。つまり、古い曲がったテキストを新しいものに置き換える必要がある状況です。 完全に.
XNUMX つのテーブルがあるとします。
XNUMXつ目は、オリジナルの多彩な企業名です。 第二に – 対応の参考書。 最初の表の会社名に列の単語が見つかった場合 見つけるには、次に、この曲がった名前を正しい名前に完全に置き換える必要があります – 列から 代替 XNUMX 番目のルックアップ テーブル。
便宜上：
- 両方のテーブルは、キーボード ショートカットを使用して動的 (「スマート」) に変換されます Ctrlキー+T またはチーム 挿入 – テーブル (挿入 — 表).
- 表示されるタブで コンストラクタ （設計） 名前付きの最初のテーブル 且つ、および XNUMX 番目の参照テーブル – 置換.
式の論理を説明するために、少し離れてみましょう。
例としてセル A2 の最初の会社を取り上げ、残りの会社を一時的に忘れて、列からどのオプションを決定してみましょう。 見つけるには そこで出会う。 これを行うには、シートの空いている部分で空のセルを選択し、そこに関数を入力します 見つけるには （探す）:
この関数は、指定された部分文字列が含まれているかどうかを判断します (最初の引数は列のすべての値です) 見つけるには) をソース テキスト (データ テーブルの最初の会社) に挿入し、テキストが見つかった文字の序数を出力するか、部分文字列が見つからない場合はエラーを出力する必要があります。
ここでのトリックは、最初の引数として 3 つではなく複数の値を指定したため、この関数も結果として 365 つの値ではなく XNUMX つの要素の配列を返すことです。 動的配列をサポートする最新バージョンの Office XNUMX を持っていない場合は、この式を入力して 入力します この配列がシートに表示されます。
以前のバージョンの Excel を使用している場合は、 入力します 結果配列の最初の値、つまりエラー #VALUE! のみが表示されます。 （＃価値！）.
恐れる必要はありません 🙂 実際、数式バーで入力した関数を選択してキーを押すと、数式が機能し、結果の配列全体を表示できます。 F9（押すのを忘れないでください。 Escキー式に戻ります):
結果の結果の配列は、元の曲がった会社名で (GKモロスコOAO) 列内のすべての値の 見つけるには 二番目だけ見つけた (モロズコ)、 4 文字目から連続して表示されます。
それでは、式に関数を追加しましょう VIEW（調べる）:
この関数には XNUMX つの引数があります。
- 希望値 – 十分に大きな数値を使用できます (主なことは、ソース データ内のテキストの長さを超えることです)。
- 表示されたベクトル – 目的の値を探している範囲または配列。 以前紹介した機能はこちら 見つけるには、配列 {#VALUE!:4:#VALUE!} を返します
- ベクター_結果 – 対応するセルに目的の値が見つかった場合に値を返す範囲。 列の正しい名前は次のとおりです 代替 私たちの参照テーブル。
ここでの主な非自明な機能は、関数が VIEW 完全に一致するものがない場合は、常に最も近い最小の (前の) 値を探します. したがって、任意の大きな数字 (たとえば 9999) を目的の値として指定することで、強制的に VIEW 配列 {#VALUE!:4:#VALUE!} で最も近い最小の数字 (4) を持つセルを見つけ、結果ベクトルから対応する値を返します。つまり、列から正しい会社名を返します。 代替.
XNUMX 番目のニュアンスは、技術的には、数式が配列数式であることです。 見つけるには 結果として XNUMX つではなく、XNUMX つの値の配列を返します。 しかし、機能以来 VIEW はすぐに使用できる配列をサポートしているため、キーボード ショートカットを使用して、この数式を従来の配列数式として入力する必要はありません。 Ctrlキー+シフト+入力します. シンプルなもので十分 入力します.
それで全部です。 ロジックを理解していただければ幸いです。
完成した数式を列の最初のセル B2 に転送する必要があります。 固定の – そして私たちの仕事は解決されました!
もちろん、通常の (スマートではない) テーブルでも、この式はうまく機能します (キーを忘れないでください)。 F4 関連するリンクを修正します):
ケース 2. 一括部分交換
このケースは少しトリッキーです。 ここでも、XNUMX つの「スマート」テーブルがあります。
修正する必要がある、曲がって書かれたアドレスを持つ最初のテーブル (私はそれを呼び出しました Data2）。 XNUMX 番目の表は参考書で、住所内の部分文字列を部分的に置換する必要があります (私はこの表を呼び出しました)。 置換2).
ここでの根本的な違いは、元のデータの断片のみを置き換える必要があることです。たとえば、最初のアドレスの 「セント。 サンクトペテルブルク」 右に 「セント。 サンクトペテルブルク」残りの住所 (郵便番号、通り、家) はそのままにします。
完成した数式は次のようになります (認識しやすいように、 他の+入力します):
ここでの主な作業は、標準の Excel テキスト関数によって行われます。 代替 （代わりの）、これには 3 つの引数があります。
- ソース テキスト – Address 列の最初の曲がった住所
- 探しているもの – ここでは、関数でトリックを使用します VIEW （調べる）列から値を取得する前の方法から 見つけるには、曲がったアドレスにフラグメントとして含まれています。
- 何を置き換えるか - 同様に、列からそれに対応する正しい値を見つけます 代替.
この式を次のように入力します。 Ctrlキー+シフト+入力します 実際には配列数式ですが、ここでも必要ありません。
そして、このような数式には、その優雅さにもかかわらず、いくつかの欠点があることがはっきりとわかります (前の図の #N/A エラーを参照)。
- 演算 SUBSTITUTE は大文字と小文字を区別しますであるため、最後から XNUMX 番目の行の「Spb」は置換テーブルに見つかりませんでした。 この問題を解決するには、次のいずれかの関数を使用できます ザメニット （交換）、または事前に両方のテーブルを同じレジスターに持ってきます。
- テキストが最初に正しいか、またはその中にある場合 置き換えるフラグメントがありません (最後の行)、式はエラーをスローします。 この瞬間は、関数を使用してエラーを傍受して置き換えることで中和できます IFエラー （IFERROR）:
- 元のテキストに含まれている場合 一度にディレクトリからいくつかのフラグメントの場合、式は最後の式のみを置き換えます (8 行目で、Ligovsky «大通り« に変わった 「pr-t」、 だが 「S-Pb」 on 「セント。 サンクトペテルブルク」 もはや、なぜなら 「S-Pb」」はディレクトリの上位にあります）。 この問題は、独自の数式を再実行することで解決できますが、既に列に沿って 固定の:
完璧ではなく、場所によっては面倒ですが、同じ手動の交換よりもはるかに優れていますよね? 🙂
PS
次の記事では、マクロと Power Query を使用して、このような一括置換を実装する方法を理解します。
- テキストを置換する SUBSTITUTE 関数のしくみ
- EXACT 関数を使用して正確に一致するテキストを検索する
- 大文字と小文字を区別する検索と置換 (大文字と小文字を区別する VLOOKUP)