《大學(xué)計(jì)算機(jī)》課件-第7章

第7章Excel應(yīng)用7.1Excel制表基礎(chǔ)7.2工作簿與工作表的操作7.3數(shù)據(jù)分析與處理7.4Excel公式和函數(shù)7.5輸入和編輯數(shù)據(jù)7.6整理和修飾表格7.7創(chuàng)建圖表7.8打印輸出工作表習(xí)題

7.1Excel制表基礎(chǔ)

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

2．Excel的幫助

Excel在啟動(dòng)狀態(tài)中可以按下“F1”鍵打開(kāi)幫助窗口，在彈出的“Office助手”對(duì)話框中選擇“目錄”選項(xiàng)卡，然后選擇相應(yīng)的一條求助目錄，在右部窗格就顯示出相關(guān)的幫助信息。

3．Excel窗口界面

Excel窗口界面如圖7.1所示。Excel的主功能區(qū)由標(biāo)題欄、選項(xiàng)卡、功能區(qū)、狀態(tài)欄、滾動(dòng)條等組成。下面介紹一些Excel特有的常用術(shù)語(yǔ)。

圖7.1Excel的窗口界面

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

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

(3)行號(hào)：每一行左側(cè)的阿拉伯?dāng)?shù)字為行號(hào)，表示該行的行數(shù)，對(duì)應(yīng)稱(chēng)為第1行、第2行……。

(4)列號(hào)：每一列上方的大寫(xiě)英文字母為列號(hào)，代表該列的列名，對(duì)應(yīng)稱(chēng)為A列、B列、C列……。

(5)單元格、單元格地址與活動(dòng)單元格：每一行和每一列交叉處的長(zhǎng)方形區(qū)域稱(chēng)為單元格，單元格為Excel操作的最小對(duì)象。單元格所在行、列的行號(hào)和列號(hào)形成單元格地址，猶如單元格的內(nèi)在名稱(chēng)，如A1單元格、C3單元格……在工作表中將鼠標(biāo)光標(biāo)指向某個(gè)單元格后單擊，該單元格被粗黑框標(biāo)出(如圖7.1中的A1單元格)，稱(chēng)為活動(dòng)單元格，活動(dòng)單元格是當(dāng)前可以操作的單元格。

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

(7)編輯欄：位于名稱(chēng)框右側(cè)，用于顯示、輸入、編輯、修改當(dāng)前活動(dòng)單元格中的內(nèi)容。

7.2工作簿與工作表的操作7.2.1工作簿基本操作1.創(chuàng)建一個(gè)工作簿默認(rèn)情況下，啟動(dòng)Excel時(shí)將會(huì)自動(dòng)打開(kāi)一個(gè)空白工作簿。自行創(chuàng)建一個(gè)新工作簿的基本方法是：?jiǎn)?dòng)Excel后，從“文件”選項(xiàng)卡上單擊“新建”，右側(cè)顯示可用模板列表，如圖7.2所示，其中：

(1)創(chuàng)建空白文檔。

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

2.保存工作簿并為其設(shè)置密碼

可以在保存工作簿文檔時(shí)為其設(shè)置打開(kāi)或修改密碼，以保證數(shù)據(jù)的安全性。具體設(shè)置方法如下：

(1)從“文件”選項(xiàng)卡上單擊“另存為”命令(如果是尚未保存過(guò)的新文檔，也可通過(guò)“快速訪問(wèn)工具欄”中的“保存”按鈕，或者“文件”選項(xiàng)卡上的“保存”命令)，打開(kāi)“另存為”對(duì)話框。

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

(3)單擊“另存為”對(duì)話框右下方的“工具”按鈕，從打開(kāi)的下拉列表中選擇“常規(guī)選項(xiàng)”，打開(kāi)“常規(guī)選項(xiàng)”對(duì)話框。

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

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

提示已經(jīng)保存過(guò)的文檔，經(jīng)過(guò)修改后再次單擊“快速訪問(wèn)工具欄”中的“保存”按鈕，或者從“文件”選項(xiàng)卡上單擊“保存”，將不會(huì)再?gòu)棾鰧?duì)話框。如果需要將文檔換個(gè)文件名保存，或者重新設(shè)置密碼，就需要從“文件”選項(xiàng)卡上單擊“另存為”命令，才會(huì)彈出相應(yīng)對(duì)話框。

3.關(guān)閉工作簿與退出Excel

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

4.打開(kāi)工作簿

常用的打開(kāi)工作簿方法有以下幾種：

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

(2)啟動(dòng)Excel，從“文件”選項(xiàng)卡上單擊“最近所用文件”命令，右側(cè)的文件列表中顯示最近編輯過(guò)的Excel工作簿名稱(chēng)，單擊需要的文件名即可將其打開(kāi)。

(3)啟動(dòng)Excel，從“文件”選項(xiàng)卡上單擊“打開(kāi)”命令，在彈出的“打開(kāi)”對(duì)話框中選擇相應(yīng)的文件名。

7.2.2創(chuàng)建和使用工作簿模板

模板是一種文檔類(lèi)型。根據(jù)日常工作和生活的需要，模板中已事先添加了一些常用的文本或數(shù)據(jù)，并進(jìn)行了適當(dāng)?shù)母袷交?，模板中還可以包含公式和宏，并以一定的文件類(lèi)型保存在特定的位置。

在模板的基礎(chǔ)上進(jìn)行簡(jiǎn)單的修改，就可以快速完成類(lèi)似的文檔的創(chuàng)建，而不必從空白頁(yè)面開(kāi)始。使用模板是節(jié)省時(shí)間和創(chuàng)建格式統(tǒng)一的文檔的絕佳方式。

Excel本身提供大量?jī)?nèi)置模板可供選用，Excel2010模板文件的后綴名為.xltx。另外，還可以自行創(chuàng)建模板并使用。

1.創(chuàng)建一個(gè)模板

①打開(kāi)要用作模板基礎(chǔ)的工作簿文檔。

②對(duì)工作簿中的內(nèi)容進(jìn)行調(diào)整修改。模板中只需要包含一些每個(gè)類(lèi)似文件都有的公用項(xiàng)目，而對(duì)于那些不同的內(nèi)容可以刪除，格式和公式應(yīng)該保留。

③在“文件”選項(xiàng)卡上單擊“另存為”命令，打開(kāi)“另存為”對(duì)話框。

④在“文件名”框中輸入模板的名稱(chēng)。

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

圖7.3在“另存為”對(duì)話框中選擇“保存類(lèi)型”

⑥單擊“保存”按鈕，新建模板將會(huì)自動(dòng)存放在Excel的模板文件夾中以供調(diào)用。

⑦關(guān)閉該模板文檔。

提示在“另存為”對(duì)話框中不要改變文檔的存放位置，以確保在需要用該模板創(chuàng)建新工作簿時(shí)該模板可以被調(diào)用。

2.使用自定義模板創(chuàng)建新工作簿

①啟動(dòng)Excel，單擊“文件”選項(xiàng)卡上的“新建”命令。

②在“可用模板”下單擊“我的模板”，打開(kāi)一個(gè)“新建”對(duì)話框，如圖7.4所示。

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

④輸入新的數(shù)據(jù)，進(jìn)行適當(dāng)?shù)母袷秸{(diào)整，然后將該文檔保存為正常的Excel工作簿即可。圖7.4“新建”對(duì)話框中的“個(gè)人模板”列表

3.修改模板

在基于模板創(chuàng)建的新文件中進(jìn)行修改調(diào)整，并不會(huì)對(duì)模板本身產(chǎn)生影響。如果要對(duì)模板本身進(jìn)行編輯修改，應(yīng)從“文件”選項(xiàng)卡上單擊“打開(kāi)”命令，選擇模板文件存放的位置，找到要編輯的模板名并打開(kāi)它進(jìn)行修改。Excel2010默認(rèn)的模板文件保存位置為：C:\Users\[實(shí)際的用戶名]\AppData\Roaming\Microsoft\Templates。

7.2.3隱藏與保護(hù)工作簿

1.隱藏工作簿

當(dāng)在Excel中同時(shí)打開(kāi)多個(gè)工作簿時(shí)，可以暫時(shí)隱藏其中的一個(gè)或幾個(gè)，需要時(shí)再顯示出來(lái)?；痉椒ǎ菏紫惹袚Q到需要隱藏的工作簿窗口，單擊“視圖”選項(xiàng)卡，在如圖7.5所示的“窗口”組中單擊“隱藏”按鈕，當(dāng)前工作簿就會(huì)被隱藏起來(lái)。

如要取消隱藏，在“視圖”選項(xiàng)卡上的“窗口”組中單擊“取消隱藏”按鈕，在打開(kāi)的“取消隱藏”對(duì)話框中選擇需要取消隱藏的工作簿名稱(chēng)，再單擊“確定”按鈕即可。圖7.5“視圖”選項(xiàng)卡上的“窗口”組

2.保護(hù)工作簿

當(dāng)不希望他人對(duì)工作簿的結(jié)構(gòu)或窗口進(jìn)行改變時(shí)，可以設(shè)置工作簿保護(hù)，基本方法如下：

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

①打開(kāi)需要保護(hù)的工作簿文檔。

②在“審閱”選項(xiàng)卡上的“更改”組中，單擊“保護(hù)工作簿”按鈕，打開(kāi)“保護(hù)結(jié)構(gòu)和窗口”對(duì)話框，如圖7.6所示。圖7.6通過(guò)“更改”組打開(kāi)“保護(hù)結(jié)構(gòu)和窗口”對(duì)話框

③在對(duì)話框中按照需要進(jìn)行各項(xiàng)設(shè)置，其中：

選中“結(jié)構(gòu)”復(fù)選框，將阻止他人對(duì)工作簿的結(jié)構(gòu)進(jìn)行修改，包括查看已隱藏的工作表，移動(dòng)、刪除、隱藏工作表或更改工作表的表名，插入新工作表，將工作表移動(dòng)或復(fù)制到另一工作簿中等。

選中“窗口”復(fù)選框，將阻止他人修改工作簿窗口的大小和位置，包括移動(dòng)窗口、調(diào)整窗口大小或關(guān)閉窗口等。

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

提示如果不提供密碼，則任何人都可以取消對(duì)工作簿的保護(hù)。如果使用密碼，一定要牢記自己的密碼，否則自己也無(wú)法再對(duì)工作簿的結(jié)構(gòu)和窗口進(jìn)行設(shè)置。

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

7.2.4工作表基本操作

1.插入工作表

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

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

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

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

2.刪除工作表

在要?jiǎng)h除的工作表標(biāo)簽上單擊鼠標(biāo)右鍵，從彈岀的快捷菜單中選擇“刪除”命令，即可刪除當(dāng)前選定的工作表。

3.改變工作表名稱(chēng)

在工作表標(biāo)簽上雙擊鼠標(biāo)，或者在“開(kāi)始”選項(xiàng)卡的“單元格”組中單擊打開(kāi)“格式”列表，從“組織工作表”下選擇“重命名工作表”命令，此時(shí)工作表標(biāo)簽名進(jìn)入編輯狀態(tài)，輸入新的工作表名后按Enter鍵確認(rèn)修改。

4.設(shè)置工作表標(biāo)簽顏色

為工作表標(biāo)簽設(shè)置顏色可以突出顯示某張工作表。

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

5.移動(dòng)或復(fù)制工作表

可以通過(guò)移動(dòng)操作在同一工作簿中改變工作表的位置或?qū)⒐ぷ鞅硪苿?dòng)到另外一個(gè)工作簿中，或通過(guò)復(fù)制操作在同一工作簿或不同的工作簿中快速生成工作表的副本。

①首先打開(kāi)工作簿文檔，在需要移動(dòng)或復(fù)制的工作表標(biāo)簽上單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“移動(dòng)或復(fù)制”命令；或者在“開(kāi)始”選項(xiàng)卡上的“單元格”組中單擊打開(kāi)“格式”列表，從“組織工作表”下選擇“移動(dòng)或復(fù)制工作表”命令，打開(kāi)“移動(dòng)或復(fù)制工作表”對(duì)話框，如圖7.8所示。圖7.8“移動(dòng)或復(fù)制工作表”對(duì)話框

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

④如果要復(fù)制工作表，需要單擊選中“建立副本”復(fù)選框，否則將會(huì)移動(dòng)工作表。

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

6.顯示或隱藏工作表

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

如果要取消隱藏，只需從上述相應(yīng)菜單中選擇“取消隱藏”命令，在打開(kāi)的“取消隱藏”對(duì)話框中選擇相應(yīng)的工作表即可。

7.2.5工作表的保護(hù)

1.保護(hù)整個(gè)工作表

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

①打開(kāi)工作簿，選擇需要設(shè)置保護(hù)的工作表。

②在“審閱”選項(xiàng)卡上的“更改”組中，單擊“保護(hù)工作表”按鈕，打開(kāi)如圖7.9所示的“保護(hù)工作表”對(duì)話框。

③在“允許此工作表的所有用戶進(jìn)行”列表中，選擇允許他人能夠更改的項(xiàng)目。圖7.9“保護(hù)工作表”對(duì)話框

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

⑤單擊“確定”按鈕，重復(fù)確認(rèn)密碼后完成設(shè)置。此時(shí)，在被保護(hù)工作表的任意一個(gè)單元格中試圖輸入數(shù)據(jù)或更改格式時(shí)，均會(huì)出現(xiàn)如圖7.10所示的提示信息。圖7.10設(shè)置保護(hù)工作表后的提示信息

2.取消工作表的保護(hù)

①選擇已設(shè)置保護(hù)的工作表，在“審閱”選項(xiàng)卡上的“更改”組中單擊“撤銷(xiāo)工作表保護(hù)”，打開(kāi)“撤銷(xiāo)工作表保護(hù)”對(duì)話框。

提示在工作表受保護(hù)時(shí)，“保護(hù)工作表”按鈕會(huì)變?yōu)椤俺蜂N(xiāo)工作表保護(hù)”。

②在“密碼”框中輸入設(shè)置保護(hù)時(shí)使用的密碼，單擊“確定”按鈕。如果未設(shè)密碼，則會(huì)直接取消保護(hù)狀態(tài)。

3.解除對(duì)部分工作表區(qū)域的保護(hù)

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

①選擇要設(shè)置保護(hù)的工作表。如果工作表已被保護(hù)，則需要先在“審閱”選項(xiàng)卡上的“更改”組中，單擊“撤銷(xiāo)工作表保護(hù)”撤銷(xiāo)保護(hù)。

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

③在“開(kāi)始”選項(xiàng)卡上的“單元格”組中，單擊“格式”按鈕，從打開(kāi)的下拉列表中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框。

④在“保護(hù)”選項(xiàng)卡下，單擊“鎖定”取消對(duì)該復(fù)選框的選擇，如圖7.11所示。單擊“確定”按鈕，當(dāng)前選定的單元格區(qū)域?qū)?huì)被排除在保護(hù)范圍之外。圖7.11在“設(shè)置單元格格式”對(duì)話框的“保護(hù)”選項(xiàng)卡下解除“鎖定”

⑤設(shè)置隱藏公式。如果不希望他人看到公式或函數(shù)的構(gòu)成，可以設(shè)置隱藏該公式。在工作表中選擇需要隱藏的公式所在的單元格區(qū)域，再次打開(kāi)“設(shè)置單元格格式”對(duì)話框，在“保護(hù)”選項(xiàng)卡中保證“鎖定”復(fù)選框被選中的同時(shí)再單擊選中“隱藏”復(fù)選框，單擊“確定”按鈕。此時(shí)，公式不但不能修改還不能被看到。

⑥在“審閱”選項(xiàng)卡上的“更改”組中，單擊“保護(hù)工作表”，打開(kāi)“保護(hù)工作表”對(duì)話框。

⑦輸入保護(hù)密碼，在“允許此工作表的所有用戶進(jìn)行”列表中設(shè)定允許他人能夠更改的項(xiàng)目后，單擊“確定”按鈕。

4.允許特定用戶編輯受保護(hù)的工作表區(qū)域

如果一臺(tái)計(jì)算機(jī)中有多個(gè)用戶，或者在一個(gè)工作組中包括多臺(tái)計(jì)算機(jī)，那么可通過(guò)該項(xiàng)設(shè)置允許其他用戶編輯工作表中指定的單元格區(qū)域，以實(shí)現(xiàn)數(shù)據(jù)共享。

①選擇要進(jìn)行設(shè)置的工作表區(qū)域，如果已設(shè)置工作表保護(hù)，則需要先撤銷(xiāo)保護(hù)。

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

