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

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

展開全文

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

問題

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

要求也簡單：

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

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

分析

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

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

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

接下來只要將這個數(shù)組求和就好了。因為這個函數(shù)結果是文本，不要忘了轉換，

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

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

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

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

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

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

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

第三步多行合并可以使用VSTACK函數(shù)。但是我們不能直接使用，因為第一步處理完成后，各行數(shù)據(jù)中的產品名稱順序和個數(shù)未必相同。

這些處理就會比較啰嗦。

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

實現(xiàn)思路

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

接下來，我們增加一個標題行數(shù)組：
{“A", "B", "C", "D"}

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

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

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

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

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

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

我們就可以得到B列數(shù)據(jù)每一行對應于標題行數(shù)組中每一個元素的數(shù)據(jù)。

于是就得到了結果表。

實現(xiàn)

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

下面是實現(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)                )            )        )    ))