时间戳

首页 > 解决方案 > 根据 Google 表格上的范围与多个 IF 语句相乘

google-sheets - 根据 Google 表格上的范围与多个 IF 语句相乘

问题描述

我正在尝试创建一个动态公式，如果一个单元格的值在一个范围内，它将乘以另一个单元格的某个值，然后检查下一个单元格，如果匹配的值乘以另一个细胞。

我在单元格上有一个基值R2，应该根据单元格的值乘以某个数字column I，然后根据 的值乘以某个数字column J。

我想出了以下内容：

=R2*ifs($I$2:I386,"<=0.85",$I$2:I386,">0.5")*R4,ifs($I$2:I386,"<=0.9",$I$2:I386,">0.85")*R5,ifs($I$2:I386,"<=0.95",$I$2:I386,">0.9")*R6,ifs($I$2:I386,"<=1",$I$2:I386,">0.95")*R7,ifs($I$2:I386,"<=1.05",$I$2:I386,">1")*R8,ifs($I$2:I386,"<=1.1",$I$2:I386,">1.05")*R9,ifs($I$2:I386,"<=1.15",$I$2:I386,">1.1")*R10,ifs($I$2:I386,"<=1.20",$I$2:I386,">1.15")*R11,ifs($I$2:I386,"<=1.25",$I$2:I386,">1.20")*R12*ifs($J$2:J386,"1")*R14,ifs($J$2:J386,"2")*R15,ifs($J$2:J386,"3")*R16,ifs($J$2:J386,"4")*R17,ifs($J$2:J386,"5")*R18,ifs($J$2:J386,"N/A")*R19

但是，我仍然遇到错误，并且卡住了。不知道如何改变它。

标签： google-sheets

解决方案

尝试：

=ARRAYFORMULA(
 IF((I2:I386 <= 0.85)*(I2:I386 > 0.5),  R2*R4,
 IF((I2:I386 <= 0.9) *(I2:I386 > 0.85), R2*R5,
 IF((I2:I386 <= 0.95)*(I2:I386 > 0.9),  R2*R6,
 IF((I2:I386 <= 1)   *(I2:I386 > 0.95), R2*R7,
 IF((I2:I386 <= 1.05)*(I2:I386 > 1),    R2*R8,
 IF((I2:I386 <= 1.1) *(I2:I386 > 1.05), R2*R9,
 IF((I2:I386 <= 1.15)*(I2:I386 > 1.1),  R2*R10,
 IF((I2:I386 <= 1.2)*(I2:I386 > 1.15), R2*R11,
 IF((I2:I386 <= 1.25)*(I2:I386 > 1.2), R2*R12,
 IF(J2:J386 = 1,     R2*R14,
 IF(J2:J386 = 2,     R2*R15,
 IF(J2:J386 = 3,     R2*R16,
 IF(J2:J386 = 4,     R2*R17,
 IF(J2:J386 = 5,     R2*R18,
 IF(J2:J386 = "N/A", R2*R19, ))))))))))))))))

见：https ://webapps.stackexchange.com/q/123729/186471

推荐阅读