☰

为文本赋值，并在单个单元格中求和

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
为文本赋值，并在单个单元格中求和

我希望在表格中为文本添加值，然后将这些值加在一起形成定价表。

例如，如果我的单元格 B1 包含字符串“ABCD”，我想为每个字符分配一个值，然后计算总和。假设此示例中的 A=1、B=2、C=3、D=4，我希望新单元格输出“10”（1+2+3+4 的总和）。

编辑：使用 ABCD 作为示例似乎不是一个好选择。在我的实际定价表上，其中一个单元格包含“8205512AS”。对于这个特定的单元格，我希望将“8205”和“512AS”的价格分开。例如，如果“8205”= 5 美元，而“512AS”= 7 美元，我希望新单元格输出 12 美元

答案1

我知道您要求使用工作表函数，但是这里有一个您可以使用的 UDF。（使用 调用=substitute_and_sum(B1)。

Function substitute_and_sum(ByRef cel As Range)

Dim i As Long
Dim subDict As Scripting.Dictionary
Set subDict = New Scripting.Dictionary

subDict.Add "8205", 5
subDict.Add "512AS", 7
subDict.Add "234", 3
subDict.Add "23", 4
subDict.Add "2", 5

Dim output As Long
Dim key As Variant
Dim tmpStr As String
tmpStr = cel.Value

Dim L As Long
If InStr(tmpStr, "512") > 0 Then
    L = 5
ElseIf InStr(tmpStr, "615") > 0 Then
    L = 12
End If

For Each key In subDict.keys
    If InStr(tmpStr, key) > 0 Then
        output = output + subDict(key)
        tmpStr = Replace(tmpStr, key, "")
    End If
Next key

If Right(tmpStr, 1) = "L" Then
    output = output + L
End If

substitute_and_sum = output
End Function

注意：我不知道您有多少个替代品，但如果有一些逻辑，请尝试按“降序”排列……？

在此处输入图片描述

答案2

假设您总是想使用这种模式A=1,B=2,C=3,D=4...，并且您的字符串只包含范围内的字符，[A-Z]那么这将起作用：

=SUMPRODUCT(CODE(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))-64)

这里发生了什么：

  1. MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)循环遍历中的每个字符B1。

  2. 代码提供字符的数字 (ASCII) 值。由于ASCII 值按照模式，A=65, B=66, ...CODE 值的偏移量减去 64。

  3. 乘积和对偏移 ASCII 码值求和。

需要注意的事项：

  1. 小写值[a-z]将提供其他值。根据您想要执行的操作，您可以使用上以避免这种情况。

  2. 您需要删除所有非字母字符（例如，使用代替）

答案3

如果您的文本很容易预测，也许一个字符串需要一个值作为单元格中的前 4 个字符，其余字符作为第二个可行字符串，则可以使用字符串函数将它们分隔开，将它们输入到将在VLOOKUP()具有正确值的表中查找它们，然后添加这些值。

因此，公式的一部分取左边的 4 个字符，并将它们插入到VLOOKUP()查找这些字符并读取价格的函数中，找到 $5。 向其添加第二个公式（RIGHT()可能是函数，使用单元格长度减去第一个字符的 4 个字符来获得长度，或者在函数中使用相同的前 4 个字符，SUBSTITUTE()将“”代入其中，有效地“从文本中减去文本”），该公式取单元格内容的其余部分并进行相同的查找，找到 $7。结果将是您要寻找的 $12。

如果它不容易预测，那么您可以使用数组功能来解决问题，但这足够复杂，需要一个具体的例子来演示。

相关内容