求和公式有很多,但今天這個求和公式是蘭色見過的最牛公式,沒有之一。
如下圖所示,要求把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集) 。詳情點選下方連結