非Office 365版Excel中FILTER函数的替代方案及问题求助

错误根源 #

你使用的AGGREGATE函数返回的是工作表绝对行号（例如A5对应的行号是5），但INDEX的第二参数需要的是目标区域内的相对偏移位置（A5:A33的第一行对应偏移量1，而非5），直接用绝对行号导致结果偏移，出现“靠后3行”的问题。

修正后的公式 #

=IFERROR(INDEX('50% Offer'!B5:B33,AGGREGATE(15,7,(ROW('50% Offer'!A5:A33)-ROW('50% Offer'!A5)+1)/('50% Offer'!A5:A33=C12),ROW($ZZ1))),"")

核心修改解释 #

• ROW('50% Offer'!A5:A33)-ROW('50% Offer'!A5)+1：将绝对行号转换为A5:A33区域内的相对行位置（A5对应1，A6对应2……），确保AGGREGATE返回的数值是INDEX所需的正确偏移量，消除结果偏移问题。
• ROW($ZZ1)用于下拉时自动递增，获取第N个匹配结果；IFERROR处理无匹配项时的空值显示。

兼容性说明 #

该公式支持所有包含AGGREGATE函数的Excel版本（Excel 2019及后续版本，部分Excel 2016版本也兼容），不会出现_xlfn/_xlws前缀导致的失效问题，非Office 365用户可正常使用。

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