实现谷歌表格高亮列内与指定值最接近数值的公式适配求助

问题原因 #

谷歌表格默认不支持Excel的隐式数组运算，你原公式中ABS($R$5:$R$22-$R$4)属于批量数组操作，谷歌表格不会自动遍历整个范围计算，只会取范围首个单元格的计算结果，导致MIN函数返回值错误，公式失效。

解决方案 #

你只需要给数组运算部分加ARRAYFORMULA显式声明数组上下文即可：

• 选中你要设置高亮的范围R5:R22
• 点击「格式」→「条件格式」，右侧条件格式面板的「格式规则」下拉到最底部，选择「自定义公式是」
• 输入以下公式：
  =ABS(R5-$R$4)=MIN(ARRAYFORMULA(ABS($R$5:$R$22-$R$4)))
• 设置你需要的高亮填充色/字体样式，点击完成即可。

优化方案（可选） #

如果要避免指定得分单元格R4为空时出现错误高亮，可以加非空判断：
=IF($R$4<>"",ABS(R5-$R$4)=MIN(ARRAYFORMULA(ABS($R$5:$R$22-$R$4))),FALSE)
该公式可以同时匹配所有和R4差值最小的数值，不管是大于还是小于指定得分，完全匹配需求。 #

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