③單擊“新建”按鈕，打開(kāi)如圖7.12(b)所示的“新區(qū)域”對(duì)話框，添加一個(gè)新的可編輯區(qū)域，默認(rèn)為當(dāng)前選定的區(qū)域。在對(duì)話框中為所選區(qū)域輸入一個(gè)標(biāo)題名稱(chēng)，并可輸入訪問(wèn)密碼。

④單擊“權(quán)限”按鈕，在彈出的“權(quán)限”對(duì)話框中指定可以訪問(wèn)該區(qū)域的用戶，單擊“確定”按鈕。

⑤單擊“允許用戶編輯區(qū)域”對(duì)話框左下角的“保護(hù)工作表”按鈕，在隨后彈出的對(duì)話框中設(shè)定保護(hù)密碼及可更改項(xiàng)目。

(a)“允許用戶編輯區(qū)域”對(duì)話框(b)“新區(qū)域”對(duì)話框圖7.12設(shè)定允許用戶編輯的區(qū)域

7.2.6同時(shí)對(duì)多張工作表進(jìn)行操作

1.選擇多張工作表

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

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

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

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

當(dāng)進(jìn)行了多張工作表組合以后，工作簿標(biāo)題欄中的文件名之后將會(huì)增加“［工作組］”字樣，如圖7.13所示。圖7.13多張工作表組合后標(biāo)題欄顯示［工作組］

2.同時(shí)對(duì)多張工作表進(jìn)行操作

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

3.填充成組工作表

可以先在一張工作表中輸入數(shù)據(jù)并進(jìn)行格式化操作，然后將這張工作表中的內(nèi)容及格式填充到同組的其他工作表中，以便快速生成一組基本結(jié)構(gòu)相同的工作表，具體方法如下。

①首先在一張工作表中輸入基礎(chǔ)數(shù)據(jù)，并對(duì)數(shù)據(jù)進(jìn)行格式化操作。

②然后插入多張空表。

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

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

⑤從“填充”區(qū)域下選擇需要填充的項(xiàng)目，單擊“確定”按鈕。其中，“全部”將復(fù)制選中的包括格式及數(shù)據(jù)的全部?jī)?nèi)容，“內(nèi)容”將只復(fù)制選中的數(shù)據(jù)內(nèi)容，而“格式”將只復(fù)制選中的格式。

⑥此時(shí)在某一張工作表中輸入數(shù)據(jù)、設(shè)置格式，均會(huì)同時(shí)顯示在同組的其他工作表中。

⑦在Sheetl表標(biāo)簽上單擊鼠標(biāo)，退出工作組狀態(tài)。查看各個(gè)工作表，看是否已應(yīng)用相同的表格數(shù)據(jù)及格式。

7.2.7工作窗口的視圖控制

當(dāng)表格中的數(shù)據(jù)量大到超過(guò)一屏?xí)r，對(duì)工作窗口的視圖控制就變得重要起來(lái)。靈活地控制視圖顯示，可以提高表格查看及編輯的速度。

1.多窗口顯示與切換

在Excel中，可以同時(shí)打開(kāi)多個(gè)工作簿。當(dāng)一個(gè)工作簿中的工作表很大，大到一個(gè)窗口中很難顯示出全部的行或列時(shí)，還可以將工作表劃分為多個(gè)臨時(shí)窗口，這樣可以進(jìn)行方便地排列及切換，以便于比較及引用。

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

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

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

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

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

2.凍結(jié)窗口

當(dāng)一個(gè)工作表超長(zhǎng)超寬，操作滾動(dòng)條查看超出窗口大小的數(shù)據(jù)時(shí)，由于已看不到行列標(biāo)題，可能無(wú)法分清楚某行或某列數(shù)據(jù)的含義，這時(shí)可以通過(guò)凍結(jié)窗口來(lái)鎖定行列標(biāo)題不隨滾動(dòng)條滾動(dòng)。

凍結(jié)窗口的方法：在工作表中的某個(gè)單元格中單擊鼠標(biāo)，該單元格上方的行和左側(cè)的列將在鎖定范圍之內(nèi)；然后在“視圖”選項(xiàng)卡上的“窗口”組中單擊“凍結(jié)窗格”按鈕，從打開(kāi)的下拉列表中選擇“凍結(jié)拆分窗格”，當(dāng)前單元格上方的行和左側(cè)的列始終保持可見(jiàn)，不會(huì)隨著操作滾動(dòng)條而消失。

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

3.拆分窗口

在工作表的某個(gè)單元格中單擊鼠標(biāo)，在“視圖”選項(xiàng)卡上的“窗口”組中單擊“拆分”按鈕，以當(dāng)前單元格為坐標(biāo)，將窗口拆分為四個(gè)，每個(gè)窗口中均可進(jìn)行編輯。再次單擊“拆分”按鈕可取消窗口拆分效果。

4.窗口縮放

通過(guò)“視圖”選項(xiàng)卡上的“顯示比例”組，可以對(duì)當(dāng)前窗口的顯示進(jìn)行縮放設(shè)置，其中：

(1)顯示比例：?jiǎn)螕粼摪粹o，彈出“顯示比例”對(duì)話框，在該對(duì)話框中可以自由指定一個(gè)顯示比例?？s放到選定區(qū)域：選擇某一個(gè)區(qū)域，單擊該按鈕，窗口中恰好顯示選定的區(qū)域。

(2)?100%：?jiǎn)螕粼摪粹o，可恢復(fù)正常大小的顯示比例。

7.3數(shù)據(jù)分析與處理

在工作表中輸入基礎(chǔ)數(shù)據(jù)后需要對(duì)這些數(shù)據(jù)進(jìn)行組織、整理、排列、分析，從中獲取更加豐富實(shí)用的信息。為了實(shí)現(xiàn)這一目的，Excel提供了豐富的數(shù)據(jù)處理功能，可以對(duì)大量、無(wú)序的原始數(shù)據(jù)資料進(jìn)行深入地處理與分析。本章的功能全部是基于正確的數(shù)據(jù)列表基礎(chǔ)上實(shí)現(xiàn)的，因此在本章內(nèi)容開(kāi)始前，需要重點(diǎn)強(qiáng)調(diào)一下數(shù)據(jù)列表的構(gòu)建規(guī)則。

(1)數(shù)據(jù)列表一般是一個(gè)矩形區(qū)域，應(yīng)與周?chē)姆菙?shù)據(jù)列表內(nèi)容用空白行列分隔開(kāi)，也就是說(shuō)一組數(shù)據(jù)列表中沒(méi)有空白的行或列。

(2)數(shù)據(jù)列表應(yīng)有一個(gè)標(biāo)題行。作為每列數(shù)據(jù)的標(biāo)志，列標(biāo)題應(yīng)便于理解數(shù)據(jù)的含義。標(biāo)題一般不能使用純數(shù)值，不能重復(fù)，也不能分置于兩行中。

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

(4)每一列中的數(shù)據(jù)格式一般應(yīng)該統(tǒng)一。

7.3.1合并計(jì)算

若要匯總和報(bào)告多個(gè)單獨(dú)工作表中數(shù)據(jù)的結(jié)果，可以將各個(gè)單獨(dú)工作表中的數(shù)據(jù)合并到一個(gè)主工作表。被合并的工作表可以與合并后的主工作表位于同一工作簿，也可以位于其他工作簿中。多表合并基本操作步驟如下：

①打開(kāi)要進(jìn)行合并計(jì)算的工作簿。

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

③在“數(shù)據(jù)”選項(xiàng)卡上的“數(shù)據(jù)工具”組中，單擊“合并計(jì)算”按鈕，打開(kāi)“合并計(jì)算”對(duì)話框，如圖7.16所示。

圖7.16打開(kāi)“合并計(jì)算”對(duì)話框

④在“函數(shù)”下拉框中，選擇一個(gè)匯總函數(shù)。

⑤在“引用位置”框中單擊鼠標(biāo)，然后在包含要對(duì)其進(jìn)行合并計(jì)算的數(shù)據(jù)的工作表中選擇合并區(qū)域。

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

⑦重復(fù)步驟⑤和步驟⑥以添加其他的合并數(shù)據(jù)區(qū)域。

⑧在“標(biāo)簽位置”組下，按照需要單擊選中表示標(biāo)簽在源數(shù)據(jù)區(qū)域中所在位置的復(fù)選框，可以只選一個(gè)，也可以兩者都選。如果選中“首行”或“最左列”，Excel將對(duì)相同的行標(biāo)題或列標(biāo)題中的數(shù)據(jù)進(jìn)行合并計(jì)算。

⑨單擊“確定”按鈕，完成數(shù)據(jù)合并。

⑩對(duì)合并后的數(shù)據(jù)表進(jìn)行修改完善，如進(jìn)行格式化、輸入相關(guān)數(shù)據(jù)等。

7.3.2對(duì)數(shù)據(jù)排序

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

①打開(kāi)工作簿文件，輸入、設(shè)計(jì)要排序的數(shù)據(jù)區(qū)域。

②選擇要排序的列中的某個(gè)單元格，Excel自動(dòng)將其周?chē)B續(xù)的區(qū)域定義為參與排序的區(qū)域，且指定首行為標(biāo)題行，或者直接選擇包含標(biāo)題行的排序區(qū)域。

③在如圖7.17所示的“數(shù)據(jù)”選項(xiàng)卡的“排序和篩選”組中，按下列提示選擇排序方式：

單擊升序按鈕，當(dāng)前數(shù)據(jù)區(qū)域按升序進(jìn)行排序；

單擊降序按鈕，當(dāng)前數(shù)據(jù)區(qū)域按降序進(jìn)行排序。圖7.17“排序和篩選”組中用于排序的按鈕

7.3.3復(fù)雜多條件排序

可以根據(jù)需要設(shè)置多條件排序。例如，在對(duì)成績(jī)按總分高低進(jìn)行排序時(shí)，總分相同的情況下語(yǔ)文成績(jī)高的排名靠前，這就需要設(shè)置多個(gè)條件。

(1)選擇要排序的數(shù)據(jù)區(qū)域，或者單擊該數(shù)據(jù)區(qū)域中的任意一個(gè)單元格。

(2)在“數(shù)據(jù)”選項(xiàng)卡的“排序和篩選”組中，單擊“排序”按鈕，打開(kāi)“排序”對(duì)話框。

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

圖7.18在“排序”對(duì)話框中設(shè)定排序條件

①在“主要關(guān)鍵字”下拉列表中選擇列標(biāo)題名，作為排序的第一依據(jù)。

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

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

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

(5)如需要對(duì)排序條件進(jìn)行進(jìn)一步設(shè)置，可單擊對(duì)話框右上方的“選項(xiàng)”按鈕，打開(kāi)“排序選項(xiàng)”對(duì)話框，在該對(duì)話框中進(jìn)行相應(yīng)的設(shè)置。其中，對(duì)西文文本數(shù)據(jù)排序時(shí)可以區(qū)分大小寫(xiě)；對(duì)中文文本數(shù)據(jù)可以改為按筆畫(huà)多少排序，還可以設(shè)置按行進(jìn)行排序，默認(rèn)情況下均是按列排序的。設(shè)置完畢后單擊“確定”按鈕。

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

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

7.3.4按自定義列表進(jìn)行排序

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

①首先，需要通過(guò)“文件”選項(xiàng)卡→“選項(xiàng)”→“高級(jí)”→“常規(guī)”→“編輯自定義列表”按鈕創(chuàng)建一個(gè)自定義序列。

②選擇要排序的數(shù)據(jù)區(qū)域，或者確保活動(dòng)單元格在數(shù)據(jù)列表中。

③在“數(shù)據(jù)”選項(xiàng)卡的“排序和篩選”組中，單擊“排序”按鈕，打開(kāi)“排序”對(duì)話框。

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

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

圖7.19應(yīng)用自定義序列進(jìn)行排序

7.3.5篩選數(shù)據(jù)

通過(guò)篩選功能，可以快速?gòu)臄?shù)據(jù)列表中查找符合條件的數(shù)據(jù)或者排除不符合條件的數(shù)據(jù)。篩選條件可以是數(shù)值或文本，可以是單元格顏色，還可以根據(jù)需要構(gòu)建復(fù)雜條件以實(shí)現(xiàn)高級(jí)篩選。

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

1.自動(dòng)篩選

使用自動(dòng)篩選來(lái)篩選數(shù)據(jù)，可以快速而又方便地查找和使用數(shù)據(jù)列表中數(shù)據(jù)的子集。

①打開(kāi)工作簿，在工作表中選擇要篩選的數(shù)據(jù)列表，或者在數(shù)據(jù)列表的任一單元格中單擊。

②在“數(shù)據(jù)”選項(xiàng)卡上的“排序和篩選”組中，單擊“篩選”按鈕，進(jìn)入到自動(dòng)篩選狀態(tài)。當(dāng)前數(shù)據(jù)列表中的每個(gè)列標(biāo)題旁邊均出現(xiàn)一個(gè)篩選箭頭。

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

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

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

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

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

·按指定的條件篩選數(shù)據(jù)。將光標(biāo)指向“數(shù)字篩選”或“文本篩選”命令，在隨后彈出的子菜單中設(shè)定一個(gè)條件。單擊最下邊的“自定義篩選”命令，將會(huì)打開(kāi)“自定義自動(dòng)篩選方式”對(duì)話框，在其中設(shè)定篩選條件即可。

⑤在第一次篩選的基礎(chǔ)上，可再次對(duì)另一列標(biāo)題設(shè)定篩選條件，實(shí)現(xiàn)雙重甚至多重嵌套篩選。例如，可以先從成績(jī)表中篩選出全年級(jí)總分前10名，然后再?gòu)目偡智?0名中篩選出男生的數(shù)據(jù)。

2．級(jí)篩選

通過(guò)構(gòu)建復(fù)雜條件可以實(shí)現(xiàn)高級(jí)篩選。所構(gòu)建的復(fù)雜條件需要放置在工作表單獨(dú)的區(qū)域中，可以為該條件區(qū)域命名以便引用。用于高級(jí)篩選的復(fù)雜條件可以像在公式中那樣使用下列運(yùn)算符比較兩個(gè)值：=(等號(hào))、>(大于號(hào))、<(小于號(hào))、>=(大于等于號(hào))、<=(小于等于號(hào))、<>(不等號(hào))。

1)創(chuàng)建復(fù)雜篩選條件

構(gòu)建復(fù)雜條件的原則：條件區(qū)域中必須有列標(biāo)題，且與包含在數(shù)據(jù)列表中的列標(biāo)題一致；表示“與(and)”的多個(gè)條件應(yīng)位于同一行中，意味著只有這些條件同時(shí)滿足的數(shù)據(jù)才會(huì)被篩選出來(lái)；表示“或(or)”的多個(gè)條件應(yīng)位于不同的行中，意味著只要滿足其中的一個(gè)條件就會(huì)被篩選出來(lái)。在要進(jìn)行篩選的數(shù)據(jù)區(qū)域外或者在新的工作表中，單擊放置篩選條件的條件區(qū)域左上角的單元格。輸入作為條件的列標(biāo)題，必須與數(shù)據(jù)表中的列標(biāo)題對(duì)應(yīng)一致。

2)高級(jí)篩選操作步驟

高級(jí)篩選必須有一個(gè)條件區(qū)域，條件區(qū)域距數(shù)據(jù)清單至少一行一列。篩選結(jié)果可以顯示在源數(shù)據(jù)區(qū)，也可以顯示在新的區(qū)域。

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

篩選條件中用到的字段名，尤其是當(dāng)字段名中含有空格時(shí)，為了確保完全相同，應(yīng)采用復(fù)制的方法將字段名復(fù)制到條件區(qū)域中，以免出錯(cuò)。

篩選條件的基本輸入規(guī)則：條件中用到的字段名在同一行中且連續(xù)，下方輸入條件值，“與”關(guān)系寫(xiě)在同一行上，“或”關(guān)系寫(xiě)在同一列上?！皺M并豎或”是最形象的說(shuō)法。

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

①條件“實(shí)發(fā)工資介于2300至2500，且主管地區(qū)為昆明”的輸入方法如圖7.21所示，對(duì)于這種多個(gè)條件的“與”也可以用“自動(dòng)篩選”完成。

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

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

(3)高級(jí)篩選操作過(guò)程。

①單擊數(shù)據(jù)清單內(nèi)的任意單元格。

②打開(kāi)“數(shù)據(jù)”，選擇“排序和篩選”級(jí)聯(lián)菜單中的“高級(jí)”命令，打開(kāi)“高級(jí)篩選”對(duì)話框，如圖7.24所示。圖7.24高級(jí)篩選設(shè)置

③對(duì)話框的使用：

·在“方式”欄內(nèi)選擇篩選結(jié)果存放的位置：原位置或新位置。

·“列表區(qū)域”文本框：系統(tǒng)自動(dòng)顯示當(dāng)前單元格所在的數(shù)據(jù)清單，若改變數(shù)據(jù)清單，可單擊右側(cè)的切換按鈕，返回工作表窗口，選定數(shù)據(jù)區(qū)域后(應(yīng)包括條件字段列和結(jié)果列)，單擊返回按鈕返回“高級(jí)篩選”對(duì)話框。

·用同樣的方法選擇條件區(qū)域。

·若在“方式”欄內(nèi)選擇了“將篩選結(jié)果復(fù)制到其他位置”復(fù)選框，則在“復(fù)制到”文本框中單擊切換按鈕，返回工作表，選擇結(jié)果存放在區(qū)域左上角的第一個(gè)單元格，單擊返回按鈕返回“高級(jí)篩選”對(duì)話框。

·若選中“選擇不重復(fù)的記錄”復(fù)選框，則篩選結(jié)果中不會(huì)存在完全相同的兩個(gè)記錄。

·單擊“確定”按鈕，篩選結(jié)果會(huì)出現(xiàn)在結(jié)果區(qū)域中。

3)清除篩選

·清除某列的篩選條件：在已設(shè)有自動(dòng)篩選條件的列標(biāo)題旁邊的篩選箭頭上單擊，從列表中選擇“從‘××’中清除篩選”，其中“××”指列標(biāo)題。

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

·退出自動(dòng)篩選狀態(tài)：在已處于自動(dòng)篩選狀態(tài)的數(shù)據(jù)列表中的任意位置單擊鼠標(biāo)，在“數(shù)據(jù)”選項(xiàng)卡上的“排序和篩選”組中單擊“篩選”按鈕。

7.3.6分類(lèi)匯總

在“工資表”中，若需要分別計(jì)算各部門(mén)的實(shí)發(fā)工資總額或部門(mén)人數(shù)，可以用分類(lèi)匯總的方法。

分類(lèi)匯總，就是將數(shù)據(jù)清單中的每類(lèi)數(shù)據(jù)進(jìn)行匯總，因此，執(zhí)行分類(lèi)匯總前必須先將數(shù)據(jù)排序，排序關(guān)鍵字作為分類(lèi)字段?！耙允裁醋侄芜M(jìn)行分類(lèi)匯總，就要以什么字段先排序”形象地說(shuō)明了這點(diǎn)。例如，“工資表”中，若按“部門(mén)”統(tǒng)計(jì)數(shù)據(jù)，就應(yīng)先按“部門(mén)”字段排序，再進(jìn)行分類(lèi)匯總。

匯總方式有：計(jì)數(shù)、求和、求平均值、最大值、最小值等。

下面以部門(mén)為分類(lèi)字段求實(shí)發(fā)工資的和為例，說(shuō)明分類(lèi)匯總的操作過(guò)程。

①先按“部門(mén)”字段排序。

②選定數(shù)據(jù)清單內(nèi)的任意單元格。

③選擇“數(shù)據(jù)”下拉菜單中的“分類(lèi)匯總”命令，打開(kāi)“分類(lèi)匯總”對(duì)話框，如圖7.25所示。圖7.25分類(lèi)匯總設(shè)置

④對(duì)話框的使用：

·在“分類(lèi)字段”下拉列表中選擇分類(lèi)字段，本例中選擇“部門(mén)”。

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

·在“選擇匯總項(xiàng)”列表框中選擇需要匯總的字段，可以選多個(gè)，本例中選擇“實(shí)發(fā)工資”。注意：要選定字段左側(cè)的復(fù)選框。

·選中“替換當(dāng)前的分類(lèi)匯總”復(fù)選框，則只顯示最新的匯總結(jié)果。

·選中“每組數(shù)據(jù)分頁(yè)”，則在每類(lèi)數(shù)據(jù)后插入分頁(yè)符。

·選中“匯總結(jié)果顯示在數(shù)據(jù)下方”復(fù)選框，則分類(lèi)匯總結(jié)果和總匯總結(jié)果顯示在明細(xì)數(shù)據(jù)下方，取消則顯示在上方。

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

圖7.26分類(lèi)匯總

利用左側(cè)的級(jí)別顯示按鈕??和折疊按鈕?、，可以隱藏或重現(xiàn)明細(xì)記錄。單擊只顯示總的匯總結(jié)果，單擊??顯示分類(lèi)匯總結(jié)果和總匯總結(jié)果，單擊??則顯示全部明細(xì)數(shù)據(jù)和匯總結(jié)果。

刪除分類(lèi)匯總：選擇“數(shù)據(jù)”下拉菜單中的“分類(lèi)匯總”命令，打開(kāi)“分類(lèi)匯總”對(duì)話框，單擊“全部刪除”按鈕。

7.3.7數(shù)據(jù)透視表

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

若要?jiǎng)?chuàng)建數(shù)據(jù)透視表，必須先行創(chuàng)建其源數(shù)據(jù)。數(shù)據(jù)透視表是根據(jù)源數(shù)據(jù)列表生成的，源數(shù)據(jù)列表中每一列都成為匯總多行信息的數(shù)據(jù)透視表字段，列名稱(chēng)為數(shù)據(jù)透視表的字段名。

1.創(chuàng)建數(shù)據(jù)透視表

①首先打開(kāi)一個(gè)空白工作簿，在工作表中創(chuàng)建數(shù)據(jù)透視表所依據(jù)的源數(shù)據(jù)列表。該源數(shù)據(jù)區(qū)域必須具有列標(biāo)題，并且該區(qū)域中沒(méi)有空行。

②在用作數(shù)據(jù)源區(qū)域中的任意一個(gè)單元格中單擊鼠標(biāo)。

③在“插入”選項(xiàng)卡上的“表格”組中單擊“數(shù)據(jù)透視表”按鈕，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，如圖7.27所示。圖7.27打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框

④指定數(shù)據(jù)來(lái)源。在“選擇一個(gè)表或區(qū)域”項(xiàng)下的“表/區(qū)域”框中顯示當(dāng)前已選擇的數(shù)據(jù)源區(qū)域，可以根據(jù)需要重新選擇數(shù)據(jù)源。

⑤指定數(shù)據(jù)透視表存放的位置。選中“新工作表”，數(shù)據(jù)透視表將放置在新插入的工作表中；選擇“現(xiàn)有工作表”，然后在“位置”框中指定放置數(shù)據(jù)透視表區(qū)域的第一個(gè)單元格，數(shù)據(jù)透視表將放置到已有工作表的指定位置。

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

⑦按照下列提示向數(shù)據(jù)透視表中添加字段。

·若要將字段放置到布局部分的默認(rèn)區(qū)域中，可在字段列表中單擊選中相應(yīng)字段名復(fù)選框。默認(rèn)情況下，非數(shù)值字段將會(huì)自動(dòng)添加到“行標(biāo)簽”區(qū)域，數(shù)值字段會(huì)添加到“數(shù)值”區(qū)域，格式為日期和時(shí)間的字段則會(huì)添加到“列標(biāo)簽”區(qū)域。

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

·如果想要?jiǎng)h除字段，只需要在字段列表中單擊取消對(duì)該字段名復(fù)選框的選擇即可。

⑧在數(shù)據(jù)透視表中篩選字段。加入到數(shù)據(jù)透視表中的字段名右側(cè)均會(huì)顯示篩選箭頭，通過(guò)該箭頭可以對(duì)數(shù)據(jù)進(jìn)行進(jìn)一步遴選。

2.更新和維護(hù)數(shù)據(jù)透視表

在數(shù)據(jù)透視表區(qū)域的任意單元格中單擊，功能區(qū)中將會(huì)出現(xiàn)“數(shù)據(jù)透視表工具”所屬的“選項(xiàng)”和“設(shè)計(jì)”兩個(gè)選項(xiàng)卡。通過(guò)如圖7.29所示的“數(shù)據(jù)透視表工具｜選項(xiàng)”選項(xiàng)卡可以對(duì)數(shù)據(jù)透視表進(jìn)行多項(xiàng)操作。其中：圖7.29“數(shù)據(jù)透視表工具｜選項(xiàng)”選項(xiàng)卡

1)刷新數(shù)據(jù)透視表

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

2)更改數(shù)據(jù)源

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

在數(shù)據(jù)透視表中單擊，從“數(shù)據(jù)透視表工具?|?選項(xiàng)”選項(xiàng)卡上單擊“數(shù)據(jù)”組中的“更改源數(shù)據(jù)”按鈕。

從打開(kāi)的下拉列表中選擇“更改數(shù)據(jù)源”命令，打開(kāi)如圖7.30所示的“更改數(shù)據(jù)透視表數(shù)據(jù)源”對(duì)話框。

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

3)更改數(shù)據(jù)透視表名稱(chēng)及布局

在“數(shù)據(jù)透視表工具?|?選項(xiàng)”選項(xiàng)卡上的“數(shù)據(jù)透視表”組中，可進(jìn)行下列設(shè)置：

在“數(shù)據(jù)透視表名稱(chēng)”下方的文本框中輸入新的透視表名稱(chēng)，可重新命名當(dāng)前透視表。

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

4)設(shè)置活動(dòng)字段

在“數(shù)據(jù)透視表工具｜選項(xiàng)”選項(xiàng)卡上的“活動(dòng)字段”組中，可進(jìn)行下列設(shè)置：

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

(2)單擊“字段設(shè)置”按鈕，打開(kāi)“值字段設(shè)置”對(duì)話框，當(dāng)前字段性質(zhì)不同，對(duì)話框中選項(xiàng)也會(huì)有所不同。圖7.32所示的是當(dāng)前字段為值匯總字段時(shí)對(duì)話框顯示的內(nèi)容，在該對(duì)話框中可以對(duì)值匯總方式、值顯示方式等進(jìn)行設(shè)置。圖7.32“值字段設(shè)置”對(duì)話框

5)對(duì)數(shù)據(jù)透視表的排序和篩選

在“數(shù)據(jù)透視表工具?|?選項(xiàng)”選項(xiàng)卡上的“排序和篩選”組中，可對(duì)透視表按行或列進(jìn)行排序。通過(guò)行標(biāo)簽或列標(biāo)簽右側(cè)的篩選箭頭，也可對(duì)透視表中的數(shù)據(jù)按指定字段進(jìn)行排序及篩選。

3.設(shè)置數(shù)據(jù)透視表格式

可以像對(duì)普通表格那樣對(duì)數(shù)據(jù)透視表進(jìn)行格式設(shè)置，因?yàn)樗緛?lái)也是個(gè)表格；還可通過(guò)如圖7.33所示的“數(shù)據(jù)透視表工具?|?設(shè)計(jì)”選項(xiàng)卡為數(shù)據(jù)透視表快速指定預(yù)置樣式。圖7.33“數(shù)據(jù)透視表工具?|?設(shè)計(jì)”選項(xiàng)卡

4.創(chuàng)建數(shù)據(jù)透視圖

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

為數(shù)據(jù)透視圖提供源數(shù)據(jù)的是相關(guān)聯(lián)的數(shù)據(jù)透視表。在相關(guān)聯(lián)的數(shù)據(jù)透視表中對(duì)字段布局和數(shù)據(jù)所做的更改，會(huì)立即反映在數(shù)據(jù)透視圖中。數(shù)據(jù)透視圖及其相關(guān)聯(lián)的數(shù)據(jù)透視表必須始終位于同一個(gè)工作簿中。

除了數(shù)據(jù)源來(lái)自數(shù)據(jù)透視表以外，數(shù)據(jù)透視圖與標(biāo)準(zhǔn)圖表的組成元素基本相同，包括數(shù)據(jù)系列、類(lèi)別、數(shù)據(jù)標(biāo)記和坐標(biāo)軸以及圖表標(biāo)題、圖例等。與普通圖表的區(qū)別在于，當(dāng)創(chuàng)建數(shù)據(jù)透視圖時(shí)，數(shù)據(jù)透視圖的圖表區(qū)中將顯示字段篩選器，以便對(duì)基本數(shù)據(jù)進(jìn)行排序和篩選。

在已創(chuàng)建好的數(shù)據(jù)透視表中單擊，該表將作為數(shù)據(jù)透視圖的數(shù)據(jù)來(lái)源。

在“數(shù)據(jù)透視表工具?|?選項(xiàng)”選項(xiàng)卡上，單擊“工具”組中的“數(shù)據(jù)透視圖”按鈕，打開(kāi)“插入圖表”對(duì)話框。

與創(chuàng)建普通圖表一樣，選擇相應(yīng)的圖表類(lèi)型和圖表子類(lèi)型。

單擊“確定”按鈕，數(shù)據(jù)透視圖插入到當(dāng)前數(shù)據(jù)透視表中，單擊圖表區(qū)中的字段篩選器，可更改圖表中顯示的數(shù)據(jù)。

在數(shù)據(jù)透視圖中單擊，功能區(qū)出現(xiàn)“數(shù)據(jù)透視圖工具”中的“設(shè)計(jì)”“布局”“格式”和“分析”四個(gè)選項(xiàng)卡。通過(guò)這四個(gè)選項(xiàng)卡，可以對(duì)透視圖進(jìn)行修飾和設(shè)置，方法與普通圖表相同。

5.刪除數(shù)據(jù)透視表或數(shù)據(jù)透視圖

可以通過(guò)下述方法刪除數(shù)據(jù)透視表或數(shù)據(jù)透視圖。

1)刪除數(shù)據(jù)透視表

(1)在要?jiǎng)h除的數(shù)據(jù)透視表的任意位置單擊。

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

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

2)刪除數(shù)據(jù)透視圖

在要?jiǎng)h除的數(shù)據(jù)透視圖中的任意空白位置單擊，然后按Delete鍵。刪除數(shù)據(jù)透視圖不會(huì)刪除相關(guān)聯(lián)的數(shù)據(jù)透視表。

7.3.8模擬分析和運(yùn)算

Excel附帶了三種模擬分析工具：?jiǎn)巫兞壳蠼?、模擬運(yùn)算表和方案管理器。方案管理器和模擬運(yùn)算表可獲取一組輸入值并確定可能的結(jié)果。單變量求解則是針對(duì)希望獲取的結(jié)果確定生成該結(jié)果的可能的各項(xiàng)值。

1.單變量求解

單變量求解用來(lái)解決以下問(wèn)題：先假定一個(gè)公式的計(jì)算結(jié)果是某個(gè)固定值，當(dāng)其中引用的變量所在單元格應(yīng)取值為多少時(shí)該結(jié)果才成立。實(shí)現(xiàn)單變量求解的基本方法如下：

(1)首先為實(shí)現(xiàn)單變量求解，在工作表中輸入基礎(chǔ)數(shù)據(jù)，構(gòu)建求解公式并輸入到數(shù)據(jù)表中。

(2)單擊選擇用于產(chǎn)生特定目標(biāo)數(shù)值的公式所在的單元格。

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

(4)在該對(duì)話框中設(shè)置用于單變量求解的各項(xiàng)參數(shù)，其中：

①目標(biāo)單元格顯示目標(biāo)值的單元格地址。

②目標(biāo)值希望得到的結(jié)果值。

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

(5)單擊“確定”按鈕，彈出“單變量求解狀態(tài)”對(duì)話框，同時(shí)數(shù)據(jù)區(qū)域中的可變單元格中顯示單變量求解值。

(6)單擊“單變量求解狀態(tài)”對(duì)話框中的“確定”按鈕，接受計(jì)算結(jié)果。

(7)重復(fù)步驟(2)～(6)，可以重新測(cè)試其他結(jié)果。

2.模擬運(yùn)算表

模擬運(yùn)算表的結(jié)果顯示在一個(gè)單元格區(qū)域中，它可以測(cè)算將某個(gè)公式中一個(gè)或兩個(gè)變量替換成不同值時(shí)對(duì)公式計(jì)算結(jié)果的影響。模擬運(yùn)算表最多可以處理兩個(gè)變量，但可以獲取與這些變量相關(guān)的眾多不同的值。模擬運(yùn)算表依據(jù)處理變量個(gè)數(shù)的不同，分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表兩種類(lèi)型。

1)單變量模擬運(yùn)算表

若要測(cè)試公式中一個(gè)變量的不同取值如何改變相關(guān)公式的結(jié)果，可使用單變量模擬運(yùn)算表。在單列或單行中輸入變量值后，不同的計(jì)算結(jié)果便會(huì)在公式所在的列或行中顯示。

(1)為了創(chuàng)建單變量模擬運(yùn)算表，首先要在工作表中輸入基礎(chǔ)數(shù)據(jù)與公式。

(2)選擇要?jiǎng)?chuàng)建模擬運(yùn)算表的單元格區(qū)域，其中第一行(或第一列)需要包含變量單元格和公式單元格。

(3)在“數(shù)據(jù)”選項(xiàng)卡上的“數(shù)據(jù)工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“模擬運(yùn)算表”命令，打開(kāi)如圖7.35所示的“模擬運(yùn)算表”對(duì)話框。

圖7.35打開(kāi)“模擬運(yùn)算表”對(duì)話框

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

(5)單擊“確定”按鈕，選定區(qū)域中自動(dòng)生成模擬運(yùn)算表。在指定的引用變量值的單元格中依次輸入不同的值，右側(cè)將根據(jù)設(shè)定公式測(cè)算不同的目標(biāo)值。

2)雙變量模擬運(yùn)算表

若要測(cè)試公式中兩個(gè)變量的不同取值如何改變相關(guān)公式的結(jié)果，可使用雙變量模擬運(yùn)算表。在單列和單行中分別輸入兩個(gè)變量值后，計(jì)算結(jié)果便會(huì)在公式所在區(qū)域中顯示。

(1)為了創(chuàng)建雙變量模擬運(yùn)算表，要在工作表中輸入基礎(chǔ)數(shù)據(jù)與公式，其中所構(gòu)建的公式至少需要引用兩個(gè)單元格。

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

(3)選擇要?jiǎng)?chuàng)建模擬運(yùn)算表的單元格區(qū)域，其中第一行和第一列需要包含公式單元格和變量值。公式應(yīng)位于所選區(qū)域的左上角。

(4)在“數(shù)據(jù)”選項(xiàng)卡上的“數(shù)據(jù)工具”組中，單擊“模擬分析”按鈕，從下拉列表中選擇“模擬運(yùn)算表”命令，打開(kāi)“模擬運(yùn)算表”對(duì)話框。

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

(6)單擊“確定”按鈕，選定區(qū)域中自動(dòng)生成一個(gè)模擬運(yùn)算表。此時(shí)，當(dāng)更改模擬運(yùn)算表中的單價(jià)或銷(xiāo)量時(shí)，其對(duì)應(yīng)的利潤(rùn)測(cè)算值就會(huì)發(fā)生變化。

3.方案管理器

模擬運(yùn)算表無(wú)法容納兩個(gè)以上的變量。如果要分析兩個(gè)以上的變量，則應(yīng)使用方案管理器。一個(gè)方案最多獲取32個(gè)不同的值，但是卻可以創(chuàng)建任意數(shù)量的方案。

方案管理器作為一種分析工具，每個(gè)方案允許建立一組假設(shè)條件，自動(dòng)產(chǎn)生多種結(jié)果，并可以直觀地看到每個(gè)結(jié)果的顯示過(guò)程，還可以將多種結(jié)果存放到一個(gè)工作表中進(jìn)行比較。

1)建立分析方案

為了創(chuàng)建分析方案，首先需要在工作表中輸入基礎(chǔ)數(shù)據(jù)與公式。數(shù)據(jù)表需要包含多個(gè)變量單元格，以及引用這些變量單元格的公式。

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

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

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

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

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

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

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

2)顯示并執(zhí)行方案

分析方案制訂好后，任何時(shí)候都可以執(zhí)行方案，以查看不同的執(zhí)行結(jié)果。

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

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

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

3)修改或刪除方案

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

(2)刪除方案：打開(kāi)“方案管理器”對(duì)話框，在“方案”列表中選擇想要?jiǎng)h除的方案，單擊“刪除”按鈕。

4)建立方案報(bào)表

當(dāng)需要將所有方案的執(zhí)行結(jié)果都顯示出來(lái)并進(jìn)行比較時(shí)，可以建立合并的方案報(bào)表。

(1)打開(kāi)已創(chuàng)建方案并希望建立方案報(bào)表的工作表，在可變單元格中輸入一組變量值作為比較的基礎(chǔ)數(shù)據(jù)，一般可以輸入0，表示未變化前的結(jié)果。

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

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

(4)在該對(duì)話框中選擇報(bào)表類(lèi)型，指定運(yùn)算結(jié)果單元格。結(jié)果單元格一般指定為方案公式所在單元格。

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

7.4Excel公式和函數(shù)

7.4.1使用公式的基本方法1.認(rèn)識(shí)公式在Excel中，公式總是以“=”開(kāi)始。默認(rèn)情況下，公式的計(jì)算結(jié)果顯示在單元格中，公式本身則可以通過(guò)編輯欄查看。構(gòu)成公式的常用要素包括：

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

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

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

2.公式的輸入與編輯

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

1)輸入公式的四個(gè)步驟

(1)定位結(jié)果：在要顯示公式計(jì)算結(jié)果的單元格中單擊鼠標(biāo)，使其成為當(dāng)前活動(dòng)單元格。

(2)構(gòu)建表達(dá)式：輸入“=”，表示正在輸入公式，否則系統(tǒng)會(huì)將其判斷為文本數(shù)據(jù)，不會(huì)產(chǎn)生計(jì)算結(jié)果。

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

(4)確認(rèn)結(jié)果：按回車(chē)鍵Enter完成輸入，計(jì)算結(jié)果顯示在相應(yīng)單元格中。

2)修改公式

用鼠標(biāo)雙擊公式所在的單元格，進(jìn)入編輯狀態(tài)，單元格及編輯欄中均會(huì)顯示公式本身，在單元格或編輯欄中均可對(duì)公式進(jìn)行修改。修改完畢，按回車(chē)鍵(Enter)確認(rèn)即可。

3)刪除公式

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

3.公式的復(fù)制與填充

輸入到單元格中的公式，可以像普通數(shù)據(jù)一樣，通過(guò)拖動(dòng)單元格右下角的填充或者從“開(kāi)始”選項(xiàng)卡上的“編輯”組中選擇“填充”進(jìn)行公式的復(fù)制填充，此時(shí)自動(dòng)填充的實(shí)際上不是數(shù)據(jù)本身，而是復(fù)制的公式。默認(rèn)情況下填充時(shí)公式對(duì)單元格的引用采用的是相對(duì)引用。

4.單元格引用

單元格引用方式分為以下幾大類(lèi)：

(1)相對(duì)引用。

(2)絕對(duì)引用。

(3)混合引用。

7.4.2定義與引用名稱(chēng)

為單元格或區(qū)域指定一個(gè)名稱(chēng)，是實(shí)現(xiàn)絕對(duì)引用的方法之一?？梢栽诠街惺褂枚x的名稱(chēng)以實(shí)現(xiàn)絕對(duì)引用?？梢远x為名稱(chēng)的對(duì)象包括常量、單元格或單元格區(qū)域、公式等。

1.了解名稱(chēng)的語(yǔ)法規(guī)則

在Excel中創(chuàng)建和編輯名稱(chēng)時(shí)需要遵循以下語(yǔ)法規(guī)則：

(1)唯一性原則。

(1)唯一性原則。

(2)有效字符。

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

(4)不能使用空格。

(5)名稱(chēng)長(zhǎng)度有限制。

(6)不區(qū)分大小寫(xiě)。

2.為單元格區(qū)域定義名稱(chēng)

為特定的單元格或區(qū)域命名可以方便快速地定位某一單元格或區(qū)域，并可在公式和函數(shù)中進(jìn)行絕對(duì)引用。

1)快速定義名稱(chēng)

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

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

(3)在“名稱(chēng)框”中輸入名稱(chēng)，最后按Enter鍵確認(rèn)。

2)將現(xiàn)有行和列標(biāo)題轉(zhuǎn)換為名稱(chēng)

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

(2)在“公式”選項(xiàng)卡上的“定義的名稱(chēng)”組中單擊“根據(jù)所選內(nèi)容創(chuàng)建”按鈕，打開(kāi)“以選定區(qū)域創(chuàng)建名稱(chēng)”對(duì)話框，如圖7.38所示。

(3)在該對(duì)話框中，通過(guò)選中“首行”“最左列”“末行”或“最右列”復(fù)選框來(lái)指定包含標(biāo)題的位置。例如選中“首行”則可將所選區(qū)域的第1行標(biāo)題設(shè)為各列數(shù)據(jù)的名稱(chēng)。

(4)單擊“確定”按鈕，完成名稱(chēng)的創(chuàng)建。通過(guò)該方式創(chuàng)建的名稱(chēng)僅引用相應(yīng)標(biāo)題下包含值的單元格，并且不包括現(xiàn)有行和列標(biāo)題。圖7.38“以選定區(qū)域創(chuàng)建名稱(chēng)”對(duì)話框

3)使用“新名稱(chēng)”對(duì)話框定義名稱(chēng)

(1)在“公式”選項(xiàng)卡上的“定義的名稱(chēng)”組中單擊“定義名稱(chēng)”按鈕，打開(kāi)如圖7.39(a)所示的“新建名稱(chēng)”對(duì)話框。

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

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

(4)可以在“備注”框中輸入最多255個(gè)字符，用于對(duì)該名稱(chēng)的說(shuō)明性批注。

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

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

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

3.引用名稱(chēng)

名稱(chēng)可直接用來(lái)快速選定已命名的區(qū)域，更重要的是可以在公式中引用名稱(chēng)以實(shí)現(xiàn)精確引用。

1)通過(guò)“名稱(chēng)框”引用

單擊“名稱(chēng)框”右側(cè)的黑色箭頭，打開(kāi)“名稱(chēng)”下拉列表，將顯示所有已被命名的單元格名稱(chēng)，但不包括常量和公式的名稱(chēng)。單擊選擇某一名稱(chēng)，該名稱(chēng)所引用的單元格或區(qū)域?qū)?huì)被選中，如果是在輸入公式的過(guò)程中，那么該名稱(chēng)將會(huì)出現(xiàn)在公式中。

2)在公式中引用

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

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

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

(4)按Enter鍵確認(rèn)輸入。

4.更改或刪除名稱(chēng)

如果更改了某個(gè)已定義的名稱(chēng)，則工作簿中所有已引用該名稱(chēng)的位置均會(huì)自動(dòng)隨之更新。

(1)在“公式”選項(xiàng)卡上的“定義的名稱(chēng)”組中，單擊“名稱(chēng)管理器”，打開(kāi)如圖7.40所示的“名稱(chēng)管理器”對(duì)話框。

(2)在該對(duì)話框的名稱(chēng)列表中，單擊要更改的名稱(chēng)，然后單擊“編輯”按鈕，打開(kāi)“編輯名稱(chēng)”。

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

(4)如果要?jiǎng)h除某一名稱(chēng)，則從列表中單擊該名稱(chēng)，然后單擊“刪除”按鈕，出現(xiàn)提示對(duì)話框，單擊其中的“確定”按鈕完成刪除操作。

(5)單擊“關(guān)閉”按鈕，退出“名稱(chēng)管理器”對(duì)話框。圖7.40“名稱(chēng)管理器”對(duì)話框

7.4.3使用函數(shù)的基本方法

1.認(rèn)識(shí)函數(shù)

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

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

函數(shù)中可以調(diào)用另一函數(shù)，稱(chēng)為函數(shù)嵌套。

2.?Excel函數(shù)分類(lèi)

Excel提供了大量工作表函數(shù)，并按其功能進(jìn)行分類(lèi)。Excel2010目前默認(rèn)提供的函數(shù)類(lèi)別共有13大類(lèi)，如表7.1中所列。

3.函數(shù)的輸入與編輯

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

1)通過(guò)“函數(shù)庫(kù)”組插入

當(dāng)能夠明確地知道所需函數(shù)屬于哪一類(lèi)別時(shí)，可釆用該方法。如平均值函數(shù)AVERAGE()為常用函數(shù)，同時(shí)又屬于統(tǒng)計(jì)類(lèi)函數(shù)，可以通過(guò)“公式”選項(xiàng)卡上“函數(shù)庫(kù)”組中的“自動(dòng)求和”類(lèi)別或者“其他函數(shù)”下的“統(tǒng)計(jì)”類(lèi)別來(lái)選擇。具體方法如下：

(1)在要輸入函數(shù)的單元格中單擊鼠標(biāo)。

(2)在“公式”選項(xiàng)卡上的“函數(shù)庫(kù)”組中單擊某一函數(shù)類(lèi)別下方的黑色箭頭。

(3)打開(kāi)的函數(shù)列表中單擊所需要的函數(shù)，彈出類(lèi)似圖7.41所示的“函數(shù)參數(shù)”對(duì)話框。

圖7.41通過(guò)“函數(shù)庫(kù)”組插入函數(shù)

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

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

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

2)通過(guò)“插入函數(shù)”按鈕插入

當(dāng)無(wú)法確定所使用的具體函數(shù)或其所屬類(lèi)別時(shí)，可通過(guò)該方法進(jìn)行“模糊”查詢，具體操作如下：

(1)在要輸入函數(shù)的單元格中單擊鼠標(biāo)。

(2)在“公式”選項(xiàng)卡上的“函數(shù)庫(kù)”組中，單擊最左邊的“插入函數(shù)”按鈕，打開(kāi)“插入函數(shù)”對(duì)話框，如圖7.42所示。

(3)在“選擇類(lèi)別”下拉列表中選擇函數(shù)類(lèi)別。

圖7.42通過(guò)插入函數(shù)按鈕插入函數(shù)

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

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

(6)單擊“確定”按鈕，在隨后打開(kāi)的“函數(shù)參數(shù)”對(duì)話框中輸入?yún)?shù)。

3)修改函數(shù)

在包含函數(shù)的單元格中雙擊鼠標(biāo)，進(jìn)入編輯狀態(tài)，對(duì)函數(shù)及參數(shù)進(jìn)行修改后按Enter鍵確認(rèn)。

7.4.4重要函數(shù)的應(yīng)用

函數(shù)是Excel應(yīng)用的利器，是必須掌握的技巧之一。本節(jié)主要介紹一些日常工作與生活中應(yīng)該了解和掌握的函數(shù)的使用方法，進(jìn)一步的函數(shù)學(xué)習(xí)則可以在實(shí)際應(yīng)用中遇到困難時(shí)借助于強(qiáng)大的幫助功能實(shí)現(xiàn)。Excel中常用函數(shù)簡(jiǎn)介如下：

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

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

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

參數(shù)說(shuō)明：至少需要包含一個(gè)參數(shù)numberl。每個(gè)參數(shù)都可以是區(qū)域、單元格引用、數(shù)組、常量、公式或另一個(gè)函數(shù)的結(jié)果。

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

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

功能：對(duì)指定單元格區(qū)域中符合指定條件的值求和。

參數(shù)說(shuō)明：

range—必需的參數(shù)，用于條件計(jì)算的單元格區(qū)域。

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

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

sum_range—可選參數(shù)，要求和的實(shí)際單元格。如果sum_range參數(shù)被省略，則Excel會(huì)對(duì)在range參數(shù)中指定的單元格求和。

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

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

criteria2],…)

功能：對(duì)指定單元格區(qū)域中滿足多個(gè)條件的單元格求和。

參數(shù)說(shuō)明：

sum_range—必需的參數(shù)，要求和的實(shí)際單元格區(qū)域。

criteria_rangel—必需的參數(shù)，在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。

criterial—必需的參數(shù)，求和的條件。條件的形式可為數(shù)字、表達(dá)式、單元格地址或文本，可用來(lái)定義將對(duì)criteria_rangel參數(shù)中的那些單元格求和。例如，條件可以表示為32、">32"、B4、"蘋(píng)果"?或?"32"。

criteria_range2，criteria2，…—可選，附加的區(qū)域及其關(guān)聯(lián)條件。最多允許127個(gè)區(qū)域/條件對(duì)。其中每個(gè)criteria_range參數(shù)區(qū)域所包含的行數(shù)和列數(shù)必須與sum_range參數(shù)相同。

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

4)絕對(duì)值函數(shù)ABS(number)

功能：返回?cái)?shù)值number的絕對(duì)值，number為必需的參數(shù)。

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

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

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

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

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

功能：將指定數(shù)值number按指定的位數(shù)num_digits進(jìn)行四舍五入。

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

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

提示通過(guò)“文件”選項(xiàng)卡→“高級(jí)”命令→選中“將精度設(shè)為所顯示的精度”復(fù)選框(如圖7.43所示)，也可以達(dá)到顯示值與實(shí)際參與計(jì)算的值相一致的效果。圖7.43設(shè)置顯示值與參與計(jì)算值的精度相一致

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

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

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

8)垂直