2021042 | Excel高效数据管理与分析
前段时间，接到一家服装企业的Excel培训需求 。上课前，客户把他们工作中经常用的服装销售的明细表格发给了我，说是他们员工每天要花至少一个小时来完成数据的统计和分析操作 。
收到了客户发过来的表格之后 ， 我不由得发出一句感叹：“源数据表格设计得如此不规范，难怪每天要花一个小时来统计不同服装的数量和金额 。”
我们可以看到这个表格主要存在有两个问题：
1、顶部的第一行，也就是表格的标题行 ， 添加了一行列序号，这个序号其实意义不大，上方的ABCD列标签本身就是起着列序号的作用 。规范的表格结构的第一行应该是现在我们看到的第二行的列标题 。
2、所有服装的销售数据都存在复合属性的数据，既有销售的数量，又有产品的总价 。数量还是中文汉字 ， Excel再厉害，也不能对这些中文汉字求和啊 。
那要优化这个表格结构，首先要使用替换功能，使用通配符*号来代表任何一个或多个字符，比如“*两*”，意味着包含两字，可以查找到订购两套的文字，将其替换为数字2 。
这是将标题行至于第一行，并完成了所有数量转为数字的表格完成效果 。
另外再建一张表 ， 将产品名称横向存储，底部的数量就可以直接用SUM求和函数对明细表中的相应列求和，最终统计出数量和金额的合计 。
如果想效率再高一些，最理想的表格结构，应该是把明细表格设计成一维表结构，而不是现在看到的二维表表结构 。
什么是二维表表结构呢？就是既有行分类字段 ， 又有列分类字段 。就好比这张明细表格中我们看到的行分类字段是每一行的客户名称，列分类字段是每一列的产品名称，行列交叉的是销售数量 。
知道了什么是二维表结构 ， 那什么是一维表结构呢？一维表结构就是只有行分类字段，没有列的分类字段 。也就是要把明细表的表结构设计成如下图所示的效果：
那如果我们的表格原本就是二维表的结构，有什么快捷的方法来转换成一维表结构吗？这里可以利用Power Query的逆透视功能轻松完成 。
【想要提高Excel的效率，从规范数据源表格开始】将光标放在数据表的任何一个单元格，点击“数据”选项卡中的“来自表格/区域”，将数据加载到Power Query中 。
在Power Query中 ， 选择前两列，点击“转换”选项卡中的“逆透视列”中的“逆透视其他列” 。
之后删除第一列 ， 修改标题名称，就得到了下图的效果：
我们再将按下图所示的另一张产品表加载到Power Query中 。
选择“主页”中的“合并查询-将查询合并为新查询” 。
分别选择明细表中的“产品”列和产品表中的“产品名称”列，联接种类是“左外部” 。
最后再添加一个计算的金额列，就可以得到下图所示的表格：
我们将数据重新加载回到Excel中，之后就可以用数据透视表，轻松统计数据 。
如果习惯了之前的二维表的明细表存储方式，就可以借助Power Query来做一个转换的一维表 。用Power Query的好处在于，明细表的数据更新了 ， Power Query生成的所有表格都可以自动更新，与之关联的数据透视表也会更新 。这样既方便阅读，也易于统计，以后只需要在明细表中添加新数据，在数据透视表中点击“刷新”功能，1秒轻松计算出最新的数据 。
每天1个小时的工作变成1秒轻松搞定 ， 是不是很厉害呢 。所以想要提高Excel的效率，就要从规范数据源表格开始！