點(diǎn)擊藍(lán)字關(guān)注【秋葉 Excel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：竺蘭

大家好，這里是秋葉 Excel~

有個(gè)做采購(gòu)的讀者向我們咨詢：如何將下面的左表處理成右表的形式？

▲ 文末領(lǐng)取練習(xí)文件

分析圖片，我們可以知道，案例中的要求是將左圖中的月份，按照不同品類的水果列進(jìn)行匯總合并。

說(shuō)到分類匯總，大家除了想到數(shù)據(jù)透視表之外，可能也會(huì)想到Groupby 函數(shù)。

分組函數(shù)

如下圖所示 :

=GROUPBY(MONTH(A2:A21)&"月",B2:D21,SUM,,0)

Groupby 函數(shù)的語(yǔ)法規(guī)則如下 :

=GROUPBY(行字段，值區(qū)域,匯總方式,【是否包含標(biāo)題】,【是否顯示總計(jì)】,【排序依據(jù)】,【篩選區(qū)域】,【相關(guān)方式】)

要匯總的字段是月份，由于月份列還是日期格式存在，所以我們先用 Month 函數(shù)將日期進(jìn)行格式化。

根據(jù)上面的講解，相信大家可以很快就能理解這個(gè)函數(shù)公式：

分組的列：處理好的月份

值區(qū)域：B2:D21

匯總方式：求和

是否包含標(biāo)題：不包含

是否顯示總計(jì)：不顯示

=GROUPBY(MONTH(A2:A21)&"月",B2:D21,SUM,,0)

函數(shù)做法很簡(jiǎn)單，但可惜的是，這位讀者朋友的要求是用 PQ 來(lái)做~

(PS：PowerQuery，簡(jiǎn)稱 PQ，是 Excel 自帶的數(shù)據(jù)處理的利器)

第一步，大家不難猜到，就是將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。

我們選擇【數(shù)據(jù)區(qū)域】，在【數(shù)據(jù)】選項(xiàng)卡，單擊【來(lái)自表格/區(qū)域】，單擊【確定】按鈕，即可進(jìn)入 PQ 編輯器。

接下來(lái)，小爽就來(lái)介紹兩種 PQ 做法。

透視方法（PQ）

由于數(shù)據(jù)區(qū)域是一個(gè)二維表，我們可以先將表格轉(zhuǎn)換為一維表。

單擊【香蕉列】，按住【Shift鍵】，再單擊【西瓜列】，鼠標(biāo)右鍵，單擊【逆透視列】。

（PS：逆透視列是指，選中的所有字段中，把列標(biāo)題全部收集起來(lái)放到一列里，再重新匯總值數(shù)據(jù)；逆透視列經(jīng)常被用在二維表轉(zhuǎn)一維表的處理上。）

轉(zhuǎn)換一維表之后，由于月份列是日期格式，所以我們需要將日期進(jìn)行轉(zhuǎn)換。

選中【月份】列，單擊【轉(zhuǎn)換】選項(xiàng)卡，單擊【日期】，單擊【月份】選項(xiàng)，單擊【月份名稱】。

轉(zhuǎn)換日期后的月份是大寫(xiě)的月份，要是想要阿拉伯?dāng)?shù)字的月份，我們可以在 M 函數(shù)公式中直接修改。

更改前

= Table.TransformColumns(逆透視的列,
      {{"月份",
      each Date.MonthName(_),
      type text}})

Date.MonthName 函數(shù)，不難理解，就是將日期轉(zhuǎn)換為月份名字，也就是一月二月......

更改后

= Table.TransformColumns(逆透視的列,
{{"月份", each
Text.From(Date.Month(_))&"月",
type text}})

M 函數(shù)公式也不難理解，Date.Month 是將日期轉(zhuǎn)換為月數(shù)字，由于 M 語(yǔ)言有嚴(yán)格的格式要求，所以需要用 Text.From 函數(shù)先轉(zhuǎn)為文本，再進(jìn)行"月"的拼接。

Text.From(Date.Month(_))&"月",

最后進(jìn)行透視列即可完成。

（透視列的含義是：選中的字段中，每一種類別都作為一個(gè)列標(biāo)題排開(kāi)，再重新匯總數(shù)據(jù)。）

選中需要透視到列標(biāo)題的列，也就是【屬性】列，單擊【轉(zhuǎn)換】選項(xiàng)卡，單擊【透視列】，值列選擇【值】，單擊【確定】按鈕。

到這里我們就匯總完成了。

前面是用透視的方法來(lái)做，接下來(lái)，小爽來(lái)介紹直接用Table.Group 函數(shù)來(lái)做~

分組匯總（PQ）

我們先將月份列的日期轉(zhuǎn)換為月份，方便后面進(jìn)行分組。

跟前面的步驟一樣。

選擇【月份列】，在【轉(zhuǎn)換】選項(xiàng)卡下，單擊【日期】，單擊【月份】，單擊【月份名稱】。

同理，將月份中文名稱改成阿拉伯?dāng)?shù)字月份。

更改前

= Table.TransformColumns(源,
{{"月份", each Date.MonthName(_), type text}})

更改后

= Table.TransformColumns(源,
{{"月份", each Text.From(Date.Month(_))&"月", type text}})

日期轉(zhuǎn)換完成后，接下來(lái)就該分組了。

▋手動(dòng)匯總

選中【月份列】，單擊【主頁(yè)】選項(xiàng)卡，單擊【分組依據(jù)】，選擇【高級(jí)】，依次錄入列名，操作，以及柱列。

此時(shí)分組匯總就完成啦。

