Excel中的常见操作

1.筛选后求和、求平均值

参考：subtotal函数，SUBTOTAL function - Microsoft Support

在经过筛选后，如果使用sum()函数进行求和，选择的其实包含了被筛选掉的列。也即选中时并没有选中筛选后的列，这样求出来的和不是想要的。注：在选中筛选的内容后，excel右下角的求和是正确的。

改用subtotal函数：

SUBTOTAL(function_num,ref1,[ref2],...)

求和：

SUBTOTAL(109,选中列)

求均值：

SUBTOTAL(101,选中列)

Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded. （1-11包含了hidden rows，101-111剔除了filtered-out cells）

注：且筛选内容变了后，对应的结果也会跟着变，不用把函数删掉再重写。

2.vlookup根据两列数据匹配

参考 https://zhuanlan.zhihu.com/p/68744986

当两列数据确定唯一值时，例如：有数据：

季度	客户经理	接待人数	开户数
21Q1	A	xx	？
21Q2	A	xx
21Q3	A	xx
21Q1	B	xx
21Q2	B	xx
21Q3	B	xx

此时想根据季度与客户经理去匹配另一张表中的开户数时，有如下方法：

VLOOKUP(E2&F2,IF({1,0},A1:A13&B1:B13,C1:C13),2,FALSE)

↑在开户数这一列，写入上面的公式，其中E2与F2指季度与客户经理这两列(单元格)，A1:A13与B1:B13指有开户数的那张表中的季度与客户经理两列（用&拼接起来），C1:C13为开户数那一列。

IF({1,0},A1:A13&B1:B13,C1:C13) 的含义：
IF(条件,返回值1,返回值2)，如果条件为真，返回"返回值1"，如果条件为假，返回"返回值2"。
{1,0}，则代表选择一个数组，第一列为真，即第一列返回"返回值1"，A1:A13&B1:B13，第二列为假，即第二列返回"返回值2"，C1:C13

可以认为IF({1,0})的写法是为了选取不连续的列(拼接后的数组看成一列，并再选中其他列)放到一个参数中

倒数第二个参数2代表C1:C13开户数这列，位于选取的范围中的第2列（A1:A13&B1:B13视为第1列）

3.countif函数

参考 Excel函数公式：关于Countif函数的用法，你知道多少？

countif(range, criteria)

示例1：

计算电脑的销售数量：

=COUNTIF(C2:C7,"电脑")

另：可结合通配符使用。例如统计包含”电脑“的销售数量：

=COUNTIF(C2:C7,"*电脑*")

示例2：

指定条件计数，例如统计销售额大于平均金额的个数：

=COUNTIF(D2:D7,">"&AVERAGE(D2:D7))

条件用到了另外的一个函数，我们需要用符号“&”来作为连接符号。否则无法得到正确的结果。

4.sumif函数

参考 Excel函数公式：条件求和：SUMIF函数！！！

SUMIF(range,criteria,[sum_range])

range:用于条件计算的单元格
criteria:用于确定对求和单元格的田间
sum_range:要求和的实际单元格。如果省略sum_range参数，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。

示例1：

求女生成绩之和：

=SUMIF(C2:D6,"女",D2:D6)
也可写作
=SUMIF(C2:C6,"女",D2:D6)

示例2：

大于89的成绩之和：

=SUMIF(D2:D6,">89")

示例3：

李丽，李章，王程成绩之和：

=SUM(SUMIF(B2:B6,{"李丽","李章","王程"},D2:D6))

示例4：

求前3名成绩之和：

=SUMIF(D2:D6,">="&LARGE(D2:D6,3))

函数LARGE(RANGE,K)的作用是返回数组中第K个最大值

↑注：如果有两个第3大的值，large(range,3) 与 large(range,4) 会返回一样的值。