Excel高级教程

Excel高级教程 在Excel中统计与上报两不误 实现思路　　 　　因为在Excel中就能实现单元格中数据的动态更新，所以我们只需在Excel文档中建立两个工作表，分别取名为“出勤统计表”和“汇总表”。把要进行数据输入与统计的“学生出勤情况统计表”建在“出勤统计表”工作表中（如图1），而把作为上报材料的“学生出勤情况汇总表”建在“汇总表”工作表中（如图2）。然后把“学生出勤情况汇总表”与“学生出勤情况统计表”中的有关表格进行链接，就可实现数据的动态更新了。 　　如果上报材料除了此汇总表以外还有许多其他文字，只需在“汇总表”工作表中插入OLE Word对象即可，这样我们在“出勤统计表”工作表中输入数据进行统计后，马上就可在“汇总表”中打印上报材料，而无需运动Word了。　　 　　数据统计的动态更新　　 　　其实，实现单元格中数据的动态更新就是把要实现数据动态更新的单元格与源数据单元格进行链接，链接方法以“汇总表”中“汉语言”专业的出勤率为例：　　 　　1. 打开“出勤统计表”工作表，激活“汉语言”专业的出勤率单元格H5，进行复制。　　 　　2. 打开“汇总表”工作表，激活“汉语言”专业的出勤率单元格B3，单击[粘贴]按钮，这时粘贴入单元格里的数据会无效。我们只要单击单元格旁边的“粘贴”图标，选择“链接单元格”（如图3）。这时函数编辑框中显示为“=出勤统计表!$H$5”，说明此单元格已与“出勤统计表”工作表中的H5单元格进行了链接。 　　数据的上报　　 　　如果需要上报的“汇总表”中还需输入许多其他文字，如何在“汇总表”工作表中插入OLE Word对象呢？这里的OLE Word对象实际就是嵌入在Excel工作表中的Word文档，它可弥补Excel对文字处理能力的不足。方法很简单，首先单击菜单中“插入/对象”命令。然后在弹出的“对象”对话框中的“新建”选项卡里的“对象类型”栏中选择“Micrsoft Word 文档”，单击[确定]按钮（如图4）。最后只需在插入的OLE Word对象中对文字进行输入并编辑，方法与在Word中一样。 Excel中单列表格的打印方法 《中国电脑教育报》2004年第23期E9版刊登了“打印Excel中的单列表格”一文，介绍利用Excel的文件另存与Word的查找替换与排版实现Excel中的单列表格的打印方法。其实利用Excel自身的公式操作，也可轻松实现单列表格文本的合成打印。以横向排列，逐行显示的“学生姓名”列为例，具体操作方法如下： 　　1. 先在当前表sheet1的适当位置选择一列作为数据合成列，如F列，并在其下第2个单元格F2中输入公式“=F1&" "&B2”，(公式表示在二个姓名之间空2格，B列为要打印的列)则F2中产生第一个姓名，选择F2，利用向下拖动填充句柄的方法将F2中公式复制到以下单元格中，直到最后一行，则以下单元格的内容将会是当前行以前的所有行中的姓名合成。 　　2. 选择sheet2表，在A1单元格中输入公式“=TRIM(Sheet1!F49)”，F49为sheet1表中最后一人的姓名合成值，即将所有姓名组合复制到A1单元格中，然后将A1单元格的对齐格式设置为自动换行，水平方向左对齐，垂直方向上对齐，并适当调整A列的宽度。 　　3. 将A1单元格设置为打印区域，通过打印预览与打印可实现单列文本打印效果的观察与输出。 　　利用这种方法操作的优点是两个单元格的内容间隔可根据需要任意设置。同时，通过操作Excel打印预览中的页边距线，可迅速任意调整页面上下左右边距，达到合理排版的功能，另外，对A1单元格的内容通过选择性粘贴的复制，也可将合成后的具体内容在Word中加以排版输出。 巧妙删除Excel中的换行符 在用Excel输入数据时，我们知道按下“Alt+Enter”可以在单元格中实现换行。笔者在管理学籍时，录入家庭住址时就常用这一招。不过，今天却遇到了一件麻烦事，领导要求把学生的家庭住址变成一行，也就是要把换行符全部去掉。真是狂晕，如果一个一个去修改，还不把我累死。 　　静下心来想一想，“解铃还需系铃人”，Excel应该会有办法的。捣鼓了一会儿，办法找到了，也非常简单，方法如下：选定当前列，单击“格式/单元格”，出现“单元格格式”对话框，单击“对齐”标签，可以看到中间的“自动换行”是选中的，把它前头面的小勾去掉，就一切OK了。　　 　　其实，我们在按下“Alt+Enter”实现在单元格换行时，就是把这个“自动换行”的选项选上了，现在反过来，也同样可以把它去掉，如此而已。 Execl中为每页设置行标 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 如果不设置每页的表头，则单击“打印预览”按钮，可以看到只有第一页中有表的表头，而其它的页中都没有；这样打印出来的表看起来会很不方便，我们可以通过给工作表设置一个打印表头来解决这个问题。 方式一：由于这个功能不能在预览视图中设置，单击“关闭”按钮回到正常的编辑视图，打开“文件”菜单，选择“页面设置”命令，打开“页面设置”对话框（如图6），单击“工作表”选项卡，单击“顶端标题行”中的拾取按钮，对话框变成了一个小的输入条， 在工作表中选择数据上面的几行，这时在“顶端标题行”框中会自动输入这个区域的的引用，把它设为表头，单击输入框中的返回按钮，回到“页面设置”对话框，单击“确定”按钮，现在单击“打印预览”按钮，所有的页中就都标题了。 方式二： 在”文件”--(”页面设置”的”打印标题”区的”顶端标题行”框输入要打印的行标题的引用,如将第1行到第3行设为每页的行标题题,就输入$1:$3即可. Excel中日期与时间的快速处理 1、任意日期与时间的输入数字键与“/”或“-”配合可快速输入日期，而数字键与“：”配合可输入时间：如输入“3/25”，然后回车即可得到“3月25日”。又如输入“9:25”，回车即得到“09:25”。 2、当前日期与时间的快速输入选定要插入的单元格，按下【Ctrl】键与分号键“；”，然后回车即可插入当前日期。而要输入当前时间，同时按住【Ctrl】+【Shift】键与分号键“；”，然后回车即可。 利用Excel的函数和筛选功能巧妙分班 1、处理思路 学校大多根据学习成绩进行分班，一般是按总分名次来划分的：比如要分8个班，则第1名分到一班、第2名分到二班……第8名分到八班，接着第9名分到八班、第10名分到七班……第16名分到一班。 2、划分班级 先将学生情况及成绩调入Excel工作表中，按总分降序排列(假设总分在H列，第一名在第三行)，在总分列的右一列(即I列)从I3单元格开始向下填充1、2、3……再在其右一列(即J列)用公式算出应分在哪一个班级。具体操作如下：在J3单元格中输入公式 “=IF(MOD(I3,2*8)>8,8-MOD(I3,8)+1,(IF(MOD(I3,2*8)=0,1,MOD(I3,2*8))))”，再在J列按公式将每名学生自动填充上班级号，每个工作表改名为一班、二班……在每个班级工作表中复制和原表一样的表头，在原成绩表中用“自动筛选”按班级号筛出各个班级的学生，并将其复制到相应的各个班级工作表即可。以上公式中的“8”是要划分的班级个数，可以根据班级数的变化而修改，若分成n个班级，则总公式为： “=IF(MOD(I3, 2*n)>n,n-MOD(I3,n)+1,(IF(MOD(I3,2*n)=0,1,MOD(I3,2*n))))”｡ 3、打印名单 分好班级后，要把各班级的学生名单打印出来，一个班最好用一张 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 纸，可用分栏打印解决班级人数多的问题。而Excel没有专门的分栏打印功能，我就利用Excel的公式制作出分栏打印效果。具体操作如下：先将某一班级名单按自己要求设置好，如页边距、表头、行高、字体等。在打印预览中，判断出每页的数据行数x(所谓数据行，指表头除外的记录行)，回到普通视图下，在表的右边空列中，从第一数据行开始填充自然数序列1、2、3……假设此列在K列，第一数据行为第3行，则在L3单元格中填入公式“=mod(int((K3-1)/x),y)”(其中x为每页的数据总行数，y为分栏的栏数，就分班而言，两栏就够用了)，并向下填充整个表。则该列出现了从0到y-1的数，即给每行计算出了该行所在的栏号，复制表头到新的工作表或新建的工作薄中。在班级工作表中也用“自动筛选”功能分别筛选出第0栏、第1栏……第y-1栏，并分别复制到新工件表中。 再对各新工作表进行相应的设置，如页边距、表头、行高、字体等，注意不要逐个工作表进行设置，按住“Ctrl”键，用鼠标单击窗口中需要设置相同内容的各个不同工作表标签，在其中一个工作表中修改设置，另外的工作表中就能自动修改。所有工作表都设置好后，分栏打印的各班名单就制作好了，用打印机打印出来便大功告成。 此方法都保留了原表，有利于修改，如要改变班级数、打印栏数、每页行数，只需改变n、x、y的值。另外，此方法用的是“自动筛选”功能，也可以用“高级筛选”来完成。 在Excel单元格中设置“斜线”效果 表格中出现斜线是经常的事(如图1)，以前遇到这种情况，要么把表格导入到WPS Office或Word中处理，要么就是打印好了之后再用笔加上去。经过一番摸索，终于发现了在Excel中设置斜线的方法。 图1 图21、 实现在单元格中分行的效果：我们首先在单元格中输入“性别”，这时候按回车键的话，光标就会转到其他单元格中去了，所以必须按住“Alt”键的同时按回车键，光标转到下一行，接着输入“姓名”，然后再在“性别”前加入若干空格键，即可达到如图2的效果。 图32 在单元格中设置斜线：选择单元格，在右键菜单中选择“设置单元格格式”，在出现的对话框中选择“边框”标签，进行斜线的设置(如图3)。 将Excel的文本数字转换成数字 在Excel中，系统将前面带有半角单引号的数字视为文本对待，而且为了某些需要，用户可以通过“格式”菜单中的“单元格”命令，将数字设置为文本格式。然而，现在我们却需要把工作表中文本格式的数字转换成数字，那么以下方法可以一试。 1．一次转换一个单元格 在“工具”菜单上，单击“选项”，再单击“错误检查”选项卡（一定要确保选中了“允许后台错误检查”和“数字以文本形式存储”复选框）。选中任何在左上角有绿色错误指示符的单元格。在单元格旁边，单击出现的按钮，再单击“转换为数字”。  2．一次转换整个区域 在某空白单元格中，输入数字“1”。选中该单元格，并在“编辑”菜单上单击“复制”命令。选取需转换的存储为文本数字的单元格区域。在“编辑”菜单上，单击“选择性粘贴”。在“运算”下，单击“乘”。单击“确定”按钮。  3．处理特殊财务数字  一些财务程序显示负值时在该值右边带有负号“-”。要将此文本字符串转换为数值，必须返回除最右边字符（即负号）以外的所有文本字符串字符，然后乘以“-1”。例如，如果单元格A2中的值为“156-”，那么公式“=LEFT(A2,LEN(A2)-1)*-1”将文本转换为数值“-156”，然后用上面的方法操作 在Excel中快速完成成绩统计查询 每次考试结束之后，最让老师头痛的就是成绩查询了。现在，我们可以利用Excel 2003的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。非常便利。 在Excel中，双击“Sheet1”，将其命名为“成绩统计”，然后建立一个如图1所示的成绩统计表，输入全部学生的各科成绩备用。用常规的方法计算出各学生的总分，并排出名次。 图1 双击“Sheet2”，将其命名为“成绩查询”。在“成绩查询”工作表中，建立如图2所示的表格。单击B2单元格，输入欲查询成绩的学生姓名。单击B3单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B $3:$I$56,2,FALSE)”，按回车键，则可以得到该名学生的 语文 八上语文短文两篇二年级语文一匹出色的马课件部编版八上语文文学常识部编八上语文文学常识二年级语文一匹出色的马课件 成绩；单击B4单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,3,FALSE)”，按回车键，得到数学成绩；单击B5单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,4,FALSE)”，按回车键，得到英语成绩；单击B6单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,5,FALSE)”，按回车键，得到机械成绩；单击B7单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,6,FALSE)”，按回车键，得到电工成绩；单击B8单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,7,FALSE)”，按回车键，得到总分；单击B9单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,8,FALSE)”，按回车键，得到名次。到此，我们就可以得到该名学生的全部考试成绩以及总分、名次了。 图2 有了这个，以后我们只需要在“成绩查询”工作表的B2单元格输入要查询成绩的学生姓名，然后回车，则相关的信息就立刻可以显示。 用Excel批量计算年龄 一位做保险业务的朋友接到一笔团队大订单，有近千条记录。她花了半天的时间用Word录入了客户的档案后，根据要求需要补充填写每个客户的年龄。如果一个个用笔算，又是需要花上大半天的时间，还好客户档案中有每个人的身份证号，于是我用Excel很快帮她计算出了每位客户的年龄。     1. 首先把Word中的客户档案全部复制到Excel中生成一个数据库文件。然后在数据库文件中新建一列，并命名为“出生年月”。因为身份证号有15位和18位，为了计算方便，先对身份证号进行排序。     2. 用MID函数计算出第一个客户的出生年月。函数表达方式如下：MID（E2，7，2），表示第一个客户的身份证号在E列第二行中，要从这个位置中的第7个文本始返回2个长度的字符。    回车确认后，“J2”中的值变为“62”，表示该职工62年出生。接着下拉J2公式复制单元格，快速求出每个职工的出生年月（若身份证为18位，则公式变为MID（E2，9，2））再把J列的格式改为数值型。     3. 在D2中输入计算机公式“=103-J2”就可求出该职工的实际年龄。下拉D2中的公式再次复制，近千个职工的年龄就一键敲定。     小编注：以上的方法中的出生年月只有在2000年以前才有效，如果客户中有2000年以后出生的人，则计算时还要做些变通 资料整理用Excel 单位里的各类文件资料堆积如山，继续存在电脑里，随时都可能遭受病毒侵害和系统崩溃的灭顶之灾。资料的保管最理想最安全的办法是刻录到光盘里。刻录之前，我们必须对杂乱的文档进行整理。有一个很有效的整理方法，虽然操作起来有些麻烦，但日后查找却极为方便。试想，当老板十万火急地要你立马找出上年度的工作总结，面对成百上千的文档，你仅仅动了几下鼠标就轻松搞定时，老板会怎样欣赏你     1．准备工作：打开资源管理器，在任意盘符新建一个文件夹，将要刻录的所有文件存在其中。     2．建立Excel表格：建立一个Excel表格，用来录入文档的相关资料。可以分为序号、文件类型、文件名称、文件来源、文件日期、备注等。文件类型又可分为上级文件、单位文件、人事资料、财务报表等。制作表格时，最好不要进行单元格的合并，以免影响以后的操作。     3．资料的录入：资料录入时无需分类进行，可以无序录入。除了在表格中直接录入外，还可以利用窗体来录入。方法是：单击菜单栏“数据/记录单”，打开窗体录入框，在相应的框格中输入资料。输入完后，回车打开新的一张表格（如图1）。输入之前可以先将文件类型、文件来源等项目中的固定分类，通过“自动更正”功能逐一用简单的符号代替，只要输入简单的符号就可以了。     4．建立超链接：录入之后，选定每个文件名称的单元格，单击鼠标右键，选择“超链接”，在弹出对话框中选择对应的文档。     5．排序：因为我们是无序录入的，录入完后，为了有序排列，就要利用排序功能了。选中表格的任意单元格，执行菜单栏上“工具/选项”，在弹出的对话框中单击“自定义序列”选项卡，在左边框中选择“新序列”，单击“导入”后，选中表格中文件类型的所在区域，再按一次“导入”（如图2），确定后我们会发现原先无序的内容一下子变得有序了。     排序之后，你会发现序号因此被打乱了。没关系，在你排序之前，在序号列之后插入一空白列，任你怎么排序，序号都不会改变。不过，记得排序之后删除这列。     将表格保存到新建的文件夹里并放在所有文档之前。好了，所有的整理工作都完成了，最后就是把文档刻录到光盘里保存了 用Excel函数快速填入部门名称 这学期，我们单位正在申报省部级重点技工学校和市级文明单位，各部门都在积极准备资料。在准备过程中，经常要输入各种表格，进行数据的处理。大量的表格中都需要填入教职员工所在部门的名称，这对于一个有一百多名老师的学校，是一件非常繁琐的工作。在工作中，我发现了一个“偷懒”的办法，现在把它写出来，和大家一起分享！     下面我就以Excel 2000中的一个“学校职工情况表”为例，来告诉大家如何利用Excel函数快速填入部门名称。如图1所示，要求填入相应部门代号后的所在部门名称。 图1     1. 选取需要填入所在部门的范围F4:F118。     2. 单击格式工具栏上的【插入函数】]按钮，接着在“粘贴函数”对话框中的“函数分类”栏选择“查找与引用”，“函数名”选择“LOOKUP”，单击【确定】按钮（如图2）。 图2     3. 在弹出的“选定参数”对话框中选择参数组合方式，单击【确定】按钮。     4. 分别在查找范围中输入E4:E118，在单列范围中输入H4:H8，在输出结果中输入I4:I8（如图3）。最后按下Ctrl+Shift+Enter键，单击【确定】按钮即可。 图3     这样，眨眼间对应教职员工所在部门的名称就填好了，看看最后的结果，是不是感觉很方便！ EXCEL中替换的妙用 我校属于初中、小学合并的一所实验性的学校，全校从小学一年级到初中三年级统一称为一年级、二年级……九年级，每年级有5个班。因此，在设计课程表时，“年级课程表”我就建立了九个，“班级课程表”相应的也是九个（每个5页），如图所示。 按照原来的做法，我开始设置课程表之间的关联。首先将“一各班”与“一”（即一年级）之间设置好关联，接着再去设置“二各班”与“二”之间的关联。当设置到此处的时候，我觉得如果继续这样设置到“九各班”与“九”之间的关联，会浪费很多时间，心里就想到去寻找“捷径”。 仔细观察，我发现了各组关联之间大同小异，仅仅在关联时的“编辑公式”中有一点小小的差别：“一各班”与“一”之间的关联编辑栏里是“=一！××”，“二各班”与“二” 之间的关联编辑栏里是“=二！××”……“九各班”与“九”之间的关联编辑栏里是“=九！××”。 于是，我就想到了使用“替换”功能，具体做法是：首先设置好“一各班”与“一”之间的关联，然后复制表“一各班”为“一各班（2）”、“一各班（3）”……并分别将其更名为“二各班”、“三各班”……接着启用“替换”操作，在“二各班”中将“一！”替换为“二！”，在“三各班”中将“一！”替换为“三！”……以此类推，即可将所有“班课程表”及其关联快速制作完成。特别说明，在“替换”操作中，键入的“！”号要用英文输入状态下的“！”号。 巧妙处理Excel工作表表头 当新建或打开一个已有的Excel工作表时，都不可避免地会遇到表格表头的创建或修改，并且在表格编辑过程中和表格输出时也涉及表头的处理问题，我们可以利用一些技巧来更好地处理Excel表头。     1． 让表头自动填写当前月份和日期     比如表格标题为“某公司6月份电费统计表”（如图），其中“6”为进行电费统计的当月月份，第二行为制表日期。假如该单位电费每月公布一次，那么每月打开此表修改编辑时，都要对标题中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时间的改动，会出现表头时间与表内数据的矛盾。我们采取一个一劳永逸的措施，随时打开此工作表，让表头自动填写当前月份和日期。这要借助Excel三个函数来实现，在标题单元格输入“="实验中学"&& MONTH(TODAY())&&"月份电费统计表"”，函数“MONTH(TODAY())”表示当前月份，在第二行制表时间单元格输入“=TODAY()”。函数“TODAY()”表示当前日期，就出现图中结果。     完成上述设置，任何时候打开该表，我们大可放心地去做表内数据的处理工作了，不必担心表头时间会出现什么差错，无需对表头做任何改动。     2． 锁定表头     当我们编辑过长或过宽的Excel工作表时，需要向下或向上滚动屏幕，而表头也相应滚动，不能在屏幕上显示，这样我们搞不清要编辑的数据对应于表头的那一个信息。按下列方法可将表头锁定，使表头始终位于屏幕可视区域。     首先选定要锁定的表头，如果我们要将图中表头（1~3行）锁定，那么单击A4单元格，然后单击“窗口”菜单中的“冻结拆分窗口”命令，即可完成表头的冻结。假如还需锁定表格左侧第一列，那么单击第一列和第三行交叉处的右下方单元格B4，以下操作步骤同上。     如果要取消表头锁定，则单击“窗口”菜单中的“撤消窗口冻结”命令。     3． 为表格多页输出自动加表头     如果我们要输出一个工作表，而此表需要输出多页，如何让第一页以后的每一页都自动加上与第一页相同的表头呢？可以这么做：单击“文件”菜单下的“页面设置”命令，弹出“页面设置”对话框；单击“工作表”选项卡，在“打印标题”下“顶端标题行”右边文本框中单击鼠标左键，出现闪烁光标后，在Excel表用鼠标左键选择表头所在行，最后选择“确定”即可。 巧用Excel的“自动筛选”功能 如图1所示的工作表（假设D列已填入数据）若要上报或打印，还必须对F列和G列做隐藏处理（若直接删除，填充到D列的数据将全部丢失）。我在实际工作中，摸索出了一种方法，即利用Excel“自动筛选”功能快速实现部门名称的录入，下面仍以“学校职工情况表”为例，说明其操作步骤。     1. 按图1格式输入表格基本数据（F列、G列不需要录入）。 图一     2. 单击“数据”菜单，选中“自动筛选”，则在每个字段右侧出现一个下箭头的按钮，单击“部门代码”右侧的按钮，打开一个下拉列表如图2所示。 图二     3. 单击下拉列表中的任意一个“部门代号”，例：单击“1”，则“部门代码”为“1”的所有记录全部显示在屏幕上（如图3）。 图三     4. 在图3中选中“所在部门”列的单元格区域，输入“教务科”，按下“Ctrl”键不松开，再按回车键，则“部门代码”为“1”的记录的“所在部门”列都填充上了“教务科”（如图4）。 图四    5. 重复步骤3和步骤4可完成所有部门的填写操作。 用Excel实现机房网络化的考试 早就想设计一个在线的网络考试系统，但是笔者的编程技术有限，暂时还无法完成一个正规的考试系统。后来经过摸索，笔者使用Excel完成了一个简单的考试系统，经过几次考试的实验与改进，完全可以在平时的教学中运用。 首先在教师机上建立一个test文件夹，并建立两个电子表格文件，分别命名为“test.xls”和“汇总.xls”，其中“test.xls”为试卷文件，“汇总.xls”是用来汇总的文件。同时在该文件夹内建立58个文件夹（假设58个学生机），可以通过批处理的方法实现： md s1 md s2 …… md s58 然后再将test.xls文件分别复制到这58个文件夹中：我们可以首先向s1中复制一个test.xls文件，然后再执行copy.dat，copy.dat内容如下： copy s1\test.xls s2 copy s2\test.xls s3 …… copy s56\test.xls s57 copy s57\test.xls s58 现在每个文件夹中都有一个test.xls了，下面我们编辑test.xls和汇总.xls文件。 1． 试卷test．xls的设置 test.xls如图1所示（假设以20道选择题为例）。其中C1单元格为姓名填写处，E1单元格为学号填写处，A3～A22为答案填写区（20道题），选择题的选项用数字“1、2、3、4”，不要用字母，以防止学生输入字母与空格的混合。并把答题区的颜色设置为灰色，防止学生抄袭。 2． 汇总．xls的设置 如图2所示，建立两个表，第一个为答案表，用来收集试卷的第一列的答案和学号姓名。A2单元格为“='D:\test\s1\[test.xls]选择填空题'!$E$1”（统计学号），B2单元格为“=D:\test\s1\[test.xls]选择填空题'!$C$1”（统计姓名），C2:V2分别为“='D:\test\s1\[test.xls]选择填空题'!$A$3”（统计答案）、“='D:\test\s1\[test.xls]选择填空题'!$A$4”……“='D:\test\s1\[test.xls]选择填空题'!$A$22”。然后选中A2:V2，将鼠标移动到右下角，变成黑色十字的时候，向下拖动到第59行（假设有58台学生机），此时第3～59行与第2行是相同的，都是“='D:\test\test\s1\[test.xls]……”，这里需要改一下，第4行改为s2、第5行该为s3，依次类推。为了更快捷，也可以使用“编辑”菜单中的“替换”命令，将每行中的“s1”替换掉。然后再设计分数表，用来判断对错并统计分数，如图2所示。c2中输入“=IF(答案表!C2=C$61,$C$62,"0")”，将标准答案写在从c61单元格开始的第61行，该公式表示：如果c2等于标准答案c61单元格的数值，则显示c62指定的分数，否则为0分。然后选中该单元格，鼠标移动到右下角横向拖动20个格，然后再选中第一行纵向拖动58个格。在C62中输入“2”，表示每道题2分，如图3所示。如果正确给2分，如果错误给0分。然后分别横向求和，就可以得到每个学生的分数。 3．学生机端设置 首先在学生机上建立一个隐藏共享文件夹，笔者所在机房中的d盘都是隐藏共享的。教师机上再共享一个“test$”共享文件夹，教师把考试文件复制到其中，让学生访问教师机上的“领取试卷”共享文件夹，运行其中的“领取试卷.dat”，内容为“copy \\teacher\test$\test.xls d:\”。 这样试卷就复制到学生机的桌面上了，学生在指定的地方填好答案后单击[保存]按钮即可，考试结束时，教师运行收试卷程序： copy \\s1\d$\ test.xls D:\test\s1 copy \\s2\d$\test.xls D:\test\s2 …… copy \\s58\d$\test.xls D:\test\s58 并把统计.xls复制到d:\test文件夹中，打开统计.xls，成绩就一目了然了。 图1 test.xls文件 图2 汇总.xls文件 图3 分数设置 用Excel实现定时提醒 如果您从事设备管理工作，有近千台机械设备需要定期进行精度检测，那么，就得每天翻阅“设备鉴定 台账 单位消防安全管理台账纪检监察工作台账设备维修台账加油站进油台账物业收费台账表 ”来寻找“到期”的设备——实在是太麻烦了！用Excel建立一本“设备鉴定台账”是不是方便得多？方法是：用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。 首先，运行Excel，将“工作簿”的名称命名为“设备鉴定台账”，输入各设备的详细信息、上次鉴定日期及到期日期（日期的输入格式应为“年-月-日”，如：2003-10-21，如图1）。 图1 然后，选中图1所示“提示栏”下的F2单元格，点击插入菜单下的函数命令，在“插入函数”对话框中选择“逻辑”函数类中的IF函数，点击【确定】按钮，就会弹出“函数参数”对话框，分别在Logical_test行中输入E2=TODAY()、value_if_true行中输入“到期”、Value_if_false行中输入“" "”(如图2)，并点击【确定】按钮。这里需要说明的是：输入的 "" 是英文输入状态下的双引号，是Excel定义显示值为字符串时的标识符号，即IF函数在执行完真假判断后显示此双引号中的内容。为了醒目，可在“单元格属性”中将F2单元格的字体颜色设置为红色。 图2 最后，拖动“填充柄”，填充F列以下单元格即可。我们知道Excel的IF函数是一个“条件函数”，它的语法是“IF(logical_test,value_if_true,value_if_false)”，具体地说就是：如果第一个参数logical_test返回的结果为真，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果；Excel的TODAY函数［语法是TODAY()］是返回当前系统日期的函数。 实际上，本文所应用的IF函数语句为IF(E2=TODAY(),"到期","")，解释为：如果E2单元格中的日期正好是TODAY函数返回的日期，则在F2单元格中显示“到期”，否则就不显示，TODAY函数返回的日期则正好是系统当天的日期。 Excel妙用----公式结果我都要 Excel 最重要的应用就是利用公式进行计算。无论输入是纯粹的数字运算，还是引用其他单元格计算，只要在一个单元格中输入公式，就能得到结果。这个直接显示结果的设计对于绝大多数场合来说都是适用的，但某些情况下就不那么让人满意了。比如说在做工程施工的预结算编写，使用Excel，既要写出工程量的计算式，也要看到它的结果，于是这样相同的公式在Excel里面要填两次，一次在文本格式的单元格中输入公式，一次是在数据格式的单元格中输入公式让Excel计算结果。 如何既能看到公式又能看到结果呢？这个问题笔者认为可以从两个方面考虑：一种方法是所谓“已知结果，显示公式”，先在数据格式单元格中输入公式让Excel计算结果，然后在相邻的单元格中看到公式；另一种方法所谓“已知公式，显示结果”，就是先在一个文本格式的单元格中输入公式，在相邻的单元格中看到结果。 已知结果，显示公式 假设C列为通过公式计算得到的结果（假设C1为“=A1+B1”，或者直接是数字运算“=2+3”），而相邻的D列是你需要显示公式的地方（即D1应该显示为“=A1+B1”或者“=2+3”）。 1. 打开“工具”菜单选择“选项”命令，出现“选项”对话框。 2. 在“常规”选项卡中，选中“R1C1引用方式”选项。 3. 定义名称，将“引用位置”由“=GET.CELL(6,Sheet1!RC[-1])”即可。这里的RC[-1]含义是如果在当前单元格的同行前一列单元格中有公式结果，则在当前单元格中得到公式内容，即在含公式结果单元格的同行后一列单元格显示公式内容；如果将RC[-1]改为RC[1]，则在公式结果的同行前一列单元格显示公式内容。 4. 如果“引用位置”中含有“RC[-1]”，则在含公式结果单元格的同行后一列单元格中输入“=FormulaofResult”即可得到公式；如果“引用位置”中含有“RC[1]”，则在含公式结果单元格的同行前一列单元格中输入“=FormulaofResult”即可得到公式。 提示：如果想要在含公式结果单元格的同行后数第2列中显示公式内容，则需要把“引用位置”中的“RC[－1]”改为“RC[－2]”。 已知公式，显示结果 假设C列为输入的没有等号公式（假设C1为“A1+B1”），而相邻的D列是你需要存放公式计算结果的地方（即D1显示A1和B1单元格相加的结果）。 1. 选中D1，然后打开“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。 2. 在“在当前工作表中的名称”输入栏中输入定义的名称“ResultofFomula”，在下方的“引用位置”编辑栏中输入“=EVALUATE(Sheet1!C1)”，单击[确认]按钮退出。 3. 在 D1中输入“=ResultofFomula”，然后选中按住右下角的填充柄向下拉动填充即可。 提示：EVALUATE是Eexcel 4．0版的宏表函数，Excel 2000和Excel 2002中还支持，但只可用于名称定义中。 4. 填充后要按[F9]进行重算，如果C列的公式有改动，也需要及时按[F9]进行重算。 避开Excel的误差 微软的Excel软件在财务应用领域使用得非常广泛、被许多单位用来处理财务数据。笔者就是用Excel来为单位做工资表的，不过笔者在使用过程中发现，当用Excel进行复杂算术运算时，其计算出来的结果有时会与电子计算器的结果有些微小的差别，特别是在进行多项除法运算而留有余数的情况下，算术“误差”很容易产生，究其原因，就是Excel在除不尽的情况下，不会自动对数值进行四舍五入的操作。这在要求严格的财务报表上当然是不允许的，避开这种“误差”的途径有两种： 1． 利用Round函数对中间值四舍五入 Excel中Round函数的格式为：Round(数值，保留的小数位数)，它的作用就是对指定数值进行四舍五入的操作并保留指定小数位数。比如Round(8.8888,2)的意思就是对8.8888的千分位进行四舍五入、结果就是8.89，当然为工作方便，此函数括号内的数值也可以是单元格地址或别的算术公式，每个中间值经过这样处理后得到的最终运算结果就和计算器算出来的一致了。 2． 正确设置Excel选项来避开“误差” 上述函数方法虽然方便，不过遇上大型Excel表格时，如果每个单元格都要设置这个函数，工作量未免太巨大，这时我们就可以进入Excel的[工具]→[菜单]→[选项]→[重新计算]，在“工作簿选项”处勾选“以显示精度为准”，上述Excel“误差”也可有效避免，而默认情况下这个小选项是没有被勾选的。 Excel快速录入小数技巧 在工作中笔者要经常录入大批保留三位小数的数据表，数据范围为0.001～100.000，由于大部分数据集中0.001～0.010之间，这样输入一个数据就需要击键5次，录入速度比较慢。能不能提高输入速度呢？经过研究，笔者发现通过对数据格式进行重新定义可大大提高录入效率，如输入“0.001”只需输入“1”即可，下面是具体的实现方法和步骤：     方法一：自动设置小数点 1. 选定需要输入数据的单元格； 2. 在“工具”菜单上，单击“选项”，再单击“编辑”选项卡； 3. 选中“自动设置小数点”复选框； 4. 在“位数”框中，输入小数位数，本例中输入“3”； 5. 单击“确定”按钮，开始输入数据。     编辑提示：“位数”框中可输入正数，也可以输入负数。例如，如果在“位数”框中输入“3”，然后在单元格中键入“1”，则其值为“0．001”。如果在“位数”框中输入“－3”，然后在单元格中键入“1”，则其值为 “1000”；在选择“自动设置小数点”选项之前输入的数字不受小数位数的影响。      方法二：自定义数据格式 1. 选定需要输入数据的单元格； 2. 在“格式”菜单上，单击“单元格”，选中“数字”选项卡； 3. 在“分类”下拉框中选中“自定义”； 4. 单击“类型”输入框，输入新的格式类型“0.000,”，注意逗号“,”为半角英文字符，而非中文全角标点； 5. 单击“确定”按钮，开始输入数据。     编辑提示：可以通过自定义“类型”来定义数据小数位数，在数字格式中包含逗号，可使逗号显示为千位分隔符，或将数字缩小一千倍。     不难看出，使用以上两种方法虽然可以实现同样的功能，但仍存在一定的区别：使用方法一更改的设置将对数据表中的所有单元格有效，方法二则只对选中单元格有效，使用方法二可以针对不同单元格的数据类型设置不同的数据格式。使用时，用户可根据自身需要选择不同的方法. 提高EXCEL录入速度十大绝招 因工作的需要，我常常用EXCEL处理大量的表格文档，感到一个最头痛的问题就是文本的录入，由于本人的录入速度不高，使我不得不常常加班加点地干，才能勉强完成老板交给的任务。经过多年来的不断摸索，我发现了一些能提高EXCEL输入速度的小招式，现归纳出来，希望大家能喜欢。 　　第一招--内嵌序列法。如果你经常需要输入一些有规律的序列文本，如数字（1、2……）、日期（1日、2日……）等，可以利用EXCEL内嵌的序列来实现其快速输入： 　　先在需要输入序列文本的第1、第2两个单元格中输入该文本的前两个元素（如"甲、乙"）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时（我们通常称其为"填充柄"），按住鼠标左键向后（或向下）拖拉至需要填入该序列的最后一个单元格后，松开左键，则该序列的后续元素（如"丙、丁、戊……"）依序自动填入相应的单元格中。 　　第二招--右键拖拉法。有时需要输入一些不是成自然递增的数值（如等比序列：2、4、8……），我们可以用右键拖拉的方法来完成： 　　先在第1、第2两个单元格中输入该序列的前两个数值（2、4）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时，按住右键向后（或向下）拖拉至该序列的最后一个单元格，松开右键，此时会弹出一个菜单（如图 1），选"等比序列"选项，则该序列（2、4、8、16……）及其"单元格格式"分别输入相应的单元格中（如果选"等差序列"，则输入2、4、6、8……）。 第三招--定义序列法。单位里职工的姓名是经常需要输入的，有的职工姓名中含有生僻的字输入极为困难，如果我们一次性定义好"职工姓名序列"，以后输入就快多了： 　　将职工姓名输入连续的单元格中，并选中他们，用"工具→选项"命令打开"选项"对话框（如图 2），选"自定义序列"标签，先后按"导入"、"确定"按钮。以后在任一单元格中输入某一职工姓名（不一定非得是第1位职工的姓名），用"填充柄"即可将该职工后面的职工姓名快速填入后续的单元格中。      第四招--自动更正法。因工作的需要，我常常需要将一些国家标准的名称（如"《建筑安装工程质量检验评定统一标准》[GBJ300-88]"）输入单元格中，对这些文本的输入要求即准确又必须统一。我利用"自动更正"功能解决了这一难题： 　　用"工具→自动更正"命令，打开"自动更正"对话框（如图 3），在"替换"下面的方框中填入"G300"，在"替换为"下面的方框中填入"《建筑安装工程质量检验评定统一标准》[GBJ300-88]"，然后按确定按钮。以后在单元格中输"G300"（注意：G一定要大写！）确定后，系统会自动将其改正为"《建筑安装工程质量检验评定统一标准》[GBJ300-88]"，既快速又准确、统一。 　　特别需要指出的是：如果表格中需要文本"G300"时，你可以先输入"G3000"及后面的文本，然后再将"1"删除即可。 　　你可以依照上述方法将有关文本一次定义好，方便以后使用。特别指出的是：在这里定义好的自动更正词条在OFFICE系列其他应用程序（如WORD）中同样可以使用。 　　第五招--函数合并法。我经常需要输入一些施工企业的名称（如"马鞍山市第九建筑安装工程有限责任公司"等），这些文本大同小异，如果采取上?"自动更正"法，多了以后自己可能也记不清其代码了（你可以打一外一览表，摆在电脑前供查找），这时我们可以用EXCEL的一个函数来实现类似这些文本的快速输入： 　　假定上述文本需要输入某一工作薄的Sheet1工作表的D列中（如D2单元格），我们先在Sheet2工作表中的两个单元格（如A1和B1）中输入文本"马鞍山市"和"建筑安装工程有限责任公司"，然后在Sheet1有D2单元格中输入公式：=CONCATENATE(Sheet2!$A$1,C2,Sheet2!$B$1)，以后我们只要在C2单元格中输入"第九"，则D2单元格中将快速输入"马鞍山市第九建筑安装工程有限责任公司"文本。 　　对于这一招有三点值得说明：①我们可以用填充柄将上述公式复制到B列的其他单元格中，以后只要在C列相应的单元格中输入某企业名称的关键词（如"第九"），则该企业的全称将填入到D列相应的单元格中。②为了便于公式的复制，我们在公式中对单元格的引用采取了"绝对引用"（如Sheet2A1和Sheet2B1，这部分单元格不随公式的复制而发生变化，加上"$"符号即表示绝对引用）和"相对引用"（如C2单元格，我们没有加"$"符号，该单元格会随着公式的复制而自动作相应的调整）。③这样做工作表中就会多出一列（C列），我们在打印时不希望将它打印出来，我们选中该列后右击鼠标，在随后弹出的菜单中选"隐藏"项，即可将该列隐藏起来而不被打印出来。　      第六招--一次替换法。有时候我们在一张工作表中要多次输入同一个文本，特别是要多次输入一些特殊符号（如※），非常麻烦，对录入速度有较大的影响。这时我们可以用一次性替换的方法来克服这一缺陷： 　　先在需要输入这些符号的单元格中输入一个代替的字母（如X。注意：不能是表格中需要的字母），等表格制作完成后，用"编辑替换"命令（或按Ctrl+H键），打开"替换"对话框（如图 4），在"查找内容"下面的方框中输入代替的字母"X"，在"替换值"下面的方框中输入"※"，将"单元格匹配"前面的"∨"号去掉（否则会无法替换），然后按"替换"按钮一个一个去替换（如果表格中确实需要字母x时，这样做就可达满足这要求。注意：此时鼠标最好选定在需要替换的单元格前面的单元格中，最好选定A1单元格。），也可以按"全部替换"按钮，一次性全部替换完毕（这样做表格中所有的x都被替换掉，且鼠标可以选定在任何单元格中）。 第七招--快速复制法。有时后面需要输入的文本前面已经输入过了，可以采取快速复制（不是通常的Ctrl+C、Ctrl+X、Ctrl+V）的方法来完成输入： 　　①如果需要在垂直方向的一些连续的单元格中输入同一文本（如"砖混结构"），我们先在第1个单元格中输入该文本，然后用"填充柄"将期复制到后续的单元格中。 　　②如果需要输入的文本前面在同一列中前面已经输入过，当你输入该文本前面几个字符时，系统会提示你，你只要直接按下"Enter"键就可以把后续文本输入。 　　③如果需要输入的文本和上一个单元格的文本相同，可以直接按下Ctrl+'键就可以完成输入。 ④如果不相邻的多个单元格需要输入同样的文本，我们可以在按住Ctrl键的同时，用鼠标点击需要输入同样文本的所有单元格，然后输入该文本，再按下"Ctrl+Enter"键即可。 　　第八招--定义格式法。有时我们需要给输入的数值加上单位（如"平方米"等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。我们用自定义"单元格格式的方法来实现单位的自动输入： 　　我们先将数值输入相应的单元格中（注意：仅限于数值！），然后在按住Ctrl键的同时，选取所在需要加同一单位的单元格，用"格式→单元格"命令，打开"单元格格式"对话框（如图 5），在"数字"标签中，选中"分类"下面的"自定义"选项，再在"类型"下面的方框中输入"#"平"方"米"，按下确定键后，单位（平方米）即一次性加到相应数值的后面。   第九招--定义词组法。对于一些经常需要输入的文本（如"建筑工程质量监督"），我认为，采取利用输入法的"手工造词功能来实现比较好（此处以"五笔输入法"为例）： 　　启动"五笔输入法"，用鼠标右击状态条，选"手工造词"选项，打开"手工造词"对话框，在"词语"后面的方框中输入词组（如"建筑工程质量监督"，最多20个汉字， 夹杂一些符号也可以），在"外码"后面的方框中输入"编码"（最好采用系统自动生成的外码，他符号该输入法的编码规则，便于记忆和使用），然后先后按"添加"和"关闭"按钮。以后可以象输入其他词组一样输入你自定义的词组。 　　第十招--零找碎敲法。下面这些方法与输入看起来没有直接的关系，但简化了对EXCEL的设置过程，自然也就提高了录入速度： 　　①大家知道，如果向EXCEL中输入位数比较多的数值（如身份证号码），则系统会将其转为科学记数的格式，为与我们的输入原意不相符，解决的方法是将该单元格中的数值设置成"文本"格式。如果用命令的方法直接去设置，自然很慢。其实我们在输入这些数值时，只要在数值的前面加上一个小"'"就可了（注意：该"'"必须是在英文状态下输入）。 　　②如果某个单元格中输入的文本一行容不下，我们在按住"Alt"键的同时，按下"Enter"键就可以快速强行让其转行。 Excel中的选择性粘贴 　Excel为我们提供了一些自动功能。 先来看看选择性粘贴： 这里的选择性粘贴是指把剪贴板中的内容按照一定的规则粘贴到工作表中，而不是像前面那样简单地拷贝。就拿这个表来说吧（如图1）。 图1 　　这里的“利润”一栏是我们使用公式计算得到的，选择这一栏，复制到Sheet2中，可以看到数值并没有跟着复制过来；这时就可以使用选择性粘贴了：打开“编辑”菜单，单击“选择性粘贴”命令，打开“选择性粘贴”对话框（如图2），在“粘贴”一栏中选择“数值”，单击“确定”按钮，数值就可以粘贴过来了。 这种情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，到时可要注意。 图2 　　选择性粘贴还有一个很常用的功能就是转置功能。简单地理解就是把一个横排的表变成竖排的或把一个竖排的表变成横排的：选择这个表格，复制一下，切换到另一个工作表中，打开“选择性粘贴”对话框，选中“转置”前的复选框，单击“确定”按钮，可以看到行和列的位置相互转换了过来。 　　另外一些简单的计算也可以用选择性粘贴来完成：选中这些单元格，复制一下，然后打开“选择性粘贴”对话框，在“运算”一栏选择“加”，单击“确定”按钮，单元格的数值就是原来的两倍了。 此外你还可以粘贴全部格式或部分格式，或只粘贴公式等等。 Execl的编辑技巧 　　（1） 分数的输入 　　如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。 　　（2） 序列“001”的输入 　　如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“'”（西文单引号），然后输入“001”。 　　（3） 日期的输入　 　　如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。 　　（4） 填充条纹 　 　　如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮（如图1）。 图１ 　　（5） 多张工作表中输入相同的内容 　　几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。 　　（6） 不连续单元格填充同一数据 　　选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。 　　（7） 在单元格中显示公式 　　如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮（如图2）。这时每个单元格中的公式就显示出来了。如果想恢复公式计算结果的显示，就再设置“窗口选项”栏下的“