Tự động chỉ dẫn lấy dữ liệu từ các file excel có tên thay đổi hàng tháng (1 người xem)

[luonganhtrung]

Kính chào anh chị em,

Xin ACE giúp tôi 1 việc. Tôi muốn tạo công thức tự động để lấy dữ liệu từ các file excel thay đổi tên hàng tháng.

Hàng tháng tôi phải tổng hợp doanh thu bán hàng của tất cả các nhân viên, tại tất cả các chi nhánh. Mỗi nhân viên có 1 mã nhân viên duy nhất, không trùng nhau.

File tổng hợp dữ liệu các tháng có tên: Tonghop.xlsx
Trong đó mỗi tháng là 1 sheet. Ví dụ: tháng 9 năm 2012 thì sheet mang tên 2012-09.xlsx

Cuối tháng, mỗi chi nhánh gửi cho tôi 1 file excel, trong mỗi file đó chỉ có dữ liệu 1 tháng vừa qua.
Tên file của mỗi chi nhánh hàng tháng được đặt như sau: YYYY-MM-TenChiNhanh.xlsx
Ví dụ: 2012-09-HANOI.xlsx

Nếu chỉ phải tổng hợp 1 tháng thì đơn giản, sẽ chỉ đúng tên file của mỗi chi nhánh và cho VLOOKUP theo mã nhân viên là xong.
Nhưng tháng nào cũng phải làm thế, và tôi có gần 200 nhân viên bán hàng. Nếu hàng tháng thay đổi lại 200 dòng chỉ dẫn file excel theo tên tháng đó thì hết hơi. Vì vậy tôi muốn có 1 công thức tự động gắn vào cột "Doanh thu thang" trong các sheet của file tổng hợp. Nó sẽ tự động thay đổi chỉ dẫn tên file thành phần theo tháng tương ứng để VLOOKUP dữ liệu.

Tôi gửi kèm ví dụ là 1 file tổng hợp và 6 file thành phần (3 chi nhánh, mỗi chi nhánh 2 file tháng 9 và 10). Các bạn thử nghiên cứu và hướng dẫn tôi cách tạo công thức tự động nhé.

Thực tế thì tôi có đến 40 chi nhánh, mỗi chi nhánh có từ 4-5 nhân viên bán hàng, tháng nào mà cũng thay đổi filename trong công thức lấy dữ liệu thì mất nhiều thời gian.

Mong các bạn chỉ dẫn.
Xin cảm ơn các bạn rất nhiều.

 

[minhung78]

File mình làm có 2 sheet: dữ liệu và tổng hợp.
Sheet dữ liệu: Hàng tháng bạn chỉ cần copy các file của các CN gừi về vào sheet dữ liệu và cập nhật vào cột tháng (cột này bạn có thể coyp cho nhanh).
Sheet tổng hợp: bạn chỉ cần nhập số tháng cần tính vào ô C1 là ra kết quả.

 

[Quang_Hải]

Kính chào anh chị em,

Xin ACE giúp tôi 1 việc. Tôi muốn tạo công thức tự động để lấy dữ liệu từ các file excel thay đổi tên hàng tháng.

Hàng tháng tôi phải tổng hợp doanh thu bán hàng của tất cả các nhân viên, tại tất cả các chi nhánh. Mỗi nhân viên có 1 mã nhân viên duy nhất, không trùng nhau.

Mong các bạn chỉ dẫn.
Xin cảm ơn các bạn rất nhiều.

Nếu bạn biết xài VBA thì copy code này sử dụng nhé

Sub tonghop()
Dim FilesToOpen, SheetToUpdate, dl(), kq()
Dim X As Integer, j As Long, i As Long
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
      (filefilter:="Excel Files (*.xlsx ; *xls, *.xlsx; *.xls", MultiSelect:=True)
If TypeName(FilesToOpen) = "Boolean" Then
   MsgBox "No Files were selected"
   GoTo ExitHandler
End If
    X = 1
    While X <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(X)
        With ActiveWorkbook
            SheetToUpdate = Left(ActiveWorkbook.Name, 7)
            With .ActiveSheet
                dl = .Range(.[C3], .[C65536].End(3)).Resize(, 2).Value
            End With
        .Close False
        End With
        With Workbooks("Tonghop.xlsm").Sheets(SheetToUpdate)
            kq = .Range(.[d2], .[D65536].End(3)).Resize(, 2).Value
            For i = 1 To UBound(kq)
                For j = 1 To UBound(dl)
                    If kq(i, 1) = dl(j, 1) Then
                        kq(i, 2) = dl(j, 2)
                    End If
                Next
            Next
            .[d2].Resize(i - 1, 2) = kq
        End With
        X = X + 1
    Wend
ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub