☰

查找属于集合的最后一个向量元素的有效方法

tag-icon • tag-icon microsoft-excel
查找属于集合的最后一个向量元素的有效方法

我有一张电子表格，其中 A 列列出了一个月的日期（1 月 1 日、1 月 2 日等），B 列列出了相应的星期几（周五、周六、周日、周一等），C 列包含各种注释，可以是“节假日”或“休假”。我想在一个单元格中计算该月的最后一个“工作日”（即，该月中不是节假日或休假日的最后一个星期几）（索引）。

如果我只是想找到该月的最后一个星期五，我可以这样做：

LOOKUP(2,1/ ($B$2:$B$32<>"Fr")),ROW($B$2:$B$32))-1))

为了获得一周中的多天，我可以使用加法将多个向量标量比较“或”在一起：

LOOKUP(2,1/ (($B$2:$B$32="Mo")+...+($B$2:$B$32="Fr")),ROW($B$2:$B$32))-1))

为了增加它们不能是假日或休假日的限制，我可以使用乘法将其“AND”在一起，并进行更多比较：

LOOKUP(2,1/ ((($B$2:$B$32="Mo")+...+($B$2:$B$32="Fr"))*(($C$2:$C$32<>"holiday")*($C$2:$C$32<>"vacation"))),ROW($B$2:$B$32))-1))

这可行，但非常庞大且难以处理。有没有更优雅的方法，将每个向量仅与列表进行比较一次，如下所示？

LOOKUP(2,1/ (IN($B$2:$B$32, {"Mo","Tu", "We", "Th", "Fr"})*NOTIN($C$2:$C$32, {"holiday","vacation"})),ROW($B$2:$B$32))-1))

答案1

假设数据类似：

在此处输入图片描述

以下公式应返回该月的最后一个工作日：

O365

=WORKDAY(EOMONTH(A2,0)+1,-1,FILTER(tblDates[Dates],(tblDates[Notes]="holiday")+(tblDates[Notes]="vacation")))

早期版本无此FILTER功能

=WORKDAY(EOMONTH(A2,0)+1,-1,AGGREGATE(15,6,1/((tblDates[Notes]="holiday")+(tblDates[Notes]="vacation"))*tblDates[Dates],ROW(INDEX($A:$A,1):INDEX($A:$A,SUM(COUNTIF(tblDates[Notes],{"vacation","holiday"}))))))

公式使用WORKDAY函数，从下个月的第一天开始减去一个 (1) 个工作日。该WORKDAY函数会自动忽略周末，并且还有一个 HOLIDAYS 参数来跳过这些。

相关内容