excel常用函数公式大全文档（Excel提高班-常用函数之数学函数）

各位小伙伴大家好，我们上节讲了Excel常用函数之数学函数（一），本节让我们来看一下Excel常用函数之数学函数（二），本节我们主要讲Sum，sumif，SUMIFS，sumproduct，和SUBTOTAL

一、SUMIFS

1、语法：

表达式：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

中文表达式：SUMIFS(求和区域，条件区域1，条件1，[条件区域2，条件2]，...)

2、说明：

A、表达式中，前三个参数是必需的，括号（[]）中的参数是可选的，省略号（...）表示继续构建[条件区域3，条件3]、[条件区域4，条件4]、...、[条件区域n，条件n]；

B、可以在条件中使用通配符“问号 (?) 和星号 (*)”，问号匹配任意单个字符，星号匹配任意一个或一串字符；如果要找“? 和 *”，需要在它们前面加转义字符 ~，例如要查找 ?，需要这样写 ~?。

C、如果在条件中使用文本条件、含有逻辑或数学符号的条件都必须用双引号 (") 括起来；例如使用大于号，应该这样写：">50" 或 ">"&50。

D、SumIfs 只对数值求和，文本则忽略，如果选中的求和区域全为文本，则返回 0；如果既有文本又有数值，则只取数值求和。

3、实例

3.1 单条件求和-统计广州的总销量

3.2 多条件求和-统计广州，T恤的总销量

3.3 数组条件且与Sum函数结合-统计在广州和深圳销售的衬衫销量之和

（知识点： SumIfs 函数用来分别统计在“广州”和“深圳”销售的“衬衫”销量之和，Sum函数用来把 SumIfs 求出的在“广州”和“深圳”销售的“衬衫”销量之和加起来。）

3.4 多数组条件-统计在广州和杭州销售的价格为 59、75或 97 元的服装销量之和

（知识点：条件2 {59;75;97} 中数字之间用半角分号（;），如果用半角逗号（,），只会返回第一条满足条件的销量）

3.5 用通配符组合条件-统计产品名称为四个字、销售地区含有“州”字、价格大于60元的全部服装销量之和

（知识点：由三组“条件区域和条件”组成；第一组（D6:D15,"????"）是从 D6:D15 中找出名称为四个字的服装；第二组（E6:E15,"*州"）是从 E6:E15 中找出含有“州”字的服装；第三组（F6:F15,">60"）是从 F6:F15中找出“价格”大于 60 元的服装。最后把三组统计出的服装销量用 Sum 求和）

二、SUMPRODUCT

对于sumproduct函数，公式参数特别简单，即=SUMPRODUCT（数组1,数组2,数组3, ……），每个数组之间用逗号隔开，表示数组之间先相乘再求和。

1、基础用法-求两列数值的乘积之和

2、单条件求和-求广州市的所有销量

3、多条件求和-求广州市，T恤的所有销量

4、单条件计数-统计出现广州市的数量

（知识点：N函数的主要作用为：将不是数值形式的值转换为数值形式，N函数也被称为Excel最短函数。）

5、多条件计数-统计出现广州市，价格大于60的数量

6、条件求乘积-分别统计T恤，衬衫，雪纺的销售额（价格*销量）

7、排名-根据销量排名

8、条件排名- 按照类别对销量分类排名

9、中国式排名

知识点：利用sumprodunct排名时，在L26单元格输入公式=SUMPRODUCT(($K$26:$K$35>K26)/COUNTIF($K$26:$K$35,$K$26:$K$35)) 1即可。这个函数比较难理解。对于L26单元格，COUNTIF($K$26:$K$35,$K$26:$K$35)函数表示条件计数，如果有重复值，则返回重复的个数，此处返回的结果是1;1;2;2;1;2;2;1;1;1，而用1/COUNTIF($K$26:$K$35,$K$26:$K$35)表示相同的数字只统计一次（因为每个重复的数字都被平均了）。返回结果为1;1;0.5;0.5;1;0.5;0.5;1;1;1，然后($K$26:$K$35>K26)/COUNTIF($K$26:$K$35,$K$26:$K$35)的返回结果为0;1;0;0;1;0;0;1;1;0，其中$K$26:$K$35>K26采用的是相对引用，所以向下填充会返回不同的结果。直接决定了计算的相对名次。然后再用sumproduct函数对上面返回结果求和，最后 1对结果修正。

三、SubTotal

1、表达式：SUBTOTAL(Function_Num, Ref1, [Ref2], ...)

中文表达式：SubTotal(函数序号, 汇总区域1，[汇总区域2])

2、说明：

A、函数序号分为两组，一组为 1 到 11，另一组为 101 到 111，它们都对应 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp 这 11 个函数，其中序号 1 至 11 不忽略隐藏值，101 到 111 忽略隐藏值，如图1所示：

B、汇总区域 Ref 参数至少有一个，最多只能有 254 个。

二、SubTotal函数的使用方法及实例

(一）包含隐藏行与不包含隐藏行的实例

1、右键第三行行号 3，在弹出的菜单中选择“隐藏”，则第三行被隐藏；把公式 =SUBTOTAL(9,D2:D6) 复制到 D7 单元格，按回车，返回结果 2977；双击 D7 单元格，把公式中的 9 改为 109，按回车，返回结果 2085；操作过程步骤，如图2所示：

图2

2、公式说明：公式 =SUBTOTAL(9,D2:D6) 中的 9 代表求和函数 Sum，D2:D6 为求和区域；当为 9 时，求和结果为 2977；当把 9 改为 109（109 也代表求和函数 Sum），求和结果为 2085；说明 9 包含了隐藏的第三行，109 没有包含隐藏的第三行，即函数序号为 1 到 11 包含隐藏行、101 到 111 不包含隐藏行。

(二）忽略已有分类汇总的实例

1、假如有一个已经按“类别”分类汇总的表格，如图3所示：

图3

2、选中 E13 单元格，把公式 =SUBTOTAL(9,E2:E12) 复制到 E13，如图4所示：

图4

3、按回车，返回对 E2:E12 的求和结果 5151，如图5所示：

图5

4、返回结果与总计相同，说明返回的结果没有包含对“T恤、衬衫、雪纺和总计”的汇总结果，否则返回结果为 5151 的两倍。

（三）忽略不包含在筛选结果中的行的实例

1、把公式 =SUBTOTAL(9,E2:E8) 复制到 E9 单元格，按回车，返回结果 5151；选中 E 列，选择“数据”选项卡，单击“筛选”图标，则 E 加上筛选下拉列表图标，单击该图标，在弹出的菜单中依次选择“数字筛选”→ 大于，打开“自定义自动筛选方式”窗口，在“大于”右边输入 700，单击“确定”，则筛选出“销量”大于 700 的服装，“销量”小于等于 700 的被隐藏；E9 中的 SubTotal 汇总结果也自动变为 3645，说明“销量”小于等于 700 的被隐藏的行被剔除汇总结果；双击 E9，把公式中的 9 改为 109，按回车，同样返回 3645；操作过程步骤，如图6所示：

图6

2、从操作过程可知，函数序号无论是 1 到 11 还是 101 到 111 都忽略不包含在筛选结果中的行。

（四）对行分类汇总隐藏值对汇总结果的影响实例

1、选中 F2 单元格，把公式 =SUBTOTAL(109,B2:E3) 复制到 F2，按回车，返回结果 3215；右键第三行行号 3，在弹出的菜单中选择“隐藏”，则把第三行隐藏，F2 中分类汇总结果也随之变为 1614，按 Ctrl Z 取消隐藏第三行；右键第四列顶部 D，在弹出的菜单中选择“隐藏”把 D 列隐藏，F2 中的分类汇总结果仍然是 3215；操作过程步骤，如图7所示：

图7

2、说明：当隐藏行时，SubTotal函数汇总结果变小，说明被隐藏的第三行被剔除汇总结果；当隐藏列时，SubTotal函数汇总结果不变，说明隐藏列不影响汇总结果；此种情况适用于函数序号为 101 到 111，当函数序号为 1 到 11 是，无论隐藏行还是列，都不会影响汇总结果。

（五）一次引用两个区域的实例

1、假如要汇总 B 列和 D 列。选中 B10 单元格，把公式 =SUBTOTAL(9,B2:B9,D2:D9) 复制到 B10，按回车，返回结果 10158，操作过程步骤，如图8所示：

图8

2、一次汇总多列，如果它们连在一起，引用一次区域即可；只有它们隔开列时才分开写，如演示中的 B 列和 D 列。

好了 今天的sumifs，sumproduct，subtotal三个求和函数讲到这里了，剩下的下节课再讲~~

,