Hôm nay mình sẽ hướng dẫn các bạn cách để tổng hợp được nhiều Sheet trong Excel để phục vụ cho các công việc và mục đích của các bạn nhé. Việc này khá phức tạp nhưng hữu dụng nên các bạn cố gắng là theo những gì mình hướng dẫn nhé. Chúng ta cùng vào bài nhé.
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.
Bạn đang xem: Cộng nhiều sheet trong excel
Ở đâ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(Lay
Ten
Sheet,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 Count
IF. Ở 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íchchọ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êndù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 Count
If 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 Count
A nhé. Trong thành phần Count
A các bạn sẽ chọn cột số thứ tự bên cái Sheet ban đầu do cột đó khôngcó 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ửahà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.
Cách tính tổng trong excel nói khó không phải khó nhưng để hiểu hết các chức năng và sử dụng tốt nhất các hàm tính tổng trong excel thì bạn cần phải nghiên cứu và tìm hiểu thêm rất nhiều.
Trong bài viết dưới đây, Chứng Chỉ Tin Học Văn Phòng sẽ hướng dẫn các bạn cách tính tổng trong Excel nhiều sheet có điều kiện. Hy vọng có thể giúp các bạn áp dụng tốt vào công việc của mình.
I. Cách tính tổng trong excel nếu trên nhiều trang tính
Nhiều dân văn phòng khi luôn gặp phải vấn đề không biết làm thế nào để tính tổng dữ liệu, trong khi dữ liệu này lại nằm rải rác ở các trang tính (sheets) khác nhau. Cách tính tổng trong excel bằng cách dùng hàm SUM trên nhiều sheet trong Excel, hay còn gọi là cách cộng các sheet trong Excel để giúp các bạn làm việc hiệu quả hơn.
Đôi khi dữ liệu của bạn có thể kéo dài một số trang tính trong một tệp Excel. Điều này là phổ biến đối với dữ liệu được thu thập định kỳ. Mỗi trang tính trong sổ làm việc có thể chứa dữ liệu trong một khoảng thời gian nhất định. Chúng tôi muốn một công thức tính tổng dữ liệu có trong hai hoặc nhiều trang tính.
Cách tính tổng trong excel bằng hàm SUM cho phép bạn dễ dàng tổng hợp dữ liệu trên nhiều trang tính bằng cách sử dụng Tham chiếu 3D: = SUM (Trang tính1: Trang tính2! A1)
Ví dụ này sẽ tổng hợp số lượng giao hàng theo kế hoạch cho mỗi Khách hàng trên nhiều trang tính, mỗi trang tính giữ dữ liệu liên quan đến một tháng khác nhau, ta có thể thực hiện cách tính tổng trong excel bằng cách sử dụng các Hàm SUMIFS, SUMPRODUCT và INDIRECT:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”), INDIRECT (“‘” & F3: F6 & “‘!” & “C3: C7”), H3))
1. Bước 1: Tạo công thức SUMIFS chỉ cho 1 trang tính đầu vào
Chúng tôi sử dụng Hàm SUMIFS để tổng hợp Số lượng Giao hàng theo Kế hoạch của Khách hàng cho một bảng dữ liệu đầu vào:
= SUMIFS (D3: D7, C3: C7, H3)
2. Bước 2: Thêm tham chiếu trang tính vào công thức
Chúng tôi giữ nguyên kết quả công thức, nhưng chúng tôi chỉ định rằng dữ liệu đầu vào nằm trong trang tính có tên là ‘Bước 2’
= SUMIFS (‘Bước 2’! D3: D7, ‘Bước 2’! C3: C7, H3)
3. Bước 3: Lồng trong một hàm SUMPRODUCT
Để chuẩn bị công thức để thực hiện các phép tính SUMIFS trên nhiều trang tính và sau đó tổng hợp các kết quả lại với nhau, chúng tôi thêm một Hàm SUMPRODUCT xung quanh công thức
= SUMPRODUCT (SUMIFS (‘Bước 3’! D3: D7, ‘Bước 3’! C3: C7, H3))
Sử dụng Hàm SUMIFS trên một trang tính sẽ mang lại một giá trị duy nhất. Trên nhiều trang tính, hàm SUMIFS xuất ra một mảng giá trị (một cho mỗi trang tính). Chúng tôi sử dụng Hàm SUMPRODUCT để tổng các giá trị trong mảng này.
4. Bước 4: Thay thế Tham chiếu Trang tính bằng Danh sách Tên Trang tính
Chúng tôi muốn thay thế phần Tên trang tính của công thức bằng một danh sách dữ liệu chứa các giá trị: Tháng 1, Tháng 2, Tháng 3 và Tháng 4. Danh sách này được lưu trữ trong các ô F3: F6.
Hàm INDIRECT để đảm bảo rằng danh sách văn bản hiển thị Tên trang được coi là một phần của tham chiếu ô hợp lệ trong Hàm SUMIFS.
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”), INDIRECT (“‘” & F3: F6 & “‘!” & “C3: C7”), H3))
Trong công thức này, tham chiếu phạm vi đã viết trước đó: ‘Bước 3’! D3: D7 Được thay thế bởi: INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”)
Dấu ngoặc kép làm cho công thức khó đọc, vì vậy ở đây nó được hiển thị với các khoảng trắng được thêm vào: INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”)
Sử dụng cách này để tham chiếu danh sách ô cũng cho phép chúng tôi tóm tắt dữ liệu từ nhiều trang tính không tuân theo kiểu danh sách số. Tham chiếu 3D tiêu chuẩn sẽ yêu cầu tên trang tính theo kiểu: Input1, Input2, Input3, v.v., nhưng ví dụ trên cho phép bạn sử dụng danh sách bất kỳ Tên trang tính nào và để chúng được tham chiếu trong một ô riêng biệt.
5. Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không có tham chiếu ô bị khóa:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”), INDIRECT (“‘” & F3: F6 & “‘!” & “C3: C7”), H3))
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & $ F $ 3: $ F $ 6 & “‘!” & “D3: D7”), INDIRECT (“‘” & $ F $ 3: $ F $ 6 & “‘!” & “C3: C7”), H3))
II. Cách tính tổng trong Excel nếu trên nhiều trang tính trong Google Trang tính
Không thể sử dụng Hàm INDIRECT để tham chiếu danh sách các trang tính trong Hàm SUMPRODUCT và SUMIFS trong Google Trang tính.
Thay vào đó, các phép tính SUMIFS riêng biệt có thể được thực hiện cho mỗi trang đầu vào và các kết quả được cộng lại với nhau:
= SUMIFS (Tháng 1! D3: D7, Tháng 1! C3: C7, H3)+ SUMIFS (Tháng 2! D3: D7, Tháng 2! C3: C7, H3)+ SUMIFS (Tháng 3! D3: D7, Tháng 3! C3: C7, H3)+ SUMIFS (Tháng 4! D3: D7, Tháng 4! C3: C7, H3)
Trên đây là những hướng dẫn chi tiết nhất của Chứng Chỉ Tin Học Văn Phòng trong cách tính tổng trong excel có điều kiện từ dữ liệu tổng hợp của nhiều sheet. Hy vọng những hướng dẫn trên có thể giúp bạn thực hiện được cách tính tổng trong excel hiệu quả nhất.
Xem thêm:
Nếu các bạn muốn nâng cao thêm kỹ năng thực hành tin học văn phòng để công việc của bạn được giải quyết một cách nhanh chóng và hiệu quả trong thời đại công nghệ số, các bạn có thể thâm khảo: Khóa học tin học văn phòng. Tìm một khóa học thực hành cùng với các chuyên gia ở những trung tâm đào tạo uy tín để thay đổi tác phong làm việc của bạn nhé.