對(duì)于初學(xué) Excel 人來(lái)說(shuō)，函數(shù)公式是其中比較重要的一部分。但 Excel 中函數(shù)有 400 多個(gè)，常用的也有 40 個(gè)左右。
除了學(xué)會(huì)每個(gè)函數(shù)的使用方式外，有哪些關(guān)于 Excel 公式的小技巧，可以提高函數(shù)的使用效率呢？今天就來(lái)淺聊一下~

一、常見(jiàn)錯(cuò)誤規(guī)避

01丨用好混合引用

這個(gè)技巧在學(xué)習(xí) Excel 公式時(shí)，很基礎(chǔ)但也很重要，如果這個(gè)不了解，可以會(huì)出現(xiàn)下面的錯(cuò)誤。

假設(shè)我們有這樣的一組成績(jī)單：

▌表格中姓名信息均為模擬

你需要將每個(gè)學(xué)生的總成績(jī)找過(guò)來(lái)，使用 VLOOKUP 函數(shù)就可以搞定：

但當(dāng)你向下拖拽時(shí)，發(fā)現(xiàn)某些數(shù)據(jù)在原數(shù)據(jù)中存在，但無(wú)法抓取過(guò)來(lái)：

檢查后發(fā)現(xiàn)，VLOOKUP 的第二參數(shù)（在哪兒找）變動(dòng)導(dǎo)致出錯(cuò)。

這個(gè)問(wèn)題很多人應(yīng)該也知道如何解決，按下F4鎖定行和列，切換為絕對(duì)引用即可：

但碰上相對(duì)引用與絕對(duì)引用混用時(shí)，可能就需要花費(fèi)一點(diǎn)時(shí)間了。

比如說(shuō)，在上面的基礎(chǔ)上，你需要查詢(xún)各個(gè)科目的成績(jī)：

由于查詢(xún)表的表頭與數(shù)據(jù)源表頭的順序并不相同，無(wú)法使用 COLOMUN 函數(shù)完成。此時(shí)，你想到了可以嵌套使用 MATCH 函數(shù)：

但當(dāng)你向右或向下拖拽填充公式時(shí)，無(wú)論是切換為純相對(duì)引用還是純絕對(duì)引用，都會(huì)出錯(cuò)：

此時(shí)，必須使用混合引用：

那么，問(wèn)題來(lái)了，如何確定鎖定行還是列？

給大家說(shuō)一下我的記憶方法，誰(shuí)不變鎖誰(shuí)。還可以進(jìn)一步聯(lián)想這樣記憶，窮則思變，窮=$。

具體到這個(gè)例子中，向右拖拽時(shí)，姓名列列號(hào)不能變，向下拖拽時(shí)，科目的行號(hào)不能變。因此，前者鎖列，后者鎖行。

02丨用好轉(zhuǎn)換符號(hào)

同樣在上面例子基礎(chǔ)上，由于姓名容易重復(fù)，統(tǒng)一使用八位數(shù)字的學(xué)號(hào)替代，而數(shù)據(jù)源如下：

那么，我們這次簡(jiǎn)單一點(diǎn)，只需要找到某個(gè)學(xué)號(hào)的總成績(jī)，但使用VOOKUP 函數(shù)查找時(shí)，卻找不到：

這是因?yàn)?，查找表中的學(xué)號(hào)是數(shù)值，而數(shù)據(jù)源表中的學(xué)號(hào)是文本。

此時(shí)，你可以讓查找的學(xué)號(hào)強(qiáng)制轉(zhuǎn)換為文本類(lèi)型，也就是添加一個(gè)空字符串，具體公式如下：

當(dāng)然，如果你的數(shù)據(jù)源的學(xué)號(hào)列本身是數(shù)值形式的，那么，可以使用--學(xué)號(hào)做強(qiáng)制轉(zhuǎn)換。你看到其他公式的0+學(xué)號(hào)學(xué)號(hào)*1等也是這個(gè)思路。

另外，在 VLOOKUP 函數(shù)是完整匹配的，如果你僅需要匹配其中的某幾個(gè)字，可以使用"*"&查找值"*"作為第一參數(shù)。比如要查詢(xún)某個(gè)公司的全稱(chēng)：

這里的“*”表示通配符。除此以外，也可以使用"?"代表一個(gè)字符。

03丨用好輔助列

很多情況下，我們需要嵌套使用多個(gè)公式，同樣是上面的例子，需要匹配不同姓名不同科目的成績(jī)，我們使用了VLOOKUP 和 MATCH 兩個(gè)函數(shù)的嵌套，公式就比較長(zhǎng)了，差不多占據(jù)了 10 個(gè)單元格的距離：

而實(shí)際工作中，我們可能還會(huì)添加一些容錯(cuò)函數(shù)，比如 IFERROR 等，這樣一來(lái)，公式就更長(zhǎng)了。

但公式一長(zhǎng)，不僅是占地多，出錯(cuò)的概率也隨之增大。并且，在整個(gè)公式錄入過(guò)程中不能中斷，否則會(huì)出現(xiàn)各種錯(cuò)誤，比如參數(shù)個(gè)數(shù)不對(duì)，括號(hào)未正確匹配等：

如果原數(shù)據(jù)源允許復(fù)制的情況下，我強(qiáng)烈建議你復(fù)制到臨時(shí)表，并且使用輔助列一步步算，幫你理清思路：

最后再整合為一個(gè)公式。這樣哪一步驟有問(wèn)題改哪個(gè)步驟就可以，不用再去那么長(zhǎng)的公式里找括號(hào)了。

04丨用好單元格內(nèi)換行

上面說(shuō)到，針對(duì)較長(zhǎng)的函數(shù)公式，可以使用輔助列，但如果遇到必須一個(gè)公式完成的情況，那么，我建議你，將編輯欄區(qū)域拉高，在公式單元格內(nèi)使用Alt + Enter換行，并使用空格來(lái)區(qū)分不同的層級(jí)：

當(dāng)然，如何劃分層級(jí)關(guān)系，看你的個(gè)人習(xí)慣，我這里是將每個(gè)參數(shù)都單獨(dú)成行了。

二、利用函數(shù)說(shuō)明

01丨基本函數(shù)

在 Office 比較高的版本（如 365、2019 等），當(dāng)你新建 Excel 文件時(shí)，可以在聯(lián)機(jī)模板中找到一份《公式教程》：

這份文檔中給出了常用的函數(shù)介紹及使用方法，比如反復(fù)提到的 VLOOKUP：

當(dāng)然，如果你的 Office 版本比較低或者有其他情況，那么我也為你準(zhǔn)備了離線(xiàn)版本，可以在「未央暮城」公眾號(hào)后臺(tái)回復(fù)「函數(shù)技巧」領(lǐng)取。

02丨常見(jiàn)函數(shù)

上面的官方教程，僅僅是一些基礎(chǔ)的函數(shù)和使用方法，很多函數(shù)并沒(méi)有添加進(jìn)來(lái)，比如 INDEX 和 MATCH 的經(jīng)典搭配。

而微軟官網(wǎng)上，給出了所有公式的使用方法：

但說(shuō)明性的文檔 + 類(lèi)似“坐和放寬”的奇怪翻譯，普通人不是很容易理解。

因此，我也為你找到了一份更容易懂，并且非常詳細(xì)的公式說(shuō)明文檔：

里面的內(nèi)容非常詳實(shí)，以 VLOOKUP 為例，介紹各種參數(shù)的說(shuō)明：

還有很多示例來(lái)幫你理解函數(shù)如何使用：

同樣，需要的同學(xué)可以在「未央暮城」公眾號(hào)后臺(tái)回復(fù)「函數(shù)技巧」領(lǐng)取。

當(dāng)然，上面這些都是別人總結(jié)的，可能并不適合你，你可以參考制作一份自己的函數(shù)手冊(cè)。

并且，不要局限在單個(gè)函數(shù)的使用方法上。比如我們知道可以通過(guò) MATCH 和 INDEX 實(shí)現(xiàn)逆向查找，那么，就可以定義一個(gè)組合函數(shù)：

有了這個(gè)函數(shù)使用說(shuō)明文件，常用的直接套用就可以，不用再去一個(gè)個(gè)看參數(shù)解釋了。

03丨自創(chuàng)函數(shù)

