Microsoft Excel (Bài 56): Hàm SumProduct trong Excel

Hàm SUMPRODUCT là hàm có rất nhiều tính năng. Do khả năng đặc biệt có thể xử lý mảng một cách tinh tế và thông minh, nên hàm SUMPRODUCT rất hữu ích, nếu không bắt buộc, khi nói đến việc so sánh dữ liệu ở hai hay nhiều dải ô và việc tính toán dữ liệu có nhiều điều kiện. Những ví dụ dưới đây sẽ cho bạn thấy toàn bộ khả năng của hàm SUMPRODUCT và sự hữu ích của nó sẽ sớm trở nên rõ ràng thôi.

HÀM SUMPRODUCT TRONG EXCEL – CÚ PHÁP VÀ CÔNG DỤNG:

Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel nhân các con số trong mảng xác định, rồi trả về tổng của các tích số đó.

Cú pháp của hàm SUMPRODUCT rất đơn giản:

SUMPRODUCT(array1, [array2], [array3], …)

Trong đó, mảng 1, mảng 2, … là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.

Số mảng tối thiểu là 1. Trong trường hợp này, hàm SUMPRODUCT chỉ đơn giản tính tổng các thành phần mảng rồi trả về tổng số.

Số mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010, và Excel 2007, và 30 trong các phiên bản trước của Excel.

Mặc dù hàm SUMPRODUCT xử lý mảng, nhưng nó không yêu cầu phím tắt mảng (Ctrl + Shift + Enter). Bạn hoàn thành một công thức SUMPRODUCT theo cách thông thường bẳng cách nhấn phím Enter.

Lưu ý:

Tất cả mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột , nếu không thì bạn sẽ nhận lỗi #VALUE!.

Nếu bất cứ câu lệnh mảng nào chứa giá trị phi số, chúng sẽ được xử lý như số 0.

Nếu mảng là phép thử lô gic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, bạn cần phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng toán tử đơn phân (–). Hãy xem qua ví dụ hàm SUMPRODUCT có nhiều điều kiện để nắm rõ hơn.

Hàm SUMPRODUCT không hỗ trợ ký tự đại diện.

CÁCH DÙNG CƠ BẢN CỦA HÀM SUMPRODUCT TRONG EXCEL:

Để có cái nhìn tổng quát về cách hàm SUMPRODUCT hoạt động, hãy xem qua ví dụ sau.

Giả sử bạn có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn có thể nhận được kết quả chỉ với một công thức SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Ảnh chụp màn hình dưới đây cho thấy công thức trên thực tế:

Đây là chi tiết những gì đang diễn ra về mặt toán học:

Công thức chọn số đầu tiên trong mảng đầu tiên rồi nhân nó cho số đầu tiên trong mảng thứ hai, rồi lấy số thứ hai trong mảng đầu tiên nhân cho số thứ hai trong mảng thứ hai rồi cứ tiếp tục như thế.

Khi đã nhân tất các thành phần mảng, công thức sẽ tính tổng các tích số rồi trả về tổng số.

Nói cách khác, công thức SUMPRODUCT biểu diễn công thức toán học sau đây:

=A2*B2 + A3*B3 + A4*B4

Hãy nghĩ đến việc bạn có thể tiết kiệm bao nhiều thời gian nếu bảng của bạn không chứa 3 hàng dữ liệu, mà là 3 trăm hay 3 ngàn hàng!

CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:

Nhân hai hay nhiều dải ô với nhau rồi cộng các tích số là cách dùng đơn giản và rõ ràng nhất của hàm SUBTOTAL trong Excel, nhưng đây không phải là cách dùng duy nhất. Vẻ đẹp thực sự của hàm SUMPRODUCT trong Excel đó là nó có thể làm nhiều hơn các tính năng đã được nêu rõ. Sâu hơn trong bài hướng dẫn này, bạn sẽ thấy vài công thức cho thấy những cách dùng thú vị và nâng cao hơn nhiều, vì thế hãy tiếp tục đọc bài viết nhé!

HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN

Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để hoàn thành công việc. Nhưng khi nói đến việc so sánh hai hay nhiều mảng, đặc biệt là có nhiều điều kiện, nếu không nói là duy nhất, hàm SUMPRODUCT là hàm hiệu quả nhất. À thì, hàm SUMPRODUCT hay công thức mảng đều được.

Giả sử bạn có danh sách các món hàng ở cột A, doanh số bán dự kiến ở cột B, và doanh số bán thực ở cột C. Mục tiêu của bạn là tìm xem có bao nhiêu có bao nhiêu món bán ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng một trong những biến phân của công thức SUMPRODUCT:

=SUMPRODUCT(–(C2:C10<B2:B10))

hay

=SUMPRODUCT((C2:C10<B2:B10)*1)

Trong đó C2:C10 là doanh số thực và B2:B10 là doanh số dự kiến.

Nhưng nếu bạn có nhiều hơn một điều kiện thì sao? Giả sử bạn muốn đếm số lần Táo bán ít hơn dự kiến. Giải pháp đó là thêm một điều kiện nữa vào công thức SUMPRODUCT:

=SUMPRODUCT(–(C2:C10<B2:B10), –(A2:A10=”táo”))

Hay, bạn có thể sử dụng cú pháp sau:

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))

Và bây giờ, hãy dành ít phút để hiểu rõ công thức trên đang xử lý điều gì. Tôi tin rằng nó đáng vài phút suy ngẫm đấy bởi vì nhiều công thức SUMPRODUCT khác có quy luật tương tự.

CÁCH CÔNG THỨC SUMPRODUCT CÓ MỘT ĐIỀU KIỆN HOẠT ĐỘNG:

Đối với người mới bắt đầu, hãy chia nhỏ công thức đơn giản hơn – công thức so sánh các con số trong hai cột hàng theo hàng, và nói cho chúng ta biết số lần cột C ít hơn cột B:

=SUMPRODUCT(–(C2:C10<B2:B10))

Nếu bạn chọn phần (C2:C10<B2:B10) trong thanh công thức, và nhấn F9 để xem giá trị ẩn, thì bạn sẽ thấy mảng sau:

Cái chúng ta có ở đây đó là giá trị hàm BOOLEAN TRUE và FALSE, trong đó, TRUE có nghĩa là đã đáp ứng điều kiện xác định (ví dụ, một giá trị ở cột B ít hơn một giá trị ở cột C trong cùng một hàng), và FALSE cho thấy điều kiện vẫn chưa được đáp ứng.

Hai dấu trừ (–), về mặt kỹ thuật được gọi là hai toán tử đơn phân, nó ép buộc TRUE và FALSE thành 1 và 0: {0;1;0;0;1;0;1;0;0}.

Cách khác để đổi giá trị lôgic thành giá trị số đó là nhân mảng với 1:

=SUMPRODUCT((C2:C10<B2:B10)*1)

Cách nào cũng được, vì chỉ có một mảng trong công thức SUMPRODUCT, nên nó chỉ nhân 1 trong mảng cho kết quả và chúng ta nhận được kết quả đếm mong muốn. Rất dễ, đúng không nào?

CÁCH CÔNG THỨC SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN HOẠT ĐỘNG:

Khi công thức SUMPRODUCT trong Excel chứa hai hay nhiều mảng, nó nhân các thành phần của mảng, rồi cộng các tích số.

Có lẽ bạn vẫn còn nhớ, chúng ta đã sử dụng công thức dưới đây để đếm số lần doanh số thực (cột C) ít hơn doanh số dự kiến (cột B) đối với Táo (cột A):

=SUMPRODUCT(–(C2:C10<B2:B10), –(A2:A10=”táo”))

hay

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))

Điểm khác biệt duy nhất về mặt kỹ thuật giữa hai công thức đó là phương pháp ép buộc TRUE và FALSE thành 1 và 0 – bằng cách sử dụng hai toán tử đơn phân hay phép nhân. Kết quả là, chúng ta nhận được hai mảng 1 và 0:

Phép nhân thực hiện bởi hàm SUMPRODUCT kết hợp chúng thành một mảng riêng. Và vì nhân 0 thì bằng 0, nên 1 chỉ xuất hiện khi cả hai điều kiện được đáp ứng, và do đó chỉ những hàng đó mới được đếm:

ĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨN

Trong Excel 2003 và các phiên bản trước thì không có hàm IFS, một trong những cách dùng thông dụng nhất của hàm SUMPRODUCT đó là tính tổng hay đếm có điều kiện các ô với nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft giới thiệu một chuỗi hàm được đặc biệt thiết kế cho những nhiệm vụ này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.

Nhưng thậm chí ở các phiên bản hiện đại của Excel, công thức SUMPRODUCT hoàn toàn có thể là một lựa chọn thay thế, ví dụ, tính tổng và đếm có điều kiện các ô có hàm lôgic OR. Dưới đây, bạn sẽ thấy vài ví dụ công thức cho thấy khả năng này.

CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC AND:

Giả sử bạn có chuỗi dữ liệu dưới đây, trong đó cột A liệt kê vùng miền, cột B – món hàng và cột C – doanh số bán ra:

Điều bạn muốn thực hiện đó là đếm, tính tổng và tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc.

Trong các phiên bản Excel gần đây: Excel 2016, 2013, 2010 và 2007, bạn có thể dễ dàng hoàn thành nhiệm vụ bằng cách sử dụng công thức SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn đang tìm một hướng đi khó khăn hơn, hay nếu bạn vẫn sử dụng Excel 2003 hay phiên bản cũ hơn, thì bạn vẫn đạt được kết quả mong muốn với hàm SUMPRODUCT.

Để đếm doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”))

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”))

Để tính tổng doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”), C2:C12)

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”)*C2:C12)

Để tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc:

Để tính trung bình cộng, chúng ta chỉ chia tổng cho phép đếm như thế này:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”), C2:C12) / SUMPRODUCT( –(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”))

Để công thức SUMPRODUCT linh hoạt hơn, bạn có thể định rõ khu vực và món hàng mong muốn ở các ô riêng, rồi tham chiếu các ô đó vào công thức giống như ảnh chụp màn hình dưới đây:

CÔNG THỨC SUMPRODUCT DÙNG CHO VIỆC TÍNH TỔNG CÓ ĐIỀU KIỆN HOẠT ĐỘNG NHƯ THẾ NÀO

Từ ví dụ trước, bạn đã biết cách công thức SUMPRODUCT trong Excel đếm số ô cùng với hiều điều kiện. Nếu bạn đã hiểu rõ cách đó, sẽ rất dễ dàng để bạn nắm rõ quy luật tính tổng.

Để tôi nhắc bạn nhớ rằng chúng ta đã dùng công thức dưới đây để tính tổng doanh số Táo ở khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”), C2:C12)

Công thức trực tiếp của công thức trên chính là 3 mảng dưới đây:

Trong mảng đầu tiên, 1 đại diện cho khu vực phía Bắc, và 0 đại diện cho các khu vực khác.

Trong mảng thứ hai, 1 đại diện cho Táo, và 0 đại diện cho các món hàng khác.

Mảng thứ ba chứa chính các con số xuất hiện từ ô C2 đến C12.

Hãy nhớ rằng nhân 0 thì luôn bằng 0, và nhân 1 thì bằng chính nó, chúng ta có mảng cuối cùng chứa doanh số và những con số 0 – doanh số chỉ xuất hiện nếu hai mảng đầu tiên có số 1 ở cùng một vị trí, cụ thể là cả hai điều kiện xác định đều được đáp ứng; còn không thì sẽ hiển thị số 0:

Cộng các con số trong mảng trên cho kết quả mong muốn – doanh số Táo bán ra ở khu vực phía Bắc.

VÍ DỤ 2. CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC OR

Để công hay đếm có điều kiện các ô sử dụng hàm lôgic OR, hãy sử dụng dấu cộng (+) giữa các mảng.

Trong công thức Excel SUMPRODUCT, cũng như trong công thức mảng, dấu cộng đóng vai trò làm toán tử OR hướng dẫn Excel trả về giá trị TRUE nếu bất kỳ điều kiện nào trong biểu thức xác định được đáp ứng.

Ví dụ, để đếm tổng doanh số Táo và Chanh không phân biệt vùng miền, hãy sử dụng công thức này:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”))

Nói đơn giản, công thức trên có nghĩa là: Hãy đếm số ô nếu dải ô B2:B12=”táo” OR B2:B12=”chanh”.

Để tính tổng doanh số Táo và Chanh, hãy thêm một câu lệnh chứa dải ô Doanh số:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)

Ảnh chụp màn hình dưới đây diễn giải công thức tương tự:

VÍ DỤ 3. CÔNG THỨC SUMPRODUCT CÙNG VỚI HÀM LÔGIC AND VÀ HÀM LÔGIC OR

Trong nhiều trường hợp, có thể bạn cần phải đếm hay tính tổng có điều kiện sử dụng cả hàm lôgic AND và OR. Thậm chí ở phiên bản Excel mới nhất , chuỗi hàm liên quan đến hàm IF cũng không thể làm được điều đó.

Một trong những giải pháp khả thi đó là kết hợp hai hay nhiều hàm SUMIFS+SUMIFS hay COUNTIFS+COUNTIFS.

Cách khác đó là sử dụng hàm SUMPRODUCT trong đó:

Dấu hoa thị (*) được dùng như toán tử AND.

Dấu cộng (+) được dùng như toán tử OR.

Để khiến mọi thứ đơn giản hơn, hãy xem xét các ví dụ dưới đây:

Để đếm số lần Táo và Chanh được bán ở khu vực phía Bắc, hãy lập công thức với hàm lôgic sau:

=Count If ((Vùng miền=”miền Bắc”) AND ((Món hàng=”Táo”) OR (Món hàng=”Chanh”)))

Khi vừa sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ có dạng như sau:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”)))

Để tính tổng doanh số Táo và Chanh bán ra ở khu vục phía Bắc, hãy dùng công thức trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”))*C2:C12)

