高效能慢生活践行335天（总结）

高效能慢生活践行335天（总结）

2018-11-25  就寝22: 00    晨起5:14

昨日午休/冥想：20min

高效能慢生活践行335天（总结）

一、EXCEL学习第一阶段告一段落，总结一下：简单是极致的复杂，Excel正是在用简单的方法去解决复杂的问题。简单到只有三个表格：参数表（事先设置好）、明细表（手工录入，引用参数表）、汇总表（根据明细表加工得到）。

Excel的操作也简单到只有七步，开始（需要什么数据）—获取（系统导出、外部单位获取、自己手工录入）—规范（能用的格式状态）—计算（想要结果）—分析（对计算结果分析）—转化（可阅读形式）—输出（想要的结果）

二、建立知识体系，高手的进阶之路

1.      建立知识框架（基础操作、高级操作、系统应用）

2.      基础操作（认知—熟悉—结合案例熟练应用）

3.      高级操作

①  数据关系（趋势、对比、构成、分布）

②  常用函数51个（文本、统计、逻辑、日期、时间）

③  必要的EXCEL操作

④  根据①结合②③，对数据进行处理

⑤  分析（运算函数、数据透视、数组、统计学知识与函数）

⑥  可视化（常用图表、动态图表、单元格内可视化、创意图表）

4.      系统应用

①  系统逻辑（输入、处理、输出）

②  复杂问题（自动化、流程化、系统化）

③  分析面板—项目管理

④  小工具—插件开发

三、使用原则

1.      规范化

不把不同类型的信息放在一个单元格里

不要合并单元格、不要有空行

不用错误日期格式

2.      自动化

    善用函数实现自动化（例如：VLOOKUP查找）

3.      可视化

满足视觉需求，快速展现信息，让表达更容易理解

4.      函数化

   

四、基础知识（界面）

名称框：定位、定义、实现区域和公式的联动使用

快速访问工具栏：工具栏中右击添加、Alt显示快捷方式

五、常用文件操作

快速新建Excel文件（Ctrl+N、）

快速展开功能区（Ctrl+F1）

切换Excel文件（Alt+Tab）

打开文件（Ctrl+O）查找最近打开的文件

自定义缩放（Ctrl+滚轮）

复制（Ctrl+C）

粘贴（Ctrl+V）

保存（Ctrl+S、F12）

快速锁屏（Win+L）

快速转到桌面（Win+D）

快速关闭Excel文件（Ctrl+W）

六、养成良好的Excel习惯，习惯决定成败

1.      保存的习惯

2.      常用快捷键的习惯

3.      自动化

4.      常保存

5.      常备份

6.      多检查

高效能慢生活践行335天（总结）
高效能慢生活践行335天（总结）

第二讲，学习的主题是围绕快捷键，目的是通过快捷键的设置和使用，来让我们的效率进一步提升！

主要内容是快捷键——提升效率的必经之路

主要有以下九个方面

一、所有快捷键（共有82个，常用的有32个）

市面上总共有82个快捷键，我们常用的有32个快捷键。

主要介绍32个常用的快捷键

1.以Ctrl开头的快捷键

2. 以Alt开头的快捷键

3.以Win开头的快捷键

4.单独按的快捷键

高效能慢生活践行335天（总结）

只记住并没有什么大的用处， 我们在平时的工作中生活中经常去使用经常去练习，让快捷键进入我们的潜意识，才能让快捷键的快捷体现价值，实现真正的快捷。

大家也可以购买Excel快捷键的鼠标垫（如上图所示），也可以将上图打印出来，放在电脑或者工作区域的附近，以提醒自己去使用。

三、快速访问工具栏（有三个方法）

方法一

1. 找到“数据----筛选”，右击“添加到快速访问工具栏”

2. 按Alt查看快捷键，直接使用快捷键

方法二

1. 点击工具栏的倒三角，“自定义快速访问工具栏----其他命令”

2. 添加

方法三

导入 “文件----Excel选项----快速访问工具栏----导入自定义文件”

四、自定义功能区

1. “文件----选项----自定义功能区”

2.勾选“开发工具”或“新建选项卡----导入常用命令”

五、格式刷的应用

1.“选定格式区域”，按格式刷

注意：若连续多次使用，双击“格式刷”实现

六、剪切板的应用

1.打开剪贴板，分别选中复制.

2.在剪贴板中“全部粘贴”

七、录制宏

1.找到“开发工具----录制宏”，定义“宏名”，“快捷键”

2.“Ctrl +G---定位条件----空值”，右击“删除整行”，“停止录制”

3.可以在开发工具中查看宏

八、加载插件（推荐以下三个）

1. 方方格子http://ffcell.net/home/ffcell.aspx

2. 易用宝http://yyb.excelhome.net/download/

3. 美化大师http://meihua.docer.com/

感兴趣可以下载使用

九、资料区

1.Excel快捷键速查表（如图三）

2.常用宏

高效能慢生活践行335天（总结）

今天学习的主要内容都是围绕排序和筛选展开的。排序和筛选——不为人知的高级用法。

主要有以下七个方面

一、基本用法（单条件的排序筛选）

为什么要排序？排序前，人的眼睛需要上下移动多次，多数字进行比较，有一个反应的时间；排序后呢，视线从人而下自然移动，能更快一步发现最大值和最小值；排序后的数据，看起来更直观。

正如金句所说“字不如表，表不如图。”如果把需要排序的数据制作成一张图表，让数据可视化，那效果更是一目了然。这就是排序的力量！

方法一“开始----排序和筛选”

方法二“数据----排序/筛选”

特别介绍筛选中的搜索器：①筛选包含1的数字，可以在搜索框中输入1；②筛选以1开始的数字在搜索框中输入1*，（这里*是通配符，可以是任意多个字符）；③筛选以1结尾的数字，在搜索框中输入*1；④筛选4位的数字，在搜索框中输入????，（四个?占4个位置。这里?表示单个占位符，占1个字符位置）；⑤筛选以1开始和以8开始的数字，在搜索框中输入1*，点确定。再打开搜索窗口，在搜索框中输入8*，勾选“将当前所选内容添加到筛选器"，筛选后就得到结果了；⑥精准筛选数字7，在搜索框中输入"7"，添加双引号后可以精确筛选；⑦筛选*，在搜索框中输入~*就可以了

二、进阶用法（多条件的排序筛选）

具体操作：选择数据区域内的任一个单元格“数据----排序”，按自己的需求在弹出的对话框中按照主次关键字、排序依据、次序来添加并编辑条件。也可以选择所需格式的单元格，右击“筛选----按所选单元格的值、颜色、字体颜色或者图标来筛选”

三、自定义排序

具体操作：“文件----选项----高级----编辑自定义列表”，在弹出的自定义序列对话框中，按照自己的需求，导入自己所需的自定义排序方式，确定，再按照进阶用法，设置排序情况。

引申：已定义的排序方式，可以在填充时构成循环引用。

四、横向筛选

方法1 选择粘贴转置。

具体操作：选择数据区域，右击----复制---选择性粘贴----转置。

缺陷是数据源数据变化后再筛选需要重复操作。

方法2 函数转置——transpose函数

transpose函数可以把一个区域进行行列转换。

具体操作：选取区域后，在编辑栏中输入=TRANSPOSE(C5:I10),光标放在公式结尾处，按ctrl+shift同时再按 回车键，再同时松开三键结束公式输入。

五、数据透视表筛选

具体操作：点击到数据里面，右击----排序----选择排序方式；对具体的数据进行排序，可以点击排序图标，选择其他排序选项，编辑排序选项。

注意：数据透视表本身并不支持筛选，可以在数据透视表旁边的单元格中，点筛选，就可以实现数据透视表的筛选了。

六、辅助列的应用

例1：工资条的制作

具体操作：添加辅助列，在辅助列中按对应内容的数量填充序号，复制以上填充序号粘贴在辅助列下方空白区域，粘贴表头在原表格的下方，点击数据区域内的任意单元格，数据----筛选，对辅助列进行升序排列。

引申：在上述例子中再加一个空行

具体操作：再在辅助列两套序号中间添加一套序号，再升序排序

例2：删选后的粘贴

具体操作：取消隐藏行，构建辅助列，在辅助列中生成序号，选中表头，筛选，按需要粘贴的数据要求排序，再粘贴数据，再还原（辅助列排序----按颜色筛选）。

七、高级筛选

简单用法：（例：单独筛选销售员）

具体操作：数据----高级----编辑高级筛选（选择在原有区域显示筛选结果、选择对应的列表区域和条件区域）

复杂用法：（例：筛选对应销售员的销售明细）

具体操作：数据----高级----编辑高级筛选（选择将筛选结果复制到其他位置、选择对应的列表区域、条件区域、复制到的位置）

引申：选择不同的销售员，显示对应的销售明细

具体操作：通过录制宏实现

八、动态筛选

数据很多时，想根据某一个字段进行筛选时使用

具体操作：Ctrl+T，生成超级表，在设计中，插入切片器，勾选需要动态筛选的字段（在选项中可以设置高度和列宽）

才到第三讲，我的Excel小白体质就暴露了，在高级筛选和动态筛选的地方卡住了好久好久，困难比预期来的要快得多（哭~~擦干眼泪继续练习~~~）

高效能慢生活践行335天（总结） 高效能慢生活践行335天（总结）

第四讲啦！

今天学习的主要内容是查找和替换，想知道查找和替换的不同玩法吗？一定静下心来往下看哦！

主要有以下十个方面

一、基本用法

如何调取

方法一：开始----查找和选择----（查找/替换）

方法二：使用快捷键（Ctrl +F 查找或者Ctrl + H替换）

二、进阶用法

1.查找多个文本（主要知识点：选项）

①    调取查找和替换窗口

②    点击选项----设置（范围为工作簿）

③    全部查找/全部替换（可以实现批量操作）

2.查找和替换颜色

①  调取查找和替换窗口

②  点击选项----格式（按需求选择需要查找的格式条件）

③  全部查找/全部替换（可以实现批量操作）

3.查找和替换公式

①调取查找和替换窗口

②在查找内容中输入=，查找全部

③Ctrl + A全选，按需求标记替换公式（可以实现批量的查找替换公式）

三、制作目录

知识点：超链接

调取：插入----超链接，或者通过快捷键“Ctrl + K”直接调取，也可以在右击菜单中调取。

如何批量制作目录

1.      在工作表中，录入所有的目录对应的表格名称（目录：******）

2.      录好之后，按Ctrl + F 进入查找替换窗口----查找内容为目录----范围为工作簿----查找全部

3.      在查找结果中，按需要选取

四、模糊查找与替换

比如：查找姓张的销售员，将张替换为李

思路：在查找内容中输入张或者张*（实现查找）；查找内容张，替换为李（实现张替换为李）

引申：筛选中搜索器的内容

①筛选包含1的数字，可以在搜索框中输入1；

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

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

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

⑤筛选以1开始和以8开始的数字，在搜索框中输入1*，点确定。再打开搜索窗口，在搜索框中输入8*，勾选“将当前所选内容添加到筛选器"，筛选后就得到结果了；

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

⑦筛选*，在搜索框中输入~*就可以了

五、精确查找与替换

例如：查找0

①  调取查找和替换窗口

②  查找中点击选项----勾选单元格匹配----查找全部

六、多列按区间查找

例如：查找数值>80的单元格，并填充黄色背景。

①  选中数据区域，Ctrl + F

②  在查找内容中输入*，查找全部

③  在查找结果中，对值进行升序排列，选中第一个大于80的值，按住Shift不放，选中最后一个值，关闭窗口（实现数值大于80的单元格查找）

④  填充颜色

引申：条件格式

①  选中数据区域

②  开始----条件格式----突出显示单元格规则----大于----按需求设置单元格格式

七、导入网站数据

①  进入新浪财经官网，在里面搜索需要数据的公司

②  进入需要的报表，复制报表页网页链接，回到Excel表格中

③  找到“数据----自网站”，在地址中粘贴复制的地址，转到

④  在资产负债表左上角，找到“单击可选定此表”，勾选它，点击右下角的导入

⑤  出现导入数据对话框，选择数据的放置位置----确定（实现数据导入）

⑥  美化报表（删除多余的信息，Ctrl + T）

八、批量替换空白

选中数据区域，Ctrl + H ，将查找内容空格，替换为空白，全部替换

九、替换PPT字体

批量改字体为微软雅黑

开始----替换----替换字体----替换为微软雅黑----替换

十、查找神奇

1、  技巧法

当我们经常要打开一些固定的文件的时候，只需要点击鼠标右键就可以查看到最近打开的一些文件，将文件锁定到此列表

2、  工具法Everything

高效能慢生活践行335天（总结） 高效能慢生活践行335天（总结）

第五讲啦！今天学习的主要内容是GPS定位，你真的用过吗？

GPS定位主要有以下九个方面

一、基本用法

1.      快速跳转单元格

快捷键Ctrl + G 或F5 调取定位窗口，通过引用位置实现

引申：①利用名称框，可快速跳转单元格；②跨工作表的跳转，=；③跨工作簿的跳转

2.      定位批注

Ctrl + G / F5 ----定位条件----批注----确定

引申：图片如何设置为批注（右击----插入批注----选中批注右击----设置批注格式----颜色与线条----填充颜色----填充效果----图片----选择图片）

3.      定位常量

Ctrl + G / F5 ----定位条件----常量（选择数字）----确定

注：文本型数字不能进行求和，需要转换为数字

引申：如果大批量需要转换格式，可以先复制一个空白单元格，然后选择性粘贴，选择数值，勾选加。

4.      定位文本型数字

Ctrl + G / F5 ----定位条件----常量（选择文本）----确定

引申：逻辑值和错误，100/0的结果会构建一个错误值

5.      定位公式

Ctrl + G / F5 ----定位条件----公式（可选数字、文本、逻辑值、错误）----确定

6.      定位错误值

Ctrl + G / F5 ----定位条件----公式（逻辑值、错误）----确定

二、进阶用法

1.      当前区域

Ctrl + G / F5 ----定位条件---当前区域----确定

引申：Ctrl + A

2.      最后一个单元格

Ctrl + G / F5 ----定位条件—最后一个单元格----确定

3.      条件格式

Ctrl + G / F5 ----定位条件---条件格式----确定

4.      数据验证

Ctrl + G / F5 ----定位条件---数据验证----确定

三、快速求和

小计行快速求和

选中数据区域，定位空值，Alt + =

注：WPS无此功能

四、快速更新报表

保留三中，小计累计公式

选中数据区域，定位常量，删除

引申：录入数据时，回车键向下移动，Tab键向右移动，Shift+Tab向左移动，Shift+回车向上移动

五、快速找不同

Ctrl + G / F5 ----定位条件---行内容差异单元格----确定

引申：①快捷键Ctrl + \ ；②设置公式=

六、不复制隐藏行

选中数据区域，Ctrl + G / F5 ----定位条件---可见单元格----确定，再复制粘贴即可实现

引申：Alt + ;  可见单元格快捷键

七、空行的插入与删除

插入：利用辅助列，隔列插入并填充序号，Ctrl + G / F5 ----定位条件---常量----确定，右击插入----整行，复制表头，定位空值，粘贴

删除：选中列，定位空值，删除整行（Ctrl + -）

引申：排序法生成工资条

八、智能填充合并单元格

操作步骤：选中所有的合并单元格区域，取消合并， Ctrl + G /F5定位所有空值，  =A4，按CTRL+ENTER 键复制公式，得出结果。 再选择性粘贴为文本值

九、快速找对象

工作表中的图片如何快速清除

方法一：Ctrl + G / F5 ----定位条件---对象----确定，删除

方法二：选中其中一个图片，Ctrl + A 全选，删除

批量改变图片大小

全选图片，格式----压缩图片----分辨率选择电子邮件----确定

高效能慢生活践行335天（总结）

六，选择性粘贴。你真的用过吗？

主要有以下七个方面

一、基本用法

复制数据区域，点击需要粘贴到的单元格，右击----粘贴选项，里面有六个功能

1.      粘贴（会把复制的公式格式都粘贴过来）

2.      粘贴值

3.      粘贴公式

4.      转置（行变列、列变行）

5.      格式

6.      粘贴链接（自动连接到复制的区域，支持跨工作簿引用）

除了以上功能，在点击右键----选择性粘贴右边的右三角，有更多功能，也可以通过快捷键是Ctrl + Alt + V .快速调取选择性粘贴窗口。

二、进阶用法

1.运算

例如，表格中的数据太大，需要批量变为万为单位

①在一个空白单元格中输入10000，复制单元格

②选择数据区域，右击----选择性粘贴----编辑选择性粘贴窗口（粘贴数值、运算除）----确定。

2.复制列宽

选择数据区域，复制，在想要粘贴的区域。右击----选择性粘贴----保留原列宽

3.带链接的图片

复制数据区域，右击----选择性粘贴----其他粘贴选项----链接的图片

或者通过照相机功能实现（文件----选项----快速访问工具栏----添加）

    三、文本数字转数值

方法一：

选择任意一个空白单元格，选择所需转换的区域（选择第一个，按Ctrl + Shift + ↓选择整列数据）----右击----选择性粘贴----编辑选择性粘贴窗口（粘贴数值、运算加）----确定。

方法二

选中列，数据----分列----完成

方法三

选择数据区域，数据----分列-----按F

四、跳过空单元格

例如：将两类插空带有空单元格的数据进行合并

具体操作：选择一列数据，在另一列粘贴，右击----选择性粘贴----编辑选择性粘贴窗口（粘贴数值、勾选跳过空单元格）----确定

引申：修改部分数据也可以通过选择性粘贴实现

五、PPT中使用

打开PPT，在Excel中复制数据，在PPT中右击----粘贴选项（推荐保留源格式）

六、仪表盘制作

最后一个章节会重点讲如何制作，这里主要介绍如何将制作好的仪表盘粘贴到PPT

具体操作：复制源数据----新建一个工作表Ctrl + N ----右击----选择性粘贴----其他粘贴选项----链接的图片----视图----不勾选网格线----再粘贴到PPT中工作汇报时用

七、二维表转换

选择数据区域----数据----从表格----创建表（选中整个表格）----确定

弹出power query编辑器，选中需要转换的区域，点击转换----逆透视列----仅逆透视选中列，即可生成二维表

开始----关闭并上载，进一步分析。

高效能慢生活践行335天（总结）

今天学习的主要内容是通过分列快速提取有效信息

主要有以下九个方面

一、基本用法

调取：数据----分列

1.      分隔符号

具体操作：选中需要分列的数据，数据----分列，在弹出的文本分列向导窗口中，选择最合适的文件类型（分隔符号）----下一步----选择合适的分隔符号（可在下方数据预览中看到分隔后的效果）----下一步----选择目标区域（如果不选择会把原来的数据给覆盖掉）----完成

2.      固定宽度

具体操作：选中需要分列的数据，数据----分列，在弹出的文本分列向导窗口中，选择最合适的文件类型（固定宽度）----下一步----选择合适的列数据格式，在下方的数据预览中点击鼠标左键确定分隔线（分隔线是可以移动的）----下一步----选择目标区域（如果不选择会把原来的数据给覆盖掉）----完成

二、进阶用法

1.      关键词的拆分

例如：把省和市分开

具体操作：选中需要分列的数据，数据----分列，在弹出的文本分列向导窗口中，选择最合适的文件类型（分隔符号）----下一步----分隔符号（其他：省），可在下方数据预览中看到分隔后的效果----下一步----选择目标区域（如果不选择会把原来的数据给覆盖掉）----完成

2.      隐藏内容

例如：在省市中只保留城市

具体操作：选中需要分列的数据，数据----分列，在弹出的文本分列向导窗口中，选择最合适的文件类型（固定宽度）----下一步----在下方的数据预览中点击鼠标左键确定分隔线（分隔线是可以移动的）选择在省的后面----下一步----列数据格式（不导入此列跳过），选择目标区域（如果不选择会把原来的数据给覆盖掉）----完成

三、文本转数值

具体操作：选中需要分列的数据，数据----分列----完成

或直接按分类键----F

引申：选择性粘贴，运用空白单元格，粘贴数值，运算加，来实现

注意：当转换后的数值变为科学计数法时，可以单元格格式设置----数字----特殊----邮政编码----确定

数值转文本

具体操作：选中数据----数据----分列----下一步----下一步----列数据格式：文本----完成

四、不规则日期巧转换

例1：错误日期2018.8.1变为正确日期2018/8/1

方法一：选中数据，Ctrl + H 调用替换，查找内容.，替换为/，全部替换

方法二：选中数据，数据----分列----下一步----下一步----列数据格式：日期----完成

例2：错误提起20180801变为正确日期2018/8/1

方法参照例1

例三：正确日期格式不一样时

具体操作：选中数据，数据----分列----下一步----下一步----在列数据格式中设置日期格式，选中目标区域----完成

五、快速拆分时间

例如：将时间拆分为日期和时间

具体操作：选中数据，数据----分列----下一步----分隔符号：空格----下一步----目标区域----完成

六、提取身份证号码的出生日期

方法一：公式法=--TEXT（MID（B2,7,8）,”0-00-00”）

方法二：选中数据，数据----分列----固定宽度----下一步----数据预览：分列线----下一步----设置列数据格式：前半部分后面部分都不导入此列（跳过），中间选择日期格式，目标区域----完成

七、公式不计算

方法一：点到单元格中，回车

方法二：选中数据，数据----分列----完成

方法三：公式----计算选项----自动

八、计算规格

例如：规格为100*100*100，计算规格

思路：=，&连接符

具体操作：在B2单元格中输入=，=B2&A2，或直接输入=“=”&A2，均显示为“=100*100*100“，复制数字区域，粘贴为数字，数据----分列----F

九、Power Query 拆分

例如：将三年的数据合并到一起

具体操作：①数据----新建查询----从文件----从文件夹----通过文件夹捷径找到需要的文件----确定----组合----合并和编辑----点击表5----确定（实现三年数据合并到一起）

②去掉文件后缀，选中列数据，开始----拆分列----按分隔符----高级选项（拆分为列，要拆分为的列数1）----确定

③数据类型改为文本，开始----数据类型----文本---替换当前转换

④开始----关闭并上载

高效能慢生活践行335天（总结）

今天学习的主要内容是用条件格式扮靓报表

主要有以下十个方面

一、基本用法

1.      突出显示单元格规则

调取：开始----条件格式----突出显示单元格规则----（大于、小于、介于、等于、文本包含、发生日期、重复值、其他规则）

例如：在2016年的数据里面，找到大于3000的销售额

具体操作：选中数据，开始----条件格式----突出显示单元格规则----大于----设置格式（3000，颜色）

引申：筛选----数字筛选----大于----3000----确定，标上颜色

注意：如果想要取消格式

具体操作：开始----条件格式----清除规则----清除所选单元格的规则

2.      快速查找重复值

例如：找出2016年数据中重复的数值

具体操作：选中数据。开始----条件格式----突出显示单元格规则----重复值----确定，按颜色排序

3.      最前/最后规则

例如：2015年的数据中最大前三个，最小后三个

最大前三具体操作：选中数据，开始----条件格式----最前/最后规则----前10项----设置格式（3，颜色）----确定

最小后三具体操作：选中数据，开始----条件格式----最前/最后规则----最后10项----设置格式（3，颜色）----确定

二、进阶用法

1.      数据条

选中数据，开始----条件格式----数据条-----按需求选择合适的填充方式（渐变填充/实心填充/其他规则）

2.      色阶

选中数据，开始----条件格式----色阶（根据数值大小用不同的颜色标识出来）

注意：数量越小，颜色越深，在（开始----条件格式----管理规则）中编辑规则

3.      图标集（让显示更加直观）

选中数据，开始----条件格式----图标集----选择合适需要的图标（方向、形状、标记、等级、其它规则）

4.      其他可视化

比例：同比（小于0 自动加粗加红）

方法一：筛选，数字筛选----小于0，再加粗标红

方法二：选中数据，开始----条件格式----突出显示单元格规则----小于（设置格式，0.红色）

方法三：盈亏图，选中数据，开始----条件格式----数据规则---编辑规则说明（样式：数据条，填充：渐变，颜色：绿，负值和坐标轴----坐标轴设置：单元格中的点值----确定）----确定

方法四：图标集，选中数据，开始----条件格式----新建规则----编辑规则说明（样式：图标集，图标样式选中合适需要的，设置值和类型）----确定

引申：设置单元格格式美化报表

具体操作：选中数据区域，设置单元格格式（Ctrl + 1）----数字----自定义----在类型中属于自定义格式代码（【蓝色】↑0%；【红色】↓0%；0%）

三、数据条神奇应用

例1：将完成进度用数据条表示出来

具体操作：①在E列对应单元格E2输入=B2,向下拖动，选中E列的数据区域，开始----条件格式----数据条----选中合适的填充（渐变/实心）

②去掉值，选中E列数据区域，开始----条件格式----管理规则----编辑规则----勾选仅显示数据条----确定----确定

例2：将数据生成一个红绿灯的效果

具体操作①在E列对应单元格E2输入=B2,向下拖动，选中E列的数据区域，开始----条件格式----图标集----形状：红绿灯

②设置颜色对应的数据区间并去掉数据，选中E列数据区域，开始----条件格式----管理规则----编辑规则----勾选仅显示图标，设置图标值----确定----确定

四、图标集的应用

选中数据区域，开始----条件格式----图标集----选择合适的图标（方向、形状、标记、等级、其他规则）

五、自动填充小计行

具体操作：选中数据区域，开始----条件格式----新建规则----选择规则类型：使用公式确定要设置格式的单元格，为符合此公式的值设置格式中输入公式（=$A2=”小计”）（注意：输入文字时要用英文状态下的双引号引起来，引用的单元格按两次F4为绝对引用）----格式----填充（颜色）----确定----确定。

引申：Ctrl + G 定位空值，Alt + =实现

六、抽奖前3名

例如：年会抽奖，进行随机抽取，按表格上的人员名单随机抽取3人

具体操作：选中数据区域，开始----条件格式-----管理规则----编辑格式规则（类型：使用公式确定要设置格式的单元格，为符合此公式的值设置格式：=$C2<=3,格式：填充红色）（C2按两次F4绝对引用,小于号在英文状态下输入）----确定----确定----确定。

抽奖结果通过排序及VLOOKUP实现

七、高大上的迷你图

折线图

①  选中数据区域，插入----迷你图（折线、柱形、盈亏）----创建迷你图（设置数据范围和位置范围）----确定，实现折线图

②  设计----显示（可设置高点、地点、负点、首点、尾点、标记）----样式（选择合适的样式）----迷你图颜色----标记颜色。高点-低点（标志出颜色），在标记颜色处可以选择要标志的颜色。柱状图和盈亏图同理。

引申：柱状图、盈亏图

具体操作：参照折线图

八、函数图表

REPT（）将对应的文本显示成对应图标的个数

例如：将销售额B列显示成元素星号（E2单元格）在微图表C列

具体操作：在C2单元格输入=REPT（$E$2,B2）

引申：如何设置元素和颜色

选中区域，开始----条件格式----管理规则中设置

九、数据透视表应用

具体操作：①点击列标签上方----分析----字段、项目和集----计算项----名称：趋势图，公式：=----确定

②选中区域---拖动到前方----选中趋势图对应区域----插入----迷你图（折线）----创建迷你图（数据范围、位置范围）----确定

十、聚光灯（OFFICE需要手工设置，WPS自带）

方法一：手工设置

具体操作①设置条件格式，Ctrl + A选择区域，开始----条件格式----新建规则----新建格式规则（类型：使用公式确定要设置格式的单元格，格式：为以下代码，格式：颜色）----确定----确定

代码=（CELL（”row”）=ROW（））+（CELL（”col”）=COLUMN（））

②复制以下代码，点击聚光灯工作表----右击----查看代码----以下代码

代码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Calculate

End Sub

③另存时，保持类型：Excel启用宏的工作簿

方法二：方方格子（自带聚光灯效果）

方法三：易用宝（自带聚光灯效果）

附相关导图

高效能慢生活践行335天（总结）

今天学习的主要内容是用神奇酷炫的下拉菜单

主要有以下七个方面

一、基本用法

1. 手工录入

例如：部门的录入（销售部、财务部、设计部）

方法一：在前三个单元格中分别输入销售部、财务部、设计部，第四个单元格直接按住Alt + ↓，即可快速生成

注意：想要实现这样的功能前提是一件输入部分的内容。

方法二：数据验证

选中对应区域，数据----数据验证----数据验证----设置（允许：序列，来源：销售部，财务部，设计部）----确定（实现下拉菜单选择）

注意，来源中录入的逗号一定要是英文状态下的逗号

2. 引用

选中对应区域，数据----数据验证----数据验证----设置（允许：序列，来源：事先设置好的参数表）----确定（实现下拉菜单选择）

3. 名称框

给参数定义一个名称：绩效

选中对应区域，数据----数据验证----数据验证----设置（允许：序列，来源：=绩效（或按F3进行选择））----确定（实现下拉菜单选择）

引申1：对数字的设置（100-500）

