Bài viết hướng dẫn sử dụng các hàm thống kê theo điều kiện trong Excel

Excel mang đến nhiều hàm tính toán thống kê, phổ biến cho các công việc thống kê, thường được thực hiện thông qua Excel. Qua bài viết này từ Siêu Marketing, chúng ta sẽ cùng tìm hiểu về một số hàm thống kê điều kiện trong Excel. Những hàm này bao gồm những gì? Chức năng và phương pháp sử dụng của chúng là như thế nào? Các bạn sẽ rõ ràng hơn khi theo dõi qua bài viết dưới đây.

Cách xác định trung bình có điều kiện

Khi nói đến việc tính trung bình, chúng ta thường nhớ đến nhóm các hàm AVERAGE. Excel cung cấp 2 hàm hỗ trợ tính giá trị trung bình theo điều kiện đặt ra:

  • AVERAGEIF(range,criteria,average_range): Dùng để tính trung bình với một điều kiện
  • AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2, criteria2],…): Ứng dụng để tính trung bình với nhiều điều kiện

Cơ cấu và cách ghi các phần trong cả hai hàm này tương tự như hàm SUMIF và SUMIFS, tuy nhiên thay bằng việc tính tổng thì nó sẽ tính trung bình các giá trị thỏa mãn.

Hãy cùng xem xét ví dụ sau để hiểu rõ hơn về hai hàm này.

1 - hướng dẫn thực hiện các hàm thống kê theo điều kiện trong Excel
1 – hướng dẫn thực hiện các hàm thống kê theo điều kiện trong Excel

Đối với yêu cầu số 1: Tính lương bình quân của bộ phận kinh doanh

Yêu cầu cần phân tích:

  • Phạm vi tính trung bình: cột Mức lương
  • Phạm vi chứa điều kiện: cột Bộ phận
  • Điều kiện cần áp dụng: Kinh doanh

Có thể áp dụng cả hai hàm cho trường hợp này, do chỉ có một điều kiện được đặt ra.

Kết quả thu được như sau:

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

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

2 - hướng dẫn thực hiện các hàm thống kê theo điều kiện trong Excel
2 – hướng dẫn thực hiện các hàm thống kê theo điều kiện trong Excel

Cả AVERAGEIF và AVERAGEIFS đều mang lại kết quả giống nhau. Tuy nhiên, khi nhập hàm, cần lưu ý rằng: cấu trúc của AVERAGEIFS có sự khác biệt so với AVERAGEIF khi phạm vi tính giá trị trung bình được đặt đầu tiên trong hàm, trong khi đối với AVERAGEIF thì phần này được đặt ở vị trí thứ ba. Điều này tương tự như cách sử dụng nhóm hàm SUMIF và SUMIFS.

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

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

  • Phạm vi tính trung bình: cột Mức lương
  • Phạm vi điều kiện thứ nhất: cột Bộ phận
  • Điều kiện thứ nhất: Kinh doanh
  • Phạm vi điều kiện thứ hai: cột Tháng
  • Điều kiện thứ hai: 2 (tháng Hai)

Yêu cầu này đặt ra tới hai điều kiện. Trong trường hợp này, chỉ có thể sử dụng hàm AverageIFS mà không dùng được hàm AverageIF.

Công thức của hàm bạn nhập là:

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
3 – cách sử dụng các hàm thống kê có điều kiện trong Excel

Hàm AverageIFS được biết đến với khả năng thay thế hàm AverageIF hiệu quả, giúp tính giá trị trung bình dựa trên các điều kiện đặt ra.

Đọc thêm: Hướng dẫn tính trung bình động sử dụng Excel

Các hàm đếm có điều kiện

Nhắc đến việc đếm trong Excel, chúng ta có bộ hàm COUNT. Để đếm với điều kiện, hàm COUNTIF phù hợp cho trường hợp có một điều kiện, trong khi đó hàm COUNTIFS áp dụng cho các trường hợp có nhiều điều kiện.

Bạn có thể tìm đọc các bài viết liên quan đến các hàm đếm có điều kiện được đăng tải trên sieumarketing.com:

Mẹo vận dụng hàm COUNTIFS và COUNTIF khi có nhiều điều kiện

Các hàm tìm giá trị lớn nhất, nhỏ nhất dựa theo điều kiện

Kể từ phiên bản OFFICE 2016 và OFFICE 365, người dùng có thêm các công cụ để xác định giá trị lớn nhất và nhỏ nhất dựa theo điều kiện. Các hàm cụ thể:

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

Một số ví dụ áp dụng hai hàm trên:

4 - cách sử dụng các hàm thống kê có điều kiện trong Excel
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: Xác định mức lương cao nhất của bộ phận Kinh doanh trong tháng Hai

Khi phân tích yêu cầu:

  • Chọn cột Mức lương làm vùng để xác định giá trị lớn nhất
  • Chọn cột Bộ phận làm điều kiện thứ nhất
  • Điều kiện thứ nhất là “Kinh doanh”
  • Chọn cột Tháng làm điều kiện thứ hai
  • Điều kiện thứ hai là tháng “2”

Do vậy, để tìm ra giá trị lớn nhất cần hai điều kiện. Hàm MAXIFS sử dụng 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
5 – cách sử dụng các hàm thống kê có điều kiện trong Excel

Cú pháp viết vùng điều kiện và điều kiện tương tự khi sử dụng các hàm Averageifs, countifs, sumifs.

Yêu cầu 2: Xác định mức lương thấp nhất trong tháng Hai

Khi phân tích yêu cầu:

  • Chọn cột Mức lương làm vùng để tìm giá trị nhỏ nhất
  • Chọn cột Tháng làm điều kiện
  • Điều kiện đặt ra là giá trị “2” (tháng Hai)

Trường hợp này chỉ cần một điều kiện. Để tìm giá trị nhỏ nhất theo điều kiện, chúng ta áp dụng hàm MINIFS 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
6 – cách sử dụng các hàm thống kê có điều kiện trong Excel

Xem thêm: Hướng dẫn cách xếp hạng dựa theo điều kiện (bài viết bao gồm 3 phần)

Qua bài viết này, ta đã học được các hàm thống kê dựa trên điều kiện trong Excel. Những thông tin này và nhiều kiến thức hữu ích khác có thể tìm hiểu qua khóa học Excel từ cơ bản đến chuyên gia của Siêu Marketing. Tham gia khóa học ngay bằng cách nhấp vào thông tin phía dưới:

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop