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

1. 隱藏數據后,圖表沒了!

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

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

解決方法:

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

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

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

2. 工作表保護后,控件沒法點了!

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

▲為啥控件就是點不了?

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

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

3. VLOOKUP 怎么不能反著查?

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)),感興趣的小伙伴不妨一試!

4. F9 這個鍵還有這個用

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

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

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

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

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

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

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

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

6. 哎!上圖里的“色條”怎么來的?

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

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

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

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

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

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

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

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

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

8. 數據有效里的空值太多怎么辦?

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

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

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

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

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

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

▲你是不是也經??吹竭@種“礙眼”的小三角

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

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

10. 怎樣禁止錄入重復值

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

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

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

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

寫在最后

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