上面介紹的兩種函數(shù)，都是使用系統(tǒng)內(nèi)置的函數(shù)來(lái)完成。優(yōu)點(diǎn)是發(fā)給別人時(shí)，不用過(guò)多考慮兼容性的問(wèn)題，只要你使用的不是 FILTER、XLOOKUP 這種僅在新版 Office 版本中才有效的函數(shù)。但缺點(diǎn)就是，某些功能無(wú)法實(shí)現(xiàn)或者實(shí)現(xiàn)起來(lái)比較復(fù)雜。

比如說(shuō)，我想要獲取單元格內(nèi)的手機(jī)號(hào)：

看起來(lái)挺簡(jiǎn)單的一個(gè)需求，但僅限使用內(nèi)置函數(shù)時(shí)，因?yàn)樯婕暗綌?shù)組，運(yùn)算邏輯很復(fù)雜：

其實(shí)， VBA 中的正則表達(dá)式可以輕松完成，也很好理解：

另外，它還可以處理更加復(fù)雜的文本，比如提取 8~10 位這種不確定個(gè)數(shù)的純數(shù)字編號(hào)：

當(dāng)然，不僅僅是提取數(shù)字，提取郵箱、QQ號(hào)、身份證號(hào)等均可以實(shí)現(xiàn)，這里只是正則的冰山一角。

如果你對(duì)使用 VBA 制作自定義函數(shù)感興趣，可以參考以下兩個(gè)開(kāi)源免費(fèi)的小工具：

??公式plus開(kāi)源，VBA還可以這么玩

??淺北表格助手 - 自定義函數(shù)

當(dāng)然，自創(chuàng)函數(shù)的缺點(diǎn)也很明顯，遷移比較麻煩。將計(jì)算后的結(jié)果同步給別人時(shí)，需要把公式轉(zhuǎn)換為值，或者將自定義公式模塊復(fù)制到 Excel 文件中。

淺北文件助手中的「原位粘貼」功能

三、個(gè)人妙招

最后，再附加給大家兩個(gè)關(guān)于 Excel 公式的小技巧。

01丨批量填充公式

我們知道，在向下拖拽填充公式時(shí)，可以直接雙擊單元格右下角的方點(diǎn)，但要實(shí)現(xiàn)向下+向右拖拽自動(dòng)填充公式，好像只能手動(dòng)拖拽。

其實(shí)，遇到這種情況時(shí)，我們可以先選中已經(jīng)編寫(xiě)好公式的單元格，然后擴(kuò)選到要填充公式的區(qū)域（比如按住 Shift 選擇兩單元格所在的矩形區(qū)域），依次按下F2?Ctrl + Enter，公式就可以批量填充了：

02丨長(zhǎng)公式編輯技巧

舉個(gè)簡(jiǎn)單的例子，我需要從一個(gè)完整路徑中提取文件名稱(chēng)，我需要用很長(zhǎng)的函數(shù)實(shí)現(xiàn)：

如果要提取文件主名（不包括擴(kuò)展名的），那么還需要嵌套一層：

轉(zhuǎn)換為比較通用的公式如下：

雖然我已經(jīng)將公式收藏了，但在將公式的路徑替換為具體的單元格時(shí)，一個(gè)個(gè)改太繁瑣了，此時(shí)可以使用替換功能完成：

如果公式內(nèi)涉及到跨表引用，直接輸入單元格的地址比較復(fù)雜。此時(shí)我們可以先在臨時(shí)單元格內(nèi)輸入=，然后使用鼠標(biāo)選擇具體的單元格引用，最后在編輯框中就可以獲得自動(dòng)生成的引用文本了：

最后

對(duì)于函數(shù)的學(xué)習(xí)，就我個(gè)人來(lái)說(shuō)，把函數(shù)的基礎(chǔ)用法學(xué)好就可以，不要覺(jué)得函數(shù)是萬(wàn)能的，無(wú)論計(jì)算什么總想著“用函數(shù)解決”。Excel 中有一些更好用的工具等你使用。

對(duì)了，如果你需要微軟官方和我本無(wú)塵編寫(xiě)的函數(shù)說(shuō)明文檔，由于鏈接可能會(huì)失效，B站由沒(méi)有自動(dòng)回復(fù)功能，你可以在「未央暮城」公眾號(hào)后臺(tái)回復(fù)「函數(shù)技巧」領(lǐng)取。