Hàm cộng các sheet cùng một vị trí trong excel

Nếu bạn là Kế toán tổng hợp hoặc Kiểm toán, ắt hẳn chúng ta thường xuyên phải tổng hợp dữ liệu và việc tính tổng cùng 1 ô trong nhiều Sheet để trình bày trên một báo cáo là một thao tác quen thuộc.

Cách đơn giản nhất nhưng cũng mất nhiều thời gian nhất là chúng ta đi từng Sheet, nhấp chuột chọn từng ô để tạo công thức.

Ví dụ lỡ trường hợp nào chúng ta có…100 Sheet thì cách làm trên đây thật sự không hiệu quả vì nó mất cả buổi sáng và đôi khi chúng ta không kiểm soát được tính đầy đủ và chính xác.

Do đó, UniTrain giới thiệu với các bạn cách làm hữu hiệu sau đây để có thể thực hiện điều này một cách chính xác và tiết kiệm thời gian.

Tính tổng cùng 1 ô trong nhiều Sheet

Để Tính tổng cùng 1 ô trong nhiều Sheet, chúng ta có thể sử dụng công thức có cú pháp sau:

= SUM(‘Sheet_đầu_tiên:Sheet_cuối_cùng’!Ô_hoặc_range_cần_tính)

Trong cú pháp ở hàm này, chúng ta nhớ lưu ý các đều sau:

– Dấu phẩy trên (‘): Nhớ để dấu này ở trước tên Sheet_đầu_tiên­ và sau tên ­Sheet_cuối_cùng nhé cả nhà.

– Sheet_đầu_tiên: Là tên của Sheet đầu tiên trong dãy Sheet cần tính

– Sheet_cuối_cùng: Là tên của Sheet cuối cùng trong dãy Sheet cần tính

– Ô_hoặc_range_cần_tính: Là địa chỉ của ô hoặc vùng cần tính tổng

Như vậy, để rút gọn công thức khi nãy lại, chúng ta cần làm 2 bước
1. Sắp xếp loạt Sheet cần tính liên tục và liền nhau từ January 2016 – Profit đến December 2016 – Profit
2. Sử dụng hàm sau đây:

= SUM(‘January 2016 – Profit: December 2016 – Profit’!A1)

Thủ thuật

Sau khi thực hiện như trên, nếu chúng ta vẫn thấy công thức của chúng ta vẫn…dài thì chúng ta có thể làm 1 mẹo sau nữa:
1. Chèn thêm 1 Sheet tên Start trước Sheet January 2016 – Profit
2. Chèn thêm 1 Sheet tên End trước Sheet December 2016 – Profit

Lưu ý là 2 Sheet này trống hoàn toàn và không chứa giá trị nào hết

3. Lúc này, chúng ta sử dụng hàm sau:

= SUM(‘Start:End’!A1)

4. Ẩn 2 Sheet Start và End lại.

Như vậy, lúc này Excel sẽ tính tổng các sheet Start, January 2016 – Profit,…, December 2016 – Profit và Endnhưng 2 sheet này không có giá trị nên không ảnh hưởng đến kết quả của chúng ta mà hàm lại ngắn hơn.

Lưu ý

Khi sử dụng tính năng này để Tính tổng cùng 1 ô trong nhiều Sheet, chúng ta cần lưu ý các điều sau:
1. Các Sheet của chúng ta phải cùng 1 định dạng, nếu không thì Excel sẽ tính sai. Vì vậy, chúng ta cần đảm bảo các Sheet của chúng ta cần phải cùng 1 định dạng.
2. Bình thường, khi chúng ta chèn thêm dòng, cột thì địa chỉ ô của chúng ta sẽ tự động thay đổi theo. Khi tính tổng trong nhiều sheet như thế này, Excel chỉ tự động thay đổi địa chỉ ô khi chúng ta chọn tất cả các Sheet cùng lúc và chèn dòng, cột.
3. Excel sẽ tính tổng tất cả các Sheet nằm giữa Sheet_đầu_tiên và Sheet_cuối_cùng nên chúng ta cẩn thận các Sheet nằm giữa, kể cả các Sheet bị ẩn. Nên chúng ta cần cẩn thận hết sức.

Nguồn: Coffee Excel

Tổng hợp dữ liệu của các sheet

Bạn có thể tải file để cùng mình thực hành tại đây.

Ở đây mình có 1 ví dụ. Các bạn cùng làm ví dụ này cùng mình nhé. 

Bây giờ chúng ta sẽ cùng nhau tổng hợp dữ liệu từ các Sheet này vào Sheet tổng hợp nhé.

Đầu tiên chúng ta sẽ lấy tên tỉnh thành ra trước. Để lấy tên các khu vực thì các bạn chỉ cần sang bôi đen cột Khu vực bên Sheet1 nhấn phải chuột rồi chọn copy sau đó quay lại Sheet tổng hợp để Paste ra rồi sau đó các bạn vào thẻ Data chọn Remove Duplicates để lấy ra các tỉnh thành duy nhất.

Tiếp theo chúng ta sẽ đi lấy tên Sheet. Để lấy tên công thức thì chúng ta phải sử dụng Define name. Công thức của Define name là: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Sau khi có công thức Define name các bạn sẽ copy công thức đó rồi vào thẻ Formulas rồi chọn Define name thì cửa sổ Define sẽ hiện ra rồi trong đó phần Name các bạn sẽ đặt tên tiêu đề bất kỳ bạn muốn rồi phần Refers to các bạn Paste cái công thức vừa Copy vào rồi ấn Ok.

