excel典型試題操作步驟

1、典型試題典型試題1打開素材庫中的“典型試題 ”文件，按下面的操作要求進行操作，并把操作結果存盤。注意 ：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在$走戌5的人仲元格中設置為只能錄入附數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 使用數(shù)組公式，對Sheet1 中“教材訂購情況表”的訂購金額進行計算，并將結果保存在該表的“金額”列當中。計算方法：金額= 訂數(shù) * 單價3. 使用統(tǒng)計函數(shù)，對 Sheet1 中 “教材訂購情況表”的結果按以下條件進行統(tǒng)計，并 將結果保存在Sheet1 中的相應位置。要求：（ 1） 統(tǒng)計出版社名稱為“高

2、等教育出版社”的書的種類數(shù) , 并將結果保存在sheet1中 L2 單元格中。（ 2）統(tǒng)計訂購數(shù)量大于110 且小于 850 的書的種類數(shù), 并將結果保存在sheet1 中L3 單元格中。4. 使用函數(shù)，計算每個用戶所訂購圖書所需支付的金額總數(shù)，并將結果保存在Sheet1中“用戶支付情況表”的“支付總額”列中。5. 使用函數(shù)，判斷Sheet2 中的年份是否為閏年，如果是，結果保存“閏年”，如果不是，則結果保存“平年”，并將結果保存在“是否為閏年”列中。說明：閏年定義：年數(shù)能被4 整除而不能被100 整除，或者能被400 整除的年份。6. 將 Sheet1 中的“教材訂購情況表”復制到 Shee

3、t3 中，對 Sheet3 進行高級篩選.要求：（ 1 ）篩選條件為“訂數(shù)>=500，且金額總數(shù)<=30000”。（ 2）將結果保存在Sheet3 中。注意：（ 1 ）無需考慮是否刪除或移動篩選條件。(2)復制過程中，將標題項“教材訂購情況表”連同數(shù)據(jù)一同復制。(3)數(shù)據(jù)表必須頂格放置。7.根據(jù)Sheetl中“教材訂購情況表”的結果，在他eet4中新建一張數(shù)據(jù)透視表。要求：(1)顯示每個客戶在每個出版社所訂的教材數(shù)目。(2)行區(qū)域設置為“出版社”。(3)列區(qū)域設置為“客戶”。(4(5)數(shù)據(jù)區(qū)域設置為“訂數(shù)”。)二、解答求和步驟1:選中Sieet5工作表中的A1單元格，選擇菜單“數(shù)據(jù)

4、” 一 “有效性”命 令(娘圖6-13所示)，在打開的“數(shù)據(jù)有效性”對話框中，選擇“設置”選項卡，再選搦“允許”為“文本長度”，“數(shù)據(jù)”為“等于”，在“長度”文本框中輸入“5”，如圖6-14 所示。選擇“出錯警告”選項卡，再選擇“樣式”為“警告”，在“錯誤信息”文本框 中輸入“只能錄入5位數(shù)字或文本”，如圖6-15所示，單擊“確定”按鈕。圖6-13選擇“有效性”命令圖6-14“設置”選項卡圖6-15 “出錯警告”選項卡步驟2 ：在 Sheetl工作 表中，先選中13:152 單元格 區(qū)域，再輸入 公式說明：公式中單元格或單元格區(qū)域也可用新選擇輸入，下同。步3G圖6-16“插入函數(shù)”對話框=罌&

5、#167;二售y 2 2 H單<3格H隼擊。插入函數(shù)按扭，打開“插入函數(shù)”對話框，在“選擇類別”下拉函致善數(shù)comiF一一 一R疝艷棗_司=外語教學與班究 ikr高等教育出版社sJ一 元 =”高等教育出版社”-g 計苴某個區(qū)域中濫足始定條件的單元格數(shù)目Rs中賽計算耳中非空單元格數(shù)目的區(qū)域計厘結果=S有關該函數(shù)的幫助優(yōu)）確定 取消圖6-17“函數(shù)參數(shù)”對話框選 中L3 單 元 格， 在 公 式 編 輯 欄 中說明：COUNF函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。步驟4:選中L8單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在8所示。再按“確定”按鈕，即可計算出 cl用戶所訂購圖書需

6、支付的金額總數(shù)“選擇函數(shù)”列表框中選擇“SUMIF函數(shù)。單擊“確定”按鈕，彈出“函數(shù)參數(shù)函款多款圖6-18“函數(shù)參數(shù)”對話框可 計 算 a 算 洲 算 CE « 晶 cs 4 軒無常明：SUMIF函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。彝驟5 ：在Sheet2工作表中，選擇B2單元格，在公式編輯欄中輸入公“輸F(OR(ANDiOD(A2,4=0,MODA2,100)<>0),MODA2,400)=0),"閏年"," 平年")"接回車 公，一施明：IF、OR AND MODS數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。密驟6:

7、選中Sieetl工作表中的A1： I52單元格區(qū)域，單擊右鍵在快捷菜單中選擇 嬲復缺命令。單擊Sieet3工作表中的A1單元格，單擊右鍵在快捷菜單中選擇 “粘 貼” 國令U如圖6-19所示，在K2： L3單元格區(qū)M M Sheet3工作表的空白區(qū)域創(chuàng)建篩選條件,篩選條件。單擊Sheet3工作表中A1: I52單元格區(qū)域中的任一單元格，選整批甲 即 ”可二go斷 «鬻集贊ttgtgftnff格單擊“確定”按鈕。區(qū)件填 入22.|L：至 tr 一inofi _ $鳥3 一;圖6-21 設置“列表區(qū)域”圖6-22 設置“條件區(qū)域”步驟7:單擊Sheetl工作表中A1:I52單元格區(qū)域中的任

8、一單元格， 選擇菜單“數(shù) 據(jù)” 一據(jù)透視 在彈出白如圖6-27所示界面中，將字段“出版社”拖至行字段區(qū)域，將字段“客表”拖至列字段區(qū)域，將字段“訂數(shù)”拖至數(shù)據(jù)項區(qū)域，結果如圖6-28所示。和保存文件后退出。數(shù)據(jù)透視圖命令(如圖數(shù)據(jù)mii排序二篩選3)卜記錄單)分航總®尿甌嬴數(shù)據(jù)說畫跡). 1一 , -圖6-23 選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令圖6-24”數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б灰?3步驟之1”對話框圖 6-25“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б灰?步驟之2”對話框教據(jù)透視表和數(shù)據(jù)話視圖向?qū)?3步靠之布局1 選項。I 瞅渭 上一步)完成Q)圖6-26"數(shù)據(jù)透視表和數(shù)據(jù)透視

9、圖向?qū)б灰?步驟之3”對話框稼名社戶型村版去者數(shù)價萄客Is教出版件七羊全tJddtluHHH日建型迭現(xiàn)蒙孚H利志將沌目拒蘭雌速里表Cis23圖 6-27“數(shù)據(jù)透視表”布局10 11L2 運 K 1EABCDEF1求和項:訂數(shù)國戶2出版社一一卜clc2<3ci總計3北京航大63es4北京理工4214215電子工業(yè)出版社555716266東北財姓大學出版社75757復旦大學1061068高等教育106110619高等教育出版社107191071910高教5095C911華東師大78_ 7612科學20320313科學出版社2940294014立信會計637637_15立信會計出版社30801

10、6遼寧美術出版社535817南京大學24024018清華大學12012019人民大學72172120人民衛(wèi)生36636621上海外語教育出版社50050022：天津人民美術出版社58se23外語教學與研究出版社965599S524浙江科技出版社1504150425浙江科學技術10610626浙江科學技術出版社50050027中國金融1601602B中國金融出版社6屯564529中國人大22422430中國人民大學出版社58516775231中國物資10910932總計2715819651968234333434圖6-28 數(shù)據(jù)透視表典型試題2打開素材庫中的“典型試題”文件，按下面的操作要求進行

11、操作，并把操作結果存盤。注意 ：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在$走戌5的人仲元格中設置為只能錄入 附數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 使用時間函數(shù)，對Sheet1 中用戶的年齡進行計算。要求：假設當前時間是“2008-11- 15”，結合用戶的出生年月，計算用戶的年齡，并將其計算結果保存在“年齡”列當中。計算方法為兩個時間年份之差。3. 使用REPLAC函數(shù)，對heetl中用戶的電話號碼進行升級。要求：對“原電話號碼”列中的電話號碼進行升級。升級方法是在區(qū)號（0571 ）后面加上“8”，并將其計算結果保

12、存在“升級電話號碼”列的相應單元格中。例如：電話號碼“008”升級后為“ 0808”4. 在Sieetl中，使用AND函數(shù)，根據(jù)“性別”及“年齡”列中的數(shù)據(jù)，判斷所有用戶是否為大于等于40 歲的男性，并將結果保存在“是否 >=40 男性”列中。注意：如果是，保存結果為 TRUE否則，保存結果為FALSE5. 根據(jù) Sheet1 中的數(shù)據(jù)，對以下條件，使用統(tǒng)計函數(shù)進行統(tǒng)計。要求：（ 1 ）統(tǒng)計性別為“男”的用戶人數(shù)，將結果填入 Sheet2 的 B2 單元格中。（ 2）統(tǒng)計年齡為“>40”歲的用戶人數(shù)，將結果填入Sheet2 的 B3 單元格中。6. 將 Sheet1 復制到 She

13、et3， 并對 Sheet3 進行高級篩選。要求：（ 1 ）篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)。（ 2）將篩選結果保存在Sheet3 中。注意：（ 1 ）無需考慮是否刪除或移動篩選條件。（ 2）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 的結果， 創(chuàng)建一個數(shù)據(jù)透視圖Chart1 。 要求：（ 1 ）顯示每個區(qū)域所擁有的用戶數(shù)量。（ 2） x 坐標設置為“所在區(qū)域”。（ 3）計數(shù)項為“所在區(qū)域”。（ 4）將對應的數(shù)據(jù)透視表保存在Sheet4 中。二、解答步驟1:操作方法參見“典型試題 1”中的步驟1。步驟2 ：在Sheetl工作表中，選中D2單元格，在公式編輯欄中輸入公說明：YEAR0

14、數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。 步擊G2單元格的填充柄，即可對其余電話號碼進行升級。說明：REPLAC函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。)1南敦咻教228圖6-29“函數(shù)參數(shù)”對話框曾判斷該用戶是否為大于等于 40歲的男性。E擊H2單元格的填充柄，即可判斷其余用戶是否為犬0等酌男性。A驟5:在Sheet2工作表的B2單元格中，輸入公式=COUNTS:heet1! B2:B37,"男按回 步驟6:選中Sheetl工作表中的A1: H37單元格區(qū)域，單擊右鍵在快捷菜單中選 擇復 ?！泵?。單擊Sieet3工作表中的A1單元格，單擊右鍵在快捷菜單中選擇“粘 貼”命令。Sh

15、eet3工作表的空白區(qū)域創(chuàng)建篩選條件，如圖6-30所示，在J1: K2單元格區(qū)域 中入篩選條件。單擊 Sieet3工作表中A1: H37單元格區(qū)域中的任一單元格，選擇 菜意：J1單元格中“性”和“別”之間有空格，原因是 B1單元格中“性”和“別”4伊圖6-31 設置“條件區(qū)域”篩 步驟7：單擊Sieetl工作表中A1：H37單元格區(qū)域中的任一單元格，選擇菜單“數(shù) 選對“列表區(qū) 域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域。將光標定位在“條件 區(qū)域據(jù)” 一 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?1 保存文件后退出。2圖6-32"數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б?/p>

16、一3步驟之1”對話框透單選鈕(如圖6-32圖6-33"數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б灰?步驟之2”對話框圖 6-34“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б灰?步驟之3”對話框圖6-35"數(shù)據(jù)透視表”布局圖6-36數(shù)據(jù)透視圖AE1計數(shù)項;所在區(qū)域,12府在區(qū)域，匯前3拱壁區(qū)64江干區(qū)65上城區(qū)56西湖區(qū)67下城區(qū)66余杭區(qū)79總計36圖6-37數(shù)據(jù)透視表典型試題 3打開素材庫中的“典型試題 ”文件，按下面的操作要求進行操作，并把操作結果存盤。注意 ：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1 .在$庵314的人仲元格中設置為只能錄入 附數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣

17、式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2要求：根據(jù)“企業(yè)銷售產(chǎn)品清單”，使用VLOOKUP數(shù)，將產(chǎn)品名稱和產(chǎn)品單價填充到“三月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)品單價”列中。使3.使用數(shù)組公式，計算Sheet1 中的“三月份銷售統(tǒng)計表”中的銷售金額，并將結果填入到該表的“銷售金用額”列中。計算方法：銷售金額= 產(chǎn)品單價* 銷售數(shù)量4V.使用統(tǒng)計函數(shù)，根據(jù)“三月份銷售統(tǒng)計表”中的數(shù)據(jù)，計算“分部銷售業(yè)績統(tǒng)計表”中的總銷售額，并將結果填入該表的“總銷售額”列。5.在Sheetl中，使用RANKS數(shù)，在“分部銷售業(yè)績統(tǒng)計”表中， 根據(jù)“總銷售額”O(jiān)各部門進行排名，并將結果填入到“銷售

18、排名”列中。6O. 將 sheet1 中的“三月份銷售統(tǒng)計表”復制到 Sheet2 中， 對 Sheet2 進行高級篩選。要K求：U （ 1 ） 篩選條件為： “銷售數(shù)量” >3、 “所屬部門” 市場 1 部、 “銷售金額”-P>1000。（ 2）將篩選結果保存在Sheet2 中。品單價”列進行填充。對 Sheet1 中的“三月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)注意：（ 1 ）無需考慮是否刪除或移動篩選條件。（ 2）復制過程中，將標題項“三月份銷售統(tǒng)計表”連同數(shù)據(jù)一同復制。（ 3）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 的 “三月份銷售統(tǒng)計表”中的數(shù)據(jù)，新建一個數(shù)據(jù)透視圖C

19、hart1 。 要求：（ 1 ）該圖形顯示每位經(jīng)辦人的總銷售額情況。（ 2） x 坐標設置為“經(jīng)辦人”。（ 3）數(shù)據(jù)區(qū)域設置為“銷售金額”。4(5)將對應的數(shù)據(jù)透視表保存在Sheet3中。)求二、解答和步驟1:操作方法參見“典型試題 1”中的步驟1。步驟2 ：選中Sheetl工作表中的G3單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框,在為“ “選銷援需G3單元格的填充柄，即可查找到其余產(chǎn)品型號的產(chǎn)品名稱。U 3說明：VLOOKUPi數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。$圖6-38“函數(shù)參數(shù)”對話框用部列>11單搔擇“，我(選S中3iL, C:AL$4C4步驟4:選中 O3單元格，

20、單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在單元格中，輸入公建SUMIF3：K44,"=市場2部",L3:L44） ”后，按回車鍵確單元格中，輸入公讀SUMIF（3:K44,"=市場3部",L3:L44） ”后，按回車鍵確彈圖6-39“函數(shù)參數(shù)”對話框兩籟券皴裴中。擊“數(shù)確短三粒 鈕逮出步驟擊P3單元格的填充柄，可統(tǒng)計出其他部門的總銷售額排名。說明：RANK函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。"ps't話出兀格物臬擊插欠函散工的舞琴款圖6-40“函數(shù)參數(shù)”對話框步驟6:選中Sheetl工作表中的日：L44單元格區(qū)域，單擊右鍵在快捷

21、菜單中選 擇“復 制”命令。單擊Sieet2工作表中的A1單元格，單擊右鍵在快捷菜單中選擇“選 擇性粘貼” 命令，打開如圖6-41所示的“選擇性粘貼”對話框，選中“值和數(shù)字格式”單選鈕，再單擊“確定”按鈕。在Sheet2工作表的空白區(qū)域創(chuàng)建篩選條件，如圖6-42所示，在J2： L3單元格 區(qū)域中輸入篩選條件。單擊 Sheet2工作表中A1:用4單元格區(qū)域中的任一單元格，選擇菜單“數(shù)據(jù)” 一 “篩選” 一 “高級篩選”命令，打開“高級篩選”對話框，“列表區(qū)域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域。將光標定位在“條件區(qū)域”文本框內(nèi)，用 鼠 標 拖 選 刖 面 創(chuàng) 建 的 篩 選 條 件 單選

22、探性箱貼。全部®0有效性噩證球）。公式o邊椎除外O數(shù)侑。列寬。格式矍）/公式和.格式9L0批注©<2值駿字格式巒匚）_ 運篁無中。乘®。加切。除O。減區(qū)I跳過空單元如轉(zhuǎn)置國 I 一藤 - I 取消 ；圖6-41"選擇性粘貼”對話框圖6-42創(chuàng)建篩選條件圖6-43 設置“條件區(qū)域”步驟7:單擊Sheetl工作表中E1:L44單元格區(qū)域中的任一單元格，選擇菜單“數(shù) 據(jù)” 一 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б灰?3步驟之1 6對保存文件后退出。4混? . . 一一 、一一 、一 , 比打開Sheet3工作表有如圖6-46

23、所小的相應的數(shù)據(jù)透視表。中據(jù) 透 視 圖款據(jù)透褪表和散據(jù)透規(guī)圖向?qū)?布局，拖到左邊的圖上銷售日期I所屬部門幫助砥匚熊z二g 股消圖6-44"數(shù)據(jù)透視表”布局圖6-45數(shù)據(jù)透視圖1234678T101112琦蹊惠成勇仕平丹榮 情成國 小 計 甘李劉孫王吳吳在趙總A 求和既:箱售金統(tǒng)157524416182326920397612116342411352643672624圖6-46數(shù)據(jù)透視表典型試題 4打開素材庫中的“典型試題 ”文件，按下面的操作要求進行操作，并把操作結果存盤。注意 ：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在$庵314的人仲元格中設置為只能錄入 附數(shù)字或文本

24、。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 使用HLOOKUP數(shù)，處eetl “停車情況記錄表”中的“單價”列進行填充。 要求： 根據(jù)Sieetl中的“停車價目表”價格，使用HLOOKUP數(shù)對“停車情況記錄表”中的“單價”列根據(jù)不同的車型進行填充。注意：函數(shù)中如果需要用到絕對地址的將使用絕對地址進行計算，其他方式無效。3. 在 Sheet1 中，使用時間函數(shù)計算汽車在停車庫中的停放時間。 要求：（ 1 ）計算方法為：“停放時間= 出庫時間- 入庫時間”。（ 2）格式為：“小時：分鐘：秒”。（ 3）將結果保存在“停車情況記錄表”中的“停放時間”列

25、中。（例如：一小時十五分十二秒在停放時間中的表示為：“1： 15： 12”）4. 使用函數(shù)公式，對“停車情況記錄表”的停車費用進行計算。要求：根據(jù)Sheet1 停放時間的長短計算停車費用，將計算結果填入到“停車情況記錄表”的“應付金額”列中。 注意：（ 1 ）停車按小時收費，對于不滿一個小時的按照一個小時計費。（ 2）對于超過整點小時數(shù)十五分鐘（包含十五分鐘）的多累積一個小時。（例如 1 小時 23 分，將以 2 小時計費）5. 使用統(tǒng)計函數(shù)，對 Sheet1 中的 “停車情況記錄表”根據(jù)下列條件進行統(tǒng)計。要求：（ 1 ）統(tǒng)計停車費用大于等于40 元的停車記錄條數(shù)，并將結果保存在J8 單元格中

26、。（ 2）統(tǒng)計最高的停車費用，并將結果保存在J9 單元格中。6. 將 Sheet1 中的 “停車情況記錄表”復制到 Sheet2 中， 對 Sheet2 進行高級篩選。要求：（1）篩選條件為：“車型”小汽車，“應付金額”>=30。（ 2）將結果保存在Sheet2 中。注意：（ 1 ）無需考慮是否刪除篩選條件。（ 2）復制過程中，將標題項“停車情況記錄表”連同數(shù)據(jù)一同復制。（3）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 中的 “停車情況記錄表”， 創(chuàng)建一個數(shù)據(jù)透視圖Chart1 。 要求：（ 1 ）顯示各種車型所收費用的匯總。（ 2） x 坐標設置為“車型”。（3）求和項為“應付金額”

27、。（ 4）將對應的數(shù)據(jù)透視表保存在Sheet3 中。、解答步驟 1 ：操作方法參見“典型試題 1”中的步驟 1。步驟2： 選中 Sheet1 工作表中的C9 單元格， 單擊插入函數(shù)按扭，打開 “插入函數(shù)”對話框，在 選擇類雙擊C9 單元格的填充柄，即可查找到其余車輛的停車單價。說明：HLOOKU函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。下 拉 列 表 框 中 選 擇,在“選擇函數(shù)”列表框 中選擇“ HLOKUP函數(shù)，單擊“確定”按鈕后，彈出“函數(shù)參數(shù)”對話框。輸入 “ Lookup_value ” 參 數(shù) 為 “ B9 ”， “ Table_ar r ay ” 參 數(shù) 為$ A $ 2搜索數(shù)組區(qū)域首行滿足條件的元素，確定待檢索單元格在區(qū)域中的列序號，再進一步 成璇定基元格的值RmJLskup邏輯值：如果為TRVE或忽暗.在第一行中查找最近似的匹配J扣 果為FAlE,查找時精確匹配計置結果=5有關覆函數(shù)的幫助® 四運 J 取消圖6-47“函數(shù)參數(shù)”對話框步輛驟索擊F9單元格的填充柄，即可計算出其余車輛的停放時間