Viết VBA tạo báo cáo hàng tháng

ZzNHCzZ · 14/7/08

Xin chào GPE!

Em có 1 File theo dỏi Vi phạm, Phụ cấp, Ngày nghĩ của nhân viên.
Em muốn nhờ anh chị viết giúp em đoạn Code để tổng hợp hàng tháng.
Em có gửi File kèm theo. Mong nhận được sự giúp đở của anh chị

Thân!

levanduyet · 14/7/08

Nếu viết code thì dễ rồi. Nhưng theo tôi bạn có thể lập công thức (chứ chưa cần phải viết code).

Lê Văn Duyệt

ZzNHCzZ · 14/7/08

Nếu viết code thì dễ rồi. Nhưng theo tôi bạn có thể lập công thức (chứ chưa cần phải viết code).

Lê Văn Duyệt

Em biết vẫn có thể lập công thức cho bài này. Nhưng vì muốn dùng VBA nên nhờ các anh/chị viết giùm em đoạn code.
Em chân thành cám ơn

Thân!

anhtuan1066 · 14/7/08

Viết hàm cho bài toán này ko phải là ko dc, nhưng hơi..buồn cười.. vì chả lẽ ta phải viết lại những gì mà SUMPRODUCT đã cung cấp sẳn (hàm cũng chưa chắc đã ngắn hơn, mà tốc độ tính toán thì chắc đến 90% là thua cái có sẳn)
(tôi cũng đã bắt tay thử viết nhưng cuối cùng thấy mắc cười qua nên lại.. thôi)

ZzNHCzZ · 14/7/08

Viết hàm cho bài toán này ko phải là ko dc, nhưng hơi..buồn cười.. vì chả lẽ ta phải viết lại những gì mà SUMPRODUCT đã cung cấp sẳn (hàm cũng chưa chắc đã ngắn hơn, mà tốc độ tính toán thì chắc đến 90% là thua cái có sẳn)
(tôi cũng đã bắt tay thử viết nhưng cuối cùng thấy mắc cười qua nên lại.. thôi)

Ý anh là dùng Công thức hay và nhanh hơn à?
Tại Sheet báo cáo dữ liệu sẽ tự cập nhật sang. Mình ko phải nhập gì cả. Trừ chọn tháng và Click button.
Nếu dùng công thức thì như thế nào vậy anh? anh cho em xin File nhé.

Thân!

anhtuan1066 · 14/7/08

ZzNHCzZ đã viết:
Ý anh là dùng Công thức hay và nhanh hơn à?
Tại Sheet báo cáo dữ liệu sẽ tự cập nhật sang. Mình ko phải nhập gì cả. Trừ chọn tháng và Click button.
Nếu dùng công thức thì như thế nào vậy anh? anh cho em xin File nhé.

Thân!

Tôi ko nói công thức nhanh hơn! Tôi muốn nói việc tạo UDF trong bài toán này là ko cần thiết!
Xem file đính kèm đây! Xem thử bạn chọn tháng và năm nó tính đúng ko nha!
ANH TUẤN

ndu96081631 · 14/7/08

Bảng tính này nếu bạn chịu khó thiết kế tất cả các sheet có cùng 1 cấu trúc thì việc tính toán còn dể dàng hơn nữa!
Tôi đang thắc mắc: Phần Vi phạm sao bạn không cho chung vào 1 sheet mà lại tách ra thành Data và Data1 thế kia!
Nếu là tôi thì tôi sẽ:
1> Cho 2 sheet VIPHAM vào 1 sheet
2> Tất cả các sheet đều sẽ có cấu trúc:

a) Cột A: Họ và tên
b) Cột B: Bộ phận
c) Cột C: Nội dung
d) Cột D: Ngày
e) Cột E: Tiền (hoặc số phép)

(Hoặc cấu trúc gì đó tùy bạn, nhưng phải thống nhất chung)
Như vậy thậm chí bạn có thể tạo 3 name động là đủ dùng cho tất cả các sheet (việc dò tìm trên sheet nào sẽ phụ thuộc vào tên của tiêu đề cột bên sheet BAOCAO)...
Trong khi với cách bố trí dử liệu như file của bạn thì phải mất đến 12 name ( rất khó quản lý), nhìn vào hoa mắt luôn!
Biểu diển thử cách mà tôi vừa nói ở trên nha (chỉ 3 name)... Mai này dù bạn có thêm bao nhiêu sheet nữa cũng chỉ có 3 name ấy là đủ

ZzNHCzZ · 15/7/08

Bảng tính này nếu bạn chịu khó thiết kế tất cả các sheet có cùng 1 cấu trúc thì việc tính toán còn dể dàng hơn nữa!
Tôi đang thắc mắc: Phần Vi phạm sao bạn không cho chung vào 1 sheet mà lại tách ra thành Data và Data1 thế kia!

Em làm vậy là cố ý và có mục đích của nó.
Mổi sheet có 1 cấu trúc riêng nhưng đều có 3 cột: Name, bộ phận, Tiền.

Tôi ko nói công thức nhanh hơn! Tôi muốn nói việc tạo UDF trong bài toán này là ko cần thiết!
Xem file đính kèm đây! Xem thử bạn chọn tháng và năm nó tính đúng ko nha!
ANH TUẤN

Sao em thêm nguyen van E mà Sheet baocao không thấy nguyen van E dau het vay anh? Em muốn Cột Name và cộ Bộ phận sheet Báo cáo cũng tự cập nhật kìa.
Hix.. Nảmecuanh nhiều quá, em đang mò tìm hiểu. Xỉu luôn.

Thân!

ZzNHCzZ · 15/7/08

Em muốn cột Name và Bộ Phận sheet Baocao cũng có Công thức tự cập nhật. Không phải nhập tay vào đâu ạ.
Các anh giúp em thêm đoạn công thức đo nhé

Thân!

SA_DQ · 15/7/08

Hoàn toàn bằng VBA đây, kể cả nhập liệu; Xin mời tham khảo

:-=

Xem trong file đính kèm

PHP:

Option Explicit:           Option Base 1

Sub TongHop()
 Dim lRow As Long, Jj As Long, Ww As Long, Zz As Long
 Dim Clls As Range, Rng As Range
 
 Application.ScreenUpdating = False:
 With Sheets("CSDL")
   lRow = .[a65432].End(xlUp).Row + 9
   AdvFilter .Range("A1:F" & lRow), .Range("M1:M2"), .Range("I5:l5")
   lRow = .[i65432].End(xlUp).Row + 9
   AdvFilter .Range("I5:I" & lRow), .Range("I5"), .Range("N5"), True
   Sheets("Baocao").Range("C5:G" & lRow + 9).Clear
   lRow = .[N65432].End(xlUp).Row
   If lRow = 5 Then Exit Sub
   ReDim MgDL(lRow + 5, 5) As Variant '-5'
   
   For Jj = 6 To lRow
      For Zz = 2 To .[s65432].End(xlUp).Row
         If .Cells(Jj, 14) = .Cells(Zz, 19) Then
            MgDL(Jj - 5, 1) = .Cells(Zz, 18)
            MgDL(Jj - 5, 2) = .Cells(Zz, 21)
            For Each Clls In .Range("I6:I" & .[i65432].End(xlUp).Row)
               If Clls = .Cells(Jj, 14) And (Clls.Offset(, 2) = "F1" Or _
                  Clls.Offset(, 2) = "F2") Then
                  MgDL(Jj - 5, 3) = MgDL(Jj - 5, 3) + Clls.Offset(, 3)
               ElseIf Clls = .Cells(Jj, 14) And Clls.Offset(, 2) = "FC" Then
                  MgDL(Jj - 5, 4) = MgDL(Jj - 5, 4) + Clls.Offset(, 3)
               ElseIf Clls = .Cells(Jj, 14) And Clls.Offset(, 2) = "Ph" Then
                  MgDL(Jj - 5, 5) = MgDL(Jj - 5, 5) + Clls.Offset(, 3)
               End If
            Next Clls
         End If
   Next Zz, Jj
 End With
 For Jj = 5 To (lRow + 5)
   Cells(Jj, 3) = MgDL(Jj - 4, 1):  Cells(Jj, 4) = MgDL(Jj - 4, 2)
   Cells(Jj, 5) = MgDL(Jj - 4, 3):  Cells(Jj, 6) = MgDL(Jj - 4, 4)
   Cells(Jj, 7) = MgDL(Jj - 4, 5)
 Next Jj
End Sub

Mã:

Sub AdvFilter(rRng As Range, cRng As Range, dRng As Range, Optional Uni As Boolean = False)
   rRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=cRng, CopyToRange:=dRng, Unique:=Uni
End Sub

ZzNHCzZ · 15/7/08

Con cám ơn Bác Sa!
Nhưng File của con đâu có gộp các sheet lại.
Con để riêng vì có mục đích mừ

Thân!

ndu96081631 · 15/7/08

Bài toán này còn nhiều vấn đề phải xem xét lại lắm (lý do là tác giã không chịu sửa phần nhập liệu)
Tôi đang nghĩ không biết cách nào để phân biệt TÊN với nhau? Trùng tên thì sao? Xét đến bộ phận uh? Nhưng nhở vừa trùng bộ phận lại trùng tên luôn thì sao?
Có lẻ thiếu phần quan trọng nhất, đó là MÃ
Hay nói đúng hơn là: Dử liệu không chuẩn, rất khó để làm được bất cứ việc gì!
(hay nói thật chính xác hơn nữa là: Tôi thấy dử liệu kiểu này cũng cãm thấy rất lười khi bắt tay vào làm)

ZzNHCzZ · 15/7/08

Bài toán này còn nhiều vấn đề phải xem xét lại lắm (lý do là tác giã không chịu sửa phần nhập liệu)
Tôi đang nghĩ không biết cách nào để phân biệt TÊN với nhau? Trùng tên thì sao? Xét đến bộ phận uh? Nhưng nhở vừa trùng bộ phận lại trùng tên luôn thì sao?
Có lẻ thiếu phần quan trọng nhất, đó là MÃ
Hay nói đúng hơn là: Dử liệu không chuẩn, rất khó để làm được bất cứ việc gì!
(hay nói thật chính xác hơn nữa là: Tôi thấy dử liệu kiểu này cũng cãm thấy rất lười khi bắt tay vào làm)

Dạ thưa anh nếu gộp dữ liệu lại thì em có thể tự làm được. Điều quan trọng là không gộp dữ liệu mà vẫn giải được.
Và chỉ cần quan tâm đến cột Name mà thôi. Lấy ra được cột Name từ các sheet Data là bài toán đã xong rồi.

Thân!

ndu96081631 · 15/7/08

ZzNHCzZ đã viết:
Dạ thưa anh nếu gộp dữ liệu lại thì em có thể tự làm được. Điều quan trọng là không gộp dữ liệu mà vẫn giải được.
Và chỉ cần quan tâm đến cột Name mà thôi. Lấy ra được cột Name từ các sheet Data là bài toán đã xong rồi.

Thân!

Bạn tưởng việc trích lọc duy nhất xuyên qua các sheet nó đơn giản lắm sao!
Bạn xem tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=12035
Chỉ mới 2 sheet mà đã rắc rối thế rồi!
Nếu là tôi thì chẳng dại gì tự làm khó mình như thế!
Tùy bạn vậy! Bài này tôi bó tay, mời các cao thủ khác tiếp tục!

SA_DQ · 16/7/08

ZzNHCzZ đã viết:
B Sa: Nhưng File con đâu có gộp các sheet lại, vì có mục đích mừ; Thân!

Nếu cần thì vẫn để nguyên cho Cường được; Vấn đề là cần thời gian để chuyển từ 4 sheeets sang sheeet trung gian có tên là 'CSDL' mà thôi
Việc này cò thể chờ vài ba ngày, nếu Cường thấy cần! :-=

