您现在的位置是：首页 > 生活消费网站首页生活消费

大家好，我是小业，我来为大家解答以上问题。sumproduct函数基本应用，sumproduct函数的功能是很多人还不知道，现在让我们一起来看看吧！

1、一、基本功能

2、1．函数SUMPRODUCT的功能

3、返回相应的区域或数组乘积的和。

4、2．基本格式

5、SUMPRODUCT（数据1，数据2，……，数据30）

6、3．示例

7、数据表

8、A列 B列 C列 D列 E列

9、数据1数据2数据3数据4数据5

10、2 3 4 12 10

11、5 5 6 5 20

12、9 7 8 #N/A 30

13、7 2 7 9 KL

14、1 6 2 8 2

15、⑴基本计算

16、①区域计算

17、要求：计算A、B、C三列对应数据乘积的和。

18、公式：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)

19、计算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6

20、即三个区域A2:A6,B2:B6,C2:C6同行数据积的和。

21、返回值788。

22、②数组计算

23、要求：把上面数据表中的三个区域A2:A6,B2:B6,C2:C6数据按一个区域一个数组，计算对应数组积的和。

24、把A2:A6,B2:B6,C2:C6分别作为一个数组,即

25、A2:A6表示为数组-{2;5;9;7;1}

26、B2:B6表示为数组-{3;5;7;2;6}

27、C2:C6表示为数组-{4;6;8;7;2}

28、公式：

29、=SUMPRODUCT({2;5;9;7;1},{3;5;7;2;6},{4;6;8;7;2})=788

30、注意：

31、数组数据用大括号{}括起来。

32、行数据之间用分号"；"分隔，如果是同一行的数据，用逗号","分隔。

33、⑵可能出现的错误

34、①编辑公式时，引用的数据区域大小不一致导致计算错误，返回值为#VALUE！。

35、示例：

36、在上面的数据表中，计算A列与B列数据区域积的和。

37、公式：

38、=SUMPRODUCT(A2:A6,B2:B5)

39、或

40、=SUMPRODUCT(A2:A6,B2:B8)

41、都会返回错误值#VALUE！。

42、所以在用SUMPRODUCT函数时，引用的数据区域大小要一致。

43、②数据区域中有错误值时，计算出现错误值。

44、示例：

45、在上面的数据表中，计算数据区域A2：A6与D2：D6对应积的和。

46、公式：=SUMPRODUCT(A2:A6,D2:D6)

47、因为D2：D6中有错误值#N/A，所以公式返回值为错误值#N/A。

48、③数据区域引用不能整列引用。

49、示例：

50、计算上面数据表中A2：A6和B2：B6区域对应数据积的和，正确公式为

51、=SUMPRODUCT(A2:A6,B2:B6)则返回正确的计算值94。如果用公式=SUMPRODUCT(A:A,B:B)则返回错误值#NUM！。

52、④数据区域有文本，计算中系统默认文本值为0。

53、示例：

54、在上面数据表中，计算A2：A6和E2：E6区域中对应数据积的和。

55、公式=SUMPRODUCT(A2:A6,E2:E6)返回值是392，其中E5是文本KL，则A5*E5=0。

56、二、用于多条件计数

57、用数学函数SUMOPRODUCT计算符合2个及以上条件的数据个数

58、⑴数据表

59、如下数据表所示：

60、A列 B列 C列

61、姓名性别职称

62、A   男中一

63、B   女中二

64、C   女中一

65、D   男中一

66、E   女中一

67、F   男中二

68、G   女中二

69、H   男中一

70、I   男中一

71、J   女中一

72、统计表

73、E列 F列   G列

74、中一 中二

75、男

76、女

77、要求：

78、统计上面数据表中男、女性中分别是中一、中二的人数。

79、如下图片，A1：C11数据区域，在E1：G3区域统计男、女中中一和中二的人数是多少。

80、⑵公式

81、在数据统计区域中的F2单元格编辑如下公式：

82、=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))

