Excel教程2016合集(应用技巧)

./Excel教程2016合集〔应用技巧1、Excel定位&跳转快捷键1、<Ctrl+A>快速选中全表2、<Ctrl+方向键>快速跳转到指定方向3、

<Ctrl+End>快速跳转到报表末尾

<Ctrl+Home>快速跳转到报表开头4、<Ctrl+鼠标左键点选>快速选中非连续单元格5、<Shift+鼠标左键点选>快速选中两次点选单元格之间的矩形报表区域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,公式为=A24、按<Ctrl+Enter>组合键,批量填充公式.5、选中填充公式区域,将公式结果转换为值显示.5、将公式结果转化为值介绍下设置步骤：1、复制公式所在单元格区域.2、单击鼠标右键,从弹出的快捷菜单[粘贴选项]中单击[123]按钮.这样就实现了将公式结果转换为值保存.6、巧用序列填充！让你填充从1到888888的数字你会怎么做？设置步骤：1、选中要输入序列的起点单元格〔如A22、单击[开始]-[填充]-[序列]3、在[序列]对话框中设置序列产生在[列],[等差序列],步长值从1到888888,单击[确定]按钮.这时,从1到888888的序号就自动批量生成啦！好啦,这就搞定了！7、一次性清除数值,同时保留公式无论是你接到别人发来的模板,还是要把模板发给别人,都经常需要仅清除数据区域,还保留公式区域对吧？比如当你遇到下面这种报表模板：上图的报表中绿色区域是填写数值的地方,黄色区域是公式生成.90%以上的白领都会一个个选中绿色的8块区域,分别清除,然后重新填制模板.当然,你可以说,我会按住Ctrl键不放,依次选中8个绿色区域,一次性清除数据,这样简单吧？我来教你一招,无论多大、多复杂的报表,一次性清除数值,同时保留公式不被破坏！介绍一下用到的技巧和原理：1、连续数据区域快速选定,使用<Ctrl+A>2、按条件定位目标数据,使用<Ctrl+G>或者F5功能键调出定位对话框3、根据定位条件勾选对应选项,批量定位目标区域4、Delete键清除数值数据.好啦！再有这种烦人的报表,你就不用怕了,记得学以致用哦！8、快速在每条数据行上方批量插入标题行方法1：使用条件定位方法工作中有时会遇到在每条数据行上方插入标题行的需求有木有？比如你要打印工资条、你要打印分发成绩条介绍下设置步骤：1、首先在数据源右侧创建辅助列〔如视频所示〔在H3、J4输入1,选中H3：J4,双击右下角+号,相当于往下拉,如上图2、按<Ctrl+G>打开定位对话框-定位条件-常量-确定3、在任意辅助列常量上单击鼠标右键-插入-插入整行这时候可以看到,已经实现了在每行数据下面插入了空行.4、选中标题行复制-选中下方区域5、再次利用定位选中空行区域6、按Enter键,批量完成标题行的粘贴.好啦,这就搞定了,批量插入标题行的技巧不但酷炫,而且简单快捷吧！方法2：使用简单的排序方法〔此技巧对于期末考试打印桌面信息条非常有用介绍下设置步骤：1、首选创建辅助列,复制行号到最右侧〔如视频所示.

2、复制标题行后,批量填充至下方的空白区域,复制区域与辅助列长度保持一致.3、根据辅助列升序排列,点击[数据]-[升序排列]

这样瞬间就可以一次性搞定多个标题行的定位插入啦,既快捷又准确！9、快速提取表内所有图片如果表格内包含的图片很多,你还要手动一个个复制出来那就太费劲啦,有好方法一次性搞定吗？步骤：1、修改文件后缀名为rar.2、打开rar压缩包,找到xl文件夹下的media子文件夹.3、将media文件夹内的图片复制到你想要放置图片的位置.10、一键快速插入或编辑批注选中单元格,按<Shift+F2>组合键,这样,不但可以快速插入批注,还可以编辑已有批注！11、数据源记录和统计行混杂的表格的汇总方法比如下面这种：当你不幸遇到这种表,又不能修改结构,需要在黄色区域对上面的数据进行汇总时,你会怎么办呢？如果要手动一个个搞,由于每组小计要汇总的行数不同,每次都要重新写公式,当数据源大的时候,简直要崩溃的节奏！介绍一下这里用到的几个关键技巧1、定位功能,可以用F5或者<Ctrl+G>调出定位对话框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:A$14&B$2:B$14,>=ROW<$2:$14>-1>*<A$2:A$14=D2>>>13、用数据透视表做统计下面我用一个简单的例子给大家介绍下〔本文使用的是MSOFFICE2007：假设我有个表格,要按月将不同列分别作统计步骤如下：点击"插入"-"数据透视表"点击图示中的按钮,选择数据透视表要统计的数字区域,另外在下面创建数据透视表位置的地方,我们选"新工作表"选的时候要注意,把最上面的标题行也选进去选好之后,点击图示中按钮回到之前的界面点击确定这个时候在原来SHEET的左面就会出现一个新的SHEET,在这个新的SHEET的最右面,可以看到刚才的几个字段名接下来,因为我们要按日期统计,所以把"日期"直接拖到行标签,把其他几个字段拖到数值里面这个时候左面的表格里就是这样了下面我们要按月统计,选中行标签里任意一行点击最上面菜单栏的"选项"-"将字段分组"由于是按月统计,所以选中"月",点确定现在就是我们要的结果了本例中做的是按月求和,如果你要计数或者求平均值,可以点击对应的列,选择"值字段设置"选择你要的统计方式,再点"确定"就可以了.14、乾坤大挪移！快速行列转置介绍下设置步骤：1、首先<Ctrl+A>选中整个报表区域〔如视频所示.2、按<Alt+C>复制3、将鼠标定位到放置新布局报表的位置〔如H1,按<Ctrl+Alt+V>打开选择性粘贴对话框.4、勾选[转置]复选框,单击[确定]按钮.15、合并计算汇总多工作表数据工作中经常遇到很多结构一致的表格,但是数据源分散在不同的工作表中,这时候需要你把所有表格汇总在一起,你怎么做呢？可以使用公式进行合并计算,如：=sum〔’sheet1:sheet3’!B1,但是必须是连续的表格才行.合并计算汇总多工作表数据的方法的原理：〔这三个表的姓名排列不一样1、数据-合并计算,打开合并计算对话框2、在引用位置中,依次选中多个工作表中要汇总的单元格区域,点击添加3、根据需要勾选"首行"和"最左列",区别大家自己试试就知道了4、单击确定按钮,合并计算就完成啦！这个过程中要说明的一点是：每个分工作表中的汇总字段是否相同,决定着合并计算以后的结果是合并在一列还是分别放置在多列.这个例子中,三张分工作表的数据字段名不同,分别是"一模成绩"、"二模成绩"、"三模成绩",所以合并计算结果是分别在三列中显示.如果数据源中的三张分工作表的数据字段名相同,比如都是"成绩",那么合并计算结果就合并在一列显示了.好啦！合并计算是很简单实用的汇总多表的工具,如果遇到再复杂的表格,而且数据源经常更新,采用数据透视表会是更适合的办法,后续课程中我们会专门讲解,你们可以小期待一下：16、正确输入分数的技巧！介绍下设置步骤：1、比如要输入二分之一,直接输入[1/2]会默认变成日期格式,正确的输入方法应该是输入[01/2],注意0后面有个空格哦！这时编辑栏显示的数字是0.52、一又二分之一的输入方法是[11/2],编辑栏显示的数字是1.53、当输入[06/5]的时候,会自动转换为[11/5],编辑栏显示1.217-1、按上档键后选择单元格拖动,可移动插入单元格,而不是移动覆盖.17-2、多工作表之间快速定位和跳转！比如要在100张工作表中快速跳转,操作步骤：1、快速跳转到最后一个工作表：按住Ctrl单击工作表左下角的右三角按钮〔excel20032、快速跳转到第一个工作表：按住Ctrl单击工作表左下角的左三角按钮〔excel20033、要查看文件中包含多少个工作表：在左下角的三角按钮上单击鼠标右键,可展开列表查看该工作簿包含的所有工作表.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、用快捷键轻松在表格内快速跳转！方法一：<Ctrl+←>快速跳转到表格最左端；<Ctrl+右>快速跳转到表格最右端；<Ctrl+↓>快速跳转到表格最下端；<Ctrl+↑>快速跳转到表格最上端.方法二：选中任意一个单元格,将鼠标移至该单元格上边框,变成十字箭头时,双击该单元格的上边框,鼠标跳至表格最顶端；同理,双击左边框,跳至最左；双击有边框跳至最右；双击下边框,跳至最后.17-6、区域内批量输入文本比如要你在Excel里批量填充单元格,如下图：介绍下设置步骤：1、选中要批量填充的单元格区域,在第一个单元格输入文本.2、按<Ctrl+Enter>组合键,批量填充选中的单元格区域.18、1个公式构建重复序号的递增序列今天我来结合一个实际案例,介绍1个公式构建重复序号的递增序列的方法.如下图所示〔黄色区域处输入公式B2单元格输入以下公式,将公式向下填充.方法1：=QUOTIENT<ROW<A3>,3>方法2：=INT<ROW<A3>/3>19、用选择性粘贴批量将文本转换为数值工作中经常会遇到文本数字转换为真正数值的需求.很多系统导出的数据源报表中的数字都是文本型数字,无法直接求和和创建数据透视表,需要先转换为真正的数值才能进行下面的数据处理,如果你一个个手动搞那就太费劲啦,有好方法一次性搞定吗？介绍下设置步骤：1、选中任意一个空单元格,按<Ctrl+C>复制.2、选中要转换为数值的文本数字所在区域,按<Ctrl+Alt+V>.3、在弹出的[选择性粘贴]对话框中单击[数值]和[加]单选按钮.4、单击[确定]按钮.这时,选中的所有文本数字就已经转换为真正的数值了,瞬间完成！20、批量对数据添加前缀工作中经常会遇到在数据前添加固定前缀的操作.比如要在产品编码前统一添加前缀"LR",80%以上的白领还在手动一个个敲前缀,其实有个快捷方式1秒就能搞定！介绍下设置步骤：1、选中产品编码列,单击<Ctrl+1>组合键打开[设置单元格格式]对话框.2、[数字]-[分类]-[自定义]里面,在原有自定义格式代码前添加前缀"LR".3、单击[确定]按钮,即可看到所有数据前面都增加前缀.这时,修改的是所有数据的显示形式,真正的单元格数据本身并不变化.21、二维交叉查询工作中经常会遇到二维条件的数据查询需求有木有？如下图所示介绍下思路和步骤：1、设置数据有效性〔数据验证实现下拉菜单,快捷选择分公司和季度2、设置条件格式,在条件格式中结合函数公式来实现动态标识行或列颜色的功能这个细节的具体方法,咱们后面专门开教程学习,敬请期待！22、批量生成1至100的随机数无论是数据测试还是验证报表公式的正确性,都需要生成随机数来辅助完成,这里介绍一个公式批量生成1至100的随机数！介绍下设置步骤：1、先选中要生成随机数的单元格区域.在公示栏输入以下公式.=RANDBETWEEN<1,100>2、按<Ctrl+Enter>组合键,批量填充选中的单元格区域.3、在取消选中之前,点击公示栏,回车,没回车一次,更换一次数字.23、快速修正不规范日期工作中经常会遇到报表中的日期格式不规范,导致函数计算结果出错.如果不规范的日期比较少,手动改几下就好了.可如果是从系统导出的数据源中有几千上万个不规范日期,再去一个个手动搞,会崩溃的！设置步骤：1、选中不规范日期所在列的整列,单击[数据]-[分列].2、在[文本分列向导]对话框中单击两次[下一步]按钮,在第3步的界面中勾选[日期]单选框,单击[完成]按钮.24、快速输入性别工作中经常会遇到在报表中输入性别的需求.如果员工数量比较少还算好,手动输入即可,如果要输入性别的员工很多,再去一个个手动搞,会崩溃的！设置步骤：1、选中需要输入性别的单元格区域,按<Ctrl+1>组合键打开[设置单元格格式]对话框.2、在[数字]选项卡下的[分类]列表中选择[自定义]选项,右侧的文本框中输入以下代码,单击[确定]按钮.[=1]男;[=2]女3、这时,输入1即可显示男,输入2即可显示女,你输入对应的1和2代表男和女即可.25、快速实现报表内所有员工奖金普调500元工作中经常会遇到在原有报表数据的基础上修改的需求.你是否遇到过要将报表中的数据批量加一个数/减一个数,或者批量乘以/除以一个数的情形呢？你会怎么做呢？比如下图所示：设置步骤：1、选中任意空白单元格输入500,并且复制这个单元格.2、选中要批量进行修改的数据区域,这里可以使用一些快捷键.3、按<Ctrl+Alt+V>组合键,打开[选择性粘贴]对话框.4、勾选[数值],勾选[加],单击[确定]按钮.26、自定义函数ConTxt,文本合并超牛！要将同一类的内容,比如同一个部门的员工、同一个门店的商品等等合并到一个单元格内.例如下图所示,需要将A列数据源中的文本内容,合并到C4单元格.解决合并文本内容的问题,离不开著名的自定义函数Contxt.这个函数是chenjun老师原创,最早发布在ExcelHome技术论坛,用户可以和使用工作表函数一样,对参数进行灵活的设置.接下来说说这个自定义函数的使用方法：步骤一：右键单击工作表标签[查看代码],打开VBE编辑器.步骤二：在VBE窗口中,点击[插入][模块].步骤三：在右侧的代码窗口中输入代码.以下是代码文字版,大家使用时可以直接复制就好.PublicFunctionConTxt<ParamArrayargs<>AsV