Lập công thức thống kê ngày phép (3 người xem)
- Thread starter amesan
- Ngày gửi
Người dùng đang xem chủ đề này
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Bạn gõ vào cell Đ1 công thức sau:
PHP:
=SUMPRODUCT(($C$4:$C$10=$C19)*(MONTH($D$4:$D$10)=MONTH(D$18))*($F$4:$F$10))Chào ndu96081631
Cám ơn bạn về công thức trên. Tuy nhiên nếu thời gian nghỉ phép nằm trong 2 tháng hoặc hai năm khác nhau thì công thức trên không còn đúng nữa.
Ví dụ: nghỉ phép từ ngày 28-03-09 đến ngày 03-04-09 hoặc từ ngày 28-12-08 đến ngày 5-01-09 thì công thức chỉ thống kê ngày phép cho tháng hoặc năm của thời gian bắt đầu nghỉ phép.
Thân
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Đồng ý điều này!
Vậy bạn cho điều kiện thống kê đi ---> Trong trường hợp thời gian bắt đầu và kết thúc nằm ở 2 tháng khác nhau thì bạn muốn thống kê thế nào?
- Tham gia
- 8/6/06
- Bài viết
- 14,628
- Được thích
- 22,960
- Nghề nghiệp
- U80
Mình thấy lạ về cách thống kê ngày phép của bạn này,:
- Từ ngày 3/14/2009 đến 3/16/2009 lại tính là 2 ngày; Vậy nếu trong bảng chấm công thì tính chấm nó vô ngày nào với ngày nào để được bằng 2; Chấm vô ngày 14 & 15 hay chấm vô 15 & 16; Chẵng lẽ lại chấm vô 14 & 16(?)
- Hồi xưa, tính ngày phép ta không kể ngày nghĩ do Nhà nước qui định; Chả ai chấm phép vô ngày CN hay ngày 1/5 & 30/04 cả; Không biết giờ có qui định mới nào không (?)
(Hay bạn không trên lãnh thổ Việt nam; Nếu vậy cho mình xin lỗi cái nha!)
- Từ ngày 3/14/2009 đến 3/16/2009 lại tính là 2 ngày; Vậy nếu trong bảng chấm công thì tính chấm nó vô ngày nào với ngày nào để được bằng 2; Chấm vô ngày 14 & 15 hay chấm vô 15 & 16; Chẵng lẽ lại chấm vô 14 & 16(?)
- Hồi xưa, tính ngày phép ta không kể ngày nghĩ do Nhà nước qui định; Chả ai chấm phép vô ngày CN hay ngày 1/5 & 30/04 cả; Không biết giờ có qui định mới nào không (?)
(Hay bạn không trên lãnh thổ Việt nam; Nếu vậy cho mình xin lỗi cái nha!)
Chào ndu96081631
Cám ơn bạn đã giúp đỡ. Mình xin gửi kèm file đính kèm để bạn xem và lập giúp công thức.
Lần chỉnh sửa cuối:
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Ái chà... Bài toán này hơi bị hay à nha!
Về lý thuyết, nếu ngày bắt đầu và ngày kết thúc nằm ở 2 tháng riêng biệt nhưng 2 tháng này liền kề nhau thì không có gì khó cả... Tuy nhiên tôi đang nghĩ đến trường hợp tổng quát: Ngày bắt đầu và kết thúc có thể nằm ở cách xa nhau nhiều tháng... Ví dụ: từ 15/1/2009 đến 4/5/2009 thì phải tính được trong từng tháng có bao nhiêu ngày nghĩ
Để suy nghĩ kỹ lại xem!
Mời các cao thủ thử sức!
---------------------
Ah... tôi quên hỏi bạn 1 vấn đề quan trọng:
- Trong những ngày đang nghĩ phép, có ngày nào đó rơi vào chủ nhật thì có tính không?
- Ông Nguyễn Văn A nghĩ từ 29/12/2008 đến 05/01/2009.. tôi tính ra số ngày nghĩ phải là 8 ngày (nếu tính luôn CN) hoặc 7 ngày (nếu trừ đi CN) ---> Trong khi đó bạn lại tính ra 6 ngày ---> vậy là sao? (xem lại cell F4)
Bạn làm rõ 2 câu hỏi này và kiểm tra lại thật kỹ 1 lần nữa về số liệu trong file đính kèm rồi ta làm tiếp
Tạm thời tôi đang đi theo hướng dùng hàm tự tạo ---> Còn công thức thì vả quá, vẩn chưa nghĩ ra
Lần chỉnh sửa cuối:
Nếu bảng 1, liệt kê các NV nghỉ phép mà 1 NV chỉ 1 dòng thì còn nghĩ ra, chớ hơn 1 dòng thì chưa nghĩ ra.
Tạm thời mới nghĩ ra tính ra số ngày nghỉ trừ CN và ngày lễ theo công thức. Với điều kiện phải có list ngày lễ
=E4-D4+1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D4&":"&$E4)))=1))-SUMPRODUCT(--(NOT(ISNA(MATCH((ROW(INDIRECT($D4&":"&$E4))),NgayLe,0)))))
Hay là thêm cột phụ thì chắc dễ hơn. Và dưới thì dùng sumif.
Bác Ndu nghiên cứu tiếp giúp.
Lần chỉnh sửa cuối:
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Tôi có UFD này
Hiện tại chưa biết tác giả có trừ CN ra không nên tôi cho nó là 1 Option luôn... Mặc định là có trừ CN, nếu Option này = FALSE thì tính luôn CN
Các bạn tham khảo file đính kèm xem thế nào nhé
PHP:
Function NghiPhep(Ten As String, VungTen As Range, NgayBD As Range, NgayKT As Range, TrongThang As Long, TrongNam As Long, Optional TruCN As Boolean = True) As Long
Dim i As Long, Dk1 As Boolean, Dk2 As Boolean, Dk3 As Boolean, Temp As Long
Dim Temp1 As Long, Temp2 As Long, Temp3 As Long
For i = 1 To VungTen.Rows.Count
If VungTen(i) = Ten Then
Dk1 = (Format(NgayBD(i), "mmyyyy") = Format(TrongThang, "00") & Format(TrongNam, "0000"))
Dk2 = (Format(NgayKT(i), "mmyyyy") = Format(TrongThang, "00") & Format(TrongNam, "0000"))
Dk3 = (DateSerial(TrongNam, TrongThang, 1) >= NgayBD(i) And DateSerial(TrongNam, TrongThang, 1) <= NgayKT(i))
If Dk1 Or Dk2 Or Dk3 Then
Temp1 = WorksheetFunction.Max(NgayBD(i), DateSerial(TrongNam, TrongThang, 1))
Temp2 = WorksheetFunction.Min(NgayKT(i), DateSerial(TrongNam, TrongThang + 1, 0))
Temp3 = Temp3 + Temp2 - Temp1 + 1 + TruCN * Int((Temp2 - Temp1 - Weekday(Temp2 - 6, vbMonday) + 8) / 7)
End If
End If
Next i
NghiPhep = Temp3
End FunctionCác bạn tham khảo file đính kèm xem thế nào nhé
huuthang_bd
Chuyên gia GPE
- Tham gia
- 10/9/08
- Bài viết
- 8,928
- Được thích
- 11,334
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Thợ đụng
Tôi làm bằng công thức. Không trừ ngày chủ nhật. Nếu có thì suy nghĩ thêm. Chèn thêm ĐK này sẽ rất phức tạp. Làm mà không dùng thì phí 
Chào bạn
- Mình không tính ngày Chủ nhật.
- Ông Nguyễn Văn A nghỉ từ 29/12/08- 05/01/2009: được nghỉ Chủ nhật và nghỉ Tết tây 01.01.09 mà không trừ vào phép nên Ông A chỉ nghỉ 6 ngày phép. Cám ơn các bạn đã quan tâm giải giúp bài toán.
Thân
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Nếu thế thì phải có 1 vùng liệt kê các ngày lể nữa
Bạn xem file nhé
Bài toán này rất thiết thực đây
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Hiện tại cú pháp trong UDF này là:
Các bạn hãy cải tiến hàm này sao cho:
- Nếu bỏ qua biến TrongThang có nghĩa là ta muốn tính số ngày phép đã dùng trong năm
- Nếu bỏ qua cả 2 biến TrongThang và TrongNam có nghĩa là ta muốn tính toàn bộ số ngày phép đã sử dụng
Xin mời các cao thủ trợ giúp 1 tay
PHP:
=NgayPhep(Ten, VungTen, Vùng Ngày BT, Vùng Ngày KT, TrongThang, TrongNam, NghiLe)- Nếu bỏ qua biến TrongThang có nghĩa là ta muốn tính số ngày phép đã dùng trong năm
- Nếu bỏ qua cả 2 biến TrongThang và TrongNam có nghĩa là ta muốn tính toàn bộ số ngày phép đã sử dụng
Xin mời các cao thủ trợ giúp 1 tay
Chào bạn
Mình muốn mở rộng bài toán này ở cấp độ khó hơn là nếu ta thêm một cột Ghi chú: Nghỉ phép, nghỉ không lương và nghỉ ốm vào bảng 1. Làm thế nào để bảng 2 chỉ thống kê những ngày nghỉ phép mà thôi. Cám ơn bạn.
Thân
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Ý tưởng tôi nêu ở trên mới khó chứ thêm điều kiện lọc như bạn thì khó gì... Đơn giản là thêm biến vào code và 1 câu lệnh AND là được!
Xem file
Bạn thử bấm Alt + F11 vào xem code mới này và so sánh với code củ, nó cũng chả khác nhau gì mấy
Thank bạn. Cho mình hỏi thêm, nếu như bài toán chỉ tính nghỉ nữa ngày phép thì công thức phải thay đổi như thế nào ?
Lần chỉnh sửa cuối:
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Ủa... tôi vừa xem file của bạn xong nhưng đâu có thấy chổ nào ghi nửa ngày phép nhỉ?
Bạn ghi rõ vào file xem!
Còn nếu tất cả đều là nửa ngày thì đơn giản cứ lấy kết quả chia 2 thôi
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,961
Trời... cái này đâu có khó... tôi nghĩ trong tầm tay bạn thôi..
Hàm của tôi có điều kiện "Nghỉ Phép" ... vậy bạn thêm 1 công thức nữa với điều kiện "Nghỉ Phép 1/2 ngày"... và với công thức này, ra kết quả bao nhiêu bạn chia cho 2 là xong chứ gì
Kiều này đây:
PHP:
=NgayPhep($C21,$C$4:$C$9,$G$4:$G$9,"Nghỉ Phép",$D$4:$D$9,$E$4:$E$9,MONTH(D$20),YEAR(D$20),$L$2:$L$6)+NgayPhep($C21,$C$4:$C$9,$G$4:$G$9,"Nghỉ Phép 1/2 ngày",$D$4:$D$9,$E$4:$E$9,MONTH(D$20),YEAR(D$20),$L$2:$L$6)/2Nếu muốn công thức gọn hơn thì đặt name đi
