当前位置: 首页 > 校园网建设 > Excel高级教程

Excel高级教程

2013年10月06日 15:54:53 访问量:508

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年第23E9版刊登了“打印Excel中的单列表格”一文,介绍利用Excel的文件另存与Word的查找替换与排版实现Excel中的单列表格的打印方法。其实利用Excel自身的公式操作,也可轻松实现单列表格文本的合成打印。以横向排列,逐行显示的“学生姓名”列为例,具体操作方法如下:
  1. 先在当前表sheet1的适当位置选择一列作为数据合成列,如F列,并在其下第2个单元格F2中输入公式“=F1&" "&B2”,(公式表示在二个姓名之间空2B列为要打印的列)F2中产生第一个姓名,选择F2,利用向下拖动填充句柄的方法将F2中公式复制到以下单元格中,直到最后一行,则以下单元格的内容将会是当前行以前的所有行中的姓名合成。
  2. 选择sheet2表,在A1单元格中输入公式“=TRIM(Sheet1!F49)”,F49sheet1表中最后一人的姓名合成值,即将所有姓名组合复制到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”,然后回车即可得到325。又如输入“9:25”,回车即得到“09:25”

2、当前日期与时间的快速输入选定要插入的单元格,按下Ctrl键与分号键,然后回车即可插入当前日期。而要输入当前时间,同时按住Ctrl+Shift键与分号键,然后回车即可。

利用Excel的函数和筛选功能巧妙分班

1、处理思路

学校大多根据学习成绩进行分班,一般是按总分名次来划分的:比如要分8个班,则第1名分到一班、第2名分到二班……8名分到八班,接着第9名分到八班、第10名分到七班……16名分到一班。

2、划分班级

先将学生情况及成绩调入Excel工作表中,按总分降序排列(假设总分在H列,第一名在第三行),在总分列的右一列(I)I3单元格开始向下填充123……再在其右一列(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的公式制作出分栏打印效果。具体操作如下:先将某一班级名单按自己要求设置好,如页边距、表头、行高、字体等。在打印预览中,判断出每页的数据行数x(所谓数据行,指表头除外的记录行),回到普通视图下,在表的右边空列中,从第一数据行开始填充自然数序列123……假设此列在K列,第一数据行为第3行,则在L3单元格中填入公式“=mod(int((K3-1)/x),y)”(其中x为每页的数据总行数,y为分栏的栏数,就分班而言,两栏就够用了),并向下填充整个表。则该列出现了从0y-1的数,即给每行计算出了该行所在的栏号,复制表头到新的工作表或新建的工作薄中。在班级工作表中也用自动筛选功能分别筛选出第0栏、第1……y-1栏,并分别复制到新工件表中。
再对各新工作表进行相应的设置,如页边距、表头、行高、字体等,注意不要逐个工作表进行设置,按住“Ctrl”键,用鼠标单击窗口中需要设置相同内容的各个不同工作表标签,在其中一个工作表中修改设置,另外的工作表中就能自动修改。所有工作表都设置好后,分栏打印的各班名单就制作好了,用打印机打印出来便大功告成。
此方法都保留了原表,有利于修改,如要改变班级数、打印栏数、每页行数,只需改变nxy的值。另外,此方法用的是自动筛选功能,也可以用高级筛选来完成。
 

Excel单元格中设置“斜线”效果

表格中出现斜线是经常的事(如图1),以前遇到这种情况,要么把表格导入到WPS OfficeWord中处理,要么就是打印好了之后再用笔加上去。经过一番摸索,终于发现了在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函数计算出第一个客户的出生年月。函数表达方式如下:MIDE272),表示第一个客户的身份证号在E列第二行中,要从这个位置中的第7个文本始返回2个长度的字符。    回车确认后,“J2”中的值变为62”,表示该职工62年出生。接着下拉J2公式复制单元格,快速求出每个职工的出生年月(若身份证为18位,则公式变为MIDE292))再把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.datcopy.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试卷testxls的设置
test.xls如图1所示(假设以20道选择题为例)。其中C1单元格为姓名填写处,E1单元格为学号填写处,A3A22为答案填写区(20道题),选择题的选项用数字“1234”,不要用字母,以防止学生输入字母与空格的混合。并把答题区的颜色设置为灰色,防止学生抄袭。
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台学生机),此时第359行与第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建立一本设备鉴定台账是不是方便得多?方法是:用ExcelIF函数嵌套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列以下单元格即可。我们知道ExcelIF函数是一个条件函数,它的语法是“IF(logical_test,value_if_true,value_if_false)”,具体地说就是:如果第一个参数logical_test返回的结果为真,则执行第二个参数Value_if_true的结果,否则执行第三个参数Value_if_false的结果;ExcelTODAY函数[语法是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显示A1B1单元格相加的结果)。
1. 选中D1,然后打开插入菜单选择名称命令中的定义子命令,出现定义名称对话框。
2. 在“在当前工作表中的名称”输入栏中输入定义的名称“ResultofFomula”,在下方的引用位置编辑栏中输入“=EVALUATE(Sheet1!C1)”,单击[确认]按钮退出。
3. D1中输入“=ResultofFomula”,然后选中按住右下角的填充柄向下拉动填充即可。
提示:EVALUATEEexcel 40版的宏表函数,Excel 2000Excel 2002中还支持,但只可用于名称定义中。
4. 填充后要按[F9]进行重算,如果C列的公式有改动,也需要及时按[F9]进行重算。

 

避开Excel的误差

微软的Excel软件在财务应用领域使用得非常广泛、被许多单位用来处理财务数据。笔者就是用Excel来为单位做工资表的,不过笔者在使用过程中发现,当用Excel进行复杂算术运算时,其计算出来的结果有时会与电子计算器的结果有些微小的差别,特别是在进行多项除法运算而留有余数的情况下,算术误差很容易产生,究其原因,就是Excel在除不尽的情况下,不会自动对数值进行四舍五入的操作。这在要求严格的财务报表上当然是不允许的,避开这种误差的途径有两种:

1利用Round函数对中间值四舍五入
ExcelRound函数的格式为:Round(数值,保留的小数位数),它的作用就是对指定数值进行四舍五入的操作并保留指定小数位数。比如Round(8.8888,2)的意思就是对8.8888的千分位进行四舍五入、结果就是8.89,当然为工作方便,此函数括号内的数值也可以是单元格地址或别的算术公式,每个中间值经过这样处理后得到的最终运算结果就和计算器算出来的一致了。
2正确设置Excel选项来避开误差
上述函数方法虽然方便,不过遇上大型Excel表格时,如果每个单元格都要设置这个函数,工作量未免太巨大,这时我们就可以进入Excel[工具]→[菜单]→[选项]→[重新计算],在工作簿选项处勾选以显示精度为准,上述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. 单击“确定”按钮,开始输入数据。
    编辑提示:可以通过自定义“类型”来定义数据小数位数,在数字格式中包含逗号,可使逗号显示为千位分隔符,或将数字缩小一千倍。

    不难看出,使用以上两种方法虽然可以实现同样的功能,但仍存在一定的区别:使用方法一更改的设置将对数据表中的所有单元格有效,方法二则只对选中单元格有效,使用方法二可以针对不同单元格的数据类型设置不同的数据格式。使用时,用户可根据自身需要选择不同的方法.

 

 

提高EXCEL录入速度十大绝招

因工作的需要,我常常用EXCEL处理大量的表格文档,感到一个最头痛的问题就是文本的录入,由于本人的录入速度不高,使我不得不常常加班加点地干,才能勉强完成老板交给的任务。经过多年来的不断摸索,我发现了一些能提高EXCEL输入速度的小招式,现归纳出来,希望大家能喜欢。

  第一招--内嵌序列法。如果你经常需要输入一些有规律的序列文本,如数字(12……)、日期(1日、2……)等,可以利用EXCEL内嵌的序列来实现其快速输入:
  先在需要输入序列文本的第1、第2两个单元格中输入该文本的前两个元素(如"甲、乙")。同时选中上述两个单元格,将鼠标移至第2个单元格的右下角成细十字线状时(我们通常称其为"填充柄"),按住鼠标左键向后(或向下)拖拉至需要填入该序列的最后一个单元格后,松开左键,则该序列的后续元素(如"丙、丁、戊……")依序自动填入相应的单元格中。
  第二招--右键拖拉法。有时需要输入一些不是成自然递增的数值(如等比序列:248……),我们可以用右键拖拉的方法来完成:
  先在第1、第2两个单元格中输入该序列的前两个数值(24)。同时选中上述两个单元格,将鼠标移至第2个单元格的右下角成细十字线状时,按住右键向后(或向下)拖拉至该序列的最后一个单元格,松开右键,此时会弹出一个菜单(如图 1),选"等比序列"选项,则该序列(24816……)及其"单元格格式"分别输入相应的单元格中(如果选"等差序列",则输入2468……)。

第三招--定义序列法。单位里职工的姓名是经常需要输入的,有的职工姓名中含有生僻的字输入极为困难,如果我们一次性定义好"职工姓名序列",以后输入就快多了:
  将职工姓名输入连续的单元格中,并选中他们,用"工具选项"命令打开"选项"对话框(如图 2),选"自定义序列"标签,先后按"导入""确定"按钮。以后在任一单元格中输入某一职工姓名(不一定非得是第1位职工的姓名),用"填充柄"即可将该职工后面的职工姓名快速填入后续的单元格中。

     第四招--自动更正法。因工作的需要,我常常需要将一些国家标准的名称(如"《建筑安装工程质量检验评定统一标准》[GBJ300-88]")输入单元格中,对这些文本的输入要求即准确又必须统一。我利用"自动更正"功能解决了一难题:
  用"工具自动更正"命令,打开"自动更正"对话框(如图 3),在"替换"下面的方框中填入"G300",在"替换为"下面的方框中填入"《建筑安装工程质量检验评定统一标准》[GBJ300-88]",然后按确定按钮。以后在单元格中输"G300"(注意:G一定要大写!)确定后,系统会自动将改正为"《建筑安装工程质量检验评定统一标准》[GBJ300-88]",既快速又准确、统一。

  特别需要指出的是:如果表格中需要文本"G300"时,你可以先输入"G3000"及后面的文本,然后再将"1"删除即可。
  你可以依照上述方法将有关文本一定义好,方便以后使用。特别指出的是:在这里定义好的自动更正词条在OFFICE系列其他应用程序(如WORD)中同样可以使用。
  第五招--函数合并法。我经常需要输入一些施工企业的名称(如"马鞍山市第九建筑安装工程有限责任公司"等),这些文本大同小异,如果采取上?"自动更正"法,多了以后自己可能也记不清其代码了(你可以打一外一览表,摆在电脑前供查找),这时我们可以用EXCEL的一个函数来实现类似这些文本的快速输入:
  假定上述文本需要输入某一工作薄的Sheet1工作表的D列中(如D2单元格),我们先在Sheet2工作表中的两单元格(如A1B1)中输入文本"马鞍山市""建筑安装工程有限责任公司",然后在Sheet1D2单元格中输入公式:=CONCATENATE(Sheet2!$A$1,C2,Sheet2!$B$1),以后我们只要在C2单元格中输入"第九",则D2单元格中将快速输入"马鞍山市第九建筑安装工程有限责任公司"文本。
  对于这一招有三点值得说明:①我们可以用填充柄将上述公式复制到B列的其他单元格中,以后只要在C列相应的单元格中输入某企业名称的关键词(如"第九"),则该企业的全称将填入到D列相应的单元格中。为了便于公式的复制,我们公式中对单元格的引用采取了"绝对引用"(如Sheet2A1Sheet2B1,这部分单元格不随公式的复制而发生变化,加上"$"符号即表示绝对引用)和"相对引用"(如C2单元格,我们没有加"$"符号,该单元格会随着公式的复制而自动作相应的调整)。这样做工作表中就会多出一列(C列),我们在打印时不希望将它打印出来,我们选中该列后右击鼠标,在随后弹出的菜单中"隐藏"项,即可将该列隐藏起来而不被打印出来。 

     第六招--一次替换法。有时候我们在一张工作表中要多次输入同一个文本,特别是要多次输入一些特殊符号(如),非常麻烦,对录入速度有较大的影响。这时我们可以用一次性替换的方法来克服这一缺陷:
  先在需要输入这些符号的单元格中输入一个代替的字母(如X。注意:不能是表格中需要的字母),等表格制作完成后,用"编辑替换"命令(或按Ctrl+H键),打开"替换"对话框(如图 4),在"查找内容"下面的方框中输入代替的字母"X",在"替换值"下面的方框中输入"※",将"单元格匹配"前面的"∨"号去掉(否则会无法替换),然后按"替换"按钮一个一个去替换(如果表格中确实需要字母x时,这样做就可达满足这要求。注意:此时鼠标最好选定在需要替换的单元格前面的单元格中,最好选定A1单元格。),也可以按"全部替换"按钮,一次性全部替换完毕(这样做表格中所有的x都被替换掉,且鼠标可以选定在任何单元格中)。

第七招--快速复制法。有时后面需要输入的文本前面已经输入过了,可以采取快速复制(不是通常的Ctrl+CCtrl+XCtrl+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”,系统会将其变为“15日”,解决办法是:先输入“0”,然后输入空格,再输入分数“1/5”。

  (2) 序列“001”的输入

  如果直接输入“001”,系统会自动判断001为数据1,解决办法是:首先输入“'”(西文单引号),然后输入“001”。

  (3) 日期的输入 

  如果要输入“45日”,直接输入“4/5”,再敲回车就行了。如果要输入当前日期,按一下“Ctrl+;”键。

  (4) 填充条纹  

  如果想在工作簿中加入漂亮的横条纹,可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号,然后单击此单元格,向右拖动鼠标,选中横向若干单元格,单击“格式”菜单,选中“单元格”命令,在弹出的“单元格格式”菜单中,选择“对齐”选项卡,在水平对齐下拉列表中选择“填充”,单击“确定”按钮(如图1)。

单元格格式-对齐对话框

图1

  (5) 多张工作表中输入相同的内容

  几个工作表中同一位置填入同一数据时,可以选中一张工作表,然后按住Ctrl键,再单击窗口左下角的Sheet1Sheet2......来直接选择需要输入相同内容的多个工作表,接着在其中的任意一个工作表中输入这些相同的数据,此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后,再次按下键盘上的Ctrl键,然后使用鼠标左键单击所选择的多个工作表,解除这些工作表的联系,否则在一张表单中输入的数据会接着出现在选中的其它工作表内。

  (6) 不连续单元格填充同一数据

  选中一个单元格,按住Ctrl键,用鼠标单击其他单元格,就将这些单元格全部都选中了。在编辑区中输入数据,然后按住Ctrl键,同时敲一下回车,在所有选中的单元格中都出现了这一数据。

  (7) 在单元格中显示公式

  如果工作表中的数据多数是由公式生成的,想要快速知道每个单元格中的公式形式,以便编辑修改,可以这样做:用鼠标左键单击“工具”菜单,选取“选项”命令,出现“选项”对话框,单击“视图”选项卡,接着设置“窗口选项”栏下的“公式”项有效,单击“确定”按钮(如图2)。这时每个单元格中的公式就显示出来了。如果想恢复公式计算结果的显示,就再设置“窗口选项”栏下的“公式”项失效即可。

选项-视图对话框

图2

  (8) 利用Ctrl*选取文本

  如果一个工作表中有很多数据表格时,可以通过选定表格中某个单元格,然后按下Ctrl*键可选定整个表格。Ctrl*选定的区域为:根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格,并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。

  (9) 快速清除单元格的内容

  如果要删除内容的单元格中的内容和它的格式和批注,就不能简单地应用选定该单元格,然后按Delete键的方法了。要彻底清除单元格,可用以下方法:选定想要清除的单元格或单元格范围;单击“编辑”菜单中“清除”项中的“全部”命令,这些单元格就恢复了本来面目。

 

在同一Excel单元格中混用文本与数值数据

Excel中,有时我们需要在同一单元格中既显示文本,又显示数值。可以通过以下这些公式技巧来将文本与数字混合显示在同一单元格中。

  技巧之一

  例如,假设A6单元格包含数值1234,我们可以在另一个单元格(D5)中输入以下公式:

  ="总数:"&A6

  则在D5单元格中就会显示出:“总数:1234”,如图1所示。

在同一Excel单元格中混用文本与数值数据

1 计算结果

  在本例中,符号&所起的作用是将文本“总数”与A6单元格中的内容连接在一起。对这样一个包含公式的单元格应用数值格式是不起作用的,因为单元格中包含文本而不是数值。

  技巧之二

  如果在公式中巧妙地使用TEXT函数,也可以实现文本与数值同时显示在一个单元格中。

  例如,我们可以在另一个单元格(D6)中输入以下公式:

  ="总数: "&TEXT(A6,"$#,##0.00")

  这样在D6中就会显示为:“总数: $1,234.00”,如图2所示。

在同一Excel单元格中混用文本与数值数据

2 计算结果

  技巧之三

  下面是一个使用NOW函数实现同一单元格同时显示文本与日期时间型数值的例子。

  ="本报告打印于"&TEXT(NOW(),"yyyy-mm-d h:mm AM/PM")

  则输入完成后显示为:“本报告打印于2006-04-19 4:21 PM”,如图3所示。

在同一Excel单元格中混用文本与数值数据

图3 使用NOW函数的效果

 

隐藏指定Excel 不让它们被打印出来

在有些情况下,我们并不需要打印Excel工作表的某些行。例如,包含机密信息的行,或者包含中间计算结果的行。

  这时,我们可以将这样的行隐藏起来,在打印完工作表之后再恢复其显示。如果工作表中包含许多不需要打印的行,隐藏与恢复显示的工作就会比较费时费力了。

  下面将向大家介绍一种快速切换行的隐藏与显示状态的技巧,使用这一技巧,会大大提高隐藏与显示非打印行的工作效率。该技巧主要用到Excel的“组与分级显示”功能。

  如图1所示,我们将以该工作表为例来进行练习,其中第51015行是不希望打印的行。点击此处下载例子工作表

隐藏指定Excel行不让它们被打印出来1 工作表

  具体操作步骤如下。

  1.选中第5行,选择菜单命令“数据|组和分级显示|组合”(或者按快捷键Alt+Shift+向右方向键),如图2所示。

隐藏指定Excel行不让它们被打印出来2 菜单命令

  这时,在第5行左侧出现如图3所示的隐藏指定Excel行不让它们被打印出来标记,并且在工作表的左上方出现12,这是分级显示符号。单击某级别的显示符号,可以隐藏或显示下一级的明细数据。我们要利用的正是分级显示符号的这一功能。

隐藏指定Excel行不让它们被打印出来3 标记

  2.选择第10行,按F4键。F4键的作用是重复最近的一次命令。这里也可以按Alt+Shift+向右方向键,只不过不如F4来得快捷。

  3.选择第15行,按F4键。现在的工作表如图4所示。

隐藏指定Excel行不让它们被打印出来4 工作表

  4.单击左上角的分级显示符号1,将第51015行隐藏,现在的工作表应如图5所示。

隐藏指定Excel行不让它们被打印出来5 工作表

  经过以上操作,我们可以快速地将不需要打印的行隐藏起来,等打印完了可以再单击分级显示符号2将其恢复显示。

  小提示:Alt+Shift+向右方向键实际上是将某行降了一级,如果想让某行升一个级别,可以按快捷键Alt+Shift+向左方向键,或者选择菜单命令“数据|组和分级显示|取消组合”。

避免Excel重复输入数据

选中需要输入身份证号码的单元格区域(D2D101),执行“数据→有效性”命令,打开“数据有效性”对话框,在“设置”标签下,按“允许”右侧的下拉按钮,在随后弹出的快捷菜单中,选择“自定义”选项,然后在下面“公式”方框中输入公式:=COUNTIF(D:D,D2)=1,确定返回。

  以后在上述单元格中输入了重复的身份证号码时,系统会弹出提示对话框(如图1),并拒绝接受输入的号码。

1

  注意:为了让输入的身份证号码能正确显示出来,请将单元格设置为文本格式。

在同一Excel图表工作表中显示多个图表

Excel中,当我们希望单独查看图表或数据透视图报表时,可以使用图表工作表。图表工作表指的是工作簿中只包含图表的工作表,在其中可以插入独立于工作表数据的图表,或者插入独立于数据透视表的透视图报表。

Excel允许将多个图表嵌入到一个独立的图表工作表中,下面的技巧将介绍如何在一个图表工作表中同时显示多个图表,并使图表的显示可以随窗口大小自动调整。

1.象平常一样在一个工作表中先输入一些用于创建图表的数据,如图1所示。

1

2.选中A1E5单元格,执行菜单命令“插入>图表”,当出现如图2所示的“图表向导”对话框时,直接单击“完成”按钮,在工作表中创建一个柱形图表。

2

3.单击工作表中的任一空白单元格,然后按快捷键F11,这将创建一个用于放置嵌入图表的图表工作表,如图3所示,新建的图表工作表出现在工作表Sheet1的左边。

3

4.单击Sheet1回到柱形图表所在的工作表,选中柱形图表,然后执行菜单命令“图表>位置”,打开如图4所示的“图表位置”对话框,选中将图表“作为其中的对象插入”单选钮,并在右侧下拉列表中选择“Chart1,单击“确定”按钮,即可将柱形图作为一个对象插入到新建的图表工作表Chart1中,结果如图5所示。

4

5

5.重复上述步骤再向图表工作表Chart1中插入两个其它类型的图表,适当改变之三个图表的大小和位置,并选择菜单命令“视图>随窗口大小调整”,这样当我们缩放Excel窗口或工作簿窗口时,图表工作表中的这三个图表就会自动随窗口的大小变化作出调整以适应窗口了,如图6所示。

 

Execl 操作三技巧

最近几天,小张一直觉得头疼,在这个月有大量的数据报表需要整理分析,而且其中的很多资料需要打印出来予以存档。由于绝大部分的资料是通过Excel编辑的,打印出来的效果总不能够让领导满意,比如分页没有标题行、没有设置页眉、页脚……更为重要的是,其中的一些资料要分发给合作单位,因此公司的LOGO必须添加到Excel文档的每页的适当位置,怎么办呢?

    其实这些难题,都可以通过Excel中的“页面设置”选项予以解决,下面就来看看具体是如何操作的。

    **让打印的Excel文档有标题

    一些内容较多的Excel文档,往往一页容不下,因此需要打印多页,但是当打印出来后,就会发现作为表格重要项目的顶端标题行(或者左端标题列)仅仅在表格的第一页中出现,这样在以后的使用过程中就会造成一些不便。如果需要让打印出来的多页Excel文档每页都有标题,可以采用以下操作予以解决。

    第一步:打开需要打印的Excel文档,比如:2004年四季度产品销售汇总分析表.xls,单击“文件”菜单,选择 “页面设置”命令,在弹出的“页面设置”对话框中,单击“工作表”选项卡(图1)。

   

    第二步:在“工作表”选项卡中,单击“打印区域”框右侧的折叠按钮,在随后弹出的“页面设置-打印区域”对话框中选择需要打印的数据区域(图2),比如$A$4:$G$96。需要注意的是,这里只需要选择主要的数据区域,而不要选择标题行所在的单元格区域。

   

    第三步:由于小张这次打印的表格标题行在文档的最上方,因此单击“工作表”选项卡中“打印标题”下面“顶端标题行”右侧的折叠按钮,在弹出的“页面设置-顶端标题行”对话框中选择文档顶端的标题行所在的单元格区域,比如$1:$3,这样打印出来的文档每页都有标题行了。

    需要注意的是,如果标题行处于多行中,在选择时,一定要直接选定所需要的所有行,而不要在按住Ctrl键的同时选择需要的行,这样是不能将选择的区域作为标题行,会弹出一个打印标题选择错误的提示窗口。

**如何设置Excel文档的页眉、页脚

   大家都知道在Word中,可以非常方便地设置文档的页眉、页脚,在Excel文档中同样可以编辑,这样在打印的处理的每页Excel文档中都会出现相应的注释信息了,会使Excel文档显得更加专业了。另外在Excel文档的页眉、页脚中,可以根据自己的需要添加图片。

    1.添加页眉、页脚

    如需要插入页眉、页脚时,单击“页面设置”对话框中的“页眉/页脚”选项卡,由于小张需要在文档中插入页脚,因此单击选项卡中“页脚”下面的列表框,从中选择一种自己比较喜欢的页脚格式,比如:wanglanfu, 第一页,2004127(图3)。

   

    2.、添加LOGO

    按照领导的意图,小张还需要在文档的适当位置添加公司的LOGO,因此他单击“页眉/页脚”选项卡中的“自定义页脚”按钮,在弹出的“页脚”对话框中,从其下方的页脚内容位置框中,选择在页脚中插入公司LOGO的位置,比如“左”。

    单击“页脚”对话框中间工具栏中的“图片”按钮,在随后弹出的窗口中选择公司的LOGO图片文件,比如:公司标志02.gif,然后单击“插入”按钮(图4)。

   

    当在页脚中插入公司LOGO图片时,其大小可能并不符合文档需要,因此需要调整一下,单击“页脚”窗口工具栏最右侧的“图片格式”按钮,在弹出的对话框中,设置插入图片的适当大小、比例等(图5)。

   

    全部设置完毕后,关闭“页面设置”对话框,通过“文件”菜单中的“打印预览”选项,就可以看到刚才编辑的漂亮的页脚了(图6)!

***能节则俭,节约每一页打印纸

    虽然行标题和页脚都设置了好了,但是小张发现最后一页的内容太少了,还不足纸面的1/3,这样是不是有点浪费纸张啊,而且也不美观!对于这个问题可以通过设置页面缩放比例来解决。

    在“页面设置”对话框中,单击“页面”选项卡,由于需要将3页内容缩小在2页上打印,因此他可以选中“缩放”下面的“调整为”复选项,然后将其后面的“页宽”、“页高”数值分别设置为“2即可(图7)。

   

 

EXCEL小技巧:两列数据合并

比如我们做好一个EXCEL表格,填好了数据,现在想修改其中的一列(行),例如:想在A列原来的数据的基础上加8,有没有这样的公式?是不是非得手工的一个一个数据地住上加?对于这个问题我们自然想到了利用公式,当你利用工式输入A1=A1+8时,你会得到EXCEL的一个警告:“MICROSOFT EXCEL不能计算该公式……”只有我们自己想办法了,这里介绍一种简单的方法:
  第一步:
  在想要修改的列(假设为A列)的旁边,插入一个临时的新列(为B列),并在B列的第一个单元格(B1)里输入8
  第二步:
  把鼠标放在B1的或下角,待其变成十字形后住下拉直到所需的数据长度,此时B列所有的数据都为8
  第三步:
  在B列上单击鼠标右键,“复制” B列。
  第四步:
  在A列单击鼠标的右键,在弹出的对话框中单击“选择性粘贴”,在弹出的对话框中选择“运算”中的你所需要的运算符,在此我们选择“加”,这是本方法的关键所在。
  第五步:
  将B列删除。
  怎么样?A列中的每个数据是不是都加上了8呢?同样的办法可以实现对一列(行)的乘,除,减等其它的运算操作。原表格的格式也没有改变。

Excel打印故障问答

问:我想打印彩色的页眉,却不能为页眉应用彩色格式。如何解决?  
  答:页眉中的文字只能打印为黑色,即便使用彩色打印机,也不能对页眉和页脚中的文字应用彩色。要打印彩色页眉,可以使用打印标题代替页眉。例如,要在每页的顶端打印公司的徽标与地址,将徽标与地址信息放置在工作表的前几行;在“文件”菜单上,单击“页面设置”,单击“工作表”选项卡,然后在“顶端标题行”框中,输入包含徽标与地址的行。  
  问:我使用“格式”菜单上的“背景”命令,为工作添加了背景图案,但打印时,却没有打印工作表背景。为什么?如何解决?  
  答:用“格式”菜单上的“背景”命令添加到工作表中的背景图案不会被打印出来。但用“插入”菜单中的“图片”命令添加到工作表中的图案和用“格式”菜单上的“单元格”命令添加到工作表中的单元格背景图案或阴影,都会被打印出来。  
  问:在我打印工作表时,Excel忽略了所设置的分页符。为什么?  
  答:如果已设置让Excel将工作簿打印到特定数目的页面中,那么,Excel将忽略已设置的手动分页符,并缩小打印工作表的尺寸。若要将工作表打印到指定的页数中并使用手动插入的分页符,请单击“文件”菜单上的“页面设置”,再单击“页面”选项卡,然后单击“缩放比例”。如果“缩放比例”不能产生预期效果,你可以在设置打印区域时,将其中的每一页都选定为独立的区域,Excel会将打印区域中不相邻的区域打印到独立的页面上。  
  问:我在工作表中设置了多个打印区域后,Excel却不能将多个打印区域打印到同一页上。如何解决?

答:如果打印区域中包含工作表中的不相邻区域,在默认情况下,Excel将把每个区域打印到单独的页面上。你可以使用下列方法指定工作表中的独立区域,并将它们打印到同一页面上。  
  1.将不相邻的列打印到相邻的位置上:选定不想打印的列,点击“格式”菜单中的“列”,然后单击“隐藏”。设置一个包含所有列的打印区域,其中也包括隐藏的列。  
  2.将不相邻的行打印到上下相邻的位置上:选定不想打印的行,点击“格式”菜单中的“行”,然后单击“隐藏”。设置一个包含所有行的打印区域,其中也包括隐藏的行。

 

EXCEL进行学生成绩管理一例

在老师的日常工作中,对学生的成绩进行统计分析管理是一项非常重要也是十分麻烦的工作,如果能够利用EXCEL强大的数据处理功能,就可以让各位老师迅速完成对学生的成绩的各项分析统计工作。下面就向各位朋友介绍一些利用EXCEL进行学生成绩管理的小技巧。
  一、快速转换学生考试成绩等级
  有的时候,会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况,如将考试成绩在90分以上的成绩转换成“A+”形式,85-89分的成绩转换成“A”形式...。一般情况,在EXCEL表格中大家会采用IF()函数来设计公式进行转换,这样所设计的公式会变得很复杂,如果进行转换的成绩等级类型超过IF()函数的最大嵌套(7)时,IF()函数就无能为力了。这时我们可用如下的方法来简化操作。
 1、打开学生成绩工作表。
 2、在G2I12单元格录入考试成绩分数段与考试成绩等级对照表。
 3、在D3单元格录入公式“=INDEX(I$3:I$12,MATCH(1,(C3>=G$3:G$12)*(C3<=H$3:H$12),0))”,由于该公式为数组公式,在录入完上述内容后,必须同时按下“Ctrl+Shift+Enter”键,为上述公式内容加上数组公式标志即大括号“{}”。该公式的作用就是,根据C3单元格中的学生成绩,在D3单元格自动将该成绩转换成相应的成绩等级。
 4、将光标移到D3单元格,向下拖动填充柄至D12单元格,将公式进行快速复制,这样就可以迅速完成转换学生成绩等级的工作。
   二、快速统计学生考试成绩分布情况
  在利用EXCEL管理学生考试成绩时,常常要统计各分数段学生考试成绩的分布情况,如果采用下面介绍的这种方法,就能使这项工作变得非常方便。
  1、打开学生成绩工作表(本例仍使用上例的工作表,格式上图
  2、在G3G6单元格录入学生考试成绩的统计分段点。如在本例中采用的统计分段点为:60697989,即统计60分以下、61-6970-7980-8990分以上五个学生考试成绩区段的人数分布情况,当然你也可以根据自己的实际需要在此进行不同的设置。
  3、选中要进行公式设计的单元格区域B14B18,按下F2键,录入公式“=FREQUENCY(C3:C12,G3:G6)”,由于该公式为数组公式,在录入完上述内容后,必须同时按下“Ctrl+Shift+Enter”键,为上述公式内容加上数组公式标志即大括号“{}”。
  4、当上述操作完成后,在B14B18单元格就迅速得到了正确的学生考试成绩分布情况(3)
  5、值得注意的是,在我们设计统计区段时,这个统计区段必须比统计分段点的数据个数多一个。这个多出来的统计区段表示超出最高间隔的数值个数。例如,在本例中,我们设计的统计分段点为60697989四个数值,这时要想取得正确的统计区段分布数据,就必须在B14B18五个单元格中输入 FREQUENCY() 函数计算的结果,多出来的这一个单元格将返回学生成绩表中大于90分的成绩的人数。

Excel高级筛选数据

  使用高级筛选功能可以对某个列或者多个列应用多个筛选条件。为了使用此功能,在工作表的数据清单上方,至少应有三个能用作条件区域的空行,而且数据清单必须有列标。“条件区域”包含一组搜索条件的单元格区域,可以用它在高级筛选筛选数据清单的数据,它包含一个条件标志行,同时至少有一行用来定义搜索条件。有了条件区域,就可以按下列操作步骤来进行高级筛选:

图16 单击这个按钮

  步骤一、选择数据清单中含有要筛选值的列标,然后单击常用工具栏中的“复制”按钮,参见图16,或者按下键盘上的Ctrl+C组合键。接着参见图17,选择条件区域第一个空行里的某个单元格,然后单击常用工具栏中的“粘贴”或者按下键盘上的Ctrl+V组合键。

图17 将选定的列标复制在条件区域中

图18 在条件区域中输入筛选条件

  步骤二、在条件区域中输入筛选条件,如图18所示。

图19 进入“高级筛选”对话框

  步骤三、从“数据”下拉菜单中选择“筛选”命令,然后从“筛选”子菜单中选择“高级筛选”命令,进入图19所示的“高级筛选”对话框。

图20 单击这个设置按钮

步骤四、参见图20,单击“高级筛选”对话框中“条件区域”设置按钮后,单击选定条件区域中的条件,然后再单击此按钮返回“高级筛选”对话框,最后单击“确定”按钮,结束操作。
  在“高级筛选”对话框中进行操作时,若筛选后要隐藏不符合条件的数据行,并让筛选的结果显示在数据清单中,可打开“在原有区域显示筛选结果”单选按扭。若要将符合条件的数据行复制到工作表的其他位置,则需要打开“将筛选结果复制到其他位置”单选按钮,并通过“复制到”编辑框指定粘贴区域的左上角,从而设置复制位置。
  注意:在“高级筛选”时,可以将某个区域命名为Criteria。此时“条件区域”框中就会自动出现对该区域的引用。也可以将要筛选的数据区域命名为Database,并将要粘贴行的区域命名为Extract,这样,中文Excel 2000就会让这些区域自动出现在“数据区域”和“复制到”框中。

 

Excel自动筛选数据

  若要查看数据清单中符合某些条件的数据,如本月中已有哪些用户与本公司有业务联系,就要使用筛选的办法把那些数据找出来。筛选数据清单可以寻找和使用数据清单中的数据子集。筛选后只显示出包含某一个值或符合一组条件的行,而隐藏其它行。
  中文Excel 2000提供有两条用于筛选的命令:“自动筛选”和“高级筛选”。
  “自动筛选”命令可以满足您的大部分需要,当需要利用复杂的条件来筛选数据清单时就可以考虑使用“高级筛选”命令。
  为了使用“自动筛选”命令,可以按下列步骤进行操作:
  步骤一、参见图12,从“数据”下拉菜单中选择“筛选”命令,然后从“筛选”子菜单中选择“自动筛选”命令。

图12 选择“自动筛选”命令

  注意:如果当前没有选定数据清单中的单元格,或者没有激活任何包含数据的单元格,选择“自动筛选”命令后,屏幕上会出现一条出错信息,并提示您可以做的操作。类似的操作还会在其它地方出现。
  此后,数据清单中第一行的各列中将分别显示出一个下拉按钮,如图13所示,自动筛选就将通过它们来进行。

图13 第一行的各列中将分别显示出一个下拉列表

  通过图13所示的列标下拉列表,就能够很容易的选定和查看数据记录。
  例如,这一步操作用于指查看工资额为800元的员工,操作结束后,它就会显示在屏幕上,如图15所示,这就是自动筛选的结果。

图14 筛选工资额为800元的数据记录

图15 查看工资额为800元的员工

 注意:若要在数据清单中恢复筛选前的显示状态,只需要进入“数据”下拉菜单后从“筛选”子菜单中选择“全面显示”命令即可。

 

EXCEL中设置排序条件

与筛选的操作不同,利用“数据”下拉菜单中的“排序”命令进行操作就不一样了。此命令对于数据内容较多的数据清单特别有用,若只想对某区域进行排序,也能让您非常满意,因为通过它可以设备各种各样的排序条件,并让中文Excel 2000自动进行排序。操作时,屏幕上将显示如图10所示的“排序”对话框,可以使用的各选项功能如下所述。

图10 进入“排序”对话框

1.主要关键字
  通过一份下拉菜单选择排序字段,打开位于右旁的单选按钮,可控制按递增或递减的方式进行排序。
2.次要关键字
  通过一份下拉菜单选择排序字段,打开位于右旁的单选按钮,可控制按递增或递减的方式进行排序。如果前面设置的“主要关键字”列中出现了重复项,就将按次要关键字来排序重复的部分。
3.第三关键字
  通过一份下拉菜单选择排序字段,打开位于右旁的单选按钮,可控制按递增或递减的方式进行排序。如果前面设置的“主要关键字”与“次要关键字”列中都出现了重复项,就将按第三关键字来排序重复的部分。
4.有标题行
  在数据排序时,包含清单的第一行。
5.无标题行
  在数据排序时,不包含清单的第一行。
  在使用“排序”命令时,若要排序的只是某一个区域,那么招待此命令时屏幕上会显示“排序报警”对话框,如图11所示。

图11 屏幕上将显示此对话框

  在这个对话框中,若选择打开“当前选定区域排序”单选按钮,则在单击“排序”按钮后将排序邻近的相关字段。若打开“以当前选定区域排序”则仅排序选定的区域。
  注意:如果排序结果与所预期的不同,说明排序数据的类型有出入。若想得到最好的结果,就要确保列中所有单元格属于同一数据类型。为了排序,输入的数据可以是数字或文字,但应避免在连续的单元格中交替输入数字或文字,而确保所有数字都要以数字或文字方式输入是排序是否成功的关键所在。若要将数字以文字方式输入,如邮政编码或产品编号,请在数字之前加上一个省略符号(),如:’610000,或在输入之前把该列格式化为文字,或输成中文符号,如:610000

 

EXCEL中排序数据记录

  在中文Excel 2000中也可以根据现有的数据资料对数据值进行排序。
  按递增方式排序的数据类型及其数据的顺序为:
  1.数字,顺序是从小数到大数,从负数到正数。
  2.文字和包含数字的文字,其顺序是
0 1 2 3 4 5 6 7 8 9(空格)!”# $ % & ' ( ) * + , - . / : ; < = > ? @ [ ] ^ _ | ~ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
  3.逻辑值,FalseTrue之前。
  4.错误值,所有的错误值都是相等的。
  5.空白(不是空格)单元格总是排在最后。
  递减排序的顺序与递增顺序恰好相反,但空白单元格将排在最后。
  日期、时间和汉字也当文字处理,是根据它们内部表示的基础值排序。

图8 单击这个按钮按升序方式排序

  最简单的排序操作是使用“常用”工具栏中的按钮。在这个工具栏上有两个用于排序的按钮,图标上有AZ与向下的箭头的哪一个按钮,用于按序方式重排数据,参见图8。按钮上标有ZA与向下的箭头的哪一个按钮用于降序排序,参见图9
  注意:若选定了某一列后来使用图8与图9所示的操作,排序将只发生在这一列中,其它列的数据排列将保持不变,其结果可能会破坏原始记录结构,造成数据错误!

图9单击这个按钮按降序方式排序

 

EXCEL创建数据清单

在中文Excel 2000中,排序与筛选数据记录的操作需要通过“数据清单”来进行,因此在操作前应先创建好“数据清单”。“数据清单”是工作表中包含相关数据的一系列数据行,如前面所建立的“员工工资表”,这张电子报表就包含有这样的数据行,它可以像数据库一样接受浏览与编辑等操作。这与使用中文Visual FoxPro 6相类似,只是在中文Excel 2000中可以很容易地将数据清单用作数据库,而在执行数据库操作时,例如查询、排序或汇总数据时也会自动将数据清单视作数据库,并使用下列数据清单元素来组织数据:
 ·数据清单中的列是数据库中的字段
 ·数据清单中的列标志是数据库中的字段名称
 ·数据清单中的每一行对应数据库中的一个记录
  那么数据清单与电子报表的区别是什么呢?
  其实没有多大的区别,前者的第一行含有列标题,后者是包含数据清单在内的数据库,而且还有标题!所以,您能很容易的建立数据清单。如员工工资表中的“姓名”、“年龄”、“职务”、“工资额”,这些就可以作为数据清单中的列标题,使用鼠标器选定如图1所示的单元格区域,一份数据清单就建立好了。

图1 选定这个区域建立数据清单

  其实,若选定一个区域,中文Excel 2000也会在需要的时候自动建立一份数据清单,只是该数据清单将包含所有的单元格,自动找到的列标题也不一定正确。
  注意:在每张工作表上只能建立并使用一份数据清单。您也应避免在一张工作表上建立多份数据清单,因为某些数据清单管理功能(如筛选)等一次也只能在一份数据清单中使用。
  一旦建立好了数据清单,还可以继续在它所包含的单元格中输入数据。无论何时输入数据,初学都应当注意遵循下列准则:

  1.将类型相同的数据项置于同一列中

  在设计数据清单时,应使同一列中的各行具有相同类型的数据项。这一点在前面建立“员工工资表”时就体现了出来,这是初学者一眼就能看明白的。

  2.使数据清单独立于其它数据

  在工作表中,数据清单与其他数据间至少要留出一个空列和一个空行,以便在执行排序、筛选或插入自动汇总等操作时,有利于中文Excel 2000检测和选定数据清单。
  3.将关键数据置于清单的顶部或底部

  这样可避免将关键数据放到数据清单的左右两侧。因为这些数据在中文Excel 2000筛选数据清单时可能会被隐藏。
  4.注意显示行和列

  在修改数据清单之前,应确保隐藏的行或列也被显示。因为,如果清单中的行和列没有被显示,那么数据有可能会被删除。

图2 进入“边框”选项卡

     5.注意数据清单格式

  如前所述,数据清单需要列标,若没有的话应在清单的第一行中创建,因为中文Excel 2000将使用列标创建报告并查找和组织数据。列标可以使用与数据清单中数据不同的字体、对齐方式、格式、图案、边框或大小写类型等。在键入列标之前,应将单元格设置为文本格式。
  6.使用单元格边框突出显示数据清单

  如果要将数据清单标志和其他数据分开,可使用单元格边框(不是空格或短划线)。
  其操作步骤如下:
  步骤一、右击选定的单元格,然后从快捷菜单中选择“设置单元格格式”命令,进入“单元格格式”对话框后单击“边框”选项卡。
  步骤二、单击“外边框”按钮后,从“线条”区域的“样式”列表窗中选择一种线型,参见图3

图3 选择边框线的样式

  步骤三、从“颜色”下拉列表中选择边框线的颜色,接着在“预览窗”中单击要使用边框线的边线,参见图4

图4 单击要使用边框线的边线

  步骤四、单击了每一条要使用边框线的边后,单击“确定”按钮,然后在中文Excel 2000的工作窗口中单击数据清单外的任意一处,就能在屏幕上看到所加入的边框线了,如图5所示。

图5 上述操作的结果

   7.避免空行和空列

  避免在数据清单中随便放置空行和空列,将有利于中文Excel 2000检测和选定数据清单,因为单元格开头和末尾的多余空格会影响排序与搜索,所以不要在单元格内文本前面或后面键入空格,可采用缩进单元格内文本的办法来代替键入空格。

图6 进入数据记录单

 

EXCEL公式中的数据类型自动转换

尽管每个运算符都需要特定类型的数值与之对应,但如果输入数值的类型与所需的不同,中文Excel 2000还是有可能做些力所能及的转换,并完成计算,虽说不是所有的转换都能成功,但下表中所列的常见输入错误都能被很好地处理。
表:公式中的数据类型自动转换

 

EXCEL技巧两则

★实现条件显示
  统计学生成绩时,希望输入60以下的分数时,能显示为“不及格”,输入60以上的分数时,显    示为“及格”。这样的效果,不妨称之为“条件显示”,用IF函数可以实现。假设分数在B3   元格中,要将等级显示在C3单元格中。那么在C3单元格中输入以下公式实现不同的等级:
  =if(b3<=60,“不及格”,“及格”) 分“不及格”和“及格”两个等级
  =if(b3<=60,“不及格”,if(b3<=90,“及格”,“优秀”)) 分三个等级
  =if(b3<=60,“不及格”,if(b3<=70,“及格”,if(b3<90,“良好”,“优秀”)))分为四个等级注意:符号为半角,IF与括弧之间不能有空格,而且最多嵌套7层。

★跨表操作数据
  设有名称为Sheet1Sheet2Sheet3的三张工作表,现要用Sheet1D8单元格的内容乘以    40%,再加上Sheet2B8单元格内容乘以60%作为Sheet3A8单元格的内容。则应该在    Sheet3A8 单元格输入以下算式:=Sheet1!D840%+Sheet2!B860%。
 

怎样同时改变多行行高?

我们知道,通过拖动行或列间的分界线可以改变行高或列宽,但怎样同时改变行或几列的高度或宽度呢?

  我们以改变行高为例,先选中要改变行高的列,按下Shift键再单击行标题头,可以选定连续的多行(如果要选中多个不连续行,可以按下Ctrl键)。选中多列后,拖动任意一个被选中的行标题间的分界线,到适当高度释放鼠标,所有被选中的行高都改变了。

改变行高前  选中欲改变行高的行

拖动分界线  改变行高后

  我们也可以精确地改变行高:选中多行后,单击“格式”菜单,选择“行”中的“行高”命令,设置行高为20,单击“确定”,行高都被设置为20了。

行高对话框

  同样道理,我们可以通过拖动或精确设置的方法改变多列的列宽。

 

如何将姓与名分开?

有这样一个问题,一个数据表中,A列是由姓和名组成了,如何将姓和名分开呢?

  一个简单的方法是应用Left函数,此函数可以返回文本串中的第一个或前几个字符。

  Left函数的语法形式为: LEFT(text,num_chars)
  其中:Text:是包含要提取字符的文本串。
     Num_chars:要提取的字符数量,这个值必须大于或等于 0
     如果 num_chars 大于文本长度,则返回所有文本;
     如果忽略 num_chars,则假定其值为 1

  下面我们看一下这个例子,要将姓分开,且例子中都是单姓的,所以我们可以在B1中添入“=left(A1)”,然后利用自动填充功能,完成下面单元格公式的复制。

填入公式 箭头4 公式结果

自动填充 箭头5 填充结果

  我们再看一个例子,某些会计程序在显示负数时会在数值的右边添加负号 ()。如果导入的文件是在按此方法保存负数的程序中创建的,则 Microsoft Excel 会将这些数值作为文本导入。要将此文本串转换为数值,必须返回除最右边字符(即负号)以外的所有文本串字符,然后乘以 –1。对于 num_chars 参数,可使用 LEN函数计算文本串中字符的个数,然后减去 1。例如,如果单元格 A1 中的值为“234–”,我们可以在B1中输入“LEFT(A1,LEN(A1)1)*1”。

输入公式

  按回车后B1中就显示为“–234”。同样,我们可以利用自动填充的功能完成其余数据的转换。

公式结果

  好,Left函数的功能就讲这些,与之相对的还有个“right”函数,可以返回文本串中后一个或几个字符,你可以放手一用了!

分列功能的妙用

上一个例子中,我们用left函数将姓名中的姓和名分开了,说实话,这个方法比较笨,因为EXCEL中提供了分列功能。

  我们可以看下面这个例子,原始数据中含有编号,而且随着行数的增加,编号位数也在增加,这时就不能用Left函数来解决问题了。

初始数据表预期数据表效果

  我们可以分列功能将编号和文本分开。选中此列,单击“数据”菜单中的“分列”命令,弹出“文本分列向导”对话框。

分列向导1

  在对话框中提供了两类数据分割方法,一是按固定宽度,一是按分隔符号。将姓与名分开的问题就可以通过选择“固定宽度”项来解决,当前这个例子可以选择“分隔符号”项,单击“下一步”。

分列向导2

  这里要选择分隔符号,因为例子中的编号和文本中间都有“.”,所以我们可以选择“其他”复选框,并在后面的文本框中输入“.”,再单击“下一步”。

分列向导3

  单击“完成”按钮,编号和文本就被分开了。

 

如何显示多行文本

一般情况下,在单元格中输入的文本是不换行的,这样看起来会很别扭。

自动换行前

  如果要在同一单元格中显示多行文本,可以这样做:选中要换行的单元格,单击“格式”菜单,选择“单元格”命令,然后选择“对齐”选项卡,选中“文本控制”标题下的“自动换行”复选框。

单元格格式对话框

  这时界面就变成了下面的样子。

自动换行后

  如果单元格中还有段落,就要在里面输入硬回车,这时Enter键的功能不是在单元格内换行,而是在单元格间移动。这时我们可以在编辑栏(或正编辑的单元格)中要换行的地方单击鼠标,按 ALT+ENTER 键来换行。

换行前

  换行后的效果如下图,在编辑区文字是换行的。

换行后

 

 

如何输入小写的“true

一看到这题目,你一定会笑,这谁不能,就在单元格里输入呗!可是你输入试试,会发现,一旦输入“true”回车后,系统会自动把小写的“true”,变为大写的“TRUE”,让你需要小写时,颇为头疼。

  不过,现在我有一个很简单的办法,可以让你正确输入小写的“true”。方法就是:在输入字母前,先输入一个英文半角的单引号“'”,然后再输入小写的“true”回车就可以了。

   在输入小写的“false”时,同样会被系统转化为大写字母,处理方法当然和上面的一样了。还有,当你想在单元格中输入“001”时,excel也不会让你轻松的输入,直接输入是不行的,必需输入“'001”才可以。

   不知你有没有注意过,当你在单元格内输入“5/1”时,系统会自动将其转化为“51日”,真是“好心帮倒忙呀!”,不过现在好了,只要先输入一个英文半角的单引号,就可以解决这类问题了。

   以后再遇到这样不能直接输入的问题,试一试这个技巧,也许会有效。

 

巧用“摄影”工具

excel中,有一个“摄影“工具,你知道吗?它可以将你的表格,图表,直接转化为图形,然后作为图形,贴到word文档或excel表格中。

首先,要把这个照相机拖到工具条中,做法是:打开“工具”菜单,选择“自定义...”,然后在弹出的对话框中,选择“命令”选项卡,再从“类别”一栏中选择“工具”,从“命令”一栏中,选择带这个照相机的“摄影”一项,选中并将它拖到工具条中,就可以了。

现在,我们来看看怎样“照相”,先简单做一个表格,将其选中,表格的周围会出现一圈虚线。然后,用鼠标点一下工具栏中的照相机图标,再在页面中的任一位置单击鼠标左键,刚才被照上的图表,就被“洗”成图片,放到页面中了。对这张图,可以进行所有的图片操作命令,如加底纹,改边框,变颜色等。

如果,你要把它放到word文档中,只要进行普通的拷贝,粘贴就可以了。

 

 

记忆式键入法的妙用

我们可以利用记忆式键入功能在同一数据列中快速填写重复录入项。

  如果在单元格中键入的起始字符与该列已有的录入项相符,Microsoft Excel 可以自动填写其余的字符。比如这一列中,我们已经输入过“张同国”这个名字,在表中同一列的不同行中再输入“张”字,单元格中就会自动显示出“张同国”字样。如果按 下ENTER 键,这个名字就输入到单元格中了。这一功能我们就称为记忆式键入法。

记忆式键入

  注意:此记忆式键入法只能“记忆”文字或文字与数字的混合项。所记忆的内容与已有录入项的大小写格式是一致的。

  如果不想采用自动提供的字符,可以继续键入。如果要删除自动提供的字符,可以按 BACKSPACE 键。

  我们还可以从当前数据列录入项列表中选择所需录入项。在录入时,按 ALT+↓ 键即显示已有录入项列表。

选择列表    右键菜单

  也可以用右键单击相应的单元格,再单击快捷菜单中的“选择列表”命令,并从列表中选择相应的条目。

 

 

如何快速了解函数的用法

Excel中提供了上百种工作表函数,我们可以根据需要选用。

  如果知道所要使用函数的名字,但又记不清它的所有格式,这时我们可以用键盘快捷键把参数粘贴到编辑栏内。

  具体方法是:在编辑栏中输入一个等号,然后录入一个函数,再按下Ctrl+A键,弹出公式选项板,里面显示了选定函数的名称及其每个参数。在打开的对话框中添入所需参数,当我们选取任何一个参数时,在对话框中会看到它的解释。 当使用易于记忆的名字且具有很长一串参数的函数时,这个方法非常有用。

输入函数  按下C trl+A后

   如果你不知道EXCEL提供了那些函数,或是记不起函数是怎么写的,可以用下面的方法查看。单击工具栏上的“粘贴函数”粘贴函数按钮按钮,弹出“粘贴函数”对话框。我们可以在左侧列表框中选择函数分类,在右侧列表框中选择函数名称,对话框的下面就会出现一行小字,提示此函数的作用和功能。

粘贴函数对话框

 

 

Excel中录入固定格式数据的技巧

在应用Excel某些单元格中需要输入固定格式的数据时,怎么办呢?我们利用“数据有效性”做成一个下拉列表,即可进行选择性输入。

  选中需要建立下拉列表的单元格区域,执行“数据→有效性”命令,打开“数据有效性”对话框,在“设置”一栏下,按“允许”右侧的下拉按钮,在随后弹出的快捷菜单中,选择“序列”选项,然后在下面“来源”方框中输入序列的各元素(如“董事长”总经理,副总经理,销售主管,其他职位”等),按确定返回。

  选中上述区域中某个单元格,在其右侧出现一个下拉按钮,点击此按钮,在随后出现的下拉列表中,选择相应的元素(如“副总经理”),即可将该元素输入到相应的单元格中。

 

Excel:利用身份证号码提取个人信

一、分析身份证号码  其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。
  15位身份证号码:第78位为出生年份(两位数),第910位为出生月份,第1112位代表出生日期,第15位代表性别,奇数为男,偶数为女。
  18位身份证号码:第78910位为出生年份(四位数),第11、第12位为出生月份,第1314位代表出生日期,第17位代表性别,奇数为男,偶数为女。
  例如,某员工的身份证号码(15)320****20807024,那么表示197287日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。
  二、提取个人信息
  这里,我们需要使用IFLENMODMIDDATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C),出生年月信息填写在D列,性别信息填写在B列。
  1. 提取出生年月信息
  由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中:
  LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。
  MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第78910位数字。
  MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9101112位数字。
  IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。
  如果需要显示为“7012月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。
  2. 提取性别信息
  由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错
  例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"","")”,其中:
  LEN(C2)=15:检查身份证号码的长度是否是15位。
  MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。
  MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。
  MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。
  IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"",""):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。
  回车确认后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。如图2所示,现在这份报表无论是提取信息或是核对,都方便多了!

 

