excel - #N/A 当我尝试在 excel 中使用此 IFS 公式时

Question

我已经尝试了几个小时来使用这两个公式，其中 A2 是一个随机数：

=IFS(A2<=0.02,3368.7*A2,0.02<=A2<0.46,46.406*LN(A2)+245.66,0.46<A2<=0.59,-3252.4*(A2^2)-838.98,0.59<A2<=0.67,35.091*A2+274.45,0.67<A2<=0.83,1466.4*A2-689.81,0.83<A2,256.31*A2+315.02)

=IFS(U2<=0.01,97.675*U2+0.0071,0.01<U2<=0.42,41.88*U2+2.2726,0.42<U2,19.72)

它显示 #N/A 错误，我不知道我做错了什么

标签： excelexcel-formulaspreadsheet

Answer 1

0.02<=A2<0.46是不正确的，人们会使用 AND：

=AND(0.02<=A2,A2<0.46)

但它不是必需的，因为 IFS 将按顺序解析，如果找到解析为的第一个标准，它将停止TRUE：

=IFS(A2<=0.02,3368.7*A2,A2<0.46,46.406*LN(A2)+245.66,A2<=0.59,-3252.4*(A2^2)-838.98,A2<=0.67,35.091*A2+274.45,A2<=0.83,1466.4*A2-689.81,TRUE,256.31*A2+315.02)

和

=IFS(U2<=0.01,97.675*U2+0.0071,U2<=0.42,41.88*U2+2.2726,TRUE,19.72)