tổng hợp dữ liệu từ nhiều sheet vào một sheet (1 người xem)

vudoanttg · 3/10/11

Tôi muốn tạo một sheet tổng hợp từ nhiều sheet khác nhau để so sánh. làm thế nào để lấy được số tiền từ các tài khoản giống nhau từ tháng8 và tháng9 vào sheet tổng hợp (she et tổng hợp sẽ có tất cả các loại tài khoản có phát sinh số tiền của các sheet tháng)trong khi có những tài khoản phát sinh ở tháng8 nhưng không phát sinh ở tháng9 và ngược lại (ví dụ đính kèm). nhờ các bạn giưp đỡ

ConnectExcel · 3/10/11

Sheet tổng hợp của bạn thiếu TK so với sheet ở tháng 8,mình làm như vậy không biết có đúng ý bạn không.?

viehoai · 3/10/11

vudoanttg đã viết:
Tôi muốn tạo một sheet tổng hợp từ nhiều sheet khác nhau để so sánh. làm thế nào để lấy được số tiền từ các tài khoản giống nhau từ tháng8 và tháng9 vào sheet tổng hợp (she et tổng hợp sẽ có tất cả các loại tài khoản có phát sinh số tiền của các sheet tháng)trong khi có những tài khoản phát sinh ở tháng8 nhưng không phát sinh ở tháng9 và ngược lại (ví dụ đính kèm). nhờ các bạn giưp đỡ

Mình không phải kế toán suy đoán làm đại thử

Lê Duy Thương · 3/10/11

vudoanttg đã viết:
Tôi muốn tạo một sheet tổng hợp từ nhiều sheet khác nhau để so sánh. làm thế nào để lấy được số tiền từ các tài khoản giống nhau từ tháng8 và tháng9 vào sheet tổng hợp (she et tổng hợp sẽ có tất cả các loại tài khoản có phát sinh số tiền của các sheet tháng)trong khi có những tài khoản phát sinh ở tháng8 nhưng không phát sinh ở tháng9 và ngược lại (ví dụ đính kèm). nhờ các bạn giưp đỡ

dùng sumif kết hợp indirect là ok

PHP:

=SUMIF(INDIRECT(--RIGHT(B$2,2)&"!$A1:$A500"),$A3,INDIRECT(--RIGHT(B$2,2)&"!$b1:$b500"))

xem thêm file

vudoanttg · 3/10/11

cám ơn bạn ý của mình đúng là như vậy. Nhưng bạn có thể giúp mình hoàn thiện hơn chút nữa nhé. Cụ thể là: nếu tháng 10 mà số hiệu tài khoản lại nhiều hơn tháng 8 thì mình lại phải thay đổi shet tổng hợp sang tháng 10 sao? có cách nào để số liệu update tự động được không(khả năng từ nay đến cuối năm sẽ phát sinh thêm nhiều số hiệu tài khoản mới) bạn ạ?

vudoanttg · 3/10/11

ConnectExcel đã viết:
Sheet tổng hợp của bạn thiếu TK so với sheet ở tháng 8,mình làm như vậy không biết có đúng ý bạn không.?

cám ơn bạn ý của mình đúng là như vậy. Nhưng bạn có thể giúp mình hoàn thiện hơn chút nữa nhé. Cụ thể là: nếu tháng 10 mà số hiệu tài khoản lại nhiều hơn tháng 8 thì mình lại phải thay đổi shet tổng hợp sang tháng 10 sao? có cách nào để số liệu update tự động được không(khả năng từ nay đến cuối năm sẽ phát sinh thêm nhiều số hiệu tài khoản mới) bạn ạ?

Lê Duy Thương · 3/10/11

vudoanttg đã viết:
cám ơn bạn ý của mình đúng là như vậy. Nhưng bạn có thể giúp mình hoàn thiện hơn chút nữa nhé. Cụ thể là: nếu tháng 10 mà số hiệu tài khoản lại nhiều hơn tháng 8 thì mình lại phải thay đổi shet tổng hợp sang tháng 10 sao? có cách nào để số liệu update tự động được không(khả năng từ nay đến cuối năm sẽ phát sinh thêm nhiều số hiệu tài khoản mới) bạn ạ?

