Excel 查询例题

题源：朋友转发的一到面试题，我看了以后觉得很有意思便做了记录。

数据结构：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.得出结论，并用条件格式填充绿色数据条，增强可读性。