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

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

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

一個是先按列排序。

=WRAPCOLS(A2:A26,5)

一個是先按行排序。

=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)容里面存在錯誤值或者空單元格，可以設(shè)置第二參數(shù)為3忽略。2個函數(shù)的用法一樣。

=TOCOL(A1:E5,3)

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

=SHEETSNAME(,1)

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

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

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

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

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

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

也可以用[一-龜]+。

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

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

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

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

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

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

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

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

=UNIQUE(A1:A18)

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

=UNIQUE(A1:B18)

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

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

那太簡單了，再嵌套個COUNTA統(tǒng)計個數(shù)就行。

=COUNTA(UNIQUE(B2:B18))

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

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

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

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

對月份降序。

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

語法說明：

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

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

=SORT(F2:G4,2,1)

9、憑證自動生成的最簡單公式

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

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

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

語法說明：

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

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

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

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

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

語法說明：

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

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

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

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

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

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

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

比如要將姓名合并過來。

=CHOOSECOLS(H2:L10,2)

語法：

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

=CHOOSECOLS(區(qū)域,第幾列)神奇的地方還在后面，這個函數(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)計的GROUPBY

統(tǒ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)在以其中一個演示。

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

統(tǒng)計每個項目對應(yīng)負(fù)責(zé)人的金額

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

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

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

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

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

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

ARRAYTOTEXT的作用就是按分隔符號合并文本。

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

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

=UNIQUE(A1:B72)

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

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

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

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

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

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

14、分表錄入，總表自動更新

格式相同的分表。

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

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

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

=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ū)域)因為分表要每天記錄新數(shù)據(jù)，可以將區(qū)域?qū)懘簏c(diǎn)，這樣就可以動態(tài)合并。

不過美中不足的是，總表就會出現(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)然，不用輔助列，一步到位也行，兩個區(qū)域都套VSTACK函數(shù)。

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

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

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

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

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

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

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

統(tǒng)計每個項目的金額

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

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

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

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

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

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

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

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

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

將項目、年月用&合并到一起再處理最簡單，ARRAYTOTEXT的作用就是按分隔符號合并文本。

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

當(dā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í)，這些行列總計、標(biāo)題之類的都可以去掉，區(qū)域從第2行開始，同時設(shè)置后面幾個參數(shù)實(shí)現(xiàn)。

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

最新評論