|
與 30萬(wàn) 粉絲一起學(xué)Excel 
最近幾個(gè)月新函數(shù)很多，有個(gè)別是WPS表格特有的，大部分Office365也可以用，盧子全部整理到一起，一共16個(gè)，方便學(xué)習(xí)。所有公式都是直接輸入后，回車(chē)即可，不需要像舊版本的數(shù)組那樣按三鍵。 
1.將一列內(nèi)容轉(zhuǎn)換成多列（WRAPCOLS和WRAPROWS） 這種有2個(gè)新函數(shù)處理，語(yǔ)法一樣。 一個(gè)是先按列排序。 
一個(gè)是先按行排序。 
2.將多行多列轉(zhuǎn)換成一列或一行（TOCOL和TOROW） TOCOL是轉(zhuǎn)換成一列。
TOROW是轉(zhuǎn)換成一行，轉(zhuǎn)成行的不直觀，平常幾乎不用。 
假如多行多列內(nèi)容里面存在錯(cuò)誤值或者空單元格，可以設(shè)置第二參數(shù)為3忽略。2個(gè)函數(shù)的用法一樣。 
3.根據(jù)工作表名稱(chēng)生成目錄SHEETSNAME一個(gè)簡(jiǎn)單的函數(shù)即可，而Office需要很復(fù)雜的公式或者VBA才行。
4.正則表達(dá)式REGEXP，提取各種內(nèi)容兩個(gè)軟件都有正則，而Office是由3個(gè)函數(shù)組成。
將字符串的數(shù)字、文字分離 
[0-9]+代表連續(xù)的數(shù)字。 
^就是非的意思，[^0-9]+代表不是數(shù)字，也就是剩下的文字。 
也可以用[一-龜]+。
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)過(guò)了測(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è)單元格輸入公式，回車(chē)以后會(huì)自動(dòng)擴(kuò)展區(qū)域，并提取不重復(fù)。
除了可以針對(duì)一列，同時(shí)也可以針對(duì)多列，比如針對(duì)公司名稱(chēng)和軟件提取不重復(fù)。
7.不重復(fù)計(jì)數(shù)
UNIQUE可以提取不重復(fù)值，怎么進(jìn)行不重復(fù)計(jì)數(shù)？
那太簡(jiǎn)單了，再嵌套個(gè)COUNTA統(tǒng)計(jì)個(gè)數(shù)就行。
那如果是按公司名稱(chēng)、軟件2個(gè)條件不重復(fù)計(jì)數(shù)呢？
同樣簡(jiǎn)單，改下區(qū)域，再除以2就可以。
=COUNTA(UNIQUE(A2:B18))/2
8.用SORT函數(shù)對(duì)內(nèi)容自動(dòng)排序 對(duì)月份降序。
語(yǔ)法說(shuō)明：
=SORT(區(qū)域,對(duì)第幾列排序,-1為降序1為升序) 比如現(xiàn)在要對(duì)金額升序。
9.憑證自動(dòng)生成的最簡(jiǎn)單公式
以前盧子分享過(guò)憑證自動(dòng)生成的方法，不過(guò)實(shí)在太繁瑣了，詳見(jiàn)：憑證自動(dòng)生成，太難了？ 只需在一個(gè)單元格輸入公式，就自動(dòng)擴(kuò)展，簡(jiǎn)單到?jīng)]朋友。 =FILTER(C2:G11,B2:B11=D14)
語(yǔ)法說(shuō)明：
=FILTER(返回區(qū)域,條件區(qū)域=條件) 10.找不到對(duì)應(yīng)值，不用再嵌套IFERROR
正常情況下，用VLOOKUP或者LOOKUP查找的時(shí)候，找不到對(duì)應(yīng)值會(huì)顯示#N/A，一般情況下需要嵌套IFERROR。
而XLOOKUP即便是找不到對(duì)應(yīng)值，也不需要嵌套其他函數(shù)。
語(yǔ)法說(shuō)明：
=XLOOKUP(查找值,查找區(qū)域,返回區(qū)域,錯(cuò)誤值顯示值) 11.將查找到的所有對(duì)應(yīng)值去除重復(fù)，再合并在一個(gè)單元格
這個(gè)前陣子幫學(xué)員寫(xiě)了一個(gè)公式，套了又套，挺復(fù)雜的?，F(xiàn)在有了新函數(shù)，那一切就不一樣了。 =TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=F2)))
這個(gè)就相當(dāng)于將前面學(xué)的函數(shù)綜合起來(lái)，F(xiàn)ILTER就是將符合條件的篩選出來(lái)，再用UNIQUE去除重復(fù)值，最后用TEXTJOIN將內(nèi)容合并起來(lái)。 12.標(biāo)題順序不一樣的合并CHOOSECOLS 2個(gè)表的標(biāo)題順序不一樣，現(xiàn)在想合并在一起，除了一列一列復(fù)制粘貼，還有什么更好的方法？語(yǔ)法：返回區(qū)域第幾列的內(nèi)容。神奇的地方還在后面，這個(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ū)域是從左到右按順序，因此可以寫(xiě)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)的方式就不再說(shuō)明，跟普通的透視表差不多，可以互相取代。下面講新函數(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ù)。
=GROUPBY(F1:F7,G1:G7,ARRAYTOTEXT,3)
將多表合并后，匯總項(xiàng)目對(duì)應(yīng)的金額合并多表以前都是借助PQ，再用透視表統(tǒng)計(jì)。
現(xiàn)在可以借助VSTACK合并，再用GROUPBY統(tǒng)計(jì)。假如原來(lái)是每個(gè)項(xiàng)目一張工作表。
=GROUPBY(VSTACK(恩施市:華容區(qū)!A1:A72),VSTACK(恩施市:華容區(qū)!D1:D72),SUM,3)
這里再單獨(dú)演示VSTACK的作用，就是將多表的數(shù)據(jù)合并在一個(gè)表。不過(guò)直接合并的時(shí)候，有一個(gè)小缺陷，會(huì)出現(xiàn)一大堆無(wú)用的0。這種當(dāng)然也可以處理掉，不過(guò)不是這篇文章討論的內(nèi)容，以后再說(shuō)。=VSTACK(恩施市:華容區(qū)!A1:D72)
14.分表錄入，總表自動(dòng)更新 多表合并的方法非常多，有VBA、PQ等，今天盧子分享新函數(shù)VSTACK+FILTER。VSTACK函數(shù)語(yǔ)法跟SUM函數(shù)幾乎一樣，懂得SUM就可以。最原始的用法，就是分別引用每個(gè)分表的區(qū)域，再用逗號(hào)隔開(kāi)。
=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('開(kāi)始表格名稱(chēng):結(jié)束表格名稱(chēng)'!區(qū)域) 因?yàn)榉直硪刻煊涗浶聰?shù)據(jù)，可以將區(qū)域?qū)懘簏c(diǎn)，這樣就可以動(dòng)態(tài)合并。不過(guò)美中不足的是，總表就會(huì)出現(xiàn)很多0。=VSTACK('01.現(xiàn)金:04.支付寶'!A2:E120) 要去掉這些0，其實(shí)也不難，借助FILTER函數(shù)，判斷E列不等于0即可。先來(lái)看輔助列方法。=FILTER(A2:E999,E2:E999<>0) =FILTER(返回區(qū)域,條件區(qū)域=條件) 當(dāng)然，不用輔助列，一步到位也行，兩個(gè)區(qū)域都套VSTACK函數(shù)。這里有一個(gè)很容易出錯(cuò)的地方要特別注意，返回區(qū)域是A2:E120，條件區(qū)域是E2:E120，千萬(wàn)別寫(xiě)一樣。
=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估計(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ū)域是從左到右按順序，因此可以寫(xiě)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)題的效果感覺(jué)不太好，3去掉就是不帶標(biāo)題，看起來(lái)更簡(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)目、年月分開(kāi)變成2列，就需要嵌套HSTACK函數(shù)。=PIVOTBY(HSTACK(A1:A11,C1:C11),,B1:B11,ARRAYTOTEXT,3)
=PIVOTBY(A1:A11,C1:C11,B1:B11,ARRAYTOTEXT,3)
其實(shí)，這些行列總計(jì)、標(biāo)題之類(lèi)的都可以去掉，區(qū)域從第2行開(kāi)始，同時(shí)設(shè)置后面幾個(gè)參數(shù)實(shí)現(xiàn)。=PIVOTBY(A2:A11,C2:C11,B2:B11,ARRAYTOTEXT,0,0,,0,,,0)
一次報(bào)名成為VIP會(huì)員，所有課程永久免費(fèi)學(xué)，永久答疑，僅需 1500 元，待你加入。 報(bào)名后加盧子微信chenxilu2019，發(fā)送報(bào)名截圖邀請(qǐng)進(jìn)群。 推薦：分表錄入數(shù)據(jù)，總表自動(dòng)更新，新函數(shù)VSTACK+FILTER真好用！ 上篇：VLOOKUP函數(shù)跟這個(gè)巧妙的輔助列，簡(jiǎn)直就是絕配，狂贊！
請(qǐng)把「Excel不加班」推薦給你的朋友
|
