excel表格中，匹配關鍵字可以找出兩個表格資料的相同值並進行分析與計算。通常有兩種方法：1 公式法:比如在sheet1的A列，需要查詢sheet2的B列在不在A列裡面，那麼在Sheet2的C列就可以寫如下公式：=vlookup(B1,sheet1!A:A,1,0)sheet1的資料：sheet2的資料與公式途中#N/A就是沒有找到，未匹配。方法二：透過vba程式碼來查詢並上色：Sub filter()Dim s1 As VariantDim i, j As IntegerDim foundRange As RangeApplication.ScreenUpdating = Falses1 = Sheet2.Range("B1:B180").ValueFor i = 1 To UBound(s1, 1)Set foundRange = Sheet1.Range("B1:B20357").Find(What:=s1(i, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)If Not foundRange Is Nothing ThenSheet1.Cells(foundRange.Row, 2).EntireRow.Interior.Color = rgbRedElseMsgBox s1(i, 1) & "並未在sheet1中找到", 64End IfNext iApplication.ScreenUpdating = TrueEnd Sub
excel表格中，匹配關鍵字可以找出兩個表格資料的相同值並進行分析與計算。通常有兩種方法：1 公式法:比如在sheet1的A列，需要查詢sheet2的B列在不在A列裡面，那麼在Sheet2的C列就可以寫如下公式：=vlookup(B1,sheet1!A:A,1,0)sheet1的資料：sheet2的資料與公式途中#N/A就是沒有找到，未匹配。方法二：透過vba程式碼來查詢並上色：Sub filter()Dim s1 As VariantDim i, j As IntegerDim foundRange As RangeApplication.ScreenUpdating = Falses1 = Sheet2.Range("B1:B180").ValueFor i = 1 To UBound(s1, 1)Set foundRange = Sheet1.Range("B1:B20357").Find(What:=s1(i, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)If Not foundRange Is Nothing ThenSheet1.Cells(foundRange.Row, 2).EntireRow.Interior.Color = rgbRedElseMsgBox s1(i, 1) & "並未在sheet1中找到", 64End IfNext iApplication.ScreenUpdating = TrueEnd Sub