第四章 Excel電子表格 經(jīng)典題目解析

1、第四章 Excel電子表格 經(jīng)典題目解析本章?？疾僮鳎?.工作表的美化：包括插入，刪除工作表、工作表的重命名、增加，刪除行或列；設(shè)置 單元格格式（數(shù)據(jù)類(lèi)型、對(duì)齊方式（含合并單元格）、字體、邊框（邊框和底紋的設(shè)置） 2.數(shù)據(jù)的自動(dòng)填充功能（包括等差數(shù)列、等比數(shù)列以及公式的填充） 3.公式和函數(shù)【求和Sum(、平均值A(chǔ)verage(、最大值Max(、最小值Min(、計(jì)數(shù)Count(、條 件if(】的使用。特別注意：考察公式和函數(shù)時(shí)，有時(shí)會(huì)結(jié)合地址引用來(lái)進(jìn)行考察，如九九乘法表。 4.選擇性粘貼 5.條件格式的設(shè)置 6.排序（按照某個(gè)屬性升序、降序排序，按照多個(gè)關(guān)鍵字進(jìn)行高級(jí)排序）、篩選（“自動(dòng)篩選”

2、中的“前10個(gè).”、“自定義.”設(shè)置，“高級(jí)篩選”中的條件設(shè)置）、分類(lèi)匯總（按要求填寫(xiě)分類(lèi)字段、匯總方式以及匯總項(xiàng)） 7.插入圖表（柱狀圖、餅圖、折線圖），應(yīng)與給出的示例一摸一樣 8.行和列的插入、刪除，工作表的重命名、插入、修改標(biāo)簽顏色（1）使用“自動(dòng)填充”功能在列輸入“星期一”到“星期日”； （2）按行反選排序從星期六、星期五星期日。 解析：（1）在A1單元格輸入“星期日”，然后按住右下方黑色“+”填充柄鼠標(biāo)往下拖動(dòng)6個(gè)單元格即出結(jié)果 (2將A1單元格選中，然后按住右下方黑色“+”填充柄鼠標(biāo)往右拖動(dòng)6個(gè)單元格。 選中A1:G1單元格后， 選擇菜單“數(shù)據(jù)”|“排序”，在出現(xiàn)的對(duì)話(huà)框中選擇左下

3、方按鈕“選項(xiàng)”，之后進(jìn)行如下設(shè)置，點(diǎn)擊“確定”后將行1進(jìn)行“降序”排序即可 2、假定一個(gè)單元格的地址為$D2，則此地址的類(lèi)型是_。 A相對(duì)地址 B絕對(duì)地址 C混合地址 D三維地址 正確答案應(yīng)選C。 解析：本題考查單元格的地址表示的知識(shí)。 單元格的地址表示可以分為相對(duì)地址、絕對(duì)地址和混合地址三種： （1）相對(duì)地址：直接用列標(biāo)和行號(hào)構(gòu)成的單元格地址。如A1、G3等都是表示對(duì)應(yīng)單元格的相對(duì)地址。 （2）絕對(duì)地址：分別在列標(biāo)和行號(hào)的前面加上“$”字符而構(gòu)成的單元格地址稱(chēng)為絕對(duì)地址。如$F$6就是第F列和第6行交界處單元格的絕對(duì)地址，它的相對(duì)地址是F6。 （3）混合地址：列標(biāo)或行號(hào)之一采用絕對(duì)地址表示的

4、單元格地址。如$G5就是一個(gè)混合地址，G$5也是一個(gè)混合地址，它們一個(gè)列標(biāo)采用絕對(duì)地址，一個(gè)行號(hào)采用絕對(duì)地址。 顯然應(yīng)該選擇混合地址。 在單元格引用時(shí)，當(dāng)把帶有單元格的公式復(fù)制或填充到其他單元格時(shí)，相對(duì)地址將隨之改變，而絕對(duì)地址不變。使用上述三種不同的地址會(huì)有明顯區(qū)別，希望大家實(shí)際動(dòng)手操作一下。 3、假定單元格D3中保存的公式為“=B3+C3”，若把它復(fù)制到E4中，則E4中保存的公式為_(kāi)。 A=B3+C3B=C3+D3C=B4+C4D=C4+D4正確答案應(yīng)選D。 解析：本題考查單元格公式操作及地址引用。 據(jù)題意，單元格D3中保存的公式為“=B3+C3”，B3、C3都為相對(duì)地址，所以把D3單元格

5、復(fù)制到E4單元格，所引用單元格地址會(huì)自動(dòng)變化，這種引用方式稱(chēng)為相對(duì)引用。目標(biāo)單元格E4與D3相比多一列，并多一行，相應(yīng)的公式“=B3+C3”中的單元格在行列方向上也會(huì)加1，即E4中的公式為“=C4+D4”。要注意如果題目要求是“移動(dòng)”到E4，則E4中的公式就應(yīng)該為“=C3+D3”,因?yàn)椤耙苿?dòng)”操作即為“剪切”。 4、在Excel 2003的工作表中，假定C3:C8區(qū)域內(nèi)的每個(gè)單元格中都保存著一個(gè)數(shù)值，則函數(shù)=COUNT(C3:C8的值為_(kāi)。 A.4B.5C.6D.8正確答案應(yīng)選C。 解析：本題統(tǒng)計(jì)函數(shù)COUNT的使用方法，COUNTA使用方法也要注意。 COUNT函數(shù)返回包含數(shù)字單元格的個(gè)數(shù)。

6、COUNT 在計(jì)數(shù)時(shí)，將把數(shù)字、日期、或以文本代表的數(shù)字計(jì)算在內(nèi)；但是錯(cuò)誤值或其他無(wú)法轉(zhuǎn)換成數(shù)字的文字將被忽略。如假定C3:C8區(qū)域內(nèi)保存的數(shù)據(jù)依次為10、abc、15、de、36、fg，則函數(shù)=COUNT(C3:C8的值為_(kāi)3_。此處如果是單純統(tǒng)計(jì)單元格的個(gè)數(shù)，可以使用COUNTA函數(shù)，COUNTA函數(shù)用來(lái)統(tǒng)計(jì)非空值的單元格個(gè)數(shù)。如使用COUNTA(C3:C8來(lái)計(jì)算，則值為6，所以大家要清楚COUNT和COUNTA這兩個(gè)函數(shù)的區(qū)別。 5、在創(chuàng)建Excel圖表的過(guò)程中，操作的第2步是選擇圖表的_。 A數(shù)據(jù)源 B類(lèi)型 C選項(xiàng) D插入位置 正確答案應(yīng)選A。 解析：本題考查在Excel中創(chuàng)建圖表的工

7、作過(guò)程，需要大家在計(jì)算機(jī)上多做練習(xí)。 創(chuàng)建圖表需要經(jīng)過(guò)4個(gè)步驟，第1步是選擇圖表類(lèi)型，第二步是選擇圖表源數(shù)據(jù)，第3步是設(shè)置圖表中的各種選項(xiàng)，第4步是選擇圖表的插入位置。 6、自動(dòng)篩選 假定在一個(gè)工作表中存在著如下圖所示的一張數(shù)據(jù)表，請(qǐng)完成如下操作： (1 試采用自動(dòng)篩選功能從中篩選出單價(jià)大于或等于15，同時(shí)其數(shù)量大于15且小于25的所有記錄。 (2 將篩選結(jié)果復(fù)制到Sheet2工作表中，從單元格A1開(kāi)始保存。 操作完成后以原文件名保存在考生文件夾下 操作提示： 選擇數(shù)據(jù)表中的任一單元格，單擊“數(shù)據(jù)”|“篩選”|“自動(dòng)篩選”選項(xiàng)，使當(dāng)前數(shù)據(jù)表處于自動(dòng)篩選狀態(tài)； 單擊“單價(jià)”單元格中的向下三角，打

8、開(kāi)下拉菜單，從中選擇“自定義”選項(xiàng)，再?gòu)拇蜷_(kāi)的“自定義自動(dòng)篩選方式”對(duì)話(huà)框中，設(shè)置“大于或等于15”的條件，接著單擊“確定”按鈕后關(guān)閉對(duì)話(huà)框； 單擊“數(shù)量”單元格中的下三角按鈕，打開(kāi)下拉菜單，從中選擇“自定義”選項(xiàng)，再?gòu)拇蜷_(kāi)的“自定義自動(dòng)篩選方式”對(duì)話(huà)框中，設(shè)置“大于15同時(shí)小于25”的條件，接著單擊“確定”按鈕后關(guān)閉對(duì)話(huà)框； 將篩選結(jié)果復(fù)制到另一個(gè)工作表中，從單元格A1開(kāi)始保存。 7、高級(jí)篩選 工作表如下所示，請(qǐng)完成以下操作： （1）采用高級(jí)篩選的功能從中篩選出單價(jià)大于或等于20，或者其數(shù)量大于18的所有記錄； （2）將篩選結(jié)果復(fù)制到其他位置，清除原數(shù)據(jù)表中的信息，再把篩選結(jié)果移動(dòng)到以H31

9、單元格為左上角的區(qū)域內(nèi)。 操作提示： 先建立篩選條件區(qū)域，假定建立在N31:O33區(qū)域，則N31和O31的內(nèi)容應(yīng)分別為“單價(jià)”和“數(shù)量”， N32和O33的內(nèi)容應(yīng)分別為“>=20”和“>18”。 選擇工作表中的任一單元格，單擊“數(shù)據(jù)”“篩選”“高級(jí)篩選”，打開(kāi)“高級(jí)篩選”對(duì)話(huà)框并選擇“將篩選結(jié)果復(fù)制到其他位置”； 在“高級(jí)篩選”對(duì)話(huà)框中，進(jìn)行“篩選區(qū)域”、“條件區(qū)域”、“復(fù)制到”等三個(gè)文本框內(nèi)容的設(shè)置(分別通過(guò)單價(jià)和數(shù)量?jī)蓚€(gè)條件選項(xiàng)進(jìn)行篩選，單擊“確定”按鈕，在“復(fù)制到”區(qū)域內(nèi)就得到了篩選結(jié)果； 將兩次篩選的結(jié)果值(無(wú)重復(fù)合并到一起； 選擇原數(shù)據(jù)表的整個(gè)區(qū)域，單擊“編輯”“清除”“

10、全部”選項(xiàng)，清除原數(shù)據(jù)表； 最后把篩選結(jié)果移動(dòng)到以H31單元格為左上角的區(qū)域內(nèi)。 保存文件 小結(jié)【高級(jí)篩選步驟】 （1）首先輸入高級(jí)篩選條件，如果條件之間是邏輯“與”的關(guān)系，則條件在同一行出現(xiàn)，如果條件之間是邏輯“或”的關(guān)系，則條件在不同行出現(xiàn)。注意：不管條件之間是邏輯與還是邏輯或，屬性文字都應(yīng)該在同一行出現(xiàn)。下表分別表示各屬性之間是邏輯與和邏輯或的關(guān)系。在下面的左邊的圖篩選條件為“單價(jià)>=20 或者 數(shù)量>18”,右邊的圖篩選條件為“單價(jià)>=20 并且 數(shù)量>18”。  （2）選中所要進(jìn)行高級(jí)篩選的數(shù)據(jù)區(qū)域。 （3）點(diǎn)擊菜單“數(shù)據(jù)”“篩選”“高級(jí)篩

11、選”，在彈出的“高級(jí)篩選”對(duì)話(huà)框中，通過(guò)點(diǎn)擊按鈕來(lái)指定列表區(qū)域和條件區(qū)域 8、分類(lèi)匯總 在下面的工作表中完成以下操作： （1）按“職稱(chēng)”升序排序。 （2）按照“性別”分類(lèi)對(duì)“醫(yī)?？鄢边M(jìn)行求和匯總（匯總結(jié)果顯示在數(shù)據(jù)下方）。 完成以上操作后將該工作簿以原文件名保存到考生文件夾下。 職工簡(jiǎn)況表 職工號(hào) 姓名 性別 出生日期 職稱(chēng) 基本工資 內(nèi)部津貼 醫(yī)?？鄢?002趙西亮 男 1973-5-17 副教授 4200108075003王曉玲 女 1978-7-18 副教授 4200108065006張艷平 女 1980-7-21 講師 390092050001劉華 男 1964-6-16 教授 54

12、00126070004蔣川 男 1970-5-19 教授 5800126060005沈芬 女 1974-5-20 教授 5800126060007黃先鋒 男 1984-5-22 助教 350075050008劉群 男 1983-5-23 助教 350075050操作提示： 點(diǎn)擊“職稱(chēng)”單元格，選擇“數(shù)據(jù)”|“排序”，在彈出的排序?qū)υ?huà)框中的“主要關(guān)鍵字”列表中選擇“職稱(chēng)”屬性，右側(cè)的排序方式中選擇“升序”，點(diǎn)擊“確定”按鈕，即可進(jìn)行排序。 選擇工作表，不包括表格標(biāo)題“職工簡(jiǎn)況表”； 選擇菜單欄“數(shù)據(jù)”|“分類(lèi)匯總”，按下圖進(jìn)行設(shè)置。 【小結(jié)】：對(duì)數(shù)據(jù)清單中的數(shù)據(jù)分門(mén)別類(lèi)的統(tǒng)計(jì)處理，不需用戶(hù)自己建

13、立公式，Excel就可以自動(dòng)的對(duì)各類(lèi)別的數(shù)據(jù)進(jìn)行求和、求平均值等多種計(jì)算，并把匯總結(jié)果以分類(lèi)匯總和總計(jì)的形式顯示出來(lái)。 步驟：先排序數(shù)據(jù)分類(lèi)匯總，按要求填寫(xiě)分類(lèi)字段、匯總方式以及匯總項(xiàng)。 9、利用公式復(fù)制的方法，求出下列工作表中的“最高分”、“優(yōu)秀人數(shù)”、“優(yōu)秀率”和“總評(píng)”。其中 “優(yōu)秀人數(shù)”為各列成績(jī)大于90分的學(xué)生人數(shù)、 “總評(píng)”為每個(gè)學(xué)生的平均分。 操作提示： 在E3中輸入公式“=(B3+C3+D3/3 ”，然后用填充柄向下拖動(dòng)完成其余學(xué)生的總評(píng)結(jié)果； 在B8中輸入公式“ =MAX(B3:B7 ”，然后用填充柄向右拖動(dòng)得到其余各列的最高分； 在B9中輸入公式“=COUNTIF(B3:B

14、7,">90"”，然后用填充柄向右拖動(dòng)得到其余3列數(shù)據(jù)的“優(yōu)秀人數(shù)”結(jié)果； 在B10中輸入公式“=B9/COUNT(B3:B7”， 然后用填充柄向右拖動(dòng)完成其余3列數(shù)據(jù)的“優(yōu)秀人數(shù)”結(jié)果； 保存文件。 注意：需要大家特別記憶的函數(shù)有： =SUM（A1:D1）? （求A1到D1的數(shù)據(jù)之和）； =AVERAGE（A1：D1）（求A1到D1的數(shù)據(jù)平均值）； =MAX（A1：D1）? （求A1到D1之間數(shù)據(jù)的最大值）； =MIN（A1：D1）? （求A1到D1之間數(shù)據(jù)的最小值）； =COUNT（A1：D1）? （統(tǒng)計(jì)A1到D1之間數(shù)字個(gè)數(shù)）； =COUNTA（A1：D1）?

15、（統(tǒng)計(jì)A1到D1之間文本個(gè)數(shù)）； =if（條件，value_if_ture，value_if_false）（如果條件成立，即結(jié)果為true，返回第二的參數(shù)，如果條件不成立，即結(jié)果為false，返回第三個(gè)參數(shù)）。 10、九九乘法表 （一）打開(kāi)Excel工作簿，在Sheet1工作表中完成以下操作： （1）在B1:J1區(qū)域內(nèi)依次輸入數(shù)字19，A2:A10區(qū)域內(nèi)依次輸入數(shù)字19。 （2）然后在單元格B2內(nèi)輸入一個(gè)公式，通過(guò)拖曳填充產(chǎn)生一個(gè)具有下三角矩陣結(jié)構(gòu)的乘法九九表。 完成以上操作后將該工作簿以原文件名保存到考生文件夾下。 操作提示： 【方法一】 在一個(gè)工作表的B1:J1區(qū)域內(nèi)依次輸入19，A2:A

16、10區(qū)域內(nèi)依次輸入19； 在B2單元格內(nèi)輸入一個(gè)條件函數(shù)公式“=IF(B$1<=$A2, B$1*$A2, " "”，需要拖曳填充兩次； 首先向下拖曳填充至B10單元格或向右拖曳填充至J2單元格； 然后向右或向下拖曳填充至J10單元格。 保存文件。 【方法二】 在B1J1中輸入19，在A2A10中輸入19。 在B2中輸入九九乘法表公式：=$A2*B$1，拖曳到B2:J10區(qū)域中，使得該矩形區(qū)域全部填滿(mǎn)數(shù)據(jù)。 根據(jù)題目要求將上三角區(qū)域或下三角區(qū)域的數(shù)據(jù)刪除（選中單元格區(qū)域后按Delete鍵）。 （二）打開(kāi)Excel工作簿，在Sheet1工作表中完成以下操作 （1）在Sheet1工作表的B12:J12區(qū)域內(nèi)依次輸入數(shù)字19，A13:A21區(qū)域內(nèi)依次輸入數(shù)字19。 （2）然后在單元格B