Khi làm việc với Excel, thông thường chúng ta luôn có nhiều hơn một điều kiện cần được đáp ứng. Khi cần tính tổng với điều kiện có thể sử dụng hàm SUMIF hoặc SUMIFS. Nhưng khi muốn tính tổng theo điều kiện là nhiều mảng, hay điều kiện là cả dòng và cả cột thì việc sử dụng hàm SUMPRODUCT kết hợp với một số hàm khác để tách như hàm RIGHT, LEFT, MID để tìm ra kết quả lại là một cách mang lại hiệu quả hơn. Vậy khi sử dụng hàm SUMPRODUCT tính tổng với nhiều điều kiện thì sẽ như thế nào, sau đây mình sẽ hướng dẫn cách sử dụng hàm SUMPRODUCT ở mức độ nâng cao hơn, các bạn cùng tham khảo nhé.
Xem Nhanh Mục Lục
Hàm SUMPRODUCT TRONG EXCEL
Trước khi đi vào chủ đề chính của bài viết này, mình sẽ nhắc lại về khái niệm, ý nghĩa của hàm SUMPRODUCT để các bạn tiện cho việc theo dõi và vận dụng.
Khái niệm, công thức hàm SUMPRODUCT
Hàm SUMPRODUCT hiểu đơn giản là sự kết hợp của hai hàm SUM-sử dụng để tính tổng và hàm PRODUCT- sử dụng để tính tích.
Cú pháp: =SUMPROUCT(array1, [array2], [array3],…)
Trong đó:
- array1 : Là tham số bắt buộc, đây là đối số của mảng thứ nhất, ở đây có chứa các thành phần mà bạn muốn nhân rồi cộng tổng lại với nhau.
- [array2], [array3],… Là tham số tùy chọn. Từ mảng thứ 2 trở đi (số mảng tối đa có thể lên tới 255) là các mảng mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
Lưu ý
- Tất cả các mảng trong hàm SUMPRODUCT phải có cùng số hàng, số cột. Nếu không cùng, thì hàm SUMPRODUCT sẽ trả về giá trị lỗi #VALUE!
- Nếu có bất kỳ ô dữ liệu nào trong mảng không ở dạng số,thì hàm SUMPRODUCT sẽ mặc định coi nó là số 0.
- Số mảng tối đa với excel 2003 là 30 mảng, còn lại số mảng tối đa là 255 trong Excel 2007, 2010, 2013, 2016.
- Hàm SUMPRODUCT không hỗ trợ ký tự đại diện( WildCard)
- Nếu mảng là phép thử lô gic, thì hàm sẽ trả về giá trị TRUE hoặc FALSE.
CÁCH SỬ DỤNG HÀM SUMPRODUCT ĐỂ TÍNH TỔNG NHIỀU ĐIỀU KIỆN
Hàm SUMPRODUCT để tính tổng của tích.
Phần này là cách sử dụng cơ bản của hàm SUMPRODUCT. Mình cũng nhắc lại để các bạn nhớ nếu còn chưa rõ cách sử dụng, và tiện theo dõi hơn.
Ví dụ 1:
Như ví dụ minh họa dưới đây, nếu không biết sử dụng hàm SUMPRODUCT thì bạn sẽ phải thêm một cột Doanh thu, để tính doanh thu của từng loại mặt hàng với nhau, sau đó dùng hàm SUM để tính tổng Doanh thu.
Nhưng với hàm SUMPRODUCT bạn hoàn toàn bỏ qua được bước tính doanh thu từng loại mặt hàng.
Chỉ cần áp dụng công thức = SUMPRODUCT(A2:A5,B2:B5). Trong đó A2:A5 và B2:B5 lần lượt là mảng số lượng và đơn giá. Sau đó nhấn enter, chúng ta có kết quả là tổng Doanh thu cần tính.
Hàm SUMPRODUCT tính tổng với nhiều điều kiện
Nội dung này chính là phần nâng cao của hàm SUMPRODUCT mà mình muốn hướng dẫn các bạn. Khi các bạn nắm chắc những kiến thức cơ bản về hàm, cũng như vận dụng vào ví dụ, thì khi gặp những bài toán, yêu cầu cao hơn các bạn cũng sẽ dễ định hướng được cách làm, hay dùng hàm nào thì phù hợp và hiệu quả nhất. Chúng ta cùng tìm hiểu thông qua ví dụ hàm SUMPRODUCT tính tổng với nhiều điều kiện nhé.
Ví dụ 2:
Tính tổng các Sp-1 thuộc miền Nam( bao gồm Đông Nam và Tây Nam) trong bảng dưới đây.
Phân tích đề bài:
Theo yêu cầu của đề bài, chúng ta phải tính tổng Sp-1 với hai điều kiện cần được đáp ứng đó là tên sản phẩm Sp-1 và thuộc miền Nam.
Cách tính:
Khi đã xác định được các điều kiện, chúng ta thực hiện theo các bước sau:
Bước 1: Với điều kiện thứ nhất, điều kiện tên sản phẩm Sp1. Nhìn vào bảng số liệu ta thấy có nhiều mã hàng có chứa Sp1. Vậy chúng ta phải tách lấy Sp1 từ các mã hàng đó bằng cách:
– Sử dụng hàm LEFT để lấy 3 ký tự bên trái “Sp1”, công thức =LEFT(A2:A7,3). Trong đó A2: A7 là mảng tên, 3 là số ký tự cần lấy.
Bước 2: Tương tự đối với mảng các miền, bạn phải tách được 3 ký tự “Nam”.
– Sử dụng hàm RIGHT để lấy 3 ký tự bên phải “Nam”, công thức =RIGHT(B1:E1,3). Trong đó B1: E1 là mảng tên miền, 3 là số ký cần lấy.
Bước 3: Sau khi xử lý được 2 điều kiện của đề bài, thì hàm SUMPRODUCT sẽ tổng hợp dữ liệu. Chúng ta sẽ kết hợp các hàm lại với nhau.
Công thức: = SUMPRODUCT((LEFT(A2:A7,3)=”Sp1″)*RIGHT(B1:E1,3)=”Nam”)*(B2:E7)).
Giải thích công thức
- LEFT(A2:A7,3)=”Sp1″ : 3 ký tự tách từ tên sản phẩm = “Sp1” ( Hay có thể gọi là mảng điều kiện I)
- RIGHT(B1:E1,3)= “Nam”: 3 ký tự miền cần lấy =”Nam” ( Hay có thể gọi là mảng điều kiện II)
- B2:E7: Là vùng số lượng của tất cả các sản phẩm và các miền ( Mảng thứ III)
- Dấu * dùng để kết nối các điều kiện với nhau.
=> Nếu điều kiện 1 và 2 được thỏa mãn, SUMPRODUCT sẽ tính tổng. Sau khi nhấn enter chúng ta có kết quả cần tính.
Ví dụ 3:
Cho bảng số liệu nguyên vật liệu nhập trong tháng 6, tháng 7,tháng 8. Tính số lượng từng loại nhập trong từng tháng.
Phân tích đề bài:
Dựa vào hai điều kiện tháng mua và mã nguyên vật liệu để tìm ra số lượng tương ứng. Sau đó tính tổng số lượng thỏa mãn hai điều kiện này.
Cách tính:
Tương tự như ví dụ 1, xác định được các điều kiện thì ta tiến hành dùng hàm SUMPRODUCT kết hợp với hàm khác để tính. Trong ví dụ này, chúng ta có cột ngày, chúng ta cần tách lấy thông tin tháng bằng cách sử dụng hàm MONTH.
Công thức: Tại ô K6 = SUMPRODUCT(($B$4:$B$13=$I6)*(MONTH($A$4:$A$13)=K$5)*($D$4:$D$13))
Giải thích công thức:
- $B$4:$B$13 là cột mã nguyên vật liệu, ký hiệu $ trước chữ và số để cố định cột ( Điều kiện I)
- =$I6 Sau đó so sánh với mã đã được liệt kê trong bảng tổng hợp, vì I chạy từ I6 đến I8, nên chúng ta đặt ký tự $ trước I để cố định cột I.
- MONTH($A$4:$A$13) : Dùng hàm MONTH để tách lấy tháng trong cột ngày.
- =K$5 Sau đó so sánh tháng tách được với điều kiện là các tháng đã được liệt kê trong bảng tổng hợp. Vì số tháng chạy từ K đến M, do đó chúng ta sẽ thêm ký tự $ trước số để cố định.
- $D$4:$D$13: Là vùng số lượng cần tính tổng nếu đáp ứng đủ 2 điều kiện trên.
Sau đó nhấn enter, kéo công thức sang ngang, và xuống dưới. Chúng ta có kết quả, các bạn có thể kiểm tra lại đáp án bằng cách đối chiếu lại với bảng số liệu bên cạnh để biết kết quả của mình có chính xác không nhé.
Tổng kết
Trên đây mình đã hướng dẫn cách sử dụng hàm SUMPRODUCT để tính tổng với nhiều điều kiện. Khi làm việc với Excel, nắm chắc cách dùng các bạn sẽ linh động trong việc sử dụng kết hợp với các hàm khác để hàm SUMPRODUCT cho kết quả như mong muốn. Hàm SUMPRODUCT còn những tính năng thú vị khác nữa như đếm với một, hay nhiều điều kiện. Các bạn hãy cùng https://updatemoi.com khám phá ở các bài viết tiếp theo nhé.
