實用到爆的10個EXCEL技巧    

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

使用環(huán)境以EXCEL2007默認安裝為準。強烈建議大家拋棄2003，因為新格式比舊格式優(yōu)秀太多，這個放到最后講。

1.統(tǒng)計不重復項數(shù)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這樣，我們在B列就生成了一個數(shù)組，每個目標行的數(shù)值都會比之前的大1。

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

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

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

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

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

然后我們就找出第一個女員工了。

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

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

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

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

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

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

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

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

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

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

6.幾種排名方法

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

首先是順位排名，也就是不管前面有沒有并列，真實反應該人的名次。

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

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

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

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

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

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

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

7.制作下拉菜單

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

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

很典型的一個應用，省份選擇，我們在寄、收快遞的時候，都會讓你選一個菜單。

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

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

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

8.用錄制宏完成高級功能

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

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

首先，我們需要調(diào)出“開發(fā)工具”選項卡。因為一般人用不到，出于安全考慮，默認是不顯示的。

然后你就正常操作。操作完了之后，點擊止錄制。

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

但是這時候我們并沒有辦法直接用，因為我們要做批量。比如，它這里的“北京”是寫死的，我們必須讓代碼自動取值，等等。

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

最后我們點擊這個“播放”鍵，運行這個改造過后的宏。

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

用代碼的好處是什么呢？可以把很多批量的操作簡化掉。比如我們在二級菜單的基礎上，要做三級菜單。就算是新版本，你也得一個一個區(qū)域框選，因為一個省有N個市，一個市又有M個縣，這樣就需要N*M次操作。而通過代碼，把數(shù)據(jù)布好局之后，只需要點一下，不管來多少數(shù)據(jù)，我們都不需要一次一次手動操作了。

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

既然用到了一點VBA，那么最強大的是什么呢？當然是自定義函數(shù)了。

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

這里簡單跟大家分享一下。

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

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

這里只是簡單的將參數(shù)1和參數(shù)2中間連接起“住在”，最主要的還是知道自定義函數(shù)的方法。因為到了需要自定義函數(shù)階段的時候，都是需求各異。

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

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

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

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

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

而2007格式呢？

我們用WinRAR強行打開xlsx文件來看看，對沒錯，用WinRAR強行打開xlsx文件。

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

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

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

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

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