大家好，我是徐軍泰——有講特約講師、唯庫(kù)函數(shù)課《一次性學(xué)透Excel函數(shù)，5分鐘搞定一天工作量》的主講老師。
今天分享的主題是——Excel中的數(shù)組公式應(yīng)用。
函數(shù)是Excel中最核心的功能，很多的操作和應(yīng)用（例如數(shù)據(jù)處理、統(tǒng)計(jì)分析）都要涉及到函數(shù)的使用。
函數(shù)能夠幫我們更快速的解決各類問(wèn)題，例如一個(gè)需要1個(gè)小時(shí)完成的數(shù)據(jù)核對(duì)問(wèn)題，用了數(shù)據(jù)查詢和匹配函數(shù)，可能最多幾分鐘就可以搞定。
但是在數(shù)據(jù)公式計(jì)算中，函數(shù)雖然厲害，卻不是最厲害的！
最厲害的還得是數(shù)組公式——函數(shù)再厲害，卻只能分步計(jì)算。數(shù)組卻可以一步實(shí)現(xiàn)批量計(jì)算，讓效率提高數(shù)十倍。
今天，我們就舉幾個(gè)簡(jiǎn)單的例子。
01
————————
對(duì)數(shù)列快速求和
假設(shè)在Excel中我們想計(jì)算1到100的和，你會(huì)怎么辦？
一種辦法是，直接用公式：=1+2+3+4+……+100（公式如長(zhǎng)龍，寫(xiě)得好累）
第二種辦法是你可能會(huì)直接在單元格中輸入一個(gè)1到100的數(shù)據(jù)序列（如A1:A100中錄入），然后用公式：=SUM(A1:A100)。
顯然，第二種方法好一些，但仍然不是最快的方法。
如果你會(huì)數(shù)組公式，直接在任意單元格編寫(xiě)公式：=SUM(ROW(1:100))，然后按CTRL+SHIFT+回車鍵，自動(dòng)完成數(shù)組計(jì)算，結(jié)果為5050。
再變化一下，如果你想計(jì)算1到100之間的所有奇數(shù)的和，怎么辦？
同樣，在任意單元格編寫(xiě)公式，然后按CTRL+SHIFT+回車實(shí)現(xiàn)數(shù)組計(jì)算：
=SUM(IF(MOD(ROW(1:100),2)=1,ROW(1:100),0))
如果要求的是1到100之間所有偶數(shù)的和，只要將公式中的MOD(ROW(1:100),2)=1改為MOD(ROW(1:100),2)=0就可以了。
公式先通過(guò)MOD函數(shù)判斷1到100之間的每一個(gè)數(shù)被2除的余數(shù)是否為1或0，即判斷每個(gè)數(shù)是否為奇數(shù)或偶數(shù)，如果是奇數(shù)或偶數(shù)，則返回對(duì)應(yīng)的數(shù)值本身，否則返回0。
類似這種ROW（1：100）的寫(xiě)法就是一咱數(shù)組的形式，因此IF函數(shù)計(jì)算的結(jié)果也是一個(gè)數(shù)組。最后用SUM函數(shù)對(duì)IF的數(shù)組結(jié)果進(jìn)行求和，即可得到奇數(shù)或者偶數(shù)的和。
02
————————
對(duì)含有非法值（錯(cuò)誤值）的數(shù)據(jù)求和
當(dāng)一組數(shù)據(jù)中包含非法值或者錯(cuò)誤值的時(shí)候，是無(wú)法進(jìn)行求和的。
但是利用數(shù)組公式批量判斷，就可以一次性算出所有正常數(shù)值的和。
如上圖，ISNA(C24:C29)為數(shù)組計(jì)算，依次判斷C24:C29區(qū)域每個(gè)單元格是否是#N/A，如果是則返回0，否則返回?cái)?shù)值本身。因此，數(shù)組計(jì)算的結(jié)果是由單元格正常數(shù)值和0組成的一個(gè)內(nèi)存數(shù)組。
最后，經(jīng)過(guò)SUM計(jì)算后即可求出所有正常數(shù)值的和。
03
————————
計(jì)算前N位數(shù)據(jù)占比
如下圖，現(xiàn)在需要計(jì)算D列凈增關(guān)注人數(shù)的前3位與總凈增關(guān)注人數(shù)的占比。
一般來(lái)說(shuō)，大家都會(huì)分步驟編寫(xiě)公式，先求出第1名、第2名和第3名的關(guān)注人數(shù)，然后再求和，最后再除以總關(guān)注人數(shù)就是要求的比例。
但是，這樣操作就很慢，如果要求的是前10名的占比呢，要計(jì)算10次。
怎么辦呢，使用數(shù)組公式：
=SUM(LARGE(D36:D54,ROW(1:3)))/SUM(D36:D54)
在這個(gè)公式中，我們把原來(lái)需要分步計(jì)算的操作，只用了一個(gè)ROW(1:3）就變成了批量計(jì)算。然后利用LARGE函數(shù)和SUM函數(shù)進(jìn)行正常的取第幾位最大值和求和計(jì)算就可以了。
數(shù)組公式還有很多應(yīng)用場(chǎng)景，例如數(shù)據(jù)的多條件查詢、多條件統(tǒng)計(jì)等，都可以幫我們極大地提升計(jì)算和處理效率。
聯(lián)系客服
