excel典型試題操作步驟

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

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

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

4、性命 令如圖 6-13 所示，在翻開的“數(shù)據(jù)有效性對話框中，選擇“設置選項卡， 再選擇“允許為“文本長度，“數(shù)據(jù)為“等于，在“長度文本框中輸入“5， 如圖 6-14 所示。選擇“出錯警告選項卡，再選擇“樣式為“警告，在“錯誤信息文本框中輸入“只能錄入 5 位數(shù)字或文本，如圖 6-15 所示，單擊“確定按鈕。圖 6-13 選擇“有效性命令圖 6-14 “設置選項卡圖 6-15 “出錯警告選項卡步驟 2 ：在 Sheet1 工作表中，先選中 I3:I52 單元格區(qū)域，再輸入公式“=G3:G52*H3:H52”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=G3:G52

5、*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)計出出版社名稱為“高等教育出版社的書的種類數(shù)6。此 時，公式編輯欄顯示“=COUNTIF(D3:D52,"高等教育出版社")。圖 6-16 “插入函數(shù)對話框圖 6

6、-17 “函數(shù)參數(shù)對話框選 中L3單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52,"<=110")，再按回車鍵確認，即可統(tǒng) 計出訂購數(shù)量大于 110 且小于 850 的書的種類數(shù)28。說明：COUNTIF 函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。步驟 4：選中 L8 單元格，單擊插入函數(shù)按扭，翻開“插入函數(shù)對話框，在 “選擇類別下拉列表框中選擇“數(shù)學與三角函數(shù)，在“選擇函數(shù)列表框中選擇 “SUMIF函數(shù)。單擊“確定按鈕，彈出“函數(shù)參數(shù)對話框，輸入“R

