今天的教程由會(huì)飛的魚提供，他是Excel不加班團(tuán)隊(duì)管理人員之一。
笨鳥：飛魚啊，為什么我學(xué)習(xí)的時(shí)候感覺(jué)都懂了，自己編寫公式的時(shí)候就會(huì)出現(xiàn)各種錯(cuò)誤。
飛魚：出現(xiàn)錯(cuò)誤后，首先要找到出問(wèn)題的原因，然后才能對(duì)癥下藥。以VLOOKUP為例，出現(xiàn)錯(cuò)誤的原因有兩種：其一是粗心大意，忘了設(shè)置某個(gè)參數(shù)或者設(shè)置函數(shù)公式不正確，其二是數(shù)據(jù)不規(guī)范。下面重點(diǎn)介紹VLOOKUP函數(shù)中幾種錯(cuò)誤值（#N/A）的場(chǎng)景。
01 公式返回錯(cuò)誤值（#N/A）。
=VLOOKUP(D2,A2:B10,2,0)
錯(cuò)誤(#N/A)的是意思查找不到，可以理解為查找值不存在。檢查公式，發(fā)現(xiàn)是因?yàn)椴檎覅^(qū)域沒(méi)有使用絕對(duì)引用鎖定，在公式向下填充后，查找區(qū)域也隨之向下改變，導(dǎo)致查找不到值，所以返回錯(cuò)誤值(#N/A)。解決方法是查找區(qū)域使用絕對(duì)引用，或者引用整列。
=VLOOKUP(D4,$A$2:$B$10,2,0)
=VLOOKUP(D4,A:B,2,0)
修改公式后，E2、E3單元格還是返回錯(cuò)誤值，我們已經(jīng)知道(#N/A)錯(cuò)誤類型是查找不到值，但我們看到A列姓名是包含D3單元格“柳瑤”的，明明有相同的內(nèi)容，為什么查找不到呢？
遇到這種情況說(shuō)明這兩個(gè)單元格的“柳瑤”并不完全相同。這里提供兩種常出現(xiàn)的場(chǎng)景及解決方法：一種是人工錄的數(shù)據(jù)，要檢查文本兩端是否存在空格，可以使用替換功能把空格替換為空；另一種是從統(tǒng)計(jì)導(dǎo)出的數(shù)據(jù)，除了要檢查空格問(wèn)題，還要檢查數(shù)據(jù)是否包含非打印字符。
笨鳥：檢查數(shù)據(jù)是否有空格這個(gè)我會(huì)，用替換功能就可以了，關(guān)鍵是怎樣檢查數(shù)據(jù)是否包含非打印字符呢？
飛魚：把數(shù)據(jù)復(fù)制Word里可以快速找出非打印字符了。我們可以看到“柳瑤”前后包含有換行符，在Word中刪除無(wú)用字符后再?gòu)?fù)制到Excel就可以了。
以上，另一種除去空格和非打印字符的方法是使用嵌套函數(shù)。首先使用CLEAN函數(shù)去除非打印字符，然后使用TRIM函數(shù)去除兩端空格。數(shù)組公式，按Ctrl+Shift+Enter三鍵結(jié)束。注意，數(shù)組公式不要引用整列，否則計(jì)算量太大，可能會(huì)導(dǎo)致Excel崩潰。
=VLOOKUP(TRIM(CLEAN(D2)),TRIM(CLEAN($A$1:$B$10)),2,0)
雖然用以上函數(shù)可以直接處理這類(#N/A)錯(cuò)誤，但不建議大家這么做。這個(gè)公式存在的意義只是幫助我們快速的查找到想要的結(jié)果，正確的做法是使用替換功能，或者通過(guò)輔助列轉(zhuǎn)換為規(guī)范數(shù)據(jù)格式。養(yǎng)成一個(gè)好的制表習(xí)慣非常重要。
笨鳥：我明白了，我一定記住要規(guī)范數(shù)據(jù)。
如果查找后沒(méi)有對(duì)應(yīng)值，我們使用IFERROR函數(shù)，把錯(cuò)誤值轉(zhuǎn)換為空，或其它提示內(nèi)容。
=IFERROR(VLOOKUP(D2,A:B,2,0),"無(wú)信息")
02 公式返回結(jié)果不匹配。
=VLOOKUP(D2,A:B,2,1)
=VLOOKUP(D2,A:B,2)
以上兩條公式都是錯(cuò)誤的。第一條，第4個(gè)參數(shù)設(shè)置1后，查找模式為模糊查找。第二條，省略了第4個(gè)參數(shù)，省略后查找模式同樣為模糊查找。正確公式應(yīng)該是第4個(gè)參數(shù)設(shè)置為0，即精確查找。當(dāng)你對(duì)函數(shù)非常熟悉后，可以省略第四個(gè)參數(shù)值，查找模式同樣是精確查找。
=VLOOKUP(D2,A:B,2,0)
=VLOOKUP(D2,A:B,2,)
笨鳥：省略參數(shù)不對(duì)，省略參數(shù)值卻可以啊，這里不明白呢？
飛魚：不明白也沒(méi)事，指定查找模式就可以了。
飛魚：還有一種情況也會(huì)導(dǎo)致返回結(jié)果不匹配。
=VLOOKUP(E2,A:C,2,0)
笨鳥：我看沒(méi)什么問(wèn)題啊，查找區(qū)域引用整列，不是絕對(duì)引用問(wèn)題，一共兩列數(shù)據(jù)，返回第2列，查找模式也設(shè)置精確查找了，真是奇怪了。
飛魚：如果你仔細(xì)看，B列被隱藏了，實(shí)際查找區(qū)域共有3列，公式返回2列，實(shí)際上是返回B列的結(jié)果，把返回列設(shè)置為3才是正確的。
=VLOOKUP(E2,A:C,3,0)
有一些小伙伴的制表習(xí)慣不好——對(duì)于無(wú)用的列，不是直接刪除，而是隱藏起來(lái)，這樣的習(xí)慣會(huì)給我們后期數(shù)據(jù)處理帶來(lái)許多麻煩。
在實(shí)際工作中，引用少則幾列，多則十幾列甚至幾十列，第3參數(shù)非常關(guān)鍵，在設(shè)置的時(shí)候也易出現(xiàn)錯(cuò)誤，并且設(shè)置錯(cuò)誤后也不易被發(fā)現(xiàn)。以下圖示，馮俊9月對(duì)應(yīng)的數(shù)值是178，因?yàn)榈?/span>3參數(shù)設(shè)置錯(cuò)了，返回的是8月的175。這種錯(cuò)誤，當(dāng)數(shù)據(jù)成千上萬(wàn)行時(shí)，是很難發(fā)現(xiàn)的。
=VLOOKUP(P3,B:N,9,0)
笨鳥：那有什么好方法嗎？
飛魚：可以使用COLUMN函數(shù)，返回查找區(qū)域?qū)?yīng)的所在列號(hào)。以下圖示，在查找區(qū)域的第一列的B7單元格，輸入公式后向右填充即可返回每一列對(duì)應(yīng)的序號(hào)。可以有效的解決隱藏列的問(wèn)題。
=COLUMN(A1)
或者在B7單元格輸入1后，按“Ctrl”同時(shí)，鼠標(biāo)拖動(dòng)填充柄向右填充也是可以的。
這樣我們就可以清楚地看到9月是第10列，第三個(gè)參數(shù)輸入10就ok。
=VLOOKUP(P3,B:N,10,0)
笨鳥：這個(gè)方法好，使用這個(gè)方法就不擔(dān)心數(shù)錯(cuò)了。
大家覺(jué)得會(huì)飛的魚教程寫得如何？覺(jué)得好記得打賞支持。
作者：盧子，清華暢銷書作者，《Excel效率手冊(cè) 早做完，不加班》系列叢書創(chuàng)始人，個(gè)人公眾號(hào)：Excel不加班（ID：Excelbujiaban）
聯(lián)系客服
