二维码购物车

部落窝在线教育欢迎您！

热门搜索：
Excel PS 海报炫酷立体字抠图去水印课程预告

图文教程 >
电脑与办公教程 >
EXCEL >
函数技巧千千万，如何制表才关键！（下篇）

函数技巧千千万，如何制表才关键！（下篇）

作者：E图表述来源：部落窝教育发布时间：2019-09-12 15:10:19点击：3039

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

哈喽，大家好！在日常工作中我们会构建很多的表格，其实这些表格无论多少，都可以归结为三大类，分别为“源数据表”、“关系信息表”和“统计分析表”。上次我们提到了一套表格“统计的核心”——“源数据表”，今天继续为大家分享“关系信息表”和“统计分析表”的创建过程。（本篇为该系列文章的下篇）

【前言】

继续前篇内容，当我们有了一个规范的《源数据表》之后，接下来就是需要处理这些数据的过程了，总不能拿着一张明细表给老板交差吧？！下面就来看看在后续的表格制作中，我们还需要注意些什么。

二、“关系信息表”是一套表格“关联的纽带”

“关系信息表”的作用是对“源数据表”中某一个或多个字段内容的信息补充。其建表规则与“源数据表”基本相同。但是“源数据表”中的“关键字”可以多次重复出现，而“关系信息表”中的“关键字”必须是唯一的。用一个图例辅助说明一下：

因为“关系信息表”的作用是补充说明，所以它的一些信息内容，经常被我们索引到其他表中使用。如果你懂得Vlookup函数被搜索区域的首列内容必须是唯一存在的话，那么就应该明白这个表中的索引值必须保证是唯一存在的重要性了。

谨记良言：不要在建表之初，给自己在以后使用的时候挖坑！

除了这些信息性质的内容，还有一些辅助计算的说明性表格也属于此类“关系信息表”的范畴，构建表格的时候，需要我们单独的列出一个Sheet以供引用。

1. 等级评定

此类问题，如果在没有“关系信息表”辅助计算的情况下，我们需要使用常量数组的方式，用函数来解决。

B3单元格函数：=LOOKUP(A3,{0,50,100,200},{"D","C","B","A"})

不仅这样写函数比较麻烦，而且也增加了写函数的难度，起码只有会常量数组的同学能写出来，否则就要用一串If函数嵌套解决。并且上面的这种方式有一个很大的弊端，如果评级条件发生改变，我们就又需要重新设定函数内容。

但是如果我们增加一个“关系信息表”，用表的形式来表述这段文字，那么上面的这些问题就都可以解决，如下：

B3单元格的函数：=VLOOKUP(A3,OFFSET($E$3,,,COUNT($E$3:$E$50),2),2,1)

本身我们直接用=VLOOKUP(A3,$E$3:$F$6,2,1)函数就可以得到结果，但是考虑到以后的评级标准可能会变动，所以我们加入了Offset函数（往期有教程，不做赘述）形成一个动态的引用，所以无论评级怎么改变、增加，统计表中的“等级”字段都会自动调整。

2. 规定时间范围内的有效值

这类问题，在日常工作中也很普遍，比如说单价的问题，无论是进货价格还是销售价格，都不可能是一成不变的，会根据市场原材料的价格时常做出上调、下调。如果我们依然使用固定值，就经常需要更改数据，那么很容易就会因漏改、错改造成数据错误。所以，我们还是需要“关系信息表”来做辅助性计算，以此方式也能最好的规避数据错误的风险。

本例中使用了LOOKUP(1,0/(条件)......结构，完成了多条件查询。（不熟悉该结构的小伙伴可以查看往期教程《VLOOKUP&LOOKUP双雄战（五）：野马崛起！》）用这样的处理方式就可以形成一个很好的价格自动引用的效果了。

三、“统计分析表”是一套表格“效率的表现”

“统计分析表”是按照“源数据表”的内容，结合“关系信息表”的说明（或直接统计），根据具体统计分析的需求，使用Excel的技术手段形成自动化报表，作为最终数据展示的过程。

1、建模的基本原理

统计分析是用“表”的形式表现出来的，但在实际的Excel操作上，其实是一个运算的过程，可以通过“源数据表”直接得到我们需要的内容，也可以结合“关系信息表”一起分析，具体问题具体分析。例如下面的数据：

黄色的区域是函数部分。

C27单元格函数：

=SUMIFS($D$3:$D$6,$A$3:$A$6,">="&$A27,$A$3:$A$6,"<="&$B27,$C$3:$C$6,C$25)

函数解析：

这是多条件求和函数的典型用法，在这里需要强调的是，关于日期范围的条件判断是对起始日期、截止日期两个条件的判断。在满足A3:A6区域大于等于起始日期的A27单元格，且A3:A6区域小于等于截止日期的B27单元格，且C3:C6区域中等于C25单元格的条件下，汇总D3:D6区域的销量。然后复制函数，复选中C27:C29、E27:E29、G27:G29，再选择性粘贴为公式填充，一定要注意相对引用和绝对引用的使用。

D27单元格函数：

=VLOOKUP(C27,OFFSET($A$9,MATCH($A27,$A$10:$A$21,0),3,COUNTIF($A$10:$A$21,$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使用者都对这个需求充满了向往，那就一起来看看是如何创建的吧，先看一个效果图：

3.1 在源数据表的首列插入两个空列，作为辅助列使用

在B3单元格输入下面内容后，下拉填充：

=IF($D$11="",1,IF(C3>=$D$11,1,0))+IF($D$12="",1,IF(C3<=$D$12,1,0))+IF($D$13="",1,IF(D3=$D$13,1,0))+IF($D$14="",1,IF(E3=$D$14,1,0))+IF($D$15="",1,IF(F3=$D$15,1,0))

函数解析：

因为我们的条件设置了5个内容，当条件为空（不填），或者满足条件的时候，计为1否则为0，目的在于如果这个函数返回值是5，那么说明5个条件均满足，是我们需要的记录条；如果不是5，那么说明不是我们需要统计分析的记录条。

在A3单元格输入下面内容后，下拉填充：

=IF(B3<>5,"",MAX($A$2:A2)+1)

函数解析：

如果B列的值不等于5，则返回空值；否则返回此单元格在同列上方的区域中的最大值+1。目的是标记出我们需要使用的记录条，并且给予一个“关键字”的赋值。

这里使用了相对引用和绝对引用的知识，不是今天的重点，大家可以找找相关的内容补充一下。

3.2 制作导出新数据的展示区域

C18单元格函数输入后，复制此单元格，并选择性粘贴——公式到C18:G25区域：

=IFERROR(VLOOKUP(ROW(C1),$A$2:$G$6,MATCH(C$17,$A$2:$G$2,0),0),"")

函数解析：

通过Row函数，可以返回行号，下拉填充后，就可以得到一串1、2、3……的序号，这样就可以和辅助列2的“关键字”相对应；再使用Match函数，可以得到字段在“源数据表”中的列序，以此作为Vlookup函数的第三参数，通过Vlookup函数就可以找到满足条件的记录条；最后再以Iferror函数，去掉Vlookup函数产生的#N/A值。这样一套完整的多条件查询系统就建立完毕了。