7、ange參數(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 單元格中輸入公式“=SUMIF(A3:A52,"=c2",I3:I52)，按回車鍵確認，即可計算出 c2 用戶所訂購圖書需支付的金額總數(shù)53337。在 L10 單元格中輸入公式“=SUMIF(A3:A52,&

8、quot;=c3",I3:I52)，按回車鍵確認，即可計算出 c3 用戶所訂購圖書需支付的金額總數(shù)65122。在 L11 單元格中輸入公式“=SUMIF(A3:A52,"=c4",I3:I52)，按回車鍵確認，即可計算出 c4 用戶所訂購圖書需支付的金額總數(shù)71253。說明：SUMIF 函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。步驟 5：在 Sheet2 工作表中，選擇 B2 單元格，在公式編輯欄中輸入公式 “=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"閏年",&qu

9、ot;平年")， 按回車鍵確認，即可判斷出該年份是“閏年還是“平年。雙擊 B2 單元格的填充柄，即可判斷出其余年份是“閏年還是“平年。說明：IF、OR、AND、MOD 函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。步驟 6：選中 Sheet1 工作表中的 A1：I52 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復制命令。單擊 Sheet3 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“粘 貼命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)立篩選條件，如圖 6-19 所示，在 K2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet3 工作表中 A1：I52 單元格區(qū)域中的任一單元格， 選擇

10、菜單“數(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)立篩選條件圖 6-20 選擇“高級篩選命令圖 6-21 設置“列表區(qū)域 圖 6-22 設置“條件區(qū)域步驟 7：單擊 Sheet1 工作表中 A1：I52 單元格區(qū)域中的任一單元格，選擇菜單“數(shù) 據(jù)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖命令如圖 6-23 所示，翻開“數(shù)據(jù)透視表和數(shù) 據(jù)透

11、視圖向導3 步驟之 1”對話框如圖 6-24 所示，選中“數(shù)據(jù)透視表單項選擇 鈕后，再單擊“下一步按鈕，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 2” 對話框，“選定區(qū)域文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域如圖 6-25 所 示，單擊“下一步按鈕，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 3”對 話框，選中“現(xiàn)有工作表單項選擇鈕，再單擊 Sheet4 工作表中的 A1 單元格，該單元格 地址會自動填入“現(xiàn)有工作表下方的地址文本框中如圖 6-26 所示，單擊“完成 按鈕。在彈出的如圖 6-27 所示界面中，將字段“出版社拖至行字段區(qū)域，將字段“客戶拖至列字段區(qū)域，將字段“訂數(shù)拖至數(shù)據(jù)項

12、區(qū)域，結果如圖 6-28 所示。保存文件后退出。圖 6-23 選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖命令圖 6-24 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 1”對話框圖 6-25 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 2”對話框圖 6-26 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 3”對話框圖 6-27 “數(shù)據(jù)透視表布局前言139圖 6-28 數(shù)據(jù)透視表典型試題 2翻開素材庫中的“典型試題 6-2.xls文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet5的A1單元格中設置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣

13、式為“警告，錯誤信息為“只能錄入5位數(shù)字或文本。2. 使用時間函數(shù)，對 Sheet1 中用戶的年齡進行計算。要求：假設當前時間是“2008-11-15，結合用戶的出生年月，計算用戶的年齡，并將其計算結果保存在“年齡列當中。計算方法為兩個時間年份之差。3. 使用 REPLACE 函數(shù)，對 Sheet1 中用戶的 號碼進行升級。 要求：對“原 號碼列中的 號碼進行升級。升級方法是在區(qū)號0571后面加上“8，并將其計算結果保存在“升級 號碼列的相應單元格中。例如： 號碼“05716742808 升級后為 “0571867428084. 在 Sheet1 中，使用 AND 函數(shù)，根據(jù)“性別及“年齡列中

14、的數(shù)據(jù)，判斷所有用戶是否為大于等于 40 歲的男性，并將結果保存在“是否>=40 男性列中。注意：如果是，保存結果為 TRUE；否那么，保存結果為 FALSE。5. 根據(jù) Sheet1 中的數(shù)據(jù)，對以下條件，使用統(tǒng)計函數(shù)進行統(tǒng)計。 要求：1統(tǒng)計性別為“男的用戶人數(shù)，將結果填入 Sheet2 的 B2 單元格中。2統(tǒng)計年齡為“>40歲的用戶人數(shù)，將結果填入 Sheet2 的 B3 單元格中。6. 將 Sheet1 復制到 Sheet3，并對 Sheet3 進行高級篩選。 要求：1篩選條件為：“性別女、“所在區(qū)域西湖區(qū)。2將篩選結果保存在 Sheet3 中。注意：1無需考慮是否刪除或移

15、動篩選條件。2數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 的結果，創(chuàng)立一個數(shù)據(jù)透視圖 Chart1。 要求：1顯示每個區(qū)域所擁有的用戶數(shù)量。2x 坐標設置為“所在區(qū)域。3計數(shù)項為“所在區(qū)域。4將對應的數(shù)據(jù)透視表保存在 Sheet4 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：在 Sheet1 工作表中，選中 D2 單元格，在公式編輯欄中輸入公式 “=YEAR("2008-11-15")-YEAR(C2)，再按回車鍵確認，即計算出該用戶的年齡41。 雙擊 D2 單元格的填充柄，即可計算出其余用戶的年齡。說明：YEAR 函數(shù)的功能和用法，請參見上

16、一節(jié)的相關內(nèi)容。步驟 3：選中 G2 單元格，單擊插入函數(shù)按扭，翻開“插入函數(shù)對話框，在“選 擇類別下拉列表框中選擇“文本，在“選擇函數(shù)列表框中選擇“REPLACE函數(shù)， 單擊“確定按鈕后，彈出“函數(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ù)的功能和用法，請參見上

17、一節(jié)的相關內(nèi)容。圖 6-29 “函數(shù)參數(shù)對話框步驟 4：在 H2 單元格中，輸入公式“=AND(B2="男",D2>=40)，再按回車鍵確認，即可判斷該用戶是否為大于等于 40 歲的男性。雙擊 H2 單元格的填充柄，即可判斷其余用戶是否為大于等于 40 歲的男性。步驟 5：在 Sheet2 工作表的 B2 單元格中，輸入公式“=COUNTIF(Sheet1!B2:B37,"男")，再按回車鍵確認，即可統(tǒng)計出男性用戶人數(shù)18。在 Sheet2 工作表的 B3 單元格中，輸入公式“=COUNTIF(Sheet1!D2:D37,">40&

18、quot;)，再按回車鍵確認，即可統(tǒng)計出大于 40 歲的用戶人數(shù)14。步驟 6：選中 Sheet1 工作表中的 A1：H37 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復制命令。單擊 Sheet3 工作表中的 A1 單元格，單擊右鍵在快捷菜單中選擇“粘 貼命令。在 Sheet3 工作表的空白區(qū)域創(chuàng)立篩選條件，如圖 6-30 所示，在 J1：K2 單元格區(qū)域 中輸入篩選條件。單擊 Sheet3 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇 菜單“數(shù)據(jù)“篩選“高級篩選命令，翻開“高級篩選對話框，“列表區(qū) 域文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域。將光標定位在“條件區(qū)域文 本框內(nèi)，用鼠標

19、拖選前面創(chuàng)立的篩選條件單元格區(qū)域 J1：K2，“條件區(qū)域文本框內(nèi) 會自動填入該區(qū)域地址如圖 6-31 所示。再單擊“確定按鈕。注意：J1 單元格中“性和“別之間有空格，原因是 B1 單元格中“性和“別之間有空格。圖 6-30 創(chuàng)立篩選條件圖 6-31 設置“條件區(qū)域步驟 7：單擊 Sheet1 工作表中 A1：H37 單元格區(qū)域中的任一單元格，選擇菜單“數(shù)據(jù)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖命令，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 1”對話框，選中“數(shù)據(jù)透視圖及數(shù)據(jù)透視表單項選擇鈕如圖 6-32 所示， 再單擊“下一步按鈕，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 2”對話 框，“選定區(qū)域文本

20、框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域如圖 6-33 所示， 單擊“下一步按鈕，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 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

21、 所示，翻開 Sheet4 工作表有如圖 6-37 所示的相應的 數(shù)據(jù)透視表。保存文件后退出。圖 6-32 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 1”對話框圖 6-33 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 2”對話框圖 6-34 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 3”對話框圖 6-35 “數(shù)據(jù)透視表布局圖 6-36 數(shù)據(jù)透視圖圖 6-37 數(shù)據(jù)透視表典型試題 3翻開素材庫中的“典型試題 6-3.xls文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet4的A1單元格中設置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提

22、示錯誤原因，樣式為“警告，錯誤信息為“只能錄入5位數(shù)字或文本。2. 使用 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)計表中的銷售金額，并將結果填入到該表的“銷售金額列中。計算方法：銷售金額 = 產(chǎn)品單價 * 銷售數(shù)量4. 使用統(tǒng)計函數(shù)，根據(jù)“三月份銷售統(tǒng)計表中的數(shù)據(jù)，計算“分部銷售業(yè)績統(tǒng)計表中的總銷售額，并將結果填入該表的“總銷售額列。5. 在

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

24、示每位經(jīng)辦人的總銷售額情況。2x 坐標設置為“經(jīng)辦人。3數(shù)據(jù)區(qū)域設置為“銷售金額。4求和項為“銷售金額。5將對應的數(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$10 ”， “Col_index_num參數(shù)

25、為“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)品單價。說明：VLOOKUP 函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。圖 6-38 “函數(shù)

26、參數(shù)對話框步驟 3：先選中 L3:L44 單元格區(qū)域，再輸入公式“=H3:H44*I3:I44”，然后同時按組合鍵 Shift+Ctrl+Enter，此時，公式編輯欄顯示“=H3:H44*I3:I44。前言149步驟 4：選中 O3 單元格，單擊插入函數(shù)按扭，翻開“插入函數(shù)對話框，在“選擇類別下拉列表框中選擇“數(shù)學與三角函數(shù)，在“選擇函數(shù)列表框中選擇“SUMIF函數(shù)。單擊“確定按鈕，彈出“函數(shù)參數(shù)對話框，輸入“Range參數(shù) 為“K3：K44”，“Criteria參數(shù)為“"=市場 1 部"，“Sum_range參數(shù)為“L3:L44”， 如圖 6-39 所示。再按“確定按鈕，

27、即可計算出市場 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ù)按扭，翻開“插入函數(shù)對話框，在 “選擇類別下拉列表框中選擇“統(tǒng)計，在“選擇函數(shù)列表框中選擇“RANK函 數(shù)。單擊“確

28、定按鈕，彈出“函數(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é)的相關內(nèi)容。圖 6-40 “函數(shù)參數(shù)對話框步驟 6：選中 Sheet1 工作表中的 E1：L44 單元格區(qū)域，單擊右鍵在快捷菜單中選 擇“復制命令。單擊 Sheet2 工作表中的

29、A1 單元格，單擊右鍵在快捷菜單中選擇“選 擇性粘貼命令，翻開如圖 6-41 所示的“選擇性粘貼對話框，選中“值和數(shù)字格 式單項選擇鈕，再單擊“確定按鈕。在 Sheet2 工作表的空白區(qū)域創(chuàng)立篩選條件，如圖 6-42 所示，在 J2：L3 單元格 區(qū)域中輸入篩選條件。單擊 Sheet2 工作表中 A1：H44 單元格區(qū)域中的任一單元格， 選擇菜單“數(shù)據(jù)“篩選“高級篩選命令，翻開“高級篩選對話框，“列 表區(qū)域文本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域。將光標定位在“條件區(qū)域 文本框內(nèi)，用鼠標拖選前面創(chuàng)立的篩選條件單元格區(qū)域 J2：L3，“條件區(qū)域文本框 內(nèi)會自動填入該區(qū)域地址如圖 6-43 所示

