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

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

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

3、heet1中的“教材訂購情況表”復制到Sheet3中，對Sheet3進行高級篩選。 要求：* 篩選條件為“訂數(shù)500，且金額30000”。* 將結果保存在Sheet3中。 注意：* 無須考慮是否刪除或移動篩選條件。* 復制過程中，將標題項“教材訂購情況表”連同數(shù)據(jù)一同復制。* 數(shù)據(jù)表必須頂格放置。* 復制過程中，數(shù)據(jù)保持一致。（8）根據(jù)Sheet1中“教材訂購情況表”的結果，在Sheet4中新建一張數(shù)據(jù)透視表。要求：* 顯示每個客戶在每個出版社所訂的教材數(shù)目。* 行區(qū)域設置為“出版社”。* 列區(qū)域設置為“客戶”。* 求和項為“訂數(shù)”。* 數(shù)據(jù)區(qū)域設置為“訂數(shù)”。2解答步驟1：根據(jù)操作要求（1）

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

5、在Sheet5的B1單元格中，輸入“0 1/3”，則在B1單元格中輸入了分數(shù)1/3。步驟5：根據(jù)操作要求（3），在Sheet1工作表中，先選中I3:I52單元格區(qū)域，再輸入公式“G3:G52*H3:H52”，然后同時按組合鍵CtrlShiftEnter，此時，公式編輯欄中顯示“G3:G52*H3:H52”。說明：公式中單元格或單元格區(qū)域也可用鼠標選擇輸入，下同。步驟6：根據(jù)操作要求（4），選中L2單元格，單擊編輯欄左側的“插入函數(shù)”按扭，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中選擇“COUNTIF”函數(shù)，如圖 5-16所示。步驟7：單擊“確定”按

6、鈕，打開“函數(shù)參數(shù)”對話框，輸入Range參數(shù)為“D3:D52”，Criteria參數(shù)為“高等教育出版社”，如圖 5-17所示，再單擊“確定”按鈕，即可統(tǒng)計出出版社名稱為“高等教育出版社”的書的種類數(shù)（6）。此時，公式編輯欄中顯示“COUNTIF(D3:D52,高等教育出版社)”。圖 5-16 “插入函數(shù)”對話框圖 5-17 “函數(shù)參數(shù)”對話框步驟8：選中L3單元格，在公式編輯欄中輸入公式“COUNTIF(G3:G52,110)COUNTIF(G3:G52,850)”，再按回車鍵確認，即可統(tǒng)計出訂購數(shù)量大于110且小于850的書的種類數(shù)（28）。說明：COUNTIF函數(shù)的功能和用法，請參見上一

7、節(jié)中的相關內容。步驟9：根據(jù)操作要求（5），選中L8單元格，單擊編輯欄左側的“插入函數(shù)”按扭，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中選擇“數(shù)學與三角函數(shù)”，在“選擇函數(shù)”列表框中選擇“SUMIF”函數(shù)。步驟10：單擊“確定”按鈕，打開“函數(shù)參數(shù)”對話框，輸入Range參數(shù)為“A3:A52”，Criteria參數(shù)為“c1”，Sum_range參數(shù)為“I3:I52”，如圖 5-18所示，再單擊“確定”按鈕，即可計算出c1用戶所訂購圖書需支付的金額總數(shù)（）。此時，公式編輯欄中顯示“SUMIF(A3:A52,c1,I3:I52)”。圖 5-18 “函數(shù)參數(shù)”對話框步驟11：在L9單元格中

8、輸入公式“SUMIF(A3:A52,c2,I3:I52)”，按回車鍵確認，即可計算出c2用戶所訂購圖書需支付的金額總數(shù)（53337）。步驟12：在L10單元格中輸入公式“SUMIF(A3:A52,c3,I3:I52)”，按回車鍵確認，即可計算出c3用戶所訂購圖書需支付的金額總數(shù)（65122）。步驟13：在L11單元格中輸入公式“SUMIF(A3:A52,c4,I3:I52)”，按回車鍵確認，即可計算出c4用戶所訂購圖書需支付的金額總數(shù)（71253）。說明：SUMIF函數(shù)的功能和用法，請參見上一節(jié)中的相關內容。步驟14：根據(jù)操作要求（6），在Sheet2工作表中，選中B2單元格，在公式編輯欄中輸

9、入公式“IF(OR(AND(MOD(A2,4)0,MOD(A2,100)0),MOD(A2,400)0),閏年,平年)”，按回車鍵確認，即可判斷出該年份是“閏年”還是“平年”。步驟15：雙擊B2單元格的填充柄，即可判斷出其余年份是“閏年”還是“平年”。說明：IF、OR、AND、MOD函數(shù)的功能和用法，請參見上一節(jié)中的相關內容。步驟16：根據(jù)操作要求（7），選中Sheet1工作表中的A1:I52單元格區(qū)域，右擊鼠標選擇“復制”命令，在Sheet3工作表的A1單元格中，右擊鼠標選擇“粘貼”命令。步驟17：在Sheet3工作表的空白區(qū)域創(chuàng)建高級篩選條件，可在K2:L3單元格區(qū)域中輸入高級篩選條件，如

10、圖 5-19所示。步驟18：在Sheet3工作表中，單擊A1:I52單元格區(qū)域中的任一單元格，在“數(shù)據(jù)”選項卡中，單擊“排序和篩選”組中的“高級”按鈕，如圖 5-20所示，打開“高級篩選”對話框，“列表區(qū)域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域，如圖 5-21所示。步驟19：將光標置于“條件區(qū)域”文本框內，用鼠標拖選前面創(chuàng)建的高級篩選條件單元格區(qū)域K2:L3，“條件區(qū)域”文本框內會自動填入該區(qū)域地址，如圖 5-22所示，再單擊“確定”按鈕。 圖 5-19 創(chuàng)建高級篩選條件 圖 5-20 “排序和篩選”組 圖 5-21 設置“列表區(qū)域” 圖 5-22 設置“條件區(qū)域”步驟20：根據(jù)操作要求

11、（8），在Sheet1工作表中，單擊A1:I52單元格區(qū)域中的任一單元格，在“插入”選項卡中，單擊“表格”組中的“數(shù)據(jù)透視表”下拉按鈕，在打開的下拉列表中選擇“數(shù)據(jù)透視表”選項，如圖 5-23所示。步驟21：在打開的“創(chuàng)建數(shù)據(jù)透視表”對話框中，“表/區(qū)域”文本框中已經自動填入光標所在的單元格區(qū)域（數(shù)據(jù)清單），如圖 5-24所示，選中“現(xiàn)有工作表”單選按鈕，將光標置于“位置”文本框中，再單擊Sheet4工作表中的A1單元格，該單元格地址會自動填入“位置”文本框中，如圖 5-25所示。圖 5-23 “數(shù)據(jù)透視表”下拉列表 圖 5-24 “創(chuàng)建數(shù)據(jù)透視表”對話框（1） 圖 5-25 “創(chuàng)建數(shù)據(jù)透視表”對話框