Cách sử dụng hàm SUMPRODUCT trong Google Sheets

GiGiKing·Tháng ba 30, 2024·0 comments

Hàm SUMPRODUCT trong Google Sheets được sử dụng để tính tổng của các tích của các phần tử trong một mảng mà không cần tính toán từng phần tử riêng lẻ rồi cộng chúng lại. Phương pháp này tiết kiệm thời gian và không gian.

Bạn có thể chỉ định hai hoặc nhiều mảng cùng kích thước bên trong hàm này, tạo ra các khả năng mới. Ví dụ, bạn có thể sử dụng hàm này để tính tổng có điều kiện hoặc tính toán sản phẩm. Làm thế nào?

Bạn có thể sử dụng các kiểm tra logic trong một cột để tạo ra giá trị TRUE hoặc FALSE (1 hoặc 0). Nếu kiểm tra logic trả về 0, tích hoặc giá trị kết quả trong hàng cụ thể đó sẽ là 0.

Do đó, bạn có thể áp dụng hàm SUMPRODUCT trong Google Sheets trong các tình huống tương tự như bạn sử dụng SUMIF hoặc SUMIFS.

Cú pháp và đối số

Cú pháp:

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

Đối số:

  • array1: Mảng đầu tiên có giá trị sẽ được nhân với các giá trị tương ứng trong mảng thứ hai.
  • array2: Mảng thứ hai có giá trị sẽ được nhân với các giá trị tương ứng trong mảng đầu tiên.

Đối số thứ hai là tùy chọn. Do đó, nếu bạn chỉ định chỉ mục array1, công thức sẽ đơn giản trả về tổng.

Trước khi đào sâu vào các ví dụ, bạn có thể sao chép bảng mẫu của tôi, chứa tất cả các bộ dữ liệu mẫu và các công thức chi tiết chúng ta sẽ trình bày trong hướng dẫn này.

Sample Sheet

Ví dụ về SUMPRODUCT (Sử dụng cơ bản): Sử dụng hai mảng

Hãy xem xét một tình huống với hai cột: số lượng (array1) và giá (array2). Để tính tổng chi phí, bạn có thể sử dụng hàm SUMPRODUCT:

Dữ liệu mẫu nằm trong khoảng ô A1:C5, trong đó cột A chứa tên sản phẩm, B chứa số lượng và C chứa giá. Ô A1:C1 được dành cho nhãn trường (tiêu đề).

Để tính tổng chi phí, bạn có thể sử dụng công thức SUMPRODUCT sau đây:

=SUMPRODUCT(B2:B5, C2:C5)

SUMPRODUCT Basic Example: Two Arrays

Đây tương đương với:

=B2*C2 + B3*C3 + B4*C4 + B5*C5

LET Use Case:

=LET(quantity, B2:B5, price, C2:C5, SUMPRODUCT(quantity, price))

Trường hợp sử dụng LET hữu ích khi xử lý các công thức SUMPRODUCT phức tạp trong Google Sheets. Trong ví dụ này, chúng tôi sử dụng chức năng này để gán tên có ý nghĩa cho các biểu thức giá trị B2:B5 (được đặt tên “số lượng”) và C2:C5 (được đặt tên “giá”). Những tên có ý nghĩa này sau đó được sử dụng trong phép tính tiếp theo, cải thiện tính rõ ràng và dễ đọc của công thức.

Ví dụ về SUMPRODUCT (Sử dụng cơ bản): Sử dụng ba mảng

Hãy xem xét một tình huống mới, trong đó chúng ta có một cột sản phẩm, cột số lượng, cột giá và cột phần trăm chiết khấu.

Lần này, chúng ta có dữ liệu trong A1:D5, trong đó A1:D1 được dành cho nhãn trường.

Mục tiêu là tính tổng chi phí sau khi tính đến các chiết khấu bằng cách sử dụng hàm SUMPRODUCT trong Google Sheets.

Công thức:

=SUMPRODUCT(B2:B5, C2:C5, 1-(D2:D5))

SUMPRODUCT Basic Example: Three Arrays

Đây tương đương với:

=(5 * 10 * (1-10%)) + (3 * 15 * (1-20%)) + (2 * 20 * (1-5%)) + (4 * 8 * (1-15%))

Nếu bạn tự hỏi tại sao phải trừ 1 từ tỷ lệ chiết khấu, tức là 1 – D2:D5, thì đó là để tạo ra các trọng số đại diện cho phần bù của tỷ lệ chiết khấu.

Ví dụ, nếu được chiết khấu 10%, việc trừ nó từ 1 sẽ cho kết quả là 90%, đại diện cho phần của giá trị ban đầu được giữ lại sau khi áp dụng chiết khấu.

LET Use Case:

=LET(quantity, B2:B5, price, C2:C5, discount, D2:D5, SUMPRODUCT(quantity, price, 1-discount))

Trường hợp sử dụng LET gán tên có ý nghĩa cho các biểu thức giá trị, cải thiện tính rõ ràng và dễ đọc của công thức SUMPRODUCT.

Lưu ý: SUMPRODUCT là một hàm mảng trong Google Sheets. Do đó, bạn không cần chỉ định rõ ràng hàm ArrayFormula khi sử dụng các biểu thức giá trị không phải là mảng, chẳng hạn như 1 – D2:D5, trong hàm này.

Sử dụng phức tạp của hàm SUMPRODUCT trong Google Sheets

Hãy xem lại bảng trên với một cột mới, E2:E5, chỉ ra “Có” hoặc “Không”. Nếu là “Có”, chiết khấu được chỉ định sẽ được áp dụng; nếu không, nó sẽ không được áp dụng.

Bây giờ, hãy giải quyết tình huống phức tạp này bằng cách sử dụng hàm SUMPRODUCT trong Google Sheets:

=SUMPRODUCT(B2:B5, C2:C5, IF(E2:E5="Có", (1 - D2:D5), 1))

SUMPRODUCT Function Complex Use Case Examples in Google Sheets

Trong đó:

  • array1: B2:B5
  • array2: C2:C5
  • array3: IF(E2:E5=”Có”, (1 – D2:D5), 1)

Array3 đưa ra sự phức tạp cho công thức SUMPRODUCT, kết hợp một kiểm tra logic IF. Công thức gán trọng số 1 nếu không áp dụng chiết khấu; nếu không, nó sử dụng (1 – D2:D5), đại diện cho phần bù của tỷ lệ chiết khấu.

LET Use Case:

=LET(quantity, B2:B5, price, C2:C5, discount, D2:D5, condition, E2:E5, SUMPRODUCT(quantity, price, IF(condition="Yes", 1-discount, 1)))

Hàm SUMPRODUCT cho Tổng có Điều kiện trong Google Sheets

Như đã đề cập trước đó, chỉ định chỉ mục array1 trong công thức SUMPRODUCT sẽ tổng hợp phạm vi cột mà mảng đại diện. Chúng ta cũng đã khám phá một ví dụ về việc sử dụng logic trong hàm SUMPRODUCT ở ví dụ trên.

Bây giờ, hãy tận dụng tính năng SUMPRODUCT cho các tổng có điều kiện với nhiều tiêu chí trong Google Sheets.

Xem xét bảng sau đây đại diện cho các chỉ tiêu doanh số bán cho các nhân viên bán hàng khác nhau:
Complex Conditional SUM using SUMPRODUCT in Google Sheets

Hãy xem cách sử dụng SUMPRODUCT cho các tổng có điều kiện trong toàn bộ đa dạng của nó trong Google Sheets.

Chúng ta muốn tính chỉ tiêu bán hàng của “Philip” từ các khu vực “East” và “West”.

Công thức:

=SUMPRODUCT((A2:A6="Philip")*((B2:B6="East")+(B2:B6="West")), C2:C6)

Trong đó:

  • array1: (A2:A6=”Philip”)*((B2:B6=”East”)+(B2:B6=”West”)).
  • Điều này có hai phần:
    • Phần 1: (A2:A6=”Philip”) trả về TRUE ở bất kỳ đâu tên phù hợp.
    • Phần 2: (B2:B6=”East”)+(B2:B6=”West”) – Phép cộng này của hai kiểm tra logic cho kết quả là 1 (TRUE) ở bất kỳ đâu một trong hai tiêu chí phù hợp, ngược lại là 0 (FALSE).
  • Tích của Phần 1 và Phần 2 trả về 1 cho việc phù hợp với tất cả các tiêu chí, ngược lại trả về 0. Đây là mảng1 trong SUMPRODUCT.
  • array2: C2:C6

LET Use Case:

=LET(salesperson, A2:A6, area, B2:B6, target, C2:C6, SUMPRODUCT((salesperson="Philip")*((area="East")+(area="West")), target))

Tài liệu tham khảo

Hướng dẫn này t covers cả cách sử dụng cơ bản và phức tạp của hàm SUMPRODUCT trong Google Sheets. Ngoài ra, chúng tôi đã khám phá ứng dụng của nó trong các tình huống tổng có điều kiện phức tạp. Dưới đây là một số hướng dẫn liên quan khác.

  1. Cách sử dụng Date Difference làm tiêu chí trong SUMPRODUCT trong Google Sheets
  2. Sự khác biệt giữa SUMIFS và SUMPRODUCT trong Google Sheets
  3. So sánh Sumifs, Sumproduct và Dsum với ví dụ trong Google Sheets
  4. Đồ thị để tìm hiểu Tiêu chí Văn bản, Ngày, Số trong Hàm Sumproduct trong Google Sheets
  5. Cách thực hiện một Sumproduct phân biệt chữ hoa chữ thường trong Google Sheets
  6. Cách sử dụng OR Condition trong SUMPRODUCT trong Google Sheets
  7. Cách sử dụng Wildcards trong Sumproduct trong Google Sheets
  8. Cách sử dụng Sumproduct với ô ghép trong Google Sheets

GiGiKing

Related posts