標籤:

Power Pivot 中如何執行 lookup+find操作(數據分析操作篇)

如下情景,我們有以對詳細的地址,我們想從中提取城市已作分析。在Excel中我們可以使用lookup+find實現如下效果,如有不明白的同學可以翻閱我之前發布的博文:

Excel多條件查找(LOOKUP的使用揭秘)-雷公子個人博客

那麼這種需求如何在powerpivot中實現呢,有人說可以通過表格生成載入到pivot中,但是,這有一個問題,如果我們是合併多表生成的地域數據呢,可能就需要通過dax鏈接回表的複雜操作趨勢線,會嚴重降低模型的運行效率,如果在powerbi中這種方法根本沒辦法使用,今天我們就介紹下如何通過powerpivot在複雜的地址中直接提取城市名稱:

1、首先,我們有一張地址的明細表和城市的參數表,我們將兩張表分別載入進power pivot中。

2、在地址表中我們添加自定義列,輸入如下公式,回車,我們看到如下效果:

=FIRSTNONBLANK(FILTER(VALUES(城市[城市]),SEARCH(城市[城市],省份[地址],1,0)),1)

所有的城市名稱就被我們提取出來了。

上邊公式為幾個函數嵌套而成,首先我們看下微軟官網對於Firstnonblank函數的介紹:

我們可以看到Firstnonblank通過兩個參數,一個單列,和一個表達式(改公式中使用1),返回了第一個不為空的值。

FILTER(VALUES(城市[城市]),SEARCH(城市[城市],省份[地址],1,0))

這塊類似於find數組查找返回的模式,通過省份上下文篩選,每個表迭代後返回當前省份匹配到的結果。

最後測試下如果匹配到兩個值的話會返回哪一個,我們在城市列別中加入陝西,刷新模型,我們看到第2、3條記錄,都返回陝西,我們在添加一條朝陽,我們發現返回的還是北京,其實這塊我們通過values函數的時候,順序是被打亂了的。

這個模式我們沒必要去記住他的運行原理,在需要的時候,我們複製這段dax直接利用即可,今天就分享這些,大家可以加我們的qq交流群:198096726
推薦閱讀:

一篇誰看了都會分享的Excel快捷鍵操作心法
Excel動態圖表方法大集合,總有一款適合你!
DAX查詢進階:駕駛DAX Studio
實戰②:如何用圖表來表現滿額減後原售價、單本折扣額、折後單價三者的關係

TAG:MicrosoftExcel |