函數(shù)是 Excel 的精髓，也是 Excel 最基礎(chǔ)的、最需要學(xué)習(xí)掌握的知識。

可以說用好它，就能解決你工作中 90%的問題。

絕大多數(shù)小伙伴們對于一些簡單的函數(shù)應(yīng)用比較熟練。比如 SUM 求和，COUNT 計數(shù)等等。
但是，一旦看到嵌套的函數(shù)，立馬懵圈。

那些復(fù)雜的數(shù)組公式，猶如天書一般。這不，下面這個問題，看起來容易，做起來還得下一番功夫。

如下圖：是一個產(chǎn)品庫存的統(tǒng)計表，

要求是：統(tǒng)計出每一種產(chǎn)品是【斷碼】還是【齊碼】。

具體的統(tǒng)計規(guī)則是：

只要碼數(shù) XS 至 XXL 中連續(xù)三個單元格中有數(shù)字的，就代表有貨，算是【齊碼】，否則為【斷碼】。

分析問題

從上圖中可以看出：

在【C2:H2】單元格區(qū)域中，【C2:E2】連續(xù)三個單元格都有貨，就算【齊碼】。

在【C5:H5】單元格區(qū)域中，【D5:F5】連續(xù)三個單元格都有貨，就算【齊碼】。

而【C3:H3】、【C4:H4】、【C6:H6】單元格區(qū)域中沒有符合「連續(xù)三個單元格都有貨」這個條件的，就是【斷碼】。

這樣的解釋想必大家都明白了吧。

那下面我們就來看看，如果有大量數(shù)據(jù)，如何用 Excel 快速解決這個問題？

解決問題

這里，我們將 OFFSET 偏移函數(shù)，和 COUNTIF 計數(shù)函數(shù)結(jié)合使用。

【I2】單元格的公式如下：

• =IF(OR(COUNTIF(OFFSET(B2:D2,0,{1;2;3;4}),">0")=3),"齊碼","斷碼")
  

公式解析：

? OFFSET(B2:D2,0,{1;2;3;4})

先將原點位置定位在【B2:D2】這三個單元格區(qū)域，然后向下不偏移，向右分別偏移 1，2，3，4 個單元格。

即返回：【C2:E2】、【D2:F2】、【E2:G2】、【F2:H2】這四個單元格區(qū)域。
??COUNTIF(?,">0")

使用 COUNTIF 條件計數(shù)函數(shù)將步驟 1 中生成的四個區(qū)域分別統(tǒng)計大于 0 的個數(shù)。
返回四個值，結(jié)果分別是：{3;2;1;1}。

??OR(?=3)

將步驟 2 返回的四個結(jié)果分別和 3 進(jìn)行比較，如果相等就返回 TRUE，不相等就返回 FALSE。

結(jié)果是：{TRUE;FALSE;FALSE;FALSE}

再用 OR 函數(shù)取出其中有 TRUE 的結(jié)果。只要有一個結(jié)果是 TRUE，OR 函數(shù)就返回 TRUE。

如果結(jié)果都是 FALSE，OR 函數(shù)就返回 FALSE。

此處 OR 函數(shù)結(jié)果返回 TRUE。

??IF(?,"齊碼","斷碼")
最后用 IF 條件判斷函數(shù)判斷返回第二參數(shù)或者第三參數(shù)。

如果第一參數(shù)是 TRUE，就返回第二參數(shù)：齊碼，否則返回第三參數(shù)：斷碼。

這里返回結(jié)果是：齊碼。

這樣一步一步拆解復(fù)雜的函數(shù)嵌套，就好理解了吧！

當(dāng)然，此時小伙伴們可能是：眼睛會了，但手還不會。

要想一下子寫出來這樣的公式，沒有幾個月的學(xué)習(xí)，根本做不出來。

其實，對于我們這些小白白們來說，倒是可以換一個角度考慮問題。

我們是不是可以把這個復(fù)雜的函數(shù)嵌套問題轉(zhuǎn)為化一個簡單的函數(shù)問題呢？

答案當(dāng)然是可以的！

如何轉(zhuǎn)換呢？

大體的思路是：
先建立一個輔助區(qū)域，對原來的數(shù)據(jù)區(qū)域進(jìn)行一個簡單的統(tǒng)計，

最后在結(jié)果區(qū)域?qū)@個輔助區(qū)域再進(jìn)行一次簡單的判斷，即可得出結(jié)果。

如下圖：

我們在【K2】輸入公式：

• =COUNT(C2:E2)
  

公式解析：

統(tǒng)計出【C2:E2】這三個單元格中的數(shù)字。

有幾個數(shù)字就返回幾。這里有三個數(shù)字，就返回 3。

并將此公式向右填充到【N2】單元格，向下填充到【N6】單元格。

這里只要統(tǒng)計到【N2】單元格就行，因為這個【N2】的公式是：

• =COUNT(F2:H2)
  

正好是最后三個碼數(shù)的單元格。

這個輔助區(qū)域的結(jié)果如下：

然后在【I2】中輸入如下公式：

• =IF(COUNTIF(K2:N2,3),"齊碼","斷碼")
  

公式解析：
? COUNTIF(K2:N2,3)

先用 COUNTIF 函數(shù)統(tǒng)計在【K2:N2】這個單元格區(qū)域中是否有數(shù)字 3，如果有，就返回大于 0 的數(shù)字，如果沒有就返回 0 值。

然后用 IF 判斷返回齊碼或者斷碼↓↓↓

? =IF(?,"齊碼","斷碼")，返回"齊碼"。

怎么樣，用一個輔助區(qū)域+一個 COUNT 函數(shù)+一個 COUNTIF 函數(shù)+IF 函數(shù)，這些簡單的函數(shù)組合就能解決上面那么復(fù)雜的函數(shù)嵌套組合問題。

這是不是你解決問題的好思路呢？

知識擴展

這里再介紹一種函數(shù)（FREQUENCY）的解決方法，助喜歡函數(shù)的小伙伴們拓展思路。
這個函數(shù)是用來統(tǒng)計頻率、次數(shù)的函數(shù)。

它有兩個參數(shù)。

• =FREQUENCY(data_array,bins_array)
  

第一參數(shù)是數(shù)據(jù)源，第二參數(shù)是分?jǐn)帱c。

如下圖：

【I2】公式如下：

• =IF(OR(FREQUENCY(IF(C2:H2>0,COLUMN(C:H)),IF(C2:H2="",COLUMN(C:H)))>=3),"齊碼","斷碼")
  

公式解析：
這里的核心公式是↓↓↓

FREQUENCY(IF(C2:H2>0,COLUMN(C:H)),IF(C2:H2="",COLUMN(C:H)))

? ?IF(C2:H2>0,COLUMN(C:H))

如果【C2:H2】大于零，就返回其所在的列號，否則返回 FALSE。

結(jié)果如下：
{3,4,5,FALSE,FALSE,8}

? ?IF(C2:H2="",COLUMN(C:H))

如果【C2:H2】等于空，就返回其所在的列號，否則返回 FALSE。

結(jié)果如下：
{FALSE,FALSE,FALSE,6,7,FALSE}

?FREQUENCY({3,4,5,FALSE,FALSE,8},{FALSE,FALSE,FALSE,6,7,FALSE})

將第一步和第二步的結(jié)果返回給 FREQUENCY。

這個函數(shù)的統(tǒng)計原理是：
第二參數(shù)的數(shù)字作為分?jǐn)帱c，忽略 FALSE 邏輯值。

分?jǐn)帱c 6：如果在第一參數(shù)中小于等于 6，將統(tǒng)計在內(nèi)，它們分別是 3，4，5，結(jié)果返回 3。

分?jǐn)帱c 7：大于 6 并且小于等于 7 的數(shù)字，在第一參數(shù)中沒有，結(jié)果返回 0。

最后還要統(tǒng)計大于 7 以上的數(shù)字的個數(shù)，在第一參數(shù)中有一個數(shù)字 8，結(jié)果返回 1。

最終該函數(shù)返回結(jié)果如下：
{3;0;1}

剩下的判斷就跟上面的一樣了，這里不再多說了。

這個函數(shù)的應(yīng)用也非常廣泛，小伙伴們可以抽空好好研究下。

寫在最后

今天，我們分享了如何對連續(xù)的多個單元格區(qū)域進(jìn)行統(tǒng)計的問題。

? 分別運用了 OFFSET 偏移函數(shù)+COUNTIF 條件計數(shù)。

? 輔助區(qū)域+ COUNT 計數(shù)和 COUNTIF 條件計數(shù)。

? FREQUENCY 計算出現(xiàn)的頻率次數(shù)的函數(shù)。

每個函數(shù)都有他們的用武之地。

雖然他們的使用方法不一樣，但是卻都能達(dá)到同樣的結(jié)果。

對于小白們，建議多多使用輔助區(qū)域來解決問題，更容易操作哦~

如果你還想學(xué)習(xí)更多更實用的 Excel 函數(shù)、更快更簡單的表格操作技巧。

剛好，秋葉《?3 天?Excel 集訓(xùn)營》專為職場職場人準(zhǔn)備，全部基于職場真實表格案例設(shè)計，超實用 Excel 技巧集合教學(xué)。

3 天錄播+直播，每天只需學(xué)習(xí) 30 分鐘左右，你也有機會成為 Excel 高手！

《3 天?Excel 集訓(xùn)營》想提升，趁現(xiàn)在！

不再被加班支配，不再為表格發(fā)愁！

現(xiàn)在報名，免費贈送【35 個常用函數(shù)說明】【100+套 Excel 模板】還等什么，趕緊掃碼吧！??????

*廣告