Các bạn có thể vào phần Name Manager để kiểm tra cái Define name các bạn vừa tạo và nếu các bạn muốn sửa lại công thức thì có thể ấn vào mục Edit.

Bây giờ các bạn sẽ đánh số thứ tự theo số Sheet của các bạn ví dụ mình có 4 Sheet mình sẽ đánh số thứ tự từ 1 đến 4 để lấy tên Sheet. Tiếp theo các bạn sẽ sử dụng hàm Index. Trong hàm Index thành phần đầu tiên của các bạn sẽ là tên Define name lúc nãy, còn thành phần thứ 2 sẽ là vị trí ô lúc nãy các bạn đánh số 1 tức Sheet 1 thì nó sẽ ra cái Sheet đầu tiên của mình là tổng hợp.

Ví dụ như trong bài của mình sẽ là: =INDEX(LayTenSheet,E1)

Sau đó các bạn kéo sang ngang ô vừa mới Index để copy công thức lấy dữ liệu sang các ô bên cạnh. Các ô bên cạnh đó theo thứ tự các số sẽ là thứ tự lần lượt các Sheet.

Như vậy mình đã có tên Sheet rồi đúng không. Đầu tiên các bạn vào ô chứa tên Sheet cần tổng hợp và tên khu vực muốn tổng hợp rồi nhập sử dụng hàm CountIF. Ở thành phần đầu tiên sẽ là cái cột dữ liệu ở Sheet gốc để mang đi tổng hợp, trong bài này sẽ là cột Khu vực các bạn tích chọn cột Khu vực sau đó thành phần thứ 2 sẽ là vị trí ô chứa tiêu chí để tổng hợp ví dụ trong bài này là Hà Nội thì các bạn tick chọn ô Hà Nội là được rồi Enter.

Ví dụ bài này sẽ là: =COUNTIF(Sheet1!B:B,tonghop!D3)

Tuy nhiên dùng cách này các bạn sẽ không thể copy công thức cho các ô khác được nên cách bạn sẽ đổi công thức cho mình như sau.

Bây giờ mình sẽ sử dụng hàm CountIf  kết hợp với hàm Indirect. Đầu tiên các bạn vào ô chứa tên Sheet cần tổng hợp và tên khu vực muốn tổng hợp rồi nhập lồng 2 hàm như sau =COUNTIF(INDIRECT("'"&F$2&"'!$B:$B"),tonghop!$D3) trong đó F$2 là vị trí chứa tên Sheet sau khi các bạn F4 2 lần, !$B:$B là cột chứa dữ liệu từ Sheet gốc chưa tổng hợp trong bài của mình là Sheet 1, tonghop!$D3 là tên Sheet đang chứa vị trí ô cần tổng hợp theo tiêu chí nào sau khi đã F4 3 lần như trong bài của mình là Khu vực: Hà Nội. (Phần này khá phức tạp các bạn nên xem video của mình để hiểu rõ hơn).

Ví dụ: =COUNTIF(INDIRECT("'"&F$2&"'!$B:$B"),tonghop!$D3)

Sau đó bạn chỉ cần kéo ra các ô khác để copy công thức thì kết quả các ô tương tự sẽ hiện ra.

Các bạn lưu ý kết quả của mình có 1 ô trống. Đó là những khách hàng không có khu vực hoặc không thuộc tỉnh thành nào cả gọi là Blanks.

Bây giờ mình sẽ đổi nó thành Other để thực hiện tổng hợp nó nhé. Các bạn sẽ dùng hàm CountA nhé. Trong thành phần CountA các bạn sẽ chọn cột số thứ tự bên cái Sheet ban đầu do cột đó không có dữ liệu trống rồi trừ đi 1 đó là dòng tiêu đề để tính ra có bao nhiêu khách hàng sau đó các bạn trừ đi các khách hàng đã có khu vực bằng cách sử dụng hàm Sum các kết quả đã tổng hợp bên trên.

Lưu ý sau khi chọn cột xong phải đóng ngoặc vào luôn nhé.

Ví dụ như trong bài của mình sẽ là: =COUNTA(Sheet1!A:A)-1-SUM(tonghop!F3:F8)

Tương tự như bài trên để copy công thức sang các ô khác các bạn phải sửa hàm thành như sau =COUNTA(INDIRECT("'"&F$2&"'!$A:$A"))-1-SUM(tonghop!F3:F8)

Giống như trên nên mình không hướng dẫn lại nữa. Các bạn cũng chỉ cần kéo sang các ô bên cạnh là được.

Cuối cùng cột tinhtong các bạn chỉ cần dùng hàm SUM để tính tổng theo từng khu vực là được.

Ví dụ: =SUM(F16:H16)

Tương tự các bạn kéo copy công thức cho các ô dưới là được. Rồi các bạn tiến hành chỉnh sửa bảng gồm 2 cột Khu vực và tinhtong sao cho đẹp nhất là được ví dụ như kẻ khung, bôi đậm..

Ngoài ra các bạn có thể vào sửa tên cột tinhtong bằng cách vào đổi tên Sheet tinhtong thành Tính tổng rồi các bạn ấn vô ô tinhtong cũ rồi ấn Enter nó sẽ tự động cập nhật. Bên cạnh đó các bạn có thể ẩn đi những dữ liệu không cần thiết bằng cách bôi đen chúng rồi nháy phải chuột chọn Format Cells.

Rồi tiếp đó các bạn vào thẻ Costom sửa phần General thành 3 dấu chấm phẩy liền nhau rồi bấm Ok là được.

Chúc các bạn thành công.

Video hướng dẫn