财务部学习成长培训会

第二课——Excel的逻辑

日期：2023年7月20日
岗位：经理
姓名：杨舒

Excel的正确使用逻辑

获取原始数据，经过整理与分析，最终呈现数据达到分类汇总这一目的是数据核算的核心主线。这一主线其实是大部分数据管理通用的核心主线，可以归纳为数据从原始数据到分类汇总结果的数据流，这也是绝大部分使用者使用Excel的正确使用逻辑，见下面Excel使用逻辑示意图。

关键步骤

如图所示，主线由原始数据到达分类汇总，中间包括3个关键步骤，即
①数据获取、②数据整理与分析、③数据呈现。
Excel为使用者提供了非常丰富的工具完成以上3个步骤。

数据获取：

Excel中的数据一般来自用户直接输入、其他工作簿/工作表以及其他文件。

数据整理与分析：

我们获取的数据多种多样，俗话说，林子大了什么鸟儿都有。同理，数据多了什么问题都可能存在。
常见的问题包括数据不完整（如存在空值或丢失）、数据格式不正确（如数据添加了单位、数字/字符串等类型错误）、数据格式不统一（如单位不统一、同一种数据使用了不同的表达方式、错别字）、数据结构错误（如数据表维数错误，应该使用一维表却使用了二维表）、数据中存在其他干扰内容（如数据中存在图片等对象）。
数据整理分析的过程就是除去数据中存在的无效信息、充分呈现数据中的有效内容、暴露数据的本质特征、探寻不同数据之间的关系，从而为后其最合适、最直观的数据呈现方式打好基础。
数据整理与分析的方法与工具很多，包括Excel自带公式、分列、筛选、汇总、数据透视表、PQ、超级透视表等。

数据呈现：

整理和分析后的数据，要选择最适宜其展示的方式呈现出来，最常见的呈现方式就是统计表以及各种统计图。
这里统计表/图是相对于数据源表而言的，是指由数据源表的数据经过某种逻辑运算，如求和、计数而获得的概括数据源某方面特征的表格或图。
统计表/图常见的特点是交叉性，即统计表/图不同于一维的数据源表，通常具有二维或者三维交叉的特性（图表由于其存在与平面上，天生可以表现出二维性，而由于人类的空间想象能力的存在也可赋予图表三维性，因此Excel也存在三维图表甚至更多维图表，只不过使用二维平面表示罢了）。
而图表的类型多种多样，可以适应于所表达的数据，见下图。
如数据间对比分析可以使用柱形图、条形图、山峰图、滑珠图、蝴蝶图、南丁格尔图，表示数据变化趋势可以使用折线图、面积图，表示占比可以使用饼图、环形图、树状图，表示关联分析可以使用散点图、气泡图，表示地理分析可以使用着色地图、热力图、三维地图等。
无论什么样的统计图，其背后均存在一张统计表；无论什么样的统计表，其均可以由数据源表经过逻辑变换获得。

数据呈现的工具与方法很多，包括Excel自带的各类图表、数据透视图/表，等等。

“三表”概念

在Excel使用逻辑示意图中，我们看到，在从“原始数据”到“分类汇总”的过程中，主要包括3个（或类）表——“数据源表”、“参数表”、“统计表”（包括统计图）。这3张表统称为“三表”。其区别如下：（请牢记）

数据源表

数据源表是记录事件流水的一维表，一维表的特征是数据只有纵向这一个维度，每一行数据是一项；
每行数据不论有几列均属于一项，不同列代表了同一项内容的内部特征。
数据源表通常会随着时间的推移记录越来越多的数据。

参数表

参数表是记录数据源表中需要使用的参数的一维表，它存放的是以下2种数据：
A、能够通过数据源表中推导而获得的数据，
B、与数据源表中存在固定对应关系的数据。
之所以将以上两种数据放在参数表中，是为了最大限度地减少数据源表的大小，从而减少Excel文件体积。
随着时间的推移，参数表并不会像数据源表那样每发生一笔业务就更新一次，只有当分类变化或同级规则变化时才会更新。

统计表

统计表是结合数据源表与参数表中的数据，发掘并呈现规律的表格，通常是二维或多维表。
二维表是指表格中的数据受到列与行2个方向的同时约束。
即不同单元格表示了不同的数据，而不像一维表中不同行才表示了不同的数据，同一行不同列也只算做同一条数据。
而且，受限于表格自身呈现在二维平面的特点，多维表中其他维度也只能叠加在行或者列上。
使用规范格式的数据源表和参数表，利用Excel透视表功能即可快速生成统计表，同时可以生成统计图（即数据透视表对应的数据透视图）。

