Hướng dẫn gộp 2 bảng trong Excel vào 1 bảng lớn với hàm SUMPRODUCT

Nếu bạn đang tìm kiếm một thao tác đơn giản để gộp dữ liệu từ 2 bảng vào 1 bảng lớn trong Excel thì hàm SUMPRODUCT chính là trợ thủ đắc lực nhất. Trong bài viết ngày hôm nay, các bạn hãy cùng Gitiho tìm hiểu cách thực hiện thủ thuật này nhé.

Giới thiệu hàm SUMPRODUCT trong Excel

Trước khi tìm hiểu về cách gộp dữ liệu từ nhiều sheet trong Excel, chúng ta hãy cùng nói về hàm SUMPRODUCT để nắm rõ công dụng của hàm trong thao tác với dữ liệu trang tính Excel.

Cú pháp hàm SUMPRODUCT

 

Hàm SUMPRODUCT trong Excel được thiết kế với mục đích trả về tổng một dải ô hoặc mảng tương ứng. Mặc dù thao tác mặc định của hàm là phép nhân, bạn hoàn toàn có thể sử dụng các phép tính trừ và chia trong cấu trúc hàm. Dưới đây là cú pháp chung của hàm SUMPRODUCCT:

=SUMPRODUCT(array1, [array2], [array3], ...)

Trong đó:

• array1 - Là tham số mảng thứ nhất bạn muốn tính tổng. Hàm sẽ tiến hành nhân các thành phần của tham số này, sau đó cộng tổng tất cả kết quả phép nhân đã tính. Đây là tham số bắt buộc.
• array2, array 3 -Là các tham số mảng tiếp theo bạn muốn tính tổng tương tự như cách làm với tham số array1. Bạn không bắt buộc phải nhập các tham số này nếu không có nhiều hơn 1 mảng cần tính.

Để thực hiện các phép tính khác phép nhân trong cấu trúc hàm, bạn chỉ cần thay thế dấu phẩy ngăn cách giữa các tham số bằng toán tử số học bạn yêu cầu, ví dụ như dấu + cho phép cộng hay dấu - cho phép trừ.

Một số lưu ý với hàm SUMPRODUCT

Khi sử dụng hàm SUMPRODUCT để tính tổng các mảng số liệu trong Excel, bạn cần lưu ý một vài điểm sau đây với cú pháp hàm:

• Các tham số trong cú pháp hàm cần phải có cùng kích thước, ví dụ như công thức: =SUMPRODUCT(A1:A5,B1:B5)
• Hàm SUMPRODUCT trong Excel sẽ trả về lỗi #VALUE! nếu các tham số có kích thước khác nhau hoặc một tham số có kích thước với các tham số còn lại.
• Nếu trong các mảng tham số của hàm SUMPRODUCT xuất hiện một mục không chứa giá trị ở định dạng số, hàm sẽ tự hiểu giá trị đó là số 0.
• Không nên sử dụng các tham chiếu cột đầy đủ cho hàm SUMPRODUCT trong Excel vì số lượng các hàng trong cột có thể lên đến hàng trăm, do đó làm chậm tốc độ làm việc của Excel.

Xem thêm: Hướng dẫn chi tiết sử dụng hàm SUMPRODUCT nâng cao trong Excel

Cách gộp 2 bảng trong Excel bằng hàm SUMPRODUCT

Có thể nói, cách dùng hàm SUMPRODUCT vô cùng đa dạng. Tuy nhiên, trong bài viết ngày hôm nay, chúng ta sẽ chỉ tập trung vào cách dùng hàm để gộp dữ liệu từ 2 bảng vào 1 bảng lớn trong Excel. Cùng thực hành với mình ngay nhé!

Đề bài

Chúng ta có một đề bài như sau:

 

Yêu cầu đặt ra là: Gộp dữ liệu từ Bảng nhóm 1 và Bảng nhóm 2 vào Bảng gộp dữ liệu.

Cách làm

Để thực hiện yêu cầu đề bài, chúng ta sẽ sử dụng hàm SUMPRODUCT để tính kết quả lần lượt từng bảng nhóm, sau đó cộng tổng và đưa vào Bảng gộp dữ liệu. Cách dùng hàm SUMPRODUCT sẽ được chia làm 3 bước trong ví dụ này.

