制表基础规范的制表理念和思路让效率

　　编按：哈喽，大家好！在日常工作中我们会构建很多的表格，其实这些表格无论多少，都可以归结为三大类，分别为源数据表、关系信息表和统计分析表。上次我们提到了一套表格统计的核心源数据表，今天继续为大家分享关系信息表和统计分析表的创建过程。（本篇为该系列文章的下篇）
　　【前言】继续前篇内容，当我们有了一个规范的《源数据表》之后，接下来就是需要处理这些数据的过程了，总不能拿着一张明细表给老板交差吧？！下面就来看看在后续的表格制作中，我们还需要注意些什么。
　　二、关系信息表是一套表格关联的纽带
　　关系信息表的作用是对源数据表中某一个或多个字段内容的信息补充。其建表规则与源数据表基本相同。但是源数据表中的关键字可以多次重复出现，而关系信息表中的关键字必须是唯一的。用一个图例辅助说明一下：
　　因为关系信息表的作用是补充说明，所以它的一些信息内容，经常被我们索引到其他表中使用。如果你懂得VLOOKUP函数被搜索区域的首列内容必须是唯一存在的话，那么就应该明白这个表中的索引值必须保证是唯一存在的重要性了。
　　谨记良言：不要在建表之初，给自己在以后使用的时候挖坑！
　　除了这些信息性质的内容，还有一些辅助计算的说明性表格也属于此类关系信息表的范畴，构建表格的时候，需要我们单独的列出一个Sheet以供引用。
　　1。等级评定
　　此类问题，如果在没有关系信息表辅助计算的情况下，我们需要使用常量数组的方式，用函数来解决。
　　B3单元格函数：LOOKUP（A3，｛0，50，100，200｝，｛D，C，B，A｝）
　　不仅这样写函数比较麻烦，而且也增加了写函数的难度，起码只有会常量数组的同学能写出来，否则就要用一串IF函数嵌套解决。并且上面的这种方式有一个很大的弊端，如果评级条件发生改变，我们就又需要重新设定函数内容。
　　但是如果我们增加一个关系信息表，用表的形式来表述这段文字，那么上面的这些问题就都可以解决，如下：
　　B3单元格的函数：VLOOKUP（A3，OFFSET（E3，，，COUNT（E3：E50），2），2，1）
　　本身我们直接用VLOOKUP（A3，E3：F6，2，1）函数就可以得到结果，但是考虑到以后的评级标准可能会变动，所以我们加入了OFFSET函数（往期有教程，不做赘述）形成一个动态的引用，所以无论评级怎么改变、增加，统计表中的等级字段都会自动调整。
　　2。规定时间范围内的有效值
　　这类问题，在日常工作中也很普遍，比如说单价的问题，无论是进货价格还是销售价格，都不可能是一成不变的，会根据市场原材料的价格时常做出上调、下调。如果我们依然使用固定值，就经常需要更改数据，那么很容易就会因漏改、错改造成数据错误。所以，我们还是需要关系信息表来做辅助性计算，以此方式也能最好的规避数据错误的风险。
　　本例中使用了LOOKUP（1，0（条件）。。。。。。结构，完成了多条件查询。（不熟悉该结构的小伙伴可以查看往期教程《VLOOKUPamp；amp；LOOKUP双雄战（五）：野马崛起！》）用这样的处理方式就可以形成一个很好的价格自动引用的效果了。
　　三、统计分析表是一套表格效率的表现
　　统计分析表是按照源数据表的内容，结合关系信息表的说明（或直接统计），根据具体统计分析的需求，使用Excel的技术手段形成自动化报表，作为最终数据展示的过程。
　　1、建模的基本原理
　　统计分析是用表的形式表现出来的，但在实际的Excel操作上，其实是一个运算的过程，可以通过源数据表直接得到我们需要的内容，也可以结合关系信息表一起分析，具体问题具体分析。例如下面的数据：
　　黄色的区域是函数部分。
　　C27单元格函数：
　　SUMIFS（D3：D6，A3：A6，amp；gt；amp；amp；A27，A3：A6，amp；lt；amp；amp；B27，C3：C6，C25）
　　函数解析：这是多条件求和函数的典型用法，在这里需要强调的是，关于日期范围的条件判断是对起始日期、截止日期两个条件的判断。在满足A3：A6区域大于等于起始日期的A27单元格，且A3：A6区域小于等于截止日期的B27单元格，且C3：C6区域中等于C25单元格的条件下，汇总D3：D6区域的销量。然后复制函数，复选中C27：C29、E27：E29、G27：G29，再选择性粘贴为公式填充，一定要注意相对引用和绝对引用的使用。
　　D27单元格函数：
　　VLOOKUP（C27，OFFSET（A9，MATCH（A27，A10：A21，0），3，COUNTIF（A10：A21，A27），2），2，1）
　　函数解析：这个函数看着有一点不好理解吧，如果嵌套函数不好理解的时候，我们可以拆分来看。OFFSET函数的返回值，被作为VLOOKUP函数的第二参数；而MATCH函数和COUNTIF函数被作为OFFSET函数的第二、四参数。
　　（1）通过MATCH函数，确定《关系信息表》中起始日期第一次出现的序号，作为行偏移的参数，再通过COUNTIF函数，确定《关系信息表》中本月中的等级条件有几行，作为行扩展的参数。
　　（2）OFFSET通过5个参数的运算，返回了当月等级评定标准的区域范围，并作为VLOOKUP的第二参数参与运算。
　　（3）最后通过VLOOKUP的模糊查询找到对应的等级。
　　然后复制函数，复选中D27：D29、F27：F29、H27：H29，再选择性粘贴为公式，进行填充。同样需要注意相对引用和绝对引用的使用。
　　篇幅有限，案例中的数据内容比较少，但是意图和思路还是可以表述清晰：销量是基于源数据表使用函数得到每个销售员每个月的销量合计；等级是按照返回的销量结合关系信息表的各月各类参数使用函数得到。上面的这个思路，就是我们俗称建模的基本原理！
　　2、统计分析表也可以是另一个表的新数据源
　　如果我们要直接用这个案例生成统计分销表2的话，在不使用VBA的情况下还是有点难度的。这里我们可以通过统计分析表1的内容进行一个过渡，把复杂问题简单化。上例的黄色区域是函数部分，都是SUMIF和VLOOKUP函数的基础用法，在这里就不列出来了，有兴趣的同学可以按照这个思路去试着做一下。（不动手练习，看多少文章都学不会！）
　　3、多条件查询式的统计分析表，让你的数据模型初具规模
　　看一下这个需求，作者相信每个Excel使用者都对这个需求充满了向往，那就一起来看看是如何创建的吧，先看一个效果图：
　　31在源数据表的首列插入两个空列，作为辅助列使用
　　在B3单元格输入下面内容后，下拉填充：
　　IF（D11，1，IF（C3amp；gt；D11，1，0））IF（D12，1，IF（C3amp；lt；D12，1，0））IF（D13，1，IF（D3D13，1，0））IF（D14，1，IF（E3D14，1，0））IF（D15，1，IF（F3D15，1，0））
　　函数解析：因为我们的条件设置了5个内容，当条件为空（不填），或者满足条件的时候，计为1否则为0，目的在于如果这个函数返回值是5，那么说明5个条件均满足，是我们需要的记录条；如果不是5，那么说明不是我们需要统计分析的记录条。
　　在A3单元格输入下面内容后，下拉填充：
　　IF（B3amp；lt；amp；gt；5，，MAX（A2：A2）1）
　　函数解析：如果B列的值不等于5，则返回空值；否则返回此单元格在同列上方的区域中的最大值1。目的是标记出我们需要使用的记录条，并且给予一个关键字的赋值。
　　这里使用了相对引用和绝对引用的知识，不是今天的重点，大家可以找找相关的内容补充一下。
　　32制作导出新数据的展示区域
　　C18单元格函数输入后，复制此单元格，并选择性粘贴公式到C18：G25区域：
　　IFERROR（VLOOKUP（ROW（C1），A2：G6，MATCH（C17，A2：G2，0），0），）
　　函数解析：通过ROW函数，可以返回行号，下拉填充后，就可以得到一串1、2、3的序号，这样就可以和辅助列2的关键字相对应；再使用MATCH函数，可以得到字段在源数据表中的列序，以此作为VLOOKUP函数的第三参数，通过VLOOKUP函数就可以找到满足条件的记录条；最后再以IFERROR函数，去掉VLOOKUP函数产生的NA值。这样一套完整的多条件查询系统就建立完毕了。
　　33进一步完善统计分析表具有说明类的数据
　　案例中做的销量合计就属于说明类的数据，我们做出满足条件的明细后，总不能让别人再自己计算想要的数据吧！所以就需要我们将一些必要的数据在表头的位置中罗列出来，还可以做出百分比、同期数据对比等等数据分析类的数据，或者也可以将索引出来的明细作为数据源，做成图表辅以可视化展示。
　　【编后语】Excel数据建模过程的原理部分就给大家介绍完了，能记下多少，又有多少内容能够应用到实际工作中，就要看每个人的理解程度了。在这里还是要给大家一句箴言：任何技能都是练习出来的，多看多用多总结，是学习的必经之路。
　　部落窝教育excel统计分析表制作技巧
　　原创：E图表述部落窝教育（未经同意，请勿转载）