☰

需要將缺少的迭代 ID 號碼新增到文字或 Excel 中的系列中。如何在不編碼的情況下完成此任務？

tag-icon • tag-icon microsoft-excel regex
需要將缺少的迭代 ID 號碼新增到文字或 Excel 中的系列中。如何在不編碼的情況下完成此任務？

我有一個文字、csv 或 excel 文件，如下所示

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

真實集合包含 1,000,000 行中的 280,000 個。我需要以某種方式添加所有缺失的 ID 號碼（它們都是連續的 +1 迭代）以及姓名和出生日期的空白字段。這樣我得到的東西看起來像：

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

一直到 32，然後再一次，直到每個整數都存在。是否有捷徑可尋？無需在循環中編寫程式碼區塊？來自 Excel、某些應用程式或任何 Windows 文字編輯器技巧的任何內容都將受到讚賞。

編輯：請忽略逗號、引號等。

答案1

一種方法是：

  1. 將 CSV 匯入 Excel，如表 1 所示。
  2. 在第 2 頁上，使用 a2:=a1+1 等公式建立完整的 ID 號碼列表，然後向下複製。
  3. 在其他 2 列中使用 vlookup 公式，參考第 1 頁上的表格=VLOOKUP(A2,Sheet1!A2:C13,2,false)。=VLOOKUP(A2,Sheet1!A2:C13,3,false)。 b2:=IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")和 c2:（=IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")這些公式現在可以複製到列中。）
  4. 填滿後，將工作表 2 儲存為 CSV。

注意：為了使其正常工作，工作表 1 上的表格需要在第 1 列上進行排序。

對於一百萬行，這實際上在許多計算機上會變得非常慢，在編寫公式之前我錯過了這一點。我透過關閉自動重新計算來對大型資料集執行類似的操作以使所有公式就位，然後進行手動重新計算。這花了幾個小時但確實正確完成了。

答案2

我將使用 Power Query 加載項解決此問題。

我已經建立了一個原型，您可以在我的 One Drive 中查看或下載它的“Power Query 演示 - 將丟失的 ID 號碼添加到系列.xlsx”：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

需要執行幾個步驟才能到達該位置，並使用 Power Query 語言 (M) 進行一些編碼來呼叫 List.Numbers 函數（它未在 Power Query UI 中公開）。但這只是一行簡單的程式碼 - 其餘部分可以透過在 Power Query 中點擊來建立。

基本上，我的技術是使用 List.Numbers 產生 ID 號碼表，然後新增合併以從輸入資料（存在 ID 號碼的位置）取得列。

List.Numbers 的文檔在這裡：

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

向 Matt Masson 的「設定」技術致敬。

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

注意 Power Query 可以直接從 CSV 檔案讀取，我將其用作「輸入資料」查詢的來源。最簡單的方法可能是刪除該查詢，然後從 CSV 檔案建立一個新查詢並將其命名為「輸入資料」。取消選取“載入到工作表”選項以節省資源。

相關內容