excel函数公式大全.doc

1、EXCEL2003EXCEL2003 公式公式函数应用大全函数应用大全 1、SUMPRODUCT 函数：该函数的 功能是在给定的几组数组中将数组间对应的元素相 乘并返回乘积之和。例如：如图 1，如果想计算 B 3：C6 和 C3：E6 这两组区域的值， 可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。 图 1 2、ABS 函数：如果在 A1、B1 单元格中分别输入 120、90，那么如果要求 A1 与 B1 之 间的差的绝对值，可以在 C1 单元格中输入以下公式：“=ABS(A1-B1)”。 3、IF 函数：如图 2，如果 C3 单元格的数据大于 D3 单元格，则在 E3

2、 单元格显示“完 成任务，超出：”，否则显示“未完成任务，差额：”，可以在 E3 单元格中输入以 下公式：“=IF(C3D3, “完成任务，超出：”,”未完成任务，差额：”。 图 2 4、Ceiling 函数：该数值向上舍入基础的倍数。如图 3，在 C3 单元格中输入以下公 式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”则是向下舍入。 图 3 5、GCD 函数：该函数计算最大公约数。如图 4，如果要计算 B3：D3 这一区域中 3 个 数字的最大公约数，可以在 E3 单元格中输入以下公式：“=GCD(B3,C3,D3)”。 图 4 6、INT 函数：该函数是向下舍入取

3、整函数。如图 5，如果要计算显示器和机箱的购 买数量，可以在 E3 单元格中输入以下公式：“=INT(D3/C3)”。 图 5 7、LCM 函数：该函数是计算最小公倍数。如图 6，如果要计算 B3：D3 这一区域中 3 个数字的最小公倍数，可以在 E3 单元格中输入以下公式：“=LCM(B3,C3,D3)”。 图 6 8、LN 函数：该函数是计算自然对数，公式为：“=LN(B3)”。 9、LOG 函数：该函数是计算指定底数的对数，公式为：“=LOG10(B3)”。 10、MOD 函数：该函数是计算两数相除的余数。如图 7，判断 C3 能否被 B3 整除，可 以在 D4 单元格中输入以下公式：“

4、=IF(MOD(B3,C3)=0,“是“,“否“)”。 图 7 11、PI 函数：使用此函数可以返回数字 3.14159265358979，即数学常量 PI，可精确 到小数点后 14 位。如图 8，计算球体的面积，可以在 C4 单元格中输入以下公式： “=PI()*(B32)*4)”；计算球体的体积，可以在 D4 单元格中输入以下公式：“= (B33)*（4* PI()）/3”。 图 8 12、POWER 函数：此函数用来计算乘幂。如图 9，首先在单元中输入底数和指数，然 后在 D3 中输入以下公式：“=POWER(B3,C3)”。 图 9 13、PRODUCT 函数：此函数可以对所有的以参数

5、形式给出的数字相乘，并返回乘积。 例如：某企业 2005 年度贷款金额为 100000 元，利率为 1.5%，贷款期限为 12 个月。 如图 10 所示，直接在单元格 E4 中输入以下公式：“ =PRODUCT(B4,C4,D4)”。 图 10 14、RADIANS 函数：此函数是用来将弧度转换为角度的。可以在 C3 单元格中输入以 下公式：“=RADIANS (B3)”。 15、RAND 函数：此函数可以返回大于等于 0 及小于 1 的均匀分布随机数，每次计算 工作表时都将返回一个新的数值。如果要使用函数 RAND 生成一个随机数，并且使之 不随单元格的计算而改变，可以在编辑栏中输入“=RA

6、ND()”，保持编辑状态，然后 按F9键，将公式永久性地改为随机数。例如：在全班 50 名同学中以随机方式抽出 20 名进行调查，如图 11，在单元格中输入开始号码以及结束号码，然后在单元格 B4 中输入以下公式：“=1+RAND()*49”。 图 11 16、ROUND 函数：此函数为四舍五入函数。如图 12，例如：将数字“12.3456”按照 指定的位数进行四舍五入，可以在 D3 单元格中输入以下公式：“=ROUND(B3,C3)”。 17、ROUNDDOWN 函数：此函数为向下舍入函数。例如：出租车的计费标准是： 起步价为 5 元，前 10 公里每一公里跳表一次，以后每半公里就跳表一次，

