让我痛恨10年的Excel，如今我狠狠的“抽了它的脸”

作者: 萤火虫的秘密 | 来源:发表于2018-08-12 12:30 被阅读362次

哈喽亲爱的小伙伴们周末愉快！今天主要跟大家一起来分享分享九天以来我们所学的知识点。无论你处在何种工作与学习都应该有个总结。

没想到九天的学习狠狠的被抽了脸。首先我们一起来回顾一下九天所学技巧。

第一节：认识Excel突破理论中储君老师主要讲述了到了以下几点

001 什么是EXCEL ，Excel不仅仅是电子表格，而是超越的意思。能够用最简单的方法解决复制的问题。

002 Excel工作薄为什么一打开就是三个表格而不是多个表格，三个表格分别代表的意思以及功能。

1.明细表手工录入或系统导出它可以是手工录入也可以系统导出还可以是别人给你的数据源

2.参数表它的功能就是利用工具进行匹配

3.汇总表也就是对明细表进行加工处理汇总比如用数据透视表汇总，函数汇总，分类汇总

003 EXCEL操作七步骤准备工作从获取数据到最后的转化输出

004 建立excel知识体系，高手必备的进阶进阶之路从基础操作转化到高级操作再到系统的运用整个过程都需要长时间积累

005 讲述了要有规范化，如何将一个单元格的文字与数字分别显示出来。尽量利用自动化管理实施表格中可以实现的如使用vookup函数查找姓名等；要有可视化工具与函数化

006 基础知识Excel界面的一些认识；如名称框、快速访问工具栏、编辑栏、菜单栏、工具栏、工作表导航、工作表标签、状态栏、显示比例如下图

007 文件的操作：一般都是一些常用的快捷键这个需要必须掌握如下图

第二节：效率加倍的快捷键储君老师主要讲述了以下几点

001 所有快捷键以及常用的快捷键

002 快速访问工具栏添加的方法的具体方法如下

方法1:数据——筛选——右键添加到快速访问工具栏；

方法2:数据——靠左边有个倒三角——其他命令——弹出到快速访问工具栏的选项区域——根据自己需求添加；

方法3:文件——选项——快速访问工具栏——导入将老师的快速访问工具栏直接导入即可。推荐我三个常用的ATL+1粘贴数值，atl+2快速筛选，atl+3选择性粘贴

003 自定义功能区:可以自定义开发工具，设置自己个性化的工具

004 格式刷的应用:格式刷实际很简单大家可以自己尝试着使用

005 剪贴板的应用:开始——打开剪贴板——根据自己需求使用

006 录制宏:开发工具——录制宏——宏名自己定快捷键也自己定

007 各种高级的插件名称的推荐以及地址的下载如下

加载插件:第一个方方格子，下载地址：http://ffcell.net/home/ffcell.aspx

第二个推荐易用宝，下载地址：http://yyb.excelhome.net/download/

第三个美化大师，下载地址：http://meihua.docer.com/

008 资料区的一个展示如下图快捷键速查表等

第三节：不为人知的排序和筛选的高级用法主要讲述了以下几个部分

001 排序的三种基本用法

方法一:鼠标点击市场份额——右击——排序——选择降序

方法二:菜单栏——数据——排序左边的降序按钮

方法三:同样菜单栏数据——排序——弹出对话框主观键词选择市场份额——升序依据选择值——次序选择降序如图所示

002 筛选的基本用法

①筛选上表格中的包含1的数字怎么操作呢？实际很简单，在搜索框中输入1即可。

②筛选以1开始的数字，在搜索框中输入1*，这里*是通配符可以是任意多个字符

③筛选以1结尾的数字，在搜索框中输入*1

④筛选4位的数字，在搜索框中输入????,四个?占4个位置。这里?表示单个占位符，占1个字符位置

⑤筛选以1开始和以8开始的数字

⑥在搜索框中输入1*，点确定。

⑦精准筛选数字7，在搜索框中输入"7"，添加双引号后可以精确筛选。

003 多条件排序进阶用法

004 自定义排序分为两个步骤

005 横向筛选，方法有两种

方法1:选择粘贴转置。这种方法一性的，数据源数据变化后再筛选需要重复操作。

方法2:函数转置。transpose函数可以把一个区域进行行列转换（重点注意啦！选取区域后，在编辑栏中输入=TRANSPOSE(C5:I10),光标放在公式结尾处，按ctrl+shift同时再按回车键，再同时松开三键结束公式输入）

006 数据透视表的排序与筛选

数据透视表的排序:点击到数据——鼠标右键——排序升序

数据透视表的筛选:在透视表的空白单元格点击筛选，就能在表格内出现筛选按钮。

007 辅助列的应用以及高级筛选

工资条制作:首先在最后添加辅助列序号——复制序号后填充——复制在下面的将表头复制后升序排列

筛选后粘贴:添加辅助列添加序号按部门排序后粘贴后再将辅助列升序排序

第四节：查找和替换的不同玩法

001 基本用法又分为两。方法1：开始菜单右侧查找和替换。方法2：ctrl+F或者ctrl+H 调出查找与替换对话框

002 进阶用法：如何查找多个数字或者文本并给替换

按ctrl+F调出查找或替换窗口选项范围查找全部（注意点:当前工作表或者当前工作薄）显示所有查找到的信息录入替换为内容全部替换

003 制作目录：制作目录呢有好几种方法。方法1：菜单栏-----插入-----超链接------即可跳转到超链接编辑栏。方法2：按CTRL+K-----弹出编辑栏

方法3：快捷的方法：首先在所有的表格中输入目录-----按ctrl+h-----跳出查找对话框----内容-----输入之前输入的目录-----范围----选择工作薄。

004 模糊查找与替换:如何查找内容为下图中“张”或“张*”.

首先按ctrl+F------输入查找内容张*------点击全部查找;替换:按ctrl+H------查找内容输入张-------在输入需要替换的-----点击确认

005 精确查找与替换：如何来查找和替换数字为0的

首先在查找内容直接输入0会将所有的都替换出来,在查找内容输入0----选项-----勾选单元格匹配点击全部查找或替换-----点击确定

006 多列按区间查找：问题，请查找销量数量>80的单元格并填充黄色背景如下图

首先方法有两种第一种就是查找全部：选中上图的整个区域-----按CTRL+F-----查找内容输入* -----查找全部-----升序----点中80-----按SHIFT----点中下限-----关闭查找窗口----填充颜

第二种方法:就是我们常用的条件格式方法：开始----条件格式----突出显示单元格规则----大于----输入值80----自定义黄色-----确定

007 导入网站数据：导入网站数据呢可能针对财务部的伙伴们使用的最多那么我们又如何来操作呢？

首先我们要在百度搜索新良财经XXX公司-------找到财务数据-----资产负债表-----点击资产负债表-----复制链接，将连接复制好后我们还要回到Excel表格中找到表格中在数据找到自网站---清理地址后将我们复制的链接地址点击转到如图。

然后右下角导入即可将资产负债表数据导出再按CTRL+T将会表格默认的选中并隔行的颜色填充这个时候我们就可以对表格进行美化了

008 批量替换空白：问题来了？如何将我们导出的财务报表空白行替换掉。按CTRL+H----输入查找内容空格----替换点击空白----点击全部替换

009 替换PPT字体：选中PPT---开始----点击替换---替换为微软雅黑---确定如图

010 查找神器。查找神奇呢分为两种首先我们来看看第一种技巧法首先打开固定的文件或文件夹---电脑桌面下面的任务栏 ------- 右键点击文件夹图标（excel,PPT文件都适用）-----显示最近打开的文件夹-----在点击文件夹后面的图直接固定-----如果说要取消再次点击图即可完成取消

第二种就是工具方法：使用此工具（Everything小工具），这个工具呢适合在公司有外网或者是公司安装有的情况下使用那么我们来看看如何使用。首先将Everything小工具安装到桌面上-----打开运行-----在最上面输入要找的文件名或文件类型（如PDF）----并可以按日期顺序排列。如图

第五.一节：GPS定位。怎样使用定位来更好的完成工作。

001 定位分三大类基本用法，进阶用法和高级用法。

1.基本用法中讲述快速跳转单元格，如何定位批注：直接按ctrl+g弹出定位框------点击定位条件-----如果默认的是批注------直接点击确定如下图

2. 定位常量：选中区域----按CTRL+g调出定位框----点击条件定位----选择常量-----选择数字

3. 定位文本型数字----按CTRL+g调出定位----- 点击条件定位-----选择常量-----选择文本----点击确定-----要将文本转化为数值

4.定位公式：选中区域-----按CTRL+g调出定位框-----点击条件定位------选择公式-----选择数字-----点击确定

5.定位错误值：选中区域----按CTRL+g调出定位框----点击条件定位----选择公式-----选择错误----点击确定

002 定位的进阶用法，

1.定位图中的当前区域:直接可以按ctrl+G----定位当前区域即可如图

