SUMPRODUCT函数用法详解.doc

1、Excel 多条件求和 5;9;7;1B2:B6 表 示 为 数 组 -3;5;7;2;6C2:C6 表 示 为 数 组 -4;6;8;7;2公 式 ： =SUMPRODUCT(2;5;9;7;1,3;5;7;2;6,4;6;8;7;2) =788 注 意 ： 数 组 数 据 用 大 括 号 括 起 来 。 行 数 据 之 间 用 分 号 “； “分 隔 ， 如果 是 同 一 行 的 数 据 ， 用 逗 号 “,“分 隔 。 可 能 出 现 的 错 误 编 辑 公 式 时 ， 引 用 的 数 据 区 域 大 小 不 一 致 导 致 计 算 错 误 ， 返 回 值 为#VALUE！ 。 示 例

2、： 在 上 面 的 数 据 表 中 ， 计 算 A 列 与 B 列 数 据 区 域 积 的 和 。公 式 ： =SUMPRODUCT(A2:A6,B2:B5) 或 =SUMPRODUCT(A2:A6,B2:B8) 都 会 返 回 错 误 值 #VALUE！ 。 所 以 在 用 SUMPRODUCT 函 数 时 ， 引 用 的 数 据区 域 大 小 要 一 致 。 数 据 区 域 中 有 错 误 值 时 ， 计 算 出 现 错 误 值 。 示 例 ： 在 上 面 的 数 据 表 中 ，计 算 数 据 区 域 A2： A6 与 D2： D6 对 应 积 的 和 。 公 式 ： =SUMPRODUC

3、T(A2:A6,D2:D6) 因 为 D2： D6 中 有 错 误 值 #N/A， 所 以 公 式 返 回 值 为 错 误 值 #N/A。 数 据 区 域 引 用 不 能 整 列 引 用 。 示 例 ： 计 算 上 面 数 据 表 中 A2： A6 和B2： B6 区 域 对 应 数 据 积 的 和 ， 正 确 公 式 为 =SUMPRODUCT(A2:A6,B2:B6)则 返回 正 确 的 计 算 值 94。 如 果 用 公 式 =SUMPRODUCT(A:A,B:B) 则 返 回 错 误 值 #NUM！ 。 数 据 区 域 有 文 本 ， 计 算 中 系 统 默 认 文 本 值 为 0。

4、示 例 ： 在 上 面 数 据 表中 ， 计 算 A2： A6 和 E2： E6 区 域 中 对 应 数 据 积 的 和 。 公 式 =SUMPRODUCT(A2:A6,E2:E6) 返 回 值 是 392， 其 中 E5 是 文 本 KL， 则 A5*E5=0。 二 、 用 于 多 条 件 计 数 用 数 学 函 数 SUMOPRODUCT 计 算 符 合 2 个 及 以 上 条件 的 数 据 个 数 数 据 表 如 下 数 据 表 所 示 ： A B C1 姓名 性别 职称2 A 男 中一3 B 女 中二4 C 女 中一5 D 男 中一6 E 女 中一7 F 男 中二8 G 女 中二9 H

5、 男 中一10I 男 中一11J 女 中一统 计 表 E 列 F 列 G 列 中 一 中 二 男 女 要 求 ： 统 计 上 面 数 据 表 中 男 、 女 性中 分 别 是 中 一 、 中 二 的 人 数 。 如 下 图 片 ， A1： C11 数 据 区 域 ， 在 E1： G3 区域 统 计 男 、 女 中 中 一 和 中 二 的 人 数 是 多 少 。 计 算 结 果 公 式 在 数 据 统 计 区 域 中 的 F2 单 元 格 编 辑 如 下 公 式 ： =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 向 下 复 制 到 F3， 向 右 复

6、制 到 G3。 公 式 分 解 及 分 析 条 件 1$B$2:$B$11=$E2 在 计 算 过 程 中 ， 条 件 1 是 一 个 数 组 ， 返回 多 值 ， 写 成 公 式 如 下 ： =$B$2:$B$11=$E2 具 体 操 作 ： 选 中 10 个 连 续 的 单元 格 ， 输 入 上 述 公 式 后 ， 按 Ctrl+Shift+回 车 键 确 认 ， 返 回 10 个 逻 辑 判 断值 TRUE； FALSE； FALSE； TRUE； FALSE； TRUE； FALSE； TRUE； TRUE； FALSE。 这 10 个 逻 辑 判 断 值 构 成 一 个 新 的 由

7、TRUE 主 FALSE 组 成 的 数 组 1。 条 件 2$C$2:$C$11=F$1 与 条 件 1 相 同 ， 是 一 个 数 组 ， 返 回 多 值 ，写 成 公 式 如 下 ： =$C$2:$C$11=F$1 具 体 操 作 ： 同 样 的 方 法 ， 选 中 对 应 的 10连 续 单 元 格 ， 输 入 上 述 公 式 ， 按 Ctrl+Shift+回 车 键 确 认 ， 返 回 10 个 逻 辑判 断 值 TRUE； FALSE； TRUE； TRUE； TRUE； FALSE； FALSE； TRUE； TRUE； TRUE。 这 10 个 逻 辑 判 断 值 构 成 另

8、一 个 新 的 由 TRUE 主 FALSE 组 成 的数 组 2。 ($B$2:$B$11=$E2)*($C$2:$C$11=F$1)由 新 构 成 的 数 组 1 乘 以 数 组2， 即 ： =($B$2:$B$11=$E2)*($C$2:$C$11=F$1) =数 组 1*数 组 2 = TRUE； FALSE； FALSE； TRUE； FALSE； TRUE； FALSE； TRUE； TRUE； FALSE * TRUE； FALSE； TRUE； TRUE； TRUE； FALSE； FALSE； TRUE； TRUE； TRUE =TRUE*TRUE;FALSE*FALSE;F

9、ALSE*TRUE;TRUE*TRUE;FALSE*TRUE;TRUE*FALSE; FALSE*FALSE;TRUE*TRUE;TRUE*TRUE; FALSE*TRUE =1;0;0;1;0;0;0;1;1;0 其 中 ,逻 辑 值 TRU*与 *LSE 参 与 计 算 时 : TRUE=1， FALSE=0， TRUE*TRUE=1， TRUE*FALSE=FALSE*TRUE=0， FALSE*FALSE=0 因 此 数 组 1*数 组 2=1;0;0;1;0;0;0;1;1;0由 1 和 0 构 成 了 一 个 新的 数 组 3。 =SUMPRODUCT($B$2:$B$11=$E2

10、)*($C$2:$C$11=F$1) 函 数 SUMPRODUCT 对 新 的 数 组 3 中 的 所 有 数 据 求 和 。 即 ： =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1)=SUMPRODUCT(数组 1*数 组 2) =SUMPRODUCT(数 组 3) =SUMPRODUCT(1;0;0;1;0;0;0;1;1;0) =4 这 里 需 要 说 明 的 是 ， 公 式 编 辑 按 照 函 数 SUMPRODUCT 的 一 般 格 式 ， 可 以编 辑 如 下 等 效 的 公 式 ： =SUMPRODUCT($B$2:$B$11=$E2)*1

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

12、E *1) =SUMPRODUCT(1;0;0;1;0;1;0;1;1;0,1;0;1;1;1;0;0;1;1;1) =SUMPRODUCT(1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1) =SUMPRODUCT(1;0;0;1;0;0;0;1;1,0) =4 注 意 ： TRUE*1=1， FALSE*1=1*FALSE=0， TRUE*0=0*TRUE=0 。 数 组 中 用分 号 分 隔 ， 表 示 数 组 是 一 列 数 组 ， 分 号 相 当 于 换 行 。 两 个 数 组 相 乘 是 同 一 行的 对 应 两 个 数 相 乘 。 三 、 用 于 多

13、 条 件 求 和 对 于 计 算 符 合 某 一 个 条 件 的 数 据 求 和 ， 可 以 用SUMIF 函 数 来 解 决 。 如 果 要 计 算 符 合 2 个 以 上 条 件 的 数 据 求 和 ， 用 SUMIF函 数 就 不 能 够 完 成 了 。 这 就 可 以 用 函 数 SUMPRODUCT。 用 函 数 SUMPRODUCT 计 算 符 合 多 条 件 的 数 据 和 ， 其 基 本 格 式 是 ：SUMPRODUCT（ 条 件 1*条 件 2*， 求 和 数 据 区 域 ） 数 据 表 A B C D1 姓名 性别 职称 课时2 A 男 中一 153 B 女 中二 164

14、 C 女 中一 145 D 男 中一 136 E 女 中一 187 F 男 中二 158 G 女 中二 169 H 男 中一 1410I 男 中一 1711J 女 中一 18要 求 ： 计 算 男 、 女 分 别 是 中 一 或 中 二 的 总 课 时 数 。 统 计 表 F 列 G 列 H 列 中 一 中 二 男 女 在 G2 中 编 辑 公 式 =SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11) 回 车 确 认 后 向 下 向 右 复 制 公 式 到 H3 单 元 格 。 公 式 释 义 ： 性 别 区 域 $B$2:$B$11

15、 中 满 足 条 件 男 和 职 称 区 域 $C$2:$C$11中 满 足 条 件 中 一 的 数 据 ， 通 过 判 断 计 算 后 由 1 和 0 组 成 一 个 新 的 数 据 区 域 ，这 个 新 的 数 据 区 域 再 和 课 时 区 域 $D$2:$D$11 中 的 对 应 数 据 相 乘 后 求 和 。 公 式 对 比 ： 到 此 ， 对 函 数 SUMPRODUCT 用 来 计 数 和 求 和 ， 试 作 一 对 比 计 数 公 式 =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 求 和 公 式 =SUMPRODUCT($B$2:$B

16、$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11) 不 难 看 出 ， 求 和 公 式 在 原 来 的 计 数 公 式 中 ， 在 相 同 判 断 条 件 下 ， 增 加 了一 个 求 和 的 数 据 区 域 。 也 就 是 说 ， 用 函 数 SUMPRODUCT 求 和 ， 函 数 需 要 的 参数 一 个 是 进 行 判 断 的 条 件 ， 另 一 个 是 用 来 求 和 的 数 据 区 域 。 四 、 用 于 排 名 次 如 下 A 列 数 据 ： A 列 B 列 数 据 名 次56658965567890526090 需 要 排 出 区 域 A2： A11 中

17、 10 个 数 据 的 名 次 。 在 B2中 编 辑 公 式 ： =SUMPRODUCT(A2$A$2:$A$11)*1)+1 向 下 复 制 到 单 元 格 B11。 公 式 释 义 ： 用 A2 到 $A$2:$A$11(用 绝 对 引 用 $保 证 公 式 在 向 下 复 制 时 整个 数 据 区 域 不 发 生 变 化 )中 进 行 比 较 ,当 A2$A$2:$A$11 成 立 时 ,则 返 回TRUE;如 果 A2$A$2:$A$11 不 成 立 就 返 回 FALSE。 所 以 数 组 公 式=A2$A$2:$A$11 返 回 一 个 由 TRUE 和 FALSE 构 成 的

18、逻 辑 数 组 。 把 数 组 公 式=A2$A$2:$A$11 构 成 的 逻 辑 数 组 乘 1， 得 到 一 个 由 0 和 1 构 成 的 新 数 组 。SUMPRODUCT 再 对 由 0 和 1 构 成 的 新 数 组 求 和 ， 表 示 在 数 据 区 域 $A$2:$A$11中 比 A2 大 的 数 据 个 数 。 所 以 A2 在 数 据 区 域 $A$2:$A$11 内 排 列 的 位 次 应 该是 比 A2 大 的 数 据 个 数 +1， 即 公 式 =数 据 区 域 $A$2:$A$11 内 比 A2 大 的 个 数+1 =SUMPRODUCT(A2$A$2:$A$11

19、)*1)+1 公 式 向 下 复 制 ， 则 依 次 对 $A$2:$A$11 中 每 一 个 数 据 重 复 进 行 上 述 判 断求 和 ， 从 而 排 出 数 据 区 域 $A$2:$A$11 中 每 一 个 数 据 的 位 次 。 很 显 然 ， 对 于数 据 区 域 $A$2:$A$11 中 相 同 的 数 ， 判 断 和 计 算 结 果 是 相 同 的 ， 也 就 是 排 出来 的 位 次 相 同 。 所 以 用 SUMPRODUCT 函 数 排 出 来 的 名 次 ， 与 直 接 用 RANK 函数 排 出 来 的 名 次 是 一 样 的 ， 有 重 复 名 次 ， 但 最 大

20、位 次 数 不 超 过 数 据 区 域$A$2:$A$11 中 的 总 数 据 个 数 。 如 果 希 望 排 出 的 位 次 没 有 重 复 ， 而 数 据 区 域 $A$2:$A$11 中 相 同 数 据 的位 次 按 数 据 出 现 的 先 后 顺 序 排 位 ， 可 以 用 下 面 的 公 式 ： =SUMPRODUCT(A2$A$2:$A$11)*1)+COUNTIF($A$2:A2,A2) 公 式 向 下 复 制 即 可 。 两 种 排 名 对 比 如 下 表 所 示 ： A 列 B 列 C 列 数 据 名 次 重 复 顺 序 名 次 56 8 865 5 589 3 365 5

21、656 8 978 4 490 1 152 10 1060 7 790 1 2 综 上 所 述 ， 对 于 多 条 件 的 计 数 或 者 求 和 ， 可 以 用 数 学 函 数 SUMPRODUCT来 比 较 方 便 的 解 决 。 在 使 用 函 数 时 ， 进 行 数 据 引 用 的 单 元 格 区 域 或 数 组 应 该大 小 一 致 ， 不 能 采 取 整 列 引 用 （ 形 如 A： A） 。 如 果 跨 表 使 用 函 数SUMPRODUCT， 与 其 它 函 数 跨 表 引 用 数 据 一 样 ， 数 据 区 域 前 面 应 该 标 明 工 作 表名 称 。 color=#EE1D24,strength=3);计 数 公 式 中 最 关 键 的 是 确 定 计 数 的判 断 条 件 。 color=#EE1D24,strength=3);求 和 公 式 在 原 来 的 计 数 公 式 中 ，在 相 同 判 断 条 件 下 增 加 了 一 个 求 和 的 数 据 区 域 。color=#EE1D24,strength=3);用 函 数 SUMPRODUCT 求 和 ， 函 数 需 要 的 参 数一 个 是 进 行 判 断 的 条 件 ， 另 一 个 是 用 来 求 和 的 数 据 区 域 。