我在多州国家(澳大利亚)安排教育课程时尝试使用 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
用于节假日的列,另一个用于州的列,你可以使用它来创建相关节假日的数组(实际上,相关假期和 的数组0
;0
将被函数中的 Holidays 参数忽略)
(HolidayTbl[State]=myState)*HolidayTbl[Holidays]
(我使用了带有表格的结构化引用,但如果您不想使用表格,您可以将其转换为普通引用)
那么公式将是:
=WORKDAY(StartDate,NumDays,(HolidayTbl[State]=myState)*HolidayTbl[Holidays])
由于这是一个数组公式,因此您需要按住ctrl+shift并点击 来“确认” enter。如果您正确执行此操作,Excel 将{...}
在公式栏中看到括号