Hỏi: Giải pháp cộng dồn số liệu của các sheet, dựa trên mã số nhân viên?! (2 người xem)

[vungoc]

Chào các bạn!

Tôi có 1 file "Bảng chấm công" trong file này các có nhiều sheet, mỗi sheet là 1 tháng trong năm (như vậy sẽ có tổng cộng là 12 sheet cho 12 tháng).

Về cấu trúc thì tất cả bảng tính ở các sheet đều giống nhau, nhưng số lượng người ở trong mỗi bảng có thể khác nhau.

Tôi muốn: Tại một sheet bất kỳ, từ cột AT đến cột AX là tổng hợp số liệu cộng dồn của các cột AN đến AQ của tất cả các sheet trước nó (có nghĩa là sheet tháng sau, sẽ cộng dồn tất cả các sheet tháng trước + chính sheet hiện hành).

Mục đích: Để khi mở bảng công ra, thấy được tính tới thời điểm đó, người nào đã nghỉ bao nhiêu ngày, trễ bao nhiêu, sớm bao nhiêu,...

Ghi chú: Cột AX = Cột AV + Cột AW (Tổng cộng ngày nghỉ = nghỉ phép + nghỉ không phép)

Trước đây tôi dùng hàm Vlookup, căn cứ mã số nhân viên để cộng dồn số liệu tương ứng với mỗi mã nhân viên của tất cả các sheet trước nó và chính sheet hiện hành cộng lại. Nhưng dùng hàm này thấy dài dòng quá, vả lại người nào không có tên ở một sheet nào đó thì nó sẽ báo lỗi.

Nay nhờ các bạn xem dùm có phương pháp nào khác hiệu quả và chính xác hơn không (dùng hàm hoặc VBA đều được)?

Các bạn vui lòng xem file đính kèm. Xin cảm ơn mọi người!

Thân chào!

 

[SA_DQ]

Đường hướng dùng macro trước, nếu mình mình sẽ làm như sau:

Lập thêm 1 Sheets để chép dồn dữ liệu tổng hợp chấm công của 12 tháng vô đó;

Nhưng vì không đọc được font, nên chưa biết tổng hợp vùng nào trên trang tính
'At:AX' hay 'AM:AR' lại với nhau

Thân ái!

 

[vungoc]

Đường hướng dùng macro trước, nếu mình mình sẽ làm như sau:

Lập thêm 1 Sheets để chép dồn dữ liệu tổng hợp chấm công của 12 tháng vô đó;

Nhưng vì không đọc được font, nên chưa biết tổng hợp vùng nào trên trang tính
'At:AX' hay 'AM:AR' lại với nhau

Thân ái!

Chào bác:

Ý em là tại mỗi sheet, vùng AT:AX sẽ tổng hợp vùng AN:AQ của tất cả các sheet khác đứng trước nó (và luôn cả vùng AN:AQ của chính sheet đó nữa).

Mục đích: Để khi mở bảng công ra, thấy được tính tới thời điểm đó, người nào đã nghỉ bao nhiêu ngày, trễ bao nhiêu, sớm bao nhiêu,...

Tuy nhiên, bác có thể triển khai theo cách suy luận của bác, miễn sao đạt được mục đích nêu trên!

Thân ái!

 

[Cao Mạnh Sơn]

Chào bác:

Ý em là tại mỗi sheet, vùng AT:AX sẽ tổng hợp vùng AN:AQ của tất cả các sheet khác đứng trước nó (và luôn cả vùng AN:AQ của chính sheet đó nữa).

Mục đích: Để khi mở bảng công ra, thấy được tính tới thời điểm đó, người nào đã nghỉ bao nhiêu ngày, trễ bao nhiêu, sớm bao nhiêu,...

Tuy nhiên, bác có thể triển khai theo cách suy luận của bác, miễn sao đạt được mục đích nêu trên!

Thân ái!

Anh dùng chức năng consolidat của EX là được rồi.Bất cứ thời điểm nào trong năm mở vùng dữ liệu tổng hợp ra vào lại chức năng này nhấn OK,dữ liệu mới lại được cập nhật.
anh Ngọc thử nhé

 

[vungoc]

Anh dùng chức năng consolidat của EX là được rồi.Bất cứ thời điểm nào trong năm mở vùng dữ liệu tổng hợp ra vào lại chức năng này nhấn OK,dữ liệu mới lại được cập nhật.
anh Ngọc thử nhé

Thực tình mà nói, chức năng này mình cũng chưa rành lắm bác ạ;
Nếu được, bác có thể làm thử mình với nhé / Tks!

 

[tsf]

Anh hỏi excel cái là nó chỉ cho anh


Vui lòng xem file đính kèm nhe, anh xem cái sheet group nhe!

Nhắn YM cho anh, mà anh tắt rồi

 

[ChanhTQ@]

.. .
Mục đích: Để khi mở bảng công ra, thấy được tính tới thời điểm đó, người nào đã nghỉ bao nhiêu ngày, trễ bao nhiêu, sớm bao nhiêu,...

Trước đây tôi dùng hàm Vlookup, căn cứ mã số nhân viên để cộng dồn số liệu tương ứng với mỗi mã nhân viên của tất cả các sheet trước nó và chính sheet hiện hành cộng lại. Nhưng dùng hàm này thấy dài dòng quá, vả lại người nào không có tên ở một sheet nào đó thì nó sẽ báo lỗi.

Hình như vẫn có cái gì đó chưa ổn Vũ Ngọc à! Ví dụ như sau:
Tháng đầu có 3 CNV A, B & C; Thàng sau thêm Ông D, Nhưng tháng tiếp theo ông A chuyển sang bộ phận khác & ông C nghĩ việc thì sao đây? Mình hình dung lúc đó danh sách sẽ lo le tứ tung hết đó;
Hình như bảng tổng hợp này phải để ở trang tính riêng thì phải(?!)
Vì có thể ông A sau tháng tám nào đó lại được điều động về không chừng!

 

[vungoc]

Anh hỏi excel cái là nó chỉ cho anh


Vui lòng xem file đính kèm nhe, anh xem cái sheet group nhe!

Nhắn YM cho anh, mà anh tắt rồi

Cảm ơn tfs
Oh, nhưng mà cái sheet group này khó xem lắm tfs ơi, mình kg muốn số liệu tổng hợp như vậy!

Hình như vẫn có cái gì đó chưa ổn Vũ Ngọc à! Ví dụ như sau:
Tháng đầu có 3 CNV A, B & C; Thàng sau thêm Ông D, Nhưng tháng tiếp theo ông A chuyển sang bộ phận khác & ông C nghĩ việc thì sao đây? Mình hình dung lúc đó danh sách sẽ lo le tứ tung hết đó;
Hình như bảng tổng hợp này phải để ở trang tính riêng thì phải(?!)
Vì có thể ông A sau tháng tám nào đó lại được điều động về không chừng!

Vâng, chính vì danh sách nhân viên về số lượng không nhất quán (có thể tăng hoặc giảm từng tháng), nên trước đây em dùng vlookup không tiện và dài dòng. Việc tổng hợp chỉ cần sao cho tại sheet đó nó tổng hợp được số liệu của tất cả các sheet tháng trước nó (và chính sheet đó). Với chính những mã nhân viên hiện hành (tại sheet đó).

Những nhân viên được điều động qua lại giữa các bộ phận thì không thành vấn đề, vì mục đích của em chỉ tổng hợp số liệu theo mã nhân viên, chứ không theo bộ phận, mà mã nhân viên thì mỗi người duy nhất chỉ 1, không thay đổi.

Nhưng, bác vẫn có thể làm theo cách của bác để em tham khảo, học tập!

Cảm ơn bác!

 

[yeudoi]

Em thấy thế này anh Vungoc ah. Bên em là em làm một form để nhập liệu và sau đó lưu vào data như vậy khi cần xem mình cũng có thể mở ra xem tháng nào cũng được, còn muốn báo cáo thì mình làm một form báo cáo riêng, bởi bảng công bên doanh nghiệp sản xuất cũng rất phức tạp.
1. Khi công nhân A ở bộ phận 1 tháng sau chuyển sang bộ phận 2 hoặc đang làm đây sau đó tăng cường cho bộ phận khác vài ngày.
2. Tháng này có làm nhưng sang tháng sau nghỉ sinh nhiều lúc không có tên trong bảng công
Vì vậy em thấy anh bố trí mỗi tháng 1 sheet là không hợp lý.

 

[ThuNghi]

Chào các bạn!

Tôi có 1 file "Bảng chấm công" trong file này các có nhiều sheet, mỗi sheet là 1 tháng trong năm (như vậy sẽ có tổng cộng là 12 sheet cho 12 tháng).

Về cấu trúc thì tất cả bảng tính ở các sheet đều giống nhau, nhưng số lượng người ở trong mỗi bảng có thể khác nhau.

Tôi muốn: Tại một sheet bất kỳ, từ cột AT đến cột AX là tổng hợp số liệu cộng dồn của các cột AN đến AQ của tất cả các sheet trước nó (có nghĩa là sheet tháng sau, sẽ cộng dồn tất cả các sheet tháng trước + chính sheet hiện hành).

Mục đích: Để khi mở bảng công ra, thấy được tính tới thời điểm đó, người nào đã nghỉ bao nhiêu ngày, trễ bao nhiêu, sớm bao nhiêu,...

Ghi chú: Cột AX = Cột AV + Cột AW (Tổng cộng ngày nghỉ = nghỉ phép + nghỉ không phép)

Trước đây tôi dùng hàm Vlookup, căn cứ mã số nhân viên để cộng dồn số liệu tương ứng với mỗi mã nhân viên của tất cả các sheet trước nó và chính sheet hiện hành cộng lại. Nhưng dùng hàm này thấy dài dòng quá, vả lại người nào không có tên ở một sheet nào đó thì nó sẽ báo lỗi.

Nay nhờ các bạn xem dùm có phương pháp nào khác hiệu quả và chính xác hơn không (dùng hàm hoặc VBA đều được)?

Các bạn vui lòng xem file đính kèm. Xin cảm ơn mọi người!

Thân chào!

Tôi đề xuất phương án như sau:
- Có 1 danh mục nhân viên, trong đó có cột ghi rõ ngày hết hạn HĐ (Còn hay hết làm).
- Tạo 1 nút tạo bảng chấm công tháng (theo tháng). Và ta sẽ cập dữ liệu vào.
- Sang tháng khác thì cũng làm lại và nhất là phải update danh mục nhé.
- Có 1 bảng theo dõi tổng hợp. Gồm tất cả những NV có làm việc trong năm. Tại SH này mình có thể tổng hợp từ tháng mấy đến tháng mấy. Cái này thiết nghĩ khá đơn giản.

Còn theo dữ liệu của file trên thì cũng khá dễ.
- Tạo thêm 1 sh Tmp, copy toàn bộ dữ liệu từ AM->AQ của các tháng đến tháng báo cáo.
- Tại sh tháng BC, dùng sumif là xong.

Bác dùng tạm code này

Option Explicit
Dim shName As String, sName As Long, iShName As String
Dim i As Long, iR As Long, eRow As Long, endR As Long, iCol As Long
Sub TonghopCC()
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
iR = 2
shName = ActiveSheet.Name
sName = CLng(Month(Sheets(shName).Range("AH2")))
If WksExists("tmp") = False Then
  Sheets.Add
  ActiveSheet.Name = "tmp"
Else
  Sheets("tmp").Cells.Clear
End If
Sheets(shName).Select
endR = iR
For i = 1 To sName
  
  iShName = "(" & i & ")"
  eRow = Sheets(iShName).Cells(65000, 1).End(xlUp).Row - 1
  With Sheets("tmp")
    .Range(.Cells(endR, 1), .Cells(endR + eRow - 13, 1)).Value = -iShName
    .Range(.Cells(endR, 2), .Cells(endR + eRow - 13, 2)).Value = Sheets(iShName).Range("B13:B" & eRow).Value  'MsNV'
    .Range(.Cells(endR, 3), .Cells(endR + eRow - 13, 6)).Value = Sheets(iShName).Range("AN13:AQ" & eRow).Value
    endR = endR + eRow - 13
    'Dat name cho de'
    For iCol = 1 To 5
      .Range(.Cells(iR, 2), .Cells(endR, 6)).Offset(, iCol - 1).Resize(, 1).Name = "Cot" & iCol
    Next
    
  End With
  'MsgBox eRow
Next
Range("AT13:AX" & eRow).ClearContents
For iCol = 2 To 5
  With Range("AT13:AT" & eRow).Offset(, iCol - 2).Resize(, 1)
    .FormulaR1C1 = "=SUMIF(Cot1,RC2,Cot" & iCol & ")"
    .Value = .Value
  End With
  
Next
With Range("AX13:AX" & eRow)
  .FormulaR1C1 = "=RC48+RC49"
  .Value = .Value
End With
'Xoa name'
ActiveWorkbook.Names("Cot1").Delete
ActiveWorkbook.Names("Cot2").Delete
ActiveWorkbook.Names("Cot3").Delete
ActiveWorkbook.Names("Cot4").Delete
ActiveWorkbook.Names("Cot5").Delete
Sheets("tmp").Delete
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[SA_DQ]

Macro chép & cộng dồn sang bảng tổng hợp các tháng từ đầu năm

