第5章Excel2003表格處理5.1知識要點(diǎn)5.2典型試題分析5.3思考題5.4實(shí)習(xí)5.5實(shí)訓(xùn)

5.1.1內(nèi)容提要

本章介紹了美國微軟公司(Microsoft)開發(fā)的辦公系列套裝軟件Office2003的組件之一Excel2003，內(nèi)容包括基本概念、工作簿的操作、數(shù)據(jù)內(nèi)容的輸入和編輯、公式、函數(shù)、引用、數(shù)據(jù)的管理和統(tǒng)計(jì)、圖表編輯和數(shù)據(jù)透視表等知識點(diǎn)。5.1知識要點(diǎn)(1)

Excel2003的基本概念。了解工作簿、工作表、單元格和單元格區(qū)域這4個(gè)概念及它們之間的關(guān)系。

(2)使用工作表。各種類型數(shù)據(jù)的輸入，自動(dòng)填充數(shù)據(jù)；單元格、單元格區(qū)域等對象的選擇方法；單元格、行、列的插入和刪除；字體、邊框、對齊等基本操作。

(3)公式、函數(shù)和引用。這部分要求掌握公式的編輯，函數(shù)的使用格式以及相對引用、絕對引用和混合引用3種引用方式。

(4)數(shù)據(jù)的管理和統(tǒng)計(jì)。這也是要重點(diǎn)掌握的內(nèi)容之一。其中有排序、篩選、分類匯總3種重要操作。

(5)圖表的制作。這部分是常識性知識，應(yīng)用廣泛，也是Excel中比較好掌握的部分。

(6)數(shù)據(jù)透視表。這部分是對大量的數(shù)據(jù)進(jìn)行快速匯總和建立交叉表的最快捷的方法。5.1.2學(xué)習(xí)重點(diǎn)

前面已經(jīng)學(xué)習(xí)了Word2003的基本操作，Excel2003在許多方面與Word2003有共性。所以諸如對象的選擇，文字的格式設(shè)置，對象的移動(dòng)、復(fù)制和粘貼等操作，都很容易理解和掌握。只要掌握了Excel的特有功能(排序、篩選和分類匯總等)、菜單和對話框的使用，其他操作就會(huì)運(yùn)用自如。

工作簿是一個(gè)Excel文件，其擴(kuò)展名為xls；工作簿是由工作表組成的，最多有255個(gè)，最少有1個(gè)，默認(rèn)有3個(gè)；工作表是由單元格組成的，共有65536行和256列。本章的學(xué)習(xí)重點(diǎn)應(yīng)該有以下幾個(gè)方面。

1.工作表的建立、編輯與格式化

建立Excel工作表，實(shí)際上就是向表格中輸入日常生活中人們所能遇見的各種數(shù)據(jù)的過程。常見的數(shù)據(jù)類型有數(shù)值、日期、貨幣、文本，其中特別要加深理解由數(shù)字組成的文本型數(shù)據(jù)的基本概念和含義，避免操作時(shí)出錯(cuò)。同時(shí)還要特別注意日期型數(shù)據(jù)的不同表示方式。Excel的數(shù)據(jù)既可以直接從鍵盤按單元格或單元格區(qū)域輸入數(shù)據(jù)，也可以選擇【編輯】→【填充】→【序列】菜單命令，在“序列”對話框中應(yīng)用填充特性輸入數(shù)據(jù)?！靶蛄小睂υ捒蛉鐖D5-1所示。如果用戶希望自行設(shè)計(jì)一個(gè)序列，可以選擇【工具】→【選項(xiàng)】菜單命令，在選項(xiàng)對話框的自定義序列中完成設(shè)置。“自定義序列”選項(xiàng)卡如圖5-2所示。

圖5-1“序列”對話框

圖5-2“自定義序列”選項(xiàng)卡工作表的編輯主要包括修改、插入、清除、刪除、復(fù)制、移動(dòng)等功能，其中大部分功能都遵循“選擇操作范圍→單擊相應(yīng)菜單→選擇菜單命令→根據(jù)需要完成不同對話框操作”這一過程。關(guān)于工作表格式化問題，只要掌握了“單元格格式”對話框中“數(shù)字”、“對齊”、“字體”、“邊框”4個(gè)選項(xiàng)卡，并熟知其中對話框的操作，基本上可以解決大部分格式設(shè)置問題。“單元格格式”對話框如圖5-3所示。

圖5-3“單元格格式”對話框

2.公式、函數(shù)、單元格的使用

Excel作為電子表格軟件，除了用于進(jìn)行一般的表格處理外，最主要的還是它的數(shù)據(jù)計(jì)算和數(shù)據(jù)處理能力。使用公式有助于分析工作表中的數(shù)據(jù)，所以公式的基本概念和使用是必須掌握的。在Excel中，使用公式的規(guī)則是：輸入公式時(shí)必須以等號(=)開頭，可以在選中的存放運(yùn)算結(jié)果的單元格中輸入公式，也可以在編輯欄中輸入公式；“=”后面可以是數(shù)字、函數(shù)單元格名稱和運(yùn)算符號，可以使用括號以確定運(yùn)算優(yōu)先級；在公式中，冒號(：)用于多個(gè)連續(xù)的單元格，書寫格式為“單元格起始地址：單元格終了地址”。逗號(，)表示多個(gè)不連續(xù)的單元格，書寫格式為“單元格地址1，單元格地址2，…，單元格地址n”。例如，計(jì)算A1到E1這5個(gè)單元格中數(shù)字的平均值并將結(jié)果放入F1單元格中。首先確定操作對象即F1單元格，如圖5-4所示。在編輯欄輸入“=(A1+B1+C1+D1+E1)/5”，如圖5-5所示單擊編輯欄中的綠色對勾(√)或直接按回車鍵。

圖5-4存放結(jié)果的F1單元格

圖5-5公式的輸入

Excel中的每個(gè)函數(shù)由函數(shù)名及參數(shù)組成，其中函數(shù)名表示了函數(shù)的功能。函數(shù)名的后面緊跟一對圓括號，其中是函數(shù)要進(jìn)行計(jì)算需要的參數(shù)。最常用的基本函數(shù)有：SUM()、AVERAGE()、MAX()、MIN()、COUNT()和IF()6個(gè)函數(shù)。輸入函數(shù)最好使用系統(tǒng)工具欄或菜單，避免輸入錯(cuò)誤的函數(shù)名。在使用函數(shù)過程中要掌握參數(shù)輸入、修改的方法。如上例，選中存放結(jié)果的單元格F1，單擊

“ ”工具下拉按鈕選擇求平均值的函數(shù)AVERAGE()，觀察圖5-6和圖5-7可以看出，在圖5-6中函數(shù)參數(shù)用“：”(冒號)連接，圖5-7中函數(shù)參數(shù)用“，”(逗號)連接。說明圖5-6中函數(shù)的參數(shù)區(qū)域是連續(xù)的，圖5-7中函數(shù)的參數(shù)區(qū)域是不連續(xù)的。

圖5-6連續(xù)單元格地址表示

圖5-7不連續(xù)單元格地址表示圖5-8相對引用計(jì)算平均值圖5-9“排序”對話框圖5-10“自定義自動(dòng)篩選”對話框在輸入公式過程中一般使用單元格的相對引用、絕對引用和混合引用3種方式。所謂相對引用，是指選中的單元格中的運(yùn)算式子會(huì)隨著引用位置的變化而發(fā)生變化。絕對引用即選中的單元格中的運(yùn)算式子不會(huì)隨著引用位置的變化而發(fā)生變化的引用，書寫時(shí)要在被引用單元格的式子中地址行、列坐標(biāo)前面加上$符號?；旌弦镁褪窃谝粋€(gè)引用中，既有相對引用又有絕對引用。

3.數(shù)據(jù)排序、篩選與分類匯總

(1)數(shù)據(jù)的排序是調(diào)整工作表數(shù)據(jù)順序的過程，選中某一列或該行中某單元格，單擊工具欄中的“ ”(升序排序)或“ ”(降序排序)按鈕，可以對該列進(jìn)行按數(shù)值類型大小排序。如果有多個(gè)排序關(guān)鍵字，可以選擇【數(shù)據(jù)】→【排序】菜單命令，使用排序?qū)υ捒蜻M(jìn)行設(shè)置，如圖5-9所示。值得注意的是：排序只能對列進(jìn)行而不能對行進(jìn)行。

(2)數(shù)據(jù)篩選是按照給定的條件對某列中的數(shù)據(jù)有選擇性地顯示或隱藏。選擇【數(shù)據(jù)】→【篩選】→【自動(dòng)篩選】菜單命令，單擊列標(biāo)右下方的下拉按鈕，按條件選擇適當(dāng)?shù)暮Y選條件。若選擇“自定義”條件，則打開“自定義自動(dòng)篩選方式”對話框，按條件進(jìn)行設(shè)置，如圖5-10所示。

自動(dòng)篩選只解決簡單選擇問題，當(dāng)有并列條件時(shí)要使用邏輯運(yùn)算。使用自動(dòng)篩選時(shí)的條件只能對一列提出要求，當(dāng)條件對多個(gè)列同時(shí)提出時(shí)，則可以選擇【數(shù)據(jù)】→【篩選】→【高級篩選】菜單命令，彈出“高級篩選”對話框，在數(shù)據(jù)區(qū)域之外輸入篩選條件，在“列表區(qū)域”顯示整個(gè)表格的數(shù)據(jù)范圍；在“條件區(qū)域”輸入篩選條件的位置；如果選中了“將篩選結(jié)果復(fù)制到其他位置”單選框，則要在“復(fù)制到”文本框中輸入篩選結(jié)果所顯示的位置。如圖5-11所示。高級篩選中，條件輸入在同一行則表示邏輯與的關(guān)系，條件輸入在不同行則表示邏輯或的關(guān)系。

圖5-11“高級篩選”對話框

(3)分類匯總是對數(shù)據(jù)清單進(jìn)行數(shù)據(jù)分析的一種方法，匯總前先對某一列中的數(shù)據(jù)進(jìn)行分組，之后再進(jìn)行匯總。以哪個(gè)字段進(jìn)行分類就先以哪個(gè)字段進(jìn)行排序。例如：在工作表Sheet1中分別計(jì)算男、女生各科的平均成績，如圖5-12所示。先要按照分類的列(性別)對所有記錄進(jìn)行排序，如圖5-13所示。選擇【數(shù)據(jù)】→【分類匯總】菜單命令，在彈出的“分類匯總”對話框中進(jìn)行設(shè)置，如圖5-14所示。

圖5-12匯總原始表

圖5-13排序圖示

圖5-14“分類匯總”對話框

4.圖表制作

在Excel中圖表是指將工作表中的數(shù)據(jù)用圖形表示出來。用圖表可以表示出數(shù)據(jù)間的某種關(guān)系。制作圖表時(shí)要把握兩點(diǎn)：一是使用圖表向?qū)Т_定圖表數(shù)據(jù)源時(shí)，要明確制作圖表需要哪些數(shù)據(jù)源，在選取這些數(shù)據(jù)源時(shí)要把它們各自的標(biāo)志區(qū)也同時(shí)選中，還要注意系列產(chǎn)生在行還是列(簡單地說就是圖表的數(shù)據(jù)內(nèi)容和圖例交換位置)；二是圖表制作完成后的編輯修改過程，要先確定操作對象再進(jìn)行編輯設(shè)置操作。常用的操作方式是選擇【圖表】【圖表類型】→【源數(shù)據(jù)】和【圖表選項(xiàng)】3個(gè)菜單命令，仔細(xì)觀察每次打開的不同的對話框，了解其中某個(gè)選項(xiàng)的功能，基本上可以解決圖表操作方面的問題。5.1.3學(xué)習(xí)中的難點(diǎn)分析

Excel除了一般的數(shù)據(jù)計(jì)算和數(shù)據(jù)處理能力外，還具有一些數(shù)據(jù)庫管理的功能，在制表、作圖等方面都體現(xiàn)了對表處理的功能。這些功能對初學(xué)者來說還是比較困難的。除此之外，要制作一個(gè)表格并把第一行的若干個(gè)單元格合并作為表格的標(biāo)題并居中顯示，這類問題屬于應(yīng)知應(yīng)會(huì)實(shí)際應(yīng)用中比較難的問題，而且是必須掌握的知識點(diǎn)。解決這類問題，首先要輸入文字，然后進(jìn)行格式設(shè)置。先把標(biāo)題文字輸入到A1單元格，選中從A1單元格開始的若干個(gè)單元格，單擊工具欄中的“合并居中”按鈕“ ”，不僅能把這幾個(gè)單元格合并，而且還把標(biāo)題文字設(shè)置為居中對齊，這種操作方式比任何一種操作都快捷。如果還要求對表格使用系統(tǒng)自帶的某種格式，可以依次選擇【格式】→【自動(dòng)套用格式】菜單命令，在彈出的對話框中選擇具體的格式模板。如果對格式有字體和邊框要求，可以單擊對話框中的“選項(xiàng)”按鈕。

在Excel2003中為表格設(shè)置不同顏色和寬度的邊框操作和Word2003里有所不同。Word2003中是在“邊框和底紋”對話框中選擇“邊框”選項(xiàng)卡，從對話框的左側(cè)往右側(cè)進(jìn)行設(shè)置。而Excel2003中則是在“單元格格式”對話框中從右往左設(shè)置，先選擇顏色，再選擇線條樣式，最后是內(nèi)外邊框和具體的某一條邊的設(shè)置。如果還要對表格中某些單元格按要求用紅色底紋顯示，可以使用Excel2003中的“條件格式”功能。依次選擇【格式】→【條件格式】菜單命令，打開“條件格式”對話框，如圖5-15所示。在對話框的引導(dǎo)下逐步完成設(shè)置。當(dāng)條件格式有多個(gè)時(shí)，單擊“添加”按鈕。

圖5-15“條件格式”對話框

5.2.1選擇題

1.在Excel中，“Sheet1!$A$1:$E$1，Sheet1!$B$3:$D$3，Sheet1!$C$5”共選定了()個(gè)單元格。

A.

9 B.

8

C.

7 D.

6

5.2典型試題分析試題分析：Excel中的運(yùn)算符有4類，分別是算術(shù)類、比較類、文本運(yùn)算類和引用運(yùn)算類。引用運(yùn)算類中的冒號(:)表示多個(gè)連續(xù)的單元格，逗號(,)表示多個(gè)不連續(xù)的單元格，這是常見的兩種引用運(yùn)算符。這種引用只能在同一個(gè)工作表中進(jìn)行單元格的引用，而不可以引用其他工作表中的單元格。如果要在當(dāng)前單元格中引用其他工作表中的單元格，就必須在引用單元格地址前面加上它所在工作表的名稱，并用嘆號(!)作為工作表與單元格之間的分隔符。

該試題正確答案：A。

2.在Excel“單元格格式”對話框的“對齊”選項(xiàng)卡中，“垂直對齊”選項(xiàng)中不包括()。

A.兩端對齊 B.分散對齊 C.居中 D.左對齊

試題分析：Excel中的對齊方式分兩大類：水平對齊和垂直對齊。在水平對齊中又有7種對齊方式，分別是常規(guī)、靠左(縮進(jìn))、居中、靠右(縮進(jìn))、填充、兩端對齊和跨列對齊；在垂直對齊中有5種，分別是靠上、居中、靠下、兩端對齊和分散對齊。

該試題正確答案：D。

3.在Excel中，如果要輸入的文本全部為數(shù)字，比如電話號碼、郵政編碼、學(xué)號等，為了避免讓Excel誤認(rèn)為輸入的是數(shù)值型數(shù)據(jù)，可以先輸入()，再輸入這些數(shù)字。

A.

$ B.

: C.

’ D.

=

試題分析：Excel是一個(gè)電子表格軟件，還略帶一些數(shù)據(jù)運(yùn)算的功能。這樣就對數(shù)據(jù)的類型提出了要求。要認(rèn)識到不同的數(shù)據(jù)類型，有不同的表示和運(yùn)算處理方法。在Excel中的數(shù)據(jù)類型有數(shù)值、貨幣、會(huì)計(jì)專用、日期、時(shí)間、百分比、分?jǐn)?shù)、科學(xué)計(jì)數(shù)、文本和特殊等類型。輸入文本型數(shù)字時(shí)應(yīng)在數(shù)字前先輸入一個(gè)英文半角下的單引號(’)。

該試題正確答案：C。

4.在Excel中，()函數(shù)用來計(jì)算某個(gè)區(qū)域中空單元格的數(shù)量。

A.

COUNT() B.

SUM() C.

IF() D.

COUNTBLANK()

分析：本題使用的是COUNTBLANK()函數(shù)。

該試題正確答案：D。

5.在Excel2003中，將3、4兩行選中，然后進(jìn)行插入操作，下面正確的敘述是()。

A.在行號2和3之間插入兩個(gè)空行

B.在行號3和4之間插入兩個(gè)空行

C.在行號4和5之間插入兩個(gè)空行

D.在行號3和4之間插入一個(gè)空行圖5-16銷售金額-獎(jiǎng)金對照表試題分析：若要同時(shí)插入多行，可以在進(jìn)行插入操作之前選中若干行(無論其中是否輸入了文字)，然后進(jìn)行插入操作。

該試題正確答案：A。

6.某單位的獎(jiǎng)金是根據(jù)職員的銷售額來確定的。如果某職員的銷售額在10000元以上，獎(jiǎng)金為銷售額的0.5%，否則為銷售額的0.1%。工作表如圖5-16所示。計(jì)算C2單元格的值時(shí)，在編輯框中輸入的計(jì)算公式是()。

A.

=IF(B2>=10000,B2*0.5%,B2*0.1%)

B.

=COUNTIF(B2>=10000,B2*0.5%,B2*0.1%)

C.

=IF(B2>=10000,B2*0.005,B2*0.001)

D.

=COUNTIF(B2>=10000,B2*0.005,B2*0.001)

試題分析：在Excel2003中對于這種只有兩種情況的問題，最佳的解決方法就是使用IF()函數(shù)。本題目中職工的獎(jiǎng)金為銷售額的0.5%或0.1%，個(gè)人到底發(fā)多少要看其銷售額是否超過10000元，這正和IF()函數(shù)的功能一致。函數(shù)格式為IF(Logical_Test，Value_If_True，Value_If_False)，其中Logical_Test表示條件，結(jié)果為True或False的任意值或表達(dá)式。如果條件的邏輯值為True，則IF函數(shù)的計(jì)算結(jié)果為Value_If_True；如果條件的邏輯值為False，則IF函數(shù)的計(jì)算結(jié)果為Value_If_False。另外，百分?jǐn)?shù)的表示也和數(shù)學(xué)中的不一樣，Excel2003中百分?jǐn)?shù)要用小數(shù)表示。

該試題正確答案：C。5.2.2填空題

1.一個(gè)Excel工作表中第5行第4列的單元格地址是

。

試題分析：Excel為用戶提供了一個(gè)很大的表格，用戶能用多少就用多少，沒有用到的單元格系統(tǒng)不會(huì)處理，也不會(huì)影響任何的操作。但是單元格太多不好區(qū)分，就采用了類似平面坐標(biāo)系的方法，以窗口的左上角為原點(diǎn)，水平方向上每一列為A、B、C、…、Z、AA、AB、…、AZ、BA、…、IV，垂直方向每一行為1、2、3、…、65536。

該試題正確答案：D5。

2.表格中已把前三季度的總和計(jì)算出來，如圖5-17所示。現(xiàn)在要把D2:D5單元格中的數(shù)值復(fù)制到F2:F5中去。把D2:D5區(qū)域復(fù)制后，在F2:F5區(qū)域中粘貼時(shí)要選擇

粘貼。

試題分析：Excel中的粘貼根據(jù)實(shí)際的需要，分為粘貼“公式”、“值”、“無邊框”、“轉(zhuǎn)置”等，如圖5-18所示。在這個(gè)題目中，要求把D2:D5單元格中的數(shù)值復(fù)制到F2：F5中去，那么在進(jìn)行粘貼操作時(shí)就一定要選擇粘貼的類型，否則就會(huì)發(fā)生錯(cuò)誤。具體應(yīng)選擇什么類型的粘貼，要看題目具體而定。

該試題正確答案：“值”。

圖5-17單元格數(shù)據(jù)的復(fù)制

圖5-18選擇性粘貼

3.在Excel2003中所有的公式和算式都是以

開頭的。

試題分析：在Excel2003中公式和算式都是以等號(=)開頭的。

該試題正確答案：=。

4.在Sheet2中引用Sheet1中A2到C5單元格區(qū)域的數(shù)據(jù)，正確的引用表述是____。

試題分析：在當(dāng)前工作表中引用非當(dāng)前工作表中的數(shù)據(jù)時(shí)，方法是在單元格地址引用格式和表示其所在工作表表名之間用驚嘆號(!)分隔。

該試題正確答案：Sheet1!A2:C5。5.2.3簡答題

1.在Excel2003中，清除和刪除有什么不同？

圖5-19清除菜單試題分析解答：刪除，顧名思義就是把單元格中的文字連同修飾格式、公式、批注等全部刪除，該位置由與其相鄰的其他單元格填充。清除包括的內(nèi)容就很多了，如圖5-19所示。清除不僅可以刪除單元格中的內(nèi)容，清除格式、批注，也可以將上述內(nèi)容全部一次刪除。

2.說明單元格相對引用和絕對引用的區(qū)別。

試題分析解答：引用是Excel2003中最重要的內(nèi)容之一。相對是指兩個(gè)單元格之間存在著某種聯(lián)系，或者說彼此之間不是孤立的。而絕對則是指兩個(gè)單元格之間彼此是孤立的，不存在某種聯(lián)系。相對引用是指，當(dāng)用戶要對其他的單元格使用與當(dāng)前單元格一樣的公式或算式時(shí)，就不必再次編輯，只需要將當(dāng)前單元格中的內(nèi)容進(jìn)行引用就可以了，也就是說被引用單元格中的內(nèi)容會(huì)隨著引用位置的不同而發(fā)生變化。絕對引用的含義是被引用單元格中的內(nèi)容不會(huì)隨著引用位置的不同而發(fā)生變化。在當(dāng)前單元格中絕對引用要在單元格地址的行列坐標(biāo)前添加“$”符號。所以，該試題正確答案：相對引用是被引用單元格中的內(nèi)容會(huì)隨著引用位置的不同而發(fā)生變化，而絕對引用是被引用單元格中的內(nèi)容不會(huì)隨著引用位置的不同而發(fā)生變化。絕對引用要在單元格地址的行列坐標(biāo)前添加“$”符號。圖5-20單元格格式對話框的“數(shù)字”選項(xiàng)卡

3.如何在單元格中保留小數(shù)的位數(shù)？

試題分析解答：選中單元格區(qū)域，打開“單元格格式”對話框，如圖5-20所示。選擇“數(shù)字”選項(xiàng)卡中的數(shù)值分類，設(shè)置保留的小數(shù)位數(shù)。在這個(gè)對話框中還可以進(jìn)行好多實(shí)用的設(shè)置操作，需要掌握的選項(xiàng)卡有：對齊、字體、邊框。所以，該試題正確答案：選中了單元格區(qū)域之后，打開“單元格”格式對話框，選擇其中的“數(shù)字”選項(xiàng)卡中的“數(shù)值分類”，然后設(shè)置保留的小數(shù)位數(shù)。

4.什么是單元格、工作表、工作簿？

試題分析解答：單元格是Excel2003中最小的單位，是進(jìn)行數(shù)據(jù)輸入和計(jì)算的依據(jù)，每個(gè)單元格都有一個(gè)地址，即列號

+

行號。工作表由眾多的單元格組成，每個(gè)工作表都有一個(gè)名字(默認(rèn)為Sheet1)。工作薄實(shí)際上就是一個(gè)擴(kuò)展名為xls的Excel文件，其中可以包括255個(gè)工作表。

5.在一個(gè)新建的Excel文件中，要在A1:A30的區(qū)域中快速地填充上1、3、5、7、…、59這個(gè)公差為2的等差數(shù)列，可以采取的最佳辦法是什么？試題分析解答：最佳的辦法就是由計(jì)算機(jī)系統(tǒng)自動(dòng)完成快速填充。選擇【編輯】→【填充】→【序列】菜單命令，打開“序列”對話框，從中完成序列產(chǎn)生的方向、數(shù)列的類型以及步長(公差或公比)的設(shè)置操作。如果沒有預(yù)先選擇單元格區(qū)域，可以填寫終止值。所以，該試題正確答案：使用Excel中的填充序列功能，并進(jìn)行相應(yīng)的序列設(shè)置進(jìn)行填充。

6.簡述在Excel2003中制作一個(gè)數(shù)據(jù)透視表的操作步驟。試題分析解答：制作一個(gè)數(shù)據(jù)透視表的步驟如下：

(1)選擇【數(shù)據(jù)】→【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂υ捒颉?/p>

(2)在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А辈襟E1中指定待分析的數(shù)據(jù)源類型和報(bào)表類型，如圖5-21所示。

(3)在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А辈襟E2中選擇透視表的數(shù)據(jù)源，如圖5-22所示。

圖5-21向?qū)Р襟E1—選擇數(shù)據(jù)源和報(bào)表類型

圖5-22向?qū)Р襟E2—選定數(shù)據(jù)源區(qū)域圖5-23向?qū)Р襟E3—選擇透視表顯示位置

(4)選擇透視表顯示的位置，如圖5-23所示。

(5)單擊“完成”按鈕后，為數(shù)據(jù)透視表選擇行列標(biāo)題以及匯總類型。

該試題正確答案：同分析過程。

7.

Excel中的運(yùn)算符都有哪些？

試題分析解答：Excel中的運(yùn)算符共有4種，即算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。算術(shù)運(yùn)算符中有

+、-、×、/、^、%

六種，其中

^

表示乘方，%

表示將運(yùn)算結(jié)果用百分?jǐn)?shù)的形式表示。比較運(yùn)算符(=、<>、>、=>、<、<=)中要注意不等號的表示(<>)，以及不同數(shù)據(jù)類型之間如何比較大小。文本運(yùn)算符(&)表示把兩個(gè)文本連接成一個(gè)文本。引用運(yùn)算符(

:

和

,

)表示選擇的單元格區(qū)域是連續(xù)的還是分散的。

8.

Excel2003是否可以對數(shù)據(jù)表進(jìn)行保護(hù)，都有哪些方法？

試題分析解答：Excel2003可以對重要的數(shù)據(jù)進(jìn)行保護(hù)，具體的做法有兩種。如果要保護(hù)工作表，可以選擇【工具】→【保護(hù)】→【保護(hù)工作表】菜單命令，打開“保護(hù)工作表“對話框，如圖5-24所示在該對話框中設(shè)置打開工作表密碼。另外一種方法是直接為工作薄的打開和修改設(shè)置密碼保護(hù)，依次選擇【工具】→【選項(xiàng)】菜單命令，打開“選項(xiàng)”對話框，如圖5-25所示，在該對話框中設(shè)置密碼保護(hù)。

圖5-24“保護(hù)工作表”對話框

圖5-25“選項(xiàng)”對話框

9.在Excel“單元格格式”對話框中的“對齊”選項(xiàng)卡中，都有哪些對齊方式？

試題分析解答：如圖5-26所示。Excel中單元格文本的對齊方式有兩類。一類是“水平對齊”，包括靠左、居中、靠右、填充、兩端對齊、跨列居中和分散對齊等。另一類是“垂直對齊”，包括靠上、居中、靠下、兩端對齊、分散對齊。

圖5-26“單元格格式”對話框的“對齊”選項(xiàng)卡

1.簡述在Excel中，打印某塊特定的區(qū)域的操作過程。

2.在Excel中，函數(shù)MIN(A1:A10)和MAX(Number1,Number2,…)的功能是什么？5.3思考題

實(shí)習(xí)5-1函數(shù)在計(jì)算中的應(yīng)用

一、實(shí)習(xí)目的

(1)熟悉并掌握在Excel2003中對表格的格式設(shè)置。

(2)掌握Excel2003中AVERAGE和IF等函數(shù)的功能和使用方法。

(3)學(xué)會(huì)使用相對引用。

(4)掌握編輯表格的方法。5.4實(shí)習(xí)

二、實(shí)習(xí)環(huán)境

(1)硬件環(huán)境：Windows兼容計(jì)算機(jī)。

(2)軟件環(huán)境：WindowsXP操作系統(tǒng)，Office2003，常用漢字輸入法。

三、實(shí)習(xí)內(nèi)容

按要求和格式輸入數(shù)據(jù)，并完成指定操作，如圖5-27所示。

(1)表格的第一行要合并且水平和垂直兩個(gè)方向均居中對齊顯示，其他單元格使用水平居中對齊方式。

(2)學(xué)號要求使用自動(dòng)填充效果(注意學(xué)號的前導(dǎo)0要保留)。

(3)學(xué)生的平均分保留一位小數(shù)，讓系統(tǒng)按總分350的分?jǐn)?shù)線劃分及格和不及格。

圖5-27數(shù)據(jù)樣表

四、簡單操作步驟

(1)先在A1單元格輸入表頭文字“物流專業(yè)2008級第二學(xué)期期末成績”，然后把除學(xué)號以外的所有文字輸入到對應(yīng)的單元格中，不進(jìn)行格式設(shè)置。

(2)選擇A1到K1的單元格區(qū)域，單擊“格式”工具欄中的“”按鈕，合并單元格并使表格標(biāo)題居中顯示。選擇【格式】→【單元格】子菜單，在彈出的“單元格格式”對話框中，選擇“對齊”選項(xiàng)卡并在文本對齊方式區(qū)域?qū)⒋怪睂R設(shè)置為居中顯示。

(3)選中要輸入學(xué)號的單元格區(qū)域(A3:A18)，在“單元格格式”對話框中“數(shù)字”選項(xiàng)卡中將“分類”設(shè)置為文本，輸入第一個(gè)學(xué)生的學(xué)號：08001001。再次選擇該單元格(注意：輸入完畢按回車鍵以后再進(jìn)行選擇操作)，把鼠標(biāo)指針置于單元格右下角的控制柄(或稱填充柄)，拖曳鼠標(biāo)至A18單元格。

(4)選中填寫平均分的第一個(gè)單元格J3，單擊常用工具欄中的“”按鈕，選擇其中的平均值，這時(shí)一定要檢查AVERAGE()函數(shù)的參數(shù)是否與要運(yùn)算的單元格區(qū)域一致，按“回車鍵”后第一個(gè)同學(xué)的平均分就算出來了。

(5)計(jì)算其他同學(xué)的平均分。選中J3單元格(注意區(qū)別選中狀態(tài)和輸入狀態(tài))，把鼠標(biāo)指針置于該單元格右下角的控制柄，拖曳鼠標(biāo)至J18單元格。完成計(jì)算后再次選中該區(qū)域，在“單元格格式”對話框的“數(shù)字”選項(xiàng)卡中將“分類”設(shè)置為數(shù)值，并將小數(shù)位數(shù)設(shè)為1。

(6)要求系統(tǒng)自動(dòng)按總分350的分?jǐn)?shù)線填入及格或不及格，可以使用IF()函數(shù)。函數(shù)的格式為：IF(Logical_Test，Value_If_True，Value_If_False)。對于較為復(fù)雜的函數(shù)，可以在編輯欄輸入函數(shù)及其參數(shù)。因?yàn)閼?yīng)填寫的條件是總分是否大于350分，所以函數(shù)的第一個(gè)參數(shù)是SUM(E3:I3)>350，用于判斷及格與否的條件，第二個(gè)參數(shù)“及格”是當(dāng)判斷條件為真時(shí)單元格中填入的內(nèi)容，第三個(gè)參數(shù)“不合格”是當(dāng)判斷條件為假時(shí)單元格中填入的內(nèi)容(注意：一定要在半角或英文數(shù)字狀態(tài)下輸入)，然后再次使用相對引用就可以讓系統(tǒng)自動(dòng)填入及格或不及格。

實(shí)習(xí)5-2Excel數(shù)據(jù)圖表的建立

一、實(shí)習(xí)目的

(1)了解Excel2003中的圖表功能，掌握創(chuàng)建圖表的操作過程。

(2)學(xué)會(huì)使用圖表的編輯功能，完成對圖表的格式化操作。

二、實(shí)習(xí)環(huán)境

(1)硬件環(huán)境：Windows兼容計(jì)算機(jī)。

(2)軟件環(huán)境：WindowsXP操作系統(tǒng)，Office2003，常用漢字輸入法。

三、實(shí)習(xí)內(nèi)容

(1)使用圖5-27的數(shù)據(jù)，制作出物流2班學(xué)生的各科成績圖表。

(2)圖表標(biāo)題為“物流2班期末成績分布圖”，水平坐標(biāo)為學(xué)生姓名，垂直坐標(biāo)為學(xué)生成績，坐標(biāo)的起始值為50分，最大值為100分，每刻度5分。在每項(xiàng)圖表上顯示具體數(shù)值。

(3)計(jì)算機(jī)分值區(qū)域的顏色為紅色。

(4)把政治科目的圖示從圖表中刪除。四、簡單操作步驟

(1)數(shù)據(jù)輸入完畢后，將當(dāng)前單元格定位于數(shù)據(jù)區(qū)域內(nèi)的任意一個(gè)單元格中，單擊工具欄中的“ ”按鈕。

(2)在圖表向?qū)У囊龑?dǎo)下，經(jīng)過四步就可以制作出圖表。第一步，選擇適當(dāng)?shù)膱D表格式。第二步，選中制作圖表的數(shù)據(jù)區(qū)域和標(biāo)志區(qū)域(數(shù)據(jù)源)，即學(xué)生的姓名、科目名和成績區(qū)域(C2:C18，E2:I18)，選擇系列產(chǎn)生在“列”。如果圖表的X軸數(shù)據(jù)和圖例數(shù)據(jù)的位置互換，可以選擇系列產(chǎn)生在“行”。第三步，使用標(biāo)題、圖例和數(shù)據(jù)標(biāo)志三個(gè)選項(xiàng)卡中的內(nèi)容，并做出適當(dāng)?shù)脑O(shè)置，確定圖表插入的位置，可以放在當(dāng)前工作表中或者新建一個(gè)專門存放圖表的工作表。

(3)圖表生成后，雙擊垂直坐標(biāo)，在如圖5-28所示的“坐標(biāo)軸格式”對話框中選擇“刻度”選項(xiàng)卡，從中將最小刻度設(shè)置為50，最大刻度設(shè)置為100，主要刻度單位設(shè)置為5。

(4)用鼠標(biāo)單擊任意一個(gè)科目的成績區(qū)域，在“數(shù)據(jù)系列格式”對話框中的“數(shù)據(jù)標(biāo)志”選項(xiàng)卡中按要求設(shè)置顯示“數(shù)據(jù)標(biāo)簽”的值，如圖5-29所示。

圖5-28“坐標(biāo)軸格式”對話框

圖5-29“數(shù)據(jù)系列格式”對話框

(5)用鼠標(biāo)單擊計(jì)算機(jī)的成績數(shù)據(jù)區(qū)域，在“數(shù)據(jù)系列格式”對話框中的“圖案”選項(xiàng)卡中按要求將計(jì)算機(jī)成績數(shù)據(jù)區(qū)域?yàn)榧t色。

(6)用鼠標(biāo)單擊政治的數(shù)據(jù)圖示，然后按Delete鍵，就可以把政治科目的成績從圖表中刪除。

實(shí)習(xí)5-3Excel分類匯總與數(shù)據(jù)透視表

一、實(shí)習(xí)目的

(1)了解Excel2003中關(guān)于數(shù)據(jù)處理的概念。

(2)掌握Excel2003中數(shù)據(jù)的分類匯總。

二、實(shí)習(xí)環(huán)境

(1)硬件環(huán)境：Windows兼容計(jì)算機(jī)。

(2)軟件環(huán)境：WindowsXP操作系統(tǒng)，Office2003，常用漢字輸入法。

三、實(shí)習(xí)內(nèi)容

按如圖5-27所示的數(shù)據(jù)計(jì)算出3個(gè)班男女生各科的平均分。

四、簡單操作步驟

(1)選擇【數(shù)據(jù)】→【排序】命令，在【排序】對話框中選擇“班級”為“主要關(guān)鍵字”，選擇“性別”為“次要關(guān)鍵字”。

(2)選中除表頭以外的數(shù)據(jù)區(qū)域，然后選擇【數(shù)據(jù)】→【分類匯總】命令，在“分類匯總”對話框中按要求進(jìn)行設(shè)置，如圖5-30所示。因?yàn)橐獙δ信謩e計(jì)算平均分，所以“分類字段”是性別，“匯總方式”是平均值，“選定匯總項(xiàng)”是各個(gè)科目。

(3)最終效果如圖5-31所示。

圖5-30“分類匯總”對話框

圖5-31分類匯總效果圖

上機(jī)操作實(shí)習(xí)報(bào)告的具體格式、內(nèi)容和要求，如表5-1所示。實(shí)習(xí)5-4編寫上機(jī)操作實(shí)習(xí)報(bào)告

表5-1Excel2003上機(jī)實(shí)習(xí)報(bào)告

實(shí)訓(xùn)案例5-1Excel計(jì)算功能的應(yīng)用

一、案例分析

用Excel創(chuàng)建“每日銷售統(tǒng)計(jì)表”，如圖5-32所示。按照題目要求完成后，用Excel的保存功能直接存盤。5.5實(shí)訓(xùn)

圖5-32數(shù)據(jù)樣表具體要求如下：

(1)表格要有可視的邊框，并將表中的內(nèi)容設(shè)置為宋體、10.5磅、居中。

(2)為表中的列標(biāo)題行設(shè)置“灰色底紋”圖案格式。

(3)用公式計(jì)算表格中各物品的銷售額和利潤，計(jì)算結(jié)果保留兩位小數(shù)。

(4)在相應(yīng)的單元格中用函數(shù)計(jì)算總利潤，計(jì)算結(jié)果保留兩位小數(shù)。

(5)在相應(yīng)的單元格中用函數(shù)計(jì)算總銷售額，計(jì)算結(jié)果保留兩位小數(shù)。

二、操作過程

(1)為表格設(shè)置可視邊框。用拖動(dòng)鼠標(biāo)的方式選中A1至G14區(qū)域內(nèi)的所有單元格，選擇【格式】→【單元格】命令，在“單元格格式”對話框中選擇“邊框”選項(xiàng)卡。選擇外邊框和表格內(nèi)部的線條樣式，單擊“外邊框”和“內(nèi)部”后單擊“確定”按鈕。

(2)選中A1至F2區(qū)域的單元格，單擊“ ”合并及居中工具按鈕，輸入“每日銷售統(tǒng)計(jì)表”文本內(nèi)容。在G1和G2單元格內(nèi)分別輸入要求的文本內(nèi)容。以同樣的方法選中A14至E14區(qū)域單元格，合并單元格后輸入“合計(jì)”文本內(nèi)容。

(3)用公式計(jì)算表格中各物品的銷售額、利潤和合計(jì)。

①鼠標(biāo)單擊F4單元格，在編輯欄中輸入銷售額計(jì)算公式“=D4*E4”，把鼠標(biāo)指針置于F4單元格右下角的填充柄，拖動(dòng)鼠標(biāo)到F13單元格。

②鼠標(biāo)單擊G4單元格，在編輯欄中輸入利潤計(jì)算公式“=(D4-C4)*E4”，把鼠標(biāo)指針置于G4單元格右下角的填充柄，拖動(dòng)鼠標(biāo)到G13單元格。③鼠標(biāo)單擊F14單元格，在編輯欄中輸入合計(jì)計(jì)算公式“=Sum(F4:F13)”，把鼠標(biāo)指針置于F14單元格右下角的填充柄，拖動(dòng)鼠標(biāo)到G14單元格。

(4)設(shè)置計(jì)算結(jié)果保留兩位小數(shù)。選中F4:G14區(qū)域的單元格，選擇【格式】→【單元格】命令，在“單元格格式”對話框中選擇【數(shù)字】選項(xiàng)卡。在“小數(shù)位數(shù)”框中輸入“2”后單擊“確定”按鈕。

(5)為表中的列標(biāo)題行設(shè)置“灰色底紋”圖案格式。選中A1:G2區(qū)域單元格，在“單元格格式”對話框中選擇“圖案”選項(xiàng)卡，選擇一種灰度后單擊“確定”按鈕。

實(shí)訓(xùn)案例5-2Excel表的排序、計(jì)算與圖表制作

一、案例分析

創(chuàng)建“成績單”工作表，如圖5-33所示。按照題目要求完成后，用Excel的保存功能直接存盤。

具體要求如下：

(1)將“成績單”文字字體設(shè)置為黑體、14號、居中。

(2)用函數(shù)計(jì)算各科的平均分?jǐn)?shù)。

(3)用公式計(jì)算總評，其中總評

=

語文

×

30%

+

數(shù)學(xué)

×

30%

+

英語

×

30%

+

物理

×

10%，計(jì)算必須使用絕對引用。

(4)以4個(gè)學(xué)生的“數(shù)學(xué)”、“英語”、“物理”3門功課的成績創(chuàng)建柱形統(tǒng)計(jì)圖，結(jié)果如圖5-34所示。

(5)以姓名的筆畫數(shù)按列遞增排序。

圖5-33數(shù)據(jù)樣表

圖5-34學(xué)生成績統(tǒng)計(jì)圖

二、操作過程

(1)參考實(shí)訓(xùn)案例5-1的操作過程制作如圖5-33所示的表格。

(2)在A2單元格中輸入文本。首先輸入“各科在總評”文本，按Alt

+

Enter鍵后輸入“中所占比例”文本內(nèi)容，按Enter鍵。

(3)用函數(shù)計(jì)算各科的平均分?jǐn)?shù)。單擊B8單元格，然后在常用工具欄的自動(dòng)求和按鈕“ ”中選擇“平均值”，系統(tǒng)自動(dòng)在編輯欄中輸入AVERAGE(B4:B7)函數(shù)，按“回車”鍵后算出語文的平均分，使用相對引用求出其他科目的平均分。

(4)用公式計(jì)算總評。單擊F4單元格，在編輯欄中輸入計(jì)算總評的公式“=

B4*$B$2

+

C4*$C$2

+

D4*$D$2

+

E4*$E$2”，使用相對引用求出其他人的總評。

(5)創(chuàng)建柱形統(tǒng)計(jì)圖，步驟如下：

①選中A3:A7區(qū)域單元格，按住Ctrl鍵拖動(dòng)鼠標(biāo)選中C3:E7區(qū)域的單元格。

②選擇【插入】→【圖表】命令，選擇某一“柱形圖”圖表類型，單擊“下一步”按鈕。

③選中系列產(chǎn)生在“列”單選框，單擊“下一步”按鈕。④選擇“數(shù)據(jù)表”選項(xiàng)卡中的“顯示數(shù)據(jù)表”復(fù)選框，單擊“下一步”按鈕。

⑤選中“作為其中的對象插入”單選框，單擊“下一步”按鈕。

⑥將“學(xué)生成績統(tǒng)計(jì)圖”圖表標(biāo)題設(shè)置為藝術(shù)字。

(6)刪除A2:F2區(qū)域的單元格，選中姓名列，選擇【數(shù)據(jù)】→【排序】菜單命令，在“排序”對話框中，確定“主關(guān)鍵字”為“姓名”，單擊“選項(xiàng)”按鈕，選中“方法”選項(xiàng)的“筆畫排序”單選框，并選擇升序排列。說明：(1)可以使用菜單方式在A2單元格中輸入文本。選中該單元格后，在“單元格格式”對話框中的“對齊”選項(xiàng)卡中，選中“文本控制”區(qū)域的“自動(dòng)換行”復(fù)選框，單擊“確定”按鈕，輸入全部文字就可以分兩行顯示。

(2)計(jì)算總評使用的是混合引用。因?yàn)榭傇u成績是各科成績按比例得到的，不管實(shí)際得分是多少，各科目的比例是不變的，所以單元格地址的行、列坐標(biāo)前面都有一個(gè)“$”符號，表示絕對引用。運(yùn)算式子中有相對引用也有絕對引用，所以叫做混合引用。

一、案例分析

創(chuàng)建“信息處理技術(shù)員考試成績表”，內(nèi)容如圖5-35所示。按照題目的要求完成后，用Excel的保存功能直接存盤。實(shí)訓(xùn)案例5-3IF函數(shù)在實(shí)際問題中的巧用

圖5-35數(shù)據(jù)樣表具體要求如下：

(1)表格要有可視的邊框，并將表中文字設(shè)置為宋體、12磅、居中。

(2)用條件函數(shù)計(jì)算是否合格，其中，信息處理技術(shù)員考試的合格標(biāo)準(zhǔn)是筆試成績和機(jī)考成績均不小于45分。

(3)給出信息處理技術(shù)員考試的合格率(填入相應(yīng)的單元格中)。

(4)給出信息處理技術(shù)員考試的缺考率，其中，只有筆試和機(jī)考都沒有參加的考生才算缺考(填入相應(yīng)的單元格中)。

(5)用條件函數(shù)統(tǒng)計(jì)主要學(xué)歷，其中，學(xué)歷列中所占比例最大的一類即為主要學(xué)歷。

二、操作過程

(1)選中A1:F25區(qū)域內(nèi)的所有單元格，選擇【格式】→【單元格】命令，在“單元格格式”對話框中選擇“邊框”選項(xiàng)卡，為表格外邊框和內(nèi)部選擇合適的線條樣式，分別單擊“外邊框”和“內(nèi)部”后單擊“確定”按鈕。

(2)用條件函數(shù)計(jì)算是否合格。單擊E3單元格，在編輯欄中輸入公式“=IF(AND(C3>=

45，D3>=45)，"合格"，"不合格")”。把鼠標(biāo)指針置于E3單元格右下角的填充柄，拖動(dòng)鼠標(biāo)到E22單元格。

(3)用條件函數(shù)統(tǒng)計(jì)主要學(xué)歷。單擊C25單元格，在編輯欄中輸入公式“=IF(COUNTIF(F3:F22，"大學(xué)本科")>COUNTIF(F3:F22，"大專")，"大學(xué)本科"，"大專")”。

實(shí)訓(xùn)案例5-4SumIf函數(shù)的使用

一、案例分析

用Excel創(chuàng)建“銷售明細(xì)表”，內(nèi)容如圖5-36所示。按照題目要求完成后，用Excel的保存功能直接存盤。具體要求如下：

(1)表格要有可視的邊框，并將表中的內(nèi)容均設(shè)置為宋體、12磅、居中。

(2)將表中的月標(biāo)題單元格填充為灰色-25%，列標(biāo)題單元格填充為茶色，序號列填充為淺色。

(3)用函數(shù)計(jì)算4月、5月和6月的銷售總量，填入相應(yīng)的單元格中。

(4)用函數(shù)統(tǒng)計(jì)銷售明細(xì)表中所有“三星手機(jī)”的銷售量匯總。

(5)用函數(shù)統(tǒng)計(jì)銷售明細(xì)表中所有工號為B開頭的銷售量匯總。

圖5-36題目樣表二、操作過程

(1)先分