比VLOOKUP重要10倍,更容易让你晋升Excel高手的3张“王牌”(下篇)

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

*********

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

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

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

因为“关系信息表”的作用是补充说明,所以它的一些信息内容,经常被我们索引到其他表中使用。如果你懂得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值。这样一套完整的多条件查询系统就建立完毕了。

3-3 进一步完善“统计分析表”具有说明类的数据

案例中做的“销量合计”就属于说明类的数据,我们做出满足条件的明细后,总不能让别人再自己计算想要的数据吧!所以就需要我们将一些必要的数据在表头的位置中罗列出来,还可以做出百分比、同期数据对比等等数据分析类的数据,或者也可以将索引出来的明细作为数据源,做成图表辅以可视化展示。

【编后语】Excel数据建模过程的原理部分就给大家介绍完了,能记下多少,又有多少内容能够应用到实际工作中,就要看每个人的理解程度了。在这里还是要给大家一句箴言:任何技能都是练习出来的,多看多用多总结,是学习的必经之路。

****部落窝教育-excel统计分析表制作技巧****

原创:E图表述/部落窝教育(未经同意,请勿转载)

(0)

相关推荐