1 输入身份证号码

  

2 提取个人信息

 

 

用填充柄自动填充工作表

  在Excel 2000工作表中,如果需要在一行或一列单元格中填入顺序排列的项目序号,用Excel 2000的自动填充功能可以帮助您快速输入这些数据序列,而且还不容易出错。你不会不知道什么是控制柄吧?选中一个表格,其右下角有一个实心的小方块,它就是控制柄,当鼠标放上去的时候便会变成实心的“+”。其方法:在第一单元格那输入第一个数据,在下一个单元格那输入第二个数据,选定这两个单元格,将光标指向单元格右下方的填充柄,沿着要填充的方向拖动填充柄,过的单元格中会自动的按Excel 2000内部规定的序列进行填充。如(下图二), 在B3中输入“第1名”,然后拖动控制柄往下拉,松开鼠标时所拉过的区域就自动填充了“第2名、第3名”。在拖动控制柄的过程中我们也可以在鼠标的右边小说明中看到所填充的内容。

图二

 

图二

 

  如果使用鼠标右键拖动单元格填充柄,也可以实现自动填充。其方法是:在某单元格内输入数据,按住鼠标右键,沿着要填充的方向拖动填充柄,此时就会弹出包含下列各项的快捷菜单(如图三):复制单元格、以序列方式填充、以格式填充、以值填充、以天数填充、以工作日填充、以年填充、等差序列、等比序列、序列,此时,您可以根据需要进行选择填充方式。显然,这种方法更有灵活性。

图三图三

另外,双击填充柄自动填充工作表也是一个不错的方法
  自动填充数字序列和文本序列也可以不拖动填充柄通过单元格区域,而采用双击填充柄的方法实现:对于数字序列的填充,要选定包含初始值单元格在内的两个单元格,然后双击填充柄。而对于文本序列的填充,只需选定初始值单元格,仍然后双击填充柄。
  例如:要想在 B2:B11 中输入数字序列“123.....10”, 只须在B2中输入“1”,选定B2:B11,双击填充柄即可。如果需要在“C2:C7”中输入文本序列“第1名、第2名、.....6名”,只需要在C2中输入“第1名”,选定C2单元格,双击填充柄即可。
双击填充柄自动填充是一种更为快速的方法,要填充的序列的单元格区域越大,如超过一屏的大区域,越能体现他的优越性。
  要注意的是,自动产生的序列数由A2向下直到遇到空白单元格为止的单元格个数决定。例如, A9为空白单元格,那么此法仅在 B2:B8中 产生序列“123.....7”。同样,若B5为空白单元格,那么仅在C2:C4中产生序列“第1名、第2名、第3名”。

Execl中快速互换数据

Execl中,相邻两列或两行的数据如果要进行互换,一般方法是用复制粘贴的方法来进行,其实还有更快的方法。假定要把A1B1的数据互换,先选取A1,接着将鼠标移到选取范围的边缘,光标的形状变成白色箭头状,这时先按住键盘上的Shift不放,再按住鼠标的左键,拖动鼠标到B1右边框线上,这时在右边框线上会出现一条虚线,先松开鼠标左键后,再松开Shift键,数据便已经互换了。相邻行之间的数据互换如法炮制即可。

输入SUM函数有快法

Excel工作表中,SUM(求和)函数是大家使用最为频繁的函数之一。要输入该函数时,大家通常的做法是单击常用工具栏上的自动求和”(∑)按钮。其实,按住Alt键,同时键入“=”,同样可以输入SUM函数。而且这免除你移动鼠标的麻烦,节省了时间,能够提高编辑效率。

巧用IF函数核对录入的数据

Excel中输入大量数据时,输入人员常因数据量大,看得头晕眼花,经常输错,结果还需人工校对几遍,但仍然不能保障完全正确。其实巧用Excel中的IF函数便能达到比人工校对效率更高。在输入的数据(C1单元格)后面另增D1E1单元格,D1单元格用作核对,在E1单元格中输入=if(C1=D1,"正确","错误")即可。那么我们先将C列中数据输完后,再在D列中重输一遍,如E列中提示错误,则表示该行数据中C列或D列中有一项是错误的。

Excel中动态公式的应用

Excel的某个工作表中,如果随时查看某个动态区域的统计结果,可以设计动态公式:在某个单元格中输入函数式:=SUM(INDIRECT(B1):INDIRECT(C1))。如果需要统计D2H51区域中数值的和(也可以进行其他计算),只要在B1C1单元格中分别输入D2H51,上述函数式所在的单元格中就显示出了相应的和。

Excel中插入拼音

在我们使用Excel时,常常会遇到十分孤僻的字,但加上拼音注释就能使人看得更明白。具体的方法是:选中想要加入拼音注释的表格,打开Excel的菜单栏中的格式菜单,选中拼音信息/设置项,在弹出的对话框中的设置字体选项中,分别选择拼音的对齐方式和字体,最后选择拼音信息子菜单中的编缉,即可以在单元格中输入拼音了。要查看拼音,可通过拼音信息子菜单中的显示和隐藏项显示注释的拼音。

在Excel中快速输入特殊符号

报表中常用的○○中的“○”。很多人都是将就地打印为00OO二零零二。其实这都是不正确的,“00”是数字键,“OO”是英文字母“o”的大写,零零是中文数字的大写,而是中文数字的小写,它们之间都不匹配。经过仔细摸索,笔者发现被忽略的中文输入法中的软键盘可以轻松地输入常用符号。打开中文输入法,先输入,在中文输入法状态下,用鼠标右键单击输入法的软键盘标志,这里选单位符号,这样输入法的软键盘就被打开且定位到中文数字上,按键盘上的Q键或用鼠标点击软键盘上的Q键,中文小写的“○”便被录入进来。