30、。再單擊“確定按鈕。圖 6-41 “選擇性粘貼對話框圖 6-42 創(chuàng)立篩選條件圖 6-43 設置“條件區(qū)域步驟 7：單擊 Sheet1 工作表中 E1：L44 單元格區(qū)域中的任一單元格，選擇菜單“數(shù)據(jù)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖命令，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 1”對話框，選中“數(shù)據(jù)透視圖及數(shù)據(jù)透視表單項選擇鈕，再單擊“下一步 按鈕，翻開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟之 2”對話框，“選定區(qū)域文 本框中已自動填入數(shù)據(jù)清單所在的單元格區(qū)域，單擊“下一步按鈕，翻開“數(shù)據(jù)透 視表和數(shù)據(jù)透視圖向導3 步驟之 3”對話框，選中“現(xiàn)有工作表單項選擇鈕，再單 擊 Sheet3 工作表中的

31、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 所示，翻開 Sheet3 工作表有如圖 6-46 所示的相應的數(shù)據(jù)透視表。保存文件后退出。圖 6-44 “數(shù)據(jù)透視表布局圖 6-45 數(shù)據(jù)透視圖圖 6-46 數(shù)據(jù)透視表典型試題 4翻開素材庫中的“典型試題 6-4

32、.xls文件，按下面的操作要求進行操作，并把操作結果存盤。注意：在做題時，不得將數(shù)據(jù)表進行更改。一、操作要求1. 在Sheet4的A1單元格中設置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告，錯誤信息為“只能錄入5位數(shù)字或文本。2. 使用 HLOOKUP 函數(shù)，對 Sheet1“停車情況記錄表中的“單價列進行填充。 要求：根據(jù) Sheet1 中的“停車價目表價格，使用 HLOOKUP 函數(shù)對“停車情況記錄表中的“單價列根據(jù)不同的車型進行填充。注意：函數(shù)中如果需要用到絕對地址的將使用絕對地址進行計算，其他方式無效。3. 在 Sheet1 中，使用時間函數(shù)計算汽車在停車庫

33、中的停放時間。 要求：1計算方法為：“停放時間 = 出庫時間 -入庫時間。2格式為：“小時：分鐘：秒。3將結果保存在“停車情況記錄表中的“停放時間列中。例如：一小時十五分十二秒在停放時間中的表示為：“1：15：12”4. 使用函數(shù)公式，對“停車情況記錄表的停車費用進行計算。要求：根據(jù) Sheet1 停放時間的長短計算停車費用，將計算結果填入到“停車情況記錄表的“應付金額列中。注意：1停車按小時收費，對于不滿一個小時的按照一個小時計費。2對于超過整點小時數(shù)十五分鐘包含十五分鐘的多累積一個小時。例如 1 小時 23 分，將以 2 小時計費5. 使用統(tǒng)計函數(shù)，對 Sheet1 中的“停車情況記錄表根

34、據(jù)以下條件進行統(tǒng)計。 要求：1統(tǒng)計停車費用大于等于 40 元的停車記錄條數(shù)，并將結果保存在 J8 單元格中。2統(tǒng)計最高的停車費用，并將結果保存在 J9 單元格中。6. 將 Sheet1 中的“停車情況記錄表復制到 Sheet2 中，對 Sheet2 進行高級篩選。 要求：1篩選條件為：“車型 小汽車，“應付金額>30。2將結果保存在 Sheet2 中。注意：1無需考慮是否刪除篩選條件。2復制過程中，將標題項“停車情況記錄表連同數(shù)據(jù)一同復制。3數(shù)據(jù)表必須頂格放置。7. 根據(jù) Sheet1 中的“停車情況記錄表，創(chuàng)立一個數(shù)據(jù)透視圖 Chart1。 要求：1顯示各種車型所收費用的匯總。2x 坐

35、標設置為“車型。3求和項為“應付金額。4將對應的數(shù)據(jù)透視表保存在 Sheet3 中。二、解答步驟 1：操作方法參見“典型試題 1”中的步驟 1。步驟 2：選中 Sheet1 工作表中的 C9 單元格，單擊插入函數(shù)按扭，翻開“插入函數(shù) 對話框，在“選擇類別下拉列表框中選擇“查找與引用，在“選擇函數(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

36、 所示。 再按“確定按鈕，即可查找到第一輛車的停車單價5。此時，公式編輯欄顯示 “=HLOOKUP(B9,$A$2:$C$3,2,FALSE)。雙擊 C9 單元格的填充柄，即可查找到其余車輛的停車單價。說明：HLOOKUP 函數(shù)的功能和用法，請參見上一節(jié)的相關內(nèi)容。圖 6-47 “函數(shù)參數(shù)對話框步驟 3：在 F9 單元格中，輸入公式“=E9-D9”，再按回車鍵確認，即計算出第一輛車的停放時間3:03:10。雙擊 F9 單元格的填充柄，即可計算出其余車輛的停放時間。步 驟4 ： 選 中G9單 元 格 ， 在 公 式 編 輯 欄 中 輸 入 公 式 “=IF(HOUR(F9)=0,1,IF(MIN

37、UTE(F9)>=15,HOUR(F9)+1,HOUR(F9)*C9”，再按回車鍵 確認，即可計算出第一輛車的停車費用15。雙擊 G9 單元格的填充柄，即可計算出其余車輛的停車費用。說明：HOUR、MINUTE 函數(shù)的功能和用法，請參見上一節(jié)的相關內(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)計出停 車費 用大 于 等 于 40 元