图片
前两天我们刚介绍了正则表达式函数，今天就遇到了一个数据处理的实际问题。本来按照习惯使用了其他方法，后来想到可以用正则表达式函数，就又处理了一遍。今天分享一下这个过程和处理方法，供大家参考。
问题
原始数据是这样的，
图片
要求也简单：
1. 提取每行的数据求和
2. 将数据转换成下面的形式：
图片
分析
第一个需求还是很简单的，我们上次介绍的时候其实提到过。以第一行数据为例，
=REGEXEXTRACT(B2,"[0-9]+",1)
这个公式就可以提取其中的所有数据，
图片
接下来只要将这个数组求和就好了。因为这个函数结果是文本，不要忘了转换，
=SUM(--REGEXEXTRACT(B2,"[0-9]+",1))
但是第二个需求就复杂一些了。分析一下，需要做如下的工作：
首先需要提取每一行数据中的那些字母（产品名称），还需要提取其中的数字。
然后需要汇总这一行中产品名称相同（字母相同）的那些数据，比如B4中的数据就需要将所有的名称为A的数据汇总到一起。
经过前两步，我们就已经将每行数据处理成了两个一行多列的数组，一个数组是产品名称，另外一个数组是数据。比如对于B2单元格的数据来说，处理结果就是：{"D", "C"}{"69", "540"}接下来，我们需要将所有的数据合并到一起。
第一步很简单，实际上我们刚才已经做了演示，可以将B列每个单元格的数据中的各产品数据取出。同样的方法也可以将各个字母取出，只不过模式字符串需要修改一下：
=REGEXEXTRACT(B2,"[A-Z]",1)
不过这里需要提醒一下，由于提取的数据组成的数组是多行数组，跟我们需要的多列数组不一样。（转换很容易实现，转置即可）
第二步本身也不复杂，有很多方法实现，比如可以使用GROUPBY函数实现，不过考虑到还有第三步，这个方法未必好。
第三步多行合并可以使用VSTACK函数。但是我们不能直接使用，因为第一步处理完成后，各行数据中的产品名称顺序和个数未必相同。
这些处理就会比较啰嗦。
所以我们就修改一下实现思路。
实现思路
第一步不变，还是使用正则函数提取名称和数据数组。
接下来，我们增加一个标题行数组：{“A", "B", "C", "D"}
这里我们仅仅用了数据中出现的四个字母，其实可以用SEQUENCE函数生成所有的字母列表。这样通用性更强。
然后呢，我们使用这个标题行数组的每一个元素对上面B列每一个数据得到来的两个数组进行条件求和，比如，对于”A：
我们计算B2中产品名称 = "A"的所有数据之和。
由于SUMIF不能用于数组，所以我们改用SUMPRODUCT：
=SUMPRODUCT(--(titleC = "A"), dataC)
其中，titleC是正则函数得到的产品名称数组，dataC是得到的数据数组。
图片
我们就可以得到B列数据每一行对应于标题行数组中每一个元素的数据。
于是就得到了结果表。
实现
上面的过程显然可以使用MAKEARRAY函数轻松实现。
下面是实现的公式：
=LET( data, B2:B27, title, {"A","B","C","D"}, VSTACK(title, MAKEARRAY(ROWS(data), COLUMNS(title), LAMBDA(r,c, LET( name, INDEX(title, 1, c), dRow, INDEX(data, r, 1), titleC, REGEXEXTRACT(dRow,"([A-Z])",1), dataC, REGEXEXTRACT(dRow,"([0-9]+)",1), SUMPRODUCT(--(titleC = name), --dataC) ) ) ) ))其中第5~15行就是生成结果数据的实现过程。
在MAKEARRAY中，根据当前的行，列（r，c），首先取出当前的产品名称（第8行），当前的B列数据（第9行）；
然后使用正则表达式取出B列数据中的产品名称数组和数据数组（第10，11行）；
第15行进行条件求和。
提醒
由于正则函数刚推出不久，看起来AFE对它的支持不到位，所以上述公式如果写在AFE中，保存时会提示有问题。
解决方案是将这个公式写在公式编辑栏中。
图片
当然，可以在AFE中写完（这里更方便编辑公式），但是不保存，而是复制到编辑栏中。
图片
详情咨询客服（底部菜单-知识库-客服）
Excel+Power Query+Power Pivot+Power BI
Power Excel 知识库 按照以下方式进入知识库学习Excel函数 底部菜单：知识库->Excel函数
自定义函数 底部菜单：知识库->自定义函数
Excel如何做 底部菜单：知识库->Excel如何做
面授培训 底部菜单：培训学习->面授培训
Excel企业应用 底部菜单：企业应用
也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。
