【原】這30個Excel實用技巧，效率大神都忍不住收藏了。

盛夏職場工具箱 2019-08-25

展開全文

Excel在日常工作中的重要性，已無需贅言，然而，無論對于初學(xué)者和高級用戶，在實際應(yīng)用過程中，仍不可避免地忽略了很多實用的技巧和竅門。文章梳理了30個常用小技巧，分享給大家參考嘗試。

1、不復(fù)制隱藏的單元格

比如，截圖中5-8行單元格都隱藏掉了，復(fù)制的時候只想復(fù)制可見單元格。如果直接復(fù)制、粘貼，那被隱藏的數(shù)據(jù)也會被復(fù)制出來，怎樣才能不復(fù)制隱藏單元格呢？

方法1: 按“Ctrl + A”選中表格，接著再按“Alt + ;（分號）”選中可見單元格，然后，按“Ctrl + C”和“Ctrl + V”復(fù)制粘貼即可。

方法2: 首先按“Ctrl + A”選中表格，接著按“Ctrl + G”打開定位窗口，設(shè)置定位條件為“可見單元格”，最后再復(fù)制粘貼即可。

2、批量刪除空行

選取工作表范圍，按CTRL+G定位，定位條件為-空值，然后刪除行。

3、下拉菜單的制作方法

操作步驟：選中要插入下拉菜單的單元格 - 數(shù)據(jù) - 數(shù)據(jù)有效性 - 序列 - 在下面的來源框中輸入以逗號分隔的字符串。注意逗號是英文的逗號，不是中文。

4、單元格內(nèi)強制換行

按Alt+Enter可以強制換行。

5、鎖定標題行

如果數(shù)據(jù)太多，當表格滾動到后面時，標題行就不見了，這對于查看數(shù)據(jù)來說極為不便，如何使Excel某一行固定不動？

步驟：視圖 - 凍結(jié)窗格 - 凍結(jié)首行，就可以鎖定第一行不動。如果是凍結(jié)第2行，就選取第3行 - 窗口 - 凍結(jié)窗口，以此類推。如果想撤銷凍結(jié)，可以點擊窗口-解凍一項，就恢復(fù)了表格。

6、刪除重復(fù)值

方法1：選中單元格范圍 - 數(shù)據(jù) - 刪除重復(fù)值，可以按某列刪除重復(fù)，也可以按整行判斷重復(fù)。

方法2：選中單元格范圍 - 數(shù)據(jù) - 篩選 - 高級篩選，在篩選時勾選“選擇不重的記錄”。

7、同時查看多個Excel文件

同時查看同一個工作簿的2個或2個以上工作表，要先視圖-新建窗口，然后再點全部重排，重排可以根據(jù)自己需要垂直并排或者水平并排。效果如圖。

8、日期格式的轉(zhuǎn)換

方法:1：選定數(shù)據(jù)范圍 - 數(shù)據(jù) - 分列 - 分隔符號 - tab鍵 - 日期 - 確定。

方法2：用text函數(shù)公式：=--TEXT(B2,"0-00-00")。需要注意：Text轉(zhuǎn)換的是文本型日期，加--可以轉(zhuǎn)換為數(shù)值型日期。如圖。

9、單元格添加對角線

選擇要添加對角線的單元格 - 右鍵“單元格格式”- 邊框 - 選擇對角線（參考截圖）；接下來在單元格內(nèi)輸入內(nèi)容，比如，先輸入“日期項目”4個字，接著把鼠標定位在"日期后面，按Alt+回車,"項目"就強制換到第二行了，然后，在“日期”前面按空格到合適的位置即可。

10、隔行填色

步驟：選定需要隔行填色的目標單元格 - 條件格式 - 新建規(guī)則 - 使用公式確定要設(shè)置格式的單元格 - 輸入公式：=mod(row(),2)=1 - 單擊右下角的“格式”-填充 - 選取填充色并點確定 - 確定。

注：公式=mod(row(),2)=1，是判斷當前行是不是奇數(shù)行，如果是奇數(shù)行，則執(zhí)行“格式”命令，否則不執(zhí)行。

11、快速插入或刪除行

快速插入：選取需要插入的行數(shù)，按快捷鍵 Ctrl+Shift++（加號），則新行在當前選中行的上方插入。

快速刪除：選擇目標行，按快捷鍵Ctrl+-（減號）即可，其他行自動向上移動。

12、統(tǒng)一添加單位或后綴

選中要添加后綴的單元格，右擊選擇“設(shè)置單元格格式”，打開單元格格式設(shè)置窗口。在“數(shù)字”下選擇“自定義“----G/通用格式，在后方輸入字符“萬元”，確定即可。

13、統(tǒng)一添加前綴

和添加后綴類似，右擊選中“設(shè)置單元格格式”- 數(shù)字 - 自定義 - 通用格式，區(qū)別是在最前邊加上要添加的后綴，比如"收入-"，注意添加的前綴必須用英文雙引號（""）括起來。

14、對齊長度不一的字符

選中目標單元格，快捷鍵Ctrl+1打開“設(shè)置單元格格式”，選擇“對齊”- 水平對齊 -分散對齊（縮進）- 確定。

15、跨表格復(fù)制粘貼

同一個工作簿，如果多個sheet都想填充同樣的內(nèi)容，可以參照如下方式。

按Shift鍵，同時選中多個工作表，然后選擇要復(fù)制的區(qū)域，接著點擊：開始——編輯——填充——成組工作表，即可。

16、快速生成圖表

方法1：選擇需要繪制的數(shù)據(jù)，按下F11鍵，Excel將自動為創(chuàng)建圖表。

方法2：選好要繪制圖表的數(shù)據(jù)，按下"Alt+F1"，也可以得到相同的結(jié)果。

17、批量求和

如果連續(xù)求和區(qū)域的右邊一列和下面一行為空白，可以用鼠標將此區(qū)域選中，注意要包含其右邊一列或下面一行。然后，單擊"Σ"求和圖標，則選中區(qū)域的右邊一列或下面一行自動生成求和公式，而且系統(tǒng)能自動識別選中區(qū)域中的非數(shù)值型單元格，求和公式不容易產(chǎn)生錯誤。如圖。

18、保持序號連續(xù)性

在處理表格數(shù)據(jù)時，經(jīng)常對指定行數(shù)進行刪減或隱藏等操作，這時如何保持序號連續(xù)性呢？可以使用SUBTOTAL函數(shù)，在A2單元格輸入公式：=SUBTOTAL(103,$B$2:B2)，往下填充即可，如圖，輸入公式后，隱藏了4、5、6三行，序號也可以保持連續(xù)性。

19、文字快速分行

對Excel表格使用不太熟練的人，常常會將所有的內(nèi)容全部輸入到一個單元格內(nèi)，如何將這些內(nèi)容，分到不同行當中呢？舉例，將截圖中單元格數(shù)據(jù)每兩個字分一行，填充到不同行。

操作步驟：將文字所在單元格寬度調(diào)整為大致2個字的寬度，然后點擊開始 - 填充 - 兩端對齊 - 確定。

20、快速“行轉(zhuǎn)列，列轉(zhuǎn)行”

在制表過程中，經(jīng)常會對行列進行轉(zhuǎn)換，如何才能快速有效的轉(zhuǎn)換呢？最簡單的方法就是，先復(fù)制表格，然后，右鍵“選擇性粘貼”，勾選“轉(zhuǎn)置”，確定即可。如圖效果。

21、比CTRL+A還簡單的全選方式

我們多數(shù)人在進行文檔全選時候，都會使用CTRL + A快捷鍵，不過，還有一個更快的方式，可以嘗試，就是點擊文檔左上角的按鈕，如截圖位置，會更快。

22、同時打開多個Excel表格

當你需要處理多個文件時，一個個打開太麻煩了，可以嘗試一次性選中所有要打開的文檔，然后點擊回車鍵，就可以一次性搞定。

23、快速移動或復(fù)制數(shù)據(jù)

在Excel表格中，如果想要快速移動一列數(shù)據(jù)，簡單方法就是選擇它，然后將指針移動到邊境后，它會變成一個十字箭頭圖標，接著拖動此列到目標位置即可。如果你要復(fù)制的此數(shù)據(jù)，同樣可以按此方法來移動，區(qū)別就是拖動時候，按住Ctrl鍵。

24、模糊搜索

Excel工作中，常常會遇到無法精準錄入或查找的情況，不得不采用關(guān)鍵詞進行模糊查找，具體如何操作呢？首先，在某個單元格輸入要查找數(shù)據(jù)的關(guān)鍵詞或簡稱，然后，在另外一個單元格輸入公式=VLOOKUP("*"&D2&"*",B2:B8,1,)。

解析：B2:B8是查找范圍；D2是關(guān)鍵字所在單元格；函數(shù)中“*”代表任意字符，也就是在B2:B8內(nèi)查找D2文本前后任意字符的內(nèi)容，即，包含D2文本的意思。其中的1代表返回B2:B8數(shù)據(jù)區(qū)域第一列結(jié)果，最后一個逗號后省略參數(shù)代表的是精確匹配，也可以輸入0或FALSE。

25、輸限制與數(shù)據(jù)驗證功能

為了保持數(shù)據(jù)的有效性，有時需要限制輸入值，并提供一些建議步驟。例如，做問卷調(diào)查，某一列要輸入年齡，那這列數(shù)據(jù)內(nèi)容，就要限定為整數(shù)，且要求參與本次調(diào)查的所有的人應(yīng)該是18至60歲。要確保這個年齡范圍之外的數(shù)據(jù)沒有進入，就要設(shè)置如下限制：點擊數(shù)據(jù) - 數(shù)據(jù)有效性 - 依次進行“設(shè)置”-“輸入信息”-“出錯警告”設(shè)置。

輸入信息內(nèi)容參考：“請輸入您的年齡，限定為整數(shù)，且范圍為18到60。如果實際輸入的內(nèi)容不符合以上要求，則會收到警告信息。

26、轉(zhuǎn)變文本的大小寫

比如，要把=E列的英文字母，分別轉(zhuǎn)換成F列的大寫，以及G列的小寫。操作方法如下：大寫=UPPER(E2)；小寫=Lower（E2），E2為轉(zhuǎn)換前的文本位置。

27、輸入值要從0開始，該如何輸入？

當輸入值從0開始（比如0001,0002），Excel將在默認情況下，刪除這些零，而不是重新設(shè)置單元格格式。要解決這個問題，很簡單，只需要在第一個零前邊，增加一個單引號即可。

28、“雙擊”，重命名工作表

有多種方法來重命名工作表，比如，點擊鼠標右鍵，選擇重命名。不過，還有更簡單的方式，就是雙擊，然后就可以直接將其重命名。

29、文檔限定編輯范圍

涉及多人操作或者跨部門合作用表等情況下，為防止表格內(nèi)容被誤操作，可以限定工作表可編輯的范圍。

操作方法：選擇允許編輯的區(qū)域(例如手機號這里) - 點擊菜單“審閱”-“允許用戶編輯的區(qū)域”-“保護工作表” -勾選“選定鎖定單元格”和“選定未鎖定的單元格”，點擊確定。這樣就只有手機號一列選定框范圍可以編輯，而試圖更改該區(qū)域外的任意單元格會提示只讀。

30、文檔加密和解密

Excel2003版本：點擊“工具”菜單 - 選擇“選項”命令 - 安全性 - 在“打開權(quán)限密碼”后面輸入密碼 - 重新輸入密碼，確定。