Excel 工作日函数跳过自定义假期数组

Excel 工作日函数跳过自定义假期数组

我在多州国家(澳大利亚)安排教育课程时尝试使用 Excel“工作日”功能来协助处理大量公共和组织假期列表。但是,大量假期列表包含特定地区的假期!因此,有些假期仅适用于特定州,而其他假期则适用于所有州(如果适用,则列出每个州的假期)。

我正在寻求帮助,以便制定一种方法来选择/过滤假期数组以找到我正在查看的特定州,同时将列表保存在单个表中。如果使用 INDEX-MATCH 类型的输入,那么我只会返回一个值。如果有多个连续的假期,这种方法就行不通了。

例如,假设我试图在复活节星期一安排塔斯马尼亚活动,下一个工作日将是星期三(塔斯马尼亚的复活节星期二是假期),而其他州可能会在星期二举办活动(因为没有公共假期)。

是否可以在 Excel 中创建一个动态数组来输入到 Workday 函数中?

示例数据

答案1

假设:

  • 假期列表按州排序
  • 为每个州输入影响所有州的节假日
  • 范围(即超过 1 天)的节假日每天都会进入列表

确定州假日列表的起始行:

AGGREGATE(15,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)

确定您所在州的假日列表的最后一行:

AGGREGATE(14,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)

使用INDEX获取相应行的单元格地址

INDEX(holsheet!D:D,AGGREGATE(15,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)):INDEX(holsheet!D:D,AGGREGATE(14,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1))

您可以将其设置为命名公式,例如 STATE_HOLIDAYS,然后在工作日函数中只需引用 STATE_HOLIDAY,而不是使用丑陋的大公式。

=WORKDAY(C3,-3,STATE_HOLIDAYS) 

答案2

如果你有一个Holiday Table用于节假日的列,另一个用于州的列,你可以使用它来创建相关节假日的数组(实际上,相关假期和 的数组00将被函数中的 Holidays 参数忽略)

(HolidayTbl[State]=myState)*HolidayTbl[Holidays]

(我使用了带有表格的结构化引用,但如果您不想使用表格,您可以将其转换为普通引用)

那么公式将是:

=WORKDAY(StartDate,NumDays,(HolidayTbl[State]=myState)*HolidayTbl[Holidays])

由于这是一个数组公式,因此您需要按住ctrl+shift并点击 来“确认” enter。如果您正确执行此操作,Excel 将{...}在公式栏中看到括号

在此处输入图片描述

相关内容