煤化工網

                                                          Power Query 閃開,最牛的Excel合并公式來了

                                                          網友投稿
                                                          后臺-系統設置-擴展變量-手機廣告位-內容正文頂部

                                                          Power Query 閃開,最牛的Excel合并公式來了

                                                          原創 蘭色幻想-趙志東 Excel精英培訓
                                                          Excel精英培訓

                                                          excelpx-tete

                                                          每天一篇excel原創教程,由淺入深,全面學習excel技巧、函數、圖表和VBA編程。有excel問題也可以提問哦!

                                                          收錄于話題

                                                          平臺一粉絲在留言中提了一個關于員工生日的難題:

                                                          其實,如果不要求姓名合并,用數據透視表挺容易實現的:

                                                          插入數據透視表 - 對日期進行月、日分組  - 把月拖到篩選標簽中 -  添加切片器:月

                                                          但要把同一天過日的員工姓名合并起來,就沒那么容易了。蘭色分享兩種高能方法,一種是power Query轉換,第二種是用函數公式。

                                                          一、使用Power Query 

                                                          1、效果演示

                                                          2、制作步驟

                                                          選取表格 - 數據 - 自表格/區域 ,啟動power Query

                                                          在power Query編輯器中,復制日期列,并分別轉換成月和天

                                                          再重命名標題為“月份”和“日期”

                                                          分組依據 - 選取高級 - 添加和設置 月份日期  為分組項,然后對 員工 進行求和。

                                                          點擊確定后,新生成的列會顯示錯誤結果

                                                          修改編輯欄中的公式

                                                          原公式:

                                                          = Table.Group(重命名的列, {"月份", "日期"},  {{"過生日的員工", each List.Sum([員工]), type text}})

                                                          修改為:

                                                          = Table.Group(重命名的列, {"月份", "日期"},  {"過生日的員工", each Text.Combine([員工],",")})

                                                          把powey中的結果導入到表格中,并用數據透視表進行透視:關閉并上傳至  -選數據透視表,再添加切片器即可( 具體步驟不再詳述

                                                          是不是感覺power query步驟很復雜,其實用一個Excel公式即可搞定。

                                                          二、使用函數公式

                                                          1、效果

                                                          可以選取不同的月份,動態生成該月份每一天過生日的名單。

                                                          2、公式

                                                          K3公式:

                                                          =IFERROR( TEXTJOIN(",",, FILTER (A$2:A326, TEXT(B$2:B326,"m-d") =K$1&"-"&J3)),"")

                                                          估計很多新手看不懂公式,蘭色就簡單介紹一下:

                                                          • TEXT(B$2:B326,"m-d")  :把B列的日期轉成換“月-日”格式和給定的 K$1&"-"&J3 進行對比,作為filter函數的篩選條件。

                                                          • FILTER (篩選返回區域, 條件 ): office365新增函數,根據條件返回篩選結果

                                                          • TEXTJOIN(連接符,,連接的多個值) office365新增函數,可以用指定的連接符號,把多個值連接成一個。

                                                          • IFERROR(表達式,"") :把返回的錯誤值轉換成空

                                                          蘭色說 :office365新增的幾個函數功能真的超級強大,原來需要一公里長才能完成的字符處理,用它們輕松就搞定。只是公式再牛....大部分用戶還沒升級,還是用前兩種方法吧。

                                                          長按 下面二維碼圖片,點上面 識別圖中二維碼 然后再點關注,每天可以收到一篇蘭色最新寫的excel教程。

                                                          標簽:

                                                          后臺-系統設置-擴展變量-手機廣告位-內容正文底部
                                                          留言與評論(共有 0 條評論)
                                                             
                                                          驗證碼:
                                                          天天操天天干