之前跟大家分享過智能考勤表的制作，原來的考勤表可以自動識別當月天數(shù)，周末自動填充顏色，并且考勤表的表頭可以自動更新。今天分享的是Excel智能考勤表（升級版），就是在之前的基礎(chǔ)上，添加考勤信息自動統(tǒng)計匯總：缺勤天數(shù)、出勤天數(shù)、是否滿勤、缺勤/出勤天數(shù)前N名等功能，告別手動統(tǒng)計，簡單又高效！

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

Excel智能考勤表（升級版）分屏顯示效果：

下面上干貨，操作步驟：

第一步：制作Excel智能考勤表

具體制作步驟之前介紹過，可以看教程《Excel智能考勤表，太實用了，一個表格可以用到退休！》，今天就不再詳細介紹了。

第二步：根據(jù)智能考勤表出勤明細統(tǒng)計相關(guān)數(shù)據(jù)

1、缺勤天數(shù)

在目標單元格輸入公式：

=SUMPRODUCT((考勤表明細表!$B$7:$AF$14="×")*(考勤表明細表!$A$7:$A$14=A4))

然后下拉填充數(shù)據(jù)即可

解讀：

其實，上面公式就是利用SUMPRODUCT函數(shù)跨工作表多條件計數(shù)。

①(考勤表明細表!$B$7:$AF$14="×")：判斷考勤表數(shù)據(jù)區(qū)域中是否是字符"×"，結(jié)果是一個由True和False組成的數(shù)組，True表示該單元格等于"×"（缺勤），F(xiàn)alse表示出勤。“考勤表明細表!$B$7:$AF$14”數(shù)據(jù)區(qū)域要絕對引用，因為要下拉填充數(shù)據(jù)，這個數(shù)據(jù)區(qū)域不能改變。

②(考勤表明細表!$A$7:$A$14=A4)：這部分是檢查“考勤表明細表!$A$7:$A$14”這個區(qū)域的員工考勤名稱每個單元格是否等于A4單元格中的值。結(jié)果是一個由True和False組成的數(shù)組，符號條件返回True，否則返回False。同樣A7:A14數(shù)據(jù)區(qū)域要決定引用。

③最后，SUMPRODUCT函數(shù)會計算上述兩個數(shù)組的對應(yīng)元素的乘積。在乘積中，True被視為1，F(xiàn)alse被視為0。因此，只有當兩個條件都為True時，對應(yīng)的乘積才為1，否則為0。

多條件計數(shù)萬能公式：

=SUMPRODUCT((條件數(shù)據(jù)區(qū)域1=條件1)*(條件數(shù)據(jù)區(qū)域2=條件2)*(條件數(shù)據(jù)區(qū)域N=條件N))

2、出勤天數(shù)

在目標單元格輸入公式：

=SUMPRODUCT((考勤表明細表!$B$7:$AF$14="√")*(考勤表明細表!$A$7:$A$14=A4))

然后下拉填充數(shù)據(jù)即可

解讀：

原理跟缺勤天數(shù)公式一樣，只是統(tǒng)計出勤數(shù)據(jù)。

3、是否滿勤

在目標區(qū)域輸入公式：

=IF(B4=0,"√","×")

然后下拉填充數(shù)據(jù)即可

解讀：

判斷是否滿勤邏輯也很簡單，只有缺勤天數(shù)等于0就是滿勤，否則就是缺勤。

4、出勤天數(shù)前3名，并且顯示出勤天數(shù)

在目標區(qū)域輸入公式：

=TAKE(SORT(HSTACK(A4:A11,C4:C11),2,-1),3,2)

然后下拉填充數(shù)據(jù)即可

解讀：

①先利用HSTACK函數(shù)把匯總表格中的A4:A11（姓名）和C4:C11（出勤天數(shù)）兩個數(shù)據(jù)區(qū)域按水平方向重新合并到一起。

②再利用SORT函數(shù)對數(shù)據(jù)按出勤天數(shù)進行降序排列（-1代表降序，1代表升序），就是根據(jù)出勤天數(shù)從高到低排序。

③最后利用TAKE函數(shù)獲取指定位置的數(shù)據(jù)，按行獲取前3條數(shù)據(jù)，按列獲取前2列數(shù)據(jù)，這樣就獲得了出勤天數(shù)前3名姓名和出勤天數(shù)了。

5、缺勤天數(shù)前3名，并且顯示缺勤天數(shù)

在目標區(qū)域輸入公式：

=TAKE(SORT(HSTACK(A4:A11,B4:B11),2,-1),3,2)

然后下拉填充數(shù)據(jù)即可

解讀：

公式原理跟統(tǒng)計“出勤天數(shù)前3名，并且顯示出勤天數(shù)”一樣，就不再重復(fù)說明了。

推薦閱讀：excel怎么判斷法定節(jié)假日? excel公式自動判定日期是否節(jié)假日的技巧

最新評論