Excel 中的篩選想必大家都經(jīng)常使用吧,它可以快速的在某一列當(dāng)中篩選出符合條件的記錄。
而其中的高級(jí)篩選,可能大家用的并不是很多吧!
但是,如果能用好它,可以大大提高我們的工作效率。
今天,我跟大家分享的是高級(jí)篩選的另類用法。
? 提取出兩個(gè)表格的共同項(xiàng)。
? 提取出兩個(gè)表格的不同項(xiàng)。
下面就跟我一起來看看吧!
提取出兩表的共同項(xiàng)
如下圖,在表 1 中是我們平時(shí)記錄的一張發(fā)票明細(xì)表:
表 2,是從網(wǎng)上導(dǎo)出來的表格:
現(xiàn)在想提取兩個(gè)表格中都有的數(shù)據(jù),以便我們作為本月的核賬和記賬的數(shù)據(jù)。
?? 具體操作步驟如下:
? 先選擇【表 3】工作表中的【A1】中的單元格,用于存放篩選出來的結(jié)果數(shù)據(jù)。
? 在【數(shù)據(jù)】選項(xiàng)卡中點(diǎn)擊【高級(jí)】篩選按鈕,調(diào)出【高級(jí)篩選】對(duì)話框,
? 點(diǎn)擊【列表區(qū)域】選擇【表 1】工作表中的數(shù)據(jù)區(qū)域,再選擇【條件區(qū)域】選擇【表 2】工作表中的數(shù)據(jù)區(qū)域。
? 點(diǎn)擊【將篩選結(jié)果復(fù)制到其他位置】,此處選擇【表 3】中的【A1】單元格,最后點(diǎn)擊【確定】。
結(jié)果如下圖:
最終,我們將兩個(gè)表中共有的數(shù)據(jù)都篩選出來了,一共篩選出來 6 條相同的數(shù)據(jù)。
PS :在使用高級(jí)篩選的時(shí)候,各表中的標(biāo)題名稱需要一樣!
如果將篩選結(jié)果放在一張新表中(比如,我們這里將篩選的結(jié)果放在【表 3】中),需要先將活動(dòng)單元格定位在【表 3】中,然后調(diào)出【高級(jí)篩選】對(duì)話框,再進(jìn)行后續(xù)操作!
另外,這里給大家稍微講下函數(shù)做法,做下補(bǔ)充。
我們也可以用 COUNTIFS 計(jì)數(shù)函數(shù)來提取兩表的相同項(xiàng)。
如下圖:
在【表 1】中的【F3】單元格輸入如下公式:
=COUNTIFS表 2!A:A,'表 1'!A3表 2!B:B,'表 1'!B3表 2!C:C,'表 1'!C3表 2!D:D,'表 1'!D3表 2!E:E,'表 1'!E3)
公式的意思是:
在【表 2】中統(tǒng)計(jì)【表 1】中每一個(gè)單元格出現(xiàn)的次數(shù)。
如果結(jié)果為 1,表示在【表 1】中的數(shù)據(jù)在【表 2】中有。
如果結(jié)果為 0,表示在【表 2】中沒有。
最后篩選結(jié)果為 1 的數(shù)據(jù),就是我們想要的共同項(xiàng)。
是不是看上去公式很長(zhǎng)很長(zhǎng),如果列數(shù)比較多的話,那公式寫起來還是挺麻煩的。
而高級(jí)篩選只要點(diǎn)點(diǎn)鼠標(biāo)就可以搞定啦!
提取出兩表的不同項(xiàng)
上面,我們通過高級(jí)篩選篩選出兩表相同的數(shù)據(jù),那如何找出他們的不同項(xiàng)呢?
有的小伙伴們肯定已經(jīng)想到了,隱藏的數(shù)據(jù)不就是它們之間沒有的數(shù)據(jù)嘛?
如何把它們找出來呢?
我們還是以上面的為例,比如,我們想知道表 1 中哪些記錄在表 2 中沒有的。
?? 操作步驟如下:
? 選中【表 1】中任意單元格,然后點(diǎn)擊【數(shù)據(jù)】中的【高級(jí)】,調(diào)出【高級(jí)篩選】對(duì)話框,并選擇【A2:E12】數(shù)據(jù)區(qū)域,
? 點(diǎn)擊【條件區(qū)域】中的文本框,并選擇【表 2】中的數(shù)據(jù)區(qū)域【A2:E12】。最后點(diǎn)擊【確定】。
? 將篩選出來的數(shù)據(jù)的字體,設(shè)置成藍(lán)色(只要跟原來的字體顏色不同就行)。
? 取消【高級(jí)篩選】,并設(shè)置為【篩選】。
PS:直接點(diǎn)擊【篩選】按鈕,就可以自動(dòng)取消【高級(jí)篩選】功能了。
? 選擇【按顏色篩選】中的【按字體顏色篩選】-【自動(dòng)】。
最終的效果如下圖:
其中,有 4 條記錄與【表 2】中的數(shù)據(jù)不同:有可能是發(fā)票號(hào)碼不同,也有可能是其他某些單元格不同。
當(dāng)然我們用上面介紹過的 Countif 函數(shù)的做法也可以,把顯示為 0 的單元格所在行篩選出來,就是我們想要的結(jié)果了。
知識(shí)擴(kuò)展
學(xué)習(xí)了上面的高級(jí)篩選方法,我們可以輕松解決兩表之間的差異情況,
但是,我們要是以超過 15 位數(shù)字的身份證號(hào)碼或者銀行卡號(hào)等作為篩選條件的話,篩選出來的結(jié)果并不是我們期望的。
如下圖,我們想以【E2】中的身份證號(hào)碼為條件進(jìn)行篩選,
我們按照上面的步驟把【列表區(qū)域】、【條件區(qū)域】都添加進(jìn)來,之后點(diǎn)擊【將篩選結(jié)果復(fù)制到其他位置】,并在【復(fù)制到】文本框中輸入【F1】。
結(jié)果如下:
所有記錄都被篩出來了。
這是咋回事呢?
原因就出在這個(gè)數(shù)字的位數(shù)上。
如果數(shù)字的位數(shù)超過 15 位的話,默認(rèn)后面的數(shù)字都是 0。
這種情況下該怎么辦呢?
其實(shí)解決辦法也很簡(jiǎn)單,只要在身份證號(hào)碼的后面加上一個(gè)星號(hào)(「*」),問題就解決了。
添加*的目的就是把數(shù)字強(qiáng)行變成文本。
這里的星號(hào)(「*」)是通配符。
當(dāng)然,如果數(shù)據(jù)比較多的話,也可以用公式來批量添加:
公式如下:
=E2&"*"
用文本連接符(&)連接一個(gè)星號(hào),因?yàn)樾翘?hào)是字符所以需要用雙引號(hào)包圍住。
我們?cè)僦匦虏僮饕幌?,把區(qū)域都添加進(jìn)來:選擇復(fù)制到【F4】單元格用來存放結(jié)果數(shù)據(jù),最后點(diǎn)【確定】即可。
結(jié)果如下:
這樣結(jié)果就正確啦!
寫在最后
今天介紹了高級(jí)篩選的另類用法:
? 提取兩表的共同項(xiàng)。
? 提取兩表的不同項(xiàng)。
? 對(duì)于超過 15 位數(shù)字的號(hào)碼篩選出錯(cuò)的解決方法。
你都學(xué)會(huì)了嗎?
本文來自微信公眾號(hào):秋葉 Excel (ID:excel100),作者:明鏡在心,編輯:小音、竺蘭
廣告聲明:文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接(包括不限于超鏈接、二維碼、口令等形式),用于傳遞更多信息,節(jié)省甄選時(shí)間,結(jié)果僅供參考,IT之家所有文章均包含本聲明。