Excel word ppt office使用技巧大全(DOC格式)-第58部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！





　　　　　　　参数：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　具有强大的数据库功能，对包含大量数据的表格进行排序、筛选等处理易如反掌，　



下面介绍常用的数据库操作。　　



　　　　　　　　1．数据排序　　



　　　　　　　Excel　XP　第一行的单元格具有特殊作用，其中的文字相当于数据库字段名，可作为关键字　



参与排序、记录单等各种操作。　　



　　　　　　　　（1）文本排序　　



　　　　　　　选举等场合需要按姓氏笔划为文本排序，Excel　　提供了比较好的解决办法。如果你要将图　



1　数据表按车间名称的笔划排序，可以使用以下方法：选中排序关键字所在列（或行）的首个　



单元格，单击　Excel―数据‖菜单下的―排序‖命令打开对话框，再单击其中的―选项‖按钮。选中―排　



序选项‖对话框―方法‖下的―笔画排序‖，再根据数据排列方向选择―按行排序‖或―按列排序‖，―确　



定‖后回到―排序‖对话框。如果你的数据带有标题行（如―单位‖之类），则应选中―有标题行‖　（反　



之不选），然后打开―主要关键字‖下拉列表，选择其中的―单位‖，选中排序方式（―升序‖或―降　



序‖　）后―确定‖，表中的所有数据就会据此重新排列。此法稍加变通即可用于―第一名‖、―第二　



名‖等文本的排序，请大家自行摸索。　　



　　　　　　　　（2　）自定义排序　　



　　　　　　　如果你要求　Excel　按照―金工车间‖、―铸造车间‖和―维修车间‖　的特定顺序重排工作表数据，　



前面介绍的几种方法就无能为力了。这类问题可以用定义排序规则的方法解决：首先单击　



Excel―工具‖菜单下的―选项‖命令，打开―选项‖对话框中的―　自定义序列‖选项卡。选中左边―　自　



定义序列‖下的―新序列‖，光标就会在右边的―输入序列‖框内闪动，你就可以输入―金工车间‖、　



―铸造车间‖等自定义序列了，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　464　　


…………………………………………………………Page　465……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



敲回车。如果序列已经存在于工作表，可以选中序列所在的单元格区域单击―导入‖，这些序列　



就会被自动进入―输入序列‖框。无论采用以上那种方法，单击―添加‖按钮即可将序列放入―　自　



定义序列‖　中备用。　　



　　　　　　　使用排序规则排序的具体方法与笔划排序很相似，只是你要打开“排序选项”对话框中的　



　“自定义排序次序”下拉列表，选中前面定义的排序规则，其他选项保持不动。回到“排序”　



对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据的自定义排序。　　



　　　　　　　2　．数据检索　　



　　　　　　　如果　Excel　　　　



　　　　　　　XP　工作表中的数据字段很多时，查看一条或几条记录需要左右滚动屏幕，此时可以采用　



以下方法检索并查看：单击―数据‖菜单下的―记录单‖命令打开对话框（图　2　）。　　



　　　　　　　再次单击其中的“条件”按钮。对话框中的字段就会变成空白等待输入，并且“条件”按　



钮变为“表单”按钮。在对话框的相应字段位置输入条件，如果在“序号”字段框内输入　12　



回车，则序号为　12　的数据就会显示在对话框中。当然，你输入的检索条件可以使用》　、《　、》=　、　



170‖　，则表示检索总分大　



于　170　的记录，回车后第一条大于　170　的数据就会显示在对话框中。继续单击―上一条‖或―下　



一条‖按钮，可以查看检索出来的其他数据。　　



　　　　　　　3　．数据筛选　　



　　　　　　　上面介绍的方法可以检索满足某一条件的记录，但是检索条件比较复杂时（如检索总分大　



于　170　和小于　120　的纪录），这种检索方法就无能为力了。此时可以使用Excel　　　　



　　　　　　　XP　提供的自定义筛选功能，操作方法是：单击―数据‖、―筛选‖子菜单下的―　自动筛选‖命令，　



每个字段名的右侧会出现一个下拉按钮。根据你要筛选的字段单击下拉按钮，在下拉菜单中选　



择―　自定义‖命令打开对话框（图　3　）。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　465　　


…………………………………………………………Page　466……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　单击第一行左侧的下拉按钮，选择其中的“大于”，然后在其右边输入　170；按相同方法在　



