Excel高级教程.docx

Excel高级教程.docx

《Excel高级教程.docx》由会员分享，可在线阅读，更多相关《Excel高级教程.docx（73页珍藏版）》请在冰豆网上搜索。

Excel高级教程

Excel高级教程

在Excel中统计与上报两不误

实现思路　　

　　因为在Excel中就能实现单元格中数据的动态更新，所以我们只需在Excel文档中建立两个工作表，分别取名为“出勤统计表”和“汇总表”。

把要进行数据输入与统计的“学生出勤情况统计表”建在“出勤统计表”工作表中（如图1），而把作为上报材料的“学生出勤情况汇总表”建在“汇总表”工作表中（如图2）。

然后把“学生出勤情况汇总表”与“学生出勤情况统计表”中的有关表格进行链接，就可实现数据的动态更新了。

　　如果上报材料除了此汇总表以外还有许多其他文字，只需在“汇总表”工作表中插入OLEWord对象即可，这样我们在“出勤统计表”工作表中输入数据进行统计后，马上就可在“汇总表”中打印上报材料，而无需运动Word了。

　　数据统计的动态更新　　

　　其实，实现单元格中数据的动态更新就是把要实现数据动态更新的单元格与源数据单元格进行链接，链接方法以“汇总表”中“汉语言”专业的出勤率为例：

　　1.打开“出勤统计表”工作表，激活“汉语言”专业的出勤率单元格H5，进行复制。

　　2.打开“汇总表”工作表，激活“汉语言”专业的出勤率单元格B3，单击[粘贴]按钮，这时粘贴入单元格里的数据会无效。

我们只要单击单元格旁边的“粘贴”图标，选择“链接单元格”（如图3）。

这时函数编辑框中显示为“=出勤统计表!

$H$5”，说明此单元格已与“出勤统计表”工作表中的H5单元格进行了链接。

　　数据的上报　　

　　如果需要上报的“汇总表”中还需输入许多其他文字，如何在“汇总表”工作表中插入OLEWord对象呢？

这里的OLEWord对象实际就是嵌入在Excel工作表中的Word文档，它可弥补Excel对文字处理能力的不足。

方法很简单，首先单击菜单中“插入/对象”命令。

然后在弹出的“对象”对话框中的“新建”选项卡里的“对象类型”栏中选择“MicrsoftWord文档”，单击[确定]按钮（如图4）。

最后只需在插入的OLEWord对象中对文字进行输入并编辑，方法与在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的公式制作出分栏打印效果。

具体操作如下：

先将某一班级名单按自己要求设置好，如页边距、表头、行高、字体等。

在打印预览中，判断出每页的数据行数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），以前遇到这种情况，要么把表格导入到WPSOffice或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中快速完成成绩统计查询

每次考试结束之后，最让老师头痛的就是成绩查询了。

现在，我们可以利用Excel2003的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。

非常便利。

在Excel中，双击“Sheet1”，将其命名为“成绩统计”，然后建立一个如图1所示的成绩统计表，输入全部学生的各科成绩备用。

用常规的方法计算出各学生的总分，并排出名次。

图1

双击“Sheet2”，将其命名为“成绩查询”。

在“成绩查询”工作表中，建立如图2所示的表格。

单击B2单元格，输入欲查询成绩的学生姓名。

单击B3单元格，在其中输入“=VLOOKUP（$B$2,成绩统计!

$B$3:

$I$56,2,FALSE）”，按回车键，则可以得到该名学生的