最近幾個(gè)月新函數(shù)很多，有個(gè)別是WPS表格特有的，大部分Office365也可以用，全部整理到一起，一共16個(gè)，方便學(xué)習(xí)。所有公式都是直接輸入后，回車即可，不需要像舊版本的數(shù)組那樣按三鍵。

1、將一列內(nèi)容轉(zhuǎn)換成多列（WRAPCOLS和WRAPROWS）

這種有2個(gè)新函數(shù)處理，語法一樣。

一個(gè)是先按列排序。

=WRAPCOLS(A2:A26,5)

一個(gè)是先按行排序。

=WRAPROWS(A2:A26,5)

2、將多行多列轉(zhuǎn)換成一列或一行（TOCOL和TOROW）

TOCOL是轉(zhuǎn)換成一列。

=TOCOL(A1:E5)

TOROW是轉(zhuǎn)換成一行，轉(zhuǎn)成行的不直觀，平常幾乎不用。

=TOROW(A1:E5)

假如多行多列內(nèi)容里面存在錯(cuò)誤值或者空單元格，可以設(shè)置第二參數(shù)為3忽略。2個(gè)函數(shù)的用法一樣。

=TOCOL(A1:E5,3)

3、根據(jù)工作表名稱生成目錄SHEETSNAME一個(gè)簡(jiǎn)單的函數(shù)即可，而Office需要很復(fù)雜的公式或者VBA才行。

=SHEETSNAME(,1)

4、正則表達(dá)式REGEXP，提取各種內(nèi)容兩個(gè)軟件都有正則，而Office是由3個(gè)函數(shù)組成。

將字符串的數(shù)字、文字分離

[0-9]+代表連續(xù)的數(shù)字。

=REGEXP(A2,"[0-9]+")

^就是非的意思，[^0-9]+代表不是數(shù)字，也就是剩下的文字。

=REGEXP(A2,"[^0-9]+")

也可以用[一-龜]+。

=REGEXP(A2,"[一-龜]+")

5、將同一個(gè)單元格的內(nèi)容拆分到多個(gè)單元格TEXTSPLIT

=TEXTSPLIT(A1," ",CHAR(10))

有的時(shí)候會(huì)出現(xiàn)輸入不規(guī)范，也就是同時(shí)存在不同分隔符號(hào)，比如現(xiàn)在有空格和橫桿存在。

正常人的思維，用查找替換，將符號(hào)統(tǒng)一。經(jīng)過了測(cè)試，發(fā)現(xiàn)這個(gè)函數(shù)，即使不統(tǒng)一也行，分隔符號(hào)可以同時(shí)輸入多個(gè)符號(hào)。{" ","-"}，也就是{"符號(hào)1","符號(hào)2"}。

=TEXTSPLIT(A1,{" ","-"},CHAR(10))

6、用UNIQUE函數(shù)就可以提取不重復(fù)

只需在一個(gè)單元格輸入公式，回車以后會(huì)自動(dòng)擴(kuò)展區(qū)域，并提取不重復(fù)。

=UNIQUE(A1:A18)

除了可以針對(duì)一列，同時(shí)也可以針對(duì)多列，比如針對(duì)公司名稱和軟件提取不重復(fù)。

=UNIQUE(A1:B18)

7、不重復(fù)計(jì)數(shù)

UNIQUE可以提取不重復(fù)值，怎么進(jìn)行不重復(fù)計(jì)數(shù)？

那太簡(jiǎn)單了，再嵌套個(gè)COUNTA統(tǒng)計(jì)個(gè)數(shù)就行。

=COUNTA(UNIQUE(B2:B18))

那如果是按公司名稱、軟件2個(gè)條件不重復(fù)計(jì)數(shù)呢？

同樣簡(jiǎn)單，改下區(qū)域，再除以2就可以。

=COUNTA(UNIQUE(A2:B18))/2

8、用SORT函數(shù)對(duì)內(nèi)容自動(dòng)排序

對(duì)月份降序。

=SORT(F2:G4,1,-1)

語法說明：

=SORT(區(qū)域,對(duì)第幾列排序,-1為降序1為升序)

比如現(xiàn)在要對(duì)金額升序。

=SORT(F2:G4,2,1)

9、憑證自動(dòng)生成的最簡(jiǎn)單公式

以前小編分享過憑證自動(dòng)生成的方法，不過實(shí)在太繁瑣了，詳見：憑證自動(dòng)生成，太難了？

只需在一個(gè)單元格輸入公式，就自動(dòng)擴(kuò)展，簡(jiǎn)單到?jīng)]朋友。

=FILTER(C2:G11,B2:B11=D14)

語法說明：

=FILTER(返回區(qū)域,條件區(qū)域=條件)

10、找不到對(duì)應(yīng)值，不用再嵌套IFERROR

正常情況下，用VLOOKUP或者LOOKUP查找的時(shí)候，找不到對(duì)應(yīng)值會(huì)顯示#N/A，一般情況下需要嵌套IFERROR。

而XLOOKUP即便是找不到對(duì)應(yīng)值，也不需要嵌套其他函數(shù)。

=XLOOKUP(E2,A:A,B:B,"")

語法說明：

=XLOOKUP(查找值,查找區(qū)域,返回區(qū)域,錯(cuò)誤值顯示值)

11、將查找到的所有對(duì)應(yīng)值去除重復(fù)，再合并在一個(gè)單元格

這個(gè)前陣子幫學(xué)員寫了一個(gè)公式，套了又套，挺復(fù)雜的?，F(xiàn)在有了新函數(shù)，那一切就不一樣了。

=TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=F2)))

這個(gè)就相當(dāng)于將前面學(xué)的函數(shù)綜合起來，F(xiàn)ILTER就是將符合條件的篩選出來，再用UNIQUE去除重復(fù)值，最后用TEXTJOIN將內(nèi)容合并起來。

12、標(biāo)題順序不一樣的合并CHOOSECOLS

2個(gè)表的標(biāo)題順序不一樣，現(xiàn)在想合并在一起，除了一列一列復(fù)制粘貼，還有什么更好的方法？

比如要將姓名合并過來。

=CHOOSECOLS(H2:L10,2)

語法：

返回區(qū)域第幾列的內(nèi)容。

=CHOOSECOLS(區(qū)域,第幾列)神奇的地方還在后面，這個(gè)函數(shù)如果要返回多列也可以，比如返回第2、3、1列。

=CHOOSECOLS(H2:L10,2,3,1)

前面提到可以用MATCH判斷內(nèi)容分別在第幾列。

=CHOOSECOLS(H2:L10,MATCH(A1:E1,H1:L1,0))

13、能實(shí)現(xiàn)透 視表各種統(tǒng)計(jì)的GROUPBY

統(tǒng)計(jì)每個(gè)項(xiàng)目的金額行區(qū)域A1:A72，值區(qū)域D1:D72，匯總方式SUM（也就是求和），3代表包含標(biāo)題。

=GROUPBY(A1:A72,D1:D72,SUM,3)

匯總方式有非常多，最大值MAX，最小值MIN，平均值A(chǔ)VERAGE等等，現(xiàn)在以其中一個(gè)演示。

=GROUPBY(A1:A72,D1:D72,AVERAGE,3)

統(tǒng)計(jì)每個(gè)項(xiàng)目對(duì)應(yīng)負(fù)責(zé)人的金額

行區(qū)域是從左到右按順序，因此可以寫A1:B72。

=GROUPBY(A1:B72,D1:D72,SUM,3)

其他情況下，都需要結(jié)合HSATCK函數(shù)才行，比如求每個(gè)負(fù)責(zé)人對(duì)應(yīng)項(xiàng)目的金額。

=GROUPBY(HSTACK(B1:B72,A1:A72),D1:D72,SUM,3)

其他傳統(tǒng)的方式就不再說明，跟普通的透 視表差不多，可以互相取代。下面講新函數(shù)優(yōu)勢(shì)的地方。

根據(jù)項(xiàng)目合并負(fù)責(zé)人（數(shù)據(jù)源已去重復(fù)）透 視表的強(qiáng)項(xiàng)是處理數(shù)據(jù)，而處理文本并不擅長(zhǎng)。而新函數(shù)不管數(shù)據(jù)還是文本，都可以處理。

ARRAYTOTEXT的作用就是按分隔符號(hào)合并文本。

=GROUPBY(A1:A7,B1:B7,ARRAYTOTEXT,3)

如果數(shù)據(jù)源有重復(fù)值，直接處理超級(jí)麻煩，建議輔助列用UNIQUE函數(shù)去重復(fù)。

=UNIQUE(A1:B72)

再引用輔助列的區(qū)域。

=GROUPBY(F1:F7,G1:G7,ARRAYTOTEXT,3)

將多表合并后，匯總項(xiàng)目對(duì)應(yīng)的金額合并多表以前都是借助PQ，再用透 視表統(tǒng)計(jì)?，F(xiàn)在可以借助VSTACK合并，再用GROUPBY統(tǒng)計(jì)。假如原來是每個(gè)項(xiàng)目一張工作表。

使用公式：=GROUPBY(VSTACK(恩施市:華容區(qū)!A1:A72),VSTACK(恩施市:華容區(qū)!D1:D72),SUM,3)

這里再單獨(dú)演示VSTACK的作用，就是將多表的數(shù)據(jù)合并在一個(gè)表。不過直接合并的時(shí)候，有一個(gè)小缺陷，會(huì)出現(xiàn)一大堆無用的0。

