因數字帶綠帽子的問題,對賬3個小時都搞不定,慘。。。
-
2024-06-24 07:40
-
Excel不加班
![](https://img1.headline01.com/images/f8/73/f873b249c8ba91162ca10d42dd5acbd4fd7d98ec.jpg?wx_fmt=jpeg)
VIP學員的問題,原始數據非常多列,爲了簡化,只留下核心的2列。要根據訂單號、金額互相覈對。左邊的訂單號沒有綠帽子,右邊的有,這就是不同的地方。![](https://img1.headline01.com/images/93/6b/936be8a71208fb8cddf3657195bbab724b26cd49.jpg?wx_fmt=png&wxfrom=13&tp=wxpic)
最近有幾位學員都被這個問題難住了,有一個甚至爲了去除綠帽子加班3個小時,最後卻做了無用功,依然解決不了問題。
其實,要解決問題,只需把這個原理牢牢記在心中。超過15位純數字,必須是用文本格式,如果不是文本格式還能正常顯示的,必然含有隱藏字符。
知道了這個原理,就能很快解決問題,跟着盧子來看看。
右邊的訂單號帶有綠帽子,證明是文本格式,沒有問題,無需做任何處理。而左邊的訂單號沒有綠帽子,證明含有隱藏字符。點開單元格,在編輯欄就能看到。
![](https://img1.headline01.com/images/3f/6d/3f6d1e87b7846481f671c03d3116bddc1aaf39ab.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
這種隱藏字符太簡單了,實際工作中很多隱藏字符是沒法在Excel中看到,而要複製到記事本中纔可以看到。
![](https://img1.headline01.com/images/5a/68/5a688236f9b8fb85d45302902ddcb2b172faf58b.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
既然前面含有隱藏字符,右邊在查找的時候,加個星號就能解決。星號代表所有字符,也就是不管訂單號前面包含什麼字符,都能查找到。
![](https://img1.headline01.com/images/e9/fb/e9fb891d7c164492b95c0b8666aaaf6d9106bf4a.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
=VLOOKUP("*"&E3&"*",A:B,2,0)
要先去除隱藏字符,這個沒有固定的公式,正常都要進行嘗試纔行。下面2個在這裏剛好都可以去除。
去除完就可以正常查找了。
=VLOOKUP(CLEAN(A3),E:F,2,0)
![](https://img1.headline01.com/images/35/b2/35b2bf340ece5319e6ca39eb1d2c90e6c04dea42.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
左邊的訂單號比右邊的多,自然有一些沒有對應值,從而返回錯誤值#N/A,可以用IFERROR讓錯誤值顯示0。
=IFERROR(VLOOKUP(CLEAN(A3),E:F,2,0),0)
![](https://img1.headline01.com/images/67/a5/67a551f5fd71f5e700a2762a45865789c3c26c46.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
=IFERROR(VLOOKUP(MID(A3,2,99),E:F,2,0),0)
從網頁或者其他軟件直接複製過來,會變成E+18,這種是無法恢復的,即使重新設置爲文本格式都不行。
![](https://img1.headline01.com/images/66/1b/661b1f2a3f7e5fdd4626d2ac08a31bb54d8def37.jpg?wx_fmt=png&tp=wxpic&wxfrom=5&wx_lazy=1&wx_co=1)
正確的做法是,先將單元格設置爲文本格式,再從其他地方複製過來。
![](https://img1.headline01.com/images/9c/59/9c591894d7b171e37a0b5b44c6b58ba30672337b.jpg?wx_fmt=gif&tp=wxpic&wxfrom=5&wx_lazy=1)
直接分列得到的依然是E+18,就不做演示。這裏提供正確的做法,按分隔符號分列,將長字符的列設置爲文本格式,完成。
![](https://img1.headline01.com/images/2b/ab/2babfb80611496d308869f3d42b3399da44c30b7.jpg?wx_fmt=gif&tp=wxpic&wxfrom=5&wx_lazy=1)
很多時候,長字符都不是第一列,這樣就需要手工去選擇,纔可以。
![](https://img1.headline01.com/images/9d/1c/9d1cae44b5ea7fa5d27ad0ebd891e3daf0672a31.jpg?wx_fmt=gif&tp=wxpic&wxfrom=5&wx_lazy=1)
陪你學Excel,一生夠不夠?
一次報名成爲VIP會員,所有課程永久免費學,永久答疑,僅需 1500 元,待你加入。
![](https://img1.headline01.com/images/8c/35/8c35958b886d2aa1f9da1cca332feae1dd02d418.jpg?wx_fmt=jpeg)
報名後加盧子微信chenxilu2019,發送報名截圖邀請進羣。
推薦:這4個對賬的方法全學會,就可以在公司橫着走
上篇:別找了,你要的Excel多表合併都在這裏了!(又增加2個新方法)
![](https://img1.headline01.com/images/73/f0/73f0db27626fc7ab95507923bb4074b2cc3c0bcf.jpg?wx_fmt=png)
作者:盧子,清華暢銷書作者,《Excel效率手冊 早做完,不加班》系列叢書創始人,個人公衆號:Excel不加班(ID:Excelbujiaban)
![](https://img1.headline01.com/images/be/b9/beb9876df236d72e24b569c82882ff0347801068.jpg?wx_fmt=jpeg)
請把「Excel不加班」推薦給你的朋友
別忘了點贊支持盧子哦↓↓↓