ZzNHCzZ · 16/7/08

Hoàn thiện File Quản lý nhân viên

Lúc đầu em gom nhặt các bài toán trên diển đàn để tự làm File này, nhưng càng lúc em làm càng rối.
Hôm nay em xin gửi toàn bộ File mong mọi người xem và giúp em hoàn thiện xong File này ạ.

Y/C:
Tại Sheet Báo cáo Khi ta chọn tháng, năm xong thì Click Button Baocao. Cho ra KQ:
Name: Danh sách nhân viên trong tháng đó
Bộ phận: Theo nhân viên
Trừ Tiền: Tổng số tiền nhân viên bị trừ trong tháng
Phụ cấp: Tổng tiền phụ cấp
Trừ Phép: Tổng số ngày phép của nhân viên bị trừ trong tháng

Lưu ý: Các trường màu xanh có công thức

Mong các sư phụ giúp em!

Thân!

voda · 16/7/08

Nguyên văn bởi ZzNHCzZ
Hôm nay em xin gửi toàn bộ File mong mọi người xem và giúp em hoàn thiện xong File này ạ.

Xem file, mình có mấy ý nhỏ:
-Cấu trúc của các sheet thiếu nhất quán. Vd: Các cột: fullname, bộ phận...lúc có lúc không. Cột có cùng tên lúc ở vị trí này, lúc ở vị trí kia. Lúc tên cột là IDvantay, lúc thì vân tay...
-Nếu muốn dùng VBA để lọc báo cáo hàng tháng, bạn cần tổ chức dữ liệu cho hợp lý, chặt chẽ hơn. Nhất là các cột lấy thông tin báo cáo cần ở vị trí cố định trên các sheet . (ví dụ: cột Họ tên ở các sheet là cột B, cột Bộ phận là cột C...Có như thế viết code sẽ gọn, rõ ràng rành mạch hơn. Người làm giúp bạn đỡ nhọc nhằn.

ndu96081631 · 16/7/08

voda đã viết:
Xem file, mình có mấy ý nhỏ:
-Cấu trúc của các sheet thiếu nhất quán. Vd: Các cột: fullname, bộ phận...lúc có lúc không. Cột có cùng tên lúc ở vị trí này, lúc ở vị trí kia. Lúc tên cột là IDvantay, lúc thì vân tay...
-Nếu muốn dùng VBA để lọc báo cáo hàng tháng, bạn cần tổ chức dữ liệu cho hợp lý, chặt chẽ hơn. Nhất là các cột lấy thông tin báo cáo cần ở vị trí cố định trên các sheet . (ví dụ: cột Họ tên ở các sheet là cột B, cột Bộ phận là cột C...Có như thế viết code sẽ gọn, rõ ràng rành mạch hơn. Người làm giúp bạn đỡ nhọc nhằn.

Em cũng đồng ý với ý kiến của thầy!
Việc bố trí dử liệu nhập góp phần không nhỏ cho việc thành công của công thức! Em đã có góp ý tại bài #7 về vấn đề này rồi (thậm chí có gợi ý việc chỉnh sửa lại)...
Hic... dử liệu như vầy chả biết làm sao mà tổng hợp được!

SA_DQ · 16/7/08

Nên hòa nhập với cộng đồng, nếu không thì tự tiếp tục

Vì bạn vẫn giữ cấu trúc dữ liệu theo ý mình, nên sẽ phải tự tiếp tục thôi;

Mình làm được chứ không phải không; nhưng trái thông lệ

Chúc tham khảo & thực thi thắng lợi:

PHP:

Option Explicit:        Option Base 1
Sub BaoCaoThang()
Dim lRow As Long, SoNV As Long, jJ As Long, Zz As Long
Dim Thang As Byte, Nam As Integer

 Application.ScreenUpdating = False
 With Sheets("CSDL")
   SoNV = .[b65432].End(xlUp).Row:           ReDim MDL(SoNV, 6)
   For jJ = 1 To SoNV - 1
      MDL(jJ, 1) = .Cells(jJ + 1, 4):         MDL(jJ, 2) = .Cells(jJ + 1, 2)
      MDL(jJ, 3) = .Cells(jJ + 1, 3)
   Next jJ
 End With
 Thang = [b1]:                               Nam = [b2]
 
 With Sheets("ViPham")
   lRow = .[b65432].End(xlUp).Row
   For jJ = 2 To lRow
      If Month(.Cells(jJ, 6)) = Thang And Year(.Cells(jJ, 6)) = Nam Then
         For Zz = 1 To SoNV
            If .Cells(jJ, 2) = MDL(Zz, 1) Then
               MDL(Zz, 4) = MDL(Zz, 4) + .Cells(jJ, 8)
               Exit For
            End If
         Next Zz
      End If
   Next jJ
 End With
 
 With Sheets("Dienthoai")
   lRow = .[b65432].End(xlUp).Row
   For jJ = 2 To lRow
      If Month(.Cells(jJ, 5)) = Thang And Year(.Cells(jJ, 5)) = Nam Then
         For Zz = 1 To SoNV
            If .Cells(jJ, 2) = MDL(Zz, 2) Then
               MDL(Zz, 4) = MDL(Zz, 4) + .Cells(jJ, 7)
               Exit For
            End If
         Next Zz
      End If
   Next jJ
 End With
 
  With Sheets("ChamCong")
   lRow = .[b65432].End(xlUp).Row
   For jJ = 3 To lRow
      If Month(.Cells(jJ, 7)) = Thang And Year(.Cells(jJ, 7)) = Nam Then
         For Zz = 1 To SoNV
            If .Cells(jJ, 5) = MDL(Zz, 1) Then
               MDL(Zz, 4) = MDL(Zz, 4) + .Cells(jJ, 12)
                  MsgBox .Cells(jJ, 13)
               MDL(Zz, 6) = MDL(Zz, 6) + .Cells(jJ, 13)
               Exit For
            End If
         Next Zz
      End If
   Next jJ
 End With
'   *         *    *          *         *    * '
 Range("C6:H" & SoNV + 9).Clear
 For jJ = 6 To SoNV + 5
   Cells(jJ, 4) = MDL(jJ - 5, 2):   Cells(jJ, 5) = MDL(jJ - 5, 3)
   Cells(jJ, 6) = MDL(jJ - 5, 4)
   Cells(jJ, 8) = MDL(jJ - 5, 6)
 Next jJ
 
 
End Sub

Cần nhấn mạnh rằng VBA để giải quyết những bài toán có tính lặp lại & đưa về trường hợp lặp lại! --=0

ZzNHCzZ · 17/7/08

Dữ liệu nhất quán

Em rất cám ơn mọi người đã gop ý cho em về trình bày cấu trúc dữ liệu. Đúng là em đã tự làm khó mình, khi để dữ liệu không nhất quán.
Em đã sửa lại cấu trúc "để hòa nhập cộng đồng" xin gửi lại ạ.

Thân!

Viết VBA tạo báo cáo hàng tháng

Thành viên bị đình chỉ hoạt động

Hãy để gió cuốn đi.

Thành viên bị đình chỉ hoạt động

Thành viên gạo cội

Thành viên bị đình chỉ hoạt động

Thành viên gạo cội

File đính kèm

Huyền thoại GPE

File đính kèm

Thành viên bị đình chỉ hoạt động

Thành viên bị đình chỉ hoạt động

/(hông là gì!

File đính kèm

Thành viên bị đình chỉ hoạt động

Huyền thoại GPE

Thành viên bị đình chỉ hoạt động

Huyền thoại GPE

/(hông là gì!

Thành viên bị đình chỉ hoạt động

Thành viên tiêu biểu

Huyền thoại GPE

/(hông là gì!

Thành viên bị đình chỉ hoạt động

Facebook

Group