Excel公式表

智能填充

1. 使用IF和COUNTA函数跳过空行

有时在我们的数据里，不可避免的存在一些空行。我们希望序号可以跳过这些空行。

=IF(COUNTA(行)=0, "",COUNTA(累积列))

例如：

=IF(COUNTA(C1:D1)a=0,"",COUNTA($C$1:C1))

2. 使用COUNTA函数设置合并单元格序号

COUNTA(累积合并区域)

例如:

=COUNTA($B$2:B2)

Excel统计唯一不重复项数量

=SUMPRODUCT(1/COUNTIF(D:D,D2:D12))

Excel多条件查找公式（数字类）

=SUMIFS(求���区域，条件区域1，条件1，[条件区域2，条件2],...)

例如：

=SUMIFS(D1:D9,A1:A9,F2,B1:B9,G2,C1:C9,H2)

IF函数

=IF(测试条件,真值,[假值])

例如：如果性别为男，则称呼为先生，性别为女，则称呼为女士

=IF(E2="男","先生","女士")

例如：专业类后加专业代号：理工-LG；文科-WK；财经-CJ

=IF(B2="理工","LG",IF(B2="文科","WK","CJ"))

例如：本地学生 总分为原始分加 30；本省学生 总分为原始分加 20；外省学生 总分为原始分加 10

=IF(G2="本地",H2+30,IF(G2="本省",H2+20,H2+10))

例如：录取情况：600分含600 显示 第一批；400-600含400分 显示 第二批；400分以下 落榜

IF(I2>=600,"第一批",IF(I2>=400,"第二批","落榜"))

例如：对于60岁以上（含）的男性员工给予1000元奖金

=IF(AND(A2="男",B2>=60),1000,0)

例如：对于60岁以上或40岁以下的员工给予1000元奖金

=IF(OR(B9>60,B9<40),1000,0)

例如：对于60岁以上的男员工或40岁以下的女员工给予1000元奖金

=IF(OR(AND(A16="男",B16>60),AND(A16="女",B16<40)),1000,0)

COUNTIF

语法：

=countif(区域，条件)

例如：计算班级男生女生各有几人

=COUNTIF(E:E,M5)

例如：计算大于600分的人数

=COUNTIF(I:I,">=600")

例如：计算超过15位数字，比如身份证号

=COUNTIF(Q:Q,Q1&"*")

例如：看是否已参加体检

=COUNTIF(O:O,D2)

=IF(COUNTIF(O:O,D2)=0,"未体检","已体检")

sunif函数

语法：

=SUMIF(区域，条件，[求和区域])

例如：求班级所有男生的总分

=SUMIF(E:E,O2,I:I)

sumifs函数

语法：

=sumifs(求和区域，区域1，条件1，...)

例如：计算本地的男女生总分各为多少

=SUMIFS(I:I,E:E,O2,G:G,P2)

Vlookup函数

语法：

=vlookup(查找值，数据表，列序数，[匹配条件])

例如：根据姓名查找学生的原始分数

=VLOOKUP(O2,D:H,5,0)

Match函数+index函数

语法：
match

=match(查找值，查找区域，[匹配类型])

index

=index(数组，行序数，[列序数],[区域序数])

1. 用match函数找到学生的数据在第几行

=MATCH(O2,D:D,0)

2. 用index函数找到引用的数据

=INDEX(A:A,Q2)

3. 通过学生的姓名找到对应的编号

=INDEX(A:A,MATCH(O2,D:D,0))

4. 通过学生的姓名找到其它对应的信息

=INDEX(A:A,MATCH($O2,$D:$D,0))

使用vlookup函数

=VLOOKUP($O12,$D:$K,COLUMN()-14,0)

顺序跟原表不一致

=VLOOKUP($N2,$A$1:$K$23,MATCH(O$1,$A$1:$K$1,0),0)

简单文本函数

1. left

=left(字符串，[字符个数])

截取前两位字符

=LEFT(A3,2)

2. right

=right(字符串，[字符个数])

截取后三位字符

=RIGHT(A3,3)

3. mid

=mid(字符串,开始位置.字符个数)

截取第二位到第三位的字符

=MID(A3,2,2)

4. find

=find(要查找的字符串，被查找字符串，[开始位置])

例如：找到@在第几位

=FIND("@",A25)

5. len、lenb

=len（字符串）
=lenb（字符串）

例如：截取表里的中文

=RIGHT(A41,LENB(A41)-LEN(A41))

数学函数

1. round函数（四舍五入函数）

=round(数值，小数位数)

2. roundup(远离零值，向上（绝对值增长的方向）舍入数字)

3. rounddown（靠近零值，向下（绝对值减小的方式）舍入数字）