昨天,有人提了一个关于员工生日的难题:
其实,如果不要求姓名合并,用数据透视表挺容易实现的:
插入数据透视表 - 对日期进行月、日分组 - 把月拖到筛选标签中 - 添加切片器:月
但要把同一天过日的员工姓名合并起来,就没那么容易了。小编分享两种高能方法,一种是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(表达式,""):把返回的错误值转换成空