Excel表格函数应用大全.doc

Excel表格函数应用大全EXCEL公式函数应用大全、SUMPRODUCT函数:该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如:如图如果想计算B:C和C:E这两组区域的值可以用以下公式:“=Sumproduct(B:C,D:E)”。图、ABS函数:如果在A、B单元格中分别输入、那么如果要求A与B之间的差的绝对值可以在C单元格中输入以下公式:“=ABS(AB)”。、IF函数:如图如果C单元格的数据大于D单元格则在E单元格显示“完成任务超出:”否则显示“未完成任务差额:”可以在E单元格中输入以下公式:“=IF(CD,“完成任务超出:”,”未完成任务差额:””。图、Ceiling函数:该数值向上舍入基础的倍数。如图在C单元格中输入以下公式:“=CEILING(B,C)”而“=FLOOR(B,C)”则是向下舍入。图、GCD函数:该函数计算最大公约数。如图如果要计算B:D这一区域中个数字的最大公约数可以在E单元格中输入以下公式:“=GCD(B,C,D)”。图、INT函数:该函数是向下舍入取整函数。如图如果要计算显示器和机箱的购买数量可以在E单元格中输入以下公式:“=INT(DC)”。图、LCM函数:该函数是计算最小公倍数。如图如果要计算B:D这一区域中个数字的最小公倍数可以在E单元格中输入以下公式:“=LCM(B,C,D)”。图、LN函数:该函数是计算自然对数公式为:“=LN(B)”。、LOG函数:该函数是计算指定底数的对数公式为:“=LOG(B)”。、MOD函数:该函数是计算两数相除的余数。如图判断C能否被B整除可以在D单元格中输入以下公式:“=IF(MOD(B,C)=,是,否)”。图、PI函数:使用此函数可以返回数字即数学常量PI可精确到小数点后位。如图计算球体的面积可以在C单元格中输入以下公式:“=PI()*(B)*)”计算球体的体积可以在D单元格中输入以下公式:“=(B)*(*PI()))”。图、POWER函数:此函数用来计算乘幂。如图首先在单元中输入底数和指数然后在D中输入以下公式:“=POWER(B,C)”。图、PRODUCT函数:此函数可以对所有的以参数形式给出的数字相乘并返回乘积。例如:某企业年度贷款金额为元利率为贷款期限为个月。如图所示直接在单元格E中输入以下公式:“=PRODUCT(B,C,D)”。图、RADIANS函数:此函数是用来将弧度转换为角度的。可以在C单元格中输入以下公式:”=RADIANS(B)”。、RAND函数:此函数可以返回大于等于及小于的均匀分布随机数每次计算工作表时都将返回一个新的数值。如果要使用函数RAND生成一个随机数并且使之不随单元格的计算而改变可以在编辑栏中输入“=RAND()”保持编辑状态然后按F键将公式永久性地改为随机数。例如:在全班名同学中以随机方式抽出名进行调查如图在单元格中输入开始号码以及结束号码然后在单元格B中输入以下公式:“=RAND()*”。图、ROUND函数:此函数为四舍五入函数。如图例如:将数字“”按照指定的位数进行四舍五入可以在D单元格中输入以下公式:“=ROUND(B,C)”。、ROUNDDOWN函数:此函数为向下舍入函数。例如:出租车的计费标准是:起步价为元前公里每一公里跳表一次以后每半公里就跳表一次每跳一次表要加收元。输入不同的公里数如图所示然后计算其费用。可以在C单元格中输入以下公式:“=IF(B=,ROUNDDOWN(B,)*,ROUNDDOWN((B)*,)*)”。图、ROUNDUP函数:此函数为向上舍入函数。例如:现在网吧的管理一般是采用向上舍入法不满一个单元按照一个单位计算。现假设每分钟计价元请计算如图中所示的上网所花费的费用。)计算上网天数:首先在单元格C中输入以下公式:“=BA”)计算上网分钟数:上网分钟数实际上就等于上网天数乘以再乘以所以应在单元格D中输入以下公式:“=C**”)计算计费时间:本例中规定每分钟计费一次不满分钟以分钟计价所以应在单元格E中输入以下公式:“=ROUNDUP(D,)”)计算上网费用:在单元格G中输入以下公式:“=E*F”。图、SUBTOTAL函数:使用该函数可以返回列表或者数据库中的分类汇总。通常利用数据分类例如某班部分同学的考试成绩如图)显示最低的语文成绩:首先在单元格B中输入“显示最低的语文成绩”的字样然后在单元格E中输入以下公式:“=SUBTOTAL(,C:C)”)显示最高的数学成绩:首先在单元格B中输入“显示最高的数学成绩”的字样然后在单元格E中输入以下公式:“=SUBTOTAL(,D:D)”。图、计算库存量和奖金:假设某公司在月底要根据员工的业绩发放工资并进行产品的库存统计本例中规定员工的基本工资为元奖金按照销售业绩的提成总工资等于基本工资与奖金之和。如图)在工作表中输入相应的数据信息)计算“现存库量”:在单元格C中输入以下公式:“=CSUM(C:C)”)计算“销售业绩”:在单元格G中输入以下公式:“=SUMPRODUCT(C:F,$C$:$F$)”,函数SUMPRODUCT是计算数组C:F与数组$C$:$F$乘积的和用数学公式表示出来就是:“=****”)计算奖金:奖金是按照销售业绩的提成得到的这样计算出来的结果可能会是小数不好找零钱所以这里采用向上舍入的方式得到整数在单元格H中输入以下公式:“=ROUNDUP(G*,)”)计算总工资:由于总工资=基本工资奖金所以在单元格J中输入以下公式:“=SUM(H:I)”。图、计算工资和票面金额:假设某公司的销售人员的销售情况如图所示按照销售业绩的计算销售提成下面需要结合上例中的函数来计算销售人员的销售业绩以及奖金工资然后再计算出发放工资时需要准备的票面数量。)计算销售业绩:在单元格H中输入以下公式:“=SUMPRODUCT(C:G,$C$:$G$)”)计算提成:在本例中假设提成后出现小于元的金额则舍入为所以需要使用ROUNDUP函数在单元格I中输入以下公式:“=ROUNDUP(H*,)”)计算工资:在单元格K中输入以下公式:“=IJ”)计算元的面值:在单元格L中输入以下公式:“=INT(K$L$)”)计算元的面值:在单元格M中输入以下公式:“=INT(MOD(K,$L$)$M$)”此公式是使用MOD函数计算发放“MOD(K,$L$)”张元后剩下的工资然后利用取整函数INT得到元票面的数量)计算元的面值:在单元格N中输入以下公式:“=INT(MOD(K,$M$)$N$)”)计算元的面值:在单元格O中输入以下公式:“=INT(MOD(K,$N$)$O$)”)计算元的面值:在单元格P中输入以下公式:“=INT(MOD(K,$O$)$P$)”。图、DATE函数:在实际工作中经常会用到此函数来显示日期。例如:如图在单元格中输入相应的年、月和图书馆日等信息然后在单元格E中输入以下公式:“=DATE(B,C,D)”。图、DATEIF函数:假设有两个已知日期开始日期和截止日期那么可以利用DATEIF函数来计算它们之间相差的年数、月数或者天数等。如图在单元格D中输入以下公式:“=DATEDIF(B,C,y)”。图、DAYS函数:该函数计算两个日期之间的天数在财务中经常会用到如果财务系统是基于一年个月并且每月天可以使用该函数帮助计算借款天数或者支付款项等。例如:某企业不同时间的贷款如图所示然后利用DAYS函数来计算其借款的时间并且计算出还款利息。)计算“借款天数”:在单元格D中输入以下公式:“=DAYS(B,C)”)计算“还款利息”:在单元格G中输入以下公式:“=D*E*F”。图、WEEKDAY函数:使用此函数可以返回某个日期为星期几。语法:WEEKDAY(serialnumber,returntype):其中参数serialnumber代表要查找的那一天的日期参数returntype为确定返回值类型的数字详细内容如下表:例如:计算当前日期是星期几:如图所示在单元格B中输入计算当前日期的公式:“=WEEKDAY(B,)图、WEEKNUM函数:使用此函数可以计算一年中的第几周。例如:已知年月日是星期五下面用WEEKNUM函数计算在参数不同的情况下返回的周数。如图所示在单元格B中输入计算当前日期的式:“=WEEKNUM(B,C)”。图、WORKDAY函数:使用此函数可以返回某个日期(起始日期)之前或之后相隔指定工作日的某一日期的期值工作日不包括周末和专门指定的日期。假设某出版社要求某个编辑从年月日起开始写稿利天将其完成(其中不包括三天节假日)此时可以利用WORKDAY函数计算出完成日期。如图所示单元格中输入上述信息然后在单元格C中输入以下公式:“=WORKDAY(C,C,C:C)”。图、计算年假天数和工龄补贴:假设某公司规定员工任职满年的开始有年假第至年每年天第年开始每年天。截止到年月日以工龄计算每年补贴元任职不足一年的按每人元计算如图所示:)首先在工作表中输入已知数据信息然后根据公司规定的内容在单元格F中输入以下公式“=IF(DATEDIF($D,TODAY(),y),入职不够一年,IF(DATE(C$,MONTH($D),DAY($D))TODAY(),年没到期,IF(DATEDIF($D,TODAY(),y),,)))”以此可以计算出员工的休假天数)在单元格G中入以下公式:“=IF(DATEDIF($D,DATE($C$,,),y)=,DATEDIF($D,DATE($C$,,),y)*,)”此可计算出员工的工龄补贴。图、计算火车站寄存包裹费用:在火车站寄存包裹是按小时数收费的。有些按整小时计数有些按半小时计没有超过半小时的以半小时计半小时以上一小时以内的按一小时计。同时包裹的大小不同收费也不同在例中假设大的每小时元中型的每小时元小型的每小时元计算在火车站寄存包裹的费用。如图示:)计算寄存天数:首先输入相关的信息然后在单元格E中输入以下公式:“=IF(TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D)),DATE(YAR(D),MONTH(D),DAY(D))DATE(YEAR(C),MONTH(C),DAY(C)),DATE(YEAR(D),MONTH(D),DY(D))DATE(YEAR(C),MONTH(C),DAY(C)))”此时可计算出所有型号的包裹寄存的天数在此公式中到了IF函数函数中的条件为“TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D)”它是用来断取走时间是否超过了寄存时间如果条件为真则表示还没有超过一天那么寄存的天数就是“DATE(YEAR(D),MONTH(D),DAY(D))DATE(YEAR(C),MONTH(C),DAY(C))”即走取的日期减寄存的日期再减如果时间超过了那么寄存的天数就是“DATE(YEAR(D),MONTH(D),DAY(D))DATE(YEAR(C),MONTH(C),DAY(C))”即取走的日期与寄时的日期之差)计算寄存小时数:在单元格F中输入以下公式:“=IF(TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D)),HOUR(ME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D))),HOUR(TIME(HOR(D),MINUTE(D),SECOND(D))TIME(HOUR(C),MINUTE(C),SECOND(C))))”此公式中的IF函数中条件与计算天数时的条件是一样的也是判断取走时间是否超过了寄存时间如果没有超过小时数则为“TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D))”其中“TIME(HOUR(C),MINUTE(C),SECOND(C)”表示寄存时间的序列数其中“TIME(HOUR(D),MINUTE(D),SECOND(D)”表示取走时间的序列数。再通过加减计算得到小时数如果过了小时数则为“HOUR(TIME(HOUR(D),MINUTE(D),SECOND(D))TIME(HOUR(C),MINUTE(C),SECOND(C)))”即接用取走时间减去存在时间取小时数)计算寄存分钟数:在单元格G中输入以下公式:“=IF(TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D)),MINUTETIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D))),MINUTE(TIMHOUR(D),MINUTE(D),SECOND(D))TIME(HOUR(C),MINUTE(C),SECOND(C))))”此时即可计算出有型号的包裹寄存的分钟数其公式形式和计算小时数的公式相似只是将HOUR换成了MINUTE其判断件和前面的一样如果取走时间没有超过寄存时间分钟数则为“MINUTE(TIME(HOUR(C),MINUTE(C),SECOND(C))TIME(HOUR(D),MINUTE(D),SECOND(D)))”如果超过了分钟数则为“MINUTE(TIME(HOUR(D),MINUTE(D),SECOND(D))TIME(HOUR(C),MINUTE(C),SECOND(C)))”直接用取走时间减去寄存时间取分钟数)计算寄存的累计小时数:在单元格H中输入以下公式:“=E*FIF(G=,,IF(G=,,))”在该公式中“E*”表示将天数转换为小时数在将分钟换为小时数时使用IF函数来判断分钟数的范围若分钟数小于等于则返回小时否则返回小时后将所有的小时数相加即可得到累计小时数)计算寄存总费用:在单元格J中输入以下公式:“=I*H此时即可计算出寄存包裹的费用。图、AND函数:当所有参数的逻辑值为真时AND函数的返回值为TRUE只要有一个参数的逻辑值为假该函数的返回值则为FALSE。例如:假设有一组民意调查数据或者调查结果如图所示下面根据各年龄段(,、,、,和以上)对数据进行分类以判断出各个年龄段的调查结果。)计年龄在,岁之间的人的调查结果在单元格E中输入以下公式:“=IF(AND(C=,C=),D,)”在该公式中使用AND函数判断单元格C中的值是否在,岁间然后根据返回的逻辑值再利用IF函数得到结果即如果为真则返回单元格D中的值否则返回空)统计年龄在,岁之间的人的调查结果在单元格F中输入以下公式:“=IF(AND(C=,C=),D,)”)统计年龄在岁以上的人的调查结果在单元格H中输入下公式:“=IF(AND(C=,C=),D,)”。图、OR函数:判断逻辑值并集的计算结果在所有的参数中只要有一个逻辑值为TRUE该函数的返回值为TRUE。例如已知某企业的员工姓名和出生年份两列值如图所示然后根据输入的年份判断员工是否有这一年出生的人并且统计出共有几个。)在单元格D中输入判断值“”即判断是否有年出生的人然后在单元格E中输入以下公式:“{=OR(D=C:C}”在该公式中表示将D单元格的值与数据区域“C:C”中的每一个值作比较判断是否相等。如果任何一人比较结果为真函数OR返回TRUE也就是D单元格中的值位于这个列表中。由于是在一个数组中查找是否存在某个指定的值所以公式要以数组的形式输入输入公式后要按CtrlShiftEnter组合键完成)计算年出的人数在单元格E中输入以下公式:“{=SUM(IF(D=C:C,,)}”在该公式中先使用IF函数将元格D中的值与数据区域“C:C”中的每一个值进行比较如果两个值相等则返回否则返回。然利用SUM函数对所有的返回值求和最后得到的数据就是“”出现的次数即有几个人是年出的。该公式要以数组公式的形式输入。图、ADDRESS函数:该函数使用方法如图所示。图、AREAS函数:该函数使用方法如图所示。图、CHOOSE函数:例如评定学生成绩利用该函数可以评定销售人员的业务能力还可以返回成绩的档以及是否及格等其计算方法都是一样的。下面以学生成绩表为例看一下CHOOSE函数的应用方法。)首在工作表中输入如图所示的学生成绩然后在单元格F中输入以下公式:“=SUM(C:E)”此时可计算出学生的平均成绩)利用CHOOSE函数计算成绩名次在G单元格中输入以下公式:“=CHOOSE(IF(F=,,IF(F=,,IF(F=,,IF(F=,,)))),优秀,良好,一般,及格,不及格)”在该公式中用到了多个IF函数用以判断平均成绩属于哪个区间再使用CHOOSE函数回不同情况下的结果这里把成绩分为了个档次即平均分以上的是“优秀”、到之间的是“好”、到之间的为“一般”、到之间的为“及格”、以下的为“不及格”。图、COLUMN函数:该函数使用方法如图所示。图、COLUMNS函数:该函数使用方法如图所示。图、HLOOKUP函数:在实际工作中此函数的应用非常广泛下面举例说明。在计算销售奖金时不同的售业绩对应不同的奖金比例因此首先需要使用HLOOKUP函数查询奖金比例然后再计算销售奖金。)入如图所示的业绩奖金以及员工的销售业绩)查找适当的奖金比例在单元格D中输入以下公式“=HLOOKUP(D,$B$:$G$,)”)分别在单元格D、D、D中输入以下公式:“=HLOOKUP(E,$B$:$G$,)”、“=HLOOKUP(F,$B$:$G$,)”、“=HLOOKUP(G,$B$:$G$,)”计算奖金:在单元格E中输入以下公式:“=C*D”。图、HYPERLINK函数:该函数使用方法如图所示。图、INDEX函数:该函数返回指定单元格中的内容。假设在图所示的课程表中:)查找出星期三第节课所上的课程:只需在单元格C中输入以下公式:“=INDEX(C:H,C,C)”)返回星期五的有课程:选中单元格区域“J:J”然后输入以下公式:“{=INDEX(B:H,,)}”此时即可显示出星五的所有课程)计算路程:已知各地之间相隔的距离如图所示那么如何计算A地和D地之间相的距离呢,只需在单元格C中输入以下公式:“=INDEX(B:G,MATCH(C,B:B,),MATCH(C,B:G,))”。图图、INDIRECT函数:该函数使用方法如图所示。图、LOOKUP函数:该函数用于在行(或列)中查找并返回数值。例如某公司员工的工资表如图所示查找姓名:首先在单元格C中输入编辑“”然后在单元格C中输入以下公式:“=LOOKUP(C,B:B,C:C)”也可输入公式:“=LOOKUP(C,B:C)”此时即可查找到编辑为“”的员工的姓名。查找基本工资、实发工资的公式类似姓名的公式。图、MATCH函数:在数组中查找数值的相应位置。该函数使用方法如图所示。图、OFFSET函数:OFFSET函数的功能是返回的引用可以为一个单元格或者单元格区域并且可以指定返的行数或者列数。其语法为:OFFSET(reference,rows,cols,height,width)。其中reference表示作为偏移量参照系引用区域此参数必须为单元格或相邻单元格区域的引用否则函数OFFSET返回错误值“#VALUE～”ro表示相对于偏移量参照系的左上角单元格上(下)偏移的行数cols表示相对于偏移量参照系的左上角单元格(右)偏移的列数height表示高度即所要返回的引用区域的行数此参数必须为正数width表示宽度所要返回的引用区域的列数此参数必须为正数。该函数的应用方法如图所示。图、ROW函数:该函数的应用方法如图所示。图、ROWS函数:该函数的应用方法如图所示。图、VLOOKUP函数:VLOOKUP函数的功能是在表格或数值数组的首行查找指定的数值并由此返回表格数组当前行中指定列处的数值。其语法为:VLOOKUP(lookupvalue,tablearray,colindexnum,rangelookup其中lookupvalue为需要在数组第一列中查找的数值colindexnum为tablearray中待返回的匹配值的序列rangelookup为一个逻辑值用以指明函数VLOOKUP返回时是精确匹配还是近似匹配。该函数的应用方法图所示。图、计算所得税:假设规定:工资、薪金所得按月征收对每月收入超过元以上的部分征税适用至的级超额累进税率即:纳税所得额(计税工资)=每月工资(薪金)所得元(不计税部分)超额进应纳税款=纳税所得额×按全额累进所用税率速算扣除数。当工资为“”和“”元的时候计算应缴纳的所得税的金额具体操作步骤如下:)如图所示在单元格C和C中输入工资金额“和“”然后在单元格D中输入“=IF($C=$F$,,($C$F$)*VLOOKUP(($C$F$),$D$:$F$,,)VLOOKUP(($C$F$),$D$:$F,,))”此时即可计算出缴纳的所得税)在单元格E中输入以下公式“=$C$D”此时即可计算实发工资。图、计算考核成绩:在公司或者企业内部为了激励员工更加积极地工作经常会制定一些考核制度下面以计某公司员工第一季度的考核成绩为例介绍一下部分查找函数的实际应用方法具体的操作步骤如下:)新一个工作薄将其中的工作表Sheet、Sheet和Sheet分别命名为“各季度缺勤记录”、“部长意见”和“一季度考核表”然后在前两个工作表中输入所需要的数据信息如图、图所示)在工作表“第一度考核表”中输入员工编号、员工姓名以及相关的标题项目如图所示)计算“缺勤记录”:在单元D中输入以下公式:“=INDEX(各季度缺勤记录!D:$G$,,)”)计算“出勤成绩”:在单元格E中输以下公式:“=IF(D,D,)”即如果缺勤天以上出勤成绩就是分)计算“工作能力”:在单格F中输入以下公式:“=INDEX(部长意见!D:E,,)”)计算“工作态度”:在单元格G中输入以下式:“=VLOOKUP(B,部长意见!$B$:$E$,)”)计算“季度考核成绩”:在单元格H中输入以下公式“=SUM(E:G)”即出勤成绩、工作能力及工作态度之和。图图图、ASC函数:此函数用来将全角转换为半角。该函数的用法见图所示。图、CONCATENATE函数:此函数用来合并字符串。该函数的用法见图所示。图、DOLLAR函数:此函数用来将数字转换为货币形式。该函数的用法见图所示。图、RMB函数:此函数用来将数字转换为货币形式。该函数的用法见图所示。图、EXACT函数:此函数用来判断字符串是否相同。该函数的用法见图所示。图、FIND函数:此函数用来查找文本串。该函数的用法见图所示。图、FIXED函数:此函数对数字进行格式化。该函数的用法见图所示。图、LEFT函数:返回第一个或前几个字符。例如:在实际工作中要取得电话号码的区号或者取得人名的姓等都可以利用LEFT函数来完成。)获取区号:假设已知一些电话号码如图所示下面利用LEFT函数取这些电话号码的区域。在单元格C中输入以下公式:“=LEFT(B,)”)输入称呼:首先在工作表中输已知的姓名和性别如图所示然后在单元格E中输入以下公式:“=LEFT(C,)IF(D=男,先生,士)”该公式表示在姓名中取出左边的第一个字用连接上先生或者女士称呼。、LEN函数:此函数用来查找文本的长度。该函数的用法见图所示。图、LOW函数:此函数用来将文本转换为小写。该函数的用法见图所示。图、MID函数:此函数可以返回文本字符串中从指定位置开始的特定字符。该数目由用户指定。例如:)如图所示:从身份证号码中提取生日:在网上注册一些表格时经常需要填写身份证号码填写完毕系统就会自动地生成出生日期这里以某公司员工为例根据其身份证号码提取出生年月日。首先在工作表中输入员工的姓名和身份证号码等数据信息如图所示然后在单元格D中输入以下公式:“=MID(C,,)”在该公式中利用MID函数返回身份证号码中从第位字符开始的共个字符即该员工的出生日期众所周知身份证前位代表的是省份、市、县编号然后从第位开始是出生年月日共位后面的数字代表其他的意义)拆分电话号码:工作表中输入已知的电话号码如图所示、PROPER函数:此函数可以自动转换大小写。首先在工作表中输入一些字母或者英文句子如图所示然后在单元格C中输入以下公式:“=PROPER(B)”。图、REPLACE函数:此函数可以使用其他的文本字符串并根据所指定的字符数替换某个文本字符串中的部分。例如某市的电话号码要升位在原来的电话号码的前面加一个“”下面使用REPLACE函数完成已知电话号码的升位。具体的操作步骤如下:)输入已知的电话号码如图所示)计算升位后的电话号码在单元格C中输入以下公式:“=REPLACE(B,,,)”在该公式中使用REPLACE函数用“”替换B中字符串中第一位开始的前位数字结果相当于区号不变在原电话号码的前面加一个“”。其中“”加引号是以文本的形式输入的否则忽略。图、REPT函数:此函数可以按照给写的次数重复显示文本也可以通过REPT函数不断地重复显示某一个文本字符串来对单元格进行填充。该函数的用法见图所示。图、RIGHT函数:使用此函数可以根据所指定的字符数返回文本字符串中最后一个或者多个字符。例如:)拆分姓名在实际中人的姓名一般是由姓和名两部分组成的下面介绍如何利用RIGHT函数将其拆分开具体的操作步骤如下:在单元格中输入一些姓名如图所示然后在单元格C中输入以下公式:“=RIGHT(B,)”)判断性别:假设有一个关于生活消费方面的调查调查者为了书写方便也为了便于进行统计分析在对被调查者编号时指定其最后一位表示性别用“”代表男性用“”代表女性首先在工作表中输入已知信息如图所示然后在单元格D中输入以下公式:“=IF(RIGHT(C,)=,男,女)”在该公式中使用RIGHT函数返回编号中的最后一个字符再利用IF函数判断。如果返回的结果为“”则为“男”反之为“女”由于函数返回的是字符所以“”要加引号当有多种情况、SEARCH函数:此函数可以查找文本字符串。该函数的用法见图所示。图、T函数:此函数可以返加引用的文本。该函数的用法见图所示。图、TEXT函数:此函数用来将数值转换为指定格式。该函数的用法见图所示。图、TRIM函数:此函数用来清除文本中的空格。该函数的用法见图所示。图、UPPER函数:此函数用来将文本转换为大写。该函数的用法见图所示。图、处理人员信息:文本函数在实际工作中也是一种常用的函数类型。一些大型的企业为了提高员工的素质使员工能及时地接触到该行业的最新科技信息有关负责人会时常请一些专家对自己的员工进行培训。下面介绍如何利用文本函数处理人员信息具体的操作步骤如下:)在工作表中输入需要的标题项目以及人员编号、姓名和性别等数据信息以便于在后面使用如图所示)从姓名中提取姓:在单元格E中输入以下公式:“=IF(LEN(C)=,LEFT(C,),LEFT(C,))”由于中国人的姓名有两个字的有个字的还有个字符个字的名字一般是复姓所以要使用IF函数判断姓名的长度是不是如果姓名的长度等于则使用LEFT函数返回左边的两个字符否则返回左边的个字符)从姓名中提取名:在单元格E中输入以下公式:“=IF(LEN(C)=,RIGHT(C,),RIGHT(C,))”在该公式中使用IF函数判断姓名的长度是不是等于若等于则利用RIGHT函数返回最右侧的个字符若不等于则返回最右侧的两个字符)添加称呼:在单元格G中输入以下公式:“=IF(D=男,CONCATENATE(E,先生),CONCATENATE(E,女士))”在该公式中首先使用IF函数判断性别是“男”还是“女”如果是“男”则返回先生如果是“女”则返回女士然后利用CONCATENATE函数将判断结果和姓连接起来组成该专家的称呼)安排入住的宾馆房间号:在单元格H中输入以下公式:“=IF(B=,滨海假日TEXT(B,),清泉宾馆TEXT(B,))”在安排专家的宾馆房间时假设前三名专家在宾馆A中休息其余的在宾馆B中休息房间号为他们的编号在该公式中先使用TEXT函数将B列中的数据转换为对应格式的文本再使用符号“”将宾馆和房间号连接起来最后使用IF函数根据专家的编号判断其入住哪个宾馆)输入各个专家的培训人数然后选中单元格K选择插入符号菜单位项弹出符号对话框切换到符号选项卡中在字体下拉列表中选择(普通文本)选项在子集下拉列表中选择零杂丁贝符(示意符号)选项设置完毕单击插入按钮即可在单元格输入选定的符号)绘制人数比较图:在单元格G中输入以下公式:“=REPT($K$,INT(I))”在该公式中使用REPT函数将单元格K中的方块元素复制“INT(I)”次为了缩小空间也为了减小培训人数比例将I列中的培训人数除以再取整数即可得到需要复制的次数。图、拆分工资金额:在前面已经介绍过利用INT函数和MOD函数进行工资数额的拆分下面介绍如何使用文本函数将工资数额按其位数分隔开。例如已知某公司部分员工的工资现要将工资按位数分开具体的操作步骤如下:)在工作表中输入姓名和工资数额以及其他的标题项目如图所示)计算千位上的数字:在单元格D中输入以下公式:“=IF(LEN(C)=,LEFT(C,),)”,在该公式中使用LEN函数得到C中字符串的长度再使用IF函数判断该字符串的长度是否等于如果是的话则利用LEFT函数返回第一个字符否则返回)计算百位上的数字:在单元格E中输入以下公式:“=IF(D=,IF(LEN(C)=,LEFT(C,),),LEFT(CD*,))”,在该公式中首先使用IF函数判断单元格D中的值是否等于如果等于则表明单元格C中的数字共位将使用LEFT函数返回第一个字符如果不等于则返回“CD*”所得结果的第一个字符)计算十位上的数字:在单元格F中输入以下公式:“=LEFT(CD*E*,)”计算结果的第一个字符。由于工资最少是“”即位数字所以不必再判断是否有两位数的情况)计算个位上的数字:在单元格G中输入以下公式:“=LEFT(CD*E*F*,)”计算结果的第一个字符。图、CELL函数:使用此函数可以返回某一个引用区域的左上角单元格的格式、位置或者内容等信息。该函数的用法见图所示。图、COUNTBLANK函数:此函数可以指定空白单元格的个数。该函数的用法见图所示。图、ISBLANK函数:此函数可以判断单元格是否为空。例如判断员工是否到岗:)输入姓名和上班时间如图所示)判断其是否到岗在单元格E中输入以下公式:“=IF(ISBLANK(D),请假,到岗)”。图、ISERR函数:此函数可以判断数值是否为任意错误值。例如:计算应收账款:)输入已知的数据信息如货物名称、数量、单价和金额等如图所示)在单元格E中输入以下公式:“=IF(ISERR(C*D),确定价格后再做处理,C*D)”。图、DAVERAGE函数:此函数可以返回列表或者数据库中满足指定条件的列中数值的平均值。例如:)在单元格中输入需要处理的问题如计算“语文大于分的平均成绩”和“英语的平均成绩”如图所示)在单元格C中输入以下公式:“=DAVERAGE(B:E,C,C:C)”)在单元格C中输入以下公式:“=DAVERAGE(B:E,,E:E)”。图、DCOUNT函数:使用此函数可以返回数据库或者列表中满足指定条件并且包含数字的单元格个数。具体的操作步骤如下:)如图所示首先在单元格中输入需要处理的问题然后在单元格C中输入以下公式:“=DCOUNT(B:E,B,B:B)”即可得到数学成绩及格的单元格个数)在单元格C中输入以下公式:“=DCOUNT(B:E,,B:B)”即可得到语文成绩大于并且数学成绩及格的单元格个数。图、DGET函数:使用此函数可以从列表或者数据库的列中提取符合指定条件的单个值。如图所示在单元格C中输入以下公式:“=DGET(B:E,,D:D)”即可查找出英语成绩大于分的同学的姓名在单元格C中输入以下公式:“=DGET(B:E,,B:C)”即可查找出语文和数学成绩全部大于分的同学的姓名。图、DMAX函数:此函数用以返回指定条件的最大数值。首先在单元格中输入需要处理的问题如图所示然后分别在单元格C和C中输入以下公式:“=DMAX(B:E,B,B:D)”、“=DMAX(B:E,D,B:D)”。DMIN函数的使用方法与DMAX函数相似不过此函数用以返回指定条件的最小数值。图、DSUM函数:此函数用以返回指定条件的数字之和。首先在单元格中输入需要处理的问题如图所示然后在单元格C和C中输入以下公式:“=DSUM(B:F,,C:C)”、“=DSUM(B:F,,C:D)”。图、处理采购数据:在EXCEL中提供有很多种数据库函数可以满足采购管理中管理人员对大量数据处理的要求。下面以图所示介绍如何使用数据库函数处理采购数据:)使用DAVERAGE函数计算采购数量的平均值首先建立一个数据模型)在单元格E中输入以下公式:“=DAVERAGE(B:F,,G:G)”即可得到台灯的平均采购数量)将单元格G中的“台灯”改为“瓷瓶”就可以计算出瓷瓶的平均采购数量。此外还可以利用DAVERAGE函数在相交或者相并两种条件下计算数据的平均值。在Excel中输入同行的条件为相交的条件即必须全部满足的条件然后在单元格E中输入以下公式:“=DAVERAGE(B:F,,C:D)”即可计算出采购数量少于的平均数)要计算购买台灯的数量大于的次数此时可以使用DCOUNT函数来求解首先输入需要求解的条件然后在单元格F中输入以下公式:“=DCOUNT(B:F,,C:D)”)要计算新新家具公司沙发的价格此时可以使用DGET函数来求解首先输入需要求解的条件然后在单元格E中输入以下公式:“=DGET(B:E,,C:D)”)要计算家具中沙发的最高价格此时可以使用DMAX函数来求解首先输入需要求解的条件然后在单元格E中输入以下公式:“=DMAX(B:E,,C:C)”。图、区分函数COUNT和COUNTA:例如:)制作月出勤加班统计表表中包括员工月出勤加班统计表以及需要统计的内容如图所示)使用COUNT函数统计各列单元格的个数在单元格B中输入以下公式:“=COUNT(B:B)”此时可以看到包含文字的单元格和空白单元格被忽略了只统计包含数字的单元格)使用COUNTA函数统计各列单元格的个数在单元格B中输入以下公式:“=COUNTA(B:B)”此时可以看到包含文字的单元格也统计在内了。图、LARGE函数:该函数用来返回数据集中第K个最大值。例如:根据某企业在一次订货后检验所订产品的合格情况计算需求量的大小、次品的多少以及不合格率最高、最低的产品等。具体的操作步骤如下:)制作检验订购的产品合格情况表如图所示)计算各个产品中订购总量的最大需求与最小需求分别在单元格G和G中输入以下公式:“=LARGE(B:B,)”、“=SMALL(B:B,)”)计算次品的最大值和最小值分别在单元格G和G中输入以下公式:“=LARGE(C:C,)”、“=LARGE(C:C,)”)查找出不合格率最高的产品在单元格G中输入以下公式:“=INDEX(A:A,MATCH(LARGE(D:D,),D:D,),)”,在该公式中首先利用LARGE函数得出不合格率的最大值然后利用MATCH函数得到该最大值在数据区域“D:D”中的行号最后使用INDEX函数在数据区域“A:A”中查找该行与第列交叉处的单元格的值即可得到不合格率最高的产品即“显示器”查找不合格率最低的产品同理。图、区分函数MAX和MAXA:现以某公司月份的出勤和加班记录表为例来看一下MAX和MAXA函数在实际工作中的具体的应用。在此表中分别计算迟到、旷工和加班次数最多的员工的姓名具体的操作步骤如下:)制作如图所示的表格)计算迟到次数最多的员工的姓名在单元格J中输入以下公式:“=INDEX(C:C,MATCH(MAX(E:E),E:E,),)”,即可得到迟到次数最多的员工的姓名即“李宁”)计算旷工次数最多的员工的姓名在单元格J中输入以下公式:“=INDEX(C:C,MATCH(MAXA(F:F),F:F,),)”即可得到旷工次数最多的员工的姓名即“邵刚”。图、MODE函数:该函数用以返回出现频率最高的数值。例如:假设已知某些同学的语文、数学和英语成绩如图所示现计算各科成绩中出现次数最多的分数在单元格C中输入以下公式:“=MODE(C:C)”。图、PERCENTRANK函数:该函数用以返回百分比排位。该函数的用法见图所示。图、RANK函数:该函数用以排名次。该函数的用法见图所示。、TRIMMEAN函数:该函数用以计算内部平均值。例如:根据实际计算需求在工作表中添加相应的项目制作如图所示的表格在单元格C中输入以下公式:“=TRIMMEAN(C:C,)”即可得到除去极值比例为的均值结果。图、统计员工培训成绩:一般来说公司招聘新员工后会对其进行培训随后人事部会对他们的培训成绩进行统计排名以此考核新员工的素质状况具体的操作步骤如下:)首先在工作表Sheet中输入相应的数据信息然后对输入的内容进行字体、边框等属性的设置如图所示)计算各位员工的平均成绩:在单元格J中输入以下公式:“=AVERAGE(E:I)”)计算总成绩:在单元格K中输入以下公式:“=SUM(E:I)”)计算员工的成绩名次:在单元格L中输入以下公式:“=RANK(J,$J$:$J$)”)计算培训人数:在单元格D中输入以下公式:“=COUNT(B:B)”)计算实际参加考试的人数:在单元格D中输入以下公式:“=COUNTA(E:E)”)计算无成绩的人数:即缺考人数在单元格D中输入以下公式:“=COUNTBLANK(E:E)”)计算参加培训的男、女员工人数:分别在单元格D和D中输入以下公式:“=COUNTIF(D:D,女)”、“=COUNTIF(D:D,男)”)计算男、女员工总成绩:分别在单元格D和D中输入以下公式:“=SUMIF(D:D,男,K:K)”、“=SUMIF(D:D,女,K:K)”)计算平均最高和最低分:分别在单元格D和D中输入以下公式:“=MAXA(J:J)”、“=MIN(J:J)”)计算倒数第名:在单元格D输入以下公式:“=SMALL(J:J,)”)计算各个分数段的人数:选中单元格区域“G:G”然后输入以下公式:“{=FREQUENCY(J:J,F:G)}”在该公式中使用FREQUENCY函数得到各个分数段的人数)计算各个分数段的人数占总人数的比例:在单元格H输入以下公式:“=G$D$”。图、CUMPRINC函数:此函数用来计算贷款本金和利息。例如一位消费者获得一项年的住房贷款金额为元按月还款年息是请计算贷款的第五年应该支付的本金和利息。具体的操作步骤如下:)制作如图所示的表格)计算按月还款时第五年内每月月末还款的本金的累计和在单元格B中输入以下公式“=CUMPRINC(D,C*,B,,,)”在该公式中“D”表示月利率“C*”表示总的付款期数“”表示第五年的第一个月“”表示第五年的最后一个月“”表示付款时间类型是月末)计算第五年内每月月末所付利累的累计和在单元格C中输入以下公式:“=CUMIPMT(D,C*,B,,,)”注意这个公式与上面唯一不同的地方是所用的函数是不同的)计算本利之和在单元格D中输入以下公式:“=SUM(B:C)”)计算按月付款时第五年内共付多少在单元格E中输入以下公式:“=PMT(D,C*,B,,)*”在该公式中“PMT(D,C*,B,,)”表示使用PMT函数计算每月月末还款数其中“D”表示月利率“C*”表示总的付款期数所得结果再乘以表示第五年内总的还款数。图、DOLLARDE函数:此函数用来将分数形式转换为小数形式。该函数的用法如图所示。图、DOLLARFR函数:此函数用来将小数形式转换为分数形式。该函数的用法如图所示。图、FV函数:此函数用来计算投资未来值。)计算本利和:例如某公司为某项投资存款银行已有的存款是元以后每年存款元年利率是试计算年后的本息和为多少,如果每月存入元那么年后的本利和又是多少,具体的操作步骤如下:制作如图所示的表格计算按存款时年后的本金和利息之和在单元格D中输入以下公式:“=FV(C,D,E,B,)”计算按月存款时年后的本金和利息之和在单元格D中输入以下公式:“=FV(C,D*,F,B,)”在该公式中“C”表示月利率“D*”表示总的月份数每月的存款和先期的存款都是负值这样计算出来的结果就是正值“”表示每次月末存款)计算累计金额:例如如果年息为那么年之后元的累计金额是多少,具体的操作步骤如下:将已知数据和相关项目输入到工作表中建立数据模型如图所示在单元格B中输入以下公式:“=FV(B,B,,B,)”)计算到期还款额:例如用户向银行借款元期限年利息请问到期还款额为多少,具体的操作步骤如下:将已知数据和相关项目输入到工作表中建立数据模型如图所示在单元格B中输入以下公式:“=FV(B,B,,B,)”)计算帐户总额:如何期初余额为元每月的月末存入元如果月息为请问三年后此帐户中的总额是多少钱,具体的操作步骤如下:将已知数据和相关项目输入到工作表中建立数据模型如图所示在单元格B中输入以下公式:、FVSCHEDULE函数:此函数用来计算本金未来值。例如某人存入银行元一年内年利率在不断地变化请计算一年后的存款额。具体的操作步骤如下:)制作如图所示的表格其中包括一年内不同的年利率以及由此得出的月利率)计算在各种利率条件下一年后的总存款数在单元格D中输入以下公式:“=FVSCHEDULE(C,F:F)”即计算不同的利率条件下元的存款年后的存款额是多少。图、IPMT函数:此函数用来返回利息偿还额。例如计算贷款利息假设有一位消费者为买房而向银行贷款元贷款期限为年年息为按月偿还请计算付款中的利息具体的操作步骤如下:)制作如图所示的表格其中包括贷款金额、贷款时间和年利息)计算第一个月应付的利息:在单元格D中输入以下公式:“=IPMT(D,,C*,B)”)计算最后一个月应付的利息:在单元格D中输入以下公式:“=IPMT(D,,C*,B)”。图、NPER函数:该函数用来返回投资总期数。该函数的用法如图所示。图、PMT函数:该函数用来返回每期付款额。)计算偿还额:某公司从银行贷款元分年偿还年利率为现需计算按年偿还和按月偿还的还款额条件为等额偿还具体的操作步骤如下:制作如图所示的表格计算按年还款时的年初偿还额在单元格E中输入以下公式:“=PMT(C,C,C,,)”计算年末偿还额在单元格F中输入以下公式:“=PMT(C,C,C)”计算按月偿还时的月初偿还额在单元格E中输入以下公式:“=PMT(C,C*,C,,)”在该公式中“C”表示月利率“C*”表示还款总时间因为是按月计算所以是*=个月“”仍表示是月初还款计算按月还款时的月末偿还额在单元格F中输入以下公式:“=PMT(C,C*,C)”)计算存款金额:例如如果某公司需要为某个项目准备资金该项目在两年后预计需要元假设银行的存款年利率为那么从现在起公司每月或者每年应当存入的金额是多少,具体的操作步骤如下:制作如图所示的表格计算按年存款时的年初存款额在单元格E中输入以下公式:“=PMT(C,C,,C,)”计算按年存款时的年末存款额在单元格F中输入以下公式:“=PMT(C,C,,C)”计算按月存款时的月初存款额在单元格E中输入以下公式:“=PMT(C,C*,,C,)”在该公式中使用PMT函数计算按月存款时的月初存款额公式中各项参数的意义依次为:“C”为月利率“C*”为存款总期数“”为本金“”为未来值“”表示存款方式是期初计算按月末存款额在单元格F中输入以下公式:“=PMT(C,C*,,C)”)计算利润租金:假设一房产的租赁利益当前可以以元出售租期年预付每月的租金元不得重新定价或者涨价如果接受了的收益请问可以得到多少利润租金,具体的操作步骤如下:制作如图所示的表格计算利润租金在单元格B中输入以下公式:“=PMT(B,B*,,,)B”)计算贷款偿还额:假设有一笔期限为年月利息为的元的贷款请计算月偿还额为多少,具体的操作步骤如下:制作如图所示的表格计算月偿还额在单元格B中输入以下公式:“=PMT(B,B*,B,,)”。图图、PPMT函数:该函数用来返回本金偿还额。例如计算偿还的本息某公司从银行贷款元年利率为还款期为年条件是等额偿还按年或者按月还款请计算付款中的本金和利息。具体的操作步骤如下:)制作如图所示的表格)计算按年还款时的年初和年末应付本金:分别在单元格D、D、D和D中输入以下公式:“=PPMT(E,,D,C,,)”、“=PPMT(E,,D,C,)”、“=PPMT(E,,D,C,,)”、“=PPMT(E,,D,C,,)”这个公式分别是用来计算第一年的期初、期末和第十年的期初及期末的应付本金的。公式的形式基本相同只是函数PPMT中的参数per和type发生了改变分别表示哪一年以及是期初还是期末的应付本金)计算按月还款时的年初和年末应付本金:分别在单元格D、D、D和D中输入以下公式:“=PPMT(E,,D*,C,,)”、“=PPMT(E,,D*,C,)”、“=PPMT(E,,D*,C,,)”、“=PPMT(E,,D*,C,)”在这个公式中“E”表示月利率“D*”表示还款总月份数“”表示还款总期数中的最后一个月由于是还款因此未来值都是)计算按年还款时的年初和年末应付利息:分别在单元格E、E、E和E中输入以下公式:“=IPMT(E,,D,C,,)”、“=IPMT(E,,D,C,)”、“=IPMT(E,,D,C,,)”、“=IPMT(E,,D,C,)”与计算本金不同的地方就是所用的函数不一样其参数的意义都是一样的)计算按月还款时的年初和年末应付利息:分别在单元格E、E、E和E中输入以下公式:“=IPMT(E,,D*,C,,)”、“=IPMT(E,,D*,C,)”、“=IPMT(E,,D*,C,,)”、“=IPMT(E,,D*,C,)”)计算各种情况下的本金和利息之和:在单元格F中输入以下公式:“=SUM(D:E)”。从上面的计算结果中可以看出:每年的年初所付款都是一样的虽然第一年和最后一年的本金和利息的值不同但总和是相等的同样每年年末的本金和利息之和也是相等的每月月初以及每月月末的本金和利息之和也是相等的。图、PV函数:该函数用来返回投资现值。例如:)计算贷款额:某公司想贷款进行投资其能承受的能力为每月支付元以年利息进行年贷款请计算该公司能承受的最多贷款额是多少,具体的操作步骤如下:制作如图所示的表格在单元格E中输入以下公式:“=PV(C,D*,B,,)”即按月计算付款数)计算投资值:现有一项保险年金购买该保险后可以在今后年内每月末领回元假设购买时需先缴付元投资回报率为请计算这笔投资是否值得。具体的操作步骤如下:制作如图所示的表格在单元格E中输入以下公式:“=PV(C,D*,B,,)”即可得到需要投资的现值。从上面的计算的结果中可以看出计算出来的投资现值“”大于预交付的购买保险的费用“”所以购买此保险还是值得的。图图、RATE函数:该函数返回年金的各期利率。该函数的用法如图所示。图