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.
Đang xem: Cách lập bảng thống kê số lượng trong excel
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ệnAVERAGEIFS(average_range,criteria_range1,criteria1,
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
1 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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ươngVù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”)
2 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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ươngVùng điều kiện 1: cột Bộ phậnĐiều kiện 1: Kinh doanhVù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)
3 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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: Bỏ Túi Cách Chế Biến Trứng Đà Điểu Cho Bà Bầu, Hướng Dẫn 3 Món Ăn Được Nấu Từ Trứng Đà Điểu
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 pacmanx.com. 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,
Ví dụ về 2 hàm này như sau:
4 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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ươngVùng điều kiện 1: cột Bộ phậnĐiều kiện 1: Kinh doanhVù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)
5 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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ươngVù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)
6 – cách sử dụng các hàm thống kê có điều kiện trong Excel
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: