1、EXCEL2003 公式 函数应用大全 1、 SUMPRODUCT 函数： 该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图 1，如果想计算 B3： C6 和 D3： 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
3、6、 INT 函数： 该函数是向下舍入取整函 数。如图 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 函数： 该函数是计算两数相除的余数。
4、如图 7，判断 C3 能否被 B3 整除，可以在 D4单元格中输入以下公式： “=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 中输入以下
5、公式： “=POWER(B3,C3)” 。 图 9 13、 PRODUCT 函数： 此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。例如：某企业 2005 年度贷款金额为 100000 元，利率为 1.5%，贷款 期限为 12 个月。如图 10 所示，直接在单元格 E4 中输入以下公式： “ =PRODUCT(B4,C4,D4)” 。 图 10 14、 RADIANS 函数 ： 此函数是用来将弧度转换为角度的。可以在 C3 单元格中输入以下公式：“=RADIANS (B3)” 。 15、 RAND 函数： 此函数可以返回大于等于 0 及小于 1 的均匀分布随机数，每次计算工作表时都将返
6、回一个新的数值。如果要使用函数 RAND 生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入 “=RAND()” ，保持编辑状态，然后按 F9键，将公式永久性地改为随机数。例如：在全班 50 名同学中以随机方式抽出 20 名进行调查，如图 11，在单元格中输入开始号码以及结束号码，然后在单元格 B4 中输入以下公式： “=1 +RAND()*49” 。 图 11 16、 ROUND 函数： 此函数为四舍五入函数。如图 12，例如：将数字 “12.3456” 按照指定的位数进行四舍 五入，可以在 D3 单元格中输入以下公式： “=ROUND(B3,C3)” 。 17、 ROUND
7、DOWN 函数： 此函数为向下舍入函数。例如：出租车的计 费标准是：起步价为 5元，前 10 公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收 2 元。输入不同的公里数，如图 13 所示，然后计算其费用。可以在 C3 单元格中输入以下公式：“ =IF(B3TODAY(),“今年没到期“,IF(DATEDIF($D5,TODAY(),“y“)=1,DATEDIF($D5,DATE($C$2,6,9),“y“)*100,50)” ，以此可计算出员工的工龄补贴。 图 24 29、计算火车站寄存包裹费用： 在火车站寄存包裹是按小时数收费的。有些按整小时计数，有些按半小时计数，没有超过半小
8、时的以半小时计，半小时以上一小时以内的按一小时计。同时包裹的大小不同收费也不同，在本例中假设大的每小时 6 元，中型的每小时 4 元，小型的每小时 2元，计算在火车站寄存包裹的费用。如图 25所示： 1）计算寄存天数： 首先输入相关的信息，然后在单元格 E4中输入以下公式：“ =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(
9、D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)” ，此时可计算出所有型号的包裹寄存的天数，在此公式中用到了 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” ，即走取的日期减去寄存的日期再减 1，如果时间超
10、过了，那么寄存的天数就是“ DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)” ，即取走的日期与寄存时的日 期之差； 2)计算寄存小时数： 在单元格 F4中输入以下公式：“ =IF(TIME(HOUR(C4),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),M
11、INUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)” ，此公式中的 IF函数中的条件与计算天数时的条件是一样的，也是判断取走时间是否超过了寄存时间，如果没有超过小时数则为“ TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4)” ，其中“ TIME(HOUR(C4),MINUTE(C4),SECOND(C4)” 表示寄存时间的序列数，其中“ TIME(HOUR(D4),MINUTE(D4),SECOND(D4)” 表示取走时间的序列数。再通过加减
12、计算得到 小时数，如果超过了小时数则为 “ HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)” ，即直接用取走时间减去存在时间，取小时数； 3)计算寄存分钟数： 在单元格 G4中输入以下公式：“ =IF(TIME(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),SECON
13、D(D4),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4)-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)” ，此时即可计算出所有型号的包裹寄存的分钟数，其公式形式和计算小时数的公式相似，只是将 HOUR换成了MINUTE，其判断条件和前面的一样，如果取走时间没有超过寄存时间，分钟数则为 “ MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)+TIME(HOUR(D4),MINUTE(D4),SECOND(D4)” 。如果超过了，分钟数则为 “ MINUTE(TIME(HOUR(D4),MINU
14、TE(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=64),D7,“)” 。 图 26 31、 OR 函数： 判断逻辑值并集的计算结果，在所有的参数中只要有一个逻辑值为 TRUE，该函数的返回值即为 TRUE。例如已知某企业的员工姓名和出生年份两列值，如图 27 所 示，然后根据输入的年份判断员工中是否有这一年出生的人，并且统计出共有几个。 1）
15、在单元格 D3 中输入判断值 “1975” ，即判断是否有 1975 年出生的人，然后在单元格 E3 中输入以下公式：“=OR(D3=C3:C8” ，在该公式中，表示将 D2 单元格中的值与数据区域 “C3:C8” 中的每一个值作比较，判断是否相等。如果任何一人比较结果为真，函数 OR 则返回 TRUE，也就是 D3 单元格中的值位于这个列表中。由于是在一个数组中查找是否存在某个指定的值，所以公式要以数组的形式输入，输入公式后要按 Ctrl+Shift+Enter组合键完成； 3)计算 1975 年出生的人数，在单元格 E3 中输入以下公式： “=SUM(IF(D3=C3:C8,1,0) ”
16、，在该公式中先使用IF 函数将单元格 D3 中的值与数据区域 “C3:C8” 中的每一个值进行比较，如果两个值相等则返回 1，否则返回 0。然后利用 SUM 函数对所有的返回值求和，最后得到的数据就是 “1975”出现的次数，即有几个人是 1975 年出生的。该公式要以数组公式的形式输入。 图 27 32、 ADDRESS 函数： 该函数使用方法如图 28 所示。 图 28 33、 AREAS 函数： 该函数使用方法如图 29 所示。 图 29 34、 CHOOSE 函数： 例如评定学生成绩，利用该函数可以评定销售人员的业务能力，还可以返回成绩的档次以及是否及格等，其计算方法都是一样的。下面以学生成绩表为例看一下CHOOSE 函数的应用方法。 1)首先在工作表中输入如图 30 所示的学生成绩，然后在单元格 F3