2.如何室友定位找到最后一个单元格或者这个表格中之前有设置条件格式：直接按ctrl+G选择最后一个单元格或者选择条件格式条件格式即可

003 高级用法如何使用定位求下图中的小计行以及累计行

1. 首先我们要选中上图中需要求和的区域按CTRL+G---定位空值---确定----按ALT+=即可实现快速求和，累计求和同样选择累计求和这一行按CTRL+G定位空值后按ALT+=

2. 快速更新报表：选中区域----按CTRL+G---常量---删除-----然后重新录入数据即可

3. 快速找不同：

方法1:选中数据区域----按CTRL+G----定位到行内容差异单元格---点击确定---标注颜色

方法2:直接按Ctrl+\---可以快速的找到

方法3:设置公式例如图中的D2单元格中输入C2等于B2往下拖动即可

4.不复制隐藏行

方法1：选中区域----按CRTL+G----定位到可见单元格----点击确定在复制粘贴

方法2：ALT+;直接复制粘贴

方法3：利用快速访问工具栏----按之前设置好的alt+2然后复制粘贴

5. 空行的插入与删除

方法1：利用排序法制作工资条

方法2：利用定位：首先第一步在下图此表中的J2单元格输入1----在K3单元格输入1----然后往下推动

按ctrl+g定位常量鼠标右击插入整行点击确定然后复制表头在按ctrl+g定位空值按ctrl+v粘贴

6. 智能填充合并单元格

步骤1：选中区域---点击开始菜单中的取消合并单元格---按CTRL+g定位空值---输入公式等于上面A4单元格按CTRL+回车如下图

7. 快速找对象：如何使用定位将整个EXCEL 表格中的图片快速的删除

方法1：选中一个图片按CTRL+A全选 ----按delete键（删除键）

方法2：按ctrl+G定位对象---按删除键即可

第五.二节：选择性粘贴

001选择性粘贴分类三个阶段个阶段。基本用法，进阶用法，高级用法

基本用法：首先要找到选择性粘贴在哪里，点击开始菜单栏——粘贴——选择性粘贴——就可以跳出选择性粘贴对话框（这里介绍一个调出选择性粘贴的快捷键按Ctrl+Alt+V可以直接调出选择性粘贴）

1.粘贴:复制需要粘贴的数据-鼠标右键（或者是在开始菜单栏）-粘贴选项第一个-值，公式、格式、颜色全部粘贴

2.粘贴值:方法1复制带有公式的数据鼠标右击（或开始菜单）——粘贴选项——粘贴数值。

方法2:复制数据后直接按alt+1使用之前设置好的快速访问工具栏即可

3.粘贴公式:复制公式——鼠标右击——粘贴选项卡——选择公式，（注:只粘贴对应公式，格式不粘贴）

4.转置:把原来行变列或者把列变行，首先全选需要复制的数据，点击鼠标右击粘贴选项卡转置。

5. 格式:只粘贴格式，如有标注颜色，也会一起粘贴

6.粘贴链接:自动连接到我们复制的区域里面且可以跨工作部

002 进阶用法

1.运算:如何将大批量的数据以万元显示

借用运算，批量除以10000，首先我们在H2这一个空格中输入10000然后复制这个单元格-----然后选中数据E列鼠标右击点击选择性粘贴，粘贴选择值。运算选择则除点击确定即可。如下图

2. 复制列宽：新建一个工作表-复制原数据--鼠标右键--选择性粘贴----保留源列宽。如下图

3. 带链接的图片：把表格转换成图片有三种方法。

方法1：复制数据---选择性粘贴----选择图片。坏处：图片数据不会与原表格数据连动更新。

方法2：选择性粘贴----选择带链接图片。好处：此功能与原图片数据可以同步更新。

方法3：照相机功能，复制需要粘贴图片的数据点击照相机然后点击小十字就会将图片粘贴到需要粘贴的区域。好处此功能与带链接图片相似，与原图片数据同步更新。

003 高级用法

1.文本数字转数值

方法1.复制空格：首先复制空格-----选择E列需要转换的区域------选择性粘贴，在粘贴处选择“数值”，在运算部分选择“加”，按确定。

方法2.使用分列功能：菜单栏找到数据-----分列-----点击确定。

方法3. 使用分列功能，按F，这个是最快速的一个方式。

2. 跳过空白单元格：复制B列在A2单元格鼠标右击选择性粘贴跳过空单元格如下图

3.在PPT中使用：首先我们复制需要粘贴到PPT中的表格，然后在PPT中鼠标右击选择第一个是默认的格式，一般情况下我们选择第二个保持原格式如图，则会把Excel中表格的格式复制粘贴为图片，也可以设置字体为10号，字体设置为微软雅黑。

缺陷：不能再表格中更改

需要更改数据的情况下可以选择第三个嵌入PPT中如图

4.仪表盘制作：首先选择仪表盘复制到新建工作表-----复制原数据-----选择性粘贴-----带链接的图片-视图-----取消网格线-复制仪表盘----然后粘贴到PPT中在调整大小即可实现下图美观的报表

5.二维表转换：如下图这样的二维码表格转换为一维表格怎么操作呢？

首先在Excel表格下的数据----从表格-----表数据的来源-----会默认选择------点击确定-----跳转到Power query 编辑器-----将总分列删除-----选中语文列----按SHIFT键------选中物理列-----转换-----逆透视列------仅逆透视选定列-----确定-----确定-------关闭-----上载如下图所示：

第六节：通过分列快速提取有效信息文本

001 基本用法

分隔符号:数据——分列——选择分隔符号——其他输入“/”（注:列数据格式有：常规、文本、日期）——点击确定

固定宽度——数据——分列——固定宽度——分隔线——点击下一步——选择自己所需的选文本等

002 进阶用法包含了关键词拆分、隐藏内容

关键词拆分:选中区域——数据分列——其他-输入关键词“省”——目标区域——确定。

隐藏内容:选中区域——数据分列——固定宽度——分隔离到隐藏部分——不导入此类——完成。

003 文本转数值:文本转为数值有以下三种方法:

方法1:复制粘贴空格。首先复制空格-----选择E列需要转换的区域------选择性粘贴，在粘贴处选择“数值”，在运算部分选择“加”，按确定。

方法2:数据——分列——按F

方法3:将分列——添加快速访问工具栏直接点击

004 不规则日期巧转换

方法1:按CTRL+h——将点替换/——点击完成

方法2:数据——分列——点击下一步——下一步——选择日期格式

005 快速拆分时间:数据——分列——点击一步——分隔符号选择空格——点击完成

006 提取身份证的出生日期

方法1:首先选中区域——菜单栏——数据——分列——选择固定宽度——在19前加分割线——后四位前加分割线——选择前面与后面的内容选择不导入（将其忽略掉）——点击下一步——分隔符号选择——日期——目标区域选择自己需要放置的区域——点击确定即可实现

方法2:就是我们上述说的使用函数:=--TEXT(MID(B2,7,8),"0-00-00")。缺陷不小心会输错公式

007 公式不计算

方法1:公式——计算选项——手动

方法2:数据——分列——完成

008 计算规格

方法1:输入等号——按&链接符号——链接到规格——复制区域——粘贴数值

方法2——加等号——与&链接符号——往下拖动——数据——分列——完成。

009 Power Query 拆分

具体步骤:数据——新建查询——从文件夹——选（如图）

——文件路劲——选择要合并数据的文件夹——点击确定——选择合并与编辑——选择表5编辑——选中第一列将尾缀拆分，按分割符号——选择高级——要拆分列数选择1数据类型如图——选择文本格式——关闭上

第七节：用条件格式扮靓报表

001 基本用法：

1.大于3000的标记黄色

方法1：筛选大于3000标记黄色

方法2：选中数据----开始菜单-----条件格式------突出显示单元格格式-----大于3000----设置为自定义----填充选择黄色----点击确定如下图

2.使用条件格式查找重复值：首先选中数据----开始菜单---条件格式----突出显示单元格规格----重复值----点击确定如图所示

3.使用条件格式查找前三项或者后三项

首先选中数据区域------开始菜单条件格式------项目选取规则选择------前10项弹出小对话框----输入3----设置为自定义----填充颜色选择----黄色如下图

如果要选择后三项同样的方法，选中区域---开始菜单条件格式----项目选取规格----最后10项弹出对话框----输入3-----设置为自定义----填充颜色---黄色或者其他颜色---点击确定

002 进阶用法

1.数据条的介绍：选中需要设置数据条的数据----开始菜单-----条件格式----选择数据条

2.色阶的使用：选中数据区域----选择色阶颜色即可（注：色阶颜色会根据数据大小而变化，数据越小颜色越深）

3.图标集的使用：选中数据区域-----图标集

4.只显示数据条样式的使用

