最近有個(gè)朋友在整理年度考核表幾十號(hào)人的績(jī)效評(píng)級(jí)全靠手工判斷眼睛都快看花了。我遞過去一份用IF函數(shù)做好的模板她填完數(shù)據(jù)后評(píng)級(jí)自動(dòng)出來(lái)整個(gè)人都愣住了——原來(lái)Excel里最基礎(chǔ)的一個(gè)邏輯函數(shù)能省下這么多事。這個(gè)場(chǎng)景我想很多人都不陌生IF函數(shù)看起來(lái)簡(jiǎn)單入門教程里一句話就帶過了可真正要把它用得得心應(yīng)手、能應(yīng)對(duì)各種復(fù)雜條件判斷很多老手也未必說得清楚。這篇內(nèi)容我圍繞IF函數(shù)從基礎(chǔ)語(yǔ)法、多條件嵌套、與統(tǒng)計(jì)/查找類函數(shù)的組合一路寫到數(shù)組公式里的特殊用法最后再把實(shí)際使用中高頻踩坑的地方集中排一遍雷。無(wú)論你是剛接觸Excel公式的新手還是已經(jīng)能熟練操作VLOOKUP的老手這篇都值得花十幾分鐘過一遍。1. 認(rèn)識(shí)IF函數(shù)一次搞懂它在做什么1.1 從生活化場(chǎng)景理解判斷邏輯IF函數(shù)干的事本質(zhì)上就是替你做一道二選一的選擇題。比如你手機(jī)里有個(gè)天氣預(yù)報(bào)App它會(huì)判斷今天是否下雨如果下雨就提醒你帶傘如果不下雨就不提醒。這個(gè)如果……就……否則就……的判斷流程放到Excel里就是IF函數(shù)。很多人學(xué)IF函數(shù)時(shí)卡在了一個(gè)概念上Excel里的真和假。IF函數(shù)的第一參數(shù)是一個(gè)條件判斷式這個(gè)式子計(jì)算出來(lái)的結(jié)果只能是兩種——成立TRUE或不成立FALSE。成立就返回第二參數(shù)的值不成立就返回第三參數(shù)的值。這聽起來(lái)很簡(jiǎn)單但它是一切復(fù)雜邏輯的根基。我用一個(gè)最直觀的例子來(lái)展示它的寫法。假設(shè)A1單元格里是銷售額我們要判斷業(yè)績(jī)是否達(dá)標(biāo)達(dá)標(biāo)線是10000元IF(A110000,達(dá)標(biāo),未達(dá)標(biāo))這個(gè)公式的意思是如果A1的值大于等于10000返回達(dá)標(biāo)否則返回未達(dá)標(biāo)。注意第二、第三參數(shù)不一定是文本可以是數(shù)字、公式甚至可以是另一個(gè)IF函數(shù)。1.2 三個(gè)參數(shù)的作用與常見誤區(qū)IF函數(shù)的參數(shù)結(jié)構(gòu)是這樣的參數(shù)名稱作用示例第一參數(shù)邏輯測(cè)試返回TRUE或FALSE的條件表達(dá)式A110000第二參數(shù)真值返回條件成立時(shí)返回的內(nèi)容達(dá)標(biāo)第三參數(shù)假值返回條件不成立時(shí)返回的內(nèi)容未達(dá)標(biāo)新手最容易犯的一個(gè)錯(cuò)誤是第二參數(shù)或第三參數(shù)留空不填以為這樣就是什么都不返回。實(shí)際上留空會(huì)返回0在很多報(bào)表里會(huì)變成讓人摸不著頭腦的0值。正確做法是寫成空字符串IF(A110000,達(dá)標(biāo),)這表示不達(dá)標(biāo)時(shí)返回一個(gè)空文本單元格看起來(lái)是空的但又不會(huì)真的為空它是有公式的。這個(gè)細(xì)節(jié)在后續(xù)做數(shù)據(jù)透視表、條件格式時(shí)相當(dāng)重要因?yàn)榭瘴谋緯?huì)被計(jì)數(shù)而真空單元格不會(huì)被計(jì)數(shù)兩者性質(zhì)完全不同。另一個(gè)常見誤區(qū)是第一參數(shù)明明已經(jīng)是一個(gè)布爾值了卻還要再拿它和TRUE比較。比如寫IF((A110000)TRUE,達(dá)標(biāo),未達(dá)標(biāo))這樣寫沒錯(cuò)但純屬多此一舉。直接寫IF(A110000,達(dá)標(biāo),未達(dá)標(biāo))就夠了。還有一個(gè)容易被忽略的點(diǎn)IF函數(shù)的第一參數(shù)對(duì)數(shù)字的處理方式。如果你寫IF(1,成立,不成立)Excel會(huì)認(rèn)為1代表TRUE返回成立同理0代表FALSE返回不成立。在實(shí)際應(yīng)用中這個(gè)特性可以用來(lái)判斷單元格是否包含數(shù)值比如IF(A1,有數(shù)字,無(wú)數(shù)字)但這里有個(gè)隱患——如果A1是文本蘋果這個(gè)公式反而會(huì)返回#VALUE!錯(cuò)誤。所以除非你很清楚單元格里只有數(shù)字否則建議還是用ISNUMBER(A1)這樣的函數(shù)來(lái)判斷更穩(wěn)妥。2. 多條件與嵌套IF函數(shù)升級(jí)的第一道坎2.1 嵌套的基本結(jié)構(gòu)當(dāng)業(yè)務(wù)規(guī)則不止一重判斷時(shí)就需要用到嵌套。所謂嵌套就是在IF函數(shù)的第二參數(shù)或第三參數(shù)里再放一個(gè)IF函數(shù)。比如最常見的等級(jí)評(píng)定場(chǎng)景將90分以上評(píng)為優(yōu)80至89分為良70至79分為中60至69分為及格60分以下為不及格。如果用嵌套來(lái)寫常規(guī)做法是逐層展開IF(A190,優(yōu),IF(A180,良,IF(A170,中,IF(A160,及格,不及格))))這種寫法的邏輯起點(diǎn)是先把最高檔條件判斷完如果成立就直接返回不成立再進(jìn)入下一層判斷。一層一層剝開直到所有條件都判斷完。這里有一個(gè)重要的優(yōu)化思路嵌套順序會(huì)影響公式的簡(jiǎn)潔程度。通常建議把范圍最大的、最不可能成立的條件放前面這樣可以讓公式在大多數(shù)情況下快速返回結(jié)果減少不必要的計(jì)算。此外在寫多層嵌套時(shí)一定要養(yǎng)成從外層到內(nèi)層逐行縮進(jìn)的習(xí)慣。Excel公式欄里按AltEnter可以換行多層嵌套時(shí)把每個(gè)IF對(duì)整齊出錯(cuò)時(shí)排查起來(lái)能省很多時(shí)間。2.2 用AND和OR簡(jiǎn)化多條件判斷有時(shí)候一個(gè)判斷需要同時(shí)滿足多個(gè)條件或者滿足多個(gè)條件中的任意一個(gè)。這時(shí)候與其層層嵌套不如直接引入AND和OR函數(shù)。AND函數(shù)的特點(diǎn)是所有參數(shù)都為TRUE時(shí)才返回TRUE只要有任何一個(gè)為FALSE就返回FALSE。OR函數(shù)的特點(diǎn)是只要有一個(gè)參數(shù)為TRUE就返回TRUE全部為FALSE才返回FALSE。舉個(gè)實(shí)際例子公司規(guī)定業(yè)績(jī)大于等于10000并且出勤天數(shù)大于等于22天才能拿全勤獎(jiǎng)。寫成公式就是IF(AND(B110000,C122),全勤獎(jiǎng),無(wú))這里的B1是業(yè)績(jī)C1是出勤天數(shù)。只要業(yè)績(jī)和出勤天數(shù)中有一個(gè)不達(dá)標(biāo)就返回?zé)o。反過來(lái)如果只想判斷業(yè)績(jī)達(dá)標(biāo)或者出勤達(dá)標(biāo)兩者至少滿足一個(gè)就發(fā)放獎(jiǎng)勵(lì)那就改成IF(OR(B110000,C122),有獎(jiǎng)勵(lì),無(wú))AND和OR可以理解為批量打包的函數(shù)它們把多個(gè)條件組合成一個(gè)布爾值再交給IF去判斷。使用它們的最大好處是避免多層嵌套帶來(lái)的可讀性災(zāi)難。我見過有人用五個(gè)嵌套IF去實(shí)現(xiàn)多個(gè)條件全滿足的判斷公式看上去像一個(gè)迷宮。而用AND一個(gè)函數(shù)就能輕松解決邏輯也更清晰。需要注意的是AND和OR可以搭配使用的。比如業(yè)績(jī)達(dá)標(biāo)且出勤達(dá)標(biāo)或有特殊貢獻(xiàn)這樣的復(fù)合條件可以這樣寫IF(OR(AND(B110000,C122),D1特殊貢獻(xiàn)),獲獎(jiǎng),無(wú))只要括號(hào)配對(duì)正確這類組合也能實(shí)現(xiàn)相當(dāng)復(fù)雜的業(yè)務(wù)規(guī)則。2.3 IFS函數(shù)多層嵌套的替代方案如果你用的是Office 365、Excel 2021及以上版本或者WPS較新版本還有一個(gè)IFS函數(shù)可以大幅簡(jiǎn)化多層嵌套。IFS函數(shù)從新版本開始引入專門用來(lái)替代多個(gè)IF反復(fù)嵌套的場(chǎng)景。它的結(jié)構(gòu)是一對(duì)對(duì)參數(shù)依次排列第一對(duì)是條件第二對(duì)是滿足該條件時(shí)的返回內(nèi)容然后接著第二對(duì)條件再是它的返回內(nèi)容……直到所有情況都覆蓋最后還可以加一個(gè)默認(rèn)返回值新版本支持的參數(shù)。比如剛才的多層評(píng)級(jí)用IFS寫就是IFS(A190,優(yōu),A180,良,A170,中,A160,及格,TRUE,不及格)最后一對(duì)TRUE,不及格的意思是以上所有條件都不滿足時(shí)返回不及格。這個(gè)TRUE在這里相當(dāng)于否則的兜底寫效果等同于嵌套IF里最后一層的假值參數(shù)。IFS的優(yōu)勢(shì)很明顯公式結(jié)構(gòu)更整齊不用逐層包裹括號(hào)閱讀時(shí)也不用看到一堆嵌套層級(jí)。但國(guó)人的使用習(xí)慣還是比較偏保守老版本Excel兼容性問題導(dǎo)致很多人還在用嵌套IF。我的建議是如果只是自己在用版本支持就用IFS如果公式要發(fā)給同事、客戶要考慮對(duì)方可能用舊版Excel就老老實(shí)實(shí)用嵌套IF。兼容性始終是辦公自動(dòng)化中最現(xiàn)實(shí)的考量。2.4 用查找替代IF巧用CHOOSE和LOOKUP碰到條件特別多、層級(jí)特別深的情況還有一個(gè)思路是跳出IF本身用查找類函數(shù)替代。比如要根據(jù)分?jǐn)?shù)段返回等級(jí)除了嵌套IF和IFS還可以用LOOKUP配合一個(gè)分?jǐn)?shù)區(qū)間表。這時(shí)可以建立一個(gè)輔助區(qū)域比如在E列和F列配置如下E10 F1不及格 E260 F2及格 E370 F3中 E480 F4良 E590 F5優(yōu)然后公式寫成LOOKUP(A1,E$1:F$5)LOOKUP函數(shù)會(huì)從E列的數(shù)值里找到小于等于A1的最大值然后返回對(duì)應(yīng)的F列內(nèi)容。這種方式的好處是業(yè)務(wù)規(guī)則變更時(shí)只需修改輔助區(qū)域不用改動(dòng)公式本身。這在大規(guī)模模板中特別實(shí)用。3. 與統(tǒng)計(jì)、查找、日期函數(shù)組合的實(shí)戰(zhàn)玩法3.1 IF加SUMIF/COUNTIF按條件統(tǒng)計(jì)再判斷IF函數(shù)不只是自己在單元格里輸出一個(gè)結(jié)果它還可以作為結(jié)果判斷器配合其他統(tǒng)計(jì)函數(shù)完成條件匯總再根據(jù)匯總結(jié)果給出判斷。舉個(gè)例子銷售表里有多個(gè)產(chǎn)品線我們想知道某個(gè)產(chǎn)品線是否達(dá)到公司給定的業(yè)績(jī)預(yù)警線?？梢韵葏R總再判斷IF(SUMIF(A:A,產(chǎn)品A,B:B)50000,達(dá)標(biāo),需要關(guān)注)這里的SUMIF先把A列中所有產(chǎn)品A對(duì)應(yīng)的B列數(shù)值加總?cè)缓笥肐F判斷匯總結(jié)果是否大于50000。同樣的邏輯也適用于COUNTIF。比如統(tǒng)計(jì)某個(gè)部門的人數(shù)是否超過編制上限IF(COUNTIF(A:A,銷售部)30,超編,正常)COUNTIF計(jì)算A列中等于銷售部的單元格個(gè)數(shù)然后交給IF判斷是否大于30。這個(gè)組合在實(shí)際人事管理、庫(kù)存管理里非常高頻。這里的核心思路是IF函數(shù)的判斷依據(jù)可以是一個(gè)函數(shù)計(jì)算的結(jié)果。很多人寫公式時(shí)只想著用單元格直接比較忽略了把統(tǒng)計(jì)函數(shù)放進(jìn)判斷條件的可能性這其實(shí)浪費(fèi)了IF函數(shù)一大半的能力。3.2 IF和VLOOKUP組合反向查找與容錯(cuò)VLOOKUP是查找函數(shù)里的老大哥但它有幾個(gè)先天限制只能從左往右查找而且查找不到時(shí)會(huì)返回#N/A錯(cuò)誤。這兩個(gè)問題都可以用IF來(lái)彌補(bǔ)。先說反向查找。假設(shè)原始數(shù)據(jù)是工號(hào)在B列、姓名在A列我們要根據(jù)姓名找工號(hào)標(biāo)準(zhǔn)的VLOOKUP沒法直接反向查找。用IF構(gòu)造一個(gè)臨時(shí)數(shù)組把兩列順序調(diào)換VLOOKUP(張三,IF({1,0},A:A,B:B),2,0)這個(gè)公式里的IF({1,0},A:A,B:B)是個(gè)數(shù)組公式寫法后面我會(huì)專門講。簡(jiǎn)單理解就是IF函數(shù)根據(jù){1,0}這個(gè)常量數(shù)組分別取出A列和B列然后重新排成姓名在前、工號(hào)在后的新數(shù)組VLOOKUP就能正常查找了。再說容錯(cuò)。VLOOKUP找不到數(shù)據(jù)時(shí)返回#N/A直接嵌套在IF外面可以顯示友好提示IF(ISNA(VLOOKUP(D1,A:B,2,0)),查無(wú)此人,VLOOKUP(D1,A:B,2,0))這里ISNA函數(shù)判斷VLOOKUP的結(jié)果是否是#N/A錯(cuò)誤如果是就返回查無(wú)此人否則就正常返回查找結(jié)果。這種寫法在制作查詢面板時(shí)很常見能避免滿屏的#N/A把表格變得沒法看。順帶說一句處理查找錯(cuò)誤還有個(gè)更簡(jiǎn)潔的函數(shù)是IFERROR后面單獨(dú)講但I(xiàn)F加ISNA的寫法有一個(gè)好處它只屏蔽#N/A錯(cuò)誤其他類型錯(cuò)誤比如#VALUE!依然會(huì)暴露出來(lái)方便排查數(shù)據(jù)問題。IFERROR則是無(wú)論什么錯(cuò)誤都包住兩者各有適用場(chǎng)景。3.3 日期判斷到期提醒與超期標(biāo)記IF函數(shù)處理日期判斷時(shí)最核心的一點(diǎn)是Excel里的日期本質(zhì)上是一個(gè)數(shù)字序列。比如2024年1月1日存的是數(shù)字452922025年1月1日是45658。所以日期可以直接用來(lái)比較大小。做一個(gè)合同到期提醒。假設(shè)C列是合同到期日要在D列判斷合同是否在30天內(nèi)到期IF(C1-TODAY()30,即將到期,未到期)這里TODAY()返回當(dāng)天日期作為數(shù)字參與運(yùn)算C1減去TODAY()得到剩余天數(shù)如果小于等于30則標(biāo)記即將到期。為了安全起見最好還要判斷這個(gè)差值是否為負(fù)數(shù)即已經(jīng)到期IF(C1-TODAY()0,已到期,IF(C1-TODAY()30,即將到期,未到期))這個(gè)邏輯和前面講的多層嵌套是一樣的先判斷是否過期再判斷是否臨近。日歷計(jì)算還有一個(gè)常用場(chǎng)景是判斷某個(gè)日期是工作日還是周末IF(WEEKDAY(A1,2)5,周末,工作日)WEEKDAY函數(shù)第二參數(shù)用2表示周一為1、周日為7。大于5即為周六或周日。這個(gè)判斷經(jīng)常用在排班表、考勤表的自動(dòng)標(biāo)記里。如果涉及復(fù)雜的節(jié)假日判斷IF函數(shù)單獨(dú)搞不定通常需要配上節(jié)假日列表和MATCH等函數(shù)但那些屬于更高級(jí)的應(yīng)用了。單純用IF做日期的到期提醒、超期標(biāo)記已經(jīng)能解決大量日常問題。4. 硬核進(jìn)階數(shù)組公式里的IF函數(shù)4.1 IF({1,0}到底是怎么回事前面提到的IF({1,0},A:A,B:B)很多老手都在用但問起來(lái)未必能講清楚原理。我來(lái)拆解一下。IF函數(shù)的判斷條件可以是一個(gè)數(shù)組而不只一個(gè)值。{1,0}是一個(gè)一行兩列的常量數(shù)組第一個(gè)元素是1代表TRUE第二個(gè)元素是0代表FALSE。當(dāng)IF函數(shù)的第一參數(shù)是數(shù)組時(shí)它會(huì)對(duì)數(shù)組的每個(gè)元素分別判斷然后返回一個(gè)同樣大小的數(shù)組?；氐竭@個(gè)公式IF({1,0},A:A,B:B)它相當(dāng)于同時(shí)執(zhí)行兩個(gè)判斷當(dāng)條件為1TRUE時(shí)取A列的內(nèi)容當(dāng)條件為0FALSE時(shí)取B列的內(nèi)容結(jié)果是一個(gè)兩列的內(nèi)存數(shù)組第一列是A列數(shù)據(jù)第二列是B列數(shù)據(jù)。也就是說A列和B列被重新排列了順序。這個(gè)方法的核心應(yīng)用場(chǎng)景就是解決VLOOKUP只能從左往右查找的問題。如果原始數(shù)據(jù)是姓名在B列工號(hào)在A列想按姓名找工號(hào)就可以用IF函數(shù)把兩列位置調(diào)換讓姓名在左、工號(hào)在右VLOOKUP的正常工作前提就滿足了。4.2 用IF生成內(nèi)存數(shù)組參與統(tǒng)計(jì)除了調(diào)換列序IF函數(shù)還能按條件生成內(nèi)存數(shù)組再配合SUM等聚合函數(shù)實(shí)現(xiàn)多條件求和。這比傳統(tǒng)的SUMIFS更靈活尤其適合處理復(fù)雜的邏輯判斷。舉個(gè)例子統(tǒng)計(jì)A列中大于100且B列小于50的數(shù)據(jù)對(duì)應(yīng)的C列總和。用SUMIFS可以寫但用IF數(shù)組可以做到更自由的組合SUM(IF((A2:A100100)*(B2:B10050),C2:C100,0))注意這個(gè)公式在舊版Excel里需要按CtrlShiftEnter輸入在老版本公式欄里會(huì)顯示成{SUM(...)}。在新版Excel365/2021里直接回車就行動(dòng)態(tài)數(shù)組會(huì)自動(dòng)處理。這里的核心邏輯是(A2:A100100)*(B2:B10050)產(chǎn)生一個(gè)由TRUE和FALSE組成的數(shù)組相乘運(yùn)算把TRUE轉(zhuǎn)成1、FALSE轉(zhuǎn)成0。IF函數(shù)根據(jù)這個(gè)數(shù)組逐行判斷C列對(duì)應(yīng)位置是否納入求和符合的返回C列值不符合的返回0最后SUM求和。這種寫法比多個(gè)SUMIFS嵌套更靈活因?yàn)樗梢栽贗F條件里直接使用復(fù)雜的邏輯表達(dá)式。比如A列100且B列50或C列重點(diǎn)這類條件用SUMIFS寫起來(lái)反而麻煩用數(shù)組IF處理反而直觀。4.3 IF處理文本拆分與提取IF函數(shù)和數(shù)組的結(jié)合還能做文本處理比如把一個(gè)單元格里的多個(gè)關(guān)鍵詞拆出來(lái)或者按條件提取字符串中的某一段。舉一個(gè)經(jīng)典的例子某單元格A1內(nèi)容是蘋果,香蕉,橙子現(xiàn)在要根據(jù)另一個(gè)單元格B1中的關(guān)鍵詞香蕉判斷A1里是否包含它并提取出來(lái)。一般需要用SEARCH和IF組合IF(ISNUMBER(SEARCH(香蕉,A1)),包含,不包含)SEARCH會(huì)從A1中查找香蕉的位置如果找到就返回一個(gè)數(shù)字ISNUMBER判斷這個(gè)結(jié)果是否為數(shù)字然后交給IF輸出結(jié)論。這個(gè)八竿子打不著的組合在實(shí)際應(yīng)用中配合數(shù)據(jù)清洗非常有用。更進(jìn)階的場(chǎng)景如果A列里有一堆城市名要判斷是否屬于華東區(qū)域如果屬于就標(biāo)記區(qū)域名否則返回其他?？梢杂脭?shù)組公式配合OR實(shí)現(xiàn)IF(OR(ISNUMBER(SEARCH({上海,江蘇,浙江,安徽},A1))),華東,其他)這里SEARCH的查找條件是數(shù)組返回一個(gè)由數(shù)字和錯(cuò)誤值組成的數(shù)組ISNUMBER把它轉(zhuǎn)成布爾數(shù)組OR判斷其中是否有任何一個(gè)成立。整個(gè)公式像一個(gè)小型分類器不需要維護(hù)繁瑣的嵌套IF。當(dāng)然處理這類問題在現(xiàn)代版本里還有更專業(yè)的TEXTJOIN、CHOOSECOLS等函數(shù)但I(xiàn)F配合SEARCH的組合在兼容性上更穩(wěn)老版本也能用。在寫這種公式時(shí)要注意SEARCH不分大小寫如果需要區(qū)分大小寫用FIND代替SEARCH。5. 高概率踩坑場(chǎng)景與排查技巧5.1 比較運(yùn)算符的方向搞反這是新手最常見的問題而且往往自查半天看不出來(lái)。比如要判斷成績(jī)是否小于60分很容易寫成IF(A160,不及格,及格)方向反了結(jié)果完全錯(cuò)位。這種問題用眼睛很難發(fā)現(xiàn)尤其是在公式很多、數(shù)據(jù)量大的情況下。我的排查習(xí)慣是每次寫條件判斷之前先在腦子里用具體數(shù)字過一遍。比如拿A158這個(gè)不及格的數(shù)字代入公式里看該返回什么。如果A158代入A160是FALSE公式會(huì)返回及格顯然不對(duì)。另外一個(gè)方向陷阱是邊界值的歸屬。比如60分以上為及格這里的以上包不包括60用A160還是A160不同業(yè)務(wù)里邊界定義可能不同一定要在公式里明確。肉眼比對(duì)數(shù)據(jù)完全看不出差別的但抽樣幾個(gè)邊界值測(cè)試就能暴露。5.2 文本型數(shù)字與真數(shù)字的混淆Excel里有一類特殊數(shù)據(jù)看起來(lái)是數(shù)字但存儲(chǔ)為文本格式。這類數(shù)據(jù)在參與IF函數(shù)的比較運(yùn)算時(shí)經(jīng)常會(huì)出現(xiàn)詭異的結(jié)果。比如A1單元格左上角有個(gè)綠色小三角內(nèi)容是100文本格式用公式IF(A1100,達(dá)標(biāo),未達(dá)標(biāo))很可能返回未達(dá)標(biāo)因?yàn)槲谋?00和數(shù)字100比較時(shí)Excel的規(guī)則是文本永遠(yuǎn)大于數(shù)字于是判斷結(jié)果就不符合預(yù)期。遇到這種情況可以先統(tǒng)一數(shù)據(jù)格式。用公式規(guī)避的話可以在比較前用VALUE函數(shù)把文本轉(zhuǎn)成數(shù)字IF(VALUE(A1)100,達(dá)標(biāo),未達(dá)標(biāo))或者用N函數(shù)針對(duì)數(shù)字文本可以不嚴(yán)謹(jǐn)?shù)剞D(zhuǎn)換但最穩(wěn)妥的還是把單元格格式調(diào)成常規(guī)重新錄入一遍。這個(gè)問題在從其他系統(tǒng)導(dǎo)入的數(shù)據(jù)里特別常見我處理過太多公式明明沒寫錯(cuò)結(jié)果卻不對(duì)的案例最后查下來(lái)都是文本數(shù)字在作祟。5.3 嵌套層級(jí)超過上限與公式長(zhǎng)度失控老版本Excel里IF函數(shù)最多嵌套7層超過就報(bào)此函數(shù)的參數(shù)過多。雖然有新函數(shù)IFS、SWITCH等替代方案出現(xiàn)但兼容舊版時(shí)仍然可能碰到限制。如果遇到嵌套層數(shù)爆表的情況有幾個(gè)解決思路改用IFS或SWITCH如果版本支持用LOOKUP配合區(qū)間表前面介紹過把條件拆到輔助列多個(gè)單元格分步計(jì)算用CHOOSE函數(shù)配合MATCH做索引還有一個(gè)普遍問題是公式太長(zhǎng)難以維護(hù)。一個(gè)三四十層的IF嵌套寫出來(lái)后面的人看起來(lái)完全像在讀天書。所以在我的工作習(xí)慣里遇到超過5層嵌套的條件判斷會(huì)主動(dòng)考慮是不是可以用輔助列或查找表來(lái)替代。代碼可讀性在Excel公式里同樣重要。5.4 錯(cuò)誤值處理IFERROR和ISERROR的取舍IF函數(shù)和錯(cuò)誤值的關(guān)系是另一個(gè)高頻場(chǎng)景。當(dāng)公式的計(jì)算過程中出現(xiàn)錯(cuò)誤值如#DIV/0!、#VALUE!、#N/A等IF函數(shù)本身并不會(huì)自動(dòng)處理它只會(huì)原樣返回錯(cuò)誤值。這就有了IFERROR和ISERROR兩個(gè)處理工具。IFERROR相對(duì)一刀切IFERROR(原公式,出錯(cuò)啦)無(wú)論什么錯(cuò)誤類型都會(huì)被捕獲并替換成指定內(nèi)容。優(yōu)點(diǎn)是簡(jiǎn)潔缺點(diǎn)是會(huì)掩蓋所有錯(cuò)誤包括公式本身的邏輯錯(cuò)誤。比如公式里單元格引用寫錯(cuò)了返回#REF!用IFERROR一包也顯示出錯(cuò)啦反而不利于排查。ISERROR配合IF函數(shù)可以更精細(xì)地控制IF(ISERROR(原公式),錯(cuò)誤,原公式)看起來(lái)和IFERROR差不多但它的應(yīng)用更靈活。比如可以只針對(duì)#N/A顯示特定提示其他錯(cuò)誤放出來(lái)IF(ISNA(VLOOKUP(...)),未找到,VLOOKUP(...))這樣可以做到只有查找不到時(shí)才顯示提示其他真正的計(jì)算錯(cuò)誤繼續(xù)暴露。5.5 用公式求值排查邏輯錯(cuò)誤當(dāng)公式很長(zhǎng)、邏輯很繞時(shí)肉眼檢查很容易漏。Excel自帶的公式求值功能是排查IF邏輯錯(cuò)誤的好幫手。操作路徑是選中公式單元格點(diǎn)擊公式選項(xiàng)卡里的公式求值Excel會(huì)一步一步展示公式的計(jì)算過程。每一步都能看到當(dāng)前的中間結(jié)果比如第一參數(shù)的計(jì)算結(jié)果是TRUE還是FALSEIF函數(shù)選擇了哪條分支。這個(gè)方法比人眼核對(duì)高效得多。更進(jìn)一步的排查方式是把公式拆開。比如把IF函數(shù)的第一參數(shù)單獨(dú)提取到一個(gè)單元格里直接看這個(gè)判斷式的結(jié)果是TRUE還是FALSE。如果判斷式本身結(jié)果都不對(duì)就不用糾結(jié)后面的返回內(nèi)容了問題一定出在條件判斷的寫法上。還有一個(gè)小工具是使用F9鍵。在公式欄里選中某一段代碼比如選中整個(gè)A1100按F9Excel會(huì)計(jì)算出該段的結(jié)果并顯示出來(lái)?？赐旰蟀碋sc退出公式不會(huì)變。這個(gè)技巧適合快速驗(yàn)證公式中的任意一段是否按預(yù)期工作。5.6 一個(gè)容易忽視的性能問題大范圍數(shù)組公式中的IF函數(shù)可能會(huì)拖慢工作表計(jì)算速度。比如在第5節(jié)里提到的數(shù)組IF統(tǒng)計(jì)公式如果用整列引用A:A而不是限定數(shù)據(jù)范圍A2:A1000Excel可能會(huì)對(duì)幾萬(wàn)個(gè)空白單元格也執(zhí)行判斷操作計(jì)算效率會(huì)明顯下降。我的建議是寫數(shù)組公式時(shí)盡量縮小引用范圍只覆蓋有數(shù)據(jù)的區(qū)域。如果區(qū)域會(huì)動(dòng)態(tài)變化可以用Excel表格CtrlT創(chuàng)建的超級(jí)表讓公式自動(dòng)調(diào)整范圍或者用OFFSET、INDEX等函數(shù)動(dòng)態(tài)劃定區(qū)域。在數(shù)據(jù)量幾萬(wàn)行時(shí)這個(gè)細(xì)節(jié)差異會(huì)非常明顯——一個(gè)秒開一個(gè)卡幾秒。個(gè)人操作體會(huì)IF函數(shù)不只是邏輯判斷寫了這么多年Excel公式我越來(lái)越覺得IF函數(shù)不只是一個(gè)工具它更像一種思維方式。遇到一個(gè)復(fù)雜業(yè)務(wù)場(chǎng)景第一反應(yīng)不是去翻有沒有專門的函數(shù)而是先想清楚判斷條件是什么、成立返回什么、不成立返回什么有了這個(gè)框架再?gòu)?fù)雜的需求都能逐步拆解成一個(gè)個(gè)清晰的IF分支。我也刻意練習(xí)過一件事盡量少用嵌套IF多用輔助列。很多人覺得輔助列不優(yōu)雅一心想用一個(gè)公式搞定所有事。但在實(shí)際工作里輔助列能大幅提高公式的可讀性和可維護(hù)性。三個(gè)月后你自己回去看那張表輔助列能讓你的思路一目了然而一口氣寫完的長(zhǎng)嵌套則可能讓自己都讀不懂。最后分享一個(gè)小技巧給IF函數(shù)的返回值加上統(tǒng)一的前綴或符號(hào)會(huì)讓后續(xù)篩選、透視更方便。比如判斷結(jié)果返回達(dá)標(biāo)?未達(dá)標(biāo)?。這里的符號(hào)實(shí)際使用中可以換成公司內(nèi)部約定的標(biāo)識(shí)這樣條件格式和篩選一眼就能定位到需要關(guān)注的記錄。我在做績(jī)效表、庫(kù)存預(yù)警表時(shí)都用了這個(gè)小技巧效率提升很明顯。如果你手頭正有某個(gè)用if判斷寫起來(lái)特別痛苦的場(chǎng)景不妨按照這篇文章的思路重新拆一遍條件再?zèng)Q定用嵌套、IFS還是查找表。邏輯理順了公式自然就順了。