Tính số số công ngày thường và công ngày cuối tuần trong file chấm vân tay Exel và thống kê các ngày giải trình (3 người xem)

[mountain84]

Thân gửi các bạn!

Mình có bảng chấm vân tay bằng Exel. Mình muốn tính số công ngày thường ; ngày cuối tuần và thống kê các ngày cần giải trình vắng hoặc là chấm 1 chiều. Nhờ các bạn chỉ giúp mình. Chi tiết như trong File đính kèm

Rất mong sớm nhận được sự giúp đỡ của các bạn. Chân thành cảm ơn!

 

[Phan Thế Hiệp]

Thân gửi các bạn!
Mình có bảng chấm vân tay bằng Exel. Mình muốn tính số công ngày thường ; ngày cuối tuần và thống kê các ngày cần giải trình vắng hoặc là chấm 1 chiều. Nhờ các bạn chỉ giúp mình. Chi tiết như trong File đính kèm
Rất mong sớm nhận được sự giúp đỡ của các bạn. Chân thành cảm ơn!

1. Tính công:

AI5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)/($E$4:$AH$4<>"T7")/($E$4:$AH$4<>"CN"))
AJ5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)/(($E$4:$AH$4="T7")+($E$4:$AH$4="CN")))
AK5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=1))

Kết thúc bằng Ctrl+Shift+Enter (CSE), rồi copy xuống dưới.

Lưu ý: Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận.

2. Liệt kê các ngày giải trình:

D26=IFERROR(AGGREGATE(15,6,$E$3:$AH$3/(OFFSET($E$3:$AH$4,MATCH($C26,$C$5:$C$22,)+1,)="GT"),COLUMN(A$1)),"")

Chỉ Enter, fill qua phải, rồi fill cả hàng xuống.

Thân

 

[mountain84]

1. Tính công:

AI5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)/($E$4:$AH$4<>"T7")/($E$4:$AH$4<>"CN"))
AJ5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)/(($E$4:$AH$4="T7")+($E$4:$AH$4="CN")))
AK5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=1))

Kết thúc bằng Ctrl+Shift+Enter (CSE), rồi copy xuống dưới.

Lưu ý: Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận.

2. Liệt kê các ngày giải trình:

D26=IFERROR(AGGREGATE(15,6,$E$3:$AH$3/(OFFSET($E$3:$AH$4,MATCH($C26,$C$5:$C$22,)+1,)="GT"),COLUMN(A$1)),"")

Chỉ Enter, fill qua phải, rồi fill cả hàng xuống.

Thân

Mình thật sự cảm ơn bạn rất nhiều vì đã giúp đỡ mình! về công thức mảng này mình mù tịt nên cũng không rõ lắm bạn làm thế nào nên mình có một số vấn đề nữa mong bạn giúp đỡ thêm cho mình:
- Về công thức tính công bạn có thể sửa giúp mình một chút là nếu đã có chữ "GT" (giải trình) ở trong giờ vào hoặc giờ ra thì cũng sẽ được tính là 1 công ngày thường hoặc cuối tuần (vì đã giải trình được vì sao vắng giờ vào hoặc giờ ra). Nên nếu đã có chữ "GT" thì sẽ không được đếm ở trong công vắng 1 chiều bạn à!
- trong phần lưu ý bạn có ghi là "Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận. " vậy bạn có cách nào để merge cell lại nhanh các ô mình đã hủy trạng thái merge cell này không?! vì nếu phải chọn từng ô để merge cell lại thì rất mất thời gian. Giống như file bạn làm cho mình mình thấy là các ô quay lại đúng trạng thái merge cell như file gốc
- Trong bảng thống kê các ngày cần giải trình mình muốn dùng công thức "=D26&" ; "&E26&" ; "&F26&" ; "&G26&" ; "&H26" để nối các ô lại với nhau nhưng nếu gạch ô trống thì khi đó kết quả sẽ có nhiều dấu ";" ở đằng sau, vậy bạn có cách nào giúp mình nối các kết quả của "bảng thống kê các ngày cần giải trình" nếu ô trống thì sẽ không có dấu ";" không?!
Chân trọng cảm ơn bạn rất nhiều!

 

[Phan Thế Hiệp]

Mình thật sự cảm ơn bạn rất nhiều vì đã giúp đỡ mình! về công thức mảng này mình mù tịt nên cũng không rõ lắm bạn làm thế nào nên mình có một số vấn đề nữa mong bạn giúp đỡ thêm cho mình:
- Về công thức tính công bạn có thể sửa giúp mình một chút là nếu đã có chữ "GT" (giải trình) ở trong giờ vào hoặc giờ ra thì cũng sẽ được tính là 1 công ngày thường hoặc cuối tuần (vì đã giải trình được vì sao vắng giờ vào hoặc giờ ra). Nên nếu đã có chữ "GT" thì sẽ không được đếm ở trong công vắng 1 chiều bạn à!
- trong phần lưu ý bạn có ghi là "Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận. " vậy bạn có cách nào để merge cell lại nhanh các ô mình đã hủy trạng thái merge cell này không?! vì nếu phải chọn từng ô để merge cell lại thì rất mất thời gian. Giống như file bạn làm cho mình mình thấy là các ô quay lại đúng trạng thái merge cell như file gốc
- Trong bảng thống kê các ngày cần giải trình mình muốn dùng công thức "=D26&" ; "&E26&" ; "&F26&" ; "&G26&" ; "&H26" để nối các ô lại với nhau nhưng nếu gạch ô trống thì khi đó kết quả sẽ có nhiều dấu ";" ở đằng sau, vậy bạn có cách nào giúp mình nối các kết quả của "bảng thống kê các ngày cần giải trình" nếu ô trống thì sẽ không có dấu ";" không?!
Chân trọng cảm ơn bạn rất nhiều!

Điều chỉnh:
1/ Tính công:

AI5=COUNT(1/((MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)+(MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))/($E$4:$AH$4<>"T7")/($E$4:$AH$4<>"CN"))
AJ5=COUNT(1/((MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)+(MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))/(($E$4:$AH$4="T7")+($E$4:$AH$4="CN")))
AK5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=1)/(1-MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))

Kết thúc bằng Ctrl+Shift+Enter (CSE), rồi copy xuống dưới.

Lưu ý: Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận.

2. Liệt kê các ngày giải trình: Dùng hàm tự tạo 'JoinText' của thầy @ndu96081631

C26=JoinText("; ",INDEX(AGGREGATE(15,6,$E$3:$AH$3/(OFFSET($E$3:$AH$4,MATCH($B26,$C$5:$C$22,)+1,)="GT"),ROW(INDIRECT("1:31"))),))

Enter fill xuống.

Thân

 

[mountain84]

Điều chỉnh:
1/ Tính công:

AI5=COUNT(1/((MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)+(MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))/($E$4:$AH$4<>"T7")/($E$4:$AH$4<>"CN"))
AJ5=COUNT(1/((MMULT({1,1},IFERROR(N(--E5:AH6>0),))=2)+(MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))/(($E$4:$AH$4="T7")+($E$4:$AH$4="CN")))
AK5=COUNT(1/(MMULT({1,1},IFERROR(N(--E5:AH6>0),))=1)/(1-MMULT({1,1},IFERROR(N(E5:AH6="GT"),))))

Kết thúc bằng Ctrl+Shift+Enter (CSE), rồi copy xuống dưới.

Lưu ý: Vì bạn đang dùng Merge Cell, nên khi xem hay sửa công thức phải hủy trạng thái này về đơn ô, sau khi xong nhấn CSE, rồi 'Merge Cell' trở lại, lúc đó nó mới chấp nhận.

2. Liệt kê các ngày giải trình: Dùng hàm tự tạo 'JoinText' của thầy @ndu96081631

C26=JoinText("; ",INDEX(AGGREGATE(15,6,$E$3:$AH$3/(OFFSET($E$3:$AH$4,MATCH($B26,$C$5:$C$22,)+1,)="GT"),ROW(INDIRECT("1:31"))),))

Enter fill xuống.

Thân

Rất cảm ơn bạn đã sửa lại công thức giúp mình! công thức như này là rất đúng ý của mình rồi! vậy bạn có cách nào trộn nhanh lại các ô đã bỏ merge cell để sửa công thức không bạn?!

 

[Phan Thế Hiệp]

Rất cảm ơn bạn đã sửa lại công thức giúp mình! công thức như này là rất đúng ý của mình rồi! vậy bạn có cách nào trộn nhanh lại các ô đã bỏ merge cell để sửa công thức không bạn?!

Chỉ trường hợp bất khả kháng mới dùng 'Merge Cell', tôi thì không thích dùng nó lắm, vì ảnh hưởng đến việc tổng hợp hay truy tìm dữ liệu.

Tôi đã ghi lưu ý cho bạn rồi: Khi xem hay sửa bạn chỉ cần tác động lên dòng đầu tiên, nhớ trả về đơn ô, xong rồi thì 'Merge cell' lại, rồi cứ để nguyên tình trạng 'Merge cell' đó mà copy và dán xuống dưới.

Thân

 

[mountain84]

Chỉ trường hợp bất khả kháng mới dùng 'Merge Cell', tôi thì không thích dùng nó lắm, vì ảnh hưởng đến việc tổng hợp hay truy tìm dữ liệu.

Tôi đã ghi lưu ý cho bạn rồi: Khi xem hay sửa bạn chỉ cần tác động lên dòng đầu tiên, nhớ trả về đơn ô, xong rồi thì 'Merge cell' lại, rồi cứ để nguyên tình trạng 'Merge cell' đó mà copy và dán xuống dưới.

Thân

Mình đã hiểu và làm được rồi bạn à! Chân thành cảm ơn bạn rất nhiều vì đã giúp đỡ mình!