一般來說,核對的核心是確定規則,我們以某列關鍵字作為核對標準的話,采用if+countif做輔助列函數即可

比如上圖,要根據sheet1已經體檢名單,在sheet2中B列標記出員工是否已參加體檢,操作步驟如下

• 在sheet2的B2寫公式=IF(COUNTIF(Sheet1!A:A,A2)>0,"是","否")

• 往下復制公式,鼠標移動到有下家雙擊或者直接復制公式均可

• 計算結果是則表示在sheet中已存在,否則代表不存在,countif函數的作用,就是計算某個區域中滿足某個條件的數據有多少個,這里用if函數檢查sheet1中所有數據是否包含了對應的本行A列數,包含了則>0,返回是,否則返回否

• 對B列進行篩選即可達到未參加體檢的名單

• 附視頻教程{!--PGC_VIDEO:{"hash_id":3808910833308536857,"thumb_height":360,"thumb_url":"eea000554e939c43d55","user_id":0,"neardup_id":3808910833308536857,"thumb_width":640,"sp":"toutiao","vposter":"https://p3-sign.toutiaoimg.com/mosaic-legacy/eea000554e939c43d55~noop.image?x-expires=1994575759&x-signature=Fr3USn%2BB53V4UeTwEQoZgLNkDcY%3D","vid":"v02004g10000ceeai0jc77u1jt93nh9g","duration":196,"video_size":{"high":{"h":480,"subjective_score":1,"w":854,"file_size":5885858},"ultra":{"h":720,"subjective_score":1,"w":1280,"file_size":9980291},"normal":{"h":360,"subjective_score":1,"w":640,"file_size":4246960}},"item_id":6334873770364240386,"media_id":50090367555,"thumb_uri":"eea000554e939c43d55","group_id":6334869691325513985,"vname":"\u5feb\u901f\u4eceEXCEL\u8868\u91cc\u4e24\u5217\u76f8\u4f3c\u6570\u636e\u4e2d\u627e\u51fa\u5f02\u540c.mp4","md5":"df198ac7c10cc777023251d9c082a7b3","vu":"v02004g10000ceeai0jc77u1jt93nh9g"}--}