Excel不起眼技巧也有大作用

Excel是很多職場朋友都在使用的數據處理工具，除了常規的操作之外，可能你不會注意到下面的幾則技巧，這些技巧雖然看起來不太起眼，但卻相當實用，可以提高不少工作效率。

技巧一：巧妙解決Excel的日期格式問題

要想在A列輸入類似于“20140518”這樣的數字時，Excel能夠自動轉換為“2014-05-18”的日期格式，但即使打開“設置單元格格式”對話框，選擇“自定義”，將類型設置為“yyyy-mm-dd”，但仍然無法得到正確的日期格式，而是顯示為類似于“########”的格式，這是怎么回事呢？

究其原因，如果不是Excel所規定的日期范圍內的數字，那么不會被Excel識別為日期，因此只有首先將相關數值格式化為日期格式，然后才能轉換為Excel能夠識別的日期。如果你不想使用輔助列通過公式進行轉換，那么可以添加“####-##-##”的自定義類型（見圖1），以后輸入“20140518”就可以得到“2014-05-18”的日期格式。

技巧二：按需抓取單元格中的多行內容

同事傳過來一份表格，這里的單元格都包含了多行內容，各個單元格的第一行是姓名，最后一行的逗號后面是國家，現在她希望將姓名、國家這些信息提取出來，除了手工操作的方法外，有沒有簡單一些的方法呢？

相信大部分朋友首先想到的是使用公式，但公式顯然相對比較麻煩，其實如果你使用的是Excel 2013，那么可以選擇更為簡單的快速填充：首先在B1單元格手工輸入A1單元格的姓名信息，切換到“數據”選項卡，選擇B1：B5單元格區域，單擊“快速填充”按鈕，即可快速提取相關信息；繼續在C1單元格手工輸入A1單元格的國家信息，選擇C1：C5單元格區域，單擊“快速填充”按鈕，很快就可以批量提取國家信息，最終效果如圖2所示。

技巧三：從不規則數據中提取指定數據

同事前來求助，她希望從一份不規則的數據中提取指定數據，希望將“稅額：”后面的數字從每個單元格中提取出來，手工提取固然可行，但工作量相當大，有沒有簡單一些的方法呢？

在B2單元格輸入公式“=--MID（A2，FIND（“稅額”，A2）+3，FIND（“認證”，A2）-FIND（“稅額”，A2）-3）”，MID函數表示從文本字符串指定的起始位置返回指定長度的字符，使用時涉及3個參數，“A2”表示需要切割的字符串，“FIND（“稅額”，A2）+3”表示從“稅額”后面第3位開始截取，第3個參數“FIND（“認證”，A2）-FIND（“稅額”，A2）-3”表示截取幾位，“--”表示修改數據格式，此時會得到“10427.31”的結果，雙擊填充柄即可獲得相應效果。

使用公式雖然可行，但上述公式對于初級用戶來說，可能還是過于麻煩了一些。如果你使用的是Excel 2013，那么可以利用快速填充實現上述需求：

經過分析，發現這里的數據還是有一些規律，例如需要提取的數據都是在“稅額：”的后面，最終數據的后面都有空格字符，因此我們可以直接提取：在B2單元格手工輸入“10427.31”，選擇B2：B15單元格區域，切換到“數據”選項卡，單擊“數據工具”功能組的“快速填充”按鈕，很快就可以獲得圖3所示的效果，是不是更為簡單？當然，如果最終數據的前后是其他的規律，也可以同時提取一些其他的字符以作區別，最后替換即可。

技巧四：快速找出前10名的最小值

同事前來求助，需要統計出一列數據中最大的10個數據中的最小值，常規的方法是排序之后進行手工篩選，操作相對麻煩，有沒有簡單一些的方法呢？這里以統計D列數據為例進行說明：

如果是Excel 2013，則可以利用條件格式解決這一問題。切換到“文件”選項卡，在“樣式”功能組選擇“條件格式”功能，在下拉菜單依次選擇“項目選取規則→前10項”，如圖4所示，此時可以直接“查看”其中的最小值，不過如果該列數據比較多，手工“查看”可能會比較麻煩。

技巧五：一鍵完成行列的同時凍結

很多時候，我們需要對Excel工作表的某些行和列進行同時凍結，常規的操作是切換到“視圖”選項卡，接下來點擊“窗口”功能組的“拆分”按鈕完成拆分，選擇需要凍結的行和列，點擊“凍結窗格→凍結拆分窗格”，操作比較麻煩，而且還容易出錯。其實，我們有更為簡單的方法，這里以凍結三行、兩列進行說明：

事先不需要作任何操作，切換到“視圖”選項卡，選擇C4單元格，點擊“凍結窗格”按鈕下面的倒三角按鈕，如圖5所示，在這里選擇“凍結拆分窗格”，即可對三行、兩列的數據進行凍結。如果需要對其他的行或列進行凍結，可以按照類似的步驟進行操作。

