我們上一章聊了如何快速、正確的編寫(xiě)Excel函數(shù)公式的11個(gè)小技巧；從單篇數(shù)據(jù)可以看得出來(lái)，大家都是實(shí)干家，所以并不在意那些小技巧——我不是嚇唬你們嘿，函數(shù)新人所犯的錯(cuò)誤，通常就是由于不知道那些小技巧，所以再次強(qiáng)烈推薦大家看一看：如何快速編寫(xiě)正確的函數(shù)公式？作為《從入門(mén)到精深，Excel函數(shù)輕松學(xué)》的第17篇推文，我們這一章來(lái)聊……坑。常在河邊走，哪能不濕腳？人在江湖飄，哪能不挨刀？所以掉坑不可怕，可怕的是沒(méi)掉過(guò)坑——連坑都沒(méi)掉過(guò)，也敢說(shuō)會(huì)函數(shù)？切~單純~8，有哪些函數(shù)可以跨工作簿自動(dòng)讀取數(shù)據(jù)？一覺(jué)醒來(lái)，忽然發(fā)現(xiàn)Excel變得連臉都不認(rèn)識(shí)了。輸入公式=A1，莫名其妙變成了=R[XX]C[XX]，讓人一臉懵懂。我們?cè)诤瘮?shù)教程的第2章講過(guò)，單元格引用有兩種樣式。一種是A1引用樣式，這是系統(tǒng)默認(rèn)的，也是最為常用的。其中A是指列標(biāo)，1是指行號(hào)，例如C5單元格，也就是指C列和第5行交叉處的單元格。另一種是R1C1引用樣式，比較少見(jiàn)，其中R是英文ROW的首字母，代表行。C是英文COLUMN的首字母，代表列。R5C3單元格也就是第5行和第3列交叉處的單元格，等同于A1引用樣式中的C5單元格。有時(shí)候您可能會(huì)收到列標(biāo)變?yōu)閿?shù)字的表格，這可能是因?yàn)橹票碚咴跓o(wú)意間設(shè)置了R1C1的單元格引用樣式，取消的方法很簡(jiǎn)單，依次單擊【文件】→【選項(xiàng)】→【公式】，然后撤銷R1C1引用樣式即可。單元格引用有兩種形式，相對(duì)引用和絕對(duì)引用。相對(duì)引用的地址會(huì)隨著公式所在位置的改變而改變，絕對(duì)引用的地址永恒不變——但很多函數(shù)新人會(huì)忘記絕對(duì)引用的存在，比如說(shuō)……如上圖所示，需要在E列查詢D列人員的考試成績(jī)，有些朋友會(huì)在E2輸入以下公式：公式有錯(cuò)嗎？有！哪里錯(cuò)了？查詢范圍沒(méi)有絕對(duì)引用！可是E2返回正確結(jié)果了?。俊荅4沒(méi)返回正確結(jié)果??？當(dāng)E2的公式向下復(fù)制填充到E4，就變成了：
查詢范圍已經(jīng)不包含A2和A3了。因此正確的查詢函數(shù)應(yīng)該是：=VLOOKUP(D4,A$4:B$12,2,0)
所以，我們說(shuō)，做人不要太摳門(mén)，該給錢(qián)時(shí)就給錢(qián)——聽(tīng)不懂這句話說(shuō)明您——沒(méi)有——看——我們的——函數(shù)——教程~同樣以上圖所示的數(shù)據(jù)為例，有時(shí)候我們會(huì)把公式寫(xiě)成：公式可以返回結(jié)果，但結(jié)果并不正確。公式省略了第4參數(shù)，也就是VLOOKUP的查詢匹配機(jī)制。有朋友會(huì)說(shuō)，VLOOKUP省略第4參數(shù)不是默認(rèn)采用精確匹配嗎？——真的嗎？我們很明確的講過(guò)，當(dāng)VLOOKUP第4參數(shù)為0時(shí)，才代表零失誤精確匹配。如果省略第4參數(shù)，則默認(rèn)為近似匹配。具有同樣問(wèn)題的還有MATCH函數(shù)等，所以千萬(wàn)不要搞錯(cuò)函數(shù)參數(shù)的默認(rèn)值。
還是以上圖所示的數(shù)據(jù)表為例，有時(shí)候我們又會(huì)把VLOOKUP公式寫(xiě)成這樣：有朋友會(huì)說(shuō)，咦，你這公式和上面那條公式，不是一樣嗎？=VLOOKUP(D2,A:B,2,)=VLOOKUP(D2,A:B,2)
第一條公式多了個(gè)逗號(hào)，VLOOKUP函數(shù)第4參數(shù)，作了留白處理。而第二條公式?jīng)]有逗號(hào)，直接省略了第4參數(shù)。省略和留白是不一樣的。省略狀態(tài)下，并不存在該參數(shù)，留白狀態(tài)下，參數(shù)是存在的，只是沒(méi)有輸入數(shù)據(jù)，但會(huì)被Excel默認(rèn)為0。而在VLOOKUP函數(shù)中，第4參數(shù)為0，即為零失誤精確匹配查詢。
盡管對(duì)參數(shù)留白會(huì)顯得很酷，但我們還是建議大家使用完整的參數(shù)描述，這既顯得你公式編寫(xiě)規(guī)范，又可以避免無(wú)意間犯下錯(cuò)誤。
還是以上圖所示的數(shù)據(jù)表為例，E2公式如下：有一個(gè)問(wèn)題，當(dāng)數(shù)據(jù)源的B列ID號(hào)為空白時(shí)，公式返回為0，比如E2單元格。這是由于在公式計(jì)算中，空白單元格默認(rèn)為0。不過(guò)，我們往往并不希望空白單元格顯示為0，而是希望它顯示為空白。
如果函數(shù)的計(jì)算結(jié)果為文本，如此例，我們可以將公式修改為：但是，如果公式的計(jì)算結(jié)果為數(shù)值，我就不推薦使用這種方法來(lái)屏蔽公式返回的零值了，盡管它也可以做到。這是由于它會(huì)將數(shù)值類型的數(shù)據(jù)，轉(zhuǎn)換為文本型數(shù)值，并不利于數(shù)據(jù)的再次運(yùn)算，比如求和、求平均、條件求和等。同樣的緣故不推薦使用TEXT函數(shù)屏蔽零值，TEXT是文本函數(shù)，一切文本函數(shù)返回的結(jié)果必然為文本。我們上一節(jié)講，為了屏蔽公式返回的零值，我們很可能采用&''的方式，這是很常見(jiàn)的。盡管這種方式會(huì)將公式返回的零值屏蔽為空白單元格，但單元格并非真正的空白，實(shí)際上它存在了一對(duì)半角雙引號(hào)：''。這一類單元格我們稱之為假空，假空的本質(zhì)是一個(gè)文本值。而真空單元格，顧名思義單元格是空白的，看起來(lái)是空白的，實(shí)際上也是空白的。從肉眼上我們很難判斷一個(gè)單元格是真空還是假空。而假空的本質(zhì)是文本，文本是不能參與數(shù)學(xué)運(yùn)算的，否則會(huì)返回錯(cuò)誤值#VALUE!此外，在函數(shù)教程里我們介紹了幾個(gè)和假空有關(guān)的函數(shù)。比如COUNTBLANK，號(hào)稱計(jì)算空白單元格的個(gè)數(shù)，實(shí)際上并不區(qū)分真空和假空，這兩者在它眼中都是空白單元格。同時(shí)對(duì)于COUNTIF和SUMIF來(lái)說(shuō)，=COUNTIF(A:A,'')，同樣并不區(qū)分真空和假空，這兩者在該公式中，也都認(rèn)為是空白單元格。計(jì)算真空單元格的個(gè)數(shù)，我們通常使用=COUNTIF(A:A,'=')7，函數(shù)無(wú)法跨工作簿引用數(shù)據(jù)怎么辦這個(gè)問(wèn)題最常出現(xiàn)在07版Excel，07版Excel是一個(gè)過(guò)渡版本，有很多BUG，所以我們并不推薦大家使用，推薦大家使用骨灰級(jí)的03版都別用07版，不然有一天你怎么被Excel坑死的都不曉得。10和13版偶爾也會(huì)遇到這種情況，16和19版就不會(huì)遇到這種情況。1）重新設(shè)置文件默認(rèn)的打開(kāi)方式。
優(yōu)先推薦第一種方案。右鍵單擊某個(gè)Excel文件，在彈出的菜單中選擇【打開(kāi)方式】→【選擇其他應(yīng)用】，勾選【始終使用此應(yīng)用打開(kāi).xlsx】。8，有哪些函數(shù)可以跨工作簿自動(dòng)讀取數(shù)據(jù)？大部分函數(shù)和公式，在工作簿未打開(kāi)的情況下，是不能跨工作簿讀取數(shù)據(jù)的。我們說(shuō)的是大部分，意思是還有一小部分會(huì)例外。最常用的有兩個(gè)：等號(hào)和VLOOKUP。即便工作簿未打開(kāi)，只要輸入完整的工作簿地址，等號(hào)一樣可以將數(shù)據(jù)引用到當(dāng)前工作表。比如：='D:\函數(shù)教程\[半島灣項(xiàng)目組.xlsx]Sheet1'!B2
這可以解決很多問(wèn)題。比如，使用等號(hào)將某個(gè)工作簿指定工作表的數(shù)據(jù)全部引用到當(dāng)前工作簿的一張工作表中，也就無(wú)需打開(kāi)目標(biāo)工作簿，即可直接處理相關(guān)數(shù)據(jù)。單純的VLOOKUP函數(shù)也不需要打開(kāi)目標(biāo)工作簿，一樣可以讀取數(shù)據(jù)，并且它可以將數(shù)據(jù)放入自身的緩存之中，即便你將目標(biāo)工作簿刪除了，也不妨礙它運(yùn)算。比如公式：=VLOOKUP(A2,'D:\函數(shù)教程\[半島灣項(xiàng)目組.xlsx]Sheet1'!A:B,2,0)不用打開(kāi)半島灣項(xiàng)目組.xlsx，一樣可以對(duì)其Sheet1工作表進(jìn)行查詢運(yùn)算。
這可以解決很多問(wèn)題。一次性設(shè)置公式，即可批量處理多個(gè)工作簿，以后只需要收集、更新其它工作簿的數(shù)據(jù)，總表數(shù)據(jù)自可自動(dòng)更新。
|
