如果你經常使用Excel中
分類彙總
,那麼你一定要學習一下PQ中
分組依據
(Table。Group),他可以說是對分類彙總進行了系統全面的增強和升級
直接上來就講PQ中的
Table.Group
估計很多人都要懵,所以我們先來透過大家熟悉的功能引入!
引子 | Excel基礎功能-分類彙總
這樣的資料,我們先按照銷售員做一個彙總,並且可以看到明細,這個時候,我們就可以使用
分類彙總
動畫操作演示
:
1、對分組的銷售員排序
2、點選【資料】-【分類彙總】
本質是
分組+聚合
!但是PQ中的分組功能,做了增強了,是那種真正的增強了很多!
類似的功能,我們看看PQ如何處理-
分組依據
(Table。Group)
關於如何把資料加入到PQ編輯器等基礎問題,這裡就不再囉嗦,因為前面已經寫過基礎專題,推薦0基礎閱讀一下,構建基本的知識框架
▍PQ 第一期 | Power Query是什麼?怎麼學?
▍PQ 第二期 | 懂Excel為什麼還是學不好PQ?
▍PQ 第三期 | M函式基礎及上下文詳解
下面,我們直接來看功能演示及講解!
PQ中的分組依據功能,使用介面操作,也是分兩步
①:
分組
- 根據那(幾)個列把內容分成幾組
②:
聚合
- 對每一組中指定的列進行聚合操作(如求和、平均等)
PQ中不僅僅可以進行類似分類彙總的常規陣列聚合操作,可以還是可以對一些
文字進行聚合處理
,比如文字使用指定分隔符連線到一起等!
下面是我們透過介面操作,自動生成的M函式公式,有點像錄製宏!
下面我們會對Table。Group函式做一些基礎講解!
入門 | Table。Group函式基礎語法
透過上面的引子,我們大概瞭解了Table。Group,下圖就是Table。Group函式的
基礎語法
拆解,說明都透過註釋的方式在圖中說明,先有一個大概的瞭解,我們再看兩個案例鞏固
第一引數比較好理解,就是一個載入到PQ中的表,部分同學不太理解第二、三引數
下面透過一個案例說明一下,第二、三引數
▎
案例:理解第二、三引數
▼ 格式化一下程式碼,加上註釋
對照著我們的手工操作,大家對Table。Group前三個引數應該有了進一步的認識、一般我們熟悉後,可以自己來書寫,每個分組的第三引數是指定型別,也可以不寫。
基礎 | 文字聚合問題
上面我們說到PQ中分組可以對文字進行聚合,大大增強了分組彙總功能,不過我們看到預設的操作介面是沒有關於文字的處理的,需要我們自己來寫!
▼ 文字聚合
新手階段,我們可以使用分組依據功能+修改來處理,等大家熟悉了之後就可以直接手工來寫M函式,跟VBA學習中的從錄製宏,修改宏,到手寫VBA差不多!
我們把之前介面操作生成的一些List。Sum求和等按照自己的需求修改為文字拼接函式Text。Combine即可輕鬆按地區把姓名按照指定分隔符聚合到一列!當然我們單獨地演示聚合
= Table。Group(源, {“地區”}, {{“銷售員”, each Text。Combine([銷售員],“/”)}})
我們對姓名去重-List。Distinct,實際可以去重合並
▼ 去重+合併
以上幾個案例,想必大家對Table。Group已經有了一些基礎的認知,完成一些常規的分組聚合應該完全OK了。鋪墊了那麼多,下面才是我們今天的硬核知識 -
區域性分組
問題!
我們都是對指定列的內容
全部相同
的分到一組,這其中知識把分類彙總的過程在內部完成了,但是有的需求,不做排序,我們只需要判斷連續的內容。
這裡就要說到Table。Group的第四和第五引數!
進階 | Table。Group 區域性分組-第三引數
為了大家更好地理解區域性分組,我們先來看一個簡單的案例
第
三
引數,決定了,我們是全域性還是區域性分組
第三引數:
1或者不寫
,預設
全域性分組,
所謂全域性,就是掃描這個欄位,把相同的全部分到一組,這個你直接案例分類彙總理解即可
第三引數:
0
,
區域性分組,
區域性相對全域性,從上往下相同的分到一組,遇到不同的就停止,分為下一組
比如上面,我們看到李四有兩組 811。4正好是最後三個數的合計
關於區域性分組的規則,其實我們還可以自己來定義,這也是第五引數的作用,自由度越大,相對也就越難,這個道理大家應該能明白!
高階 | Table。Group 區域性分組-自定義規則
預設情況,區域性分組就是從上往下,遇到不同,上面的內容就分為一組,這裡的判斷邏輯有兩個,一個是基準點(x),一個是下一個不同(y)
具體執行規則,我們還是來看案例
案例:整理小組成員
這裡我們的分組依據就是
是否內容包含組
,如果是我們就分成一組
= Table。Group( 源,“內容”,{“組員”,each _}, 0, (x,y)=>Byte。From(Text。EndsWith(y,“組”)))
核心內容就是
(x,y)=>Byte.From(Text.EndsWith(y,"組")),
下面我們來拆解一下執行邏輯
初始x就是首個內容,下面的每一個都是y,然後我們直接自己寫的邏輯判斷,是否已組結尾
判斷的結果無法是True或者False,
當是True的時候停止,True上面的內容分組一組,True的位置變成新的x,x下面的內容是新一次的y
更多實戰案例推薦
往期推薦
PQ實戰 | 客戶下單資料整理!
PQ實戰 | 同類項的合併與拆分
PQ實戰 | 文字中提取數值並求和
PQ實戰 | 這種類似VLOOKUP的查詢為什麼不對?
PQ實戰 | 快速找出差異資料,資料變動及時更新
PQ實戰 | 每天出差到底有幾人?
PQ實戰 | 使用Excel收集全國天氣歷史資料
PQ實戰 | 資料分組壓縮思路分享