如何在Excel的FILTER函数中排除指定列且无需手动构建数组？

针对你需要筛选大型数组但排除指定列（如D列）的需求，以下是几种无需手动输入{1,1,1,0,...}这类固定数组的解决方案，适用于不同版本的Excel：

方法1：使用CHOOSECOLS + 动态列号筛选（Excel 365/2021及以上） #

这是最灵活的方法，适合排除任意位置的列，无需硬编码列数：

=CHOOSECOLS(
    FILTER(OCC_Format!A1:BA150000,
        (OCC_Format!B1:B150000<>"")*(OCC_Format!A1:A150000=TIMS_OCC_Analysis!BX1)
    ),
    FILTER(SEQUENCE(COLUMNS(OCC_Format!A1:BA150000)),
        SEQUENCE(COLUMNS(OCC_Format!A1:BA150000))<>4
    )
)

原理： #

1. 先用FILTER筛选出符合行条件的所有数据
2. 用SEQUENCE(COLUMNS(...))生成从1到总列数的列号数组
3. 再用FILTER剔除等于4的列号（对应D列）
4. 最后用CHOOSECOLS提取筛选后的列号对应的列数据

如果想让公式更简洁易维护，可以用LET函数封装变量，避免重复计算：

=LET(
    data, OCC_Format!A1:BA150000,
    filteredRows, FILTER(data, (INDEX(data,,2)<>"")*(INDEX(data,,1)=TIMS_OCC_Analysis!BX1)),
    validCols, FILTER(SEQUENCE(COLUMNS(data)), SEQUENCE(COLUMNS(data))<>4),
    CHOOSECOLS(filteredRows, validCols)
)

方法2：使用DROP/TAKE + HSTACK拆分合并（Excel 365/2021及以上） #

如果要排除的列是中间某一列（如D列，前3列是A-C，后面是E-BA），可以直接拆分数据再合并：

=HSTACK(
    TAKE(
        FILTER(OCC_Format!A1:BA150000,
            (OCC_Format!B1:B150000<>"")*(OCC_Format!A1:A150000=TIMS_OCC_Analysis!BX1)
        ),,3
    ),
    DROP(
        FILTER(OCC_Format!A1:BA150000,
            (OCC_Format!B1:B150000<>"")*(OCC_Format!A1:A150000=TIMS_OCC_Analysis!BX1)
        ),,4
    )
)

原理： #

1. TAKE(...,3)提取筛选结果的前3列（A-C）
2. DROP(...,4)移除筛选结果的前4列，保留E-BA列
3. HSTACK将两部分数据横向合并，得到排除D列的最终结果

方法3：旧版Excel兼容方案（无动态数组函数） #

如果你使用的是不支持动态数组的旧版Excel，可以用INDEX+SMALL组合实现，需按Ctrl+Shift+Enter作为数组公式输入：

=INDEX(OCC_Format!A:BA,
    SMALL(
        IF((OCC_Format!B1:B150000<>"")*(OCC_Format!A1:A150000=TIMS_OCC_Analysis!BX1),
            ROW(OCC_Format!A1:A150000)-ROW(OCC_Format!A1)+1
        ),
        ROWS($A$1:A1)
    ),
    IF(COLUMN(A1)<4,COLUMN(A1),COLUMN(A1)+1)
)

原理： #

1. SMALL+IF定位符合条件的行号
2. IF(COLUMN(A1)<4,...)调整列号：当当前列号小于4时直接使用，否则加1跳过D列（第4列）
3. 下拉填充获取所有符合条件的行，右拉填充获取所有排除D列的列

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