曾經(jīng)星光老師用一個(gè)sum公式教訓(xùn)了無(wú)數(shù)狂妄自詡為高手的家伙：

=SUM(1,{'2','S','B'},'1',TRUE,'2',{'貨'})

這個(gè)公式的本質(zhì)是函數(shù)不同參數(shù)類(lèi)型(常量/數(shù)組/引用)下對(duì)數(shù)字/邏輯值的統(tǒng)計(jì)差異,，在理解這個(gè)公式后的深度挖掘,現(xiàn)在才有了用sum畫(huà)畫(huà)的新應(yīng)用,。 

當(dāng)然,這篇不介紹這種旁門(mén)左道,而是介紹下sum的基礎(chǔ)性質(zhì)，星光老師當(dāng)年的公式,只用了常量和數(shù)組,，那么只用引用的話問(wèn)題又來(lái)了,，假設(shè)所有單元格的值都是1且公式所在單元格避開(kāi)循環(huán)引用的話，這個(gè)公式的結(jié)果又是多少?                             

=sum(a1:e5:d9b2:f4,((((a3),b2:b4))))   

這又是一個(gè)可以讓新手崩潰的公式,這個(gè)公式涉及到了sum的3種運(yùn)算符和區(qū)域聯(lián)合性質(zhì),。

如圖所示,對(duì)sum的2個(gè)區(qū)域使用不同的運(yùn)算符，結(jié)果有很大差異,。

對(duì)新手來(lái)說(shuō)這是sum的第1個(gè)盲區(qū),，由于sum的求和能力,我們也可以用sum來(lái)進(jìn)行獲取一些規(guī)則序列,

上面這個(gè)只是基本應(yīng)用下的不常規(guī)衍生用途,沒(méi)有偏離對(duì)sum的認(rèn)識(shí),然而sum一旦進(jìn)入數(shù)組階段,那么對(duì)新手來(lái)說(shuō)，完全就是另一個(gè)函數(shù)了,。

以下為幾種sum的數(shù)組求和應(yīng)用：

1)  斜線求和

2)  條件多列求和

3)  剩余庫(kù)存

4)  條件排名

5)  區(qū)間取值

6)  合并單元格結(jié)構(gòu)對(duì)應(yīng)求和

7) 篩選下的條件求和

8) 階梯計(jì)算/工時(shí)計(jì)算

1,斜線求和

掌握引用或數(shù)學(xué)思維,sum的求和方向不限于橫縱方向的

2,多列條件求和

混過(guò)e圈的應(yīng)該知道這題有個(gè)段子,，菜鳥(niǎo)公式為多個(gè)sumif列相加和sum無(wú)關(guān)故不列舉：

高手公式：

=SUM(SUMIF(OFFSET(A$41:A$50,,ROW($1:$4)*0),G41,OFFSET(A$41:A$50,,ROW($1:$4))))                   

該公式可簡(jiǎn)化為：

=SUM(SUMIF(A$41:A$50,G41,OFFSET(A$41:A$50,,ROW($1:$4)))) 

高高手公式                 

=SUM(SUBTOTAL(9,OFFSET(B$40:E$40,ROW($1:$10),))*(A$41:A$50=G41))   

高高高手公式

=SUM(MMULT(B$41:E$50,ROW(1:4)^0)*(A$41:A$50=G41)) 

可是這題我一直奇怪難道不是一個(gè)sum就完事了么……                 

純一維數(shù)組解法                 

=SUM((A$41:A$50=G41)*(B$41:B$50+C$41:C$50+D$41:D$50+E$41:E$50)) 

只適合學(xué)習(xí)時(shí)使用,列多了和sumif相加一樣屬于低效解法。

常規(guī)二維數(shù)組解法

=SUM((A$41:A$50=G41)*B$41:E$50) (最簡(jiǎn))     

當(dāng)然這題如果你sum熟悉了還有很多別的解法……               

=SUM(IF(A$41:A$50=G41,B$41:E$50)) 