不過(guò)，完成是完成了，但是大家有沒(méi)有發(fā)現(xiàn)，這樣做一點(diǎn)都不智能，總不能每次換不同列匯總都進(jìn)行手動(dòng)吧?

所以，我們下一步，需要做的就是將每列的匯總進(jìn)行動(dòng)態(tài)化。

▋動(dòng)態(tài)匯總

首先，我們來(lái)觀察一下，手動(dòng)匯總的 M 函數(shù)公式。

= Table.Group(
   提取的月份名稱,
    {"月份"},
    {
     {"香蕉", each List.Sum([香蕉]), type number},
     {"蘋(píng)果", each List.Sum([蘋(píng)果]), type number},
     {"西瓜", each List.Sum([西瓜]), type number}
   })

從公式中，我們不難發(fā)現(xiàn)，核心就是要構(gòu)建下面這種列表。

{
    {"香蕉", each List.Sum([香蕉]), type number},
    {"蘋(píng)果", each List.Sum([蘋(píng)果]), type number},
    {"西瓜", each List.Sum([西瓜]), type number}
   }

大家跟著我一起思考~

Q1：那列名從哪里來(lái)?

Table.ColumnNames函數(shù)就可以獲取列標(biāo)題。

Q2：如何跳過(guò)月份?

List.Skip函數(shù)就可以跳過(guò)月份。

Q3：如何構(gòu)造循環(huán)構(gòu)造?

List.Transform函數(shù)來(lái)構(gòu)造。

Q4：如何選取分組后的指定列的數(shù)據(jù)?

利用Table.Column函數(shù)來(lái)選擇表中的指定列數(shù)據(jù)。

Q5：如何對(duì)列進(jìn)行求和?

List.Sum函數(shù)就能對(duì)列表進(jìn)行求求和。

到這里，我們就將前面的手動(dòng)分組匯總的 M 函數(shù)公式的第三參數(shù)，進(jìn)行動(dòng)態(tài)化了。

= Table.Group(   提取的月份名稱,{"月份"},
   List.Transform(
       List.Skip(Table.ColumnNames(源)),
       (r)=>{r,each List.Sum(Table.Column(_,r))})
)

不過(guò)，假如你的數(shù)據(jù)源不太敏感，數(shù)據(jù)量也不多，那也可以試試讓 AI 來(lái)幫你干活。

WPS 靈犀

打開(kāi) WPS，側(cè)邊欄就有靈犀，點(diǎn)擊進(jìn)入。

上傳我們的數(shù)據(jù)源附件，添加提示詞。

主要框架：說(shuō)明數(shù)據(jù)源區(qū)域+表格處理要求+處理后示例+輸出格式

背景：上傳的 excel 工作簿中的 Sheet1 表中 A 列到 D 列是原始數(shù)據(jù)源。

要求：基于月份列中的日期列，按照月份進(jìn)行分組合并

參考示例：|月份|香蕉|蘋(píng)果|西瓜| |1月|...

輸出格式：返回處理好的 excel 工作簿。

選擇【深度思考】，單擊【發(fā)送】。

此時(shí)，AI 就在思考啦~耐心等待一下下。

思考完成之后，靈犀會(huì)返給我們處理后的文件，單擊【按月份分組合并結(jié)果.xlsx】，就可以下載了。

打開(kāi)處理后的表格，也是我們想要的結(jié)果：

似不似很簡(jiǎn)單 ! ! !

本文主要介紹，在數(shù)據(jù)處理中，我們針對(duì)月份進(jìn)行多列分組匯總的做法。

利用新函數(shù) Groupy，進(jìn)行匯總，超級(jí)方便。

針對(duì)二維表的處理，在 PQ 中，逆透視+透視是比較常見(jiàn)的思路；也給大家拓展了，PQ 中，多列分組，從手動(dòng)多列分組，改成動(dòng)態(tài)多列分組的思路。

最后還帶大家體驗(yàn)了一下 WPS 靈犀的做法，感受 AI 工具在數(shù)據(jù)處理中的強(qiáng)大之處。

從傳統(tǒng)函數(shù)，到 PQ 自動(dòng)化處理，再到 AI 工具，我們正邁向效率爆炸的辦公新時(shí)代。

不過(guò)，想要真正吃透 AI，只靠自己摸索還遠(yuǎn)遠(yuǎn)不夠。

數(shù)據(jù)分析可以交給用WPS 靈犀、DeepSeek，海報(bào)設(shè)計(jì)可以靠即夢(mèng)一鍵生成，PPT 制作也能用Kimi快速產(chǎn)出……

AI 工具確實(shí)很強(qiáng)大，但前提是——你要會(huì)用、用得好、用得對(duì)。

所以，如果你也想玩轉(zhuǎn) AI，不妨加入秋葉《AI 高效辦公 3 天實(shí)戰(zhàn)營(yíng)》！

課程中，有金牌講師 @越越 手把手教學(xué)AI 數(shù)據(jù)分析、AI 寫(xiě)文案、AI 設(shè)計(jì)、AI 做 PPT，助你從小白變高手，真正用 AI 提升工作效率 ↓

課程原價(jià) 99 元

還能免費(fèi)領(lǐng)取 AI 辦公資料包

名額有限，立即掃碼報(bào)名吧！

掃碼報(bào)名后自動(dòng)彈出班主任微信

一定要添加，才能上課+免費(fèi)領(lǐng)取資料

最后，大家有利用 AI 處理過(guò)哪些數(shù)據(jù)問(wèn)題呢?

歡迎留言討論~

發(fā)送【采購(gòu)】

免費(fèi)領(lǐng)本文配套練習(xí)文件！