excel常用公式函数教程.docx

excel常用公式函数教程.docx

《excel常用公式函数教程.docx》由会员分享，可在线阅读，更多相关《excel常用公式函数教程.docx（9页珍藏版）》请在冰豆网上搜索。

excel常用公式函数教程

excel经常运用公式函数教程

1．乞降函数SUM

语法：

SUM（number1,number2,．．．）.

参数：

number1.number2．．．为1到30个数值（包含逻辑值和文本表达式）.区域或引用,各参数之间必须用逗号加以分隔.

留意：

参数中的数字.逻辑值及数字的文本表达式可以介入盘算,个中逻辑值被转换为1,文本则被转换为数字.假如参数为数组或引用,只有个中的数字介入盘算,数组或引用中的空白单元格.逻辑值.文本或错误值则被疏忽.

运用实例一：

跨表乞降

运用SUM函数在统一工作表中乞降比较简略,假如须要对不合工作表的多个区域进行乞降,可以采取以下办法：

选中ExcelXP“拔出函数”对话框中的函数,“肯定”后打开“函数参数”对话框.切换至第一个工作表,鼠标单击“number1”框后选中须要乞降的区域.假如统一工作表中的其他区域须要介入盘算,可以单击“number2”框,再次选中工作表中要盘算的其他区域.上述操纵完成后切换至第二个工作表,反复上述操纵即可完成输入.“肯定”后公式地点单元格将显示盘算成果.

运用实例二：

SUM函数中的加减混杂运算

财务统计须要进行加减混杂运算,例如扣除现金流量表中的若干支出项目.按照划定,工作表中的这些项目没有输入负号.这时可以结构“=SUM（B2:

B6,C2:

C9,-D2,-E2）”如许的公式.个中B2:

B6,C2:

C9引用是收入,而D2.E2为支出.因为Excel不许可在单元格引用前面加负号,所以应在暗示支出的单元格前加负号,如许即可盘算出准确成果.即使支出数据地点的单元格持续,也必须用逗号将它们逐个离隔,写成“=SUM（B2:

B6,C2:

C9,-D2,-D3,D4）”如许的情势.

运用实例三：

合格人数统计

假如B1:

B50区域存下学素性别,C1:

C50单元格存放某班学生的测验成绩,要想统计测验成绩合格的女生人数.可以运用公式“=SUM（IF（B1:

B50=″女″,IF（C1:

C50>=60,1,0）））”,因为它是一个数组公式,输入停止后必须按住Ctrl+Shift键回车.公式双方会主动添加上大括号,在编辑栏显示为“{=SUM（IF（B1:

B50=″女″,IF（C1:

C50>=60,1,0）））}”,这是运用数组公式必不成少的步调.

语法：

AVERAGE（number1,number2,．．．）.

参数：

number1.number2．．．是须要盘算平均值的1～30个参数.

留意：

参数可所以数字.包含数字的名称.数组或引用.数组或单元格引用中的文字.逻辑值或空白单元格将被疏忽,但单元格中的零则介入盘算.假如须要将参数中的零清除在外,则要运用特别设计的公式,下面的介绍.

运用实例一：

跨表盘算平均值

标签名为“一班”.“二班”和“三班”的工作表存放各班学生的成绩,则它们的总平均分盘算公式为“=AVERAGE（一班!

C1:

C36,三班!

C1:

C32,三班!

C1:

C45）”.式中的引用输入办法与SUM跨表乞降时雷同.

运用实例二：

疏忽零的平均值盘算公式

假设A1:

A200随机存放包含零在内的48个数值,在AVERAGE参数中去失落零引用很麻烦,这种情形可以运用公式“=AVERAGE（IF（A1:

A200<>0,A1:

A200,″″）”.公式输入停止后按住Ctrl+Shift回车,即可对A1:

A200中的数值（不包含零）盘算平均值.

3．逻辑函数IF

语法：

IF（logical_test,value_if_true,value_if_false）.

参数：

logical_test是成果为true（真）或false（假）的数值或表达式;value_if_true是logical_test为true时函数的返回值,假如logical_test为ture并且省略了value_if_true,则返回true.并且value_if_true可所以一个表达式;value_if_false是logical_test为false时函数的返回值.假如logical_test为false并且省略value_if_false,则返回false.Value_if_false也可所以一个表达式.

运用实例一：

小我收入调节税盘算

假设小我收入调节税的稽征办法是：

工资在1000元以下的免征调节税,工资1000元以上至1500元的超出部分按5%的税率征收,1500元以上至2000元的超出部分按8%的税率征收,高于2000元的超出部分按30%的税率征收.

假如工作表的B列存放职工姓名,C列存下班资,选中D列的空白单元格输入公式“=IF（C2<=1000,″″,IF（（C2-1000）<=1500,（C2-1000）*0.05,IF（C2-E2<=1500,（C2-1500）*0.08,IF（C2>2000,（C2-2000）*0.3））））”,回车后即可盘算出C2工资应缴纳的收入调节税.

公式中的IF语句是逐次盘算的,假如第一个逻辑断定C2<=1000成立,则公式地点单元格被填入空格;假如第一个逻辑断定式不成立,则盘算第二个IF语句;直至盘算停止.假如税率征收尺度产生了变更,只须转变逻辑和盘算式中的值,如1000.1500和2000等即可.

运用实例二：

清除无意义的零

用SUM函数盘算工资总额等问题时,若引用的全体参数均为空白单元格,公式仍然管帐算出一个“0”.如许打印出来的报表不但很不美不雅.为此可将盘算公式设计为“=IF（SUM（A1:

B1,D2:

E8）<>0,SUM（A1:

B1,D2:

E8）,″″）”,等于当SUM（A1:

B1,D2:

E8）盘算成果不等于零时,公式地点单元格显示SUM（A1:

B1,D2:

E8）的盘算成果,不然显示一个空格.

上面这个问题在财务盘算中依旧消失,如公式“=A1-A6-A9”有可能为零或显示负数.为了防止打印零或负数,可以设计公式“=IF（A2-A6-A9=0,″″,IF（A2-A6-A9<0,RMB（A2-A6-A9）,A2-A6-A9））”.当A2-A6-A9=0时在单元格中填写零,不然进行下一个逻辑断定.假如A2-A6-A9<0则在成果前添加一个“￥”符号,不然进行正常运算直至停止.

运用实例三：

多前提乞降

假设C1:

C460区域内存放着职工的职称,D1:

D460区域存放着职工的性别.假如要统计具有高等职称的男性职工总数,可以运用公式“=SUM（IF（C1:

C460=″高等″,IF（D1:

D460=″男″,1,0）））”.这是一个数组公式,输入停止后按住Ctrl+Shift回车即可盘算出成果.

4．快捷方法或链接创建函数HYPERLINK

语法：

HYPERLINK（link_location,friendly_name）.

参数：

link_location是文件的路径和文件名,它可以指向文档中的某个具体地位.如Excel工作表中的特定单元格或区域,或某个word文档中的书签,也可以指向硬盘中的文件或是Internet或Intranet的URL.Friendly_name为单元格中显示的链接文字或数字,它用蓝色显示并带有下划线.假如省略了friendly_name,单元格就将link_location显示为链接.

运用实例：

治理文档或网站地址

假如你失去大量文档或收集了很多网站地址,治理起来必定有艰苦.假如可以或许将个中的文档名.URL等与文档等对象链接起来,不但治理便利,还可以直接打开文档或拜访站点.具体操纵办法是：

起首根据文档类型树立治理工作表,个中的文件名或网站名必须运用以下办法输入：

选中一个空白单元格,单击ExcelXP对象栏中的“粘贴函数”按钮,打开“粘贴函数”对话框,在“函数分类”框下选中“查找与引用”,然后在“函数名”框内找到HYPERLINK函数.单击对话框中的“肯定”按钮,弹出“HYPERLINK”函数领导.在“link_location”框中输入文件的完全路径和名称（包含扩大名）,如“”,然后在“friendly_name”框中输入文件名（如“IT网站集锦”）.确认输入无误单击“肯定”按钮,所选单元格即消失带下划线的紫色文件名“IT网站集锦”.

此后你就可以从工作表中打开文档,办法是：

打开工作表,在个中找到须要打开的文件.箭头光标指向文件名会变成手形,若逗留少焉则会显示该文件的完全路径和名称,单击则会挪用联系关系程序将文件打开.

假如你收集了很多网站的URL,还可以用此法树立一个大型“珍藏夹”.既可以用Excel的壮大功效进行治理,又可以从工作表中直接拜访Web站点.

5．计数函数COUNT

语法：

COUNT（value1,value2,．．．）.

参数：

value1,value2．．．是包含或引用各类数据的1～30个参数.

留意：

COUNT函数计数时数字.日期或文本暗示的数字会介入计数,错误值或其他无法转换成数字的文字被疏忽.假如参数是一个数组或引用,那么只稀有组或引用中的数字介入计数;个中的空白单元格.逻辑值.文字或错误值均被疏忽.

运用实例：

合格率统计

假如C1:

G42存放着42逻辑学生的测验成绩,在一个空白单元格内输入公式“=COUNTIF（C1:

C42,″>=60″）/COUNTA（C1:

C42）”回车,即可盘算出该列成绩的合格率（即分数为60及以上的人数占总人数的百分比）.

6．最大值函数MAX.最小值函数MIN

语法：

MAX（number1,number2,．．．）,MIN（number1,number2,．．．）.

参数：

number1,number2．．．是须要找出最大值（最小值）的1至30个数值.数组或引用.

留意：

函数中的参数可所以数字.空白单元格.逻辑值或数字的文本情势,假如参数是不克不及转换为数字的内容将导致错误.假如参数为数组或引用,则只稀有组或引用中的数字介入盘算,空白单元格.逻辑值或文本则被疏忽.

运用实例：

查询最高分（最低分）

假如C1：

G42存放着42逻辑学生的测验成绩,则选中一个空白单元格,在编辑栏输入公式“=MAX（C1:

C42）”,回车后即可盘算出个中的最高分是若干.

假如将上述公式中的函数名改为MIN,其他不变,就可以盘算出C1：

G42区域中的最低分.

7．前提乞降函数SUMIF

语法：

SUMIF（range,criteria,sum_range）.

参数：

range是用于前提断定的单元格区域,criteria是由数字.逻辑表达式等构成的剖断前提,sum_range为须要乞降的单元格.区域或引用.

运用实例：

合格平均分统计

假如A1:

A36单元格存放某班学生的测验成绩,若要盘算合格学生的平均分,可以运用公式“=SUMIF（A1:

A36,″>=60″,A1:

A36）/COUNTIF（A1:

A36,″>=60″）.公式中的“=SUMIF（A1:

A36,″>=60″,A1:

A36）”盘算合格学生的总分,式中的“A1:

A36”为供给逻辑断定根据的单元格引用,“>=60”为断定前提,不相符前提的数据不介入乞降,A1:

A36则是逻辑断定和乞降的对象.公式中的COUNTIF（A1:

A36,″>=60″）用来统计合格学生的人数.

8．贷款清偿盘算函数PMT

语法：

PMT（rate,nper,pv,fv,type）.

参数：

现在贷款买房子或车子的人越来越多,盘算某一贷款的月清偿金额是斟酌贷款的主要根据,ExcelXP供给的PMT函数是完成这一义务的好对象.语法中的rate是贷款利率;nper为贷款清偿刻日;pv是贷款本金;fv为最后一次付款后残剩的贷款金额,假如省略fv,则认为它的值为零;type为0或1,用来指定付款时光是在月初照样月末.假如省略type,则假设其值为零.

运用实例：

购房还款金额

假如你为购房贷款十万元,假如年利率为7%,每月末还款.采取十年还清方法时,月还款额盘算公式为“=PMT（7%/12,120,-100000）”.其成果为￥-1,161.08,就是你每月须清偿贷款1161.08元.

9．样本的尺度误差函数STDEV

语法：

STDEV（number1,number2,．．．）.

参数：

number1,number2,．．．为对应于总体样本的1到30个参数,它们可所以数值.引用或数组.

留意：

STDEV函数的参数是总体中的样本,并疏忽参数中的逻辑值（true或false）和文本.假如须要用全体数据盘算尺度误差,则应运用STDEVP函数.假如参数中的逻辑值和文本不克不及疏忽,请运用STDEVA函数.

运用实例一：

成绩离散度估量

假设某班共有36逻辑学生介入测验,随机抽取的五个分数为A1=78.A2=45.A3=90.A4=12和A5=85.假如要估算本次测验成绩相对平均分的离散程度,即学生的测验成绩偏离平均分的若干,可以运用公式“=STDEV（A1:

A5）”.其盘算成果为33.00757489,尺度误差的数值越大成绩越疏散.

运用实例二：

质量摇动估量

质量掌握等场合也能用到STDEV函数,如从一批钢丝绳中随机抽出若干进行实验,分离测出它们的抗拉强度.根据STDEV函数的盘算成果即可断定钢丝绳的抗拉强度是否疏散,假如盘算的尺度误差比较小,解释抗拉强度的一致性好,质量比较稳固.反之解释钢丝绳的质量摇动较大,抗拉强度不敷一致.

10．排序函数RANK

语法：

RANK（number,ref,order）.

参数：

number是须要盘算其排位的一个数字;ref是包含一组数字的数组或引用（个中的非数值型参数将被疏忽）;order是用来解释排序方法的数字（假如order为零或省略,则以降序方法给出成果,反之按升序方法）.

运用实例：

产值排序

盘算各车间产值排名的办法是：

在F2单元格内输入公式“=RANK（E2,$E$2:

$E$4）”,敲回车即可盘算出锻造车间的产值排名是2.再将F2中的公式复制到剪贴板,选中F3.F4单元格按Ctrl+V,就能盘算出其余两个车间的产值排名3和1.假如B1单元格中输入的公式为“=RANK（E2,$E$2:

$E$4,1）”,则盘算出的序数按升序方法分列,即2.1和3.

须要留意的是：

雷同数值用RANK函数盘算得到的序数（名次）雷同,但会导致后续数字的序数空白.假如上例中F2单元格存放的数值与F3雷同,则按本法盘算出的排名分离是3.3和1（降序时）.即176.7消失两次时,锻造和维修车间的产值排名均为3,后续金工车间的排名就是1（没有2）.

11．四舍五入函数

语法：

ROUND（number,num_digits）.

参数：

number是须要四舍五入的数字;num_digits为指定的位数,number将按此位数进行四舍五入.

留意：

假如num_digits大于0,则四舍五入到指定的小数位;假如num_digits等于0,则四舍五入到最接近的整数;假如num_digits小于0,则在小数点左侧按指定位数四舍五入.

运用实例：

清除盘算误差

假设Excel工作表中有D2=356.68.E2=128.12,须要将D2与E2之和乘以0.1,将盘算成果四舍五入取整数,再将这个成果乘以1.36（取两位小数）得到最终成果.

一般用户的做法是选中某个单元格（如F2）,运用“单元格”敕令将它的小数位数设为零,然后在个中输入公式“”.再将G2单元格的小数位数设成两位,最后把F2*1.36的成果存入个中就可以了.从概况上看,上述办法没有什么问题.因为（D1+E1）*0.1在F2单元格显示48（留意：

是显示48）,假如F2单元格的小数位数为零,（D1+E1）*0.1经四舍五入后的成果就是48.接下去却出了问题,因为F2*1.36的盘算成果是65.90,基本不是48*1.36的准确成果65.28,个中65.90是（D2+E2）*0.1未经四舍五入直接乘以1.36的成果.

以上盘算成果解释：

“单元格格局”.“数字”选项卡设置的“小数位数”,只能将单元格数值的显示成果进行四舍五入,其实不克不及对所存放的数值进行四舍五入.换句话说,单元格数值的显示成果与现实存放成果其实不完全一致,假如不留意这个问题,盘算工资等迟钝数据就会消失错误.例如在上例中,F2单元格内的数值固然显示为48,但现实存放的倒是48.45,天然得出了48.45*1.36=65.90的成果（按盘算请求应为65.28）.

要解决这个问题其实不难,你只须在G2单元格内输入公式“”,就可以按请求盘算出准确成果65.28.式中的ROUND函数按指定位数对“”进行四舍五入,函数中的参数0将“”四舍五入到最接近的整数.

12．前提计数函数COUNTIF

语法：

COUNTIF（range,criteria）.

参数：

range为须要统计的相符前提的单元格区域;criteria为介入盘算的单元格前提,其情势可认为数字.表达式或文本（如36.″>160″和″男″等）.前提中的数字可以直接写入,表达式和文本必须加引号.

运用实例：

男女职工人数统计

假设A1:

A58区域内存放着员工的性别,则公式“=COUNTIF（A1:

A58,″女″）”统计个中的女职工数量,“=COUNTIF（A1:

A58,″男″）”统计个中的男职工数量.

COUNTIF函数还可以统计优良或合格成绩的数量,假如C1：

G42存放着42逻辑学生的测验成绩,则公式“=COUNTIF（C2：

G2,″>=85″）”可以盘算出个中高于等于85分的成绩数量.如将公式改为“=COUNTIF（C2：

G2,″>=60″）”,则可以盘算出合格分数的个数.

13．百分排位猜测函数PERCENTILE

语法：

PERCENTILE（array,k）.

参数：

array为界说相对地位的数值数组或数值区域,k为数组中须要得到其百分排位的值.

留意：

假如array地点单元格为空白或数据个数超出8191,则返回#NUM！

错误.假如k<0或k>1,则返回#NUM！

错误.假如k不是1/（n-1）的倍数,该函数运用插值法肯定其百分排位.

运用实例：

利润排行猜测

假设C1:

C60区域存放着几十个公司的利润总额.假如你想知道某公司的利润达到若干,才干进入排名前10%的行列,可以运用公式“=PERCENTILE（C1:

C60,0.9）”.假如盘算成果为9867万元,解释利润排名要想进入前10%,则该公司的利润至少应当达到9867万元.

14．数值探测函数ISNUMBER

语法：

ISNUMBER（value）.

参数：

假如value为数值时函数返回ture（真）,不然返回false（假）.

运用实例一：

无错误除法公式假如工作表为C1设计了公式“=A1/B1”,一旦B1单元格没有输入除数,就会在C1中显示错误信息“＃css.shtml'target='_blank'title='div视频教程'>div／0！

”.这不但看起来很不美不雅,一旦作为报表打印还可能引起误解.为防止上面提到的问题消失,可将C1单元格中的公式设计成“=IF（ISNUMBER（B1）,A1/B1,″″）”.式中的ISNUMBER函数对B1单元格进行探测,当B1被填入数值时返回true（真）,反之返回false（假）.为真时IF函数履行A1/B1的运算,为假时在C1单元格中填入空格.

运用实例二：

清除无意义的零

运用SUM函数盘算工资总额时,若引用的单元格区域没稀有据,Excel仍然管帐算出一个成果“0”.如许打印出来的报表不相符财务划定,为此可将公式设计成“=IF（ISNUMBER（A1:

B1）,SUM（A1:

B1）,″″）”.

式中ISNUMBER函数测试SUM函数所引用的单元格区域是否全体为空,当不为空时返回true（真）,反之返回fales（假）.为真时IF函数履行SUM（A1:

B1）,为假时在存放盘算成果的F1单元格中填入空格,如许即可防止在F1单元格中消失“0”.

15．零存整取收益函数PV

语法：

PV（rate,nper,pmt,fv,type）.

参数：

rate为存款利率;nper为总的存款时光,对于三年期零存整取存款来说共有3*12=36个月;pmt为每月存款金额,假如疏忽pmt则公式必须包含参数fv;fv为最后一次存款后愿望得到的现金总额,假如省略了fv则公式中必须包含pmt参数;type为数字0或1,它指定存款时光是月初照样月末.

运用实例：

零存整取收益函数PV

假如你每月初向银行存入现金500元,假如年利2.15%（按月计息,即月息2.15%/12）.假如你想知道5年后的存款总额是若干,可以运用公式“=FV（2.15%/12,60,-500,0,1）”盘算,其成果为￥31,698.67.

式中的2.15%/12为月息;60为总的付款时光,在按月储蓄的情形下为储蓄月份的总和;-500为每月支出的储蓄金额（-暗示支出）;0暗示储蓄开端时账户上的金额,假如账户上没有一分钱,则为0不然应当输入基本金额;1暗示存款时光是月初照样月末,1为月初0或疏忽为月末.

16．内部平均值函数TRIMMEAN

语法：

TRIMMEAN（array,percent）.

参数：

array为须要去失落若干数据然后求平均值的数组或数据区域;percent为盘算时须要除去的数据的比例,假如percent=0.2,解释在20个数据中除去4个,即头部除去2个尾部除去2个.假如percent=0.1,则30个数据点的10%等于3,函数TRIMMEAN将在数据首尾各去失落一个数据.

运用实例：

评选打分统计

歌颂比赛采取打分的办法进行评价,为了防止个他人的极端行动,一般盘算平均分数要去失落若干最高分和最低分.假如B1:

B10区域存放某位歌手的比赛得分,则去失落一个最高分和一个最低分后的平均分盘算公式为“=TRIMMEAN（B1:

B10,0.2）”.公式中的0.2暗示10个数据中去失落2个（10×0.2）,即一个最高分和一个最低分.

17．日期年份函数YEAR

语法：

YEAR（serial_number）.

参数：

serial_number为待盘算年份的日期.

运用实例：

“虚工龄”盘算

所谓“虚工龄”就是从介入工作算起,每过一年就增长一年工龄,运用YEAR函数盘算工龄的公式是“=YEAR（A1）-YEAR（B1）”.公式中的A1和B1分离存下班龄的起止日期,YEAR（A1）和YEAR（B1）分离盘算出两个日期对应的年份,相减后得出虚工龄.

18．起止天数函数DAYS360

语法：

DAYS360（start_date,end_date,method）.

参数：

start_date和end_date是用于盘算时代天数的起止日期,可以运用带引号的文本串（如"1998/01/30"）.系列数和嵌套函数的成果.假如start_date在end_date之后,则DAYS360将返回一个负数.

19．取整函数TRUNC

语法：

TRUNC（number,num_digits）.

参数：

number是须要截去小数部分的数字,num_digits则指定保存到几位小数.

运用实例：

“实工龄”盘算

现实工作满一年算一年的工龄称为“实工龄”,如1998年6月1日至2001年12月31日的工龄为3年.盘算“实工龄”的公式是“=TRUNC（（DAYS360（″1998/6/1″,″2001/12/31″））/360,0）”,公式中的DAYS360（″1998/6/1″,″2001/12/31″）盘算两个日期相差的天数,除以360后算出日期相差的年份（小数）.最后TRUNC函数将（DAYS360（A1,B1）/360的盘算成果截去小数部分,从而得出“实工龄”.假如盘算成果须要保存一位小数,只须将公式修正为“=TRUNC（（DAYS360（″1998/6/1″,″2001/12/31″））/360,1）”即可.

假如你要盘算介入工作到体系当前时光的实工龄,可以将公式修正为“=TRUNC（（DAYS360（″1998/6/1