Help: Nhờ tìm số ngày nghỉ gần nhất và ....

[tadien2212]

Nhờ các cô dì chú bác giúp em như hình. Có file excel đính kèm cho các cậu các mợ.

 

[CHAOQUAY]

Nhờ các cô dì chú bác giúp em như hình. Có file excel đính kèm cho các cậu các mợ.

Kết quả cần hiển thị như thế nào?

 

[BuiQuangThuan]

Bạn ấy bố trí dữ liệu kiểu này khó nhìn quá. Chẳng hạn nếu có 500 nhân viên chắc còn nhìn rối nữa

 

[SA_DQ]

CSDL chưa chuẩn: Chưa có mã duy nhất để xài;
Xin giới thiệu xài thử:

Mã	Ho & Tên
FBM00	Đặng Bảo Minh
FNK00	Đặng Nguyên Khôi
GHF00	Giang Hồng Đức
HND00	Hoàng Ngọc Danh
LFC00	Lê Đức Chính
NFH00	Ngô Đức Hiệu
NFH01	Nguyễn Đình Hà
NTA00	Nguyễn Thị An
NJB00	Nguyễn Bánh
NTH00	Nguyễn Trần Hiếu
TTT00	Trần Thị Trang
TVB00	Trần Văn Bình
TVT00	Trần Văn Tình
VTF00	Vũ Tuấn Đạt

Chủ bài đăng nên lập CSDL cho những nhân viên nghỉ ngày công như sau:

[Số TT], [Mã NV], [Ngày nghỉ], ([Ghi chú])

Lập như vậy cả 1 năm hay vài năm đều được.

 

[tadien2212]

Kết quả cần hiển thị như thế nào?

Cho em xin kết quả dạng như này ra 1 sheet khác để e có số liệu làm báo cáo hoặc kiểm tra dễ dàng hơn.

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

CSDL chưa chuẩn: Chưa có mã duy nhất để xài;
Xin giới thiệu xài thử:

Mã	Ho & Tên
FBM00	Đặng Bảo Minh
FNK00	Đặng Nguyên Khôi
GHF00	Giang Hồng Đức
HNC00	Hoàng Ngọc Cảnh
LFC00	Lê Đức Chính
NFH00	Ngô Đức Hiệu
NFH01	Nguyễn Đình Hà
NTA00	Nguyễn Thị An
NJB00	Nguyễn Bánh
NTH00	Nguyễn Trần Hiếu
TTT00	Trần Thị Trang
TVB00	Trần Văn Bình
TVT00	Trần Văn Tình
VTF00	Vũ Tuấn Đạt

Chủ bài đăng nên lập CSDL cho những nhân viên nghỉ ngày công như sau:

[Số TT], [Mã NV], [Ngày nghỉ], ([Ghi chú])

Lập như vậy cả 1 năm hay vài năm đều được.

Cám ơn bác đã góp ý. Tiện thể bác giúp em cái đề bài ở trên được không ạ ????

 

[CHAOQUAY]

Cho em xin kết quả dạng như này ra 1 sheet khác để e có số liệu làm báo cáo hoặc kiểm tra dễ dàng hơn.
View attachment 228199

Bạn gửi file đính kèm lên cho tiện

 

[tadien2212]

Bạn gửi file đính kèm lên cho tiện

Ok Bác.

 

[SA_DQ]

Bạn kiểm tra xem 3 UDF này kết quả theo ý bạn chưa?

 

[tadien2212]

Bạn kiểm tra xem 3 UDF này kết quả theo ý bạn chưa?

Đầu tiên xin cám ơn toàn thể ace, các đồng chí, đồng bào đã đi qua góp ý cho em. Đặc Biệt là bác SA-DQ (em gà nên ko biết trích tên Bác vào )
File Bác gửi cho em gần được rồi, còn mỗi cái ngày nghỉ gần nhất là chưa đúng. Cụ thể như hình:

P.S: Tiện thể bác cho em xin hỏi bác xài sim của nhà mạng nào để em gửi bác cái thẻ để cảm ơn ạ.

 

[CHAOQUAY]

Ok Bác.

Bài này chỉ làm được 3 yêu cầu: Ngày nghỉ gần nhất, số ngày nghỉ, thời gian làm dài nhất.
Về thời gian làm ngắn nhất, có lẽ vẫn viết được công thức nhưng vì quá dài nên dừng.
Bài này có lẽ là dùng VBA phù hợp hơn.

Trong bài có dùng Name, nhấn ctrl+F3 để xem

 

[SA_DQ]

. . . . (em gà nên ko biết trích tên Bác vào )
(1) File Bác gửi cho em gần được rồi, còn mỗi cái ngày nghỉ gần nhất là chưa đúng. Cụ thể như hình:
(2) P.S: Tiện thể bác cho em xin hỏi bác xài sim của nhà mạng nào để em gửi bác cái thẻ để cảm ơn ạ.

(1) Tại ô [D4] đang trả về là ngày nghỉ (làm) của NTA00 ;
Như vậy bạn muốn hiện con số 6 thì xài thêm cú pháp =Date(Nam, Tháng, Ngày) - fDay('Tháng 9.2019'!$A$4:B29,ThKe!D2)

(2) Mình đã có lần nhận gián tiếp 1 thẻ cào & gởi ngay vố ĐT của thầy P. T. Hướng để ủng hộ hội từ thiện của GPE thôi;
Rất cảm ơn bạn có nhã ý & liên lạc với thầy Hướng về việc này nha!
(https://www.giaiphapexcel.com/diend...n-của-clbxh-miền-bắc.73688/page-2#post-902483)

 

[tadien2212]

háp =Date(Nam, Tháng, Ngày) - fDay('Th

(1) Em vẫn chưa hiểu ý Bác lắm. Bác có thể giải thích kỹ hơn chút hoặc xuất luôn ra file excel rồi up lên được không ạ? Trình excel của em con vịt lắm!!!
(2) Em đã theo Link Bác để nhưng không thấy hướng dẫn ủng hộ thế nào. Bác bày giúp em với ạ.

 

[SA_DQ]

(1) Công thức tại D4: =IF(D3=0, 0, DATE(2019,10,-1)-fDay('Tháng 9.2019'!$A$4:B29,ThKe!D2)) => 6

(2) Nhắn tin cho thầy Hướng số seri của THẺ CÀO là ĐƯỢC.

 

[tadien2212]

(1) Công thức tại D4: =IF(D3=0, 0, DATE(2019,10,-1)-fDay('Tháng 9.2019'!$A$4:B29,ThKe!D2)) => 6

(2) Nhắn tin cho thầy Hướng số seri của THẺ CÀO là ĐƯỢC.

(1) Em copy theo công thức của Bác rồi nhưng vẫn chưa đúng (cột sai em bôi màu đỏ). Em up file lên Bác xem giúp em với. Với Bác sửa giúp em thành 1 hàm như đếm ngày max được không ạ. Ý em là tính trực tiếp ý. Không phải trừ như bây giờ ạ.
(2) ok Bác.

 

[SA_DQ]

Mình đã viết lại hàm để xác định ngày nghỉ gần nhất như sau:

Function F_Day(CSDL As Range) As Integer
 Dim J As Long, W As Integer, Col As Integer
 
 Col = CSDL.Columns.Count
 For J = 1 To CSDL.Rows.Count
    If CSDL.Cells(J, Col).Value = 1 Then
        F_Day = F_Day + 1
    ElseIf CSDL.Cells(J, Col).Value = 0 Then
        Exit Function
    End If
 Next J
End Function

Cú pháp chỉ là =F_day('Tháng 9.2019'!$A$5:B29)

 

[tadien2212]

Mình đã viết lại hàm để xác định ngày nghỉ gần nhất như sau:

Function F_Day(CSDL As Range) As Integer
Dim J As Long, W As Integer, Col As Integer

Col = CSDL.Columns.Count
For J = 1 To CSDL.Rows.Count
    If CSDL.Cells(J, Col).Value = 1 Then
        F_Day = F_Day + 1
    ElseIf CSDL.Cells(J, Col).Value = 0 Then
        Exit Function
    End If
Next J
End Function

Cú pháp chỉ là =F_day('Tháng 9.2019'!$A$5:B29)

Hàm chạy được rồi Bác ạ. Chân thành cám ơn Bác. Mà tiện thể Bác giúp hộ em vấn đề nhỏ này được không ạ.

 

[hocexcel_1991]

Hàm chạy được rồi Bác ạ. Chân thành cám ơn Bác. Mà tiện thể Bác giúp hộ em vấn đề nhỏ này được không ạ.
View attachment 228351

Công thức

B20=SUMPRODUCT(SUMIF($B$4:$I$4,B$16:B$19,OFFSET($A$4,MATCH($A20,$A$5:$A$11,0),1)))

 

[SA_DQ]

Bạn xài hàm UDF này cũng là thêm 1 tham khảo:

Function KTra(CSDL As Range, Dat As Date, NgNh As Range) As Double
Dim Rws As Long, Col As Integer, J As Long, Dem As Byte
Dim Cls As Range, Rng As Range

Rws = CSDL.Rows.Count:                 Col = CSDL.Columns.Count
For J = 1 To Rws
    If CSDL.Cells(J, 1).Value = Dat Then
        For Each Cls In Range(CSDL.Cells(J, 2), CSDL.Cells(J, Col))
            Dem = Dem + 1
            For Each Rng In NgNh
                If CSDL(Dem + 1).Value = Rng.Value Then
                    KTra = KTra + Cls.Value
                End If
            Next Rng
        Next Cls
    End If
Next J
End Function

Lúc đó cú pháp hàm tại [B20] sẽ là: =KTra($A$4:$I$11, A20, B16:B19)

 

[tadien2212]

Dạ em cảm ơn các bác đã nhiệt tình giúp đỡ em. Chúc các bác sức khỏe. Chúc diễn đàn ngày 1 lớn mạnh. Em xin phép đóng topic. Chào thân ái !!!

 

[Phan Thế Hiệp]

...thời gian làm dài nhất.
...thời gian làm ngắn nhất, có lẽ vẫn viết được công thức nhưng vì quá dài nên dừng.

1/ Công thức cho thời gian làm dài nhất:

B6=MAX(FREQUENCY(IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=1,ROW($4:$29)-3),IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=0,ROW($4:$29)-3)))
Hoặc:
B6=MAX(FREQUENCY(ROW($4:$29)-3,IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=0,ROW($4:$29)-3))-1)

Kết thúc bằng Ctrl+Shift+Enter. Copy qua phải.
2/ Công thức cho thời gian làm ngắn nhất:

B7=MIN(IFERROR(1/(1/FREQUENCY(IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=1,ROW($4:$29)-3),IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=0,ROW($4:$29)-3))),""))
Hoặc:
B7=MIN(IFERROR(TEXT(FREQUENCY(ROW(4:29)-3,IF(INDEX('Tháng 9.2019'!$B$4:$N$29,,MATCH(B$3,'Tháng 9.2019'!$B$3:$N$3,))=0,ROW(4:29)-3))-1,"0;;")*1,""))

Kết thúc bằng Ctrl+Shift+Enter. Copy qua phải.


Thân