每天一點小技能
職場打怪不得慫
編按：一說到數據分析，很多人第一時間想到的就是數據透視表。而今天，小E給大家講的不是數據透視表，而是一個比數據透視表強大的多的，被稱為數據建模、微軟20年來最偉大發(fā)明的Excel功能——power pivot！下面，大家就一起來看看這個最強數據分析利器怎么使用吧！
今天boss突然想知道公司的每個銷售團隊中的銷售尖子的業(yè)績情況。經過一番思考，我決定給他展示下每個團隊的業(yè)績前三名的銷售人員的總業(yè)績。目的有了，接下來就是怎么實現(xiàn)的問題。
筆者也借這篇文章給大家分享下函數和excel中的數據分析利器——power pivot！它們是兩種截然不同的問題處理思路。
銷售數據截圖：
1
函數法
邏輯思路：
①首先，明確寫公式的目的。
目的越詳細，思路越清晰。這里的目的是求和，更加精準的說是區(qū)域求和，區(qū)域就是團隊中的業(yè)績第一名到第三名對應的銷售金額所在單元格區(qū)域。
②其次，聯(lián)系目的思考實現(xiàn)過程中的困難點，明確了困難點，才好針對性的用公式。
難點1：數據源中需要求和的區(qū)域不連續(xù)。既然我的目的是對區(qū)域進行求和，那么寫公式前最好讓需要求和的區(qū)域是連續(xù)的，解決辦法是用排序功能重新排列數據源即可。
難點2：明確區(qū)域的起點，起點一旦明確，區(qū)域長度就確定了，即起點及其之后的三個單元格。確定起點位置可以用三個函數來嵌套，分別是：indirect、address、match；再在嵌套函數的外層套一個offset，即可確定區(qū)域。最后用sum函數對區(qū)域求和。
以上就是筆者在寫公式前的邏輯思路。邏輯思路有了，公式就水到渠成了。
Step.01
整理數據源，用鼠標點擊“開始”，“排序和篩選”，“自定義排序”，然后以“銷售團隊”為主要關鍵字升序，“銷售金額”為次要關鍵字降序。
公眾號回復：入群，下載課件
Step.02
將銷售團隊復制粘貼到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)))
寫好后，按回車鍵完成公式輸入。然后用鼠標下拉單元格右下角小黑定至G6即可。
函數解析（為了方便大家閱讀，公式中的圓圈序號即為上一步的公式）：
公式的主體部分就是【SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1))】，分成四步講解。
①MATCH(F2,A:A,0)部分是找到F列的銷售團隊在A列中第一次出現(xiàn)的位置對應的行數
②ADDRESS(①,3,,)返回的是$C$27，確定了團隊名稱第一次出現(xiàn)時對應的銷售金額的位置，這個位置就是接下來要求和的區(qū)域起點。
③OFFSET(INDIRECT(②),0,0,3,1)返回的是{85;63;53}，對應的就是野狼團隊前三名的銷售金額。注意，如果不加indirect函數會返回錯誤，在公式中如果要引用位置，一般都要加indirect函數作為橋梁。
④SUM(③)這一步即對OFFSET返回的3個值{85;63;53}進行求和，返回的便是201。
以上四步就是公式主體部分的解釋。
外層嵌套的if判斷，作用是當團隊銷售明細不足3個時，讓公式計算它本身所有的銷售業(yè)績之和。如果沒有這個判斷，當銷售明細不足3個時，會將別的團隊的業(yè)績算在它身上，造成不公平。
以上是公式法的思路邏輯解釋。
2
power pivot 法
很多小伙伴對power pivot比較陌生，大家可以簡單認為是Excel數據透視表（Pivot Table）的升級。其功能比數據透視表強大很多，所以也被人們稱作數據建模，名字很高大上有木有？更高大上的是它被稱作是微軟20年來最偉大的發(fā)明。而她使用的語言就是DAX 。
下面，就以今天的案例開啟“從工作表函數到DAX函數之旅”吧。文中把power pivot簡稱PP。
用PP解決這個問題不需要將數據源排序！
Step.01
將數據源添加到數據模型。
如果你找不到PP選項卡。就在開發(fā)工具下面的“COM加載項”中勾選“Microsoft Power Pivot for Excel ”。
Step.02
進入了PP的數據模型編輯器，首先將光標放在“銷售金額”下的空白處，點擊“主頁”下的“自動匯總”，就自動生產了DAX表達式。
銷售金額的總和:=SUM([銷售金額])
Step.03
然后在下面的空白處寫上下面這段DAX函數：
銷售量前3:=CALCULATE([銷售金額的總和],TOPN(3,'表1',[銷售金額的總和],0) )
函數解析：
①“銷售量前3:”這個前綴是自定義的，不同于excel中工作表的函數，DAX函數需要先自定義一個前綴。
②[銷售金額的總和]是引用上面的銷售金額求和表達式。
③TOPN(3,'表1',[銷售金額的總和],0)返回的是銷售金額最多的三個明細，它返回的內容實質是一長表。第一參數是限定返回的數量；第二參數是表的名稱，相當于工作表中的sheet名稱；第三參數0表示降序。
④CALCULATE([銷售金額的總和],③），代表用③返回的表來篩選[銷售金額的總和]，其返回的就是最大的三個銷售金額相加的值。
Step.04
DAX函數寫完之后，用鼠標點擊“主頁”，“數據透視表”，將內容加載到透視表中進行分析。
將“銷售團隊”拉到行，然后找到大家剛剛寫的DAX函數“銷售量前3”，把“銷售量前3”拉到“值”。（在PP中生成的透視表都可以在字段中找到大家寫過的表達式，其以大家設置過的前綴顯示）。
