某公司的统计员需要对全厂员工的生日明细进行一个台账跟进，需要把员工生日明细表按生产部和销售部门分开，并统计各月过生日的员工，如果有多名员工在同一个月过生日，需要合并在一个单元格。希望设计一个自动模板，用函数公式一键转换统计。手工模拟数据如下图1所示。

图1

需求分析

根据源表（员工生日明细表）转换成生日台账表，根据上图的结果看来，这是一个特殊的一维数据转换二维数据的需求。

需要把E列中城市部门中的部门单独转换成列（水平）方向，把D列中的出生月份转换成行（垂直）方向，并根据这两个条件筛选出对应过生日的人员。最后把这个筛选结果合并成一个单元格。

需求分析完后，就开始写逻辑了：先用SEQUENCE生成月份，然后用RIGHT提取部门并用UNIQUE删除重复项后通过TOROW函数转置，接着用筛选函数FILTER双条件筛选，得到筛选结果后用TEXTJION合并。逻辑树如下图2所示：

图2

垂直条件

需求中需要按月显示员工的生日，所以需要生成一列月份，因为月份是固定的，也就是数字1，2，3……到12，所以可以用SEQUENCE配合自定义格式来生成1月到12月。录入函数：

F3=SEQUENCE(12)，录入完成后按Ctrl+1，设置单元格格式为自定义格式，并录入“#"月"”，效果如下图3所示。列条件已经设置好了；

图3

水平条件

需求是按部门来显示，因为源数据中的格式为“城市+部门”，目测是有规律的，如无规律可以用FIND生产和FIND销售来判断，这里直接用文本函数RIGHT来批量提取。为了方便大家理解，分三次录入：

=RIGHT(D3:D14,3),提取部门后三个字符；

=UNIQUE(RIGHT(D3:D14,3))，删除这列的重复项；

=TOROW(UNIQUE(RIGHT(D3:D14,3)))，转置成水平方向；

这样就得到二维数据中的水平方向条件；

完后如下图4所示：

图4

多条件筛选

根据需求转换成筛选函数FILTER函数的两个条件，一个是生日等于月的，另一个是部门等于生产部门的。为了方便大家理解FILTER函数的多条件筛选，分别把条件1和条件2分开写。

条件1：=MONTH(C3:C14)=F3

函数释义：批量提取C列出生日期的生日，并等于F列条件中的“1月”满足条件的有“赵云、白起”，效果如下图5所示；

图5

条件2：=RIGHT(D3:D14,3)=G2

函数释义：提取D列城市部门的后三个字符并等于条件F3(生产部）的结果，TURE为满足的结果，效果如下图6

图6

有了这两个条件后，直接套用FILTER函数就可以得到结果了，录入函数：

=FILTER($B$3:$B$14,I3#*J3#,"")，注意如果筛选没有的话显示为空。

这个是有辅助列的，合并后为：

=FILTER($B$3:$B$14,(RIGHT($D$3:$D$14,3)=G$2)*(MONTH($C$3:$C$14)=$F3),"")

需要注意的是，绝对锁定B列，C列，D列，垂直方向锁定列，水平方向锁定行。效果如下图7

图7

合并筛选结果

最后一部就是合并筛选后的结果，用TEXTJION就可以了，录入函数：

=TEXTJOIN("、",,FILTER($B$3:$B$14,(RIGHT($D$3:$D$14,3)=G$2)*(MONTH($C$3:$C$14)=$F3),""))，向右向下填充公式就完成了此次员工生日统计建模。

效果如下图8

图8

我是古哥：

从事制造行本业20年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！