Tính tổng từng sheet rổi cộng tổng các sheet

Thien · 21/11/06

Chào cả nhà

Mình có bài toán này đây:
- Có 13 Sheet (Số thứ tự các sheet không liên tục hoặc tên mỗi sheet khác nhau)
- Sheet 14 là sheet tổng được nối lại từ 13 sheet kia & có tên là Master.
- Tính tổng tại các cột M800,N800,O800 của từng sheet (từ sheet 1 đến sheet 13)
- Tại các cột M801,N801,O801 của Sheet Master sẽ được tính tổng tại các cột M800,N800,O800 của từng sheet kia.

Hiện tại mình làm thủ công: Vào từng sheet tính tổng các cột M800,N800,O800. Rồi vào sheet Master tại các cột M801,N801,O801 được đặt công thức sum(Sheet1:sheet13!M800) , sum(Sheet1:sheet13!N800) & sum(Sheet1:sheet13!o800). Nhưng không cho ra kết quả (báo lỗi #NAME?).

Mình muốn dùng VBA nhưng không biết ứng dụng & cải tiến hàm này cho phù hợp với yêu cầu của bái toán:

Function Sumall1(Cel As Range)
Dim wSht As Worksheet
Dim Diachi
Dim Tong As Long, i As Integer
Tong = 0
Diachi = Cel.Address
Application.Volatile True
For i = 2 To Sheets.Count
Tong = Tong + Sheets(i).Range(Diachi).Value
Next
Sumall1 = Tong
End Function

Các bạn có code nào hay hơn & giải được đề này không ?

Thân chào cả nhà.

SA_DQ · 22/11/06

Hiện tại từng sheet tính tổng các cột M800,N800,O800. Rồi vào sheet Master tại các cột M801,N801,O801 được đặt công thức sum(Sheet1:sheet13!M800) , sum(Sheet1:sheet13!N800) & sum(Sheet1:sheet13!o800). Nhưng không cho ra kết quả (báo lỗi #NAME?).

Chi bằng bạn thử viết dài dài ra 1 chút:
='S1'!M801+'S2'!M801. . .

Mã:
Muốn dùng VBA nhưng không biết ứng dụng & cải tiến hàm này cho phù hợp với yêu cầu của bái toán:

Mã:

[b]Function Sumall1(Cel As Range)[/b] Dim wSht As Worksheet: Dim Diachi Dim Tong As Long, i As Integer: Tong = 0 Diachi = Cel.Address Application.Volatile True For i = 2 To Sheets.Count Tong = Tong + Sheets(i).Range(Diachi).Value Next 9[i] Sumall1 = Tong[/i] [b]End Function[/b]

.

Hình như bạn chỉ cần viết Sub thôi & khi đã có Tong rồi, thì sửa câu lệnh 9 lại để nó gán Tong Vô ô bạn muốn của sheet Master!
Chắc câu lệnh đó là vầy:

Mã:

 Sheet1.Select:             Range("N801").Value = Tong

Thien · 22/11/06

Dear SA_QD

1/ Mình muốn dùng macro, bạn có code nào giải được không?
2/ Mình dùng hàm sumall1 trên không được & và cũng không giải hết yêu cầu bài toán.

Thân Chào bạn

nvson · 22/11/06

Tôi nghĩ công thức tổng tất cả các sheet của anh Thiện là đúng (vì tôi cũng hay làm như vậy).
Ngoài các cách trên tôi nghĩ cũng còn một cách khá hay là sử dụng chức năng hợp nhất thông tin (Data/ Consolidate). Nhưng do số lượng sheet của anh khá nhiều nên viết thành Macro cho đỡ mất công chọn.

Mã:

[B]Public Sub Consolidate2()
[/B]'Hop nhat thong tin tren tung sheet
'Chuong trinh se tu dong tinh tong tat ca cac o trong mot vung nao do cua tat ca cac sheet
'Can chu y dia chi cua cac o dang R1RC
'Neu ban chua quen voi cach nay thi vao Tools/Options, chon tab General
'roi danh dau vao tuy chon R1C1 reference style de xac dinh vung cho de dang
Dim arrayData()
Dim i As Integer
Dim sh As Worksheet
ReDim arrayData(1 To ActiveWorkbook.Worksheets.Count - 1)
i = 0
For Each sh In Worksheets
    If sh.Name <> ActiveSheet.Name Then
        i = i + 1
        'Vung R800C13:R800C19 tuong tu nhu vung M800:N800, ban co the chon vung bat ky
        arrayData(i) = sh.Name & "!R800C13:R800C15"
    End If
Next sh
Selection.Consolidate Sources:=arrayData(), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
End Sub

Thien · 22/11/06

Dear nvson

Anh bổ sung thêm 1 chút nhen: Macro của anh
- Không tự đi vào từng sheet để tính tổng tại các cột chỉ dịnh.
- Không tự cộng tổng của từng sheet, rồi đặt giá trị tổng tại ô đặt trỏ chuột trong sheet Master.

Anh xem lại dùm.

Thân chào bạn.

nvson · 22/11/06

Vậy anh thử code sau:

Mã:

Public Sub Consolidate2()
'Hop nhat thong tin tren tung sheet
'Chuong trinh se tu dong tinh tong tat ca cac o trong mot vung nao do cua tat ca cac sheet
'Can chu y dia chi cua cac o dang R1RC
'Neu ban chua quen voi cach nay thi vao Tools/Options, chon tab General
'roi danh dau vao tuy chon R1C1 reference style de xac dinh vung cho de dang
Dim arrayData()
Dim i As Integer
Dim sh As Worksheet
ReDim arrayData(1 To ActiveWorkbook.Worksheets.Count - 1)
Sheets("Master").Select
i = 0
For Each sh In Worksheets
    If sh.Name <> ActiveSheet.Name Then
        i = i + 1
        'Vung R800C13:R800C19 tuong tu nhu vung M800:N800
        arrayData(i) = sh.Name & "!R800C13:R800C15"
        sh.Cells(800, "M").Formula = "=sum(M1:M799)"
        sh.Cells(800, "N").Formula = "=sum(N1:N799)"
        sh.Cells(800, "O").Formula = "=sum(O1:O799)"
    End If
Next sh
Range("M800").Select
Selection.Consolidate Sources:=arrayData(), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
End Sub

Thien · 22/11/06

Dear nvson

Rất hay, thật hoàn chỉnh.

Cảm ơn nhen.

Thân chào bạn

Nguyễn Bích Khuyên · 22/11/06

Thien đã viết:
Hiện tại mình làm thủ công: Vào từng sheet tính tổng các cột M800,N800,O800. Rồi vào sheet Master tại các cột M801,N801,O801 được đặt công thức sum(Sheet1:sheet13!M800) , sum(Sheet1:sheet13!N800) & sum(Sheet1:sheet13!o800). Nhưng không cho ra kết quả (báo lỗi #NAME?).

Chào các bạn!
Mình không biết gì về VBA nên rất ghét viết code, hi hữu lắm mình mới nghĩ tới nó.
Mình thấy công thức của bạn không sai cú pháp mà vẫn bị lỗi #NAME thì có thể do tham chiếu trong công thức có một số hạng nào đó mắc lỗi này. Còn một cách đơn giản hơn là bạn có thể đặt cho mỗi ô tổng cộng trên mỗi sheet một cái tên theo chỉ số của sheet (đặt tên một cách gợi nhớ, dễ hiểu), ví dụ:
- Ô tổng cộng trên Sheet1 đặt tên: Tong_01
- Ô tổng cộng trên Sheet2 đặt tên: Tong_02
- Ô tổng cộng trên Sheet3 đặt tên: Tong_03
-...
- Ô tổng cộng trên Sheet13 đặt tên: Tong_13
Việc lập công thức tổng ở sheet Master được lập như sau:

Tong_01+Tong_02+Tong_03+Tong_04+Tong_05+Tong_06+Tong_07+Tong_08+Tong_09+Tong_10+Tong_11+Tong_12+Tong_13

Mình cảm thấy việc làm này không tốn nhiều công hơn việc phải mất công lập trình.

minhnc · 2/2/07

Sao anh em ko up file cho anh em tham khảo luôn.
Những người bắt đầu VBA chưa chắc đã biết cop va dán vào đâu đấy???

Mr Okebab · 2/2/07

Hoặc có một cách khác nữa, đó là việc cộng theo Name.

Các bạn xem thử nhé :

Thân!

atkdef88 · 14/1/10

híc, cái file kia là cái j thế pac, em down ve mo ra trắng toát với thêm vài số linh tinh, ko hỉu j hết trơn. hic, đoạn này em bí quá

sumisumo · 15/4/10

atkdef88 đã viết:
híc, cái file kia là cái j thế pac, em down ve mo ra trắng toát với thêm vài số linh tinh, ko hỉu j hết trơn. hic, đoạn này em bí quá

Bạn vào insert\name\define là bạn sẽ hiểu mấy anh\chị viết gì liền à!

fso56 · 24/1/13

View attachment 201.rar
Nhờ các bác giúp em tính tổng các sheet về một sheet trên cùng một file.

Hong.Van · 24/1/13

fso56 đã viết:
View attachment 96310
Nhờ các bác giúp em tính tổng các sheet về một sheet trên cùng một file.

Bạn muốn gì thì fải
1/có File
2/ Mô tả rõ ràng
3/ Làm thử kết quả bằng tay
Thì có người sẽ giúp bạn! Nếu kg thì bạn sẽ kg có câu trả lời đâu?
Thân!

fso56 · 24/1/13

Em có cai link 201.rar ở trên thây bác. Trong đó em có cái cân giúp mà bác

fso56 · 25/1/13

hj hj
Có ai đó giúp em với!
Thank!

fso56 · 28/1/13

Em gửi lại link mong các bác giúp
View attachment hoi cach tinh tong tren cac sheet ve mot sheet.rar

Ba Tê · 28/1/13

fso56 đã viết:
Em gửi lại link mong các bác giúp
View attachment 96505

Dữ liệu nhập thủ công trên nhiều sheet, không dùng mã mà dùng Họ tên, nhập dư 1 dấu cách là "tèo".
(Trong File của bạn có nhiều tên dư 1 dấu cách phía sau)
Nếu dùng VBA thì xem tạm file này, mỗi lần mở sheet "Tong BH" là có kết quả.
Trùng tên cũng "tèo" luôn.

Quang_Hải · 28/1/13

fso56 đã viết:
Em gửi lại link mong các bác giúp
View attachment 96505

Bạn copy code này về chạy thử xem. Không dùng hàm Ucase và không dùng Dictionary không biết có phản ứng phụ gì không.

PHP:

Sub cong()
Dim sh As Worksheet, i, data(), Res(), j
With Sheets("tong BH")
   .[C7:C1000].ClearContents
   Res = .Range(.[B7], .[B65536].End(3)).Resize(, 2).Value
   For Each sh In Worksheets
      If sh.Name <> "tong BH" Then
         data = sh.Range(sh.[b11], sh.[b11].End(4)).Resize(, 13).Value
         For i = 1 To UBound(data)
            For j = 1 To UBound(Res)
               With Application
                  If .Trim(StrConv(Res(j, 1), 1)) = .Trim(StrConv(data(i, 1), 1)) Then
                     If data(i, 13) = 1 Then
                        Res(j, 2) = Res(j, 2) + data(i, 12)
                     End If
                  End If
               End With
            Next
         Next
      End If
   Next
   .[B7].Resize(j - 1, 2) = Res
End With
End Sub

fso56 · 28/1/13

Ba Tê đã viết:
Dữ liệu nhập thủ công trên nhiều sheet, không dùng mã mà dùng Họ tên, nhập dư 1 dấu cách là "tèo".
(Trong File của bạn có nhiều tên dư 1 dấu cách phía sau)
Nếu dùng VBA thì xem tạm file này, mỗi lần mở sheet "Tong BH" là có kết quả.
Trùng tên cũng "tèo" luôn.

Em cám ơn bác!
Nếu cứ cho rằng em nhập tên trên các sheet và tên trên sheet tổng là như nhau 100% ( chỉ khác số thứ tự hàng mà của các tên trên các sheet và sheet tổng) thì có cách nào dùng hàm bình thường của excel ( không dùng VBA) để làm không bác

Tính tổng từng sheet rổi cộng tổng các sheet

Thành viên thường trực

/(hông là gì!

Thành viên thường trực

Geotechnics

Thành viên thường trực

Geotechnics

Thành viên thường trực

Thành viên mới

Nguyễn Công Minh

Ngon Ngất Ngây

File đính kèm

Thành viên mới

Thành viên mới

Power Query

Busy

Power Query

Power Query

Power Query

Cạo Rồi Khỏi Gội

File đính kèm

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

Power Query

Đếm ngược thời gian