平时用 Excel/WPS表格求和，最怕遇到文本 + 数字混合的单元格，比如一个单元格内有苹果25.83元、香蕉15.65元、猕猴桃28.03元、西瓜26.3元、水蜜桃38.58元，要计算买这些水果总共用了多少钱，手动拆分数字再计算，又慢又容易错！今天给大家分享一个万能 VBA 自定义函数：SumMix，一键提取混合内容里的所有数字，自动求和，正负小数都能算，选区一框就出结果一、函数核心功能SumMix是专为 Excel/WPS表格打造的混合文本数字求和函数，核心能力拉满：1.自动提取：不管单元格里是汉字、符号、空格、字母，只揪出里面的数字（整数、小数、负数都能识别）2.智能求和：提取所有有效数字后自动累加，无需手动拆分、整理数据3.容错处理：自动忽略多余小数点、无效负号，避免计算错误4.批量计算：支持选中整行、整列、任意连续 / 非连续区域，一键出总和示例1：A1:苹果25.83元、香蕉15.65元、猕猴桃28.03元、西瓜26.3元、水蜜桃38.58元输入公式：SumMix(A1)计算结果：134.39
示例2：A1:苹果25.83元、香蕉15.65元；A2:猕猴桃28.03元、西瓜26.3元、水蜜桃38.58元输入公式：SumMix(A1:A2)计算结果：134.39
示例3：A1:苹果25.83元、香蕉15.65元；A2:猕猴桃28.03元、西瓜26.3元；C3:水蜜桃38.58元输入公式：SumMix(A1:A2,C3)计算结果：134.39
Option ExplicitPublic Function SumMix(ParamArray Ranges() As Variant) As Double Dim total As Double Dim item As Variant Dim rng As Range Dim cell As Range For Each item In Ranges If TypeOf item Is Range Then Set rng = item For Each cell In rng If Not IsEmpty(cell.Value) And Not IsError(cell.Value) Then total = total + ExtractNumbersFromString(CStr(cell.Value)) End If Next cell End If Next item SumMix = totalEnd FunctionPrivate Function ExtractNumbersFromString(ByVal txt As String) As Double Dim i As Long Dim ch As String Dim currNum As String Dim result As Double currNum = "" result = 0 For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "[0-9]" Or ch = "." Or ch = "-" Then If ch = "-" Then If currNum = "" Then currNum = "-" Else If currNum <> "" And currNum <> "-" And currNum <> "." Then result = result + Val(currNum) End If currNum = "-" End If ElseIf ch = "." Then If InStr(currNum, ".") > 0 Then If currNum <> "" And currNum <> "-" And currNum <> "." Then result = result + Val(currNum) End If currNum = "" Else currNum = currNum & ch End If Else currNum = currNum & ch End If Else If currNum <> "" And currNum <> "-" And currNum <> "." Then result = result + Val(currNum) End If currNum = "" End If Next i If currNum <> "" And currNum <> "-" And currNum <> "." Then result = result + Val(currNum) End If ExtractNumbersFromString = resultEnd Function
- 打开 Excel，按
Alt+F11 打开 VBA 编辑器；
夜雨聆风
