當前位置: 妍妍網 > 辦公

Excel新函式公式大全,熬夜整理出32個新函式用法+範例

2024-07-15辦公

32個新Excel 函式用法及範例

(按實用性排序)

第1名 Pivotby 函式

功能:用公式實作數據透視,支持多列多表

根據城市(A列)和產品(B列)透視銷量(C列)

=pivotby(A1:A10,B1:B10,C1:C10,Sum,3)

第2名 Groupby 函式

功能:用公式分類匯總, 支持多列多表

根據城市(A列)和產品(B列)匯總銷量(C列)

=Groupby(A1:B10 ,C1:C10,Sum ,3)

第3名 Regexextract函式

功能:用正規表式提取字元

提取字元中的所有整數

= Regexextract(A1,"\d+" )

第4名 Filter函式

功能:一對多篩選

篩選財務部(A列)所有行

=Filter(A1:F100,A1:A100="財務")

第5名 Vstack函式

功能:合並多個表格

合並1~12月表格

=Vstack('1月:12月'!A1:B100)

第6名 Xlookup 函式

功能:多條件尋找、從後向前尋找

根據部門(A列)和姓名(b列)查詢學歷(D列)

=Xlookup(「財務部"&"張三",A1:A10&B1:B10,D1:D10)

第7名 Textjoin 函式

功能:用分隔符連線多個值

把A1:A10的值用-連線在一起

=Textjoin("-",,A1:a10)

第8名 Textsplit函式

功能:根據 分隔符把一個字串拆分成多個值

把A1單元格中 張三-男-20 拆分到3個單元格中

=Textsplit(A1,"-")

第9名 Textbefore函式

功能:提取某個字元前的內容

提取省之前的省份名稱

=Textbefore(A1,"省")

第10名 TextAfter函式

功能:提取某個字元前的內容

提取市的詳細地址

=Textafter(A1,"市")

第11名 Unique函式

功能:提取不重復列表

提取A列不重復公司名稱

=Unique(A:A)

第12名 sort函式

功能:對表格進行排序

按表格的第3列降序排列

=SORT(A1:D10,3,-1)

第13名 sortBy函式

功能:多列排序

按表格的C列、D列升序排列

=SORTBY(A2:D11,C2:C11,1,D2:D11,1)

第14名 Tocol函式

功能:把多列值轉換成一列

把A1:F10區域轉換成一列

=Tocol(A1:F10)

第15名 ToRow函式

功能:把多列值轉換成一行

把A1:F10區域轉換成一行

=ToRow(A1:F10)

第16名 Hstack函式

功能:橫向合並多個表格

把A列、C列、F列合並成一個新表格

=Hstack(A1:A10,C1:C10,F1:F10)

第17名 ChooseCols函式

功能:從表格中提取部份列

提取表格的1,2,5列

=ChooseCols(A1:G10,1,2,5)

第18名 ChooseRows函式

功能:從表格中提取部份行

提取表格的1,2,5行

=ChooseRows(A1:G10,1,2,5)

第19名 Drop函式

功能:刪除表格的行或列

刪除表格的第1行

=Drop(a1:a100,1)

第20名 Take函式

功能:提取表格的前/行N行或N列

提取表格的前10行

=Take(a1:f100,10)

第21名 Arraytotext函式

功能:用逗號連線字元或數位

把A1:A10的值用逗號連線起來

=Arraytotext(a1:a10)

第22名 Concat函式

功能:連線字元或數位

把A1:A10的值用連線起來

=Concat(a1:a10)

第23名 Sequence函式

功能:生成序列

生成1~10之間的偶數

=Sequence(5,,2,2)

第24名 Regexreplace函式

功能: 用正規表式賛換字元

把A1中的整理都替換成100

= Regexreplace(A1,"\d+",100 )

第25名 Regextest函式

功能:用正規表式判斷是否包含

判斷A1中是否包含數位

= Regexextract(A1,"d\+" )

第26名 lambda函式

功能:自訂函式

定義一個兩個數相加的函式

=Lambda(x,y,x+y)

第27名 Reduce函式

功能:遍歷陣列內每個值,累計出運算結果

把A1:A10中的正數累加

=Reduce(0,a1:A10,Lambda(x,y,IF(y>0,x+y,x)))

第28名 Scan 函式

功能:和Reduce同樣的運算模式,區別是保存每一次運算結果

把A1:A10中的正數累加並返回每次累計結果

=Reduce(0,a1:A10,Lambda(x,y,IF(y>0,x+y,x)))

第29名 Map 函式

功能:對陣列的每個值進行處理,並返回每個值

把A1:A10中空值替換為大寫零

=Map(A1:A10,lambda(X,IF(X=0,"零",X)))

第30名 Let 函式

功能:定義名稱用來簡化公式

對Vlookup尋找結果進行判斷

=Let(x,vlookup(d1,a:b,2,0),if(x>10,」完成",「未完成"))

第31名 Byrow 函式

功能:對每一行值進行運算

返回區域每一行平均值

=Byrow(b2:f5,AVERAGE)

第32名 ByCol 函式

功能:對每一列值進行運算返回多個結果

返回區域每一列平均值

=B yCol (b 2 :f5 ,AVERAGE)

蘭色說 :今天整理的只是函式的基本用法,每個函式的擴充套件套用都值得單獨寫一篇教程,有的甚至寫十篇都不夠,如pivotby和 Regexextract。

另,在蘭色函式公式大全視訊教程中已更新了上述17個新函式的用法,買過課程的同學可以去下載範例並看視訊學習。 如果你也想跟蘭色學習更多函式用法,可以點選下方連結購買四全一大全套課程。( 函式大全158集+技巧大全119集+透視表50集套+圖表185集