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

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

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

以前在開(kāi)發(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ù)可以做到呢？畢竟工具所限，不太可能用哈希表。

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

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

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

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

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

這樣一來(lái)，這個(gè)值在范圍內(nèi)出現(xiàn)過(guò)N次，它在數(shù)組里也就會(huì)返回N次值，值還是為N。比如B2的“波導(dǎo)一”，它出現(xiàn)過(guò)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.快捷生成大寫(xiě)數(shù)字

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

6.幾種排名方法

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

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

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

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

函數(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)然也可以。

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

然后，以“北京”為頭選中這個(gè)區(qū)域，公式，定義名稱(chēng)。在這里，2007版只能一個(gè)一個(gè)來(lái)，并且不能自動(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ū)域生成。那么我們有沒(méi)有辦法在舊版本里批量做呢？有的。

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

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

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

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

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

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

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

執(zhí)行完之后，我們來(lái)看一下名稱(chēng)管理器，確認(rèn)一下有沒(méi)有哪里寫(xiě)錯(cuò)。

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

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

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

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

這里簡(jiǎn)單跟大家分享一下。

首先，函數(shù)一定要寫(xiě)在模塊里面，函數(shù)一定要寫(xiě)在模塊里面，函數(shù)一定要寫(xiě)在模塊里面。
第二，函數(shù)前面加Public以供外部調(diào)用。
第三，VBA用bdzjz_1 = s這樣的方式來(lái)返回值（其他語(yǔ)言比較常見(jiàn)的是return xxx）
第四，VBA的語(yǔ)法是弱屬性，變量可以不聲明類(lèi)型。

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

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

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

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

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

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

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

而2007格式呢？

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

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

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

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

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

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