逻辑总结

利用“三表”可以完全记录事件流水、最大限度减小文件大小、最快生成统计图表，同时在数据源有所改变时直接刷新统计图表。因此，利用“三表”原理构建Excel文件才是Excel最常用的使用逻辑。这或许是原先新建Excel文件默认存在3张工作表的原因之一吧！
综上所述，统计表不是人工做出来的，是通过数据源表和参数表“变”出来的。一张规范、清晰的数据源表是数据分析的起点，决定了生成统计表的工作量大小。这也是一个设计规范的数据源表被称为“天下第一表”的原因所在。

Excel数据源表的构建规范

做表规范千万条，数据规范第一条；
数据结构不规范，清洗数据两行泪！

在上一节中，我们梳理了正确使用Excel从数据源到统计结果的最常规使用逻辑。
这个逻辑适用于各个行业绝大多数的场景。
整个过程包含数据源表、参数表、统计表3张表，以及数据获取、数据整理与分析、数据呈现3个步骤。
我们可以看到，要得到想要的结果，最关键的是一张设计规范的数据源表。
下面我们就来谈谈数据源表的规范问题。

数据源表必须是一维表

在上节中，我们提到了一维表与二维表甚至多维表的区别。
简单地说，一维表即表格的扩展只有一个维度。在几何中，即“线性”。所谓线性，只有长度，没有宽度。在表格中即用行表示一条数据，不同的数据纵向排列。同一个属性只存在于同一列中，不能存在于多列。错误例子如下：

上图中性别“男”“女”同属于“性别”属性的值，应该放在同一列中。

上图中4列的日期同属于“签到日期”属性的值，应该放在同一列中。

上图中4个月份同属于“月份”属性的值，应该放在同一列中。

表中数据纵向扩展

Excel中行多列少，xlsx文件最多可以装入1048576行和16384列，xls文件最多可以装入65536行和256列。所以说，Excel是天生设计用来纵向扩展的，请不要使用横向扩展，如下图为错误做法：

注：
xls和xlsx格式在数据结构上有较大差异，后者比前者体积可以减少60%以上，对于数据量较大的Excel文件，xlsx可以显著减少打开时间，降低内存占用，提升运行速度，减少卡顿。

数据区域保持连续

在Excel中，所有的数据源要放在连续的区域中，不能人为设置空白行或者空白列而达到分离数据的目的。
因为在利用Ctrl加方向键选择单元格、筛选数据、数据排序、利用Ctrl加A全选数据区域、自动添加数据透视表、双击填充柄批量添加公式，Excel默认会识别连续的数据区域作为一个数据单元。
如果有人为的空行空列就会导致其他区域的数据无法被包括其中，导致最终计算、筛选、统计结果的错误。
这也是很多时候结果中漏掉了某些数据的原因。如果大家遇到此类问题，可以从这一点找找原因。
另外一个个人经验是，对于没有内容的单元格要填写“无”或者“0”或者“-”或者其他内容，以此表示此单元格是输入过内容的，与等待输入数据的单元格做区分。同时也可以保持数据区域的连续性。

杜绝使用合并单元格

在数据源表中，使用合并单元格是大忌。
它会破坏表格结构，因为合并单元格中只有左上角的首个单元格有数据，其他单元格都是空值；
同时，合并单元格还会对数据排序及筛选造成麻烦。
合并单元格的使用场景是减少重复文字，这种情况在统计表中最为多见。
因此，合并单元格要使用在统计表中，避免出现在数据源表中。
而统计表的制作无需手动合并，利用数据透视表就可以分分钟制作出一张含有合并单元格的统计表，还不会破坏数据源表的结构。见下图：

上图中，上部为数据源表；下部为数据透视表，它由数据源表直接变化而来。此图展示了利用数据透视表将字段“来源期刊”以合并单元格的方式展现，只需将需要合并的字段放在数据透视表的“行”标签中即可（红框），无需手动合并。如果你一定需要合并单元格又不想在数据源表中直接合并，你可以先使用数据透视表生成带有合并单元格的表格，再复制到其他位置即可（这就是利用数据透视表作为中介可以迅速制作其他表格）。

杜绝人为设置汇总行列

人为设置的汇总行列严重地破坏了数据源表的结构，并且无法即时更新，费时费力。
利用数据透视表可以自动汇总并且即时更新，方便快捷。见下图：

