第1章 職場必備Excel技能

在使用Excel 2016時，首先要熟悉軟件功能和常用操作技巧。這些功能和技巧是數據管理工作中必備的，本章就從Excel 2016軟件的界面功能、實用技能和多表操作三個方面來進行介紹。

本章內容知識點的思維導圖見圖1-1。

圖1-1 第1章內容思維導圖

1.1 Excel 2016界面工具

Excel 2016在界面和功能上都做了很多改進。如果對于軟件界面不熟悉，可以通過本節介紹的Tell Me按鈕和快速訪問工具，來搜索和收藏自己熟悉的命令按鈕。

1.1.1 Tell Me（告訴你）按鈕在哪里

使用Office 2016時，很多人在使用時找不到常用的命令。注意到圖1-2軟件右上方的小燈泡了嗎？

圖1-2 操作搜索

這是微軟在Office軟件新版本中，讓用戶獲取幫助的一種新方式——搜索與幫助（Tell Me，以下簡稱TM）。只需在TM框中輸入操作需求，它便會提供相關操作的命令或幫助，從而大大提高工作效率。

應用情景 1

糟糕，又忘了VLOOKUP函數怎么用了？

別急，只需單擊TM框或按快捷鍵【Alt+Q】后，輸入“VLOOKUP”，就可以查看幫助文檔，如圖1-3所示。

圖1-3 通過TM框獲取函數VLOOKUP函數幫助

應用情景 2

我要做多個表格數據匯總，不知道用什么工具按鈕？

只需在TM框“匯總”，就可以智能搜索到所有相關工具，單擊想要的工具即可。比如“合并數據”，單擊后自動打開“合并計算”工具窗口，如圖1-4所示。

圖1-4 通過TM框智能搜索命令工具

1.1.2 快速訪問工具欄

默認情況下，Excel 2016快速訪問工具欄只包含4個按鈕，分別是“保存”“撤銷”“恢復”和“觸摸/鼠標模式”。有的人很少去使用它們，如圖1-5所示。

圖1-5 默認快速訪問工具欄

快速訪問工具欄的功能從名稱上就能看出來，就是能自定義用戶經常使用的按鈕，并快速找到它。不要小瞧這個工具，除了能提高工作效率外，還能把看不到的按鈕添加上去。

下面介紹如何為快速訪問工具欄添加命令按鈕，分別是系統命令、常用命令和后臺命令。

1.添加系統命令按鈕

單擊工具欄右側的下拉按鈕，在彈出的菜單中選擇要添加到快速訪問工具欄中的命令，如圖1-6所示。已添加的命令左側會顯示一個對號標記。

圖1-6 為快速訪問工具欄添加系統命令

2.添加常用命令

右擊功能區中經常使用的命令圖標，在彈出的菜單中選擇“添加到快速訪問工具欄”，如圖1-7所示。

圖1-7 右鍵添加常用命令到快速訪問工具欄

如果要添加的命令沒有出現在功能區中，或者希望對快速訪問工具欄中的命令位置進行重新排列，那么需要使用下面介紹的第3種方法。

3.添加后臺命令

在快速訪問工具欄上或者功能區任意位置，單擊鼠標右鍵并選擇【自定義快速訪問工具欄】命令，進入自定義快速訪問工具欄界面，如圖1-8所示。

圖1-8 自定義快速訪問工具欄

從左側列表框中選擇要添加的命令，通過單擊“添加”按鈕將所選命令添加到右側列表框中，如圖1-9所示?？梢栽谧髠取皬南铝形恢眠x擇命令”下拉列表中選擇命令所屬的類型，下方的列表框會根據所選類型自動篩選其中包含的命令。左側命令列表是根據命令首字符排序的。對于右側列表框中的命令，可以在選中命令后，使用最右側的 和 按鈕調整命令順序。

圖1-9 為快速訪問工具欄添加后臺命令

圖1-9所示的是添加后臺命令“朗讀單元格”，這個功能適用于電子版與紙質版文件校對，也用于眼睛看文字和數據累的時候，閉上眼睛用耳朵聽計算機讀出這些文字和數據。關于朗讀單元格功能共有4個命令，可參考圖1-10。

圖1-10 朗讀單元格系列命令

在默認情況下，快速訪問工具欄位于標題欄的左側、功能區的上方。筆者習慣將其置于功能區下方，以便顯示更清晰。右擊快速訪問工具欄，在彈出的菜單中選擇“在功能區下方顯示快速訪問工具欄”命令，即可將其移動到功能區下方，效果如圖1-11所示。

圖1-11 將快速訪問工具欄置于功能區下方

如果辦公室的計算機中已經設置好了快速訪問工具欄，要在家中的計算機也做同樣的設置，可以用導入法。先在辦公室的計算機上的自定義功能區或自定義快速訪問工具欄窗口中單擊“導入/導出”-“導出所有自定義設置”，保存導出的OfficeUI文件，再將該文件復制到家中的計算機上，單擊“導入自定義文件”即可，如圖1-12所示。

圖1-12 快速訪問工具欄導入導出設置

1.1.3 恢復未保存文檔

有時在編輯Excel文檔時，由于斷電或其他原因導致文檔未保存就異常退出，當再次打開文檔時，卻發現之前編輯的內容不見了。那么該如何恢復沒有保存的Excel文檔呢？

首先要做的事情就是開啟Excel自動保存文檔功能，可以讓數據丟失的可能性減少到最低，如圖1-13所示，設置自動保存的時間和自動恢復的文件位置。

圖1-13 文檔自動保存設置

如果遇到異常情況，可以在之前所設置的“自動恢復文件位置”目錄中恢復未保存的文檔，也可以通過命令來恢復未保存的工作簿，如圖1-14所示，功能簡單卻可以解決大問題。筆者建議還是養成好的文檔保存習慣，在文檔編輯過程中經常保存，快捷鍵是【Ctrl+S】。

圖1-14 恢復未保存文檔設置

1.1.4 新工作簿的默認字體

在使用Excel時，Excel 2016版本默認字體是“等線”，Excel 2007-2013版本默認是“宋體”，要做調整時，每次都要全選所有單元格，然后將字體設置為“微軟雅黑”。更惱人的是，新建工作表時，又要重新設置一次。

其實不用這么麻煩，在Excel選項中可以設置新工作簿的默認字體，如圖1-15所示。只有重新啟動Excel軟件，新設置才能生效。

圖1-15 設置默認字體

1.2 實用技能

作者見過很多學員在數據表格的選擇和操作時，仍然采用手工的方法，費時費力。本節主要介紹數據的快速選擇和定位，選擇性粘貼和重復操作，讓讀者在面對規律性的數據選擇和操作上，更快一步。

1.2.1 區域選擇

要選取一個單元格區域，除了常規的鼠標拖動，還有其他哪些方法？如果有成千上萬行數據要選取，怎么操作最快？在包含隱藏行列的情況下，怎樣只選擇可見部分數據？其實，這些復雜的操作，只需幾個快捷鍵，就可以讓工作效率提升N倍。

1.選擇首、尾單元格

單擊任何一個單元格，按＜Ctrl+方向鍵＞，可以實現在同一行和同一列首末單元格，如圖1-16所示。如果有空白單元格，則定位到與空格相鄰的非空單元格。

圖1-16 定位行列首尾單元格

其實，在Excel中無論當前活動單元格在哪里，都可以通過＜Ctrl+Home＞和＜Ctrl+End＞快捷鍵來快速定位區域首末單元格，如圖1-17所示。

圖1-17 定位矩形區域首尾單元格

2.選擇連續區域

很多人選擇連續表格區域是直接按住鼠標向下拖動。如果是幾十萬行的大表格，這樣要拖到什么時候才能結束？如果要選擇連續區域，可以用快捷鍵＜Ctrl+Shift+方向鍵＞將當前活動單元格擴展到同一行或同一列中不間斷的最后一個非空單元格，如圖1-18所示。

圖1-18 選擇連續區域

3.選擇連續矩形區域

如果要選擇規范表格范圍，可以單擊數據區域的某一個單元格，然后使用＜Ctrl+A＞快捷鍵。這個快捷鍵可以選中活動單元格所在的【當前區域】，如圖1-19所示。

圖1-19 選擇連續矩形區域

所謂【當前區域】，指的是當前單元格周圍包含數據的連續相鄰（包括斜角方向相鄰）單元格的最大矩形范圍，工作中常常是無間斷的矩形區域。

4.選擇不連續區域

對于選擇不連續區域有兩種方法，快捷鍵不同，分別是＜Ctrl＞和＜Shift+F8＞。

按?。糃trl＞鍵同時用鼠標拖選區域，可以選中多個不連續的單元格區域，如圖1-20所示。

圖1-20 選擇不連續區域方法 1

也可以先按下＜Shift+F8＞快捷鍵，然后用鼠標選擇不同的區域即可，這種方法常用于選擇較多區域范圍，如圖1-21所示。

圖1-21 選擇不連續區域方法 2

這兩種方法選定的多個不連續區域稱之為“多重選定區域”，多重選定區域通常情況下不能進行整體復制和剪切操作（同行同列的除外），但是可以進行數據輸入、設置格式等基本操作。

5.選擇指定區域

例如要選取區域范圍A1：B100，最簡便的方法就是在左上角的名稱框中輸入【A1：B100】，按＜Enter＞鍵即可，如圖1-22所示。

圖1-22 選擇指定區域

1.2.2 選擇性粘貼

在使用Excel時，用得最多的功能恐怕就是復制、粘貼了。但許多人不清楚，Excel還有個非常實用的功能，就是選擇性粘貼，如圖1-23所示。

選擇性粘貼的快捷鍵是＜Ctrl+Alt+V＞，之后會出現圖1-23的命令窗口，也可以直接單擊鼠標右鍵，出現圖1-24所示的菜單。

下面介紹職場中選擇性粘貼常用的6個功能，如表1-1所示。

圖1-23 選擇性粘貼窗口

圖1-24 選擇性粘貼右鍵菜單

表1-1 選擇性粘貼常用的6個功能

1.粘貼值

通過函數公式計算的結果，如果引用數據刪除或將結果區域復制到新位置，常常會出現錯誤提示，解決辦法就是采用粘貼值的方法。復制包含公式的區域，單擊鼠標右鍵并單擊粘貼值按鈕 即可。在Excel 2007～2010版本中，單擊鼠標右鍵不會直接出現粘貼值按鈕 ，可以單擊【選擇性粘貼】，出現命令窗口后，選擇【數值】，如圖1-25所示。

圖1-25 粘貼數值

粘貼值功能去除了公式的同時，也去除了源數據單元格的格式。

2.保留原列寬

列寬不等的表格直接復制、粘貼后，列寬都發生了變化，再重新調整會耽誤不少時間。這時就要用到保留原列寬按鈕 ，如圖1-26所示，列寬和原表完全相同。

圖1-26 粘貼并保留原列寬

該功能同樣支持保留行高，前提是只有選取整行復制時，行的高度才會復制過去。

3.行列轉置

常常會遇到數據表格行、列互換的情況，這時可以在粘貼時選擇行列轉置按鈕 ，效果如圖1-27所示。

圖1-27 行列轉置效果

4.表格變圖片

如果希望單元格區域復制到新位置時顯示效果不變，可以用到粘貼圖片。在選擇性粘貼菜單中，按鈕 表示靜態圖片，圖片顯示內容不會根據源數據的變化自動更新；按鈕 表示動態圖片，圖片顯示內容可以根據源數據的變化自動更新，這也是常說的“照相機”的功能。

如圖1-28所示，有2015年～2017年3張年度銷售數據表，每張表格通過數據透視表匯總出當年的銷售數據，“分析表”是銷售數據分析表，通過【復制】和【鏈接圖片】的功能，可以將各表統計結果在首頁上快速顯示，并能根據每年數據變化而自動更新。

該功能常用于多工作表中數據圖表在另一張表上顯示，作為工作匯報的摘要頁面，快速顯示匯報結論，符合金字塔原理的“結論先行”原則。

需要說明的是，“鏈接圖片”功能僅支持Excel軟件不同工作簿或工作表之間的動態鏈接。如果把圖片復制到PowerPoint和Word軟件中，圖片不會根據源數據的變化自動更新。

圖1-28 表格變圖片效果示意

5.跳過空單元格

在復制、粘貼時，如果源數據中的空單元格不需要粘貼，就要用到【跳過空單元格】功能，如圖1-29所示。

圖1-29 跳過空單元格功能

這個功能常常用于數據的規范整理過程中。

示例1見圖1-30，將B列數據復制到A列，用【跳過空單元格】功能可以讓兩列文本快速合并成一列。

圖1-30 跳過空單元格示例 1

示例2見圖1-31，是對費用進行校對調整。B列是費用列表，C列是校對審核過的費用，其中C列空白單元格表示B列的費用無誤，C列有數據的單元格表示對應的B列的費用需要更新調整。將C列數據復制到B列，用【跳過空單元格】功能可以實現費用更新，實現的效果如E列。

圖1-31 跳過空單元格示例 2

6.批量運算

如果要對已有數據做批量加減乘除運算，一般都是通過函數計算得出的結果復制、粘貼到原來的位置。現在使用選擇性粘貼，可以在數據原本位置直接計算出結果。

工作中經常會遇到萬元與元的轉換，如圖1-32所示，單位是元的利潤表，需要轉換成單位是萬元。

圖1-32 單位是元的利潤表

只需在區域外單元格中輸入10000，復制10000，選中需要轉換的區域，調出選擇性粘貼框，選擇“數值”和“除”，然后確定就可以了，步驟如圖1-33所示。

圖1-33 選擇性粘貼運算

轉換后的表格如圖1-34所示。

圖1-34 單位是萬元的利潤表

如果在選擇性粘貼中沒有選擇第3步的“數值”，就會把10000所在單元格的格式也復制過去，影響表格的美觀。

工作中，以下這些場合都能用到此方法：

?對計量單位的轉換；

?文本形式存儲的數字轉換成數值格式可以用以上方法乘1或者除1；

?給指定區域的數值加、減、乘、除固定數值。

1.2.3 使用＜F4＞鍵重復上一次操作

是否經常遇到以下這幾種情形？

?手動標注多處問題數據；

?有多處要合并的單元格；

?在表中插入多行。

如果在Excel中需要多次重復操作，先給軟件做個示范，然后使用快捷鍵＜F4＞即可自動重復剛才的操作。具體在應用時要看使用的鍵盤，部分鍵盤需要輔助功能按鍵＜Fn+F4＞。

情形1：快速設置單元格格式

先手工設置某個單元格填充為紅色，然后選擇指定數據范圍，按＜F4＞鍵即可快速填充紅色，如圖1-35所示。

圖1-35 使用＜F4＞鍵設置單元格格式

情形2：快速合并單元格

如圖1-36所示，先手動合并7月的兩個單元格，然后選擇8月區域，按＜F4＞鍵快速進行合并，9月也是同樣的方法。

圖1-36 使用＜F4＞鍵設置合并單元格

情形3：批量插入或刪除空行

先手工在表中插入1行，然后選取指定行，按＜F4＞鍵就可以自動插入行，如圖1-37所示。

圖1-37 使用＜F4＞鍵可快速插入行

1.2.4 使用＜F5＞鍵快速定位

Excel軟件中的定位是十分有用的功能，它可以根據單元格數據或格式的屬性來有條件地選中這些單元格。按＜F5＞鍵或組合鍵＜Ctrl+G＞可以打開定位對話框，單擊【定位條件】就可以在其中選擇所需的條件，如圖1-38所示。常用的條件包括常量、公式、空值和可見單元格等。

圖1-38 定位與定位條件

在使用定位功能前，先選定一個區域，那就會在這個區域內查找和選取符合條件的單元格，否則就會在整個工作表中進行查找。

情形1：定位空值

當需要對報表中的空白單元格進行編輯時，如何快速選中空白單元格？選擇數據范圍，在定位對話框中選擇“空值”，可以快速選取區域內空白單元格，如圖1-39所示。

情形2：定位公式

對于報表，如果想知道哪些單元格數據是由公式計算出來的，可以用到定位中的“公式”，如圖1-40所示。這樣也可以快速對公式進行保護和隱藏。

圖1-39 定位空值

圖1-40 定位公式

情形3：定位文本型數字

有時候使用SUM求和的結果和手工計算不一致，很可能是文本型數字搞的鬼，在定位時選擇“常量”中的“文本”，能將文本型數字快速定位出來，如圖1-41所示。

圖1-41 定位文本型數字

情形4：定位差異數據

工作中經常需要核對兩行或兩列內容是否完全一致，除了可以用IF公式來判斷，還可以用定位功能來實現。如圖1-42所示，先選中需要比較的兩列數據

圖1-42 定位差異數據

（B2：B13），在定位條件中選擇“行內容差異單元格”，確定后把不同的5個單元格快速定位選擇出來了。

情形5：定位可見單元格

對做了數據篩選或者隱藏行列的表格，如果只選擇可見單元格，也可以用定位功能實現，如圖1-43所示。

圖1-43 定位可見單元格

該功能也可以使用快捷鍵＜ALT+；＞（分號；）快速實現。

1.3 多表操作

在處理多個表格數據時，是否遇到過下面這些囧境：

?在包含很多Sheet的Excel文件中，要找到其中一個Sheet，翻了半天還沒翻到那一頁；

?比較多個表格數據不斷切換窗口查看；

?多個相同結構的表格做同樣的操作，一個一個表格設置。

其實這些都是可以避免的，本節就介紹這些操作方法。

1.3.1 多工作表快速定位跳轉

多工作表之間定位和跳轉其實可以又快又準的。先找到工作表左下角的箭頭位置，如圖1-44所示。

圖1-44 多工作表之間定位跳轉位置

使用表1-2總結的快捷鍵可以實現工作表的快速定位和跳轉。

表1-2 多工作表之間定位跳轉的快捷鍵

需要說明的是，表中的“滾動”只是把工作表名稱從折疊狀態展開，需要單擊工作表名稱才能定位到該工作表；“定位”是直接選擇并激活指定工作表。

1.3.2 兩文件并排查看

在使用Excel的時候，都會遇到這樣的問題：需要查看多個工作簿，所以就不得不在各個工作簿之間來回切換。這樣操作不僅很麻煩，而且很容易出錯。這里就給大家介紹一下如何在一個窗口下同時顯示兩個工作簿，并能實現同步滾動，對于比較兩個文件的數據特別方便。

這個功能是“并排查看”，先看下它的按鈕位置，如圖1-45所示。如果這個按鈕不可用，說明還沒有打開兩個Excel文件窗口。

圖1-45 窗口并排查看的位置

比如打開了兩個文件，分別是“2015業務數據”和“2016業務數據”，第一次用“并排查看”功能時，兩個窗口有可能是上下排列，按圖1-46所示的3個步驟實現垂直排列，并能實現同步滾動。

圖1-46 并排查看方向調整

如果是一個Excel文件的兩個工作表，要實現并排查看，需按圖1-47所示位置新建窗口才可以。

圖1-47 新建窗口

1.3.3 工作組批量操作多工作表

在很多情況下，都會需要同時在多張工作表的相同單元格中輸入同樣的內容。

比如已有9個城市的1月銷售數據表，現在到2月份了，每個工作表的標題要統一修改為2月，可以按圖1-48的操作步驟進行批量操作。

圖1-48 工作組批量操作

工作組的操作可以批量輸入相同內容和公式，從而解決多工作表同步修改或計算的問題，節省工作時間。