函數(shù)中級應(yīng)用筆記

目錄

第一講數(shù)組基礎(chǔ).................................................1

1.什么叫數(shù)組...........................................................1

2.數(shù)組長什么樣.........................................................1

3.數(shù)組里的符號.........................................................1

4.數(shù)組的輸入...........................................................1

5.數(shù)組修改與刪除.......................................................1

6.數(shù)組維數(shù).............................................................1

7.數(shù)組維數(shù)的擴展.......................................................1

8.數(shù)組的"降維”.........................................................1

9.二維引用.............................................................1

10.數(shù)組可以分為三種.....................................................2

11.以普通公式輸入法輸入的數(shù)組公式結(jié)果不同...............................2

12.識別公式是普通公式還是數(shù)組公式.......................................2

第二講數(shù)組運算.................................................2

1.相同大小區(qū)域的數(shù)組運算...............................................2

2.數(shù)組公式（內(nèi)存數(shù)組）的優(yōu)勢...........................................2

3.二維引用與單個單元格的數(shù)組運算.......................................2

4.二維引用與一維引用的數(shù)組運算.........................................2

5.一行多列與一列多行的數(shù)組運算.........................................3

6.計算前三大銷量之和...................................................4

7.計算不重復(fù)個數(shù).......................................................4

第三講基礎(chǔ)統(tǒng)計（一）多條件計數(shù)...............................5

1.基本用法.............................................................5

2.區(qū)分大小寫:..........................................................6

3.條件內(nèi)容變化.........................................................6

4.條件符號變化.........................................................7

5.區(qū)域變化.............................................................7

第四講基礎(chǔ)統(tǒng)計（二）多條件求和.................................8

1.基本用法.............................................................8

2.區(qū)域變化.............................................................8

3.求和區(qū)域包含文本（2007、2010版整列引用問題）........................9

第五講基礎(chǔ)統(tǒng)計（三）其他多條件統(tǒng)計............................11

1,多條件平均..........................................................11

2.多條件平均基本用法..................................................12

3.其它情況............................................................12

4.統(tǒng)計區(qū)域可以包含文本................................................12

5.其他多條件平均的方法................................................13

6.其他多條件統(tǒng)計......................................................13

7.多條件統(tǒng)計各種方法比較..............................................13

第六講時間與日期函數(shù)..........................................18

1.指定年份判斷閏年....................................................18

2.指定月份最大天數(shù)....................................................19

1

3.指定期間天數(shù)........................................................20

第七講字符串函數(shù)..............................................21

1.提取數(shù)字............................................................21

2.案例1：按條件組合數(shù)字..............................................22

3.案例2:數(shù)字分列......................................................22

4.其他數(shù)字截取方法....................................................23

5.SUBSTITUTE函數(shù).....................................................25

第八講文本函數(shù)TEXT.................................................................................25

1.Text的條件格式......................................................25

2.TEXT在很大程度上可以取代IF........................................................................................27

3.練習(xí)一：利用占位符插入字符..........................................27

4.練習(xí)二：利用REPT函數(shù)構(gòu)建重復(fù)占位符.................................27

5.練習(xí)三：根據(jù)身份證號提取出生日期....................................28

6.練習(xí)四：補位........................................................28

7.練習(xí)五：萬位分隔符..................................................28

8.練習(xí)六：查找字符出現(xiàn)的位置..........................................28

9.練習(xí)七:反向查找.....................................................29

10.練習(xí)八:中文轉(zhuǎn)數(shù)字...................................................29

11.練習(xí)九:交叉合并.....................................................29

第九講區(qū)域不一致的數(shù)組（一）Frequency................................................30

1.Frequency的特點.....................................................30

2.Frequency的基本用法.................................................30

3.計算不重復(fù)個數(shù)......................................................31

4.中式排名............................................................32

5.計算連續(xù)出現(xiàn)的最大次數(shù)..............................................32

第十講區(qū)域不一致的數(shù)組（二）Mmult.....................................................33

1.MM函數(shù)的計算特點...................................................33

2.一行與一列..........................................................33

3.一行與多列..........................................................34

4.多行與一列..........................................................34

5.一列與一行..........................................................34

6.多行與多列..........................................................35

7.無法得出正確結(jié)果的分布..............................................35

8.MM函數(shù)的用處.......................................................35

第十一講：查找與引用函數(shù)（一）二維間接引用.....................36

1.三個間接引用函數(shù)的共性..............................................36

2.Indirect................................................................................................................................36

3.Index.......................................................................................................................................36

4.Offset....................................................................................................................................36

5.直接引用與間接引用混用..............................................37

6.交集和并集..........................................................37

7.If/Choose..............................................................................................................................37

8.連接字符串..........................................................37

9.去除重復(fù)項..........................................................37

2

第十二講：查找與引用函數(shù)（二）各種查找.........................38

1.單條件查找..........................................................38

2.If（{1,0}…結(jié)構(gòu)......................................................38

3.反向與錯位單條件查找................................................39

4.多條件查找..........................................................40

5.一對多查找..........................................................40

6.查找指定單元格的位置................................................41

7.不規(guī)則轉(zhuǎn)置..........................................................41

8.不規(guī)則轉(zhuǎn)置的延伸（隔列多行求最值）....................................43

第十三講：查找與引用函數(shù)（三）增強Excel技巧功能................43

1.函數(shù)與數(shù)據(jù)透視表....................................................44

2.函數(shù)與數(shù)據(jù)有效性....................................................47

3.函數(shù)與動態(tài)圖表......................................................50

3

第一講數(shù)組基礎(chǔ)

1.什么叫數(shù)組

數(shù)組就是一組數(shù)！很多數(shù)整齊有序的排列

2.數(shù)組長什么樣

{1,2,3;4,5,6}

3.數(shù)組里的符號

①一對半角大括號；②半角逗號：表示橫向排列（分隔列數(shù)據(jù)）；③半角分號：表示縱向排列（分

隔行數(shù)據(jù)）④先橫后縱

例如：

ABC

1123

2456

Al：C2返回{1,2,3;4,5,6}

注：幫助記憶（分行逗列）

4.數(shù)組的輸入

選取單元格區(qū)域，按CTRL+SHIFT+ENTER組合鍵

注：必須在編輯狀態(tài)下輸入，用鼠標(biāo)點下公示欄或鍵盤F2

5.數(shù)組修改與刪除

快速選取數(shù)組:可選中數(shù)組中任一單元格后CTRL+/,03、07版本還可以通過選中數(shù)組中任一單元

格后按組合鍵，10版本還可以通過定位當(dāng)前數(shù)組來選中整個數(shù)組

6.數(shù)組維數(shù)

一維數(shù)組；二維數(shù)組；三維引用

7.數(shù)組維數(shù)的擴展

①單個的數(shù)可以向縱橫兩個方向擴展

②一行多列的數(shù)組可以縱向擴展（不可以橫向擴展，即使是倍數(shù)個單元格也不行）

③多行一列的數(shù)組可以橫向擴展（不可以縱向擴展，即使是倍數(shù)個單元格也不行）

④多行多列的數(shù)組無法擴展

8.數(shù)組的“降維“

多維引用可以降成二維數(shù)組；沒聽過二維數(shù)組可以降為一維數(shù)組

9.二維引用

①一行多列，一列多行稱為一維引用，多行多列稱為二維引用，一維引用是特殊的二維引用

②二維引用分直接引用和間接引用

1

10.數(shù)組可以分為三種

①常量數(shù)組；②二維引用；③運算數(shù)組

11.以普通公式輸入法輸入的數(shù)組公式結(jié)果不同

①對于一列數(shù)據(jù)，結(jié)果放在不同行有不同的結(jié)果

②對于一行數(shù)據(jù)，結(jié)果放在不同的列有不同的結(jié)果

12.識別公式是普通公式還是數(shù)組公式

①大括號不是絕對的標(biāo)志

②數(shù)組公式中每一個數(shù)都分別參與計算

第二講數(shù)組運算

1.相同大小區(qū)域的數(shù)組運算

例如:

CDEFGHIJKLM

8123102030104090

9456405060160250360

選中K8:M9,輸入C8:E9*G8:I9后按組合鍵

注：相同大小區(qū)域的數(shù)組運算的運算規(guī)律是數(shù)組的相同位置的數(shù)參與運算，且返回結(jié)果區(qū)域的相

對位置

2.數(shù)組公式(內(nèi)存數(shù)組)的優(yōu)勢

①內(nèi)存數(shù)組可以進行再運算

②內(nèi)存數(shù)組與公式參數(shù)的數(shù)組運算有區(qū)別，但習(xí)慣上都叫做數(shù)組公式

例如：仁SUM(C8:E9*G8:I9)｝返回910

嚴(yán)格說屬于公式參數(shù)的數(shù)組參與運算

3.二維引用與單個單元格的數(shù)組運算

CDEGH1KLM

20123101010102030

21456101010405060

選中K20：M20,在其中輸入｛=C20:E21*G10｝與｛=C2O:E21*G2O:I21｝結(jié)果相同

注:二維引用與單個單元格的數(shù)組運算返回二維引用中各個位置的數(shù)分別與單個單元格運算后所

組成的二維引用

4.二維引用與一維引用的數(shù)組運算

例一：

CDEGH1KLM

25123102030104090

2645610203040100180

選中K25:M26,輸入｛=C25:E26*G25:I25｝與｛=C25:E26*G25:I26｝結(jié)果相同

2

例二:

CDEGH1KLM

28123101010102030

29456404040160200240

選中K28:M29,輸入｛=C28:E29*G28:G29｝與｛=C28:E29*G28:I29｝結(jié)果相同

注:①二維引用與一維多列的運算返回二維引用中的各列分別與對應(yīng)的一行多列中的各列數(shù)字運

算后所組成的與原二維引用位置相同的二維數(shù)組

②二維引用與多行一列的運算返回二維引用中的各行分別與對應(yīng)的多行一列中的各行數(shù)字運算

后所組成的數(shù)組

5.一行多列與一列多行的數(shù)組運算

CDEGH1KLM

33123101010102030

341234040404080120

選中K33:M34,輸入仁C33:E33*G33:G34｝與｛=C33:E34*G33:I34｝結(jié)果相同

注：

①一行多列與一列多行的數(shù)組運算與一列多行與一行多列的數(shù)組運算結(jié)果相同

②一行多列與一列多行或一列多行與一行多列的運算等價于特殊的二維引用與一維引用的運算

③不能擴展的方向數(shù)據(jù)個數(shù)必須一致才能進行數(shù)組運算，多出部分會變成錯誤值#N/A

例：

CDEFGH1JKLM

3812310201040#N/A

39

4011010

4122040

423#N/A

43

4412310201040#N/A

4545640100#N/A

擴展錯誤造成的結(jié)果

選中區(qū)域過大，多選中的區(qū)域也會顯示#N/A,這只屬于顯示錯誤；不能擴展方向數(shù)據(jù)個數(shù)不同

造成的#N/A是本身計算錯誤

注：①只粘貼數(shù)值不粘貼格式與公式快捷鍵：CTRL+C與CTRL+V后，CTRL后V

②ROW(1:10)與ROW(A1:A10)的區(qū)別是ROW(1:10)計算機需要運行計算1到10行的每個單元格，

而ROW(A1:A10)計算機只運行計算1到10行的A列單元格，因此ROW(1:10)比較省字符，但是

占用電腦內(nèi)存，影響計算速度

