Excel教程合集应用技巧.docx
《Excel教程合集应用技巧.docx》由会员分享，可在线阅读，更多相关《Excel教程合集应用技巧.docx（37页珍藏版）》请在冰点文库上搜索。
Excel教程2016合集（应用技巧）
1、Excel定位&跳转快捷键
1、快速选中全表
2、快速跳转到指定方向
3、 快速跳转到报表末尾
快速跳转到报表开头
4、快速选中非连续单元格
5、快速选中两次点选单元格之间的矩形报表区域
6、快速从当前单元格选取数据直至报表右下角区域
7、快速从当前单元格选取数据直至报表左上角区域
8、快速选中整行、整列、多行、多列数据区域
9、从当前单元格快速向左选取行数据直至报表最左侧单元格
10、从当前单元格快速向上选取列数据直至报表最上方单元格
动图
2、格式转换秘技（数值、日期、时间、货币、百分比）
你是否遇到过接收的报表格式错乱，或者从系统中下载的报表很多格式都需要一一调整的需求呢？
比如下图，要调整报表中数值、日期、时间、货币、百分比的格式。
今天我教你一招：
快速设置格式（数值、日期、时间、货币、百分比）各种格式调整，1秒搞定！
！
介绍下设置步骤：
1、选中单元格区域，按下组合键，快速设置常规的默认格式。
2、选中单元格区域，按下组合键，快速设置带千分符的数值格式。
3、选中单元格区域，按下组合键，快速设置为h:
mm的时间格式。
4、选中单元格区域，按下组合键，快速设置为年月日的日期格式。
5、选中单元格区域，按下组合键，快速设置为带人民币符号的货币格式。
6、选中单元格区域，按下组合键，快速设置为带百分号的百分比格式。
这样，原本繁琐重复的格式设置，瞬间就可以一键搞定啦，既快捷又准确！
3、按员工名单创建并命名1000个文件夹
如果要批量创建很多文件夹，而且要按照规定的名称命名，你知道怎么搞定吗？
比如领导让你按员工名单创建并命名1000个文件夹！
你手动折腾两三天，我只需1分钟就搞定！
！
今天我教你一招：
按名单批量创建并命名文件夹！
介绍下设置步骤：
1、首先在员工名单表基础上利用函数公式，批量生成批处理命令，公式=“md”&A2
（注意md后面有个空格，不要丢了哦）
2、将在Excel中生成的命令列（如B列）复制到txt文件里
3、更改txt文件的后缀为.bat，将其转换为批处理文件
4、双击执行批处理文件，实现批量生成文件夹
4、智能填充合并单元格
合并单元格给正常的数据处理和统计带来诸多困扰，很多情况下，我们都需要将合并单元格取消合并，再逐一填充，可如果要处理的合并单元格很多，一个个手动搞就太麻烦了，有更好的办法吗？
介绍下设置步骤：
1、选中合并单元格所在区域，单击【取消合并】按钮。
2、按组合键，打开定位对话框，定位条件选【空值】按钮。
3、输入公式，等于当前活动单元格的上一个单元格（如视频中活动单元格为A3，公式为=A2）
4、按组合键，批量填充公式。
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，双击右下角+号，相当于往下拉，如上图）
2、按打开定位对话框-定位条件-常量-确定
3、在任意辅助列常量上单击鼠标右键-插入-插入整行
这时候可以看到，已经实现了在每行数据下面插入了空行。
4、选中标题行复制-选中下方区域
5、再次利用定位选中空行区域
6、按Enter键，批量完成标题行的粘贴。
好啦，这就搞定了，批量插入标题行的技巧不但酷炫，而且简单快捷吧！
方法2：
使用简单的排序方法（此技巧对于期末考试打印桌面信息条非常有用）
介绍下设置步骤：
1、首选创建辅助列，复制行号到最右侧（如视频所示）。
2、复制标题行后，批量填充至下方的空白区域，复制区域与辅助列长度保持一致。
3、根据辅助列升序排列，点击【数据】-【升序排列】
这样瞬间就可以一次性搞定多个标题行的定位插入啦，既快捷又准确！
9、快速提取表内所有图片
如果表格内包含的图片很多，你还要手动一个个复制出来那就太费劲啦，有好方法一次性搞定吗？
步骤：
1、修改文件后缀名为rar。
2、打开rar压缩包，找到xl文件夹下的media子文件夹。
3、将media文件夹内的图片复制到你想要放置图片的位置。
10、一键快速插入或编辑批注
选中单元格，按组合键，这样，不但可以快速插入批注，还可以编辑已有批注！
11、数据源记录和统计行混杂的表格的汇总方法
比如下面这种：
当你不幸遇到这种表，又不能修改结构，需要在黄色区域对上面的数据进行汇总时，你会怎么办呢？
如果要手动一个个搞，由于每组小计要汇总的行数不同，每次都要重新写公式，当数据源大的时候，简直要崩溃的节奏！
介绍一下这里用到的几个关键技巧
1、定位功能，可以用F5或者调出定位对话框
2、定位空值，可以批量选中那些间隔不一致的黄色小计行
3、智能求和，快捷键是，可以实现按照上方数据行数自动填充SUM公式进行求和
12、提取不重复值、统计不重复值个数（多图附案例）
工作中经常需要处理不重复值，包括提取不重复值列表、统计不重复值的个数，甚至更复杂的情况下会遇到按条件统计不重复值的个数......
比如按照产品统计不重复用户的数量，下面就结合一个实际案例来介绍。
上图中A列和B列是数据源区域，要根据产品的用户使用记录提取不重复产品列表，并统计不重复用户数量。
下面给出两种解决方案：
方案1、用“删除重复项”结合数据透视表搞定。
（方法一比较简单便捷，容易掌握）
先利用【数据】-【删除重复项】，同时勾选“产品”和“用户”作为筛选条件删除重复值，再创建数据透视表，将产品放置在数据透视表的行区域，将用户放置在数据透视表的值区域，如下图所示。
方案2、使用函数公式来一步搞定。
D2输入以下数组公式，按组合键结束输入，并向下填充。
=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:
A$14&B$2:
B$14,）=ROW（$2:
$14）-1）*（A$2:
A$14=D2）））
13、用数据透视表做统计
下面我用一个简单的例子给大家介绍下（本文使用的是MSOFFICE2007）：
假设我有个表格，要按月将不同列分别作统计
步骤如下：
点击“插入”-“数据透视表”
点击图示中的按钮，选择数据透视表要统计的数字区域，另外在下面创建数据透视表位置的地方，我们选”新工作表“
选的时候要注意，把最上面的标题行也选进去
选好之后，点击图示中按钮回到之前的界面
点击确定
这个时候在原来SHEET的左面就会出现一个新的SHEET，在这个新的SHEET的最右面，可以看到刚才的几个字段名
接下来，因为我们要按日期统计，所以把”日期“直接拖到行标签，把其他几个字段拖到数值里面
这个时候左面的表格里就是这样了
下面我们要按月统计，选中行标签里任意一行
点击最上面菜单栏的”选项“-”将字段分组“
由于是按月统计，所以选中”月“，点确定
现在就是我们要的结果了
本例中做的是按月求和，如果你要计数或者求平均值，可以点击对应的列，选择”值字段设置“
选择你要的统计方式，再点”确定“就可以了。
14、乾坤大挪移！
快速行列转置
介绍下设置步骤：
1、首先选中整个报表区域（如视频所示）。
2、按复制
3、将鼠标定位到放置新布局报表的位置（如H1），按打开选择性粘贴对话框。
4、勾选【转置】复选框，单击【确定】按钮。
15、合并计算汇总多工作表数据
工作中经常遇到很多结构一致的表格，但是数据源分散在不同的工作表中，这时候需要你把所有表格汇总在一起，你怎么做呢？
可以使用公式进行合并计算，如：
=sum（’sheet1:
sheet3’!
B1）,但是必须是连续的表格才行。
合并计算汇总多工作表数据的方法的原理：
（这三个表的姓名排列不一样）
1、数据-合并计算，打开合并计算对话框
2、在引用位置中，依次选中多个工作表中要汇总的单元格区域，点击添加
3、根据需要勾选“首行”和“最左列”，区别大家自己试试就知道了
4、单击确定按钮，合并计算就完成啦！
这个过程中要说明的一点是：
每个分工作表中的汇总字段是否相同，决定着合并计算以后的结果是合并在一列还是分别放置在多列。
这个例子中，三张分工作表的数据字段名不同，分别是“一模成绩”、“二模成绩”、“三模成绩”，所以合并计算结果是分别在三列中显示。
如果数据源中的三张分工作表的数据字段名相同，比如都是“成绩”，那么合并计算结果就合并在一列显示了。
好啦！
合并计算是很简单实用的汇总多表的工具，如果遇到再复杂的表格，而且数据源经常更新，采用数据透视表会是更适合的办法，后续课程中我们会专门讲解，你们可以小期待一下：
）
16、正确输入分数的技巧！
介绍下设置步骤：
1、比如要输入二分之一，直接输入【1/2】会默认变成日期格式，正确的输入方法应该是输入【01/2】，注意0后面有个空格哦！
这时编辑栏显示的数字是0.5
2、一又二分之一的输入方法是【11/2】，编辑栏显示的数字是1.5
3、当输入【06/5】的时候，会自动转换为【11/5】，编辑栏显示1.2
17-1、按上档键后选择单元格拖动，可移动插入单元格，而不是移动覆盖。
17-2、多工作表之间快速定位和跳转！
比如要在100张工作表中快速跳转，操作步骤：
1、快速跳转到最后一个工作表：
按住Ctrl单击工作表左下角的右三角按钮（excel2003）
2、快速跳转到第一个工作表：
按住Ctrl单击工作表左下角的左三角按钮（excel2003）
3、要查看文件中包含多少个工作表：
在左下角的三角按钮上单击鼠标右键，可展开列表查看该工作簿包含的所有工作表。
4、快速跳转到目标工作表：
在上一步的工作表列表中单击选择目标工作表，单击【确定】按钮，即可瞬间跳转到该sheet表。
17-3、批量提取文件夹内的所有文件名
领导让你提取文件夹中的1000个文件名，明早交给他！
你通宵苦逼加班！
还不敢保证完全准确。
今天我教你一招：
批量提取文件夹内的所有文件名！
只需1分钟就搞定！
快准狠！
介绍下设置步骤：
1、首先打开要提取文件名的文件夹，新建一个txt文本文件
2、编辑txt文本，输入以下批处理命令，保存文本文件。
dir*.*/b>a.txt
（提示：
这一步如果需要提取固定后缀的文件名，修改*.*为相应的后缀即可，如dir*.xls/b>a.txt）
（提示：
如果需要保存为word文档则改为dir*.*/b>a.doc，如果需要保存为excel文档则改为dir*.*/b>a.xls，）
3、更改txt文件的后缀为.bat，将其转换为批处理文件
4、双击执行批处理文件，实现批量提取文件名
17-4、按名单批量创建文件夹
1、首先在员工名单表基础上利用函数公式，批量生成批处理命令。
（公式=“md”&A2注意md后面有个空格）
2、将在Excel中生成的命令列（如B列）复制到txt文件里。
3、更改txt文件的后缀为.bat，将其转换为批处理文件
4、双击执行批处理文件，实现批量生成文件夹
17-5、用快捷键轻松在表格内快速跳转！
方法一：
快速跳转到表格最左端；快速跳转到表格最右端；快速跳转到表格最下端；快速跳转到表格最上端。
方法二：
选中任意一个单元格，将鼠标移至该单元格上边框，变成十字箭头时，双击该单元格的上边框，鼠标跳至表格最顶端；同理，双击左边框，跳至最左；双击有边框跳至最右；双击下边框，跳至最后。
17-6、区域内批量输入文本
比如要你在Excel里批量填充单元格，如下图：
介绍下设置步骤：
1、选中要批量填充的单元格区域，在第一个单元格输入文本。
2、按组合键，批量填充选中的单元格区域。
18、1个公式构建重复序号的递增序列
今天我来结合一个实际案例，介绍1个公式构建重复序号的递增序列的方法。
如下图所示（黄色区域处输入公式）
B2单元格输入以下公式，将公式向下填充。
方法1：
=QUOTIENT（ROW（A3）,3）
方法2：
=INT（ROW（A3）/3）
19、用选择性粘贴批量将文本转换为数值
工作中经常会遇到文本数字转换为真正数值的需求。
很多系统导出的数据源报表中的数字都是文本型数字，无法直接求和和创建数据透视表，需要先转换为真正的数值才能进行下面的数据处理，如果你一个个手动搞那就太费劲啦，有好方法一次性搞定吗？
介绍下设置步骤：
1、选中任意一个空单元格，按复制。
2、选中要转换为数值的文本数字所在区域，按。
3、在弹出的【选择性粘贴】对话框中单击【数值】和【加】单选按钮。
4、单击【确定】按钮。
这时，选中的所有文本数字就已经转换为真正的数值了，瞬间完成！
20、批量对数据添加前缀
工作中经常会遇到在数据前添加固定前缀的操作。
比如要在产品编码前统一添加前缀“LR”，80%以上的白领还在手动一个个敲前缀，其实有个快捷方式1秒就能搞定！
介绍下设置步骤：
1、选中产品编码列，单击组合键打开【设置单元格格式】对话框。
2、【数字】-【分类】-【自定义】里面，在原有自定义格式代码前添加前缀“LR”。
3、单击【确定】按钮，即可看到所有数据前面都增加前缀。
这时，修改的是所有数据的显示形式，真正的单元格数据本身并不变化。
21、二维交叉查询
工作中经常会遇到二维条件的数据查询需求有木有？
如下图所示
介绍下思路和步骤：
1、设置数据有效性（数据验证）实现下拉菜单，快捷选择分公司和季度
2、设置条件格式，在条件格式中结合函数公式来实现动态标识行或列颜色的功能
这个细节的具体方法，咱们后面专门开教程学习，敬请期待！
22、批量生成1至100的随机数
无论是数据测试还是验证报表公式的正确性，都需要生成随机数来辅助完成，这里介绍一个公式批量生成1至100的随机数！
介绍下设置步骤：
1、先选中要生成随机数的单元格区域。
在公示栏输入以下公式。
=RANDBETWEEN（1,100）
2、按组合键，批量填充选中的单元格区域。
3、在取消选中之前，点击公示栏，回车，没回车一次，更换一次数字。
23、快速修正不规范日期
工作中经常会遇到报表中的日期格式不规范，导致函数计算结果出错。
如果不规范的日期比较少，手动改几下就好了。
可如果是从系统导出的数据源中有几千上万个不规范日期，再去一个个手动搞，会崩溃的！
设置步骤：
1、选中不规范日期所在列的整列，单击【数据】-【分列】。
2、在【文本分列向导】对话框中单击两次【下一步】按钮，在第3步的界面中勾选【日期】单选框，单击【完成】按钮。
24、快速输入性别
工作中经常会遇到在报表中输入性别的需求。
如果员工数量比较少还算好，手动输入即可，如果要输入性别的员工很多，再去一个个手动搞，会崩溃的！
设置步骤：
1、选中需要输入性别的单元格区域，按组合键打开【设置单元格格式】对话框。
2、在【数字】选项卡下的【分类】列表中选择【自定义】选项，右侧的文本框中输入以下代码，单击【确定】按钮。
[=1]男;[=2]女
3、这时，输入1即可显示男，输入2即可显示女，你输入对应的1和2代表男和女即可。
25、快速实现报表内所有员工奖金普调500元
工作中经常会遇到在原有报表数据的基础上修改的需求。
你是否遇到过要将报表中的数据批量加一个数/减一个数，或者批量乘以/除以一个数的情形呢？
你会怎么做呢？
比如下图所示：
设置步骤：
1、选中任意空白单元格输入500，并且复制这个单元格。
2、选中要批量进行修改的数据区域，这里可以使用一些快捷键。
3、按组合键，打开【选择性粘贴】对话框。
4、勾选【数值】，勾选【加】，单击【确定】按钮。
26、自定义函数ConTxt，文本合并超牛！
要将同一类的内容，比如同一个部门的员工、同一个门店的商品等等合并到一个单元格内。
例如下图所示，需要将A列数据源中的文本内容，合并到C4单元格。
解决合并文本内容的问题，离不开著名的自定义函数Contxt。
这个函数是chenjun老师原创，最早发布在ExcelHome技术论坛，用户可以和使用工作表函数一样，对参数进行灵活的设置。
接下来说说这个自定义函数的使用方法：
步骤一：
右键单击工作表标签【查看代码】，打开VBE编辑器。
步骤二：
在VBE窗口中，点击【插入】【模块】。
步骤三：
在右侧的代码窗口中输入代码。
以下是代码文字版，大家使用时可以直接复制就好。
PublicFunctionConTxt（ParamArrayargs（）AsVariant）AsVariant
DimtmptextAsVariant,iAsVariant,cellvAsVariant
DimcellAsRange
tmptext=""
Fori=0ToUBound（args）
IfNotIsMissing（args（i））Then
SelectCaseTypeName（args（i））
Case"Range"
ForEachcellInargs（i）
tmptext=tmptext&cell
Nextcell
Case"Variant（）"
ForEachcellvInargs（i）
tmptext=tmptext&cellv
Nextcellv
CaseElse
tmptext=tmptext&args（i）
EndSelect
EndIf
Nexti
ConTxt=tmptext
EndFunction
关闭VBE窗口，回到Excel工作表界面，就可以使用这个自定义的ConTxt函数了。
除了直接使用单元格引用，还可以对参数进行条件设置。
回到本例的题目中，我们仅要求合并文本内容，可使用下面的数组公式，记得要按Ctrl+Shift+回车结束输入。
=ConTxt（IF（ISTEXT（A1:
A12）,A1:
A12&"、"，""））
先使用ISTEXT函数判断A1:
A12单元格的内容是否为文本。
再使用IF函数，如果是文本，则返回A1:
A12并连接一个顿号"、"，否则返回空文本""。
最后使用ConTxt函数对返回的内容进行连接。
连接后的文本，最后部分还多了一个顿号，大家可以根据平时学习的技巧或是函数方法，将这个顿号去掉。
使用这个自定义公式，可以非常灵活的完成多种同类项的内容合并，关键是数据源发生变化后，结果能够自动更新，一劳永逸哦。
最后提醒大家，工作簿必须保存为启用宏的工作簿，也就是xlsm格式，并且重新打开时要根据提示点击启用宏，否则函数无法运行呢。
光说不练假把式，你也练练吧。
-37-
升级会员 