第7章Excel應用7.1Excel制表基礎7.2工作簿與工作表的操作7.3數據分析與處理7.4Excel公式和函數7.5輸入和編輯數據7.6整理和修飾表格7.7創建圖表7.8打印輸出工作表習題

7.1Excel制表基礎

1．Excel文件MicrosoftExcel2010版本的Excel文件擴展名是“.xlsx”，Excel模板擴展名是“.xltx”，Excel加載宏的擴展名是“.xlsm”。

2．Excel的幫助

Excel在啟動狀態中可以按下“F1”鍵打開幫助窗口，在彈出的“Office助手”對話框中選擇“目錄”選項卡，然后選擇相應的一條求助目錄，在右部窗格就顯示出相關的幫助信息。

3．Excel窗口界面

Excel窗口界面如圖7.1所示。Excel的主功能區由標題欄、選項卡、功能區、狀態欄、滾動條等組成。下面介紹一些Excel特有的常用術語。

圖7.1Excel的窗口界面

(1)工作簿與工作表：一個工作簿就是一個電子表格文件。一個工作簿可以包含多張工作表，默認情況下為三個，分別以Sheetl、Sheet2、Sheet3命名。一張工作表就是一張規整的表格，由若干行和若干列構成。

(2)工作表標簽：一般位于工作表的下方，用于顯示工作表名稱。用鼠標單擊工作表標簽，可以在不同的工作表間切換，當前可以編輯的工作表稱為活動工作表。

(3)行號：每一行左側的阿拉伯數字為行號，表示該行的行數，對應稱為第1行、第2行……。

(4)列號：每一列上方的大寫英文字母為列號，代表該列的列名，對應稱為A列、B列、C列……。

(5)單元格、單元格地址與活動單元格：每一行和每一列交叉處的長方形區域稱為單元格，單元格為Excel操作的最小對象。單元格所在行、列的行號和列號形成單元格地址，猶如單元格的內在名稱，如A1單元格、C3單元格……在工作表中將鼠標光標指向某個單元格后單擊，該單元格被粗黑框標出(如圖7.1中的A1單元格)，稱為活動單元格，活動單元格是當前可以操作的單元格。

(6)名稱框：一般位于工作表的左上方，其中顯示活動單元格的地址或已命名的活動單元格或區域的名稱。

(7)編輯欄：位于名稱框右側，用于顯示、輸入、編輯、修改當前活動單元格中的內容。

7.2工作簿與工作表的操作7.2.1工作簿基本操作1.創建一個工作簿默認情況下，啟動Excel時將會自動打開一個空白工作簿。自行創建一個新工作簿的基本方法是：啟動Excel后，從“文件”選項卡上單擊“新建”，右側顯示可用模板列表，如圖7.2所示，其中：

(1)創建空白文檔。

(2)基于模板創建。圖7.2新建工作簿

2.保存工作簿并為其設置密碼

可以在保存工作簿文檔時為其設置打開或修改密碼，以保證數據的安全性。具體設置方法如下：

(1)從“文件”選項卡上單擊“另存為”命令(如果是尚未保存過的新文檔，也可通過“快速訪問工具欄”中的“保存”按鈕，或者“文件”選項卡上的“保存”命令)，打開“另存為”對話框。

(2)依次選擇保存位置、保存類型，并輸入文件名。

(3)單擊“另存為”對話框右下方的“工具”按鈕，從打開的下拉列表中選擇“常規選項”，打開“常規選項”對話框。

(4)在文本框中輸入密碼，所輸入的密碼以“*”顯示。若設置“打開權限密碼”，則在打開工作簿時需要輸入該密碼；若設置“修改權限密碼”，則在對工作簿中的數據進行修改時需要輸入該密碼；當選中“建議只讀”復選框時，在下次打開該文檔時將提示可以以只讀方式打開。上述三項可以只設置一項，也可以三項全部設置。如果要取消密碼，只需再次進入到“常規選項”對話框中刪除密碼即可。

(5)單擊“確定”按鈕，在隨后彈出的“確認密碼”對話框中再次輸入相同的密碼并確定。最后單擊“保存”按鈕。

提示已經保存過的文檔，經過修改后再次單擊“快速訪問工具欄”中的“保存”按鈕，或者從“文件”選項卡上單擊“保存”，將不會再彈出對話框。如果需要將文檔換個文件名保存，或者重新設置密碼，就需要從“文件”選項卡上單擊“另存為”命令，才會彈出相應對話框。

3.關閉工作簿與退出Excel

要想只關閉當前工作簿而不影響其他正在打開的Excel文檔，可從“文件”選項卡上單擊“關閉”命令；要想退出Excel程序，可從“文件”選項卡上單擊“退出”命令，如果有未保存的文檔，將會出現提示保存的對話框。

4.打開工作簿

常用的打開工作簿方法有以下幾種：

(1)直接在資源管理器的文件夾下找到相應的Excel文檔，用鼠標雙擊即可打開。

(2)啟動Excel，從“文件”選項卡上單擊“最近所用文件”命令，右側的文件列表中顯示最近編輯過的Excel工作簿名稱，單擊需要的文件名即可將其打開。

(3)啟動Excel，從“文件”選項卡上單擊“打開”命令，在彈出的“打開”對話框中選擇相應的文件名。

7.2.2創建和使用工作簿模板

模板是一種文檔類型。根據日常工作和生活的需要，模板中已事先添加了一些常用的文本或數據，并進行了適當的格式化，模板中還可以包含公式和宏，并以一定的文件類型保存在特定的位置。

在模板的基礎上進行簡單的修改，就可以快速完成類似的文檔的創建，而不必從空白頁面開始。使用模板是節省時間和創建格式統一的文檔的絕佳方式。

Excel本身提供大量內置模板可供選用，Excel2010模板文件的后綴名為.xltx。另外，還可以自行創建模板并使用。

1.創建一個模板

①打開要用作模板基礎的工作簿文檔。

②對工作簿中的內容進行調整修改。模板中只需要包含一些每個類似文件都有的公用項目，而對于那些不同的內容可以刪除，格式和公式應該保留。

③在“文件”選項卡上單擊“另存為”命令，打開“另存為”對話框。

④在“文件名”框中輸入模板的名稱。

⑤打開如圖7.3所示的“保存類型”列表，從中單擊“Excel模板”命令。

圖7.3在“另存為”對話框中選擇“保存類型”

⑥單擊“保存”按鈕，新建模板將會自動存放在Excel的模板文件夾中以供調用。

⑦關閉該模板文檔。

提示在“另存為”對話框中不要改變文檔的存放位置，以確保在需要用該模板創建新工作簿時該模板可以被調用。

2.使用自定義模板創建新工作簿

①啟動Excel，單擊“文件”選項卡上的“新建”命令。

②在“可用模板”下單擊“我的模板”，打開一個“新建”對話框，如圖7.4所示。

③在“個人模板”列表中雙擊要使用的模板。

④輸入新的數據，進行適當的格式調整，然后將該文檔保存為正常的Excel工作簿即可。圖7.4“新建”對話框中的“個人模板”列表

3.修改模板

在基于模板創建的新文件中進行修改調整，并不會對模板本身產生影響。如果要對模板本身進行編輯修改，應從“文件”選項卡上單擊“打開”命令，選擇模板文件存放的位置，找到要編輯的模板名并打開它進行修改。Excel2010默認的模板文件保存位置為：C:\Users\[實際的用戶名]\AppData\Roaming\Microsoft\Templates。

7.2.3隱藏與保護工作簿

1.隱藏工作簿

當在Excel中同時打開多個工作簿時，可以暫時隱藏其中的一個或幾個，需要時再顯示出來。基本方法：首先切換到需要隱藏的工作簿窗口，單擊“視圖”選項卡，在如圖7.5所示的“窗口”組中單擊“隱藏”按鈕，當前工作簿就會被隱藏起來。

如要取消隱藏，在“視圖”選項卡上的“窗口”組中單擊“取消隱藏”按鈕，在打開的“取消隱藏”對話框中選擇需要取消隱藏的工作簿名稱，再單擊“確定”按鈕即可。圖7.5“視圖”選項卡上的“窗口”組

2.保護工作簿

當不希望他人對工作簿的結構或窗口進行改變時，可以設置工作簿保護，基本方法如下：

提示此處的工作簿保護不能阻止他人更改工作簿中的數據。如果想要達到保護數據的目的，可以進一步設置工作表保護，或者在保存工作簿文檔時設定密碼。

①打開需要保護的工作簿文檔。

②在“審閱”選項卡上的“更改”組中，單擊“保護工作簿”按鈕，打開“保護結構和窗口”對話框，如圖7.6所示。圖7.6通過“更改”組打開“保護結構和窗口”對話框

③在對話框中按照需要進行各項設置，其中：

選中“結構”復選框，將阻止他人對工作簿的結構進行修改，包括查看已隱藏的工作表，移動、刪除、隱藏工作表或更改工作表的表名，插入新工作表，將工作表移動或復制到另一工作簿中等。

選中“窗口”復選框，將阻止他人修改工作簿窗口的大小和位置，包括移動窗口、調整窗口大小或關閉窗口等。

④如果要防止他人取消工作簿保護，可在“密碼(可選)”框中輸入密碼，單擊“確定”按鈕，在隨后彈出的對話框中再次輸入相同的密碼進行確認。

提示如果不提供密碼，則任何人都可以取消對工作簿的保護。如果使用密碼，一定要牢記自己的密碼，否則自己也無法再對工作簿的結構和窗口進行設置。

如要取消對工作簿的保護，只需再次在“審閱”選項卡上的“更改”組中單擊“保護工作簿”按鈕，如果設置了密碼，則在彈出的對話框中輸入密碼即可。

7.2.4工作表基本操作

1.插入工作表

默認情況下，一個空白的工作簿中包含三張工作表。增加工作表的方法有以下三種。

(1)單擊工作表標簽右邊的“插入工作表”按鈕，可在最右邊插入一張空白工作表。

(2)在工作表標簽上單擊鼠標右鍵，在彈出的快捷菜單中單擊“插入”命令，打開“插入”對話框，如圖7.7所示，從中雙擊表格類型。雙擊其中的“工作表”將會在當前工作表前插入一張空白工作表。

(3)在“開始”選項卡上的“單元格”組中單擊“插入”按鈕旁的黑色箭頭，從打開的下拉列表中單擊“工作表”，即可插入一張空白工作表。圖7.7通過鼠標右鍵快捷菜單打開“插入”對話框

2.刪除工作表

在要刪除的工作表標簽上單擊鼠標右鍵，從彈岀的快捷菜單中選擇“刪除”命令，即可刪除當前選定的工作表。

3.改變工作表名稱

在工作表標簽上雙擊鼠標，或者在“開始”選項卡的“單元格”組中單擊打開“格式”列表，從“組織工作表”下選擇“重命名工作表”命令，此時工作表標簽名進入編輯狀態，輸入新的工作表名后按Enter鍵確認修改。

4.設置工作表標簽顏色

為工作表標簽設置顏色可以突出顯示某張工作表。

在要改變顏色的工作表標簽上單擊鼠標右鍵，彈出快捷菜單，將光標指向“工作表標簽顏色”命令；或者在“開始”選項卡的“單元格”組中單擊打開“格式”列表，從“組織工作表”下選擇“工作表標簽顏色”命令，從顏色列表中單擊選擇一種顏色。

5.移動或復制工作表

可以通過移動操作在同一工作簿中改變工作表的位置或將工作表移動到另外一個工作簿中，或通過復制操作在同一工作簿或不同的工作簿中快速生成工作表的副本。

①首先打開工作簿文檔，在需要移動或復制的工作表標簽上單擊鼠標右鍵，從彈出的快捷菜單中選擇“移動或復制”命令；或者在“開始”選項卡上的“單元格”組中單擊打開“格式”列表，從“組織工作表”下選擇“移動或復制工作表”命令，打開“移動或復制工作表”對話框，如圖7.8所示。圖7.8“移動或復制工作表”對話框

③在“下列選定工作表之前”中指定工作表要插入的位置。

④如果要復制工作表，需要單擊選中“建立副本”復選框，否則將會移動工作表。

⑤單擊“確定”按鈕。所選工作表將被移動或復制到新的位置，如果是移動或復制到另一個工作簿，則自動切換到新工作簿窗口。

6.顯示或隱藏工作表

在要隱藏的工作表標簽上單擊鼠標右鍵，從彈出的快捷菜單中選擇“隱藏”命令；或者在“開始”選項卡上的“單元格”組中單擊“格式”命令，打開“格式”列表，從“隱藏或取消隱藏”下選擇“隱藏工作表”命令。

如果要取消隱藏，只需從上述相應菜單中選擇“取消隱藏”命令，在打開的“取消隱藏”對話框中選擇相應的工作表即可。

7.2.5工作表的保護

1.保護整個工作表

保護整個工作表，使得任何一個單元格都不允許被更改的方法如下。

①打開工作簿，選擇需要設置保護的工作表。

②在“審閱”選項卡上的“更改”組中，單擊“保護工作表”按鈕，打開如圖7.9所示的“保護工作表”對話框。

③在“允許此工作表的所有用戶進行”列表中，選擇允許他人能夠更改的項目。圖7.9“保護工作表”對話框

④在“取消工作表保護時使用的密碼”框中輸入密碼，該密碼用于設置者取消保護，要牢記自己的密碼。

⑤單擊“確定”按鈕，重復確認密碼后完成設置。此時，在被保護工作表的任意一個單元格中試圖輸入數據或更改格式時，均會出現如圖7.10所示的提示信息。圖7.10設置保護工作表后的提示信息

2.取消工作表的保護

①選擇已設置保護的工作表，在“審閱”選項卡上的“更改”組中單擊“撤銷工作表保護”，打開“撤銷工作表保護”對話框。

提示在工作表受保護時，“保護工作表”按鈕會變為“撤銷工作表保護”。

②在“密碼”框中輸入設置保護時使用的密碼，單擊“確定”按鈕。如果未設密碼，則會直接取消保護狀態。

3.解除對部分工作表區域的保護

保護工作表后，默認情況下所有單元格都將無法被編輯。但在實際工作中，有些單元格中的原始數據還是允許輸入和編輯的，為了能夠更改這些特定的單元格，可以在保護工作表之前先取消對這些單元格的鎖定。

①選擇要設置保護的工作表。如果工作表已被保護，則需要先在“審閱”選項卡上的“更改”組中，單擊“撤銷工作表保護”撤銷保護。

②在工作表中選擇要解除鎖定的單元格或單元格區域。

③在“開始”選項卡上的“單元格”組中，單擊“格式”按鈕，從打開的下拉列表中選擇“設置單元格格式”命令，打開“設置單元格格式”對話框。

④在“保護”選項卡下，單擊“鎖定”取消對該復選框的選擇，如圖7.11所示。單擊“確定”按鈕，當前選定的單元格區域將會被排除在保護范圍之外。圖7.11在“設置單元格格式”對話框的“保護”選項卡下解除“鎖定”

⑤設置隱藏公式。如果不希望他人看到公式或函數的構成，可以設置隱藏該公式。在工作表中選擇需要隱藏的公式所在的單元格區域，再次打開“設置單元格格式”對話框，在“保護”選項卡中保證“鎖定”復選框被選中的同時再單擊選中“隱藏”復選框，單擊“確定”按鈕。此時，公式不但不能修改還不能被看到。

⑥在“審閱”選項卡上的“更改”組中，單擊“保護工作表”，打開“保護工作表”對話框。

⑦輸入保護密碼，在“允許此工作表的所有用戶進行”列表中設定允許他人能夠更改的項目后，單擊“確定”按鈕。

4.允許特定用戶編輯受保護的工作表區域

如果一臺計算機中有多個用戶，或者在一個工作組中包括多臺計算機，那么可通過該項設置允許其他用戶編輯工作表中指定的單元格區域，以實現數據共享。

①選擇要進行設置的工作表區域，如果已設置工作表保護，則需要先撤銷保護。

②在“審閱”選項卡上的“更改”組中，單擊“允許用戶編輯區域”按鈕，打開如圖7.12(a)所示的“允許用戶編輯區域”對話框。

③單擊“新建”按鈕，打開如圖7.12(b)所示的“新區域”對話框，添加一個新的可編輯區域，默認為當前選定的區域。在對話框中為所選區域輸入一個標題名稱，并可輸入訪問密碼。

④單擊“權限”按鈕，在彈出的“權限”對話框中指定可以訪問該區域的用戶，單擊“確定”按鈕。

⑤單擊“允許用戶編輯區域”對話框左下角的“保護工作表”按鈕，在隨后彈出的對話框中設定保護密碼及可更改項目。

(a)“允許用戶編輯區域”對話框(b)“新區域”對話框圖7.12設定允許用戶編輯的區域

7.2.6同時對多張工作表進行操作

1.選擇多張工作表

(1)選擇全部工作表：在某個工作表標簽上單擊鼠標右鍵，從彈出的快捷菜單中選擇“選定全部工作表”命令，可以選擇當前工作簿中的所有工作表。被選中的工作表標簽將會反白顯示。

(2)選擇連續的多張工作表：在首張工作表標簽上單擊，按下Shift鍵不放，再在最后一張工作表標簽上單擊，即可選擇連續的一組工作表。

(3)選擇不連續的多張工作表：在某張工作表標簽上單擊，按下Ctrl鍵不放，再依次單擊其他工作表標簽，即可選擇不連續的一組工作表。

(4)取消工作表組合：單擊組合工作表以外的任一張工作表，或者從右鍵快捷菜單中選擇“取消組合工作表”命令，即可取消成組選擇。

當進行了多張工作表組合以后，工作簿標題欄中的文件名之后將會增加“［工作組］”字樣，如圖7.13所示。圖7.13多張工作表組合后標題欄顯示［工作組］

2.同時對多張工作表進行操作

形成工作表組合后，在其中一張工作表中所做的任何操作都會同時反映到同組的其他工作表中，這樣可以快速格式化一組工作表或在一組工作表中輸入相同的數據和公式等。

3.填充成組工作表

可以先在一張工作表中輸入數據并進行格式化操作，然后將這張工作表中的內容及格式填充到同組的其他工作表中，以便快速生成一組基本結構相同的工作表，具體方法如下。

①首先在一張工作表中輸入基礎數據，并對數據進行格式化操作。

②然后插入多張空表。

③在首張工作表中選擇包含填充內容及格式的單元格區域，然后選擇其他工作表以形成工作組。

④在“開始”選項卡上的“編輯”組中，單擊“填充”按鈕，從下拉列表中選擇“成組工作表”命令，打開“填充成組工作表”對話框，如圖7.14所示。圖7.14打開“填充成組工作表”對話框

⑤從“填充”區域下選擇需要填充的項目，單擊“確定”按鈕。其中，“全部”將復制選中的包括格式及數據的全部內容，“內容”將只復制選中的數據內容，而“格式”將只復制選中的格式。

⑥此時在某一張工作表中輸入數據、設置格式，均會同時顯示在同組的其他工作表中。

⑦在Sheetl表標簽上單擊鼠標，退出工作組狀態。查看各個工作表，看是否已應用相同的表格數據及格式。

7.2.7工作窗口的視圖控制

當表格中的數據量大到超過一屏時，對工作窗口的視圖控制就變得重要起來。靈活地控制視圖顯示，可以提高表格查看及編輯的速度。

1.多窗口顯示與切換

在Excel中，可以同時打開多個工作簿。當一個工作簿中的工作表很大，大到一個窗口中很難顯示出全部的行或列時，還可以將工作表劃分為多個臨時窗口，這樣可以進行方便地排列及切換，以便于比較及引用。

(1)定義窗口：打開一個工作簿，在一個工作表中選擇某個區域，在“視圖”選項卡上的“窗口”組中(如圖7.15所示)單擊“新建窗口”按鈕，被選定區域即會顯示在一個新的窗口中。圖7.15“視圖”選項卡上的“窗口”組

(2)切換窗口：在“視圖”選項卡的“窗口”組中單擊“切換窗口”，打開的下拉列表中將顯示所有窗口名稱。其中工作簿以文件名顯示，工作表中劃分出的窗口則以“工作簿名：序號”的形式顯示。單擊其中的窗口名稱，即可切換到該窗口。

(3)并排查看：用于按上下排列的方式比較兩個工作窗口中的內容。首先切換到一個待比較的窗口中，然后在“視圖”選項卡上的“窗口”組中單擊“并排查看”，打開如“并排比較”對話框，從中選擇另一個用于比較的窗口，單擊“確定”按鈕，兩個窗口將并排顯示。默認情況下，操作一個窗口中的滾動條，另一個窗口將會同步滾動。在“視圖”選項卡上的“窗口”組中單擊“同步滾動”可取消兩個窗口的聯動，再次單擊“并排查看”可取消并排比較。

(4)全部重排：要想同時查看所有打開的窗口，可在“視圖”選項卡上的“窗口”組中單擊“全部重排”，打開“重排窗口”對話框，在“排列方式”下選擇顯示方式，如果選中“當前活動工作簿的窗口”，則只對當前工作簿中已劃分的窗口進行排列，而不考慮其他已打開的工作簿。

(5)隱藏窗口：首先切換到要隱藏的窗口，在“視圖”選項卡上的“窗口”組中單擊“隱藏”即可。如要取消隱藏，可在“窗口”組中單擊“取消隱藏”，從打開的對話框中選擇需要顯示的窗口名稱即可。

2.凍結窗口

當一個工作表超長超寬，操作滾動條查看超出窗口大小的數據時，由于已看不到行列標題，可能無法分清楚某行或某列數據的含義，這時可以通過凍結窗口來鎖定行列標題不隨滾動條滾動。

凍結窗口的方法：在工作表中的某個單元格中單擊鼠標，該單元格上方的行和左側的列將在鎖定范圍之內；然后在“視圖”選項卡上的“窗口”組中單擊“凍結窗格”按鈕，從打開的下拉列表中選擇“凍結拆分窗格”，當前單元格上方的行和左側的列始終保持可見，不會隨著操作滾動條而消失。

如要取消窗口凍結，只需從“凍結窗格”下拉列表中選擇“取消凍結窗格”即可。

3.拆分窗口

在工作表的某個單元格中單擊鼠標，在“視圖”選項卡上的“窗口”組中單擊“拆分”按鈕，以當前單元格為坐標，將窗口拆分為四個，每個窗口中均可進行編輯。再次單擊“拆分”按鈕可取消窗口拆分效果。

4.窗口縮放

通過“視圖”選項卡上的“顯示比例”組，可以對當前窗口的顯示進行縮放設置，其中：

(1)顯示比例：單擊該按鈕，彈出“顯示比例”對話框，在該對話框中可以自由指定一個顯示比例。縮放到選定區域：選擇某一個區域，單擊該按鈕，窗口中恰好顯示選定的區域。

(2)?100%：單擊該按鈕，可恢復正常大小的顯示比例。

7.3數據分析與處理

在工作表中輸入基礎數據后需要對這些數據進行組織、整理、排列、分析，從中獲取更加豐富實用的信息。為了實現這一目的，Excel提供了豐富的數據處理功能，可以對大量、無序的原始數據資料進行深入地處理與分析。本章的功能全部是基于正確的數據列表基礎上實現的，因此在本章內容開始前，需要重點強調一下數據列表的構建規則。

(1)數據列表一般是一個矩形區域，應與周圍的非數據列表內容用空白行列分隔開，也就是說一組數據列表中沒有空白的行或列。

(2)數據列表應有一個標題行。作為每列數據的標志，列標題應便于理解數據的含義。標題一般不能使用純數值，不能重復，也不能分置于兩行中。

(3)數據列表中不能包括合并單元格，標題行單元格一般不插入斜線表頭。

(4)每一列中的數據格式一般應該統一。

7.3.1合并計算

若要匯總和報告多個單獨工作表中數據的結果，可以將各個單獨工作表中的數據合并到一個主工作表。被合并的工作表可以與合并后的主工作表位于同一工作簿，也可以位于其他工作簿中。多表合并基本操作步驟如下：

①打開要進行合并計算的工作簿。

②切換到放置合并后數據的主工作表中，在要顯示合并數據的單元格區域中，單擊左上方的單元格。

③在“數據”選項卡上的“數據工具”組中，單擊“合并計算”按鈕，打開“合并計算”對話框，如圖7.16所示。

圖7.16打開“合并計算”對話框

④在“函數”下拉框中，選擇一個匯總函數。

⑤在“引用位置”框中單擊鼠標，然后在包含要對其進行合并計算的數據的工作表中選擇合并區域。

⑥在“合并計算”對話框中，單擊“添加”按鈕，選定的合并計算區域顯示在“所引用的位置”列表框中。

⑦重復步驟⑤和步驟⑥以添加其他的合并數據區域。

⑧在“標簽位置”組下，按照需要單擊選中表示標簽在源數據區域中所在位置的復選框，可以只選一個，也可以兩者都選。如果選中“首行”或“最左列”，Excel將對相同的行標題或列標題中的數據進行合并計算。

⑨單擊“確定”按鈕，完成數據合并。

⑩對合并后的數據表進行修改完善，如進行格式化、輸入相關數據等。

7.3.2對數據排序

對數據進行排序有助于快速直觀地組織并查找所需數據，可以對一列或多列中的數據文本、數值、日期和時間按升序或降序的方式進行排序。還可以按自定義序列、格式(包括單元格顏色、字體顏色等)進行排序。大多數排序操作都是列排序。快速簡單排序操作步驟如下：

①打開工作簿文件，輸入、設計要排序的數據區域。

②選擇要排序的列中的某個單元格，Excel自動將其周圍連續的區域定義為參與排序的區域，且指定首行為標題行，或者直接選擇包含標題行的排序區域。

③在如圖7.17所示的“數據”選項卡的“排序和篩選”組中，按下列提示選擇排序方式：

單擊升序按鈕，當前數據區域按升序進行排序；

單擊降序按鈕，當前數據區域按降序進行排序。圖7.17“排序和篩選”組中用于排序的按鈕

7.3.3復雜多條件排序

可以根據需要設置多條件排序。例如，在對成績按總分高低進行排序時，總分相同的情況下語文成績高的排名靠前，這就需要設置多個條件。

(1)選擇要排序的數據區域，或者單擊該數據區域中的任意一個單元格。

(2)在“數據”選項卡的“排序和篩選”組中，單擊“排序”按鈕，打開“排序”對話框。

(3)在如圖7.18所示的“排序”對話框中設置排序的第一依據。

圖7.18在“排序”對話框中設定排序條件

①在“主要關鍵字”下拉列表中選擇列標題名，作為排序的第一依據。

②在“排序依據”下拉列表中，選擇是依據指定列中的數值還是格式進行排序。

③在“次序”列表中，選擇要排序的順序。

(4)繼續添加排序第二依據。單擊“添加條件”按鈕，條件列表中新增一行，依次指定排序列的次要關鍵字、排序依據和次序。

(5)如需要對排序條件進行進一步設置，可單擊對話框右上方的“選項”按鈕，打開“排序選項”對話框，在該對話框中進行相應的設置。其中，對西文文本數據排序時可以區分大小寫；對中文文本數據可以改為按筆畫多少排序，還可以設置按行進行排序，默認情況下均是按列排序的。設置完畢后單擊“確定”按鈕。

(6)如果有必要，還可以增加更多的排序條件。最后單擊“確定”按鈕，完成排序設置。

(7)如果要在更改數據列表中的數據后重新應用排序條件，可單擊排序區域中的任一單元格，然后在“數據”選項卡上的“排序和篩選”組中單擊“重新應用”按鈕。

7.3.4按自定義列表進行排序

我們還可以按照自定義列表的順序進行排序，不過只能基于數據(文本、數值以及日期或時間)創建自定義列表，而不能基于格式(單元格顏色、字體顏色等)創建自定義列表。

①首先，需要通過“文件”選項卡→“選項”→“高級”→“常規”→“編輯自定義列表”按鈕創建一個自定義序列。

②選擇要排序的數據區域，或者確保活動單元格在數據列表中。

③在“數據”選項卡的“排序和篩選”組中，單擊“排序”按鈕，打開“排序”對話框。

④在排序條件的“次序”列表中，選擇“自定義序列”，打開“自定義序列”對話框，如圖7.19所示。

⑤從中選擇自定義序列后，依次單擊“添加”和“確定”按鈕。

圖7.19應用自定義序列進行排序

7.3.5篩選數據

通過篩選功能，可以快速從數據列表中查找符合條件的數據或者排除不符合條件的數據。篩選條件可以是數值或文本，可以是單元格顏色，還可以根據需要構建復雜條件以實現高級篩選。

對數據列表中的數據進行篩選后，就會僅顯示那些滿足指定條件的行，并隱藏那些不希望顯示的行。對于篩選結果可以直接復制、查找、編輯、設置格式、制作圖表和打印。

1.自動篩選

使用自動篩選來篩選數據，可以快速而又方便地查找和使用數據列表中數據的子集。

①打開工作簿，在工作表中選擇要篩選的數據列表，或者在數據列表的任一單元格中單擊。

②在“數據”選項卡上的“排序和篩選”組中，單擊“篩選”按鈕，進入到自動篩選狀態。當前數據列表中的每個列標題旁邊均出現一個篩選箭頭。

③單擊某個列標題中的篩選箭頭，將打開一個篩選器選擇列表，列表下方將顯示當前列中包含的所有值。當列中的數據格式為文本時，顯示“文本篩選”命令，如圖7.20(a)所示；當列中的數據格式為數值時顯示“數字篩選”命令，如圖7.20(b)所示。

圖7.20單擊列標題中的篩選箭頭打開篩選器選擇列表a)“文本篩選”菜單(b)“數字篩選”菜單

④選用下列方法，在數據列表中搜索或選擇要顯示的數據：

·直接在“搜索”框中輸入要搜索的文本或數字，可以使用通配符“*”或“？”。

·在“搜索”下方的列表中指定要搜索的數據。首先單擊“全選”取消對該復選框的選擇，這將刪除所有復選框的選中標記；然后僅單擊選中希望顯示的值，最后單擊“確定”按鈕。

·按指定的條件篩選數據。將光標指向“數字篩選”或“文本篩選”命令，在隨后彈出的子菜單中設定一個條件。單擊最下邊的“自定義篩選”命令，將會打開“自定義自動篩選方式”對話框，在其中設定篩選條件即可。

⑤在第一次篩選的基礎上，可再次對另一列標題設定篩選條件，實現雙重甚至多重嵌套篩選。例如，可以先從成績表中篩選出全年級總分前10名，然后再從總分前10名中篩選出男生的數據。

2．級篩選

通過構建復雜條件可以實現高級篩選。所構建的復雜條件需要放置在工作表單獨的區域中，可以為該條件區域命名以便引用。用于高級篩選的復雜條件可以像在公式中那樣使用下列運算符比較兩個值：=(等號)、>(大于號)、<(小于號)、>=(大于等于號)、<=(小于等于號)、<>(不等號)。

1)創建復雜篩選條件

構建復雜條件的原則：條件區域中必須有列標題，且與包含在數據列表中的列標題一致；表示“與(and)”的多個條件應位于同一行中，意味著只有這些條件同時滿足的數據才會被篩選出來；表示“或(or)”的多個條件應位于不同的行中，意味著只要滿足其中的一個條件就會被篩選出來。在要進行篩選的數據區域外或者在新的工作表中，單擊放置篩選條件的條件區域左上角的單元格。輸入作為條件的列標題，必須與數據表中的列標題對應一致。

2)高級篩選操作步驟

高級篩選必須有一個條件區域，條件區域距數據清單至少一行一列。篩選結果可以顯示在源數據區，也可以顯示在新的區域。

(1)篩選條件的輸入方法。

篩選條件中用到的字段名，尤其是當字段名中含有空格時，為了確保完全相同，應采用復制的方法將字段名復制到條件區域中，以免出錯。

篩選條件的基本輸入規則：條件中用到的字段名在同一行中且連續，下方輸入條件值，“與”關系寫在同一行上，“或”關系寫在同一列上。“橫并豎或”是最形象的說法。

(2)高級篩選條件輸入示例。

①條件“實發工資介于2300至2500，且主管地區為昆明”的輸入方法如圖7.21所示，對于這種多個條件的“與”也可以用“自動篩選”完成。

②條件“實發工資介于2300至2500，或主管地區為昆明”的輸入方法如圖7.22所示。

③條件“實發工資大于2500且主管地區為昆明，或主管地區為北京”的輸入方法如圖7.23所示。圖7.21高級篩選條件設置1圖7.22高級篩選條件設置2圖7.23高級篩選條件設置3

(3)高級篩選操作過程。

①單擊數據清單內的任意單元格。

②打開“數據”，選擇“排序和篩選”級聯菜單中的“高級”命令，打開“高級篩選”對話框，如圖7.24所示。圖7.24高級篩選設置

③對話框的使用：

·在“方式”欄內選擇篩選結果存放的位置：原位置或新位置。

·“列表區域”文本框：系統自動顯示當前單元格所在的數據清單，若改變數據清單，可單擊右側的切換按鈕，返回工作表窗口，選定數據區域后(應包括條件字段列和結果列)，單擊返回按鈕返回“高級篩選”對話框。

·用同樣的方法選擇條件區域。

·若在“方式”欄內選擇了“將篩選結果復制到其他位置”復選框，則在“復制到”文本框中單擊切換按鈕，返回工作表，選擇結果存放在區域左上角的第一個單元格，單擊返回按鈕返回“高級篩選”對話框。

·若選中“選擇不重復的記錄”復選框，則篩選結果中不會存在完全相同的兩個記錄。

·單擊“確定”按鈕，篩選結果會出現在結果區域中。

3)清除篩選

·清除某列的篩選條件：在已設有自動篩選條件的列標題旁邊的篩選箭頭上單擊，從列表中選擇“從‘××’中清除篩選”，其中“××”指列標題。

·清除工作表中的所有篩選條件并重新顯示所有行：在“數據”選項卡上的“排序和篩選”組中單擊“清除”按鈕。

·退出自動篩選狀態：在已處于自動篩選狀態的數據列表中的任意位置單擊鼠標，在“數據”選項卡上的“排序和篩選”組中單擊“篩選”按鈕。

7.3.6分類匯總

在“工資表”中，若需要分別計算各部門的實發工資總額或部門人數，可以用分類匯總的方法。

分類匯總，就是將數據清單中的每類數據進行匯總，因此，執行分類匯總前必須先將數據排序，排序關鍵字作為分類字段。“以什么字段進行分類匯總，就要以什么字段先排序”形象地說明了這點。例如，“工資表”中，若按“部門”統計數據，就應先按“部門”字段排序，再進行分類匯總。

匯總方式有：計數、求和、求平均值、最大值、最小值等。

下面以部門為分類字段求實發工資的和為例，說明分類匯總的操作過程。

①先按“部門”字段排序。

②選定數據清單內的任意單元格。

③選擇“數據”下拉菜單中的“分類匯總”命令，打開“分類匯總”對話框，如圖7.25所示。圖7.25分類匯總設置

④對話框的使用：

·在“分類字段”下拉列表中選擇分類字段，本例中選擇“部門”。

·在“匯總方式”中選擇匯總方式，本例選擇“求和”。

·在“選擇匯總項”列表框中選擇需要匯總的字段，可以選多個，本例中選擇“實發工資”。注意：要選定字段左側的復選框。

·選中“替換當前的分類匯總”復選框，則只顯示最新的匯總結果。

·選中“每組數據分頁”，則在每類數據后插入分頁符。

·選中“匯總結果顯示在數據下方”復選框，則分類匯總結果和總匯總結果顯示在明細數據下方，取消則顯示在上方。

·單擊“確定”按鈕，完成匯總。本例匯總結果如圖7.26所示。

圖7.26分類匯總

利用左側的級別顯示按鈕??和折疊按鈕?、，可以隱藏或重現明細記錄。單擊只顯示總的匯總結果，單擊??顯示分類匯總結果和總匯總結果，單擊??則顯示全部明細數據和匯總結果。

刪除分類匯總：選擇“數據”下拉菜單中的“分類匯總”命令，打開“分類匯總”對話框，單擊“全部刪除”按鈕。

7.3.7數據透視表

數據透視表是一種可以從源數據列表中快速提取并匯總大量數據的交互式表格。使用數據透視表可以匯總、分析、瀏覽數據以及呈現匯總數據，達到深入分析數值數據、從不同的角度查看數據，并對相似數據的數值進行比較的目的。

若要創建數據透視表，必須先行創建其源數據。數據透視表是根據源數據列表生成的，源數據列表中每一列都成為匯總多行信息的數據透視表字段，列名稱為數據透視表的字段名。

1.創建數據透視表

①首先打開一個空白工作簿，在工作表中創建數據透視表所依據的源數據列表。該源數據區域必須具有列標題，并且該區域中沒有空行。

②在用作數據源區域中的任意一個單元格中單擊鼠標。

③在“插入”選項卡上的“表格”組中單擊“數據透視表”按鈕，打開“創建數據透視表”對話框，如圖7.27所示。圖7.27打開“創建數據透視表”對話框

④指定數據來源。在“選擇一個表或區域”項下的“表/區域”框中顯示當前已選擇的數據源區域，可以根據需要重新選擇數據源。

⑤指定數據透視表存放的位置。選中“新工作表”，數據透視表將放置在新插入的工作表中；選擇“現有工作表”，然后在“位置”框中指定放置數據透視表區域的第一個單元格，數據透視表將放置到已有工作表的指定位置。

⑥單擊“確定”按鈕，Excel會將空的數據透視表添加至指定位置并在右側顯示“數據透視表字段列表”窗格，如圖7.28所示。該窗口上半部分為字段列表，顯示可以使用的字段名，也就是源數據區域的列標題；下半部分為布局部分，包含“報表篩選”區域、“列標簽”區域、“行標簽”區域和“數值”區域。圖7.28在新工作表中插入空白的透視表并顯示數據透視表字段列表窗口

⑦按照下列提示向數據透視表中添加字段。

·若要將字段放置到布局部分的默認區域中，可在字段列表中單擊選中相應字段名復選框。默認情況下，非數值字段將會自動添加到“行標簽”區域，數值字段會添加到“數值”區域，格式為日期和時間的字段則會添加到“列標簽”區域。

·若要將字段放置到布局部分的特定區域中，可以直接將字段名從字段列表中拖動到布局部分的某個區域中；也可以在字段列表的字段名稱上單擊右鍵，然后從快捷菜單中選擇相應命令。

·如果想要刪除字段，只需要在字段列表中單擊取消對該字段名復選框的選擇即可。

⑧在數據透視表中篩選字段。加入到數據透視表中的字段名右側均會顯示篩選箭頭，通過該箭頭可以對數據進行進一步遴選。

2.更新和維護數據透視表

在數據透視表區域的任意單元格中單擊，功能區中將會出現“數據透視表工具”所屬的“選項”和“設計”兩個選項卡。通過如圖7.29所示的“數據透視表工具｜選項”選項卡可以對數據透視表進行多項操作。其中：圖7.29“數據透視表工具｜選項”選項卡

1)刷新數據透視表

在創建數據透視表之后，如果對數據源中的數據進行了更改，那么需要在“數據透視表工具｜選項”選項卡上單擊“數據”組中的“刷新”按鈕，所做的更改才能反映到數據透視表中。

2)更改數據源

如果在源數據區域中添加或減少了行或列數據，則可以通過更改源數據將這些行列包含到數據透視表或剔除出數據透視表，方法如下：

在數據透視表中單擊，從“數據透視表工具?|?選項”選項卡上單擊“數據”組中的“更改源數據”按鈕。

從打開的下拉列表中選擇“更改數據源”命令，打開如圖7.30所示的“更改數據透視表數據源”對話框。

重新選擇數據源區域以包含新增行列數據或減少行列數據，然后單擊“確定”按鈕。圖7.30“更改數據透視表數據源”對話框

3)更改數據透視表名稱及布局

在“數據透視表工具?|?選項”選項卡上的“數據透視表”組中，可進行下列設置：

在“數據透視表名稱”下方的文本框中輸入新的透視表名稱，可重新命名當前透視表。

單擊“選項”按鈕，在隨后彈出的如圖7.31所示的“數據透視表選項”對話框中可對透視表的布局、行列及數據顯示方式進行設定。其中在圖7.31(b)所示的“匯總和篩選”選項卡中可以設定是否自動顯示匯總行列。圖7.31“數據透視表選項”對話框

4)設置活動字段

在“數據透視表工具｜選項”選項卡上的“活動字段”組中，可進行下列設置：

(1)在“活動字段”下方的文本框中輸入新的字段名，可更改當前字段名稱。

(2)單擊“字段設置”按鈕，打開“值字段設置”對話框，當前字段性質不同，對話框中選項也會有所不同。圖7.32所示的是當前字段為值匯總字段時對話框顯示的內容，在該對話框中可以對值匯總方式、值顯示方式等進行設置。圖7.32“值字段設置”對話框

5)對數據透視表的排序和篩選

在“數據透視表工具?|?選項”選項卡上的“排序和篩選”組中，可對透視表按行或列進行排序。通過行標簽或列標簽右側的篩選箭頭，也可對透視表中的數據按指定字段進行排序及篩選。

3.設置數據透視表格式

可以像對普通表格那樣對數據透視表進行格式設置，因為它本來也是個表格；還可通過如圖7.33所示的“數據透視表工具?|?設計”選項卡為數據透視表快速指定預置樣式。圖7.33“數據透視表工具?|?設計”選項卡

4.創建數據透視圖

數據透視圖以圖形形式呈現數據透視表中的匯總數據，其作用與普通圖表一樣，可以更為形象地對數據進行比較、反映趨勢。

為數據透視圖提供源數據的是相關聯的數據透視表。在相關聯的數據透視表中對字段布局和數據所做的更改，會立即反映在數據透視圖中。數據透視圖及其相關聯的數據透視表必須始終位于同一個工作簿中。

除了數據源來自數據透視表以外，數據透視圖與標準圖表的組成元素基本相同，包括數據系列、類別、數據標記和坐標軸以及圖表標題、圖例等。與普通圖表的區別在于，當創建數據透視圖時，數據透視圖的圖表區中將顯示字段篩選器，以便對基本數據進行排序和篩選。

在已創建好的數據透視表中單擊，該表將作為數據透視圖的數據來源。

在“數據透視表工具?|?選項”選項卡上，單擊“工具”組中的“數據透視圖”按鈕，打開“插入圖表”對話框。

與創建普通圖表一樣，選擇相應的圖表類型和圖表子類型。

單擊“確定”按鈕，數據透視圖插入到當前數據透視表中，單擊圖表區中的字段篩選器，可更改圖表中顯示的數據。

在數據透視圖中單擊，功能區出現“數據透視圖工具”中的“設計”“布局”“格式”和“分析”四個選項卡。通過這四個選項卡，可以對透視圖進行修飾和設置，方法與普通圖表相同。

5.刪除數據透視表或數據透視圖

可以通過下述方法刪除數據透視表或數據透視圖。

1)刪除數據透視表

(1)在要刪除的數據透視表的任意位置單擊。

(2)在“數據透視表工具?|?選項”選項卡上，單擊“操作”組中的“選擇”按鈕下方的箭頭。

(3)從下拉列表中單擊選擇“整個數據透視表”命令，按Delete鍵。

2)刪除數據透視圖

在要刪除的數據透視圖中的任意空白位置單擊，然后按Delete鍵。刪除數據透視圖不會刪除相關聯的數據透視表。

7.3.8模擬分析和運算

Excel附帶了三種模擬分析工具：單變量求解、模擬運算表和方案管理器。方案管理器和模擬運算表可獲取一組輸入值并確定可能的結果。單變量求解則是針對希望獲取的結果確定生成該結果的可能的各項值。

1.單變量求解

單變量求解用來解決以下問題：先假定一個公式的計算結果是某個固定值，當其中引用的變量所在單元格應取值為多少時該結果才成立。實現單變量求解的基本方法如下：

(1)首先為實現單變量求解，在工作表中輸入基礎數據，構建求解公式并輸入到數據表中。

(2)單擊選擇用于產生特定目標數值的公式所在的單元格。

(3)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“單變量求解”命令，打開“單變量求解”對話框，如圖7.34所示。圖7.34打開“單變量求解”對話框

(4)在該對話框中設置用于單變量求解的各項參數，其中：

①目標單元格顯示目標值的單元格地址。

②目標值希望得到的結果值。

③可變單元格能夠得到目標值的可變量所在的單元格地址。

(5)單擊“確定”按鈕，彈出“單變量求解狀態”對話框，同時數據區域中的可變單元格中顯示單變量求解值。

(6)單擊“單變量求解狀態”對話框中的“確定”按鈕，接受計算結果。

(7)重復步驟(2)～(6)，可以重新測試其他結果。

2.模擬運算表

模擬運算表的結果顯示在一個單元格區域中，它可以測算將某個公式中一個或兩個變量替換成不同值時對公式計算結果的影響。模擬運算表最多可以處理兩個變量，但可以獲取與這些變量相關的眾多不同的值。模擬運算表依據處理變量個數的不同，分為單變量模擬運算表和雙變量模擬運算表兩種類型。

1)單變量模擬運算表

若要測試公式中一個變量的不同取值如何改變相關公式的結果，可使用單變量模擬運算表。在單列或單行中輸入變量值后，不同的計算結果便會在公式所在的列或行中顯示。

(1)為了創建單變量模擬運算表，首先要在工作表中輸入基礎數據與公式。

(2)選擇要創建模擬運算表的單元格區域，其中第一行(或第一列)需要包含變量單元格和公式單元格。

(3)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“模擬運算表”命令，打開如圖7.35所示的“模擬運算表”對話框。

圖7.35打開“模擬運算表”對話框

(4)指定變量值所在的單元格。如果模擬運算表變量值輸入在一列中，應在“輸入引用列的單元格”框中選擇第一個變量值所在的位置。如果模擬運算表變量值輸入在一行中，應在“輸入引用行的單元格”框中選擇第一個變量值所在的位置。

(5)單擊“確定”按鈕，選定區域中自動生成模擬運算表。在指定的引用變量值的單元格中依次輸入不同的值，右側將根據設定公式測算不同的目標值。

2)雙變量模擬運算表

若要測試公式中兩個變量的不同取值如何改變相關公式的結果，可使用雙變量模擬運算表。在單列和單行中分別輸入兩個變量值后，計算結果便會在公式所在區域中顯示。

(1)為了創建雙變量模擬運算表，要在工作表中輸入基礎數據與公式，其中所構建的公式至少需要引用兩個單元格。

(2)輸入變量值(提示：也可以在創建了模擬運算表區域之后再輸入相關的變量值)。在公式所在的行從左向右輸入一個變量的系列值，沿公式所在的列由上向下輸入另一個變量的系列值。

(3)選擇要創建模擬運算表的單元格區域，其中第一行和第一列需要包含公式單元格和變量值。公式應位于所選區域的左上角。

(4)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“模擬運算表”命令，打開“模擬運算表”對話框。

(5)依次指定公式中所引用的行列變量值所在的單元格。

(6)單擊“確定”按鈕，選定區域中自動生成一個模擬運算表。此時，當更改模擬運算表中的單價或銷量時，其對應的利潤測算值就會發生變化。

3.方案管理器

模擬運算表無法容納兩個以上的變量。如果要分析兩個以上的變量，則應使用方案管理器。一個方案最多獲取32個不同的值，但是卻可以創建任意數量的方案。

方案管理器作為一種分析工具，每個方案允許建立一組假設條件，自動產生多種結果，并可以直觀地看到每個結果的顯示過程，還可以將多種結果存放到一個工作表中進行比較。

1)建立分析方案

為了創建分析方案，首先需要在工作表中輸入基礎數據與公式。數據表需要包含多個變量單元格，以及引用這些變量單元格的公式。

(1)選擇可變單元格所在的區域。

(2)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“方案管理器”命令，打開如圖7.36(a)所示的“方案管理器”對話框。

(3)單擊右上方的“添加”按鈕，接著彈出如圖7.36(b)所示的“添加方案”對話框。在“方案名”下的文本框中輸入方案名稱，在“可變單元格”框中可重新指定顯示變量的單元格區域。

(4)在“添加方案”對話框中單擊“確定”按鈕，繼續打開“方案變量值”對話框，依次輸入方案的變量值。

(5)單擊“確定”按鈕，返回到“方案管理器”對話框。

(6)重復步驟(4)～(5)，繼續添加其他方案。注意：其引用的可變單元格區域始終保持不變。

(7)所有方案添加完畢后，單擊“方案管理器”對話框中的“關閉”按鈕。圖7.36在“方案管理器”對話框中添加方案

2)顯示并執行方案

分析方案制訂好后，任何時候都可以執行方案，以查看不同的執行結果。

(1)打開包含已制訂方案的工作表。

(2)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“方案管理器”命令，打開“方案管理器”對話框。

(3)在“方案”列表框中單擊選擇想要查看的方案，單擊對話框下方的“顯示”按鈕，工作表中的可變單元格中自動顯示出該方案的變量值，同時公式中顯示方案執行結果。

3)修改或刪除方案

(1)修改方案：打開“方案管理器”對話框，在“方案”列表中選擇想要修改的方案，單擊“編輯”按鈕，在隨后彈出的對話框中可修改名稱、變量值等。

(2)刪除方案：打開“方案管理器”對話框，在“方案”列表中選擇想要刪除的方案，單擊“刪除”按鈕。

4)建立方案報表

當需要將所有方案的執行結果都顯示出來并進行比較時，可以建立合并的方案報表。

(1)打開已創建方案并希望建立方案報表的工作表，在可變單元格中輸入一組變量值作為比較的基礎數據，一般可以輸入0，表示未變化前的結果。

(2)在“數據”選項卡上的“數據工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“方案管理器”命令，打開“方案管理器”對話框。

(3)單擊右側的“摘要”按鈕，打開“方案摘要”對話框，如圖7.37所示。

(4)在該對話框中選擇報表類型，指定運算結果單元格。結果單元格一般指定為方案公式所在單元格。

(5)單擊“確定”按鈕，將會在當前工作表之前自動插入“方案摘要”工作表，其中顯示各種方案的計算結果，可以立即比較各方案的優劣。圖7.37“方案摘要”對話框

7.4Excel公式和函數

7.4.1使用公式的基本方法1.認識公式在Excel中，公式總是以“=”開始。默認情況下，公式的計算結果顯示在單元格中，公式本身則可以通過編輯欄查看。構成公式的常用要素包括：

(1)單元格引用。單元格引用也就是前面所說的單元格地址，用于表示單元格在工作表中所處位置的坐標。例如，顯示在B列和第3行交叉處的單元格，其引用形式為“B3”。公式中還可以引用經過命名的單元格或區域。

(1)單元格引用。單元格引用也就是前面所說的單元格地址，用于表示單元格在工作表中所處位置的坐標。例如，顯示在B列和第3行交叉處的單元格，其引用形式為“B3”。公式中還可以引用經過命名的單元格或區域。

(3)運算符。運算符用于連接常量、單元格引用、函數等，從而構成完整的表達式。公式中常用的運算符有算術運算符(如?+、-、*、/、’)、字符連接符(如&)、關系運算符(如?=、<>、>、>=、<、<=)等。通過運算符可以構建復雜公式，完成復雜運算。

2.公式的輸入與編輯

在Excel中輸入公式與輸入普通文本不同，需要遵循一些特殊規定。

1)輸入公式的四個步驟

(1)定位結果：在要顯示公式計算結果的單元格中單擊鼠標，使其成為當前活動單元格。

(2)構建表達式：輸入“=”，表示正在輸入公式，否則系統會將其判斷為文本數據，不會產生計算結果。

(3)引用位置：直接輸入常量或單元格地址，或者用鼠標選擇需要引用的單元格或區域。

(4)確認結果：按回車鍵Enter完成輸入，計算結果顯示在相應單元格中。

2)修改公式

用鼠標雙擊公式所在的單元格，進入編輯狀態，單元格及編輯欄中均會顯示公式本身，在單元格或編輯欄中均可對公式進行修改。修改完畢，按回車鍵(Enter)確認即可。

3)刪除公式

單擊選擇公式所在的單元格或區域，然后按Delete鍵即可刪除。

3.公式的復制與填充

輸入到單元格中的公式，可以像普通數據一樣，通過拖動單元格右下角的填充或者從“開始”選項卡上的“編輯”組中選擇“填充”進行公式的復制填充，此時自動填充的實際上不是數據本身，而是復制的公式。默認情況下填充時公式對單元格的引用采用的是相對引用。

4.單元格引用

單元格引用方式分為以下幾大類：

(1)相對引用。

(2)絕對引用。

(3)混合引用。

7.4.2定義與引用名稱

為單元格或區域指定一個名稱，是實現絕對引用的方法之一。可以在公式中使用定義的名稱以實現絕對引用。可以定義為名稱的對象包括常量、單元格或單元格區域、公式等。

1.了解名稱的語法規則

在Excel中創建和編輯名稱時需要遵循以下語法規則：

(1)唯一性原則。

(1)唯一性原則。

(2)有效字符。

(3)不能與單元格地址相同。

(4)不能使用空格。

(5)名稱長度有限制。

(6)不區分大小寫。

2.為單元格區域定義名稱

為特定的單元格或區域命名可以方便快速地定位某一單元格或區域，并可在公式和函數中進行絕對引用。

1)快速定義名稱

(1)打開工作簿，選擇要命名的單元格或單元格區域，例如選擇區域B4:D12。

(2)在編輯欄左側的“名稱框”中單擊，原單元格地址被反白選中。

(3)在“名稱框”中輸入名稱，最后按Enter鍵確認。

2)將現有行和列標題轉換為名稱

(1)選擇要命名的區域，必須包括行或列標題。

(2)在“公式”選項卡上的“定義的名稱”組中單擊“根據所選內容創建”按鈕，打開“以選定區域創建名稱”對話框，如圖7.38所示。

(3)在該對話框中，通過選中“首行”“最左列”“末行”或“最右列”復選框來指定包含標題的位置。例如選中“首行”則可將所選區域的第1行標題設為各列數據的名稱。

(4)單擊“確定”按鈕，完成名稱的創建。通過該方式創建的名稱僅引用相應標題下包含值的單元格，并且不包括現有行和列標題。圖7.38“以選定區域創建名稱”對話框

3)使用“新名稱”對話框定義名稱

(1)在“公式”選項卡上的“定義的名稱”組中單擊“定義名稱”按鈕，打開如圖7.39(a)所示的“新建名稱”對話框。

(2)在“名稱”文本框中輸入用于引用的名稱，例如“工齡工資”。

(3)設定名稱的適用范圍。在“范圍”下拉列表框中選擇“工作簿”或工作表的名稱，可以指定該名稱只在某個工作表中有效還是在工作簿的所有工作表中均有效。

(4)可以在“備注”框中輸入最多255個字符，用于對該名稱的說明性批注。

(5)在“引用位置”框中顯示當前選擇的單元格或區域。如果需要修改命名對象，可選擇下列操作執行。

在“引用位置”框單擊鼠標，然后在工作表中重新選擇區域單元格或單元格區域。若要為一個常量命名，則輸入“=”，然后輸入常量值；若要為一個公式命名，則輸入“=”，然后輸入公式。例如，在“引用位置”框中輸入“=50”，則表示將常量50的名稱定義為“工齡工資”。設置完成的對話框如圖7.39(b)所示。

(6)單擊“確定”按鈕，完成命名并返回當前工作表。圖7.39在“新建名稱”對話框中定義名稱

3.引用名稱

名稱可直接用來快速選定已命名的區域，更重要的是可以在公式中引用名稱以實現精確引用。

1)通過“名稱框”引用

單擊“名稱框”右側的黑色箭頭，打開“名稱”下拉列表，將顯示所有已被命名的單元格名稱，但不包括常量和公式的名稱。單擊選擇某一名稱，該名稱所引用的單元格或區域將會被選中，如果是在輸入公式的過程中，那么該名稱將會出現在公式中。

2)在公式中引用

(1)單擊準備輸入公式的單元格。

(2)在“公式”選項卡上的“定義的名稱”組中，單擊“用于公式”按鈕，打開名稱下拉列表。

(3)從下拉列表中單擊選擇需要引用的名稱，該名稱將出現在當前單元格的公式中。

(4)按Enter鍵確認輸入。

4.更改或刪除名稱

如果更改了某個已定義的名稱，則工作簿中所有已引用該名稱的位置均會自動隨之更新。

(1)在“公式”選項卡上的“定義的名稱”組中，單擊“名稱管理器”，打開如圖7.40所示的“名稱管理器”對話框。

(2)在該對話框的名稱列表中，單擊要更改的名稱，然后單擊“編輯”按鈕，打開“編輯名稱”。

(3)在“編輯名稱”對話框中按照需要修改名稱、引用位置、備注說明等，但是適用范圍不能更改。修改完成后單擊“確定”按鈕。

(4)如果要刪除某一名稱，則從列表中單擊該名稱，然后單擊“刪除”按鈕，出現提示對話框，單擊其中的“確定”按鈕完成刪除操作。

(5)單擊“關閉”按鈕，退出“名稱管理器”對話框。圖7.40“名稱管理器”對話框

7.4.3使用函數的基本方法

1.認識函數

Excel提供大量預置函數以供選用，如求和函數(SUM)、平均值函數(AVERAGE)，條件函數(IF)等。

函數通常表示為：函數名([參數1],[參數2],……)，括號中的參數可以有多個，中間用逗號分隔，其中[]中的參數是可選參數，而沒有[]的參數是必需的，有的函數可以沒有參數。函數中的參數可以是常量、單元格地址、數組、已定義的名稱、公式、函數等。

函數中可以調用另一函數，稱為函數嵌套。

2.?Excel函數分類

Excel提供了大量工作表函數，并按其功能進行分類。Excel2010目前默認提供的函數類別共有13大類，如表7.1中所列。

3.函數的輸入與編輯

函數的輸入方式與公式類似，可以直接在單元格中輸入“=函數名(所引用的參數)”，但是要想記住每一個函數名稱并正確輸入所有參數是相當困難的。因此，通常情況下釆用參照的方式輸入一個函數。

1)通過“函數庫”組插入

當能夠明確地知道所需函數屬于哪一類別時，可釆用該方法。如平均值函數AVERAGE()為常用函數，同時又屬于統計類函數，可以通過“公式”選項卡上“函數庫”組中的“自動求和”類別或者“其他函數”下的“統計”類別來選擇。具體方法如下：

(1)在要輸入函數的單元格中單擊鼠標。

(2)在“公式”選項卡上的“函數庫”組中單擊某一函數類別下方的黑色箭頭。

(3)打開的函數列表中單擊所需要的函數，彈出類似圖7.41所示的“函數參數”對話框。

圖7.41通過“函數庫”組插入函數

(4)按照對話框中的提示輸入或選擇參數。

(5)單擊對話框左下角的鏈接“有關該函數的幫助”，可以獲取相關的幫助信息。

(6)輸入完畢，單擊“確定”按鈕。

2)通過“插入函數”按鈕插入

當無法確定所使用的具體函數或其所屬類別時，可通過該方法進行“模糊”查詢，具體操作如下：

(1)在要輸入函數的單元格中單擊鼠標。

(2)在“公式”選項卡上的“函數庫”組中，單擊最左邊的“插入函數”按鈕，打開“插入函數”對話框，如圖7.42所示。

(3)在“選擇類別”下拉列表中選擇函數類別。

圖7.42通過插入函數按鈕插入函數

(4)如果無法確定具體函數，可按需求在“搜索函數”框中輸入函數的簡單描述，如“查找文件”，然后單擊“轉到”按鈕。

(5)在“選擇函數”列表中單擊所需的函數名。同樣可以通過“有關該函數的幫助”鏈接獲取相關的幫助信息。

(6)單擊“確定”按鈕，在隨后打開的“函數參數”對話框中輸入參數。

3)修改函數

在包含函數的單元格中雙擊鼠標，進入編輯狀態，對函數及參數進行修改后按Enter鍵確認。

7.4.4重要函數的應用

函數是Excel應用的利器，是必須掌握的技巧之一。本節主要介紹一些日常工作與生活中應該了解和掌握的函數的使用方法，進一步的函數學習則可以在實際應用中遇到困難時借助于強大的幫助功能實現。Excel中常用函數簡介如下：

以下所列是Excel中的常用函數，我們應該很好地掌握其語法規則和實際用法。熟練掌握這些函數的用法可以極大地提高工作效率。

1)求和函數SUM(numberl,[number2])

功能：將指定的參數numberl、number2、…相加求和。

參數說明：至少需要包含一個參數numberl。每個參數都可以是區域、單元格引用、數組、常量、公式或另一個函數的結果。

示例：“=SUM(A1:A5)”是將單元格Al至A5中的所有數值相加，“=SUM(A1,A3,A5)”是將單元格A1、A3和A5中的數字相加。

2)條件求和函數SUMIF(range,criteria,[sum_range])

功能：對指定單元格區域中符合指定條件的值求和。

參數說明：

range—必需的參數，用于條件計算的單元格區域。

criteria—必需的參數，求和的條件，其形式可以為數字、表達式、單元格引用、文本或函數。

示例：條件可以表示為32、">32"、B5、32、"32"、"蘋果"或TODAY()。

sum_range—可選參數，要求和的實際單元格。如果sum_range參數被省略，則Excel會對在range參數中指定的單元格求和。

示例：“=SUMIF(B2:B25,">5")”表示對B2:B25區域大于5的數值進行相加；“=SUMIF(B2:B5,"John",C2:C5)”表示對單元格區域C2:C5中與單元格區域B2:B5中等于“John”的單元格對應的單元格中的值求和。

3)多條件求和函數SUMIFS(sum_range,criteria_rangel,criterial,[criteria_range2,

criteria2],…)

功能：對指定單元格區域中滿足多個條件的單元格求和。

參數說明：

sum_range—必需的參數，要求和的實際單元格區域。

criteria_rangel—必需的參數，在其中計算關聯條件的第一個區域。

criterial—必需的參數，求和的條件。條件的形式可為數字、表達式、單元格地址或文本，可用來定義將對criteria_rangel參數中的那些單元格求和。例如，條件可以表示為32、">32"、B4、"蘋果"?或?"32"。

criteria_range2，criteria2，…—可選，附加的區域及其關聯條件。最多允許127個區域/條件對。其中每個criteria_range參數區域所包含的行數和列數必須與sum_range參數相同。

示例：“=SUMIFS(A1:A20,Bl:B20,">0",C1:C20,"<10")”表示對區域A1:A20中符合以下條件的單元格的數值求和：Bl:B20中的相應數值大于0且C1:C20中的相應數值小于10。

4)絕對值函數ABS(number)

功能：返回數值number的絕對值，number為必需的參數。

示例：“=ABS(-2)”表示求?-2的絕對值，“=ABS(A2)”表示對單元格A2中的數值求取絕對值。

5)向下取整函數INT(number)

功能：將數值number向下舍入到最接近的整數，number為必需的參數。

示例：“=INT(8.9)”表示將8.9向下舍入到最接近的整數，結果為8；“=INT(-8.9)”表示將-8.9向下舍入到最接近的整數，結果為?-9。

6)四舍五入函數ROUND(number,num_digits)

功能：將指定數值number按指定的位數num_digits進行四舍五入。

示例：“=ROUND(25.7825，2)”表示將數值25.7825四舍五入為小數點后兩位，結果為25.78。

Round函數用于取得保留2位小數后的精確數值，與通過設置單元格數字格式的結果是有差異的。例如，在單元格中輸入“=10/3”，設置數字格式保留兩位小數后顯示結果為3.33，但單元格中實際值仍為3.33333……；而輸入11=ROUND(10/3，2)之后，單元格中顯示值和實際值均為3.33。如果希望顯示值與實際參與計算的值一致，應通過Round函數進行四舍五入。

提示通過“文件”選項卡→“高級”命令→選中“將精度設為所顯示的精度”復選框(如圖7.43所示)，也可以達到顯示值與實際參與計算的值相一致的效果。圖7.43設置顯示值與參與計算值的精度相一致

7)取整函數TRUNC(number,[num_digits])

功能：將指定數值number的小數部分截去，返回整數。num_digits為取整精度，默認為0。

示例：“=TRUNC(8.9)”表示取8.9的整數部分，結果為8；“=TRUNC(-8.9)”表示取-8.9的整數部分，結果為?-8。

8)垂直