③二維數(shù)組只能擴展不能收縮

④本節(jié)課例子只用了乘法，其他的四則運算同乘法規(guī)律相同

⑤二維數(shù)組間的運算結(jié)果(單個單元格、一行多列、一列多行相當(dāng)于特殊的二維數(shù)組)等價于參與

運算的二維數(shù)組的能擴展的方向擴展成相同的行數(shù)與列數(shù)后再進行運算的結(jié)果

3

6.計算前三大銷量之和

法1：{=SUM(1/COUNTIF(C69:C81,C69:C81))}

法：

2{=SUM(IF(MATCH(C69:C81,C69:C81J=ROW(1:13);1))}

注：①法2中MATCH最后的逗號不可省，若省代表模糊查找，只寫逗號不加參數(shù)相當(dāng)于省略了

零0,代表精確查找

②法2中IF省略第二個參數(shù)，則若不滿足條件時返回FALSE

③公式中省略標(biāo)點符號相當(dāng)于省略了一個參數(shù)，若有符號沒有參數(shù)只是省略了一個數(shù)

法3：{=COUNT(0/(MATCH(C69:C81,C69:C81,)=ROW(1:13)))}

法4：{=SUM(1*(MATCH(C69:C81,C69:C81,)=ROW{1:13)))}

法5：{=SUM(N(MATCH(C69:C81,C69:C81,)=ROW(1:13)))}

注：法5中的N()函數(shù)返回轉(zhuǎn)化為數(shù)值后的值，具體如下

4

數(shù)值或引用返回值

數(shù)字該數(shù)字

日期該日期的序列號

TRUE1

FALSE0

錯誤值錯誤值

其他值0

第三講基礎(chǔ)統(tǒng)計（一）多條件計數(shù)

1.基本用法

語法結(jié)構(gòu)：

=SumProduct（（條件區(qū)域1=條件1）*（條件區(qū)域2=條件2）*…*（條件區(qū)域N=條件N））

仁Sum（《條件區(qū)域1=條件1）*（條件區(qū)域2=條件2）*…，條件區(qū)域N=條件N））｝

例子：

EFG1JKL

11員工姓名部門職級部門職級人數(shù)人數(shù)

12趙一傷生產(chǎn)部X生產(chǎn)部X22

13錢二敗生產(chǎn)部Y生產(chǎn)部Y33

14孫三毀生產(chǎn)部Y市場部X11

15李四摧市場部X市場部Z11

16周五輸生產(chǎn)部Y人事部X11

17吳六破生產(chǎn)部X

18鄭七滅市場部Z

19王八衰人事部X

K12中：=SUMPRODUCT(($F$12:$F$19=I12)*($G$12:$G$19=J12))

L12中：{=SUM(($F$12:$F$19=I12)*($G$12:$G$19=J12))}

計算原理：

計算原理

條件區(qū)域仁條件1條件區(qū)域2=條件2結(jié)果

TRUETRUE1

TRUEFALSE0

TRUEFALSE0

FALSE乘以TRUE等J'-0

TRUEFALSE0

TRUETRUE1

FALSEFALSE0

FALSETRUE0

因條件區(qū)域1=條件1返回的是由TRUE和FALSE組成的數(shù)組，而sumproduct的參數(shù)要求是由數(shù)

字組成的數(shù)組，因此2個條件之間不能用逗號，而一般通過條件之間相乘將邏輯值轉(zhuǎn)化成數(shù)字,

或者在其外套一個N函數(shù)、或者同1相乘（除卜或者加上（減去）0,但是這種方法沒有直接相乘簡

潔（除非只有一個參數(shù)）。Sumproduct（true,false）返回的結(jié)果是錯誤值#value,完全無法計算，而su

5

mproduct({true,false}威者sumproduct的參數(shù)是帶true和fake的單元格引用返回的結(jié)果是0；另

sum(truejalse)是可以計算的，結(jié)果是1,但若是sum({true,false})或者sum的參數(shù)是帶true和fal

se的單元格引用，則無法計算，結(jié)果是0。

2.區(qū)分大小寫：

EFGIJK

34員工姓名部門職級職級SumExact

35趙一傷生產(chǎn)部XX83

36錢二敗生產(chǎn)部XX85

37孫三毀生產(chǎn)部X

38李四摧市場部X

39周五輸生產(chǎn)部X

40吳六破生產(chǎn)部X

41鄭七滅市場部X

42王八衰人事部X

J35單元格中：{=SUM(N($G$35:$G$42=I35))}

K35單元格中：{=SUM(N(EXACT($G$35:$G$42,I35)))}

3.條件內(nèi)容變化

數(shù)據(jù)源：

EFG

46員工姓名部門職級

47趙一傷生產(chǎn)部X

48錢二敗生產(chǎn)部Y

49孫三毀生產(chǎn)部Y

50李四摧市場部X

51周五輸生產(chǎn)部Y

52吳六破生產(chǎn)部X

53鄭七滅市場部Z

54王八衰人事部X

數(shù)組公式中內(nèi)容的變化:

IJK

46部門職級J列公式

47生產(chǎn)部X

48

49引用2{=SUM((F47:F54=I47)*(G47:G54=J47))}

50常量2{=SUM((F47:F54="生產(chǎn)部2*(G47:G54=J47))}

51混合2{=SUM((F47:F54=LEFT(I47,2)&"^")*(G47:G54=J47))}

52通配符0{=SUM((F47:F54="*部")*(G47:G54=J47))}

53函數(shù)14{=SUM(ISNUMBER(FIND("gf",F47:F54))*(G47:G54=J47)))

54函數(shù)24{=SUM((RIGHT(F47:F54)="gP")*(G47:G54=J47))}

6

4.條件符號變化

數(shù)據(jù)源：

EFG

58員工姓名部門工資

59趙一傷生產(chǎn)部1000

60錢二敗生產(chǎn)部1100

61孫三毀生產(chǎn)部1200

62李四摧市場部1300

63周五輸生產(chǎn)部1400

64吳六破生產(chǎn)部1500

65鄭七滅市場部1600

66王八衰人事部1700

條件符合變化:

IJK

58部門工資J列公式

59生產(chǎn)部1200

60生產(chǎn)部>1200

61

62不等于3{=SUM(N(F59:F66<>I59))}

63大于5{=SUM(N(G59:G66>J59))}

=COUNTIF(G59:G66,">1200n)

64Countif5=COUNTIF(G59:G66,">"&J59)

=COUNTIF(G59:G66,J60)

65>12000=SUM(N(G59:G66=J60))

66V&1200錯誤框=SUM(N(G59:G66">"&1200))

5.區(qū)域變化

數(shù)據(jù)源：

EFG

70部門一月工資二月工資

71生產(chǎn)部10001700

72生產(chǎn)部11001600

73生產(chǎn)部12001500

74市場部13001400

75生產(chǎn)部14001300

76生產(chǎn)部15001200

77市場部16001100

78人事部17001000

7

區(qū)域變化:

IJK

70部門工資J列公式

71生產(chǎn)部1200

72

73一月工資1{=SUM((E71￡78=171)*(F71:F78=J71))}

74二月工資1{=SUM((E71:E78=I71)*(G71:G78=J71))}

75全部2{=SUM((E71:E78=I71)*(F71:F78=J71))+SUM((E71:E78=l71)*(G71:G78=J71))}

76全部2{=SUM((E71:E78=I71)*(F71:G78=J71))}

77Countifs#VALUE!=COUNTIFS(E71:E78,I71,F71:G78,J71)

注：①自動計算改手動計算，03版在工具一一選項一一重新計算一一手動計算；07版在公式一

一計算選項一一手動；手動計算時候按F9即可

②03版不支持整行整列引用

③其他多條件計數(shù)的方法：Countlfs,數(shù)據(jù)庫函數(shù)、數(shù)據(jù)透視表

第四講基礎(chǔ)統(tǒng)計（二）多條件求和

1.基本用法

語法結(jié)構(gòu)：

=SumProduct（（條件區(qū)域1=條件1）*（條件區(qū)域2=條件2）*…*（條件區(qū)域N=條件N）*求和區(qū)域）

{=Sum（（條件區(qū)域仁條件1）*（條件區(qū)域2=條件2）*…*（條件區(qū)域N=條件N）*求和區(qū)域）}

計算原理、區(qū)分大小寫、條件內(nèi)容變化、條件符號變化同多條件計數(shù)相同

2.區(qū)域變化

數(shù)據(jù)源

EFGH

一月工二月工

40部門職級

資資

41生產(chǎn)部X10001700

42生產(chǎn)部Y11001600

43生產(chǎn)部Y12001500

44市場部X13001400

45生產(chǎn)部Y14001300

46生產(chǎn)部X15001200

47市場部Z16001100

48人事部X17001000

8

區(qū)域變化:

JKLM

40部門職級一月工資K列及L列公式

41生產(chǎn)部X>1200

42

{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:G48)}

43一月工資25002500

{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J43))}

{=SUM((E41:E48=J41)*(F41:F48=K41)*H41:H48)}

44二月工資29002900

{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J44))}

=SUMIFS(G41:G48,E41:E48,J41,F41:F48,K41)

45Sumifs2500#VALUE!

=SUMIFS(G41:H48,E41:E48,J41,F41:F48,K41)

=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*(G41:G48>1200

46加條件4400)*G41:G48)+SUMPRODUCT((E41:E48=J41)*(F41：F48=K41)*H41:

H48)

=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*((G41:G48>120

47加條件4400

0)*G41:G48+H41:H48))

=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*(G41:H48>{120

48加條件4400

0,0})*G41:H48)

注：sumifs必須每個參數(shù)的區(qū)域相同；加條件是指一月份工資大于1200的一月份工資和與二月

工資和的合計；公式｛=SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J44))｝理論分析：

條件1-2條件3結(jié)果

1TRUEFALSE10

000

000

0乘以等于00

000

110

000

000

sum可以對區(qū)域(二維數(shù)組)求和，sumproduct也可以對區(qū)域(二維數(shù)組)求和，因此其條件

參數(shù)與求和參數(shù)可以不是相同的大小，但必須不能出現(xiàn)計算導(dǎo)致的錯誤值；2003版本無法整行

整列引用；

3.求和區(qū)域包含文本(2007、2010版整列引用問題)

注：無論是EXCEL2003還是EXCEL2007其求和區(qū)域都不能有不能計算的文本

例如下表所示數(shù)據(jù)源G列數(shù)字是文本格式

EFGH

65部門職級工資

66生產(chǎn)部X10001000

67生產(chǎn)部Y11001000

68生產(chǎn)部Y12001000

69市場部Xaaaa

對G列進行求和SUM(G65：G68)結(jié)果返回0,因為G列數(shù)字是文本格式，在不改變其格式的

9

情況下可以進行求和的方法，即強制將文本型數(shù)字轉(zhuǎn)成數(shù)值型數(shù)字的辦法:

方法公式結(jié)果

雙負(fù)號{=SUM(-G66:G68)}3300

{=SUM(1*G66:G68)}

乘以1或者除以13300

{=SUM(G66:G68/1)}

{=SUM(G66:G68+0)}

加0或者減03300

{=SUM(G66:G68-0)}

0&……{=SUM(0&G66:G68)}0

公式中本身有四則運算

Value函數(shù)

其他一些函數(shù)COUNTIF和SUMIF

注：countif和sumif的條件區(qū)域是可以忽略數(shù)字的格式的，但是sumif的求和區(qū)域不可以(求和

區(qū)域需是數(shù)字格式)例如=COUNTIF(G66:G68,G66)返回結(jié)果1,=SUMIF(G66:G68,G66)返回結(jié)果0,

ffU=SUMIF(G66:G68,G66,H66:H68)ig[Hl^^1000

其他包含文本情況：

JKL

65部門職級

66生產(chǎn)部X

67

68文本型1000{=SUM((E66:E68=J66)*(F66:F68=K66)*G66:G68)}

69純文本#VALUE!{=SUM((E66:E69=J66)*(F66:F69=K66)*G66:G69)}

70不含表頭1000{=SUM((E66:E68=J66)*(F66:F68=K66)*G66:G68)}

71包含表頭#VALUE!{=SUM((E65:E68=J66)*(F65:F68=K66)*G65:G68)}

{=SUM(IFERROR((E65:E68=J66)*(F65:F68

72IfError1000

=K66)*G65:G68,0))}

73空

注：

①整列引用時可以用iferror將錯誤值轉(zhuǎn)換為0；

②條件求和時(sum,sumif)求和區(qū)域含假空單元格不行，返回#VALUE,含有真空單元格行，含有

空格不行，返回#VALUE；

③countifs和sumifs的條件參數(shù)最多是127個；

④其他多條件求和的方法：sumifs、數(shù)據(jù)庫函數(shù)、數(shù)據(jù)透視表

10

第五講基礎(chǔ)統(tǒng)計(三)其他多條件統(tǒng)計

1.多條件平均

源數(shù)據(jù)：

DEFG

5員工姓名部門職級工資

6趙一傷生產(chǎn)部X1000

7錢二敗生產(chǎn)部Y1001

8孫三毀生產(chǎn)部Y1002

9李四摧市場部X1003

10周五輸生產(chǎn)部Y1004

11吳六破生產(chǎn)部X1005

12關(guān)卜七滅市場部Z1006

13王八衰人事部X1007

效果

IJKLM

5部門職級求和/計數(shù)平均平均

6生產(chǎn)部X1002.5250,6251002.5

7生產(chǎn)部Y1002.333375.8751002.333

8市場部X1003125.3751003

9市場部Z1006125.751006

10人事部X1007125.8751007

K6中公式：{=SUM(($E$6:$E$13=I6)*($F$6:$F$13=J6)*$G$6:$G$：13)/SUM(($E$6:$E$：13=I6)*($F$6:$F

$13=J6))}

L6中公式：{=AVERAGE(($E$6:$E$13=I6)*($F$6:$F$13=J6)*$G$6:$G$13)}

M6中公式：仁AVERAGE(IF(($E$6:$E$13=I6)*($F$6:$F$13=J6),$G$6:$G$13))}

L6中公式結(jié)果不正確，現(xiàn)分析如下：

條件結(jié)果平均區(qū)域結(jié)果

110001000

010010

010020

0乘以1003等于0

010040

110051005

010060

010070

條件結(jié)果中數(shù)組公式｛=(E6:E13=I6)*(F6:F13=J6)｝；平均區(qū)域中數(shù)組公式｛=G6:G13｝；結(jié)果中數(shù)組公

式仁D17:D24*F17:F24｝,對結(jié)果列進行求平均數(shù)，平均數(shù)計算中的0算個數(shù)。

11

M6i公式的計算原理分析:

IF

1000

FALSE

FALSE

FALSE

FALSE

1005

FALSE

FALSE

其中數(shù)組公式：IF(($E$6:$E$13d6)*($F$6:$F$13=J6),$G$6:$G$13)

平均數(shù)計算中FALSE不算個數(shù)

2.多條件平均基本用法

仁Average#((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)*…，條件區(qū)域N=條件N),平均區(qū)域))}

此結(jié)構(gòu)也適用于多條件求和和多條件計數(shù)，不過多條件計數(shù)沒有最后一個參數(shù)，