前言

    虽然excel没有必要刻意的去学，但是在学习和工作中也确实经常需要用到，有的时候没有注意思维转换，用的时候未免会笨手笨脚的。更多的时候我们要养成的是思维发散的模式，在一定基础上，要更多的从宏观上去关注某些功能的使用场景。这样在工作中才能够发挥应有的效率，得心应手，最后能够达到触类旁通的效果。以下是结合视频资料在以前做的整理，这里一并发出来，也是希望后面自己用到的时候能够有意识的往这方面去靠。

提纲挈领性：制表的思路

1. 技术+思路（粗细均匀的两条腿）

2. 发现数据之美

第1课时：快速选择数据

• 按照位置选择（行、列、区域）；

• 用键盘快捷键：（ctrl+shift+方向键），遇到有空格的可以再按一次方向键，用ctrl+方向键直接跳到最边上的；

• 键盘鼠标一起用：ctrl+左键选中不连续的单元格；某行和某列某区域：开头+shift+结束；

• 用地址：在地址输入名称然后回车。

第2课时:快速移数

1. 移动＝复制+粘贴(去掉剪切和复制的两部操作)

2. 工作组的妙招

第3课时:快速填数

• 拖拉填充（带有公式的常用）

  • 双击填充（带有公式的常用）

  • ctrl+enter:在选中的单元格中，填入相同的内容

第4课时：数据美化格式

1. 数据条的实现方式：选中数据+条件格式

2. 数据条改变方向：数据里面的条件格式+管理规则+编辑规则+条形图方向

数据排名

• 表格美化神器：套用表格格式

• 选中数据+条件格式+项目选取规则+前10（或自定义）/低于均值的课

• 数值分布：选中数据+条件格式+图标集+然后可以进入条件格式的管理规则（分区域管理）

• 重复值篇（找出重复的）：条件格式+突出显示单元格规则+重复值

• 别人的条件规则借鉴：选中数据源+格式刷

第5课时：复制和粘贴

• 完整复制别人的表格：按行选中整张表（保证行高）+复制+ 开始中的粘贴（保持原列宽）并且体会：黏贴值、黏贴值和数值格式、黏贴值和原格式

• 转置：表格旋转90度：能竖着就不要横着（方便打印、阅读和分析）

• 粘贴的扩展功能

• 把全部列自动调整成合适的列宽：左上角全部选中+任意表格之间双击（行类似）

第6课时：图表--快手作图

1、快速入门

• 基础素颜图篇：自带的图表（柱、条、雷、折、饼）：

  • 三步：选图、选样式（一键套用，直接美化）、选颜色。

  • 图表主要元素一览表：网格线、图表标题、图例、坐标轴、数据标签、数据系列、属性

• 常见的图表类型介绍之柱形图篇：簇状柱形图（系列间的绝对值比较）、堆积柱形图（叠罗汉）、百分比堆积图（绝对值--->百分比）

• 条形图：规避柱形图的显示的问题（与柱状图很类似的）

• 雷达图：从宏观角度来看。（基本雷达图、带数据雷达图、填充雷达图）

• 进阶专业图篇：简洁、稳重、有主标题、副标题、图例、绘图区（突出图表，弱化背景）、脚注区（放数据的来源）、对齐、颜色可以采用相近色和对比色、画面的平衡。需要创建自定义的组合图（条形与折线的组合）、建议用微软雅黑字体、黑体、宋体

2、创意图表:

3、图表展示:

第7课时：图表--创意图表

• 温度计图：要点在于：设置次坐标轴（常见的商业图表） 预算和实际、收入与成本、今年和去年、企业定价与市场均价：只是一个参数的设置：次坐标轴、分类间距大小。

• 形状图：插入形状、圆柱（基本的方法就是通过先做出形状--填充出不同的光影效果---拷贝到自己的图表中）

• 迷你图篇：例如公司净利一览表，可以在每行数据的后面放上一张迷你图。有助于来了解这行数据的大致趋势。单元格数字变化，对应的图形也会相应的更改。常见的迷你图有三种：

  • 折线图

  • 柱形图

  • 盈亏图：忽视数字的大小，只关心正负（盈亏），并用不同的颜色来表示。

总结以下：字不如表，表不如图，形象直观是非常有必要的，清除迷你图不能直接delete,通过清除迷你图的格式来完成。

第8课时：word、ppt与excle连接

如何关联

1. 关联PPT:用链接对象来实现：在excle中选中表格复制---在ppt中选择性粘贴--粘贴链接---点excle对象（图形类似，建立关联，源文件发生更改，PPT自动获取）

2. 关联word：类似如何更新：自动改好。或者手动刷新。没打开的时候，打开会自动提示，就自动更新链接就好。

3. 直接在word和ppt中修改数据：三个之间只要建立了互相调用，就会一动百动

注意事项

• 作为数据源的excle文件修改换目录存放或删除会导致链接断掉。需要重新建立连接，修复链接关系。演示文件也需要重新建立联系

• 如果只想要excle中的数据，但是图表的样式以word或ppt的格式为准：保持原格式和链接数据以及使用目标主题和链接数据，用第二个就相当于把excle和word及ppt关联了，相当于只变数据，不变格式。

第9课时：excle的多表合并

常见的问题：按月、按季度和按年份的多个，进行相应的数据分析，跨表的分析是相对比较麻烦的。比较保险的方法是将各个单独的表合并到一张表中，然后用数据透视表做出各种分析报告。

1. power query:数据的获取
   

2. power pivot:数据分析（升级版的数据透视表）
   

3. power view:交互图表（图表的加强版）
   

4. power map:可视地图
   

常规步骤：

• 指定合并文件

• 读文件的内容：保证列数和列标题是一样的，这样才能够进行相应的合并。

• 合并工作表

工具完成：

• 指定合并文件夹：所有要合并的文件都放在同一个文件夹里面，加载到query中

• 读取文件：选中第一列的，然后只留下文件的内容，添加新列：表内容

好处：能够建立原始数据与合并结果的动态链接关系：原始数据的任何变化，在表格工具中进行刷新就能重新定义。重点是把合并的结构搭建起来。

第10课时：不可编辑的图片导入

三种不同来源的表格：

• 纸张、图片、PDF这类不可编辑表格

• 文本文件的数据：CSV文件和带分隔符的文本文件：对于有分隔符的可以用数据的分列

• 网页表格：对于网站的数据，可以链接到表中（将链接来自网络，就好像是网站里面自带的可以导入的表格一样。而单存的复制网上的数据是不太好实现的。）

OCR识别软件：

第11课时：公式+逗号、引号、括号

常用的英文：year month day week left right sum if net work40-50个常用的函数:由单词提升到词串的难度引用位置：

• 相对引用

• 绝对/混合引用：在前面按F4（用&船锚将表格的数据固定）可以进行来回的切换,行列都固定，同理还有行固定以及列固定

加	+	大于	>
减	-	小于	<
乘	*	大于等于	>=
除	/	小于等于	<=
乘方/开方	^	不等于	<>
百分号	%

名称	英文符号（能用)	中文符号（不能用）
大于	>	》
小于	<	《
大于等于	>=	》=
小于等于	<=	《=
不等于	<>	《》
等于	=	=
逗号	a,a	字，字
双引号	a""a	字“”字
括号	()	（）
冒号	a:a	字：字

英文	中文	英文	中文	英文	中文
Year	年	Left	左	Up	上
Month	月	Right	右	Down	下
Day	日	Mid	中间	If	如果
Hour	时	Find	查找	And	并且
Minute	分	Search	查找	Or	或者
Second	秒	Len	长度	Error	错误
Date	日期	Replace	替换	Look Up	查找
Time	时间	Sum	求和	Row	行
Today	当天	Average	平均	Column	列
Now	现在	Count	计数	Num	数量
Net	净	Max	最大	Round	舍入
Week	星期	Min	最小

 

第12课时：3个统计函数

1. 简单计算：特殊的地方，四舍五入的函数：round（四舍五入） roundup（向上舍入） rounddown（向下舍入）

2. 条件求和与平均（averageifs）:sumif 、sumifs（单条件求和、多条件求和、模糊条件求和）excle函数只认单元格自定义名称其他不认，函数参数必须加“”，其他的一律是外来户。用“”包住，用&结成一家。

3. 条件计数

第13课时：4个文本函数

工作中中常用的文本函数：（在excle中玩文字游戏，分、找、长、合）

• 分拆字串（left、mid、right）：excle中的单个函数的功能可能比较有限，但是函数套用函数后，相应的功能就会大大的增强。在拆分字串这一块，我觉得对于剪切左边、剪切右边、以及剪切中间，都需要自己去摸索一下。（找位置的函数除了find 还有search函数，用法是一样的，只是seach不区分相应的大小写）

• 找位置：find函数

• 求长度：len函数

• 合并字串：左边&“中间”&右边，其中&相当于啥都能胶的文本胶水。

    智能工具：快速填充（做出其的一个例子，excel可以非常智能的进行自主的学习和判断，根据现有数据推测用户的意图，给它学习的例子越多，相应的准确度就越高）可以做到分、找、长、合这四类函数的功能，但是也有相应的缺陷，由于数据的更改，使用函数来填充的会随着数据的更改而更改，但是快速填充却不能随之改变。各有好处，按需选择。总结来说：实际中的情况可能会更加的灵活多变，需要自己去体会。

第14课时：5个数据函数

算天数：算年龄、算工龄、算倒计时、算工作天数、甚至各种花式放假请款下的天数：DatedDif（起始日，结束日，时间类型）

求日期：求交货日期、合同日期、工作日期，正推，反推，工作日还是自然日.workday（推算工作日，将周末定义为周六和周天），或者workday.intl（周末或者假期自己定义）

算星期：比如把某日期转成星期显示，并显示在哪个星期。日期和星期间的转换函数，用weeknum函数，推算到第几个周，用weekday推算到星期几

text----将数字以文本的格式显示，（日期的本质是数字，所以日期可以用text函数来进行相应的化妆），text函数的本质就是一个数字化妆师，给数字穿上什么样的衣服，就会显示什么样的格式，也没有太大的实际的意义。

第15课时：让If函数去自动判断

主要内容：

• 啥是if函数;if（条件、成立、不成立）

• if函数的两个小伙伴，and和or：

  • and（条件1，条件2，条件3......），具有一票否决权

  • or（ 条件1、条件2、......），具有一票赞成权

• if函数多条件判断：梯度评级，适用于简单的函数

• 用if函数屏蔽错误值：iferror函数，把错误进行美化

第16课时：Vlookup函数

1. Vlookup一般用途：补全信息。Vlookup（找什么，在哪里找，结果所在列，false的精确匹配）

2. Vlookup用途二：阶梯匹配，实力更强大，第四个参数用ture的近似匹配，这里采用ture的时候要注意自己去认真的理解相关的含义：矮子里面挑高个，近似匹配的玩法。（有点类似if的梯度评级，但是层级太多，用if函数不方便）

3. #N/A错误：not available（没有找到）,出现的原因:由于是用的精确查找，要求找与被找的要完全一样，但是有些有空格、系统导出的文件中夹杂看不见的字符等脏数据（回车符、制表符......），这里引用Len函来进行检查，然后用Trim函数去掉多余的字符（将字符的头尾合其他看不见的字符删掉）

4. HlookUp函数简介：vertical（垂直的），horizontal（水平查找），相应的用法一样，不过将行改为列

第17课时：公式返回#value怎么处理？

主要是三类下面的8个错误

• 第一类：很好理解的错误：列宽不够或者是日期错

• 第二类：不存在的情况，怎么办？

  • #ref（引用失效）、

  • #N/A错误（大多出现在查找函数vlookup、hlookup、)

  • #NULL（空，找的东西不存在 ）

• 第三类：存在却是错的，怎么办？：

  • #NAME（函数名错、单元格名字错、自定义名字错、外来字符没有加" "）

  • #value错误（值错误，比如相同类型的进行相加减）

  • #NUM（数值错）

第18课时：公式失灵了怎么办？

重点

• 公式失灵的原因;公式的格式必须是常规，然后按f2+回车（快速的方法，=替换=,批量搞定）

• 不一样的公式失灵了怎么办？

• 更改过了还是没有用？

• 如何检查公式？：解构别人的公式还是编辑自己的公式，都需要灵活运用，运用相应的小工具（插入函数一个个分析、公式求值）

第19课时：常见的死表大救援

1、多此一举类：

• 滥用合并：筛选漏数据、排序不能用、数据透视表漏数据（有合并单元格的数据源做出的数据透视表会漏数据）、增加数据要重做。

  • 正确的数据表：一维、连续、无人为合并、分割。

  • 正确的方法：数据表+展示表（仓库+展示柜）

• 多余小计：排序小计错位（人工小计只要重新一排序就会全部错乱）、新增数据要重算、求总和重复劳动（小计被计算）、数据透视表虚增数据（小计被计算）。

  • 正确的方法：用数据表+展示表，在正确的数据上进行操作。

• 多余分割：筛选漏数据（只有分割前的一部分数据被统计）、排序漏数据（只有分割前的一部分数据被排序）。

  • 正确的方法：修改边框的颜色和粗细达到一样的效果。

• 多余表格：常见的错误：喜欢按月份、年份或其他规则把同一张表格拆分成多个，想做全盘的筛选、排序或者数据透视表就都做不了，如果有修改只能在每一个表里面分别改。

  • 正确的方法：用 power query进行数据表的合并，然后再进行后续的操作。

• 多余表头：做展示可以，做统计是大忌（excel默认第一行就是标题行），筛选不正常、排序不正常、数据透视表不正常、修改表样受限制。只能手动选择指定区域才能做筛选、数据透视工作。

  • 正确做法：表格的标题应该放在工作名上面。

2、萝卜错乱：正确做法：一个萝卜一个坑

• 一列多事：

• 一事多列

第20课时：活表应该长什么样子？

用设计展厅的思路来设计仓库是存在很大的问题的。三张（类）表组成活表体系。

第一张表（参数表篇）：很像是花名册，是静态的表格。

• 减少重复输入

• 节约时间

• 保护数据

第二张表（数据表篇）：把死表的毛病规避就是一张合格的数据表，主要思路，记住正确的思路表，保持完整唯一，没有破洞，没有分割。在数据表阶段做好，为后续的展示提供健康的数据基础。数据验证起到的作用：能填的数据可以用“数据验证”来进行相应的限定

第三张表（展示表篇）：想怎么组合摆放都行，就是把数据包装好。

后记

还有一些我没有写在里面，不然就真成了记事本了，有很多是需要实际运用的时候去慢慢发现的，余不再叙，以下为视频的百度网盘链接。

链接：https://pan.baidu.com/s/1ITZmKiK6aF6HuBEvEVYSKw
提取码：1234