Truy vấn dữ liệu theo điều kiện bằng hàm hoặc VBA

Liên hệ QC

BuiQuangThuan

❆❆❆❆❆❆❆❆❆❆❆❆
Tham gia
17/12/10
Bài viết
2,418
Được thích
2,842
Giới tính
Nam
Chào các Thầy cô
Do sếp bắt phải quản lý giờ tăng ca của nhân viên trong bộ phận nên em tạo form nhờ các thầy cô giúp đỡ với ạ.
Hiện tại em có form (như đính kèm) gồm 3 sheet: FORM , DATA, Q-LY
Yêu cầu:
Các cột được bôi màu vàng ở sheet FORM sẽ lấy dữ liệu từ sheet DATA và Q-LY theo ngày hiện tại.

Em cũng đã lấy ví dụ ngày hôm nay luôn và kết quả sẽ trả về tương ứng như trong file đính kèm.

Vậy nên nhờ các thầy cô giúp em với ạ. Em cám ơn
 

File đính kèm

  • Form Tang ca.xlsb
    42.1 KB · Đọc: 29
Chào các Thầy cô
Do sếp bắt phải quản lý giờ tăng ca của nhân viên trong bộ phận nên em tạo form nhờ các thầy cô giúp đỡ với ạ.
Hiện tại em có form (như đính kèm) gồm 3 sheet: FORM , DATA, Q-LY
Yêu cầu:
Các cột được bôi màu vàng ở sheet FORM sẽ lấy dữ liệu từ sheet DATA và Q-LY theo ngày hiện tại.

Em cũng đã lấy ví dụ ngày hôm nay luôn và kết quả sẽ trả về tương ứng như trong file đính kèm.

Vậy nên nhờ các thầy cô giúp em với ạ. Em cám ơn
Thử code
Mã:
Sub Form_Run()
  Dim i As Long, ik As Long, eR As Long, eC As Long, sR As Long, sC As Long
  Dim aData(), aQLy(), Res(), S, Rng As Range
  Dim Ngay As Date, dNam As Date, dThang As Date, ThoiGian, GioPhut As String
 
  Ngay = Date
  With Range("A14:AD36")
    ReDim Res(1 To .Rows.Count, 1 To .Columns.Count)
  End With
  Set Rng = Sheets("Form").Range("AG14:AG36")
  With Sheets("Data")
    eR = .Range("A" & Rows.Count).End(xlUp).Row
    If eR < 7 Then MsgBox ("Khong co du lieu"): Exit Sub
    aData = .Range("A7:E" & eR).Value
    GioPhut = .Range("C1")
  End With
  With Sheets("Q-Ly")
    sR = .Range("A1").End(xlDown).Row
    sC = .Range("A1").End(xlToRight).Column
    aQLy = .Range("A1").Resize(sR, sC).Value
  End With
  dNam = DateSerial(Year(Ngay), 1, 1)
  dThang = DateSerial(Year(Ngay), Month(Ngay), 1)
  For j = 3 To sC
    If aQLy(1, j) = Ngay Then
      For i = 2 To sR
        ThoiGian = aQLy(i, j)
        If ThoiGian > 0 Then
          k = k + 1
          Res(k, 1) = k
          Res(k, 2) = aQLy(i, 1)
          Res(k, 7) = aQLy(i, 2)
          Res(k, 20) = ThoiGian
          
          If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
          Res(k, 15) = aData(ThoiGian, S)

          For jk = 3 To j
            If aQLy(1, jk) >= dThang Then Res(k, 23) = Res(k, 23) + aQLy(i, jk)
            If aQLy(1, jk) >= dNam Then Res(k, 24) = Res(k, 24) + aQLy(i, jk)
          Next jk
          
          Res(k, 25) = GioPhut
          S = Split(Application.Trim(Res(k, 7)), " ")
          Res(k, 29) = S(UBound(S))
        End If
      Next i
      Exit For
    End If
  Next j
  If k Then Sheets("Form").Range("A14").Resize(k, 29) = Res
End Sub
 
Thầy ơi. Đoạn code này đang được thầy setup cho ngày hiện thại đúng không ạ. Chẳng hạn khi em nhập lần 1 rồi cho chạy code. thì nó đúng. nhưng trường hợp em sửa lần 2. cho chạy lại. thì nó cứ vẫn giữ y kết quả như lần 1 ạ
 
Cám ơn @HieuCD ạ. vấn đề trên em đã sửa được rồi ạ. Em test sau rồi thông tin thêm cho anh sau ạ
 
Chào Thầy @HieuCD . cám ơn thầy vì đã giúp đỡ em hoàn thành form trên. Có 1 chút vấn đề em gặp phải khi chạy đoạn code trên:
- Trên sheet "FORM", cột Lũy kế tháng và năm số liệu nhảy chưa chính xác thầy ạ?
- Phần Lũy kế tháng cách lấy khác với ban đầu em đưa ra yêu cầu: do công ty em tính tháng lương dựa vào 1 nửa tháng trước và 1 nửa tháng sau (ví dụ: tháng tính lương tháng 1/2019 sẽ bắt đầu từ 16/12/2018 đến 15/01/2019). khả năng vì thế mà chỗ lũy kế tháng nó ra số liệu không khớp.
- Còn cột lũy kế năm thì nhảy chưa đúng.
Chính vì vậy phiền thầy 1 lần nữa kiểm tra giúp em đươc không ạ. vì em không biết chỗ code của thầy phải chỉnh sửa chỗ nào ạ. xin cám ơn thầy nhiều
 
Thầy ơi. em xin lỗi. dữ liệu lũy kế năm đúng rồi ạ
 
Chào Thầy @HieuCD . cám ơn thầy vì đã giúp đỡ em hoàn thành form trên. Có 1 chút vấn đề em gặp phải khi chạy đoạn code trên:
- Trên sheet "FORM", cột Lũy kế tháng và năm số liệu nhảy chưa chính xác thầy ạ?
- Phần Lũy kế tháng cách lấy khác với ban đầu em đưa ra yêu cầu: do công ty em tính tháng lương dựa vào 1 nửa tháng trước và 1 nửa tháng sau (ví dụ: tháng tính lương tháng 1/2019 sẽ bắt đầu từ 16/12/2018 đến 15/01/2019). khả năng vì thế mà chỗ lũy kế tháng nó ra số liệu không khớp.
- Còn cột lũy kế năm thì nhảy chưa đúng.
Chính vì vậy phiền thầy 1 lần nữa kiểm tra giúp em đươc không ạ. vì em không biết chỗ code của thầy phải chỉnh sửa chỗ nào ạ. xin cám ơn thầy nhiều
Ngày 10/8/2019. Nói rỏ lũy kế tháng và năm , từ ngày nào tới ngày nao?
Gởi file với kết quả đúng nhập tay mà code chạy sai để mình kiểm tra
 
Ngày 10/8/2019. Nói rỏ lũy kế tháng và năm , từ ngày nào tới ngày nao?
Gởi file với kết quả đúng nhập tay mà code chạy sai để mình kiểm tra
Em lấy ngày 12/08 để nhập giờ tăng ca. Sheet("FORM") là kết quả em muốn ạ. Lũy kế năm thì đúng rồi. Xin lỗi thầy do em đặt sai công thức. Còn lũy kế tháng = từ ngày 16 tháng trước đến 15 tháng này
 

File đính kèm

  • Form Tang ca.xlsb
    46.7 KB · Đọc: 8
Em lấy ngày 12/08 để nhập giờ tăng ca. Sheet("FORM") là kết quả em muốn ạ. Lũy kế năm thì đúng rồi. Xin lỗi thầy do em đặt sai công thức. Còn lũy kế tháng = từ ngày 16 tháng trước đến 15 tháng này
Lũy kế tháng từ 16 tháng trước (nầy) đến ngày hiện tại
Mã:
Sub Form_Run()
  Dim i As Long, ik As Long, eR As Long, eC As Long, sR As Long, sC As Long
  Dim aData(), aQLy(), Res(), S, Rng As Range
  Dim Ngay As Date, dNam As Date, dThang As Date, ThoiGian, GioPhut As String
 
  Ngay = Date  'Ngay Hien hanh
  With Range("A14:AD36")
    ReDim Res(1 To .Rows.Count, 1 To .Columns.Count)
  End With
  Set Rng = Sheets("Form").Range("AG14:AG36")
  With Sheets("Data")
    eR = .Range("A" & Rows.Count).End(xlUp).Row
    If eR < 7 Then MsgBox ("Khong co du lieu"): Exit Sub
    aData = .Range("A7:E" & eR).Value
    GioPhut = .Range("C1")
  End With
  With Sheets("Q-Ly")
    sR = .Range("A1").End(xlDown).Row
    sC = .Range("A1").End(xlToRight).Column
    aQLy = .Range("A1").Resize(sR, sC).Value
  End With
  dNam = DateSerial(Year(Ngay), 1, 1)
  If tmp < 16 Then
    dThang = DateSerial(Year(Ngay), Month(Ngay) - 1, 16)
  Else
    dThang = DateSerial(Year(Ngay), Month(Ngay), 16)
  End If
  For j = 3 To sC
    If aQLy(1, j) = Ngay Then
      For i = 2 To sR
        ThoiGian = aQLy(i, j)
        If ThoiGian > 0 Then
          k = k + 1
          Res(k, 1) = k
          Res(k, 2) = aQLy(i, 1)
          Res(k, 7) = aQLy(i, 2)
          Res(k, 20) = ThoiGian
          
          If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
          Res(k, 15) = aData(ThoiGian, S)

          For jk = 3 To j
            If aQLy(1, jk) >= dThang Then Res(k, 23) = Res(k, 23) + aQLy(i, jk)
            If aQLy(1, jk) >= dNam Then Res(k, 24) = Res(k, 24) + aQLy(i, jk)
          Next jk
          
          Res(k, 25) = GioPhut
          S = Split(Application.Trim(Res(k, 7)), " ")
          Res(k, 29) = S(UBound(S))
        End If
      Next i
      Exit For
    End If
  Next j
  Sheets("Form").Range("A14").Resize(UBound(Res), 29) = Res
End Sub
 
Bảng dưới tính nguyên tháng, code tính theo bảng trên tới ngày hiện tại
Thầy @HieuCD ạ. Em phiền thầy chút được không ạ. Sau vài hôm test đoạn code thầy viết. Em có phát hiện ra chỗ này nhảy không đúng ạ
Mã:
 If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
          Res(k, 15) = aData(ThoiGian, S)
Phiền thầy có thể xem qua chút cho em được không ạ
 
Thầy @HieuCD ạ. Em phiền thầy chút được không ạ. Sau vài hôm test đoạn code thầy viết. Em có phát hiện ra chỗ này nhảy không đúng ạ
Mã:
 If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
          Res(k, 15) = aData(ThoiGian, S)
Phiền thầy có thể xem qua chút cho em được không ạ
Chỉnh
If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
Thành
If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 2
 
Chỉnh
If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 1
Thành
If Rng(k, 1) = "HC" Then S = 2 Else S = Rng(k, 1) + 2
Gửi thầy @HieuCD.
Trước tiên em xin cám ơn thầy rất nhiều về những gì thầy đã giúp đỡ tại topic này.
Sau nửa năm sử dụng code của thầy.Mọi thứ đều rất tốt.
Bây giờ là tiếp giáp giữa năm 2019-2020. Do muốn quản lý dữ liệu nhiều năm 1 file.
Ở phần Lũy Kế năm (trên sheet FORM) nó lại cộng dồn tất cả các năm lại mà không phải là tách biệt từng năm 1. Cũng xem code của thầy nhưng lại không thể chỉnh sửa gì thêm được do không hiểu được cách thầy viết
Nếu có thể. Phiền thầy ngó lại giúp em chút được không ạ.
Cám ơn thầy nhiều
 
Gửi thầy @HieuCD.
Trước tiên em xin cám ơn thầy rất nhiều về những gì thầy đã giúp đỡ tại topic này.
Sau nửa năm sử dụng code của thầy.Mọi thứ đều rất tốt.
Bây giờ là tiếp giáp giữa năm 2019-2020. Do muốn quản lý dữ liệu nhiều năm 1 file.
Ở phần Lũy Kế năm (trên sheet FORM) nó lại cộng dồn tất cả các năm lại mà không phải là tách biệt từng năm 1. Cũng xem code của thầy nhưng lại không thể chỉnh sửa gì thêm được do không hiểu được cách thầy viết
Nếu có thể. Phiền thầy ngó lại giúp em chút được không ạ.
Cám ơn thầy nhiều
Code lâu quá nên quên hết rồi, bạn cho ví dụ và gởi lại file
 
Cám ơn thầy @HieuCD đã hồi âm. Mấy bữa nay thầy đi đâu ạ.
Em xin nói lại 1 xíu về file lần trước thầy giúp đỡ ạ
Hinh dưới là phần quản lý giờ tăng ca của nhân viên theo từng ngày.
1576725157717.png

Phần Lũy Kế Tháng thì như thầy làm nó vẫn nhảy đúng:
- ví dụ : Lũy Kế tháng 12 = thời gian từ 16/11- đến 15/12
Phần Lũy kế năm: Năm 2019 = tổng từ 16/12/2018 -15/12/2019
Ở ví dụ từ 2 hình ảnh em đính kèm. Nay 19/12/2019 khi điền giờ tăng ca vô. Lũy kế năm nó vẫn là tổng của năm 2019
Đúng nó sẽ là tổng thời gian từ 16/12/2019 điến hiện tại ạ



1576725195691.png
Do không hiểu rõ thuật toán của thầy. nên không biết sửa chỗ nào. Không phiền nhờ thầy coi giúp em với ạ.
Em cám ơn !
 

File đính kèm

  • Tang ca 2019.xlsb
    86.3 KB · Đọc: 5
  • 1576725132672.png
    1576725132672.png
    36.2 KB · Đọc: 3
Cám ơn thầy @HieuCD đã hồi âm. Mấy bữa nay thầy đi đâu ạ.
Em xin nói lại 1 xíu về file lần trước thầy giúp đỡ ạ
Hinh dưới là phần quản lý giờ tăng ca của nhân viên theo từng ngày.
View attachment 230155

Phần Lũy Kế Tháng thì như thầy làm nó vẫn nhảy đúng:
- ví dụ : Lũy Kế tháng 12 = thời gian từ 16/11- đến 15/12
Phần Lũy kế năm: Năm 2019 = tổng từ 16/12/2018 -15/12/2019
Ở ví dụ từ 2 hình ảnh em đính kèm. Nay 19/12/2019 khi điền giờ tăng ca vô. Lũy kế năm nó vẫn là tổng của năm 2019
Đúng nó sẽ là tổng thời gian từ 16/12/2019 điến hiện tại ạ



View attachment 230156
Do không hiểu rõ thuật toán của thầy. nên không biết sửa chỗ nào. Không phiền nhờ thầy coi giúp em với ạ.
Em cám ơn !
Chỉnh lại code
Mã:
Sub Form_Run()
  Dim i&, r&, ik&, eR&, eC&, sR&, sC&, sRow&
  Dim aData(), aQLy(), Res(), s, Rng As Range
  Dim Ngay As Date, dNam As Date, dThang As Date, cThang As Date, ThoiGian, GioPhut As String
 
  Ngay = Date
  With Sheets("Form").Range("A14:AD36")
    ReDim Res(1 To .Rows.Count, 1 To .Columns.Count)
  End With
  Set Rng = Sheets("Form").Range("AG14:AG36")
  With Sheets("Data")
    eR = .Range("A" & Rows.Count).End(xlUp).Row
    If eR < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    aData = .Range("A2:F" & eR).Value
    sRow = UBound(aData) ' so dong Data
    GioPhut = .Range("C1")
  End With
  With Sheets("Q-Ly")
    sR = .Range("A1").End(xlDown).Row
    sC = .Range("A1").End(xlToRight).Column
    aQLy = .Range("A1").Resize(sR, sC).Value
  End With
  If Month(Ngay) = 12 And Day(Ngay) >= 16 Then'Dau Nam
    dNam = DateSerial(Year(Ngay), 12, 16)
  Else
    dNam = DateSerial(Year(Ngay) - 1, 12, 16)
  End If
  If Day(Ngay) < 16 Then
    dThang = DateSerial(Year(Ngay), Month(Ngay) - 1, 16)'Dau Thang
    cThang = DateSerial(Year(Ngay), Month(Ngay), 15)'Cuoi thang
  Else
    dThang = DateSerial(Year(Ngay), Month(Ngay), 16)
    cThang = DateSerial(Year(Ngay), Month(Ngay) + 1, 15)
  End If
  For j = 3 To sC 'dòng "Ngay" Sheets("Q-Ly")
    If aQLy(1, j) = Ngay Then
      For i = 2 To sR
        ThoiGian = aQLy(i, j)
        If ThoiGian > 0 Then
          k = k + 1
          Res(k, 1) = k
          Res(k, 2) = aQLy(i, 1)
          Res(k, 7) = aQLy(i, 2)
          Res(k, 20) = ThoiGian
          
          If Rng(k, 1) = "HC" Then s = 2
          If Rng(k, 1) = "1" Then s = 3
          If Rng(k, 1) = "2" Then s = 4
          If Rng(k, 1) = "3" Then s = 5
          'If Rng(k, 1) = "3" Then s = 6
          For r = 1 To sRow
            If aData(r, 1) = ThoiGian Then
              Res(k, 15) = aData(r, s)
              Exit For
            End If
          Next r
          For jk = 3 To sC
            If aQLy(1, j) > cThang Then Exit For
            If aQLy(1, jk) >= dThang Then Res(k, 23) = Res(k, 23) + aQLy(i, jk)
            If aQLy(1, jk) >= dNam Then Res(k, 24) = Res(k, 24) + aQLy(i, jk)
          Next jk
          
          Res(k, 25) = GioPhut
          s = Split(Application.Trim(Res(k, 7)), " ")
          Res(k, 29) = s(UBound(s))
        End If
      Next i
      Exit For
    End If
  Next j
  Sheets("Form").Range("A14").Resize(UBound(Res), 29) = Res
End Sub
 
Web KT
Back
Top Bottom