bạn thử dùng code của gpe tôi chế thêm xem sao

PHP:

Sub add_acount()
  Dim Dic, ws As Worksheet, iRow As Long, i As Long, Arr(), TmpArr, Tmp
  On Error Resume Next
  Application.ScreenUpdating = False
  Sheet1.Range("a3:f5000").ClearContents
  Set Dic = CreateObject("Scripting.Dictionary")
  For Each ws In Worksheets
    If ws.Name <> Sheet1 Then
      TmpArr = ws.Range(ws.[a2], ws.[a5000].End(xlUp)).Value
      For iRow = 1 To UBound(TmpArr, 1)
        Tmp = TmpArr(iRow, 1)
        If Not IsEmpty(Tmp) Then
          If Not Dic.Exists(Tmp) Then
          Dic.Add Tmp, ""
          i = i + 1
          ReDim Preserve Arr(1 To 1, 1 To i)
          Arr(1, i) = TmpArr(iRow, 1)
       '.................................
          End If
        End If
      Next
    End If
  Next
  With Sheet1
  .Range("a3").Resize(i, 1) = WorksheetFunction.Transpose(Arr)
  .Range("B3").FormulaR1C1 = _
        "=SUMIF(INDIRECT(--RIGHT(R2C,2)&""!$A1:$A5000""),RC1,INDIRECT(--RIGHT(R2C,2)&""!$b1:$b5000""))"
    .Range("B3").AutoFill Destination:=Range("B3:F3"), Type:=xlFillDefault
    .Range("B3:F3").AutoFill Destination:=Range("B3:f" & [a5000].End(xlUp).Row)
    .Range("B3:f" & [a5000].End(xlUp).Row).Value = Range("B3:f" & [a5000].End(xlUp).Row).Value
     
      End With
    Application.ScreenUpdating = True
 End Sub

concogia · 3/10/11

Bài này dùng VBA thôi
Tham gia với Duy Thương cho dzui

Mã:

Private Sub Worksheet_Activate()
    Dim d, Ws, I, K, kK, kKk, Thang, Mg(), Vung
    Set Thang = [A2:F2]
    ReDim Mg(1 To 1000, 1 To Sheets.Count)
    Set d = CreateObject("scripting.dictionary")
        For Each Ws In Worksheets
            If Ws.Name <> "TongHop" Then
                Vung = Ws.Range(Ws.[a2], Ws.[a10000].End(xlUp)).Resize(, 2).Value
                K = Application.WorksheetFunction.Match(Ws.Name, Thang, 0)
                    For I = 1 To UBound(Vung)
                        If Not d.exists(Vung(I, 1)) Then
                            kK = kK + 1
                            d.Add Vung(I, 1), kK
                            Mg(kK, 1) = Vung(I, 1)
                            Mg(kK, K) = Vung(I, 2)
                        Else
                            kKk = d.Item(Vung(I, 1))
                            Mg(kKk, K) = Vung(I, 2)
                        End If
                    Next I
            End If
        Next
    [A3:F10000].ClearContents
    [A3].Resize(kK, Sheets.Count) = Mg
End Sub

Bạn cứ thên dữ liệu ở các tháng, cho shett TongHop xem kết quả
Thân

TrungChinhs · 3/10/11

Tại sao các bạn không sử dụng phương thức Find nhỉ ? thuật giải nằm ngay trong câu hỏi:
Dùng vòng lặp for each để quét Sheet, lập tiếp vòng lặp for each để tìm tên TK trong các sheet. Nếu tìm thấy thì lập tiếp vòng lặp for each để tìm tên cột tháng.... Nếu cột tháng... giống tên Sheet thì lấy dữ liệu của sheet đó về sheet Tong Hop... về tốc độ thì tôi chưa thử nhưng code ngắn gọn, dễ hiểu vì viết theo kiểu mực hệt (mô tả hành động).

Lê Duy Thương · 4/10/11

TrungChinhs đã viết:
Tại sao các bạn không sử dụng phương thức Find nhỉ ? thuật giải nằm ngay trong câu hỏi:
Dùng vòng lặp for each để quét Sheet, lập tiếp vòng lặp for each để tìm tên TK trong các sheet. Nếu tìm thấy thì lập tiếp vòng lặp for each để tìm tên cột tháng.... Nếu cột tháng... giống tên Sheet thì lấy dữ liệu của sheet đó về sheet Tong Hop... về tốc độ thì tôi chưa thử nhưng code ngắn gọn, dễ hiểu vì viết theo kiểu mực hệt (mô tả hành động).

phương thức find em chưa nghiên cứu . bác có thể đưa code bác viết lên em nghiên cứu được không ?
về tốc độ thì dữ liệu nhiều cỡ 65000 rows mỗi sheet thì em nghĩ dictionary sẽ nhanh hơn

ndu96081631 · 4/10/11

Lê Duy Thương đã viết:
phương thức find em chưa nghiên cứu . bác có thể đưa code bác viết lên em nghiên cứu được không ?

Về Find method, có thể tham khảo tại đây:
http://www.giaiphapexcel.com/forum/...p-về-phương-thức-tìm-kiếm-FIND-(-Find-Method)

Lê Duy Thương đã viết:
về tốc độ thì dữ liệu nhiều cỡ 65000 rows mỗi sheet thì em nghĩ dictionary sẽ nhanh hơn

Điều này là đương nhiên! Mọi code thực hiện tính toán trên Array đều cho tốc độ nhanh hơn trên Range (Find hoạt động trên Range)

vudoanttg · 4/10/11

Cám ơn các anh, chị đã nhiệt tình giúp đỡ. Tôi mới bắt đầu tìm hiểu về code nên chưa biết gì đang ngồi dịch ù hết cả tai mà chưa được.

TrungChinhs · 4/10/11

Lê Duy Thương đã viết:
phương thức find em chưa nghiên cứu . bác có thể đưa code bác viết lên em nghiên cứu được không ?
về tốc độ thì dữ liệu nhiều cỡ 65000 rows mỗi sheet thì em nghĩ dictionary sẽ nhanh hơn

Mã:

Sub TongHop()
    Application.ScreenUpdating = False
    On Error Resume Next
    Sheets("Tong Hop").Range("c5:z" & [b65536].End(3).Row).ClearContents
    For Each sh In Worksheets
        If sh.Name <> "Tong Hop" Then
            For Each Tk In Sheets("Tong Hop").Range("b4:b" & [b65536].End(3).Row)
                TkTim = Sheets(sh.Name).Cells.Find(Tk, LookAt:=xlWhole, MatchCase:=True).Address
                If TkTim > 0 Then
                    For Each Thang In Sheets("Tong Hop").Range("c3:" & [iv3].End(1).Address)
                        If Thang = sh.Name Then
                            Tk(1, Thang.Column - 1) = Sheets(sh.Name).Range(TkTim)(1, 2)
                        End If
                    Next
                End If
                TkTim = 0
            Next
        End If
    Next
End Sub

Bạn muốn thêm tháng nào thì chèn thêm cột (khỏi phải định dạng bảng), Nhập tháng giống với tên Sheet và mở lại Sheet Tong Hop

anhcunha · 21/3/12

Nếu mình muốn làm 12 tháng cho năm 2012 thì mình phải làm thế nào đây/ dốt quá mong cac A/C hướng dẫn cụ thể và từ từ, đừng chửi là dốt nhé. Cám ơn nhiều ạ.

tueyennhi · 27/9/16

Mọi người cho em hỏi đối với file tổng hợp này của em thì code sẽ như thế nào. Code của anh Lê Duy Thương em chưa áp dụng được triệt để, mới chỉ dừng ở mức lấy ID từ các sheet sao cho sheet tổng hợp ID đó là duy nhất, còn lại để áp giờ công của từng ngày vào sheet tổng hợp thì em chưa làm được.

Lê Duy Thương · 27/9/16

tueyennhi đã viết:
Mọi người cho em hỏi đối với file tổng hợp này của em thì code sẽ như thế nào. Code của anh Lê Duy Thương em chưa áp dụng được triệt để, mới chỉ dừng ở mức lấy ID từ các sheet sao cho sheet tổng hợp ID đó là duy nhất, còn lại để áp giờ công của từng ngày vào sheet tổng hợp thì em chưa làm được.

thì thử dùng cái này xem sao
lâu lắm mới vào GPE
code cho sheet tông hop

PHP:

Private Sub Worksheet_Activate()
Dim Arr(), Ws As Worksheet, i As Long
  For Each Ws In Worksheets
    If Ws.Name <> "Tong hop" Then
      i = i + 1
      ReDim Preserve Arr(1 To i)
      Arr(i) = "'" & Ws.Name & "'!" & Range("A2:C200").Address(, , 2)
    End If
  Next
     Sheets("Tong hop").Cells.Clear
     Sheets("Tong hop").Range("A3").Consolidate Arr, xlSum, True, True, False
     Sheets("Tong hop").Range("A3") = "ID"
     Sheets("Tong hop").Range("A3").CurrentRegion.Borders.LineStyle = 1
End Sub

hdg2318 · 31/10/16

Mình cũng đang gặp phải trường hợp gần giống như này, nhưng có 1 chút khác biệt nhỏ.
Mình đã chú thích cụ thể trong file đính kèm, rất mong các bạn giúp đỡ.

ChanhTQ@ · 31/10/16

Các dữ liệu này được nhập vào theo ngày hoặc tuần, số lượng ko cố định.
-Có thể phát sinh dữ liệu Name mới so với dữ liệu Name ở sheet TongHop.
-Sau khi ấn UPDATE, các dữ liệu này sẽ được điền tự động sang sheet TongHop.
-Mỗi lần update, các dữ liệu được điền nối tiếp vào các ô còn trống từ trái sang phải.
-Nếu phát sinh dữ liệu Name mới, sẽ tự động thêm dòng dữ liệu mới cho Name này.

Không thể hiểu bạn muốn gì & như thế nào luôn!

hdg2318 · 31/10/16

ChanhTQ@ đã viết:
Không thể hiểu bạn muốn gì & như thế nào luôn!

ý mình là mỗi khi ấn nút update, dữ liệu Rev-Date bên sheet "rev" sẽ được điền vào các cột Rev-Date bên sheet TongHop với Name tương ứng.
nếu bên sheet "rev" có Name mới mà bên sheet TongHop chưa có, ch.trình sẽ tự động thêm 1 dòng mới xuống dưới cùng và điền dữ liệu cho Name mới này cùng format với các dòng trên.

SA_DQ · 31/10/16

Mỗi khi ấn nút update, dữ liệu Rev-Date bên sheet "rev" sẽ được điền vào các cột Rev-Date bên sheet TongHop với Name tương ứng.
nếu bên sheet "rev" có Name mới mà bên sheet TongHop chưa có, ch.trình sẽ tự động thêm 1 dòng mới xuống dưới cùng và điền dữ liệu cho Name mới này cùng format với các dòng trên.

Ý này bạn đã mô tả rồi;
Không hiểu là ở chỗ, ví dụ mã A001-B1, sao lại sang bên trang 'TH' lại có dòng dữ liệu 3; nhất là các ô từ [e3:j3]

& mã A002-G3 ở trang này bổng dưng xuất hiện số liệu là từ đâu?

Nên viết cho người khác hiểu, chứ không fải viết những gì mình hiểu.

hdg2318 · 31/10/16

HYen17 đã viết:
Ý này bạn đã mô tả rồi;
Không hiểu là ở chỗ, ví dụ mã A001-B1, sao lại sang bên trang 'TH' lại có dòng dữ liệu 3; nhất là các ô từ [e3:j3]

Các ô dữ liệu từ E:N là các ô được nhập vào với thông số lấy từ bên sheet "rev".
Còn các ô từ C đã có công thức để đưa ra tình trạng mới nhất hiện tại.

HYen17 đã viết:
& mã A002-G3 ở trang này bổng dưng xuất hiện số liệu là từ đâu?

Mã nào cũng có giá trị Rev-Date mà bạn, hiện tại mình đang nhập thủ công thôi. Dữ liệu được cung cấp với định dạng như bên sheet "rev"

hdg2318 · 1/11/16

Hiện tại mình đã mầy mò và viết được như này, nhưng mới chỉ đưuọc chức năng copy, nếu có mã mới, chưa tự động thêm vào được.

Sub TongHop()Dim arr As Variant, D_arr As Variant
Dim i As Integer, j As Integer, r As Long, c As Long
Set arr = Sheet1.Range("A3:C25")
Set D_arr = Sheet2.Range("B3:N25")

MSG = "Please confirm the latest data already input here !!!"
Style = vbYesNo + vbInformation
Title = "Notice For" & Ad & " " & Yy & "Update data! "
Note = MsgBox(MSG, Style, Title)
If Note = vbYes Then
For i = 1 To arr.Rows.Count
For j = 1 To D_arr.Rows.Count
If arr(i, 1) = D_arr(j, 1) Then
For c = 4 To D_arr.Columns.Count
If D_arr(j, c) = "" Then
D_arr(j, c) = arr(i, 2)
D_arr(j, c + 1) = arr(i, 3)
Exit For
End If
Next c
End If
Next j
Next i
Else: Exit Sub
End If
End Sub

Mình đang muốn ch.trình tự xác định cận dưới của mảng, nhưng khi thay 2 dòng đỏ thành như bên dưới thì báo lỗi, mong các bạn chỉ giáo giúp.

Set arr = Sheet1.Range([A3], [C10000].End(xlUp))
Set D_arr = Sheet2.Range([B3], [N10000].End(xlUp))

giaiphap · 1/11/16

hdg2318 đã viết:
Mình đang muốn ch.trình tự xác định cận dưới của mảng, nhưng khi thay 2 dòng đỏ thành như bên dưới thì báo lỗi, mong các bạn chỉ giáo giúp.

Bạn thử sửa thế này thử xem.

Mã:

Set arr = Sheet1.Range([A3], [COLOR=#ff0000][B]Sheet1.[/B][/COLOR][C10000].End(xlUp))
Set D_arr = Sheet2.Range([B3], [COLOR=#ff0000][B]Sheet2.[/B][/COLOR][N10000].End(xlUp))

hdg2318 · 1/11/16

giaiphap đã viết:
Bạn thử sửa thế này thử xem.

Mã:

Set arr = Sheet1.Range([A3], [COLOR=#ff0000][B]Sheet1.[/B][/COLOR][C10000].End(xlUp)) Set D_arr = Sheet2.Range([B3], [COLOR=#ff0000][B]Sheet2.[/B][/COLOR][N10000].End(xlUp))

không được bạn ơi, vẫn báo lỗi : "Method 'Range' of object '_Worsheet' failed"

HieuCD · 1/11/16

Mã:

Dim arr As range, D_arr As range
Dim i As Integer, j As Integer, r As Long, c As Long
Set arr = Sheet1.Range("A3:C25")
Set D_arr = Sheet2.Range("B3:N25")

hoặc

Mã:

Dim arr, D_arr
Dim i As Integer, j As Integer, r As Long, c As Long
arr = Sheet1.Range("A3:C25")
D_arr = Sheet2.Range("B3:N25")

hdg2318 · 2/11/16

HieuCD đã viết:

Mã:

Dim arr As range, D_arr As range
Dim i As Integer, j As Integer, r As Long, c As Long
Set arr = Sheet1.Range("A3:[COLOR=#ff0000]C25[/COLOR]")
Set D_arr = Sheet2.Range("B3:[COLOR=#ff0000]N25[/COLOR]")

hoặc

Mã:

Dim arr, D_arr
Dim i As Integer, j As Integer, r As Long, c As Long
arr = Sheet1.Range("A3:[COLOR=#ff0000]C25[/COLOR]")
D_arr = Sheet2.Range("B3:[COLOR=#ff0000]N25[/COLOR]")

mình đang muốn các bạn giúp làm sao để ch.trình tự xác định được cận dưới của bảng.
Trong đoạn code mình đang test, thì cận dưới là các ô C25 và N25. Mình đang thử với dạng như dưới này nhưng bị báo lỗi.

PHP:

Set arr = Sheet1.Range([A3], [C10000].End(xlUp))
Set D_arr = Sheet2.Range([B3], [N10000].End(xlUp))

HieuCD · 2/11/16

hdg2318 đã viết:
mình đang muốn các bạn giúp làm sao để ch.trình tự xác định được cận dưới của bảng.
Trong đoạn code mình đang test, thì cận dưới là các ô C25 và N25. Mình đang thử với dạng như dưới này nhưng bị báo lỗi.

PHP:

Set arr = Sheet1.Range([A3], [C10000].End(xlUp)) Set D_arr = Sheet2.Range([B3], [N10000].End(xlUp))

mình thường khai báo thế nầy

Mã:

arr = Sheets("tên sheet").Range("A3:C" &Sheets("tên sheet").Range("C10000").End(xlUp).row)

nếu có Dim arr as range thì thêm set phía trước

hdg2318 · 2/11/16

HieuCD đã viết:
mình thường khai báo thế nầy

Mã:

arr = Sheets("tên sheet").Range("A3:C" &Sheets("tên sheet").Range("C10000").End(xlUp).row)

nếu có Dim arr as range thì thêm set phía trước

File mình gửi dưới đây được làm theo 2 cách, nút Update là cố định vùng dữ liệu A3:C25, nút Update_02 được sửa lại theo gợi ý của bạn.
Bạn xem giúp mình sao chỉ có dòng đầu tiên được update khi dùng theo nút Update_02.

HieuCD · 2/11/16

hdg2318 đã viết:
File mình gửi dưới đây được làm theo 2 cách, nút Update là cố định vùng dữ liệu A3:C25, nút Update_02 được sửa lại theo gợi ý của bạn.
Bạn xem giúp mình sao chỉ có dòng đầu tiên được update khi dùng theo nút Update_02.

bạn chỉnh lại code

Mã:

...
Set arr = Sheet1.Range("A3:C" & Sheet1.Range("C10000").End(xlUp).Row)
Set D_arr = Sheet2.Range("B3:R" & Sheet2.Range[COLOR=#ff0000]("B10000"[/COLOR]).End(xlUp).Row)
...

bạn phải chọn cột có đủ dữ liệu, trong file là cột B
sheet rev bạn phải chọn những dòng dưới không có dữ liệu và bấm phím Delete, để code tính đúng dòng cuối, nếu không xóa sẽ tính dư, tuy không ảnh hưởng đến kết quả trong trường hợp nầy, nhưng có thể sai trong trường hợp khác, ngoài ra còn làm code chạy chậm
bạnphải

hdg2318 · 2/11/16

HieuCD đã viết:
bạn chỉnh lại code

Mã:

... Set arr = Sheet1.Range("A3:C" & Sheet1.Range("C10000").End(xlUp).Row) Set D_arr = Sheet2.Range("B3:R" & Sheet2.Range[COLOR=#ff0000]("B10000"[/COLOR]).End(xlUp).Row) ...

bạn phải chọn cột có đủ dữ liệu, trong file là cột B
sheet rev bạn phải chọn những dòng dưới không có dữ liệu và bấm phím Delete, để code tính đúng dòng cuối, nếu không xóa sẽ tính dư, tuy không ảnh hưởng đến kết quả trong trường hợp nầy, nhưng có thể sai trong trường hợp khác, ngoài ra còn làm code chạy chậm
bạnphải

Thanks bạn, code đã chạy ngon lành rồi %#^#$

hdg2318 · 21/11/16

sau 1 thời gian mày mò, mình đã tạo được file như bên dưới, đã có chức năng tự động thêm dòng khi có mã mới.
tuy code chạy tạm ổn, nhưng với trường hợp có nhiều mã mới hoặc nhiều mã cần update, chương trình chạy khá chậm.
mình mới đang tập viết code, chưa biết làm sao cải thiện được tốc độ. nhờ các bạn xem qua và chỉ bảo giúp.

tổng hợp dữ liệu từ nhiều sheet vào một sheet (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Thành viên mới

File đính kèm

Thành viên gắn bó

File đính kèm

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Thành viên mới

Thành viên mới

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Gội rồi mới Cạo

File đính kèm

Thành viên tích cực

Cạo lấy gì gội (Dịch quá không gội được)

Huyền thoại GPE

Thành viên mới

Thành viên tích cực

File đính kèm

Thành viên mới

Thành viên tích cực

File đính kèm

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Thành viên mới

File đính kèm

0901452không62

Thành viên mới

/(hông là gì!

Thành viên mới

Thành viên mới

==(^o^)==

Thành viên mới

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

Thành viên mới

File đính kèm

Chuyên gia GPE

Thành viên mới

Thành viên mới

File đính kèm

Thời gian đếm ngược.