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

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

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

4、二、解答步驟 1：選中 Sheet5 工作表中的 A1 單元格，選擇菜單“數(shù)據(jù)”“有效性”命 令（如圖 6-13 所示），在打開的“數(shù)據(jù)有效性”對話框中，選擇“設(shè)置”選項卡， 再選擇“允許”為“文本長度”，“數(shù)據(jù)”為“等于”，在“長度”文本框中輸入“5”， 如圖 6-14 所示。選擇“出錯警告”選項卡，再選擇“樣式”為“警告”，在“錯誤信息”文本框中輸入“只能錄入 5 位數(shù)字或文本”，如圖 6-15 所示，單擊“確定”按鈕。圖 6-13 選擇“有效性”命令圖 6-14 “設(shè)置”選項卡圖 6-15 “出錯警告”選項卡步驟 2 ：在 Sheet1 工作表中，先選中 I3:I52 單元格區(qū)域，再輸入

5、公式“=G3:G52*H3:H52”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=G3:G52*H3:H52”。說明：公式中單元格或單元格區(qū)域也可用鼠標選擇輸入，下同。步驟 3：選中 L2 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在 “選擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中選擇“COUNTIF” 函數(shù)，如圖 6-16 所示。單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，輸入“Range” 參數(shù)為“D3：D52”，“Criteria”參數(shù)為“高等教育出版社”，如圖 6-17 所示。再按 “確定”按鈕，即可統(tǒng)計出出版社名稱為“高等教育出版社

6、”的書的種類數(shù)（6）。此 時，公式編輯欄顯示“=COUNTIF(D3:D52,"高等教育出版社")”。圖 6-16 “插入函數(shù)”對話框圖 6-17 “函數(shù)參數(shù)”對話框選 中L3單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52,"<=110")”，再按回車鍵確認，即可統(tǒng) 計出訂購數(shù)量大于 110 且小于 850 的書的種類數(shù)（28）。說明：COUNTIF 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。步驟 4：選中 L8 單元格，單擊插入函

7、數(shù)按扭，打開“插入函數(shù)”對話框，在 “選擇類別”下拉列表框中選擇“數(shù)學(xué)與三角函數(shù)”，在“選擇函數(shù)”列表框中選擇 “SUMIF”函數(shù)。單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，輸入“Range”參數(shù) 為“A3：A52”，“Criteria”參數(shù)為“"=c1"”，“Sum_range”參數(shù)為“I3:I52”，如圖6-18 所示。再按“確定”按鈕，即可計算出 c1 用戶所訂購圖書需支付的金額總數(shù)（721301）。此時，公式編輯欄顯示“=SUMIF(A3:A52,"=c1",I3:I52)”。圖 6-18 “函數(shù)參數(shù)”對話框在 L9 單元格中輸入公式“=SUMI

8、F(A3:A52,"=c2",I3:I52)”，按回車鍵確認，即可計算出 c2 用戶所訂購圖書需支付的金額總數(shù)（53337）。在 L10 單元格中輸入公式“=SUMIF(A3:A52,"=c3",I3:I52)”，按回車鍵確認，即可計算出 c3 用戶所訂購圖書需支付的金額總數(shù)（65122）。在 L11 單元格中輸入公式“=SUMIF(A3:A52,"=c4",I3:I52)”，按回車鍵確認，即可計算出 c4 用戶所訂購圖書需支付的金額總數(shù)（71253）。說明：SUMIF 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。步驟 5：在 Shee

9、t2 工作表中，選擇 B2 單元格，在公式編輯欄中輸入公式 “=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"閏年","平年")”， 按回車鍵確認，即可判斷出該年份是“閏年”還是“平年”。雙擊 B2 單元格的填充柄，即可判斷出其余年份是“閏年”還是“平年”。說明：IF、OR、AND、MOD 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。步驟 6：選中 Sheet1 工作表中的 A1：I52 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復(fù)制”命令。單擊 Sheet3 工作表中的 A1 單元格

10、，單擊右鍵在快捷菜單中選擇“粘 貼”命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-19 所示，在 K2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet3 工作表中 A1：I52 單元格區(qū)域中的任一單元格， 選擇菜單“數(shù)據(jù)”“篩選”“高級篩選”命令（如圖 6-20 所示），打開“高級篩 選”對話框，“列表區(qū)域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域（如圖 6-21 所示）。將光標定位在“條件區(qū)域”文本框內(nèi)，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 K2：L3，“條件區(qū)域”文本框內(nèi)會自動填入該區(qū)域地址（如圖 6-22 所示）。再單擊“確定”按鈕。圖 6-19 創(chuàng)建篩選條件圖

11、 6-20 選擇“高級篩選”命令圖 6-21 設(shè)置“列表區(qū)域” 圖 6-22 設(shè)置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 A1：I52 單元格區(qū)域中的任一單元格，選擇菜單“數(shù) 據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令（如圖 6-23 所示），打開“數(shù)據(jù)透視表和數(shù) 據(jù)透視圖向?qū)? 步驟之 1”對話框（如圖 6-24 所示），選中“數(shù)據(jù)透視表”單選 鈕后，再單擊“下一步”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 2” 對話框，“選定區(qū)域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域（如圖 6-25 所 示），單擊“下一步”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 3”對 話框，

12、選中“現(xiàn)有工作表”單選鈕，再單擊 Sheet4 工作表中的 A1 單元格，該單元格 地址會自動填入“現(xiàn)有工作表”下方的地址文本框中（如圖 6-26 所示），單擊“完成” 按鈕。在彈出的如圖 6-27 所示界面中，將字段“出版社”拖至行字段區(qū)域，將字段“客戶”拖至列字段區(qū)域，將字段“訂數(shù)”拖至數(shù)據(jù)項區(qū)域，結(jié)果如圖 6-28 所示。保存文件后退出。圖 6-23 選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令圖 6-24 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 1”對話框圖 6-25 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 2”對話框圖 6-26 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 3”對話框圖 6-27

13、“數(shù)據(jù)透視表”布局前言139圖 6-28 數(shù)據(jù)透視表典型試題 2打開素材庫中的“典型試題 6-2.xls”文件，按下面的操作要求進行操作，并把操作結(jié)果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet5的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 使用時間函數(shù)，對 Sheet1 中用戶的年齡進行計算。要求：假設(shè)當(dāng)前時間是“2008-11-15”，結(jié)合用戶的出生年月，計算用戶的年齡，并將其計算結(jié)果保存在“年齡”列當(dāng)中。計算方法為兩個時間年份之差。3. 使用 REPLACE 函數(shù)，對

14、Sheet1 中用戶的電話號碼進行升級。 要求：對“原電話號碼”列中的電話號碼進行升級。升級方法是在區(qū)號（0571）后面加上“8”，并將其計算結(jié)果保存在“升級電話號碼”列的相應(yīng)單元格中。例如：電話號碼“05716742808” 升級后為 “057186742808”4. 在 Sheet1 中，使用 AND 函數(shù)，根據(jù)“性別”及“年齡”列中的數(shù)據(jù)，判斷所有用戶是否為大于等于 40 歲的男性，并將結(jié)果保存在“是否>=40 男性”列中。注意：如果是，保存結(jié)果為 TRUE；否則，保存結(jié)果為 FALSE。5. 根據(jù) Sheet1 中的數(shù)據(jù)，對以下條件，使用統(tǒng)計函數(shù)進行統(tǒng)計。 要求：（1）統(tǒng)計性別為

15、“男”的用戶人數(shù)，將結(jié)果填入 Sheet2 的 B2 單元格中。（2）統(tǒng)計年齡為“>40”歲的用戶人數(shù)，將結(jié)果填入 Sheet2 的 B3 單元格中。6. 將 Sheet1 復(fù)制到 Sheet3，并對 Sheet3 進行高級篩選。 要求：（1）篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)。（2）將篩選結(jié)果保存在 Sheet3 中。注意：（1）無需考慮是否刪除或移動篩選條件。（2）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 的結(jié)果，創(chuàng)建一個數(shù)據(jù)透視圖 Chart1。 要求：（1）顯示每個區(qū)域所擁有的用戶數(shù)量。（2）x 坐標設(shè)置為“所在區(qū)域”。（3）計數(shù)項為“所在區(qū)域”。（4）將對應(yīng)的數(shù)據(jù)透

16、視表保存在 Sheet4 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：在 Sheet1 工作表中，選中 D2 單元格，在公式編輯欄中輸入公式 “=YEAR("2008-11-15")-YEAR(C2)”，再按回車鍵確認，即計算出該用戶的年齡（41）。 雙擊 D2 單元格的填充柄，即可計算出其余用戶的年齡。說明：YEAR 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。步驟 3：選中 G2 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在“選 擇類別”下拉列表框中選擇“文本”，在“選擇函數(shù)”列表框中選擇“REPLACE”函數(shù)， 單擊“確定”按鈕后，

17、彈出“函數(shù)參數(shù)”對話框，輸入“Old_text”參數(shù)為“F2”， “Start_num”參數(shù)為“5”，“Num_chars”參數(shù)為“0”，“New_text”參數(shù)為“8”，如 圖 6-29 所示。再按“確定”按鈕，即可對第一個電話號碼進行升級。此時，公式編 輯欄顯示“=REPLACE(F2,5,0,"8")”。雙擊 G2 單元格的填充柄，即可對其余電話號碼進行升級。說明：REPLACE 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。圖 6-29 “函數(shù)參數(shù)”對話框步驟 4：在 H2 單元格中，輸入公式“=AND(B2="男",D2>=40)”，再按回車

18、鍵確認，即可判斷該用戶是否為大于等于 40 歲的男性。雙擊 H2 單元格的填充柄，即可判斷其余用戶是否為大于等于 40 歲的男性。步驟 5：在 Sheet2 工作表的 B2 單元格中，輸入公式“=COUNTIF(Sheet1!B2:B37,"男")”，再按回車鍵確認，即可統(tǒng)計出男性用戶人數(shù)（18）。在 Sheet2 工作表的 B3 單元格中，輸入公式“=COUNTIF(Sheet1!D2:D37,">40")”，再按回車鍵確認，即可統(tǒng)計出大于 40 歲的用戶人數(shù)（14）。步驟 6：選中 Sheet1 工作表中的 A1：H37 單元格區(qū)域，單擊右鍵在

19、快捷菜單中選 擇“復(fù)制”命令。單擊 Sheet3 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“粘 貼”命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-30 所示，在 J1：K2 單元格區(qū)域 中輸入篩選條件。單擊 Sheet3 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇 菜單“數(shù)據(jù)”“篩選”“高級篩選”命令，打開“高級篩選”對話框，“列表區(qū) 域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域。將光標定位在“條件區(qū)域”文 本框內(nèi)，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 J1：K2，“條件區(qū)域”文本框內(nèi) 會自動填入該區(qū)域地址（如圖 6-31 所示）。再單擊“確定”按鈕

20、。注意：J1 單元格中“性”和“別”之間有空格，原因是 B1 單元格中“性”和“別”之間有空格。圖 6-30 創(chuàng)建篩選條件圖 6-31 設(shè)置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇菜單“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 1”對話框，選中“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”單選鈕（如圖 6-32 所示）， 再單擊“下一步”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 2”對話 框，“選定區(qū)域”文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域（如圖 6-33 所示）， 單擊“下一步”按鈕，打開“數(shù)據(jù)透視

21、表和數(shù)據(jù)透視圖向?qū)? 步驟之 3”對話框， 選中“現(xiàn)有工作表”單選鈕，再單擊 Sheet4 工作表中的 A1 單元格，該單元格地址會 自動填入“現(xiàn)有工作表”下方的地址文本框中（如圖 6-34 所示）。單擊“布局”按鈕， 在彈出的如圖 6-35 所示界面中，將字段“所在區(qū)域”分別拖至“行”區(qū)域和“數(shù)據(jù)” 區(qū)域內(nèi)，然后按“確定”按鈕返回圖 6-34 所示對話框，按“完成”按鈕。這時在 Sheet4 工作表前會插入一張名為 Chart1 的工作表。打開該工作表可看到根據(jù) Sheet1 工作表 創(chuàng)建的數(shù)據(jù)透視圖（如圖 6-36 所示），打開 Sheet4 工作表有如圖 6-37 所示的相應(yīng)的 數(shù)據(jù)透視

22、表。保存文件后退出。圖 6-32 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 1”對話框圖 6-33 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 2”對話框圖 6-34 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 3”對話框圖 6-35 “數(shù)據(jù)透視表”布局圖 6-36 數(shù)據(jù)透視圖圖 6-37 數(shù)據(jù)透視表典型試題 3打開素材庫中的“典型試題 6-3.xls”文件，按下面的操作要求進行操作，并把操作結(jié)果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet4的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2.

23、 使用 VLOOKUP 函數(shù)，對 Sheet1 中的“三月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn) 品單價”列進行填充。要求：根據(jù)“企業(yè)銷售產(chǎn)品清單”，使用 VLOOKUP 函數(shù)，將產(chǎn)品名稱和產(chǎn)品單價填充到“三月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)品單價”列中。3. 使用數(shù)組公式，計算 Sheet1 中的“三月份銷售統(tǒng)計表”中的銷售金額，并將結(jié)果填入到該表的“銷售金額”列中。計算方法：銷售金額 = 產(chǎn)品單價 * 銷售數(shù)量4. 使用統(tǒng)計函數(shù)，根據(jù)“三月份銷售統(tǒng)計表”中的數(shù)據(jù)，計算“分部銷售業(yè)績統(tǒng)計表”中的總銷售額，并將結(jié)果填入該表的“總銷售額”列。5. 在 Sheet1 中，使用 RANK 函數(shù)，在

24、“分部銷售業(yè)績統(tǒng)計”表中，根據(jù)“總銷售額”對各部門進行排名，并將結(jié)果填入到“銷售排名”列中。6. 將 sheet1 中的“三月份銷售統(tǒng)計表”復(fù)制到 Sheet2 中，對 Sheet2 進行高級篩選。 要求：（1）篩選條件為：“銷售數(shù)量” >3、“所屬部門” 市場 1 部、“銷售金額”->1000。（2）將篩選結(jié)果保存在 Sheet2 中。注意：（1）無需考慮是否刪除或移動篩選條件。（2）復(fù)制過程中，將標題項“三月份銷售統(tǒng)計表”連同數(shù)據(jù)一同復(fù)制。（3）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 的“三月份銷售統(tǒng)計表”中的數(shù)據(jù)，新建一個數(shù)據(jù)透視圖 Chart1。 要求：（1）該圖形顯

25、示每位經(jīng)辦人的總銷售額情況。（2）x 坐標設(shè)置為“經(jīng)辦人”。（3）數(shù)據(jù)區(qū)域設(shè)置為“銷售金額”。（4）求和項為“銷售金額”。（5）將對應(yīng)的數(shù)據(jù)透視表保存在 Sheet3 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：選中 Sheet1 工作表中的 G3 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)” 對話框，在“選擇類別”下拉列表框中選擇“查找與引用”，在“選擇函數(shù)”列表框 中選擇“VLOOKUP”函數(shù)，單擊“確定”按鈕后，彈出“函數(shù)參數(shù)”對話框。輸入 “ Lookup_value ” 參 數(shù)為 “ F3 ”，“ Table_array ” 參 數(shù)為 “ $A$3:$B$

26、10 ”， “Col_index_num”參數(shù)為“2”，“Range_lookup”參數(shù)為“FALSE”，如圖 6-38 所示。 再按“確定”按鈕，即可查找到產(chǎn)品型號為“A01”的產(chǎn)品名稱（卡特掃描槍）。此時， 公式編輯欄顯示“=VLOOKUP(F3,$A$3:$B$10,2,FALSE)”。雙擊 G3 單元格的填充柄，即可查找到其余產(chǎn)品型號的產(chǎn)品名稱。在 H3 單元格中，輸入公式“=VLOOKUP(F3,$A$3:$C$10,3,FALSE)”，再按回車鍵確認， 即可查找到產(chǎn)品型號為“A01”的產(chǎn)品單價（368）。雙擊 H3 單元格的填充柄，即可查 找到其余產(chǎn)品型號的產(chǎn)品單價。說明：VLOO

27、KUP 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。圖 6-38 “函數(shù)參數(shù)”對話框步驟 3：先選中 L3:L44 單元格區(qū)域，再輸入公式“=H3:H44*I3:I44”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=H3:H44*I3:I44”。前言149步驟 4：選中 O3 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在“選擇類別”下拉列表框中選擇“數(shù)學(xué)與三角函數(shù)”，在“選擇函數(shù)”列表框中選擇“SUMIF”函數(shù)。單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，輸入“Range”參數(shù) 為“K3：K44”，“Criteria”參數(shù)為“"=市場 1 部&qu

28、ot;”，“Sum_range”參數(shù)為“L3:L44”， 如圖 6-39 所示。再按“確定”按鈕，即可計算出市場 1 部的總銷售額（35336）。此 時，公式編輯欄顯示“=SUMIF(K3:K44,"=市場 1 部",L3:L44)”。在 O4 單元格中，輸入公式“=SUMIF(K3:K44,"=市場 2 部",L3:L44)”后，按回車鍵確認。在 O5 單元格中，輸入公式“=SUMIF(K3:K44,"=市場 3 部",L3:L44)”后，按回車鍵確認。圖 6-39 “函數(shù)參數(shù)”對話框步驟 5：選中 P3 單元格，單擊插入函數(shù)按扭，

29、打開“插入函數(shù)”對話框，在 “選擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中選擇“RANK”函 數(shù)。單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，輸入“Number”參數(shù)為“O3”，“Ref” 參數(shù)為“$O$3:$O$5”，“Order”參數(shù)為“0”，如圖 6-40 所示。再按“確定”按鈕， 即 可 統(tǒng) 計 出 市場 1 部 的 總 銷售額 排 名 （ 1 ）。 此 時 ，公 式 編 輯 欄顯 示 “=RANK(O3,$O$3:$O$5,0)”。雙擊 P3 單元格的填充柄，可統(tǒng)計出其他部門的總銷售額排名。說明：RANK 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。圖 6-40 “函數(shù)參數(shù)

30、”對話框步驟 6：選中 Sheet1 工作表中的 E1：L44 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復(fù)制”命令。單擊 Sheet2 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“選 擇性粘貼”命令，打開如圖 6-41 所示的“選擇性粘貼”對話框，選中“值和數(shù)字格 式”單選鈕，再單擊“確定”按鈕。在 Sheet2 工作表的空白區(qū)域創(chuàng)建篩選條件，如圖 6-42 所示，在 J2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet2 工作表中 A1：H44 單元格區(qū)域中的任一單元格， 選擇菜單“數(shù)據(jù)”“篩選”“高級篩選”命令，打開“高級篩選”對話框，“列 表區(qū)域”文本框中已自動填入數(shù)據(jù)清單所

31、在的單元格區(qū)域。將光標定位在“條件區(qū)域” 文本框內(nèi)，用鼠標拖選前面創(chuàng)建的篩選條件單元格區(qū)域 J2：L3，“條件區(qū)域”文本框 內(nèi)會自動填入該區(qū)域地址（如圖 6-43 所示）。再單擊“確定”按鈕。圖 6-41 “選擇性粘貼”對話框圖 6-42 創(chuàng)建篩選條件圖 6-43 設(shè)置“條件區(qū)域”步驟 7：單擊 Sheet1 工作表中 E1：L44 單元格區(qū)域中的任一單元格，選擇菜單“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 1”對話框，選中“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”單選鈕，再單擊“下一步” 按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)? 步驟之 2”對話框，“選定區(qū)域”

32、文 本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域，單擊“下一步”按鈕，打開“數(shù)據(jù)透 視表和數(shù)據(jù)透視圖向?qū)? 步驟之 3”對話框，選中“現(xiàn)有工作表”單選鈕，再單 擊 Sheet3 工作表中的 A1 單元格，該單元格地址會自動填入“現(xiàn)有工作表”下方的地 址文本框中。單擊“布局”按鈕，在彈出的如圖 6-44 所示界面中，把字段“經(jīng)辦人” 拖至“行”區(qū)域內(nèi)，把字段“銷售金額”拖至“數(shù)據(jù)”區(qū)域內(nèi)，然后按“確定”按鈕 返回，再按“完成”按鈕。這時在 Sheet3 工作表前會插入一張名為 Chart1 的工作表。 打開該工作表可看到根據(jù) Sheet1 中“三月份銷售統(tǒng)計表”創(chuàng)建的數(shù)據(jù)透視圖（如圖6-45 所示）

33、，打開 Sheet3 工作表有如圖 6-46 所示的相應(yīng)的數(shù)據(jù)透視表。保存文件后退出。圖 6-44 “數(shù)據(jù)透視表”布局圖 6-45 數(shù)據(jù)透視圖圖 6-46 數(shù)據(jù)透視表典型試題 4打開素材庫中的“典型試題 6-4.xls”文件，按下面的操作要求進行操作，并把操作結(jié)果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet4的A1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當(dāng)錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2. 使用 HLOOKUP 函數(shù)，對 Sheet1“停車情況記錄表”中的“單價”列進行填充。 要求：根據(jù) Sheet1 中的“停車

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

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

36、額”>30。（2）將結(jié)果保存在 Sheet2 中。注意：（1）無需考慮是否刪除篩選條件。（2）復(fù)制過程中，將標題項“停車情況記錄表”連同數(shù)據(jù)一同復(fù)制。（3）數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 中的“停車情況記錄表”，創(chuàng)建一個數(shù)據(jù)透視圖 Chart1。 要求：（1）顯示各種車型所收費用的匯總。（2）x 坐標設(shè)置為“車型”。（3）求和項為“應(yīng)付金額”。（4）將對應(yīng)的數(shù)據(jù)透視表保存在 Sheet3 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：選中 Sheet1 工作表中的 C9 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)” 對話框，在“選擇類別”下拉列表框

37、中選擇“查找與引用”，在“選擇函數(shù)”列表框 中選擇“HLOOKUP”函數(shù)，單擊“確定”按鈕后，彈出“函數(shù)參數(shù)”對話框。輸入 “ Lookup_value ” 參 數(shù)為 “ B9 ”，“ Table_array ” 參 數(shù)為 “ $A$2:$C$3 ”， “Row_index_num”參數(shù)為“2”，“Range_lookup”參數(shù)為“FALSE”，如圖 6-47 所示。 再按“確定”按鈕，即可查找到第一輛車的停車單價（5）。此時，公式編輯欄顯示 “=HLOOKUP(B9,$A$2:$C$3,2,FALSE)”。雙擊 C9 單元格的填充柄，即可查找到其余車輛的停車單價。說明：HLOOKUP 函數(shù)的

38、功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。圖 6-47 “函數(shù)參數(shù)”對話框步驟 3：在 F9 單元格中，輸入公式“=E9-D9”，再按回車鍵確認，即計算出第一輛車的停放時間（3:03:10）。雙擊 F9 單元格的填充柄，即可計算出其余車輛的停放時間。步 驟4 ： 選 中G9單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)*C9”，再按回車鍵 確認，即可計算出第一輛車的停車費用（15）。雙擊 G9 單元格的填充柄，即可計算出其余車輛的停車費用。說明：HOUR、MINUTE 函數(shù)的功能和用法，請參見上一節(jié)的相關(guān)內(nèi)容。上述公式中 用到了 IF 函數(shù)的嵌套（即函數(shù)中還有函數(shù)）。步驟 5：選中 J8 單元格，單擊插入函數(shù)按扭，打開“插入函數(shù)”對話框，在“選 擇類別”下拉列表框中選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中選擇“COUNTIF”函數(shù)，單擊“確定”按鈕后，彈出“函數(shù)參數(shù)”對話框，輸入“Range”參數(shù)為“G9:G39”， “Criteria”參數(shù)為“">=40"”，如圖 6-48 所示。再按“確定”按鈕，即可統(tǒng)計出停 車費 用大 于 等 于