Option Explicit
Sub THop()
 Dim StrC As String:                      Dim Sh As Worksheet
 Dim sRng As Range, Rng As Range, Clls As Range, bRng As Range
 Dim Col As Byte
 Dim Zz As Long, Rw As Long, lRw As Long
 
 Application.ScreenUpdating = False:      Set Sh = Sheets("THop")
 Sh.Cells.Clear:                          [a3].Value = "Ma"
 For Zz = 1 To Worksheets.Count - 1
   StrC = "(" & Zz & ")"
   Sheets(StrC).Select:             Rw = [B65500].End(xlUp).Row
      
   Range([b14], [b14].End(xlDown)).Copy Destination:=Sh.[A65500].End(xlUp).Offset(1)
   Set Rng = Sh.Range(Sh.[a3], Sh.[A65500].End(xlUp))
   lRw = Rng.Rows.Count
   With Sh.Cells(1, Zz * 5 - 3)
      .Resize(3, 4).Value = Range("AT10").Resize(3, 4).Value
      .Value = "TONG HOP TU THANG 01 DEN THANG " & Right("0" & Zz, 2)
      .Interior.ColorIndex = 35
   End With
   If Zz = 1 Then
      With Sh.[b4].Resize(Rw, 4)
         .Value = [AN14].Resize(Rw, 4).Value
         .Offset(, 5).Value = [AN14].Resize(Rw, 4).Value
      End With
   Else
      For Each Clls In [b14].Resize(Rw)
         Set sRng = Rng.Find(Clls, , xlFormulas, xlWhole)
         If Not sRng Is Nothing Then
            sRng.Interior.ColorIndex = 38
            For Col = 0 To 3
               With sRng.Offset(, 5 * Zz - 3 + Col)
                  .Value = .Value + Clls.Offset(, 38 + Col).Value
               End With
            Next Col
         End If
      Next Clls
      With Sh.Cells(4, 5 * Zz - 3).Resize(lRw, 4)
         .Offset(, 5).Value = .Value
      End With
   End If
 Next Zz
 For Zz = (lRw + 9) To 3 Step -1
   If Sh.Cells(Zz, "A").Interior.ColorIndex <> 38 Then _
      Sh.Cells(Zz, "A").EntireRow.Delete
 Next Zz
End Sub

 

[vungoc]

Tôi đề xuất phương án như sau:
- Có 1 danh mục nhân viên, trong đó có cột ghi rõ ngày hết hạn HĐ (Còn hay hết làm).
- Tạo 1 nút tạo bảng chấm công tháng (theo tháng). Và ta sẽ cập dữ liệu vào.
- Sang tháng khác thì cũng làm lại và nhất là phải update danh mục nhé.
- Có 1 bảng theo dõi tổng hợp. Gồm tất cả những NV có làm việc trong năm. Tại SH này mình có thể tổng hợp từ tháng mấy đến tháng mấy. Cái này thiết nghĩ khá đơn giản.

Còn theo dữ liệu của file trên thì cũng khá dễ.
- Tạo thêm 1 sh Tmp, copy toàn bộ dữ liệu từ AM->AQ của các tháng đến tháng báo cáo.
- Tại sh tháng BC, dùng sumif là xong.

Bác dùng tạm code này

Option Explicit
Dim shName As String, sName As Long, iShName As String
Dim i As Long, iR As Long, eRow As Long, endR As Long, iCol As Long
Sub TonghopCC()
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
iR = 2
shName = ActiveSheet.Name
sName = CLng(Month(Sheets(shName).Range("AH2")))
If WksExists("tmp") = False Then
  Sheets.Add
  ActiveSheet.Name = "tmp"
Else
  Sheets("tmp").Cells.Clear
End If
Sheets(shName).Select
endR = iR
For i = 1 To sName
  
  iShName = "(" & i & ")"
  eRow = Sheets(iShName).Cells(65000, 1).End(xlUp).Row - 1
  With Sheets("tmp")
    .Range(.Cells(endR, 1), .Cells(endR + eRow - 13, 1)).Value = -iShName
    .Range(.Cells(endR, 2), .Cells(endR + eRow - 13, 2)).Value = Sheets(iShName).Range("B13:B" & eRow).Value  'MsNV'
    .Range(.Cells(endR, 3), .Cells(endR + eRow - 13, 6)).Value = Sheets(iShName).Range("AN13:AQ" & eRow).Value
    endR = endR + eRow - 13
    'Dat name cho de'
    For iCol = 1 To 5
      .Range(.Cells(iR, 2), .Cells(endR, 6)).Offset(, iCol - 1).Resize(, 1).Name = "Cot" & iCol
    Next
    
  End With
  'MsgBox eRow
