☰

Как получить сумму числовых значений в буквенно-цифровой строке в формуле Microsoft Excel?

tag-icon • tag-icon microsoft-excel
Как получить сумму числовых значений в буквенно-цифровой строке в формуле Microsoft Excel?

У меня есть данные в ячейке, например:

Хороший ответ -6, RNR -2, Нет голоса -1

Мне нужно получить сумму только чисел в ячейке. Как это сделать в одной формуле?

решение1

Я использую ExcelНадстройка RegEx Find/Replace, он добавляет в Excel функциональность регулярных выражений.
(Я никак не связан с этим дополнением, просто энтузиаст-пользователь)

Здесь вы можете использовать такую ​​формулу, чтобы найти и суммировать все числа:
=SUM(RegExFind(A1,"\d+"))

Или рассмотреть и отрицательные значения:
=SUM(RegExFind(A1,"[-\d]+"))

введите описание изображения здесь

решение2

На основании предоставленной информации и при условии, что будет извлекаться только 3 значения, каждое из которых будет иметь длину в 1 цифру, приведенный ниже код будет работать для строки вА2:

=LEFT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(A2,1)

Конечно, я сделал здесь несколько предположений,

Если это не сработает, объясните мне более подробно, как структурированы ваши данные и т. д., и я постараюсь внести изменения.

решение3

Если ваши данные действительно такие, как вы их показываете, и перед числом стоит символ <space>, а за ним следует символ <comma>или конец строки, то вы можете использовать следующее.

Определите формулу и назовите ее, seq_99где

seq_99 refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

Тогда вы можете использовать этовведенный массивформула:

=SUM(IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))),--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))))

Кмассив-вводформулу, удерживайте ctrl+ shiftпри нажатии enter. Если вы сделаете это правильно, Excel поместит скобки {...}вокруг формулы, которые вы увидите в строке формул.

Я предположил, что -обозначено отрицательное число. Если это не так, то тривиально изменить формулу, чтобы исключить его.

решение4

Я хотел бы предложить небольшую функцию VBA. Обратите внимание, что разделитель можно настроить, как показано ниже.

Function SumNumbers(rngS As Range, Optional strDelim As String = "-") As Double

    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)

    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum

End Function

введите описание изображения здесь

Примечания:

  • Используйте эту функцию обычным способом:=SumNumbers(Адрес ячейки).
  • Если разделителем является ПРОБЕЛ, то используйтеstrDelim как строка = " "в списке аргументов.
  • Если разделителем является ЗАПЯТАЯ, то используйтеstrDelim как строка = ","в списке аргументов.

Я надеюсь, это поможет вам.

Связанный контент