excel表格_excel函数公式大全_execl从入门到精通
如何快速找到并提取各项目各阶段起始结束日期-使用Excel逻辑函数2024-01-31 22:02:29
本文将用到:
TEXT(数据,指定文本格式)IF(条件,满足条件结果,不满足条件结果)OR(条件1,条件2,条件N)结果为各条件的并集,满足括号任一条件即可AND(条件1,条件2,条件N)结果为各条件的交集,必需满足括号内全部条件自定义排序:不但可选择多条件排升序降序,而且可以按照指定顺序进行排序在做项目汇总时,通常进行实施日期的汇总,形成波次。比如:
工程项目子项目阶段执行天数汇总
广告投放各波次汇总
员工在每个项目投入的天数汇总
……
如何在每一天日期的明细执行表中,找到各项目各阶段的起始结束日期并汇总为时间段呢?
思路:将数据按照项目名及日期升序排序,每个项目第一个日期为开始日期,最后一个日期为结束日期;项目实施过程中分阶段的起终点判断根据是否与上一阶段或下一阶段终点相连接。
第一步,将数据按照项目名、日期前后进行排序
自定义排序选中数据区域,找到"开始"菜单栏"排序和筛选"->选择"自定义排序"
自定义排序在自定义排序对话框添加条件,根据红圈圈进行添加条件的设定
第二步,在D列判断是否为开始日期。
如果是则显示当前日期,若不是则显示最近的开始日期
判断是否为开始日期判断是否为开始日期的两种情况:
(1) 项目间:每个项目第一个日期(C列当前行与上一行的两个单元格项目名不一致)
(2) 项目内:当同一个项目中,相邻日期间隔超过1天(C列上下单元格项目名一致,B列当前行与上一行的单元格日期差异大于1)
将以上两种情况以公式表达
以单元格D3为例,公式为:
IF(OR(C3<>C2,AND(C3=C2,B3>SUM(B2)+1)),B3,D2)
第三步,在E列判断每个项目的结束日期。
如果是结束日期,则显示当前日期,若不是则为空
判断是否为结束日期判断是否为结束日期的三种情况:
(1) 项目间:每个项目最后一个日期(C列当前行与下一行单元格项目名不一致)
(2) 项目内:当同一个项目中,相邻日期间隔超过1天(C列上下单元格项目名一致,B列当前行与下一行的单元格日期差异大于1)
(3)是否当前行为最后一行(当前行的下一行是否为空"")
以E3单元格为例,公式为:
IF(OR(C4<>C3,B4="",AND(C3=C4,B3+1<>B4)),B3,"")
第四步,连接并筛选起始日期阶段
连接开始日期与结束日期如果结束日期不为空,则连接当前行D列与E列,如果为空,则当前单元格为空。
以F3为例,公式为:
IF(E3="","",TEXT(D3,"yyyy/m/d")&"-"&TEXT(E3,"yyyy/m/d"))
表哥TIPS:TEXT(D3,"yyyy/m/d")=2019/1/7,若将逗号后边的格式调整为TEXT(D3,"yyyy/mm/dd"),则结果为2019/01/07。可根据自己的日期表达习惯进行调整。
希望表哥的思路能够对你起到抛砖引玉的作用;
以上案例解决思路一定不止文章中所讲,
如果你发现了新思路,
欢迎与表哥分享你大法
↖(^ω^)↗撒花
标签: excel提取时间段公式