☰

透過有條件地從另一個表複製行來建立表 - 在 MS-Excel 中可以嗎？

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
透過有條件地從另一個表複製行來建立表 - 在 MS-Excel 中可以嗎？

CRM 系統每週都會產生一個新的 Excel 文件，該文件的文件名稱中包含日期戳，但放置在固定位置（共用點 URL）的資料夾中。

該文件非常大，通常包含 25-30,000 行。由此，我需要建立一個與我的目的相關的新表。

與我的目的相關的行必須與 30-40 個此類關鍵字/關鍵字詞的清單中的一個（或多個）關鍵字/關鍵字詞相符。然而，這個關鍵字/關鍵字詞列表也在增長，但速度很慢，每隔幾個月就會添加一次新關鍵字。

有沒有什麼方法可以自動完成這項手動、乏味且容易出錯的任務？

答案1

由於需要檢查三個不同的列（下面假設為B、D和），因此函數看起來比較合適：FOR

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

這樣將傳回三列中任何一列的符合項TRUE。將其放置在資料佔據的第一行的「輔助」列中（例如第 1 行，或相應地調整B1和D1上方F1）。

MATCH例如，檢查 的值是否B1存在於 中keyarray，其中該值是包含關鍵字/關鍵字詞列表的範圍的給定名稱（數量約為 30-40） – 不需要位於同一工作表或工作簿中但如果不是，則必須指定完整路徑，並建議在將公式套用至新一批資料時開啟「其他」工作簿。

0僅強制精確匹配（或-1對於大於或等於 的最小值B1，或1對於最大值。）

MATCH傳回數組中找到的值的位置（否則，使用參數0, #N/A）。這是數字，因此ISNUMBER測試一個數字（任何數字）——以排除#N/A結果。

因此，如果結果中存在B1或D1，則結果將為- 否則。F1keyarrayTRUEFALSE

要方便地將公式複製到25-30,000 行（其中一些包含合併單元格），請在最後佔用的行和“輔助”列的交集處放置一些內容（例如“結束”）（以防止不必要地擴大電子表格的大小） ）。複製包含公式的儲存格，選擇緊鄰其下方的儲存格，然後Ctrl+Shift+Down/Paste 向下填入所有佔用行的輔助列，而不繼續越過最後佔用的行並覆蓋「end」。

過濾「幫助程序」列TRUE，選擇所有佔用的列，複製並貼上到新工作表/工作簿中。刪除新工作表/工作簿中的空白行並儲存。 （也可以選擇從來源中刪除「helper」欄位。）

確保偶爾將項目新增至keyarray命名範圍時會覆蓋添加內容。

答案2

這是一項更大的任務，可以透過多種方式解決。但簡而言之，是的，您可以將其自動化。

只是為了讓你開始：

您絕對確定您的 CRM 系統無法提供您想要直接提取的資訊嗎？

通常它們基於資料庫系統，例如 SQL，並且因為您已經從中提取數據，所以您可能能夠修改此輸出以滿足您的需求。

現在談談 Excel 的可能性：

  1. 從 CRM 匯入基礎數據

您也許能夠與 CRM 建立資料連接

您可以建立 VBA 程式碼來匯入資料夾中的最新檔案或更新與此新檔案的已建立資料連接

您可以透過 VBA 開啟最新檔案並將所需資料複製到目標工作表中

…

  1. 如果仍然需要，過濾數據

我建議您研究過濾器和高級過濾器、vlookup、countifs 和 vba 程式碼範例。這裡和 stackoverflow 處理多條件過濾器中有很多問題，但您必須提供更多細節才能正確解決這個問題。

作為建議：我會將此任務拆分為獲取基礎數據，並且僅在仍然需要時對其進行過濾。在我看來，應該可以在過程中更準確地獲得過濾後的數據。或告訴我其他情況:)

相關內容