Tính tổng từ nhiều sheet ở cùng 1 ô trong Excel cực đơn giản, có ví dụ
Khánh Duy 01/11/2021
Tổng hợp dữ liệu từ nhiều sheet trong Excel sẽ giúp bạn nhanh chóng xử lý, giải quyết được công việc và nếu bạn chưa biết cách sử dụng. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về cách thực hiện nhé.
Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2016. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.
I. Ví dụ về cách tính tổng từ nhiều sheet trong Excel
1. Ví dụ
Ta có 3 bảng dữ liệu ở 3 sheet tương ứng như hình phía dưới.
Yêu cầu: Tính tổng sản phẩm của các tuần trong tháng 1,2,3 với các sản phẩm tương ứng Áo sơ mi, Áo thun, Quần Jean vào bảng tổng hợp. Ví dụ bảng như hình.
Cách làm: Bạn sử dụng công thức hàm Sum ở bảng tổng hợp. Sau đó lần lượt lấy dữ liệu tương ứng cho sản phẩm yêu cầu ở các Sheets. Sau đó, bạn cộng dồn các Sheets đó lại với nhau sẽ ra kết quả.
2. Cách sử dụng hàm Sum
Hàm SUM là hàm tính tổng tất cả các số có trong dãy ô dữ liệu.
Cú pháp hàm SUM: =SUM[number1, [number2],…]
Trong đó: Number1 và Number2 là những số hoặc dãy số sẽ tính.
II. Cách tính tổng từ nhiều sheet trong Excel
1. Cách làm
Mình sử dụng ví dụ trên và thực hiện theo các bước sau. Bạn cùng theo dõi và làm theo nha.
Bước 1: Ở bảng tổng hợp sản phẩm "Áo thun" bạn nhập hàm =SUM[Sheet1!C3:F3]+SUM[Sheet2!C3:F3]+SUM[Sheet3!C3:F3] vào ô tham chiếu "Số lượng" như hình nha.
Bước 2: Bạn nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị cho các sản phẩm còn lại như hình.
2. Những lưu ý khi tính tổng từ nhiểu sheet
Bạn cần lưu ý những điểm sau khi sử dụng trong tính tổng từ nhiều sheet:
- Các trang tính phải có cùng một định dạng.
- Các dữ liệu trong trang tính để tính tổng cần được sắp xếp hợp lý để đảm bảo kết quả vì do công thức sẽ thực hiện tính dữ liệu liên tiếp.
- Nếu bạn muốn sửa tên sheet [trang tính] mà bị dài, bạn hoàn toàn có thể chèn thêm sheet đầu và sheet cuối với tên ngắn gọn [có thể trống] rồi thực hiện hàm SUM để cộng các sheet trong Excel như bình thường.
- Cách làm này có thể gây khó khăn trong việc lập báo cáo. Bạn có thể coi đây là kiến thức căn bản để làm bàn đạp tiến đến các kiến thức lập báo cáo nâng cao trong Excel nha.
III. Các trường hợp tính tổng từ nhiều sheet khác
1. Tính tổng bằng công thức phức tạp hơn
Để có thể rút ngắn thời gian trong việc tính tổng dữ liệu từ nhiều sheets. Ngoài cách tính như mình đã giới thiệu ở trên thì bạn còn có thể sử dụng hàm Consolidate một trong những hàm tính toán cực nhanh và chính xác và nếu bạn thuần thục cách này sẽ giúp bạn rút ngắn thời gian trong việc tính với dạng trang tính lớn có nhiều bảng dữ liệu.
2. Tính tổng bằng Consolidate
Consolidate là một trong những hàm thống kê dữ liệu giúp tính toán hiệu quả khi được tích hợp cho phép thực hiện nhanh các phép tính tổng, trung bình, giá trị lớn nhất, nhỏ nhất,...
Trên đây là Tính tổng từ nhiều sheet ở cùng 1 ô trong Excel cực đơn giản, có ví dụ minh họa. Hy vọng bài viết sẽ giúp bạn thực hiện được trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
4.588 lượt xem
Trong công việc, nhất là những công việc liên quan đến dữ liệu như ngân hàng, tài chính, kế toán và các ngành nghiên cứu, các bạn đã gặp phải trường hợp cần phải tổng hợp dữ liệu từ nhiều file Excel khác nhau nhưng có cấu trúc giống nhau vào cùng 1 file master. Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn làm thế nào để có thể tổng hợp dữ liệu từ nhiều file excel vào 1 file không cần mở file rất nhanh chóng và tiện lợi qua video sau đây:
Tìm hiểu thêm: Hướng dẫn học excel cơ bản đến nâng cao
Đoạn code các bạn cần thay đổi cho phù hợp với dữ liệu là [xem phần comment trong code]
Code cho hàm GetConnXLS[]
Lưu ý:
1. Trong bài có sử dụng kiến thức về lập trình VBA nâng cao, kết hợp với kiến thức về SQL, nếu bạn có ý định đầu tư thời gian học bài bản những kiến thức này, hãy tham khảo trên Học Excel Online
2. Hãy backup [tạo 1 bản copy của file bạn đang làm việc cùng và lưu ở 1 nơi an toàn] trước khi thử code copy từ trên mạng về, nếu bạn không biết mình đang làm gì, hãy dừng lại trước khi có kiến thức về VBA tốt hơn và biết trước kết quả đoạn code mình copy vào Excel.
Cách làm này có 1 điểm cần lưu ý là: các file con được tổng hợp cần có cấu trúc giống hệt nhau [số cột, tên cột và số dòng, vùng dữ liệu]. Nếu sử dụng cách này để tổng hợp các file có cấu trúc tên cột, số lượng cột giống nhau nhưng khác nhau về số dòng sẽ dẫn đến việc thiếu dữ liệu trong file tổng hợp.
VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook… giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp…
Để có thể học VBA một cách đầy đủ, có hệ thống, bạn hãy tham gia khoá học VBA101 – VBA cơ bản dành cho người mới bắt đầu của hệ thống Học Excel Online. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký khóa học này. Bạn có thể chủ động liên lạc qua email: để biết thêm chi tiết về những ưu đãi hiện có cho bạn nếu bạn là học viên mới.
Chào các anh chi, xin các anh chị trên diễn đàn giúp đỡ mình phần này.
Em có 3 file báo cáo gồm: Báo cáo tháng 1, báo cáo tháng 2, báo cáo tháng 3. Và một file báo cáo quý tổng hợp từ 3 file trên. Em đã tạo được công thức tính tổng liên kết với 3 file, nhưng mỗi lần mở file báo cáo quý lên em phải mở 3 file báo cáo tháng lên mới cập nhật được [Trong mỗi file báo cáo tháng có rất nhiều sheet, mỗi sheet sẽ ứng với một huyện thị trong tỉnh. Em đã xóa đi những huyện khác chỉ để lại 2 sheet mẫu]. Xin các anh chị giúp đỡ em là sao cho mỗi lần em chỉ cần mở file tổng hợp lên thì có đoạn lệnh mở 3 file báo cáo tháng lên để cho file tổng hợp cập nhật kết quả rồi đóng lại. Mong các anh chị trên diễn đàn giúp đỡ cho em. Em xin cám ơn. Em trình bày hơi khó hiểu. Em nhờ các anh chi trên diễn đàn hướng dẫn em viết code để khi mở file tổng hợp lên thì đoạn code sẽ các file liên kết lên thôi ạ.
Lần chỉnh sửa cuối: 20/6/11
Chào các anh chi, xin các anh chị trên diễn đàn giúp đỡ mình phần này.
Em có 3 file báo cáo gồm: Báo cáo tháng 1, báo cáo tháng 2, báo cáo tháng 3. Và một file báo cáo quý tổng hợp từ 3 file trên. Em đã tạo được công thức tính tổng liên kết với 3 file, nhưng mỗi lần mở file báo cáo quý lên em phải mở 3 file báo cáo tháng lên mới cập nhật được [Trong mỗi file báo cáo tháng có rất nhiều sheet, mỗi sheet sẽ ứng với một huyện thị trong tỉnh. Em đã xóa đi những huyện khác chỉ để lại 2 sheet mẫu]. Xin các anh chị giúp đỡ em viết code sao cho mỗi lần em chỉ cần mở file tổng hợp lên là tự động mở 3 file báo cáo tháng lên để cập nhật kết quả từ 3 file báo cáo tháng rồi tự đóng lại. Mong các anh chị trên diễn đàn giúp đỡ cho em. Em xin cám ơn
Không phải ý mình như vậy. Ý mình là khi mở file tổng hợp lên thì có đoạn code tự động mở những file liên kết lên để file tổng hợp cập nhật xong rồi có đoạn code cho những đóng những file này lại. Tại vì khi những file liên kết mở lên thì file tổng hợp sẽ tự động cập nhật theo công thức rồi. Mình cũng muốn làm như ý bạn lắm nhưng vấn đề ở đây là dữ liệu rất nhiều. Mỗi sheet đại diện cho một huyện thi và số bệnh nhân khám nhiều lắm. Nếu để chung một sheet rất là rối.
Chào các anh chi, xin các anh chị trên diễn đàn giúp đỡ mình phần này.
Em có 3 file báo cáo gồm: Báo cáo tháng 1, báo cáo tháng 2, báo cáo tháng 3. Và một file báo cáo quý tổng hợp từ 3 file trên. Em đã tạo được công thức tính tổng liên kết với 3 file, nhưng mỗi lần mở file báo cáo quý lên em phải mở 3 file báo cáo tháng lên mới cập nhật được [Trong mỗi file báo cáo tháng có rất nhiều sheet, mỗi sheet sẽ ứng với một huyện thị trong tỉnh. Em đã xóa đi những huyện khác chỉ để lại 2 sheet mẫu]. Xin các anh chị giúp đỡ em là sao cho mỗi lần em chỉ cần mở file tổng hợp lên thì có đoạn lệnh mở 3 file báo cáo tháng lên để cho file tổng hợp cập nhật kết quả rồi đóng lại. Mong các anh chị trên diễn đàn giúp đỡ cho em. Em xin cám ơn. Em trình bày hơi khó hiểu. Em nhờ các anh chi trên diễn đàn hướng dẫn em viết code để khi mở file tổng hợp lên thì đoạn code sẽ các file liên kết lên thôi ạ.
Có ai có đoạn code để mở 3 file này lên chỉ giúp em với. Em chỉ cần đoạn code thực hiện lệnh tự động mở 3 file liên kết lên thôi. Giúp em với?
Có ai có đoạn code để mở 3 file này lên chỉ giúp em với. Em chỉ cần đoạn code thực hiện lệnh tự động mở 3 file liên kết lên thôi. Giúp em với?
Private Sub Auto_Open[] On Error Resume Next Dim i As Long For i = Mid[ThisWorkbook.Name, 15, 1] * 3 - 2 To Mid[ThisWorkbook.Name, 15, 1] * 3 Workbooks.Open Filename:=ThisWorkbook.Path & "\Bao cao BH thang " & i & "\Ngoai tru thang " & i & ".xls" Next End Sub
Lần chỉnh sửa cuối: 21/6/11
Bạn dùng code này.
End Sub[/php]
[/php]Private Sub Aut
Mình đã làm được cám ơn bạn rất nhiều. Bạn có thể giải thích cho mình hiểu lệnh này như thế nào không: "Mid[ThisWorkbook.Name, 15, 1] * 3 - 2 ". Vì chỉ mới làm quen vba mấy hôm nay. Cám ơn bạn nhiều nha
Mid[ThisWorkbook.Name, 15, 1]
Dùng để lấy quý [ở ví dụ của bạn là quý 2] Quý 2 thì từ tháng 4 [2*3-2] đến tháng 6 [2*3]Tôi làm vậy là để bạn có thể dùng code này cho các tháng khác.
Cái đó là xác định những file cần mở là file nào Mid[ThisWorkbook.Name, 15, 1]
Tôi làm vậy là để bạn có thể dùng code này cho các tháng khác.
Mình hiểu rồi. Sẵn đây bạn cho mình làm phiền chút nữa nha. Mình mở lên xong mình muốn đóng 3 file này lại như thế nào bạn. Thành thật xl bạn nha vì mình mù tịt vba.
Mình hiểu rồi. Sẵn đây bạn cho mình làm phiền chút nữa nha. Mình mở lên xong mình muốn đóng 3 file này lại như thế nào bạn. Thành thật xl bạn nha vì mình mù tịt vba.
Private Sub Auto_Open[] On Error Resume Next Dim i As Long For i = Mid[ThisWorkbook.Name, 15, 1] * 3 - 2 To Mid[ThisWorkbook.Name, 15, 1] * 3 Workbooks.Open Filename:=ThisWorkbook.Path & "\Bao cao BH thang " & i & "\Ngoai tru thang " & i & ".xls" Next For i = Mid[ThisWorkbook.Name, 15, 1] * 3 - 2 To Mid[ThisWorkbook.Name, 15, 1] * 3 Workbooks["Ngoai tru thang " & i & ".xls"].Close Next End Sub
Mình cám ơn bạn rất nhiều
Theo ý của bạn là muốn tạo liên kết ở bảng tính có chứa giá trị tổng các sheet mà bạn muốn liên kết đến nó, để khi nhập dữ liệu đầu vào cho các sheet này thì Sheet tổng tự động cập nhật vào sheet tổng và ta được kết quả tổng hợp. Để liên kết từ 01 Sheet tổng với các sheet con chứa dữ liệu nhập đầu vào bạn cần phải tạo tiêu đề cột của toàn bộ các Sheet cho giống nhau và các mục đầu dòng của các sheet cũng phải giống nhau.
Giả sử tôi đặt tên cho sheet tổng là TỔNG HỢP còng các sheet con là Hàng bán tháng 1, Hàng bán tháng 2 ,Hàng bán tháng 3.
Sau đó ở sheet TỔNG HỢP bạn click vào cell trống đầu tiên [ví dụ B2] và bạn gõ dấu = sau đó bạn click vào sheet Hàng bán tháng 1 và bạn click vào vị trí B2 của sheet này và nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 2 và bạn click vào vị trí B2 của sheet này và bạn nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 3 và bạn click vào vị trí B2 của sheet này, xong bạn nhấn nút Enter, lúc này tại cell B2 của sheet TỔNG HỢP ta được kết quả tổng hợp của số liêu B2 của các Sheet hàng bán 1, 2, 3. Tại cell B2 của sheet TỔNG HỢP ta có công thức như sau:
='Hàng bán tháng 1'!B2+'Hàng bán tháng 2'!B2+'Hàng bán tháng 3'!B2
Vậy là ta đã tạo xong việc liên kết giữa sheet TỔNG HỢP với các Sheet hàng bán 1, 2, 3.
Chúc bạn thực hiện thành công.
Theo ý của bạn là muốn tạo liên kết ở bảng tính có chứa giá trị tổng các sheet mà bạn muốn liên kết đến nó, để khi nhập dữ liệu đầu vào cho các sheet này thì Sheet tổng tự động cập nhật vào sheet tổng và ta được kết quả tổng hợp. Để liên kết từ 01 Sheet tổng với các sheet con chứa dữ liệu nhập đầu vào bạn cần phải tạo tiêu đề cột của toàn bộ các Sheet cho giống nhau và các mục đầu dòng của các sheet cũng phải giống nhau.
Giả sử tôi đặt tên cho sheet tổng là TỔNG HỢP còng các sheet con là Hàng bán tháng 1, Hàng bán tháng 2 ,Hàng bán tháng 3.
Sau đó ở sheet TỔNG HỢP bạn click vào cell trống đầu tiên [ví dụ B2] và bạn gõ dấu = sau đó bạn click vào sheet Hàng bán tháng 1 và bạn click vào vị trí B2 của sheet này và nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 2 và bạn click vào vị trí B2 của sheet này và bạn nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 3 và bạn click vào vị trí B2 của sheet này, xong bạn nhấn nút Enter, lúc này tại cell B2 của sheet TỔNG HỢP ta được kết quả tổng hợp của số liêu B2 của các Sheet hàng bán 1, 2, 3. Tại cell B2 của sheet TỔNG HỢP ta có công thức như sau:
='Hàng bán tháng 1'!B2+'Hàng bán tháng 2'!B2+'Hàng bán tháng 3'!B2
Vậy là ta đã tạo xong việc liên kết giữa sheet TỔNG HỢP với các Sheet hàng bán 1, 2, 3.
Chúc bạn thực hiện thành công.
//www.4shared.com/document/OQ6NUu6_/To_lin_kt_gia_cc_sheet.html
Chúc các bạn thực hiện thành công.
Do bận quá tôi hướng dẫn mà chưa kịp tạo File để Post lên, hôm nay tôi mới tạo xong File mẫu [liên kết các sheet trong 1 File], mong các bạn thông cãm.
Thực tế đối với những xí nghiệp, nhà máy tạo ra nhiều sản phẩm khi theo dõi thì phải chứa trong nhiều File [nếu bạn nào quan tâm thì đón đọc bài viết tạo liên kết 1 bảng tính đến nhiều bảng tính chứa ở nhiều File khác nhau] do bận nhiều việc của cơ quan nên tôi chưa làm xong, hẹn các bạn lần sau. Phần đã hướng dẫn lần trước các bạn tải File về để tham khảo tại Link sau:
//www.4shared.com/document/OQ6NUu6_/To_lin_kt_gia_cc_sheet.html
Chúc các bạn thực hiện thành công.
Thanks bạn nhiều
Link die rồi bạn ơi, bạn cho mình link down khác được ko. Mình đang rất cần để làm cái này
Thanks bạn nhiều
Nếu cần tại sao không mở Topic mới nêu rõ vấn đề muốn làm rồi Up File lên bạn sẽ dễ theo dõi và tập trung hỏi về vấn đề của mình sẽ hay hơn không?
Các bác cho em hỏi vấn đề này nhé. Em có nhiều file excel. Bây giờ em muốn tổng hợp thành 1 file excel [có nhiều sheet] của các tất cả file excel gốc]. Các bác hướng dẫn giúp em nhé. em cám ơn ạ!
Tks bác nhé. Nhưng em cần tổng hợp nhiều file excel [file con] thành 1 file excel [file tổng] chứa tất cả các sheet [của file con].
Tks bác nhé. Nhưng em cần tổng hợp nhiều file excel [file con] thành 1 file excel [file tổng] chứa tất cả các sheet [của file con].
Lưu ý khi sử dụng:
1/ Dường dẫn File là D:\Gop File_Folder\ và tất cả các File cần lấy dữ liệu phải chứa trong Folder có tên Gop File_Folder
Theo ý của bạn là muốn tạo liên kết ở bảng tính có chứa giá trị tổng các sheet mà bạn muốn liên kết đến nó, để khi nhập dữ liệu đầu vào cho các sheet này thì Sheet tổng tự động cập nhật vào sheet tổng và ta được kết quả tổng hợp. Để liên kết từ 01 Sheet tổng với các sheet con chứa dữ liệu nhập đầu vào bạn cần phải tạo tiêu đề cột của toàn bộ các Sheet cho giống nhau và các mục đầu dòng của các sheet cũng phải giống nhau.
Giả sử tôi đặt tên cho sheet tổng là TỔNG HỢP còng các sheet con là Hàng bán tháng 1, Hàng bán tháng 2 ,Hàng bán tháng 3.
Sau đó ở sheet TỔNG HỢP bạn click vào cell trống đầu tiên [ví dụ B2] và bạn gõ dấu = sau đó bạn click vào sheet Hàng bán tháng 1 và bạn click vào vị trí B2 của sheet này và nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 2 và bạn click vào vị trí B2 của sheet này và bạn nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 3 và bạn click vào vị trí B2 của sheet này, xong bạn nhấn nút Enter, lúc này tại cell B2 của sheet TỔNG HỢP ta được kết quả tổng hợp của số liêu B2 của các Sheet hàng bán 1, 2, 3. Tại cell B2 của sheet TỔNG HỢP ta có công thức như sau:
='Hàng bán tháng 1'!B2+'Hàng bán tháng 2'!B2+'Hàng bán tháng 3'!B2
Vậy là ta đã tạo xong việc liên kết giữa sheet TỔNG HỢP với các Sheet hàng bán 1, 2, 3.
Chúc bạn thực hiện thành công.
Chào be09, ngoài cách cộng như thế này mình có thể dùng code ko vì không thể mỗi cell lại cứ đặt công thức như vậy, có thể làm cho các cell trong một vùng luôn chẳng hạn.
Đây là Topic của người khác bạn không nên chen ngang một chủ đề khi không có cùng một nội dung sẽ vi phạm nội quy. Vì vậy, bạn nên mở Topic khác và đính kèm File để các thành viên xem mặt mũi nó thế nào mới biết có làm được hay không.
Page 2
Theo ý của bạn là muốn tạo liên kết ở bảng tính có chứa giá trị tổng các sheet mà bạn muốn liên kết đến nó, để khi nhập dữ liệu đầu vào cho các sheet này thì Sheet tổng tự động cập nhật vào sheet tổng và ta được kết quả tổng hợp. Để liên kết từ 01 Sheet tổng với các sheet con chứa dữ liệu nhập đầu vào bạn cần phải tạo tiêu đề cột của toàn bộ các Sheet cho giống nhau và các mục đầu dòng của các sheet cũng phải giống nhau.
Giả sử tôi đặt tên cho sheet tổng là TỔNG HỢP còng các sheet con là Hàng bán tháng 1, Hàng bán tháng 2 ,Hàng bán tháng 3.
Sau đó ở sheet TỔNG HỢP bạn click vào cell trống đầu tiên [ví dụ B2] và bạn gõ dấu = sau đó bạn click vào sheet Hàng bán tháng 1 và bạn click vào vị trí B2 của sheet này và nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 2 và bạn click vào vị trí B2 của sheet này và bạn nhấn nút + tiếp theo bạn click vào sheet Hàng bán tháng 3 và bạn click vào vị trí B2 của sheet này, xong bạn nhấn nút Enter, lúc này tại cell B2 của sheet TỔNG HỢP ta được kết quả tổng hợp của số liêu B2 của các Sheet hàng bán 1, 2, 3. Tại cell B2 của sheet TỔNG HỢP ta có công thức như sau:
='Hàng bán tháng 1'!B2+'Hàng bán tháng 2'!B2+'Hàng bán tháng 3'!B2
Vậy là ta đã tạo xong việc liên kết giữa sheet TỔNG HỢP với các Sheet hàng bán 1, 2, 3.
Chúc bạn thực hiện thành công.
//www.giaiphapexcel.com/diend...sheet-trong-các-file-khác.135321/#post-861507
File bài 3 không có tiêu đề và cái gì ở cột B, chẳng thành viên nào hiểu để trả lời cho bạn.