典型試題1題目要求與操作解答參考

1、文檔供參考，可復(fù)制、編制，期待您的好評(píng)與關(guān)注！ Excel 2010高級(jí)應(yīng)用操作題典型試題1打開(kāi)素材庫(kù)中的“典型試題5-1.xlsx”文件，按下面的操作要求進(jìn)行操作，并把操作結(jié)果存盤(pán)。注意：在做題時(shí)，不得將數(shù)據(jù)表進(jìn)行隨意更改。1操作要求（1）在Sheet5的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯(cuò)誤時(shí)，提示錯(cuò)誤原因，樣式為“警告”，錯(cuò)誤信息為“只能錄入5位數(shù)字或文本”。（2）在Sheet5的B1單元格中輸入分?jǐn)?shù)1/3。（3）使用數(shù)組公式，對(duì)Sheet1中“教材訂購(gòu)情況表”的訂購(gòu)金額進(jìn)行計(jì)算。* 將結(jié)果保存在該表的“金額”列當(dāng)中。* 計(jì)算方法為：金額訂數(shù) * 單價(jià)。（4）使用統(tǒng)計(jì)函

2、數(shù)，對(duì)Sheet1中“教材訂購(gòu)情況表”的結(jié)果按以下條件進(jìn)行統(tǒng)計(jì)，并將結(jié)果保存在Sheet1中的相應(yīng)位置。要求：* 統(tǒng)計(jì)出版社名稱(chēng)為“高等教育出版社”的書(shū)的種類(lèi)數(shù),并將結(jié)果保存在Sheet1中L2單元格中。* 統(tǒng)計(jì)訂購(gòu)數(shù)量大于110且小于850的書(shū)的種類(lèi)數(shù)，并將結(jié)果保存在Sheet1中L3單元格中。（5）使用函數(shù)，計(jì)算每個(gè)用戶所訂購(gòu)圖書(shū)所需支付的金額總數(shù)，并將結(jié)果保存在Sheet1中“用戶支付情況表”的“支付總額”列中。（6）使用函數(shù)，判斷Sheet2中的年份是否為閏年，如果是，結(jié)果保存“閏年”；如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是否為閏年”列中。* 閏年定義：年數(shù)能被4整除而不能被

3、100整除，或者能被400整除的年份。（7）將Sheet1中的“教材訂購(gòu)情況表”復(fù)制到Sheet3中，對(duì)Sheet3進(jìn)行高級(jí)篩選。 要求：* 篩選條件為“訂數(shù)500，且金額30000”。* 將結(jié)果保存在Sheet3中。 注意：* 無(wú)須考慮是否刪除或移動(dòng)篩選條件。* 復(fù)制過(guò)程中，將標(biāo)題項(xiàng)“教材訂購(gòu)情況表”連同數(shù)據(jù)一同復(fù)制。* 數(shù)據(jù)表必須頂格放置。* 復(fù)制過(guò)程中，數(shù)據(jù)保持一致。（8）根據(jù)Sheet1中“教材訂購(gòu)情況表”的結(jié)果，在Sheet4中新建一張數(shù)據(jù)透視表。要求：* 顯示每個(gè)客戶在每個(gè)出版社所訂的教材數(shù)目。* 行區(qū)域設(shè)置為“出版社”。* 列區(qū)域設(shè)置為“客戶”。* 求和項(xiàng)為“訂數(shù)”。* 數(shù)據(jù)區(qū)域

4、設(shè)置為“訂數(shù)”。2解答步驟1：根據(jù)操作要求（1），選中Sheet5工作表中的A1單元格，在“數(shù)據(jù)”選項(xiàng)卡中，單擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”下拉按鈕，在打開(kāi)的下拉列表中選擇“數(shù)據(jù)有效性”選項(xiàng)，如圖 5-13所示。步驟2：在打開(kāi)的“數(shù)據(jù)有效性”對(duì)話框中，選擇“設(shè)置”選項(xiàng)卡，再選擇“允許”為“文本長(zhǎng)度”，“數(shù)據(jù)”為“等于”，在“長(zhǎng)度”文本框中輸入“5”，如圖 5-14所示。步驟3：在“出錯(cuò)警告”選項(xiàng)卡中，選擇“樣式”為“警告”，在“錯(cuò)誤信息”文本框中輸入“只能錄入5位數(shù)字或文本”，如圖 5-15所示，單擊“確定”按鈕。圖 5-13 “數(shù)據(jù)有效性”下拉列表圖 5-14 “設(shè)置”選項(xiàng)卡圖 5-15

5、 “出錯(cuò)警告”選項(xiàng)卡步驟4：根據(jù)操作要求（2），在Sheet5的B1單元格中，輸入“0 1/3”，則在B1單元格中輸入了分?jǐn)?shù)1/3。步驟5：根據(jù)操作要求（3），在Sheet1工作表中，先選中I3:I52單元格區(qū)域，再輸入公式“G3:G52*H3:H52”，然后同時(shí)按組合鍵CtrlShiftEnter，此時(shí)，公式編輯欄中顯示“G3:G52*H3:H52”。說(shuō)明：公式中單元格或單元格區(qū)域也可用鼠標(biāo)選擇輸入，下同。步驟6：根據(jù)操作要求（4），選中L2單元格，單擊編輯欄左側(cè)的“插入函數(shù)”按扭，打開(kāi)“插入函數(shù)”對(duì)話框，在“或選擇類(lèi)別”下拉列表框中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“COUNTIF”

6、函數(shù)，如圖 5-16所示。步驟7：?jiǎn)螕簟按_定”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框，輸入Range參數(shù)為“D3:D52”，Criteria參數(shù)為“高等教育出版社”，如圖 5-17所示，再單擊“確定”按鈕，即可統(tǒng)計(jì)出出版社名稱(chēng)為“高等教育出版社”的書(shū)的種類(lèi)數(shù)（6）。此時(shí)，公式編輯欄中顯示“COUNTIF(D3:D52,"高等教育出版社")”。圖 5-16 “插入函數(shù)”對(duì)話框圖 5-17 “函數(shù)參數(shù)”對(duì)話框步驟8：選中L3單元格，在公式編輯欄中輸入公式“COUNTIF(G3:G52,"110")COUNTIF(G3:G52,"850")”，再按回

7、車(chē)鍵確認(rèn)，即可統(tǒng)計(jì)出訂購(gòu)數(shù)量大于110且小于850的書(shū)的種類(lèi)數(shù)（28）。說(shuō)明：COUNTIF函數(shù)的功能和用法，請(qǐng)參見(jiàn)上一節(jié)中的相關(guān)內(nèi)容。步驟9：根據(jù)操作要求（5），選中L8單元格，單擊編輯欄左側(cè)的“插入函數(shù)”按扭，打開(kāi)“插入函數(shù)”對(duì)話框，在“或選擇類(lèi)別”下拉列表框中選擇“數(shù)學(xué)與三角函數(shù)”，在“選擇函數(shù)”列表框中選擇“SUMIF”函數(shù)。步驟10：?jiǎn)螕簟按_定”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框，輸入Range參數(shù)為“A3:A52”，Criteria參數(shù)為“c1”，Sum_range參數(shù)為“I3:I52”，如圖 5-18所示，再單擊“確定”按鈕，即可計(jì)算出c1用戶所訂購(gòu)圖書(shū)需支付的金額總數(shù)（721301）

8、。此時(shí)，公式編輯欄中顯示“SUMIF(A3:A52,"c1",I3:I52)”。圖 5-18 “函數(shù)參數(shù)”對(duì)話框步驟11：在L9單元格中輸入公式“SUMIF(A3:A52,"c2",I3:I52)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出c2用戶所訂購(gòu)圖書(shū)需支付的金額總數(shù)（53337）。步驟12：在L10單元格中輸入公式“SUMIF(A3:A52,"c3",I3:I52)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出c3用戶所訂購(gòu)圖書(shū)需支付的金額總數(shù)（65122）。步驟13：在L11單元格中輸入公式“SUMIF(A3:A52,"c4",I3:I5

9、2)”，按回車(chē)鍵確認(rèn)，即可計(jì)算出c4用戶所訂購(gòu)圖書(shū)需支付的金額總數(shù)（71253）。說(shuō)明：SUMIF函數(shù)的功能和用法，請(qǐng)參見(jiàn)上一節(jié)中的相關(guān)內(nèi)容。步驟14：根據(jù)操作要求（6），在Sheet2工作表中，選中B2單元格，在公式編輯欄中輸入公式“IF(OR(AND(MOD(A2,4)0,MOD(A2,100)0),MOD(A2,400)0),"閏年","平年")”，按回車(chē)鍵確認(rèn)，即可判斷出該年份是“閏年”還是“平年”。步驟15：雙擊B2單元格的填充柄，即可判斷出其余年份是“閏年”還是“平年”。說(shuō)明：IF、OR、AND、MOD函數(shù)的功能和用法，請(qǐng)參見(jiàn)上一節(jié)中的相關(guān)內(nèi)

10、容。步驟16：根據(jù)操作要求（7），選中Sheet1工作表中的A1:I52單元格區(qū)域，右擊鼠標(biāo)選擇“復(fù)制”命令，在Sheet3工作表的A1單元格中，右擊鼠標(biāo)選擇“粘貼”命令。步驟17：在Sheet3工作表的空白區(qū)域創(chuàng)建高級(jí)篩選條件，可在K2:L3單元格區(qū)域中輸入高級(jí)篩選條件，如圖 5-19所示。步驟18：在Sheet3工作表中，單擊A1:I52單元格區(qū)域中的任一單元格，在“數(shù)據(jù)”選項(xiàng)卡中，單擊“排序和篩選”組中的“高級(jí)”按鈕，如圖 5-20所示，打開(kāi)“高級(jí)篩選”對(duì)話框，“列表區(qū)域”文本框中已自動(dòng)填入數(shù)據(jù)清單所在的單元格區(qū)域，如圖 5-21所示。步驟19：將光標(biāo)置于“條件區(qū)域”文本框內(nèi)，用鼠標(biāo)拖選

11、前面創(chuàng)建的高級(jí)篩選條件單元格區(qū)域K2:L3，“條件區(qū)域”文本框內(nèi)會(huì)自動(dòng)填入該區(qū)域地址，如圖 5-22所示，再單擊“確定”按鈕。 圖 5-19 創(chuàng)建高級(jí)篩選條件 圖 5-20 “排序和篩選”組 圖 5-21 設(shè)置“列表區(qū)域” 圖 5-22 設(shè)置“條件區(qū)域”步驟20：根據(jù)操作要求（8），在Sheet1工作表中，單擊A1:I52單元格區(qū)域中的任一單元格，在“插入”選項(xiàng)卡中，單擊“表格”組中的“數(shù)據(jù)透視表”下拉按鈕，在打開(kāi)的下拉列表中選擇“數(shù)據(jù)透視表”選項(xiàng)，如圖 5-23所示。步驟21：在打開(kāi)的“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中，“表/區(qū)域”文本框中已經(jīng)自動(dòng)填入光標(biāo)所在的單元格區(qū)域（數(shù)據(jù)清單），如圖 5-24所示，選中“現(xiàn)有工作表”單選按鈕，將光標(biāo)置于“位置”文本框中，再單擊Sheet4工作表中的A1單元格，該單元格地址會(huì)自動(dòng)填入“位置”文本框中，如圖 5-25所示。圖 5-23 “數(shù)據(jù)透視表”下拉列表 圖 5-24 “創(chuàng)建數(shù)據(jù)透視表”對(duì)話框（1） 圖 5-25 “創(chuàng)建數(shù)據(jù)透視表”對(duì)