EXCEL教學 | 使用365新函數GROUPBY 將資料快速建立摘要 | 粉絲解題系列

閱讀時間約 7 分鐘
raw-image


Dcard網友提出了一個求救,想要把原始資料根據日期跟代號分組後再進行加總,這個需求使用樞紐就能快速解決了,如果EXCEL是365的話,其實有一個新函數GROUPBY也是超級方便的🤩

raw-image




📂練習檔下載

看教學之前可以先下載練習檔,學中做、做中學效果更好哦。

檔案下載


❗版本與使用限制

不過目前微軟只開放給365 Beta通道人員測試使用,如果你是365的使用者但沒有看到GROUPBY這個函數,那麼可以按照下面步驟申請看看。

  1. EXCEL左上角點擊「檔案」
  2. 在左下角點擊「帳戶」
  3. 點擊 Microsoft 365 測試人員
  4. 選擇變更通道
  5. 將測試人員的頻道改設為 Beta 版通道
  6. 勾選同意條款和條件 > 確定
  7. 最後點擊「更新選項 > 立即更新」
  8. 有可能不會馬上有,過個幾天就會出現了


raw-image




365函數GROUPBY介紹

✍🏾GROUPBY將資料依據指定運算進行摘要
函數說明=GROUPBY(摘要依據,摘要數值,運算方式,標題選項,合計選項,排序選項,篩選選項)
  • 摘要依據:包含用於分組列及產生列標題的值
  • 摘要數值:以欄為導向的陣列或要匯總的資料範圍
  • 運算方式:用於匯總值的 SUM、PERCENTOF、AVERAGE、COUNT 等
  • 標題選項:
    0:無標題
    1:有標題,不顯示
    2:無標題,但產生
    3:有標題和顯示
  • 合計選項:
    0:無總計
    1:總計
    2:總計和小計
    -1:頂端的總計
    -2:頂端的總計和小計
  • 排序選項:輸入要排序的欄數
    正數:遞增
    負數:遞減
  • 篩選選項:欄導向的布林值 1D 陣列,指出是否應該考慮對應的資料列


GROUPBY這個函數包含了7個引數😱,但其實只有前三項是必要的,其他就是產生數據後的微調。






GROUPBY提問解答

要將左邊的表格中的品項A跟B為依據進行摘要,摘要方式以加總進行運算,變成右圖的預期結果。

G3=GROUPBY(B2:B8,C2:E8,SUM)

  • 第一個引數|摘要依據:B2:B8,也就是品項要進行摘依據的範圍
  • 第二個引數|摘要數值:C2:E8,所有要進行加總的數據範圍
  • 第三個引數|運算方式:SUM,代表加總

只要輸入這段函數之後,就會直接將A跟B加總後的數值列出來,但是目前會發現,這樣的結果有點美中不足,沒有標題不太方便閱讀,這點待會再來解決。

raw-image



如果要顯示標題,這時候就要使用第四個引數標題選項

G3=GROUPBY(B2:B8,C2:E8,SUM,3)

第四個引數輸入3,代表原始資料有標題,要把標題顯示出來,這樣資料閱讀就更加方便了。

raw-image



目前的摘要方式會包含總計,如果不想要顯示總計或者要變更總計的位置,就要請出第五個引數合計選項

G2=GROUPBY(B2:B8,C2:E8,SUM,3,0)

第五個引數輸入0,代表沒有總計,所以摘要的總計就會消失不見了,可以依據需求調整各種不同的總計與小計的效果。

raw-image



如果要針對每一個摘要結果進行排續,就會使用到第六個引數,排序選項

=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,代表依據摘要結果第三欄進行遞減排序

所以第六個引數的數字代表要排序的依據是摘要的第幾欄,而正負號是用來控制遞增或者遞減排序。

raw-image



如果在原始資料中,希望總金額大於500元進行摘要加總,這時候第7個引數篩選選項,就會是你的好麻吉。

G2=GROUPBY(B2:B8,C2:E8,SUM,3,0,-3,E2:E8>=500)

第七個引數直接套用布林邏輯,當條件成立(TRUE)的時候,才會將資料進行摘要,所以當條件設定為『E2:E8>=500』時,代表原始資料總金額要大於等於500的資料才會被進行摘要加總。

  • A總金額大於等於500 : 720+540=1260
  • B總金額大於等於500 : 750
raw-image





網友題目解題|365函數GROUPBY

了解GROUPBY原理之後,直接來進行解題。

H1=GROUPBY(B1:C10,D1:F10,SUM,3,0)
  • 第一個引數|摘要依據:B1:C1,摘要依據有兩欄,分別為B跟C,所以兩欄的範圍都要選取
  • 第二個引數|摘要數值:D1:F10,要進行摘要的數值範圍包含淨重、單價、總價,這三欄都要選取
  • 第三個引數|運算方式:SUM,代表加總
  • 第四個引數|標題選項:輸入3,代表原始資料有標題,並且要摘要上要顯示標題
  • 第五個引數|合計選項:輸入0,代表不顯示合計與總計

這樣一瞬間日期跟代號為依據的加總摘要就計算出來了。

raw-image



但這時候會有一個問題,就是當資料有新增的時候,摘要的結果不會自動更新,因為這個函數的範圍只有到第10列。

raw-image



如果新增資料後要能自動更新,函數就要稍微調整一下。

=GROUPBY(B:C,D:F,SUM,3,0)

摘要依據摘要數值都改為整列的範圍,這樣未來只要有新增資料就會自動更新了。

raw-image



但改成整列參照後,會發現摘要最後一列會出現0,這時只要請出第7個引數篩選選項就可以了。

H1=GROUPBY(B:C,D:F,SUM,3,0,,B:B<>"")

第7個引數輸入『B:B<>""』,這句話的翻譯就是,B欄不等於空格的時候才會將資料進行摘要加總,這樣就能將0的那一列刪除了。

raw-image




如果最後要在最底部加上總計的話,也只要稍微修改一下第五個引數合計選項就可以了。

H1=GROUPBY(B:C,D:F,SUM,3,1,,B:B<>"")
raw-image






網友題目解題|樞紐分析表

接下來要介紹樞紐分析表的方法,這個方法所有版本都適用,而且也是簡單到爆炸。

在進行樞紐分析之前,建議先將資料轉換成動態表格,這樣未來資料如果有新增就可以不用額外再去調整範圍。

  1. 點選資料任一儲存格
  2. 插入
  3. 表格
  4. 我的表格有標題打勾
  5. 確定
raw-image



變成表格後就能插入樞紐分析表進行分析

  1. 點選表格任何一個儲存格
  2. 插入
  3. 樞紐分析表
  4. 現有工作表(新增工作表也可以,樞紐會額外創建一個分頁)
  5. 點選樞紐分析表要放置的位置
raw-image



插入樞紐分析表後,將右邊樞紐分析表欄位的項目拖拉到相對應的位置。

  1. 日期、代號:用滑鼠拖拉到的窗格
  2. 淨重、單價、總價:用滑鼠拖拉到的窗格
raw-image




但是會發現跟預期結果好像有一點點不太一樣,這時候就要調整樞紐分析表的版面配置。

  1. 報表版面配置:以列表方式顯示、重複所有項目標籤
  2. 小計:不要顯示小計

這樣就順利依據日期與代號進行摘要了,是不是覺得樞紐好神奇,完全不需要寫函數呢~

raw-image

本次的分享就到這邊,希望有幫助到你❤️





歡迎訂閱贊助效率職人沙龍解鎖更多優質文章

<訂閱沙龍BONUS>

  • 贊助訂閱: 99元/月 or 999/年
  • 限閱文章:4篇文章/月
  • 解鎖房間:職場設計新思維
  • 解鎖可閱讀內容:
1️⃣ EXCEL特殊圖表
2️⃣ POWER QUERY從0到1
3️⃣ 素材分享(ICON、簡報元素)
4️⃣ 全自動抽獎系統模
5️⃣ 直播分享錄影檔:❌不用函數的日期處理術





  • 👍喜歡的話可以幫忙案個讚、分享來幫助更多人或是右下珍藏起來哦
  • 💭留言回復「職場生存讚」讓我知道你把這個小技巧學起來了
  • ❤️追蹤我的方格子,學習更多職場小技巧
  • 請我喝杯咖啡,鼓勵我更有動力分享更多優質內容
  • 📈訂閱EXCEL設計新思維,學習更多更深更廣的職場技能

😎可以找到我的地方

  1. LINE社群
  2. IG
  3. FB粉絲團
  4. YOUTUBE
  5. TIKTOK
  6. DCARD
raw-image

raw-image


18.4K會員
243內容數
此專題旨在幫助職場人士提升工作效率、提升專注力並更有效地管理時間,以達到更高的生產力和工作成果。在這個快節奏且競爭激烈的職場環境中,掌握提升效率的技巧尤為重要,主要會著重於分享OFFICE上最常使用的軟體,EXCEL、PPT、WORD各種增加效率的小技巧。
留言0
查看全部
發表第一個留言支持創作者!
效率基地 的其他內容
這集要來分享如何用EXCEL把不重複的資料全部提取出來,主要會分為EXCEL內建功能的方法與函數法。 看教學影片之前可以先下載練習檔,學中做、做中學效果更好哦。 檔案下載 EXCEL內建功能 <▶️影片教學> <📝圖文教學> ✨1.移除重複 選取資料範圍
看文章教學之前,可以先下載檔案來試著自己做看看哦!!! 檔案下載 有網友提問,如何將所有的平日加班與假日加班時數合計到K欄,假日加班為了註明星期幾加班,前面分別會加上六、日當作前贅詞。 只不過是加總而已,讓我直接SUM看看好了!!! 答案好像怪怪的,怎麼只有平日的數據加總而已
日期與時間在職場上肯定是常常會遇到的一個課題,這集來分享EXCEL資料中,如果有日期與時間,那麼如何快速把他們提取出來 下圖為例,B欄的資料包含了日期與時間,想要將日期提取到C欄,時間提取到D欄,要怎麼做會比較快速呢? ▶️影片教學 看教學影片之前可以先下載練習檔,學中做、做中
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只
之前有分享過GOOGLE SHEET可以用函數快速取得股票價格,那EXCEL如果要取得股票價格有沒有辦法呢? EXCEL取得股票的方法有超多種,這集要介紹最簡單的方式,完全不用寫任何函數,只要有滑鼠就可以了!! ▶️短片介紹 看教學影片之前可以先下載練習檔,學中做、做中學效果更好哦。
上次有分享一個全自動的抽獎系統,的確超級方便,但是要做出全自動抽獎是有一定的難度,需要搭配不少函數與VBA的觀念。 EXCEL全自動抽獎系統-公司尾牙、各大活動必備 全自動的抽獎系統難度較高不知該從哪裡下手,那今天就來分享一個超級簡易版的抽籤系統,看完影片或文章不用1分鐘就能自己做出來了
這集要來分享如何用EXCEL把不重複的資料全部提取出來,主要會分為EXCEL內建功能的方法與函數法。 看教學影片之前可以先下載練習檔,學中做、做中學效果更好哦。 檔案下載 EXCEL內建功能 <▶️影片教學> <📝圖文教學> ✨1.移除重複 選取資料範圍
看文章教學之前,可以先下載檔案來試著自己做看看哦!!! 檔案下載 有網友提問,如何將所有的平日加班與假日加班時數合計到K欄,假日加班為了註明星期幾加班,前面分別會加上六、日當作前贅詞。 只不過是加總而已,讓我直接SUM看看好了!!! 答案好像怪怪的,怎麼只有平日的數據加總而已
日期與時間在職場上肯定是常常會遇到的一個課題,這集來分享EXCEL資料中,如果有日期與時間,那麼如何快速把他們提取出來 下圖為例,B欄的資料包含了日期與時間,想要將日期提取到C欄,時間提取到D欄,要怎麼做會比較快速呢? ▶️影片教學 看教學影片之前可以先下載練習檔,學中做、做中
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只
之前有分享過GOOGLE SHEET可以用函數快速取得股票價格,那EXCEL如果要取得股票價格有沒有辦法呢? EXCEL取得股票的方法有超多種,這集要介紹最簡單的方式,完全不用寫任何函數,只要有滑鼠就可以了!! ▶️短片介紹 看教學影片之前可以先下載練習檔,學中做、做中學效果更好哦。
上次有分享一個全自動的抽獎系統,的確超級方便,但是要做出全自動抽獎是有一定的難度,需要搭配不少函數與VBA的觀念。 EXCEL全自動抽獎系統-公司尾牙、各大活動必備 全自動的抽獎系統難度較高不知該從哪裡下手,那今天就來分享一個超級簡易版的抽籤系統,看完影片或文章不用1分鐘就能自己做出來了
你可能也想看
Google News 追蹤
Thumbnail
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議 分析兩位候選人政策利多/ 利空的板塊和股票
Thumbnail
🤔為什麼團長的能力是死亡筆記本? 🤔為什麼像是死亡筆記本呢? 🤨作者巧思-讓妮翁死亡合理的幾個伏筆
Thumbnail
在這篇教學中,我們將使用 C# 和 Microsoft.Office.Interop.Excel 庫將圖片插入到 Excel 試算表中。
Thumbnail
在工作中,我們經常會遇到需要處理會議、講座、活動等報銷的情況。對於報銷人員來說,快速查找報銷金額費用是一件非常重要的工作。今天要教大家一個Excel技巧,可以來快速查找會議、講座、活動報銷金額費用。這個技巧利用的是VLOOKUP函數。
Thumbnail
ChatGPT可助你提升Excel操作。無論是基本如求和、還是複雜如統計數據種類,ChatGPT都能提供適切的Excel函數代碼。例如,查找特定產品價格可用vlookup;提取郵件名稱,除快速填充外,還有對應函數。向ChatGPT詢問,它將提供和解釋函數,讓你高效學習和完成任務。
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
Excel有個極其實用的工具VBA,讓我們透過語法撰寫對Excel下指令,雖然UiPath也有內建Excel相關功能,不過相較之下VBA可處理更多精細且完整的活動,因此我們在用UiPath開發流程時,可使用Invoke VBA功能呼叫Excel檔案中已撰寫好的VBA語法!
Thumbnail
https://www.youtube.com/watch?v=AUq-BqPG500 程式執行只有配樂展示,無講話 前言: 這次跟大家分享一下怎麼用EXCEL VBA抓取Goodinfo中,經營績效理不同分頁的資料,如上圖。 文後會附上程式範例給大家做參考。 4.資料整理,欄位定位。
Thumbnail
這篇是記錄一下透過VBA抓取goofinfo股息跟報價的方式。適合對VBA做投資工具設計有興趣的朋友閱讀,因為這個比較進階,有興趣再研究。
熟悉Excel試算表基礎操作,可以有效幫助使用者更快速的處理資訊、數據整理,作者今天統整了57種Excel小技巧,可以加快在使用Excel時的速度,並且附上EXCEL試算表技巧教學目錄大全,一次讓你成為EXCEL高手。 EXCEL全系列目錄 【EXCEL模板、工具系列】 【Excel模板】財務
Thumbnail
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議 分析兩位候選人政策利多/ 利空的板塊和股票
Thumbnail
🤔為什麼團長的能力是死亡筆記本? 🤔為什麼像是死亡筆記本呢? 🤨作者巧思-讓妮翁死亡合理的幾個伏筆
Thumbnail
在這篇教學中,我們將使用 C# 和 Microsoft.Office.Interop.Excel 庫將圖片插入到 Excel 試算表中。
Thumbnail
在工作中,我們經常會遇到需要處理會議、講座、活動等報銷的情況。對於報銷人員來說,快速查找報銷金額費用是一件非常重要的工作。今天要教大家一個Excel技巧,可以來快速查找會議、講座、活動報銷金額費用。這個技巧利用的是VLOOKUP函數。
Thumbnail
ChatGPT可助你提升Excel操作。無論是基本如求和、還是複雜如統計數據種類,ChatGPT都能提供適切的Excel函數代碼。例如,查找特定產品價格可用vlookup;提取郵件名稱,除快速填充外,還有對應函數。向ChatGPT詢問,它將提供和解釋函數,讓你高效學習和完成任務。
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
Excel有個極其實用的工具VBA,讓我們透過語法撰寫對Excel下指令,雖然UiPath也有內建Excel相關功能,不過相較之下VBA可處理更多精細且完整的活動,因此我們在用UiPath開發流程時,可使用Invoke VBA功能呼叫Excel檔案中已撰寫好的VBA語法!
Thumbnail
https://www.youtube.com/watch?v=AUq-BqPG500 程式執行只有配樂展示,無講話 前言: 這次跟大家分享一下怎麼用EXCEL VBA抓取Goodinfo中,經營績效理不同分頁的資料,如上圖。 文後會附上程式範例給大家做參考。 4.資料整理,欄位定位。
Thumbnail
這篇是記錄一下透過VBA抓取goofinfo股息跟報價的方式。適合對VBA做投資工具設計有興趣的朋友閱讀,因為這個比較進階,有興趣再研究。
熟悉Excel試算表基礎操作,可以有效幫助使用者更快速的處理資訊、數據整理,作者今天統整了57種Excel小技巧,可以加快在使用Excel時的速度,並且附上EXCEL試算表技巧教學目錄大全,一次讓你成為EXCEL高手。 EXCEL全系列目錄 【EXCEL模板、工具系列】 【Excel模板】財務