幾乎每個人都在自己的簡歷中寫過“精通 Excel”，可在現(xiàn)實工作中…… 還是書到用時方恨少！其實作為辦公室里的當家一哥，Excel 絕對能算得上是那個最容易上手卻最不容易精通的一位。那么在你的日常工作中，是否也遇到過一些書本里沒講透的小難題？如果答案是肯定的話，那么下面這篇文章就絕對不能錯過了！

1. 隱藏數(shù)據(jù)后，圖表沒了！

如果你的表格加入了很多中間數(shù)據(jù)，并且用這些中間數(shù)據(jù)做成了圖表，那么一定會遇到下面這個尷尬。將數(shù)據(jù)表隱藏后（右擊列標→“隱藏”），圖表也隨之清空了。遇到這種情況，我們又該如何處理呢？

▲數(shù)據(jù)被隱藏后，圖表也會隨之清空

解決方法：

1） 右擊圖表→“選擇數(shù)據(jù)”，點擊彈出面板左下角的“隱藏的單元格和空單元格”；

2） 勾選“顯示隱藏行列中的數(shù)據(jù)”前面的復選框，確定后。再次隱藏數(shù)據(jù)列就不會影響到圖表的正常顯示了；

▲勾選“顯示隱藏行列中的數(shù)據(jù)”

2. 工作表保護后，控件沒法點了！

有時想在老板面前顯擺一回，費了九牛二虎之力，用控件搞了個交互報表。當你興沖沖地將報圖擺在老板面前時，尷尬的事發(fā)生了，原本沒有問題的控件不知為啥就是點不了。結(jié)果顯擺沒顯成，反而弄了個欺騙上司的罪名，那么問題到底出在哪兒呢？

▲為啥控件就是點不了？

出于表結(jié)構(gòu)保護等方面的考慮，我們通常會對最終成表執(zhí)行一次工作表保護。正是這一步保護操作，才最終導致了控件失效。當然并不是說帶有控件的報表就無法使用工作表保護了，想讓這兩項功能并存其實非常簡單，那就是右擊控件鏈接單元格，取消“設置單元格格式”→“保護”→“鎖定”前面的復選框。處理完成后，再次執(zhí)行工作表保護就不會妨礙到控件的正常運行了。

▲取消控件鏈接單元格的“鎖定”狀態(tài)即可

3. VLOOKUP 怎么不能反著查？

VLOOKUP 算是日常點擊率較高的一組函數(shù)了，這個函數(shù)什么都好，就是沒法倒著查。于是很多小伙伴一遇到拿姓名查工號的操作，就開始頭疼，這個又該如何處理呢？

▲VLOOKUP 無法執(zhí)行反向查詢

想要解決這個問題，我們可以先用 IF 函數(shù)中轉(zhuǎn)一下，即通過 IF 函數(shù)的數(shù)組功能，將 VLOOKUP 的查找域調(diào)換一下，變相解決這個問題。具體方法就是，將公式修改為“=VLOOKUP (R8,IF ({1,0},C:C,B:B),2,FALSE)”。這里“IF ({1,0},C:C,B:B)”所產(chǎn)生的作用，就是將 B 列與 C 列臨時調(diào)換一下，以保證 VLOOKUP 的正常運行。

▲通過添加一個 IF 數(shù)組函數(shù)臨時調(diào)換 B 列與 C 列，保證 VLOOKUP 正常運行

此外，小編平時用得比較多的還有一個 LOOKUP 函數(shù)，同樣也能完成上述操作，而且比 VLOOKUP 更簡潔（=LOOKUP (R8,C:C,B:B)），感興趣的小伙伴不妨一試！

4. F9 這個鍵還有這個用

Excel 中有很多快捷鍵，比方說 F9 鍵。通常大家所了解的 F9 是全表重算（比如隨機生成演示數(shù)據(jù)），但它的另一個用途卻很少有人知道。舉個例子，比方說你建立了一個超級超級復雜的公式，結(jié)果…… 公式出錯啦！當然我們知道 Excel 的函數(shù)排錯是非常垃圾的。于是你開始想到了一個“笨辦法”，即將主公式拆分成若干個子公式單獨排錯。當然劇本通常是以主人公成功找到錯誤來皆大歡喜，卻很少有人注意到，主人公其實早就累趴在排錯的路上。

▲通過 F9 鍵單獨計算選中區(qū)域

好吧，還是上面這個劇本，我們換一種解法。即將你感覺有問題的公式先部分選中，然后按下 F9 鍵，單獨計算所選區(qū)域的結(jié)果。有問題處理它，沒問題繼續(xù)下一個，直到把問題完全解決。其實這條操作與上一條本質(zhì)上沒有區(qū)別，卻省去了很多拆分子公式的麻煩。而這恰恰是 F9 鍵的另一項功能 —— 單獨計算選中區(qū)域結(jié)果。

5. 設好的格式無法跟隨記錄增長怎么辦？

當你辛辛苦苦地為表格制作好邊框、色條，卻發(fā)現(xiàn)新記錄無法自動繼承這些格式，是不是想 si 的心都有了。不光是你，很多使用 Excel 制作流水表的童鞋，其實都遭遇過類似的尷尬。

▲新記錄不會自動繼承上一行格式

怎么解決呢？很簡單，首先選中你已經(jīng)設好格式的表格域，按下 Ctrl+T 鍵，將其轉(zhuǎn)換成“超級表”。然后點擊“表格工具”→“表格樣式”，將當前的表格樣式設置為“無”（即不使用超級表默認樣式）。接下來，取消“表格樣式”里的“篩選按鈕”（當然如果需要也可以保留）。這時你會發(fā)現(xiàn)，新記錄已經(jīng)可以自動繼承前面設好的表格樣式了。

▲通過“超級表”實現(xiàn)格式自動繼承

6. 哎！上圖里的“色條”怎么來的？

細心的小伙伴或許已經(jīng)發(fā)現(xiàn)，在上面這組演示圖里，我的表格似乎可以隔行換色。而且無論怎么對表格進行添加刪除記錄，都不會影響到色條的排列。那么，這個又是如何實現(xiàn)的？

▲色條可以不受添行刪行影響

這項功能的實現(xiàn)，同樣也有兩種方法。第一種依舊是使用“超級表”功能，與普通表相比，超級表本身就自帶表格拓展功能，當一條記錄被新建到超級表的最后一組行列時，就會自動擴展原表格域。與此同時，新的行列也將繼承原表格的格式。

第二種方法是使用條件公式，首先選中要處理的區(qū)域，點擊“條件格式”→“新建規(guī)則”→“使用公式確定要設置格式的單元格”，然后在公式框內(nèi)輸入“=MOD (ROW (),2)=0”，并設置一組背景色。這條公式的作用，是通過取余函數(shù)對行號計算，從而產(chǎn)生隔一跳一的效果，然后為符合條件的行（即每隔一行）刷上設好的背景色，同樣也可以實現(xiàn)上述效果。

▲通過條件格式 + 公式的方式，實現(xiàn)隔行換色

7. 如何快速制作一個模板？

現(xiàn)在的表格越來越復雜，很多都夾雜了大量的公式。那么問題來了，如何快速生成一組模板，又不把公式刪除掉呢？

首先選中數(shù)據(jù)區(qū)域，點擊“開始”→“編輯”→“查找和選擇”→“定位條件”，接下來點擊“常量”→“確定”，按下鍵盤上的 Del 鍵。這時你會發(fā)現(xiàn)，表格中的所有常量都被刪除了，而公式卻沒有受到影響，于是一張僅帶有公式的空白模板表就這樣出爐了！

▲借助“定位條件”快速刪除常量制作模板

8. 數(shù)據(jù)有效性里的空值太多怎么辦？

制作大型表格時，常常會利用“數(shù)據(jù)有效性”來統(tǒng)一數(shù)據(jù)。不過這也會導致另一個問題，即先期為數(shù)據(jù)預留的空間太多，就會在下拉列表產(chǎn)生大量空值。那么這個問題又該如何規(guī)避呢？

▲默認制作的下拉菜單空值很多，不便于操作

要想解決這個問題，還是要利用一組函數(shù)。以上圖為例，依舊進入“數(shù)據(jù)”→“數(shù)據(jù)驗證”→“序列”欄，然后在“來源”框中填入公式“=OFFSET ($O$6,,,COUNTA ($O$6:$O$19))”。它的意思就是，首先通過 COUNTA 函數(shù)求出當前數(shù)據(jù)源的有效記錄數(shù)（即“主講教師”列），再通過 OFFSET 函數(shù)確定好最終的提取范圍，這樣我們便得到了一組沒有空值的下拉列表。

▲通過修改序列來源，去除選單中多余空值

9. 單元格左上角三角很礙眼怎么弄？

有時我們會在某些單元格的左上角看到一些小三角，特別是在一些格式化好的文檔中，這些小三角會顯得特別礙眼。其實這是 Excel 的自動查錯功能，說白了就是和 Word 里的“波浪線”一個道理。通常小三角往往意味著該單元格存在問題（比如公式不正常，數(shù)據(jù)類型不正確等等），但如果這就是我們故意為之（比如使用文本格式存儲身份證號等），那就沒必要讓它提示了。

▲你是不是也經(jīng)常看到這種“礙眼”的小三角

解決方法有兩種：一是點擊三角左側(cè)的提示符，勾選“忽略錯誤”，但這種方法只能對連續(xù)單元格有效，如果表格中要處理的單元格很多時，效率就很低了。還有一種方法是直接關(guān)閉錯誤檢查功能，點擊三角左側(cè)的提示符，在選單里選擇“錯誤檢查選項”，接下來取消“允許后臺錯誤檢查”前面的復選框，這樣小三角也將不再提示。

▲關(guān)閉“允許后臺錯誤檢查”可以一勞永逸解決掉小三角

10. 怎樣禁止錄入重復值

如果你制作了一個流水表，又不希望錄入時出現(xiàn)重復記錄，那么就可以借助“數(shù)據(jù)驗證”搞定它。具體方法是：首先選中要限制的數(shù)據(jù)列，點擊“數(shù)據(jù)”→“數(shù)據(jù)工具”→“數(shù)據(jù)驗證”。然后將驗證條件修改為“自定義”，并在公式欄內(nèi)輸入“=COUNTIF (B:B,B1)=1”。這里公式的含義是在 B:B 范圍內(nèi)，匹配與 B1 單元格內(nèi)容相同的記錄并計數(shù)，一旦發(fā)現(xiàn)有重復（即 COUNTIF 值 > 1），便中止錄入，具體效果如下。

▲設置好后就不能輸入重復數(shù)值了

除了完全禁止重復數(shù)值錄入外，我們也可以將出錯禁止形式修改為“警告”，來實現(xiàn)僅提示不禁止的效果。

▲修改這里，可以僅提示不禁止

寫在最后

Excel 中隱藏的秘密很多，有些不但書里沒寫，甚至連幫助文件中都查不到。當然就像千千萬萬個 Excel 命令一樣，這些“隱藏版”小技巧同樣也能在關(guān)鍵時候幫上大忙。好了，這就是本期要和大家分享的幾組 Excel 小技巧，你都 Get 到了么！