Next
Range("AT13:AX" & eRow).ClearContents
For iCol = 2 To 5
  With Range("AT13:AT" & eRow).Offset(, iCol - 2).Resize(, 1)
    .FormulaR1C1 = "=SUMIF(Cot1,RC2,Cot" & iCol & ")"
    .Value = .Value
  End With
  
Next
With Range("AX13:AX" & eRow)
  .FormulaR1C1 = "=RC48+RC49"
  .Value = .Value
End With
'Xoa name'
ActiveWorkbook.Names("Cot1").Delete
ActiveWorkbook.Names("Cot2").Delete
ActiveWorkbook.Names("Cot3").Delete
ActiveWorkbook.Names("Cot4").Delete
ActiveWorkbook.Names("Cot5").Delete
Sheets("tmp").Delete
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Cảm ơn anh ThuNghi nhiều lắm, cái này rất đúng với mong đợi của em. Ôi, anh thật tuyệt!!!
Cho em gửi lời cảm ơn bác SA luôn, chúc bác luôn mạnh giỏi!

 

[Cao Mạnh Sơn]

Thực tình mà nói, chức năng này mình cũng chưa rành lắm bác ạ;
Nếu được, bác có thể làm thử mình với nhé / Tks!

Thực ra việc tổng hợp công kiểu này tôi thấy thường chỉ áp dụng vào dịp các này lễ và cuối năm nên tôi thường dùng chức năng consolidat,còn việc để hiển thị ngay trong bảng chấm công ở từng tháng thì tôi cũng chưa làm.có 1 vấn đề hạn chế là chức năng này không tự động update khi dữ liệu thay đổi mà ta phải tác động lại vùng dữ liệu đó trước khi muốn xem chính xác.
Cách làm như sau:Vì tiêu đề các cột của bảng chấm công có các cell bị merge nên tôi chèn thêm 1 dòng và đánh số thứ tự cột.Tạo thêm sheet mới có các tiêu đề cột giống với phom chấm công các tháng.
bây giờ ta đưa trỏ chuột tới vị trí cần đặt dữ liệu.ở đây vì ta tổng hợp theo mã số nhân viên nên ta đưa đến cột đánh số 2(trong sheet1 bôi vàng).chọn Data/consolidat.Vùng Function ta chọn hàm Sum.Vùng Reference ta chọn từ tháng 1 bắt đầu từ tiêu đề cột số 2 đến hết vùng dữ liệu trong bảng chấm công sau đó ta nhấn Add để đưa dữ liệu hiển thị vào vùng All Reference.Tiếp đến ta chọn sang tháng 2 cho đến hết tháng 12.Lưu ý nếu cần lấy dữ liệu đến tháng nào thì ta chỉ Add đến tháng đó.Anh đưa chuột đến cell bôi vàng trong sheet 1 và chọn Data/consolidat sẽ thấy các vùng dữ liệu được tổng hợp.
Trong file tôi lấy lần lượt đủ 12 vùng cho 12 tháng,sau mỗi tháng tôi lại xóa đi 1 vùng dữ liệu trong All Reference.(Thực ra tôi thấy không cần thiết nhưng làm thử cho đủ)
Sau cùng tại vùng Lable use in anh chọn Top Row và Left column rồi nhấn OK.
Sau này khi anh cập nhật thêm dữ liệu trong bảng chấm công anh chỉ cần vào lại Cell bôi vàng và chọn Data/consolidat/OK lập tức dữ liệu được cộng dồn đầy đủ,không sợ nhầm cột,không sợ nhầm tên nhân viên kể cả anh xắp xếp tiêu đề cột không giống nhau hay thứ tự mã số nhân viên khác nhau.
ở đây EX chỉ Sum các cell có dữ liệu kiểu số còn kiểu chữ là nó bỏ qua nên chỉ có cột mã số nhân viên do mình chọn làm tiêu đề dòng có đầy đủ, cột tên NV bị bỏ trắng,ngày công được cộng dồn.Phần các cột không cần thiết tôi Hide cho dễ nhìn.
Tôi thấy dùng Macro đã đạt mục đích của anh nhưng vẫn đưa cách làm này lên vì anh nói chưa rõ và tôi thấy có nhiều người cũng chưa áp dụng cách này cho công việc của mình vì không phải ai cũng có thể viết được code,kể cả tôi nữa.
anh tham khảo file đính kèm nhé