Tìm tổng các ô trên nhiều sheet (1 người xem)

[dacohoailang]

Giả sử tôi có tập tin với 40 sheet. Tôi muốn tìm tổng giá trị các ô A1 từ sheet 1 đến sheet 39 và kết quả được hiển thị ở ô A5 của sheet 40. Ví dụ:
- Sheet 1: A1=2
- Sheet 2: A1=5
- ……….
- Sheet 39: A1=10
Vậy Sheet 40 sẽ cho kết quả ở ô A5=A1.sheet1+A1.sheet2+…..+A1.sheet39
=2+5+…….+10
Có thể dùng hàm toán học nào hay cách nào để thực hiện thao tác này thật nhanh mà không phải thực hiện bằng tay bằng cách nhấp vào A1 của từng sheet không?

Xin cám ơn

 

[Nguyễn Xuân Sơn]

ở ô a1 sít 40 bạn viết=sum() trong dấu ngoặc bạn ấn phím shift và đồng thời dùng chuột chọn sheet1 sau đó chon sheet39 rồi bạn gõ a1 và enter

 

[funnyzui]

Public Sub Tong()
Application.ScreenUpdating = False
    Dim i As Integer
    Dim tongi As Long
    Dim Tong As Long
    Tong = 0
    For i = 1 To 39
        tongi = Worksheets("sheet" & i).Cells(1, 1).Value
        Tong = Tong + tongi
    Next
    Worksheets("sheet40").Cells(5, 1).Value = Tong
Application.ScreenUpdating = True
End Sub

Chào bạn!
Mình cũng mới tập tễnh VBA thôi, nhưng hy vọng đoạn code trên sẽ giúp được gì đó cho bạn. Bạn sử dụng thử xem sao, hy vọng bạn biết xài macro. Nếu ko biết thì bạn có thể học trên diễn đàn này, có bài của bác Phantuhuong viết về macro rất hay.
Chúc vui!
P/S: Do sơ suất trong lúc đánh máy nên bài đang viết thì bị Enter. Phiền các mod xóa dùm bài trên của em. Thanks!

 

[anhtuan1066]

Tôi ko biết macro nên tôi thấy cái này nó hay... nhưng đúng trên quan điểm của người dùng thì tôi có mấy ý sau:
1> Vì ko biết file có bao nhiêu sheet nên bạn nên làm cách nào đó cho có tính tổng quát hơn
2> Cũng ko biết sẽ cộng trên cell nào nên cái này cũng nên xem lại...
Tóm lại, nếu tôi là người dùng thì tôi sẽ yêu cầu 1 hàm như sau:
=CONG(cell nào, mấy sheet)
Lấy VD: file có 30 sheet, tôi đang ở sheet thứ 30 và tôi muốn cộng tất cả các cell F4 nằm trên các sheet từ sheet5 đến sheet10, vậy tôi muốn 1 hàm có cú pháp như thế này đây:
=CONG(F4,5:10)
Giả sử trường hợp nửa là cộng các cell trên các sheet ko liền kề... chẳng hạn cộng các cell F4 từ sheet5 đến sheet10 và các cell F4 từ sheet15 đến sheet17... vậy hàm phải có cú pháp như sau:
=CONG(F4,5:10;15:17)
Tôi là người dùng thì tôi muốn như thế... Ko biết các bạn có thể làm dc ko? Làm luôn thành 1 Add-in thì quá tốt
Mến
ANH TUẤN

 

[Nguyễn Xuân Sơn]

Tại sao mọi người cứ phức tạp hóa vấn đề bằng VBA thế nhỉ? Nếu giải quyết bằng công thức thông thường mà cũng được thì làm sao phải mất công viết mã cho tốn thời gian. Chúng ta nên vận dụng thế nào cho hiệu quả và tối ưu nhất bài toán , cần gì phải cứ dùng dao mổ bò mà giết gà làm gì cho nát hết cả "Kê" ra.

 

[anhtuan1066]

Uh... nếu là tôi thì tôi sẽ dùng công thức trong trường hợp này... Nhưng đã lở làm bằng Macro rồi thì làm cho.. tới luôn.. Nếu ko cái code ấy chạy còn dở hơn công thức thì macro làm gì, đúng ko?
ANH TUẤN

 

[anhtuan1066]

Còn nếu để tính tổng của các cell từ Sheet 1 đến sheet kế cuối thì có thể sử dụng code này:
Function TONG(rng As Range)
Application.Volatile
For i = 1 To Worksheets.Count - 1
TONG = TONG + Sheets(i).Cells(rng.Row, rng.Column).Value
Next i
End Function

Save nó thành .xla để làm User Define Function luôn... hi.. hi...

 

[Mr Okebab]

Còn nếu để tính tổng của các cell từ Sheet 1 đến sheet kế cuối thì có thể sử dụng code này:
Function TONG(rng As Range)
Application.Volatile
For i = 1 To Worksheets.Count - 1
TONG = TONG + Sheets(i).Cells(rng.Row, rng.Column).Value
Next i
End Function

Save nó thành .xla để làm User Define Function luôn... hi.. hi...

Em xin phép sửa lại xíu

Lý do : Đề phòng trường hợp tên của các Sheet thay đổi và có ô không phải là số

Function Tong(rng As Range) As Long
    Application.Volatile
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If IsNumeric(Sh.Cells(rng.Row, rng.Column).Value) Then Tong = Tong + Sh.Cells(rng.Row, rng.Column).Value
    Next
    Set rng = Nothing
    Set Sh = Nothing
