Excel-资料汇总_Excel高级教程(珍藏)

Excel-资料汇总_Excel高级教程(珍藏)内容摘要：

Excel 高级教程 在 Excel 中统计与上报两不误 实现思路　　 　　因为在 Excel 中就能实现单元格中数据的动态更新，所以我们只需在 Excel 文档中建立两个 工作表，分别取名为“出勤统计表”和“汇总表”。把要进行数据输入与统计的“学生出勤情况统计 表”建在“出勤统计表”工作表中（如图 1），而把作为上报材料的“学生出勤情况汇总表”建在 “汇总表”工作表中（如图 2）。然后把“学生出勤情况汇总表”与“学生出勤情况统计表”中的有关 表格进行链接，就可实现数据的动态更新了。　　 　　如果上报材料除了此汇总表以外还有许多其他文字，只需在“汇总表”工作表中插入 OLE Word 对象即可，这样我们在“出勤统计表”工作表中输入数据进行统计后，马上就可在“汇总表” 中打印上报材料，而无需运动 Word 了。　　 　　数据统计的动态更新　　 　　其实，实现单元格中数据的动态更新就是把要实现数据动态更新的单元格与源数据单元格 进行链接，链接方法以“汇总表”中“汉语言”专业的出勤率为例：　　 　　1. 打开“出勤统计表”工作表，激活“汉语言”专业的出勤率单元格 H5，进行复制。　　 　　2. 打开“汇总表”工作表，激活“汉语言”专业的出勤率单元格 B3，单击[粘贴]按钮，这时粘 贴入单元格里的数据会无效。我们只要单击单元格旁边的“粘贴”图标，选择“链接单元格”（如图 3）。这时函数编辑框中显示为“=出勤统计表!$H$5”，说明此单元格已与“出勤统计表”工作表中 的 H5 单元格进行了链接。 1 　　数据的上报　　 　　如果需要上报的“汇总表”中还需输入许多其他文字，如何在“汇总表”工作表中插入 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 中一样。 2 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”实现在单元格换行时，就是把这个“自动换行”的选项选上了， 3 现在反过来，也同样可以把它去掉，如此而已。 Execl 中为每页设置行标题 如果不设置每页的表头，则单击“打印预览”按钮，可以看到只有第一页中有表的表头，而其它 的页中都没有；这样打印出来的表看起来会很不方便，我们可以通过给工作表设置一个打印表 头来解决这个问题。 方式一：由于这个功能不能在预览视图中设置，单击“关闭”按钮回到正常的编辑视图，打开“文 件”菜单，选择“页面设置”命令，打开“页面设置”对话框（如图 6），单击“工作表”选项卡，单 击“顶端标题行”中的拾取按钮，对话框变成了一个小的输入条， 在工作表中选择数据上面的几行，这时在“顶端标题行”框中会自动输入这个区域的的引用，把 它为表头，单击输入框中的返回按钮，回到“页面设置”对话框，单击“确定”按钮，现在单击“打 印预览”按钮，所有的页中就都标题了。 方式二： 在”文件”--”页面设置”的”打印标题”区的”顶端标题行”框输入要打印的行标题的引用,如将第 1 行到第 3 行设为每页的行标题题,就输入$1:$3 即可. Excel 中日期与时间的快速处理 1、任意日期与时间的输入数字键与“/”或“-”配合可快速输入日期，而数字键与“：”配合可输入时 间：如输入“3/25”，然后回车即可得到“3 月 25 日”。又如输入“9:25”，回车即得到“09:25”。2、当 前日期与时间的快速输入选定要插入的单元格，按下“Ctrl”键与分号键“；”，然后回车即可插入 当前日期。而要输入当前时间，同时按住“Ctrl”键、“Shift”键与 分号键，然后回车即可。3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以 右击该单元格，选定“设置单元格格式→数字→日期”或“时间”，然后在类型框中选择即可。（出处： 赛迪网--中国电脑教育报） 4 利用 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)，以前遇到这种情况，要么把表格导入到 WPS Office 或 Word 中处理，要么就是打印好了之后再用笔加上去。经过一番摸索，终于发现了在 Excel 中设 置斜线的方法。 5 图1 图 21、实现在单元格中分行的效果：我们首先在单元格中输入“性别”，这时候按 回