EXCEL常用函数

公式与函数一.公式的构成1.1函数的基本构成等号开头函数名在中间括号结尾1.2函数参数参数的含义参数的省略[]表示这个参数可省略参数的默认值1.3算术运算符+-*/%&^121.31TRUE与FALSE1与0FALSE01.32文本、数值的比较和转换数字的两种表现形式00例：本地考生加30分，本省加20分，外省加10分姓名性别来源原始分总分汪梅男本地599629郭磊女本地661691林涛男本省467487朱健男本省31033034484李明女本省584604文本转数值=code()王建国女外省260270数值转文本=char()陈玉女本省406426我1.4逻辑运算符===例:依据评定标准为学生填写评定项姓名得分评定汪梅90优90-100优郭磊61可75-89良林涛58差60-74可朱健74可0-59差李明45差评定标准王建国98优陈玉78良=CHAR((C38=90)*CODE(优)+(C38=75)*(C3890)*CODE(良)+(C38=60)*(C3875)*CODE(可)+(C3860)*CODE(差))=CHAR((C38=90)*CODE(优)+(AND((C38=75),(C38=89)))*CODE(良)+(AND(C38=60,C38=74))*CODE(可)+(C38=59)*CODE(差))1.5运算符优先级=CHAR((C3889)*CODE($G$38)+(C38=75)*(C38=89)*CODE($G$39)+(C38=60)*(C38=74)*CODE($G$40)+(C38=0)*(C38=59)*CODE($G$41))=IF(C3089,$G$30,IF(C3074,$G$31,IF(C3059,$G$32,$G$33)))负号百分比求幂工作表保护testzzywAAABBBABAABB.乘和除加和减文本连接比较三函数使用3.1手工编写3.2使用插入函数对话框3.3函数错误排查四认识函数4.1SUM函数ALT+=4.2AVERAGE函数4.3RANK函数4.4LARGE函数4.5SMALL函数例：求总分学科姓名数学语文英语化学平均分总分排名李明39.055.090.039.055.8223.08王小二60.064.077.055.064.0256.06郑准86.079.098.088.087.8351.03张大民90.085.088.088.087.8351.03李节43.047.054.085.057.3229.07阮大56.071.049.083.064.8259.05孔庙90.089.098.088.091.3365.0192.085.098.090.091.3365.01最高分:92.089.098.090.0MAX(C62:C69)最低分:39.047.049.039.0MIN(C62:C69)平均分:69.5071.8881.5077.00AVERAGE(C62:C69)正数第二名的成绩:90859888LARGE(C62:C69,2)倒数第三名的成绩:56647783SMALL(C62:C69,3)4.6MIN函数4.7MAX函数例：使用辅助列计算个人所得税分级累进求和起征点1600*/+-&=,,,=,=,－%^个人应税所得税应税所得额=应发合计-起征点级数全月应纳税所得额级别税率速扣数1不超过500元的部分05%02超过500元～2000元的部分50010%253超过2000元～5000元的部分200015%1254超过5000元～20000元的部分500020%37516055超过20000元～40000元的部分2000025%13756超过40000元～60000元的部分4000030%33757超过60000元～80000元的部分6000035%63758超过80000元～100000元的部分8000040%103759超过100000元的部分10000045%15375=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}-25*{0,1,5,15,55,135,255,415,615},0)=ROUND(MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}-25*{0,1,5,15,55,135,255,415,615},0),2)姓名应发工资扣税=ROUND(IF($C20,0,IF($C2500,$C2*0.05,IF($C22000,$C2*0.1-25,IF($C25000,$C2*0.15-125,IF($C220000,$C2*0.2-375,IF($C240000,$C2*0.25-1375,IF($C260000,$C2*0.3-3375,$C2*0.35-6375))))))),2)张三1150016051605=IF(IF(K2-有关计算比率表!$B$12=0,0,IF(K2-有关计算比率表!$B$12=500,(K2-有关计算比率表!$B$12)*0.05,IF(K2-有关计算比率表!$B$12=2000,(K2-有关计算比率表!$B$12)*0.1-25,IF((K2-有关计算比率表!$B$12)=5000,(K2-有关计算比率表!$B$12)*0.15-125,IF((K2-有关计算比率表!$B$12)=20000,(K2-有关计算比率表!$B$12)*0.2-375,IF((K2-有关计算比率表!$B$12)=40000,(K2-有关计算比率表!$B$12)*0.25-1375,IF((K2-有关计算比率表!$B$12)=60000,(K2-有关计算比率表!$B$12)*0.3-3375,过了)))))))=过了,IF((K2-有关计算比率表!$B$12)=80000,(K2-有关计算比率表!$B$12)*0.35-6375,IF((K2-有关计算比率表!$B$12)=100000,(K2-有关计算比率表!$B$12)*0.4-10375,(K2-有关计算比率表!$B$12)*0.45-15375)),IF(K2-有关计算比率表!$B$12=0,0,IF(K2-有关计算比率表!$B$12=500,(K2-有关计算比率表!$B$12)*0.05,IF(K2-有关计算比率表!$B$12=2000,(K2-有关计算比率表!$B$12)*0.1-25,IF((K2-有关计算比率表!$B$12)=5000,(K2-有关计算比率表!$B$12)*0.15-125,IF((K2-有关计算比率表!$B$12)=20000,(K2-有关计算比率表!$B$12)*0.2-375,IF((K2-有关计算比率表!$B$12)=40000,(K2-有关计算比率表!$B$12)*0.25-1375,IF((K2-有关计算比率表!$B$12)=60000,(K2-有关计算比率表!$B$12)*0.3-3375,过了))))))))4.8ROUND函数4.9RAND函数4.6554三种取整方式比较=CEILING(A100,1)=ROUND(A100,)=TRUNC(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)结果和上面的公式相同。(2)如果要去掉两个最好分和两个最低分，对剩下数求平均值，则可以直接用公式：=TRIMMEAN(A1:A12,4/12)4.9SUBTOTAL函数1在部门栏选择不同的部门看看合计的变化2合计行用了subtotal函数求和（而不用SUM求和），它会按筛选范围进行合计，该函数说明如下SUBTOTAL(function_num,ref1,ref2,...)Function_num   为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。1101AVERAGE6106PRODUCT2102COUNT7107STDEV3103COUNTA8108STDEVP4104MAX9109SUM5105MIN10110VAR11111VARP序号部门名称姓名基本工资岗位工资技术工资津贴其他应发合计1办公室张三9.003.803.004.005.0024.802办公室李四8.803.607.008.009.0036.403财务王五9.002.908.009.0010.0038.904财务赵六8.802.904.005.006.0026.705财务马七8.802.904.005.006.0026.706业务胡八8.802.905.006.007.0029.707业务陈九8.802.909.0010.0011.0041.708工会刘十8.802.905.006.007.0029.709工会齐甲8.802.905.006.007.0029.7010工会张三8.802.905.006.007.0029.7011工会李四8.802.905.006.007.0029.7012业务王五8.802.905.006.007.0029.7013业务赵六8.802.905.006.007.0029.7014纪检马七8.802.9021.0022.0023.0077.7015纪检胡八8.802.9024.0025.0026.0086.70合计15人132.4045.10115.00130.00145.00567.50AVERAGE/AVERAGEA实现效果：求平均数操作方法：AVERAGE/AVERAGEA（单元格区域或数值）示例：AVERAGEAVERAGEA数值1011求平均12.47.8751011MAX/MAXA/MIN/MINA7.875实现效果：求最大值/最小值动态计数动态求和操作方法：MAX/MAXA/MIN/MINA（单元格区域或数值）示例：MAX/MINMAXA/MINA数值1011最大值1515最小值100数值文本逻辑值N函数原值01或0T函数空原文本空*1或/1文本函数如RIGHT/LEFT/MID等+0或-0TEXT函数文本型数字0☼小贴士：N函数和T函数……--（双负号）原文本型数字0&（注：不是&0）……VALUE()☼小贴士：文本数值相互的强制转换文本型数字或逻辑值转数值数值转文本型数字&文本运算符含义&(和号)将两个文本值连接起来产生一个连续的文本值OSRAM.COM算术运算符含义+(加号)加重新编辑双击单元格-(减号)减选择性粘贴*(星号)乘分列/(斜杠)除%（百分号）百分比11=L13&^(脱字符)乘方=H14*122单元格格式=--H1433=H14+044分列55选择性粘贴33025---65535CHAR的范围52946我=CHAR((C38=90)*CODE(优)+(C38=75)*(C3890)*CODE(良)+(C38=60)*(C3875)*CODE(可)+(C3860)*CODE(差))=CHAR((C38=90)*CODE(优)+(AND((C38=75),(C38=89)))*CODE(良)+(AND(C38=60,C38=74))*CODE(可)+(C38=59)*CODE(差))=CHAR((C3889)*CODE($G$38)+(C38=75)*(C38=89)*CODE($G$39)+(C38=60)*(C38=74)*CODE($G$40)+(C38=0)*(C38=59)*CODE($G$41))=IF(C3089,$G$30,IF(C3074,$G$