第二行中选择―小于‖，并在右边输入　120，选中两行中间的―或‖后单击―确定‖按钮，工作表会　



自动显示所有符合筛选条件的记录。筛选出来的记录可以执行复制等操作，如果你要清除筛选　



结果，只要将―数据‖、―筛选‖子菜单下的―　自动筛选‖前的选中标记去掉即可。　　



　　　　　　　4　．数据汇总　　



　　　　　　　小计、合计是应用非常广泛的数据统计方式，Excel　　　　



　　　　　　　XP　分类汇总是此类操作的高手。你只要将光标放在工作表的任一单元格中，单击―数据‖　



菜单下的―分类汇总‖命令打开对话框。如果要对某个班级的数学、语文成绩进行汇总，则应在　



―分类字段‖　中选中―班级‖　，在―汇总方式‖　中选择―求和‖　，在―选择汇总项‖　中选中―数学‖和―语　



文‖　。如果选中―汇总结果显示在数据下放‖，单击―确定‖按钮后汇总结果会显示在工作表数据　



的下面，反之则会显示在工作表数据上方。如果你想清除工作表中的分类汇总结果，只要打开　



―分类汇总‖对话框，单击其中的―全部删除‖按钮就可以了。　　



　　　　　　　5　．数据透视表　　



　　　　　　　数据透视表是　Excel　　　　



　　　　　　　XP　数据库功能的重要组成部分，它能够非常灵活的汇总数据。如果你要对一个学生成绩　



工作表进行统计，计算所有学生的数学成绩总分，采用数据透视表可以快速完成任务。　　



　　　　　　　单击“数据”菜单下的“数据透视表和数据透视图”命令打开向导，首先选择数据源类型　



和创建的报表类型，完成后单击“下一步”按钮。这时要选择建立数据透视表的数据区域，你　



只要用鼠标在工作表中拖动即可将引用的数据区域放入对话框。接着选择数据透视表的位置，　



可以选择“新建工作表”或“现有工作表”。单击“完成”按钮，将“数据透视表字段列表”　



中的“数学”拖入工作表的指定位置，Excel　　　　



　　　　　　　XP　会立刻计算出所有学生的数学成绩合计。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　466　　


…………………………………………………………Page　467……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　6　．数据查找　　



　　　　　　　数据筛选的对象是工作表中的某条记录，这里介绍的查找对象则是单元格中的数据，Excel　　



XP　可以执行的查找有以下几种类型：　　



　　　　　　　　（1）普通查找　　　　



　　　　　　　它利用编辑菜单中的查找命令查找特定的数据，操作方法如下：单击“编辑”菜单中的“查　



找”命令，打开“查找”对话框。在“查找内容”后输入要查找的内容，单击“查找下一个”　



按钮，光标就会停留在首先发现数据的单元格中。如果没有发现要查找的数据，Excel　　　　



　　　　　　　XP　会弹出对话框告知用户。　　



　　　　　　　　（2　）特殊查找　　



　　　　　　　特殊查找可以找出包含相同内容（如文本）或格式的所有单元格，或者存在内容差异的单　



元格，其操作步骤如下：如果你要找出工作表中含有文本的所有单元格，请选中含有文本任意　



一个单元格，然后单击“编辑”菜单中的“定位”命令。再单击对话框中的“定位条件”按钮，　



选中“定位条件”对话框中的“常量”，再选中复选项中的“文本”。单击“确定”按钮后，所　



有符合条件的所有单元格就会选中。　　　　



　　　　　　　　　　　　如果你要找出数据行或列中与活动单元格不匹配的部分，可以打开　“定位条　



件”对话框。如果需要找出特定行中与活动单元格不同的单元格，可以选择　“行内容差异单　



元格”；若要找出列中与活动单元格不同的单元格，则应选中　“列内容差异单元格”，单击　



　“确定”按钮即可完成查找。　　



　　　　　　　　四、Excel　XP　网络集成　　



　　　　　　　　1．Web　数据共享　　



　　　　　　　　Excel　　　



　　　　　　　　XP　可以从网页中导入数据进行分析。你只须选择　“数据”、　“导入外部数据”子菜　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　467　　


…………………………………………………………Page　468……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



单下的　“导入数据”，在　“选取数据源”对话框中找到　Web　页，单击　“打开”按钮就可以将　



