Khi làm việc với dữ liệu trong Excel, các hàm tính tổng có điều kiện không chỉ gói gọn trong 2 hàm phổ biến SUMIF, SUMIFS mà Excel còn cung cấp một số các hàm tính tổng khác cho phép bạn thực hiện tính tổng với điều kiện phức tạp hơn. Trong bài viết này, Tinhocmos sẽ tổng hợp các hàm Excel bạn có thể sử dụng để tính tổng có điều kiện từ đơn giản đến phức tạp.
Mục lục
Tổng quan về các hàm tính tổng có điều kiện trong Excel
Một trong những ưu điểm của Excel chính là thực hiện các phép tính theo một hoặc nhiều điều kiện bạn mong muốn cho một vùng dữ liệu lớn vô cùng nhanh chóng. Tuy nhiên, để lựa chọn và thực hiện đúng các hàm đòi hỏi kỹ năng tư duy cũng như sự hiểu biết nhất định của bạn về các hàm và cách kết hợp các hàm. Excel có sẵn các hàm để bạn có thể:
- Tính tổng các giá trị theo một điều kiện hoặc nhiều điều kiện kèm theo với hàm SUMIF, SUMIFS.
- Tính tổng của tích một vài cặp giá trị với hàm SUMPRODUCT.
- Tự động tính tổng các giá trị sau khi lọc/ẩn với hàm SUBTOTAL.
Các hàm tính tổng có điều kiện trong Excel
Hàm SUMIF
Hàm SUMIF trả về giá trị tổng của các ô thỏa mãn một điều kiện cho trước.
Cú pháp
SUMIF(range, criteria, [sum_range])
Mô tả tham số
range: là tham số bắt buộc, là vùng ô mà bạn muốn đánh giá theo điều kiện để tính tổng.
criteria: là tham số bắt buộc, là điều kiện để bạn đánh giá dải ô phía trước.
sum_range: là tham số tùy chọn. Đây là các ô thực tế được đem ra tính tổng nếu các ô tương ứng ở phần range thỏa mãn điều kiện. Nếu bạn không điền sum_range, Excel sẽ tính các ô thỏa mãn trong vùng range.
Ví dụ
Bạn sử dụng hàm SUMIF để tính tổng doanh thu của tất cả các sản phẩm thuộc phân loại Phụ kiện trong danh sách dưới đây. Bạn có công thức =SUMIF(A3:A222,”Phụ kiện”,C3:C222)
Trong đó, A3:A222 là vùng điều kiện; Phụ kiện là điều kiện lọc, vì đây là văn bản nên bạn nhớ đặt vào dấu ngoặc kép và C3:C222 là vùng tính tổng theo điều kiện.
Hàm SUMIF tương đối đơn giản và chỉ có thể tính tổng kèm theo một điều kiện cho trước. Để mở rộng điều kiện, bạn có thể kết hợp với các hàm khác ở tham số criteria.
Hàm SUMIFS
Nếu hàm SUMIF chỉ cho phép bạn tạo một điều kiện cho tổng thì hàm SUMIFS trả về giá trị tổng của các ô thỏa mãn nhiều điều kiện cho trước.
Cú pháp
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Mô tả tham số
Sum_range: là tham số bắt buộc. Là vùng cần tính tổng trong bảng dữ liệu, giá trị trống và giá trị text được bỏ qua.
Criteria_range1: là tham số bắt buộc, là phạm vi cần được kiểm tra bằng điều kiện criteria1.
Criteria1: là tham số bắt buộc, là tiêu chí xác định ô nào trong Criteria_range1 sẽ được tính tổng
Criteria_range2,criteria2,…: Các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/tiêu chí.
Xem chi tiết: Hàm SUMIFS Trong Excel Để Tạo Lập Dashboard: Hướng Dẫn Từ A Đến Z
Một trong những lưu ý quan trọng khi sử dụng hàm SUMIFS đó là bạn phải điền chính xác thứ tự điều kiện cho vùng đối số điều kiện. Excel sẽ chỉ tính tổng các giá trị thỏa mãn toàn bộ các điều kiện được nhập vào mà không phải tổng các giá trị thỏa mãn một trong các điều kiện đó. Đó có thể xem là một ưu điểm cũng có thể là một hạn chế của hàm SUMIFS. Như trong ảnh dưới đây, hàm SUMIFS sẽ tính tổng các giá trị doanh thu thỏa mãn thuộc phân loại Phụ kiện VÀ là Đèn xếp.
Hàm SUMPRODUCT
Hàm SUMPRODUCT trả về tổng của tích các dải ô hoặc mảng tương ứng.
Cú pháp
SUMPRODUCT(array1, [array2], [array3], …)
Mô tả tham số
array1: là tham số bắt buộc. Đối số mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
[array2], [array3],… : là tham số tùy chọn. Các đối số mảng từ 2 đến 255 mà bạn muốn nhân các ô của nó với array1 rồi cộng tổng.
Xem chi tiết: Hàm SUMPRODUCT Trong Excel Tính Tổng Các Tích Cực Hiệu Quả
Lưu ý: Khi bạn muốn sử dụng một mảng đối số như một điều kiện, sử dụng dấu hoa thị (*) cùng với mảng đối số đó. Ví dụ khi bạn nhập *(Table1[Saleperson]=”Anh”) vào hàm SUMPRODUCT, hàm sẽ lọc các giá trị trong cột Saleperson và chọn ra ô chứa “Anh”. Đây cũng là tính năng vượt trội hơn hẳn của hàm SUMPRODUCT khi bạn thậm chí còn có thể so sánh mảng này với mảng khác. Và đặc biết nếu kết hợp với hàm OR, bạn có thể tính tổng của các giá trị thỏa mãn một trong các điều kiện khác nhau.
Hàm SUBTOTAL
Hàm SUBTOTAL là hàm trả về tổng phụ của một chuỗi giá trị. Tổng phụ tức là tổng của một số giá trị thỏa mãn một điều kiện chung nào đó.
Cú pháp
SUBTOTAL(function_num,ref1,[ref2],…)
Mô tả tham số
Function_num: là con số xác định hàm thực hiện phép tính.
Ref1, Ref2, …: là 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ.
Xem chi tiết: Tất Tần Tật Về Hàm SUBTOTAL Trong Excel
Lời kết về các hàm tính tổng có điều kiện trong Excel
Trong thực tế, bạn cần dựa vào dữ liệu và điều kiện thực hiện tính tổng để lựa chọn hàm hoặc kết hợp các hàm cho phù hợp.
4 hàm tính tổng có điều kiện trong Excel phổ biến nhất:
- Hàm SUMIF
- Hàm SUMIFS
- Hàm SUMPRODUCT
- Hàm SUBTOTAL
Việc nắm vững kiến thức nền về toàn bộ các hàm tính tổng có điều kiện sẽ giúp bạn tư duy, sắp xếp các hàm nhanh và chính xác hơn. Mong rằng với bài viết này, bạn có thể bổ sung, mở rộng kiến thức cũng như kỹ năng để tăng hiệu quả làm việc.
Ngoài ra, nếu công việc của bạn gắn liền với Tin học văn phòng nhưng vì quá bận rộn nên bạn không có thời gian tham gia các lớp học trực tiếp ở trung tâm thì bạn có thể tham khảo khóa Luyện thi MOS online các môn Excel, Word, PowerPoint, và Excel AZ từ cơ bản đến nâng cao và ứng dụng của Trung tâm tin học văn phòng MOS nhé!
Thông Tin Liên Hệ
Trung tâm tin học văn phòng MOS – Tinhocmos– Đại học Ngoại Thương
Email: [email protected]
Hotline: 0914 444 343
