根据人员的休假日期，能够自动统计出本周和下周的人员

wayne981

因工作需要，想设计1个表格，能够根据输入休假的起始日期，每一天都能自动统计出本周和下周的休假人员。具体见附件。非常感谢~！

changhong8

F3=IFERROR(INDEX($C$3:$C$23,SMALL(IF((($A$3:$A$23)>(TODAY()-WEEKDAY(TODAY(),2)))*(($A$3:$A$23)<(TODAY()-WEEKDAY(TODAY(),2)+8))+(($B$3:$B$23)>(TODAY()-WEEKDAY(TODAY(),2)))*(($B$3:$B$23)<(TODAY()-WEEKDAY(TODAY(),2)+8))+(($A$3:$A$23)<=(TODAY()-WEEKDAY(TODAY(),2)))*(($B$3:$B$23)>=(TODAY()-WEEKDAY(TODAY(),2)+8)),ROW($A$1:$A$21),FALSE),ROW(A1))),"")
G3=IFERROR(INDEX($C$3:$C$23,SMALL(IF((($A$3:$A$23)>(TODAY()+7-WEEKDAY(TODAY(),2)))*(($A$3:$A$23)<(TODAY()-WEEKDAY(TODAY(),2)+15))+(($B$3:$B$23)>(TODAY()+7-WEEKDAY(TODAY(),2)))*(($B$3:$B$23)<(TODAY()-WEEKDAY(TODAY(),2)+15))+(($A$3:$A$23)<=(TODAY()+7-WEEKDAY(TODAY(),2)))*(($B$3:$B$23)>=(TODAY()-WEEKDAY(TODAY(),2)+15)),ROW($A$1:$A$21),FALSE),ROW(A1))),"")
数组公式，三键下拉

wayne981

changhong8 发表于 2017-7-24 23:42
F3=IFERROR(INDEX($C$3:$C$23,SMALL(IF((($A$3:$A$23)>(TODAY()-WEEKDAY(TODAY(),2)))*(($A$3:$A$23)(TODAY ...

非常感谢，专业成就价值！

wayne981

changhong8 发表于 2017-7-24 23:42
F3=IFERROR(INDEX($C$3:$C$23,SMALL(IF((($A$3:$A$23)>(TODAY()-WEEKDAY(TODAY(),2)))*(($A$3:$A$23)(TODAY ...

你好，我确实不够专业，能否再咨询一下：附件里是20行数据，如果有200行数据呢。
看了好久，还是没有看懂和学会。

changhong8

200行就把23、21类的尾数+对应的数值就行了，建议用替换的功能批量替换，查找范围为公式

wayne981

changhong8 发表于 2017-7-31 22:34
200行就把23、21类的尾数+对应的数值就行了，建议用替换的功能批量替换，查找范围为公式

谢谢你，非常专业！非常耐心！

wayne981

changhong8 发表于 2017-7-24 23:42
F3=IFERROR(INDEX($C$3:$C$23,SMALL(IF((($A$3:$A$23)>(TODAY()-WEEKDAY(TODAY(),2)))*(($A$3:$A$23)(TODAY ...

谢谢你，再打扰您一下，出于实际应用的考虑，可能需要删除旧数据，这样就可能需要改变表格形式，想在表格上面增加几行，本周和下周外出人员由原来的 列统计，变为 行统计，如附件所示。
最近加班多，还没有去理解和学会你的公式。