Để công thức trông gọn hơn, bạn có thể gõ các biến vào các ô riêng biệt – Khu vực ở ô F1 và Món hàng ở ô F2 và H2 – rồi tham chiếu các ô này vào công thức:

CÔNG THỨC SUMPRODUCT DÙNG ĐỂ TÍNH TRUNG BÌNH CÓ TRỌNG LƯỢNG

Ở một trong những ví dụ trước, chúng ta đã bàn về công thức SUMPRODUCT dùng để tính trung bình có điều kiện. Cách dùng phổ biến khác của hàm SUMPRODUCT trong Excel đó là tính trung bình có trọng lượng trong đó mỗi giá trị có một trọng lượng nhất định.

Công thức SUMPRODUCT tính trung bình có trọng lượng tổng quát có dạng như sau:

SUMPRODUCT(values, weights) / SUM(weights)

Giả sử các giá trị nằm ở dải ô B2:B7 và trọng lượng ở dải ô C2:C7, công thức SUMPRODUCT tính trung bình có trọng lượng có dạng như sau:

=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)

Tôi tin rằng ngay bây giờ, bạn sẽ không gặp khó khăn trong việc hiểu quy luật công thức nữa.

HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM THAY THẾ CHO CÔNG THỨC MẢNG

Ngay cả khi bạn đọc bài viết này chỉ để có thêm thông tin và bạn không còn nhớ rõ các chi tiết nữa, hãy nhớ một điểm mấu chốt quan trọng – hàm SUMPRODUCT trong Excel xử lý mảng. Và vì hàm SUMPRODUCT có các khả năng của công thức mảng, nên nó có thể trở thành hàm thay thế dễ sử dụng cho công thức mảng.

Ưu điểm là gì? Về mặt cơ bản, bạn có khả năng quản lý công thức một cách dễ dàng mà không cần phải nhấp Ctrl + Shift + Enter mỗi lần bạn nhập một công thức mảng mới hay chỉnh sửa công thức mảng.

Ví dụ, chúng ta có thể sử dụng một công thức mảng đơn giản để đếm số ký tự trong dải ô xác định:

{=SUM(LEN(range))}

và biến nó thành công thức thông thường:

=SUMPRODUCT(LEN(range))

Để thực hành, bạn có thể chọn các công thức mảng này trong Excel rồi cố gắng viết lại sử dụng hàm SUMPRODUCT.

LỒNG GHÉP HÀM KHÁC TRONG HÀM SUMPRODUCT ĐỂ THAY ĐỔI GIÁ TRỊ CỦA MẢNG CẦN TÍNH

Trong ví dụ trên, để có thể tính tổng tiền của những mặt hàng bán được trong tháng 3, nhưng dữ liệu chỉ có cột Ngày bán, không có sẵn cột Tháng. Để tính được, chúng ta phải tách được Tháng trong cột Ngày.

Ở cách tính thông thường, chúng ta phải làm như sau:

• Dùng hàm IF để xét xem tháng của từng ngày bán có phải là 3 không. Nếu đúng thì sẽ lấy số lượng nhân đơn giá, còn không thì trả kết quả là số 0
• Sau đó dùng hàm SUM để cộng tổng lại các kết quả thu được từ hàm IF trên từng dòng

Cách làm như vậy sẽ tốn nhiều công thức và thời gian phải không? Nếu dùng hàm SUMPRODUCT chúng ta chỉ cần 1 hàm là ra ngay kết quả, trong đó xét 3 mảng:

• Mảng Ngày bán: B2:B5 sẽ kết hợp hàm MONTH cho mảng này để có thể đưa về giá trị số tháng, từ đó so sánh với số 3
• Mảng Số lượng: C2:C5
• Mảng Đơn giá: D2:D5
• Nhân 3 mảng này lại với nhau, ta thu được kết quả

HÀM SUMPRODUCT TÍNH TOÁN CHO BẢNG DỮ LIỆU 2 CHIỀU

Trong bảng tính trên, ta có yêu cầu xác định đơn giá của 1 mặt hàng gồm 2 điều kiện: Mã hàng và Mã công ty.

Bảng chứa thông tin cần tìm (Bảng đơn giá) là bảng 2 chiều:

• Chiều dọc là thông tin mã Công ty
• Chiều ngang là thông tin mã hàng
• Giao điểm của các dòng, các cột trong bảng này chính là đơn giá cần tìm của sản phẩm.

Khi đó chúng ta sử dụng hàm SUMPRODUCT như sau:

G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)

• Xét mảng Mã công ty (B14:B17) có chứa mã công ty tại ô C3 không
• Xét mảng Mã hàng (C13:F13) có chứa mã hàng tại ô B3 không
• Nếu chứa kết quả đúng ở cả 2 mảng trên, thì sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột chứa các mã đó.