如何在Excel单元格中以分号分隔的值求和 [英] How to sum values separated by semicolon in excel cell

问题描述

我在excel中有一个单元格，其中包含多个用分号分隔的值，我需要对每个单元格求和.

I have cell in excel that contains multiple values separated by semicolon, I need to sum each cell.

每个单元格中的值数为N(未知)，它只能是一个值，也可以是20或更大.

Number of values in each cell is N (unknown), it can be only one value or it can be 20 or more.

为什么我用分号分隔值是每个行都代表我们要销售的产品.在该行中是包含该产品售价的单元格.
例子名为室内门"的产品以不同的价格售出了3次，因此单元格包含三个值:102,5; 98,2; 101,00现在，我需要对这三个值求和以获得总价(以该产品的销售价).

The issue why I have values separated by semicolon is that each row represents product that we are selling. In that row is cell that contains selling prices of that product.
Example Product called "indoor door" were sold three times at different price, so cell contains three values: 102,5;98,2;101,00 Now I need to sum those three values to get total price (at which product was sold).

推荐答案

您可以使用以下公式:

=eval(SUBSTITUTE(SUBSTITUTE(A1, ";", "+"),",","."))

将用户定义的功能添加到您的工作簿后:

after adding user defined function to your workbook:

Function eval(str As String)
   eval = Evaluate(str)
End Function

这篇关于如何在Excel单元格中以分号分隔的值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！