在Excel中快速输入大写数字

智能ABC输入法状态下,按住Shift键,再按下“i”(不含引号),松开Shift键,然后输入任一数字键,就得到相对应的大写数字了。另外,财会人员常用的单位词,如个、拾、佰、仟、万等,同样可以借助于i键加上单位词相对应的汉语拼音第一个字母方便地得到(具体可查看智能ABC输入法帮助菜单)

 

让Excel启动时自动打开工作簿

用户可自定义默认工作簿,以便每次启动Excel时,均可自动打开此工作簿。方法是:自定义一个新工作簿,然后将它保存在XLStart文件夹下。在C:\Documents and Settings\用户名\Application Data\Microsoft\ExcelC:\Program Files\Microsoft Office\Office10下就可以找到XLStart文件夹。具体路径还要视你的系统安装盘符而定。

彩色Excel表格显示打印两不误

  如果将Excel工作表中的数据设置为彩色格式,而打印机为黑白打印机,那么工作表中的彩色区域打印出来的效果可能会不理想。在不更改工作表颜色设置的情况下,可以进行如下设置:

  1.在“文件”菜单中,单击“页面设置”命令。

  2.单击“工作表”选项,然后选择“打印”框下的“单色打印”项。如果使用的是彩色打印机,选择该选项可减少打印所需时间。

  3.单击“确定”按钮。

现在,你的Excel工作表在屏幕上看起来仍是赏心悦目,打印出的黑白稿也能称心如意,感觉不错吧!

巧用“选择性粘贴”使行列互换

假如A1-A10单元格中有不同的内容,现在需要将这些内容分别排列在A1B1C1D1...J1单元格中,即将原来在同一列中的内容重新排列在一行中。难道要一个一个单元格去复制粘贴吗?完全不用。首先,选中A1-A10单元格,单击右键,在弹出的菜单中选择复制命令;接着单击B1单元格,再单击右键,在弹出的菜单中选择选择性粘贴命令,弹出对话框,选择其中的复选项转置,点击确定。这时你会看到B1C1....K1单元格中已有了A1-A10单元格中的内容,将A列选中,在右键菜单中删除,此时行列转换工作就完成了。

在Excel中快速输入小数

利用Excel处理数据时,经常会要输入一些小数,有时一整列都是小数,这样对输入速度有很大影响,大家不妨来试试这个办法:点击工具/选项,在弹出的的选项对话框中选择编辑选项,然后选中自动设置小数点的复选框,并在位数框内设定小数点的位数。这样当你输入小数时就不用再输入小数点了,它会将你输入的数字根据设置的小数点位数自动转化为小数!如设置位数是1位,输入123就自动变为12.3了。

让“@”减轻你的数据录入工作量

Excel工作表中,如果有大量诸如“[HP打印机]”“[EPSON打印机]”之类的重复文字,可以如此定义这些单元格的格式,以减轻数据的录入工作量:选择需要的单元格区域,然后单击格式菜单中的单元格,单击数字选项,在分类框中单击自定义,在类型框中输入“[@打印机]”(@为半角字符),单击确定按钮。之后,在这些单元格中,当你输入“HP”后,该单元格将会显示为“[HP打印机]”

快速批量编辑Excel数据

要将Excel工作表某单元格区域中的所有值均减少10%,可采用以下步骤:在某单元格中键入数字“0.9”,复制该单元格,选中该区域,单击编辑菜单中的选择性粘贴命令,单击,单击确定按钮。

同时在多个单元格中输入相同数据

Excel XP中,一个工作表的多个单元格中(单元格可以不必相邻)有时需要输入相同的数据,那么如何才能快速地完成输入呢?方法很简单,你只要选定需要输入数据的单元格,用“Shift”(“Ctrl”)键与鼠标相配合,进行连续(或分散)选择多个单元格,然后输入相应的数据(例如“ABC”),然后按下“Ctrl+Enter”组合键,此时你会发现所有被选中的单元格均被自动添入了“ABC”

快速设置不同工作簿的表间引用公式

不同工作簿的工作表之间的数据引用公式设置,通过键盘手工直接录入很麻烦,也很容易出错,利用Excel 2000/2002提供的鼠标操作方法,比较方便快捷。操作过程是:打开数据来源数据处理两个工作簿,在数据处理工作簿的某个工作表的某个单元中输入一个等号;通过菜单上的窗口选项,从当前工作簿切换到数据来源工作簿;选中数据来源工作簿中的某工作表中某单元或区域;按回车键,回到数据处理工作簿的编辑工作表,发现编辑的单元出现了公式。公式中的单元或区域是绝对引用,可以把$删除,改为相对引用,以方便其他单元公式的复制。

让表头跟随时间走

Excel2000/2002中使用函数可制作出跟随时间走的表头。选中要输入表头的行,输入函数“=MONTH(NOW())&″××″”,回车即显示系统日期所在月份的××表;如是本月制作上月的统计表,则输入函数=MONTH(NOW())-1&″×××,则显示系统日期月份的上一月××表。

Excel中少用鼠标的方法

Excel 2000/2002中快速编辑数据时,当想对单元格做改动时,因为经常需要用到鼠标,从而使编辑速度大大减慢。其实你可以使用一个快捷键──F2,这样你的手就不用离开键盘了。方法如下:利用方向键选择要编辑的单元格,按下F2键,编辑单元格内容。编辑完成后,按Enter键确认所做改动,或者按Esc键取消改动。

为新工作簿设置工作表张数

Excel2002是我们常用的办公软件,但一个新工作簿文件系统默认只有3张工作表,虽然可以通过插入来添加,但总感觉不便。如果我们已规划好一个工作簿文件需要多少张工作表时,我们可以用下面方法来事先设定。具体操作方法是,执行工具选项,打开选项对话框,单击常规选项,在新工作簿内的工作表数栏内输入你要设定的张数,确定退出。这时再新建工作簿文件就按你的需要自动提供出若干张工作表了。

设置多页打印标题与行列标志

打开Excel 2002,单击相应的工作表,再单击文件菜单中的页面设置命令,然后单击其中的工作表选项。如果要使每一页上都重复打印列标志,单击顶端标题行框右侧的按钮,在弹出的顶端标题行编辑框中,输入列标志所在行的行号。如果要使每一页上都重复打印行标志,单击左端标题列框右侧的按钮,在弹出的左端标题列编辑框中,输入行标志所在列的列标即可。

用Excel和Word把单列数据分为多列数据

在工作中常常需要单位人员名单,可用Excel 2000/2002直接打开Dbase等工资表数据库文件,将姓名所在列保留并保存为Excel文件格式。在Word 2000/2002中打开此文件,点击表格/转换/表格转换文字,再重复选中文字转换表格,弹出提示转换成几列,填入所需列数,此文件便按需要编辑成功。如不需要显示表格,单击鼠标右键,选中设置表格属性,将其颜色改为无色即可。用此法也可将分析家数据文件编辑成多列的《股票代码及名称表》。

快速切换相对引用与绝对引用

Excel 2000/2002中输入公式时,只要正确使用F4键,就能简单地对单元格的相对引用和绝对引用进行切换。现举例说明:对于某单元格所输入的公式为“=SUM(B4:B8)”。选中整个公式,按下F4键,该公式内容变为“=SUM($B$4:$B$8)”,表示对横、纵行单元格均进行绝对引用;第二次按下F4键,公式内容又变为“=SUM(B$4:B$8)”,表示对横行进行绝对引用,纵行相对引用;第三次按下F4键,公式则变为“=SUM($B4:$B8)”,表示对横行进行相对引用,对纵行进行绝对引用;第四次按下F4键时,公式变回到初始状态“=SUM(B4:B8)”,即对横行纵行的单元格均进行相对引用。需要说明的一点是,F4键的切换功能只对所选中的公式段有作用。

巧用MS Office的预览功能

熟悉Office 2000/XP的朋友知道,如果你在电脑中保存了许多文档,如果你取的文件名区别不大的话,时间一长就很容易混淆。如果你巧用MS Office的预览功能的话,就不会有此烦恼了。以Excel 2002为例,如果你想打开一个Excel文档,可以先打开Excel 2002,然后单击打开按钮,在弹出的打开对话框中选择该文件所在的文件夹,单击第一行工具栏的视图按钮旁边的三角符号,展开视图选项,选择预览,这样,只要选中一个文档,其内容就会在对话框右边显示出来,这样你就能快速查找和认出你所需要的文件了。MS Office的其他组件,如Word 2000/2002PowerPoint 2000/2002等都有这种预览功能。

Excel中巧借光标横向输入

我们知道,在使用Excel 2000/2002时,每次回车之后,光标大多都自动跳到了下面的相邻单元格中,这使我们纵向输入很方便。但是进行横向输入的时候就要麻烦一些了。在Excel 2000/2002中单击工具”→“选项,系统就会弹出选项对话框。单击编辑选项,选中Enter键后移动复选框,然后在方向下拉列表框中,选择需要激活的相邻单元格方向,比如单击向右,然后单击确定按钮。如果你想纵向输入,只要再把这个选项改回去就行了。

以“千”为单位显示数字

在编辑完成一个以为单位的Excel 2000/2002工作表后,如果需要改为以为单位,一个简单的方法是:选中工作表中需要改为以为单位的单元格区域,然后在格式菜单中,单击单元格,再单击数字选项,在分类框中单击自定义,然后,在类型框中键入“0”(实际键入的内容不含引号,且为半角字符),最后单击确定按钮。此时,选定区域中的数字将以为单位显示,如“2000”就会显示成“2”“2600”显示成“3”等。如果在类型框中键入“0,,,则以百万为单位显示数字。

启用Excel 2002的“自动保存”功能

为使用户的数据损失降至最低,许多软件都提供了自动保存功能。但是在Excel 2002中却没有直接提供这一功能,不过我们可以手工启用它。操作步骤如下:单击工具菜单执行加载宏命令选中自动保存选项单击确定按钮。如果该项功能尚未安装,需要将Office 2002安装盘放入光驱中,按照提示安装即可。安装完毕后,单击工具菜单执行自动保存命令,在自动保存对话框中设置合适的保存参数。至此,Excel 2002自动保存功能就被成功启用了,我们的数据又多了一层保护。

巧用“查找与替换”实现数据统计功能

Excel 2000/2002中,有时需要统计表中相同数据出现的次数。如统计人员花名册中性别为的人数,可选中性别所在列,点击编辑菜单下的替换项。在查找内容中填写替换为中填上“A”,选择全部替换,此时Excel会弹出菜单提示完成了X处被替换,这个X即为统计出的男性人数,再选择编辑菜单下撤消功能,使原单元格数据恢复。此法适用于对函数运用不熟悉者。

在Excel单元格内输入分数

要在Excel 2000/2002工作表的某个单元格内输入分数,如“1/3”,可以先键入“0”,再键入一个空格,然后键入“1/3”,其他类似。

Excel中保证录入数据的唯一性

在用Excel 2000/2002管理资料时,常常需要保证某一列中的数据不能重复,如身份证号。假设要从B2单元格开始录入,首先选中B2单元格,然后点击“数据→有效性”菜单,调出“数据有效性对话框”;在“有效性条件”选项中选择“自定义”,在公式中输入“=COUNTIF(B:B,B2)=1”,接着单击确定。为了将这个设置复制到B列中的其他单元格,我们还需要向下拖动B2单元格的填充柄。以后我们在向B列中输入数据时,如果输入了重复数据就会出现提示。这样就可以保证数据输入的唯一性了。

这里我们还可以扩展一下(还是以身份证为例),将上面的公式改为“=AND(COUNTIF(B:B,B2)=1,LEN(B2)=15)”,更改后,不仅要求录入的数据是唯一的,而且长度必须是15,从而大大减少用户在录入中产生的错误。

隐藏Excel工作表中不打印的列

在编辑完成一张Excel工作表后,如果C列不想打印出来,但又不能从工作表中删除,那么可以巧妙地将该列隐藏起来。方法是:向左拖动C列标的右边界,当越过B列的右边界时,松开鼠标左键,则C列就被隐藏起来了,而且隐藏起来的列不会被打印。之后,如果要重新显示C列,可将鼠标指向B列标的右边界,当指针变为中间一条黑粗竖线和左右向的双箭头时,稍微向右移动鼠标,并注意观察鼠标指针的变化:当指针中间的黑粗竖线变为两条细线时,向右拖动鼠标,则C列就再次显示在屏幕上了。以上操作对Excel工作表的行同样适用。

快速输入“度”的符号

在记事本或Excel 2002中,如果要经常输入的符号,如直角90°或气温28,可要记住下面的技巧。打开记事本或Excel 2002,按住Alt键,然后用数字键盘输入186,即可得到度这个符号。但这种方法不能在Word 2002中使用。

让Excel准确计算

  Excel 2000/2002使用15位有效数字精度进行存储和计算。当公式执行计算时,Excel通常会使用所引用的单元格中的存储值而不是显示值。例如,如果两个单元格中存储值都为$10.005,并且格式都设置为货币格式,那么每一单元格中都会显示为$10.01。如果将两个单元格相加,由于Excel引用的是存储值10.005,而不是显示值10.01,因此所得的结果将是$20.01而不是$20.02。为了能够得到$20.02这个正确结果,需要进行如下设置:

  1.在“工具”菜单上,单击“选项”,再单击“重新计算”选项。

  2.在“工作簿选项”标题下,选定“以显示值为准”复选框。

  3.单击“确定”按钮。

不过,如果使用工作表中显示的(带格式的)数值更改工作簿的计算精度,那么当前工作簿中所有工作表上的所有常量数值都将被永久性地更改。如果在以后计算中又重新选择完全精度,则初始的基本数值将无法恢复。

在Excel中快速插入图表

要在Excel 2000/2002中插入图表有许多种方式,但操作起来都不太方便快捷。根据笔者的实际经验,下面的这种方法比较快捷好用:在制作好的表格文件中,选择好要插入图表的区域,然后按“Alt+F1”键即可。

特殊单元格的选定

Excel 2000/2002中选定特殊的单元格,会给我们的日常使用带来许多方便。如我们要选定所有的空单元格,只要按以下步骤就可以了:先选择你所要选定空格的一块区域,如笔者要选定C2E5内的所有空单元格,就先选定C2E5。然后选择编辑菜单中的定位命令,出现定位对话框。在该对话框内选择定位条件按钮,选择所需条件,这里我们选择空值复选框,单击确定就能将C2E5中所有空单元格选定。

让Excel自动创建备份文件

Excel 2000/2002编辑重要数据文件时,如果害怕不小心把数据修改错了,可以让Execl在保存文件时自动创建一份备份文件。在Excel 2002中点击文件菜单,选择保存命令,在打开的保存对话框中,按下右上角的工具,点选常规选项,出现保存选项对话框后,勾选生成备份文件选项,按下确定,现在保存一下文档,Excel就会自动给这个文件创建一份备份文件,备份文件的文件名为原文件名后加上的备份字样,如“Book1的备份.xlk”,而且图标也不相同,相信很容易分别出哪个是备份文件。

Excel表格数据的行列互换

一张Excel报表,行是项目栏、列是单位栏,现在想使整张表格反转,使行是单位栏、列为项目栏,且其中的数据也随之变动。可先选中需要反转的数据单元格区域,执行复制操作,然后选中能粘贴下数据的空白区域的左上角第一个单元格,执行编辑、选择性粘贴命令,打开选择性粘贴对话框,选中其中的转置选项后,确定返回即可。

把文本型数据转换为数值型数据

Excel设置成文本型的单元格中输入数字,不能排序、求和等,但可以将它们转换成数值格式:在任意一个空白单元格中输入数值1,选中该单元格,执行复制操作,然后选中需要转换的单元格(区域),执行编辑、选择性粘贴命令,打开选择性粘贴对话框,选中其中的选项后,确定返回即可。以上方法可用于所有版本的Excel中,如果你使用的是Excel 2002Excel 2003,则选中需要转换单元格(区域),右边会出现一个智能标记,按一下这个智能标记,在随后弹出的下拉列表中,选中转换为数字选项,即可快速完成转换。

Excel中输入平方数

Excel单元格中要输入100的平方这样的数字,可以先输入“'1002”(最外层的引号不要输入),然后在编辑栏中选中最后的2,点击菜单命令格式单元格,在打开的对话框中,

点击字体选项,选中上标复选框即可。不过,用这种方式输入的是文本型数字。

Excel中用鼠标滚轮实现显示比例缩放

平时在使用Excel 2000/2002编辑较大的工作表时,常常要不停地拖动滚动条来查看更多的数据,其实我们可以通过按住Ctrl键再拨动鼠标滚轮使文档在10%100%之间进行缩放,这样就能够在一屏中看到更多的信息。先选择工具/选项,在常规标签中选中用智能鼠标缩放(Z)”,确定后退出,这时我们就可以在文档中直接拨动鼠标滚轮使表格缩放,是不是更方便了?

 

 

Excel中限定可输入数值的范围

学生的考试成绩通常位于0—100之间,在用Excel登记学生的成绩时,为了防止出错,我们可以设置一个范围:选中保存成绩的单元格区域,打开“数据有效性”对话框,在“设置”标签下,按“允许”右侧的下拉按钮,在随后弹出的快捷菜单中,选择“小数”选项,然后在下面分别输入最小值(“0”)和最大值(“100”);切换到“输入信息”标签中,设置提示信息;切换到“出错警告”标签下,设置出错提示及处理方法。全部设置完成后,确定返回。

  以后再输入成绩时,当输入的成绩不在0—100之间时,系统会给出相应的提示(见图),并拒绝接受录入的数值。

  注意:不进行“输入信息”和“出错警告”设置,不影响此有效性的使用。

 

 

Excel常见问题几例

笔者与Excel经常“打交道”的过程中,常遇到这样或那样的问题。其中,以下几方面的问题尤为突出。现将出现的问题及解决的方法告知同行,以此共勉。
  一、在Excel中不能进行求和运算
  由于在操作中更改了字段的数值后,求和字段的所有单元格中的数值没有随之变化,造成不能正常运算。可以单击“工具→选项”命令,在打开的“选项”对话框中单击“重新计算”选项卡。在“计算”选项区中选中“自动重算”单选按钮,单击“确定”按钮,就可以进行自动计算并更新单元格的值了。
  二、在Excel中打开多个工作表时提示“内存不足,不能执行显示”
  这时首先关闭所有的应用程序,在桌面上单击鼠标右键,从弹出的快捷菜单中选择“属性”选项,在打开的“显示属性”对话框中单击“设置”选项卡,将“颜色”下拉列表框设置为256色,即可解决问题。
  三、在Excel中出现启动慢且自动打开多个文件
  进入Excel中后,单击“工具→选项”命令,在打开的“选项”对话框中单击“常规”选项卡,删除“替补启动目录”文本框中的内容,单击“确定”按钮即可。
  四、在Excel中出现“#DIV/0!”错误信息
  若输入的公式中的除数为0,或在公式中除数使用了空白单元格(当运算对象是空白单元格,Excel将此空值解释为零值),或包含零值单元格的单无格引用,就会出现错误信息“#DIV/0!”。只要修改单元格引用,或者在用作除数的单元格中输入不为零的值即可解决问题。

 

 

- 50 -

 

编辑:索建康
评论区
发表评论

评论仅供会员表达个人看法,并不表明网校同意其观点或证实其描述
中国现代教育网 中华人民共和国 不良信息 垃圾信息 网警110
忻州市原平立达学校 版权所有中华人民共和国电信经营许可证 京ICP备13002626号-8
联系地址:忻州市原平立达学校 15034494466(索老师)QQ:125621549
京公网安备11010502032087
现代教育网 提供技术支持 违法和不良信息举报中心
Copyright 2006-2020 ypldzx.30edu.com.cn , All Rights Reserved
河北省威县第一中学 五寨县教育科技局 五寨县东城区幼儿园 忻州市教育局 山西省忻州市七一路学校 山西省忻州市长征小学 新晃一中 黑龙江省民间文艺家协会狼文化艺术专业委员会 代县教育科技局 定襄县河边中心校 定襄县教育科技局 神池县大严备学校 肥东县店埠学区中心学校 湛江经济技术开发区实验学校 连南瑶族自治县三排镇南岗中心学校 聊城幼儿师范学校 成都市籍田中学 原平教育科技局 博白县教研网 华池县南梁希望小学