83、向下复制到F3，向右复制到G3。

84、⑶公式分解及分析

85、①条件1——$B$2:$B$11=$E2

86、在计算过程中，条件1是一个数组，返回多值，写成公式如下：

87、=$B$2:$B$11=$E2

88、具体操作：

89、选中10个连续的单元格，输入上述公式后，按Ctrl+Shift+回车键确认，返回10个逻辑判断值——TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。

90、这10个逻辑判断值构成一个新的由TRUE主FALSE组成的数组1。

91、②条件2——$C$2:$C$11=F$1

92、与条件1相同，是一个数组，返回多值，写成公式如下：

93、=$C$2:$C$11=F$1

94、具体操作：

95、同样的方法，选中对应的10连续单元格，输入上述公式，按Ctrl+Shift+回车键确认，返回10个逻辑判断值——TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE。

96、这10个逻辑判断值构成另一个新的由TRUE主FALSE组成的数组2。

97、③($B$2:$B$11=$E2)*($C$2:$C$11=F$1)

98、由新构成的数组1乘以数组2，即：

99、=($B$2:$B$11=$E2)*($C$2:$C$11=F$1)

100、={数组1*数组2}

101、={ TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE }

102、*{ TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE }

103、={TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;TRUE*TRUE;FALSE*TRUE;TRUE*FALSE; FALSE*FALSE;TRUE*TRUE;TRUE*TRUE; FALSE*TRUE}

104、={1;0;0;1;0;0;0;1;1;0}

105、其中,逻辑值TRU*与**LSE参与计算时:

106、TRUE=1

107、FALSE=0

108、TRUE*TRUE=1

109、TRUE*FALSE=FALSE*TRUE=0

110、FALSE*FALSE=0

111、因此

112、{数组1*数组2}={1;0;0;1;0;0;0;1;1;0}

113、由1和0构成了一个新的数组3。

114、④SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))

115、函数SUMPRODUCT对新的数组3中的所有数据求和。即：

116、=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))

117、=SUMPRODUCT({数组1*数组2})

118、=SUMPRODUCT({数组3})

119、=SUMPRODUCT({1;0;0;1;0;0;0;1;1;0})=4

120、这里需要说明的是，公式编辑按照函数SUMPRODUCT的一般格式，可以编辑如下等效的公式：

121、=SUMPRODUCT(($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1)

122、函数SUMPRODUCT的作用是对数组($B$2:$B$11=$E2)与数组($C$2:$C$11=F$1))计算其乘积的和，即：

123、=SUMPRODUCT(($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1)

124、=SUMPRODUCT({ TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE }*1，{ TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE }*1)

125、=SUMPRODUCT({1;0;0;1;0;1;0;1;1;0},{1;0;1;1;1;0;0;1;1;1})

126、=SUMPRODUCT({1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1})

127、=SUMPRODUCT({1;0;0;1;0;0;0;1;1,0})=4

128、注意：

129、TRUE*1=1，FALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0 。

130、数组中用分号分隔，表示数组是一列数组，分号相当于换行。两个数组相乘是同一行的对应两个数相乘。

131、三、用于多条件求和

132、对于计算符合某一个条件的数据求和，可以用SUMIF函数来解决。如果要计算符合2个以上条件的数据求和，用SUMIF函数就不能够完成了。这就可以用函数SUMPRODUCT。

133、用函数SUMPRODUCT计算符合多条件的数据和，其基本格式是：

134、SUMPRODUCT（条件1*条件2*……，求和数据区域）

135、数据表

136、A列B列C列D列

137、姓名性别职称课时

138、A男中一15

139、B女中二16

140、C女中一14

141、D男中一13

142、E女中一18

143、F男中二15

144、G女中二16

145、H男中一14

146、I男中一17

147、J女中一18

148、要求：

149、计算男、女分别是中一或中二的总课时数。

150、统计表

151、F列   G列   H列

152、    中一 中二

153、男

154、女

155、在G2中编辑公式

156、=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)

157、回车确认后向下向右复制公式到H3单元格。

158、公式释义：

159、性别区域$B$2:$B$11中满足条件男和职称区域$C$2:$C$11中满足条件中一的数据，通过判断计算后由1和0组成一个新的数据区域，这个新的数据区域再和课时区域$D$2:$D$11中的对应数据相乘后求和。

160、公式对比：

161、到此，对函数SUMPRODUCT用来计数和求和，试作一对比

162、计数公式

163、=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))

164、求和公式

165、=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)

166、不难看出，求和公式在原来的计数公式中，在相同判断条件下，增加了一个求和的数据区域。

167、也就是说，用函数SUMPRODUCT求和，函数需要的参数一个是进行判断的条件，另一个是用来求和的数据区域。

168、四、用于排名次

169、如下A列数据：

170、A列   B列

171、数据 名次

172、56

173、65

174、89

175、65

176、56

177、78

178、90

179、52

180、60

181、90

182、需要排出区域A2：A11中10个数据的名次。

183、在B2中编辑公式：

184、= SUMPRODUCT((A2<$A$2:$A$11)*1)+1

185、向下复制到单元格B11。

186、公式释义：

187、用A2到$A$2:$A$11(用绝对引用$保证公式在向下复制时整个数据区域不发生变化)中进行比较,当A2<$A$2:$A$11成立时,则返回TRUE;如果A2<$A$2:$A$11不成立就返回FALSE。所以数组公式=A2<$A$2:$A$11返回一个由TRUE和FALSE构成的逻辑数组。

188、把数组公式=A2<$A$2:$A$11构成的逻辑数组乘1，得到一个由0和1构成的新数组。

189、SUMPRODUCT再对由0和1构成的新数组求和，表示在数据区域$A$2:$A$11中比A2大的数据个数。

190、所以A2在数据区域$A$2:$A$11内排列的位次应该是比A2大的数据个数+1，即公式

191、=数据区域$A$2:$A$11内比A2大的个数+1

192、=SUMPRODUCT((A2<$A$2:$A$11)*1)+1

193、公式向下复制，则依次对$A$2:$A$11中每一个数据重复进行上述判断求和，从而排出数据区域$A$2:$A$11中每一个数据的位次。

194、很显然，对于数据区域$A$2:$A$11中相同的数，判断和计算结果是相同的，也就是排出来的位次相同。所以用SUMPRODUCT函数排出来的名次，与直接用RANK函数排出来的名次是一样的，有重复名次，但最大位次数不超过数据区域$A$2:$A$11中的总数据个数。

195、如果希望排出的位次没有重复，而数据区域$A$2:$A$11中相同数据的位次按数据出现的先后顺序排位，可以用下面的公式：

196、=SUMPRODUCT((A2<$A$2:$A$11)*1)+COUNTIF($A$2:A2,A2)

197、公式向下复制即可。

198、两种排名对比如下表所示：

199、A列   B列 C列

200、数据 名次重复顺序名次

201、56 8    8

202、65 5    5

203、89 3    3

204、65 5    6

205、56 8    9

206、78 4    4

207、90 1    1

208、52 10    10

209、60 7    7

210、90 1    2

211、综上所述，对于多条件的计数或者求和，可以用数学函数SUMPRODUCT来比较方便的解决。在使用函数时，进行数据引用的单元格区域或数组应该大小一致，不能采取整列引用（形如A：A）。如果跨表使用函数SUMPRODUCT，与其它函数跨表引用数据一样，数据区域前面应该标明工作表名称。

212、[color=#EE1D24,strength=3);]计数公式中最关键的是确定计数的判断条件。

213、[color=#EE1D24,strength=3);]求和公式在原来的计数公式中，在相同判断条件下增加了一个求和的数据区域。[color=#EE1D24,strength=3);]用函数SUMPRODUCT求和，函数需要的参数一个是进行判断的条件，另一个是用来求和的数据区域。

本文到此讲解完毕了，希望对大家有帮助。