Excel SUM(FILTER)公式替换Q14为Q14:Q50出现#N/A错误的解决方法

为什么替换后返回#N/A？ #

原公式用单个单元格Q14时，Tbl_Assets[Asset Group as per Co. Act (SCH II)]=Q14会生成一维数组（每个资产组对应一个匹配结果），和后面的处置日期条件数组维度一致，能正常运算。

换成区域Q14:Q50后，这个比较会生成二维数组（每一行资产对应Q列所有单元格的匹配结果），而后面的(Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)是一维数组，两者维度不兼容，导致FILTER函数无法正确处理，最终返回#N/A。

修正后的公式 #

方法1：用MATCH匹配多值（通用所有Excel版本） #

SUM(FILTER(Tbl_Assets[WDV on Year 0],ISNUMBER(MATCH(Tbl_Assets[Asset Group as per Co. Act (SCH II)],Q14:Q50,0))*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))

• ISNUMBER(MATCH(...))会为每个资产组返回1（匹配到Q列任一类别）或0（未匹配），生成的一维数组和处置日期条件维度匹配。
• 若Q列存在空白单元格，可添加过滤规则排除空值：

SUM(FILTER(Tbl_Assets[WDV on Year 0],ISNUMBER(MATCH(Tbl_Assets[Asset Group as per Co. Act (SCH II)],FILTER(Q14:Q50,Q14:Q50<>""),0))*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))

方法2：用XLOOKUP反向匹配（Excel 365/2021及以上） #

SUM(FILTER(Tbl_Assets[WDV on Year 0],NOT(ISERROR(XLOOKUP(Tbl_Assets[Asset Group as per Co. Act (SCH II)],Q14:Q50,Q14:Q50)))*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))

逻辑和MATCH一致，通过XLOOKUP检查资产组是否存在于Q列区域中。

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