展开单元格的值并将分隔符两边找到的值相乘 [英] Explode a cell's value and multiply together values found on either side of a separator
问题描述
我在印尼工作的印刷工厂的发票上工作。
我非常接近，几乎得到了工作。基本上我有一个列表，可以从列表中选择单元格的纸张尺寸。如果选择符合任何值，则公式对纸张区域进行数学计算。但是我想要插入自定义纸张尺寸，如24x19，并且公式也可以计算。
我尝试将公式放入单元格中，例如24 * 19，但是可以获得excel来实际执行数学运算 - 只需返回值。 p>
所以我继续尝试通过搜索x两边的所有字符并重新声明其值，从而改变单元格的值： D（=（（D（SEARCH（x，D12，1））））*（（RIGHT（D12 SEARCH（x，D12，1））））
但是没有骰子，获得#VALUE！
作为第二个问题 - 我也不喜欢与OR的实例，我有以下。不得不重新声明每个纸张大小的实例将难以维护 - 这可以简化吗？我正在寻求一个ELSE！任何帮助将不胜感激。
= IF（D12 =A5，14.8 * 21，IF（D12 =A4，21 * 29.7，IF（D12 =A3，29.7 * 42，IF（D12 =A33，32.9 * 48.3，IF D12 =A2，42 * 59.4，IF（D12 =A1，59.4 * 84.1，IF（OR（D12A5，D12A4 （D12（SE）（x，D12,1）），D12 =（（D12（SE）（x，D12,1）），A3，D12A3 +，D12A2，D12A1 ））*（（RIGHT（D12（SEARCH（x，D12,1）））））））））））
感谢您的帮助和经验
让我们说你的单元格D12有24x19
将以下公式放在需要答案的地方。
= VALUE（LEFT（D12，FIND（x，D12）-1））* VALUE（RIGHT（D12，LEN（D12）-FIND（x，D12）））
将D12替换为上述公式中的任何单元格，您都可以使用
I'm working on an invoice for the printing facility here at the school Im working at.
Im very close and almost got it working. Basically I have a list of values for papers sizes wich will can be selected in the cell from a list. If the selection matches any of the values the formula does the math for the paper area. But I want to be able to insert custom paper dimensions such as "24x19" and have the formula calculate that as well.
I tried just putting in a formula into the cell such as "24*19" but could get excel to actually do the mathematical operation - just return the value.
So I've gone on to trying to change the value of the cell by searching for all the characters on either side of 'x' and redeclaring its value like so:
D12=((LEFT(D12(SEARCH("x", D12, 1))))*((RIGHT(D12(SEARCH("x", D12, 1))))
But no dice there either, I just get "#VALUE!"
As a secondary question - Im also not please with the instance of OR I have below. Having to redeclare each instance of paper size is going to be hard to maintain - can this be simplified? I'm yearning for an ELSE! any help there would be appreciated.
=IF(D12="A5",14.8*21,IF(D12="A4", 21*29.7,IF(D12="A3",29.7*42,IF(D12="A33",32.9*48.3,IF(D12="A2",42*59.4, IF(D12="A1",59.4*84.1, IF(OR(D12<>"A5", D12<>"A4",D12<>"A3",D12<>"A3+",D12<>"A2",D12<>"A1"), D12=((LEFT(D12(SEARCH("x", D12, 1))))*((RIGHT(D12(SEARCH("x", D12, 1)))) )))))))
Thanks for your help and experience
Lets say you have '24x19' in cell number D12.
Put this following formula where you want the answer
=VALUE(LEFT(D12,FIND("x",D12)-1))*VALUE(RIGHT(D12,LEN(D12)-FIND("x",D12)))
Replace D12 everywhere in above formula with whatever cell you have
这篇关于展开单元格的值并将分隔符两边找到的值相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
