Excel word ppt office使用技巧大全(DOC格式)-第86部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
应用实例：消除计算误差
假设 Excel 工作表中有 D2=356。68 、E2=128。12 ，需要将D2 与 E2 之和乘以 0。1，将计算结
果四舍五入取整数，再将这个结果乘以 1。36 （取两位小数）得到最终结果。
一般用户的做法是选中某个单元格（如 F2 ），使用―单元格‖命令将它的小数位数设为零，
然后在其中输入公式―F2=（D1＋E1 ）*0。1‖。再将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
458
…………………………………………………………Page 459……………………………………………………………
的正确结果 65。28，其中 65。90 是（D2＋E2 ）*0。1 未经四舍五入直接乘以 1。36 的结果。
以上计算结果说明：“单元格格式”、“数字”选项卡设置的“小数位数”，只能将单元格数
值的显示结果进行四舍五入，并不能对所存放的数值进行四舍五入。换句话说，单元格数值的
显示结果与实际存放结果并不完全一致，如果不注意这个问题，计算工资等敏感数据就会出现
错误。例如在上例中，F2 单元格内的数值虽然显示为 48 ，但实际存放的却是 48。45 ，自然得
出了48。45*1。36=65。90 的结果（按计算要求应为 65。28 ）。
要解决这个问题并不难，你只须在 G2 单元格内输入公式―= （ROUND （（D2＋E2 ）*0。1，0 ））
*1。36‖，就可以按要求计算出正确结果 65。28 。式中的ROUND 函数按指定位数对― （D2＋E2 ）
*0。1‖进行四舍五入，函数中的参数 0 将― （D2＋E2 ）*0。1‖四舍五入到最接近的整数。
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
459
…………………………………………………………Page 460……………………………………………………………
语法：PERCENTILE （array，k ）。
参数：array 为定义相对位置的数值数组或数值区域，k 为数组中需要得到其百分排位的值。
注意：如果 array 所在单元格为空白或数据个数超过 8191，则返回#NUM ！错误。如果k1 ，则返回#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 中显示
错误信息― ＃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 ），″″）‖ 。
460
…………………………………………………………Page 461……………………………………………………………
式中 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 =
461
…………………………………………………………Page 462……………………………………………………………
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 ）。
462
…………………………………………………………Page 463……………………………………………………………
参数：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″，NOW ））/360 ，0 ）‖ 。其中NOW 函数返回当前
的系统日期和时间。
20 ．字符提取函数MID
语法：MID （text ，start_num，num_chars ）或MIDB （text ，start_num，num_bytes ）。
参数：text 是含有要待提取字符的文本，start_num 是要提取的第一个字符的位置（其中第
一个字符的 start_num 为 1 以此类推），num_chars
指定 MID 从文本中提取的字符个数，Num_bytes 指定 MIDB 从文本中提取的字符个数（按
字节计算）。
应用实例：姓氏提取和统计
假如工作表 C 列存放着职工的姓名，如果想知道某一姓氏的职工人数，可以采用以下方法。
首先使用公式―=MID （C1，1，1）‖或―=MIDB （C1，1，2 ），从C1 中提取出职工的姓氏，采
用拖动或复制的方法即可提取其他单元格中的职工姓氏。然后使用公式―=COUNTIF（D1：D58 ，″
463
…………………………………………………………Page 464……………………………………………………………
张″）‖统计出姓张的职工数量，随后改变公式中的姓氏就可以统计出其他姓氏职工的数量。
三、Excel XP 数据库速成
Excel XP 具有强大的数据库功能，对包含大量数据的表格进行排序、筛选�
