excel xp函数实用教程 excel xp函数实用教程

《excel xp函数实用教程 excel xp函数实用教程》由会员分享，可在线阅读，更多相关《excel xp函数实用教程 excel xp函数实用教程（103页珍藏版）》请在金锄头文库上搜索。

1、Excel XP 函数实用教程 Excel XP 函数实用教程张迎新Excel 是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据 库。本文介绍 Excel XP 函数的新增功能、常用函数和数据库的使用方法，以及 Excel XP 的网络集成特 性。一、Excel XP 函数新增功能1公式错误检查公式是 Excel 处理数据的主要工具，由于它的运算符和参数比较复杂，用户构造公 式时极易发生错误。为了加快错误的排除速度，Excel XP 提供了公式错误检查功能。当单元格中的公式出现错误时，其左侧会出现智能标 记按钮。单击该按钮可以打开一个智能标记菜单，其上端显示出错误的名称（如

2、“无效名 称错误” ） ，能帮助用户迅速发现错误原因。2函数工具提示用户直接输入公式的函数名和参数时，不能看到函数格式和参数的提示，这是公式 发生错误的一个重要原因。为此，Excel XP 增加了函数工具提示功能。以公式“=SUMIF（A1:A8） ”为例，当你输入“=SUMIF（） ”后，公式下方会显示黄色的函数工具提示条，提供了有关函数和参数的语 法信息。你只要单击其中的函数或参数名称，就可以打开帮助获得更多信息。3用自然语言搜索函数Excel 拥有数百个函数，寻找适用的函数是初级用户面临的难题。为此，Excel XP 在“插入函数”对话框中增加了“搜索函数”功能。假如你要完成数据排序任务

3、， 可以单击工具栏中的“插入函数”按钮打开对话框，在其中的“搜索函数”框内输入“排 序” ，然后单击“转到”按钮，对话框下面的“选择函数”框中显示“RANK”等排序函数。4监视窗口如果一个工作表的不同单元格或者多个工作表设置了公式，要想查看其内容和计算 结果，必须在不同单元格或工作表中转换。为此，Excel XP 增加了一个名为监视窗口的工具。其使用方法是：选中含有公式的待监视单元格， 再用鼠标右键单击工具栏选择“监视窗口” 。然后单击“监视窗口”中的“添加监视”按钮， 被监视的公式及其计算结果就会显示在监视窗口中。5公式审核Excel XP 新增了一个“公式审核”工具栏，它提供了几个新的数据

4、审查工具，例如“错误 检查” 、 “追踪引用单元格”和“公式求值” 。 “错误检查”与语法检查程序类似，它用特定 的规则检查公式中存在的问题，可以查找并发现常见错误，你可以在“选项”对话框的 “错误检查”选项卡中启用或关闭这些规则。 “追踪引用单元格”可以用蓝色箭头等标出公 式引用的所有单元格，追踪结束后可以使用“移去单元格追踪箭头”按钮将标记去掉。 “公 式求值”可以打开一个对话框，用逐步执行方式查看公式计算顺序和结果，能够清楚了解 复杂公式的计算过程。二、Excel XP 函数快速上手1求和函数 SUM语法：SUM（number1，number2， ） 。参数：number1、number

5、2 为 1 到 30 个数值（包括逻辑值和文本表达式） 、区域或引用，各参数之间必须用逗号加以分隔。注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转 换为 1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组 或引用中的空白单元格、逻辑值、文本或错误值则被忽略。应用实例一：跨表求和使用 SUM 函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域 进行求和，可以采用以下方法：选中 Excel XP“插入函数”对话框中的函数， “确定”后打开“函数参数”对话框。切换至第 一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一

6、工作表中的其他 区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上 述操作完成后切换至第二个工作表，重复上述操作即可完成输入。 “确定”后公式所在单元 格将显示计算结果。应用实例二：SUM 函数中的加减混合运算财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规 定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，- E2） ”这样的公式。其中 B2:B6，C2:C9 引用是收入，而 D2、E2 为支出。由于 Excel 不允 许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出

7、正确 结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4） ”这样的形式。应用实例三：及格人数统计假如 B1:B50 区域存放学生性别，C1:C50 单元格存放某班学生的考试成绩，要想统 计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=女 ，IF（C1:C50=60，1，0） ） ） ” ，由于它是一个数组公式，输入结束后必须按住 Ctrl+Shift 键回车。公式两边会自动添加上大括号，在编辑栏显示为“=SUM（IF（B1:B50=女 ，IF（C1:C50=60，1，0） ） ）” ，这是使用数组

8、公式必不可少的步骤。2.平均值函数 AVERAGE语法：AVERAGE（number1，number2， ） 。参数：number1、number2 是需要计算平均值的 130 个参数。注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文 字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零 排除在外，则要使用特殊设计的公式，下面的介绍。应用实例一：跨表计算平均值标签名为“一班” 、 “二班”和“三班”的工作表存放各班学生的成绩，则它们的总 平均分计算公式为“=AVERAGE（一班!C1:C36，三班!C1:C32，三班!C1:C45） ” 。式中

