☰

別のテーブルから条件付きで行をコピーしてテーブルを作成する - MS-Excel で可能ですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
別のテーブルから条件付きで行をコピーしてテーブルを作成する - MS-Excel で可能ですか?

毎週、CRM システムによって新しい Excel ファイルが生成されます。このファイルのファイル名には日付スタンプが含まれますが、このファイルは固定の場所 (SharePoint URL) にあるフォルダーに配置されます。

このファイルは非常に大きく、通常は 25,000 ～ 30,000 行が含まれています。このファイルから、目的に合った新しいテーブルを作成する必要があります。

私の目的に関連する行は、30～40 個のキーワード/キーフレーズのリストから 1 個 (または複数) のキーワード/キーフレーズに一致する必要があります。ただし、このキーワード/キーフレーズのリストも、数か月ごとに新しいキーワードを追加することで、ゆっくりと増えていきます。

この手動で面倒で、エラーが発生しやすい作業を自動化する方法はありますか?

答え1

3 つの異なる列 (以下ではB、Dと想定F) をチェックする必要があるため、OR関数が適切に思えます。

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

3 つの列のいずれかに一致すると が返されるようになりますTRUE。これを、データが含まれる最初の行 (たとえば行 1) の「ヘルパー」列に配置するか、それに応じてB1、D1およびF1上記を調整します。

MATCHたとえば、 の値がB1に存在するかどうかを確認しますkeyarray。ここで、 はキーワード/キーフレーズのリスト (約 30 ～ 40 個) を含む範囲の指定名です。同じシートまたはワークブック内にある必要はありませんが、そうでない場合は完全なパスを指定する必要があります。また、数式を新しいデータ バッチに適用するときには、「他の」ワークブックを開いておくことをお勧めします。

0完全一致のみを強制します (または、-1以上の最小値B1、または1最大値に対して強制します)。

MATCH配列内で見つかった値の場所を返します (そうでない場合は、パラメータ0、を使用#N/A)。これは数値なのでISNUMBER、結果を除外するために数値 (任意の数値) をテストします#N/A。

B1したがって、、D1またはのいずれかが にある場合、結果は になりますF1。それ以外の場合は になります。keyarrayTRUEFALSE

結合されたセルを含む 25,000 ～ 30,000 行にわたって数式をコピーするには、最後の占有行と「ヘルパー」列の交点に何か (たとえば「end」) を配置します (スプレッドシートのサイズが不必要に拡大するのを防ぐため)。数式を含むセルをコピーし、そのすぐ下のセルを選択してからCtrl+Shift+Down/Paste を実行すると、占有されているすべての行のヘルパー列が下方向に入力されます。最後の占有行を超えて続行することはなく、「end」は上書きされます。

の「ヘルパー」列をフィルターしTRUE、すべての占有列を選択してコピーし、新しいシート/ワークブックに貼り付けます。新しいシート/ワークブックの空白行を削除して保存します。(ソースから「ヘルパー」列を削除することもできます。)

keyarray名前付き範囲にアイテムを追加する場合は、追加内容がカバーされることを確認します。

答え2

これは、さまざまな方法で解決できる大きなタスクです。しかし、簡単に言えば、はい、これを自動化できます。

まず始めに:

CRM システムが、直接抽出したい情報を提供できないことは確かですか?

通常、これらは SQL などのデータベース システムに基づいており、すでにそこからデータを抽出しているため、この出力をニーズに合わせて変更できる可能性があります。

次に、Excel の可能性について説明します。

  1. CRMから基本データをインポートする

CRMへのデータ接続を確立できる可能性があります

フォルダ内の最新のファイルをインポートしたり、確立されたデータ接続をこの新しいファイルに更新するためのVBAコードを作成できます。

VBA経由で最新のファイルを開き、目的のワークシートに必要なデータをコピーできます。

...

  1. それでも必要な場合は、データのフィルタリング

フィルターと高度なフィルター、vlookup、countifs、および vba コードの例を調べることをお勧めします。ここと stackoverflow には、複数条件フィルタリングの処理に関する質問がかなりありますが、これを適切に解決するには、より具体的な情報を提供する必要があります。

提案として: このタスクを、基本データの取得と、必要な場合にのみフィルタリングに分割します。私の見解では、フィルタリングされたデータはプロセスのかなり早い段階で取得できるはずです。そうでない場合は教えてください :)

関連情報