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

　　编按：哈喽，大家好！在日常工作中我们会构建很多的表格，其实这些表格无论多少，都可以归结为三大类，分别为“源数据表”、“关系信息表”和“统计分析表”。上次我们提到了一套表格“统计的核心”“源数据表”，今天继续为大家分享“关系信息表”和“统计分析表”的创建过程。（本篇为该系列文章的下篇）
　　【前言】继续前篇内容，当我们有了一个规范的《源数据表》之后，接下来就是需要处理这些数据的过程了，总不能拿着一张明细表给老板交差吧？！下面就来看看在后续的表格制作中，我们还需要注意些什么。
　　二、“关系信息表”是一套表格“关联的纽带”
　　“关系信息表”的作用是对“源数据表”中某一个或多个字段内容的信息补充。其建表规则与“源数据表”基本相同。但是“源数据表”中的“关键字”可以多次重复出现，而“关系信息表”中的“关键字”必须是唯一的。用一个图例辅助说明一下：
　　因为“关系信息表”的作用是补充说明，所以它的一些信息内容，经常被我们索引到其他表中使用。如果你懂得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（条件）。。。。。。结构，完成了多条件查询。（不熟悉该结构的小伙伴可以查看往期教程《VLOOKUPLOOKUP双雄战（五）：野马崛起！》）用这样的处理方式就可以形成一个很好的价格自动引用的效果了。
　　三、“统计分析表”是一套表格“效率的表现”
　　“统计分析表”是按照“源数据表”的内容，结合“关系信息表”的说明（或直接统计），根据具体统计分析的需求，使用Excel的技术手段形成自动化报表，作为最终数据展示的过程。
　　1、建模的基本原理
　　统计分析是用“表”的形式表现出来的，但在实际的Excel操作上，其实是一个运算的过程，可以通过“源数据表”直接得到我们需要的内容，也可以结合“关系信息表”一起分析，具体问题具体分析。例如下面的数据：
　　黄色的区域是函数部分。
　　C27单元格函数：
　　SUMIFS（D3：D6，A3：A6，A27，A3：A6，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（C3D11，1，0））IF（D12，1，IF（C3D12，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（B35，，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图表述部落窝教育（未经同意，请勿转载）