第二十四題 員工工資表

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

=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0)),TRUE,FALSE)

*編者注：里面的TODAY()也可以與NOW()進(jìn)行互換，在這里沒有影響。

【查詢函數(shù)】3.在員工工資表，使用Vlookup函數(shù)和Hlookup函數(shù)，填寫“基本工資”列：基本工資=崗位工資+學(xué)歷津貼。

=VLOOKUP(C3,$L$15:$M$17,2,0)+HLOOKUP(E3,$O$14:$Q$15,2,0)

*編者注：豎向查找用Vlookup，橫向查找用Hlookup。

【數(shù)組公式】4.使用數(shù)組公式計算“應(yīng)發(fā)工資”列，應(yīng)發(fā)工資=基本工資+績效工資。

*編者注：同時摁住Ctrl+Shift，不松手的情況下再去按Enter。

【數(shù)據(jù)庫函數(shù)】5.使用數(shù)據(jù)庫函數(shù)統(tǒng)計：（1）業(yè)務(wù)代表中績效工資最高的（填入單元格P21）和應(yīng)發(fā)工資最低的（填入單元格P22）；（2）所有女業(yè)務(wù)代表的應(yīng)發(fā)工資總額（填入單元格P23）。

在P21單元格中填入：=DMAX(A2:I39,7,G2:G39)

在P22單元格中填入：=DMIN(A2:I39,8,H2:H39)

在P23單元格中填入：=DSUM(A2:I39,8,R19:S20)

*編者注：使用dsum函數(shù)時，和高級篩選一樣，需要自己創(chuàng)建條件區(qū)域，在這里條件區(qū)域的范圍就是dsum函數(shù)的第3個參數(shù)。

【邏輯函數(shù)】6.使用邏輯函數(shù)and和or判斷是否是高級職位員工、且應(yīng)發(fā)工資大于等于10000，填入“是”或“否”高級職位指項(xiàng)目經(jīng)理和銷售經(jīng)理。

=IF(AND(OR(C3="項(xiàng)目經(jīng)理",C3="銷售經(jīng)理"),H3>10000),"是","否")

第二十五題 員工信息表

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

=ROUND(B1*96,0)/96

【文本函數(shù)】3.使用REPLACE函數(shù)，對Sheet1中“員工信息表”的員工代碼進(jìn)行升級。

=REPLACE(B3,3,0,0)

*編者注：replace函數(shù)第二個參數(shù)指的是從第幾位文本或數(shù)值開始進(jìn)行替換，第三個參數(shù)指的是需要替換掉原來單元格多少位文本或數(shù)值，第三個參數(shù)寫0即表示不進(jìn)行替換，直接進(jìn)行增加。

【時間函數(shù)】4.使用時間函數(shù)，計算Sheet1中“員工信息表”的“年齡”和“工齡”列。

年齡：=YEAR("2013-5-1")-YEAR(E3)

工齡：=YEAR("2013-5-1")-YEAR(G3)

*編者注：需要將這一列單元格格式設(shè)置為“常規(guī)”。

【統(tǒng)計函數(shù)】5.使用統(tǒng)計函數(shù)，根據(jù)Sheet1中“員工信息表”的數(shù)據(jù)，對以下條件進(jìn)行統(tǒng)計。

在N3單元格中填入：=COUNTIF(D2:D66,"男")

在N4單元格中填入：=COUNTIF(I2:I66,"高級工程師")

在N5單元格中填入：=COUNTIF(H2:H66,">=10")

【邏輯函數(shù)】6.使用邏輯函數(shù)，判斷員工是否有資格評“高級工程師”。

=IF(AND(H3>20,I3="工程師"),TRUE,FALSE)