运用什么公式能够自动根据选定型号进行自动计算

cutecpu

kiki特别帅 发表于 2020-4-6 11:10
如果规格中出现“2*（20*100）”是否能自动计算 2*“20*100”的价格数值 然后再进行求总系数计算呢 太复 ...

您好，請上傳附件

kiki特别帅

cutecpu 发表于 2020-4-6 13:00
您好，請上傳附件

已上传

cutecpu

kiki特别帅 发表于 2020-4-6 16:27
已上传

E4 數組公式:
=ROUND(SUM(
ISNUMBER(FIND("("&I$4:I$15&")","("&TRIM(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99))&")"))*(A4=H$4:H$15)*(D4=J$4:J$15)*K$4:K$15*
IFERROR(LEFT(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99),FIND("(",MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99))-2),1)
)*0.8,)



※數組公式，請記得按Ctrl+Shift+Enter


祝順心，南無阿彌陀佛！

kiki特别帅

cutecpu 发表于 2020-4-6 23:34
E4 數組公式:
=ROUND(SUM(
ISNUMBER(FIND("("&I$4:I$15&")","("&TRIM(MID(SUBSTITUTE(B4,"+",REPT(" ", ...

如果是很多项的话怎么添加呢

cutecpu

kiki特别帅 发表于 2020-4-7 10:43
如果是很多项的话怎么添加呢

原公式:
=ROUND(SUM(
ISNUMBER(FIND("("&I$4:I$15&")","("&TRIM(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99))&")"))*(A4=H$4:H$15)*(D4=J$4:J$15)*K$4:K$15*
IFERROR(LEFT(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99),FIND("(",MID(SUBSTITUTE(B4,"+",REPT(" ",99)),{1,100},99))-2),1)
)*0.8,

新公式:
=ROUND(SUM(
ISNUMBER(FIND("("&I$4:I$15&")","("&TRIM(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),COLUMN($A:$J)*99-98,99))&")"))*(A4=H$4:H$15)*(D4=J$4:J$15)*K$4:K$15*IFERROR(LEFT(MID(SUBSTITUTE(B4,"+",REPT(" ",99)),COLUMN($A:$J)*99-98,99),FIND("(",MID(SUBSTITUTE(B4,"+",REPT(" ",99)),COLUMN($A:$J)*99-98,99))-2),1)
)*0.8,

只有紅色的地方變成藍色而已


祝順心，南無阿彌陀佛！