題源：朋友轉(zhuǎn)發(fā)的一到面試題，我看了以后覺得很有意思便做了記錄。

數(shù)據(jù)結(jié)構(gòu)：4張表，分別為sales，Institution，Product 和Price，字段如下如。機(jī)構(gòu)的銷售數(shù)據(jù)、機(jī)構(gòu)詳細(xì)信息、商品信息和價格信息分別在四張表上；需要做跨多張表的引用。

表1：sales

表2：Institution

表3：Product

表4：price

問題：求全年銷售金額(全部產(chǎn)品)Top1的醫(yī)院

難點1：雙主鍵。不同sku在不同時間的價格是不同的，需要做一個雙條件（SKU和EffectiveDate）的匹配才能確認(rèn)對應(yīng)的價格；

難點2：區(qū)間匹配。SalesDate和EffectiveDate又不是一一對應(yīng)的關(guān)系，而是區(qū)間對應(yīng)的關(guān)系；

難點3：需要手動劃定查詢區(qū)間。Price表中SKU和EffectiveDate組成的數(shù)據(jù)組的排列是無序的；

求解思路：

用MATCH 和INDEX 函數(shù)一起使用來識別出雙主鍵——解決難點1；

手動設(shè)置排序，加上MATCH的匹配模式——解決難點2；

用OFFSET函數(shù)來引用一個數(shù)據(jù)區(qū)間——解決難點3；

解題過程：

1.數(shù)據(jù)清理：數(shù)據(jù)結(jié)構(gòu)較為清晰，檢查空值、錯誤值，填補(bǔ)幾個缺漏的值即可；

2.用vlookup匹配Institituion信息到Sales表上，期間發(fā)現(xiàn)#N/A，發(fā)現(xiàn)是源數(shù)據(jù)在文本前后設(shè)置了空字符串，用TRIM() 函數(shù)去除即可。

=VLOOKUP(TRIM($A2),Institution!$A:$E,2,FALSE)

3.匹配價格信息：1）先手動對price表格做雙條件排序，排序主鍵為SKU和EffectiveDate；2）用MATCH函數(shù)定位出要查詢的Sales表中的SKU中的初始位置，再用OFFSET函數(shù)以初始位置為基點劃定引用區(qū)域；3）用Index函數(shù)定位SKU在對應(yīng)的引用區(qū)域中的位置，得出價格信息

=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全表插入數(shù)據(jù)透視表

5.在透視表中將SalesDate作為篩選項，搜索“2017”篩選出2017年的銷售數(shù)據(jù)，并做排序。

6.得出結(jié)論，并用條件格式填充綠色數(shù)據(jù)條，增強(qiáng)可讀性。