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

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

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

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

5、 “出錯警告”選項卡步驟4：根據操作要求（2），在Sheet5的B1單元格中，輸入“0 1/3”，則在B1單元格中輸入了分數1/3。步驟5：根據操作要求（3），在Sheet1工作表中，先選中I3:I52單元格區域，再輸入公式“G3:G52*H3:H52”，然后同時按組合鍵CtrlShiftEnter，此時，公式編輯欄中顯示“G3:G52*H3:H52”。說明：公式中單元格或單元格區域也可用鼠標選擇輸入，下同。步驟6：根據操作要求（4），選中L2單元格，單擊編輯欄左側的“插入函數”按扭，打開“插入函數”對話框，在“或選擇類別”下拉列表框中選擇“統計”，在“選擇函數”列表框中選擇“COUNTIF”

6、函數，如圖 5-16所示。步驟7：單擊“確定”按鈕，打開“函數參數”對話框，輸入Range參數為“D3:D52”，Criteria參數為“高等教育出版社”，如圖 5-17所示，再單擊“確定”按鈕，即可統計出出版社名稱為“高等教育出版社”的書的種類數（6）。此時，公式編輯欄中顯示“COUNTIF(D3:D52,"高等教育出版社")”。圖 5-16 “插入函數”對話框圖 5-17 “函數參數”對話框步驟8：選中L3單元格，在公式編輯欄中輸入公式“COUNTIF(G3:G52,"110")COUNTIF(G3:G52,"850")”，再按回

7、車鍵確認，即可統計出訂購數量大于110且小于850的書的種類數（28）。說明：COUNTIF函數的功能和用法，請參見上一節中的相關內容。步驟9：根據操作要求（5），選中L8單元格，單擊編輯欄左側的“插入函數”按扭，打開“插入函數”對話框，在“或選擇類別”下拉列表框中選擇“數學與三角函數”，在“選擇函數”列表框中選擇“SUMIF”函數。步驟10：單擊“確定”按鈕，打開“函數參數”對話框，輸入Range參數為“A3:A52”，Criteria參數為“c1”，Sum_range參數為“I3:I52”，如圖 5-18所示，再單擊“確定”按鈕，即可計算出c1用戶所訂購圖書需支付的金額總數（721301）

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

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

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

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