Chấm công tự động bằng excel (4 người xem)

[valongtano]

Kính gửi các anh chị trên diễn đàn,
Theo các hướng dẫn đã được các anh chị đăng trên diễn đàn này, tôi xây dựng được một bảng chấm công tự động, trong đó tự đếm số ngày đi làm, số ngày nghỉ, số ngày phép, số ngày đi muộn về sớm, cuối cùng có báo cáo tổng hợp (ở sheet cuối cùng). Tuy nhiên, tôi còn một số khó khăn như sau:
- Thứ nhất: trong một số trường hợp, trong một ngày, nhân viên có tới hai giá trị chấm công (ý nói là phải dùng 2 ký hiệu mới diễn tả được trạng thái đi làm của người đó) khi đó bảng chấm công của tôi không tự động hiện thị được.
- Thứ hai: mỗi tháng tôi phải cộng bằng tay để tính số ngày đi làm (ví dụ trong tháng Giêng là 22 ngày đi làm, tháng Hai là 20 ngày). Vậy có cách nào tính tự động được không,
Tôi tải bảng chấm công lên để anh chị xem và cho ý kiến.
Trân trọng cảm ơn

 

[Phan Thế Hiệp]

Kính gửi các anh chị trên diễn đàn,
Theo các hướng dẫn đã được các anh chị đăng trên diễn đàn này, tôi xây dựng được một bảng chấm công tự động, trong đó tự đếm số ngày đi làm, số ngày nghỉ, số ngày phép, số ngày đi muộn về sớm, cuối cùng có báo cáo tổng hợp (ở sheet cuối cùng). Tuy nhiên, tôi còn một số khó khăn như sau:
- Thứ nhất: trong một số trường hợp, trong một ngày, nhân viên có tới hai giá trị chấm công (ý nói là phải dùng 2 ký hiệu mới diễn tả được trạng thái đi làm của người đó) khi đó bảng chấm công của tôi không tự động hiện thị được.
- Thứ hai: mỗi tháng tôi phải cộng bằng tay để tính số ngày đi làm (ví dụ trong tháng Giêng là 22 ngày đi làm, tháng Hai là 20 ngày). Vậy có cách nào tính tự động được không,
Tôi tải bảng chấm công lên để anh chị xem và cho ý kiến.
Trân trọng cảm ơn

Bạn không cho ví dụ kiểu chấm công có nhiều ký tự, thì ai giúp bạn được!?

Ngày công của tháng (trừ thứ 7, CN và ngày lễ)

B6=NETWORKDAYS(DATE($B$8,$B$7,1),EOMONTH(DATE($B$8,$B$7,1),0),Setting!$A$7:$A$20)

Xem file kèm.
Thân.

 

[valongtano]

Bạn không cho ví dụ kiểu chấm công có nhiều ký tự, thì ai giúp bạn được!?

Ngày công của tháng (trừ thứ 7, CN và ngày lễ)

B6=NETWORKDAYS(DATE($B$8,$B$7,1),EOMONTH(DATE($B$8,$B$7,1),0),Setting!$A$7:$A$20)

Xem file kèm.
Thân.

Cám ơn bạn Phan Thế Hiệp về code.
Về ví dụ chấm nhiều ký tự, xin lỗi tôi chưa hỏi rõ. Ý là thế này: trong cùng một ngày, một người vừa đi muộn (ký hiệu là m), vừa về sớm (ký hiệu là s) thì tôi mà đánh dấu cả m và s vào thì sẽ không tự động cộng thêm vào cột thống kê số lần đi muộn về sơm được,

 

[Phan Thế Hiệp]

Cám ơn bạn Phan Thế Hiệp về code.
Về ví dụ chấm nhiều ký tự, xin lỗi tôi chưa hỏi rõ. Ý là thế này: trong cùng một ngày, một người vừa đi muộn (ký hiệu là m), vừa về sớm (ký hiệu là s) thì tôi mà đánh dấu cả m và s vào thì sẽ không tự động cộng thêm vào cột thống kê số lần đi muộn về sơm được,

