二維碼 購(gòu)物車(chē)
部落窩在線教育歡迎您!

用power pivot的DAX函數(shù)做數(shù)據(jù)分析

?

作者:過(guò)兒來(lái)源:部落窩教育發(fā)布時(shí)間:2021-05-28 11:43:17點(diǎn)擊:4437

分享到:
0
收藏    收藏人氣:0人
版權(quán)說(shuō)明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

一說(shuō)到數(shù)據(jù)分析,很多人想到就是眾多的函數(shù)或數(shù)據(jù)透視表。而今天,小E給大家講的是比Excel函數(shù),比數(shù)據(jù)透視表強(qiáng)大的多的,被稱(chēng)為數(shù)據(jù)建模、微軟20年來(lái)最偉大發(fā)明的Excel功能——power pivot!下面,大家就一起來(lái)看看這個(gè)最強(qiáng)數(shù)據(jù)分析利器怎么使用吧!

 

 

今天boss突然想知道公司的每個(gè)銷(xiāo)售團(tuán)隊(duì)中的銷(xiāo)售尖子的業(yè)績(jī)情況。經(jīng)過(guò)一番思考,我決定給他展示下每個(gè)團(tuán)隊(duì)的業(yè)績(jī)前三名的銷(xiāo)售人員的總業(yè)績(jī)。目的有了,接下來(lái)就是怎么實(shí)現(xiàn)的問(wèn)題。

 

筆者也借這篇文章給大家分享下函數(shù)和excel中的數(shù)據(jù)分析利器——power pivot!它們是兩種截然不同的問(wèn)題處理思路。

 

銷(xiāo)售數(shù)據(jù)截圖:

 

 

一、函數(shù)法

 

邏輯思路:

①首先,明確寫(xiě)公式的目的。

目的越詳細(xì),思路越清晰。這里的目的是求和,更加精準(zhǔn)的說(shuō)是區(qū)域求和,區(qū)域就是團(tuán)隊(duì)中的業(yè)績(jī)第一名到第三名對(duì)應(yīng)的銷(xiāo)售金額所在單元格區(qū)域。

 

②其次,聯(lián)系目的思考實(shí)現(xiàn)過(guò)程中的困難點(diǎn),明確了困難點(diǎn),才好針對(duì)性的用公式。

難點(diǎn)1:數(shù)據(jù)源中需要求和的區(qū)域不連續(xù)。既然我的目的是對(duì)區(qū)域進(jìn)行求和,那么寫(xiě)公式前最好讓需要求和的區(qū)域是連續(xù)的,解決辦法是用排序功能重新排列數(shù)據(jù)源即可。

難點(diǎn)2:明確區(qū)域的起點(diǎn),起點(diǎn)一旦明確,區(qū)域長(zhǎng)度就確定了,即起點(diǎn)及其之后的三個(gè)單元格。確定起點(diǎn)位置可以用三個(gè)函數(shù)來(lái)嵌套,分別是:indirect、address、match;再在嵌套函數(shù)的外層套一個(gè)offset,即可確定區(qū)域。最后用sum函數(shù)對(duì)區(qū)域求和。

 

以上就是筆者在寫(xiě)公式前的邏輯思路。邏輯思路有了,公式就水到渠成了。

 

Step 01

整理數(shù)據(jù)源,用鼠標(biāo)點(diǎn)擊“開(kāi)始”,“排序和篩選”,“自定義排序”,然后以“銷(xiāo)售團(tuán)隊(duì)為主要關(guān)鍵字升序,“銷(xiāo)售金額”為次要關(guān)鍵字降序。

 

 

Step 02

將銷(xiāo)售團(tuán)隊(duì)復(fù)制粘貼到F列,然后在“數(shù)據(jù)”選項(xiàng)卡下單擊“刪除重復(fù)值”,實(shí)現(xiàn)去重。

 

G2中寫(xiě)下公式

=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)))

寫(xiě)好后,按回車(chē)鍵完成公式輸入。然后用鼠標(biāo)下拉單元格右下角小黑定至G6即可。

 

 

函數(shù)解析(為了方便大家閱讀,公式中的圓圈序號(hào)即為上一步的公式):

公式的主體部分就是【SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1))】,分成四步講解。

MATCH(F2,A:A,0)部分是找到F列的銷(xiāo)售團(tuán)隊(duì)在A列中第一次出現(xiàn)的位置對(duì)應(yīng)的行數(shù)

ADDRESS(,3,,)返回的是$C$27,確定了團(tuán)隊(duì)名稱(chēng)第一次出現(xiàn)時(shí)對(duì)應(yīng)的銷(xiāo)售金額的位置,這個(gè)位置就是接下來(lái)要求和的區(qū)域起點(diǎn)。

OFFSET(INDIRECT(),0,0,3,1)返回的是{85;63;53},對(duì)應(yīng)的就是野狼團(tuán)隊(duì)前三名的銷(xiāo)售金額。注意,如果不加indirect函數(shù)會(huì)返回錯(cuò)誤,在公式中如果要引用位置,一般都要加indirect函數(shù)作為橋梁。

SUM()這一步即對(duì)OFFSET返回的3個(gè)值{85;63;53}進(jìn)行求和,返回的便是201

 

