Công thức hàm SUMPRODUCT: Hướng dẫn Từ A -> Z

Tìm hiểu cách tính toán dựa trên nhiều điều kiện hay các bảng tính phức tạp bằng hàm SUMPRODUCT trong Excel và Google Sheet. Dưới đây là bài viết hướng dẫn chi tiết và ví dụ minh họa đơn giản.

Hàm SUMPRODUCT là một hàm trong Excel được sử dụng để tính tổng của tích các phần tử tương ứng trong một hoặc nhiều dãy dữ liệu. Trong bài viết sau đây, hãy cùng Techtuts nghiên cứu hàm SUMPRODUCT trong Excel bạn nhé.

Hàm SUMPRODUCT trong Excel là gì?

Hàm Sumproduct là hàm tính tổng các phần tử tương ứng trong một hoặc nhiều dãy dữ liệu chứa nhiều điều kiện Excel. Về cơ bản, nó thực hiện phép nhân giữa các phần tử tương ứng của hai hay nhiều mảng, sau đó tính tổng của các kết quả đó. Đây là hàm được kết hợp từ hai hàm cơ bản trong Excel là hàm SUM và hàm.Product.

Hàm SUMPRODUCT trong Excel

Cú pháp và các trường hợp cần sử dụng hàm SUMPRODUCT.

Cú pháp hàm SUMPRODUCT

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

Trong đó:

array1, array2, array3: Là các dãy (mảng) dữ liệu mà bạn muốn tính phép nhân các phần tử tương ứng, rồi cộng lại với nhau. Tất cả các mảng phải đảm bảo có cùng kích thước, số dòng, số cột trong dữ liệu.

Các trường hợp cần sử dụng hàm SUMPRODUCT

• Tính tổng có trọng số: tức là nhân từng giá trị tương ứng với từng trọng số và cộng lại.
• Tính tổng doanh thu: khi bạn cần tính tổng doanh thu sản phẩm tương đương với số lượng bán ra
• Tính tổng có điều kiện: thay thế hàm SUMIF hoặc SUMIFS

Xem thêm: Tối ưu hóa báo cáo Excel với Hàm Sumifs nhiều điều

Hướng dẫn sử dụng hàm SUMPRODUCT trong Excel

Hàm Sumproduct nhiều điều kiện

Hàm SUMPRODUCT có thể xử lý nhiều điều kiện khác nhau một cách hiệu quả trong Excel. Khi sử dụng nhiều điều kiện, bạn có thể áp dụng các phép toán logic như nhân (*) để kết hợp các điều kiện lại với nhau. Mỗi điều kiện trả về TRUE hoặc FALSE, chuyển TRUE thành 1 và FALSE thành 0. Sau đó, hàm SUMPRODUCT sẽ tính tổng các sản phẩm của các giá trị tương ứng từ các mảng khi thỏa mãn các điều kiện.

Cú pháp của hàm SUMPRODUCT nhiều điều kiện:

=SUMPRODUCT((điều kiện1) * (điều kiện2) * ... * (dãy1) * (dãy2))

• điều kiện1, điều kiện2, ...: Là các điều kiện logic (ví dụ, so sánh, bằng, lớn hơn, nhỏ hơn).
• dãy1, dãy2: Là các mảng số mà bạn muốn tính tổng tích của chúng.

Ví dụ 1: Tính tổng với nhiều điều kiện số

Giả sử bạn có bảng dữ liệu sau:

Bạn muốn tính tổng doanh thu (Số lượng * Giá mỗi sản phẩm) cho các sản phẩm có giá lớn hơn 20 và thuộc khu vực "Bắc".

Công thức:

=SUMPRODUCT((C2:C5 > 20) * (D2:D5 = "Bắc") * B2:B5 * C2:C5)

Kết quả:

Chỉ có sản phẩm C thỏa mãn điều kiện (5 * 30 = 150), do đó kết quả là 150.

Hàm Sumproduct tính điểm trung bình có trọng số

Khi bạn tính điểm trung bình có trọng số, mỗi giá trị sẽ được nhân với trọng số tương ứng của nó, sau đó tổng các giá trị có trọng số này sẽ được chia cho tổng các trọng số.

Cấu trúc: = SUMPRODUCT(Điểm, Trọng_số) / SUM(Trọng_số)

Trong đó:

• SUMPRODUCT(Điểm, Trọng_số): Tính tổng tích giữa điểm số và trọng số.
• SUM(Trọng_số): Tính tổng các trọng số.

Ví dụ: Giả sử bạn có bảng điểm các môn học như sau:

Sử dụng công thức: =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)

Kết quả:

Điểm trung bình = 80 / 10 = 8.0.

Tại sao phải sử dụng hàm SUMPRODUCT trong Excel?

Hàm SUMPRODUCT trong Excel là một công cụ mạnh mẽ và linh hoạt được sử dụng để thực hiện các phép tính toán phức tạp một cách dễ dàng. Việc sử dụng hàm này mang lại nhiều lợi ích vượt trội trong nhiều trường hợp mà các hàm thông thường không thể giải quyết hiệu quả. Dưới đây là những lý do chính khiến SUMPRODUCT trở thành lựa chọn ưu tiên trong nhiều tình huống:

Tính tổng và tích nhanh chóng trong một công thức duy nhất

Thay vì phải tính riêng từng phần tử một rồi sau đó mới cộng vào, thì Sumproduct cho phép thực hiện tất cả các bước trên chỉ trong một lần tính với một công thức duy nhất. Đây là cách giúp tiết kiệm thời gian một cách tối ưu để tính toán hiệu quả, chính xác.

Xử lý nhiều điều kiện phức tạp

Dễ dàng kết hợp các điều kiện phức tạp, đa chiều để lọc nhanh và tính toán mà không cần đến hàm mảng như các hàm Sumif hay Sumifs.

Linh hoạt khi kết hợp với các hàm khác

Hàm SUMPRODUCT có thể dễ dàng kết hợp với các hàm khác trong Excel như IF, ISNUMBER, IFERROR để xử lý các trường hợp đặc biệt, hoặc khắc phục lỗi dữ liệu.

Các lỗi hay gặp phải trong quá trình sử dụng hàm Sumproduct

Trong quá trình sử dụng hàm Sumproduct trong Excel, không thể tránh khỏi việc mắc sai lầm khiến các lỗi xảy ra. Nhằm tránh các lỗi có thể gặp phải và có thể xử lý một cách hiệu quả, dưới đây là một vài lỗi phổ biến thường gặp:

Hàm sumproduct bị lỗi Value,

Nguyên nhân:

• Kích thước các mảng không đồng nhất
• Dữ liệu không phải số
• Sử dụng hàm SUMPRODUCT với dữ liệu không được hỗ trợ

Cách khắc phục:

• Đảm bảo rằng các dãy trong công thức có cùng kích thước, phải có cùng số ô.
• Đảm bảo tất cả các giá trị trong mảng là số hoặc có thể chuyển đổi thành số. Nếu bạn cần bỏ qua các giá trị không phải số, hãy sử dụng hàm ISNUMBER để lọc dữ liệu.

Lỗi Value khi sử dụng SUMPRODUCT trong Excel

Lỗi hàm sumproduct bằng 0

Nguyên nhân:

• Không có giá trị nào thỏa mãn điều kiện:
• Nhân với giá trị 0 hoặc FALSE:
• Dữ liệu trống hoặc không hợp lệ:

Cách khắc phục:

• Đảm bảo rằng có ít nhất một giá trị thỏa mãn các điều kiện của bạn.
• Xác nhận rằng các ô bạn đang tính toán không trống hoặc không chứa giá trị 0.
• Nếu bạn sử dụng điều kiện logic, kiểm tra xem có điều kiện nào bị sai dẫn đến toàn bộ dữ liệu bị loại bỏ không.

Lỗi kết quả bằng 0

Kết luận

Trên đây là những thông tin cần phải biết về hàm SUMPRODUCT trong Excel, Google Sheet vô cùng đơn giản và dễ hiểu. Hy vọng rằng từ những thông tin và ví dụ minh hoạ cụ thể về hàm SUMPRODUCT, bạn có thể nhanh chóng làm chủ kiến thức về các hàm cú pháp này vào dữ liệu Excel.

Nếu bạn muốn tìm hiểu thêm về cách sử dụng và bài tập kèm theo của các hàm khác trong Excel, hãy tham khảo các bài viết hướng dẫn chi tiết trên Techtuts.net.