近來(lái)在網(wǎng)上看到一則查找兩列數(shù)據(jù)差異項(xiàng)的方法，試著做發(fā)現(xiàn)07版的excel根本就沒(méi)有那個(gè)功能，而且設(shè)置過(guò)程也很麻煩，有那個(gè)功夫，不如去網(wǎng)上找函數(shù)公式解決。今天給大家分享一下用函數(shù)組合公式和自定義函數(shù)公式兩種方法實(shí)現(xiàn)兩列數(shù)據(jù)差異查找并列出的效果。

案例

上圖中各部門(mén)收集的客戶名單存在不同，需要找到產(chǎn)品有而運(yùn)營(yíng)沒(méi)有的客戶名單，有人可能會(huì)直接選中兩列數(shù)據(jù)設(shè)置條件格式，突出顯示唯一值，得到下面的情況，在逐個(gè)復(fù)制過(guò)去就行了。

確實(shí)很簡(jiǎn)單，但是模擬的數(shù)據(jù)有限，要是成百上千條數(shù)據(jù)查找，查找出來(lái)后的復(fù)制粘貼工作是很煩的。通常我們會(huì)設(shè)置函數(shù)公式處理這種問(wèn)題。

解決方案

通過(guò)設(shè)置excel函數(shù)數(shù)組公式和vba編輯自定義函數(shù)兩種方法均可一次性找到差異值，并自動(dòng)寫(xiě)入到要求的地方。

兩種方法公式和實(shí)現(xiàn)效果如下圖：

1、常規(guī)組合數(shù)組公式：

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(ISNUMBER(MATCH($B$3:$B$13,$A$3:$A$11,)),4^9,ROW($B$3:$B$13)-2),ROW(A1))),"")

輸入完成后ctrl+shift+enter三鍵同時(shí)按生效。

這個(gè)公式有點(diǎn)復(fù)雜，需要一定的函數(shù)功底才能掌握，當(dāng)要比對(duì)其他列數(shù)據(jù)時(shí)，復(fù)制粘貼公式后，要修改對(duì)應(yīng)的選擇單元格區(qū)域有4處，即要修改4次才能正確顯示結(jié)果，會(huì)用的人不多。對(duì)不想在函數(shù)上費(fèi)精力的人來(lái)說(shuō)可以用下面的自定義函數(shù)。

2、自定義函數(shù)，具體公式為：

=czbt($B$3:$B$13,$A$3:$A$11,ROW(A1))

這個(gè)函數(shù)比較簡(jiǎn)單，函數(shù)名為"查找不同"的拼音首字符組合，比較好記。共三個(gè)參數(shù)，第一個(gè)參數(shù)$B$3:$B$13為目標(biāo)單元格區(qū)域(案例為產(chǎn)品客戶名單)，第二個(gè)參數(shù)$A$3:$A$11為對(duì)比單元格區(qū)域(案例為運(yùn)營(yíng)客戶名單)，第三個(gè)參數(shù)為row(a1)代表查找出來(lái)的第一個(gè)差異項(xiàng)，隨著公式往下填充，一次找出第2、3...個(gè)差異項(xiàng)。當(dāng)對(duì)比其他列數(shù)據(jù)時(shí)，復(fù)制粘貼公式后，只需修改對(duì)應(yīng)的目標(biāo)單元格區(qū)域和對(duì)比單元格區(qū)域各一次即可。函數(shù)參數(shù)設(shè)置簡(jiǎn)單明了，容易掌握。

自定義函數(shù)的用法還是提前插入模塊，打開(kāi)VBA編輯框，文件-導(dǎo)入文件-查找差異項(xiàng).bas。或者在VBA編輯框插入模塊，在代碼區(qū)域粘貼下面的代碼即可。

Function czbt(r1 As Range, r2 As Range, k As Integer) As StringDim arr1, arr2, arr3()arr1 = r1arr2 = r2ReDim arr3(1 To Application.Max(UBound(arr1), UBound(arr2)))n = 0For i = 1 To UBound(arr1) For j = 1 To UBound(arr2) If arr1(i, 1) = arr2(j, 1) Then Exit For End If If j = UBound(arr2) Then n = n + 1 arr3(n) = arr1(i, 1) End If Next NextIf k <= UBound(arr3) Then czbt = arr3(k)Else czbt = ""End If

總結(jié)

以上是生活随笔為你收集整理的找出两列数据的差集_excel快速查找数据差异项的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。