7、每跳一次 表要加收 2 元。输入不同的公里数，如图 13 所示，然后计算其费用。可以在 C3 单 元格中输入以下公式： “=IF(B3TIME(HOUR(D4),MINUTE(D 4),SECOND(D4),DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MON TH(C4),DAY(C4)-1,DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),M ONTH(C4),DAY(C4)”，此时可计算出所有型号的包裹寄存的天数，在此公式中用 到了 IF 函数，函数中的条件为 “TIME(HOUR(C4),MINUT

8、E(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SE COND(D4)”，它是用来判断取走时间是否超过了寄存时间， 如果条件为真则表示还 没有超过一天，那么寄存的天数就是 “DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C 4)-1”，即走取的日期减去寄存的日期再减 1，如果时间超过了，那么寄存的天数就 是 “DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C 4)”，即取走的日期与寄存时的日期之差；2)计算寄存小时

9、数：在单元格 F4 中输入 以下公式： “=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D 4),SECOND(D4),HOUR(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HO UR(D4),MINUTE(D4),SECOND(D4),HOUR(TIME(HOUR(D4),MINUTE(D4),SECO ND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”，此公式中的 IF 函数中 的条件与计算天数时的条件是一样的，也是判断取走时间是否超过了寄

10、存时间，如 果没有超过小时数则为 “TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SE COND(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)”，即

11、直接用取走时间减去存在时间，取小时数；3)计算寄存分 钟数：在单元格 G4 中输入以下公式： “=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D 4),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)”，此时即可计算出 所有型号的

12、包裹寄存的分钟数， 其公式形式和计算小时数的公式相似， 只是将 HOUR 换成了 MINUTE，其判断条件和前面的一样，如果取走时间没有超过寄存时间，分 钟数则为 “MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HOUR(D4),MIN UTE(D4),SECOND(D4)”。如果超过了，分钟数则为 “MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINU TE(C4),SECOND(C4)”，即直接用取走时间减去寄存时间，取分钟数；4)计算寄存 的累计小时数：在单元格 H

13、4 中输入以下公式： “=E4*24+F4+IF(G4=0,0,IF(G4=18,C7=35,C7=50,C7=90,1,IF(F3=80,2,IF(F3=70,3,IF(F3=60,4,5),“优秀“,“ 良好“,“一般“,“及格“,“不及格“)”，在该公式中用到了多个 IF 函数，用以判断平 均成绩属于哪个区间，再使用 CHOOSE 函数返回不同情况下的结果，这里把成绩分为 了 5 个档次，即平均分 90 以上的是“优秀”、80 到 90 之间的是“良好”、70 到 80 之间的为“一般”、60 到 70 之间的为“及格”、60 以下的为“不及格”。 图 30 35、COLUMN 函数：该

14、函数使用方法如图 31 所示。 图 31 36、COLUMNS 函数：该函数使用方法如图 32 所示。 图 32 37、HLOOKUP 函数：在实际工作中此函数的应用非常广泛，下面举例说明。在计算销 售奖金时，不同的销售业绩对应不同的奖金比例，因此首先需要使用 HLOOKUP 函数 查询奖金比例，然后再计算销售奖金。1）输入如图 33 所示的业绩奖金以及员工的 销售业绩；2）查找适当的奖金比例，在单元格 D7 中输入以下公式： “=HLOOKUP(D3,$B$3G$4,2)”；3）分别在单元格 D8、D9、D10 中输入以下公式： “=HLOOKUP(E3,$B$3G$4,2)”、“=HLOO

15、KUP(F3,$B$3G$4,2)”、 “=HLOOKUP(G3,$B$3G$4,2)”；3）计算奖金：在单元格 E7 中输入以下公式： “=C7*D7”。 图 33 38、HYPERLINK 函数：该函数使用方法如图 34 所示。 图 34 39、 INDEX 函数： 该函数返回指定单元格中的内容。 假设在图 35 所示的课程表中： 1） 查找出星期三第 4 节课所上的课程：只需在单元格 C13 中输入以下公式： “=INDEX(C3:H9,C12,C11)”；2）返回星期五的所有课程：选中单元格区域 “J2:J9”，然后输入以下公式：“=INDEX(B2:H9,6)”，此时即可显示出星期五

16、 的所有课程；3）计算路程：已知各地之间相隔的距离如图 36 所示，那么如何计算 A 地和 D 地之间相隔的距离呢？只需在单元格 C11 中输入以下公式： “=INDEX(B2:G7,MATCH(C9,B2:B7,0),MATCH(C10,B2:G2,0)”。 图 35 图 36 40、INDIRECT 函数：该函数使用方法如图 37 所示。 图 37 60、PROPER 函数：此函数可以自动转换大小写。首先在工作表中输入一些字母或 者英文句子，如图 61 所示，然后在单元格 C3 中输入以下公式：“=PROPER(B3)”。 图 6161、REPLACE 函数：此函数可以使用其他的文本字符串

17、并根据所指定的字符数 替换某个文本字符串中的部分。例如某市的电话号码要升位，在原来的电话号码的 前面加一个“8”，下面使用 REPLACE 函数完成已知电话号码的升位。具体的操作步 骤如下：1）输入已知的电话号码，如图 62 所示；2）计算升位后的电话号码，在单 元格 C3 中输入以下公式：“=REPLACE(B3,1,4,“05328“)”，在该公式中，使用 REPLACE 函数用“0108”替换 B3 中字符串中第一位开始的前 4 位数字，结果相当于 区号不变，在原电话号码的前面加一个“8”。其中“05328”加引号是以文本的形 式输入的，否则忽略 0。 图 62 62、REPT 函数：此

18、函数可以按照给写的次数重复显示文本，也可以通过 REPT 函数不 断地重复显示某一个文本字符串来对单元格进行填充。该函数的用法见图 63 所示。 图 63 63、 RIGHT 函数： 使用此函数可以根据所指定的字符数返回文本字符串中最后一个或 者多个字符。例如：1）拆分姓名，在实际中人的姓名一般是由姓和名两部分组成的， 下面介绍如何利用 RIGHT 函数将其拆分开，具体的操作步骤如下：在单元格中输入 一些姓名，如图 64 所示，然后在单元格 C3 中输入以下公式：“=RIGHT(B3,2)”；2） 判断性别：假设有一个关于生活消费方面的调查，调查者为了书写方便也为了便于 进行统计分析，在对被调

19、查者编号时指定其最后一位表示性别，用“1”代表男性， 用“2”代表女性，首先在工作表中输入已知信息，如图 65 所示，然后在单元格 D3 中输入以下公式：“=IF(RIGHT(C3,1)=“1“,“男“,“女“)”，在该公式中，使用 RIGHT 函数返回编号中的最后一个字符，再利用 IF 函数判断。如果返回的结果为“1”则 为“男”，反之为“女”，由于函数返回的是字符，所以“1”要加引号，当有多种 情况时还可以使用嵌套的 IF 函数。 图 64 图 65 64、SEARCH 函数：此函数可以查找文本字符串。该函数的用法见图 66 所示。 图 66 65、T 函数：此函数可以返加引用的文本。该函

20、数的用法见图 67 所示。 图 67 66、TEXT 函数：此函数用来将数值转换为指定格式。该函数的用法见图 68 所示。 图 68 67、TRIM 函数：此函数用来清除文本中的空格。该函数的用法见图 69 所示。 图 69 68、UPPER 函数：此函数用来将文本转换为大写。该函数的用法见图 70 所示。 图 70 69、处理人员信息：文本函数在实际工作中也是一种常用的函数类型。一些大型的 企业为了提高员工的素质，使员工能及时地接触到该行业的最新科技信息，有关负 责人会时常请一些专家对自己的员工进行培训。下面介绍如何利用文本函数处理人 员信息，具体的操作步骤如下：1）在工作表中输入需要的标题

21、项目以及人员编号、 姓名和性别等数据信息，以便于在后面使用，如图 71 所示；2）从姓名中提取姓： 在单元格 E3 中输入以下公式：“=IF(LEN(C3)=4,LEFT(C3,2),LEFT(C3,1)”，由于 中国人的姓名有两个字的，有 3 个字的，还有 4 个字符，4 个字的名字一般是复姓， 所以要使用 IF 函数判断姓名的长度是不是 4，如果姓名的长度等于 4，则使用 LEFT 函数返回左边的两个字符，否则返回左边的 1 个字符；3）从姓名中提取名：在单元 格 E3 中输入以下公式：“=IF(LEN(C3)=2,RIGHT(C3,1),RIGHT(C3,2)”，在该公式 中使用 IF

22、函数判断姓名的长度是不是等于 2，若等于 2 则利用 RIGHT 函数返回最右 侧的 1 个字符，若不等于 2 则返回最右侧的两个字符；4）添加称呼：在单元格 G3 中输入以下公式：“=IF(D3=“男“,CONCATENATE(E3,“先生“),CONCATENATE(E3,“女士 “)”， 在该公式中， 首先使用 IF 函数判断性别是“男”还是“女”， 如果是“男” 则返回先生，如果是“女”则返回女士，然后利用 CONCATENATE 函数将判断结果和 姓连接起来组成该专家的称呼；5）安排入住的宾馆房间号：在单元格 H3 中输入以 下公式：“=IF(B3=3,“滨海假日“&TEXT(B3,

23、“300“),“清泉宾馆 “&TEXT(B3,“200“)”， 在安排专家的宾馆房间时， 假设前三名专家在宾馆 A 中休息， 其余的在宾馆 B 中休息，房间号为他们的编号，在该公式中先使用 TEXT 函数将 B 列 中的数据转换为对应格式的文本，再使用符号“&”将宾馆和房间号连接起来，最后 使用 IF 函数根据专家的编号判断其入住哪个宾馆；6）输入各个专家的培训人数， 然后选中单元格 K2， 选择插入符号菜单位项弹出符号对话框， 切换到符号 选项卡中，在字体下拉列表中选择（普通文本）选项，在子集下拉列表中选 择零杂丁贝符（示意符号）选项，设置完毕单击插入按钮即可在单元格输入选 定的符号；7）绘

24、制人数比较图：在单元格 G3 中输入以下公式： “=REPT($K$2,INT(I3/12)”，在该公式中，使用 REPT 函数将单元格 K2 中的方块 元素复制“INT(I3/12)”次，为了缩小空间也为了减小培训人数比例，将 I 列中的 培训人数除以 12 再取整数即可得到需要复制的次数。 图 71 70、拆分工资金额：在前面已经介绍过利用 INT 函数和 MOD 函数进行工资数额的拆 分，下面介绍如何使用文本函数将工资数额按其位数分隔开。例如已知某公司部分 员工的工资，现要将工资按位数分开，具体的操作步骤如下：1）在工作表中输入姓 名和工资数额以及其他的标题项目，如图 72 所示；2）计

25、算千位上的数字：在单元 格 D4 中输入以下公式：“=IF(LEN(C4)=4,LEFT(C4,1),0)”,在该公式中使用 LEN 函 数得到 C4 中字符串的长度， 再使用 IF 函数判断该字符串的长度是否等于 4， 如果是 的话则利用 LEFT 函数返回第一个字符，否则返回 0；3）计算百位上的数字:在单元 格 E4 中输入以下公式： “=IF(D4=0,IF(LEN(C4)=3,LEFT(C4,1),0),LEFT(C4-D4*1000,1)”,在该公式中， 首先使用 IF 函数判断单元格 D4 中的值是否等于 0，如果等于 0 则表明单元格 C4 中 的数字共 3 位，将使用 LEF

26、T 函数返回第一个字符；如果不等于 0 则返回 “C4-D4*1000”所得结果的第一个字符；4）计算十位上的数字：在单元格 F4 中输 入以下公式：“=LEFT(C4-D4*1000-E4*100,1)”计算结果的第一个字符。由于工资 最少是“988”，即 3 位数字，所以不必再判断是否有两位数的情况；5）计算个位 上的数字：在单元格 G4 中输入以下公式：“=LEFT(C4-D4*1000-E4*100-F4*10,1)” 计算结果的第一个字符。 图 72 71、CELL 函数：使用此函数可以返回某一个引用区域的左上角单元格的格式、位置 或者内容等信息。该函数的用法见图 73 所示。 图

27、73 72、COUNTBLANK 函数：此函数可以指定空白单元格的个数。该函数的用法见图 74 所示。 图 74 73、ISBLANK 函数：此函数可以判断单元格是否为空。例如判断员工是否到岗：1） 输入姓名和上班时间，如图 75 所示；2）判断其是否到岗，在单元格 E3 中输入以下 公式：“=IF(ISBLANK(D3),“请假“,“到岗“)”。 图 75 74、ISERR 函数：此函数可以判断数值是否为任意错误值。例如：计算应收账款：1） 输入已知的数据信息，如货物名称、数量、单价和金额等，如图 76 所示；2）在单 元格 E3 中输入以下公式：“=IF(ISERR(C3*D3),“确定价

28、格后再做处理“,C3*D3)”。 图 76 75、DAVERAGE 函数：此函数可以返回列表或者数据库中满足指定条件的列中数值的 平均值。例如：1）在单元格中输入需要处理的问题，如计算“语文大于 59 分的平 均成绩”和“英语的平均成绩”，如图 77 所示；2）在单元格 C12 中输入以下公式： “=DAVERAGE(B2:E8,C10,C10:C11)”；3）在单元格 C13 中输入以下公式： “=DAVERAGE(B2:E8,4,E2:E8)”。 图 77 76、DCOUNT 函数：使用此函数可以返回数据库或者列表中满足指定条件并且包含数 字的单元格个数。具体的操作步骤如下：1）如图 78

29、 所示，首先在单元格中输入需 要处理的问题，然后在单元格 C12 中输入以下公式： “=DCOUNT(B2:E8,B10,B10:B11)”，即可得到数学成绩及格的单元格个数；2）在单 元格 C13 中输入以下公式：“=DCOUNT(B2:E8,2,B10:B11)”，即可得到语文成绩大 于 70 并且数学成绩及格的单元格个数。 图 78 77、DGET 函数：使用此函数可以从列表或者数据库的列中提取符合指定条件的单个 值。 如图 79 所示， 在单元格 C12 中输入以下公式： “=DGET(B2:E8,1,D1011)”， 即可查找出英语成绩大于 89 分的同学的姓名；在单元格 C13 中

30、输入以下公式： “=DGET(B2:E8,1,B10:C11)”，即可查找出语文和数学成绩全部大于 80 分的同学的 姓名。 图 79 78、DMAX 函数：此函数用以返回指定条件的最大数值。首先在单元格中输入需要处 理的问题，如图 80 所示，然后分别在单元格 C12 和 C13 中输入以下公式： “=DMAX(B2:E8,B10,B1011)”、“=DMAX(B2:E8,D10,B1011)”。DMIN 函数 的使用方法与 DMAX 函数相似，不过此函数用以返回指定条件的最小数值。 图 80 79、DSUM 函数：此函数用以返回指定条件的数字之和。首先在单元格中输入需要处 理的问题，如图

31、81 所示，然后在单元格 C12 和 C13 中输入以下公式： “=DSUM(B1:F6,1,C8:C9)”、“=DSUM(B1:F6,5,C89)”。 图 81 80、处理采购数据：在 EXCEL 中提供有很多种数据库函数，可以满足采购管理中管 理人员对大量数据处理的要求。下面以图 82 所示，介绍如何使用数据库函数处理采 购数据：1）使用 DAVERAGE 函数计算采购数量的平均值，首先建立一个数据模型；2） 在单元格 E15 中输入以下公式：“=DAVERAGE(B2:F12,5,G15:G16)”，即可得到台灯 的平均采购数量；3）将单元格 G16 中的“台灯”改为“瓷瓶”就可以计算出

32、瓷瓶的 平均采购数量。此外还可以利用 DAVERAGE 函数在相交或者相并两种条件下计算数据 的平均值。在 Excel 中输入同行的条件为相交的条件，即必须全部满足的条件，然 后在单元格 E19 中输入以下公式：“=DAVERAGE(B2:F12,5,C2122)”，即可计算 出采购数量少于 20 的平均数；4）要计算购买台灯的数量大于 10 的次数，此时可以 使用 DCOUNT 函数来求解，首先输入需要求解的条件，然后在单元格 F26 中输入以下 公式：“=DCOUNT(B2:F12,5,C2829)”；5）要计算新新家具公司沙发的价格， 此时可以使用 DGET 函数来求解，首先输入需要求解

33、的条件，然后在单元格 E33 中输 入以下公式：“=DGET(B2:E12,4,C3536)”；6）要计算家具中沙发的最高价格， 此时可以使用 DMAX 函数来求解，首先输入需要求解的条件，然后在单元格 E39 中输 入以下公式：“=DMAX(B2:E12,4,C41:C42)”。 图 82 81、区分函数 COUNT 和 COUNTA：例如：1）制作 1 月出勤加班统计表，表中包括员工 1 月出勤加班统计表以及需要统计的内容，如图 83 所示；2）使用 COUNT 函数统计各 列单元格的个数，在单元格 B13 中输入以下公式：“=COUNT(B3:B11)”，此时可以 看到包含文字的单元格和

34、空白单元格被忽略了，只统计包含数字的单元格；3）使用 COUNTA 函数统计各列单元格的个数，在单元格 B14 中输入以下公式： “=COUNTA(B3:B11)”，此时可以看到包含文字的单元格也统计在内了。 图 83 82、LARGE 函数：该函数用来返回数据集中第 K 个最大值。例如：根据某企业在一次 订货后检验所订产品的合格情况，计算需求量的大小、次品的多少以及不合格率最 高、最低的产品等。具体的操作步骤如下：1）制作检验订购的产品合格情况表，如 图 84 所示；2）计算各个产品中订购总量的最大需求与最小需求，分别在单元格 G2 和 G3 中输入以下公式：“=LARGE(B3:B12,1

35、)”、“=SMALL(B3:B12,1)”；3）计算 次品的最大值和最小值，分别在单元格 G6 和 G6 中输入以下公式： “=LARGE(C3:C12,1)”、“=LARGE(C3:C12,10)”；4）查找出不合格率最高的产品， 在单元格G10中输入以下公式： “=INDEX(A3:A12,MATCH(LARGE(D312,1),D3 12,0),1)”,在该公式中首先利用 LARGE 函数得出不合格率的最大值，然后利用 MATCH 函数得到该最大值在数据区域“D3：D12”中的行号，最后使用 INDEX 函数在 数据区域“A3：A12”中查找该行与第 1 列交叉处的单元格的值，即可得到不

36、合格率 最高的产品即“显示器”；查找不合格率最低的产品同理。 图 84 83、区分函数 MAX 和 MAXA：现以某公司 1 月份的出勤和加班记录表为例，来看一下 MAX 和 MAXA 函数在实际工作中的具体的应用。在此表中分别计算迟到、旷工和加班 次数最多的员工的姓名，具体的操作步骤如下：1）制作如图 85 所示的表格；2）计 算迟到次数最多的员工的姓名，在单元格 J5 中输入以下公式： “=INDEX(C3:C11,MATCH(MAX(E3:E11),E3:E11,0),1)”,即可得到迟到次数最多的 员工的姓名即“李宁”；3）计算旷工次数最多的员工的姓名，在单元格 J7 中输入 以下公式

37、：“=INDEX(C3:C11,MATCH(MAXA(F3:F11),F3:F11,0),1)”，即可得到旷工 次数最多的员工的姓名即“邵刚”。 图 85 84、MODE 函数：该函数用以返回出现频率最高的数值。例如：假设已知某些同学的 语文、数学和英语成绩如图 86 所示，现计算各科成绩中出现次数最多的分数，在单 元格 C14 中输入以下公式：“=MODE(C4:C13)”。 图 86 85、PERCENTRANK 函数：该函数用以返回百分比排位。该函数的用法见图 87 所示。 图 87 86、RANK 函数：该函数用以排名次。该函数的用法见图 89 所示。 87、TRIMMEAN 函数：该

38、函数用以计算内部平均值。例如：根据实际计算需求在工作 表中添加相应的项目，制作如图 88 所示的表格，在单元格 C15 中输入以下公式： “=TRIMMEAN(C3:C14,0.05)”，即可得到除去极值比例为 5%的均值结果。 图 88 88、统计员工培训成绩：一般来说公司招聘新员工后会对其进行培训，随后人事部 会对他们的培训成绩进行统计排名，以此考核新员工的素质状况，具体的操作步骤 如下：1）首先在工作表 Sheet1 中输入相应的数据信息，然后对输入的内容进行字 体、边框等属性的设置，如图 89 所示；2）计算各位员工的平均成绩：在单元格 J4 中输入以下公式：“=AVERAGE(E4:

39、I4)”；3）计算总成绩：在单元格 K4 中输入以下 公式：“=SUM(E4:I4)”；4）计算员工的成绩名次：在单元格 L4 中输入以下公式： “=RANK(J4,$J$4J$21)”；5）计算培训人数：在单元格 D23 中输入以下公式： “=COUNT(B4:B21)”； 6） 计算实际参加考试的人数： 在单元格 D24 中输入以下公式： “=COUNTA(E4:E21)”；7）计算无成绩的人数：即缺考人数，在单元格 D25 中输入 以下公式：“=COUNTBLANK(E4:E21)”；8）计算参加培训的男、女员工人数：分别 在单元格 D26 和 D27 中输入以下公式：“=COUNTIF

40、(D421,“女“)”、 “=COUNTIF(D421,“男“)”；9）计算男、女员工总成绩：分别在单元格 D29 和 D30 中输入以下公式：“=SUMIF(D421,“男“,K4:K21)”、“=SUMIF(D421,“ 女“,K4:K21)”；10）计算平均最高和最低分：分别在单元格 D31 和 D32 中输入以下 公式：“=MAXA(J4:J21)”、“=MIN(J4:J21)”；11）计算倒数第 2 名：在单元格 D33 输入以下公式：“=SMALL(J4:J21,2)”；12）计算各个分数段的人数：选中单元格 区域“G29：G33”，然后输入以下公式：“=FREQUENCY(J4:

41、J21,F24:G27)”，在 该公式中使用 FREQUENCY 函数得到各个分数段的人数；13）计算各个分数段的人数 占总人数的比例：在单元格 H29 输入以下公式：“=G29/$D$24”。 图 89 89、CUMPRINC 函数：此函数用来计算贷款本金和利息。例如一位消费者获得一项 30 年的住房贷款，金额为 400000 元，按月还款，年息是 5%，请计算贷款的第五年应该 支付的本金和利息。具体的操作步骤如下：1）制作如图 90 所示的表格；2）计算按 月还款时第五年内每月月末还款的本金的累计和，在单元格 B6 中输入以下公式 “=CUMPRINC(D3/12,C3*12,B3,49,

42、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*12,B3,0,0)*12”，在该公

43、式 中，“PMT(D3/12,C3*12,B3,0,0)”表示使用 PMT 函数计算每月月末还款数，其中 “D3/12”表示月利率，“C3*12”表示总的付款期数，所得结果再乘以 12 表示第五 年内总的还款数。 图 90 90、DOLLARDE 函数：此函数用来将分数形式转换为小数形式。该函数的用法如图 91 所示。 图 91 91、DOLLARFR 函数：此函数用来将小数形式转换为分数形式。该函数的用法如图 92 所示。 图 92 92、FV 函数：此函数用来计算投资未来值。1）计算本利和：例如某公司为某项投资 存款，银行已有的存款是 50000 元，以后每年存款 20000 元，年利率是

44、 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）计算累计金额：例如如果年息为 6%

45、，那么 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 图 96 93、FVSCHEDULE 函数：此函数用来计算本金未来值。例如某人存入银行 150000 元， 一年内年利率在不断地变化，请计算一年后的存款额。具体的操作步骤如下：1）制 作如图 97 所示的表格，其中包括一年内不同的年利率以及由此得出的月利率；2） 计算在各种利率条件下一年后的总存款数，在单元格 D15 中输入以下公式： “=FVSCHEDUL

47、E(C2,F3:F14)”，即计算不同的利率条件下 150000 元的存款 1 年后的 存款额是多少。 图 97 94、IPMT 函数：此函数用来返回利息偿还额。例如计算贷款利息，假设有一位消费 者为买房而向银行贷款 200000 元，贷款期限为 10 年，年息为 4.5%，按月偿还，请 计算付款中的利息，具体的操作步骤如下：1）制作如图 98 所示的表格，其中包括 贷款金额、贷款时间和年利息；2）计算第一个月应付的利息：在单元格 D5 中输入 以下公式：“=IPMT(D3/12,1,C3*12,B3)”；3）计算最后一个月应付的利息：在单 元格 D6 中输入以下公式：“=IPMT(D3/12

48、,120,C3*12,B3)”。 图 98 95、NPER 函数：该函数用来返回投资总期数。该函数的用法如图 99 所示。 图 99 96、 PMT 函数： 该函数用来返回每期付款额。 1） 计算偿还额： 某公司从银行贷款 200000 元，分 6 年偿还，年利率为 8%，现需计算按年偿还和按月偿还的还款额，条件为等 额偿还，具体的操作步骤如下：制作如图 100 所示的表格，计算按年还款时的年初 偿还额， 在单元格 E4 中输入以下公式： “=PMT(C7,C5,C3,0,1)”； 计算年末偿还额， 在单元格 F4 中输入以下公式： “=PMT(C7,C5,C3)”； 计算按月偿还时的月初偿还

49、额， 在单元格 E8 中输入以下公式：“=PMT(C7/12,C5*12,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*