值班安排與出勤統(tǒng)計(jì)

第三章值班安排與出勤統(tǒng)計(jì)主要知識(shí)點(diǎn)：1、編輯和設(shè)置拼音內(nèi)容2、SUMPRODUCT()函數(shù)使用3、COUNTIF（）函數(shù)使用4、TIMEVALUE()函數(shù)使用5、創(chuàng)建和設(shè)置數(shù)據(jù)透視表值班安排與出勤統(tǒng)計(jì)第1頁(yè)制作員工出勤情況記錄表1、制作基本數(shù)據(jù)表值班安排與出勤統(tǒng)計(jì)第2頁(yè)制作員工出勤情況記錄表2、編輯拼音單擊“字體”選項(xiàng)組中“顯示或隱藏拼音字段”下拉按鈕，在展開列表中單擊“編輯拼音”選項(xiàng)值班安排與出勤統(tǒng)計(jì)第3頁(yè)制作員工出勤情況記錄表3、插入符號(hào)在拼音編輯框中輸入拼音，因?yàn)椴荒茌斎霂曊{(diào)字母，所以要設(shè)置插入符號(hào)在插入選項(xiàng)卡下單擊“特殊符號(hào)”選項(xiàng)組“符號(hào)”按鈕，在展開列表中單擊“更多”選項(xiàng)值班安排與出勤統(tǒng)計(jì)第4頁(yè)制作員工出勤情況記錄表4、計(jì)算遲到次數(shù)【條件】遲到：9:00--9:30TIMEVALUE返回由文本字符串所代表時(shí)間小數(shù)值TIMEVALUE(time_text)Time_text

文本字符串，代表以MicrosoftExcel時(shí)間格式表示時(shí)間比如本例D3單元格是否大于9:00時(shí)間能夠以下表示=D3>TIMEVALUE("9:00")D3單元格是否小于9:30時(shí)間能夠以下表示=D3<TIMEVALUE("9:30")值班安排與出勤統(tǒng)計(jì)第5頁(yè)制作員工出勤情況記錄表SUMPRODUCT（）在給定幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)元素相乘，并返回乘積之和。SUMPRODUCT(array1,array2,array3,...)數(shù)組參數(shù)必須含有相同維數(shù)，不然，函數(shù)SUMPRODUCT將返回錯(cuò)誤值#VALUE!。一、基本使用方法比如：=SUMPRODUCT(A1:B3,C1:D3)【3*2+4*7+8*6+6*7+1*5+9*3=156】值班安排與出勤統(tǒng)計(jì)第6頁(yè)制作員工出勤情況記錄表SUMPRODUCT（）二、用于多條件計(jì)數(shù)用數(shù)學(xué)函數(shù)SUMOPRODUCT計(jì)算符合2個(gè)及以上條件數(shù)據(jù)個(gè)數(shù)統(tǒng)計(jì)上面數(shù)據(jù)表中男、女性中分別是中一、中二人數(shù)。=SUMPRODUCT(($B$2:$B$11=$E2)*1,($C$2:$C$11=F$1)*1)值班安排與出勤統(tǒng)計(jì)第7頁(yè)制作員工出勤情況記錄表公式分解及分析①條件1——$B$2:$B$11=$E2在計(jì)算過程中，條件1是一個(gè)數(shù)組，返回多值，寫成公式以下：=$B$2:$B$11=$E2詳細(xì)操作：選中10個(gè)連續(xù)單元格，輸入上述公式后，按Ctrl+Shift+回車鍵確認(rèn)，返回10個(gè)邏輯判斷值——TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。這10個(gè)邏輯判斷值組成一個(gè)新由TRUE主FALSE組成數(shù)組1。②條件2——$C$2:$C$11=F$1與條件1相同，是一個(gè)數(shù)組，返回多值，寫成公式以下：=$C$2:$C$11=F$1詳細(xì)操作：一樣方法，選中對(duì)應(yīng)10連續(xù)單元格，輸入上述公式，按Ctrl+Shift+回車鍵確認(rèn)，返回10個(gè)邏輯判斷值——TRUE；FALSE；TRUE；TRUE；TRUE；FALSE；FALSE；TRUE；TRUE；TRUE。這10個(gè)邏輯判斷值組成另一個(gè)新由TRUE主FALSE組成數(shù)組2。=SUMPRODUCT({TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE}*1，{TRUE；FALSE；TRUE；TRUE；TRUE；FALSE；FALSE；TRUE；TRUE；TRUE}*1)=SUMPRODUCT({1;0;0;1;0;1;0;1;1;0},{1;0;1;1;1;0;0;1;1;1})=SUMPRODUCT({1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1})=SUMPRODUCT({1;0;0;1;0;0;0;1;1,0})=4

注意：TRUE*1=1，F(xiàn)ALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0。數(shù)組中用分號(hào)分隔，表示數(shù)組是一列數(shù)組，分號(hào)相當(dāng)于換行。兩個(gè)數(shù)組相乘是同一行對(duì)應(yīng)兩個(gè)數(shù)相乘。值班安排與出勤統(tǒng)計(jì)第8頁(yè)制作員工出勤情況記錄表5、計(jì)算遲到次數(shù)在Z3單元格中輸入=SUMPRODUCT((D3:Y3>TIMEVALUE("9:00"))*1,(D3:Y3<TIMEVALUE("9:30"))*1)然后向下填充即可值班安排與出勤統(tǒng)計(jì)第9頁(yè)制作員工出勤情況記錄表5、計(jì)算早退次數(shù)在AA3單元格中輸入=SUMPRODUCT((D4:Y4>TIMEVALUE("17:00"))*1,(D4:Y4<TIMEVALUE("18:00"))*1)然后向下填充即可值班安排與出勤統(tǒng)計(jì)第10頁(yè)制作員工出勤情況記錄表6、計(jì)算曠工次數(shù)=SUMPRODUCT((D5:Y5>TIMEVALUE("9:30"))*1,(D5:Y5<1)*1)+SUMPRODUCT((D5:Y5<=TIMEVALUE("9:30"))*1,(D6:Y6<TIMEVALUE("17:00"))*1,(D6:Y6>0)*1)值班安排與出勤統(tǒng)計(jì)第11頁(yè)制作員工出勤情況記錄表7、計(jì)算事假次數(shù)COUNTIF計(jì)算區(qū)域中滿足給定條件單元格個(gè)數(shù)COUNTIF(range,criteria)range

是一個(gè)或多個(gè)要計(jì)數(shù)單元格，其中包含數(shù)字或名稱、數(shù)組或包含數(shù)字引用?？罩岛臀谋局祵⒈缓雎?。Criteria

為計(jì)算在內(nèi)條件，其形式可認(rèn)為數(shù)字、表達(dá)式、單元格引用或文本。例如，條件可以表示為32、"32"、">32"、"apples"或B4。=COUNTIF(D3:Y4,"事假")值班安排與出勤統(tǒng)計(jì)第12頁(yè)制作員工出勤情況記錄表8、計(jì)算病假次數(shù)=COUNTIF(D3:Y4,"病假")值班安排與出勤統(tǒng)計(jì)第13頁(yè)制作員工出勤統(tǒng)計(jì)表1、粘貼數(shù)據(jù)值班安排與出勤統(tǒng)計(jì)第14頁(yè)制作員工出勤統(tǒng)計(jì)表2、利用VLOOUKUP函數(shù)自動(dòng)計(jì)算出遲到、早退、曠工、病假、事假次數(shù)值班安排與出勤統(tǒng)計(jì)第15頁(yè)制作員工出勤統(tǒng)計(jì)表值班安排與出勤統(tǒng)計(jì)第16頁(yè)制作員工出勤統(tǒng)計(jì)表值班安排與出勤統(tǒng)計(jì)第17頁(yè)制作員工出勤統(tǒng)計(jì)表值班安排與出勤統(tǒng)計(jì)第18頁(yè)制作員工出勤情況記錄表值班安排與出勤統(tǒng)計(jì)第19頁(yè)制作員工出勤情況記錄表3、選中單元格區(qū)域H3：K14，在“開始”選項(xiàng)卡下單擊“數(shù)字”選項(xiàng)組中“會(huì)計(jì)數(shù)字格式”下拉按鈕，在展開列表中單擊“漢字（中國(guó)）”選項(xiàng)值班安排與出勤統(tǒng)計(jì)第20頁(yè)制作員工出勤情況記錄表4、計(jì)算規(guī)則1、遲到一次或早退扣除1%工資2、礦工一次扣除5%工資3、病假一次扣除當(dāng)日20%工資4、事假一次扣除當(dāng)日全部工資5、全勤獎(jiǎng)為當(dāng)月工資20%值班安排與出勤統(tǒng)計(jì)第21頁(yè)制作員工出勤情況記錄表5、計(jì)算應(yīng)扣工資值班安排與出勤統(tǒng)計(jì)第22頁(yè)制作員工出勤情況記錄表6、計(jì)算全勤獎(jiǎng)值班安排與出勤統(tǒng)計(jì)第23頁(yè)制作員工出勤情況記錄表7、計(jì)算實(shí)際工資值班安排與出勤統(tǒng)計(jì)第24頁(yè)創(chuàng)建數(shù)據(jù)透視表1、創(chuàng)建數(shù)據(jù)透視表選中單元格區(qū)域A2：A14，單擊“插入”選項(xiàng)卡下“表”選項(xiàng)組中“數(shù)據(jù)透視表”按鈕，在展開列表中單擊“數(shù)據(jù)透視表”選項(xiàng)值班安排與出勤統(tǒng)計(jì)第25頁(yè)創(chuàng)建數(shù)據(jù)透視表2、設(shè)置創(chuàng)建選項(xiàng)值班安排與出勤統(tǒng)計(jì)第26頁(yè)創(chuàng)建數(shù)據(jù)透視表3、生成數(shù)據(jù)透視表值班安