幾乎每個人都在自己的簡歷中寫過“精通Excel”，可在現實工作中……還是書到用時方恨少！

其實作為辦公室里的當家一哥，Excel絕對能算得上是那個最容易上手卻最不容易精通的一位。

那么在你的日常工作中，是否也遇到過一些書本里沒講透的小難題？如果答案是肯定的話，那么下面這篇文章就絕對不能錯過了！

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

數據被隱藏后，圖表也會隨之清空

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

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

勾選“顯示隱藏行列中的數據”

有時想在老板面前顯擺一回，費了九牛二虎之力，用控件搞了個交互報表。當你興沖沖地將報圖擺在老板面前時，尷尬的事發生了，原本沒有問題的控件不知為啥就是點不了。

結果顯擺沒顯成，反而弄了個欺騙上司的罪名，那么問題到底出在哪兒呢？

為啥控件就是點不了？

出于表結構保護等方面的考慮，我們通常會對最終成表執行一次工作表保護。

正是這一步保護操作，才最終導致了控件失效。當然并不是說帶有控件的報表就無法使用工作表保護了，想讓這兩項功能并存其實非常簡單，那就是右擊控件鏈接單元格，取消“設置單元格格式”→“保護”→“鎖定”前面的復選框。

處理完成后，再次執行工作表保護就不會妨礙到控件的正常運行了。

取消控件鏈接單元格的“鎖定”狀態即可

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

VLOOKUP無法執行反向查詢

想要解決這個問題，我們可以先用IF函數中轉一下，即通過IF函數的數組功能，將VLOOKUP的查找域調換一下，變相解決這個問題。

具體方法就是，將公式修改為“=VLOOKUP(R8,IF({1,0},C:C,B:B),2,FALSE)”。

這里“IF({1,0},C:C,B:B)”所產生的作用，就是將B列與C列臨時調換一下，以保證VLOOKUP的正常運行。

通過添加一個IF數組函數臨時調換B列與C列，保證VLOOKUP正常運行

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

Excel中有很多快捷鍵，比方說F9鍵。通常大家所了解的F9是全表重算（比如隨機生成演示數據），但它的另一個用途卻很少有人知道。舉個例子，比方說你建立了一個超級超級復雜的公式，結果……公式出錯啦！

當然我們知道Excel的函數排錯是非常垃圾的。于是你開始想到了一個“笨辦法”，即將主公式拆分成若干個子公式單獨排錯。

當然劇本通常是以主人公成功找到錯誤來皆大歡喜，卻很少有人注意到，主人公其實早就累趴在排錯的路上。

通過F9鍵單獨計算選中區域

好吧，還是上面這個劇本，我們換一種解法。即將你感覺有問題的公式先部分選中，然后按下F9鍵，單獨計算所選區域的結果。

有問題處理它，沒問題繼續下一個，直到把問題完全解決。

其實這條操作與上一條本質上沒有區別，卻省去了很多拆分子公式的麻煩。而這恰恰是F9鍵的另一項功能——單獨計算選中區域結果。

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

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

怎么解決呢？很簡單，首先選中你已經設好格式的表格域，按下Ctrl+T鍵，將其轉換成“超級表”。

然后點擊“表格工具”→“表格樣式”，將當前的表格樣式設置為“無”（即不使用超級表默認樣式）。

接下來，取消“表格樣式”里的“篩選按鈕”（當然如果需要也可以保留）。

這時你會發現，新記錄已經可以自動繼承前面設好的表格樣式了。

通過“超級表”實現格式自動繼承

細心的小伙伴或許已經發現，在上面這組演示圖里，我的表格似乎可以隔行換色。而且無論怎么對表格進行添加刪除記錄，都不會影響到色條的排列。

那么，這個又是如何實現的？

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

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

與此同時，新的行列也將繼承原表格的格式。

第二種方法是使用條件公式，首先選中要處理的區域，點擊“條件格式”→“新建規則”→“使用公式確定要設置格式的單元格”，然后在公式框內輸入“=MOD(ROW(),2)=0”，并設置一組背景色。

這條公式的作用，是通過取余函數對行號計算，從而產生隔一跳一的效果，然后為符合條件的行（即每隔一行）刷上設好的背景色，同樣也可以實現上述效果。

通過條件格式+公式的方式，實現隔行換色

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

首先選中數據區域，點擊“開始”→“編輯”→“查找和選擇”→“定位條件”，接下來點擊“常量”→“確定”，按下鍵盤上的Del鍵。

這時你會發現，表格中的所有常量都被刪除了，而公式卻沒有受到影響，于是一張僅帶有公式的空白模板表就這樣出爐了！

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

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

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

要想解決這個問題，還是要利用一組函數。以上圖為例，依舊進入“數據”→“數據驗證”→“序列”欄，然后在“來源”框中填入公式“=OFFSET($O$6,,,COUNTA($O$6:$O$19))”。

它的意思就是，首先通過COUNTA函數求出當前數據源的有效記錄數（即“主講教師”列），再通過OFFSET函數確定好最終的提取范圍，這樣我們便得到了一組沒有空值的下拉列表。

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

有時我們會在某些單元格的左上角看到一些小三角，特別是在一些格式化好的文檔中，這些小三角會顯得特別礙眼。其實這是Excel的自動查錯功能，說白了就是和Word里的“波浪線”一個道理。

通常小三角往往意味著該單元格存在問題（比如公式不正常，數據類型不正確等等），但如果這就是我們故意為之（比如使用文本格式存儲身份證號等），那就沒必要讓它提示了。

你是不是也經常看到這種“礙眼”的小三角

解決方法有兩種：一是點擊三角左側的提示符，勾選“忽略錯誤”，但這種方法只能對連續單元格有效，如果表格中要處理的單元格很多時，效率就很低了。

還有一種方法是直接關閉錯誤檢查功能，點擊三角左側的提示符，在選單里選擇“錯誤檢查選項”，接下來取消“允許后臺錯誤檢查”前面的復選框，這樣小三角也將不再提示。

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

如果你制作了一個流水表，又不希望錄入時出現重復記錄，那么就可以借助“數據驗證”搞定它。

具體方法是：首先選中要限制的數據列，點擊“數據”→“數據工具”→“數據驗證”。

然后將驗證條件修改為“自定義”，并在公式欄內輸入“=COUNTIF(B:B,B1)=1”。

這里公式的含義是在B:B范圍內，匹配與B1單元格內容相同的記錄并計數，一旦發現有重復（即COUNTIF值>1），便中止錄入，具體效果如下。

設置好后就不能輸入重復數值了

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

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

Excel中隱藏的秘密很多，有些不但書里沒寫，甚至連幫助文件中都查不到。當然就像千千萬萬個Excel命令一樣，這些“隱藏版”小技巧同樣也能在關鍵時候幫上大忙。

好了，這就是本期要和大家分享的幾組Excel小技巧，你都Get到了么！

總結

以上是生活随笔為你收集整理的用了N年浑不知！老司机教你解决Excel疑难杂症的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。