私信回覆關鍵詞【UP】,獲取VLOOKUP函數用法教程合集,一看就會!
大家好,我是秋小 E~這裡是秋葉 Excel 的【問答精華】專欄!
問題主要來自秋葉 Excel 的同學,回答由講師、助教共同完成;
每週一期,每期主題不同,希望能更有針對性地解決大家的共性問題。
今天的主題是——數據核對。
來看看這些小問題,你有沒有遇到過,當時又是怎麼解決的?
01
核對產品上下架
提問:對比 7 月和 8 月的產品,查找哪些產品是 8 月新上架的?哪些是已經下架的?
該怎麼做?
答疑人:玉霞助教
思路分析:
先核對 A 列的商品是否在 D 列。
如果在,就顯示為空,否則顯示為已經下架。
再核對 D 列的商品。
如果不在 A 列,說明是 8 月份新上架的商品,顯示「上架」,否則為空。
解決方法 ❶:
構建輔助列「C 列」,用 COUNTIF 函數條件計數,再嵌套於 IF 函數顯示核對結果。
對於 7 月商品,輔助列「C 列」數值如果大於 0,顯示為空,否則顯示為已經下架;
對於 8 月商品,輔助列結果如果等於 0,顯示為新產品上架,否則顯示為空。
B2 單元格公式為:
<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新產品上架")/<code>
▲左右滑動查看
C2 單元格公式為:
<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新產品上架")/<code>
同理,E2 單元格公式為:
<code>=IF(COUNTIF($A$2:$A$14,D2)>0,"","新產品上架")/<code>
▲左右滑動查看
F2 單元格公式為:
<code>COUNTIF(A2:A14,D2)/<code>
解決方法 ❷:
構建輔助列「C 列」,用 VLOOKUP 函數查找;
因部分數據查找不到,會顯示錯誤值#N/A。
所以,嵌套 IFERROR 函數,設定返回錯誤值#N/A 的,顯示為空,再嵌套 IF 函數顯示核對結果。
和「解決方法❶」一樣,對於 7 月商品,輔助列如果為空,顯示為已下架,否則顯示為空;
對於 8 月商品,輔助列結果為空,顯示為新產品上架,否則顯示為空。
C2 單元格公式為:
<code>IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),"")/<code>
▲左右滑動查看
B2 單元格公式為:
<code>IF(IFERROR(VLOOKUP(A2,$D$2:$D$14,1,0),"")="","已經下架","")/<code>
▲左右滑動查看
特別提示:函數的書寫,符號均需為英文狀態下格式;
VLOOKUP 函數的第二個參數,查找區域需加 $ 鎖定。
02
核對兩表
提問:請問查找兩張獨立表裡的重複數據?
答疑人:拉登老師、玉霞助教
解決方法 ❶:
將兩個表格合併到一起,然後用條件格式,快速標記重複值。
解決方法 ❷:
使用 VLOOKUP 函數,將兩個表格相同的內容匹配到一起,查詢結果為#N/A,代表是不重複的。
05
數據太長,重複值標記不對
提問:想要標記重複值高亮,為什麼出來的結果卻是錯的?
答疑人:黃群金 King 老師
原因分析:
會一點 Excel 的都知道。
利用條件格式的重複值選項,可以突出顯示重複的數據,只要點 2 下鼠標就可以快速完成。
可是,這個功能,在碰到超過 15 位的數字時會失效。
例如,案例中的 18 位運單號,只要前面 15 位數一樣,後面 3 位不管是什麼,Excel 都把它當成重複數據了。
身份證號、銀行賬號一樣會碰到這個問題。
因為超過 15 位,Excel 就會把它識別成天文數字,自動後面的數字。
解決方法:
解決方法也簡單,需要用 Countif 函數來救場,突破條件格式的功能限制。
❶ 選中兩列數據。
❷ 新建條件格式規則。
點擊【開始】-【條件格式】,新建規則,選擇最後一個規則類型「使用公式」。
❸ 添加規則公式。
<code>=AND(COUNTIF($B$2:$C$11,"*"&B2&"*")>1,B2<>"")/<code>
▲左右滑動查看
公式包含兩層:
計算當前單元格中數據。
完整出現在整個區域中的次數,大於 1 次,則符合條件。
<code>COUNTIF($B$2:$C$11,"*"&B2&"*")>1/<code>
▲左右滑動查看
其中,*是通配符,表示任意字符。
B2&"*",用來代表包含 B2 的數據。
強行將當前單元格里的數據當成文本來計數,超過 1 個就代表有重複。
且當前單元格不能為空。
<code>B2<>""/<code>
沒想到吧?
平時以為很簡單的數據核對,居然還有這麼多使用場景,解決辦法也是多種多樣!
私信回覆關鍵詞【UP】,獲取VLOOKUP函數用法教程合集,一看就會!