=SUM(DSUM(A$40:E$50,ROW($2:$5),G$40:G41))-SUM(H$40:H40)

=SUM(COUNTIF(G41,A$41:A$50)*B$41:E$50) 

3,剩余庫(kù)存(入庫(kù)-出庫(kù)的總數(shù))

=SUM((B$82:I$82={'入庫(kù)';'出庫(kù)'})*{1;-1}*B83:I83)

=SUM(IF(B$82:I$82='入庫(kù)',B83:I83,-B83:I83)) 

=SUM((B83:H83-C83:I83)*(B$82:H$82='入庫(kù)')) 

=SUM(B83:I83*-1^COLUMN(B:I)) 

=SUM(COS(PI()*COLUMN(B:I))*B83:I83)

=SUM(NOT(B$82:I$82<>{'入庫(kù)';'出庫(kù)'})*B83:I83*{1;-1}) 

解法數(shù)就這么湊出來(lái)的,反正你打不到我

4,條件排名

美式排名

=SUM((A$106:A$114=A106)*(B$106:B$114>B106))+1 

不重復(fù)排名

=SUM((A$106:A$114=A106)*(B$106:B$114>B106),(A$106:A106=A106)*(B$106:B106=B106))

中式排名

=SUM((A$106:A$114=A106)*(B$106:B$114>B106)/COUNTIFS(A$106:A$114,A$106:A$114,B$106:B$114,B$106:B$114))+1       

(注意這里是作為案例所以使用sum數(shù)組,常規(guī)情況下條件排名里美式與不重復(fù)排名直接用countifs的效率高些,且本案例的不重復(fù)排名公式為極端寫(xiě)法只使用了1個(gè)sum,，這里sum的2個(gè)參數(shù)的數(shù)組大小是不一樣的)

5,，區(qū)間取值

=INDEX({'優(yōu)','良','中','及格','不及格'},SUM(N(E129<{60,90,70,80}))+1)

sum+常量數(shù)組的結(jié)構(gòu)類(lèi)似match,但match內(nèi)需要排序，sum的常量數(shù)組沒(méi)有這個(gè)限制

而且使用sum的另一個(gè)好處是比lookup等更適合左開(kāi)右閉區(qū)間

(左開(kāi)右閉時(shí)lookup等需要修正臨界點(diǎn)的小數(shù)精度)

6,合并單元格結(jié)構(gòu)下條件求和

這題只是介紹幾種合并單元格結(jié)構(gòu)處理方式,，如果不用合并單元格這題明明一個(gè)sumif完事,。

7，篩選下的條件求和

篩選下的條件判斷基本只能靠subtotal的多維來(lái)構(gòu)造條件判斷的內(nèi)存數(shù)組,。 

8,階梯求和

這個(gè)案例是寫(xiě)sum必備的,這個(gè)思路當(dāng)你足夠了解后,，是可以用來(lái)解決相對(duì)復(fù)雜的時(shí)間問(wèn)題的。

8-2)工時(shí)計(jì)算

這是考勤類(lèi)的常見(jiàn)問(wèn)題,，對(duì)新手來(lái)說(shuō)這題難度較高,且部分解法使用了如median等特殊函數(shù)且運(yùn)用了大量數(shù)學(xué)思路,新手很難駕馭而且通常僅適合固定N段工時(shí),無(wú)法快速應(yīng)用于其他規(guī)則,，所以在時(shí)間的計(jì)算原理(小數(shù))配合階梯算法的思路下找出來(lái)一個(gè)可以借鑒的套路類(lèi)解法：

=SUM(TEXT(MOD(A2:B2,1)-TIME({0;8;12;13;18;18;20},{0;30;0;30;0;30;30},0),'[<0]!0')*{0;1;-1;1;-1;1;-1}*{-1,1},INT(A2:B2)*{-1,1}*10/24)*24     

或