选中对应区域，数据----数据验证----数据验证----设置（允许：整数，最小值：100，最大值：500）----确定（实现下拉菜单选择）

引申2：身份证号码的设置

选中对应区域，数据----数据验证----数据验证----设置（允许：文本长度，数据：等于，长度：18）----确定（实现下拉菜单选择）

注意：输入身份证号的时候需要用单引号

二、进阶用法

1. 提示信息

选中数据区域，数据----数据验证----数据验证----输入信息（编辑标题和输入信息）----确定（实现提示信息的需求）

2. 出错警告

选中数据区域，数据----数据验证----数据验证----出错警告（选择样式、编辑标题和错误信息）----确定（实现提示信息的需求）

3. 圈释无效信息

例如：校验已经输入好的数据

① 设置好数据验证

② 数据----数据验证----诠释无效数据

三、动态下拉菜单

运用超级表

具体操作：①选中数据，插入----表格----确定（或Ctrl + T 实现快速创建超级表）②给对应的区域定义名称：销售员③数据----数据验证----数据验证----设置（允许：序列，来源：=销售员）----确定

四、快速录入当前时间

公式法=NOW（）

具体操作：①选择数据区域外的一个空白单元格，在单元格D1中输入公式=NOW（）②选择时间区域，数据----数据验证----数据验证----设置（允许：序列，来源：=$D$1）----确定③设置单元格格式（Ctrl + 1）自定义类型时分秒----确定

五、借贷只能一方输入

具体操作：选择数据区域，数据----数据验证----数据验证----设置（允许：自定义，公式=COUNTA（$A2:$B2）=1）----确定

引申：COUNTA函数，返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含的数据的单元格个数

六、二级下拉菜单

例如：省市的二级菜单

具体操作①公式----名称管理器---删除之前设置②选中参数表中的省份，定义名称为省份，选中需要设置下拉菜单的省份区域，数据----数据验证----数据验证----设置（允许：序列，来源：=省份）----确定（实现定义省份的下拉菜单）③选中参数表中省市的对应区域，Ctrl + G定位到常量，公式----根据所选内容创建（根据实际情况选择：首行）----确定④选中需要设置下拉菜单的城市区域，数据----数据验证----数据验证----设置（允许：序列，来源：=INDIRECT（A2））----确定（实现二级下拉菜单城市的设置）

引申1：函数INDIRECT（）

例如：在单元格A2中输入C4，在单元格C4中输入吃饭

公式=A2，显示的结果就是C4

公式=INDIRECT（”A2”），显示的结果就是C4

公式=INDIRECT（A2），显示的结果就是吃饭

引申2：二级下拉菜单的设置（动态）

函数法(了解)

具体操作：①选中需要设置下拉菜单的省份区域，数据----数据验证----数据验证----设置（允许：序列，来源：=OFFSET（$F$1,0,0,1,COUNTA($XFD$1)））----确定（实现定义省份的动态下拉菜单②选中需要设置下拉菜单的城市区域，数据----数据验证----数据验证----设置（允许：序列，来源：=OFFSET($F$1,1,(MATCH(A2,$F$1:$FD$1,0)-1),COUNTA(OFFSET($D$1,1(MATCH(A2,$F$1:$XFD$1,0)-1),999,1)))）----确定（实现动态二级下拉菜单城市的设置）

七、联想式下拉菜单

例如：输入安徽省，可出现安徽省下的所有城市

具体操作：①源数据排序。目的是把相似的内容排列在一起②选取A列要设置的区域，右键菜单----定义名称----新建名称（名称：城市，引用位置：=OFFSET('7.联想式下拉菜单'!$C$1,MATCH('7.联想式下拉菜单'!E6&"*",'7.联想式下拉菜单'!G:G,0)-1,,COUNTIF('7.联想式下拉菜单'!G:G,'7.联想式下拉菜单'!E6&"*"),1)）（实现定义名称城市）③选择数据区域，数据----数据验证----数据验证----设置（允许：序列，来源：=城市），出错警告（不勾选输入无效数据时显示出错警告）----确定

引申：公式说明

MATCH（）查找输入的字符串在C列的开始位置，&“*”实现模糊查找

COUNTIF（）统计以输入字符串开头的类型有多少个

OFFSET（）获取开始行数和总行数，用OFFSET生成动态引用区域

附相关导图

高效能慢生活践行335天（总结）