其导入　Excel。当你进行过一次数据导入后，用鼠标右键单击打开的网页，可以看到快捷菜单　



中增添了　“导出到　Microsoft　　　



　　　　　　　　Excel”命令。如果希望从远程数据源导入数据，则应使用　“数据连接向导”，它可　



以从Microsoft　SQL　　　



　　　　　　　　Server、开放式数据库连接（ODBC）以及联机分析处理（OLAP）等数据源中查找并　



导入数据。　　



　　　　　　　　此外，Excel　XP　可以直接粘贴　Word　XP　表格，从而共享其中的数据，进一步扩大了　



两者协同工作的范围。　　



　　　　　　　　2．E…mail　功能　　



　　　　　　　　Excel　的　“常用”工具栏带有一个　“电子邮件”按钮。单击它可以在当前窗口显示一　



个邮件发送工具栏，其界面与　Outlook　　　



　　　　　　　　Express　很相似，你只要输入收件人的　E…mail　地址和主题等，就可以将文件以附件　



或正文的形式发送出去。作为正文发送时，只须填写对方的　E…mail　地址，并在　“主题”栏内　



进行简单介绍，然后单击　“发送该工作表”按钮（如果你选中了工作表中的部分区域，该按　



钮变成　“发送所选区域”）即可。当其他用户收到邮件后，可以直接对正文进行编辑修改，　



处理完毕后用同样方法将文件发给你。单击　“附加文件按钮”，可以打开　“插入附件”对话　



框，像　Outlook　　　



　　　　　　　　Express　那样插入并发送附件。　　



　　　　　　　　3．网页保存和预览　　



　　　　　　　　Excel　　　



　　　　　　　　XP“文件”菜单下有一条　“保存为　Web　页”命令，用户可以选择将工作簿或当前工　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　468　　


…………………………………………………………Page　469……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



作表保存为　Web　页（HTML、XML），还可以在工作表中添加交互功能。　　



　　　　　　　　Excel　XP　内建了网页浏览功能，如果你想将工作表发布到网上，只要单击　“文件”　



菜单下的　“Web页预览”命令，Excel　　　



　　　　　　　　XP　就会自动调用　IE　打开工作表。　　　



　　



Excel　XP　函数运用实例四则　　　　　　　　　



　　　　　　　实例一：身份证中信息的提取　　



　　　　　　　　1。持证人性别的判断：我们知道，旧身份证（15　位）号码的最后　1　位数值，表示持证人的性　



别，若为奇数则为男，若为偶数则为女。我们利用　Excel　函数，对身份证号码进行适当处理，　



即可自动判断持证人的性别，并将结果填入相应的单元格中（此处假定身份证号码保存在　B2　



单元格中，结果填入　C2　单元格中）　。　　



　　　　　　　　（1）利用函数的嵌套来判断。在C2　单元格中输入公式：=IF（RIGHT（B2　，1）=〃1〃，〃男〃，　　



IF（RIGHT（B2　，1）=〃3〃，〃男〃，　　　　



　　　　　　　IF（RIGHT（B2　，1）=〃5〃，〃男〃，　　IF（RIGHT（B2　，1）=〃7〃，〃男〃，　　　　



　　　　　　　IF（RIGHT（B2　，1）=〃9〃，〃男〃，〃女〃）））））。　　



　　　　　　　（2　）综合运用函数来判断。在C2　单元格中输入公式：=IF（OR（RIGHT（B2　，1）=〃1〃，RIGHT（B2，　



1）=〃3〃，RIGHT（B2　，1）=〃5〃，RIGHT（B2　，1）=〃7〃，RIGHT（B2　，1）=〃9〃），〃男〃，〃女〃）。　　



　　　　　　　在上面两个公式中，前一个是利用　IF　函数的嵌套来实现的，由于函数最多只能嵌套　7　层，　



因而有一定的局限性，且比较麻烦；后一个公式由于采用了　OR　函数，既没有层数的限制，也　



简单一些。　　



　　　　　　　　　　（3　）运用其他函数来判断。我们在　C2　　　　　　　　　　　　　　　　　　单元格中输入公式　―=IF（RIGHT（B2　，　



1）/2=INT（RIGHT（B2，1）/2），〃女〃，〃男〃）‖或―=IF（MOD（RIGHT（B2　，1），