|
文/盧子 前幾天，盧子發過一篇文章，跟AI進行PK，輕松碾壓。詳見文章：18 年 Excel 老將出馬，僅 3 個回合，就把 AI 狠狠的按在地上摩擦！ 那是不是證明AI沒啥用？不是的。AI的專業知識不如我，但是整理資料卻強得離譜。比如我要將WPS表格的32個最新函數整理出來，手工要花費2天時間，而AI僅需2分鐘，效率飆升！現在以騰訊元寶為例，選擇DeepSeek模式，深度思考，聯網。AI必須在聯網狀態下才懂得新函數。整理出來的32個新函數，再進行審核、加工，驗證公式的準確性，以及對重點函數提供更詳細的案例。重點學習我提供案例的函數，其他函數知道存在就行。1.TEXTSPLIT，功能：按分隔符拆分文本為多列或多行。示例：=TEXTSPLIT(A1, "-", ",")輸入A1為“北京-上海,廣州-深圳”，輸出為2行2列矩陣。將同一個單元格的內容拆分到多個單元格TEXTSPLIT
=TEXTSPLIT(A1," ",CHAR(10)) 
示例：=TOCOL(A1:C3)，將3x3區域轉為9行單列。TOCOL是轉換成一列。 示例：=TOROW(A1:A5)，將A1:A5垂直列轉為水平單行。TOROW是轉換成一行，轉成行的不直觀，平常幾乎不用。 示例：=VSTACK(A1:B3, D1:E3)，合并為6行2列數據。VSTACK函數語法跟SUM函數幾乎一樣，懂得SUM就可以。最原始的用法，就是分別引用每個分表的區域，再用逗號隔開。
=VSTACK('01.現金'!A2:E11,'02.銀行'!A2:E12,'03.微信'!A2:E11,'04.支付寶'!A2:E10) =VSTACK('01.現金:04.支付寶'!A2:E12) =VSTACK('開始表格名稱:結束表格名稱'!區域) 因為分表要每天記錄新數據，可以將區域寫大點，這樣就可以動態合并。不過美中不足的是，總表就會出現很多0。=VSTACK('01.現金:04.支付寶'!A2:E120) 要去掉這些0，其實也不難，借助FILTER函數，判斷E列不等于0即可。先來看輔助列方法。=FILTER(A2:E999,E2:E999<>0) 當然，不用輔助列，一步到位也行，兩個區域都套VSTACK函數。這里有一個很容易出錯的地方要特別注意，返回區域是A2:E120，條件區域是E2:E120，千萬別寫一樣。
=FILTER(VSTACK('01.現金:04.支付寶'!A2:E120),VSTACK('01.現金:04.支付寶'!E2:E120)<>0) 示例：=HSTACK(A1:A5, C1:C5)，合并為5行2列數據。示例：=LET(x, A1 * 0.3, y, B1 * 0.7, x+y)，計算加權和。7.XLOOKUP，功能：增強版查找函數，支持反向/多條件匹配。示例：=XLOOKUP(F2, A:A, D:D, "未找到", 0)，精確查找F2并返回D列值。正常情況下，用VLOOKUP或者LOOKUP查找的時候，找不到對應值會顯示#N/A，一般情況下需要嵌套IFERROR。 而XLOOKUP即便是找不到對應值，也不需要嵌套其他函數。 語法說明： =XLOOKUP(查找值,查找區域,返回區域,錯誤值顯示值)
8.XMATCH，功能：返回查找值的行號位置。示例：=XMATCH("蘋果", B1:B20)，返回“蘋果”在B列中的行號。示例：=UNIQUE(C2:C100)，提取C列不重復的客戶名單。只需在一個單元格輸入公式，回車以后會自動擴展區域，并提取不重復。 除了可以針對一列，同時也可以針對多列，比如針對公司名稱和軟件提取不重復。 示例：=FILTER(A2:E100, (D2:D100>5000)*(C2:C100="華東"))，篩選銷售額>5000且地區為華東的數據。憑證自動生成的最簡單公式，只需在一個單元格輸入公式，就自動擴展，簡單到沒朋友。=FILTER(C2:G11,B2:B11=D14) 語法說明： 示例：=SORTBY(A2:C10, B2:B10, -1)，按B列降序排列數據。示例：=SEQUENCE(5,3,10,2)，生成5行3列、從10開始步長2的序列示例：=RANDARRAY(3,4,1,100,1)，生成3行4列的1~100隨機整數。14.ARRAYTOTEXT，功能：將數組轉為文本。示例：=ARRAYTOTEXT(A1:C3, 1)，用逗號連接數組為字符串。示例：=DROP(A1:E10,3,1)，刪除前3行和第1列。示例：=TAKE(A1:E10,5,3)，提取前5行和前3列。示例：=REDUCE(0, A1:A10, LAMBDA(a,b, a+b))，累加A1:A10的值（需結合LAMBDA函數）18.TEXTAFTER，功能：提取分隔符后的內容。示例：=TEXTAFTER(A2,"-",2)，從“墾哥-市場部-007”提取第二個“-”后的“007”示例：=REGEXP(B3,"\d+")，提取文本中所有連續數字。將字符串的數字、文字分離 [0-9]+代表連續的數字。 ^就是非的意思，[^0-9]+代表不是數字，也就是剩下的文字。 也可以用[一-龜]+。 20、21、22這3個函數AI提供錯了，是Office365才有的函數。20.REGEXPEXTRACT，功能：提取匹配正則表達式的子字符串。示例：=REGEXPEXTRACT(A1,"\d{4}")，提取4位數字。21.REGEXPMATCH，功能：判斷文本是否匹配正則表達式。示例：=REGEXPMATCH(A2,"Excel",1)，判斷A2是否包含“Excel”（不區分大小寫）。22.REGEXPREPLACE，功能：替換匹配正則的文本。示例：=REGEXPREPLACE(A2,"$.*$",2)，刪除括號內的內容。示例：=WPSAI(A2,"逗號換成強制換行符")，將逗號分隔的文本轉為換行顯示。請看截圖，這是WPS最新版本的AI函數，第2參數說出需求就可以。=WPSAI(A2,"提取最后一個_之后的內容") =WPSAI(A2,"將里面的科目編碼提取出來") 說明需求的時候盡量詳細，就把AI當成一個外行人，讓外行人能聽懂你的需求，你就成功了。=WPSAI(A2,"從第7位開始提取8位，并將數字轉換成日期格式，如1987-09-05") 如果你一直把AI當成神，那得到的結果可能就會出錯，比如提取性別。你要告訴AI，性別是根據第17位判斷，這樣AI才能得到正確答案。=WPSAI(A2,"根據身份證第17位判斷男女") =WPSAI(A2,"包含關鍵詞餓了么，就提取里面的數字，其他情況顯示空白") =WPSAI(A2,"包含關鍵詞美團，就提取里面的數字，其他情況顯示空白") =WPSAI(A2,"將LN后面的文字提取出來") =WPSAI(F2,"將單元格轉換成人民幣大寫") 24.WPSAI.CLASSIFY，功能：文本分類。示例：=WPSAI.CLASSIFY(A2,"肉類,水果,蔬菜")，對食品進行分類。25.WPSAI.EXTRACT，功能：提取指定信息。示例：=WPSAI.EXTRACT(A2,"年齡")，從混合信息中提取年齡。26.WPSAI.SENTIANALYSIS，功能：情感分析。示例：=WPSAI.SENTIANALYSIS(A2)，分析評論文本的情感傾向。27.WPSAI.SUMMARIZE，功能：文本摘要。示例：=WPSAI.SUMMARIZE(A2)，生成文本摘要。28.WPSAI.TRANSLATE，功能：翻譯文本。示例：=WPSAI.TRANSLATE(A2,"en")，將A2內容翻譯為英文。示例：=LAMBDA(x,y, x+y)(A1,B1)，定義加法函數并調用。示例：=MAP(A1:A5, LAMBDA(a, a*2))，將A1:A5每個值乘以2。示例：=SCAN(0, A1:A5, LAMBDA(a,b, a+b))，計算A1:A5的累加和。示例：=MAKEARRAY(3,3, LAMBDA(r,c, r*c))，生成3x3乘法表。AI還是很強的，除了個別提供錯，還有幾個漏了，比如：根據工作表名稱生成目錄SHEETSNAME、將查找到的所有對應值合并在一個單元格TEXTJOIN、能實現透視表各種統計的GROUPBY和PIVOTBY。 陪你學Excel，一生夠不夠？
|
