Excel2010二級典型試題解析Word版

1、傳播優(yōu)秀Word版文檔 ，希望對您有幫助，可雙擊去除！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

2、“采購表”中的“折扣”列進(jìn)行填充。要求：* 根據(jù)“折扣表”中的商品折扣率，使用相應(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é)果保存在S

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

4、，如圖 3102所示。（注意：在已知函數(shù)具體名的情況下，可以在“搜索函數(shù)”文本框中輸入函數(shù)名，然后單擊【轉(zhuǎn)到】按鈕，即可快速找到函數(shù)。）圖 3102插入ROUND函數(shù)步驟2：單擊【確定】按鈕，打開“函數(shù)參數(shù)”對話框并設(shè)置參數(shù)，如圖 3103所示。圖 3103 ROUND函數(shù)參數(shù)對話框步驟3：單擊【確定】按鈕，完成設(shè)置。2. 步驟1：選中Sheet1的“采購數(shù)量”列的數(shù)據(jù)區(qū)域。切換到“開始”選項(xiàng)卡，單擊“樣式”選項(xiàng)組的【條件格式】按鈕。步驟2：從彈出的菜單中選擇“突出顯示單元格規(guī)則”下的“大于”命令（如圖 3104所示），打開“大于”對話框：輸入條件“100”；單擊“設(shè)置為”文本框右側(cè)的下拉箭頭

5、，從彈出的子菜單里面選擇“自定義格式”（如圖 3105所示），打開“設(shè)置單元格格式”對話框。圖 3104 “條件格式”菜單圖 3105 設(shè)置“大于”對話框步驟3：在“設(shè)置單元格格式”對話框中，切換到“字體”選項(xiàng)卡，選擇字形和顏色，如圖 3106所示。圖 3106“設(shè)置單元格格式”對話框步驟4：單擊【確定】按鈕返回“大于”對話框，再單擊【確定】按鈕完成設(shè)置。3. 選中D11單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇函數(shù)VLOOKUP。單擊【確定】按鈕打開VLOOKUP函數(shù)參數(shù)對話框，在相應(yīng)的文本框中輸入如圖 3107所示的參數(shù)（注意：Table_Arrray區(qū)域的絕對引用。

6、可在拖選選區(qū)后直接按F4功能鍵快速實(shí)現(xiàn)輸入）。單擊【確定】按鈕。雙擊D11單元格的填充柄填充該列的數(shù)據(jù)。圖 3107 VLOOKUP函數(shù)參數(shù)對話框4. 根據(jù)題意分析IF嵌套函數(shù)，畫出其流程圖如圖 3108所示。 NNYYNY折扣率為B3單元格所示的（絕對引用）B11100B11200折扣率為B4單元格所示的（絕對引用）折扣率為B5單元格所示的（絕對引用）B11300折扣率為B6單元格所示的（絕對引用）圖 3108 IF嵌套函數(shù)流程圖據(jù)此，則在E11單元格中輸入公式：“=IF(B11100,$B$3,IF(B11200,$B$4,IF(B11=500，且金額總數(shù)=30000”；*將結(jié)果保存在Sh

7、eet3中。（2）注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項(xiàng)“教材訂購情況表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置；*復(fù)制過程中，數(shù)據(jù)保持一致。8. 根據(jù)Sheet1中“教材訂購情況表”的結(jié)果，在Sheet4中新建一張數(shù)據(jù)透視表。要求：*顯示每個客戶在每個出版社所訂的教材數(shù)目；*行區(qū)域設(shè)置為“出版社”；*列區(qū)域設(shè)置為“客戶”；*求和項(xiàng)為訂數(shù)；*數(shù)據(jù)區(qū)域設(shè)置為“訂數(shù)”。l 操作步驟如下：1. 步驟1：選中Sheet5工作表中的A1單元格，切換到功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡，單擊“數(shù)據(jù)工具”選項(xiàng)組中的“數(shù)據(jù)有效性”的上半部按鈕，打開“數(shù)據(jù)有效性”對話框。步驟2：切換到“設(shè)置”選項(xiàng)卡

8、：選擇“允許”下拉菜單為“文本長度”；選擇“數(shù)據(jù)”下拉菜單為“等于”；并在“長度”文本框中輸入“5”，如圖 3119所示。圖 3119設(shè)置數(shù)據(jù)有效性步驟3：再切換到“出錯警告”選項(xiàng)卡：選擇“樣式”下拉菜單為“警告”；在“錯誤信息”文本框中輸入“只能錄入5位數(shù)字或文本”，如圖 3120所示，單擊【確定】按鈕完成設(shè)置。圖 3120設(shè)置出錯信息2. 在Sheet5的B1單元格中，輸入“0 1/3”（注意：0和1中間以空格間隔）。3. 在Sheet1工作表中，先選中“金額”列的數(shù)據(jù)區(qū)域，再輸入公式“=G3:G52*H3:H52”（注意：各列數(shù)據(jù)區(qū)域建議用鼠標(biāo)拖拽選?。缓蟀聪隆癈trl+Shift+

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

10、對話框，并在相應(yīng)的文本框中輸入如圖 3122所示的參數(shù)（注意：Criteria_Range各文本框中的單元格區(qū)域可拖拽鼠標(biāo)進(jìn)行輸入），單擊【確定】按鈕。圖 3122 COUNTIFS函數(shù)參數(shù)對話框5. 選中L8單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“數(shù)學(xué)與三角函數(shù)”函數(shù)中的SUMIF函數(shù)，單擊【確定】按鈕打開SUMIF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 3123所示的參數(shù)（注意：絕對引用可在鼠標(biāo)拖選相應(yīng)的單元格區(qū)域后，按F4功能鍵快速實(shí)現(xiàn)輸入）。雙擊L8單元格的填充柄。圖 3123SUMIF函數(shù)參數(shù)對話框6. 步驟1：根據(jù)題意，閏年條件分析如錯誤!未找到引用源

11、。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圖 3124閏年條件分析步驟2：選中Sheet2工作表的B2單元格，單擊編輯欄上的【插入函數(shù)】按鈕，打開插入函數(shù)對話框并選擇“邏輯”函數(shù)中的IF函數(shù)，單擊【確定】按鈕打開IF函數(shù)參數(shù)對話框，并在相應(yīng)的文本框中輸入如圖 3124所示的參數(shù)（Logical_test文本框中的參數(shù)為“O

12、R(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)”）。雙擊B2單元格的填充柄。圖 3124 IF函數(shù)參數(shù)對話框7. 參考3.2.1中的題7。8. 可參考3.2.1中的題8。步驟1：將光標(biāo)定位在Sheet1數(shù)據(jù)區(qū)域的任一單元格，切換到功能區(qū)中的“插入”選項(xiàng)卡，在“表格”選項(xiàng)組中單擊【數(shù)據(jù)透視表】下拉箭頭，在彈出的菜單中選擇“數(shù)據(jù)透視表”命令（如圖 3125所示），打開打開“創(chuàng)建數(shù)據(jù)透視表”對話框。圖 3125 “數(shù)據(jù)透視表”菜單此時，在“選擇一個表或區(qū)域”單選按鈕下方的“表/區(qū)域”文本框中自動填入了表格的數(shù)據(jù)區(qū)域。步驟2：選擇“現(xiàn)有工作表”單選按鈕

13、，將光標(biāo)定位在“位置”右側(cè)的文本框中，單擊Shee4工作表標(biāo)簽切換到Sheet4工作表，并單擊A1單元格。步驟3：單擊【確定】按鈕，進(jìn)入數(shù)據(jù)透視表設(shè)計環(huán)境：從“選擇要添加到報表的字段”列表框中，將“出版社”拖到“行標(biāo)簽”框中；將“客戶”拖到“列標(biāo)簽”框中；將“定數(shù)”拖到“數(shù)值”框中，如圖 3126所示。最后的效果圖見圖 3127。圖 3126設(shè)置數(shù)據(jù)透視表字段列表圖 3127數(shù)據(jù)透視表效果圖3.2.3 公務(wù)員考試成績表1. 在Sheet5的A1單元格中輸入分?jǐn)?shù)1/3。2. 在Sheet1中，使用條件格式將“性別”列中為“女”的單元格中字體顏色設(shè)置為紅色、加粗顯示。3. 使用IF函數(shù)，對Shee

14、t1中“學(xué)位”列進(jìn)行自動填充。要求：填充的內(nèi)容根據(jù)“學(xué)歷”列的內(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ù)

15、以下要求修改“公務(wù)員考試成績表”中的數(shù)組公式，并將結(jié)果保存在Sheet2的相應(yīng)列中。要求：*修改“筆試比例分”的計算，計算方法為：筆試比例分（筆試成績/2）*60%，并將結(jié)果保存在“筆試成績比例分”列中。注意：*復(fù)制過程中，將標(biāo)題項(xiàng)“公務(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）要求：*篩選條件為：“報考單

16、位” 一中院、“性別”男、“學(xué)歷”碩士研究生；*將篩選結(jié)果保存在Sheet3中。（2）注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項(xiàng)“公務(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ù)項(xiàng)為學(xué)歷。l 操作步驟如下：1. 參考3.2.2中的題2。2. 參考3.2.1中的題2。3. 步驟1：根據(jù)題意，分析IF嵌套函數(shù)，畫出其流程圖如圖

17、3128所示。 NNYYNY博士G3=”博研”生”=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）要求：*篩選條件為：“性別”女，“所在區(qū)域”西湖區(qū)；*將篩選結(jié)果保存在Sheet3中。（2）注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放

18、置。8. 根據(jù)Sheet1的結(jié)果，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet4中。要求：*顯示每個區(qū)域所擁有的用戶數(shù)量；*x坐標(biāo)設(shè)置為“所在區(qū)域”；*計數(shù)項(xiàng)為“所在區(qū)域”；*將對應(yīng)的數(shù)據(jù)透視表保存在Sheet4中。l 操作步驟如下：1. 參考3.2.2中的題1。2. 參考3.2.2中的題6。此處輸入公式“=OR(AND(MOD(YEAR(NOW(),4)=0, MOD(YEAR(NOW(),100)0), MOD(YEAR(NOW(),400)=0)”。（注意：NOW函數(shù)無參數(shù)，只有括號）3. 步驟1：單擊Sheet的D2單元格，插入時間函數(shù)YEAR，在打開的YEAR函數(shù)參數(shù)對話框中，輸入如圖 31

19、30所示的參數(shù)，得到年份2013。圖 3130 YEAR函數(shù)參數(shù)對話框(1)步驟2：再在該公式后面減去如所圖 3131示的計算所得的年份，按回車。（此時公式顯示為“=YEAR(2013-5-1)-YEAR(C2)”）。按回車，再填充公式。圖 3131 YEAR函數(shù)參數(shù)對話框(2)4. 選擇Sheet的G2單元格，插入REPLACE函數(shù)，在打開的REPLACE函數(shù)參數(shù)對話框中輸入如圖 3132所示的參數(shù)，單擊【確定】按鈕。雙擊G2單元格的填充柄填充該列的數(shù)據(jù)。圖 3132 REPLACE函數(shù)參數(shù)對話框5. 選擇Sheet1的H2單元格，插入AND函數(shù)，在打開的AND函數(shù)參數(shù)對話框中輸入如圖 31

20、33所示的參數(shù)，單擊【確定】按鈕。雙擊H2單元格的填充柄填充該列的數(shù)據(jù)。圖 3133 AND函數(shù)參數(shù)對話框6. 參考3.2.2中的題4。7. 參考3.2.1中的題7。8. 參考3.2.1中的題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“停車情況記錄

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

22、用，將計算結(jié)果填入到“停車情況記錄表”中的“應(yīng)付金額”列中。注意：*停車按小時收費(fèi)，對于不滿一個小時的按照一個小時計費(fèi)；*對于超過整點(diǎn)小時數(shù)十五分鐘（包含十五分鐘）的，多累積一個小時；*例如：1小時23分，將以2小時計費(fèi)。6. 使用統(tǒng)計函數(shù)，對Sheet1中的“停車情況記錄表”根據(jù)下列條件進(jìn)行統(tǒng)計。*統(tǒng)計停車費(fèi)用大于等于40元的停車記錄條數(shù)，并將結(jié)果保存在J8單元格中；*統(tǒng)計最高的停車費(fèi)用，并將結(jié)果保存在J9單元格中。7. 將Sheet1中的“停車情況記錄表”復(fù)制到Sheet2中，對Sheet2進(jìn)行高級篩選。要求：*篩選條件為：“車型”小汽車，“應(yīng)付金額”=30；*將結(jié)果保存在Sheet2中。

23、注意：*無須考慮是否刪除篩選條件；*復(fù)制過程中，將標(biāo)題項(xiàng)“停車情況記錄表”連同數(shù)據(jù)一同復(fù)制；*復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中的“停車情況記錄表”，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet3中。要求：*顯示各種車型所收費(fèi)用的匯總；*x坐標(biāo)設(shè)置為“車型”；*求和項(xiàng)為“應(yīng)付金額”；*將對相應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。l 操作步驟如下：1. 參考3.2.2中的題1。2. 參考3.2.4中的題2。3. 單擊Sheet1工作表的C9單元格，插入HLOOKUP函數(shù)，在HLOOKUP函數(shù)對話框中輸入如圖 3134所示的參數(shù)（注意Table_Arrray區(qū)域的絕對引用，可

24、拖選選區(qū)后直接按F4功能鍵實(shí)現(xiàn)快速輸入）。單擊【確定】按鈕。雙擊C9單元格的填充柄填充該列的數(shù)據(jù)。圖 3134 HLOOKUP函數(shù)對話框4. 單擊F9單元格，輸入公式“=E9-D9”，按回車確認(rèn)。雙擊F9單元格的填充柄。5. 步驟1：根據(jù)題意，分析IF嵌套函數(shù)，畫出其流程圖如圖 3135所示。 NYNY1HOUR(F9)=0HOUR(F9)+1HOUR(F9)MINUTE(F9)=155圖 3135 IF嵌套函數(shù)流程圖步驟2：據(jù)根據(jù)以上分析，在G9單元格輸入公式：“=IF(HOUR(F9)=0,1,IF(MINUTE(F9)=15,HOUR(F9)+1,HOUR(F9)”，再乘以C9。按回車確

25、認(rèn)，雙擊G9單元格的填充柄填充該列的數(shù)據(jù)。6. 步驟1：參考3.2.2中的題4。步驟2：單擊J9單元格，插入MAX函數(shù)，在MAX函數(shù)參數(shù)對話框中輸入如圖 3136所示的參數(shù)，單擊【確定】按鈕。圖 3136MAX函數(shù)參數(shù)對話框7. 參考3.2.1中的題7。（注意：復(fù)制“三月份銷售統(tǒng)計表”的時候注意選擇使用“粘貼選項(xiàng)”中的“值”。）8. 參考3.2.1中的題8。3.2.6 語數(shù)英成績表1. 在Sheet5中使用多個函數(shù)組合，計算A1:A10中奇數(shù)的個數(shù)，結(jié)果存放在B1單元格中。2. 在Sheet1中，使用條件格式將“語文”列中數(shù)據(jù)大于80的單元格中，字體顏色設(shè)置為紅色、加粗顯示。3. 使用數(shù)組公式

26、，根據(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. 根據(jù)Sheet1中的結(jié)果，使用統(tǒng)計函數(shù)，統(tǒng)計“數(shù)學(xué)”考試成績各個分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計結(jié)果保存到Sheet2的相應(yīng)位置。

27、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ù)項(xiàng)為優(yōu)等生。l 操作步驟如下：1. 單擊選擇Sheet5中的B1單元格，輸入公式：“=SUMPRODUCT(MOD(A1:A10,2)”。2. 參考3.2.1中

28、的題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. 參考3.2.3中的題6。5. 步驟1：根據(jù)題意，條件分析如圖 3137所示（注意求各科平均分時相應(yīng)單元格區(qū)域的絕對引用，注意括號的配對）。三個條件同時滿足AND(C2AVERAGE($C$2:$C$39), D2AVERAGE($D$2:$D$39), E2AV

29、ERAGE($E$2:$E$39)語文大于語文平均分C2AVERAGE($C$2:$C$39)數(shù)學(xué)大于數(shù)學(xué)平均分D2AVERAGE($D$2:$D$39)英語大于英語平均分E2AVERAGE($E$2:$E$39)圖 3137條件分析步驟2：根據(jù)以上分析，在I2單元格中插入邏輯函數(shù)AND，在AND函數(shù)參數(shù)對話框中輸入圖 3138中所示的3個條件。單擊【確定】按鈕。雙擊I2單元格的填充柄。圖 3138 AND函數(shù)參數(shù)對話框6. 單擊Sheet2的B2單元格，插入COUNTIFS函數(shù)，在打開的COUNTIFS函數(shù)參數(shù)對話框中輸入如圖 3139所示的參數(shù)（注意使用絕對引用）。圖 3139 COUNT

30、IFS函數(shù)參數(shù)對話框其余同理，只需按題意更改Criteiral中的條件即可。（為簡單起見，可復(fù)制公式后直接修改條件）。7. 參考3.2.1中的題7。8. 參考3.2.2中的題8。3.2.7房產(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)

31、總額面積*單價。4. 使用數(shù)組公式，計算Sheet1中“房產(chǎn)銷售表”的契稅總額，并保存在“契稅總額”列中。* 計算公式為：契稅總額契稅*房產(chǎn)總額。5. 使用函數(shù)，根據(jù)Sheet1中“房產(chǎn)銷售表”的結(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；*將

32、篩選結(jié)果保存在Sheet3中。注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項(xiàng)“房產(chǎn)銷售表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中“房產(chǎn)銷售表”的結(jié)果，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet4中。要求：*顯示每個銷售人員所銷售房屋應(yīng)繳納契稅總額匯總情況；*x坐標(biāo)設(shè)置為“銷售人員”；*數(shù)據(jù)區(qū)域?yàn)椤捌醵惪傤~”；*求和項(xiàng)設(shè)置為契稅總額；*將對應(yīng)的數(shù)據(jù)透視表也保存在Sheet4中。l 操作步驟如下：1. 參考3.2.2中的題1。2. 步驟1：參考3.2.1中的題2。步驟2：選中C列，切換到“開始選項(xiàng)卡”，單擊“單元格”選項(xiàng)組中的【格式】按鈕，在彈出的下拉菜單中選擇

33、“自動調(diào)整列寬”，如圖 3140所示。圖 3140自動調(diào)整列寬3. 在“房產(chǎn)總額”列的I3單元格中，輸入公式“=F3*G3”，按回車確認(rèn)。雙擊I3單元格的填充柄。4. 選中“契稅總額”列的數(shù)據(jù)區(qū)域，輸入公式“=H3:H26*I3:I26”， 按下Ctrl+Shift+Enter組合鍵。5. 單擊Sheet2的B2單元格，插入SUMIF函數(shù)，在其參數(shù)對話框中輸入如圖 3141所示的參數(shù)，（注意絕對引用和相對引用的使用），單擊【確定】按鈕。雙擊B2單元格的填充柄。圖 3141 SUMIF函數(shù)參數(shù)對話框6. 參考3.2.3中的題6。7. 參考3.2.1中的題7。8. 參考3.2.1中的題8。3.2.

34、8 溫度情況表1. 在Sheet5的A1單元格中設(shè)置為只能錄入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é)果保存在“相差溫度值”列中。*計算方法：相差溫度值杭州平均氣溫上海平均

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

36、溫度”30，“工齡”=10，“職稱”助工；*將結(jié)果保存在Sheet2中。注意：*無須考慮是否刪除或移動篩選條件；*復(fù)制過程中，將標(biāo)題項(xiàng)“員工信息表”連同數(shù)據(jù)一同復(fù)制；*數(shù)據(jù)表必須頂格放置。8. 根據(jù)Sheet1中的數(shù)據(jù)，創(chuàng)建一個數(shù)據(jù)透視圖，保存在Sheet3中。要求：*顯示工廠中各種職稱人數(shù)的匯總情況；*x坐標(biāo)設(shè)置為“職稱”；*計數(shù)取為職稱；*數(shù)據(jù)區(qū)域?yàn)椤奥毞Q”；*將對應(yīng)的數(shù)據(jù)透視表也保存在Sheet3中。l 操作步驟如下：1. 參考3.2.2中的題1。2. 在Sheet4的B1單元格中，輸入函數(shù)“=TIME(HOUR(B1),15*ROUND(MINUTE(B1)/15,0),SECOND(

37、0)”。3. 參考3.2.4中的題4。4. 參考3.2.4中的題3。此時默認(rèn)的數(shù)字格式為“日期”。在“開始”選項(xiàng)卡的“數(shù)字”選項(xiàng)組中，單擊“日期”右側(cè)的下拉箭頭，在彈出的菜單中選擇“常規(guī)”即可。5. 參考3.2.2中的題4。6. 參考3.2.4中的題5。7. 參考3.2.1中的題7。8. 參考3.2.1中的題8。3.2.10 燈泡采購情況表1. 在Sheet5中設(shè)定A列不能輸入重復(fù)的數(shù)值。2. 在Sheet1中，使用條件格式將“瓦數(shù)”列中數(shù)據(jù)小于100的單元格中，字體顏色設(shè)置為紅色、加粗顯示。3. 使用數(shù)組公式，計算Sheet1“采購情況表”中的每種產(chǎn)品的采購總額，將結(jié)果保存到表中的“采購總額

38、”列中。*計算方法：采購總額單價*每盒數(shù)量*采購盒數(shù)4. 根據(jù)Sheet1中的“采購情況表”，使用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，計算以下情況的結(jié)果。*計算：商標(biāo)為上海，瓦數(shù)小于100的白熾燈的平均單價，并將結(jié)果填入Sheet1中的G25單元格中，保留小數(shù)2位。*計算：產(chǎn)品為白熾燈，瓦數(shù)大于等于80且小于等于100的品種數(shù)，并將結(jié)果填入Sheet1中的G26單元格中。5. 某公司對各個部門員工吸煙情況進(jìn)行統(tǒng)計，作為人力資源搭配的一個數(shù)據(jù)依據(jù)。對于調(diào)查情況，只能回答Y（吸煙）或者N（不吸煙）。根據(jù)調(diào)查情況，制作出Sheet2中的“吸煙情況調(diào)查表”。使用函數(shù)，統(tǒng)計符合以下條件的數(shù)值。*統(tǒng)計未登記的部門個數(shù)，將