學電腦知識

又快又準 Excel數據核對就這么搞!

學電腦知識 http://www.nnyvff.tw 2018-09-19 12:05 出處:網絡 編輯:@學電腦
平時工作中我們經常需要都數據進行核對,不過需要核對的數據有的在不同列,有的則在不同工作表。對于這些數據,可以通過本文的方法進行快速核對。

平時工作中我們經常需要都數據進行核對,不過需要核對的數據有的在不同列,有的則在不同工作表。對于這些數據,可以通過本文的方法進行快速核對。

單表數據——條件格式快速核對

很多時候需要核對的數據是在一個工作表的不同行列中,比如在倉庫的盤點統計中,庫存數是根據公式自動計算出來,還有一個實盤數則是手工填寫。現在需要對這兩列數據進行核對,以便找出數值不同的數據(圖1)。

又快又準 Excel數據核對就這么搞!

圖1核對庫存和實盤數

對于這種在同一工作表不同列數據的核對,最簡單的方法是用條件格式進行核對。依次選中庫存數和實盤數,點擊“條件格式→突出顯示單元格規則→重復值”,將重復值設置為默認淺紅色顯示,這樣兩列中數據不同的數值就可以很直觀的標示出來了(圖2)。

又快又準 Excel數據核對就這么搞!

圖2使用條件格式快速找出數據不同的單元格

條件格式可以很快將不同數據篩選出來,但是如果每列的數據很多,要在眾多的數據中查找還是有些不便,對于這類數據還可以使用“條件格式+IF函數”進行核對。首先在實盤數后插入一個輔助列F,選中F4,插入公式“=IF(OR(D4=E4),"正確","請重新核對")”,然后向下填充。公式的意思是,對D、E列的數值進行核對,如果相同則顯示“正確”,否則顯示“請重新核對”。繼續選中F列,點擊“條件格式→突出顯示單元格規則→等于”,然后在彈出的窗口,為等于以下值的單元格設置格式→輸入“請重新核對”→設置為淺紅填充深紅色文本(圖3)。

又快又準 Excel數據核對就這么搞!

圖3等于單元格設置

這樣對于不同值的單元格后面就會顯示紅色的“請重新核對”,完成后再對F列按照單元格的顏色進行排序,這樣所有不同數據會自動排列并且突出顯示(圖4)。

又快又準 Excel數據核對就這么搞!

圖4 使用IF函數進行篩選和排序

跨表核對——篩選+函數高效查找

同一工作表數據可以使用條件格式快速核對,但是如果核對的數據在不同工作表,此時就需要使用其他方法進行快速核對。如在倉庫盤點中,很多公司的實盤是其他工作人員參與盤點的,這樣實盤數目輸入在另外一個工作表中,而且由于每個倉庫的物品不同,每個人盤點的產品只是總庫中一部分。比如現在總庫表有1024件產品,張三實盤表只有其中24件,現在需要在總庫表中快速找出張三點物品中庫存數和實盤數不同的物品。

顯然解決這個問題的關鍵是,要在兩個工作表中找到物品名稱相同,但是數量不同的物品,這個可以借助VLOOKUP函數完成查找。首先在總庫工作表中定位到D2,接著輸入公式“=VLOOKUP(A2,張三實盤表!$A$2:$B$24,2,0)<>B2”,這個公式的作用是查找查找材料名稱相同,但是數量不同的數據(圖5)。

又快又準 Excel數據核對就這么搞!

圖5

公式解釋:

這里是使用VLOOKUP函數查找數據,公式中的A2表示查找目標,即這里查找的是“材料名稱”,“張三實盤表!$A$2:$B$24”表示查找范圍,即在張三編制的實盤報表有效數據區(A1-B24)中進行查找。“2”表示“返回值”在第二個參數給定的區域中的列數,由于這里是查找“盤存數量”,位于工作表的第2列(即B列),這里要注意的是列數不是在工作表中的列數,而是在查找范圍區域的第幾列。“0”則表示精確查找(而值為1 或TRUE時則表示模糊)。<>B2則表示不等于,也就是在張三實盤表中查找盤存數量不同的數據。

找到符合條件的數據后,接下來使用高級篩選進行特定數據的核對。依次單擊“數據→篩選→高級”在出現“高級篩選”對話框中,篩選方式選擇“在原有區域顯示篩選結果”;“列表區域”選擇“庫存總表表”中的所有數據區,條件區域則選D1→D2,即Vlookup函數查找數據(圖6)

又快又準 Excel數據核對就這么搞!

圖6篩選設置

點擊“確定”,這樣總庫表中就會自動顯示出張三盤點物品(其他張三沒有盤點或者數量正確的物品則自動隱藏),并且其數據是和庫存數量不同的,按提示將其數據字體標記為紅色,這樣在總庫那么多物品中很快就可以找出盤點和庫存不一致的數據,按要求進行復核即可(圖7)。

又快又準 Excel數據核對就這么搞!

圖7 篩選可以自動列出符合要求數據

繼續點擊“數據→篩選→清除”,這樣會顯示出總庫原來所有物品,可以看到這里紅色標記的即為符合要求的數據。如果還有其他盤點表,操作同上將所有符合要求的數據全部標紅,最后在使用單元格顏色排序即可(圖8)。

又快又準 Excel數據核對就這么搞!

圖8 排序后顯示所有符合要求的數據

可以看到,類似在不同工作表中關鍵字相同,但是數值不同的數據,在Excel中我們可以使用VLOOKUP函數作為條件區域,然后借助高級篩選即可快速找出這些數據。這里還要注意的是,高級篩選時用于篩選的兩個列的標題行內容必需一致,如本例中是查找“材料名稱”和“盤存數量”(兩個工作表標題內容一定要一樣,否則查找會出錯)。此外用于篩選的兩列里不能有空白單元格,如不要使用“材料名稱”這樣的標題。平時大家在工作中一定要養成數據輸入規范的好習慣,這樣在后續數據排序、查找、篩選才不會帶來不便。


0

精彩評論

双色球26选5开奖结果