[電腦基礎(chǔ)知識]Excel2003高級應用

1、excel2003高級應用目錄考生文件夾：d:result126202716醫(yī)院病人護理統(tǒng)計表（126202716dexcel）1考生文件夾：d:result126202711溫度情況表（126202711dexcel）4考生文件夾：d:result126204028房產(chǎn)銷售表7考生文件夾：d:result126202722員工信息表（126202722dexcel）9考生文件夾：d:result126202714停車情況記錄表（126202714dexcel）11考生文件夾：d:result126202709圖書訂購信息表（126202709dexcel）13考生文件夾：d:result12

2、6202723優(yōu)等生（126202723dexcel）15考生文件夾：d:result126202807零件檢測結(jié)果表（126202807dexcel）17考生文件夾：d:result126204106采購情況表19考生文件夾：d:result126202720是否=40男性（126202720dexcel）21考生文件夾：d:result126202706教材訂購情況表（126202706dexcel）22考生文件夾：d:result126202809學生成績表1（126202809dexcel）24考生文件夾：d:result126202804通訊費年度計劃表（126202804dexce

3、l）26考生文件夾：d:result126204118公務員考試成績表28考生文件夾：d:result126202707三月份銷售統(tǒng)計表（126202707dexcel）30考生文件夾：d:result126202710學生成績表2（126202710dexcel）32考生文件夾：d:result126202728學生成績表3（126202728dexcel）34考生文件夾：d:result126202717員工資料表（126202717dexcel）36考生文件夾：d:result126202616采購表38考生文件夾：d:result126202716醫(yī)院病人護理統(tǒng)計表（126202716

4、dexcel）1在sheet4中，使用函數(shù)，根據(jù)a1單元格中的身份證號碼判斷性別，結(jié)果為“男”或“女”，存放在a2單元格中。u 倒數(shù)第二位為奇數(shù)的為“男”，為偶數(shù)的為“女”在sheet4中選擇a2單元格單擊“插入函數(shù)”按鈕選擇“if”函數(shù)設(shè)置參數(shù)如圖所示：2在sheet4中，使用函數(shù)，將b1單元格中的數(shù)四舍五入到整百，存放在c1單元格中。單擊sheet4中c1單元格，在編輯欄中輸入3使用vlookup函數(shù)，根據(jù)sheet1中的“護理價格表”，對“醫(yī)院病人護理統(tǒng)計表”中的“護理價格”列進行自動填充。單擊f3單元格單擊“插入函數(shù)”按鈕選擇“vlookup”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值進行自

5、動填充4使用數(shù)組公式，根據(jù)sheet1中“醫(yī)院病人護理統(tǒng)計表”中的“入住時間”列和“出院時間”列中的數(shù)據(jù)計算護理天數(shù)，并把結(jié)果保存在“護理天數(shù)”列中。計算方法為：護理天數(shù)=出院時間入住時間。單擊h3單元格輸入“g3-d3” 按“ctrl+shift+enter”鍵輸入數(shù)組公式對該列下方的值進行自動填充5使用數(shù)組公式，根據(jù)sheet1中“醫(yī)院病人護理統(tǒng)計表”的“護理價格”和“護理天數(shù)”列，對病人的護理費用進行計算，并把結(jié)果保存在該表的“護理費用”列中。計算方法為：護理費用=護理價格*護理天數(shù)。選擇i3單元格輸入“=f3*h3”按“ctrl+shift+enter”鍵輸入數(shù)組公式選擇i3單元格鼠標

6、定位到右下角變?yōu)槭止鈽藭r拖動（即自動填充）6使用數(shù)據(jù)庫函數(shù)，按以下要求計算。要求：（a）計算sheet1“醫(yī)院病人護理統(tǒng)計表”中，性別為女性，護理級別為中級護理，護理天數(shù)大于30天的人數(shù)，并保存n13單元格中；（b）計算護理級別為高級護理的護理費用總和，并保存n22單元格中。單擊n13單元格單擊“插入函數(shù)”按鈕選擇“dcounta”函數(shù)設(shè)置參數(shù)如圖所示：單擊n22單元格單擊“插入函數(shù)”按鈕選擇“dsum”函數(shù)設(shè)置參數(shù)如圖所示：7把sheet1中的“醫(yī)院病人護理統(tǒng)計表”復制到sheet2，進行自動篩選。要求：（a）篩選條件為：“性別”女、“護理級別”高級護理；（b）將篩選結(jié)果保存在sheet2

7、中。注意：（a）復制過程中，將標題項“醫(yī)院病人護理統(tǒng)計表”連同數(shù)據(jù)一同復制。 （b）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。選擇a1至i30單元格“復制”單擊sheet2工作表單擊a1單元格“粘貼”復制sheet工作表中f3至f30單元格單擊sheet2工作表單擊f3單元格“選擇性粘貼”“數(shù)值”將光標定位到sheet2的a2單元格“數(shù)據(jù)”菜單“篩選”“自動篩選”單擊“性別”標題項右邊的篩選按鈕選擇“女”單擊“護理級別”標題項右邊的篩選按鈕選擇“高級護理”8根據(jù)sheet1中的“醫(yī)院病人護理統(tǒng)計表”，創(chuàng)建一個數(shù)據(jù)透視圖chart1，要求：（a）顯示每個護理級別的護理費用情況；（b）x坐標設(shè)置為

8、“護理級別”；（c）數(shù)據(jù)區(qū)域設(shè)置為“護理費用”；（d）求和為護理費用；（e）將對應的數(shù)據(jù)透視表保存在sheet3中。單擊“sheet1”表中的數(shù)據(jù)區(qū)域的任意單元格內(nèi)選擇“數(shù)據(jù)”菜單數(shù)據(jù)透視表和數(shù)據(jù)透視圖按向?qū)瓿扇缦虏僮鳎?最終效果圖：考生文件夾：d:result126202711溫度情況表（126202711dexcel）1在sheet5的a1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。單擊sheet5單元格單擊a1單元格“數(shù)據(jù)”菜單“有效性”設(shè)置如圖所示： 2在sheet5中，使用函數(shù)，根據(jù)a2單元格中的身份證

9、號碼判斷性別，結(jié)果為“男”或“女”，存放在b2單元格中。u 倒數(shù)第二位為奇數(shù)的為“男”，為偶數(shù)的為“女”3使用if函數(shù)，對sheet1“溫度情況表”中的“溫度較高的城市”列進行自動填充，填充結(jié)果為城市名稱。如杭州、上海。單擊d3單元格單擊“插入函數(shù)”按鈕選擇“if”函數(shù)設(shè)置如圖： 選擇d3單元格鼠標定位到右下角變?yōu)槭止鈽藭r拖動（即自動填充）4使用數(shù)組公式，對sheet1“溫度情況表”中的相差溫度值（杭州相對于上海的溫差）進行計算，并把結(jié)果保存在“相差溫度值”列中。計算方法：相差溫度值=杭州平均氣溫上海平均氣溫。選擇e3單元格輸入“=b3-c3”按“ctrl+shift+enter”鍵輸入數(shù)組

10、公式選擇e3單元格鼠標定位到右下角變?yōu)槭止鈽藭r拖動（即自動填充）5利用函數(shù)，根據(jù)sheet1“溫度情況表”中的結(jié)果，符合以下條件的進行統(tǒng)計。 a杭州這半個月以來的最高氣溫和最低氣溫，保存在相應單元格中。b上海這半個月以來的最高氣溫和最低氣溫，保存在相應單元格中。單擊c19單元格單擊“插入函數(shù)”按鈕選擇“max”函數(shù)拖動鼠標選擇b3到b17單元格，確定，即求得杭州半個月以來的最高氣溫。選擇c20單元格單擊“插入函數(shù)”按鈕選擇“min”函數(shù)拖動鼠標選擇b3到b17單元格，確定，即求得杭州半個月以來的最低氣溫。 同理可求得上海這半個月以來的最高氣溫和最低氣溫。6將sheet1中的“溫度情況表”復制

11、到sheet2中，在sheet2中，重新編輯數(shù)組公式， 將sheet2中的“相差的溫度值”中的數(shù)值取絕對值（均為正數(shù)）。注意：（a）復制過程中，將標題項“溫度情況表”連同數(shù)據(jù)一同復制； （b）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。選擇a1到e22復制單擊sheet2表粘貼選擇e3單元格按delete鍵單擊“插入函數(shù)”按鈕選擇“abs”函數(shù)輸入“b3-c3”確定按“ctrl+shift+enter”鍵輸入數(shù)組公式選擇e3單元格鼠標定位到右下角變?yōu)槭止鈽藭r拖動（即自動填充）7將sheet2中的“溫度情況表”復制到sheet3中，并對sheet3進行高級篩選，要求： （a）篩選條件：“杭州平均

12、氣溫”=20，“上海平均氣溫”1000000; b將結(jié)果保存在sheet3中。注意：（a）無需考慮是否刪除篩選條件； （b）復制過程中，將標題項“房產(chǎn)銷售表”連同數(shù)據(jù)一同復制； （c）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。7根據(jù)sheet1中“房產(chǎn)銷售表”的結(jié)果，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a顯示每個銷售人員所銷售房屋應繳納契稅總額匯總情況； bx坐標設(shè)置為“銷售人員”； c數(shù)據(jù)區(qū)域為“契稅總額”。 d求和項設(shè)置為契稅總額； e將對應的數(shù)據(jù)透視表保存在sheet4中?？忌募A：d:result126202722員工信息表（126202722dexcel）1在sheet4的a1

13、單元格中設(shè)置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2在sheet4中，使用函數(shù)，將b1中的時間四舍五入到最接近的15分鐘的倍數(shù)，結(jié)果存放在c1單元格中。單擊sheet4中的b1單元格，在編輯欄中輸入3使用replace函數(shù)，對sheet1中“員工信息表”的員工代碼進行升級。 要求：（a）升級方法：在pa后面加上0； （b）將升級后的員工代碼結(jié)果填入表中的“升級員工代碼”列中。 例如：pa125，修改后為pa0125。單擊c3單元格單擊“插入函數(shù)”按鈕選擇“replace”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充4

14、使用時間函數(shù)，計算sheet1中“員工信息表”的“年齡”列和“工齡”列。 要求：假設(shè)當前時間是“2009-11-15”，結(jié)合表中的“出生年月”、“參加工作時間”列，對員工“年齡”和“工齡”進行計算，計算方法為兩年份之差。并將結(jié)果保存到表中的“年齡”列和“工齡”列中。單擊f3單元格輸入“=2009-year(e3)”回車右鍵單擊f3單元格設(shè)置單元格格式選擇“數(shù)字” 設(shè)置小數(shù)位0，確定，并對該列下方的單元格的值進行自動填充單擊h3單元格輸入“=2009-year(g3)”回車右鍵單擊“設(shè)置單元格格式”選擇“數(shù)字”標簽設(shè)置如圖所示。對該列下方的單元格的值進行自動填充5使用統(tǒng)計函數(shù)，根據(jù)sheet1中

15、“員工信息表”的數(shù)據(jù)，對以下條件進行統(tǒng)計。條件：（a）統(tǒng)計男性員工的人數(shù)，結(jié)果填入n3單元格中；（b）統(tǒng)計高級工程師人數(shù)，結(jié)果填入n4單元格中；（c）統(tǒng)計工齡大于等于10的人數(shù)，結(jié)果填入n5單元格中。單擊n3單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：單擊n4單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：單擊n5單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：6使用邏輯函數(shù)，判斷員工是否有資格評“高級工程師”。要求：（a）評選條件為：工齡大于20，且為工程師的員工；（b）并將結(jié)果保存在“是否資格評選高級工程師”列中；（c）

16、如果有資格，保存結(jié)果為true；否則為false。單擊k3單元格單擊“插入函數(shù)”按鈕選擇“if”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的單元格的值進行自動填充7將sheet1的“員工信息表”復制到sheet2中，并對sheet2進行高級篩選。a篩選條件為：“性別”男，“年齡”30，“工齡”=10，“職稱”助工；; b將結(jié)果保存在sheet2中。注意：（a）無需考慮是否刪除或移動篩選條件； （b）復制過程中，將標題項“員工信息表”連同數(shù)據(jù)一同復制； （c）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中的數(shù)據(jù)，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a顯示工廠中各種職稱人數(shù)的匯總情況；

17、bx坐標設(shè)置為“職稱”； c計數(shù)項為職稱。 d數(shù)據(jù)區(qū)域為“職稱”； e將對應的數(shù)據(jù)透視表保存在sheet3中。考生文件夾：d:result126202714停車情況記錄表（126202714dexcel）1在sheet5的a1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2在sheet4的b1單元格中輸入公式，判斷當前年份是否為閏年，結(jié)果為true或false。u 閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。 單擊sheet4的b1單元格，單擊“插入函數(shù)”按鈕選擇“if”函數(shù) 設(shè)置參數(shù)如圖所

18、示：條件框：or(and(mod(year(today(),4)=0,mod(year(today(),100)0),mod(year(today(),400)=0)3使用hlookup函數(shù)，對sheet1“停車情況記錄表”中的“單價”列進行填充。 要求：根據(jù)sheet1中的“停車價目表”價格，利用hlookup函數(shù)對“停車情況記錄表”中的“單價”列根據(jù)不同的車型進行填充。注意：函數(shù)中如果需要用到絕對地址的將使用絕對地址進行計算，其他方式無效。單擊c9單元格單擊“插入函數(shù)”按鈕選擇“hlookup”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充4在sheet1中，利用時間函數(shù)計算汽車在停車庫

19、中的停放時間。 要求：a公式計算方法為“停放時間=出庫時間入庫時間”； b格式為：“小時：分鐘：秒”。 c將結(jié)果保存在“停車情況登記表”中的“停放時間”列中。 （例如：一小時十五分十二秒在停放時間中的表示為：“1：15：12”）選擇f9單元格輸入“=e9-d9”按enter鍵對該列下方的值實行自動填充5使用函數(shù)公式，計算停車費用，要求：根據(jù)停放時間的長短計算停車費用，將計算結(jié)果填入到“應付金額”列中。注意：a停車按小時收費，對于不滿一個小時的按照一個小時計費； b對于超過整點小時數(shù)十五分鐘的多累積一個小時。 （例如：1小時23分，將以2小時計費）選擇g9單元格輸入如下公式按enter鍵“=if

20、(hour(f9)0,if(minute(f9)=15,hour(f9)+1,hour(f9)*c9,c9)” 或“=if(hour(f9)=1,if(minute(f9)=15,hour(f9)+1,hour(f9),1)*c9”對該列下方的值實行自動填充6使用統(tǒng)計函數(shù)，對sheet1中的“停車情況記錄表”根據(jù)下列條件進行統(tǒng)計，要求： a統(tǒng)計停車費用大于等于40元的停車記錄條數(shù)，并將結(jié)果保存在j8單元格中； b統(tǒng)計最高的停車費用，并將結(jié)果保存在j9單元格中。單擊j8單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：單擊j9單元格單擊“插入函數(shù)”按鈕選擇“max”函數(shù)輸入“

21、g9:g39”按確定按鈕7將sheet1的“停車情況記錄表”復制到sheet2中，并對sheet2進行高級篩選。要求：a篩選條件為：“車型”小汽車、“應付金額”=30； b將結(jié)果保存在sheet2中。注意：（a）無需考慮是否刪除或移動篩選條件； （b）復制過程中，將標題項“停車情況記錄表”連同數(shù)據(jù)一同復制； （c）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中的“停車情況記錄表”，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a顯示各種車型所收費用的匯總； bx坐標設(shè)置為“車型”； c求和項為“應付金額”。 d將對應的數(shù)據(jù)透視表保存在sheet3中。 考生文件夾：d:result12

22、6202709圖書訂購信息表（126202709dexcel）1在sheet4中，使用函數(shù)，根據(jù)e1單元格中的身份證號碼判斷性別，結(jié)果為“男”或“女”，存放在f1單元格中。倒數(shù)第二位為奇數(shù)的為“男”，為偶數(shù)的為“女”在sheet4中選擇f1單元格單擊“插入函數(shù)”按鈕選擇“if”函數(shù)設(shè)置參數(shù)如圖所示：2在sheet4中，使用條件格式將“性別”列中數(shù)據(jù)為“女”的單元格中字體顏色設(shè)置為紅色、加粗顯示。選中c2:c56，再選擇“格式”“條件格式”，在“條件格式”對話框中設(shè)置條件，單擊“格式”按鈕進入單元格格式的設(shè)置，具體如下的設(shè)置： 3使用if和mid函數(shù)，根據(jù)sheet1中的“圖書訂購信息表”中的“

23、學號”列對“所屬學院”列進行填充。要求：根據(jù)每位學生學號的第七位填充對應的“所屬學院”。 學號第七位為1計算機學院 學號第七位為0電子信息學院選擇c3單元格單擊“插入函數(shù)”按鈕選擇“if”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。4使用countblank函數(shù)，對sheet1中的“圖書訂購信息表”中的“訂書種類數(shù)”列進行填充。注意：（a）其中“1”表示該同學訂購該圖書，空格表示沒有訂購； （b）將結(jié)果保存在sheet1中的“圖書訂購信息表”中的“訂書種類數(shù)”列。選擇h3單元格在編輯框中輸入（如圖所示）：對該列下方的值實行自動填充。5使用公式，對sheet1中的“圖書訂購信息表”中的“應

24、繳總額（元）”列進行填充。計算方法為：應繳總額=c語言*單價+高等數(shù)學*單價+大學語文*單價+大學英語*單價。選擇i3單元格在編輯框中輸入（如圖所示）：對該列下方的值實行自動填充。6使用統(tǒng)計函數(shù)，根據(jù)sheet1中“圖書訂購信息表”的數(shù)據(jù)，統(tǒng)計應繳總額大于100元的學生人數(shù)，將結(jié)果保存在sheet1的m9單元格中。單擊m9單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充7將sheet1中“圖書訂購信息表”復制到sheet2中，并對sheet2進行自動篩選，要求： a篩選條件為：“訂書種類數(shù)”=3，“所屬學院”計算機學院; b將篩選結(jié)果保存在she

25、et2中。注意：（a）復制過程中，將標題項“圖書訂購信息表”連同數(shù)據(jù)一同復制； （b）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中“圖書訂購信息表”，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a顯示每個學院圖書訂購的訂書金額匯總情況； bx坐標設(shè)置為“所屬學院”； c數(shù)據(jù)區(qū)域為“訂購金額（元）”。 d求和項設(shè)置為訂書金額（元）； e將對應的數(shù)據(jù)透視表保存在sheet3中。考生文件夾：d:result126202723優(yōu)等生（126202723dexcel）1在sheet5中使用函數(shù)計算a1:a10中奇數(shù)的個數(shù)，結(jié)果存放在b1單元格中。在sheet5中選擇b1單元格單擊“插入函數(shù)

26、”按鈕選擇“sumproduct”函數(shù)設(shè)置參數(shù)如圖所示：2在sheet1中，使用條件格式將“語文”列中數(shù)據(jù)大于80的單元格中字體顏色設(shè)置為紅色、加粗顯示。選中sheet1中“語文”列的數(shù)據(jù)，選擇“格式”“條件格式”，做相關(guān)的條件設(shè)置，單擊“格式”按鈕，做紅色、加粗的設(shè)置。3使用數(shù)組公式，根據(jù)sheet1中的數(shù)據(jù)，計算總分和平均分，將其計算結(jié)果保存到表的“總分”列和“平均分”列當中。選擇f2單元格單擊“插入函數(shù)”按鈕選擇“sum”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。選擇g2單元格單擊“插入函數(shù)”按鈕選擇“average”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。4使用ran

27、k函數(shù)，根據(jù)sheet1中的“總分”列對每個同學排名情況進行統(tǒng)計，并將排名結(jié)果保存到表中的“排名”列當中。選擇h2單元格單擊“插入函數(shù)”按鈕選擇“rank”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。5使用邏輯函數(shù)，判斷sheet1中每個同學的每門功課是否均高于全班單科平均分。要求：如果是，保存結(jié)果為true，否則，保存結(jié)果為false，將結(jié)果保存在表中的“優(yōu)等生”列當中。注意：優(yōu)等生條件：每門功課均高于全班單科平均分。選擇i2單元格在編輯框中輸入（如圖所示）：對該列下方的值實行自動填充。6根據(jù)sheet1中的結(jié)果，使用統(tǒng)計函數(shù)，統(tǒng)計“數(shù)學”考試成績各個分數(shù)段的同學人數(shù)，將統(tǒng)計結(jié)果保存到

28、sheet2中的相應位置。單擊sheet2表雙擊b2單元格輸入如下公式回車“=countif(sheet1!d2:d39,=0)-countif(sheet1!d2:d39,=20)” 單擊sheet2表雙擊b3單元格輸入如下公式回車“=countif(sheet1!d2:d39,20)-countif(sheet1!d2:d39,=40)” 單擊sheet2表雙擊b4單元格輸入如下公式回車“=countif(sheet1!d2:d39,40)-countif(sheet1!d2:d39,=60)” 單擊sheet2表雙擊b5單元格輸入如下公式回車“=countif(sheet1!d2:d39

29、,60)-countif(sheet1!d2:d39,=80)” 單擊sheet2表雙擊b6單元格輸入如下公式回車“=countif(sheet1!d2:d39,80)” 7將sheet1復制到sheet3中，并對sheet3進行高級篩選，要求： a篩選條件為：“語文”=75，“數(shù)學”=75，“英語”=75，“總分”=250; b將篩選結(jié)果保存在sheet3中。注意：（a）無需考慮是否刪除篩選條件； （b）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中的結(jié)果，在sheet3中創(chuàng)建一張數(shù)據(jù)透視表，要求： a顯示是否為優(yōu)等生的學生人數(shù)匯總情況； b行區(qū)域設(shè)置為“優(yōu)等生”； c數(shù)據(jù)區(qū)

30、域為“優(yōu)等生”。 d計數(shù)項為優(yōu)等生；考生文件夾：d:result126202807零件檢測結(jié)果表（126202807dexcel）1在sheet4的a1單元格中輸入分數(shù)1/32在sheet4中，使用函數(shù)，將b1中的時間四舍五入到最接近的15分鐘的倍數(shù)，結(jié)果存放在c1單元格中。3使用數(shù)組公式，根據(jù)sheet1中“零件檢測結(jié)果表”的“外輪直徑”和“內(nèi)輪直徑”列，計算內(nèi)外輪差，并將結(jié)果表保存在“輪差”列中。計算方法為：輪差=外輪直徑內(nèi)輪直徑。選擇d3單元格輸入“=b3-c3”按“ctrl+shift+enter”鍵輸入數(shù)組公式選擇d3單元格鼠標定位到右下角變?yōu)槭止鈽藭r拖動（即自動填充）4使用if函

31、數(shù)，對sheet1中的“零件檢測結(jié)果表”的“檢測結(jié)果”列進行填充。要求：（a）如果“輪差”=40男性（126202720dexcel）1在sheet5的a1單元格中設(shè)置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2在sheet5的b1單元格中輸入公式，判斷當前年份是否為閏年，結(jié)果為true或false。u 閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。3使用時間函數(shù)，對sheet1中用戶的年齡進行計算。要求：假設(shè)當前時間是“2012-4-1”，結(jié)合用戶的出生年月，計算用戶的年齡，并將其計算結(jié)果保存在“年

32、齡”列當中。計算方法為兩個時間年份之差。單擊d2單元格輸入“=year(today()-year(c2)”回車對該列下方的單元格的值進行自動填充4使用replace函數(shù)，對sheet1中用戶的電話號碼進行升級。 要求：對“原電話號碼”列中的電話號碼進行升級。升級方法是在區(qū)號（0571）后面加上“8”，并將其計算結(jié)果保存在“升級電話號碼”列的相應單元格中。例如：電話號碼“05716742808”升級后為“057186742808”。選擇g2單元格單擊“插入函數(shù)”按鈕選擇“replace”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。5在sheet1中，使用and函數(shù)，根據(jù)“性別”及“年齡”列

33、中的數(shù)據(jù)，判斷所有用戶是否為大于等于40歲的男性，并將結(jié)果保存在sheet1中的“是否=40男性”列中。注意：如果是，保存結(jié)果為true，否則，保存結(jié)果為false。選擇h2單元格單擊“插入函數(shù)”按鈕選擇“and”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。6對sheet1中的數(shù)據(jù)，根據(jù)以下條件，利用統(tǒng)計函數(shù)進行統(tǒng)計，要求： a統(tǒng)計性別為“男”的用戶人數(shù)，將結(jié)果填入sheet2的b2單元格中； b統(tǒng)計年齡為“40”歲的用戶人數(shù)，將結(jié)果填入sheet2的b3單元格中。單擊sheet2單擊b2單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)。單擊b3單元格單擊“插入函數(shù)”按鈕選擇“cou

34、ntif”函數(shù)。7將sheet1復制到sheet3中，并對sheet3進行高級篩選，要求： a篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)； b將篩選結(jié)果保存在sheet3中。注意：（a）無需考慮是否刪除或移動篩選條件； （b）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中的結(jié)果，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a顯示每個區(qū)域所擁有的用戶數(shù)量； bx坐標設(shè)置為“所在區(qū)域”； c計數(shù)項為“所在區(qū)域”； d將對應的數(shù)據(jù)透視表保存在sheet4中?？忌募A：d:result126202706教材訂購情況表（126202706dexcel）1在sheet5的a1單元格中設(shè)置為只

35、能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。2在sheet5的b1單元格中輸入分數(shù)1/33使用數(shù)組公式，對sheet1中“教材訂購情況表”的訂購金額進行計算，并將結(jié)果保存在該表的“金額”列當中。計算方法為：金額=訂數(shù)*單價。單擊i3單元格輸入“=g3*h3”按“ctrl+shift+enter”鍵輸入數(shù)組公式對該列下方的單元格的值進行自動填充4使用統(tǒng)計函數(shù)，對sheet1中“教材訂購情況表”的結(jié)果按以下條件進行統(tǒng)計，并將結(jié)果保存在sheet1中的相應位置。要求：（a）統(tǒng)計出版社名稱為“高等教育出版社”的書的種類數(shù)，并將結(jié)果保存在s

36、heet1中l(wèi)2單元格中。（b）統(tǒng)計訂購數(shù)量大于110且小于850的書的種類數(shù)，并將結(jié)果保存在sheet1中l(wèi)3單元格中。單擊l2單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)。單擊l3單元格在編輯框中輸入（如圖所示）:5使用函數(shù)，計算每個用戶所訂購圖書所需支付的金額總數(shù)，并將結(jié)果保存在sheet1中“用戶支付情況表”的“支付總額”列中。選擇l8單元格單擊“插入函數(shù)”按鈕選擇“sumif”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。6使用函數(shù)，判斷sheet2中的年份是否為閏年，如果是，結(jié)果保存“閏年”，如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是否為閏年”列中。說明：閏年定義

37、：閏年能被4整除而不能被100整除，或者能被400整除的年份。單擊sheet2單擊b2單元格在編輯框中輸入（如圖所示）:7將sheet1中的“教材訂購情況表”復制到sheet3中，并對sheet3進行高級篩選，要求： a篩選條件為：“訂數(shù)=500、且金額總數(shù)20075032001選擇b3單元格單擊“插入函數(shù)”按鈕選擇“replace”函數(shù)設(shè)置參數(shù)如圖所示：按ctrl+shift+alt鍵，計算值，并對該列下方的值實行自動填充。4使用if函數(shù)和邏輯函數(shù)，對sheet1中“學生成績表”中的“結(jié)果1”和“結(jié)果2”列進行填充。要求：填充的內(nèi)容根據(jù)以下條件確定：（將男生、女生分開寫進if函數(shù)當中）結(jié)果1

38、：如果是男生，成績=14.00，填充為“不合格”； 如果是女生，成績=16.00，填充為“不合格”；結(jié)果2：如果是男生，成績7.50，填充為“合格”；成績5.50，填充為“合格”；成績=5.50，填充為“不合格”；單擊f3單元格在編輯框中輸入（如圖所示）:對該列下方的值實行自動填充。單擊h3單元格在編輯框中輸入（如圖所示）:對該列下方的值實行自動填充。5對于sheet1“學生成績表”中的數(shù)據(jù)，根據(jù)以下條件，使用統(tǒng)計函數(shù)進行統(tǒng)計，要求：（a）獲取“100米跑的最快的學生成績”，并將結(jié)果填入到sheet1的k4單元格中；（b）統(tǒng)計“所有學生結(jié)果1為合格的總?cè)藬?shù)”，并將結(jié)果填入到sheet1的k5單

39、元格中；選擇k4單元格單擊“插入函數(shù)”按鈕選擇“min”函數(shù)設(shè)置參數(shù)如圖所示：選擇k5單元格單擊“插入函數(shù)”按鈕選擇“countif”函數(shù)設(shè)置參數(shù)如圖所示：6根據(jù)sheet2中的貸款情況，使用財務函數(shù)對貸款償還金額進行計算，要求：（a）計算“按年償還貸款金額（年末）”，并將結(jié)果填入到sheet2的e2單元格中；（b）計算“第9個月貸款利息金額”，并將結(jié)果填入到sheet2的e3單元格中；單擊sheet2單擊e2單元格單擊“插入函數(shù)”按鈕選擇“pmt”函數(shù):單擊e3單元格單擊“插入函數(shù)”按鈕選擇“ispmt”函數(shù):7將sheet1中的“學生成績表”復制到sheet3中，并對sheet3進行高級篩

40、選，要求： a篩選條件為：“性別男、100米成績（秒）9.00”； b將篩選結(jié)果保存在sheet3中。注意：（a）無需考慮是否刪除或移動篩選條件； （b）復制過程中，將標題項“學生成績表”連同數(shù)據(jù)一同復制。 （c）復制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。8根據(jù)sheet1中“學生成績表”的結(jié)果，在sheet4中創(chuàng)建一張數(shù)據(jù)透視表，要求： a顯示每種性別學生的合格與不合格總?cè)藬?shù)； b行區(qū)域設(shè)置為“性別”；c列區(qū)域設(shè)置為“結(jié)果1”； d計數(shù)項為結(jié)果1； e數(shù)據(jù)區(qū)域設(shè)置為“結(jié)果1”?？忌募A：d:result126202804通訊費年度計劃表（126202804dexcel）1在sheet4的b

41、1單元格中輸入公式，判斷當前年份是否為閏年，結(jié)果為true或false。u 閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。2在sheet1中，使用條件格式將“崗位類別”列中單元格數(shù)據(jù)按下列要求顯示。u 數(shù)據(jù)為“副經(jīng)理”的單元格中字體顏色設(shè)置為紅色、加粗顯示；u 數(shù)據(jù)為“服務部”的單元格中字體顏色設(shè)置為藍色、加粗顯示選中sheet1中的“崗位類別”的數(shù)據(jù)，選擇“格式”“條件格式”，在“條件格式”對話框中，單擊“添加”按鈕，并做如下兩個條件的輸入設(shè)置，并分別選擇“格式”按鈕進行相應的顏色和加粗設(shè)置。3使用vlookup函數(shù)，根據(jù)sheet1中的“崗位最高限額明細表”，填充“通訊費年度計劃表”中的“崗位標準”列。選擇d4單元格單擊“插入函數(shù)”按鈕選擇“vlookup”函數(shù)設(shè)置參數(shù)如圖所示：對該列下方的值實行自動填充。4使用int函數(shù)，計算sheet1中“通訊費年度計劃表”的“預計報銷總時間”列。要求：（a）每月以30天計算；（b）將結(jié)果填充在“預計報銷總時間”列中。單擊g4單元格在編輯框中輸入（如圖所示）:對該列下方的值實行自動填充。5使用數(shù)組