1、EXCEL2003 公式函数应用大全1、SUMPRODUCT 函数：该函数的 功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图 1，如果想计算 B 3：C6 和 C3：E6 这两组区域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。图 12、ABS 函数：如果在 A1、B1 单元格中分别输入 120、90，那么如果要求 A1 与B1 之间的差的绝对值，可以在 C1 单元格中输入以下公式：“=ABS(A1-B1)”。3、IF 函数：如图 2，如果 C3 单元格的数据大于 D3 单元格，则在 E3 单元格显示“完成任务，超出：”，否则显示“未完成
2、任务，差额：”，可以在 E3 单元格中输入以下公式：“=IF(C3D3, “完成任务，超出：”,”未完成任务，差额：”。图 24、Ceiling 函数：该数值向上舍入基础的倍数。如图 3，在 C3 单元格中输入以下公式： “=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”则是向下舍入。图 35、GCD 函数：该函数计算最大公约数。如图 4，如果要计算 B3：D3 这一区域中 3 个数字的最大公约数，可以在 E3 单元格中输入以下公式：“=GCD(B3,C3,D3)”。图 46、INT 函数：该函数是向下舍入取整函数。如图 5，如果要计算显示器和机箱的购买数量，可以在 E3 单
3、元格中输入以下公式：“=INT(D3/C3)”。图 57、LCM 函数：该函数是计算最小公倍数。如图 6，如果要计算 B3：D3 这一区域中 3 个数字的最小公倍数，可以在 E3 单元格中输入以下公式：“=LCM(B3,C3,D3)”。图 68、LN 函数：该函数是计算自然对数，公式为：“=LN(B3)”。9、LOG 函数：该函数是计算指定底数的对数，公式为：“=LOG10(B3)”。10、MOD 函数：该函数是计算两数相除的余数。如图 7，判断 C3 能否被 B3 整除，可以在 D4 单元格中输入以下公式：“=IF(MOD(B3,C3)=0,“是“,“否“)”。图 711、PI 函数：使用此
4、函数可以返回数字 3.14159265358979，即数学常量 PI，可精确到小数点后 14 位。如图 8，计算球体的面积，可以在 C4 单元格中输入以下公式：“=PI()*(B32)*4)” ；计算球体的体积，可以在 D4 单元格中输入以下公式： “= (B33)*（4* PI()）/3”。图 812、POWER 函数：此函数用来计算乘幂。如图 9，首先在单元中输入底数和指数，然后在 D3 中输入以下公式：“=POWER(B3,C3)”。图 913、PRODUCT 函数：此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。例如：某企业 2005 年度贷款金额为 100000 元，利率为
5、1.5%，贷款期限为 12 个月。如图 10 所示，直接在单元格 E4 中输入以下公式：“ =PRODUCT(B4,C4,D4)”。图 1014、RADIANS 函数：此函数是用来将弧度转换为角度的。可以在 C3 单元格中输入以下公式：“=RADIANS (B3)”。15、RAND 函数：此函数可以返回大于等于 0 及小于 1 的均匀分布随机数，每次计算工作表时都将返回一个新的数值。如果要使用函数 RAND 生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9键，将公式永久性地改为随机数。例如：在全班 50 名同学中以随机方式抽出 2
6、0 名进行调查，如图 11，在单元格中输入开始号码以及结束号码，然后在单元格 B4 中输入以下公式：“=1+RAND()*49”。图 1116、 ROUND 函数：此函数为四舍五入函数。如图 12，例如：将数字“12.3456”按照指定的位数进行四舍五入，可以在 D3 单元格中输入以下公式：“=ROUND(B3,C3)”。17、ROUNDDOWN 函数：此函数为向下舍入函数。例如：出租车的计费标准是：起步价为 5 元，前 10 公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收 2 元。输入不同的公里数，如图 13 所示，然后计算其费用。可以在 C3 单元格中输入以下公式： “=I
7、F(B3TODAY(),“今年没到期“,IF(DATEDIF($D5,TODAY(),“y“)=1,DATEDIF($D5,DATE($C$2,6,9),“y“)*100,50)”，以此可计算出员工的工龄补贴。图 2429、计算火车站寄存包裹费用：在火车站寄存包裹是按小时数收费的。有些按整小时计数，有些按半小时计数，没有超过半小时的以半小时计，半小时以上一小时以内的按一小时计。同时包裹的大小不同收费也不同，在本例中假设大的每小时 6 元，中型的每小时 4 元，小型的每小时 2 元，计算在火车站寄存包裹的费用。如图 25 所示：1）计算寄存天数：首先输入相关的信息，然后在单元格 E4 中输入以下
8、公式：“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4),DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)-1,DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)”，此时可计算出所有型号的包裹寄存的天数，在此公式中用到了 IF 函数，函数中的条件为“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4)
9、,MINUTE(D4),SECOND(D4)”，它是用来判断取走时间是否超过了寄存时间，如果条件为真则表示还没有超过一天，那么寄存的天数就是“DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)-1”，即走取的日期减去寄存的日期再减 1，如果时间超过了，那么寄存的天数就是“DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)”，即取走的日期与寄存时的日期之差；2)计算寄存小时数：在单元格 F4 中输入以下公式：“=IF(TIME(HOUR(C4),
10、MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4),HOUR(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HOUR(D4),MINUTE(D4),SECOND(D4),HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”，此公式中的 IF 函数中的条件与计算天数时的条件是一样的，也是判断取走时间是否超过了寄存时间，如果没有超过小时数则为“TIME(HOUR(C4),MINUTE(C4),SE
11、COND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”，其中“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”表示寄存时间的序列数，其中“TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”表示取走时间的序列数。再通过加减计算得到小时数，如果超过了小时数则为“HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”，即直接用取走时间减去存在时间，取小时数；3)计算寄存分钟数：在单元格 G4 中输入以下公式：“=IF(TI
12、ME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4),MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HOUR(D4),MINUTE(D4),SECOND(D4),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”，此时即可计算出所有型号的包裹寄存的分钟数，其公式形式和计算小时数的公式相似，只是将 HOUR 换成了 MINUTE，其判断条件和前面的一样，
13、如果取走时间没有超过寄存时间，分钟数则为“MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”。如果超过了，分钟数则为“MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”，即直接用取走时间减去寄存时间，取分钟数；4)计算寄存的累计小时数：在单元格 H4 中输入以下公式：“=E4*24+F4+IF(G4=0,0,IF(G4=18,C7=35,C7=50,C7=90,1,IF(F3=
14、80,2,IF(F3=70,3,IF(F3=60,4,5),“优秀“,“良好“,“一般“,“及格“,“不及格“)”，在该公式中用到了多个 IF 函数，用以判断平均成绩属于哪个区间，再使用 CHOOSE 函数返回不同情况下的结果，这里把成绩分为了 5 个档次，即平均分 90 以上的是“优秀”、80 到 90 之间的是“良好”、70 到 80 之间的为“一般”、60 到 70 之间的为“及格”、60 以下的为“不及格”。图 3035、COLUMN 函数：该函数使用方法如图 31 所示。图 3136、COLUMNS 函数：该函数使用方法如图 32 所示。图 3237、HLOOKUP 函数：在实际工作
15、中此函数的应用非常广泛，下面举例说明。在计算销售奖金时，不同的销售业绩对应不同的奖金比例，因此首先需要使用HLOOKUP 函数查询奖金比例，然后再计算销售奖金。1）输入如图 33 所示的业绩奖金以及员工的销售业绩；2）查找适当的奖金比例，在单元格 D7 中输入以下公式：“=HLOOKUP(D3,$B$3 G$4,2)”；3）分别在单元格 D8、D9、D10 中输入以下公式：“=HLOOKUP(E3,$B$3 G$4,2)”、“=HLOOKUP(F3,$B$3 G$4,2)”、“=HLOOKUP(G3,$B$3 G$4,2)”；3）计算奖金：在单元格 E7 中输入以下公式：“=C7*D7”。图
16、3338、HYPERLINK 函数：该函数使用方法如图 34 所示。图 3439、INDEX 函数：该函数返回指定单元格中的内容。假设在图 35 所示的课程表中：1）查找出星期三第 4 节课所上的课程：只需在单元格 C13 中输入以下公式：“=INDEX(C3:H9,C12,C11)”；2）返回星期五的所有课程：选中单元格区域“J2:J9”，然后输入以下公式：“=INDEX(B2:H9,6)”，此时即可显示出星期五的所有课程；3）计算路程：已知各地之间相隔的距离如图 36 所示，那么如何计算 A 地和 D 地之间相隔的距离呢？只需在单元格 C11 中输入以下公式：“=INDEX(B2:G7,M
17、ATCH(C9,B2:B7,0),MATCH(C10,B2:G2,0)”。图 35图 36 40、INDIRECT 函数：该函数使用方法如图 37 所示。图 3760、 PROPER 函数：此函数可以自动转换大小写。首先在工作表中输入一些字母或者英文句子，如图 61 所示，然后在单元格 C3 中输入以下公式：“=PROPER(B3)”。图 6161、 REPLACE 函数：此函数可以使用其他的文本字符串并根据所指定的字符数替换某个文本字符串中的部分。例如某市的电话号码要升位，在原来的电话号码的前面加一个“8”，下面使用 REPLACE 函数完成已知电话号码的升位。具体的操作步骤如下：1）输入已
18、知的电话号码，如图 62 所示；2）计算升位后的电话号码，在单元格 C3 中输入以下公式：“=REPLACE(B3,1,4,“05328“)”，在该公式中，使用 REPLACE 函数用“0108”替换 B3 中字符串中第一位开始的前 4 位数字，结果相当于区号不变，在原电话号码的前面加一个“8”。其中“05328”加引号是以文本的形式输入的，否则忽略 0。图 6262、REPT 函数：此函数可以按照给写的次数重复显示文本，也可以通过 REPT函数不断地重复显示某一个文本字符串来对单元格进行填充。该函数的用法见图 63 所示。图 6363、RIGHT 函数：使用此函数可以根据所指定的字符数返回文
19、本字符串中最后一个或者多个字符。例如：1）拆分姓名，在实际中人的姓名一般是由姓和名两部分组成的，下面介绍如何利用 RIGHT 函数将其拆分开，具体的操作步骤如下：在单元格中输入一些姓名，如图 64 所示，然后在单元格 C3 中输入以下公式：“=RIGHT(B3,2)”；2）判断性别：假设有一个关于生活消费方面的调查，调查者为了书写方便也为了便于进行统计分析，在对被调查者编号时指定其最后一位表示性别，用“1”代表男性，用“2”代表女性，首先在工作表中输入已知信息，如图 65 所示，然后在单元格 D3 中输入以下公式：“=IF(RIGHT(C3,1)=“1“,“男“,“女“)”，在该公式中，使用
20、RIGHT 函数返回编号中的最后一个字符，再利用 IF 函数判断。如果返回的结果为“1”则为“男”，反之为 “女”，由于函数返回的是字符，所以“1”要加引号，当有多种情况时还可以使用嵌套的 IF 函数。图 64 图 6564、SEARCH 函数：此函数可以查找文本字符串。该函数的用法见图 66 所示。图 6665、T 函数：此函数可以返加引用的文本。该函数的用法见图 67 所示。图 6766、TEXT 函数：此函数用来将数值转换为指定格式。该函数的用法见图 68 所示。图 6867、TRIM 函数：此函数用来清除文本中的空格。该函数的用法见图 69 所示。图 6968、UPPER 函数：此函数
21、用来将文本转换为大写。该函数的用法见图 70 所示。图 7069、处理人员信息：文本函数在实际工作中也是一种常用的函数类型。一些大型的企业为了提高员工的素质，使员工能及时地接触到该行业的最新科技信息，有关负责人会时常请一些专家对自己的员工进行培训。下面介绍如何利用文本函数处理人员信息，具体的操作步骤如下：1）在工作表中输入需要的标题项目以及人员编号、姓名和性别等数据信息，以便于在后面使用，如图 71 所示；2）从姓名中提取姓：在单元格 E3 中输入以下公式：“=IF(LEN(C3)=4,LEFT(C3,2),LEFT(C3,1)”，由于中国人的姓名有两个字的，有 3 个字的，还有 4 个字符，
22、4 个字的名字一般是复姓，所以要使用 IF 函数判断姓名的长度是不是 4，如果姓名的长度等于 4，则使用 LEFT 函数返回左边的两个字符，否则返回左边的 1 个字符； 3）从姓名中提取名：在单元格 E3 中输入以下公式：“=IF(LEN(C3)=2,RIGHT(C3,1),RIGHT(C3,2)”，在该公式中使用 IF 函数判断姓名的长度是不是等于 2，若等于 2 则利用 RIGHT 函数返回最右侧的 1 个字符，若不等于 2 则返回最右侧的两个字符；4）添加称呼：在单元格 G3 中输入以下公式：“=IF(D3=“男“,CONCATENATE(E3,“先生“),CONCATENATE(E3,
23、“女士“)”，在该公式中，首先使用 IF 函数判断性别是“男”还是“女”，如果是“男”则返回先生，如果是“女”则返回女士，然后利用 CONCATENATE 函数将判断结果和姓连接起来组成该专家的称呼；5）安排入住的宾馆房间号：在单元格 H3中输入以下公式：“=IF(B3=3,“滨海假日“&TEXT(B3,“300“),“清泉宾馆“&TEXT(B3,“200“)”，在安排专家的宾馆房间时，假设前三名专家在宾馆 A中休息，其余的在宾馆 B 中休息，房间号为他们的编号，在该公式中先使用TEXT 函数将 B 列中的数据转换为对应格式的文本，再使用符号“&”将宾馆和房间号连接起来，最后使用 IF 函数根
24、据专家的编号判断其入住哪个宾馆；6）输入各个专家的培训人数，然后选中单元格 K2，选择插入符号菜单位项弹出符号 对话框，切换到 符号选项卡中，在字体下拉列表中选择（普通文本） 选项，在 子集下拉列表中选择零杂丁贝符（示意符号）选项，设置完毕单击插入按钮即可在单元格输入选定的符号；7）绘制人数比较图：在单元格 G3 中输入以下公式： “=REPT($K$2,INT(I3/12)”，在该公式中，使用 REPT 函数将单元格 K2 中的方块元素复制“INT(I3/12)”次，为了缩小空间也为了减小培训人数比例，将 I 列中的培训人数除以 12 再取整数即可得到需要复制的次数。图 7170、拆分工资金
25、额：在前面已经介绍过利用 INT 函数和 MOD 函数进行工资数额的拆分，下面介绍如何使用文本函数将工资数额按其位数分隔开。例如已知某公司部分员工的工资，现要将工资按位数分开，具体的操作步骤如下：1）在工作表中输入姓名和工资数额以及其他的标题项目，如图 72 所示；2）计算千位上的数字：在单元格 D4 中输入以下公式：“=IF(LEN(C4)=4,LEFT(C4,1),0)”,在该公式中使用 LEN 函数得到 C4 中字符串的长度，再使用 IF 函数判断该字符串的长度是否等于 4，如果是的话则利用 LEFT 函数返回第一个字符，否则返回 0；3）计算百位上的数字:在单元格 E4 中输入以下公式
26、：“=IF(D4=0,IF(LEN(C4)=3,LEFT(C4,1),0),LEFT(C4-D4*1000,1)”,在该公式中，首先使用 IF 函数判断单元格 D4 中的值是否等于 0，如果等于 0 则表明单元格 C4 中的数字共 3 位，将使用 LEFT 函数返回第一个字符；如果不等于 0 则返回“C4-D4*1000”所得结果的第一个字符；4）计算十位上的数字：在单元格 F4 中输入以下公式：“=LEFT(C4-D4*1000-E4*100,1)”计算结果的第一个字符。由于工资最少是“988”，即 3 位数字，所以不必再判断是否有两位数的情况； 5）计算个位上的数字：在单元格 G4 中输入
27、以下公式：“=LEFT(C4-D4*1000-E4*100-F4*10,1)”计算结果的第一个字符。图 7271、CELL 函数：使用此函数可以返回某一个引用区域的左上角单元格的格式、位置或者内容等信息。该函数的用法见图 73 所示。图 7372、COUNTBLANK 函数：此函数可以指定空白单元格的个数。该函数的用法见图74 所示。图 7473、ISBLANK 函数：此函数可以判断单元格是否为空。例如判断员工是否到岗：1）输入姓名和上班时间，如图 75 所示；2）判断其是否到岗，在单元格 E3 中输入以下公式： “=IF(ISBLANK(D3),“请假“,“到岗“)”。图 7574、ISER
28、R 函数：此函数可以判断数值是否为任意错误值。例如：计算应收账款：1）输入已知的数据信息，如货物名称、数量、单价和金额等，如图 76 所示；2）在单元格 E3 中输入以下公式：“=IF(ISERR(C3*D3),“确定价格后再做处理“,C3*D3)”。图 7675、DAVERAGE 函数：此函数可以返回列表或者数据库中满足指定条件的列中数值的平均值。例如：1）在单元格中输入需要处理的问题，如计算“语文大于59 分的平均成绩”和“英语的平均成绩”，如图 77 所示；2）在单元格 C12 中输入以下公式： “=DAVERAGE(B2:E8,C10,C10:C11)”；3）在单元格 C13 中输入以
29、下公式： “=DAVERAGE(B2:E8,4,E2:E8)”。图 7776、DCOUNT 函数：使用此函数可以返回数据库或者列表中满足指定条件并且包含数字的单元格个数。具体的操作步骤如下：1）如图 78 所示，首先在单元格中输入需要处理的问题，然后在单元格 C12 中输入以下公式：“=DCOUNT(B2:E8,B10,B10:B11)”，即可得到数学成绩及格的单元格个数；2）在单元格 C13 中输入以下公式： “=DCOUNT(B2:E8,2,B10:B11)”，即可得到语文成绩大于 70 并且数学成绩及格的单元格个数。图 7877、DGET 函数：使用此函数可以从列表或者数据库的列中提取符
30、合指定条件的单个值。如图 79 所示，在单元格 C12 中输入以下公式：“=DGET(B2:E8,1,D1011)”，即可查找出英语成绩大于 89 分的同学的姓名；在单元格 C13 中输入以下公式：“=DGET(B2:E8,1,B10:C11)”，即可查找出语文和数学成绩全部大于 80 分的同学的姓名。图 7978、DMAX 函数：此函数用以返回指定条件的最大数值。首先在单元格中输入需要处理的问题，如图 80 所示，然后分别在单元格 C12 和 C13 中输入以下公式：“=DMAX(B2:E8,B10,B10 11)”、“=DMAX(B2:E8,D10,B10 11)”。DMIN函数的使用方法
31、与 DMAX 函数相似，不过此函数用以返回指定条件的最小数值。图 8079、DSUM 函数：此函数用以返回指定条件的数字之和。首先在单元格中输入需要处理的问题，如图 81 所示，然后在单元格 C12 和 C13 中输入以下公式：“=DSUM(B1:F6,1,C8:C9)”、“=DSUM(B1:F6,5,C8 9)”。图 8180、处理采购数据：在 EXCEL 中提供有很多种数据库函数，可以满足采购管理中管理人员对大量数据处理的要求。下面以图 82 所示，介绍如何使用数据库函数处理采购数据：1）使用 DAVERAGE 函数计算采购数量的平均值，首先建立一个数据模型； 2）在单元格 E15 中输入
32、以下公式：“=DAVERAGE(B2:F12,5,G15:G16)”，即可得到台灯的平均采购数量；3）将单元格 G16 中的 “台灯 ”改为“瓷瓶”就可以计算出瓷瓶的平均采购数量。此外还可以利用 DAVERAGE 函数在相交或者相并两种条件下计算数据的平均值。在Excel 中输入同行的条件为相交的条件，即必须全部满足的条件，然后在单元格 E19 中输入以下公式：“=DAVERAGE(B2:F12,5,C21 22)”，即可计算出采购数量少于 20 的平均数； 4）要计算购买台灯的数量大于 10 的次数，此时可以使用 DCOUNT 函数来求解，首先输入需要求解的条件，然后在单元格 F26 中输入
33、以下公式：“=DCOUNT(B2:F12,5,C28 29)”；5）要计算新新家具公司沙发的价格，此时可以使用 DGET 函数来求解，首先输入需要求解的条件，然后在单元格 E33 中输入以下公式： “=DGET(B2:E12,4,C35 36)”；6）要计算家具中沙发的最高价格，此时可以使用 DMAX 函数来求解，首先输入需要求解的条件，然后在单元格 E39 中输入以下公式：“=DMAX(B2:E12,4,C41:C42)”。图 8281、区分函数 COUNT 和 COUNTA：例如：1）制作 1 月出勤加班统计表，表中包括员工 1 月出勤加班统计表以及需要统计的内容，如图 83 所示；2）使
34、用COUNT 函数统计各列单元格的个数，在单元格 B13 中输入以下公式：“=COUNT(B3:B11)”，此时可以看到包含文字的单元格和空白单元格被忽略了，只统计包含数字的单元格；3）使用 COUNTA 函数统计各列单元格的个数，在单元格 B14 中输入以下公式： “=COUNTA(B3:B11)”，此时可以看到包含文字的单元格也统计在内了。图 8382、LARGE 函数：该函数用来返回数据集中第 K 个最大值。例如：根据某企业在一次订货后检验所订产品的合格情况，计算需求量的大小、次品的多少以及不合格率最高、最低的产品等。具体的操作步骤如下：1）制作检验订购的产品合格情况表，如图 84 所示
35、；2）计算各个产品中订购总量的最大需求与最小需求，分别在单元格 G2 和 G3 中输入以下公式：“=LARGE(B3:B12,1)”、“=SMALL(B3:B12,1)”；3）计算次品的最大值和最小值，分别在单元格 G6 和G6 中输入以下公式：“=LARGE(C3:C12,1)”、“=LARGE(C3:C12,10)”；4）查找出不合格率最高的产品，在单元格 G10 中输入以下公式：“=INDEX(A3:A12,MATCH(LARGE(D3 12,1),D3 12,0),1)”,在该公式中首先利用 LARGE 函数得出不合格率的最大值，然后利用 MATCH 函数得到该最大值在数据区域 “D3
36、：D12”中的行号，最后使用 INDEX 函数在数据区域“A3：A12” 中查找该行与第 1 列交叉处的单元格的值，即可得到不合格率最高的产品即 “显示器”；查找不合格率最低的产品同理。图 8483、区分函数 MAX 和 MAXA：现以某公司 1 月份的出勤和加班记录表为例，来看一下 MAX 和 MAXA 函数在实际工作中的具体的应用。在此表中分别计算迟到、旷工和加班次数最多的员工的姓名，具体的操作步骤如下：1）制作如图 85 所示的表格；2）计算迟到次数最多的员工的姓名，在单元格 J5 中输入以下公式：“=INDEX(C3:C11,MATCH(MAX(E3:E11),E3:E11,0),1)
37、”,即可得到迟到次数最多的员工的姓名即“李宁”；3）计算旷工次数最多的员工的姓名，在单元格J7 中输入以下公式：“=INDEX(C3:C11,MATCH(MAXA(F3:F11),F3:F11,0),1)”，即可得到旷工次数最多的员工的姓名即“邵刚”。图 8584、MODE 函数：该函数用以返回出现频率最高的数值。例如：假设已知某些同学的语文、数学和英语成绩如图 86 所示，现计算各科成绩中出现次数最多的分数，在单元格 C14 中输入以下公式：“=MODE(C4:C13)”。图 8685、PERCENTRANK 函数：该函数用以返回百分比排位。该函数的用法见图 87 所示。图 8786、RAN
38、K 函数：该函数用以排名次。该函数的用法见图 89 所示。87、TRIMMEAN 函数：该函数用以计算内部平均值。例如：根据实际计算需求在工作表中添加相应的项目，制作如图 88 所示的表格，在单元格 C15 中输入以下公式：“=TRIMMEAN(C3:C14,0.05)”，即可得到除去极值比例为 5%的均值结果。图 8888、统计员工培训成绩：一般来说公司招聘新员工后会对其进行培训，随后人事部会对他们的培训成绩进行统计排名，以此考核新员工的素质状况，具体的操作步骤如下：1）首先在工作表 Sheet1 中输入相应的数据信息，然后对输入的内容进行字体、边框等属性的设置，如图 89 所示；2）计算各
39、位员工的平均成绩：在单元格 J4 中输入以下公式：“=AVERAGE(E4:I4)”；3）计算总成绩：在单元格 K4 中输入以下公式： “=SUM(E4:I4)”；4）计算员工的成绩名次：在单元格 L4 中输入以下公式： “=RANK(J4,$J$4 J$21)”；5）计算培训人数：在单元格 D23 中输入以下公式： “=COUNT(B4:B21)”；6）计算实际参加考试的人数：在单元格 D24 中输入以下公式：“=COUNTA(E4:E21)”；7）计算无成绩的人数：即缺考人数，在单元格 D25 中输入以下公式：“=COUNTBLANK(E4:E21)”；8）计算参加培训的男、女员工人数：分
40、别在单元格 D26 和 D27 中输入以下公式：“=COUNTIF(D4 21,“女“)”、“=COUNTIF(D4 21,“男“)”；9）计算男、女员工总成绩：分别在单元格D29 和 D30 中输入以下公式：“=SUMIF(D4 21,“男“,K4:K21)”、“=SUMIF(D4 21,“女“,K4:K21)”；10）计算平均最高和最低分：分别在单元格 D31 和 D32 中输入以下公式： “=MAXA(J4:J21)”、“=MIN(J4:J21)”；11）计算倒数第 2 名：在单元格 D33 输入以下公式：“=SMALL(J4:J21,2)”；12）计算各个分数段的人数：选中单元格区域“
41、G29：G33”，然后输入以下公式：“=FREQUENCY(J4:J21,F24:G27)”，在该公式中使用 FREQUENCY 函数得到各个分数段的人数；13）计算各个分数段的人数占总人数的比例：在单元格H29 输入以下公式：“=G29/$D$24”。图 8989、CUMPRINC 函数：此函数用来计算贷款本金和利息。例如一位消费者获得一项 30 年的住房贷款，金额为 400000 元，按月还款，年息是 5%，请计算贷款的第五年应该支付的本金和利息。具体的操作步骤如下：1）制作如图 90 所示的表格；2）计算按月还款时第五年内每月月末还款的本金的累计和，在单元格B6 中输入以下公式“=CUM
42、PRINC(D3/12,C3*12,B3,49,60,0)”，在该公式中“D3/12”表示月利率， “C3*12”表示总的付款期数，“49”表示第五年的第一个月， “60”表示第五年的最后一个月，“0”表示付款时间类型是月末；2）计算第五年内每月月末所付利累的累计和，在单元格 C6 中输入以下公式：“=CUMIPMT(D3/12,C3*12,B3,49,60,0)”，注意这个公式与上面唯一不同的地方是所用的函数是不同的；3）计算本利之和，在单元格 D6 中输入以下公式：“=SUM(B6:C6)”； 4）计算按月付款时第五年内共付多少，在单元格 E6 中输入以下公式：“=PMT(D3/12,C3
43、*12,B3,0,0)*12”，在该公式中，“PMT(D3/12,C3*12,B3,0,0)”表示使用 PMT 函数计算每月月末还款数，其中“D3/12”表示月利率， “C3*12”表示总的付款期数，所得结果再乘以 12 表示第五年内总的还款数。图 9090、DOLLARDE 函数：此函数用来将分数形式转换为小数形式。该函数的用法如图 91 所示。图 9191、DOLLARFR 函数：此函数用来将小数形式转换为分数形式。该函数的用法如图 92 所示。图 9292、FV 函数：此函数用来计算投资未来值。1）计算本利和：例如某公司为某项投资存款，银行已有的存款是 50000 元，以后每年存款 20
44、000 元，年利率是8%，试计算 10 年后的本息和为多少？如果每月存入 2000 元，那么 10 年后的本利和又是多少？具体的操作步骤如下：制作如图 93 所示的表格，计算按存款时 10 年后的本金和利息之和，在单元格 D5 中输入以下公式：“=FV(C3,D3,E3,B3,0)”；计算按月存款时 10 年后的本金和利息之和，在单元格 D6 中输入以下公式： “=FV(C3/12,D3*12,F3, B3,0)”，在该公式中“C3/12”表示月利率， “D3*12”表示总的月份数，每月的存款和先期的存款都是负值，这样计算出来的结果就是正值，“0”表示每次月末存款；2）计算累计金额：例如如果年
45、息为 6%，那么 5 年之后 2000 元的累计金额是多少？具体的操作步骤如下：将已知数据和相关项目输入到工作表中建立数据模型，如图 94 所示，在单元格 B6 中输入以下公式：“=FV(B3,B4,0,B5,0)”；3）计算到期还款额：例如用户向银行借款 5000 元，期限 6 年，利息 6%，请问到期还款额为多少？具体的操作步骤如下：将已知数据和相关项目输入到工作表中建立数据模型，如图 95 所示，在单元格 B6 中输入以下公式：“=FV(B3,B4,0,B5,0)”；4）计算帐户总额：如何期初余额为 5000 元，每月的月末存入 600 元，如果月息为 0.75%，请问三年后此帐户中的总
46、额是多少钱？具体的操作步骤如下：将已知数据和相关项目输入到工作表中建立数据模型，如图 96 所示，在单元格 B7 中输入以下公式：“=FV(B3,B4,B5,B6,0)”，即可求出三年后的账户总额。图 93 图 94/td 图 95 图 9693、FVSCHEDULE 函数：此函数用来计算本金未来值。例如某人存入银行150000 元，一年内年利率在不断地变化，请计算一年后的存款额。具体的操作步骤如下：1）制作如图 97 所示的表格，其中包括一年内不同的年利率以及由此得出的月利率；2）计算在各种利率条件下一年后的总存款数，在单元格 D15中输入以下公式：“=FVSCHEDULE(C2,F3:F1
47、4)”，即计算不同的利率条件下150000 元的存款 1 年后的存款额是多少。图 9794、IPMT 函数：此函数用来返回利息偿还额。例如计算贷款利息，假设有一位消费者为买房而向银行贷款 200000 元，贷款期限为 10 年，年息为 4.5%，按月偿还，请计算付款中的利息，具体的操作步骤如下：1）制作如图 98 所示的表格，其中包括贷款金额、贷款时间和年利息；2）计算第一个月应付的利息：在单元格 D5 中输入以下公式： “=IPMT(D3/12,1,C3*12,B3)”；3）计算最后一个月应付的利息：在单元格 D6 中输入以下公式：“=IPMT(D3/12,120,C3*12,B3)”。图
48、9895、NPER 函数：该函数用来返回投资总期数。该函数的用法如图 99 所示。图 9996、PMT 函数：该函数用来返回每期付款额。1）计算偿还额：某公司从银行贷款 200000 元，分 6 年偿还，年利率为 8%，现需计算按年偿还和按月偿还的还款额，条件为等额偿还，具体的操作步骤如下：制作如图 100 所示的表格，计算按年还款时的年初偿还额，在单元格 E4 中输入以下公式：“=PMT(C7,C5,C3,0,1)”；计算年末偿还额，在单元格 F4 中输入以下公式：“=PMT(C7,C5,C3)”；计算按月偿还时的月初偿还额，在单元格 E8 中输入以下公式：“=PMT(C7/12,C5*12
49、,C3,0,1)”，在该公式中“C7/12”表示月利率，“C5*12”表示还款总时间，因为是按月计算，所以是 6*12=72 个月，“1”仍表示是月初还款；计算按月还款时的月末偿还额，在单元格 F8 中输入以下公式：“=PMT(C7/12,C5*12,C3)”；2）计算存款金额：例如如果某公司需要为某个项目准备资金，该项目在两年后预计需要 100000 元，假设银行的存款年利率为 10%，那么从现在起公司每月或者每年应当存入的金额是多少？具体的操作步骤如下：制作如图 101 所示的表格，计算按年存款时的年初存款额，在单元格 E4 中输入以下公式： “=PMT(C7,C5,0,C3,1)”；计算按年存款时的年末存款额，在单元格 F4 中输入以下公式：“=PMT(C7,C5,0,C3)”；计算按月存款时的月初存款额，在单元格 E8 中输入以下公式：“=PMT(C7/12,C5*12,0,C3,1)”，在该公式中使用 PMT 函数计算按月存款时的月初存款额，公式中各项参数的意义依次为：“C7/12”为月利率，“C5*12”为存款总期数，“0” 为本金，“100000”为未来值，“1”表示存款方式是期初；计算按月末存款额，在
