Dcard網友提出了一個求救,想要把原始資料根據日期跟代號分組後再進行加總,這個需求使用樞紐就能快速解決了,如果EXCEL是365的話,其實有一個新函數GROUPBY也是超級方便的🤩
看教學之前可以先下載練習檔,學中做、做中學效果更好哦。
不過目前微軟只開放給365 Beta通道人員測試使用,如果你是365的使用者但沒有看到GROUPBY這個函數,那麼可以按照下面步驟申請看看。
✍🏾GROUPBY將資料依據指定運算進行摘要
函數說明=GROUPBY(摘要依據,摘要數值,運算方式,標題選項,合計選項,排序選項,篩選選項)
GROUPBY這個函數包含了7個引數😱,但其實只有前三項是必要的,其他就是產生數據後的微調。
要將左邊的表格中的品項A跟B為依據進行摘要,摘要方式以加總進行運算,變成右圖的預期結果。
G3=GROUPBY(B2:B8,C2:E8,SUM)
只要輸入這段函數之後,就會直接將A跟B加總後的數值列出來,但是目前會發現,這樣的結果有點美中不足,沒有標題不太方便閱讀,這點待會再來解決。
如果要顯示標題,這時候就要使用第四個引數標題選項。
G3=GROUPBY(B2:B8,C2:E8,SUM,3)
第四個引數輸入3,代表原始資料有標題,要把標題顯示出來,這樣資料閱讀就更加方便了。
目前的摘要方式會包含總計,如果不想要顯示總計或者要變更總計的位置,就要請出第五個引數合計選項。
G2=GROUPBY(B2:B8,C2:E8,SUM,3,0)
第五個引數輸入0,代表沒有總計,所以摘要的總計就會消失不見了,可以依據需求調整各種不同的總計與小計的效果。
如果要針對每一個摘要結果進行排續,就會使用到第六個引數,排序選項。
=GROUPBY(B2:B8,C2:E8,SUM,3,0,2)
第六個引數輸入2,代表依據摘要結果第二欄進行遞增排序
=GROUPBY(B2:B8,C2:E8,SUM,3,0,-2)
第六個引數輸入-2,代表依據摘要結果第二欄進行遞減排序
=GROUPBY(B2:B8,C2:E8,SUM,3,0,3)
第六個引數輸入2,代表依據摘要結果第三欄進行遞增排序
=GROUPBY(B2:B8,C2:E8,SUM,3,0,-3)
第六個引數輸入-2,代表依據摘要結果第三欄進行遞減排序
所以第六個引數的數字代表要排序的依據是摘要的第幾欄,而正負號是用來控制遞增或者遞減排序。
如果在原始資料中,希望總金額大於500元進行摘要加總,這時候第7個引數篩選選項,就會是你的好麻吉。
G2=GROUPBY(B2:B8,C2:E8,SUM,3,0,-3,E2:E8>=500)
第七個引數直接套用布林邏輯,當條件成立(TRUE)的時候,才會將資料進行摘要,所以當條件設定為『E2:E8>=500』時,代表原始資料總金額要大於等於500的資料才會被進行摘要加總。
了解GROUPBY原理之後,直接來進行解題。
H1=GROUPBY(B1:C10,D1:F10,SUM,3,0)
這樣一瞬間日期跟代號為依據的加總摘要就計算出來了。
但這時候會有一個問題,就是當資料有新增的時候,摘要的結果不會自動更新,因為這個函數的範圍只有到第10列。
如果新增資料後要能自動更新,函數就要稍微調整一下。
=GROUPBY(B:C,D:F,SUM,3,0)
將摘要依據、摘要數值都改為整列的範圍,這樣未來只要有新增資料就會自動更新了。
但改成整列參照後,會發現摘要最後一列會出現0,這時只要請出第7個引數篩選選項就可以了。
H1=GROUPBY(B:C,D:F,SUM,3,0,,B:B<>"")
第7個引數輸入『B:B<>""』,這句話的翻譯就是,B欄不等於空格的時候才會將資料進行摘要加總,這樣就能將0的那一列刪除了。
如果最後要在最底部加上總計的話,也只要稍微修改一下第五個引數合計選項就可以了。
H1=GROUPBY(B:C,D:F,SUM,3,1,,B:B<>"")
接下來要介紹樞紐分析表的方法,這個方法所有版本都適用,而且也是簡單到爆炸。
在進行樞紐分析之前,建議先將資料轉換成動態表格,這樣未來資料如果有新增就可以不用額外再去調整範圍。
變成表格後就能插入樞紐分析表進行分析
插入樞紐分析表後,將右邊樞紐分析表欄位的項目拖拉到相對應的位置。
但是會發現跟預期結果好像有一點點不太一樣,這時候就要調整樞紐分析表的版面配置。
這樣就順利依據日期與代號進行摘要了,是不是覺得樞紐好神奇,完全不需要寫函數呢~
本次的分享就到這邊,希望有幫助到你❤️
歡迎訂閱贊助效率職人沙龍解鎖更多優質文章
1️⃣ EXCEL特殊圖表
2️⃣ POWER QUERY從0到1
3️⃣ 素材分享(ICON、簡報元素)
4️⃣ 全自動抽獎系統模
5️⃣ 直播分享錄影檔:❌不用函數的日期處理術