這種當(dāng)然也可以處理掉，不過不是這篇文章討論的內(nèi)容，以后再說。=VSTACK(恩施市:華容區(qū)!A1:D72)

14、分表錄入，總表自動(dòng)更新

格式相同的分表。

多表合并的方法非常多，有VBA、PQ等，今天小編分享新函數(shù)VSTACK+FILTER。

VSTACK函數(shù)語法跟SUM函數(shù)幾乎一樣，懂得SUM就可以。

最原始的用法，就是分別引用每個(gè)分表的區(qū)域，再用逗號(hào)隔開。

=VSTACK('01.現(xiàn)金'!A2:E11,'02.銀行'!A2:E12,'03.微信'!A2:E11,'04.支付寶'!A2:E10)

語法：

=VSTACK(區(qū)域1,區(qū)域2,區(qū)域3,區(qū)域4)使用最多的還是下面這種。

=VSTACK('01.現(xiàn)金:04.支付寶'!A2:E12)

語法：

=VSTACK('開始表格名稱:結(jié)束表格名稱'!區(qū)域)因?yàn)榉直硪刻煊涗浶聰?shù)據(jù)，可以將區(qū)域?qū)懘簏c(diǎn)，這樣就可以動(dòng)態(tài)合并。

不過美中不足的是，總表就會(huì)出現(xiàn)很多0。

=VSTACK('01.現(xiàn)金:04.支付寶'!A2:E120)

要去掉這些0，其實(shí)也不難，借助FILTER函數(shù)，判斷E列不等于0即可。先來看輔助列方法。

=FILTER(A2:E999,E2:E999<>0)

語法：

=FILTER(返回區(qū)域,條件區(qū)域=條件)

當(dāng)然，不用輔助列，一步到位也行，兩個(gè)區(qū)域都套VSTACK函數(shù)。

這里有一個(gè)很容易出錯(cuò)的地方要特別注意，返回區(qū)域是A2:E120，條件區(qū)域是E2:E120，千萬別寫一樣。

=FILTER(VSTACK('01.現(xiàn)金:04.支付寶'!A2:E120),VSTACK('01.現(xiàn)金:04.支付寶'!E2:E120)<>0)

假如在最后一個(gè)表輸入一行新內(nèi)容。

在總表就能看到，相當(dāng)于自動(dòng)合并，實(shí)現(xiàn)一勞永逸。

15.能實(shí)現(xiàn)透 視表各種統(tǒng)計(jì)的PIVOTBYPIVOTBY

PIVOTBY估計(jì)是參數(shù)最多的函數(shù)，共計(jì)11個(gè)參數(shù)，今天只講前5個(gè)。這里多了一個(gè)列區(qū)域。

=PIVOTBY(行區(qū)域,列區(qū)域,值區(qū)域,匯總方式,是否包含標(biāo)題)

統(tǒng)計(jì)每個(gè)項(xiàng)目的金額

行區(qū)域A1:A11，列區(qū)域不需要就用逗號(hào)占位，值區(qū)域D1:D11，匯總方式SUM（也就是求和），3代表包含標(biāo)題。

=PIVOTBY(A1:A11,,D1:D11,SUM,3)

統(tǒng)計(jì)每個(gè)項(xiàng)目對(duì)應(yīng)負(fù)責(zé)人的金額行區(qū)域是從左到右按順序，因此可以寫A1:B11。

=PIVOTBY(A1:B11,,D1:D11,SUM,3)

其實(shí)，還有一種效果，項(xiàng)目在行區(qū)域，負(fù)責(zé)人在列區(qū)域，金額在值區(qū)域。

=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM,3)

這種帶標(biāo)題的效果感覺不太好，3去掉就是不帶標(biāo)題，看起來更簡(jiǎn)潔。

=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM)

根據(jù)項(xiàng)目、年月合并負(fù)責(zé)人

將項(xiàng)目、年月用&合并到一起再處理最簡(jiǎn)單，ARRAYTOTEXT的作用就是按分隔符號(hào)合并文本。

=PIVOTBY(A1:A11&C1:C11,,B1:B11,ARRAYTOTEXT,3)

當(dāng)然也可以將項(xiàng)目、年月分開變成2列，就需要嵌套HSTACK函數(shù)。

=PIVOTBY(HSTACK(A1:A11,C1:C11),,B1:B11,ARRAYTOTEXT,3)

還有一種就是年月放在列區(qū)域。

=PIVOTBY(A1:A11,C1:C11,B1:B11,ARRAYTOTEXT,3)

其實(shí)，這些行列總計(jì)、標(biāo)題之類的都可以去掉，區(qū)域從第2行開始，同時(shí)設(shè)置后面幾個(gè)參數(shù)實(shí)現(xiàn)。

=PIVOTBY(A2:A11,C2:C11,B2:B11,ARRAYTOTEXT,0,0,,0,,,0)