EXCEL常用函数

5
选择性粘贴
文本运算 符
含义
&(和号)
将两个文 本值连接 起来产生 一个连续 的文本值
OSRAM .COM
算术运算 符
+(加号)
含义 加
-(减号)
减
*(星号)
乘
/(斜杠)
除
%（百分 号）
百分比
^(脱字符) 乘方
33025--65535 CHAR的范 围
52946 我
B列为销售数量，C列是用RANK（）函数做的排名， 现在想实现D列式的排名，可以在D2单元格输入 数组公式： =SUM(IF(B$2:B$9>B2,1/COUNTIF(B$2:B$9,B$2:B $9),""))+1输入完毕后按CTRL+SHIFT+回车 表示 作为数组公式输入然后将D2公式向下拖动复制到 D9即可。
SUBTOTAL( function_ num, ref1, ref2, ...)
Function_n um 为 1 到 11 （包含隐藏 值）或 101 到 111（忽 略隐藏值） 之间的数 字，指定使 用何种函数 在列表中进 行分类汇总 计算。
1
2
3
4
5
101 AVERAGE 102 COUNT 103 COUNTA 104 MAX 105 MIN
AVERAGE /AVERAG EA
实现效 果： 操作方 法：
示例：
MAX/MAX A/MIN/MIN A
实现效 果：
求平均数
AVERAGE
/AVERAG EA（单元 格区域或 数值）
AVERAGE
AVERAGE A
求平均 12.4
7.875
7.875
求最大值 /最小值
数值
10
11
10
11
操作方 法：
合计
张三 李四 王五 赵六 马七 胡八 陈九 刘十 齐甲 张三 李四 王五 赵六 马七 胡八 15人
9.00 8.80 9.00 8.80 8.80 8.80 8.80 8.80 8.80 8.80 8.80 8.80 8.80 8.80 8.80 132.40
动态计数
3.80 3.60 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 2.90 45.10
ALT+=
=CHAR((C 38>89)*CO DE($G$38) +(C38>=75 )*(C38<=8 9)*CODE($ G$39)+(C3 8>=60)*(C 38<=74)*C ODE($G$4 0)+(C38>= 0)*(C38<= 59)*CODE( $G$41))
=IF(C30> 89,$G$30 ,IF(C30> 74,$G$31 ,IF(C30> 59,$G$32 ,$G$33)) )
动态求和
5.00 9.00 10.00 6.00 6.00 7.00 11.00 7.00 7.00 7.00 7.00 7.00 7.00 23.00 26.00 145.00
24.80 36.40 38.90 26.70 26.70 29.70 41.70 29.70 29.70 29.70 29.70 29.70 29.70 77.70 86.70 567.50
公式与函数
一.公式的
构成
1.1 函数的 基本构成
等号开头
函数名在中 间 括号结尾
1.2 函数参 数
参数的含义
参数的省略 参数的默认 值
[]表示这个 参数可省略
1.3 算术运
算符+ - * /%&^
1.31 TRUE 与FALSE 1.32 文本 、数值的比 较和转换
1与0
数字的两种 表现形式
例：本地考 生加30分， 本省加20 分，外省加 10分
★
中国式排名 评比
6 ★★ 4 ★★★ 2 ★★★★ 2 ★★★★ 5 ★★ 3 ★★★ 1 ★★★★ 1 ★★★★
=SUBTOT AL(4,C62: C69) =SUBTOT AL(5,C62: C69) =SUBTOT AL(1,C62: C69)
7
ቤተ መጻሕፍቲ ባይዱ
7
1
5
5
3
2
2
5
2
2
5
6
6
2
4
4
4
1
1
7
、去掉一个最低分， 个数，占总
姓名
性别
汪梅
男
郭磊
女
林涛
男
朱健
男
李明
女
来源 本地 本地 本省 本省 本省
原始分 599 661 467 310 584
总分 629 691 487 330 604
1
2
FALSE
0
0 0
34484 文本转数值 =code()
王建国
女
外省
陈玉
女
本省
1.4 逻辑运
算符= > < >= <= <>
例:依据评
示例：
MAX/MAX
A/MIN/MIN A（单元 格区域或 数值）
最大值 最小值
MAXA/MIN
MAX/MIN A
15
15
10
0
数值
10
11
☼ 小贴士：文本数值相互的强制转换 文本型数字或逻辑值转数值 --（双负号） *1或/1 +0或-0 0&（注：不是&0） VALUE() ……
☼ 小贴士：N函数和T函数
结果和上面的公式相同。
=TRIMMEAN(A1:A12,1/6) 结果和上面的公式相同。 (2)如果要去掉两个最好分和两个最低分，对剩下数求平均值，则可以直接 用公式： =TRIMMEAN(A1:A12,4/12)
4.9 SUBTOTA L函数 在部门栏选 择不同的部 门看看合计 1 的变化
合计行用了 subtotal函 数求和（而 不用SUM求 和），它会 按筛选范围 进行合计， 该函数说明 2 如下
张三
11500
1605
1605
=IF(IF(K 2-有关计 算比率 表!$B$1 2<=0,0,I F(K2-有 关计算比 率 表!$B$1 2<=500,( K2-有关 计算比率 表!$B$1
2)*0.05,I F(K2-有 关计算比 率 表!$B$1 2<=2000 ,(K2-有 关计算比 率 表!$B$1 2)*0.125,IF((K 2-有关计 算比率 表!$B$1
4.8 ROUND函 数
4.9 RAND 函数
4.6
5
5
4
三种取整 方式比较
=CEILING(A =ROUND( =TRUNC(
100,1) A100,) A100,)
TRIMMEAN（数组或引用，要去除的数据点比例）函数 例如：左边的示例，（1）求A1：A12中去掉一个最高分、去掉一个最低分， 然后求平均值： 常规做法是： =(SUM(A1:A12)-MAX(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12)-2) 而利用TRIMMEAN函数就方便多了，因为一个最高和一个最低是2个数，占总 个数12的百分比是2/12，即1/6，所以公式可以写成： =TRIMMEAN(A1:A12,1/6)
分级累进求 和
超过5000元
4
～20000元的 5000
部分
超过20000元
5
～40000元的 20000
部分
超过40000元
6
～60000元的 40000
部分
超过60000元
7
～80000元的 60000
部分
超过80000元
8
～100000元 80000
的部分
9
超过100000 元的部分
100000
N函数 T函数
数值 原值 空
文本 0
原文本
文本型数字 0
原文本型数字
数值转文本型数字 &""
文本函数如RIGHT/LEFT/MID等 TEXT函数 ……
逻辑值 1或0 空
重新编辑 <<双击单 元格
选择性粘贴
分列
1
1
=L13&""
=H14*1 2
2
单元格格式
=--H14 3
3
=H14+0 4
4
分列
5
=应发合计起征点
级数
全月应纳税 所得额
级别
1
不超过500元 的部分
0
超过500元～
2
2000元的部 500
分
超过2000元
3
～5000元的 2000
部分
税率 5% 10%
15%
77.00 88 83
速扣数 0 25
125
MAX(C62: C69)
MIN(C62:C 69)
AVERAGE (C62:C69) LARGE(C6 2:C69,2) SMALL(C6 2:C69,3)
工作表保护 test
zzyw
AAABBBA BAABB.
4.4 LARGE 函数
4.5 SMALL 函数
例：求总分
学科 姓名
数学
语文
英语
化学
平均分 总分
排名
李明
39.0
55.0
90.0
39.0
55.8
223.0
8
王小二
60.0
64.0
77.0
55.0
64.0
256.0
6
郑准
86.0
79.0
98.0
88.0
87.8
351.0
3
张大民
90.0
85.0
88.0
88.0
87.8
351.0
3
李节
43.0
47.0
54.0
85.0
57.3
229.0
7
阮大
56.0
71.0
49.0
83.0
64.8
259.0
5
孔庙
90.0
89.0
98.0
88.0
91.3
365.0
1
92.0
85.0
98.0
90.0
91.3
365.0
20%
375
25%
1375
30%
3375
35%
6375
40%
10375
45%
15375
1605
=MAX(应 纳税所得 额 *0.05*{1, 2,3,4,5,6 ,7,8,9}25*{0,1,5 ,15,55,1 35,255,4 15,615}, 0)
=ROUN D(MAX( 应纳税所 得额 *0.05*{1, 2,3,4,5,6 ,7,8,9}25*{0,1,5 ,15,55,1 35,255,4 15,615}, 0),2)
1
最高分: 92.0
89.0
98.0
90.0
最低分: 39.0
47.0
49.0
39.0
平均分: 69.50
71.88
81.50
正数第二名 的成绩:
90
85
98
倒数第三名 的成绩:
56
64
77
4.6 MIN函 数
4.7 MAX函 数
例：使用辅 助列计算个 人所得税
个人应税所得税
起征点
1600
应税所得额
平均值，则可以直接
工会费 养老保险 扣发合计 实发工资 签名
6.00 10.00 11.00
7.00 7.00 8.00 12.00 8.00 8.00 8.00 8.00 8.00 8.00 24.00 27.00 160.00
7.00 11.00 12.00
8.00 8.00 9.00 13.00 9.00 9.00 9.00 9.00 9.00 9.00 25.00 28.00 175.00
姓名
应发工资 扣税
=ROUN D(IF($C2 <0,0,IF($ C2<500, $C2*0.0 5,IF($C2 <2000,$ C2*0.125,IF($C 2<5000, $C2*0.1 5125,IF($ C2<2000 0,$C2*0. 2375,IF($ C2<4000 0,$C2*0. 251375,IF( $C2<600 00,$C2* 0.33375,$C 2*0.356375))))) )),2)
定标准为学 生填写评定 项
姓名
得分
评定
汪梅
90
优
郭磊
61
可
林涛
58
差
朱健
74
可
李明
45
差
王建国
98
优
陈玉
78
良
260
270
406
426
数值转文本 =char() 我
评定标准
90-100 优
75-89
良
60-74
可
0-59
差
=CHAR((C 38>=90)*C ODE("优 ")+(C38>= 75)*(C38< 90)*CODE( "良 ")+(C38>= 60)*(C38< 75)*CODE( "可 ")+(C38<6 0)*CODE(" 差")) =CHAR((C 38>=90)*C ODE("优 ")+(AND(( C38>=75),( C38<=89)) )*CODE(" 良 ")+(AND(C 38>=60,C3 8<=74))*C ODE("可 ")+(C38<= 59)*CODE( "差"))
6
106 PRODUCT
7
107 STDEV
8
108 STDEVP
9
109 SUM
10
110 VAR
11
111 VARP
序号 部门名称 姓名 基本工资 岗位工资 技术工资 津贴
其他 应发合计
1 办公室 2 办公室 3 财务 4 财务 5 财务 6 业务 7 业务 8 工会 9 工会 10 工会 11 工会 12 业务 13 业务 14 纪检 15 纪检
1.5 运算符 优先级
－ %
^ */ +& =,<,>,<=,>=,<>
三 函数使 用
3.1 手工编 写 3.2 使用插 入函数对话 框 3.3 函数错 误排查
四 认识函 数
4.1 SUM函 数 4.2 AVERAGE 函数 4.3 RANK 函数
负号 百分比 求幂 乘和除 加和减 文本连接 比较
3.00 7.00 8.00 4.00 4.00 5.00 9.00 5.00 5.00 5.00 5.00 5.00 5.00 21.00 24.00 115.00
4.00 8.00 9.00 5.00 5.00 6.00 10.00 6.00 6.00 6.00 6.00 6.00 6.00 22.00 25.00 130.00