PDA

View Full Version : Tính tổng từng sheet rổi cộng tổng các sheet



Thien
21-11-06, 08:55 PM
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, 07:17 AM
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 bạn thử viết dài dài ra 1 chút:
='S1'!M801+'S2'!M801. . .

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
9 Sumall1 = Tong
End Function .
Hình như bạn chỉ cần viết Sub thôi & khi đã có Tong rồi, thì sửa câu lệnh 9 lại để nó gán Tong Vô ô bạn muốn của sheet Master!
Chắc câu lệnh đó là vầy:

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

Thien
22-11-06, 09:06 AM
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, 09:08 AM
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.



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)
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, 09:42 AM
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, 10:00 AM
Vậy anh thử code sau:




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, 10:15 AM
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, 10:24 AM
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+To ng_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
02-02-07, 12:39 PM
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
02-02-07, 01:11 PM
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-01-10, 08:36 AM
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-04-10, 02:43 PM
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-01-13, 10:39 AM
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.

Hong.Van
24-01-13, 11:57 AM
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-01-13, 12:37 PM
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-01-13, 01:12 PM
hj hj
Có ai đó giúp em với!
Thank!

fso56
28-01-13, 10:51 AM
Em gửi lại link mong các bác giúp
96505

Ba Tê
28-01-13, 12:21 PM
Em gửi lại link mong các bác giúp
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.

quanghai1969
28-01-13, 12:30 PM
Em gửi lại link mong các bác giúp
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.


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-01-13, 01:17 PM
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

thanhtrg
08-07-13, 09:29 AM
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à.

Với bài toán này bạn không cần phải dùng hàm, name hay VBA gì cả cho tốn điện, chỉ cần làm như sau
1. Chọn ô M800 của sheet đầu tiên trong số 13 sheets (ví dụ sheet!Con cò)
2. Nhấn giữ Shift và click chuột vào sheet thứ 13 (ví dụ sheet!tàng) (nếu 13 sheets nằm liền nhau) hoặc nhấn Ctrl và click để chọn hết 12 sheets còn lại (nếu các sheets không liền kề nhau) .
3. Nhấp vào biểu tượng Autosum để tính tổng cho cột M (hoặc có thể dùng công thức sum(…)). Enter . Kéo qua phải để tính cho cột N và O
4. Chọn ô M801 của sheet!Master, nhấp vào biểu tượng Autosum, click chuột vào sheet đầu tiên (sheet!Con cò) . Nhấn giữ Shift rồi click vào sheet cuối cùng (sheet!tàng), xong click vào ô M800 . Enter, kéo qua phải sẽ cho kết quả của ô N801 và O801 . Lưu ý 13 sheets phải nằm theo thứ tự từ trái qua phải, từ sheet!Con cò đến sheet!tàng