End Function

Cú pháp :

=tong(A1) ----> tính tổng các ô A1 trên các Sheet.
​

Thân!

 

[LikeIt]

Anh Tuấn thử 1 VD cụ thể đi, để cho dễ hiểu, mà dạo này anh bắt đầu thực hành đc VBA rồi hả? anh học nhanh thế, có cách nào chỉ em với,

 

[Mr Okebab]

Anh Tuấn thử 1 VD cụ thể đi, để cho dễ hiểu, mà dạo này anh bắt đầu thực hành đc VBA rồi hả? anh học nhanh thế, có cách nào chỉ em với,

Tham khảo nhé :


Thân!

 

[LikeIt]

Em xin phép sửa lại xíu

Lý do : Đề phòng trường hợp tên của các Sheet thay đổi và có ô không phải là số

Option Explicit
--------------------------------
Function Tong(rng As Range) As Long
    Application.Volatile
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If IsNumeric(Sh.Cells(rng.Row, rng.Column).Value) Then Tong = Tong + Sh.Cells(rng.Row, rng.Column).Value
    Next
End Function

Cú pháp :

=tong(A1) ----> tính tổng các ô A1 trên các Sheet.
​

Thân!

Em thử theo cú pháp của anh nhưng không đc. ANh xem VD của em sai cái gì, vì em ko vào đc GPE ở cty, nên nếu dc anh đồng thời CC cho em vào mail thombn@gmail.com nhé. Tks anh

 

[minhlev]

Mạn phép bác Bab xin được trả lời bạn là: đưa code vào module thì sẽ Oke.

 

[Mr Okebab]

Em thử theo cú pháp của anh nhưng không đc. ANh xem VD của em sai cái gì, vì em ko vào đc GPE ở cty, nên nếu dc anh đồng thời CC cho em vào mail thombn@gmail.com nhé. Tks anh

Mạn phép bác Bab xin được trả lời bạn là: đưa code vào module thì sẽ Oke.

Đúng như minhlev nói (lại chơi chữ nữa) bạn có 2 nguyên nhân để hàm không chạy :

1. Hàm này phải đưa vào trong Module : Vì hàm này có tác dụng trên nhiều sheet khác nhau nên không được để ở sheet.
2. Không tính ô chính nó : Đây là hàm tính tổng tất cả các ô có cùng địa chỉ trên các sheet, kể cả sheet hiện hành. Vì vậy nếu bạn đang ở A1, mà lại nhập là =tong(A1) thì có nghĩa là nó cộng thêm cả chính nó. Bạn phải sang ô khác để nhập. Nếu không muốn cộng giá trị của ô tại sheet hiện hành thì Code như sau :

Function Tong(rng As Range) As Long
    Application.Volatile ' Co the bo di
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> ActiveSheet.Name Then
            If IsNumeric(Sh.Cells(rng.Row, rng.Column).Value) Then Tong = Tong + Sh.Cells(rng.Row, rng.Column).Value
        End If
    Next
    Set rng = Nothing
    Set Sh = Nothing
End Function

Thân!

 

[anhtuan1066]

Theo tôi thì nên bỏ qua sheet hiện hành, như vậy hợp với thực tế hơn... Sheet cuối cùng là đễ làm tổng hợp mà
Mến
ANH TUẤN

 

[anhtuan1066]

Ah... còn nữa...
1>Code đầu tiên sẽ báo lỗi nếu cell ko phải là số... theo tôi nếu đễ nguyên như thế cũng ko phải là dở, có 1 vài trường hợp phải cần báo lỗi Value như thế thì mới hay (Ít nhất biết dc mình nhập sai 1 cell nào đó)... Bỏ qua lỗi này e rằng người dùng sẽ chủ quan, kết quả sai mà ko biết
2>Nên Save nó thành 1 Add-In thì sẽ ko có chuyện báo lỗi khi sheet bị rename
Mến
ANH TUẤN

 

[Mr Okebab]

Ah... còn nữa...
1>Code đầu tiên sẽ báo lỗi nếu cell ko phải là số... theo tôi nếu đễ nguyên như thế cũng ko phải là dở, có 1 vài trường hợp phải cần báo lỗi Value như thế thì mới hay (Ít nhất biết dc mình nhập sai 1 cell nào đó)... Bỏ qua lỗi này e rằng người dùng sẽ chủ quan, kết quả sai mà ko biết
2>Nên Save nó thành 1 Add-In thì sẽ ko có chuyện báo lỗi khi sheet bị rename
Mến
ANH TUẤN

1. Cũng đúng thôi, đó cũng là 1 ý hay
2. Việc Rename : Đúng là em nhầm, thay đổi tên cũng không ảnh hưởng gì cả
3. Hàm của bác sẽ tính tổng cộng ở tất cả các Sheet trừ sheet cuối cùng
   

Thân!

 

[nguyenrock]

=Công thức này chắc được: =Sum(sheet1:sheetn!cell)
Ví dụ: cộng tất cả các ô A3 của các sheet1 đến sheet 10: sum(sheet1:sheet10!A3)
(Note:Đối với các ô bị merge thì nhớ xóa dấu nháy ' ' trong phần cell của công thức.
ví dụ ô bị merge 'A3:A5' thì gõ: =sum(sheet1:sheet10!A3:A5))