=SUM(TEXT(MOD(A2:B2,1)-G$18:G$24,'[<0]!0')*H$18:H$24*{-1,1},INT(A2:B2)*{-1,1}*10/24)*24       

第2個(gè)公式針對(duì)不會(huì)用time+數(shù)字直接構(gòu)造需要的常量數(shù)組的,時(shí)間與累加序列直接放單元格里引用。

公式2引用區(qū)域如下：

 這思路看起來(lái)復(fù)雜其實(shí)不難 ,，首先要理解sum+text的階梯計(jì)算原理 ,，然后,mod提取起止的時(shí)間部分 ， 利用time函數(shù)構(gòu)造的時(shí)間的內(nèi)存數(shù)組來(lái)配合text進(jìn)行相應(yīng)階梯計(jì)算。

time構(gòu)造的時(shí)間的數(shù)組(注意為垂直方向與提取時(shí)間構(gòu)造成二維數(shù)組)

利用相應(yīng)時(shí)間的階梯計(jì)算來(lái)獲取起止日期的時(shí)間部分的工時(shí)時(shí)間,，{-1,1}常規(guī)方式在數(shù)組內(nèi)相減來(lái)獲取工時(shí)差(1天以內(nèi)的)

而sum第2個(gè)參數(shù)部分比較好理解,，int取日期部分，{-1,1} 方式相減獲取天數(shù)差,而每天的整個(gè)工時(shí)為10小時(shí)所以*10/24：

最后sum聚合2個(gè)部分的內(nèi)存數(shù)組求和 ,。

注意結(jié)果為小數(shù)(時(shí)間）,，要轉(zhuǎn)為小時(shí)單位故最后*24       

由于這個(gè)思路涉及時(shí)間，小數(shù)的精度要求很高,，所以text使用 '[<0]!0' 簡(jiǎn)潔的寫(xiě)法來(lái)使正數(shù)時(shí)支持產(chǎn)生大量小數(shù)位數(shù)且負(fù)數(shù)時(shí)結(jié)果為0,。

注意每個(gè)數(shù)組使用的逗號(hào)與分號(hào)的差異,目的是構(gòu)造需要的二維數(shù)組，不熟悉常量數(shù)組的可以把時(shí)間如解法2直接列在單元格內(nèi),。 

而整個(gè)sum公式是按照時(shí)間計(jì)算的因此結(jié)果為時(shí)間單位,結(jié)果單位為小時(shí)所以*24,。 

看懂該思路后這類(lèi)計(jì)算工時(shí)問(wèn)題只是個(gè)數(shù)學(xué)題而且只要對(duì)time部分(與int部分的每日全部工時(shí))進(jìn)行參數(shù)修改，無(wú)論每天是幾段上班時(shí)間均能快速統(tǒng)計(jì),。

這就是階梯思路配合時(shí)間的數(shù)學(xué)處理思路下的配合,。

整個(gè)sum公式有2個(gè)參數(shù)，是大小不一致的內(nèi)存數(shù)組(第1個(gè)是二維的,，第2個(gè)是一維的）,，但是在sum里作為不同參數(shù)，所以可以被統(tǒng)一求和 ,，這也是這公式最后能變成只用1個(gè)sum的原因所在(注意該公式不能直接用sumproduct替代,如果用sumproduct避免使用三鍵,需要用2個(gè)sumproduct把時(shí)間與日期分開(kāi)統(tǒng)計(jì),。          

以上為sum在數(shù)組階段一些常見(jiàn)不常見(jiàn)的求和應(yīng)用，這個(gè)函數(shù)在熟悉數(shù)組的條件構(gòu)造后能配合實(shí)現(xiàn)太多的求和問(wèn)題,。

當(dāng)然,重劍無(wú)鋒,，大巧不工，你就算堅(jiān)持看完,，這里有幾個(gè)簡(jiǎn)單的sum公式你也還是不一定看得懂：

圖文作者：流浪鐵匠

專(zhuān)業(yè)的職場(chǎng)技能充電站