Cách tính tổng trong excel với hàm SUMPRODUCT nhiều điều kiện

Trong công việc tin học văn phòng, để tổng hợp và phân tích số liệu, chúng ta không thể không sử dụng tới những công cụ để thực hiện cách tính tổng trong Excel. Một trong số những công cụ quyền lực nhất là SUMPRODUCT.

Khi bạn nghe tới cái tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ như chỉ là một công thức vô ích – công thức cung cấp cách tính tổng trong Excel thông thường của các tích số. Nhưng định nghĩa này thực tế không cho thấy thậm chí là chỉ một phần nhỏ tính năng hữu ích của hàm SUMPRODUCT. Trong bài hướng dẫn dưới đây, 123job.vn sẽ giải thích cách sử dụng cơ bản và nâng cao của hàm SUMPRODUCT thế nào trong Excel. Bạn sẽ thấy một vài ví dụ cụ thể về công thức dùng để so sánh mảng, tính tổng có điều kiện, cùng với tính năng đếm số ô có nhiều điều kiện, và hơn thế nữa.   

I. Sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện hiệu quả trong Excel Link 

1. Sơ lược về hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT cung cấp cách tính tổng trong Excel là hàm tính tích lần lượt các phạm vi hoặc mảng với nhau và trả về tổng số sản phẩm. Điều này về cơ bản nghe có vẻ khá nhàm chán, nhưng hàm tính tổng Excel này có những chức năng cực kỳ linh hoạt và tất nhiên có thể được sử dụng để đếm và tính tổng linh hoạt hơn. Trong thực tế, hàm SUMPRODUCT cung cấp cách tính tổng trong Excel được dùng khá nhiều và cực kỳ hữu ích. 

2. Công thức tính của hàm SUMPRODUCT

Về mặt kỹ thuật, cách tính tổng trong Excel bằng hàm này sẽ là quá trình thực hiện nhân các số trong các mảng đã chỉ định và trả về tổng của các sản phẩm đó.

Cú pháp của hàm SUMPRODUCT tính tổng trong Excel rất đơn giản và dễ hiểu:

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

Trong đó:

• array1: là mảng đầu tiên bắt buộc phải có trong cách tính tổng trong Excel nếu muốn nhân các thành phần của nó rồi cộng tổng lại.
• [array2], [array3] (Mảng 1, mảng 2, v.v.) là các phạm vi ô tính tổng Excel hoặc mảng liên tục tiếp theo có các phần tử mà bạn muốn nhân, sau đó cộng tổng vào.
• Số lượng mảng tối thiểu với cách tính tổng trong Excel là 1. Trong trường hợp này, công thức hàm SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử tương ứng trong mảng và trả về tổng.
• Số lượng mảng tính tổng Excel tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và chỉ có 30 trong các phiên bản Excel trước đó.
• Mặc dù cách tính tổng trong Excel với hàm SUMPRODUCT hoạt động với các mảng, nhưng thực tế nó lại không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter). Bạn chỉ cần sử dụng một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn vào phím Enter.

Lưu ý:

• Tất cả các mảng tính tổng Excel trong công thức hàm SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ lập tức nhận được #VALUE! lỗi.
• Nếu bất kỳ đối số mảng nào tương ứng chứa các giá trị không phải là số, chúng sẽ được coi là số không đối với cách tính tổng trong Excel.
• Nếu một mảng tính tổng Excel là một bài kiểm tra logic, nó sẽ dẫn đến các giá trị TRUE và FALSE. Trong hầu hết các trường hợp cách tính tổng trong Excel, bạn cần chuyển đổi giá trị của chúng thành 1 và 0 bằng cách sử dụng ký tự toán tử đơn nguyên kép (-). 
• Tổng hợp theo cách tính tổng trong Excel không hỗ trợ các ký tự đại diện.

3. Ví dụ cách sử dụng hàm SUMPRODUCT

Giả sử bạn có số lượng được cho trong các ô A2: A4, giá trong các ô B2: B4 và bạn muốn tìm hiểu tổng số bằng cách tính tổng trong Excel. Nếu bạn đang làm bài kiểm tra toán ở trường, bạn sẽ thực hiện tính tích số lượng với giá cho mỗi mục, sau đó trực tiếp cộng các tổng phụ. Tuy nhiên thì trong Microsoft Excel, bạn có thể nhận kết quả chính xác chỉ bằng một công thức hàm SUMPRODUCT duy nhất:

=SUMPRODUCT(A2:A4,B2:B4)

Ví dụ cách sử dụng hàm SUMPRODUCT

4. Cách tính tổng trong Excel sử dụng hàm SUMPRODUCT

Trường hợp 1: Sử dụng hàm trong Excel với một tiêu chí duy nhất

Ví dụ: Tập dữ liệu được cho sẵn dưới đây chứa 6 tên trong các hàng và 12 tháng trong các cột. Hãy tưởng tượng rằng sếp của bạn đang yêu cầu bạn cộng tất cả các số cho tháng Sáu. Bắt đầu công thức cách tính tổn trong Excel của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) và thực hiện nhân nó với mảng chứa các dữ liệu tháng bằng 6. Các bước thực hiện cách tính tổng trong Excel:

Bạn nhập vào công thức: 

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

Trường hợp 1

Trong đó:

• Mảng 1 của công thức cách tính tổn trong Excel cộng tất cả các số trong lựa chọn C9: N14. 
• Array2 là trường kiểm tra từng số tháng xem nó có bằng 6. Kết quả của hàm này sẽ trả về là TRUE (1) hoặc FALSE (0). Bạn có thể thấy kết quả tương ứng của hàng này trong hàng 16. 
• Chỉ với cột H trong trang tính cho kết quả là TRUE, vì vậy kết quả là 1. 
• Để nhân các trường Array1 với Array2 có nghĩa là nhân các số thực với 0 hoặc 1. 
• Trong ví dụ này, công thức Excel của chúng ta đã nhân tất cả các số trong cột C bằng 0 và tiếp đến tất cả các số từ cột H bằng 1. 
• Tất cả các giá trị theo đó được nhân với 0 cộng với không. 
• Các số duy nhất còn lại theo cách tính tổng trong Excel được nhân với 1, trong trường hợp này là tháng 6.

Trường hợp 2: Sử dụng cách tính tổng trong Excel bằng hàm SUMPRODUCT làm tiêu chí cho các cột

Hãy tiếp tục ví dụ trên bằng cách thực hiện thêm các tiêu chí khác. Sếp của bạn bây giờ yêu cầu phải biết doanh số bán hàng trong quý 2 là bao nhiêu, vì vậy chúng ta cần dùng các dữ liệu từ tháng thứ 4 trở lên và bao gồm cả tháng 6.

Trường hợp 2

Mô tả:

Công thức tính tổng Excel chúng ta sử dụng là:

=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8

Có 3 tháng dữ liệu thuộc về Q2. Lưu ý rằng bạn không cần ba phần đối số để chỉ định mỗi tháng. Thay vào đó, bạn có thể sử dụng duy nhất một công thức để thể hiện trong đó các tháng lớn hơn hoặc bằng 4 (> = 4) và nhỏ hơn hoặc bằng 6 (

Thường thì bạn sẽ muốn làm cho công thức này là linh hoạt và bao quát nhất có thể. Không phải ai cũng cảm thấy thoải mái khi thay đổi công thức phức tạp. Tuy nhiên, hầu hết chúng ta đều hoàn toàn ổn với việc điều chỉnh một số duy nhất trong đối số. Thay vì mã hóa cố định các tháng để thêm, bạn có thể tham khảo các ô chỉ định tương ứng của các giá trị này. Các ô này thường được gọi chung với cái tên là tham số.

Công thức tính tổng Excel

Để cách tính tổng trong Excel đạt được điều đó, hãy thêm tháng giới hạn là giá trị thấp hơn trong ô J4 và giới hạn cao hơn trong ô J5. Bây giờ, bạn có thể thay đổi công thức để phản ánh điều này. Thay vì tham khảo số 4, hãy viết vào công thức là ô J4. Thay số 6 tương ứng bằng ô J5.

Bạn sẽ được công thức cách tính tổng trong Excel cuối cùng sẽ là:

=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8

II. Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện  Link 

1. Nguyên tắc đếm của hàm SUMPRODUCT

Cách tính tổng trong Excel bằng hàm SUMPRODUCT là một hàm xử lý dữ liệu dạng mảng. Cấu trúc của hàm tính tổng Excel SUMPRODUCT đơn giản chỉ là các mảng:

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

Nếu chúng ta chỉ nhìn vào cấu trúc trên thì bạn có hình dung được thực sự hàm SUMPRODUCT có sức mạnh thế nào không? Sức mạnh của hàm này nằm ở chữ “”Mảng-Array”, bởi vì:

• Phạm vi của mảng Excel này rất linh hoạt, có thể bao gồm nhiều vùng dữ liệu tương ứng được kết nối lại hoặc một phần cụ thể trong mỗi phần tử của 1 vùng dữ liệu. Việc tính toán bằng cách tính tổng trong Excel theo mảng gần như không gặp giới hạn gì
• Bản chất của việc đếm này là mỗi phần tử trong 1 vùng nếu được thỏa mãn điều kiện thì được tính là 1. Kết quả cần đếm chính là tổng của các giá trị thỏa mãn điều kiện được cho đó. Như vậy, trong việc đếm theo điều kiện cho trước có thể hiểu gần giống tính tổng các giá trị thỏa mãn điều kiện trong Excel, chỉ khác là mỗi giá trị được cho là thỏa mãn này không phụ thuộc 1 giá trị nào mà chỉ có giá trị là 1.

2. Ứng dụng hàm SUMPRODUCT thực hiện đếm theo nhiều điều kiện 

Đếm chính xác số lần bán của ca 1 trong tháng 6 với mặt hàng Táo

Trong yêu cầu này, chúng ta thấy bao hàm tới 3 điều kiện: Ca1, Tháng 6 và Mặt hàng “Táo”

Khi tăng thêm điều kiện cần đáp ứng cho đề bài thì chúng ta chỉ cần tăng thêm các thành phần trong hàm SUMPRODUCT như sau:

=SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18=”Táo”)*1)

Kết quả:

Kết quả

Như vậy việc thêm điều kiện đối với cách tính tổn trong Excel thực ra chỉ làm hàm SUMPRODUCT dài hơn, còn ý nghĩa và độ khó thực sự không hề tăng lên.

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

1. Lồng ghép hàm khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng cần tính

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 Excel dữ liệu tiền của những mặt hàng bán được trong tháng 3, nhưng dữ liệu tương ứng chỉ có cột Ngày bán, không có sẵn cột Tháng. Để có thể tính được, chúng ta phải tách được phần dữ liệu Tháng trong cột Ngày.

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

• Dùng hàm IF trong Excel để xét xem tháng của từng ngày bán trong đây có phải là 3 không. Nếu đúng thì sẽ lấy số lượng của hàng hóa nhân đơn giá, còn không thì hàm sẽ trả kết quả là số 0
• Sau đó dùng hàm SUM trong Excel để cộng tổng lại từng 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ẽ khiến bạn tốn nhiều công thức và thời gian phải không? Nếu dùng cách tính tổng trong Excel với hàm SUMPRODUCT trong Excel thì 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 trong Excel cho mảng này để có thể đưa về giá trị số tháng, từ đó hàm sẽ so sánh với số 3
• Mảng dữ liệu Số lượng: C2:C5
• Mảng dữ liệu Đơn giá: D2:D5
• Nhân 3 mảng này lại với nhau, ta sẽ lập tức thu được kết quả

2. Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều

Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều

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

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

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

Khi đó chúng ta sử dụng cách tính tổng trong Excel cho hàm SUMPRODUCT với công thức 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ương ứng tại ô C3 không
• Xét mảng Mã hàng (C13:F13) có chứa mã hàng cần làm đối chiếu tính tổng Excel tại ô B3 không
• Nếu chứa kết quả đúng ở cả 2 mảng trên, thì hàm Excel trên sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột cụ thể chứa các mã đó.

IV. So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo điều kiện

1. Dùng hàm COUNTIFS  

Ưu điểm:

• Các thành phần trong hàm Excel này được phân biệt bởi dấu phẩy nên rất rõ ràng, dễ sửa, thậm chí là dễ kiểm tra công thức.
• Khi thực hiện tính tổng trong Excel, hoặc các tính toán khác trên dữ liệu lớn và nhiều công thức thì hàm này sẽ tính nhanh hơn so với hàm Sumproduct.

Nhược điểm: Khi phải tính với 1 thành phần cụ thể được cho trong mảng (ví dụ như tính giá trị Tháng ở cột Ngày) thì dữ liệu sẽ không tính trực tiếp được mà phải thông qua giá trị tương ứng của ngày đầu tháng và ngày cuối tháng đó (hoặc có thể phải tạo cột phụ để tách giá trị tháng).

2. Dùng hàm SUMPRODUCT

Ưu điểm:

• Viết dữ liệu nhanh và gọn hơn hơn so với hàm COUNTIFS
• Thực hiện cách tính tổng trong Excel được dễ dàng cho đối tượng mảng, xét 1 phần dữ liệu tương ứng trong mảng

Nhược điểm:

• Phải hiểu rõ được bản chất của dữ liệu tạo ra bởi từng thành phần trong mảng thì cách tính tổng trong Excel mới ra kết quả đúng 
• Tính toán cách tính tổng trong Excel trên dữ liệu lớn và nhiều công thức thì hàm này sẽ chậm hơn so với COUNTIFS
• Nếu trong vùng dữ liệu tính tổng Excel có lỗi bất kỳ tại vị trí nào thì kết quả sẽ ra lỗi, trong khi hàm COUNTIFS khi đó sẽ bỏ qua lỗi và tính vẫn đúng

Dùng hàm SUMPRODUCT

Như vậy chúng ta có thể rút ra kết luận như sau:

• Trong trường hợp không phải làm việc với đối tượng mảng thì chúng ta nên dùng hàm COUNTIFS
• Không nên dùng hàm SUMPRODUCT để tính toán quá nhiều, bởi chỉ 1 lỗi nhỏ trong bảng dữ liệu cho trước cũng có thể khiến công thức báo lỗi. Chỉ nên dùng hàm này trong trường hợp nhỏ, lẻ hoặc khi các yêu cầu cho không thể dùng được hàm COUNTIFS.

V. Kết luận  

Khi đã hiểu rõ phương pháp và cách vận dụng hàm SUMPRODUCT vào cách tính tổng trong Excel theo nhiều điều kiện thì chúng ta sẽ thấy tính ứng dụng của việc này rất cao trong thực tế. Đồng thời, chúng ta đã biết được cách kết hợp hợp lý các hàm khác bên trong hàm SUMPRODUCT, cùng với cách sử dụng hàm SUMPRODUCT trong Excel để tính toán trên những bảng tính 2 chiều. Hy vọng những kiến thức đầy đủ này sẽ giúp ích cho các bạn trong công việc.