Excel高级教程(珍藏).doc

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中一样。 如果需要上报的“汇总表”中还需输入许多其他文字，如何在“汇总表”工作表中插入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格)则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”键与 分号键，然后回车即可。3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以右击该单元格，选定“设置单元格格式数字日期”或“时间”，然后在类型框中选择即可。（出处：赛迪网-中国电脑教育报）利用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个班级，则总公式为“IFMODI32*nnn-MODI3n1IFMODI32*n01MODI32*n”。3、打印名单分好班级后，要把各班级的学生名单打印出来，一个班最好用一张标准纸，可用分栏打印解决班级人数多的问题。而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 2000的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。非常便利。在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所示，要求填入相应部门代号后的所在部门名称。图11. 选取需要填入所在部门的范围F4:F118。2. 单击格式工具栏上的插入函数按钮，接着在“粘贴函数”对话框中的“函数分类”栏选择“查找与引用”，“函数名”选择“LOOKUP”，单击确定按钮（如图2）。图23. 在弹出的“选定参数”对话框中选择参数组合方式，单击确定按钮。4. 分别在查找范围中输入E4:E118，在单列范围中输入H4:H8，在输出结果中输入I4:I8（如图3）。最后按下Ctrl+Shift+Enter键，单击确定按钮即可。图3这样，眨眼间对应教职员工所在部门的名称就填好了，看看最后的结果，是不是感觉很方便！附表为函数LOOKUP的结构说明EXCEL中替换的妙用我试着制作本校的课程表。在制作过程中，我发现了“替换”功能在Excel中的一则妙用，一起来看看吧。我校属于初中、小学合并的一所实验性的学校，全校从小学一年级到初中三年级统一称为一年级、二年级九年级，每年级有5个班。因此，在设计课程表时，“年级课程表”我就建立了九个，“班级课程表”相应的也是九个（每个5页），如图所示。按照原来的做法，我开始设置课程表之间的关联。首先将“一各班”与“一”（即一年级）之间设置好关联，接着再去设置“二各班”与“二”之间的关联。当设置到此处的时候，我觉得如果继续这样设置到“九各班”与“九”之间的关联，会浪费很多时间，心里就想到去寻找“捷径”。仔细观察，我发现了各组关联之间大同小异，仅仅在关联时的“编辑公式”中有一点小小的差别：“一各班”与“一”之间的关联编辑栏里是“=一！”，“二各班”与“二” 之间的关联编辑栏里是“=二！”“九各班”与“九”之间的关联编辑栏里是“=九！”。于是，我就想到了使用“替换”功能，具体做法是：首先设置好“一各班”与“一”之间的关联，然后复制表“一各班”为“一各班（2）”、“一各班（3）”并分别将其更名为“二各班”、“三各班”接着启用“替换”操作，在“二各班”中将“一！”替换为“二！”，在“三各班”中将“一！”替换为“三！”以此类推，即可将所有“班课程表”及其关联快速制作完成。特别说明，在“替换”操作中，键入的“！”号要用英文输入状态下的“！”号。巧妙处理Excel工作表表头当新建或打开一个已有的Excel工作表时，都不可避免地会遇到表格表头的创建或修改，并且在表格编辑过程中和表格输出时也涉及表头的处理问题，我们可以利用一些技巧来更好地处理Excel表头。1 让表头自动填写当前月份和日期比如表格标题为“某公司6月份电费统计表”（如图），其中“6”为进行电费统计的当月月份，第二行为制表日期。假如该单位电费每月公布一次，那么每月打开此表修改编辑时，都要对标题中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时间的改动，会出现表头时间与表内数据的矛盾。我们采取一个一劳永逸的措施，随时打开此工作表，让表头自动填写当前月份和日期。这要借助Excel三个函数来实现，在标题单元格输入“=实验中学& MONTH(TODAY()&月份电费统计表”，函数“MONTH(TODAY()”表示当前月份，在第二行制表时间单元格输入“=TODAY()”。函数“TODAY()”表示当前日期，就出现图中结果。完成上述设置，任何时候打开该表，我们大可放心地去做表内数据的处理工作了，不必担心表头时间会出现什么差错，无需对表头做任何改动。2 锁定表头当我们编辑过长或过宽的Excel工作表时，需要向下或向上滚动屏幕，而表头也相应滚动，不能在屏幕上显示，这样我们搞不清要编辑的数据对应于表头的那一个信息。按下列方法可将表头锁定，使表头始终位于屏幕可视区域。首先选定要锁定的表头，如果我们要将图中表头（13行）锁定，那么单击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 s1md s2md s58然后再将test.xls文件分别复制到这58个文件夹中：我们可以首先向s1中复制一个test.xls文件，然后再执行copy.dat，copy.dat内容如下：copy s1test.xls s2copy s2test.xls s3copy s56test.xls s57copy s57test.xls s58现在每个文件夹中都有一个test.xls了，下面我们编辑test.xls和汇总.xls文件。1 试卷testxls的设置test.xls如图1所示（假设以20道选择题为例）。其中C1单元格为姓名填写处，E1单元格为学号填写处，A3A22为答案填写区（20道题），选择题的选项用数字“1、2、3、4”，不要用字母，以防止学生输入字母与空格的混合。并把答题区的颜色设置为灰色，防止学生抄袭。2 汇总xls的设置如图2所示，建立两个表，第一个为答案表，用来收集试卷的第一列的答案和学号姓名。A2单元格为“=D:tests1test.xls选择填空题!$E$1”（统计学号），B2单元格为“=D:tests1test.xls选择填空题!$C$1”（统计姓名），C2:V2分别为“=D:tests1test.xls选择填空题!$A$3”（统计答案）、“=D:tests1test.xls选择填空题!$A$4”“=D:tests1test.xls选择填空题!$A$22”。然后选中A2:V2，将鼠标移动到右下角，变成黑色十字的时候，向下拖动到第59行（假设有58台学生机），此时第359行与第2行是相同的，都是“=D:testtests1test.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分。然后分别横向求和，就可以得到每个学生的分数。学生机端设置首先在学生机上建立一个隐藏共享文件夹，笔者所在机房中的d盘都是隐藏共享的。教师机上再共享一个“test$”共享文件夹，教师把考试文件复制到其中，让学生访问教师机上的“领取试卷”共享文件夹，运行其中的“领取试卷.dat”，内容为“copy teachertest$test.xls d:”。这样试卷就复制到学生机的桌面上了，学生在指定的地方填好答案后单击保存按钮即可，考试结束时，教师运行收试卷程序： copy s1d$ test.xls D:tests1copy s2d$test.xls D:tests2copy s58d$test.xls D:tests58并把统计.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计算结果。如何既能看到公式又能看到结果呢？这个问题笔者认为可以从两个方面考虑：一种方法是所谓“已知结果，显示公式”，先在数据格式单元格中输入公式让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改为RC1，则在公式结果的同行前一列单元格显示公式内容。4. 如果“引用位置”中含有“RC-1”，则在含公式结果单元格的同行后一列单元格中输入“=FormulaofResult”即可得到公式；如果“引用位置”中含有“RC1”，则在含公式结果单元格的同行前一列单元格中输入“=FormulaofResult”即可得到公式。提示：如果想要在含公式结果单元格的同行后数第2列中显示公式内容，则需要把“引用位置”中的“RC1”改为“RC2”。已知公式，显示结果假设C列为输入的没有等号公式（假设C1为“A1+B1”），而相邻的D列是你需要存放公式计算结果的地方（即D1显示A1和B1单元格相加的结果）。1. 选中D1，然后打开“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义名称”对话框。2. 在“在当前工作表中的名称”输入栏中输入定义的名称“ResultofFomula”，在下方的“引用位置”编辑栏中输入“=EVALUATE(Sheet1!C1)”，单击确认按钮退出。3. 在 D1中输入“=ResultofFomula”，然后选中按住右下角的填充柄向下拉动填充即可。提示：EVALUATE是Eexcel 40版的宏表函数，Excel 2000和Excel 2002中还支持，但只可用于名称定义中。4. 填充后要按F9进行重算，如果C列的公式有改动，也需要及时按F9进行重算。巧施妙计，就能让公式和结果在Excel中和平共处了，你也试试吧。避开Excel的误差微软的Excel软件在财务应用领域使用得非常广泛、被许多单位用来处理财务数据。笔者就是用Excel来为单位做工资表的，不过笔者在使用过程中发现，当用Excel进行复杂算术运算时，其计算出来的结果有时会与电子计算器的结果有些微小的差别，特别是在进行多项除法运算而留有余数的情况下，算术“误差”很容易产生，究其原因，就是Excel在除不尽的情况下，不会自动对数值进行四舍五入的操作。这在要求严格的财务报表上当然是不允许的，避开这种“误差”的途径有两种：1 利用Round函数对中间值四舍五入Excel中Round函数的格式为：Round(数值，保留的小数位数)，它的作用就是对指定数值进行四舍五入的操作并保留指定小数位数。比如Round(8.8888,2)的意思就是对8.8888的千分位进行四舍五入、结果就是8.89，当然为工作方便，此函数括号内的数值也可以是单元格地址或别的算术公式，每个中间值经过这样处理后得到的最终运算结果就和计算器算出来的一致了。2 正确设置Excel选项来避开“误差”上述函数方法虽然方便，不过遇上大型Excel表格时，如果每个单元格都要设置这个函数，工作量未免太巨大，这时我们就可以进入Excel的工具菜单选项重新计算，在“工作簿选项”处勾选“以显示值为准”，上述Excel“误差”也可有效避免，而默认情况下这个小选项是没有被勾选的。用EXCEL建立家庭收支表无需家政管理方面的软件，利用EXCEL电子表格强大的数据处理功能，根据自己的实际情况设计具体项目，就能建立家庭收支情况动态分析表，可以很好地进行个人理财。下面以一个三口之家(父李明，母黄芳，女儿)为例介绍建立分析表的一般步骤与方法。一、建立收入账目表1.李明收入表双击EXCEL左下“Sheet1”，键入“李明收入”，按回车键。选A1，键入“年月”，回车，选A2，键入“Jan98”(1998年1月)，回车。选A2，将鼠标对准单元格的右下角，出现黑“”后，按住鼠标左键向下拖动至A13，在A14中键入“合计”。选B1、C1、D1、E1，依次输入“工资”、“奖金”、“其他收入”和“总收入”。选E2，键入“=B2C2D2”，回车。点E2，将鼠标对准单元格右下角，出现黑“”后，按住鼠标左键向下拖至E13，回车。选B14，键入“=B2B3B13”，回车。点B14，将鼠标对准单元格右下角，出现黑“”后，按住鼠标左键向右拖至E14，回车。2.黄芳收入表双击“Sheet2”，键入“黄芳收入”，回车。余下步骤同“李明收入表”。3.家庭总收入表双击“Sheet3”，键入“家庭总收入”，回车。选A1，键入“年月”，选A2，键入“Jan98”，照前法建立年月栏目。选A14，键入“合计”。选B1、C1、D1，依次输入“李明收入”、“黄芳收入”、“总收入”。在B2中键入“=李明收入!E2”，在C2中键入“=黄芳收入!E2”，在C2中键入“=黄芳收入!E2”，在D2中键入“=B2C2”，回车。分别照前法将鼠标下拖至B13、C13、D13，选B14，键入“=B2B3B13”，回车。照前法将鼠标向右拖至D14，回车。二、建立支出账目表1.日支出明细表。双击“Sheet4”，键入“每日支出”，回车。选A1，键入“日期”，回车。选A2，键入“19980101”，回车。选A2，将鼠标对准单元格的右下角，出现黑“”后，按住鼠标左键向下拖动至A366，(出现“19981231为止”)。选A33，单点击“插入”选“行”，然后在A33中键入“合计”。分别选A66、A94、A125、A157、A188、A220、A252、A283、A315、A346、A378照“A33”建各月支出合计项。选B1、C1、D1、E1、F1、G1、H1、I1、J1、K1、L1，依次键入“伙食费”、“水电费”、“电话费”、“文化娱乐”、“人情往来”、“家用物品”、“子女费用”、“李明支出”、“黄芳支出”、“其他支出”和“总支出”(具体项目可根据家庭实际情况定)。选B33，键入“=B2B3B32(从每月第一天所在单元格起一直加到最后一天所在单元格)”，回车。将鼠标对准B33单元格右下角，待出现黑“”后按住鼠标左键拖动至K33。分别选B66、B94、B125、B188、B220、B252、B283、B315、B346、B378，照“B33”所用方法键入并拖动鼠标。选L2，键入“=B2C2D2K2”，回车。选K2，将鼠标对准单元格的右下角，出现黑“”后，按住鼠标左键向下拖动至K378。这样一年中每一天的支出明细表就建好了。2.家庭总支出表双击“Sheet5”，键入“家庭总支出”，回车。在A1至A14中建立年月栏目和合计项，照方法4，选B1、C1、K1，依次建立各具体开支项目。分别选B2、B3、B13，键入“=每日开支!B33”、“=每日开支!B66”“=每日开支!B66”“=每日开支!B378”(日开支明细表中每个月合计项所在单元格)”并照前法分别将鼠标向右拖动至L2、L3、L13。选B14，键入“=B2B3B13”，回车，照前法将鼠标向右拖动至L14。三、建立收支总账目表双击“Sheet6”，键入“收支总表”，回车。照前法建立年月栏和合计项。分别选B1、C1、D1，键入“总收入”、“总开支”、“结余”。选B2、C2、D2，依次分别键入“=家庭总收入!D2”，“=家庭总支出!L2”，“=B2C2”，分别将鼠标对准B2、C2、D2单元格的右下角，出现黑“”后，按住鼠标左键向下拖动至B13、C13、D13。选B14，键入“=B2B3B13”，回车。照前法向右拖鼠标至D14。以上所有表格建好后，使用操作极为简便，在收入表和每日支出表单元格中输入相应数据，所需数据都将自动生成。同时可利用EXCEL的功能随时进行数据查询，筛选并绘制各种动态分析曲线和图表。及时掌握了解家庭收支变化情况。Excel快速录入小数技巧在工作中笔者要经常录入大批保留三位小数的数据表，数据范围为0.001100.000，由于大部分数据集中0.0010.010之间，这样输入一个数据就需要击键5次，录入速度比较慢。能不能提高输入速度呢？经过研究，笔者发现通过对数据格式进行重新定义可大大提高录入效率，如输入“0.001”只需输入“1”即可，下面是具体的实现方法和步骤： 方法一：自动设置小数点 1. 选定需要输入数据的单元格； 2. 在“工具”菜单上，单击“选项”，再单击“编辑”选项卡； 3. 选中“自动设置小数点”复选框； 4. 在“位数”框中，输入小数位数，本例中输入“3”； 5. 单击“确定”按钮，开始输入数据。 编辑提示：“位数”框中可输入正数，也可以输入负数。例如，如果在“位数”框中输入“3”，然后在单元格中键入“1”，则其值为“0001”。如果在“位数”框中输入“3”，然后在单元格中键入“1”，则其值为 “1000”；在选择“自动设置小数点”选项之前输入的数字不受小数位数的影响。 方法二：自定义数据格式 1. 选定需要输入数据的单元格； 2. 在“格式”菜单上，单击“单元格”，选中“数字”选项卡； 3. 在“分类”下拉框中选中“自定义”； 4. 单击“类型”输入框，输入新的格式类型“0.000,”，注意逗号“,”为半角英文字符，而非中文全角标点； 5. 单击“确定”按钮，开始输入数据。 编辑提示：可以通过自定义“类型”来定义数据小数位数，在数字格式中包含逗号，可使逗号显示为千位分隔符，或将数字缩小一千倍。如对于数字“1000”，定义为类型“”时将显示为“1000”，定义为“”时显示为“1”。 不难看出，使用以上两种方法虽然可以实现同样的功能，但仍存在一定的区别：使用方法一更改的设置将对数据表中的所有单元格有效，方法二则只对选中单元格有效，使用方法二可以针对不同单元格的数据类型设置不同的数据格式。使用时，用户可根据自身需要选择不同的方法. 巧用Excel建立数据库大法！日常工作中，我们常常需要建立一些有规律的数据库。例如我为了管理全乡的农业税，需建立一数据库，该数据库第一个字段名为村名，第二个字段名为组别。我乡共19个村，每个村717个组不等，共计258个组。这个数据库用数据库软件（哪怕是Visual FoxPro 6.0或是Access97等高档次的）很不好建立逐个儿输入吗，只有傻瓜才有这种想法。用Access宏或FoxPro编程来输入吧，这些数据似乎还嫌不够规则（每个村对应的组数不一