第十一題 學(xué)生成績表

【計數(shù)函數(shù)】2. 在Sheet1中使用函數(shù)計算全部數(shù)學(xué)成績中奇數(shù)的個數(shù)，結(jié)果存放在A41單元格中。

在A41單元格中輸入=SUMPRODUCT(MOD(F3:F24,2))，然后摁回車。

SUMPRODUCT (array1， [array2]， [array3]， ...)

【替換函數(shù)】3. 使用REPLACE函數(shù)，將Sheet1中“學(xué)生成績表”的學(xué)生學(xué)號進行更改，并將更改后的學(xué)號填入到“新學(xué)號”列中，學(xué)號更改的方法為：在原學(xué)號的前面加上“2009”。

在B3單元格中輸入=REPLACE(A3,1,0,"2009")，然后進行自動填充。

【數(shù)組公式】4. 使用數(shù)組公式，對Sheet1中“學(xué)生成績表”中的“總分”列進行計算。

選中“總分”列，在公式編輯欄輸入=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24，然后同時摁Ctrl+Shift+Enter。

【邏輯函數(shù)】5. 使用IF函數(shù)，根據(jù)以下條件，對Sheet1中“學(xué)生成績表”的“考核”列進行計算。

在K3單元格輸入=IF(J3>=350,"合格","不合格")，然后進行自動填充。

【數(shù)據(jù)庫函數(shù)】6. 在Sheet1中，利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，根據(jù)以下情況計算，并將結(jié)果填入到相應(yīng)的單元格當(dāng)中。

在D28單元格中輸入=DCOUNTA(A2:K24,6,M2:N3)，然后摁回車。

DCOUNTA(database, field, criteria)

在D29單元格中輸入=DGET(A2:K24,3,M7:N8)，然后摁回車。

DGET(database, field, criteria)

在D30單元格中輸入=DAVERAGE(A2:K24,9,M12:M13)，然后摁回車。

DAVERAGE?(database, field, criteria)

在D31單元格中輸入=DMAX(A2:K24,9,M12:M13)，然后摁回車。

DMAX(database, field, criteria)

第十二題 三月份銷售統(tǒng)計表

【計數(shù)函數(shù)】1. 在Sheet4中使用函數(shù)計算A1:A10中奇數(shù)的個數(shù)，結(jié)果存放在A12單元格中。

在A12單元格中輸入=SUMPRODUCT(MOD(A1:A10,2))，然后摁回車。

【查詢函數(shù)】3. 使用VLOOKUP函數(shù)，對Sheet1中的“三月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)品單價”列進行填充。

在G3單元格中輸入=VLOOKUP(F3,$A$2:$C$10,2,0)，注意絕對引用，然后進行自動填充。

在H3單元格中輸入=VLOOKUP(F3,$A$2:$C$10,3,0)，注意絕對引用，然后進行自動填充。

【數(shù)組公式】4. 使用數(shù)組公式，計算Sheet1中的“三月份銷售統(tǒng)計表”中的銷售金額，并將結(jié)果填入到該表的“銷售金額”列中。

選中“銷售金額”列，在公式編輯欄輸入=H3:H44*I3:I44，然后同時摁Ctrl+Shift+Enter。

【統(tǒng)計函數(shù)】5. 使用統(tǒng)計函數(shù)，根據(jù)“三月份銷售統(tǒng)計表”中的數(shù)據(jù)，計算“分部銷售業(yè)績統(tǒng)計表”中的總銷售額，并將結(jié)果填入該表的“總銷售額”列。

在O3單元格中輸入=SUMIF($K$3:$K$44,N3,$L$3:$L$44)，注意絕對引用，然后進行自動填充。

【排序函數(shù)】6. 在Sheet1中，使用RANK函數(shù)，在“分部銷售業(yè)績統(tǒng)計表”中，根據(jù)“總銷售額”對各部門進行排名，并將結(jié)果填入到“銷售排名”列中。

在P3單元格中輸入=RANK(O3,$O$3:$O$5,0)，注意絕對引用，然后進行自動填充。

第十三題 學(xué)生成績表

【數(shù)據(jù)查重】1. 在Sheet5中設(shè)定F列中不能輸入重復(fù)的數(shù)值。

“允許”選擇自定義，勾選忽略空值，公式輸入=COUNTIF(F:F,F1)=1。

【數(shù)組公式】3. 使用數(shù)組公式，根據(jù)Sheet1中“學(xué)生成績表”的數(shù)據(jù)，計算考試總分，并將結(jié)果填入到“總分”列中。

選中“總分”列，在公式編輯欄輸入=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57，然后同時摁Ctrl+Shift+Enter。

【文本函數(shù)】4. 使用文本函數(shù)中的一個函數(shù)，在Sheet1中，利用“學(xué)號”列的數(shù)據(jù)，根據(jù)以下要求獲得考生所考級別，并將結(jié)果填入“級別”列中。

在C3單元格中輸入=MID(A3,8,1)，然后進行自動填充。

【統(tǒng)計函數(shù)】5. 使用統(tǒng)計函數(shù)，根據(jù)以下要求對Sheet1中“學(xué)生成績表”的數(shù)據(jù)進行統(tǒng)計。

在N2單元格中輸入=COUNTIF(C3:C57,"1")，然后摁回車。

在N3單元格中輸入=COUNTIF(J3:J57,">=60")，然后摁回車。

在N4單元格中輸入=SUMIF(C3:C57,"1",J3:J57)/N2，然后摁回車。

【財務(wù)函數(shù)】6. 使用財務(wù)函數(shù)，根據(jù)以下要求對Sheet2中的數(shù)據(jù)進行計算。

在B7單元格中輸入=FV(B3,B5,B4,B2)，然后摁回車。

FV(rate,nper,pmt,[pv],[type])

在E7單元格中輸入=PV(E3,E4,E2,0)，然后摁回車。

PV(rate, nper, pmt, [fv], [type])

第十四題 通訊費年度計劃表

【閏年公式】1. 在Sheet4的B1單元格中輸入公式，判斷當(dāng)前年份是否為閏年，結(jié)果為TRUE或FALSE。

閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet4表B1單元格中輸入=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0)),TRUE,FALSE)，然后摁回車。

【查詢函數(shù)】3. 使用VLOOKUP函數(shù)，根據(jù)Sheet1中的“崗位最高限額明細表”，填充“通訊費年度計劃表”中的“崗位標準”列。

在D4單元格輸入=VLOOKUP(C4,$K$5:$L$12,2,0)，注意絕對引用，然后進行自動填充。

【取整函數(shù)】4. 使用INT函數(shù)，計算Sheet1中“通訊費年度計劃表”的“預(yù)計報銷總時間”列。

在G4單元格輸入=INT((F4-E4)/30)，然后進行自動填充。

INT(Number)

【數(shù)組公式】5. 使用數(shù)組公式，計算Sheet1中“通訊費年度計劃表”的“年度費用”列。

選中“年度費用”列，在公式編輯欄輸入=D4:D26*G4:G26，然后同時摁Ctrl+Shift+Enter。

【求和公式】6. 根據(jù)Sheet1中“通訊費年度計劃表”的“年度費用”列，計算預(yù)算總金額。

在C2單元格輸入=SUM(H4:H26)，然后摁回車。

第十五題 醫(yī)院病人護理統(tǒng)計表

【邏輯函數(shù)】1. 在Sheet4中，使用函數(shù)，根據(jù)A1單元格中的身份證號碼判斷性別，結(jié)果為“男”或“女”，存放在A2單元格中。

在A2單元格中輸入公式=IF(MOD(MID(A1,17,1),2)=1,"男","女")，然后摁回車。

【四舍五入】2. 在Sheet4中，使用函數(shù)，將B1單元格中的數(shù)四舍五入到整百，存放在C1單元格中。

在C1單元格中輸入公式=ROUND(B1,-2)，然后摁回車。

【查詢函數(shù)】3. 使用VLOOKUP函數(shù)，根據(jù)Sheet1中的“護理價格表”，對“醫(yī)院病人護理統(tǒng)計表”中的“護理價格”列進行自動填充。

在F3單元格中輸入公式=VLOOKUP(E3,$K$3:$L$5,2,0)，注意絕對引用，然后進行自動填充。

【數(shù)組公式】4. 使用數(shù)組公式，根據(jù)Sheet1中“醫(yī)院病人護理統(tǒng)計表”中的“入住時間”列和“出院時間”列中的數(shù)據(jù)計算護理天數(shù)，并把結(jié)果保存在“護理天數(shù)”列中。

選中“護理天數(shù)”列，在公式編輯欄輸入=G3:G30-D3:D30，然后同時摁Ctrl+Shift+Enter。

【數(shù)組公式】5.?使用數(shù)組公式，根據(jù)Sheet1中“醫(yī)院病人護理統(tǒng)計表”中的“護理價格”列和“護理天數(shù)”列，對病人的護理費用進行計算，并把結(jié)果保存在“護理費用”列中。

選中“護理費用”列，在公式編輯欄輸入=F3:F30*H3:H30，然后同時摁Ctrl+Shift+Enter。

【數(shù)據(jù)庫函數(shù)】6. 使用數(shù)據(jù)庫函數(shù)，按以下要求計算。

在N13中輸入=DCOUNTA(A2:I30,3,K8:M9)，然后摁回車。

在N22中輸入=DSUM(A2:I30,9,K17:K18)，然后摁回車。