實用到爆的10個EXCEL技巧

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

使用環境以EXCEL2007默認安裝為準。強烈建議大傢拋棄2003,因為新格式比舊格式優秀太多,這個放到最後講。

1.統計不重復項數

實用到爆的10個EXCEL技巧

以前在開發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,最後相加。

而如果參數隻有一個,那就沒得乘,直接變成簡單的數組內元素相加,我們利用的就是這一點。

實用到爆的10個EXCEL技巧

接下來再來看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的個數有幾個呢?四個。也即范圍內不重復的項數。

2.快捷生成大寫數字

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

實用到爆的10個EXCEL技巧

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

不過這個函數也有缺點:不支持小數。

實用到爆的10個EXCEL技巧

如果有小數的話,函數會自動四舍五入取整,註意,會四舍五入。

一般情況下,我們的小數隻有兩位,可以用上圖方式分別取出來,然後轉成大寫的伍和陸,後面自己手動接X角X分。

或者直接把小數部分弄成整數,然後中間自己加“點”,變成一二三四點五六。

具體方法還有很多,看實際需求再具體改函數。

寫這點也是有感而發。一個是之前初入職場的時候手動寫過這種函數,現在回頭來看蠢死瞭。

另一個就是提醒大傢四舍五入一定要註意。以前我開發ERP的時候,就和公司裡的財務扯過蛋。

之前公司裡的折扣都是2位數,後來擴展到3位數瞭,這時候問題出現:你要全程保持可見數值的精度,就得全程保留3位小數,這很好理解吧。

舉個最簡單的例子:0.995+0.005=1.000,如果隻保留兩位小數會出現什麼問題呢?1.00+0.10=1.00或者1.10。為什麼會或者?一個是後臺相加的實際值,一個是前臺已經四舍五入過一次之後的值相加。如果前面已經四舍五入過瞭,精度損失,這兩個數不可能兼得呀。

而當時的財務卻要求:不將2位精度改為3位精度,同時結果既滿足後臺實際值,又滿足前臺可見值,而且還隻能有一個結果。這明顯就不可能。說白瞭那個財務懶得一逼,啥也不想做罷瞭;而且也蠢得一逼,連EXCEL函數都不懂得改。

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

VLOOKUP這個函數,很多人都有聽過,但經常有人用不明白。

實用到爆的10個EXCEL技巧

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

1.VLOOKUP是豎著從參數2的范圍內,找第一列值,如果想橫著找,請用HLOOKUP。

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

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

實用到爆的10個EXCEL技巧

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

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

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

4.VLOOKUP的高級應用

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

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

實用到爆的10個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個目標員工。以此類推。

實用到爆的10個EXCEL技巧

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

很簡單,VLOOKUP支持通配符,比如我在後面加個問號,查找的就是“波導1”後面再跟一個字符的數值。問號代表一個,星號代表任意,這些DOS時代過來的瞭,不再多講。

實用到爆的10個EXCEL技巧

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

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

然後我們就找出第一個女員工瞭。

個人不推薦這麼做,很容易亂,後面如果改個東西,函數就很麻煩,還是輔助列好用。

VLOOKUP可以嵌套非常多函數,根據使用場景來實際操作比較直觀,有需要的可以關註點贊,留個言。

5.數據透視表

實用到爆的10個EXCEL技巧

首先我們來看一下這張表。隻是演示效果,所以就隨便打瞭一些數據。

之前開發ERP的時候,對於報表就有一個非常強烈的需求:數據透視表。源頭就是EXCEL的這個功能。

當然有人可能會問,為什麼不讓他們自己拉EXCEL呢?呃,是這樣的,當時數據輕輕松上億條,EXCEL怕是……

回到正題,我們選定一個范圍的數據之後,點擊插入,數據透視表,確定。

實用到爆的10個EXCEL技巧

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

實用到爆的10個EXCEL技巧

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

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

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

6.幾種排名方法

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

實用到爆的10個EXCEL技巧

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

這個很好解決,EXCEL自帶RANK函數。但如果我們要讓並列的人不占用名次,或者說不管並列多少名,不讓排名數字有空檔呢?比如100個人裡,99個都考瞭100分,則考瞭98分的人,是第100名,還是第2名?

這裡我要事先說一下,此處的前提是不對數據進行排序,我們要在不動到之前數據的前提下來做。不要問我為什麼,一問就說明你還沒經歷職場……如果能排序,那也沒啥好講的瞭~

實用到爆的10個EXCEL技巧

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

實用到爆的10個EXCEL技巧

那如果有多個數值,在並列的時候需要做第二次排序呢?

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

思路也很簡單,先取得真實名次,然後數出與其分數並列、第二排序列大於它的單元格個數,也就是這一格需要往後退(名次數值加上)的數值瞭。

還是這個函數。記住SUMPRODUCT這個函數哦。

7.制作下拉菜單

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

實用到爆的10個EXCEL技巧

可以的,這個功能在EXCEL2007裡叫“數據有效性”,2010之後的版本叫“數據驗證”。在數據有效性功能內,選擇“序列”,並且指定之前輸好的固定值,就可以瞭。

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

實用到爆的10個EXCEL技巧

那麼,如果要做多級菜單呢?比如省、市、區這樣的?當然也可以。

不過要先說一句,EXCEL2007做這個比較麻煩,我隻是告訴大傢舊版本的實現方法。有用新版本的朋友肯定是更方便瞭。

一級菜單的做法不變,第二級菜單的話,我們需要先把它的下級預填出來,比如“北京”下面有某幾個區。

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

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

上一節提到2007裡,公式需要一個個手動點,而更高版本可以整個區域生成。那麼我們有沒有辦法在舊版本裡批量做呢?有的。

請先記住一句話:所有功能,其本質都是宏(VBA代碼),我們可以錄制、編輯,實現自己的高級功能。

實用到爆的10個EXCEL技巧

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

實用到爆的10個EXCEL技巧

然後,我們把剛才的單次操作錄制成一個宏。錄制方法也很簡單,先切換到開發工具,點擊錄制宏。

然後你就正常操作。操作完瞭之後,點擊止錄制。

實用到爆的10個EXCEL技巧

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

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

實用到爆的10個EXCEL技巧

把代碼稍加改造,這裡我范例隻有三個,列從8到10,所以循環的下標就從8到10。取得表頭的名稱之後,指定表身的部分即可。

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

實用到爆的10個EXCEL技巧

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

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

9.制作自定義函數

實用到爆的10個EXCEL技巧

既然用到瞭一點VBA,那麼最強大的是什麼呢?當然是自定義函數瞭。

自定義函數你就可以脫離EXCEL內置函數的限制,幾乎想做什麼就能做什麼。具體要怎麼幹,就看每個人需求瞭。

這裡簡單跟大傢分享一下。首先,函數一定要寫在模塊裡面,函數一定要寫在模塊裡面,函數一定要寫在模塊裡面。

第二,函數前面加Public以供外部調用。

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

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

實用到爆的10個EXCEL技巧

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

這裡隻是簡單的將參數1和參數2中間連接起“住在”,最主要的還是知道自定義函數的方法。因為到瞭需要自定義函數階段的時候,都是需求各異。

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

之所以聊到這個,主要是前段時間某群裡有某人是這麼說的:2003和2007的格式其實就是改個後輟騙人而已,內容是完全一樣的,我的2003改個後輟就能打開2007的文件。

這句話犯瞭幾個很嚴重的錯誤:

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

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

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

實用到爆的10個EXCEL技巧

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

而且在xlsx的文件頭,我們可以看到xml這樣的字樣。有經驗的朋友應該很熟悉瞭,對吧?

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

而2007格式呢?

實用到爆的10個EXCEL技巧

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

2007的格式都以XML,說簡陋點就是文本形式存儲,然後用弱校驗的類ZIP壓縮。如果有損壞,則隻會造成很少的數據損失。

更通俗點來講,2007的格式相當於一個記事本文件,或者視頻文件。一小點地方壞瞭,結果就是一點亂碼或者花屏。

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

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

實用到爆的10個EXCEL技巧

好的,感謝大傢觀看,我是波導終結者,喜歡的朋友請點個關註和贊吧,有什麼疑問歡迎留言,我們下期再見。

Published in News by Awesome.

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *