题源：朋友转发的一到面试题，我看了以后觉得很有意思便做了记录。
数据结构：4张表，分别为sales，Institution，Product 和Price，字段如下如。机构的销售数据、机构详细信息、商品信息和价格信息分别在四张表上；需要做跨多张表的引用。
表1：sales
表2：Institution
表3：Product
表4：price
问题：求全年销售金额(全部产品)Top1的医院
难点1：双主键。不同sku在不同时间的价格是不同的，需要做一个双条件（SKU和EffectiveDate）的匹配才能确认对应的价格；
难点2：区间匹配。SalesDate和EffectiveDate又不是一一对应的关系，而是区间对应的关系；
难点3：需要手动划定查询区间。Price表中SKU和EffectiveDate组成的数据组的排列是无序的；
求解思路：
用MATCH 和INDEX 函数一起使用来识别出双主键——解决难点1；
手动设置排序，加上MATCH的匹配模式——解决难点2；
用OFFSET函数来引用一个数据区间——解决难点3；
解题过程：
1.数据清理：数据结构较为清晰，检查空值、错误值，填补几个缺漏的值即可；
2.用vlookup匹配Institituion信息到Sales表上，期间发现#N/A，发现是源数据在文本前后设置了空字符串，用TRIM() 函数去除即可。
=VLOOKUP(TRIM($A2),Institution!$A:$E,2,FALSE)
3.匹配价格信息：1）先手动对price表格做双条件排序，排序主键为SKU和EffectiveDate；2）用MATCH函数定位出要查询的Sales表中的SKU中的初始位置，再用OFFSET函数以初始位置为基点划定引用区域；3）用Index函数定位SKU在对应的引用区域中的位置，得出价格信息
=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))
4.对Sales全表插入数据透视表
5.在透视表中将SalesDate作为筛选项，搜索“2017”筛选出2017年的销售数据，并做排序。
6.得出结论，并用条件格式填充绿色数据条，增强可读性。
网友评论