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