一般的な質問には、Excel 2007 の大きなリストを並べ替えて、小さなサブセット リストに一致するエントリを見つけることが含まれます。
この問題への取り組み方についてはいくつかアイデアがありますが、それらのアイデアを実装するための技術的な知識が不足しています。質問をより明確にするために、具体的なユースケースの要件を概説します。
具体例:
私は、自分の販売地域 (約 1,000 の顧客アカウント) で管理している会社名のマスター リストを持っています。毎週、私の会社は、米国のすべての販売地域 (自分の地域と他の何百もの地域) で取引されたすべてのビジネスのリストを発行しています。この取引ログは 10,000 行以上あるため、目でスキャンして自分のアカウントに関連する取引を見つけることはほぼ不可能です。
私が現在行っている不十分な解決策は、アカウント リストを黄色で強調表示し、その強調表示されたリストをコピーして、その強調表示されたリストを週次トランザクション ログの下部に貼り付け、AZ 順に並べ替えて、強調表示された項目まで手動でスクロールすることです。トランザクション ログに自分のアカウントの 1 つが含まれている場合、トランザクション ログ エントリは、挿入した強調表示されたエントリのすぐ上または下に表示されます。この方法は効果的ですが、非常に時間がかかります。
Excel で重複を削除する方法は知っています。重複以外のすべてを削除する方法はありますか? これがあれば、リストを視覚的にスキャンしやすくなります。
データの不整合により、単純なマクロ、フィルター、または「重複の検索」ボタンの使用が制限されるため、別の問題が残っています。トランザクション ログの名前のスペルは、マスター リストとは少し異なることがよくあります。
例: Acme Widget Company, Inc.、Acme Widget Inc、Acme Widget、 例: 米国ハンドボール協会、US ハンドボール協会、US ハンドボール、USHO
ファジー ロジックを使用して、正確でないエントリを一致させることができるサードパーティ アプリがいくつかあることは知っています。ただし、エンタープライズ マシンでプラグインを実行することはできません。(非常に説得力のあるケースがない限り...)
スペースや句読点を削除してトランザクション ログを「正規化」できるマクロはありますか? 最初の X 文字数を一致させることができるマクロはありますか (文字数が多いほど精度は高くなりますが、ほぼ重複したエントリを見逃す可能性が高くなります...)? 結果の「一致」リストを出力またはフィルター処理できるマクロはありますか?
これらのタスクが複雑すぎる場合は、もっと簡単なアイデアがあります。強調表示されたアカウント リストをトランザクション ログにマージした後、強調表示された項目の上下 5 行以内にある他のすべてのトランザクション ログ行を非表示にできれば便利です。これにより、非標準のスペルに対してある程度の柔軟性が確保されると同時に、リストを視覚的に検査するタスクが大幅に簡素化されます。
これらのアイデア、またはまったく異なるアプローチを実装する方法についてのご意見は、ぜひお聞かせください。この質問に対する一般的な回答は、私が説明した狭いユースケースを超えて、他の人にとって価値があると思います。
ありがとう！
答え1
ここでは答えるべき質問が多すぎることは確かです (hyperslug のコメント通り)。私も非常に似たような状況にあり、エンコードする種類が多すぎるため、重複を見つけるには手動で行う必要があることがわかりました。
提案されたマクロはすべて記述可能です。どれが最も効果的かを決めたら、別の質問としてそのことを尋ねてください。できる限りのことをします。最後のマクロは実装が簡単で、スクロール時間を節約できます。そのマクロを作成し、重複を非表示にした後、「標準」エントリをクリックして他のエントリの上にドラッグするだけです。
答え2
必要なデータを取得するには、コピーや並べ替えを行う代わりに、Excel の MATCH 関数を使用します。
マスターリストが名前付き範囲トランザクション ログの会社名は列 D にあります。トランザクションの行のどこかに次の数式を入力し、 =IF(ISNA(MATCH(D1,Master,0)),0,1)それをトランザクション テーブルのすべての行にコピーします。この数式の結果は、会社名が一致する場合は 1、そうでない場合は 0 になります。
これは正確な名前のみに一致します。可能なすべてのバージョンを取得するには、マスター範囲に代替名を追加する必要があります (名前を追加した後は必ず並べ替えてください)。
答え3
マスター リストに代替スペルを追加するというアプローチには賛成です (メールなどの優先形式と、会社のデータに一致させるための形式を示す 2 番目の列があるかもしれません)。連続した SUBSTITUTE 関数を使用して、名前の代替バージョンを生成すると、ある程度成功するかもしれません。例:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1)," inc",""),".","")," ","")...
したがって、各置換は、選択したテキストのインスタンスを置換で置き換えます。このケースでは何も起こりません。異なるシステムの名前間の同様のあいまい一致の経験から、一致するものを取得するには、inc、corp、plc などを削除する必要があります。これには SUBSTITUTE を使用できますが、「Income Corporation」が「omeorporation」になるなど、奇妙な結果になる可能性があるため、次のようなものを使用する方が安全です。
IF(RIGHT(lower(A1),4)="corp",left(lower(A1),len(A1)-4)),lower(A1))。
スペースの置換は最後に行います。
同様の結果を持つ MATCH または COUNTIF を使用して、リストに一致するトランザクションを示す列を作成することもできます。
代替案としては、マスター リストを基準として高度なフィルターを作成することが挙げられます。これにより、顧客名に一致するトランザクション リスト エントリのコピーを簡単に取得し、このフィルターされたコピーを別の場所 (たとえば、片側または別のシート) に配置することができます。上記と同様に、元の名前からあまりにも離れているバリアントを追加する必要があります。
答え4
ピボット テーブルの使用を試してみたかどうか気になりました。私は PT を使用して大量のデータを処理していますが、PT を使用すると、問題をさまざまな方法で非常に迅速に、完全なデータ整合性で調べることができます。
すべてのデータを強調表示し、ピボット テーブルの挿入を選択します。これで、さまざまなインタラクティブな方法でデータを確認して、厄介な二重入力やスペルミスなどを絞り込むことができます。その後、カスタム並べ替えなどを使用して並べ替えたり、AZ を使用したりすることができます。