涉及函数及相关知识点

函数名	格式	含义
ROW	=row([reference])	提取行号，参数可省略
ROWS	=rows(reference)	计算行数，参数不可省
COLUMNS	=column([reference])	提取列号，参数可省略
COLUMN	=columns(reference)	计算列数，参数不可省

其中参数为引用区域，以ROW函数为例：

若参数省略，则提取当前单元格的行号；若参数为单元格，则提取单元格的行号；

若参数为区域或多行单元格，需配合数组使用；

根据函数的定义，有以下几种用法：

1、 构造数列，如等差数列，其拓展可用于制作九九乘法表

2、 有规律的单元格引用，可配合相关数组对单元格数据有规律的引用

函数名	格式	含义
IF	=IF(logical_test, value_if_true, value_if_fause)	条件判断函数

其中logical_test可为逻辑值或数字，若为0，意味着FALSE，否则意味着TRUE

value_if_fause可省略，此时，若判断值为FALSE，结果为0

根据函数的定义，有以下几种用法：

1、 条件求平均。在未学习AVERAGEIF函数之前，一般用average结合if函数求平均，由于在判断过程中，如果直接用条件值与数值相乘，会得到数值，那么在计算平均值过程中会导致结果不准确，所以利用if函数，把逻辑值为FALSE时的值设为空，则average函数不会将其纳入计算范围。（注意，需要结合数组使用）

函数名	格式	含义
IFERROR	=IFERROR(value, value_if_error)	检查是否存在错误的参数
ISODD	=Isodd(number)	判断参数是不是奇数
ISEVEN	= ISEVEN(number)	判断参数是不是偶数

注意：IFERROR函数中参数不可省略；ISODD/ ISEVEN的参数只能为单个数值，不能为数组，但可以结合数组进行计算。

参数类型判断函数较多，一般以is开头，这里不多举例。

函数名	格式	含义
PRODUCT	=PRODUCT（number1,number2.....）	用于计算给出的数字的乘积

注意：product函数中参数可以用“，”或“”连接，且参数不能为字符（串）或错误值，若参数为逻辑值，且参数用“，”连接时，需要转换为数值。*

函数名	格式	含义
SUMIF	=SUMIF(range, criteria, [sum_range] )	条件求和，若条件区域和求和区域一致，求和区域可省略
SUMIFS	=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)	多条件求和
COUNTIF	=countif( range, criteria)	条件计数
COUNTIFS	=countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)	多条件计数
AVERAGEIF	AVERAGEIF(range, criteria, [average_range])	条件求平均
AVERAGEIFS	=averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)	多条件求平均

若条件参数涉及数值的单元格引用，可用&连接使用,本表格的函数中条件判断均可使用通配符，用法类似。

通配符	含义	备注
*	任何字符串
?	单一字符	注意中英文的区别
~	用于识别通配符自身

经典案例：

注意：以下案例均来自Scalers成长会2020Excel小组，欢迎来参加Excel小组第二期，具体参加方式见群文件。

案例一：九九乘法表

1×1=1	2×1=2	3×1=3	4×1=4	5×1=5	6×1=6	7×1=7	8×1=8	9×1=9
1×2=2	2×2=4	3×2=6	4×2=8	5×2=10	6×2=12	7×2=14	8×2=16	9×2=18
1×3=3	2×3=6	3×3=9	4×3=12	5×3=15	6×3=18	7×3=21	8×3=24	9×3=27
1×4=4	2×4=8	3×4=12	4×4=16	5×4=20	6×4=24	7×4=28	8×4=32	9×4=36
1×5=5	2×5=10	3×5=15	4×5=20	5×5=25	6×5=30	7×5=35	8×5=40	9×5=45
1×6=6	2×6=12	3×6=18	4×6=24	5×6=30	6×6=36	7×6=42	8×6=48	9×6=54
1×7=7	2×7=14	3×7=21	4×7=28	5×7=35	6×7=42	7×7=49	8×7=56	9×7=63
1×8=8	2×8=16	3×8=24	4×8=32	5×8=40	6×8=48	7×8=56	8×8=64	9×8=72
1×9=9	2×9=18	3×9=27	4×9=36	5×9=45	6×9=54	7×9=63	8×9=72	9×9=81

公式一：=CONCATENATE(COLUMN(A:A),"×",ROW(1:1),"=",ROW(1:1)*COLUMN(A:A))

公式二：=CONCATENATE(COLUMN(A:I),"×",ROW(1:9),"=",ROW(1:9)*COLUMN(A:I))

注释：CONCATENATE为连接函数，可用“&”代替

区别：公式一的结果为“1×1=1”，其他的算式需经过填充，通过改变行号和列号得到不同的算式；公式二需结合数组，可直接一步得到整个九九乘法表

案例二：

A1.jpg

总业绩（需结合数组使用）

公式一：

=SUM(IF(F12=B3,B4,IF(F12=C3,C4,IF(F12=D3,D4,IF(F12=E3,E4,IF(F12=F3,F4))))):IF(F13=B3,B9,IF(F13=C3,C9,IF(F13=D3,D9,IF(F13=E3,E9,IF(F13=F3,F9))))))

公式二：=SUM(OFFSET(A3:F9,1,MATCH(F12,B3:F3,FALSE),COUNTA(A4:A9),F13-F12+1))

注释：求和需要选中特定区域，公式一唯一的要点是用到了引用运算符“：”，尽管平时很常见，但可能不太容易想到这样的用法

公式二则是利用offset函数匹配起始年份和结束年份所对应的区域。

案例三：

A2.png

单月业绩

公式一：=SUM(ISODD(ROW(D119:D130))*E119:E130)

公式二：=SUM(IF(ISODD(ROW(D119:D130)),E119:E130,0))

解析：两个公式基本类似，就是变了写法，但是要注意的一点是：isodd函数的参数不能直接引用12个月份，会报错；而ROW(D119:D130)虽然结合数组计算结果是{119;120;121;122;123;124;125;126;127;128;129;130}，但并非一次引用多个数，所以可行

案例四：中国式排名

A3.png

公式：=SUMPRODUCT((B$20:B$24>=B20)/COUNTIF(B$20:B$24,B$20:B$24))

解析：公式乍一看不是太好理解，但是用到自带的【公式求值】功能，就能一步一步观察其求值过程。

(B24>=B20)是对整体所有分数进行判断，看是否大于等于目标分数；

COUNTIF(B24,B24)则是计算所有分数重复的次数，最后得到{1;1;2;2;1}

“/”使得{1;1;2;2;1}变为{1;1;0.5;0.5;1}，那么大于等于目标分数的为TRUE，与对应的数值相乘并累加后即可得到排名。

这个过程类似于有多种颜色的多个小球混合在一起，通过COUNTIF可求得每个小球对应颜色的小球个数，“/”使得数值转化为每个小球占对应颜色小球个数的比，而且这个比值累加得1。