Thì bạn cho dữ liệu mẫu vào file đi, tùy theo tình trạng dữ liệu của bạn như thế nào, lúc đó mới có hàm tương thích.
Vd: giả dụ nhập cho NV: ngày 02/07/2018: "0.5m - 1s", tức 0.5h đi muộn, 1h về sớm.
Càng có nhiều dữ liệu mẫu, với áp dụng nhiều ký hiệu, và kết quả tính tay của bạn thì anh em GPE "mới hiểu" bạn muốn điều gì.
Thân.

 

[valongtano]

Thì bạn cho dữ liệu mẫu vào file đi, tùy theo tình trạng dữ liệu của bạn như thế nào, lúc đó mới có hàm tương thích.
Vd: giả dụ nhập cho NV: ngày 02/07/2018: "0.5m - 1s", tức 0.5h đi muộn, 1h về sớm.
Càng có nhiều dữ liệu mẫu, với áp dụng nhiều ký hiệu, và kết quả tính tay của bạn thì anh em GPE "mới hiểu" bạn muốn điều gì.
Thân.

Rất cảm động vì bạn trả lời nhanh thế.
Tôi gửi lại dữ liệu để minh họa:
Anh Nguyễn Văn A có một ngày vừa đi muộn (ký hiệu m), vừa về sớm (ký hiệu s), khi đó tôi đánh dấu là m, s trong cùng 1 ô. và ở cột tổng hợp số lần đi muộn tôi phải gõ +1 vào ô công thức để bảng tính hiểu là có 1 lần đi muộn về sớm.

Nhân đây, bạn hướng dẫn giúp tôi sử dụng hàm Conditional Formatting để tự tô màu các ngày nghỉ lễ theo số ngày nghỉ lễ mà bạn đã giúp ghi ở sheet Setting.
Cám ơn bạn

 

[dazkangel]

Rất cảm động vì bạn trả lời nhanh thế.
Tôi gửi lại dữ liệu để minh họa:
Anh Nguyễn Văn A có một ngày vừa đi muộn (ký hiệu m), vừa về sớm (ký hiệu s), khi đó tôi đánh dấu là m, s trong cùng 1 ô. và ở cột tổng hợp số lần đi muộn tôi phải gõ +1 vào ô công thức để bảng tính hiểu là có 1 lần đi muộn về sớm.

Nhân đây, bạn hướng dẫn giúp tôi sử dụng hàm Conditional Formatting để tự tô màu các ngày nghỉ lễ theo số ngày nghỉ lễ mà bạn đã giúp ghi ở sheet Setting.
Cám ơn bạn

Đi muộn về sớm thì sửa lại 1 chút:

=COUNTIF($D11:$AH11,"*"&BA$9&"*")

Hàm condition:

=1-NETWORKDAYS(D$9,D$9,Setting!$A$7:$A$10)

 

[valongtano]

Đi muộn về sớm thì sửa lại 1 chút:

=COUNTIF($D11:$AH11,"*"&BA$9&"*")

Hàm condition:

=1-NETWORKDAYS(D$9,D$9,Setting!$A$7:$A$10)

Thế mà tôi cứ loay hoay vơi hàm IF
Code
=IF(D$10="Setting!Setting!$B$6:$B$17";1;0)

Nhưng code bạn cho không hoạt động bạn ơi.

 

[dazkangel]

Thế mà tôi cứ loay hoay vơi hàm IF
Code
=IF(D$10="Setting!Setting!$B$6:$B$17";1;0)

Nhưng code bạn cho không hoạt động bạn ơi.

bạn phải cho biết code nào không chạy chứ ??
hay kết quả không đúng ?

 

[valongtano]

bạn phải cho biết code nào không chạy chứ ??
hay kết quả không đúng ?

Code này:
=1-NETWORKDAYS(D$9,D$9,Setting!$A$7:$A$10)

 

[Phan Thế Hiệp]

Rất cảm động vì bạn trả lời nhanh thế.
Tôi gửi lại dữ liệu để minh họa:
Anh Nguyễn Văn A có một ngày vừa đi muộn (ký hiệu m), vừa về sớm (ký hiệu s), khi đó tôi đánh dấu là m, s trong cùng 1 ô. và ở cột tổng hợp số lần đi muộn tôi phải gõ +1 vào ô công thức để bảng tính hiểu là có 1 lần đi muộn về sớm.

Nhân đây, bạn hướng dẫn giúp tôi sử dụng hàm Conditional Formatting để tự tô màu các ngày nghỉ lễ theo số ngày nghỉ lễ mà bạn đã giúp ghi ở sheet Setting.
Cám ơn bạn

1/ Nếu ô dữ liệu chỉ chứa ký hiệu đơn như "h", dùng (bạn đã thực hiện):

=COUNTIF($D11:$AH11,"h")​

2/ Nếu ô dữ liệu chỉ chứa ký hiệu đơn như "P" hoặc "P/2", lại tính toán cho một hoặc nửa ngày công, dùng:

=SUM(COUNTIF($D11:$AH11,{"p","p/2"})*{1,0.5})​

3/ Nếu ô dữ liệu chứa nhiều ký hiệu đơn xen kẽ ký hiệu khác, có tính toán cho một hoặc nửa ngày công dùng:

=SUMPRODUCT(ISNUMBER(SEARCH(","&{"x";"x/2"}&",",","&SUBSTITUTE($D11:$AH11," ","")&","))*{1;0.5})​

4/ Nếu ô dữ liệu chứa nhiều ký hiệu đơn xen kẽ ký hiệu khác, không tính toán cho một hoặc nửa ngày công dùng:

=COUNT(INDEX(SEARCH(","&$K$6&",",","&SUBSTITUTE($D11:$AH11," ","")&","),))​

​

Dạng tổng quát (1 công thức dùng chung cho các cột) như sau:​

AJ11=SUMPRODUCT(ISNUMBER(SEARCH(","&AJ$9&{"";"/2"}&",",","&SUBSTITUTE($D11:$AH11," ","")&","))*{1;0.5})

Enter, fill qua phải, rồi fill cả hàng xuống dưới.​

​

Đã xóa bớt các cột phụ không cần thiết mà bạn đã ẩn đi.​

​

5/ Conditional Formatting: (bạn vào công cụ này xem sẽ dễ hiểu hơn)

• Ngày T7 và chủ nhật: =WEEKDAY(D$10,2)>5
• Ngày lễ theo danh sách liệt kê: =COUNTIF(Setting!$A$3:$A$16,D$10)*(D$10<>"")

6/ Định dạng các chữ: "Thứ Hai, Thứ Ba,...., Chủ Nhật" tại D10:AH10:

Đứng tại D10, nhấn Ctrl+1, thấy Tab Number\Custom có định dạng: [$-vi-41]dddd​

Thân.

 

[SA_DQ]

Xin fép góp í với bạn về thiết kế trang tính BCC (bảng chấm công) vài điểm như sau:
1./ Bạn tham khảo ở đây: https://webketoan.com/threads/2976642-bang-cham-cong-mau-bao-gom-ngay-nghi-le-trong-1-nam/
Trong đó có cả dịnh dạng fân biệt đến cả danh sách 9 ngày lễ hàng năm theo qui định của CF;
2./ Mã NV của bạn không có tính tương tác cao giữa người quản lý lao động & người lao đông;
Mình xin gợi í sau:

'
Mã     Họ Tên    '
DJF00  Dương Đông
LFQ00  Lê Đình Quân
LTT00  Lương Thị Thắm
NKD00  Ngô Khôn Diệp
NTN00  Ngô Thị Nga
NVN00  Ngô Võ Viết Nam
NTN01  Nguyễn Thị Nguyệt
PKT00  Phạm Thị Kim Tiến
TVT00  Trần Văn Thìn
TTA00  Trần Thái Ẩn
. . .    . . . . . .

3./ Các kí hiệu chấm công của cơ quan bạn còn 03 chỗ cần lưu tâm như sau:

Vắng không phép          k
Đi muộn không xin phép   mk
Về sớm không xin phép    sk

Kí hiệu 'K' (nên xài chữ in thay vì chữ thường) đã xài ở 3 nơi sẽ đễ dẫn đến sai trong tính toán hay đếm
Cũng vậy, các kí tự 'M' & "S' Bạn đã xài trong các loại công khác;
Nếu được ta nên thay #: Vắng không fép;
$: Sớm & muộn sẽ là @
Một khi xài như gợi í, vạn hoàn toàn có thể nhờ hàm COUNTIF() giúp bạn trong nhiều tính toán

(Trên đây chỉ là những gợi í; không buộc bạn fải tuân thủ)

 

[valongtano]

1/ Nếu ô dữ liệu chỉ chứa ký hiệu đơn như "h", dùng (bạn đã thực hiện):

=COUNTIF($D11:$AH11,"h")​

2/ Nếu ô dữ liệu chỉ chứa ký hiệu đơn như "P" hoặc "P/2", lại tính toán cho một hoặc nửa ngày công, dùng:

=SUM(COUNTIF($D11:$AH11,{"p","p/2"})*{1,0.5})​

3/ Nếu ô dữ liệu chứa nhiều ký hiệu đơn xen kẽ ký hiệu khác, có tính toán cho một hoặc nửa ngày công dùng:

=SUMPRODUCT(ISNUMBER(SEARCH(","&{"x";"x/2"}&",",","&SUBSTITUTE($D11:$AH11," ","")&","))*{1;0.5})​

4/ Nếu ô dữ liệu chứa nhiều ký hiệu đơn xen kẽ ký hiệu khác, không tính toán cho một hoặc nửa ngày công dùng:

=COUNT(INDEX(SEARCH(","&$K$6&",",","&SUBSTITUTE($D11:$AH11," ","")&","),))​

​

Dạng tổng quát (1 công thức dùng chung cho các cột) như sau:​

AJ11=SUMPRODUCT(ISNUMBER(SEARCH(","&AJ$9&{"";"/2"}&",",","&SUBSTITUTE($D11:$AH11," ","")&","))*{1;0.5})

Enter, fill qua phải, rồi fill cả hàng xuống dưới.​

​

Đã xóa bớt các cột phụ không cần thiết mà bạn đã ẩn đi.​

​

5/ Conditional Formatting: (bạn vào công cụ này xem sẽ dễ hiểu hơn)

• Ngày T7 và chủ nhật: =WEEKDAY(D$10,2)>5
• Ngày lễ theo danh sách liệt kê: =COUNTIF(Setting!$A$3:$A$16,D$10)*(D$10<>"")

6/ Định dạng các chữ: "Thứ Hai, Thứ Ba,...., Chủ Nhật" tại D10:AH10:

Đứng tại D10, nhấn Ctrl+1, thấy Tab Number\Custom có định dạng: [$-vi-41]dddd​

Thân.

Cám ơn bạn, bạn thật nhiệt tình quá.
Mình không ngờ công thức theo kiểu của bạn lại làm cái bảng gọn hẳn, trước mình phải làm bao nhiêu cột, rồi ẩn đi để đỡ vướng.

Bài đã được tự động gộp: 11/7/18

Xin fép góp í với bạn về thiết kế trang tính BCC (bảng chấm công) vài điểm như sau:
1./ Bạn tham khảo ở đây: https://webketoan.com/threads/2976642-bang-cham-cong-mau-bao-gom-ngay-nghi-le-trong-1-nam/
Trong đó có cả dịnh dạng fân biệt đến cả danh sách 9 ngày lễ hàng năm theo qui định của CF;
2./ Mã NV của bạn không có tính tương tác cao giữa người quản lý lao động & người lao đông;
Mình xin gợi í sau:

'
Mã     Họ Tên    '
DJF00  Dương Đông
LFQ00  Lê Đình Quân
LTT00  Lương Thị Thắm
NKD00  Ngô Khôn Diệp
NTN00  Ngô Thị Nga
NVN00  Ngô Võ Viết Nam
NTN01  Nguyễn Thị Nguyệt
PKT00  Phạm Thị Kim Tiến
TVT00  Trần Văn Thìn
TTA00  Trần Thái Ẩn
. . .    . . . . . .

3./ Các kí hiệu chấm công của cơ quan bạn còn 03 chỗ cần lưu tâm như sau:

Vắng không phép          k
Đi muộn không xin phép   mk
Về sớm không xin phép    sk

Kí hiệu 'K' (nên xài chữ in thay vì chữ thường) đã xài ở 3 nơi sẽ đễ dẫn đến sai trong tính toán hay đếm
Cũng vậy, các kí tự 'M' & "S' Bạn đã xài trong các loại công khác;
Nếu được ta nên thay #: Vắng không fép;
$: Sớm & muộn sẽ là @
Một khi xài như gợi í, vạn hoàn toàn có thể nhờ hàm COUNTIF() giúp bạn trong nhiều tính toán

(Trên đây chỉ là những gợi í; không buộc bạn fải tuân thủ)

Cám ơn bạn.
Cho mình hỏi về mã nhân viên mà bạn gợi ý: cách đặt mã theo bạn hình như là lấy tên tắt của nhân viên, nhưng thế lại không phản ánh được vị trí làm việc của họ phải không bạn? Với lại đã ghi tắt tên rồi thì còn thêm số đằng sau để làm gì?
(đây là mình hỏi trên tinh thần không hiểu thì hỏi, không phải là hỏi xách mé đâu, rất tôn trọng bạn đã gợi ý)

 

[SA_DQ]

Cho mình hỏi về mã nhân viên mà bạn gợi ý: cách đặt mã theo bạn hình như là lấy tên tắt của nhân viên, (1) nhưng thế lại không phản ánh được vị trí làm việc của họ phải không bạn?
(2) Với lại đã ghi tắt tên rồi thì còn thêm số đằng sau để làm gì?

(1) Vị trí công tác trong CQ không là bất di bất dịch & sẽ càng thay đổi trong xu hướng luan chuyển CB của ta hiện nay;
Mã NV hơn cả số CMND là ngắn gọn, xúc tích & quan trọng là tính tương tác cao;
Một khi ta thuộc qui luật tạo mã thì khi nghe tên (cả họ nữa) ta sẽ mường tượng ra 3 kí tự đầu tiên của mã NV của người í.

(2) 2 Kí số fía sau để ta dễ trong sử dụng 1 danh sách đông, như 1 trượng học có gần 5 ngàn HS
& tên người hiện tại của nước ta rất nhiều trùng nhau tên họ, nhất là những tên đẹp như

NVH00  Ngô Thị Việt Hoa
NVH01 Nhâm Vĩnh Hoàn
NVH02  Nguyễn Viết Hòa
NVH9A  Ngô Thị Vỹ Hoài
. . . . . . . .

Cho nên 2 kí số (hay kí tự cuối đó cho fép ta hơn ngàn người có cùng 3 kí tự đầu giống nhau.

Mã như vậy tiện cho ta tìm kiếm 1 người cụ thể nào đó trong 1 danh sách dài lê thê trong 1 tập thể lớn (sinh viên, chiến sỹ của sư đoàn,. . . )

Nói thêm: Mình đã có maco để tạo mã như vậy cho hàng loạt cá nhân trong 1 tập thể đông nào đó;