excel实用技巧.docx
- 文档编号:30692222
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:28
- 大小:2.86MB
excel实用技巧.docx
《excel实用技巧.docx》由会员分享,可在线阅读,更多相关《excel实用技巧.docx(28页珍藏版)》请在冰豆网上搜索。
excel实用技巧
一.常用的excel快捷键大全
一、处理工作表的快捷键总结
1、插入新工作表 Shift+F11或Alt+Shift+F1
2、移动到工作簿中的下一张工作表 Ctrl+PageDown
3、移动到工作簿中的上一张工作表 Ctrl+PageUp
4、选定当前工作表和下一张工作表 Shift+Ctrl+PageDown
5、取消选定多张工作表 Ctrl+PageDown
6、选定其他的工作表 Ctrl+PageUp
7、选定当前工作表和上一张工作表 Shift+Ctrl+PageUp
8、对当前工作表重命名 Alt+OHR
9、移动或复制当前工作表 Alt+EM
10、删除当前工作表 Alt+EL
二、工作表内移动和滚动的快捷键总结
1、向上、下、左或右移动一个单元格 箭头键
2、移动到当前数据区域的边缘 Ctrl+箭头键
3、移动到行首 Home
4、移动到工作表的开头 Ctrl+Home
5、移动到工作表的最后一个单元格,位于数据中的最右列的最下行 Ctrl+End
6、向下移动一屏 PageDown
7、向上移动一屏 PageUp
8、向右移动一屏 Alt+PageDown
9、向左移动一屏 Alt+PageUp
10、切换到被拆分的工作表中的下一个窗格 Ctrl+F6
11、切换到被拆分的工作表中的上一个窗格 Shift+F6
12、滚动以显示活动单元格 Ctrl+Backspace
13、弹出“定位”对话框 F5
14、弹出“查找”对话框 Shift+F5
15、查找下一个 Shift+F4
16、在受保护的工作表上的非锁定单元格之间移动 Tab
三、在选定区域内移动
1、在选定区域内从上往下移动 Enter
2、在选定区域内从下往上移动 Shift+Enter
3、在选定区域中从左向右移动。
如果选定单列中的单元格,则向下移动 Tab
4、在选定区域中从右向左移动。
如果选定单列中的单元格,则向上移动 Shift+Tab
5、按顺时针方向移动到选定区域的下一个角 Ctrl+句号
6、在不相邻的选定区域中,向右切换到下一个选定区域 Ctrl+Alt+向右键
7、向左切换到下一个不相邻的选定区域 Ctrl+Alt+向左键
四、以“结束”模式移动或滚动
1、打开或关闭“结束”模式 End
2、在一行或一列内以数据块为单位移动 End+箭头键
3、移动到工作表的最后一个单元格,在数据中所占用的最右列的最下一行中 End+Home
4、移动到当前行中最右边的非空单元格 End+Enter
五、在ScrollLock打开的状态下移动和滚动
1、打开或关闭ScrollLock ScrollLock
2、移动到窗口左上角的单元格 Home
3、移动到窗口右下角的单元格 End
4、向上或向下滚动一行 向上键或向下键
5、向左或向右滚动一列 向左键或向右键
六、选定单元格、行和列以及对象
1、选定整列 Ctrl+空格键
2、选定整行 Shift+空格键
3、选定整张工作表 Ctrl+A
4、在选定了多个单元格的情况下,只选定活动单元格 Shift+Backspace
5、在选定了一个对象的情况下,选定工作表上的所有对象 Ctrl+Shift+空格键
6、在隐藏对象、显示对象和显示对象占位符之间切换 Ctrl+6
七、选定具有特定特征的单元格
1、选定活动单元格周围的当前区域 Ctrl+Shift+*(星号)
2、选定包含活动单元格的数组 Ctrl+/
3、选定含有批注的所有单元格 Ctrl+Shift+O(字母O)
4、在选定的行中,选取与活动单元格中的值不匹配的单元格 Ctrl+\
5、在选定的列中,选取与活动单元格中的值不匹配的单元格 Ctrl+Shift+|
6、选取由选定区域中的公式直接引用的所有单元格 Ctrl+[(左方括号)
7、选取由选定区域中的公式直接或间接引用的所有单元格 Ctrl+Shift+{(左大括号)
8、选取包含直接引用活动单元格的公式的单元格 Ctrl+](右方括号)
9、选取包含直接或间接引用活动单元格的公式的单元格 Ctrl+Shift+}(右大括号)
10、选取当前选定区域中的可见单元格 Alt+;(分号)
八、扩展选定区域
1、打开或关闭扩展模式 F8
2、将其他区域的单元格添加到选定区域中,或使用箭头键移动到所要添加的区域的起始处,然后按“F8”和箭头键以选定下一个区域 Shift+F8
3、将选定区域扩展一个单元格 Shift+箭头键
二.教你做表格----动画效果
excel教程集珍藏版,简单明了,也比较实用。
1、Excel表格边框设置
2、自动筛选
3、在Excel中字符替换
4、在Excel中为导入外部数据
5、在Excel中行列快速转换
6、在Excel中添加说明文字
7、在Excel中添加多个视图
8、在Excel中建立图表
9、在Excel中插入超级链接
10、给Excel数据表添加页眉页脚
11、Excel自定义输入数据下拉列表
12、Excel“公式求值”功能
13、Excel中文日期格式
14、Excel中特殊符号的输入
15、Excel工作簿加密保存
三EXCEL图形和图表编辑技巧
Excel的重要功能之一就是能快速方便地将工作表数据生成柱状、圆饼、折线等分析图形。
要想在Web发布这类以及用于报表装饰示意类图形,首先单击“工具→选项”命令,在“常规”中选择“Web选项”,选择是否采用便携网络图形格式(PNG)存储文件,以加快下载速度和减少磁存储空间,但要注意这一格式图形文件将要求浏览器支持,并非对所有浏览器都合适。
如果未选择PNG图形格式,Excel会自动选择并转换为GIF、JPG格式文件,并创建名为“文件名files”的子文件夹来保存转换过的图形。
例如,如果文件名为“dog.htm”,则Excel将创建名为“dog/files”的子文件夹。
Excel也会支持文件指定文件名,例如“image01.jpg”和“image02.jpg等。
若在Web发布时选中图表的“选择交互”选项框,则生成的Web页面将保留Excel的图形与表格数据互动的功能,即页面中显示数据表格和对应分析图形,用户如果改变表格中的数据,则对应图形随之发生改变。
但要注意的是,这一交互并不能刷新存放在服务器端的数据表数据,如果你需要刷新或修改服务器端的数据,则须利用VB等编制脚本程序。
106. 创建图表连接符
当你绘制了一些基本图表以后,经常需要用直线、虚线和箭头来连接它们,并说明图表中的关系。
Excel2002提供了真正的图表连接符,这些线条在基本形状的预设位置保持连接,当你移动基本形状时,连接符与它们一起移动,而不需要你手工绘制它们。
要创建连接符,可按以下步骤进行:
首先绘制需要连接的基本性状。
在“绘图”工具栏上单击“自选图形”按钮,选择“连接符”,并选中需要使用的连接符类型。
鼠标指针将变成带有4条放射线的方形,当鼠标停留在某个形状上,形状上预先定义的连接点将变成边界上彩色的点,单击希望连接符连接的点。
然后在另一形状的连接点上重复这个过程。
107. 将Excel单元格转换成图片形式插入到Word中
假如要把Excel中某些单元格区域转换成图片形式,可先选中要进行转换的单元格区域,然后按住Shift键,单击“编辑→复制图片”命令,在弹出的“复制图片”对话框中,可选择“如屏幕所示”和“如打印效果”两种显示方式(图1),如果选择“如屏幕所示”,还可以进一步选择“图片”和“位图”两种格式。
在这里选择“如屏幕所示”和“图片”单选按钮,单击“确定”按钮。
然后进入Word中,单击“编辑→粘贴”命令,即可将选中的Excel单元格区域以图片形式粘贴过来。
如果用户没用添加表格框线,那么选择“如打印效果”后,在进行“粘贴图片”操作后图片中没有边框;如果选择“如屏幕所示”选项,“粘贴图片”操作后,图片会有和屏幕显示一样的边框。
108. 将Word内容以图片形式插入到Excel表格中
如果要把Word中的某一段内容以图片形式插入到Excel表格中,可按照如下步骤进行:
首先在Word中选中要复制的内容,然后单击“编辑→复制”命令,进入Excel中,按住Shift键,单击“编辑→粘贴图片”命令,即可将刚才复制的内容以图片格式插入到Excel表格中,而且在该图片上双击,还可进行文字修改。
利用这种方法也可以把“记事本”等其他程序中的内容以图片格式粘贴到Excel中。
109. 将Word中的内容作为图片链接插入Excel表格中
首先在Word中选中要复制的内容,然后单击“编辑→复制”命令,进入Excel中,按住Shift键,单击“编辑→粘贴链接图片”命令可将选中内容作为一个图片链接插入Excel中。
110. 在独立的窗口中处理内嵌式图表
在某些情况下,你可能希望在独立的窗口中处理内嵌式图表,例如,一个图表比工作表窗口要大的话,那么在它自己的窗口中处理它将更容易、更灵活。
要在一个窗口中处理图表,可在图表区单击鼠标右键,并从快捷菜单中选择“图表窗口”。
111. 在图表中显示隐藏数据
通常,Excel不对隐藏单元格的数据制图。
但是,你也许不希望隐藏的数据从图表中消失,可以这样操作:
首先激活图表,单击“工具→选项→图表”,在“图表”标签中取消选择“只绘制可见单元格数据”复选框()。
要注意的是,“只绘制可见单元格数据”只适用于激活的图表,因此,你在进行这个操作之前,必须激活图表,否则“只绘制可见单元格数据”选项是不可选的。
112. 在图表中增加文本框
只要你愿意,你可以在图表中的任何地方增加能够移动的文本内容(不限于标题)。
方法为:
选定图表除标题或数据系列外的任何部分,然后在编辑栏中键入文本内容,接着按回车键,这样,图表中就自动生成包含键入内容的文本框,你可以把它移到任何地方并根据自己的喜好进行格式化。
113. 建立文本与图表文本框的链接
在工作表的空白单元格内输入要链接的文本,单击选中图表,在编辑栏输入等号,然后单击包含要链接文本的单元格,接着按回车键,该文本就出现在图表中的某个位置上了。
这样,不管什么时候工作表单元格内的文本发生变化时,图表内的文本也随着改变。
但要注意的是,一旦你取消了选中文本框,就很难再用鼠标选中该文本框进一步地修改,此时你可以使用我们前面介绍过的图表元素选取方法,即使用箭头键移动选择图表元素。
114. 给图表增加新数据系列
有时我们需要对已创建好的图表增加新的数据系列,虽然你可以重新创建包含新数据系列的图表,但对已经存在的图表增加新数据系列显得更为简单、方便。
方法一:
使用“数据源”对话框激活图表,单击“图表→源数据→系列”,单击“添加”按钮,在“名称”栏中指定数据系列的名称,在“值”栏中指定新的数据系列,单击“确定”按钮即可()。
方法二:
使用“选择性粘贴”对话框选择要增加的数据系列并将其复制到剪贴板上,然后激活图表,单击“编辑”菜单中的“选择性粘贴”命令,出现“选择性粘贴”对话框,选择添加单元格为“新系列”,并选择合适的数值轴,然后单击“确定”按钮即可。
方法三:
拖动鼠标法选择要增加为新数据系列的单元格区域,鼠标指针指向区域的边框,把它拖到图表中。
当你松开鼠标按钮的时候,图表中就增加了新的数据系列。
注意:
这一方法仅对内嵌式图表起作用。
方法四:
使用“添加数据”对话框激活图表,单击“图表→添加数据”命令,然后选择要增加为新数据系列的单元格区域,单击“确定”按钮即可。
115. 快速修改图表元素的格式
通常,我们通过使用“格式”菜单或者选定图表元素后单击鼠标右键,从快捷菜单中选择“格式”命令来对图表元素进行格式化。
其实还有快速的方法:
双击图表元素,将会调出此图表元素的格式对话框。
根据选择的图表元素不同,此对话框会有所不同。
四.Excel打造计算准确美观大方工资条
企业的财务管理中总是少不了工资计算和工资条的设计,如果能够掌握Excel设计工资条的方法和技巧,即可事半功倍地快速设计好计算准确又美观大方的工资条,否则不仅效率低下而且无法保证准确性。
工资条的特点分析
首先让我们看看工资条都有些什么特点。
1.工资条头可能一行,也可能两行,根据不同企业工资栏目需求而定。
但相同处是每一项条目(或者一个工人的工资信息)具有一个条头,条头具有指定数目的重复性。
2.每一项条目中间有一个空行,方便裁剪。
根据以上特点,我们可以初步拟定工资条头部制作方案:
首先建立“工资明细表”,用于储存员工工资表信息,也用于平时编辑和汇总。
其次建立“工资条目表”,用于引用工资信息,同时产生工资条形式之工资目。
需要用到的函数可以因人而异,有很多公式都可以达成目的,但最后选择目标是效率最高又易于理解者。
现在,我们通过两个实例向大家详解工资条头的Excel设计过程,并分析其中技巧,希望望大家能从中受益,举一反三,设计出适合各自需求的Excel工资计算公式。
单行工资条头之设计
先看下面的数据,这是一个简易的单行条头工资信息摘录(数据随机生成,非实际工资),见图1。
利用此表数据,我们可以通过函数公式来引用数据,生成需要的工资条形式。
步骤与公式如下:
1.进入“单行表头工资条”工作表。
2.选中单元格B1,输入以下公式:
=CHOOSE(MOD(ROW(),3)+1,“”,单行表头工资明细!
A$1,OFFSET(单行表头工资明细!
A$1,ROW()/3+1,))
3.选中单元格B1,鼠标置于单元格右下角,当箭头变成十字形时(图2)
则向右拉至J1单元格。
然后再选中B1:
J1向下拉,直至公式结果出现空白。
此时工资条效果见图3。
4.基本达到目的了,但表格还没有边框,打印则不太美观。
现在为它加上边框:
选中B1:
J2,并点击工具栏边框按钮中的田字形按钮添加边框;再点击大方框按钮(图4),使工资表条目四周边框显示粗一些,比较美观。
5.选择B1:
J3单元格,向下拖拉,直至有数据的最后一行。
最后效果见图5。
公式解释:
=CHOOSE(MOD(ROW(),3)+1,“”,单行表头工资明细!
A$1,OFFSET(单行表头工资明细!
A$1,ROW()/3+1,))
1.CHOOSE函数是选择函数,支持30个参数。
第一个参数是选择序号(数值),其余参数是被选值。
第一个参数是N则结果返回其余参数中第N个数值。
2.MOD函数是求余数函数,支持两个参数,第一个参数是被除数,第二个参数是除数,结果返回余数。
3.ROW函数是返回指定行行号之函数,若省略参数时则返回当前行。
4.OFFSET函数是返回偏移量之函数。
支持五5参数,分别是[参照区域]、[行数]、[列数]、[高度]、[宽度]。
5.“”表示空白,返回空。
本公式巧妙动用MOD和ROW函数产生一个循环的序列2/3/1/2/3/1/2/3/1,再通过CHOOSE函数参数的变化动态地引用工资明细表的数据,其中“”的作用是当前行行号为3的倍数时返回空,从而产生一个空白行,方便制作工资条后裁剪。
当然,实现功能还有很多公式,如用以下IF函数实现等,各位用户自己去多摸索吧:
=IF(MOD(ROW(),3)=1,单行表头工资明细!
A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!
A$1,ROW()/3+1,0),“”))
双行工资条头设计
先看数据如图6。
双行条头工资条和单行条头实现方法基本相同,仅仅是公式有些差异。
现暂列如下:
=CHOOSE(MOD(ROW(),4)+1,“”,双行表头工资明细!
A$1,双行表头工资明细!
A$2,OFFSET(双行表头工资明细!
A$1,ROW()/4+2,))
输入公式后向后拉再向下拉至末行,然后通过前面介绍的方法设置边框,其中部分单元格需去掉左边框或者右边框,使之显得美观。
最后结果如下见图7。
五.Excel五分钟校对学生考试成绩
老师们经常用Excel制作学习考试成绩统计表,在数据录入中难免会出错,只好对对学生考试成绩进行加班校对。
其实不必这么辛苦,只需要用到Excel强大的计算能力,五分钟就能将所有学生的成绩校对完毕,让辛勤教师们有更多的时间休息。
Step1:
添加学生校对成绩栏
打开学生成绩表所在的Excel文件,根据教育部门有关规定,所有学生成绩必须要经过录入和审核两道程序,因此这里采用两次录入法进行校对。
在“语文”成绩所在列B后插入一列,将其名称更改为“语文校对”,然后在该列中再次录入语文成绩,以符合规定要求。
添加校对栏
Step2:
设置好校对方式及背景颜色
选择C2区,点击“格式→条件格式”,将选项依次设定为“单元格数值”、“不等于”、“=B2”,再在“单元格格式”中选择“图案”,并于“单元格底纹颜色”下选取红色,“确定”后返回。
经过这样设置后,如果单元格C2与单元格B2数值不相等,则单元格C2的底纹变为红色,若相等,则不会有任何变化。
设置校对方式
Step3:
根据背景,手动更改误录成绩
点击Excel工具栏中的“格式刷”,选择列字母“C”,使这列中的所有单元格格式与C2相同。
如果第二次与第一次成绩录入不相同的部分会以红色底纹显示,相同的单元格就不会有任何变化。
此时将录入数据有差别的地方修正即可。
更改误录成绩
Step4:
删除校对列,完成成绩校对
在经过前面的步骤后,学生所有成绩均校对完毕,此时我们需要将校对好的Excel文档予以保存,方便做成报表。
右键点击校对成绩列的英文字母,如“语文校对”所对应的“C”列,右键点击,选择“删除”即可,按照该步骤,将所有校对列删除完毕后,学生成绩报表就做好了。
完成成绩校对
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 实用技巧