解決excel兩表之間資料關聯關係，知道這幾招就夠了

NewJune發表於2022-01-13
Excel

　　用過SAP的憑證批量錄入模板（Excel檔案）的都知道，一個憑證由【抬頭】和多個【行專案】組成，這是一個關於excel兩表資訊關聯的典型場景。

　　這裡頭蘊藏著一個麻煩：當我們需要一次性錄入多個憑證時，如何將每個憑證的抬頭與行專案關聯起來呢？

假定這是一個SAP憑證的excel錄入模板,包含多個待錄入的憑證：

【抬頭】表：

號碼 憑證日期 憑證型別 公司程式碼 記賬日期 貨幣 匯率 參考憑證號 抬頭文字
1 20220113 RE CN10 20200113 CNY     這是一個僅用於測試的憑證1
2 20220113 RE CN10 20200113 CNY     這是一個僅用於測試的憑證2

 

 

 

 

【行專案】表：

號碼 記賬碼 科目賬戶 特別總賬標識 憑證貨幣金額 稅碼 成本中心 訂單 文字 基準日 分配 原因程式碼 貿易伙伴
1 40 1001010000   123.25       這是一個用於測試的憑證1   20210106 A11  
1 19 1066322 A 123.25       這是一個用於測試的憑證1   20210106    
2 40 1001010000   427.86       這是一個用於測試的憑證2   20210108 A11  
2 19 1066323 A 427.86       這是一個用於測試的憑證2   20210108    
                         

 

 

 

 

 

 

 

 　　看了表結構，如果實際業務中【抬頭】文字跟【行專案】文字要求有一定差異，那麼能將兩張表關聯起來的就剩【號碼】欄位了。此處【抬頭】號碼1跟【行專案】號碼1說的都是第1個待錄入憑證。

當我們第一層迴圈是遍歷【抬頭】表的每一行時，第二層內迴圈便是遍歷該憑證行專案對應的每一行。進一步分解需求，比如號碼1，我們需要動態計算號碼1在【行專案】的起始和終止行號。小爬能想到的思路有以下三個。

　　①利用excel公式來動態計算；

　　②利用兩個字典分別儲存某個號碼在【行專案】中的首尾行號；

　　③建立個臨時表temp，利用sql或Excel自帶篩選功能快速篩選出號碼等於特定數字（比如1）的資料，寫入臨時表。

 

先說方法一：使用match結合countif分別得到首尾行號。

如下圖所示：

 

 

接著說說方法二：使用字典來得到某個憑證行專案的首尾行號

　　我們從上至下遍歷【行專案】每一行，用字典（key,value)分別儲存號碼和對應的行號，由於字典【key】的唯一性和可覆蓋性，我們永遠key對應【號碼】，則value永遠對應的該key最後一次出現的位置（行號），因為該key第一次存入字典的value（起始行號），隨著逐行往下遍歷，被不停用新的value（行號）覆蓋。

此時我們只需要掌握一個trick，先定義個字典，從上至下遍歷，最終儲存key（號碼）和value（結束行號）；緊接著再定義個字典，從下至上遍歷，便可儲存key（號碼）和value（起始行號），你get到這一點了嗎？

　　完整實現的程式碼示例（VBA）如下：

 1 Sub voucherEntry()
 2 Dim headerSht As Worksheet, itemSht As Worksheet, numberStr As String, startNum As Integer, endNum As Integer, headerMaxRow As Long, itemMaxRow As Long, i As Integer, j As Integer
 3 Dim startDic As Object, endDic As Object
 4 Set startDic = CreateObject("scripting.dictionary")
 5 Set endDic = CreateObject("scripting.dictionary")
 6 
 7 Set headerSht = ThisWorkbook.Sheets("抬頭")
 8 Set itemSht = ThisWorkbook.Sheets("行專案")
 9 headerMaxRow = headerSht.Cells(Rows.Count, 1).End(xlUp) '[抬頭]表的最後一行行號
10 itemMaxRow = itemSht.Cells(Rows.Count, 1).End(xlUp) '[行專案]表的最後一行行號
11 For i = 2 To itemMaxRow
12     endDic.Add CStr(itemSht.Range("A" & i).Value), i '將號碼與對應行號的關係存入字典，思考下，為啥要轉為字串格式
13 Next
14 
15 For i = itemMaxRow To 2 Step -1
16     startDic.Add CStr(itemSht.Range("A" & i).Value), i '將號碼與對應行號的關係存入字典，思考下，為啥要轉為字串格式
17 Next
18 
19 For i = 2 To headerMaxRow '遍歷【抬頭】表每一個抬頭行
20     numberStr = CStr(headerSht.Range("A" & i).Value) '【號碼】，思考下，為啥要轉為字串格式
21     startNum = startDic(numberStr) '憑證對應的起始行號
22     endNum = endDic(numberStr) '憑證對應的結束行號
23     For j = startNum To endNum '遍歷該憑證行專案的每一行
24         此處維護核心【程式碼塊】
25     Next
26 
27 Next
28 
29 End Sub

至於方法三，小爬就暫時不對此進行具體實現了，感興趣的同學不妨自己動手嘗試一波~

 

快來掃碼關注我的公眾號 獲取更多爬蟲、資料分析的知識！

相關文章