Excel 2202版本下，如何用动态溢出数组生成用户排序日期列表？

解决方案 #

在你的Excel版本限制下（支持动态数组函数但无法使用BYROW/LAMBDA），可以通过以下公式实现无需拖拽、自动溢出的用户降序日期列表：

步骤1：确认唯一用户列表

确保A2单元格已通过以下公式生成唯一用户的溢出列表：

=UNIQUE(main_data[USER])

步骤2：生成自动溢出的日期列表

在C2单元格输入以下公式，它会自动向下+向右溢出，每行对应A2#中的一个用户，横向展示该用户近365天的降序日期，无有效日期的位置显示空值（避免冗余空白）：

=IFERROR(INDEX(
    SORT(FILTER(main_data[DATE], main_data[DATE] > TODAY() - 365), , -1),
    SMALL(
        IF(
            (main_data[USER] = TRANSPOSE(A2#)) * (main_data[DATE] > TODAY() - 365),
            ROW(main_data[DATE]) - MIN(ROW(main_data[DATE])) + 1
        ),
        SEQUENCE(, MAX(COUNTIFS(main_data[USER], A2#, main_data[DATE], ">" & TODAY() - 365)))
    )
), "")

公式说明

• FILTER(main_data[DATE], main_data[DATE] > TODAY() - 365)：先筛选所有近365天的日期
• SORT(..., , -1)：将筛选后的日期按降序排列
• TRANSPOSE(A2#)：把用户列表转置为列，实现与每行用户的匹配
• (main_data[USER] = TRANSPOSE(A2#)) * (main_data[DATE] > TODAY() - 365)：标记每个用户对应的有效日期位置
• SMALL(..., SEQUENCE(...))：为每个用户提取对应日期的排序序号，横向覆盖所有用户的最大日期数量
• INDEX(...)：从排序后的日期数组中提取对应序号的日期
• IFERROR(..., "")：将无日期的位置转为空值，避免错误显示

注意事项 #

由于动态数组溢出区域为矩形，列数会等于单个用户的最多有效日期数，无日期的列会显示空值而非空白单元格，这是现有版本限制下的最优结果，不会影响数据使用。

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