Excel2010二級典型試題解析

1、3.1 Excel2010二級典型試題解析（*）3.2.1 衣物采購表1. 在Sheet5中，使用函數(shù)，將A1單元格中的數(shù)四舍五入到整百，存放在B1單元格中。2. 在Sheet1中，使用條件格式將“采購數(shù)量”列中數(shù)量大于100的單元格中字體設(shè)置為紅色，加粗顯示。3. 使用VLOOKUP函數(shù)，對Sheet1中的“采購表”的“單價”列進(jìn)行填充。* 根據(jù)“價格表”中的商品單價，使用VLOOKUP函數(shù)，將其單價填充到采購表中的“單價”列中。* 函數(shù)中參數(shù)如果需要用到絕對地址的，請使用絕對地址進(jìn)行答題，其他方式無效。4. 使用邏輯函數(shù)，對Sheet1“采購表”中的“折扣”列進(jìn)行填充。要求：* 根據(jù)“折扣

2、表”中的商品折扣率，使用相應(yīng)的函數(shù)，將其折扣率填充到采購表中的“折扣”列中。5. 使用公式，對Sheet1中“采購表”的“合計”列進(jìn)行填充。* 根據(jù)“采購數(shù)量”，“單價”和“折扣”，計算采購的合計金額，將結(jié)果保存在“合計”列中。* 計算公式：單價*采購數(shù)量*（1折扣率）6. 使用SUMIF函數(shù)，計算各種商品的采購數(shù)量和采購總金額，將結(jié)果保存在Sheet1 “統(tǒng)計表”當(dāng)中相應(yīng)位置。7. 將Sheet1中的“采購表”復(fù)制到Sheet2，并對Sheet2進(jìn)行高級篩選。（1）要求：*篩選條件為：“采購數(shù)量”>150，“折扣率”>0；*將篩選結(jié)果保存在Sheet2中。（2）注意：*無須考慮是

3、否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項“采購表”連同數(shù)據(jù)一同復(fù)制；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置；*復(fù)制過程中，保持?jǐn)?shù)據(jù)一致。8. 根據(jù)Sheet1中“采購表”，新建一個數(shù)據(jù)透視圖，保存在Sheet3中。要求：*該圖形顯示每個采購時間點所采購的所有項目數(shù)量匯總的情況；*x坐標(biāo)設(shè)置為“采購時間”；*求和項為“采購數(shù)量”；*將對相應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。l 操作步驟如下：1. 步驟1：單擊選擇Sheet5的B1單元格，在“開始”選項卡中，單擊編輯欄中的【插入函數(shù)】按鈕，打開“插入函數(shù)”函數(shù)對話框，選擇“全部”中的“ROUND”函數(shù)，如圖 31所示。（注意：在已知函數(shù)具

4、體名的情況下，可以在“搜索函數(shù)”文本框中輸入函數(shù)名，然后單擊【轉(zhuǎn)到】按鈕，即可快速找到函數(shù)。）圖 Error! No text of specified style in document.1插入ROUND函數(shù)步驟2：單擊【確定】按鈕，打開“函數(shù)參數(shù)”對話框并設(shè)置參數(shù)，如圖 32所示。圖 Error! No text of specified style in document.2 ROUND函數(shù)參數(shù)對話框步驟3：單擊【確定】按鈕，完成設(shè)置。2. 步驟1：選中Sheet1的“采購數(shù)量”列的數(shù)據(jù)區(qū)域。切換到“開始”選項卡，單擊“樣式”選項組的【條件格式】按鈕。步驟2：從彈出的菜單中選擇“突出顯示

5、單元格規(guī)則”下的“大于”命令（如圖 33所示），打開“大于”對話框：輸入條件“100”；單擊“設(shè)置為”文本框右側(cè)的下拉箭頭，從彈出的子菜單里面選擇“自定義格式”（如圖 34所示），打開“設(shè)置單元格格式”對話框。圖 Error! No text of specified style in document.3 “條件格式”菜單圖 Error! No text of specified style in document.4 設(shè)置“大于”對話框步驟3：在“設(shè)置單元格格式”對話框中，切換到“字體”選項卡，選擇字形和顏色，如圖 35所示。圖 Error! No text of specified st

6、yle in document.5“設(shè)置單元格格式”對話框步驟4：單擊【確定】按鈕返回“大于”對話框，再單擊【確定】按鈕完成設(shè)置。3. 選中D11單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇函數(shù)VLOOKUP。單擊【確定】按鈕打開VLOOKUP函數(shù)參數(shù)對話框，在相應(yīng)的文本框中輸入如圖 36所示的參數(shù)（注意：Table_Arrray區(qū)域的絕對引用?？稍谕线x選區(qū)后直接按F4功能鍵快速實現(xiàn)輸入）。單擊【確定】按鈕。雙擊D11單元格的填充柄填充該列的數(shù)據(jù)。圖 Error! No text of specified style in document.6 VLOOKUP函數(shù)參數(shù)對話框

7、4. 根據(jù)題意分析IF嵌套函數(shù)，畫出其流程圖如圖 37所示。 NNYYNY折扣率為B3單元格所示的（絕對引用）B11<100B11<200折扣率為B4單元格所示的（絕對引用）折扣率為B5單元格所示的（絕對引用）B11<300折扣率為B6單元格所示的（絕對引用）圖 Error! No text of specified style in document.7 IF嵌套函數(shù)流程圖據(jù)此，則在E11單元格中輸入公式：“=IF(B11<100,$B$3,IF(B11<200,$B$4,IF(B11<300,$B$5,$B$6)”。按回車確認(rèn)。雙擊E11單元格的填充柄填

8、充該列的數(shù)據(jù)。（注意：如果數(shù)據(jù)格式不對，單擊“開始”選項卡的“數(shù)字選項組”中的【百分比】按鈕即可。）5. 在F11單元格中輸入公式“=B11*D11*(1-E11)“，按回車確認(rèn)，雙擊F11單元格填充柄。（注意：如果單元格中出現(xiàn)“#”，表示該列寬度不足，無法顯示數(shù)據(jù)。只需加寬該列即可）6. 計算采購數(shù)量：單擊J12單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇SUMIF函數(shù)，單擊“確定”打開SUMIF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 38所示的參數(shù)(注意絕對引用和相對引用的使用)。雙擊J12單元格的填充柄。圖 Error! No text of specified

9、style in document.8 SUMIF函數(shù)參數(shù)對話框計算采購總金額：同理，在K12單元格中插入函數(shù)，設(shè)置SUMIF函數(shù)參數(shù)如圖 39所示，單擊【確定】按鈕。雙擊K12單元格的填充柄。圖 Error! No text of specified style in document.9 SUMIF函數(shù)參數(shù)對話框7. 步驟1：復(fù)制表格。選擇Sheet1的采購表，按下“Ctrl+C”；將光標(biāo)定位在Sheet2的A1單元格，按下“Ctrl+v”（注意：此時復(fù)制的數(shù)據(jù)會出現(xiàn)一個出錯信息，如圖 310所示）。單擊【確定】按鈕。（注意：由于包含了公式的循環(huán)引用，此時復(fù)制的數(shù)據(jù)有錯誤。）圖 Error

10、! No text of specified style in document.10“循環(huán)引用警告”對話框選擇Sheet1采購表中的計算過的數(shù)據(jù)區(qū)域（即“單價”、“折扣”、“合計”列下方的數(shù)據(jù)區(qū)域），按下“Ctrl+C”；將光標(biāo)定位在Sheet2的錯誤的數(shù)據(jù)區(qū)域的起始單元格（即“單價”下方的單元格），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“選擇性粘貼值和數(shù)字格式”（如圖 311所示）。圖 Error! No text of specified style in document.11選擇性粘貼（注意：當(dāng)某單元格出現(xiàn)“”時，表示單元格寬度不夠，此時可調(diào)整單元格的寬度以看到完整的單元格數(shù)據(jù)）步驟2

11、：建立高級篩選的條件區(qū)域。在數(shù)據(jù)區(qū)域的下方，根據(jù)題目要求建立條件區(qū)域，如圖 312所示。（建議直接復(fù)制表格中的相關(guān)字段）。圖 Error! No text of specified style in document.12高級篩選的條件區(qū)域步驟3：高級篩選。單擊數(shù)據(jù)區(qū)域中的任一單元格，然后切換功能區(qū)的“數(shù)據(jù)”選項卡，在“排序和篩選”選項組中單擊【高級】按鈕，打開“高級篩選”對話框。此時，“列表區(qū)域”的文本框中已自動填入所有數(shù)據(jù)區(qū)域。再把光標(biāo)定位在“條件區(qū)域”文本框內(nèi)，拖動鼠標(biāo)選中條件區(qū)域（如圖 313所示），單擊【確定】按鈕完成設(shè)置。圖 Error! No text of specified

12、style in document.13選擇條件區(qū)域8. 步驟1：將光標(biāo)定位在Sheet1工作表數(shù)據(jù)區(qū)域中的任一單元格，切換到功能區(qū)中的“插入”選項卡，在“表格”選項組中單擊【數(shù)據(jù)透視表】下拉箭頭，在彈出的菜單中選擇“數(shù)據(jù)透視圖”命令（如圖 314所示），打開“創(chuàng)建數(shù)據(jù)透視表及數(shù)據(jù)透視圖”對話框。圖 Error! No text of specified style in document.14選擇“數(shù)據(jù)透視表”菜單圖 Error! No text of specified style in document.15 “創(chuàng)建數(shù)據(jù)透視表及數(shù)據(jù)透視圖”對話框此時，在“選擇一個表或區(qū)域”單選按鈕下方的

13、“表/區(qū)域”文本框中自動填入了表格的數(shù)據(jù)區(qū)域，如圖 Error! No text of specified style in document.15上方所示。步驟2：選擇“現(xiàn)有工作表”單選按鈕，將光標(biāo)定位在“位置”右側(cè)的文本框中，單擊Sheet3工作表標(biāo)簽切換到Sheet3工作表，并單擊A1單元格，如圖 Error! No text of specified style in document.15下方所示。步驟3：單擊【確定】按鈕，進(jìn)入數(shù)據(jù)透視表及數(shù)據(jù)透視圖設(shè)計環(huán)境：從“選擇要添加到報表的字段”列表框中，將“采購時間”拖到“軸字段”框中；將“采購數(shù)量”拖到“數(shù)值”框中，如圖 316所示。最后

14、的效果圖見圖 317。圖 Error! No text of specified style in document.16設(shè)置數(shù)據(jù)透視圖（表）字段列表圖 Error! No text of specified style in document.17數(shù)據(jù)透視圖（表）效果圖3.2.2 教材訂購情況表1. 在Sheet5的A1單元格中設(shè)置只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 在Sheet5的B1單元格中輸入分?jǐn)?shù)1/3。3. 使用數(shù)組公式，對Sheet1中“教材訂購情況表”的訂購金額進(jìn)行計算。*將結(jié)果保存在該表的“金額”列

15、當(dāng)中。*計算方法：金額訂數(shù)*單價。4. 使用統(tǒng)計函數(shù)，對Sheet1中“教材訂購情況表”的結(jié)果按以下條件進(jìn)行統(tǒng)計，并將結(jié)果保存在Sheet1中的相應(yīng)位置。要求：*統(tǒng)計出版社名稱為“高等教育出版社”的書的種類數(shù)，并將結(jié)果保存在Sheet1中的L2單元格中。*統(tǒng)計訂購數(shù)量大于110且小于850的書的種類數(shù)，并將結(jié)果保存在Sheet1中的L3單元格中。5. 使用函數(shù)，計算每個用戶所訂購圖書所需支付的金額總數(shù)，并將結(jié)果保存在Sheet1中的“用戶支付情況表”的“支付金額”列中。6. 使用函數(shù)，判斷Sheet2中的年份是否為閏年，如果是，結(jié)果保存“閏年”；如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是

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

17、社”；*列區(qū)域設(shè)置為“客戶”；*求和項為訂數(shù)；*數(shù)據(jù)區(qū)域設(shè)置為“訂數(shù)”。l 操作步驟如下：1. 步驟1：選中Sheet5工作表中的A1單元格，切換到功能區(qū)的“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”選項組中的“數(shù)據(jù)有效性”的上半部按鈕，打開“數(shù)據(jù)有效性”對話框。步驟2：切換到“設(shè)置”選項卡：選擇“允許”下拉菜單為“文本長度”；選擇“數(shù)據(jù)”下拉菜單為“等于”；并在“長度”文本框中輸入“5”，如圖 318所示。圖 Error! No text of specified style in document.18設(shè)置數(shù)據(jù)有效性步驟3：再切換到“出錯警告”選項卡：選擇“樣式”下拉菜單為“警告”；在“錯誤信息”文本

18、框中輸入“只能錄入5位數(shù)字或文本”，如圖 319所示，單擊【確定】按鈕完成設(shè)置。圖 Error! No text of specified style in document.19設(shè)置出錯信息2. 在Sheet5的B1單元格中，輸入“0 1/3”（注意：0和1中間以空格間隔）。3. 在Sheet1工作表中，先選中“金額”列的數(shù)據(jù)區(qū)域，再輸入公式“=G3:G52*H3:H52”（注意：各列數(shù)據(jù)區(qū)域建議用鼠標(biāo)拖拽選?。?，然后按下“Ctrl+Shift+Enter”組合鍵即可。4. 步驟1：選中L2單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“統(tǒng)計”函數(shù)中的COUNTIF函數(shù)，單

19、擊【確定】按鈕打開COUNTIF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 320所示的參數(shù)（注意：Range文本框中的單元格區(qū)域可拖拽鼠標(biāo)進(jìn)行輸入；Criteria文本框中的文本可直接單擊“高等教育出版社”所在的單元格進(jìn)行輸入），單擊【確定】按鈕。圖 Error! No text of specified style in document.20 COUNTIF函數(shù)參數(shù)對話框步驟2：選中L2單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“統(tǒng)計”函數(shù)中的COUNTIFS函數(shù)，單擊“確定”打開COUNTISF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 321所示的參數(shù)（注意：C

20、riteria_Range各文本框中的單元格區(qū)域可拖拽鼠標(biāo)進(jìn)行輸入），單擊【確定】按鈕。圖 Error! No text of specified style in document.21 COUNTIFS函數(shù)參數(shù)對話框5. 選中L8單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“數(shù)學(xué)與三角函數(shù)”函數(shù)中的SUMIF函數(shù)，單擊【確定】按鈕打開SUMIF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 322所示的參數(shù)（注意：絕對引用可在鼠標(biāo)拖選相應(yīng)的單元格區(qū)域后，按F4功能鍵快速實現(xiàn)輸入）。雙擊L8單元格的填充柄。圖 Error! No text of specified style

21、 in document.22SUMIF函數(shù)參數(shù)對話框6. 步驟1：根據(jù)題意，閏年條件分析如Error! Reference source not found.23所示。兩個條件滿足其中之一OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0), MOD(A2,400)=0)兩個條件同時滿足AND(MOD(A2,4)=0, MOD(A2,100)<>0)能被400整除MOD(A2,400)=0能被4整除MOD(A2,4)=0不能被100整除MOD(A2,100)<>0圖 323閏年條件分析步驟2：選中Sheet2工作表的B2單元格，單擊編輯欄

22、上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“邏輯”函數(shù)中的IF函數(shù)，單擊【確定】按鈕打開IF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 323所示的參數(shù)（Logical_test文本框中的參數(shù)為“OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0), MOD(A2,400)=0)”）。雙擊B2單元格的填充柄。圖 Error! No text of specified style in document.23 IF函數(shù)參數(shù)對話框7. 參考中的題7。8. 可參考中的題8。步驟1：將光標(biāo)定位在Sheet1數(shù)據(jù)區(qū)域的任一單元格，切換到功能區(qū)中的“插入”選項卡，在“表格

23、”選項組中單擊【數(shù)據(jù)透視表】下拉箭頭，在彈出的菜單中選擇“數(shù)據(jù)透視表”命令（如圖 324所示），打開打開“創(chuàng)建數(shù)據(jù)透視表”對話框。圖 Error! No text of specified style in document.24 “數(shù)據(jù)透視表”菜單此時，在“選擇一個表或區(qū)域”單選按鈕下方的“表/區(qū)域”文本框中自動填入了表格的數(shù)據(jù)區(qū)域。步驟2：選擇“現(xiàn)有工作表”單選按鈕，將光標(biāo)定位在“位置”右側(cè)的文本框中，單擊Shee4工作表標(biāo)簽切換到Sheet4工作表，并單擊A1單元格。步驟3：單擊【確定】按鈕，進(jìn)入數(shù)據(jù)透視表設(shè)計環(huán)境：從“選擇要添加到報表的字段”列表框中，將“出版社”拖到“行標(biāo)簽”框中；將

24、“客戶”拖到“列標(biāo)簽”框中；將“定數(shù)”拖到“數(shù)值”框中，如圖 325所示。最后的效果圖見圖 326。圖 Error! No text of specified style in document.25設(shè)置數(shù)據(jù)透視表字段列表圖 Error! No text of specified style in document.26數(shù)據(jù)透視表效果圖3.2.3 公務(wù)員考試成績表1. 在Sheet5的A1單元格中輸入分?jǐn)?shù)1/3。2. 在Sheet1中，使用條件格式將“性別”列中為“女”的單元格中字體顏色設(shè)置為紅色、加粗顯示。3. 使用IF函數(shù)，對Sheet1中“學(xué)位”列進(jìn)行自動填充。要求：填充的內(nèi)容根據(jù)“學(xué)歷

25、”列的內(nèi)容來確定（假定學(xué)生均已獲得相應(yīng)學(xué)位）*博士研究生博士*碩士研究生碩士*本科學(xué)士*其他無。4. 使用數(shù)組公式，在Sheet1中計算：計算筆試比例分，并將結(jié)果保存在“公務(wù)員考試成績表”中的“筆試比例分”中。*計算方法為：筆試比例分（筆試成績/3）*60%計算面試比例分，并將結(jié)果保存在“公務(wù)員考試成績表”中的“面試比例分”中。*計算方法為：面試比例分面試成績*40%計算總成績，并將結(jié)果保存在“公務(wù)員考試成績表”中的“總成績”中。* 計算方法：總成績筆試比例分+面試比例分5. 將Sheet1中的“公務(wù)員考試成績表”復(fù)制到Sheet2，根據(jù)以下要求修改“公務(wù)員考試成績表”中的數(shù)組公式，并將結(jié)果保

26、存在Sheet2的相應(yīng)列中。要求：*修改“筆試比例分”的計算，計算方法為：筆試比例分（筆試成績/2）*60%，并將結(jié)果保存在“筆試成績比例分”列中。注意：*復(fù)制過程中，將標(biāo)題項“公務(wù)員考試成績表”連同數(shù)據(jù)一同復(fù)制；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。6. 在Sheet2中，使用函數(shù)，根據(jù)“總成績”列對所有考生進(jìn)行排名。（如果多個數(shù)值排名相同，則返回該組數(shù)值的最佳排名）*要求：將排名結(jié)果保存在“排名”列中。7. 將Sheet2中的“公務(wù)員考試成績表”復(fù)制到Sheet3，并對Sheet3進(jìn)行高級篩選。（1）要求：*篩選條件為：“報考單位” 一中院、“性別”男、“學(xué)歷”碩士研究生；*將篩選結(jié)

27、果保存在Sheet3中。（2）注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項“公務(wù)員考試成績表”連同數(shù)據(jù)一同復(fù)制；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet2中的“公務(wù)員考試成績表”，在Sheet4中創(chuàng)建一張數(shù)據(jù)透視表。要求：*顯示每個報考單位的人的不同學(xué)歷的人數(shù)匯總情況；*行區(qū)域設(shè)置為“報考單位”；*列區(qū)域設(shè)置為“學(xué)歷”；*數(shù)據(jù)區(qū)域設(shè)置為“學(xué)歷”；*計數(shù)項為學(xué)歷。l 操作步驟如下：1. 參考中的題2。2. 參考中的題2。3. 步驟1：根據(jù)題意，分析IF嵌套函數(shù)，畫出其流程圖如圖 327所示。 NNYYNY博士G3=”博研”生”<100G3=”碩研”

28、碩士學(xué)士G3=”本科”無圖 Error! No text of specified style in document.27 IF嵌套函數(shù)流程圖步驟2：根據(jù)以上流程圖，在H3單元格中輸入公式：“=IF(G3="博士研究生","博士",IF(G3="碩士研究生","碩士",IF(G3="本科","學(xué)士","無")”（注意：務(wù)必在英文輸入法狀態(tài)下輸入字符，僅中文字符例外；且括號要配對）。按回車確認(rèn)。雙擊H3單元格的填充柄填充該列的數(shù)據(jù)。4. 選中“筆試比例分”

29、列的數(shù)據(jù)區(qū)域，輸入公式“=I3:I18/3*0.6”，（各列數(shù)據(jù)區(qū)域可用鼠標(biāo)拖拽選?。缓蟀聪翪trl+Shift+Enter組合鍵。同理，選中“面試比例分”列的數(shù)據(jù)區(qū)域，輸入公式“=I3:I18*0.4”，同上。 同理，選中“總成績”列的數(shù)據(jù)區(qū)域，輸入公式“=J3:J18+L3:L18”，同上。5. 步驟1：選中Sheet1工作表中的數(shù)據(jù)區(qū)域，復(fù)制；單擊Sheet2工作表的A1單元格，右鍵單擊選擇“粘貼選項”的第一項。步驟2：在“筆試成績比例分”中修改公式“=I3:I18/2*0.6”，按下Ctrl+Shift+Enter組合鍵。6. 選中Sheet2工作表的N3單元格，單擊編輯欄上的【插

30、入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇RANK.EQ函數(shù)，單擊【確定】按鈕打開RANK.EQ函數(shù)參數(shù)對話框：在相應(yīng)的文本框中輸入如圖 328所示的參數(shù)（注意使用絕對引用，用鼠標(biāo)拖選選區(qū)后，直接按下F4功能鍵快速實現(xiàn)輸入）。按回車確認(rèn)。雙擊N3單元格的填充柄填充該列的數(shù)據(jù)。圖 Error! No text of specified style in document.28 RANK.EQ函數(shù)參數(shù)對話框7. 參考中的題7。8. 參考中的題8。3.2.4 杭州電話用戶情況表1. 在Sheet5的A1單元格中設(shè)置只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄

31、入5位數(shù)字或文本”。2. 在Sheet5的B1單元格中輸入公式，判斷當(dāng)年是否為閏年，結(jié)果為TRUE或FALSE。*閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。3. 使用時間函數(shù)，對Sheet1中用戶的年齡進(jìn)行計算。要求：*假設(shè)當(dāng)前時間是“2013-5-1”，結(jié)合用戶的出生年月，計算用戶的年齡，并將其計算結(jié)果保存在“年齡”列當(dāng)中。計算方法為兩個時間年份之差。4. 使用REPLACE函數(shù)，對Sheet1中用戶的電話號碼進(jìn)行升級。要求：*對“原電話號碼”列中的電話號碼進(jìn)行升級。升級方式是在區(qū)號（0571）后面加上“8”，并將其計算結(jié)果保存在“升級電話號碼”列的相應(yīng)單元格中。

32、*例如：電話號碼“”升級后為“057186742808”。5. 在Sheet1中，使用AND函數(shù)，根據(jù)“性別”及“年齡”列中的數(shù)據(jù)，判斷所有用戶是否為大于等于40歲的男性，并將結(jié)果保存在“是否>=40男性”列中。*注意：如果是，保存結(jié)果為TRUE；否則，保存結(jié)果為FALSE。6. 根據(jù)Sheet1中的數(shù)據(jù)，對以下條件，使用統(tǒng)計函數(shù)進(jìn)行統(tǒng)計。要求： *統(tǒng)計性別為“男”的用戶人數(shù)，將結(jié)果填入到Sheet2的B2單元格中。*統(tǒng)計年齡為“>40”歲的用戶人數(shù)，將結(jié)果填入到Sheet2的B3單元格中。7. 將Sheet1復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選。（1）要求：*篩選條

33、件為：“性別”女，“所在區(qū)域”西湖區(qū)；*將篩選結(jié)果保存在Sheet3中。（2）注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1的結(jié)果，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet4中。要求：*顯示每個區(qū)域所擁有的用戶數(shù)量；*x坐標(biāo)設(shè)置為“所在區(qū)域”；*計數(shù)項為“所在區(qū)域”；*將對應(yīng)的數(shù)據(jù)透視表保存在Sheet4中。l 操作步驟如下：1. 參考中的題1。2. 參考中的題6。此處輸入公式“=OR(AND(MOD(YEAR(NOW(),4)=0, MOD(YEAR(NOW(),100)<>0), MOD(YEAR(NOW(),400)=0

34、)”。（注意：NOW函數(shù)無參數(shù)，只有括號）3. 步驟1：單擊Sheet的D2單元格，插入時間函數(shù)YEAR，在打開的YEAR函數(shù)參數(shù)對話框中，輸入如圖 329所示的參數(shù)，得到年份2013。圖 Error! No text of specified style in document.29 YEAR函數(shù)參數(shù)對話框(1)步驟2：再在該公式后面減去如所圖 330示的計算所得的年份，按回車。（此時公式顯示為“=YEAR("2013-5-1")-YEAR(C2)”）。按回車，再填充公式。圖 Error! No text of specified style in document.30

35、 YEAR函數(shù)參數(shù)對話框(2)4. 選擇Sheet的G2單元格，插入REPLACE函數(shù)，在打開的REPLACE函數(shù)參數(shù)對話框中輸入如圖 331所示的參數(shù)，單擊【確定】按鈕。雙擊G2單元格的填充柄填充該列的數(shù)據(jù)。圖 Error! No text of specified style in document.31 REPLACE函數(shù)參數(shù)對話框5. 選擇Sheet1的H2單元格，插入AND函數(shù)，在打開的AND函數(shù)參數(shù)對話框中輸入如圖 332所示的參數(shù)，單擊【確定】按鈕。雙擊H2單元格的填充柄填充該列的數(shù)據(jù)。圖 Error! No text of specified style in document

36、.32 AND函數(shù)參數(shù)對話框6. 參考中的題4。7. 參考中的題7。8. 參考中的題8。3.2.5 停車情況記錄表1. 在Sheet4的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 在Sheet4的B1單元格中輸入公式，判斷當(dāng)前年份是否為閏年，結(jié)果為TURE或FALSE.* 閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。3. 使用HLOOKUP函數(shù)，對Sheet1“停車情況記錄表”中的“單價”列進(jìn)行填充。要求：*根據(jù)Sheet1中的“停車價目表”價格，使用HLOOKUP函數(shù)對“停車情

37、況記錄表”中的“單價”列根據(jù)不同的車型進(jìn)行填充。注意*函數(shù)中如果需要用到絕對地址的請使用絕對地址進(jìn)行計算，其他方式無效。4. 在Sheet1中，使用時間函數(shù)計算汽車在停車庫中的停放時間。要求：*計算方法為：“停放時間出庫時間入庫時間”*格式為：“小時：分鐘：秒”*將結(jié)果保存在“停車情況記錄表”中的“停放時間”列中*例如：一小時十五分十二秒在停放時間中的表示為：“1:15:12”。5. 使用函數(shù)公式，對“停車情況記錄表”中的停車費用進(jìn)行計算。要求：*根據(jù)Sheet1停放時間的長短計算停車費用，將計算結(jié)果填入到“停車情況記錄表”中的“應(yīng)付金額”列中。注意：*停車按小時收費，對于不滿一個小時的按照一

38、個小時計費；*對于超過整點小時數(shù)十五分鐘（包含十五分鐘）的，多累積一個小時；*例如：1小時23分，將以2小時計費。6. 使用統(tǒng)計函數(shù)，對Sheet1中的“停車情況記錄表”根據(jù)下列條件進(jìn)行統(tǒng)計。*統(tǒng)計停車費用大于等于40元的停車記錄條數(shù)，并將結(jié)果保存在J8單元格中；*統(tǒng)計最高的停車費用，并將結(jié)果保存在J9單元格中。7. 將Sheet1中的“停車情況記錄表”復(fù)制到Sheet2中，對Sheet2進(jìn)行高級篩選。要求：*篩選條件為：“車型”小汽車，“應(yīng)付金額”>=30；*將結(jié)果保存在Sheet2中。注意：*無須考慮是否刪除篩選條件；*復(fù)制過程中，將標(biāo)題項“停車情況記錄表”連同數(shù)據(jù)一同復(fù)制；*復(fù)制數(shù)

39、據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中的“停車情況記錄表”，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet3中。要求：*顯示各種車型所收費用的匯總；*x坐標(biāo)設(shè)置為“車型”；*求和項為“應(yīng)付金額”；*將對相應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。l 操作步驟如下：1. 參考中的題1。2. 參考中的題2。3. 單擊Sheet1工作表的C9單元格，插入HLOOKUP函數(shù)，在HLOOKUP函數(shù)對話框中輸入如圖 333所示的參數(shù)（注意Table_Arrray區(qū)域的絕對引用，可拖選選區(qū)后直接按F4功能鍵實現(xiàn)快速輸入）。單擊【確定】按鈕。雙擊C9單元格的填充柄填充該列的數(shù)據(jù)。圖 Error! No

40、text of specified style in document.33 HLOOKUP函數(shù)對話框4. 單擊F9單元格，輸入公式“=E9-D9”，按回車確認(rèn)。雙擊F9單元格的填充柄。5. 步驟1：根據(jù)題意，分析IF嵌套函數(shù)，畫出其流程圖如圖 334所示。 NYNY1HOUR(F9)=0HOUR(F9)+1HOUR(F9)MINUTE(F9)>=155圖 Error! No text of specified style in document.34 IF嵌套函數(shù)流程圖步驟2：據(jù)根據(jù)以上分析，在G9單元格輸入公式：“=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=

41、15,HOUR(F9)+1,HOUR(F9)”，再乘以C9。按回車確認(rèn)，雙擊G9單元格的填充柄填充該列的數(shù)據(jù)。6. 步驟1：參考中的題4。步驟2：單擊J9單元格，插入MAX函數(shù)，在MAX函數(shù)參數(shù)對話框中輸入如圖 335所示的參數(shù)，單擊【確定】按鈕。圖 Error! No text of specified style in document.35MAX函數(shù)參數(shù)對話框7. 參考中的題7。（注意：復(fù)制“三月份銷售統(tǒng)計表”的時候注意選擇使用“粘貼選項”中的“值”。）8. 參考中的題8。3.2.6 語數(shù)英成績表1. 在Sheet5中使用多個函數(shù)組合，計算A1:A10中奇數(shù)的個數(shù)，結(jié)果存放在B1單元格中

42、。2. 在Sheet1中，使用條件格式將“語文”列中數(shù)據(jù)大于80的單元格中，字體顏色設(shè)置為紅色、加粗顯示。3. 使用數(shù)組公式，根據(jù)Sheet1中的數(shù)據(jù)，計算總分和平均分，將其計算結(jié)果保存到表中的“總分”列和“平均分”列當(dāng)中。4. 使用函數(shù)，根據(jù)Sheet1中的“總分”列，對每個同學(xué)排名情況進(jìn)行統(tǒng)計，并將結(jié)果保存到表中的“排名”列當(dāng)中。（若有相同排名，返回最佳排名）5. 使用邏輯函數(shù)，判斷Sheet1中每個同學(xué)的每門功課是否均高于全班單科平均分。要求*如果是，保存結(jié)果為TRUE；否則，保存結(jié)果為FALSE；*將結(jié)果保存在表中的“優(yōu)等生”列當(dāng)中；注意*優(yōu)等生條件：每門功課均高于全班單科平均分。6.

43、 根據(jù)Sheet1中的結(jié)果，使用統(tǒng)計函數(shù)，統(tǒng)計“數(shù)學(xué)”考試成績各個分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計結(jié)果保存到Sheet2的相應(yīng)位置。7. 將Sheet1復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選。 要求：*篩選條件為：“語文”>=75，“數(shù)學(xué)”>=75，“英語”>=75，“總分”>=250；*將結(jié)果保存在Sheet3中。注意：*無須考慮是否刪除篩選條件；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中的結(jié)果，在Sheet4中創(chuàng)建一張數(shù)據(jù)透視表。要求：*顯示是否為優(yōu)等生的學(xué)生人數(shù)匯總情況；*行區(qū)域設(shè)置為“優(yōu)等生”；*數(shù)據(jù)區(qū)域設(shè)置為“優(yōu)等生”；*計數(shù)項為

44、優(yōu)等生。l 操作步驟如下：1. 單擊選擇Sheet5中的B1單元格，輸入公式：“=SUMPRODUCT(MOD(A1:A10,2)”。2. 參考中的題2。3. 步驟1：選中“總分”列的數(shù)據(jù)區(qū)域，輸入公式“=C2:C39+D2:D39+E2:E39”（各列數(shù)據(jù)區(qū)域可用鼠標(biāo)拖拽選?。缓蟀聪翪trl+Shift+Enter組合鍵。步驟2：選中“平均分”列的數(shù)據(jù)區(qū)域，輸入公式“=F2:F39/3”（各列數(shù)據(jù)區(qū)域可用鼠標(biāo)拖拽選?。?，然后按下Ctrl+Shift+Enter組合鍵。4. 參考中的題6。5. 步驟1：根據(jù)題意，條件分析如圖 336所示（注意求各科平均分時相應(yīng)單元格區(qū)域的絕對引用，注意括號

45、的配對）。三個條件同時滿足AND(C2>AVERAGE($C$2:$C$39), D2>AVERAGE($D$2:$D$39), E2>AVERAGE($E$2:$E$39)語文大于語文平均分C2>AVERAGE($C$2:$C$39)數(shù)學(xué)大于數(shù)學(xué)平均分D2>AVERAGE($D$2:$D$39)英語大于英語平均分E2>AVERAGE($E$2:$E$39)圖 Error! No text of specified style in document.36條件分析步驟2：根據(jù)以上分析，在I2單元格中插入邏輯函數(shù)AND，在AND函數(shù)參數(shù)對話框中輸入圖 337中

46、所示的3個條件。單擊【確定】按鈕。雙擊I2單元格的填充柄。圖 Error! No text of specified style in document.37 AND函數(shù)參數(shù)對話框6. 單擊Sheet2的B2單元格，插入COUNTIFS函數(shù)，在打開的COUNTIFS函數(shù)參數(shù)對話框中輸入如圖 338所示的參數(shù)（注意使用絕對引用）。圖 Error! No text of specified style in document.38 COUNTIFS函數(shù)參數(shù)對話框其余同理，只需按題意更改Criteiral中的條件即可。（為簡單起見，可復(fù)制公式后直接修改條件）。7. 參考中的題7。8. 參考中的題8。

47、房產(chǎn)銷售表1. 在Sheet5的A1單元格中設(shè)置只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 在Sheet1中，使用條件格式將“預(yù)訂時期”列中日期為2008-4-1后的單元格中字體顏色設(shè)置為紅色、加粗顯示。對C列，設(shè)置“自動調(diào)整列寬”。3. 使用公式，計算Sheet1中“房產(chǎn)銷售表”的房價總額，并保存在“房產(chǎn)總額”列中。* 計算公式為：房產(chǎn)總額面積*單價。4. 使用數(shù)組公式，計算Sheet1中“房產(chǎn)銷售表”的契稅總額，并保存在“契稅總額”列中。* 計算公式為：契稅總額契稅*房產(chǎn)總額。5. 使用函數(shù)，根據(jù)Sheet1中“房產(chǎn)銷

48、售表”的結(jié)果，在Sheet2中統(tǒng)計每個銷售人員的銷售總額，將結(jié)果保存在Sheet2的“銷售總額”列中。6. 使用函數(shù)，根據(jù)Sheet2中“銷售總額”列的結(jié)果，對每個銷售人員的銷售情況進(jìn)行排序，并將結(jié)果保存在“排名”列當(dāng)中。（若有相同排名，返回最佳排名）7. 將Sheet1的“房產(chǎn)銷售表”復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選。要求：*篩選條件為：“戶型”為兩室一廳，“房價總額”>1000000；*將篩選結(jié)果保存在Sheet3中。注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項“房產(chǎn)銷售表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中“房產(chǎn)銷售

49、表”的結(jié)果，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet4中。要求：*顯示每個銷售人員所銷售房屋應(yīng)繳納契稅總額匯總情況；*x坐標(biāo)設(shè)置為“銷售人員”；*數(shù)據(jù)區(qū)域為“契稅總額”；*求和項設(shè)置為契稅總額；*將對應(yīng)的數(shù)據(jù)透視表也保存在Sheet4中。l 操作步驟如下：1. 參考中的題1。2. 步驟1：參考中的題2。步驟2：選中C列，切換到“開始選項卡”，單擊“單元格”選項組中的【格式】按鈕，在彈出的下拉菜單中選擇“自動調(diào)整列寬”，如圖 339所示。圖 Error! No text of specified style in document.39自動調(diào)整列寬3. 在“房產(chǎn)總額”列的I3單元格中，輸入公式“=F

50、3*G3”，按回車確認(rèn)。雙擊I3單元格的填充柄。4. 選中“契稅總額”列的數(shù)據(jù)區(qū)域，輸入公式“=H3:H26*I3:I26”， 按下Ctrl+Shift+Enter組合鍵。5. 單擊Sheet2的B2單元格，插入SUMIF函數(shù)，在其參數(shù)對話框中輸入如圖 340所示的參數(shù)，（注意絕對引用和相對引用的使用），單擊【確定】按鈕。雙擊B2單元格的填充柄。圖 Error! No text of specified style in document.40 SUMIF函數(shù)參數(shù)對話框6. 參考中的題6。7. 參考中的題7。8. 參考中的題8。3.2.8 溫度情況表1. 在Sheet5的A1單元格中設(shè)置為只能

51、錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 在Sheet5中，使用函數(shù)，根據(jù)A2單元格中的身份證號碼判斷性別，結(jié)果為“男”或“女”，存放在B2單元格中。*倒數(shù)第二位為奇數(shù)的為“男”，為偶數(shù)的為“女”。3. 使用IF函數(shù)，對Sheet1“溫度情況表”中的“溫度較高的城市”列進(jìn)行填充，填充結(jié)果為城市名稱。4. 使用數(shù)組公式，對Sheet1“溫度情況表”中的相差溫度值（杭州相對于上海的溫度）進(jìn)行計算，并把結(jié)果保存在“相差溫度值”列中。*計算方法：相差溫度值杭州平均氣溫上海平均氣溫。5. 使用函數(shù)，根據(jù)Sheet1“溫度情況表”中的結(jié)

52、果，對符合以下條件的進(jìn)行統(tǒng)計。要求：*杭州這半個月以來的最高氣溫和最低氣溫，保存在相應(yīng)單元格中。*上海這半個月以來的最高氣溫和最低氣溫，保存在相應(yīng)單元格中。6. 將Sheet1中的“溫度情況表”復(fù)制到Sheet2中，在Sheet2中，重新編輯數(shù)組公式，將Sheet2中的“相差的溫度值”中的數(shù)值取其絕對值（均為正數(shù)）。注意：*復(fù)制過程中，將標(biāo)題項“溫度情況表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。7. 將Sheet2中的“溫度情況表”復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選。要求：*篩選條件：“杭州平均溫度”>=20，“上海平均溫度”<20*將篩選結(jié)果保存在Sheet3中

53、。注意：*無須考慮是否刪除篩選條件；*復(fù)制過程中，將標(biāo)題項“溫度情況表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中“溫度情況表”的結(jié)果，在Sheet4中創(chuàng)建一張數(shù)據(jù)透視表。要求：*顯示溫度較高天數(shù)的匯總情況；*行區(qū)域設(shè)置為“溫度較高的城市”；*數(shù)據(jù)區(qū)域設(shè)置為“溫度較高的城市”；*計數(shù)項為“溫度較高的城市”。l 操作步驟如下：1. 參考中的題1。2. 單擊Sheet5的B2單元格，輸入公式“=IF(MOD(MID(A2,17,1),2)=1,"男","女")”。3. 單擊Sheet1的D3單元格，插入IF函數(shù)，在其參數(shù)對話框中輸入如圖

54、 341所示的參數(shù)，單擊【確定】按鈕。雙擊C2單元格的填充柄。圖 Error! No text of specified style in document.41 IF函數(shù)參數(shù)對話框4. 選擇“相差溫度值”列的數(shù)據(jù)區(qū)域，輸入公式：“=B3:B17-C3:C17”，按下Ctrl+Shift+Enter組合鍵。5. 單擊C19單元格，插入MAX函數(shù)，在其參數(shù)對話框中輸入如圖 342所示的參數(shù)，單擊【確定】按鈕。圖 Error! No text of specified style in document.42 MAX函數(shù)參數(shù)對話框同理，依次插入MIN(B3:B17)，MAX(C3:C17)，MIN

55、(C3:C17)。6. 復(fù)制粘貼完畢，把Sheet2的E3單元格中的公式修改為：“=ABS(B3:B17-C3:C17)”，按下Ctrl+Shift+Enter組合鍵。7. 參考中的題7。8. 參考中的題8。員工信息表1. 在Sheet4的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 在Sheet4中，使用函數(shù)，將B1中的時間四舍五入到最接近的15分鐘的倍數(shù)，結(jié)果存放在C1單元格中。3. 使用REPLACE函數(shù)，對Sheet1中“員工信息表”的員工代碼進(jìn)行升級。要求：*升級方法：在PA后面加上0；*將升級后

56、的員工代碼結(jié)果填入表中的“升級員工代碼”列中；*例如：PA125，修改后PA0125。4. 使用時間函數(shù)，計算Sheet1中“員工信息表”的“年齡”列和“工齡”列。要求：*假設(shè)當(dāng)前時間是“2013-5-1”，結(jié)合表中的“出生年月”、“參加工作時間”列，對員工“年齡”和“工齡”進(jìn)行計算；*計算方法為兩年份之差。并將結(jié)果保存到表中的“年齡”列和“工齡”列中。5. 使用統(tǒng)計函數(shù)，根據(jù)Sheet1中“員工信息表”的數(shù)據(jù)，對以下條件進(jìn)行統(tǒng)計。*統(tǒng)計男性員工的人數(shù)，結(jié)果填入N3單元格中；*統(tǒng)計高級工程師人數(shù)，結(jié)果填入N4單元格中；*統(tǒng)計工齡大于等于10的人數(shù)，結(jié)果填入N5單元格中。6. 使用邏輯函數(shù)，判斷員工是否有資格評“高級工程師”。要求：*評選條件為：工齡大于20，且為工程師的員工；*并將結(jié)果保存在“是否有資格評選高級工程師”列中；*如果有資格，保存結(jié)果為TRUE；否則為FALSE。7. 將Sheet1中“員工信息表”復(fù)制到Sheet2中，并對Sheet2進(jìn)行高級篩選。要求：*篩選條件為：“性別”男，“年齡”>30，“工齡”>=10，“職稱”助工；*將結(jié)果保存在Sheet2中。注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項“員工信息表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中的數(shù)據(jù)，