实现月度工作日到调度工具导入文件格式的转换优化

问题拆解 #

你当前的MAKEARRAY公式会重复堆叠同一日期，核心原因是它直接按需求数量复制整行数据，没有基于月度工作日序列循环提取日期。要解决这个问题，需要完成三个核心步骤：

• 生成目标月份的完整工作日列表（自动排除周末）
• 根据需求的工作日数，从列表中循环提取日期（而非重复同一日期）
• 为每个匹配到的工作日绑定9:00-18:00的时间段

假设数据结构 #

假设你的表格布局如下（可按需扩展范围）：

• A列：月度所需工作日数（例：A1=25对应5月的需求）
• C列：对应月份的起始日期（例：C1=2024-05-01，需为Excel标准日期格式）

优化后的公式 #

直接使用以下公式即可生成无重复堆叠的时间块：

=LET(
    // 定义数据范围，按需调整行数
    reqs, A1:A2,
    month_starts, C1:C2,
    // 生成单月所有工作日序列
    get_month_workdays, LAMBDA(start,
        LET(
            end, EOMONTH(start,0),
            WORKDAY.INTL(start, SEQUENCE(NETWORKDAYS.INTL(start, end)), "0000011")
        )
    ),
    // 根据需求循环提取工作日，避免重复堆叠
    get_demand_dates, LAMBDA(start, demand,
        LET(
            wd_list, get_month_workdays(start),
            wd_count, ROWS(wd_list),
            // 用MOD实现循环索引，按工作日顺序依次取值
            indices, MOD(SEQUENCE(demand)-1, wd_count)+1,
            INDEX(wd_list, indices)
        )
    ),
    // 合并所有月份的目标日期
    all_dates, VSTACK(
        get_demand_dates(C1,A1),
        get_demand_dates(C2,A2)
    ),
    // 生成Start/End时间
    starts, all_dates + TIME(9,0,0),
    ends, all_dates + TIME(18,0,0),
    // 输出最终导入格式
    HSTACK(starts, ends)
)

关键细节说明 #

• get_month_workdays：通过WORKDAY.INTL生成指定月份的所有工作日，第三个参数"0000011"表示周六周日休息，可根据实际排班调整（比如"0000001"仅周日休息）。
• get_demand_dates：利用MOD函数计算循环索引，确保按工作日序列依次提取日期，彻底避免同一日期重复堆叠的问题。
• 如果你的月份是文本格式（如"May"），可通过DATEVALUE("1 "&B1&" 2024")转换为标准日期格式后再使用公式。

内容的提问来源于stack exchange，提问作者Giovanni