|
前段時間微軟一口氣測試了14個新函數(shù)，其中有些函數(shù)極其重要（本文第3小節(jié)）,，較大程度改變了函數(shù)核心數(shù)據(jù)結(jié)構數(shù)組的薄弱性,。函數(shù)獲取數(shù)據(jù)的能力提高了,，解決復雜數(shù)據(jù)查詢問題的能力更是指數(shù)級提升，今天就給大家稍微展開聊一下,。 這次測試的14個新函數(shù)里有三個文本函數(shù)：這仨貨,，特別是TextSplit對Excel函數(shù)來說還是很重要的，畢竟字符串拆分是極其常見的基礎問題,。這個函數(shù)可以將字符串按一到多個分隔符拆成一維或二維的內(nèi)存數(shù)組,。但這函數(shù)本來20年前就該有，微軟硬是拖拖拉拉磨蹭了7個版本才更新——這就好比男足20年后好不容易贏了越南,，你那時問我什么感受？男足贏越南當然是重要的,，但要問我感受嘛,，那我只能說：草,，是一種植物,。
TEXTSPLIT有5個參數(shù),。前倆參數(shù)是必須的,，第1個參數(shù)是源字符串,，第2個參數(shù)是列分隔符,。其它3個參數(shù)依次是行分隔符,、是否忽略空值以及指定缺失值的填充值。
如上圖所示，需要將A列的字符串按分隔符'-'拆分為B:E的數(shù)據(jù),。
當分隔符有多個時,，第2和3參數(shù)還可以使用數(shù)組形式。
如下圖所示的A列數(shù)據(jù),，分隔符既有-又有/，可以使用以下公式拆分,。還可以將一個復雜的字符串按分隔符拆成二維數(shù)組,。
如下圖所示,，A2單元格是一個混合字符串，有多個記錄構成,，每個記錄的結(jié)構均為人名-性別-科目-成績,，不同記錄之間用分號串聯(lián),。
使用以下函數(shù)可以將A2拆成為二維數(shù)組：
=TEXTSPLIT(A2,'-',';',,'數(shù)據(jù)缺失')
對于源字符串,，公式先用,；拆分為多行,，再用-拆分為多列,，對于缺失值,，例如那個既美麗又知性的姑娘小白沒有成績,，則用第5參數(shù)的指定值'數(shù)據(jù)缺失'填充,。
顧名思義，分別獲取指定分隔符前和后的數(shù)據(jù),，意義不大,，沒啥好說的,。 數(shù)組結(jié)構轉(zhuǎn)換函數(shù) Excel函數(shù)一直缺失結(jié)構轉(zhuǎn)換類函數(shù),，例如，二維數(shù)組轉(zhuǎn)一維,，一維數(shù)據(jù)轉(zhuǎn)二維；之前需要使用OFFSET等函數(shù)操作,，過程復雜效率低下不說,，涉及到內(nèi)存數(shù)組維度轉(zhuǎn)換時,，在不同版本的穩(wěn)定性也很糟糕,。TOROWS：將二維數(shù)組轉(zhuǎn)換為一行TOCOL：將二維數(shù)組轉(zhuǎn)換為一列WRAPCOLS：將一維數(shù)組按列轉(zhuǎn)換為二維數(shù)組WRAPROWS：將一維數(shù)組按行轉(zhuǎn)換為二維數(shù)組有一個經(jīng)典的數(shù)據(jù)結(jié)構轉(zhuǎn)換問題,，將多列數(shù)據(jù)轉(zhuǎn)換為一列,。這問題的舊的常規(guī)解法可以查閱【往期教程】，而現(xiàn)在用新函數(shù)一個函數(shù)就可以了：
第1個參數(shù)是數(shù)據(jù)源,，第2個參數(shù)表示忽略空值，第3個參數(shù)表示按列掃描數(shù)據(jù),。這個問題延伸一下,，就有了多列數(shù)據(jù)轉(zhuǎn)N列的情況。
如下圖所示,，A:D是數(shù)據(jù)源，有4列N行,，需要轉(zhuǎn)換為F:G所示的2列N行,。=WRAPROWS(TOCOL(A2:D6,1,0),2
公式先用TOCOL函數(shù)將4列數(shù)據(jù)轉(zhuǎn)換為1列的一維數(shù)組，再用WRAPROW函數(shù)將一維數(shù)組按每2個元素為一行轉(zhuǎn)換為二維數(shù)組,。
打個響指,，以上無聊的小東西總算說完了,，這節(jié)說下這波新函數(shù)里最重要的幾個,?？次倚⊙凵?，這很有趣 ,。2年前微軟剛推出LAMBDA的時候,，國外有大佬說Excel函數(shù)正式進化為編程語言了。那時看還是妄言,，甚至是笑話。畢竟一個編程語言,，不但要有分支和循環(huán)等語句,，還得有數(shù)據(jù)結(jié)構做支撐,，典型如數(shù)組和字典等,。而Excel函數(shù)有什么？那時什么都沒有,。可能有朋友說有數(shù)組——但函數(shù)的數(shù)組太弱,，連基礎的動態(tài)讀寫及調(diào)整大小的能力都沒有，完全不足以支撐循環(huán)函數(shù)的廣泛應用,。微軟后來不斷測試推出各種循環(huán)作用的函數(shù)：這使Excel函數(shù)日益強大的同時，也進一步凸顯它唯一的數(shù)據(jù)結(jié)構——數(shù)組的極度薄弱性,，
攤手,，而這一波更新終于明顯增強了函數(shù)數(shù)組的能力,。擁有這類函數(shù),，搭配變量功能的LET,，循環(huán)語句的REDUCT/MAP/SCAN等，以及自定義過程的LAMBDA,，再說Excel加速向編程語言進化就一點都不為過了,。當你熟練掌握了Excel函數(shù),，后期再學習其它編程語言,，完全可以事半功倍,，少掉很多頭發(fā)——嗯,，是的,，畢竟你已經(jīng)掉過了。1,，VSTACK：將多個數(shù)組縱向合并,。
2,，HSTACK：將多個數(shù)組橫向合并,。
3,，TAKE：從數(shù)組的頭或尾截取指定行/列數(shù)
4,，DROP：從數(shù)組的頭或尾刪除指定行/列數(shù)
5,，CHOOSEROWS：從二維數(shù)組中選取指定的行數(shù)
6,，CHOOSECOLS：從二維數(shù)組中選取指定的列數(shù)
7,，EXPAND：將數(shù)組擴展指定行/列,，并填入指定值
照例舉幾個簡單的小栗子,。 ▎選取指定行列 有一張數(shù)據(jù)表,，如下圖所示,。 假設你現(xiàn)在只需要選取班級、姓名和英語三個字段的數(shù)據(jù),，參考函數(shù)如下：
=CHOOSECOLS(A1:E7,{1,2,5})
第1參數(shù)是數(shù)據(jù)源，第2參數(shù)表示選取的列的序列號,。如果事前并不知道班級/姓名和英語的序列號,，可以將公式修改為以下,，使用MATCH函數(shù)定位相關字段的位置,。
=CHOOSECOLS(A1:E7,MATCH({'班級','姓名','英語'},A1:E1,0))
▎合并多個子數(shù)組
將多個子數(shù)組合并是很常見的問題，以前的解決方式需要借助LOOKUP函數(shù)或者字符串結(jié)構,，復雜低效不靈活，這可以參考往期推文【看暈了,，用Excel函數(shù)將多個一維數(shù)組合并成一維數(shù)組】
如下圖所示，需要計算A列和C列兩列人名不重復的名單,。
代碼看不全可以左右拖動...▼ =UNIQUE(VSTACK({'不重復名單'},A2:A7,C2:C8))
又或者將多個工作表的數(shù)據(jù)合并?? 如上圖所示,，有兩張工作表,，名稱分別是一班和二班,，需要將兩表合并成總表↓
舊函數(shù)的解法極其繁瑣，感興趣的朋友可以參考【往期推文】,，而新函數(shù)在總表的A1單元格輸入以下公式即可：
=VSTACK(一班!A1:C7,二班!A2:C7)
VSTACK可以將多個數(shù)組按垂直方向合并成一個數(shù)組,。和VLOOKUP一樣,，這里的V是vertical,，表示垂直的,、縱向的,。與之相對的還有一個HSTACK,，可以將多個數(shù)組橫向合并成一個數(shù)組,。=HSTACK(一班!A2:C7,二班!A2:C7)
以上都是比較基礎實在的用法,，再給大家舉一個數(shù)組動態(tài)擴展函數(shù)和循環(huán)函數(shù)搭配使用的簡單小案例,。如下圖所示,，B列是開始的發(fā)票號,，C列是結(jié)束的發(fā)票號,，需要展開如E列所示,。比如12340-12346，展開后是12340/12341...12346,。代碼看不全可以左右拖動...▼ =REDUCE({'發(fā)票展開'},B2:B4,LAMBDA(_a,_b,LET(_y,_b+SEQUENCE(VLOOKUP(_b,B:C,2,0)-_b+1)-1,VSTACK(_a,_y))))
REDUCT是累加循環(huán)函數(shù),，源值是常量數(shù)組{'發(fā)票展開'}，遍歷范圍是B2:B4,，計算過程是第3參數(shù)的LAMBDA,。第2行代碼利用SEQUENCE函數(shù)生成遞增序列，再和初始發(fā)票號相加,，返回一個內(nèi)存數(shù)組：
12340,12341,12342,12343,12345,12346
第3行代碼使用VSTACK函數(shù),，將第2行代碼返回的內(nèi)存數(shù)組和REDUCT的源數(shù)組縱向合并，作為新的源數(shù)組,。
如此迭代直至遍歷完成B2:B4區(qū)域的最后一個元素,。
將數(shù)組動態(tài)擴展函數(shù)和REDUCT搭配使用,，工作表函數(shù)就可以有機會像Power Query M函數(shù)的List.Accumulate似的,，變成萬能套路,；如果你擅長M函數(shù),，那么對于該套路的強大應該并不陌生 再舉個小栗子。
如上圖所示,，A列是混合字符串,，需要從中統(tǒng)計人名及總分,。
這樣的問題，舊的常規(guī)的函數(shù)解法是先用輔助列把數(shù)據(jù)拆分整理,，然后再分類匯總,。而擁有這波新函數(shù),，我們就可以實現(xiàn)編程式函數(shù),，一條公式直接搞定：
=LET(_lst,DROP(REDUCE({''},A2:A11,LAMBDA(_d,_s,VSTACK(_d,INDEX(TEXTSPLIT(_s,{'-',';'}),{1,4})))),1),_nm,CHOOSECOLS(_lst,1),_score,CHOOSECOLS(_lst,2)*1,_unm,UNIQUE(_nm),_rst,MAP(_unm,LAMBDA(_r,SUM(FILTER(_score,_nm=_r)))),VSTACK({'姓名','總分'},HSTACK(_unm,_rst)))
……
正式擁有這些新函數(shù)后,，Excel函數(shù)就可以稱之為編程語言了嗎,？假笑來說，仁者見仁智者見智,，坦白而言，那當然還差…咳,，這事你開心就好,，咱們誰和誰啊，誰都不認識誰不是o(?Д?)っ啥,！ 留一個話題吧,，你期待微軟下一步推出測試哪些新函數(shù)呢？數(shù)組動態(tài)讀寫,？跨工作簿讀取？分組自定義聚合Group,？正則表達式Reg,？還是……不妨留言說說,。
|
