煤化工网

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 条评论)
   
验证码: