Google Sheets中IF函数数组错误及<100%筛选问题求助

问题概述 #

在Google Sheets仪表盘使用嵌套IF+FILTER实现数据筛选时，存在两个问题：

• 当B31选择<100%且D31等于'Master Data'!AC1时，仅在下方无新增数据时正常返回结果，新增数据后失效
• 需要实现：当B31设为100%且选择具体学生姓名时，返回"Select All"提示

问题分析与修复方案 #

1. 新增数据后筛选失效的修复 #

原公式使用固定列范围（如AC2:AC）会包含大量空行，新增数据后FILTER的条件判断会被空行干扰。改用动态数据范围，仅包含有实际内容的行，避免空行影响筛选逻辑。

2. 100%筛选分支的逻辑补充 #

原公式未处理B31为100%的场景，新增该分支：

• 若D31为'Master Data'!AC1（全选），筛选AI列等于1的行
• 若D31为具体学生姓名，返回"Select All"提示
• 若D31无效，返回"Select a valid student name"

修复后的完整公式 #

=LET(
    // 定义动态数据范围，仅包含有数据的行
    data_range, 'Master Data'!AC2:INDEX('Master Data'!AI:AI, COUNTA('Master Data'!AC:AC)),
    student_col, INDEX(data_range, , 1),
    progress_col, INDEX(data_range, , 7), // AI列是第7列（AC为第1列，AF=4, AH=6, AI=7）
    // 核心筛选逻辑
    IF(
        B31 = "All",
        IF(
            D31 = 'Master Data'!AC1,
            FILTER(CHOOSECOLS(data_range, 1, 4, 6, 7), student_col <> ""),
            IF(
                ISNUMBER(MATCH(D31, student_col, 0)),
                FILTER(CHOOSECOLS(data_range, 1, 4, 6, 7), student_col = D31),
                "Select a student name"
            )
        ),
        IF(
            B31 = "<100%",
            IF(
                D31 = 'Master Data'!AC1,
                FILTER(CHOOSECOLS(data_range, 1, 4, 6, 7), progress_col < 1, student_col <> ""),
                "Select 'All' to filter by student name"
            ),
            IF(
                B31 = "100%",
                IF(
                    D31 = 'Master Data'!AC1,
                    FILTER(CHOOSECOLS(data_range, 1, 4, 6, 7), progress_col = 1, student_col <> ""),
                    IF(
                        ISNUMBER(MATCH(D31, student_col, 0)),
                        "Select All",
                        "Select a valid student name"
                    )
                ),
                "Invalid selection for B31"
            )
        )
    )
)

关键修改说明 #

• 使用LET函数定义动态范围data_range，通过INDEX+COUNTA自动锁定有数据的行，兼容后续新增数据
• 用CHOOSECOLS简化列选择逻辑，避免重复写长列引用
• 新增100%分支的完整逻辑，满足"选择学生姓名时返回Select All"的需求
• 在<100%的筛选条件中保留student_col <> ""，排除空学生行

内容的提问来源于stack exchange，提问作者Gina Brasongré