在Google Sheets中用ARRAYFORMULA+IFS+AND转换单位时遇范围不匹配错误

错误原因分析 #

• AND函数不兼容数组运算：AND是单值函数，在ARRAYFORMULA中调用时只会返回单个布尔值，而IFS要求每个条件和对应的结果范围必须维度一致，这直接触发了"IFS HAS MISMATCH RANGE"错误。
• 单位字符串大小写不一致：公式中存在G3="CM"（大写）和其他小写单位的匹配逻辑，会导致部分条件无法正确触发。
• 结果范围不统一：部分CONVERT使用H2单单元格，另一部分用H2:J2数组范围，长度不匹配加剧了范围错误。

修正后的公式 #

=ARRAYFORMULA(IFS(
  (G2="inches")*(G3="cm"), CONVERT(H2:J2,"in","cm"),
  (G2="inches")*(G3="mm"), CONVERT(H2:J2,"in","mm"),
  (G2="inches")*(G3="Meter"), CONVERT(H2:J2,"in","m"),
  (G2="cm")*(G3="mm"), CONVERT(H2:J2,"cm","mm"),
  (G2="cm")*(G3="inches"), CONVERT(H2:J2,"cm","in"),
  (G2="cm")*(G3="Meter"), CONVERT(H2:J2,"cm","m"),
  (G2="mm")*(G3="cm"), CONVERT(H2:J2,"mm","cm"),
  (G2="mm")*(G3="inches"), CONVERT(H2:J2,"mm","in"),
  (G2="mm")*(G3="Meter"), CONVERT(H2:J2,"mm","m"),
  (G2="Meter")*(G3="inches"), CONVERT(H2:J2,"m","in"),
  (G2="Meter")*(G3="cm"), CONVERT(H2:J2,"m","cm"),
  (G2="Meter")*(G3="mm"), CONVERT(H2:J2,"m","mm"),
  (G2="pound")*(G3="kg"), CONVERT(H2:J2,"lbm","kg"),
  (G2="kg")*(G3="pound"), CONVERT(H2:J2,"kg","lbm"),
  TRUE, ""
))

关键修改说明 #

1. 替换AND为数组逻辑与：用(条件1)*(条件2)代替AND(条件1,条件2)，在数组环境中能逐个单元格判断逻辑与，返回和结果范围长度一致的布尔数组，解决维度不匹配问题。
2. 统一单位大小写：将G3="CM"改为G3="cm"，确保单位匹配逻辑一致。
3. 统一结果范围：所有CONVERT的输入范围统一为H2:J2，保证条件数组和结果数组的维度完全对应。
4. 添加默认空值：最后加TRUE, ""，当所有条件都不满足时返回空值，避免出现#N/A错误。

特殊情况处理（仅H2为重量单位） #

如果只有H2是重量（磅/千克），H3:J2为长度单位，可调整重量相关的转换逻辑，限定仅H2单元格生效：

=ARRAYFORMULA(IFS(
  (G2="inches")*(G3="cm"), CONVERT(H2:J2,"in","cm"),
  // 省略其他长度转换条件
  (G2="pound")*(G3="kg"), IF(COLUMN(H2:J2)=COLUMN(H2), CONVERT(H2,"lbm","kg"), ""),
  (G2="kg")*(G3="pound"), IF(COLUMN(H2:J2)=COLUMN(H2), CONVERT(H2,"kg","lbm"), ""),
  TRUE, ""
))

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