怎樣才算精通Excel？
引子
Excel? 這么簡單的東西有學的必要嗎？公式加減乘除，誒等等這個日期怎么變成 #### 了，雖然文本數(shù)字搞不清楚，但也算熟練使用 Excel 啦。
WOW, Awsome, Excel 有點厲害，VLOOKUP 函數(shù)這么方便的嗎？我會了這么多東西，簡歷上寫個精通 Excel 不過分吧。
Emmm, Excel 好像比我想象中復雜一點，函數(shù)圖表有點麻煩噢，好多不知道的東西學也學不完，自己只能說略懂。
額，DAX 是什么，PowerQuery 又是什么，PivotTable 和 PowerPivot 又是什么和什么 @_@ 會手寫 VBA，會用數(shù)據(jù)透視表，搞不定的時候會借助 Python，勉強算是入門好了。
精通 Excel 的幾個階段說了一大堆，也不知道到底說了啥。簡單講哲人蘇格拉底說過，知道的越多，才知知道的越少。Excel 正是第一眼簡單，深入了解才知其博大精深。業(yè)余使用者從精通到入門大致分為五個階段：
基礎(chǔ)知識、基本操作單元格格式，表格樣式、查找、凍結(jié)窗格、自動求和
進階操作，快捷鍵，圖表條件格式、篩選、分列等等點點鼠標就可以實現(xiàn)的功能。微博、QQ 空間上有很多只需要點點鼠標就能完成的 Excel 炫酷操作就屬于第二階段。到這里基本上能處理超過大部分工作，而且覺得比較順手。
如果不是專業(yè)做，快捷鍵不用記很多。用好 Ctrl、Shift、Tab、方向鍵等的組合鍵，就能感到得心應手行云流水。
圖表本來應該單獨歸為一類。我的本業(yè)是數(shù)據(jù)可視化，但我一直相信條理清楚的數(shù)據(jù)自己會說話，做表格的時候拎得清比花哨的圖表更重要，在恰當?shù)臅r機會用圖表即可。
公式、函數(shù)公式指輸入框中等號開始的內(nèi)容，加減乘除等于。
函數(shù)指帶形式上括號的，返回一定結(jié)果的計算過程。主要分為邏輯、文本、時間和日期、查找四大類。
精通 Excel 的分界線
數(shù)據(jù)透視表數(shù)據(jù)透視表實際上是個篩選器，通過五層方法，1切片器篩選，2整表篩選，3列篩選，4行篩選，5輸出的值，抽絲剝繭，從復雜的數(shù)據(jù)表中找出我們需要的數(shù)據(jù)。
VBAExcel + VBA 是圖靈完備的，也就是說其他語言能完成的事情，VBA 同樣也能。理論上你可以通過 VBA 完成任何計算機可能完成的任務(wù)。真正的屠龍寶刀點擊丟送（劃掉）。
入門 Excel 的分界線
（論外）更深入的領(lǐng)域DAX? PowerPivot? 是什么，PowerQuery?
其他，文件，Office 套件聯(lián)動文件類型，xls xlsx xlsm xlsb 分別是什么，了解例如 csv json xml 等其他的數(shù)據(jù)文件格式，知道 Excel 自動保存機制，知道 Excel 為什么被鎖定……不想讓努力付諸東流，還是要多了解一下噢。另外很少有人只用到 Excel，和 PowerPoint、Word 聯(lián)動也是必備技能。值得一提的是 Word 里面的表格會更讓人抓狂，千萬別把表格用成了一種繪圖工具 = =
一個例子總是說 Excel 有多厲害，難免有往自己臉上貼金之嫌。那么通過實際的工作，展示一下處理好一張 Excel 表，需要用到哪些知識和技巧。
場景再現(xiàn)
作為元氣滿滿的公司新人，那些無關(guān)大局，卻又有點麻煩的事情，當然是要主動去承擔啦，不然大事做不來，小事不愿做，對得起公司開的工資嘛。
老師：小王呀，聽說你大學學的計算機，那你 Excel 用得一定特別溜啦？
我：（警覺）差不多吧，也不是所有人都用的好，我們專業(yè)不考計算機二級的……
老師：那太好啦，我們公司為了激勵員工健身，構(gòu)建了一個考勤機制。每個月健身房打卡超過六次的員工可以發(fā)獎金。但是不是每次打卡都是有效的。上班時間打卡不能算。周一到周五下午,周三周五中午是有效時間，超過這個時間就不算了。但是如果沒有在上班時間鍛煉，可以掐頭去尾只留有效時間。外勤、手機打卡不算， 只有在公司健身房門口的考勤機打卡才算。
我：好的，我明白了（大概），我這就去做
老師：這是考勤記錄表，你這周做好就行。
我：好的，沒問題。俺這就去做
整理思路
先理一理需求：
通過健身打卡記錄表，計算員工有效健身的時間和次數(shù)。再讓我理一理考勤記錄表：
有一個數(shù)據(jù)規(guī)模一萬行左右的 Excel 表。每行是一條打卡記錄。員工只要打卡就會留下一行數(shù)據(jù)。但是這個數(shù)據(jù)可能是正常簽到，也可能是正常簽退，當然有更多無效的打卡姓名可以重名，但是員工 ID 可以當作“主鍵”考勤日期和時間，是指預設(shè)的打卡時間段。打卡系統(tǒng)對時間段有一個判斷，比如說周五 17:39 打卡，對應的是周五下午 17:30 到 20:30 這個時間段。但這個判斷不一定準確。打卡時間，顧名思義員工實際的打卡時間。打卡地址：在考勤機上打卡，地點固定是公司。打卡設(shè)備：健身房門口考勤機或者手機上打卡。最后我們理一理其中的邏輯：
上班時間不能打卡，12:00 和 17:30 前十分鐘之外,也就是 11:50 和 17:20 之前打卡無效。周一到周五下午，周三周五中午打卡有效，其余時間段打卡無效。打卡設(shè)備：這條很好判斷，健身房門口考勤機有效，其余無效。正常情況下，連續(xù)的兩條有效的記錄正好是一條有效簽到，一條有效簽退。但是有的簽到和簽退無效，與之匹配的另一條也隨之失效。所以只有找同一天同一時段同一個人連續(xù)兩條有效記錄。下午 17:30 到 20:30，中午 12:00 到 14:00 是有效時間。超過有效時間的部分不計入總有效時間。單次有效時間超過 60 分鐘，計為一次有效健身。最后累加統(tǒng)計每個人的有效健身時間和健身次數(shù)。著手工作
判斷的條件和過程可以用一張圖清晰的羅列出來。接下來是具體步驟的說明：
首先是篩選。打卡設(shè)備“考勤機”，打卡結(jié)果不能是“外勤、打卡無效”等。將篩選后的數(shù)據(jù)另建新表。接下來的判斷和計算分成小步驟。每個判斷結(jié)果 "TRUE/FALSE" 都會獨占一列，最后用 "AND" 邏輯連結(jié)。計算結(jié)果也會各獨占一列,我們稱這樣的列叫“中間列”。這樣做的好處是易讀易查易改。判斷是否是同一個人，同一天，直接用 "=" 比較本行（從簽到數(shù)據(jù)開始）和下一行（正常有效的話會是簽退數(shù)據(jù)）共兩行的 userid 和考勤日期單元格，有效數(shù)據(jù)會體現(xiàn)在簽到的一行，后文所有的計算結(jié)果也在簽到的一行有效。判斷打卡時間段，簽到或簽退，用 FIND( ) 函數(shù)，如果 "12:00" 字符串被找到，說明是中午簽到時間。判斷出來簽到和簽退后，也直接用 "=" 試著匹配上下兩行簽到簽退，如果本行是簽到，下一行簽退，則形成一次完整的打卡記錄。轉(zhuǎn)換打卡時間，用 RIGHT( ) 和 LEN( ) 從形如 "2019-10-24 17:28" 的時間日期字符串中取出 "17:28" 的時間。接著使用 TIMEVALUE( ) 函數(shù)轉(zhuǎn)換成數(shù)值，單位是天，0:00 的值是 0，12:00 就是 0.5，18:00 的值是 0.75，24:00 的值是…… 0，實際上是第二天的 0。上文中的 17:28 是 0.727777778。判斷是否是非工作時間，用上一步中得到的打卡時間值，和工作時間的時間值比較即可。調(diào)整時間也很簡單，用 IF( ) 函數(shù)判斷在規(guī)定時間之前打卡簽到，若提前時間點自動調(diào)整為打卡時間點，若沒有提前，則原樣保留。計算有效時間和判斷是否計入總數(shù)，用以上步驟得到調(diào)整后的簽到簽退，做差，得到時間段，單位是天，乘以 24 再乘 60 得到分鐘數(shù)，判斷是否大于 60 得到是否是有效的一次健身。至此，一次記錄的處理過程結(jié)束。然后我們可以拖動單元格，整個表格處理完畢。接下來，我們可以選中數(shù)據(jù)，插入數(shù)據(jù)透視表，選中姓名、有效時間、有效次數(shù)，數(shù)據(jù)透視表會自動按姓名分組，將時間累加，我們可以得到每個人的有效時間之和、總有效次數(shù)。在有效時間或次數(shù)列右鍵降序排序，我們可以很清楚的知道哪位同事健身時間比較多、次數(shù)比較多，可以給他們多發(fā)獎金。
自動化過程
過程清晰，結(jié)果正確，但是這樣還不能交差。因為每個月都要統(tǒng)計一次，把表交出去之后要和接手的人講清思路和用法，不亞于重做一遍的工作量，關(guān)鍵是不一定保證一個月之后還有人能記得住。
不過學計算機的孩子嘛，能點一下，就絕不點兩下，能自動完成，就絕不手動。只有足夠懶，才能變得足夠聰明、足夠優(yōu)秀。接下來請“宏”來幫我們完成重復工作。
簡單說一下我對“宏”和 VBA 的理解，宏是 VBA 語言編寫的腳本，可以完成我們指定的操作，但 VBA 包含的范圍不僅僅是宏。VBA 是一種圖靈完備的語言，定義變量，賦值運算，邏輯判斷，循環(huán)跳轉(zhuǎn)都不在話下，順序執(zhí)行步驟只是它強大功能的一小部分。
既然我們只需要自動化一些操作，只需要用到錄制宏即可，不需要深入了解 VBA。
我們需要錄制的功能如下：
篩選數(shù)據(jù)，選出考勤機、打卡有效的數(shù)據(jù)復制篩選后的數(shù)據(jù)到新數(shù)據(jù)表復制判斷和計算過程函數(shù)創(chuàng)建數(shù)據(jù)透視表隱藏過程中的數(shù)據(jù)表在開發(fā)工具-->錄制宏點擊開始錄制宏，然后稍做調(diào)試修改即可。主要是刪改一下數(shù)據(jù)表名稱，使得重復運行時不會讀到空表、重復建表、引用舊表。
最后，我們在原始數(shù)據(jù)表插入一個按鈕，關(guān)聯(lián)宏。下次使用時替換原始數(shù)據(jù)內(nèi)容，點擊就可以運行宏啦。一鍵得出結(jié)果的感覺有沒有很棒呢？
這個例子完整的涵蓋了精通 Excel 的前五個階段：
基礎(chǔ)知識、基本操作 單元格文本格式、時間日期格式。表格樣式，套用表格格式進階操作，快捷鍵，圖表 通過篩選去除不符合要求的數(shù)據(jù) 用到了各種快捷鍵公式、函數(shù) 公式用到等于大于小于 函數(shù)涉及了四大類，邏輯、文本、時間和日期、查找,包括計算過程用到的 FIND( ) COUNT( ) IF( ) ISERR( ) AND( ) RIGHT( ) TIMEVALUE( ) 和輔助工作中用到的，干什么都繞不開的 VLOOKUP( )數(shù)據(jù)透視表 用到了最基礎(chǔ)的數(shù)據(jù)透視表功能，篩選分組求和。VBA 用到了簡單的錄制和運行宏功能也就意味著，做完這張表就證明你的 Excel 達到了入門水平，可以繼續(xù)探索更豐富多彩的表格世界啦。
另一個例子場景再現(xiàn)
leader：我們這次考核，90 分及格，題庫有八百多道題，我們都要背會。這是題庫，你們這幾天重點記一下。
組員 A：我們可以把這些選項都填到題目里去，打印出來，方便我們看。
組員 B：有的是單選，有的是多選，還挺麻煩。
組員 C：我們每個人分一百道題，把它們復制進去再匯總吧。
我：我們老員工們都很忙，這個就交給我來做吧。下班前能做好。
一行函數(shù)，做到把多選題的正確答案填進題目中的空格：
=SUBSTITUTE([@題目],"（）","（"&IF(ISERROR(FIND("A",[@答案])),"",[@選項A])&" "&IF(ISERROR(FIND("B",[@答案])),"",[@選項B])&" "&IF(ISERROR(FIND("C",[@答案])),"",[@選項C])&" "&IF(ISERROR(FIND("D",[@答案])),"",[@選項D])&"）")&" "&[@答案]
下面是解釋：
思路很簡單，答案中包含 A，就把 A 選項的內(nèi)容拉出來，否則不拉取。BCD 同理。然后將拉出來的選項拼接成字符串，替換掉空的括號。形如 "[@列名]" 的引用方式是“結(jié)構(gòu)化引用”，套用表格格式之后可以使用這種引用方式，代替 A1 B2 這種單元格名。SUBSTITUTE( ) 是文本替換函數(shù)，具體用法請查閱文檔IF( ) 我們上個例子中用到過，表示判斷FIND( ) 也用到了，用來查找是否包含某字符ISERROR( ) 有點難理解，但只要你試一下就立刻會明白它有什么用。FIND( ) 找不到結(jié)果會返回一個錯誤值，只要其中一個返回了錯誤，就會導致整個嵌套函數(shù)的值變成錯誤。我們要避免單個的結(jié)果影響整個函數(shù)。& 用于拼接字符串，字符串可以從單元格得到，也可以是引號中的內(nèi)容。多選會了，單選更是小菜一碟。接下來把 Excel 內(nèi)容復制進 Word，括號中選項標紅。Word 替換功能不僅可以替換文字，還可以替換成特定格式。這就是另一個故事了。
總結(jié)本篇結(jié)合例子，聊到了 Excel 進階要掌握的幾大塊知識，基礎(chǔ)、進階、公式、透視表、VBA。別的點未能提及，不代表它用的不多，只是他們游離在主線之外（更多的因為我自己也不會，以己昏昏使人昭昭哈哈）。
拋磚引玉，學 Excel 不是說今天記了幾個公式，明天會了幾種操作，馬上就能學會了，這些是日積月累的過程。如果硬要說有一種通用的方法，那就是想方設(shè)法把自己的問題，轉(zhuǎn)化分解成合乎邏輯的，Excel 可能解決的小問題，然后去搜索每個小問題的解決方案。就像多選填答案的例子，把稍復雜的問題一步步分解成搜索、拼接、替換這些我們已經(jīng)很熟悉的小問題。
另外學好 Excel 可以免費領(lǐng)女朋友，不是我騙你，不信你自己看。后面的話寫到這里，已經(jīng)有五千字。給我一個贊行不行？