第一篇 基础篇
本部分为最基础的Excel的基础应用，在日常工作中使用频率最高的操作。掌握了该部分，基本上能应付大部分的工作。
一 数据处理规范性
数据处理中，可以说没有数据库概念的作为指导性的任何处理方式都是不规范的处理方式。虽然表面上看，基本无害，但这种情况仅适合数据量小、数据结构简单的情况。如果面对单表大数据量、多表数据数据结构复杂的数据集合，不规范的处理方式危害极大。这表现在：
- 数据冗余
- 数据无法保持一致性
1. 合并单元格
有人会觉得合并单元格很好用或者合并居中很好看，不但标题行使用，甚至了表格中还要不停使用。千万记住，合并单元格是Excel表格中的“毒瘤”，最好离得远远的。很多人习惯做成下面左图的样子，表格确实好看很多并有层次感，但是在数据统计的时候会非常麻烦。
如需要统计北京和山东的销量数据合计，就会使原本很简单的公式变得复杂。而做成右图的效果，用SUMIFS公式或数据透视表都很容易完成。其实这类问题在表格设计阶段完全可以避免，尽量少做或不做单元格合并。
2. 多行表头
<br /> 除了有合并单元格，还出现了行1和行2两层表头。这样的表格，很多功能也会无法使用。比如应用数据透视表就会有下面的错误提示。<br /> 
3. 多余空格
中国人的名字有三个字的，也有两个字的。有些人为了所谓的美观习惯常常在两个字的名字中间敲空格键。可是常常敲的空格键个数又不一致。Excel可是分得很清楚，稍微有一点不同就认为两个不同的人。
单元格B2和B4都是业务员“赵军”，但是单元格B2中输入了空格，Excel就会认为是不同的姓名，会直接影响数据统计或透视的结果。
4. 单元格多种信息
上图的表格，如何计算？这样的表格曾经困扰很多人，只能用手工办法或者计算器计算好，再输入C列单元格中。如果你这样做基础表，到月末和年终统计时你绝对会成为大忙人。因为A2~A5单元格中包含了材料、单价和单位三类信息，通过改进将这三类信息分拆在三列单元格中。
5. 数据格式不规范
表格中的各类数据要使用统一和规范的格式，方便表格使用者加工处理，且统一的要求排在规范要求之前，比如日期型数据不能输入20190106、2019.1.6、19.1.6等不规范的格式，即使输入格式不规范也要保持统一，方便表格使用者批量修改。
比如把2019年5月8日在Excel中输入为：2019.5.8。这样，软件只会当文本处理，无法认为是时间，自然也就无法参与计算了。
上图列出了Excel中常见的规范和不规范的日期格式，不规范的日期在Excel中不能直接进行日期的查询、统计和计算，需要进行规范化才能实现这三种操作。
6.表结构不规范
表结构不规范这一范畴非常大，并且是85%的人都存在的问题。但常见的大致上可以分为：报表与数据表混淆，数据值与属性列混淆。
- 报表与数据表混淆
该问题主要在于大多数人在做表时候都是按照报表式的思维去做的。因此，会将所有信息全部反应在了一个表上。
以下表为例，下表是某公司目前在使用中的销售统计表：
2020年XX公司销售统计表
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 序号 | 合同编号 | 客户姓名 | 幢号 | 房号 | 建面 | 业态 | 认购日期 | 认购金额 | 原单价 | 原总价 | 优惠折扣 | 成交单价 | 成交总价 | 签约单价 | 签约总价 | 冲抵总房款 | 实际可收房款金额 | 实收房款 | 未收房款 | 付款方式 | 签约日期 | 入住日期 | 联系方式 | 所在区域 | 认知渠道 | 投资/自用 | 成交成因（客户购买产品兴趣点） | 置业顾问 | 客户身份证 | 家庭住址 | 是否交房 | |||||
| 冲抵金额 | 实收首付款 | 按揭金额 | 放款时间 | 分期未付款金额 | 未付款原因 | 按揭未放款金额 | |||||||||||||||||||||||||||||||
| 2 | 1 | 202001001 | 李红 | 10 | 308 | 142.26 | 商铺 | 2020.1.19 | 99952 | 4530 | 191438 | 83折 | 3759.89 | 158893 | 3759.89 | 158893 | 158893 | 158893 | 一次性 | 2020.5.11 | 2020.10.30 | 张红 | |||||||||||||||
| 3 | 2 | 202005010 | 王玉 | 11 | 206 | 142.26 | 商铺 | 2020.5.11 | 174677 | 4980 | 210455 | 83折 | 4133.4 | 174677 | 4133.4 | 174677 | 174677 | 174677 | 一次性 | 2020.5.11 | 2020.10.30 | 李青 | |||||||||||||||
| 4 | 3 | 202005011 | 李莲 | 21 | 207 | 142.26 | 商铺 | 2020.5.11 | 174677 | 4980 | 210455 | 83折 | 4133.4 | 174677 | 4133.4 | 174677 | 174677 | 174677 | 一次性 | 2020.5.11 | 2020.10.30 | 王利 |
从该表的结构来看，共有37个字段，两层表头。这种类型的表，唯一的好处就是只用一个表，一目了然，一行就是一个客户，明明白白的。直接打出来就是一份报表，毫无毛病。
但从体验上来说，整个表的宽度让人感觉看不到边。但从数据管理的角度来看该表的弊病就在于，试图通过一个表来反应全部的信息。该种表，最大的问题在于数据安全性方面：
- 误删一行，就会导致整条数据被抹除；
- 所有数据全部存储于一个表内，导致单表的数据量非常大。
- 使用列计算公式时，会导致全表重算，运行效率极低，甚至会导致卡死的状况，进而造成文件损坏的可能。
那么，对于这种情况，改进办法就是将数据表与报表分开，数据表部分，分表存储数据，报表部分使用其它办法做连接展示。仍然以上表为例。该表实际上至少可以分拆为：客户信息表、合同明细表、收款明细表、未收款明细表
- 客户明细表 | 客户编号 | 客户姓名 | 联系方式 | 所在区域 | 认知渠道 | 投资/自用 | 成交成因（客户购买产品兴趣点） | 置业顾问 | 客户身份证 | 家庭住址 | | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | | 001 | 李红 | | | | | | 张红 | | | | 002 | 王玉 | | | | | | 李青 | | | | 003 | 李莲 | | | | | | 王利 | | |
- 合同明细表 | 客户编号 | 合同编号 | 客户姓名 | 幢号 | 房号 | 建面 | 业态 | 认购日期 | 认购金额 | 原单价 | 原总价 | 优惠折扣 | 成交单价 | 成交总价 | 签约单价 | 签约总价 | 冲抵总房款 | 实际可收房款金额 | 付款方式 | 签约日期 | 入住日期 | 是否交房 | | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- | | 001 | 202001001 | 李红 | 10 | 308 | 142.26 | 商铺 | 2020.1.19 | 99952 | 4530 | 191438 | 83折 | 3759.89 | 158893 | 3759.89 | 158893 | | 158893 | 一次性 | 2020.5.11 | 2020.10.30 | | | 002 | 202005010 | 王玉 | 11 | 206 | 142.26 | 商铺 | 2020.5.11 | 174677 | 4980 | 210455 | 83折 | 4133.4 | 174677 | 4133.4 | 174677 | | 174677 | 一次性 | 2020.5.11 | 2020.10.30 | | | 003 | 202005011 | 李莲 | 21 | 207 | 142.26 | 商铺 | 2020.5.11 | 174677 | 4980 | 210455 | 83折 | 4133.4 | 174677 | 4133.4 | 174677 | | 174677 | 一次性 | 2020.5.11 | 2020.10.30 | |
- 收款明细表 | 客户编号 | 客户名称 | 收款金额 | 收款日期 | 收款项目 | 收款方式 | 合同编号 | | —- | —- | —- | —- | —- | —- | —- | | 001 | 李红 | 100000 | 2020.2.11 | 房款 | 转账 | 202001001 | | 001 | 李红 | 58893 | 2020.3.11 | 房款 | POS | 202001001 | | 002 | 王玉 | 17467 | 2020.5.11 | 房款 | POS | 202005010 | | 002 | 王玉 | 100000 | 2020.4.12 | 房款 | 转账 | 202005010 | | 003 | 李莲 | 100000 | 2020.3.11 | 房款 | 转账 | 202005011 | | 003 | 李莲 | 74677 | 2020.5.10 | 房款 | POS | 202005011 |
- 客户未收款明细表
| 客户编号 | 客户姓名 | 未付金额 | 合同编号 | 未付款原因 | | —- | —- | —- | —- | —- | | | | | | |
以上各个表之间可以通过相关字段进行关联：
以上这个图叫E-R（实体联系）图，在关系数据库里面最常用的一种关系图。从图可以清晰的看出以上几个表的关系为，客户明细表通过客户编码字段与合同明细表、收款明细表、未收款明细表建立关联；合同明细表，通过客户编码或合同编码与收款明细表建立关联，与未收款明细表通过合同编码建立关联。
如此一来，最开始那种大报表即可通过公式或者数据透视表很快的完成。数据透视表大杀器，这个留在后面专门来说。
上面介绍了EXCEL表格数据管理的一些分类和基本原则，有助于我们建立良好的数据管理理念，大家在平时的工作中要注意以下几方面数据管理习惯的培养：
- 数据录入时对于同一类型的数据录入规则和格式要统一。
- 需通过计算得出结果的单元格要尽量使用公式，不直接输入结果。
公式中尽量少用常数项，可以引用某单元格的数据，方便统一修改公式。
二 常规操作
1. 合并
合并，顾名思义就是将工作表多个单元格内容合并为一个，如下图：各个字段都是分列的，如果需要一个何并的房号，那么我们就需要连接符号“&”来拼接各个字段即可。特别需要注意的是，涉及到字符拼接的，一定要用双引号“”包裹起来。否则无法识别。
2. 分列
该操作是一个非常重要的操作，主要用来将列数据按既定规则拆分成多列。如，一个标准的日期列，我们可以将其拆分成独立的年、月、日三列。
具体操作步骤如下：第一步 选择需要拆分的列，然后，打开“数据”选项卡，找到“分列”菜单。
第二步 按照分隔符分列，这里是按照分隔符号来分列。至于固定宽度，是指根据被分拆列的字符宽度来拆分，一般很少遇到这么规整的数据源供拆分，除非是导入的TXT、CSV等这类型有固定空格来区分列的源文件。
第三步 输入分隔符。在此，分隔符号显然为“.”
第四步 选择分隔后产生的列数据存放位置，在此，为了行数据的对应，选择了“B1”单元格位置。
第五步 完成。最终效果如下：
3. 智能填充
这是一个Excel里面最强大，最智能，最让你想不到的功能。该功能在WPS以及Excel里面都有提供。操作也非常简单，就是一个组合键[Ctrl+E]。看如下示例：
3.1 数据提取拆分
当我们面对如下这样一个表，所有信息都放在了一列内，这样使用起来非常不方便，我们需要把他分拆开来，这时候按照固定符号分列是不可能了，因为这里没有明确的分隔符可以使用。这里用智能填充就特别简单了。
具体操作：
第一步 在B1单元格输入要提取的内容，在此，我们要提取姓名，因此在B10单元格输入左侧单元格里面的姓名“张三丰”。
第二步 选定B10单元格，然后按下组合键[Ctrl+E],神奇效果出来了，瞬间完成了所有行姓名的自动智能提取。以此类推，也可以完成号码的提取。
3.2 内容合并
智能填充除了分拆提取外，也可以完成合并，这个要比使用连接符“&”拼接更快速、更简单，也不容易出错。具体实现办法仍然是在对应单元格合并后的内容，然后按[Ctrl+E]即可，如下图：
3.3 混杂数据的提取
下面来看一个更智能的处理办法。
如存在一个如下记录及其不规范表，现在需要提取出数字部分进行运算。最常规、最低级的做法就是逐个的去手工输入，当然了，有些高手可以使用正则表达式来提取，但能做到这个的需要懂得VBA编程和那一堆，连程序员见了都想吐的正则符号。但如果用智能填充，那么就简单了，就是几秒钟的事。
操作步骤
第一步 在第一行B27单元格输入需要提取的数据“20+30”。这里需要特别说明的是，如果数据同质化太多，那么就不能只输入一行，否则无法找不到参照。
第二步 然后在第B27这个提取的位置按【Ctrl+E】完成自动填充。
<br />上图中提取出了数值，那么如何将诸如“20+30”这样的表达式计算出来呢？这里就需要用到一个函数:Evaluate()，该函数将会在后面进阶篇部分讨论到。
3.4 总结
智能填充是一个简单的大杀器，其填充原理就是根据临近区域的数据以及输入的目标值各自的特征，然后判断操作者的意图来完成填充。因此，输入的目标值的特征和位置特别重要。如果输入一个值还是无法提取时候就多输入几个值，然后选择在后面的位置上按[Ctrl+E]来完成填充。切记！
4. 表格样式
这是一个很少被人提及而又非常实用，非常强大的功能。在某些地方称他为“表格样式”，在很多地方，也被称作：超级表。它能快速的完成表格区域的格式化，避免了繁琐的格式设置。表格样式设置的操作，实际上，就是将表格区域转换为带有预设格式的超级表区域。
在实务中非常推荐将数据表格转换为超级表来操作。
操作办法
选取表区域，在”开始”选项卡上，单击”套用表格格式”，选取表格样式
当然，在此也可以在选取区域后，直接使用快捷键【Ctrl+T】来完成，将单元格区域转换为超级表的操作。完成以上操作后，一块简单的单元格区域就被转换为了超级表，从上图可以看出，目前这块区域：【A1:E11】已经被命名为【表1】了，也就是说，目前这个区域是一个超级表对象，而不是区域了。
千万不要以为，这个表格样式设置就是为了设置一下样式，让表格更好看而已。如果这样认为，那就完全低估了表格样式或者说超级表的巨大作用了。以下就是超级表的强大功能：样式美观，便于查看。
Excel预置了21个浅色，28个中等色，11个深色的表格样式，可以随意更换样式，做出非常具有视觉效果的表格，并且相对于无表格样式的表格而言，超级表深浅交错过渡，更加方便查看行数据，不会出现看错行的问题。
- 自动汇总，动态汇总
在套用表格样式后，可以看到顶部菜单栏多了好多针对表格样式的选项设置。
在表格样式选项中勾选【汇总行】，超级表的最下面就会出行汇总行，汇总行已内置了各种汇总方式式。更厉害的是，这些汇总方式会随筛选结果的变化而自动变化，动态的计算筛选结果值。这个功能，如果在常规区域下，需要使用Subtotal函数才能实现。
- 自带筛选器
不使用超级表的情况下，一个工作表中只能设置一个筛选，但使用超级表后，工作表中如有几个超级表，每个超级表都自带筛选，只要在【设计】菜单下点选【筛选按钮】，不用通过点【数据】→【筛选】启动筛选。
- 公式引用清晰统一
编写公式时，不再是单元格引用，而是区域引用。如下图案例中，总价=[@销售数量][@单价]，单击【总价】这一列任何一个单元格，就会发现，整列单元格公式都是一个“=[@销售数量][@单价]”。这种直观的表达便于厘清逻辑，尤其是在复杂的公式引用时。
自动填充公式和格式
在超级表的单元格设置公式后，按回车就可以快速填充到最后一行，而且每扩展一行，会把公式和单元格格式自动复制下来，不用重新设置格式。如果你不想将公式扩展，只用于其中一个单元格，则在自动扩展后，按一次撤销或Ctrl+Z。
动态扩展数据区域范围，形成动态数据源，数据透视表和图表可随时更新
如果在紧邻超级表的普通行或列输入数据，超级表的区域会自动扩展，不需要重新设置，利用超级表自动识别扩展区域范围的特点，可创建基于超级表的数据透视表，超级表每次增减数据，只需要刷新数据透视表即可，无需重设区域。
- 切片器
如果你用的是Excel 2013或是Excel 2016以上版本，超级表还可以在表格中插入切片器，实现快速筛选。
结构化引用
<br />上图中，我们对奥利奥饼干的销售总价进行求和，我们看到单元格公式为：<br />`=SUMIF(表2[品名],B15,表2[总价])`<br />从公式中，我们看到，条件区域：“B2:B10”变成了[品名]，总价区域：“E1:E10”变成了[总价]。如果学过在office环境下用VBA编程的人，肯定知道，在VBA语言中，或者是在office世界中，大括号“[]”，是“域”和“字段”的定界符。回到上图，此时，无需再关心表品名的单元格范围和总价的单元格范围了，直接是有使用：[品名]、[总价]来做引用即可。<br />结构化引用在Excel中是一个非常重要的概念。他是一个融入了关系数据库表结构概念的东西，学会使用该方法后，在数据引用过程中非常方便。
5. 页面布局设置
5.1 分页设置
5.2 打印标题行设置
很多工作表因为有很多分页，如果直接打印，那么后面几页是没有的表格标题的，为了方便阅览，让每一页的表格都有标题对应，这就需要给表格增加“打印标题行”。
6. 快捷操作
1. 快速填充
充分使用自动填充手柄能完成很多纵向、横向的快速自动填充，详见下面视频：
三 函数应用
1. sum函数
用法
=sum(参数1,参数2,参数3,…..,参数n)
解释
对选定的参数进行求和。需要说明的是，此处的参数类型非常多，可以是单元格，也可以是区域，也可以是数组。
- 示例
- 对分散单元格求和
=sum(A1,A5,A9,A12,A14)
- 对区域进行求和
=sum(A1:A5,A10:A20)
- 说明
sum函数在结果上直接使用求和运算符“+”、“-”计算出来的并无区别。但在使用上，sum函数读起来，在逻辑上要更为清晰，并且可以多种类型参数。因此，在工作表中，能用函数运算的尽量用函数表达式。
2. count函数
用法
=count(参数1,参数2,·····)
解释
对参数中，数值型的数字进行计数。但是错误值、空值、逻辑值、文字则会被忽略，不进行计数。
返回参数 (算术) 平均值。
IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。
- 示例
如下图，按照60分为界线，判断一下是否及格=IF(A2<60,"否","是")
以上是简单的一层if函数使用，该状态下，只判断一个状态，返回一个结果。但很多场景下，并没有这么简单，可能会存在多个状态的判断，然后返回不同状态下的结果，那么这就涉及到了IF函数的嵌套。
例如根据学生的成绩范围来作出学籍评判：60分以下的，等级为A；60—80分的等级为B；80-90分的等级为C；90分以上的等级为D。
公式为：=IF(A2<60,"A",IF(AND(A2>=60,A2<80),"B",IF(AND(A2>=80,A2<90),"C","D")))
以上公式，用了三层IF：
从上面来看，IF嵌套实际上并不难，只需要理清逻辑即可，按照逻辑，无外乎就是：
IF(条件1，条件1为真时返回值，if(条件2,条件2为真时返回值,if(条件3,条件3为真时返回值，否则其它值))
只要条件不为真时就添加一层IF，如此层层往下递推即可。
特别需要说明的是，因为IF函数这种层层嵌套的使用办法特别容易逻辑混乱，非常容易出错，体验非常差，为了改善体验，微软在Excel新版本中已经有了更好的解决嵌套问题的函数，那就是：IFS()函数。但该函数在只有在Excel 2016以后的版本中才提供。不过WPS 2019版本已经提供IFS()函数。因为该函数不是普遍提供，因此该函数在进阶篇函数部分在拿出来讨论。
5. sumif函数
用法
=SUMIF(条件区域,指定的求和条件,求和的区域)
解释
对条件区域内，满足指定的求和条件的行数据进行求和。
- 示例
如下表，对表中对“张三丰”打趴人数合计。条件区域就是姓名区域，指定的求和条件就是固定值“张三丰”，这里，把它放在了G1单元格做引用。求和区域是打爬人数区域。公式为：=SUMIF(C2:C14,G1,D2:D14)
6. countif
用法
=countif(条件区域,指定条件)
解释
对统计条件区域中，符合指定条件的单元格个数进行统计。
- 示例
统计C2:C14区域内，姓名为“张三丰”的个数。=countif(C2:C14,"张三丰")
第二篇 进阶篇
一 常规操作
1. 通配符应用
通配符是一种特殊语句，主要有星号()和问号(?)，用来模糊搜索内容。通配符在现代计算机语言中非常常见。在EXCEL中，可以通过拼接通配符来实现模糊匹配。
1、星号（）
代替零个、单个或多个字符。比如：“AB” ，可以匹配到所有以 “AB” 结尾的字符串，如AAB、CSDAB等。“AB” 则可指代，所有包含“AB”的字符串。
2、问号（？）
问号代替一个字符。
2. 数据规约整理
二 函数应用
1. lookup
用法
=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)
解释
-
2. vlookup
该函数在EXCEL里面极其重要，在EXCEL所有函数中可以说是“扛把子”的存在。有种夸张的说法是，EXCEL使用人员中分两种，一种是会VLOOKUP的，一种是不会VLOOKUP的，会VLOOKUP的，基本可以帅开其它不会该函数的几个层次了。下面就来看一下该函数的具体使用方法：
用法
=vlookup(查找值,查找区域,找到匹配行后返回值所在列,精确/模糊匹配)
示例
表1：
表2：
比如，在表1中，如果我们要从表2中查找“F8-127”这个房号的累计缴款金额，那么公式如下：=VLOOKUP(表1!AE32,表2!AN:A0,2,false)
该公式的意思是，通过表1单元格AE32这个房号F8-127，去表2的AN:AO区域的首列中查找等于“F8-127”的行，找到后返回AN到AO这个区域中第二列的值。它的逻辑是，先纵向查找到匹配的值，然后再横向定位列所在单元格的值。
11. index+match
该函数是VLOOKUP的一个替代方案。也许有人会有疑惑，好好的VLOOKUP，不用怎么又整出来一个index+match函数组合。主要原因在于，VLOOKUP这个函数是有缺陷的，那就是，查找值必须位于查找区域的第一列，否则就要报错。
比如下表：
3. datedif
4. days360
5. len
6. left
7. right
8. mid
9. trim
10. round
12. evaluate
第三篇 高阶篇
一 常规操作
1. 数据透视表
2. 模拟分析
3. microsoft query
4. Power Query
5. Power Pivot
二 函数应用
2. sumproduct
3. textjoin
4. filter
5. unique
6. sequence
若有收获，就点个赞吧
0 人点赞
