转载
适用于EXCEL相关,与ETL关系不大
前两天，有个网友（就叫小B吧）发邮件，向我求助。表格的内容如下。
表格中，每隔14列是一个【项目信息表】，横向向右铺开，格式完全一样，只是具体的“编号”、“项目名称”等信息不同。
你能猜到，求助者小B的问题是什么吗？
——-思考时间——-
——-思考时间——-
——-思考时间——-
你可能已经猜对了，小B求助的问题是：
把每个“项目信息表”中的，<计量表编号>信息都提取出来，汇总到同一个sheet中。
好在每个“项目信息表”，都占据了相同的列宽（14列），还是有规律的，所以汇总起来也不难，我把这类问题叫做“等差汇总”。
我们来看一下具体如何实现。
临时对策
首先，我根据小B的需求，使用Index公式，提取<计量表编号>的内容，不过这只是临时的对策，至于为什么，后面会再详细说明。
1. 分析数据列号
首先使用COLUMN函数，把每一列的列号标注出来，分析一下<计量表编号>的规律。
不难看出：第1个到第4个<计量表编号>的列号依次是，3、17、31、45，每个数字之间相差14，也就是一个等差数列。然后我可以通过简单的换算，得到下面的公式：
其中的14的倍数，我们可以通过ROW()函数计算行号来获得，这样就有了下面的公式：
我们把公式，填充到汇总表中，这样就把<计量表编号>都提取出来了，实现了小B的需求。
但这只是临时的对策，我相信，后面老板提出更多的统计需求，会让小B再度陷入困惑。
问题分析
现在我们只是把<计量表编号>汇总出来了，但这个信息，没有统计的意义，更有分析价值的信息是：
工程数量排名，哪个编号的工程数量最多？
总共有多少个项目？
这些项目里有没有重复的内容？
随便一个问题，都会让这个漂亮的【项目信息表】变的“丑陋”，所以我们有必要，把每个单元格的信息，都提取出来，输出一个【项目清单】。
推荐你学习一下伍昊老师的《你早该这么玩Excel》，学习一下“天下第一表”的概念。
有了这个【项目清单】，我们就可以通过排序、透视表等方法，统计出前面提到的，更有价值的信息了。
接下来，我们来看一下，具体如何实现吧。
解决方案
解决方案，我大致分成了3个部分：
整理数据表
设计动态表格
动态图片技巧
我们逐一学习一下。
1. 整理数据表
如前面所说，整理数据表，就是把每个栏位的信息，都提取出来，汇总到同一个表格里。
方法和我们提取<计量表编号>是类似的。我们只需要根据各个信息所在的行和列，对应的修改公式就可以了。
2. 设计动态表格
虽然我们整理出来的数据表，可以方便的统计出各种信息，但是小B又跳出来说了：
我们老板就是要有一个，这样的【项目信息表】，方便查看，必要的时候也可以打印出来。你整理出来的数据表，看着就没那么漂亮了。
输出可视化的表格，是没有问题的，但是，不要一味的横向的图纸表格，为此，我们可以设计一个动态的表格，方便的输出，每一个【项目信息表】。
首先我们看一下动态的效果。选择不同的<计量表编号>，就可以实时的查项目信息。
实现的原理也不复杂。
首先使用【数据有效性】，添加一个<计量表编号>的下拉菜单。
然后在A2单元格，使用Match函数，获取<计量表编号>在【项目清单】sheet中的位置。
最后，在<项目名称><部位><工程数量>等各个单元格里，使用Index函数，应用出【项目清单】中的数据。
图纸信息的动态引用需要使用EXCEL的照相机功能.
修改好表格之后，我们再回头看一下，前面我提到的3个问题：
1. 工程数量排名，哪个编号的工程数量最多？
很简单，在【项目清单】中，对“工程数量”排序就可以，找到了。
2. 总共有多少个项目？
在【项目清单】中，使用CTRL+↓键，快速定位最后一行，看一下序号就知道了。
3. 这些项目里有没有重复的内容？
在【项目清单】中，对<计量表编号>列，使用重复值条件格式，就可以快速定位重复内容了。
网友评论