Excel公式问题：按Z score筛选并将A列值填入C列无空单元格

问题描述 #

需求：仅当B列的Z score处于-2.68至2.68区间时，将A列对应值复制到C列，要求C列无空单元格，公式自动读取A列至数据末尾。尝试以下公式出现问题：

• 使用=FILTER(A2:A35,AND(B2:B35>-2.68,B2:B35<2.68))时返回#CALC!错误
• 使用=FILTER(Table1,ABS(Table1[Z score])<2.68)时生成额外列，未达预期

错误原因 #

1. AND函数的局限性：AND返回单个布尔值（TRUE/FALSE），而非逐行对应的布尔值数组，无法满足FILTER对数组型条件的要求，因此触发#CALC!错误。
2. 结构化表格的范围错误：直接引用Table1会返回整个表格的行数据，而非仅A列内容，因此会生成多余列。

正确公式 #

1. 普通单元格区域（非结构化表格） #

在C2单元格输入以下公式，Excel会自动溢出填充筛选结果至下方单元格，且无空值：

=FILTER(A:A, (B:B > -2.68) * (B:B < 2.68), "")

• 用*代替AND：在数组运算中，*等价于逻辑与，会逐行生成布尔值数组，符合FILTER的条件要求
• 第三个参数""：当无匹配数据时返回空文本（避免#CALC!错误），筛选出的结果均为符合条件的A列值，不会出现空单元格

2. 结构化表格（Table） #

若使用Excel结构化表格，需指定仅提取A列数据（假设A列标题为数据），公式如下：

=FILTER(Table1[数据], ABS(Table1[Z score]) < 2.68, "")

• 直接引用Table1[数据]确保仅提取A列内容，不会生成额外列
• ABS(Table1[Z score]) < 2.68等价于-2.68 < Table1[Z score] < 2.68，简化了条件写法

注意事项 #

• 确保B列的Z score为数值型数据，否则公式无法正确判断条件
• 动态数组公式仅支持Excel 365/2021及以上版本，旧版Excel需改用INDEX+SMALL组合实现类似效果

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