如图，左侧为数据源表，右侧为数据透视表，其“总计”行列、“汇总”行均可以设置显示或者不显示。红、绿框设置显示行或列总计及显示方式，黄框设置是否显示汇总单元格。

单元格中数据杜绝使用复合属性

在正规数据源表中，每列数据要求使用单一属性，避免使用复合属性，这样才能方便计算与统计。
如同在中文中，词是能独立运用的最小语义单位类似，单一属性是指属性被划分为最小的语义单位，通常就是“词”，比如一串数字、一个名字、一个日期、一个物品等。
也可以通过观察数据类型协助判断属性是否单一。
比如数字与字符串同时存在于同一个单元格中，通常预示着复合属性，比如“数字+单位”形式，“52元”“520支”“1314个”。
这种情况通常要将数值和单位分为2列；
多个有等级关系的词组合在一起也有可能是复合属性，可以拆分为单一属性，比如“中国”“河南省”“焦作市”“博爱县”，每个词是无法拆分的单位，但是它们有等级区分，在统计精确度要求达到“县级”时必须放在不同的列中作为单一属性。

采用统一的方式表述相同内容

在数据源表中，每一列的内容要做到相同内容表述形式也相同。
这样的不统一对数据分析造成的影响较大，以下图的数据源为例：

假如你要表达的“电脑”“计算机”“台式电脑”是同一概念（绿色高亮），你最好将这3种名称统一。同理，“笔记本”和“笔记本电脑”也如此（黄色高亮）。如果不这样做，会出现：
①筛选时，原本应该聚合在一项的项目被分开，见下图：

②应用数据透视表时，Excel会将其作为不同的项目分别统计，见下图：

③在利用公式查找、匹配、求和、计数时，如利用vlookup、xlookup、index配match、sumif、countif函数时会导致参与计算的数据不全。
在这里，经常出现却不容易发现的导致表述方式差异的因素是
①各类空格字符，其特点是存在但看不到，如普通空格（Unicode32）、无中断空格（Unicode160）、零宽间隔（Unicode8203）、象形字间隔（即全角空格，Unicode12288），
②个人使用习惯不同的常用符号，其特点是常具有半角和全角两种形式，如逗号（,/，）、冒号（:/：）、分号（;/；）、句点（./．）、小括号（()/（））、中括号（[]/【】）、大括号（{}/｛｝）、数字（如2/２）、英文字母大小写（B/Ｂ）。
对于①，处理方法是利用Excel自带的trim与clean公式，将它们删除。但是，这2个公式也有自己的局限性，有些类型的空格字符是没法删除的，如全角空格。
对于②，处理方法是利用Excel查找替换将字符统一，如将全角字符全部统一为半角字符。

表格标题不可以占用首行

在Excel中，首行通常被软件默认为标题行。所谓标题行，它代表了每列数据的属性，是筛选的依据。表名应该放在工作表标签上，而不应该放在标题行，如下图：

标题行的特性是，当你要筛选数据时，无需选中需要筛选的区域，或者好多人习惯选中要筛选的第一行，而是只需要选中你所要筛选数据的连续区域中的任意一个单元格，这时点击筛选，Excel会在当前区域的首行显示筛选箭头。

规范总结

本节简单介绍了数据源表的构建规范，按照以上规范构建数据源表，可以大大节约数据整理的时间，这些都是为后续的数据分析以及呈现打基础。
个人经验是，你在规范数据源表上花费的时间比使用不规范数据源表而直接做数据分析所用的时间短得多，而且分析结果可以即时更新，几乎不需要重复分析的时间，仅需刷新数据即可。
希望大家重视对数据源表的规范构建。
另外，在构建数据源表的过程中，涉及到许多细节操作，比如公式使用、数据透视表使用、分列、单元格格式、查找替换等，我们在以后的培训中介绍。

Excel数据源表的构建流程

规范、流程两相续，万物二者其中寓；规范教你去哪里，流程教你怎么去！
规范、流程本为一，相辅相成永不离；规范好比千仞岭，流程就是登云梯！

在上一节中，我们描述了使用Excel数据源表的构建规范。
知道了构建规范，下一步就是要懂得如何达到规范。
本节就来谈谈数据源表的构建流程，按照这个流程才能更加省时、省力地构建一张规范的数据源表。

创建数据源的备份副本

我们在获得原始数据后，不论数据是什么样子，首先要将数据源备份，以免后续操作失误导致数据无法恢复到原始状态。
注：我们拿到一张数据源表，应该按照先整体后局部的顺序进行整理。因为一方面这是认识事物的正确逻辑，我们在刚开始还观察不到这张表格的细节问题；另外一方面，先解决整体问题有助于缩减文件大小，优化运行速度，使得后面的细节操作速度更快。

