知乎精選好文，怎樣才算精通Excel？

作者：龍逸凡來源：知乎已獲得轉載授權

前言

看到這個問題很久了，一直想斗膽談談本人對「精通Excel的理解「，今天終於成文，大家看完了如果同意本人觀點，就點個贊，不同意，歡迎指正討論，但請勿拍磚，很痛的。

本人不是高手，只是一個用Excel多年的大表哥，充其量只是掌握了20%功能的半桶水而已，套用《「偷懶」技術：打造財務Excel達人》合著者錢勇在後記中的一句話「本人一直在努力地抬頭仰望，卻發現視線所及之處，才僅僅是Excel的半山腰「。

正文

怎麼才算精通Excel？能編製多層嵌套非常複雜的公式，就是精通Excel嗎？能編寫一段VBA代碼，用代碼實現Excel沒有的功能，就是精通Excel嗎？

首先，我們先來看三張喝飲料的圖片，看看Excel菜鳥是什麼樣的，普通的Excel用戶、精通Excel的人又是什麼樣的。

然後，我們再來看二個例子，

▼第一個：

請統計下表中《「偷懶」的技術：打造財務Excel達人》銷售數量、金額的合計。

凡對Excel的脾氣有點了解的人都知道，用於計算的數字不能和文字混搭，否則會造成Excel計算的腦梗塞。

但是當沒摸清Excel脾氣的新手們在「沒有困難製造困難也要上」提出這樣的問題時，一些Excel高手不是指導規勸新手們從規範做起，而是列出諸如下面的公式，我們能說這些高手們是真的高手嗎？

=SUMPRODUCT(SUBSTITUTE(D4:D8,"元","")*1)

▼第二個：

又比如，表哥龍逸凡要對銷售員的銷售情況做一個登記台賬，他做出的銷售台賬如下圖：

由於表格設計不規範，如果要統計某產品的銷售數量銷售金額，不能使用SUMIF函數，龍逸凡編製的求和公式是複雜的數組公式，如下：

=SUM((LOOKUP(ROW(4:22),ROW(4:22)/(A4:A22>0),A4:A22)=K3)*D4:F22)

此公式很複雜，思路也很巧妙，但是，我們能說龍逸凡精通Excel嗎？

如果將上面的表格做成規範的清單式表格，那麼用一個簡單的條件求和函數SUMIF就能統計出來：

從上面的二個例子出發，談談我對「怎麼才算精通Excel」的理解。

竊以為，我們至少要做到以下四點，才算得上精通Excel：

1. 規範。要有規範的數據處理理念和操作；

2. 廣度。要全面掌握Excel的常用功能；

3. 深度。深入研究過各種常用功能，對常用功能會拓展應用。

4. 靈活。對已掌握的知識能融會貫通，會靈活運用。

下面分別說明。

一、規範

要輕鬆駕馭Excel，把Excel發揮到極致，首先要做到規範。

不要以為Excel是任我們擺弄的玩偶，殊不知，它也是有脾氣的。

什麼數據用什麼格式、什麼功能用什麼表格布局，都要順著它的性子，以從根源上簡化數據的處理。只有讓Excel順心了，它才能充分發揮其洪荒之力為我們所用。如果沒有正確的數據處理理念、做不到表格規範、數據規範，而是完全靠霸王硬上弓的粗暴技術編製高超精巧的公式來完成相關運算，其實是一種基本功不紮實的表現。

真正精通Excel的高手懂得「上醫治未病，中醫治欲病，下醫治已病」的道理。只有將「規範」的理念貫穿始終，才能把表格玩得行雲流水天馬行空。

障礙應消滅於萌芽之處，而不是任由其變異膨脹再想辦法消滅。

孫子曰：「古之所以謂善戰者，無智名，無勇功，勝於易勝也「。Excel，道理亦然。

二、廣度

Excel博大精深，不能玩轉個十七八般武藝，好意思說自己是高手嗎？

咱們來自我評估一下吧：

▼基本功：

不但精通應用各種高級篩選、分類匯總、合併計算、數據透視表等常規的基本功能，對那些不怎麼常用的功能如單變數求解、模擬運算表及分析工具庫等載入項也能熟練應用。對新功能如、Power Pivot、Power View、Power BI也能及時學習並掌握。

▼函數技：

Excel中400多個函數除了那些專業函數沒用過，其他的函數都能信手拈來。別說普通的函數嵌套組合應用，那怕是讓普通用戶感覺燒腦無比的複雜數組公式也能一次性成功編寫出來。

▼圖表訣：

不但熟練掌握常用的柱形圖、折線圖，餅圖，能將這些圖表的各種元素玩出各種花樣，組合出新穎而又貼切的圖表，還能使用熟練應用不太常用的散點圖、曲面圖等，並能利用散點圖作為輔助工具，繪製出複雜的圖表。

▼VBA：

不但精通常見的VBA屬性對象方法，還能熟練應用SQL、字典、ADO、API、類模塊。什麼？VBA落伍了？沒什麼，VSTO也已熟練掌握。

精通Excel的高手，知識面一定是廣博的。遇到問題，能馬上給出解決方法。精通Excel的高手，既要知道條條大路通羅馬，更要知道在什麼背景下選哪條路才更高效便捷。

三、深度

做為精通Excel的高手，Excel知識掌握得既精又深才是應有的標準。Excel很多看似「膚淺」的功能，其實都蘊藏著更為深邃的技法。

比如查找替換功能，只要使用過Excel的人，都會認為自己會使用此功能，但實際上，查找功能的一些選項或功能很多人都沒用過，比如查找結果能排序。作為Excel高手應該能掌握常見功能的方方面面，並能將其放揮出別樣的用途。

案例：下圖的表格， 工作表已保護，請批量清除C2:H23單元格區域的數字，而保留有灰色底色單元格的公式

由於表格已保護，無法使用定位功能，這時我們可以使用查找功能的通配符及查找結果排序功能，間接實現定位的定位常量、數字、公式等功能。利用查找功能批量選定常量（數字）單元格，從而批量將其清除。

做為精通Excel的高手，對函數來講，要深入掌握各類函數的知識點，要知其然也知其所以然。比如深入理解SUMIF第三參數的定位原理，LOOKUP的查找原理。

舉例：SUMIF第三參數的定位原理

SUMIF函數第三參數單元格區域起作用的就是左上角那個單元格，此單元格的作用是定位定點，只要有此定位點，SUMIF會自動以此單元格為原點，按照第一參數區域符合條件的單元格的坐標，找到同樣坐標位置的單元格，並對其數值求和。

我們可以利用此特點，完成看似難以完成的任務

▼SUMIF函數應用案例1：錯行求和

▼SUMIF函數應用案例2：錯行求和

▼SUMIF函數應用案例3：錯列求和

▼案例：又比如，要統計下表某片區1－6月的銷售額，該用什麼公式呢？

網上的文章給的幾個公式是這樣的：

高手公式：

=SUMPRODUCT(SUMIF(OFFSET(A2:A9,,ROW(2:7)*0),A12,OFFSET(A2:A9,,ROW(2:7))))

高高手的公式：

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C1:H1,ROW(1:8),))*(A2:A9=A12))

高高高手的公式：

=SUMPRODUCT(MMULT(C2:H9,ROW(2:7)^0)*(A2:A9=A12))

實際上，用不著這麼複雜，只需一個SUMPRODUCT函數就可解決：

=SUMPRODUCT((A2:A9=A12)*C2:H9)

再舉個圖表的例子，比如常用的圖表也就那幾種，但精通Excel的人，不但熟練掌握各類圖表的基本應用，還會深入研究圖表的各種小選項的設置，並將其發揮出特別的作用。

比如下面的圖表，利用了線條的漸變填充，僅用一條折線就表明了圖書排名的下降和上升階段，還同時用顏色的深淺表示排名的高低，增強了圖表的表現力。

又比如下圖的儀錶盤看起來很複雜精美豪華高端大氣上檔次，那它是怎麼做出來的呢，是用諸如PhotoShop等專業軟體製作的嗎？

非也，實際上它僅僅用了Excel的散點圖和圓環圖而已。

四、靈活

精通Excel的人，會靈活運用常規的功能、函數，能將常規功能發揮出別樣的用途，能將普通的函數進行組合出令人拍案叫絕的用法。

▼案例1：比如上文中的運用查找功能實現定位功能，

▼案例2：使用「填充－兩端對齊」和定位功能提取字元串中的數字

解題思路：先將A列寬度設為小於一個字元，然後選定A列，「填充－兩端對齊」，分離出A列的數字和漢字，再用定位功能刪除非數字字元的單元格，即可提取出各單格中的數字。

▼案例3：不使用函數，在練習題027工作表的B3:C9單元格引用sheet1表格中各人員的實際金額，要求批量操作。

本題實際上是間行引用，故不能直接下拉填充，習慣了函數公式的高手，習慣性的會使用OFFSET、INDEX等函數，實際上這道題，只需使用簡單的功能就可實現：

方法一：

在本工作表B3，B4單元格輸入字元串Sheet1!B3、Sheet1!B5，C列類同，然後下拉填充，再用查找替換將Sheet替換為＝Sheet

方法二：

在B3輸入公式=Sheet1!B3、B5輸入公式=Sheet1!B5，C列類同，下拉填充，然後定位－空值，刪除空白單元格，下面單元格上移，

方法三：

篩選，複製－選擇性粘貼（鏈接），要操作二次。

▼案例4：要對下圖的各部門進行小計求和

一般的用戶只會一個部門一個部門的設置求和區域，實際上如果靈活應用sum公式，選定E2：15單元格，然後輸入下面的公式也可以批量求和。

=SUM(D2:$D$15)-SUM(E3:$E$15)

這個公式巧妙的應用了「借用」的思想－借用了後面單元格的計算結果。此思路非常巧妙！

對真正的劍道高手來說，飛花摘葉皆可傷人，草木竹石均可為劍，甚至可以妙滲造化，劍在心中，無劍無我，無跡可尋，無堅不摧！

對真正的Excel高手來說，Excel問題的解決方案，俯拾皆是，順手拈來。手中無招，心中卻有招，無往不至！無敵天下！

希望本文，對你的Excel成長之路有所啟發。

作者：龍逸凡
推薦閱讀：