Excel高级教程收藏.docx
- 文档编号:11224141
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:111
- 大小:1.89MB
Excel高级教程收藏.docx
《Excel高级教程收藏.docx》由会员分享,可在线阅读,更多相关《Excel高级教程收藏.docx(111页珍藏版)》请在冰豆网上搜索。
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中一样。
若是需要上报的“汇总表”中还需输入许多其他文字,如安在“汇总表”工作表中插入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格)那么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中日期与时刻的快速处置
一、任意日期与时刻的输入数字键与“/”或“-”配合可快速输入日期,而数字键与“:
”配合可输入时刻:
如输入“3/25”,然后回车即可取得“3月25日”。
又如输入“9:
25”,回车即取得“09:
25”。
二、当前日期与时刻的快速输入选定要插入的单元格,按下“Ctrl”键与分号键“;”,然后回车即可插入当前日期。
而要输入当前时刻,同时按住“Ctrl”键、“Shift”键与
分号键,然后回车即可。
3、日期与时刻格式的快速设置若是对日期或时刻的格式不中意,能够右击该单元格,选定“设置单元格格式→数字→日期”或“时刻”,然后在类型框当选择即可。
(出处:
赛迪网--中国电脑教育报)
利用Excel的函数和挑选功能巧妙分班
一、处置思路
学校大多依照学习成绩进行分班,一样是按总分名次来划分的:
比如要分8个班,那么第1名分到一班、第2名分到二班……第8名分到八班,接着第9名分到八班、第10名分到七班……第16名分到一班。
2、划分班级
先将学生情形及成绩调入Excel工作表中,按总分降序排列(假设总分在H列,第一名在第三行),在总排列的右一列(即I列)从I3单元格开始向下填充一、二、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(所谓数据行,指表头除外的记录行),回到一般视图下,在表的右边空列中,从第一数据行开始填充自然数序列一、二、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
图2一、实此刻单元格中分行的成效:
咱们第一在单元格中输入“性别”,这时按回车键的话,光标就会转到其他单元格中去了,因此必需按住“Alt”键的同时按回车键,光标转到下一行,接着输入“姓名”,然后再在“性别”前加入假设干空格键,即可达到如图2的成效。
图3二、在单元格中设置斜线:
选择单元格,在右键菜单当选择“设置单元格格式”,在显现的对话框当选择“边框”标签,进行斜线的设置(如图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,成绩统计!
$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函数快速填入部门名称
这学期,咱们单位正在申报省部级重点技工学校和市级文明单位,各部门都在踊跃预备资料。
在预备进程中,常常要输入各类表格,进行数据的处置。
大量的表格中都需要填入教职员工所在部门的名称,这关于一个有一百多名教师的学校,是一件超级繁琐的工作。
在工作中,我发觉了一个“偷懒”的方法,此刻把它写出来,和大伙儿一路分享!
下面我就以Excel2000中的一个“学校职工情形表”为例,来告知大伙儿如何利用Excel函数快速填入部门名称。
如图1所示,要求填入相应部门代号后的所在部门名称。
图1
1.选取需要填入所在部门的范围F4:
F118。
2.单击格式工具栏上的[插入函数]按钮,接着在“粘贴函数”对话框中的“函数分类”栏选择“查找与引用”,“函数名”选择“LOOKUP”,单击[确信]按钮(如图2)。
图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.单击下拉列表中的任意一个“部门代号”,例
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 高级 教程 收藏
![提示](https://static.bdocx.com/images/bang_tan.gif)