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