删除表格首行标题

在开始整理时，第1步就是将表格首行的标题删除（如果首行为表格标题的话），因为标题行通常是合并单元格，或者不是合并单元格，但是真正的表头在下一行，这样会影响后续内容的筛选。如下图，好多数据库导出的原始文件均会将第一行作为表题：

区域连续性检验

我们要确保表格中的数据全部连续，你需要以此检查工作簿所有工作表的数据。当然系统导出的数据通常不会存在这样的问题。
检查方法是，选中数据中任意列的一个单元格（通常是第1列），然后按“Ctrl”和方向键“↓”，此时会到达连续数据的最后一行，然后保持“Ctrl”按着不放，再连续多按几下方向键“↓”。
在第1次按“Ctrl”加“↓”时，如果数据区域没有空白行，该数据区域最后一行的单元格会被选中，在第2次按时会跳到工作表的最后一行，即第1048576行（对于.xlsx后缀名）或者第65536行（对于.xls后缀名）；如果数据区域存在空白行，在第2次按时会跳到下一个数据区域的第一行，你需要继续按直到搞清楚数据共有多少行以及何处存在空白行。
同样，你需要依次按“Ctrl”加“→”来检查数据是否存在空白列或者空白单元格。
在搞清楚空白行或者空白列后，就要根据你的实际需要选择删除。删除方法就是使用“Ctrl”或者“Shift”配合鼠标左键不连续（Ctrl）或者连续（Shift）选中行或者列然后右键删除；也可以利用“定位”功能（F5或者Ctrl+G）定位到某一列或行的空白单元格然后选择删除整行或整列。具体操作网上教程太多，这里不赘述。

拆分合并单元格并填充数据

既然是数据源表，我们要杜绝使用合并单元格。这一步就要将数据表中的合并单元格进行拆分并将新生成的空白单元格进行填充。
你需要事先选中该区域或按“Ctrl+a”将当前连续区域选中后点击“取消单元格合并”即可，这部操作对本身没合并的单元格没有影响，可以放心操作，见下图。

由于拆分单元格后只有左上角的单元格保留了数据，其下方和右侧的单元格全部为空，这时我们就要将空白单元格进行填充。在绝大多数情况下，填充的内容与空白单元格最靠近的上方有数据的单元格内容相同。
我们可以利用“定位”功能（F5或者Ctrl+G）定位到空白单元格，然后输入公式，使之等于上方有内容的单元格。
该方法在网上也很容易查到，这里不赘述。你也可以通过修改公式即可让空白单元格填充其左侧、右侧，或者下方的有内容单元格。

删除重复数据行

经过上一步的拆分单元格，我们的数据源表就变得比较整齐了。这一步就要删除冗余数据，即重复数据行。需要利用“数据”选项卡下的“删除重复值”功能，见下图：

这一步操作有一个注意点，就是你要搞清楚数据重复的标准。由于每行数据通常不止1列，不同行的数据可能在某些列是相同的、但是在某些列上是不同的。
你要先确定，某行数据的所有列与其他行的相同列都不重复才能认为这行不同于其他行，还是某行数据只要某些列与其他行的相同列不重复就能认为这行不同于其他行。
另外请注意3点：
A、当你的数据含有标题行，要选中①选项（这项通常都要选中）。
B、Excel删除重复项是删除整行数据，不要误解为只删除重复项目打勾列的数据。
C、Excel删除重复项的机制是在当前数据排序中从上到下保留第1次出现行，第2次及以上出现行将被删除。因此，你可以根据这个规则在删除重复项之前先对数据进行排序，使得你想保留的项目排在最靠上的行。比如：实际应用中经常会出现的场景是，随着时间的推移产生多条数据，它们其他项目基本相同，只是生成时间不同，通常生成时间最新的数据要保留，你就先将整个数据表按照时间降序排列，这样就能将早先生成的数据删除。

检查每列数据属性是否单一

此步用于检查每列数据是否为复合属性。如果为复合属性，就要将不同的属性分开。常用的分列手段有4种：
①函数公式，如left、mid、right函数提取字符串的一部分；
②分列功能，如利用单元格内容中共有的、固定的分隔符或者按照固定的字符数将一个单元格分为2列；
③Excel的Ctrl+E自动填充功能；
④导入PQ进行处理，如利用“转换”标签下的“拆分列”或“提取”功能，也可以使用“添加列”标签下添加“条件列”和“自定义列”功能，如下图：

