Excel表格从菜鸟到高手Word文档下载推荐.docx
《Excel表格从菜鸟到高手Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《Excel表格从菜鸟到高手Word文档下载推荐.docx（105页珍藏版）》请在冰点文库上搜索。
以横向排列，逐行显示的“学生姓名”列为例，具体操作方法如下：
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*n＞nn-MODI3n＋1IFMODI32*n＝01MODI32*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中快速完成成绩统计查询
每次考试结束之后，最让老师头痛的就是成绩查询了。
现在，我们可以利用Excel2000的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。
非常便利。
在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,成绩统计!
$I$56,4,FALSE）”，按回车键，得到英语成绩；
单击B6单元格，在其中输入“=VLOOKUP（$B$2,成绩统计!
$I$56,5,FALSE）”，按回车键，得到机械成绩；
单击B7单元格，在其中输入“=VLOOKUP（$B$2,成绩统计!
$I$56,6,FALSE）”，按回车键，得到电工成绩；
单击B8单元格，在其中输入“=VLOOKUP（$B$2,成绩统计!
$I$56,7,FALSE）”，按回车键，得到总分；
单击B9单元格，在其中输入“=VLOOKUP（$B$2,成绩统计!
$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函数快速填入部门名称
这学期，我们单位正在申报省部级重点技工学校和市级文明单位，各部门都在积极准备资料。
在准备过程中，经常要输入各种表格，进行数据的处理。
大量的表格中都需要填入教职员工所在部门的名称，这对于一个有一百多名老师的学校，是一件非常繁琐的工作。
在工作中，我发现了一个“偷懒”的办法，现在把它写出来，和大家一起分享！
下面我就以Excel2000中的一个“学校职工情况表”为例，来告诉大家如何利用Excel函数快速填入部门名称。
如图1所示，要求填入相应部门代号后的所在部门名称。
1.选取需要填入所在部门的范围F4:
F118。
2.单击格式工具栏上的[插入函数]按钮，接着在“粘贴函数”对话框中的“函数分类”栏选择“查找与引用”，“函数名”选择“LOOKUP”，单击[确定]按钮（如图2）。
3.在弹出的“选定参数”对话框中选择参数组合方式，单击[确定]按钮。
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工作表时，需要向下或向上滚动屏幕，而表头也相应滚动，不能在屏幕上显示，这样我们搞不清要编辑的数据对应于表头的那一个信息。
按下列方法可将表头锁定，使表头始终位于屏幕可视区域。
首先选定要锁定的表头，如果我们要将图中表头（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中选中“所在
升级会员 