大家好我是波導(dǎo)終結(jié)者，這次跟大家分享一下10個(gè)實(shí)用的EXCEL技巧。跟那些爛大街的什么提取生日不一樣，可能會(huì)比較復(fù)雜，所以我會(huì)附上詳細(xì)的函數(shù)解釋、說明和思路。

使用環(huán)境以EXCEL2007默認(rèn)安裝為準(zhǔn)。強(qiáng)烈建議大家拋棄2003，因?yàn)樾赂袷奖扰f格式優(yōu)秀太多，這個(gè)放到最后講。

1.統(tǒng)計(jì)不重復(fù)項(xiàng)數(shù)

以前在開發(fā)ERP的時(shí)候，曾經(jīng)有一個(gè)需求，就是從龐大的數(shù)據(jù)中統(tǒng)計(jì)出SKU。當(dāng)時(shí)研究了半天，最后用Hashtable然后取其個(gè)數(shù)實(shí)現(xiàn)了，這個(gè)屬于編程范疇，就有點(diǎn)扯遠(yuǎn)了。

那如果我們?cè)贓XCEL中需要這么做，用什么函數(shù)可以做到呢？畢竟工具所限，不太可能用哈希表。

方法很簡(jiǎn)單：

=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

這個(gè)方法用到了兩個(gè)函數(shù)，一個(gè)是大家很熟悉的COUNTIF，另一個(gè)是沒怎么見過的SUMPRODUCT。

先來講SUMPRODUCT，這個(gè)函數(shù)拆開來看就是SUM和PRODUCT，即“把乘積求和”。

它接受的參數(shù)，是N個(gè)數(shù)組（重要），每個(gè)參數(shù)數(shù)組的大小必須是一樣的，然后這個(gè)函數(shù)就會(huì)把對(duì)應(yīng)的項(xiàng)先相乘，最后相加。

比如SUMPRODUCT(A1:A5,B1:B5)，那么就會(huì)計(jì)算A1*B1，然后是A2*B2……一直到A5*B5，最后相加。

而如果參數(shù)只有一個(gè)，那就沒得乘，直接變成簡(jiǎn)單的數(shù)組內(nèi)元素相加，我們利用的就是這一點(diǎn)。

接下來再來看COUNTIF。COUNTIF一般的應(yīng)用我們見過挺多，但是COUNTIF(B2:B15,B2:B15)這是個(gè)什么操作，條件竟然是個(gè)區(qū)域，而且與值域一樣？

對(duì)于這樣的寫法，COUNTIF會(huì)返回一個(gè)數(shù)組，里面存儲(chǔ)著B2在B2:B15中的個(gè)數(shù)，B3在B2:B15中的個(gè)數(shù)……類推。

這樣一來，這個(gè)值在范圍內(nèi)出現(xiàn)過N次，它在數(shù)組里也就會(huì)返回N次值，值還是為N。比如B2的“波導(dǎo)一”，它出現(xiàn)過3次，并且也被數(shù)到3次。

而1/COUNTIF(B2:B15,B2:B15)則會(huì)將1除以這個(gè)數(shù)組內(nèi)的每個(gè)N，作為一個(gè)新的數(shù)組返回。這樣，“波導(dǎo)一”出現(xiàn)3次，在數(shù)組里就會(huì)有3個(gè)1/3，“波導(dǎo)三”出現(xiàn)2次，就會(huì)有2個(gè)1/2……

大家發(fā)現(xiàn)了吧，N個(gè)的1/N相加，結(jié)果肯定是1。然后1的個(gè)數(shù)有幾個(gè)呢？四個(gè)。也即范圍內(nèi)不重復(fù)的項(xiàng)數(shù)。

2.快捷生成大寫數(shù)字

有時(shí)候需要生成大寫數(shù)字，如果自己一個(gè)一個(gè)敲還是很煩的，其實(shí)EXCEL有這么個(gè)函數(shù)：

NUMBERSTRING這個(gè)函數(shù)簡(jiǎn)直是本地化的典范，中文專用，第2個(gè)參數(shù)可以取1、2、3，效果直接在圖上演示了，就不湊字?jǐn)?shù)了。

不過這個(gè)函數(shù)也有缺點(diǎn)：不支持小數(shù)。

如果有小數(shù)的話，函數(shù)會(huì)自動(dòng)四舍五入取整，注意，會(huì)四舍五入。

一般情況下，我們的小數(shù)只有兩位，可以用上圖方式分別取出來，然后轉(zhuǎn)成大寫的伍和陸，后面自己手動(dòng)接X角X分。

或者直接把小數(shù)部分弄成整數(shù)，然后中間自己加“點(diǎn)”，變成一二三四點(diǎn)五六。

具體方法還有很多，看實(shí)際需求再具體改函數(shù)。

寫這點(diǎn)也是有感而發(fā)。一個(gè)是之前初入職場(chǎng)的時(shí)候手動(dòng)寫過這種函數(shù)，現(xiàn)在回頭來看蠢死了。

另一個(gè)就是提醒大家四舍五入一定要注意。以前我開發(fā)ERP的時(shí)候，就和公司里的財(cái)務(wù)扯過蛋。

之前公司里的折扣都是2位數(shù)，后來擴(kuò)展到3位數(shù)了，這時(shí)候問題出現(xiàn)：你要全程保持可見數(shù)值的精度，就得全程保留3位小數(shù)，這很好理解吧。

舉個(gè)最簡(jiǎn)單的例子：0.995+0.005=1.000，如果只保留兩位小數(shù)會(huì)出現(xiàn)什么問題呢？1.00+0.10=1.00或者1.10。為什么會(huì)或者？一個(gè)是后臺(tái)相加的實(shí)際值，一個(gè)是前臺(tái)已經(jīng)四舍五入過一次之后的值相加。如果前面已經(jīng)四舍五入過了，精度損失，這兩個(gè)數(shù)不可能兼得呀。

而當(dāng)時(shí)的財(cái)務(wù)卻要求：不將2位精度改為3位精度，同時(shí)結(jié)果既滿足后臺(tái)實(shí)際值，又滿足前臺(tái)可見值，而且還只能有一個(gè)結(jié)果。這明顯就不可能。說白了那個(gè)財(cái)務(wù)懶得一逼，啥也不想做罷了；而且也蠢得一逼，連EXCEL函數(shù)都不懂得改。

3.查找某行或者某列的特定值

VLOOKUP這個(gè)函數(shù)，很多人都有聽過，但經(jīng)常有人用不明白。

這函數(shù)說白了，從某個(gè)區(qū)域內(nèi)找到某個(gè)數(shù)，但是使用上卻有以下幾個(gè)要點(diǎn)：

1.VLOOKUP是豎著從參數(shù)2的范圍內(nèi)，找第一列值，如果想橫著找，請(qǐng)用HLOOKUP。

2.參數(shù)3返回對(duì)應(yīng)的，另一列的值。這個(gè)數(shù)字是范圍內(nèi)的第幾列，而不是整個(gè)表格的第幾列。當(dāng)然你可以試試設(shè)為1……另外，這個(gè)數(shù)必須為正數(shù)，不能反著找。你可以把目標(biāo)列復(fù)制一列，放后頭隱藏起來。

3.參數(shù)4設(shè)為FALSE為精確匹配，TRUE為近似匹配。然而，近似匹配卻有兩個(gè)弱點(diǎn)，如上圖▲

近似匹配時(shí)，第一列必須為升序排列，否則報(bào)錯(cuò)。數(shù)值的話好理解，字符串就會(huì)有些頭痛。

另外，近似匹配很容易得到無法預(yù)料的效果。不管是字符串還是數(shù)字，它取的都是“相近”的值，而這個(gè)相近很容易得到你不想要的結(jié)果。所以一概建議大家使用精確匹配。

另外要注意，字符串前后有空格，或者查找數(shù)字但目標(biāo)區(qū)域是字符串格式都會(huì)導(dǎo)致得到錯(cuò)誤結(jié)果，一定要檢查仔細(xì)。

4.VLOOKUP的高級(jí)應(yīng)用

首先我們來看多重查找。比如現(xiàn)在東哥想找出所有不能拼命的員工，列成一個(gè)表，或者丁哥想把所有患重病的員工找出來，列成一個(gè)表。

這個(gè)需求我們當(dāng)然可以直接用現(xiàn)成的篩選或者過濾來做，但是這樣有時(shí)候會(huì)破壞原表格。而且有的領(lǐng)導(dǎo)不太會(huì)用EXCEL，到時(shí)候亂搞一通，顯示結(jié)果亂了，咱又得背鍋。

這里我們用添加輔助列的方式來做。輔助列也是學(xué)好EXCEL必備的方法，有點(diǎn)類似數(shù)學(xué)題里的輔助線。有的題不加，還能做，有的題不加還真的做不了。

A列和H列分別為公式文本。

首先看一下這個(gè)：(D2=$F$2)+B1。利用到了EXCEL里，TRUE為1，F(xiàn)ALSE為0的特性。如果是男員工，則數(shù)字加1，如果不是，就一直保持之前的數(shù)字。而絕對(duì)引用和相對(duì)引用這些我真的不想再說一遍了。

這樣，我們?cè)贐列就生成了一個(gè)數(shù)組，每個(gè)目標(biāo)行的數(shù)值都會(huì)比之前的大1。

再來看：IFERROR(VLOOKUP(ROW(B1),B1:D$6,2,0),"無")。IFERROR只是為了防止、過濾報(bào)錯(cuò)結(jié)果，你可以填成空字符串，這樣結(jié)果就直接可拷走。

ROW(B1)返回1，ROW(B2)返回2，往下拉類推。而查找1，就是找到第一個(gè)目標(biāo)員工。

下拉之后，ROW(B2)返回2，B1:D$6變?yōu)锽2:D$6，即從剩下的單元格中，查找第2個(gè)目標(biāo)員工。以此類推。

第二個(gè)，通配符查找。剛才我提到過了，用近似匹配很難得到你想要的值，但是你想要模糊查找怎么辦呢？

很簡(jiǎn)單，VLOOKUP支持通配符，比如我在后面加個(gè)問號(hào)，查找的就是“波導(dǎo)1”后面再跟一個(gè)字符的數(shù)值。問號(hào)代表一個(gè)，星號(hào)代表任意，這些DOS時(shí)代過來的了，不再多講。

最后再來分享一下反向查找。剛才提過，VLOOKUP不支持反向查找，前提是不用其他函數(shù)做輔助處理。

這里我們用：VLOOKUP(C12,CHOOSE({2,1},C2:C6,D2:D6),2,FALSE)。核心要點(diǎn)在CHOOSE函數(shù)，說白了就是把第二列先返回，再返回第一列，則生成一個(gè)臨時(shí)表，性別列排在名字列前面。

然后我們就找出第一個(gè)女員工了。

個(gè)人不推薦這么做，很容易亂，后面如果改個(gè)東西，函數(shù)就很麻煩，還是輔助列好用。

VLOOKUP可以嵌套非常多函數(shù)，根據(jù)使用場(chǎng)景來實(shí)際操作比較直觀，有需要的可以關(guān)注點(diǎn)贊，留個(gè)言。

5.數(shù)據(jù)透視表

首先我們來看一下這張表。只是演示效果，所以就隨便打了一些數(shù)據(jù)。

之前開發(fā)ERP的時(shí)候，對(duì)于報(bào)表就有一個(gè)非常強(qiáng)烈的需求：數(shù)據(jù)透視表。源頭就是EXCEL的這個(gè)功能。

當(dāng)然有人可能會(huì)問，為什么不讓他們自己拉EXCEL呢？呃，是這樣的，當(dāng)時(shí)數(shù)據(jù)輕輕松上億條，EXCEL怕是……

回到正題，我們選定一個(gè)范圍的數(shù)據(jù)之后，點(diǎn)擊插入，數(shù)據(jù)透視表，確定。

簡(jiǎn)單的拖拉，我們就能得到這么一張匯總表：所有男鞋、女鞋、配件分別求和。

再簡(jiǎn)單的拖拉，又能生成另一張表：按年匯總，品名列成小項(xiàng)，可折疊。如果把品名和年份位置對(duì)調(diào)，就是品名匯總，年份折疊。

這玩藝兒用來應(yīng)付那些一會(huì)兒要看這個(gè)表，一會(huì)兒要看那個(gè)表的領(lǐng)導(dǎo)非常好用。我總不可能天天蹲著給你做表格吧？給你一個(gè)數(shù)據(jù)透視，自己玩去。

如果想要開發(fā)控件，我當(dāng)年用的是DevExpress，非常強(qiáng)大，別無二選。就是有點(diǎn)貴，不過方法總是有的你懂的~

6.幾種排名方法

這個(gè)之前有人問過，今天把幾種情況一起寫了。

首先是順位排名，也就是不管前面有沒有并列，真實(shí)反應(yīng)該人的名次。

這個(gè)很好解決，EXCEL自帶RANK函數(shù)。但如果我們要讓并列的人不占用名次，或者說不管并列多少名，不讓排名數(shù)字有空檔呢？比如100個(gè)人里，99個(gè)都考了100分，則考了98分的人，是第100名，還是第2名？

這里我要事先說一下，此處的前提是不對(duì)數(shù)據(jù)進(jìn)行排序，我們要在不動(dòng)到之前數(shù)據(jù)的前提下來做。不要問我為什么，一問就說明你還沒經(jīng)歷職場(chǎng)……如果能排序，那也沒啥好講的了~

函數(shù)不難：=SUMPRODUCT(($B$2:$B$7>B7)/COUNTIF($B$2:$B$7,$B$2:$B$7))+1。思路跟第1節(jié)的去重是一樣的，不再重復(fù)解釋。

那如果有多個(gè)數(shù)值，在并列的時(shí)候需要做第二次排序呢？

函數(shù)如下：RANK(B2,$B$2:$B$7)+SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7>C2))。

思路也很簡(jiǎn)單，先取得真實(shí)名次，然后數(shù)出與其分?jǐn)?shù)并列、第二排序列大于它的單元格個(gè)數(shù)，也就是這一格需要往后退（名次數(shù)值加上）的數(shù)值了。

還是這個(gè)函數(shù)。記住SUMPRODUCT這個(gè)函數(shù)哦。

7.制作下拉菜單

有的時(shí)候，一些場(chǎng)合我們并不需要讓用戶自由輸入，而是希望有個(gè)下拉菜單，提供現(xiàn)成的選項(xiàng)直接選項(xiàng)，這樣既快捷，又避免輸入錯(cuò)誤，不使用VBA控件可以實(shí)現(xiàn)嗎？

可以的，這個(gè)功能在EXCEL2007里叫“數(shù)據(jù)有效性”，2010之后的版本叫“數(shù)據(jù)驗(yàn)證”。在數(shù)據(jù)有效性功能內(nèi)，選擇“序列”，并且指定之前輸好的固定值，就可以了。

很典型的一個(gè)應(yīng)用，省份選擇，我們?cè)诩?、收快遞的時(shí)候，都會(huì)讓你選一個(gè)菜單。

那么，如果要做多級(jí)菜單呢？比如省、市、區(qū)這樣的？當(dāng)然也可以。

不過要先說一句，EXCEL2007做這個(gè)比較麻煩，我只是告訴大家舊版本的實(shí)現(xiàn)方法。有用新版本的朋友肯定是更方便了。

一級(jí)菜單的做法不變，第二級(jí)菜單的話，我們需要先把它的下級(jí)預(yù)填出來，比如“北京”下面有某幾個(gè)區(qū)。

然后，以“北京”為頭選中這個(gè)區(qū)域，公式，定義名稱。在這里，2007版只能一個(gè)一個(gè)來，并且不能自動(dòng)排除表頭，所以我們得一個(gè)個(gè)手動(dòng)。如果更高版本的朋友就有福了，EXCEL提供更多選項(xiàng)，可以直接指定表頭，把整個(gè)區(qū)域一次性做進(jìn)去。

8.用錄制宏完成高級(jí)功能

上一節(jié)提到2007里，公式需要一個(gè)個(gè)手動(dòng)點(diǎn)，而更高版本可以整個(gè)區(qū)域生成。那么我們有沒有辦法在舊版本里批量做呢？有的。

請(qǐng)先記住一句話：所有功能，其本質(zhì)都是宏（VBA代碼），我們可以錄制、編輯，實(shí)現(xiàn)自己的高級(jí)功能。

首先，我們需要調(diào)出“開發(fā)工具”選項(xiàng)卡。因?yàn)橐话闳擞貌坏?，出于安全考慮，默認(rèn)是不顯示的。

然后，我們把剛才的單次操作錄制成一個(gè)宏。錄制方法也很簡(jiǎn)單，先切換到開發(fā)工具，點(diǎn)擊錄制宏。

然后你就正常操作。操作完了之后，點(diǎn)擊止錄制。

這時(shí)候切換到VBA界面，我們便可以看到剛才的代碼，竟然只有兩行，比鼠標(biāo)點(diǎn)擊的次數(shù)還要少。

但是這時(shí)候我們并沒有辦法直接用，因?yàn)槲覀円雠?。比如，它這里的“北京”是寫死的，我們必須讓代碼自動(dòng)取值，等等。

把代碼稍加改造，這里我范例只有三個(gè)，列從8到10，所以循環(huán)的下標(biāo)就從8到10。取得表頭的名稱之后，指定表身的部分即可。

最后我們點(diǎn)擊這個(gè)“播放”鍵，運(yùn)行這個(gè)改造過后的宏。

執(zhí)行完之后，我們來看一下名稱管理器，確認(rèn)一下有沒有哪里寫錯(cuò)。

用代碼的好處是什么呢？可以把很多批量的操作簡(jiǎn)化掉。比如我們?cè)诙?jí)菜單的基礎(chǔ)上，要做三級(jí)菜單。就算是新版本，你也得一個(gè)一個(gè)區(qū)域框選，因?yàn)橐粋€(gè)省有N個(gè)市，一個(gè)市又有M個(gè)縣，這樣就需要N*M次操作。而通過代碼，把數(shù)據(jù)布好局之后，只需要點(diǎn)一下，不管來多少數(shù)據(jù)，我們都不需要一次一次手動(dòng)操作了。

9.制作自定義函數(shù)

既然用到了一點(diǎn)VBA，那么最強(qiáng)大的是什么呢？當(dāng)然是自定義函數(shù)了。

自定義函數(shù)你就可以脫離EXCEL內(nèi)置函數(shù)的限制，幾乎想做什么就能做什么。具體要怎么干，就看每個(gè)人需求了。

這里簡(jiǎn)單跟大家分享一下。首先，函數(shù)一定要寫在模塊里面，函數(shù)一定要寫在模塊里面，函數(shù)一定要寫在模塊里面。

第二，函數(shù)前面加Public以供外部調(diào)用。

第三，VBA用bdzjz_1 = s這樣的方式來返回值（其他語言比較常見的是return xxx）

第四，VBA的語法是弱屬性，變量可以不聲明類型。

函數(shù)寫好之后我們可以來測(cè)試一下。在表格中鍵入等號(hào)，后面跟自定義函數(shù)名，如果成功的話可以看到完整函數(shù)名的提示。

這里只是簡(jiǎn)單的將參數(shù)1和參數(shù)2中間連接起“住在”，最主要的還是知道自定義函數(shù)的方法。因?yàn)榈搅诵枰远x函數(shù)階段的時(shí)候，都是需求各異。

10.為什么叫你們拋棄2003格式

之所以聊到這個(gè)，主要是前段時(shí)間某群里有某人是這么說的：2003和2007的格式其實(shí)就是改個(gè)后輟騙人而已，內(nèi)容是完全一樣的，我的2003改個(gè)后輟就能打開2007的文件。

這句話犯了幾個(gè)很嚴(yán)重的錯(cuò)誤：

1.Office 2007最大的進(jìn)步就是格式上的進(jìn)步。舊的2003格式太易損壞，一旦出問題修復(fù)率幾乎為零。

2.2003能打開2007的文檔，只是因?yàn)楝F(xiàn)在很多集成安裝包帶了兼容插件。就算如此，也只是能兼容常用內(nèi)容，一些新特性根本用不了，也保存不了。

3.微軟沒傻到單純依靠后輟名來判斷文件類型，事實(shí)上，大部分軟件都沒這么傻~

不信我們來看看xls文件和xlsx文件的文件頭，雖然看不懂，但是“明顯不一樣”這一點(diǎn)是可以確定的。

而且在xlsx的文件頭，我們可以看到xml這樣的字樣。有經(jīng)驗(yàn)的朋友應(yīng)該很熟悉了，對(duì)吧？

簡(jiǎn)單來說，舊的2003格式是緊實(shí)的16進(jìn)制內(nèi)容，一旦損壞基本就沒救了，有救過的朋友應(yīng)該都感同身受。

而2007格式呢？

我們用WinRAR強(qiáng)行打開xlsx文件來看看，對(duì)沒錯(cuò)，用WinRAR強(qiáng)行打開xlsx文件。

2007的格式都以XML，說簡(jiǎn)陋點(diǎn)就是文本形式存儲(chǔ)，然后用弱校驗(yàn)的類ZIP壓縮。如果有損壞，則只會(huì)造成很少的數(shù)據(jù)損失。

更通俗點(diǎn)來講，2007的格式相當(dāng)于一個(gè)記事本文件，或者視頻文件。一小點(diǎn)地方壞了，結(jié)果就是一點(diǎn)亂碼或者花屏。

而2003格式一旦壞了，就像你安裝游戲的時(shí)候，安裝文件損壞……慘遭GG。

微軟給2003出兼容補(bǔ)丁是實(shí)屬無奈，沒想到十幾年后，還有人抱著極易損壞的舊版本和舊格式不放。但我也明白，不是每個(gè)人都能換上最新版或者上Office365，所以折中一下用2007版本來演示，格式問題這是底限。我不是詛咒你們，但是萬一辛辛苦苦做了幾個(gè)月的PPT，或者積累了幾年的數(shù)據(jù)突然損壞的時(shí)候，能不能救回來就在此一舉了。

好的，感謝大家觀看，我是波導(dǎo)終結(jié)者，喜歡的朋友請(qǐng)點(diǎn)個(gè)關(guān)注和贊吧，有什么疑問歡迎留言，我們下期再見。