在检查每列数据属性的同时，也要判断某列数据或者分列生成的新列数据是否具有放入参数表的2个条件（见Excel的正确使用逻辑），如果该列数据能够放入参数表，就可以将该列数据从数据源表中删除。
因为在数据源表中的数据总会不停重复，而参数表中的数据不会重复，或者重复次数较少，这样就能大大减小数据源表的大小。

统一单元格内容与形式

此步骤中，我们要做的是让单元格中的内容统一、形式与数据类型相符。对于同一列中的属性（由于每列表示一个参数，每列的内容可以称为属性），相同的属性要使用相同的、尽量简洁的术语来表述。即在Excel数据源表的构建规范中提到的“采用统一的方式表述相同内容”。
遇到不相同的内容，我们可以选择以下几种能够提高效率的处理方式。
①采用查找替换的方法对内容进行统一，这种方法可以灵活地统一全表的内容，但是需要注意不要将正确的替换为错误的，可以充分利用选中操作范围、匹配单元格、区分大小写等替换对话框中自带的功能为替换增加特异性；
②将数据源表中反复出现的特殊字符记录下来，利用VBA编写处理程序，对指定单元格范围进行替换；
③利用PQ以及M语言（或称M函数）处理数据源表，同样可以完成替换。
④充分利用Excel中已经具备的功能，如分列功能可以更改数据类型，对数字、日期、时间格式最为有用，函数公式可以帮助清除空格、连接文本等。
方法②和③可以实现处理操作的重复利用，尤其是PQ，由于其已经记录的数据处理的具体过程，当数据源结构没有改变只是数据量发生了变化或者数据源日期更新，你只需要刷新数据即可秒获结果，非常建议大家在数据源经常更新但是数据处理过程、步骤不变的场景下应用。

二维表转一维表

在Excel数据源表的构建规范中，我们提到，数据源表一定要是一维表，因为它是变成任何二维、三维等统计表的基础。（如果你发现此时数据源表是二维表或者多维表，那么它大概率就是一张统计表了，并不是数据源表。没有数据源表支撑的统计表是“死数据”，如果你不需要增删改数据也可以不把它当作数据源表而直接使用。）
前面的所有步骤，我们已经消除了合并单元格、拆分了复合属性、统一了单元格数据内容，这就完全具备了二维表向一维表转化的条件。当然有些数据源表本身就是一维表，可以省略此步。

二维表转一维表的方法主要有以下几种，
①PQ逆透视，这是一个最好用、最高效的方法，甚至可以在含有合并单元格的情况下直接完成转换；
②数据透视表的多重合并计算数据区域；
③利用VBA编写程序。

前两种方法在网络上有很多讲解，比如Excel应用大全 | 二维表转换一维表，大家可以自行学习，第③种方法比较灵活，可以根据表格的特征自定义进行转换。

最极端的情况就是多维表，这种情况下，我们的原则是搞清数据结构，每次降低一个维度，分步降维直到一维表。

按逻辑顺序调整列位置

到上一步为止，二维表已经转为一维表了。接下来就是要对生成的一维表做一些调整，对各列属性进行归类和排序，原则是使列的排列更符合逻辑。
调整列顺序的快捷方法就是先选中整列，然后按着Shift键不放，把光标放在选中列的边缘，在光标变成下面的样子时对列进行拖动即可。

去除单元格样式

在上一步，数据源表的内容已经基本完成构建。这一步就要对它的样式进行简单的修改。建议是不使用任何特殊样式，不使用任何字体颜色、单元格背景颜色、表格边框等样式，仅仅调整字体字号、单元格行列宽度高度，使表格看着舒服即可。
这样做的原因是，各种样式只起到装饰作用，没有通用的规范，比如表示上升数据的字体颜色就是红色、下降就是绿色，或者高亮标黄。这些装饰只有设计表格的人才知道，因此你在分享时一定不要带着这些样式分享。
正确的做法应该是再添加一列做为说明列，用文字准确地标明是“上升”“下降”“缺勤”“迟到”“待确认”等等。
老板或者其他同事根本不明白你做的花花绿绿的表格是干嘛的。
另外，额外的样式会增加Excel文件的大小，影响运行速度。

流程总结

本节介绍了构建数据源表的具体流程，按照以上流程构建数据源表可以比较系统化地规避数据源表不应出现的、不利于数据分析的设计错误，同时也能减少数据源表的文件大小，希望能给大家带来一些启发。