以上四步就是公式主體部分的解釋。

 

外層嵌套的if判斷,作用是當(dāng)團(tuán)隊(duì)銷(xiāo)售明細(xì)不足3個(gè)時(shí),讓公式計(jì)算它本身所有的銷(xiāo)售業(yè)績(jī)之和。如果沒(méi)有這個(gè)判斷,當(dāng)銷(xiāo)售明細(xì)不足3個(gè)時(shí),會(huì)將別的團(tuán)隊(duì)的業(yè)績(jī)算在它身上,造成不公平。

 

以上是公式法的思路邏輯解釋。

 

二、下面換一種方法——power pivot

 

很多小伙伴對(duì)power pivot比較陌生,大家可以簡(jiǎn)單認(rèn)為是Excel數(shù)據(jù)透視表(Pivot Table)的升級(jí)。其功能比數(shù)據(jù)透視表強(qiáng)大很多,所以也被人們稱(chēng)作數(shù)據(jù)建模,名字很高大上有木有?更高大上的是它被稱(chēng)作是微軟20年來(lái)最偉大的發(fā)明。而她使用的語(yǔ)言就是DAX 。

 

下面,就以今天的案例開(kāi)啟“從工作表函數(shù)到DAX函數(shù)之旅”吧。文中把power pivot簡(jiǎn)稱(chēng)PP。

 

PP解決這個(gè)問(wèn)題不需要將數(shù)據(jù)源排序!

 

Step 01

將數(shù)據(jù)源添加到數(shù)據(jù)模型。

 

 

如果你找不到PP選項(xiàng)卡。就在開(kāi)發(fā)工具下面的“COM加載項(xiàng)”中勾選“Microsoft Power Pivot for Excel ”。

 

 

Step 02

進(jìn)入了PP的數(shù)據(jù)模型編輯器,首先將光標(biāo)放在“銷(xiāo)售金額”下的空白處,點(diǎn)擊“主頁(yè)”下的“自動(dòng)匯總”,就自動(dòng)生產(chǎn)了DAX表達(dá)式。

 

銷(xiāo)售金額的總和:=SUM([銷(xiāo)售金額])

 

 

Step 03

然后在下面的空白處寫(xiě)上下面這段DAX函數(shù):

 

銷(xiāo)售量前3:=CALCULATE([銷(xiāo)售金額的總和],TOPN(3,'1',[銷(xiāo)售金額的總和],0) )

 

函數(shù)解析:

①“銷(xiāo)售量前3:”這個(gè)前綴是自定義的,不同于excel中工作表的函數(shù),DAX函數(shù)需要先自定義一個(gè)前綴。

[銷(xiāo)售金額的總和]是引用上面的銷(xiāo)售金額求和表達(dá)式。

TOPN(3,'1',[銷(xiāo)售金額的總和],0)返回的是銷(xiāo)售金額最多的三個(gè)明細(xì),它返回的內(nèi)容實(shí)質(zhì)是一長(zhǎng)表。第一參數(shù)是限定返回的數(shù)量;第二參數(shù)是表的名稱(chēng),相當(dāng)于工作表中的sheet名稱(chēng);第三參數(shù)0表示降序。

CALCULATE([銷(xiāo)售金額的總和],③),代表用③返回的表來(lái)篩選[銷(xiāo)售金額的總和],其返回的就是最大的三個(gè)銷(xiāo)售金額相加的值。

 

Step 04

DAX函數(shù)寫(xiě)完之后,用鼠標(biāo)點(diǎn)擊“主頁(yè)”,“數(shù)據(jù)透視表”,將內(nèi)容加載到透視表中進(jìn)行分析。

 


 

將“銷(xiāo)售團(tuán)隊(duì)”拉到行,然后找到大家剛剛寫(xiě)的DAX函數(shù)“銷(xiāo)售量前3,把銷(xiāo)售量前3”拉到“值”。(在PP中生成的透視表都可以在字段中找到大家寫(xiě)過(guò)的表達(dá)式,其以大家設(shè)置過(guò)的前綴顯示)。

 

 

如此就用power pivot(PP完成了銷(xiāo)售統(tǒng)計(jì),并且后續(xù)數(shù)據(jù)源有變化還可以自動(dòng)刷新。關(guān)于PP如果大家喜歡歡迎轉(zhuǎn)發(fā)分享,筆者以后可以分享更多的數(shù)據(jù)分析利器—PP的知識(shí)。

 

本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。

Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你!

掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel

IMG_256

相關(guān)推薦:

用數(shù)據(jù)透視表處理公司內(nèi)各部門(mén)員工的組內(nèi)排名問(wèn)題

數(shù)據(jù)透視表送溫暖來(lái)了:嘿,鼠標(biāo)拖兩下一次搞定業(yè)績(jī)統(tǒng)計(jì)和排名!

excel數(shù)據(jù)透視表動(dòng)態(tài)刷新數(shù)據(jù)的三種方法:VBA自動(dòng)刷新透視表、超級(jí)表、現(xiàn)有連接

統(tǒng)計(jì)中國(guó)式排名的三種方法,一定有一個(gè)適合你

版權(quán)申明:

本文作者過(guò)兒;同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。