某計劃營運在分析産品銷量的問題:從ERP系統中導出今年全部産品對應不同貨号的商品銷售數量彙總。産品大類有幾十種,如夾克、風衣、衛衣、羽絨服、皮衣等,每個類别下面有不同的商品,如夾克對應的商品命名有:“3-1C18807、3-1C88260、3-1C88616、夾克1J3302、夾克3E515-2”,每個銷量對應的不同,現在領要求他快速的找出每個類别的“爆款”,也就是銷量最大的前5名産品,分别銷量是多少?
下圖是手工一個一個填寫上去的效果,因為原始資料庫實在太多了,有幾萬條件記錄,問古哥有沒有好的辦法,通過公式一次實作?
古老師仔細得看了一下手工統計的結果,有幾個難點要解決:
第一:商品名和銷量是在一個單元格,而這兩個類别在源資料是分别是兩列;
第二:銷量和産品大類在源資料中沒有規律,銷量也不是按類别,按降序排列的;
第三:一個産品對應多個商品;
第四:源資料是一維資料,上司要求的結果是二維資料;
知道問題難點就友善解決多了,這類問題有一個特點,特定的問題特定函數:第一個問題屬于合并單元格問題,用到函數TEXTJOIN; 第二個問題是資料沒有規律,就用到排序函數SORT讓其有規律,指定降序;第三個問題是一對多問題,用到TRANSPOSE+ FITER函數就可以了;最後一個問題,一維轉二維,就用UNIQUE+TRANSPOSE+ FITER 以及一些特定的查找引用函數即可;
解決步驟1:确定産品的唯一大類值,錄入函數:=UNIQUE(A:A)
解決步驟2:确定銷量前5名的名稱,錄入函數:=SEQUENCE(,5)
到這裡為了友善大家了解,分開寫函數步驟示範
解決步驟3:篩選出夾克對應的商品命名和銷量,錄入函數:
=FILTER($C:$D,$A:$A=$F2)
解決步驟4:對篩選出夾克對應的商品命名和銷量進行排序,錄入函數:
=SORT(FILTER($C:$D,$A:$A=$F2),2,-1),參數-1代表降序,也就是對應銷量的從大到小排序;
解決步驟5:對篩選出夾克對應的商品命名和銷量進行排序後的數量進行取前5名,因為已經排序過了,是以用INDEX函數取第1列和第2列的,前5行就可以了,再用&符号連接配接起來就成為一個單元格了;
=INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&","&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2)
解決步驟6:到步驟5基本上就解決了這個問題了,隻需要加上轉置函數就可以了,錄入函數:
=TRANSPOSE(INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&","&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2))
解決步驟7:有些産品的下面的商品沒有前5項,隻有1項或者2項的話,就會傳回錯誤,此時加上屏蔽錯誤函數後,此問題得到解決:
=IFERROR(TRANSPOSE(INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&","&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2)),"")
總結:在更新OFFICE365版本後,配合去重函數UNIQUE、篩選函數FILTER、排序函數SORT、轉置函數TRANSPOSE以及動态數組的運用,可以非常友善去做一些資料分析的工作,而且非常高效、快速。
我是古哥:
從事制造行業18年,在企業營運、供應鍊管理、智能制造系統等方面具有豐富的實戰經驗。企業智能化,柔性化計劃營運管理專家,擅長通過企業流程優化規範,企業管理、導入計劃營運提升企業效率;對提高企業準時交貨率,降低企業庫存,輸出智能制造人才有豐富的經驗。學習PMC生産計劃,關注古哥計劃!