【原】Excel正則表達(dá)式處理數(shù)據(jù)的一個(gè)實(shí)例 - Excel函數(shù)式編程

ExcelEasy 2024-06-21 發(fā)布于北京

展開全文

前兩天我們剛介紹了正則表達(dá)式函數(shù)，今天就遇到了一個(gè)數(shù)據(jù)處理的實(shí)際問題。本來按照習(xí)慣使用了其他方法，后來想到可以用正則表達(dá)式函數(shù)，就又處理了一遍。今天分享一下這個(gè)過程和處理方法，供大家參考。

問題

原始數(shù)據(jù)是這樣的，

要求也簡單：

1. 提取每行的數(shù)據(jù)求和

2. 將數(shù)據(jù)轉(zhuǎn)換成下面的形式：

分析

第一個(gè)需求還是很簡單的，我們上次介紹的時(shí)候其實(shí)提到過。以第一行數(shù)據(jù)為例，

=REGEXEXTRACT(B2,"[0-9]+",1)

這個(gè)公式就可以提取其中的所有數(shù)據(jù)，

接下來只要將這個(gè)數(shù)組求和就好了。因?yàn)檫@個(gè)函數(shù)結(jié)果是文本，不要忘了轉(zhuǎn)換，

=SUM(--REGEXEXTRACT(B2,"[0-9]+",1))

但是第二個(gè)需求就復(fù)雜一些了。分析一下，需要做如下的工作：

首先需要提取每一行數(shù)據(jù)中的那些字母（產(chǎn)品名稱），還需要提取其中的數(shù)字。
然后需要匯總這一行中產(chǎn)品名稱相同（字母相同）的那些數(shù)據(jù)，比如B4中的數(shù)據(jù)就需要將所有的名稱為A的數(shù)據(jù)匯總到一起。
經(jīng)過前兩步，我們就已經(jīng)將每行數(shù)據(jù)處理成了兩個(gè)一行多列的數(shù)組，一個(gè)數(shù)組是產(chǎn)品名稱，另外一個(gè)數(shù)組是數(shù)據(jù)。比如對(duì)于B2單元格的數(shù)據(jù)來說，處理結(jié)果就是：
{"D", "C"}
{"69", "540"}
接下來，我們需要將所有的數(shù)據(jù)合并到一起。

第一步很簡單，實(shí)際上我們剛才已經(jīng)做了演示，可以將B列每個(gè)單元格的數(shù)據(jù)中的各產(chǎn)品數(shù)據(jù)取出。同樣的方法也可以將各個(gè)字母取出，只不過模式字符串需要修改一下：

=REGEXEXTRACT(B2,"[A-Z]",1)

不過這里需要提醒一下，由于提取的數(shù)據(jù)組成的數(shù)組是多行數(shù)組，跟我們需要的多列數(shù)組不一樣。（轉(zhuǎn)換很容易實(shí)現(xiàn)，轉(zhuǎn)置即可）

第二步本身也不復(fù)雜，有很多方法實(shí)現(xiàn)，比如可以使用GROUPBY函數(shù)實(shí)現(xiàn)，不過考慮到還有第三步，這個(gè)方法未必好。

第三步多行合并可以使用VSTACK函數(shù)。但是我們不能直接使用，因?yàn)榈谝徊教幚硗瓿珊?，各行?shù)據(jù)中的產(chǎn)品名稱順序和個(gè)數(shù)未必相同。

這些處理就會(huì)比較啰嗦。

所以我們就修改一下實(shí)現(xiàn)思路。

實(shí)現(xiàn)思路

第一步不變，還是使用正則函數(shù)提取名稱和數(shù)據(jù)數(shù)組。

接下來，我們?cè)黾右粋€(gè)標(biāo)題行數(shù)組：
{“A", "B", "C", "D"}

這里我們僅僅用了數(shù)據(jù)中出現(xiàn)的四個(gè)字母，其實(shí)可以用SEQUENCE函數(shù)生成所有的字母列表。這樣通用性更強(qiáng)。

然后呢，我們使用這個(gè)標(biāo)題行數(shù)組的每一個(gè)元素對(duì)上面B列每一個(gè)數(shù)據(jù)得到來的兩個(gè)數(shù)組進(jìn)行條件求和，比如，對(duì)于”A：

我們計(jì)算B2中產(chǎn)品名稱 = "A"的所有數(shù)據(jù)之和。

由于SUMIF不能用于數(shù)組，所以我們改用SUMPRODUCT：

=SUMPRODUCT(--(titleC = "A"), dataC)

其中，titleC是正則函數(shù)得到的產(chǎn)品名稱數(shù)組，dataC是得到的數(shù)據(jù)數(shù)組。

我們就可以得到B列數(shù)據(jù)每一行對(duì)應(yīng)于標(biāo)題行數(shù)組中每一個(gè)元素的數(shù)據(jù)。

于是就得到了結(jié)果表。

實(shí)現(xiàn)

上面的過程顯然可以使用MAKEARRAY函數(shù)輕松實(shí)現(xiàn)。

下面是實(shí)現(xiàn)的公式：

=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)                )            )        )    ))