Excel教程合集应用技巧Word文档格式.docx
《Excel教程合集应用技巧Word文档格式.docx》由会员分享,可在线阅读,更多相关《Excel教程合集应用技巧Word文档格式.docx(37页珍藏版)》请在冰豆网上搜索。
6、<
Ctrl+Shift+End>
快速从当前单元格选取数据直至报表右下角区域
7、<
Ctrl+Shift+Home>
快速从当前单元格选取数据直至报表左上角区域
8、<
Ctrl+Shift+方向键>
快速选中整行、整列、多行、多列数据区域
9、<
Shift+Home>
从当前单元格快速向左选取行数据直至报表最左侧单元格
10、<
Shift+PageUp>
从当前单元格快速向上选取列数据直至报表最上方单元格
动图
2、格式转换秘技(数值、日期、时间、货币、百分比)
你是否遇到过接收的报表格式错乱,或者从系统中下载的报表很多格式都需要一一调整的需求呢?
比如下图,要调整报表中数值、日期、时间、货币、百分比的格式。
今天我教你一招:
快速设置格式(数值、日期、时间、货币、百分比)各种格式调整,1秒搞定!
!
介绍下设置步骤:
1、选中单元格区域,按下<
Ctrl+Shift+`>
组合键,快速设置常规的默认格式。
2、选中单元格区域,按下<
Ctrl+Shift+1>
组合键,快速设置带千分符的数值格式。
3、选中单元格区域,按下<
Ctrl+Shift+2>
组合键,快速设置为h:
mm的时间格式。
4、选中单元格区域,按下<
Ctrl+Shift+3>
组合键,快速设置为年月日的日期格式。
5、选中单元格区域,按下<
Ctrl+Shift+4>
组合键,快速设置为带人民币符号的货币格式。
6、选中单元格区域,按下<
Ctrl+Shift+5>
组合键,快速设置为带百分号的百分比格式。
这样,原本繁琐重复的格式设置,瞬间就可以一键搞定啦,既快捷又准确!
3、按员工名单创建并命名1000个文件夹
如果要批量创建很多文件夹,而且要按照规定的名称命名,你知道怎么搞定吗?
比如领导让你按员工名单创建并命名1000个文件夹!
你手动折腾两三天,我只需1分钟就搞定!
按名单批量创建并命名文件夹!
1、首先在员工名单表基础上利用函数公式,批量生成批处理命令,公式=“md”&
A2
(注意md后面有个空格,不要丢了哦)
2、将在Excel中生成的命令列(如B列)复制到txt文件里
3、更改txt文件的后缀为.bat,将其转换为批处理文件
4、双击执行批处理文件,实现批量生成文件夹
4、智能填充合并单元格
合并单元格给正常的数据处理和统计带来诸多困扰,很多情况下,我们都需要将合并单元格取消合并,再逐一填充,可如果要处理的合并单元格很多,一个个手动搞就太麻烦了,有更好的办法吗?
1、选中合并单元格所在区域,单击【取消合并】按钮。
2、按<
Ctrl+G>
组合键,打开定位对话框,定位条件选【空值】按钮。
3、输入公式,等于当前活动单元格的上一个单元格(如视频中活动单元格为A3,公式为=A2)
4、按<
Ctrl+Enter>
组合键,批量填充公式。
5、选中填充公式区域,将公式结果转换为值显示。
5、将公式结果转化为值
1、复制公式所在单元格区域。
2、单击鼠标右键,从弹出的快捷菜单【粘贴选项】中单击【123】按钮。
这样就实现了将公式结果转换为值保存。
6、巧用序列填充!
让你填充从1到888888的数字你会怎么做?
设置步骤:
1、选中要输入序列的起点单元格(如A2)
2、单击【开始】-【填充】-【序列】
3、在【序列】对话框中设置序列产生在【列】,【等差序列】,步长值从1到888888,单击【确定】按钮。
这时,从1到888888的序号就自动批量生成啦!
好啦,这就搞定了!
7、一次性清除数值,同时保留公式
无论是你接到别人发来的模板,还是要把模板发给别人,都经常需要仅清除数据区域,还保留公式区域对吧?
比如当你遇到下面这种报表模板:
上图的报表中绿色区域是填写数值的地方,黄色区域是公式生成。
90%以上的白领都会一个个选中绿色的8块区域,分别清除,然后重新填制模板。
当然,你可以说,我会按住Ctrl键不放,依次选中8个绿色区域,一次性清除数据,这样简单吧?
我来教你一招,无论多大、多复杂的报表,一次性清除数值,同时保留公式不被破坏!
介绍一下用到的技巧和原理:
1、连续数据区域快速选定,使用<
2、按条件定位目标数据,使用<
或者F5功能键调出定位对话框
3、根据定位条件勾选对应选项,批量定位目标区域
4、Delete键清除数值数据。
好啦!
再有这种烦人的报表,你就不用怕了,记得学以致用哦!
8、快速在每条数据行上方批量插入标题行
方法1:
使用条件定位方法
工作中有时会遇到在每条数据行上方插入标题行的需求有木有?
比如你要打印工资条、你要打印分发成绩条......
1、首先在数据源右侧创建辅助列(如视频所示)
(在H3、J4输入1,选中H3:
J4,双击右下角+号,相当于往下拉,如上图)
打开定位对话框-定位条件-常量-确定
3、在任意辅助列常量上单击鼠标右键-插入-插入整行
这时候可以看到,已经实现了在每行数据下面插入了空行。
4、选中标题行复制-选中下方区域
5、再次利用定位选中空行区域
6、按Enter键,批量完成标题行的粘贴。
好啦,这就搞定了,批量插入标题行的技巧不但酷炫,而且简单快捷吧!
方法2:
使用简单的排序方法(此技巧对于期末考试打印桌面信息条非常有用)
1、首选创建辅助列,复制行号到最右侧(如视频所示)。
2、复制标题行后,批量填充至下方的空白区域,复制区域与辅助列长度保持一致。
3、根据辅助列升序排列,点击【数据】-【升序排列】
这样瞬间就可以一次性搞定多个标题行的定位插入啦,既快捷又准确!
9、快速提取表内所有图片
如果表格内包含的图片很多,你还要手动一个个复制出来那就太费劲啦,有好方法一次性搞定吗?
步骤:
1、修改文件后缀名为rar。
2、打开rar压缩包,找到xl文件夹下的media子文件夹。
3、将media文件夹内的图片复制到你想要放置图片的位置。
10、一键快速插入或编辑批注
选中单元格,按<
Shift+F2>
组合键,这样,不但可以快速插入批注,还可以编辑已有批注!
11、数据源记录和统计行混杂的表格的汇总方法
比如下面这种:
当你不幸遇到这种表,又不能修改结构,需要在黄色区域对上面的数据进行汇总时,你会怎么办呢?
如果要手动一个个搞,由于每组小计要汇总的行数不同,每次都要重新写公式,当数据源大的时候,简直要崩溃的节奏!
介绍一下这里用到的几个关键技巧
1、定位功能,可以用F5或者<
调出定位对话框
2、定位空值,可以批量选中那些间隔不一致的黄色小计行
3、智能求和,快捷键是<
Alt+=>
,可以实现按照上方数据行数自动填充SUM公式进行求和
12、提取不重复值、统计不重复值个数(多图附案例)
工作中经常需要处理不重复值,包括提取不重复值列表、统计不重复值的个数,甚至更复杂的情况下会遇到按条件统计不重复值的个数......
比如按照产品统计不重复用户的数量,下面就结合一个实际案例来介绍。
上图中A列和B列是数据源区域,要根据产品的用户使用记录提取不重复产品列表,并统计不重复用户数量。
下面给出两种解决方案:
方案1、用“删除重复项”结合数据透视表搞定。
(方法一比较简单便捷,容易掌握)
先利用【数据】-【删除重复项】,同时勾选“产品”和“用户”作为筛选条件删除重复值,再创建数据透视表,将产品放置在数据透视表的行区域,将用户放置在数据透视表的值区域,如下图所示。
方案2、使用函数公式来一步搞定。
D2输入以下数组公式,按<
Ctrl+Shift+Enter>
组合键结束输入,并向下填充。
=INDEX(A:
A,SMALL(IF(MATCH(A$2:
A$14,A$2:
A$14,)=ROW($2:
$14)-1,ROW($2:
$14),4^8),ROW(A1)))&
"
E2输入以下公式,并向下填充。
=IF(D2="
"
SUMPRODUCT((MATCH(A$2:
A$14&
B$2:
B$14,A$2:
B$14,)=ROW($2:
$14)-1)*(A$2:
A$14=D2)))
13、用数据透视表做统计
下面我用一个简单的例子给大家介绍下(本文使用的是MSOFFICE2007):
假设我有个表格,要按月将不同列分别作统计
步骤如下:
点击“插入”-“数据透视表”
点击图示中的按钮,选择数据透视表要统计的数字区域,另外在下面创建数据透视表位置的地方,我们选”新工作表“
选的时候要注意,把最上面的标题行也选进去
选好之后,点击图示中按钮回到之前的界面
点击确定
这个时候在原来SHEET的左面就会出现一个新的SHEET,在这个新的SHEET的最右面,可以看到刚才的几个字段名
接下来,因为我们要按日期统计,所以把”日期“直接拖到行标签,把其他几个字段拖到数值里面
这个时候左面的表格里就是这样了
下面我们要按月统计,选中行标签里任意一行
点击最上面菜单栏的”选项“-”将字段分组“
由于是按月统计,所以选中”月“,点确定
现在就是我们要的结果了
本例中做的是按月求和,如果你要计数或者求平均值,可以点击对应的列,选择”值字段设置“
选择你要的统计方式,再点”确定“就可以了。
14、乾坤大挪移!
快速行列转置
1、首先<
选中整个报表区域(如视频所示)。
Alt+C>
复制
3、将鼠标定位到放置新布局报表的位置(如H1),按<
Ctrl+Alt+V>
打开选择性粘贴对话框。
4、勾选【转置】复选框,单击【确定】按钮。
15、合并计算汇总多工作表数据
工作中经常遇到很多结构一致的表格,但是数据源分散在不同的工作表中,这时候需要你把所有表格汇总在一起,你怎么做呢?
可以使用公式进行合并计算,如:
=sum(’sheet1:
sheet3’!
B1),但是必须是连续的表格才行。
合并计算汇总多工作表数据的方法的原理:
(这三个表的姓名排列不一样)
1、数据-合并计算,打开合并计算对话框
2、在引用位置中,依次选中多个工作表中要汇总的单元格区域,点击添加
3、根据需要勾选“首行”和“最左列”,区别大家自己试试就知道了
4、单击确定按钮,合并计算就完成啦!
这个过程中要说明的一点是:
每个分工作表中的汇总字段是否相同,决定着合并计算以后的结果是合并在一列还是分别放置在多列。
这个例子中,三张分工作表的数据字段名不同,分别是“一模成绩”、“二模成绩”、“三模成绩”,所以合并计算结果是分别在三列中显示。
如果数据源中的三张分工作表的数据字段名相同,比如都是“成绩”,那么合并计算结果就合并在一列显示了。
合并计算是很简单实用的汇总多表的工具,如果遇到再复杂的表格,