9、的 引用输入方法与 SUM 跨表求和时相同。应用实例二：忽略零的平均值计算公式假设 A1:A200 随机存放包括零在内的 48 个数值，在 AVERAGE 参数中去掉零引用 很麻烦，这种情况可以使用公式“=AVERAGE（IF（A1:A200=60，A1:A36）/COUNTIF（A1:A36，=60） 。 公式中的“=SUMIF（A1:A36，=60，A1:A36） ”计算及格学生的总分，式中的“A1:A36”为提供逻辑判断依据的单元格引用， “=60”为判断条件，不符合条件的数据 不参与求和，A1:A36 则是逻辑判断和求和的对象。公式中的 COUNTIF（A1:A36，=60）用来统计及

10、格学生的人数。8贷款偿还计算函数 PMT语法：PMT（rate，nper，pv，fv，type）.参数：如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷 款的重要依据，Excel XP 提供的 PMT 函数是完成这一任务的好工具。语法中的 rate 是贷款利率；nper 为 贷款偿还期限；pv 是贷款本金；fv 为最后一次付款后剩余的贷款金额，如果省略 fv，则认 为它的值为零；type 为 0 或 1，用来指定付款时间是在月初还是月末。如果省略 type，则 假设其值为零。应用实例：购房还款金额假如你为购房贷款十万元，如果年利率为 7%，每月末还款。采用十年还清方式时， 月

11、还款额计算公式为“=PMT（7%/12，120，-100000） ” 。其结果为￥-1，161.08，就是你 每月须偿还贷款 1161.08 元。9样本的标准偏差函数 STDEV语法：STDEV（number1，number2， ） 。参数：number1，number2， 为对应于总体样本的 1 到 30 个参数，它们可以是数 值、引用或数组。注意：STDEV 函数的参数是总体中的样本，并忽略参数中的逻辑值（true 或 false） 和文本。如果需要用全部数据计算标准偏差，则应使用 STDEVP 函数。如果参数中的逻辑 值和文本不能忽略，请使用 STDEVA 函数。应用实例一：成绩离散度估

12、计假设某班共有 36 名学生参加考试，随机抽取的五个分数为 A1=78、A2=45、A3=90、A4=12 和 A5=85。如果要估算本次考试成绩相对平均分的离散程 度，即学生的考试成绩偏离平均分的多少，可以使用公式“=STDEV（A1:A5） ” 。其计算结 果为 33.00757489，标准偏差的数值越大成绩越分散。应用实例二：质量波动估计质量控制等场合也能用到 STDEV 函数，如从一批钢丝绳中随机抽出若干进行试验， 分别测出它们的抗拉强度。根据 STDEV 函数的计算结果即可判断钢丝绳的抗拉强度是否 分散，如果计算的标准偏差比较小，说明抗拉强度的一致性好，质量比较稳定。反之说明 钢丝绳

13、的质量波动较大，抗拉强度不够一致。10排序函数 RANK语法：RANK（number，ref，order） 。参数：number 是需要计算其排位的一个数字；ref 是包含一组数字的数组或引用 （其中的非数值型参数将被忽略） ；order 是用来说明排序方式的数字（如果 order 为零或省 略，则以降序方式给出结果，反之按升序方式） 。应用实例：产值排序假如图 1 中的 E2、E3、E4 单元格存放一季度的总产值。(图 1)计算各车间产值排名的方法是：在 F2 单元格内输入公式“=RANK（E2，$E$2:$E$4） ” ，敲回车即可计算出铸造车间的产值排名是 2。再将 F2 中的 公式复制

14、到剪贴板，选中 F3、F4 单元格按 Ctrl+V，就能计算出其余两个车间的产值排名 3 和 1。如果 B1 单元格中输入的公式为“=RANK（E2，$E$2:$E$4，1） ” ，则计算出的序 数按升序方式排列，即 2、1 和 3。需要注意的是：相同数值用 RANK 函数计算得到的序数（名次）相同，但会导致后续数字的序数空缺。假如上例中 F2 单元格存放的数值与 F3 相同，则按本法计算出的排名 分别是 3、3 和 1（降序时） 。即 176.7 出现两次时，铸造和维修车间的产值排名均为 3，后 续金工车间的排名就是 1（没有 2） 。11四舍五入函数语法：ROUND（number，num_

15、digits） 。参数：number 是需要四舍五入的数字；num_digits 为指定的位数，number 将按此位 数进行四舍五入。注意：如果 num_digits 大于 0，则四舍五入到指定的小数位；如果 num_digits 等于 0，则四舍五入到最接近的整数；如果 num_digits 小于 0，则在小数点左侧按指定位数四舍五入。应用实例：消除计算误差假设 Excel 工作表中有 D2=356.68、E2=128.12，需要将 D2 与 E2 之和乘以 0.1，将 计算结果四舍五入取整数，再将这个结果乘以 1.36（取两位小数）得到最终结果。一般用户的做法是选中某个单元格（如 F2）

16、 ，使用“单元格”命令将它的小数位数 设为零，然后在其中输入公式“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 的正确结果 65.28，其中 65.90 是（D2+E2）*0.1 未经四舍五 入直接乘以 1.36 的结果。以上计算结果说明：“单元格格式” 、 “数字”选项卡设置的“小数位数” ，只能将单 元格数值的显示结果进行四舍五入，并不能对所存放的数值进行四舍五入。换句话说，单 元格数值的显示结果与实际存放结果并不完全一致，如果不注意这个问题，计算工资等敏 感数据就会出现错误。例如在上例