EXCEL之Index+Match+Offset+Countif+Vlookup函数
首先必须对EXCEL的几个函数进行深入理解:
函数 | 功能 |
---|---|
INDEX(C1, C2, C3) | C1数组的行列随意,函数返回C1中C2行C2列处的值 |
Match(C1, C2, C3) | 通过C3(精确,上/下模糊)匹配模式,查找C1在C2(单行/列的数组)中的位置 |
Offset(C1, C2, C3, C4, C5) | 以C1为基点,偏C2行C3列,取C4行C5列区域的值 |
Countif(C1, C2) | 在C1(一维数组)区域查找满足C2条件的单元格计数 |
Vlookup(C1, C2, C3) | 在C2区域查找C1的值,并返回C2区域的第C3列 |
问题:各省份2017全年进货金额(全部产品)Top1的厂家?
精准获取业务需求的干净数据,需要对业务(问题)进行全方位解读:通过题目“各省份2017全年进货金额(全部产品)Top1的厂家”可知,需要的信息关键词有省份、2017全年、金额(全部产品)、Top1、厂家。下面进一步看需求方提供的原始数据,根据数据的具体结构来组装嵌套函数即可。
原始数据
在问题所给的原始数据工作簿中,有四个工作表,分别是问题(需求)、订单表、机构表、产品表、价格表。此处只需要看订单表、机构表、价格表的字段结构:
工作表 | 结构 |
---|---|
Sales(订单表) | InstitutionCode;SKU;Quantity;Unit;SalesDate |
Institution (机构表) | InstitutionCode;InstitutionName;InstitutionType;Province;City |
Price(价格表) | SKU;Unit;Price;EffectiveDate |
可以知道,金额等于Sales(订单表)中的Quantity 与Price(价格表)中的Price之积,Sales(订单表)中的Quantity由Sales(订单表)中的SKU和SalesDate唯一标记,即可把SKU和SalesDate当作表Sales(订单表)临时的“主键”。在Price(价格表)中的Price由SKU和EffectiveDate唯一确定。那么将Price(价格表)的Price提取到Sales(订单表)中的Quantity对应的行,就可以算得金额。
数据整理
将Price(价格表)的Price提取到Sales(订单表)中的Quantity对应的行
在这里,EXCEL的Index+Match+Offset+Countif函数的强大功能就显现出来了。首先分析表中Price字段(列)的记录(行)数据特点:Price中的SKU字段为有序型数据,EffectiveDate字段为连续型日期数据,Price字段为连续型数据。每一个Price记录由sku、EffectiveDate记录同时标记。
用下面的公式(重难点)即可为订单表的Quantity字段匹配到对应的price:
// excel 函数公式
=INDEX(Price!C:C,MATCH(B2,Price!$A$2:$A$37,0)
+MATCH(E2,OFFSET(Price!$A$1,MATCH(B2,Price!$A$2:$A$37,0),
3,COUNTIF(Price!A:A,B2),1),1))
计算销售额=Quantity*单价提取(Price)
接下来就是直接计算销售额,并将机构表中的机构名称和省份匹配到订单表中,得到最终可以用于分析的数据表。
// excel 函数公式
=VLOOKUP(A2,Institution!$A$1:$E$17,2,FALSE) #匹配机构名称
=VLOOKUP(A2,Institution!$A$1:$E$17,4,FALSE) #匹配机构所在省份
结果提取
利用整理得到的信息完整的最终可以直接利用的数据表,插入数据透视表,提取各省各机构的销售额。在透视表中插入切片器,切取年份为2017年的数据并以,销售额降序排序。得到如下及结果。