當前位置: 妍妍網 > 辦公

學Excel20年,這是我見過的最牛求和公式,沒有之一

2024-06-24辦公

求和公式有很多,但今天這個求和公式是蘭色見過的最牛公式,沒有之一。

如下圖所示,要求把C列做成右側的統計表,統計出每個食品的金額合計。

怎麽樣,看了是不是有點懵? 別說分類求和了,就是把數位和食品名稱提取出來就是一大難題。

嘿嘿,在新版函式加持下,讓你看看最牛求和公式是怎麽寫出來的。

在最新的Excel365版本和WPS內測版中,新增了正則函式。不同的是WPS只是一個REGEXP,而Excel則一分為三,一個提取,一個替換,最後一個是判斷。

正則函式有多牛?很簡單的公式就可以分別提取數位和文本。

提取之前需要把所有行用CONCAT連結起來。

=CONCAT(C3:C18)

然後用regexp函式提取

=REGEXP(CONCAT(C3:C18)," \D+ ")

為了能適用於groupby的第1個參數,還需要用Tocol轉換為一列

= TOCOL (REGEXP(CONCAT(C3:C18),"\D+"))

相比之下,Excel則更簡單,不需Tocol,直接就可以生成一列。

=REGEXEXTRACT(CONCAT(C3:C18),"\D+", 1 )

最後一個參數1是指提取所有符合條件的,省略只提取第1個

提取對應的數位,只需要把大寫 D 改為小寫 d 即可(Excel相同)

分離了食品名稱和金額,剩下的就交給Groupby函式了。最終公式為:

=GROUPBY( T OCOL(REGEXP(CONCAT(C3:C18),"\D+")) , TOCOL(--REGEXP(CONCAT(C3:C18),"\d+")) ,SUM,0)

蘭色說 :要想使用這兩個函式,office365需要加入預覽版計劃(無門檻都可以加入,方法百度),而WPS需要安裝內測版( 官方提供,不方便公開上傳,僅提供平台四合一學員和綠卡使用者在群中下載 ),WPS正式版可能在7月底會釋出這些新函式。

groupby函式用法已添加到蘭色四合一教程的第56集,而正則函式蘭色正在整理中,很快就會添加進來。想跟蘭色學習新函式的同學可以購買四合一大全套課程( 包括 Excel表格92個函式用法 119個使用技巧 透視表從入門到精通50集 圖表從入門到精通185集) 。詳情點選下方連結