用power pivot的DAX函數(shù)做數(shù)據(jù)分析
?
作者:過兒來源:部落窩教育發(fā)布時間:2021-05-28 11:43:17點擊:4041
編按:
一說到數(shù)據(jù)分析,很多人想到就是眾多的函數(shù)或數(shù)據(jù)透視表。而今天,小E給大家講的是比Excel函數(shù),比數(shù)據(jù)透視表強大的多的,被稱為數(shù)據(jù)建模、微軟20年來最偉大發(fā)明的Excel功能——power pivot!下面,大家就一起來看看這個最強數(shù)據(jù)分析利器怎么使用吧!
今天boss突然想知道公司的每個銷售團隊中的銷售尖子的業(yè)績情況。經(jīng)過一番思考,我決定給他展示下每個團隊的業(yè)績前三名的銷售人員的總業(yè)績。目的有了,接下來就是怎么實現(xiàn)的問題。
筆者也借這篇文章給大家分享下函數(shù)和excel中的數(shù)據(jù)分析利器——power pivot!它們是兩種截然不同的問題處理思路。
銷售數(shù)據(jù)截圖:
一、函數(shù)法
邏輯思路:
①首先,明確寫公式的目的。
目的越詳細(xì),思路越清晰。這里的目的是求和,更加精準(zhǔn)的說是區(qū)域求和,區(qū)域就是團隊中的業(yè)績第一名到第三名對應(yīng)的銷售金額所在單元格區(qū)域。
②其次,聯(lián)系目的思考實現(xiàn)過程中的困難點,明確了困難點,才好針對性的用公式。
難點1:數(shù)據(jù)源中需要求和的區(qū)域不連續(xù)。既然我的目的是對區(qū)域進(jìn)行求和,那么寫公式前最好讓需要求和的區(qū)域是連續(xù)的,解決辦法是用排序功能重新排列數(shù)據(jù)源即可。
難點2:明確區(qū)域的起點,起點一旦明確,區(qū)域長度就確定了,即起點及其之后的三個單元格。確定起點位置可以用三個函數(shù)來嵌套,分別是:indirect、address、match;再在嵌套函數(shù)的外層套一個offset,即可確定區(qū)域。最后用sum函數(shù)對區(qū)域求和。
以上就是筆者在寫公式前的邏輯思路。邏輯思路有了,公式就水到渠成了。
Step 01
整理數(shù)據(jù)源,用鼠標(biāo)點擊“開始”,“排序和篩選”,“自定義排序”,然后以“銷售團隊”為主要關(guān)鍵字升序,“銷售金額”為次要關(guān)鍵字降序。
Step 02
將銷售團隊復(fù)制粘貼到F列,然后在“數(shù)據(jù)”選項卡下單擊“刪除重復(fù)值”,實現(xiàn)去重。
在G2中寫下公式
=IF(COUNTIF(A:A,F2)>3,SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1)),SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,1,1)),0,0,COUNTIF(A:A,F2),1)))
寫好后,按回車鍵完成公式輸入。然后用鼠標(biāo)下拉單元格右下角小黑定至G6即可。
函數(shù)解析(為了方便大家閱讀,公式中的圓圈序號即為上一步的公式):
公式的主體部分就是【SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1))】,分成四步講解。
①MATCH(F2,A:A,0)部分是找到F列的銷售團隊在A列中第一次出現(xiàn)的位置對應(yīng)的行數(shù)
②ADDRESS(①,3,,)返回的是$C$27,確定了團隊名稱第一次出現(xiàn)時對應(yīng)的銷售金額的位置,這個位置就是接下來要求和的區(qū)域起點。
③OFFSET(INDIRECT(②),0,0,3,1)返回的是{85;63;53},對應(yīng)的就是野狼團隊前三名的銷售金額。注意,如果不加indirect函數(shù)會返回錯誤,在公式中如果要引用位置,一般都要加indirect函數(shù)作為橋梁。
④SUM(③)這一步即對OFFSET返回的3個值{85;63;53}進(jìn)行求和,返回的便是201。
以上四步就是公式主體部分的解釋。
外層嵌套的if判斷,作用是當(dāng)團隊銷售明細(xì)不足3個時,讓公式計算它本身所有的銷售業(yè)績之和。如果沒有這個判斷,當(dāng)銷售明細(xì)不足3個時,會將別的團隊的業(yè)績算在它身上,造成不公平。
以上是公式法的思路邏輯解釋。
二、下面換一種方法——power pivot。
很多小伙伴對power pivot比較陌生,大家可以簡單認(rèn)為是Excel數(shù)據(jù)透視表(Pivot Table)的升級。其功能比數(shù)據(jù)透視表強大很多,所以也被人們稱作數(shù)據(jù)建模,名字很高大上有木有?更高大上的是它被稱作是微軟20年來最偉大的發(fā)明。而她使用的語言就是DAX 。
下面,就以今天的案例開啟“從工作表函數(shù)到DAX函數(shù)之旅”吧。文中把power pivot簡稱PP。
用PP解決這個問題不需要將數(shù)據(jù)源排序!
Step 01
將數(shù)據(jù)源添加到數(shù)據(jù)模型。
如果你找不到PP選項卡。就在開發(fā)工具下面的“COM加載項”中勾選“Microsoft Power Pivot for Excel ”。
Step 02
進(jìn)入了PP的數(shù)據(jù)模型編輯器,首先將光標(biāo)放在“銷售金額”下的空白處,點擊“主頁”下的“自動匯總”,就自動生產(chǎn)了DAX表達(dá)式。
銷售金額的總和:=SUM([銷售金額])
Step 03
然后在下面的空白處寫上下面這段DAX函數(shù):
銷售量前3:=CALCULATE([銷售金額的總和],TOPN(3,'表1',[銷售金額的總和],0) )
函數(shù)解析:
①“銷售量前3:”這個前綴是自定義的,不同于excel中工作表的函數(shù),DAX函數(shù)需要先自定義一個前綴。
②[銷售金額的總和]是引用上面的銷售金額求和表達(dá)式。
③TOPN(3,'表1',[銷售金額的總和],0)返回的是銷售金額最多的三個明細(xì),它返回的內(nèi)容實質(zhì)是一長表。第一參數(shù)是限定返回的數(shù)量;第二參數(shù)是表的名稱,相當(dāng)于工作表中的sheet名稱;第三參數(shù)0表示降序。
④CALCULATE([銷售金額的總和],③),代表用③返回的表來篩選[銷售金額的總和],其返回的就是最大的三個銷售金額相加的值。
Step 04
DAX函數(shù)寫完之后,用鼠標(biāo)點擊“主頁”,“數(shù)據(jù)透視表”,將內(nèi)容加載到透視表中進(jìn)行分析。
將“銷售團隊”拉到行,然后找到大家剛剛寫的DAX函數(shù)“銷售量前3”,把“銷售量前3”拉到“值”。(在PP中生成的透視表都可以在字段中找到大家寫過的表達(dá)式,其以大家設(shè)置過的前綴顯示)。
如此就用power pivot(PP)完成了銷售統(tǒng)計,并且后續(xù)數(shù)據(jù)源有變化還可以自動刷新。關(guān)于PP如果大家喜歡歡迎轉(zhuǎn)發(fā)分享,筆者以后可以分享更多的數(shù)據(jù)分析利器—PP的知識。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
用數(shù)據(jù)透視表處理公司內(nèi)各部門員工的組內(nèi)排名問題
數(shù)據(jù)透視表送溫暖來了:嘿,鼠標(biāo)拖兩下一次搞定業(yè)績統(tǒng)計和排名!
excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)的三種方法:VBA自動刷新透視表、超級表、現(xiàn)有連接
版權(quán)申明:
本文作者過兒;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!