Bước 1: Đặt tên cho phạm vi dữ liệu

Đừng vội xây dựng công thức hàm SUMPRODUCT ngay bạn nhé! Thay vào đó, hãy giúp các thao tác sau trong bài trở nên dễ dàng hơn bằng cách đặt tên cho các phạm vi dữ liệu cần tính.

 

Như bạn đã thấy, mình đặt tên lần lượt cho các phạm vi dựa vào tên bảng và tiêu đề cột. Đầu tiên là Bảng nhóm 1, chúng ta có 3 phạm vi cần đặt tên như sau:

• A3:A6 - B1_SanPham
• B2:D2 - B1_NhanVien
• B3:D6 - B1_KetQua

Cách đặt tên hoàn toàn tương tự với Bảng nhóm 2.

Xem thêm: Hướng dẫn các cách để bạn đặt tên cho vùng trong Excel

Bước 2: Xác định các thao tác theo trình tự

Khi phân tích yêu cầu đề bài, chúng ta đã thấy rằng:

• Điều kiện gộp bảng dựa vào 2 phần Tên sản phẩm theo cột F và Tên nhân viên tại dòng 2.
• Kết quả gộp bảng sẽ là phần kết quả tương ứng với Tên sản phẩm và Tên nhân viên trong 2 bảng.

Dựa vào các dữ kiện này, chúng ta có thể xây dụng công thức tính tổng vùng dữ liệu trong Bảng nhóm 1 và Bảng nhóm 2.

Bước 3: Xây dụng công thức Excel tính tổng

Chúng ta sẽ xây dựng công thức tính tổng để giải bài toán này với hàm SUMPRODUCT trong Excel. Cách dùng hàm SUMPRODUCT như sau:

=SUMPRODUCT((B1_NhanVien=G$2)*(B1_SanPham=$F3)*B1_KetQua)

Trong đó, chúng ta sẽ thực hiện các thao tác lần lượt dưới đây:

• Xét từng giá trị tên sản phẩm trong cột Sản phẩm (cột A) của Bảng nhóm 1.
• Xét từng giá trị tên nhân viên tại dòng 2 của Bảng nhóm 1.
• Lấy kết quả ở vị trí tương ứng trong phạm vi Kết quả (B3:D6) của Bảng nhóm 1.

Tương tự, chúng ta có công thức hàm SUMPRODUCT tính kết quả của Bảng nhóm 2 với các thao tác trên:

=SUMPRODUCT((B2_NhanVien=G$2)*(B2_SanPham=$F3)*B2_KetQua)

Sau khi đã có 2 công thức riêng biệt để tính tổng kết quả của từng bảng, chúng ta chỉ cần cộng chúng lại là đã hoàn thành được công thức tính gộp rồi. Bạn hãy điền công thức này vào ô G3 trong Bảng gộp dữ liệu nhé.

Từ ô G3, chúng ta hoàn toàn có thể áp dụng công thức tương tự vào các ô còn lại trong Bảng gộp dữ liệu để hoàn thiện cách dùng hàm SUMPRODUCT gộp 2 bảng trong Excel.

Kết quả Bảng gộp dữ liệu của chúng ta đã được điền đầy đủ và chính xác các số liệu theo yêu cầu đề bài:

Xem thêm: Hướng dẫn đếm các điều kiện loại trừ với SUMPRODUCT

Tổng kết

Qua bài viết ngày hôm nay, chúng ta đã học được thủ thuật sử dụng hàm SUMPRODUCT trong Excel để tính tổng và gộp dữ liệu từ 2 bảng khác nhau vào 1 bảng lớn. Hy vọng bài viết đã giúp các bạn hiểu thêm về cách dùng hàm SUMPRODUCT với các trường hợp xử lý số liệu Excel.

Để học thêm các ứng dụng khác của hàm SUMPRODUCT cũng như các hàm khác trong Excel, hãy tham khảo các bài viết trên blog Gitiho bạn nhé. Chắc chắn bạn sẽ học được thêm nhiều điều mới đó!

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!