Excel混合数据直接计算公式法与正则法

将下面混合的数据直接计算出结果:

分析:这个数据其实很有规律

1、“+”加号分割,4段字符串

2、"["左方括号前面是数值,后面是字符

所以可以考虑使用公式提取数值直接运算。

公式法:

=SUM(--LEFT(TRIM(MID(SUBSTITUTE(A2,"+",REPT(" ",LEN(A2))),(ROW(1:4)-1)*LEN(A2)+1,LEN(A2))),FIND("[",TRIM(MID(SUBSTITUTE(A2,"+",REPT(" ",LEN(A2))),(ROW(1:4)-1)*LEN(A2)+1,LEN(A2))))-1))

我们来看看这个公式的组成:

这个公式最核心的部分:就是上图标颜色的部分,功能是:

将原来的字符串从加号(“+”)处分成4段。

这段公式,我们在前面讲过,稍有不同的地方是,MID函数的截取位置参数,也就是第二参数:

(ROW(1:4)-1)*LEN(A2)+1

前面讲的这个参数我们使用的单个行号或列号参与运算,

在这个例子里面,为了一次性计算4个值的和,我们采用了数组参与运算。

用ROW(1:4)构造了一个这样的数组

直接一次性处理4段字符串。

我们用TRIM代替这段核心代码,公式就简化成这个样子:

记住这里的TRIM是4个字符串组成的数组,并不是单个字符

SUM----是求和,求什么和?

求的是--用LEFT从TRIM中截取出来的数值的和(“--”用来将字符转换成数值)

LEFT截取的长度是FIND“["的位置减去1。

以上是公式法的过程分析。

正则法:

上一篇我们讲过用自定义正则函数提取数字或字符

正好这里可以用到。

不过要稍作改动:

为了求和,也需要将字符串转换为数值,然后相加

用Val函数转换成数值,加上找到的符合条件的值。

附上代码,方便大家做测试

Function ExAdd(Str As Range, Parttern As String, ActionID As Integer, Optional RepStr As String = "")

Dim regex As Object

Set regex = CreateObject("vbscript.regexp")

With regex

.Global = True

.IgnoreCase = True

.MultiLine = True

.Pattern = Parttern

End With

Select Case ActionID

Case 1:

ExAdd = regex.Replace(Str, RepStr)

Case 2:

ExAdd = regex.test(Str)

Case 3:

Dim matches As Object

Set matches = regex.Execute(Str)

For Each Match In matches

ExAdd = Val(ExAdd) + Match.Value '用Val转换为数值并相加

'ExStr = ExStr & Match.Value 用&链接字符串

Next

End Select

End Function

当然还要用到正则表达式

这个表达式就是提取浮点数值用的表达式

这两种方法大家都可以试一试

公式法,别忘记按三键CTRL+SHIFT+ENTER。

正则法,别忘记保存为启用宏的工作簿再使用。

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180303A0019B00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券