首先第一步我们选中数据区域-----选择条件格式-----数据条；第二步选择带有数据条的数据-----条件格式-----新建规则---选择第一个规则基于各自值的设置-----格式样式选择数据条----将仅显示数据条打勾-----填充选择所需要的颜色----边框选择实心----点击确定（如果需要图标集步骤相同样式训选择图集）如下图

5.其他图标集的用法：

首先选中数据区域-----开始菜单----条件格式-----图标集等级---根据需求选择如下图

003 高级用法

1.使用条件格式自动填充小计行并标记颜色

首先选中数据区域条----件格式-----新建规则选择-------使用公式------选择=A2单元格按两次F4在等于“小计”（用英文状态下的双引号）-----格式选择填充黄色或其他颜色-------点击确定如下图

2. 使用条件格式抽取前3名的奖项：方法与上述相同首先选中数据区域----条件格式-----新建规则----使用公式如图所示

3.条件格式除了以上的还有一个更高达上的功能就是----迷你图，迷你图分为以下三种折线，柱状，盈亏。

4.函数图表的使用方法：

首先第一步使用=REPT($E$2,B2)，第二步条件格式新建规则使用公式

5.数据透视表应用：点击列标签---分析计算字段和项目集-----计算项------名称定义为----趋势图2------公式直接输入等号----趋势图2生成后---选中区域---插入图表----范围----选择需要设置数据区域数据-----点击确定

6.高大上的聚光灯使用法：首先第一步设置条件格式-----选择数据区域------条件格式-----新建规则-----使用公式---------站粘此公式 =(CELL("row")=ROW())+(CELL("col")=COLUMN())----颜色-----选择聚光灯显示的颜色------点击确定-----第二步复制VBA代码(Private Sub

Worksheet_SelectionChange(ByVal Target As Range)Calculate End Sub)-----鼠标点击设置好聚光灯表格下方右击----查看代码-----粘贴对应代码----确定(这时候已经设置好聚光灯了)----最后一步点击另存(选择启动宏的表格）.(注：如果你表格中装了方方格子，应用宝中直接有自带的聚光灯）

第七节：神奇酷炫的下拉菜单

001 基本用法

1.基本用法的分类：第一种是手工录入，第二种是引用，第三种是使用名称框

手工输入

方法1：首先输入每个部门名称------输入第四个的时候按ALT+向下键头-----选择即可

方法2：选中区域-----数据选项卡----数据验证------允许选择序列--------来源手工输入需要的数据（中间的分隔号”，逗号”一定要在英文状态下输入）----确定-----手动选择即可----如下图

如何使用引用的方法来输入

首先选中数据区域-----数据选项卡-----数据验证----允许-----选择序列-----来源-------选择基础信息表里面的参数如下图

选择销售员区域----确定-----然后-----选择即可

使用名称框的方法有两种

方法1：先给数据区域定义名称框为-----绩效-----然后选中数据区域-----数据选项卡----有效性----数据验证----数据验证

-----允许选择序列-----来源------输入=绩效（或者按f3）如下图

可以调出粘贴名称的窗口-----直接选择即可------确定

方法2：例如在F列输入工龄工资

选中区域----数据选项卡---数据验证----数据验证对话框----允许选择整数----来源-----输入如最大值100----最小值10

确定---然后直接输入----这时候我们输入大于或小于设置的值-----就会有错误提醒

方法3：在B列输入长达18位的身份证号码

选中区域----数据选项卡----数据验证---允许----文本长度-----来源设置为18位----确定即可如下图

002 进阶用法

1.提示信息：例如输入身份证号码----选中区域----数据选项卡----数据验证----输入信息----标题---提示---输入信息

-----请输入18位的身份证号码如图---确定

2.出错警告：选中区域---数据选项卡---数据验证---出错提示---样式选择停止----标题输入提示---错误信息----填写请输入正确的号码如图

3.圈释无效信息：将对输入好的数据进行圈示----先输入好数据验证信息----然后数据验证下拉框----圈出无效信息----自动会出错误无效信息

----当修正数据后---圈出无效信息的框框就会自动消失

003 动态下拉菜单

1.首先选中数据----菜单栏-----插入选项卡----表格----就已将超级表格创建好如图

然后数据选项卡---数据验证----允许选择序列来源----输入=销售员（或者按fn+f3）-----调出粘贴名称框----直接选择确定

2.快速录入当前时间：

选中空白单元格---输入公式"=now"----点击确定即显示当前时间----然后在选中区域---数据选项卡---数据验证----允许选择序列来源---等于我们刚才输入公式的空白单元格确定----然后再按CTRL+1或右击-----设置单元格格式----自定义----把类型更改为-----h:mm:即可如图