?我們首先看到的是1級(jí)競(jìng)技場(chǎng)，也就是最初級(jí)的水平。在這個(gè)層級(jí)當(dāng)中的需求呢，通常是一些最基本的統(tǒng)計(jì)需求，例如求和、統(tǒng)計(jì)個(gè)數(shù)、求平均值、最大值、最小值等等；有時(shí)候還需要在統(tǒng)計(jì)的基礎(chǔ)上對(duì)結(jié)果的精度做一些調(diào)整，例如四舍五入并保留幾位小數(shù)，或者直接保留整數(shù)結(jié)果等等。
那么在這里需要用到的就是跟這些統(tǒng)計(jì)需求相關(guān)的函數(shù)，比如SUM求和函數(shù)和COUNT/COUNTA計(jì)數(shù)函數(shù)，就是使用頻率很高的函數(shù)，就好像哥布林兩兄弟一樣。
?
??這幾個(gè)函數(shù)都非常簡(jiǎn)單易用，掌握這些函數(shù)幾乎不需要具備什么背景知識(shí)或技巧，使用Excel的自動(dòng)求和功能甚至可以幫你自動(dòng)生成這些函數(shù)公式。
這里唯一需要提醒的，就是要注意COUNT函數(shù)和COUNTA函數(shù)的區(qū)別。這兩個(gè)函數(shù)都是用來(lái)統(tǒng)計(jì)個(gè)數(shù)，但前者的統(tǒng)計(jì)對(duì)象只有數(shù)值，而后者會(huì)把其他類型的數(shù)據(jù)單元格也包含其中，只要不是空白單元格，就都會(huì)統(tǒng)計(jì)進(jìn)去。
COUNT函數(shù)和COUNTA函數(shù)的區(qū)別
?例如上面這張圖當(dāng)中，同樣都是對(duì)7個(gè)單元格進(jìn)行統(tǒng)計(jì)，但COUNT函數(shù)的眼里只有那三個(gè)數(shù)字，剩下的幾個(gè)名字都被忽略了；而COUNTA函數(shù)就把包含數(shù)字和名字的這幾個(gè)單元格一股腦兒統(tǒng)計(jì)在內(nèi)，只把空白單元格排除在外。COUNTA函數(shù)名稱當(dāng)中的字母A可以看作是英文“All”全部、所有的意思。
2級(jí)競(jìng)技場(chǎng)
?接下來(lái)進(jìn)入2級(jí)競(jìng)技場(chǎng)。從這一級(jí)開(kāi)始，才算是真正踏上函數(shù)公式的道路了。在這里，你需要了解一些有關(guān)于函數(shù)參數(shù)的知識(shí)，學(xué)會(huì)讀懂函數(shù)的語(yǔ)法（沒(méi)錯(cuò)，函數(shù)公式就像遣詞造句一樣，也有語(yǔ)法規(guī)則），了解每個(gè)參數(shù)的具體含義是什么，以及參數(shù)的不同設(shè)置會(huì)對(duì)結(jié)果產(chǎn)生什么樣的影響。
?
?在這一級(jí)別當(dāng)中，你需要掌握一些簡(jiǎn)單的文本處理公式技巧；
了解和掌握多個(gè)函數(shù)嵌套組合的使用方法，函數(shù)就像卡牌，組合起來(lái)使用才更具威力；
學(xué)會(huì)使用IF函數(shù)進(jìn)行一些簡(jiǎn)單的邏輯判斷；
除了獲取最大值和最小值之外，還會(huì)使用LARGE或SMALL函數(shù)提取任意排名中的數(shù)據(jù)；
除此之外，如果需要進(jìn)行排班、抽簽等涉及公平性、隨機(jī)性方面的事務(wù)，你還有必要了解一下隨機(jī)函數(shù)RAND。
野豬騎士是游戲中很多人喜歡使用的一張卡牌，進(jìn)攻非常犀利。類似的，在對(duì)文本字符串進(jìn)行拆分處理的一些問(wèn)題當(dāng)中，LEFT、MID和RIGHT這幾個(gè)函數(shù)也是出場(chǎng)頻率最高的函數(shù)，簡(jiǎn)單而實(shí)用。但在一些現(xiàn)實(shí)工作當(dāng)中，各種情況復(fù)雜多變，光靠前面這三個(gè)函數(shù)往往無(wú)法做到靈活處理，還需要搭配LEN函數(shù)、FIND函數(shù)等一些函數(shù)來(lái)進(jìn)行配合使用。這就好比野豬同樣也需要搭配閃電或冰凍法術(shù)來(lái)使用一樣。
來(lái)舉兩個(gè)例子。
文本處理案例一
?第一個(gè)例子，有B列這樣一列文本，每個(gè)單元格的內(nèi)容是兩個(gè)站點(diǎn)的名稱，需要將其中的兩個(gè)名稱分別提取出來(lái)，生成C列和D列這樣的結(jié)果。這里由于每個(gè)站點(diǎn)名稱的長(zhǎng)度都不盡相同，如果單純使用LEFT或RIGHT函數(shù)很難找到一個(gè)統(tǒng)一的第二參數(shù)一次性得到全部對(duì)象的提取結(jié)果。
因此更科學(xué)高效的方案是利用每?jī)蓚€(gè)站點(diǎn)名稱當(dāng)中出現(xiàn)的標(biāo)志性字符“至”，比如“新天地至南京西路”中間的“至”、“上海圖書館至靜安寺”中間的“至”，利用這個(gè)字的分隔性作用，借助FIND函數(shù)來(lái)找到這個(gè)字的所在位置，再根據(jù)這個(gè)位置進(jìn)行一些調(diào)整，這樣就能夠確定LEFT或RIGHT函數(shù)所需要的第二參數(shù)的具體取值了。我們來(lái)看一下下面這張圖：
?先通過(guò)FIND函數(shù)先查找“至”字所在的位置，將這個(gè)位置減去1，就是左側(cè)站點(diǎn)名稱的長(zhǎng)度；類似的，如果將整個(gè)字符串的長(zhǎng)度減去“至”的位置，就可以確定右側(cè)站點(diǎn)名稱的長(zhǎng)度。有了這兩個(gè)長(zhǎng)度結(jié)果，最后再使用LEFT和RIGHT函數(shù)就可以分別提取出左側(cè)站點(diǎn)和右側(cè)站點(diǎn)的名稱了。
所以最終的解決方案可以是下面圖中這個(gè)樣子的，C列使用第12行中所顯示的公式，D列的公式顯示在第13行當(dāng)中：
?再來(lái)看第二個(gè)例子：
文本處理案例二
?第二個(gè)例子，B列當(dāng)中有中文和英文數(shù)字混排的字符串，比較有規(guī)律的地方在于中文字符都在左側(cè)，而字母和數(shù)字都出現(xiàn)在右側(cè)，沒(méi)有相互混雜的情況，現(xiàn)在需要把這兩部分分別提取出來(lái)，生成C列和D列這樣的結(jié)果。這個(gè)問(wèn)題應(yīng)該如何處理呢？
需要說(shuō)明的是，到目前為止，Excel當(dāng)中還沒(méi)有能夠自動(dòng)識(shí)別中文還是英文的這樣一個(gè)函數(shù)，但是中文字符和英文字符以及數(shù)字之間，存在一個(gè)比較隱蔽的差異，就是中文字符都是全角字符，每個(gè)字符包含兩個(gè)字節(jié)；而普通的英文數(shù)字都是半角字符，每個(gè)字符只包含一個(gè)字節(jié)。利用這個(gè)特性，我們可以使用LEN函數(shù)和LENB函數(shù)分別測(cè)量出目標(biāo)字符串中的字符個(gè)數(shù)和字節(jié)個(gè)數(shù)，兩者對(duì)比產(chǎn)生的差異，就可以反映出其中中文字符的個(gè)數(shù)了。
具體的原理可以看一下下面這張圖：
?使用LENB函數(shù)可以獲取整個(gè)字符串當(dāng)中所包含的字節(jié)數(shù)，比如圖上的這些A就代表了字節(jié)；而使用LEN函數(shù)可以獲取整個(gè)字符串當(dāng)中的字符個(gè)數(shù)，這張圖上的這些B就代表了字符；可以很明顯的看出來(lái)，每個(gè)漢字會(huì)多出一個(gè)A，因此A的總數(shù)量會(huì)比B的數(shù)量多出4個(gè)，也就是其中漢字的個(gè)數(shù)。所以，漢字的個(gè)數(shù)就等于字節(jié)數(shù)減去字符數(shù)。
?所以最終的解決方案可以是上面圖中的這個(gè)樣子，通過(guò)LENB和LEN函數(shù)分別獲取字符串中的字節(jié)個(gè)數(shù)和字符個(gè)數(shù)，通過(guò)兩者的差值得到漢字個(gè)數(shù)，再用LEFT函數(shù)將其提取出來(lái)；另一方面，英文和數(shù)字的個(gè)數(shù)就等于總字符個(gè)數(shù)減去漢字個(gè)數(shù)，做一下數(shù)學(xué)換算可以知道實(shí)際就等價(jià)于兩倍的字符個(gè)數(shù)減去字節(jié)個(gè)數(shù)，獲取到這個(gè)結(jié)果以后就可以使用RIGHT函數(shù)提取出右邊的這些字母和數(shù)字，這樣就能實(shí)現(xiàn)中英文的分離處理了。
所以通過(guò)上面的兩個(gè)例子，我們可以了解到對(duì)于一些復(fù)雜的字符串處理問(wèn)題，通常都需要多種不同功能的文本處理函數(shù)一起協(xié)同工作、配合使用，才能有效的達(dá)到目的。
3級(jí)競(jìng)技場(chǎng)
?接下來(lái)進(jìn)入到3級(jí)競(jìng)技場(chǎng)，到了這一層級(jí)，就有必要掌握一些有關(guān)于日期時(shí)間數(shù)據(jù)的處理方法了。在工作當(dāng)中很多數(shù)據(jù)都是跟日期相關(guān)的，比如每天的進(jìn)銷存數(shù)據(jù)、每天每個(gè)時(shí)刻的用戶訪問(wèn)數(shù)據(jù)、項(xiàng)目計(jì)劃的時(shí)間安排等等，要對(duì)這類數(shù)據(jù)進(jìn)行有效處理分析，就有必要了解日期的相關(guān)背景知識(shí)。其中包括日期和數(shù)值之間的轉(zhuǎn)換關(guān)系、日期的規(guī)范化處理以及日期相關(guān)的運(yùn)算方法等等。
?
?其實(shí)，在Excel當(dāng)中，日期的實(shí)質(zhì)就是從1900年1月1日這天開(kāi)始每天累計(jì)遞增的一個(gè)數(shù)字，了解了這個(gè)本質(zhì)特性之后，日期的常規(guī)運(yùn)算都可以轉(zhuǎn)化成數(shù)學(xué)上簡(jiǎn)單的算術(shù)運(yùn)算。要處理一些更復(fù)雜的日期換算呢就需要用到圖上所顯示的這些常用的日期函數(shù)了，但這些日期函數(shù)在使用上也都算不上復(fù)雜，只有一些簡(jiǎn)單的參數(shù)設(shè)置。
YEAR/MONTH/DAY函數(shù)可以從日期當(dāng)中分別拆分出年/月/日信息；
TODAY和NOW函數(shù)可以自動(dòng)獲取系統(tǒng)當(dāng)前的日期和時(shí)間，可以用于建立一些具備到期提醒功能的自動(dòng)化模型；WEEKDAY和WEEKNUM可以處理與星期相關(guān)的問(wèn)題；
而WORKDAY和NETWORKDAYS函數(shù)則主要進(jìn)行跟工作日有關(guān)的運(yùn)算。
這里我選擇了一些有時(shí)間限制的游戲卡牌作為他們的象征。
其中值得特別一提的是WORKDAY函數(shù)，它可以用來(lái)推算若干個(gè)工作日以后的具體日期，在一些項(xiàng)目管理的場(chǎng)景中應(yīng)用較多。在常規(guī)的用法當(dāng)中，這個(gè)函數(shù)對(duì)工作日的定義就是一周當(dāng)中排除掉周六和周日以后的其他幾天。比如下面圖中所顯示的這個(gè)例子：
工作日計(jì)算案例
?2016年4月25日之后的第10個(gè)工作日的日期，使用WORKDAY函數(shù)得到的結(jié)果是2016年5月9日，實(shí)際上就是把這段日期當(dāng)中所包含的四個(gè)周六和周日都排除在外了，可以看一下下面這張圖的示意：
?但是除了常規(guī)的周六周日之外，有時(shí)候也會(huì)有一些法定假日不能算在工作日之內(nèi)，比如今年的五一節(jié)，除了30號(hào)和1號(hào)之外，五月二號(hào)禮拜一也是安排為假日。在這種情況下，如何可以把法定假日也排除在外，正確的推算工作日日期呢？
在這種情況下就可以利用WORKDAY函數(shù)隱含的第三個(gè)參數(shù)，來(lái)為函數(shù)指定一些需要特殊處理的非周末假期。具體操作方法是將這些非周末假期羅列在表格當(dāng)中，然后使用WORKDAY函數(shù)時(shí)將第三參數(shù)引用這個(gè)羅列了假期的單元格區(qū)域，就可以正確計(jì)算了。具體公式可以看下面這張圖。與此類似，NETWORKDAYS函數(shù)也可以在計(jì)算工作日天數(shù)時(shí)排除一些特定的假期。
?下面這張圖就示意了定義過(guò)特殊假期以后，WORKDAY函數(shù)的實(shí)際運(yùn)算方式：
?從這個(gè)案例當(dāng)中，可以了解到，有些函數(shù)會(huì)包含一些比較隱蔽的參數(shù)，這些參數(shù)在平常函數(shù)的使用中可以不參與不出現(xiàn)，所以往往容易被人忽略，但有一些時(shí)候這些參數(shù)卻能起到非常重要的作用，除了上面提到的WORKDAY、NETWORKDAYS函數(shù)之外，類似的情況還有RANK函數(shù)的第3個(gè)參數(shù)、FIND函數(shù)的第3個(gè)參數(shù)、SUBSTITUTE函數(shù)的第4個(gè)參數(shù)等等。
下面圖片當(dāng)中簡(jiǎn)單羅列了這些需要注意的函數(shù)：
包含可忽略參數(shù)的函數(shù)
?圖上這些函數(shù)當(dāng)中都包含了比較隱蔽的參數(shù)，這些參數(shù)平時(shí)可以忽略掉，也就是不去使用，但是不應(yīng)該忽視他們的作用，說(shuō)不定什么時(shí)候就用得上了。
4級(jí)競(jìng)技場(chǎng)
?再往上就可以進(jìn)入到4級(jí)競(jìng)技場(chǎng)了。
?
?到了這個(gè)層級(jí)，很多人見(jiàn)到了熟悉的VLOOKUP函數(shù)。這個(gè)函數(shù)是許多朋友對(duì)于函數(shù)公式的初戀，很多人都是通過(guò)這個(gè)函數(shù)開(kāi)始認(rèn)識(shí)和了解Excel函數(shù)公式的，也有很多人從這里開(kāi)始體會(huì)到函數(shù)公式的強(qiáng)大。因?yàn)槭煜?，所以很多人覺(jué)得VLOOKUP函數(shù)比較簡(jiǎn)單，但要把這個(gè)函數(shù)真正理解吃透還是需要下點(diǎn)功夫。
這就好比游戲當(dāng)中的巨人卡牌，這張牌從一開(kāi)始就可以使用，非常強(qiáng)大也非常好用，但是越到后面才越體會(huì)到這張牌用得好不好還是有很大差別的；
HLOOKUP函數(shù)和VLOOKUP用法幾乎一樣，所以經(jīng)常會(huì)放到一起來(lái)介紹；
接觸到這些查詢函數(shù)之后，難免有時(shí)候會(huì)出現(xiàn)查找不到目標(biāo)的情況，因此有時(shí)還要對(duì)查找產(chǎn)生的錯(cuò)誤值進(jìn)行一些優(yōu)化處理，IFERROR函數(shù)就是這類需求當(dāng)中一個(gè)比較好的選擇；
到了這個(gè)層級(jí)之后，許多公式往往需要進(jìn)行大范圍的填充復(fù)制來(lái)發(fā)揮更大的作用，在這種情況下就必須清楚的理解單元格地址的絕對(duì)引用和相對(duì)引用這兩個(gè)概念了；
除此之外，ROW函數(shù)和COLUMN函數(shù)是表格中的高級(jí)計(jì)數(shù)器，可以對(duì)函數(shù)公式的大范圍應(yīng)用起到非常重要的輔助作用，要讓公式從跑變飛，往往少不了它倆的出手相助，就好比游戲里的狂暴藥水一樣，非常有用的一種輔助類法術(shù)；
COUNTIF函數(shù)和SUMIF函數(shù)，可以把單一的個(gè)數(shù)統(tǒng)計(jì)和求和運(yùn)算，變成具備一定篩選功能的統(tǒng)計(jì)，這就比原先的統(tǒng)計(jì)方式更加提升了一步；
最后一個(gè)RANK函數(shù)可以在不改變數(shù)據(jù)原有排列順序的情況下得到每個(gè)數(shù)的排名，比排序操作來(lái)的更加靈活方便。
以上這些就是這個(gè)層級(jí)當(dāng)中這些函數(shù)的一個(gè)基本介紹。
接下來(lái)，我也舉一個(gè)具體的例子，來(lái)詳細(xì)講一講VLOOKUP函數(shù)的用法。
很多時(shí)候我們都會(huì)整理出一些信息表，比如員工信息表、商品信息表、材料明細(xì)表等等，有時(shí)候我們會(huì)需要根據(jù)某些關(guān)鍵信息去這些表里面查詢與之相關(guān)的內(nèi)容，比如根據(jù)員工的姓名去信息表中查詢他的學(xué)歷，根據(jù)某個(gè)商品編號(hào)去信息表中查詢它的進(jìn)貨價(jià)格等等，在這類需求當(dāng)中，如果依靠人工查找的話既費(fèi)時(shí)又費(fèi)力，而使用VLOOKUP函數(shù)則可以很輕松的實(shí)現(xiàn)大批量的自動(dòng)查詢，因此也有朋友把VLOOKUP函數(shù)稱為“查表函數(shù)”。
信息查詢案例
?比如在上面這張圖當(dāng)中，F(xiàn)列到J列提供了一份培訓(xùn)信息庫(kù)，里面包含了許多員工參加培訓(xùn)的一些信息?，F(xiàn)在B列當(dāng)中提供了一些待查詢的員工姓名，需要根據(jù)這些提供的名單，找出他們參加的培訓(xùn)課程名稱以及取得的學(xué)分。這個(gè)問(wèn)題就是適合使用VLOOKUP函數(shù)來(lái)處理的一個(gè)典型問(wèn)題。
下面先來(lái)看一下VLOOKUP函數(shù)的語(yǔ)法規(guī)則：
VLOOKUP函數(shù)語(yǔ)法規(guī)則
?VLOOKUP函數(shù)需要設(shè)置四個(gè)參數(shù)來(lái)進(jìn)行具體的查詢，第一個(gè)參數(shù)是查詢依據(jù)，也就是用于查詢的關(guān)鍵信息，例如待查詢的某個(gè)姓名或是某個(gè)商品編號(hào)等等。
第二個(gè)參數(shù)是信息源，也就是需要給出存放具體信息的信息庫(kù)所在的單元格范圍。這里需要注意的一個(gè)問(wèn)題是，不管信息庫(kù)本身有多少列，必須用可以找到關(guān)鍵信息的那一列作為查詢范圍的首列。比如假定用姓名作為關(guān)鍵信息進(jìn)行查詢的話，這個(gè)單元格范圍就必須以信息庫(kù)當(dāng)中的姓名列作為首列，而是要編號(hào)進(jìn)行查詢的話，就必須以編號(hào)列作為首列。
下面這個(gè)圖上就給出了一個(gè)具體的例子，比如我希望根據(jù)姓名作為關(guān)鍵信息來(lái)查詢學(xué)歷，那么第二參數(shù)選擇信息庫(kù)范圍時(shí)就應(yīng)該從姓名列開(kāi)始一直包含到學(xué)歷列；而如果希望根據(jù)編號(hào)來(lái)查詢學(xué)歷，那么第二參數(shù)的選擇范圍就要從編號(hào)列開(kāi)始選取一直包含到學(xué)歷列為止。
?第三個(gè)參數(shù)要給出目標(biāo)位置，也就是想要查詢的具體信息在信息源范圍當(dāng)中的相對(duì)列序號(hào)，這句話聽(tīng)上去比較拗口，實(shí)際操作的時(shí)候可以這樣來(lái)確定：假定希望根據(jù)姓名找出學(xué)歷信息，那么在信息庫(kù)當(dāng)中以姓名作為首列，從姓名列開(kāi)始一直到學(xué)歷所在的列，總共包含了多少列，就是這個(gè)參數(shù)的取值。
比如在前面的圖中可以看到，如果用姓名查學(xué)歷的話，那么第三參數(shù)就是5，從姓名列到學(xué)歷列一共包含了5列；如果用編號(hào)來(lái)查詢學(xué)歷的話，那么第三參數(shù)就應(yīng)該取6，其中一共包含了6列。
第四個(gè)參數(shù)用來(lái)設(shè)定VLOOKUP函數(shù)的工作模式，通常取值為0表示工作模式為精確查找。
搞清楚這四個(gè)參數(shù)的具體含義和設(shè)置規(guī)則之后，就不難寫出整個(gè)VLOOKUP公式了。在前面那個(gè)案例當(dāng)中使用這個(gè)公式，就可以完成幾個(gè)人的信息查詢，具體可以看一下下面這張圖：
?這張圖上，查找課程名稱和查找學(xué)分使用了兩個(gè)不同的VLOOKUP公式，其中的區(qū)別就在于第三參數(shù)的設(shè)置上，需要根據(jù)具體信息存放的位置來(lái)調(diào)整，一個(gè)設(shè)成2，另一個(gè)設(shè)成4。
在這個(gè)結(jié)果當(dāng)中，我們看到莊偉、王美芬、黃桂晶這幾個(gè)人的相關(guān)信息都用公式查找到了，但是周愛(ài)暉的查詢結(jié)果顯示了兩個(gè)錯(cuò)誤值#N/A，這是因?yàn)樵贔列提供的培訓(xùn)信息庫(kù)名單當(dāng)中沒(méi)有這位員工的相關(guān)信息。因此VLOOKUP函數(shù)會(huì)得到一個(gè)錯(cuò)誤值，以此來(lái)表示沒(méi)有找到目標(biāo)的這么一個(gè)結(jié)果。與此類似的，LOOKUP、MATCH等其他一些查找函數(shù)也會(huì)在查詢不到目標(biāo)的情況下返回這樣的錯(cuò)誤值。
有些朋友覺(jué)得錯(cuò)誤值太扎眼，希望進(jìn)行一些優(yōu)化處理，不讓這些錯(cuò)誤值顯示出來(lái)，而是使用一些更加友好的提示信息來(lái)替代，比如“信息不存在”等等。這類需求就可以派IFERROR函數(shù)來(lái)登場(chǎng)了。
IFERROR函數(shù)和IF函數(shù)的用法有些相似，但也有不同。它只需要兩個(gè)參數(shù)，第一個(gè)參數(shù)是需要判斷是否出現(xiàn)錯(cuò)誤值的對(duì)象，通常是另外的某個(gè)公式，比如我們上面所使用的VLOOKUP查詢公式，如果這個(gè)對(duì)象沒(méi)有出現(xiàn)錯(cuò)誤，就直接得到它的結(jié)果，如果出現(xiàn)錯(cuò)誤，那就得到第二參數(shù)中設(shè)定的另外一個(gè)結(jié)果。因此，我們把這個(gè)函數(shù)用上，就可以讓前面案例中的結(jié)果顯得更好看一些：
?從這張圖上可以看到，我們把VLOOKUP函數(shù)這部分作為IFERROR函數(shù)的第一個(gè)參數(shù)來(lái)使用，如果VLOOKUP函數(shù)能夠正常查找的話，就直接顯示結(jié)果了；如果找不到目標(biāo)產(chǎn)生錯(cuò)誤值呢，那么就用IFERROR函數(shù)第二個(gè)參數(shù)中所設(shè)定的結(jié)果來(lái)替代。所以周愛(ài)暉的查詢結(jié)果就會(huì)顯示“無(wú)”這個(gè)字表示沒(méi)有找到。
5級(jí)競(jìng)技場(chǎng)
?更進(jìn)一步就來(lái)到了5級(jí)競(jìng)技場(chǎng)。
?
?在這一級(jí)別當(dāng)中，你應(yīng)該了解更多的查詢引用函數(shù)例如MATCH函數(shù)和INDEX函數(shù)，來(lái)處理一些更加復(fù)雜的查詢問(wèn)題，例如交叉查詢。這兩個(gè)函數(shù)經(jīng)常配合起來(lái)一起使用，就好比游戲當(dāng)中白王子和黑王子兩張卡牌；
與他們關(guān)系密切的還有OFFSET函數(shù)和INDIRECT函數(shù)，這兩個(gè)函數(shù)的特點(diǎn)是可以通過(guò)給定的行列坐標(biāo)，定位到一個(gè)具體的單元格對(duì)象上。這就好比游戲中的閃電和火箭這樣的遠(yuǎn)程武器卡牌，可以精確打擊戰(zhàn)場(chǎng)上的任意一個(gè)位置；
最后兩個(gè)COUNTIFS函數(shù)和SUMIFS函數(shù)是前一級(jí)競(jìng)技場(chǎng)中COUNTIF函數(shù)和SUMIF函數(shù)的升級(jí)版，他們差別就在于函數(shù)名稱當(dāng)中的那個(gè)字母S，從單數(shù)變成了復(fù)數(shù)，從處理單個(gè)條件的統(tǒng)計(jì)變成可以處理多個(gè)條件的統(tǒng)計(jì)，這兩個(gè)函數(shù)的統(tǒng)計(jì)能力又再一次得到了提升。
下面舉一個(gè)具體的例子，來(lái)介紹一下MATCH函數(shù)和INDEX函數(shù)配合使用的方法。
交叉查詢案例
?上面的圖中顯示了這樣一個(gè)場(chǎng)景，某個(gè)公司制定了非常細(xì)致的獎(jiǎng)金系數(shù)規(guī)則，需要根據(jù)不同的崗位級(jí)別以及所屬的部門來(lái)確定具體的獎(jiǎng)金系數(shù)，即使在同一個(gè)崗位級(jí)別上也會(huì)因?yàn)椴块T的不同而造成系數(shù)的差異。
具體的規(guī)則形成了G列到L列當(dāng)中顯示的這張系數(shù)對(duì)照表?，F(xiàn)在呢，需要根據(jù)B列到D列當(dāng)中所提供的這些人員名單，來(lái)查詢他們具體的獎(jiǎng)金系數(shù)應(yīng)該取多少。大家來(lái)思考一下這個(gè)問(wèn)題憑借你們現(xiàn)在所掌握的函數(shù)卡牌，有沒(méi)有理想的解決方案呢？
如果仔細(xì)觀察這個(gè)案例的話，其實(shí)可以發(fā)現(xiàn)跟前一個(gè)案例當(dāng)中的信息查詢有那么一些類似，假定以部門作為關(guān)鍵信息，忽略崗位級(jí)別的話，就是要根據(jù)每個(gè)人所屬的部門去數(shù)據(jù)源當(dāng)中進(jìn)行查詢，這就看上去跟我們前面VLOOKUP函數(shù)的場(chǎng)景很像了。
但是隨著而來(lái)的問(wèn)題是，如果使用VLOOKUP函數(shù)，那么第三參數(shù)應(yīng)該怎么取呢？
這里的第三參數(shù)貌似并不是一個(gè)固定的取值，而是需要根據(jù)不同的崗位級(jí)別來(lái)確定的，如果崗位級(jí)別為A，第三參數(shù)就取2；如果為B，就取3等等。所以需要提供一種機(jī)制，能夠根據(jù)崗位級(jí)別來(lái)進(jìn)行自動(dòng)判斷。
因此這里實(shí)際上不僅要在縱向上查詢部門，還要在橫向上查詢崗位級(jí)別，兩者確定以后的交叉位置，才是真正需要尋找的目標(biāo)信息所在，因此這類問(wèn)題也稱為交叉查詢問(wèn)題。僅僅依靠VLOOKUP函數(shù)或HLOOKUP函數(shù)這種單一方向的查詢函數(shù)很難奏效。因此有必要再學(xué)習(xí)了解一下MATCH函數(shù)的用法。
MATCH函數(shù)運(yùn)作原理
?MATCH函數(shù)又稱為位置查詢函數(shù)，它的作用是查詢某個(gè)對(duì)象在一組對(duì)象當(dāng)中的排列位置。
使用這個(gè)函數(shù)需要提供三個(gè)參數(shù)，第一個(gè)參數(shù)是具體的查找對(duì)象；
第二個(gè)參數(shù)目標(biāo)范圍，也就是包含這個(gè)查找對(duì)象的一組對(duì)象所在范圍，通常是某一行或某一列的單元格區(qū)域；
第三個(gè)參數(shù)也是工作模式，通常我們也選擇0使用精確查找的工作模式。
有了這個(gè)函數(shù)，我們就可以分別根據(jù)部門和崗位級(jí)別，找出對(duì)照表里面相應(yīng)的行列坐標(biāo)位置。我們可以在表格中添加兩個(gè)輔助列，先用MATCH函數(shù)來(lái)獲取行坐標(biāo)和列坐標(biāo)，就像下面圖中所顯示的這樣：
?F列用MATCH函數(shù)對(duì)部門進(jìn)行一個(gè)縱向查找，這樣可以找到部門所在行的坐標(biāo)。比如黃曉薇屬于客服部，客服部根據(jù)MATCH函數(shù)找到相關(guān)的系數(shù)信息位于第8行，8就是這個(gè)行的坐標(biāo)。
類似的，G列根據(jù)崗位級(jí)別進(jìn)行MATCH函數(shù)的橫向查詢。比如黃曉薇A級(jí)的相關(guān)系數(shù)信息就找到都位于第10列（也就是J列）當(dāng)中。
那么有了這兩個(gè)坐標(biāo)以后，怎么樣可以定位到具體的獎(jiǎng)金系數(shù)呢？下面就輪到INDEX函數(shù)出場(chǎng)了。
INDEX函數(shù)運(yùn)作原理
?INDEX函數(shù)可以根據(jù)行列坐標(biāo)來(lái)具體定位到目標(biāo)單元格。
它需要三個(gè)參數(shù)，第一個(gè)參數(shù)是先確定一個(gè)區(qū)域范圍；
第二參數(shù)和第三參數(shù)分別代表行坐標(biāo)和列坐標(biāo)；
如果只用一個(gè)坐標(biāo)，也可以在單獨(dú)某一行或某一列中進(jìn)行定位。
從這里可以看出來(lái)，INDEX函數(shù)和MATCH函數(shù)簡(jiǎn)直就是天生的好基友，兩個(gè)函數(shù)一個(gè)能找坐標(biāo)，另一個(gè)就能根據(jù)坐標(biāo)找到目標(biāo)。
因此在前面的這個(gè)案例當(dāng)中，我們最終可以借助INDEX函數(shù)查詢到具體的獎(jiǎng)金系數(shù)，詳細(xì)公式顯示在下面的圖中：
?
6級(jí)競(jìng)技場(chǎng)
?功力繼續(xù)精進(jìn)的話，就可以到達(dá)6級(jí)競(jìng)技場(chǎng)了。在這個(gè)層面上，你需要認(rèn)識(shí)一些平時(shí)不太關(guān)注但卻非常有用的函數(shù)。
?
?比如VLOOKUP函數(shù)的大師兄LOOKUP函數(shù)，這個(gè)函數(shù)的不僅能夠?qū)崿F(xiàn)精確查詢的作用，更重要的是他具備區(qū)間分段查詢的功能，并且在具體使用上比VLOOKUP函數(shù)更加靈活。他們兩者的關(guān)系就好像皇家巨人跟巨人之間的關(guān)系，兩張牌各有千秋，都擁有不少的粉絲；
如果你已經(jīng)用慣了COUNTIFS和SUMIFS函數(shù)，也許就不太會(huì)注意到SUMPRODUCT這個(gè)函數(shù)了，事實(shí)上SUMPRODUCT函數(shù)比前面兩個(gè)函數(shù)更加強(qiáng)大、更加靈活。如果把COUNTIF家族比作骷髏卡牌的話，那么SUMPRODUCT就是加了盾牌的骷髏；
DATEDIF函數(shù)是一個(gè)Excel當(dāng)中的隱藏函數(shù)，在函數(shù)列表和函數(shù)向?qū)М?dāng)中甚至找不到它的蹤影。但是它卻是用來(lái)計(jì)算兩個(gè)日期之間間隔了多少個(gè)月、多少年的有力武器，最重要的是，它在計(jì)算過(guò)程當(dāng)中可以精確到具體的某一天。想要算周歲年齡嗎，就可以用它來(lái)處理；
TEXT函數(shù)非常神奇，它和單元格格式的作用有一些相似，可以把一個(gè)數(shù)據(jù)變換成另外一種樣子，就好像一面魔鏡，鏡子里顯現(xiàn)出另一番景象。用好這個(gè)函數(shù)的關(guān)鍵在于需要掌握許多格式代碼，通過(guò)這些格式代碼來(lái)呈現(xiàn)不同的效果，這些格式代碼就像對(duì)魔鏡念的咒語(yǔ)；
SUBSTITUTE函數(shù)，因?yàn)檫@個(gè)函數(shù)名稱字母很多，很難念也很難寫，因此幾乎被很多人遺忘了。但事實(shí)上要記住這個(gè)函數(shù)并不困難，在體育比賽中的換人用的就是這個(gè)詞，而它在函數(shù)中的作用就是在文本字符串當(dāng)中進(jìn)行查找替換，就像換人一樣；
最后一個(gè)PHONETIC函數(shù)的經(jīng)歷也很奇特，它的功能是可以同時(shí)引用一批單元格，把這些單元格當(dāng)中的文字內(nèi)容拼接成一個(gè)完整的字符串，但這個(gè)函數(shù)被創(chuàng)造出來(lái)的初衷其實(shí)并不是用來(lái)干這個(gè)活兒的，被大家這么用來(lái)拼接字符串完全屬于無(wú)心插柳。
所以，到了這個(gè)層級(jí)，你自然而然就會(huì)去了解許多函數(shù)背后的故事。在這個(gè)層級(jí)上，你也許還會(huì)慢慢認(rèn)識(shí)數(shù)組公式這個(gè)強(qiáng)大的工具，它就好像一把強(qiáng)大的螺絲刀，可以把普普通通的函數(shù)拼裝出更加高效更加強(qiáng)力的公式來(lái)。這是一個(gè)可以讓公式能力得到飛躍的工具，如果你希望達(dá)到更高水平的話，很有必要了解一下。
下面呢同樣舉個(gè)例子，來(lái)為大家介紹一下LOOKUP函數(shù)在區(qū)間分段查詢場(chǎng)景當(dāng)中的使用方法。
區(qū)間分段查詢案例
?現(xiàn)在許多公司都施行精細(xì)化管理，在考核和激勵(lì)機(jī)制上的設(shè)計(jì)都非常細(xì)致，比如有這樣一家公司，為了更好的激勵(lì)員工的銷售，設(shè)計(jì)了一套銷售提成比例的規(guī)則，為各個(gè)不同檔次的銷售業(yè)績(jī)制定了不同的銷售提成比例，形成了F列到H列這樣五六個(gè)分檔區(qū)間。
現(xiàn)在需要根據(jù)C列當(dāng)中每位員工實(shí)際的銷售業(yè)績(jī)，來(lái)確定到底應(yīng)該按照多少的提成比例進(jìn)行提成計(jì)算。
有很多初學(xué)者碰到類似這樣的問(wèn)題，會(huì)首先考慮使用IF函數(shù)來(lái)羅列一個(gè)個(gè)判斷條件，通過(guò)一串復(fù)雜的邏輯組合得到最終的查詢結(jié)果。沒(méi)錯(cuò)，這種做法算不上錯(cuò)，還可以很好的鍛煉邏輯分析能力，但從公式編寫效率上來(lái)講是非常失敗的，整個(gè)公式會(huì)變得非常復(fù)雜，編寫當(dāng)中還容易出錯(cuò)。對(duì)于這類需要在不同區(qū)間分段上進(jìn)行判斷的場(chǎng)景，更高效實(shí)用的方案還是使用LOOKUP函數(shù)來(lái)進(jìn)行處理。
LOOKUP函數(shù)語(yǔ)法規(guī)則
?LOOKUP函數(shù)有兩種不同的用法，比較常用的一種方式需要三個(gè)參數(shù)。
第一個(gè)參數(shù)是具體需要判斷查詢的目標(biāo)數(shù)值，比如某個(gè)員工的具體銷售額；
第二個(gè)參數(shù)是每個(gè)分段區(qū)間的下限數(shù)值所組成的一個(gè)數(shù)組，比如1000～2999、3000～4999、5000～9999這幾個(gè)分段區(qū)間的話，他們的區(qū)間下限就分別是1000、3000、5000這幾個(gè)數(shù)。把這幾個(gè)數(shù)組成數(shù)組，并且要求從小到大排列，就構(gòu)成了第二個(gè)參數(shù)；
第三個(gè)參數(shù)就是要給出每個(gè)區(qū)間所對(duì)應(yīng)的具體信息，比如目標(biāo)是查詢提成比例，那么剛才那幾個(gè)區(qū)間所對(duì)應(yīng)的幾個(gè)具體的提成比例，組成一個(gè)數(shù)組，就是它的第三參數(shù)。
這個(gè)LOOKUP函數(shù)的難點(diǎn)主要在原理的理解上，具體的參數(shù)設(shè)置其實(shí)并不復(fù)雜。把前面介紹的這些參數(shù)規(guī)則套在剛才那個(gè)具體的案例當(dāng)中，就不難寫出公式了，具體實(shí)現(xiàn)方法可以參考下面的圖片：
?D列當(dāng)中就是簡(jiǎn)簡(jiǎn)單單使用了LOOKUP函數(shù)，不需要一個(gè)個(gè)區(qū)間去人工判斷，它自動(dòng)就能夠?qū)N售業(yè)績(jī)?cè)诿總€(gè)區(qū)間里面進(jìn)行查詢判斷，最終得到所在區(qū)間所對(duì)應(yīng)的提成比例。
7級(jí)競(jìng)技場(chǎng)
?再往高處走，就到達(dá)本次分享當(dāng)中的最高層級(jí)7級(jí)競(jìng)技場(chǎng)了，但這并不意味著函數(shù)學(xué)習(xí)的終點(diǎn)。在這個(gè)層級(jí)當(dāng)中，你會(huì)接觸到一些更加冷門的函數(shù)，冷門意味著知道和使用的人不是那么多，并不代表函數(shù)本身沒(méi)有太大用處。
?
?女巫是骷髏軍團(tuán)的大Boss，在統(tǒng)計(jì)類的函數(shù)一族當(dāng)中，我們之前接觸過(guò)小骷髏COUNTIF、骷髏軍團(tuán)COUNTIFS以及帶盾牌的骷髏守衛(wèi)SUMPRODUCT函數(shù)。而現(xiàn)在，家族中的另一位成員FREQUENCY函數(shù)也體現(xiàn)出了更高級(jí)別的功能，它可以將數(shù)值分成多個(gè)區(qū)間分組，并且統(tǒng)計(jì)每個(gè)分組當(dāng)中的數(shù)值個(gè)數(shù)，也就是計(jì)算頻數(shù)。因此它會(huì)生成一組結(jié)果，而不是單一的結(jié)果；
TRIMMEAN函數(shù)，學(xué)名叫做修剪平均值，通俗的講就是可以在去除一些極大值和極小值之后，再來(lái)計(jì)算平均數(shù)，在體育比賽例如跳水、體操這類比賽的打分當(dāng)中經(jīng)常能看到這種評(píng)分方式，相當(dāng)于小皮卡AVERAGE平均值函數(shù)的升級(jí)版，因此我用大皮卡來(lái)代表它。這個(gè)函數(shù)在計(jì)算平均值時(shí)可以去除一些異常數(shù)據(jù)的干擾，對(duì)于數(shù)據(jù)分析工作很有幫助，在招投標(biāo)等一些場(chǎng)景當(dāng)中也很有使用價(jià)值；
PERCENTRANK函數(shù)，RANK排名函數(shù)的兄弟，它可以用百分比的形式來(lái)體現(xiàn)排名位置；
MMULT函數(shù)，又被稱為MM函數(shù)，因此我用女武神作為它的形象代言人。它的作用是對(duì)數(shù)組進(jìn)行矩陣乘法運(yùn)算，聽(tīng)著就很高大上，在一些復(fù)雜的統(tǒng)計(jì)處理當(dāng)中可以發(fā)揮作用。但是這個(gè)函數(shù)在學(xué)習(xí)和參數(shù)構(gòu)造上都有比較高的難度，因此實(shí)際使用者寥寥；
HYPERLINK函數(shù)，一個(gè)非常另類的函數(shù)，絕大部分函數(shù)都是在單元格中得到一個(gè)具體的數(shù)據(jù)結(jié)果，而這個(gè)函數(shù)卻可以在單元格當(dāng)中產(chǎn)生一個(gè)超級(jí)鏈接，鏈接到其他位置、其他文件甚至鏈接到網(wǎng)頁(yè)等等。就好像非常另類的狂暴樵夫卡牌，游戲中的大部分卡牌我們都希望能夠活得越久越好，而使用樵夫的時(shí)候，通常我們總是希望他能早點(diǎn)掛掉；
WEBSERVICE函數(shù)，同樣也是一個(gè)非常特殊的函數(shù)，從Excel 2013版開(kāi)始才提供了這個(gè)函數(shù)。它的作用是可以直接從網(wǎng)絡(luò)上動(dòng)態(tài)的獲取數(shù)據(jù)。比如有些網(wǎng)站提供了查詢股票信息的網(wǎng)絡(luò)服務(wù)，將服務(wù)地址填寫在這個(gè)函數(shù)的參數(shù)當(dāng)中，通過(guò)這個(gè)函數(shù)就能實(shí)時(shí)獲取到相應(yīng)的股票數(shù)據(jù)信息（例如：=WEBSERVICE("http://hq.sinajs.cn/list=s_sh000001")）。這是非常有想象力的一個(gè)函數(shù)，它實(shí)現(xiàn)了網(wǎng)絡(luò)數(shù)據(jù)與Excel之間的無(wú)縫鏈接，借助它可以實(shí)現(xiàn)許多自動(dòng)化智能化的實(shí)時(shí)數(shù)據(jù)查詢解決方案，而在以往，這樣的功能往往需要通過(guò)VBA編程才能實(shí)現(xiàn)。
由于時(shí)間關(guān)系，這個(gè)層級(jí)的函數(shù)我就不再具體舉例詳細(xì)說(shuō)明了。
下面我們可以換個(gè)角度再來(lái)回顧一下今天提到的幾個(gè)函數(shù)：
函數(shù)中的“升級(jí)版”
?有些函數(shù)功能和用法比較相近，但能力上會(huì)有些差異，其中一個(gè)函數(shù)可以看作是另外一個(gè)函數(shù)的升級(jí)版，例如圖上所提到的這四對(duì)，能力上都分別有所提升，有些甚至可以替代低級(jí)版的函數(shù)。
七大查詢引用類函數(shù)
?查詢引用類函數(shù)所包含的七大函數(shù)是Excel當(dāng)中最重要的函數(shù)代表之一，掌握他們就至少掌握了半個(gè)函數(shù)江湖。
以上就是今天我為大家?guī)?lái)的函數(shù)功力七大層級(jí)的猜想和一些函數(shù)的推薦和介紹，希望能夠幫助大家查漏補(bǔ)缺、按圖索驥。Excel當(dāng)中總共有400多個(gè)函數(shù)，全部都去學(xué)習(xí)和了解既不現(xiàn)實(shí)也沒(méi)必要，職場(chǎng)當(dāng)中常用的函數(shù)其實(shí)也就是這樣四五十個(gè)。掌握和熟練運(yùn)用這些函數(shù)，就能讓你在大部分工作當(dāng)中游刃有余。
經(jīng)常有朋友會(huì)詢問(wèn)如何學(xué)習(xí)函數(shù)，經(jīng)常抱怨記不住等等。在這里我也給出一些學(xué)好函數(shù)的建議：
?首先第一條，建議通過(guò)聯(lián)想式的方式來(lái)記憶函數(shù)名稱以及他們的作用。就好比今天這樣，我把函數(shù)跟游戲結(jié)合起來(lái)，把有關(guān)聯(lián)的函數(shù)放到一起來(lái)介紹，通過(guò)這樣的一些聯(lián)想式記憶，可以幫助你更好的記住函數(shù)名稱，可以幫你更好的理解這些函數(shù)的作用和相關(guān)性；
第二條，深刻理解函數(shù)的參數(shù)語(yǔ)法規(guī)則。每個(gè)函數(shù)都有特定的參數(shù)語(yǔ)法規(guī)則，其中包括參數(shù)的順序位置、參數(shù)的含義作用、甚至參數(shù)的類型等等。比如有的函數(shù)參數(shù)類型是ref，這就表示這個(gè)參數(shù)只能通過(guò)單元格地址引用來(lái)設(shè)定，而不能直接使用某個(gè)常量或使用另外某個(gè)函數(shù)的運(yùn)算結(jié)果。只有深刻理解了這些規(guī)則，才能正確而合理的運(yùn)用好函數(shù)。
第三條，建議使用一些手段，去跟蹤了解公式運(yùn)算的中間過(guò)程。比如通過(guò)F9這個(gè)快捷鍵，在公式編輯欄當(dāng)中選中一段公式內(nèi)容，然后按F9快捷鍵，就可以看到這段公式的內(nèi)部運(yùn)算結(jié)果。通過(guò)這樣的方式就可以了解到公式運(yùn)算的中間過(guò)程。因?yàn)樵S多公式很長(zhǎng)，大都會(huì)由多個(gè)函數(shù)組合而成，通過(guò)這種方法可以查看到每一部分的中間運(yùn)算結(jié)果，這個(gè)對(duì)于整個(gè)公式的算法理解和編輯修改是非常有幫助的，有了這個(gè)手段以后可以讓函數(shù)的學(xué)習(xí)能力得到極大的提升。
當(dāng)然，學(xué)習(xí)函數(shù)公式也和游戲當(dāng)中練功升級(jí)一樣，并非一朝一夕之功，找到合適的學(xué)習(xí)方法，養(yǎng)成良好的學(xué)習(xí)習(xí)慣，經(jīng)常性的實(shí)踐操作和鍛煉，嘗試用函數(shù)公式去高效的解決問(wèn)題，這樣才能形成長(zhǎng)期的經(jīng)驗(yàn)積累，幫助你邁向更高的水平層次，迎接更大的挑戰(zhàn)。
