Trong Excel có rất nhiều hàm sử dụng cho mục đích thống kê, bởi việc thống kê được thực hiện chủ yếu thông qua công cụ Excel. Trong bài viết này của Học Excel Online chúng ta sẽ cùng nhau tìm hiểu một số hàm thống kê có điều kiện trong Excel. Các hàm đó là những hàm nào? Công dụng và cách sử dụng ra sao? Các bạn sẽ có câu trả lời sau khi tìm hiểu bài viết này.

Tính trung bình có điều kiện

Nhắc tới tính trung bình, chúng ta nghĩ ngay tới nhóm hàm AVERAGE. Và để phục vụ cho việc tính trung bình theo điều kiện thì Excel cung cấp cho chúng ta 2 hàm là:

• AVERAGEIF(range,criteria,average_range): Tính trung bình theo 1 điều kiện
• AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2, criteria2],…): Tính trung bình theo nhiều điều kiện

Về cấu trúc và cách viết các thành phần trong hàm thì hai hàm này khá giống với hàm SUMIF và SUMIFS, nhưng thay vì tính tổng thì sẽ tính trung bình của các giá trị.

Để giải thích rõ hơn về 2 hàm này chúng ta xét ví dụ sau

Với yêu cầu 1: Tính mức lương trung bình bộ phận kinh doanh

Phân tích yêu cầu:

• Vùng tính trung bình: cột Mức lương
• Vùng chứa điều kiện: cột Bộ phận
• Điều kiện: Kinh doanh

Chúng ta có thể sử dụng cả 2 hàm để tính trong trường hợp này, bởi yêu cầu này chỉ có 1 điều kiện.

Kết quả như sau:

E12=AVERAGEIF(C2:C9,”Kinh doanh”,D2:D9)

F12=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”)

Cả 2 hàm đều ra kết quả giống nhau. Tuy nhiên khi viết hàm cần chú ý: Cấu trúc của hàm AVERAGEIFS khác so với hàm AVERAGEIF khi vùng tính giá trị trung bình được viết trước, tại thành phần đầu tiên của hàm, trong khi hàm AverageIF thì thành phần đó đặt ở vị trí thứ 3. Điều này rất giống với nhóm hàm SUMIF / SUMIFS

Với yêu cầu 2: Tính mức lương trung bình bộ phận kinh doanh trong tháng 2

Phân tích yêu cầu:

• Vùng tính trung bình: cột Mức lương
• Vùng điều kiện 1: cột Bộ phận
• Điều kiện 1: Kinh doanh
• Vùng điều kiện 2: cột Tháng
• Điều kiện 2: 2 (tháng 2)

Như vậy trong yêu cầu này có tới 2 điều kiện. Trường hợp này không dùng được hàm AverageIF mà chỉ dùng được hàm AverageIFS

Cách viết hàm như sau:

F13=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)

Như vậy hàm AverageIFS là một hàm rất hiệu quả, hoàn toàn có thể thay thế cho hàm AverageIF trong mọi trường hợp cần tính trung bình theo điều kiện.

Xem thêm: Cách tính trung bình động trong Excel

Hàm đếm theo điều kiện

Trong yêu cầu đếm thì chúng ta có nhóm hàm COUNT. Việc đếm theo điều kiện có thể sử dụng hàm COUNTIF để đếm theo 1 điều kiện, hàm COUNTIFS để đếm theo nhiều điều kiện.

Các bài viết về hàm đếm theo điều kiện đã có sẵn khá nhiều trên blog.hocexcel.online. Các bạn có thể tham khảo các bài viết sau:

Cách dùng hàm COUNTIFS và COUNTIF với nhiều điều kiện

Hàm tìm giá trị lớn nhất, nhỏ nhất theo điều kiện

Từ phiên bản OFFICE 2016, OFFICE 365 trở đi chúng ta có thêm hàm giúp tìm giá trị lớn nhất, nhỏ nhất theo điều kiện. Cụ thể là

• MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị lớn nhất theo 1 hoặc nhiều điều kiện
• MINIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị nhỏ nhất theo 1 hoặc nhiều điều kiện

Ví dụ về 2 hàm này như sau:

Yêu cầu 1: Tìm giá trị mức lương lớn nhất bộ phận Kinh doanh trong tháng 2

Phân tích yêu cầu:

• Vùng xét giá trị lớn nhất: cột Mức lương
• Vùng điều kiện 1: cột Bộ phận
• Điều kiện 1: Kinh doanh
• Vùng điều kiện 2: cột Tháng
• Điều kiện 2: 2 (tháng 2)

Như vậy cần dùng tới 2 điều kiện để tìm ra giá trị lớn nhất. Cách viết hàm MAXIFS như sau:

E12=MAXIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)

Cách viết vùng điều kiện, điều kiện giống như khi chúng ta viết với các hàm Averageifs, countifs, sumifs

Yêu cầu 2: tìm mức lương nhỏ nhất trong tháng 2

Phân tích yêu cầu:

• Vùng xét giá trị nhỏ nhất: cột Mức lương
• Vùng điều kiện 1: cột Tháng
• Điều kiện 1: giá trị là 2 (tháng 2)

Như vậy trường hợp này chỉ cần dùng 1 điều kiện. Tuy nhiên hàm tìm giá trị nhỏ nhất theo điều kiện chỉ có hàm MINIFS nên chúng ta sử dụng hàm này như sau:

E13=MINIFS(D2:D9,A2:A9,2)

Xem thêm: Cách xếp hạng theo điều kiện (bài viết có 3 phần)

Như vậy thông qua bài viết này, chúng ta đã có thể nắm được những hàm giúp thống kê theo điều kiện trong Excel. Những kiến thức này các bạn có thể học được trong khóa học Excel từ cơ bản tới chuyên gia của Học Excel Online. Ngoài ra khóa học còn cung cấp cho bạn nhiều kiến thức bổ ích khác nữa, giúp bạn ứng dụng tốt hơn Excel trong công việc. Hãy tham gia ngay khóa học bằng cách bấm vào thông tin dưới đây: