第十六題 圖書訂購信息表

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

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

【邏輯函數(shù)】3. 使用IF和MID函數(shù)，根據(jù)Sheet1中的“圖書訂購信息表”中的“學(xué)號(hào)”列對(duì)“所屬學(xué)院”列進(jìn)行填充。要求：根據(jù)每位學(xué)生學(xué)號(hào)的第七位填充對(duì)應(yīng)的“所屬學(xué)院”。

在C3單元格中輸入=IF(MID(A3,7,1)="1","計(jì)算機(jī)學(xué)院",IF(MID(A3,7,1)="0","電子信息學(xué)院"))，然后進(jìn)行自動(dòng)填充。

【統(tǒng)計(jì)函數(shù)】4. 使用COUNTBLANK函數(shù)，對(duì)Sheet1中的“圖書訂購信息表”中的“訂書種類數(shù)”列進(jìn)行填充。

在H3單元格中輸入=4-COUNTBLANK(D3:G3)，然后進(jìn)行自動(dòng)填充。

【簡(jiǎn)單公式】5. 使用公式，對(duì)Sheet1中的“圖書訂購信息表”中的“訂書金額（元）”列進(jìn)行填充。

在I3單元格中輸入公式=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$6，注意絕對(duì)引用，然后進(jìn)行自動(dòng)填充。

【統(tǒng)計(jì)函數(shù)】6. 使用統(tǒng)計(jì)函數(shù)，根據(jù)Sheet1中“圖書訂購信息表”的數(shù)據(jù)，統(tǒng)計(jì)訂書金額大于100元的學(xué)生人數(shù)，將結(jié)果保存在Sheet1的M9單元格中。

在M9單元格中輸入公式=COUNTIF(I3:I50,">100")，然后摁回車。

第十七題 學(xué)生成績(jī)表

【四舍五入】1. 在Sheet5中，使用函數(shù)，將B1中的時(shí)間四舍五入到最接近的15分鐘的倍數(shù)，結(jié)果存放在C1單元格中。

在C1單元格中輸入=ROUND(B1*96,0)/96，然后摁回車。

*一天24小時(shí)由96個(gè)15分鐘組成。

【數(shù)組公式】3. 在Sheet1“學(xué)生成績(jī)表”中，使用REPLACE函數(shù)和數(shù)組公式，將原學(xué)號(hào)轉(zhuǎn)變?yōu)樾聦W(xué)號(hào)并填入“新學(xué)號(hào)”列中。

選中“新學(xué)號(hào)”列，在公式編輯欄輸入=REPLACE(A3:A30,5,0,5)，然后同時(shí)摁Ctrl+Shift+Enter。

【邏輯函數(shù)】4. 使用IF函數(shù)和邏輯函數(shù)，對(duì)Sheet1“學(xué)生成績(jī)表”中的“結(jié)果1”和“結(jié)果2”列進(jìn)行填充。填充的內(nèi)容根據(jù)以下條件確定。

在F3單元格中輸入=IF(OR(AND(D3="男",E3<14),AND(D3="女",E3<16)),"合格","不合格")，然后進(jìn)行自動(dòng)填充。

在H3單元格中輸入=IF(OR(AND(D3="男",G3>7.5),AND(D3="女",G3>5.5)),"合格","不合格")，然后進(jìn)行自動(dòng)填充。

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

在K4單元格中輸入=MIN(E3:E30)，然后摁回車。

在K5單元格中輸入=COUNTIF(F3:F30,"合格")，然后摁回車。

【財(cái)務(wù)函數(shù)】6. 根據(jù)Sheet2中的貸款情況，使用財(cái)務(wù)函數(shù)對(duì)貸款償還金額進(jìn)行計(jì)算。

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

PMT(rate, nper, pv, [fv], [type])

在E3單元格中輸入=IPMT(B4/12,9,B3*12,B2)，然后摁回車。

IPMT(rate, per, nper, pv, [fv], [type])

第十八題?員工資料表

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

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

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

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

【文本函數(shù)】3. 僅使用MID函數(shù)和CONCATENATE函數(shù)，對(duì)Sheet1中“員工資料表”的“出生日期”列進(jìn)行填充。

在G3單元格中輸入=CONCATENATE(MID(E3,7,4),"年",MID(E3,11,2),"月",MID(E3,13,2),"日")，然后摁回車。

CONCATENATE(text1, [text2], ...)

【查詢函數(shù)】4. 根據(jù)Sheet1中“職務(wù)補(bǔ)貼率表”的數(shù)據(jù)，使用VLOOKUP函數(shù)，對(duì)“員工資料表”中的“職務(wù)補(bǔ)貼率”列進(jìn)行自動(dòng)填充。

在J3單元格中輸入=VLOOKUP(H3,$A$3:$B$6,2,0)，注意絕對(duì)引用，然后進(jìn)行自動(dòng)填充。

【數(shù)組公式】5. 使用數(shù)組公式，在Sheet1中對(duì)“員工資料表”的“工資總額”列進(jìn)行計(jì)算，并將計(jì)算結(jié)果保存在“工資總額”列。

選中“工資總額”列，在公式編輯欄輸入=I3:I38*(1+J3:J38)，然后同時(shí)摁Ctrl+Shift+Enter。

【財(cái)務(wù)函數(shù)】6. 在Sheet2中，根據(jù)“固定資產(chǎn)情況表”，使用財(cái)務(wù)函數(shù)，對(duì)以下條件進(jìn)行計(jì)算。

在E2單元格中輸入=SLN(B2,B3,B4*365)，然后摁回車。

SLN?(cost, salvage, life)

在E3單元格中輸入=SLN(B2,B3,B4*12)，然后摁回車。

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

第十九題 公司員工人事信息表

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

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

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

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

【文本函數(shù)】3. 使用大小寫轉(zhuǎn)換函數(shù)，根據(jù)Sheet1中“公司人事信息表”的“編號(hào)”列，對(duì)“新編號(hào)”列進(jìn)行填充。

在B3單元格中輸入=UPPER(A3)，然后進(jìn)行自動(dòng)填充。

UPPER(text)

【文本函數(shù)】4. 使用文本函數(shù)和時(shí)間函數(shù)，根據(jù)Sheet1中“公司人事信息表”的“身份證號(hào)碼”列，計(jì)算用戶的年齡，并保存在“年齡”列中。

在F3單元格中輸入=YEAR(TODAY())-MID(G3,7,4)，然后進(jìn)行自動(dòng)填充。

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

在E31單元格中輸入=DGET(A2:J27,3,L3:M4)，然后摁回車。

【文本函數(shù)】6. 使用函數(shù)，判斷Sheet1中L12和M12單元格中的文本字符串是否完全相同。

在N12單元格中輸入=EXACT(L12,M12)，然后摁回車。

第二十題 打印機(jī)備貨清單

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

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

【閏年公式】2. 在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. 使用IF函數(shù)，對(duì)Sheet1中的“界面”列，根據(jù)“打印機(jī)類型”列的內(nèi)容，進(jìn)行自動(dòng)填充。

在E3單元格輸入=IF(D3="點(diǎn)陣","D",IF(D3="噴墨","P",IF(D3="黑白激光","H",IF(D3="彩色激光","C","T"))))，然后進(jìn)行自動(dòng)填充。

【數(shù)組公式】4.?使用REPLACE函數(shù)和數(shù)組公式對(duì)“新貨號(hào)”列進(jìn)行填充。

選中“新貨號(hào)”列，在公式編輯欄輸入=REPLACE(A3:A189,1,3,"0233PRT")，然后同時(shí)摁Ctrl+Shift+Enter。

【查詢函數(shù)】5. 使用VLOOKUP函數(shù)對(duì)“供貨商”列進(jìn)行填充。

在I3單元格輸入=VLOOKUP(B3,$M$12:$N$29,2,0)，注意絕對(duì)引用，然后進(jìn)行自動(dòng)填充。

【數(shù)據(jù)庫函數(shù)】6. 使用數(shù)據(jù)庫函數(shù)統(tǒng)計(jì)廠牌為EPSON，兼容性為支持的型號(hào)總數(shù)（不計(jì)空白型號(hào)）。

在N38單元格輸入=DCOUNTA(A2:F189,F2,M35:N36)，然后摁回車。

【自定義序列】7. 將Sheet1中的“打印機(jī)備貨清單”復(fù)制到Sheet2中，然后依照打印機(jī)類型重新排序。

復(fù)制后，依次進(jìn)行如下操作：