10個Excel實用技巧

今天給大家分享10個實用的EXCEL技巧。跟那些爛大街的什麼提取生日不一樣，可能會比較複雜，所以我會附上詳細的函數解釋、說明和思路。使用環境以EXCEL2007默認安裝為準。強烈建議大家拋棄2003，因為新格式比舊格式優秀太多，這個放到最後講。

統計不重複項數

以前在開發ERP的時候，曾經有一個需求，就是從龐大的數據中統計出SKU。當時研究了半天，最後用Hashtable然後取其個數實現了，這個屬於編程範疇，就有點扯遠了。那如果我們在EXCEL中需要這麼做，用什麼函數可以做到呢？畢竟工具所限，不太可能用哈希表。方法很簡單：

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

這個方法用到了兩個函數，一個是大家很熟悉的COUNTIF，另一個是沒怎麼見過的SUMPRODUCT。

SUMPRODUCT函數拆開來看就是SUM和PRODUCT，即“把乘積求和”。它接受的參數，是N個數組（重要），每個參數數組的大小必須是一樣的，然後這個函數就會把對應的項先相乘，最後相加。

比如SUMPRODUCT(A1:A5,B1:B5)，那麼就會計算A1*B1，然後是A2*B2……一直到A5*B5，最後相加。而如果參數只有一個，那就沒得乘，直接變成簡單的數組內元素相加，我們利用的就是這一點。

接下來再來看COUNTIF。COUNTIF一般的應用我們見過挺多，但是COUNTIF(B2:B15,B2:B15)這是個什麼操作，條件竟然是個區域，而且與值域一樣？對於這樣的寫法，COUNTIF會返回一個數組，裡面存儲著B2在B2:B15中的個數，B3在B2:B15中的個數……類推。這樣一來，這個值在範圍內出現過N次，它在數組裡也就會返回N次值，值還是為N。比如B2的“波導一”，它出現過3次，並且也被數到3次。

而1/COUNTIF(B2:B15,B2:B15)則會將1除以這個數組內的每個N，作為一個新的數組返回。這樣“波導一”出現3次，在數組裡就會有3個1/3，“波導三”出現2次，就會有2個1/2……大家發現了吧，N個的1/N相加，結果肯定是1。然後1的個數有幾個呢？四個。也即範圍內不重複的項數。

NUMBERSTRING函數

快捷生成大寫數字有時候需要生成大寫數字，如果自己一個一個敲還是很煩的，其實EXCEL有這麼個函數：

NUMBERSTRING這個函數簡直是本地化的典範，中文專用，第2個參數可以取1、2、3，效果直接在圖上演示了，就不湊字數了。不過這個函數也有缺點：不支持小數。

如果有小數的話，函數會自動四捨五入取整，注意，會四捨五入。一般情況下，我們的小數只有兩位，可以用上圖方式分別取出來，然後轉成大寫的伍和陸，後面自己手動接X角X分。或者直接把小數部分弄成整數，然後中間自己加“點”，變成

VLOOKUP函數

查找某行或者某列的特定值VLOOKUP這個函數，很多人都有聽過，但經常有人用不明白。

這函數說白了，從某個區域內找到某個數，但是使用上卻有以下幾個要點：

• VLOOKUP是豎著從參數2的範圍內，找第一列值。如果想橫著找，請用HLOOKUP。

• 參數3返回對應的，另一列的值。這個數字是範圍內的第幾列，而不是整個表格的第幾列。當然你可以試試設為1……另外，這個數必須為正數，不能反著找。你可以把目標列複製一列，放後頭隱藏起來。

• 參數4設為FALSE為精確匹配，TRUE為近似匹配。然而，近似匹配卻有兩個弱點，如上圖

近似匹配時，第一列必須為升序排列，否則報錯。數值的話好理解，字符串就會有些頭痛。另外，近似匹配很容易得到無法預料的效果。不管是字符串還是數字，它取的都是“相近”的值，而這個相近很容易得到你不想要的結果。所以一概建議大家使用精確匹配。

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

VLOOKUP的高級應用

1. 多重查找

比如現在我想找出所有不能拼命的員工，列成一個表，或者想把所有患重病的員工找出來，列成一個表。這個需求我們當然可以直接用現成的篩選或者過濾來做，但是這樣有時候會破壞原表格。而且有的領導不太會用EXCEL，到時候亂搞一通，顯示結果亂了，咱又得背鍋。

這裡我們用添加輔助列的方式來做。輔助列也是學好EXCEL必備的方法，有點類似數學題裡的輔助線。有的題不加，還能做，有的題不加還真的做不了。A列和H列分別為公式文本。

首先看一下這個：(D2=$F$2)+B1。利用到了EXCEL裡，TRUE為1，FALSE為0的特性。如果是男員工，則數字加1，如果不是，就一直保持之前的數字。而絕對引用和相對引用這些我真的不想再說一遍了。這樣，我們在B列就生成了一個數組，每個目標行的數值都會比之前的大1。

再來看：IFERROR(VLOOKUP(ROW(B1),B1:D$6,2,0),"無")

IFERROR只是為了防止、過濾報錯結果，你可以填成空字符串，這樣結果就直接可拷走。ROW(B1)返回1，ROW(B2)返回2，往下拉類推。而查找1，就是找到第一個目標員工。下拉之後，ROW(B2)返回2，B1:D$6變為B2:D$6，即從剩下的單元格中，查找第2個目標員工。以此類推。

2. 通配符查找

剛才我提到過了，用近似匹配很難得到你想要的值，但是你想要模糊查找怎麼辦呢？很簡單，VLOOKUP支持通配符，比如我在後面加個問號，查找的就是“波導1”後面再跟一個字符的數值。問號代表一個，星號代表任意，這些DOS時代過來的了，不再多講。

3. 反向查找

剛才提過，VLOOKUP不支持反向查找，前提是不用其他函數做輔助處理。這裡我們用：

VLOOKUP可以嵌套非常多函數，根據使用場景來實際操作比較直觀。

數據透視表

首先我們來看一下這張表。只是演示效果，所以就隨便打了一些數據。之前開發ERP的時候，對於報表就有一個非常強烈的需求：數據透視表。源頭就是EXCEL的這個功能。我們選定一個範圍的數據之後，點擊插入，數據透視表，確定。

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

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

排名方法

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

首先是順位排名，也就是不管前面有沒有並列，真實反應該人的名次。這個很好解決，EXCEL自帶RANK函數。但如果我們要讓並列的人不佔用名次，或者說不管並列多少名，不讓排名數字有空檔呢？比如100個人裡，99個都考了100分，則考了98分的人，是第100名，還是第2名？

這裡我要事先說一下，此處的前提是不對數據進行排序，我們要在不動到之前數據的前提下來做。

函數不難：

=SUMPRODUCT(($B$2:$B$7>B7)/COUNTIF($B$2:$B$7,$B$2:$B$7))+1

思路跟第1節的去重是一樣的，不再重複解釋。

那如果有多個數值，在並列的時候需要做第二次排序呢？函數如下：

RANK(B2,$B$2:$B$7)+SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7>C2))

思路也很簡單，先取得真實名次，然後數出與其分數並列、第二排序列大於它的單元格個數，也就是這一格需要往後退（名次數值加上）的數值了。還是這個函數。記住SUMPRODUCT這個函數哦。

數據有效性

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

可以的，這個功能在EXCEL2007裡叫“數據有效性”，2010之後的版本叫“數據驗證”。在數據有效性功能內，選擇“序列”，並且指定之前輸好的固定值，就可以了。很典型的一個應用，省份選擇，我們在寄、收快遞的時候，都會讓你選一個菜單。

那麼，如果要做多級菜單呢？比如省、市、區這樣的？當然也可以。不過要先說一句，EXCEL2007做這個比較麻煩，我只是告訴大家舊版本的實現方法。有用新版本的朋友肯定是更方便了。

一級菜單的做法不變，第二級菜單的話，我們需要先把它的下級預填出來，比如“北京”下面有某幾個區。然後，以“北京”為頭選中這個區域，公式，定義名稱。在這裡，2007版只能一個一個來，並且不能自動排除表頭，所以我們得一個個手動。如果更高版本的朋友就有福了，EXCEL提供更多選項，可以直接指定表頭，把整個區域一次性做進去。

用錄製宏完成高級功能上一節提到2007裡，公式需要一個個手動點，而更高版本可以整個區域生成。那麼我們有沒有辦法在舊版本里批量做呢？有的。

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

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

然後，我們把剛才的單次操作錄製成一個宏。錄製方法也很簡單，先切換到開發工具，點擊錄製宏。然後你就正常操作。操作完了之後，點擊止錄製。

這時候切換到VBA界面，我們便可以看到剛才的代碼，竟然只有兩行，比鼠標點擊的次數還要少。但是這時候我們並沒有辦法直接用，因為我們要做批量。比如，它這裡的“北京”是寫死的，我們必須讓代碼自動取值，等等。

把代碼稍加改造，這裡我範例只有三個，列從8到10，所以循環的下標就從8到10。取得表頭的名稱之後，指定表身的部分即可。最後我們點擊這個“播放”鍵，運行這個改造過後的宏。

執行完之後，我們來看一下名稱管理器，確認一下有沒有哪裡寫錯。

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

製作自定義函數

既然用到了一點VBA，那麼最強大的是什麼呢？當然是自定義函數了。自定義函數你就可以脫離EXCEL內置函數的限制，幾乎想做什麼就能做什麼。具體要怎麼幹，就看每個人需求了。這裡簡單跟大家分享一下。

• 函數一定要寫在模塊裡面，函數一定要寫在模塊裡面，函數一定要寫在模塊裡面。

• 函數前面加Public以供外部調用。

• VBA用bdzjz_1 = s這樣的方式來返回值（其他語言比較常見的是return xxx）第四，VBA的語法是弱屬性，變量可以不聲明類型。

函數寫好之後我們可以來測試一下。在表格中鍵入等號，後面跟自定義函數名，如果成功的話可以看到完整函數名的提示。這裡只是簡單的將參數1和參數2中間連接起“住在”，最主要的還是知道自定義函數的方法。因為到了需要自定義函數階段的時候，都是需求各異。

拋棄Office2003吧

為什麼叫你們拋棄2003格式之所以聊到這個，主要是前段時間某群裡有某人是這麼說的：2003和2007的格式其實就是改個後輟騙人而已，內容是完全一樣的，我的2003改個後輟就能打開2007的文件。這句話犯了幾個很嚴重的錯誤：

• Office 2007最大的進步就是格式上的進步。舊的2003格式太易損壞，一旦出問題修復率幾乎為零。

• 2003能打開2007的文檔，只是因為現在很多集成安裝包帶了兼容插件。就算如此，也只是能兼容常用內容，一些新特性根本用不了，也保存不了。

• 微軟沒傻到單純依靠後輟名來判斷文件類型，事實上，大部分軟件都沒這麼傻。

不信我們來看看xls文件和xlsx文件的文件頭，雖然看不懂，但是“明顯不一樣”這一點是可以確定的。而且在xlsx的文件頭，我們可以看到xml這樣的字樣。有經驗的朋友應該很熟悉了，對吧？

簡單來說，舊的2003格式是緊實的16進制內容，一旦損壞基本就沒救了，有救過的朋友應該都感同身受。而2007格式呢？

我們用WinRAR強行打開xlsx文件來看看，對沒錯，用WinRAR強行打開xlsx文件。2007的格式都以XML，說簡陋點就是文本形式存儲，然後用弱校驗的類ZIP壓縮。如果有損壞，則只會造成很少的數據損失。

更通俗點來講，2007的格式相當於一個記事本文件，或者視頻文件。一小點地方壞了，結果就是一點亂碼或者花屏。而2003格式一旦壞了，就像你安裝遊戲的時候，安裝文件損壞……慘遭GG。

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

End.

零基礎入職數據分析就業班

課程的形式主要是“直播+錄播”

報名專享：課程項目作業+1v1班主任監督學習+愛數據學院學員專屬網站+班級答疑群

課程結束後能熟練掌握SQL、Python、Excel、PPT等工具

適合人群：

1.轉行（崗位相關，專業相關、對數據分析感興趣）

2.從事數據分析工作，但是需要提升技能以及增加實戰經驗

3.應屆畢業生入職數據分析

閱讀更多 愛數據網 的文章

關鍵字: Excel 跳槽那些事兒 實用技巧