技巧六：巧妙實現空值對應單元格的顏色填充

最近遇到一個Excel方面的問題，要求將B列單元格顯示為空值的A列單元格自動填充相應的顏色以示區別，手工操作顯然是相當麻煩，因此考慮另覓他法：

選擇A1：A7單元格區域，單擊“樣式”功能組的“條件格式”按鈕，從彈出菜單選擇“新建規則”，打開“新建格式規則”對話框，規則類型選擇“使用公式確定要設置格式的單元格”，公式使用“=B1=\"\"”，單擊“格式”按鈕，打開“設置單元格格式”對話框，切換到“填充”選項卡，在這里設置填充顏色，返回“編輯格式規則”對話框，檢查無誤之后關閉對話框，隨后就可以看到填充效果。

如果對公式不太熟悉，也可以利用篩選、定位的方法實現，在“編輯”功能組的“排序和篩選”菜單下選擇“篩選”，單擊B列的倒三角箭頭，如圖6所示，在這里勾選“（空白）”復選框，現在就可以將A列填充顏色，最后只要將篩選條件設置為“全部”即可。

技巧七：利用定位條件快速轉移同一列的中文內容

手頭有一份Excel文檔，其中都是中英文參照的術語，但由于是在同一列顯示，看起來很不順眼，現在希望將英文對應的翻譯對應顯示在“中文”一列，如果采取純手工的方法，操作起來顯然相當繁瑣。有沒有簡單一些的方法呢？

選中B2單元格，輸入公式“=A3”，同時選中B2：B3單元格區域，雙擊右下角的填充柄，快速復制公式，此時可以獲得圖7所示的效果；保持B列的選中狀態，按下“Ctrl+C”組合鍵進行復制，原位粘貼時請選擇“值和數字格式”；繼續保持B列的選中狀態，按下F5功能鍵，打開“定位條件”對話框，將定位條件設置為“空值”，確認之后可以選中B列所有的空白單元格，右擊任意一空值單元格選擇“刪除”，此時會彈出“刪除”提示框，請選擇“整行”刪除即可。

完成上述操作之后，可以發現英文、中文已經分別顯示在不同的列，是不是很方便？

技巧八：利用“合并計算”完成跨工作表的求和計算

同事拿過來一份Excel工作簿，要求在“匯總表”對1月、2月、3月、4月的相關產品的產量進行統計，逐表進行手工求和的方法雖然可取，但工作量實在太大，而且也容易出錯，當然也可以采取引用的方式進行求和，但操作同樣繁瑣。有沒有簡單一些的方法呢？

其實，我們可以通過“合并計算”的方法獲得各產品產量的匯總求和：

選中B5：C11單元格區域，切換到“數據”選項卡，單擊“數據工具”功能組的“合并計算”按鈕，打開“合并計算”對話框，函數自然是選擇“求和”，點擊“引用位置”右側的選擇按鈕，分別在1月、2月、3月、4月工作表選擇引用位置，需要指出的是，選擇時必須包括“產品”、“產量”兩個標題列，如圖8所示，最后不要忘記勾選“首行”和“最左列”兩個復選框。

檢查無誤之后，點擊“確定”按鈕，很快就可以獲得最終的求和結果，是不是很方便？當然，除了完成求和計算之外，我們還可以完成計數、平均值、最大值、最小值等其他的計算，此時并不需要重新開始，只要再次點擊“合并計算”按鈕，打開“合并計算”對話框，在這里直接重置相應的函數就可以了。

技巧九：利用函數實現字母編號的自動填充

我們知道，在Excel可以實現數字編號的自動填充，但遺憾的是Excel并未提供字母的自動填充功能，如果純粹采取手工填充的方法，既麻煩而且也不現實。其實，我們可以利用兩個函數解決這一問題：

首先請選中需要填充位置的空白單元格，例如A1，在公式編輯欄填入計算公式“=CHAR（65+COLUMN（）-1）”，“CHAR”函數的作用是根據本機中的字符集返回由代碼數字指定的字符，“65”表示字母A的ASCII代碼，使用CHAR函數讀取A1單元格時，生成的字母為A，如果字母編號不是從A開始，那么請更換為其他的ASCII代碼，“COLUMN”函數是用來返回引用的列號，然后向右拖拽填充柄至指定位置，即可自動填充相應的字母，效果如圖9所示。

如果需要實現豎向填充，只要將公式更改為“=CHAR（65+ROW（）-1）”即可。如果需要填充小寫的字母，只要將ASCII代碼更改為97就可以了。

補充：如果不清楚ASCII代碼，可以從Word或Excel的“符號”對話框查詢。