各位老师看看这题，谁能写出最简洁的公式？

fsw0812

名称管理器建立=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(香烟包数!$A2,"箱","*500+"),"条","*10+"),"包","*1+")&0)

hjxszg

mykitami 发表于 2024-1-12 14:54
=SUM(MAP({"箱","包","条"},LAMBDA(x,IFERROR(MATCH(,-FIND(COLUMN(1:1)&x,A2)),)))*{500,1,10})

整体思路类似，区别在于提取的方式，相差1个字符。
5023=SUM(MAP({"箱","条","包"},LAMBDA(X,IFERROR(REGEXP(REGEXP(A2,"\d+"&X),"\d+"),)))*{500,10,1})

公式一=MAP(A2:A12,LAMBDA(Y,SUM(MAP({"箱","条","包"},LAMBDA(X,IFERROR(REGEXP(REGEXP(Y,"\d+"&X),"\d+"),)))*{500,10,1})))

公式二=SUM(MAP(A2:A12,LAMBDA(Y,SUM(MAP({"箱","条","包"},LAMBDA(X,IFERROR(REGEXP(REGEXP(Y,"\d+"&X),"\d+"),)))*{500,10,1}))))

ＷＳＣ123

=EVALUATE(REGEXP(SUBSTITUTES(A2,{"箱","条","包"},{"*500+","*10+","*1+"}),"\+$",2,""))    公式81个字符

hjxszg

ＷＳＣ123 发表于 2025-6-5 10:13
=EVALUATE(REGEXP(SUBSTITUTES(A2,{"箱","条","包"},{"*500+","*10+","*1+"}),"\+$",2,""))    公式81个字 ...

这个公式只计算出A2单元格的和，并没有计算出全部。如果只计算A2。=SUM(IFERROR(REGEXP(REGEXP(A2,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},))
71字符

hjxszg

71字符A2=SUM(IFERROR(REGEXP(REGEXP(A2,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},))


92字符公式一=MAP(A2:A12,LAMBDA(Y,SUM(IFERROR(REGEXP(REGEXP(Y,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},))))


97字符公式二=SUM(MAP(A2:A12,LAMBDA(Y,SUM(IFERROR(REGEXP(REGEXP(Y,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},)))))

hjxszg

=BYROW(IFERROR(REGEXP(REGEXP(A2:A12,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},),SUM)公式一81个字符
=SUM(BYROW(IFERROR(REGEXP(REGEXP(A2:A12,"\d+"&{"箱","条","包"}),"\d+")*{500,10,1},),SUM))公式二86个字符
还能优化这样提取 "\d+(?=箱)"

人有言兮是然

=SUM(--IFERROR(REGEXP(A2,"\d+"&"(?="&{"箱","条","包"}&")"),0)*{500,10,1})
71个字符

hjxszg

人有言兮是然 发表于 2025-6-5 11:27
=SUM(--IFERROR(REGEXP(A2,"\d+"&"(?="&{"箱","条","包"}&")"),0)*{500,10,1})
71个字符

"\d+(?=箱)"就是在找这个表达式，感谢，学习了！

szl3178

1. =IFERROR(SUM(IFNA(REGEXP(A2,"\d+(?="&{"箱","条","包"}&")"),)*{500,10,1}),)

复制代码

hjxszg

szl3178 发表于 2025-6-5 12:00

"\d+(?=箱)"不会写表达式到处找，感谢大神们。