Hướng dẫn sử dụng các hàm thống kê có điều kiện trong Excel

Việc áp dụng các hàm thống kê có điều kiện trong Excel không chỉ giúp bạn dễ dàng phân tích số liệu mà còn mang lại cái nhìn rõ ràng hơn trong việc hoạch định chiến lược kinh doanh. Bài viết này sẽ giúp bạn hiểu rõ hơn về các hàm này và cách ứng dụng chúng trong thực tế.

1. Hàm tính giá trị trung bình có điều kiện

Hàm tính giá trị trung bình có điều kiện có hai hình thức chính:

- Tính trung bình với một điều kiện cụ thể: Averageif

- Tính trung bình với các điều kiện đa dạng: Averageifs

a) Hàm Averageif

Ý nghĩa: Hàm này thực hiện phép tính trung bình của các giá trị trong phạm vi dữ liệu đã chọn, với một điều kiện được chỉ định trước.

Cú pháp: Averageif(Range, Criteria, Average_range)

Công thức bao gồm các thành phần sau:

- Range: Vùng dữ liệu chứa các giá trị để áp dụng điều kiện tính trung bình.

- Criteria: Điều kiện dùng để tính giá trị trung bình, có thể là địa chỉ ô chứa điều kiện hoặc điều kiện được nhập trực tiếp vào công thức.

- Average_range: Vùng dữ liệu chứa các giá trị cần tính trung bình dựa trên điều kiện đã đặt.

Chú ý:

- Các giá trị cần tính trung bình phải là những giá trị số.

- Nếu điều kiện không có trong dữ liệu, hàm sẽ trả về lỗi #DIV/0!

- Nếu biểu thức điều kiện chứa các ký tự đặc biệt như ? và *, bạn cần thêm dấu ~ trước điều kiện đó để tránh lỗi.

Ví dụ:

Khi vùng dữ liệu tính trung bình không phải kiểu số, Excel sẽ không thông báo mà chỉ đơn giản bỏ qua và không thực hiện phép tính.

b) Hàm Averageifs

Ý nghĩa: Hàm này tính giá trị trung bình của các số liệu trong một phạm vi, nhưng chỉ tính những giá trị thoả mãn đồng thời nhiều điều kiện khác nhau.

Cú pháp: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Công thức bao gồm các yếu tố sau:

- Average_range: Phạm vi dữ liệu chứa các giá trị cần tính trung bình theo các điều kiện đã đưa ra.

- Tiêu chí 1, Tiêu chí 2: Điều kiện để tính toán giá trị trung bình có thể được nhập dưới dạng ô địa chỉ chứa điều kiện hoặc trực tiếp trong công thức, với tối đa 127 điều kiện có thể được sử dụng.

- Vùng dữ liệu Criteria1, Criteria2: Các vùng dữ liệu chứa các điều kiện tương ứng với Criteria1, Criteria2... có thể bao gồm tối đa 127 vùng dữ liệu cho các điều kiện này.

Lưu ý:

- Các giá trị cần tính toán trung bình phải là những giá trị số.

- Nếu biểu thức điều kiện không tồn tại trong cơ sở dữ liệu, hàm sẽ trả về lỗi #DIV/0!

- Khi biểu thức điều kiện có chứa các ký tự thay thế như ? và *, đừng quên đặt dấu ~ trước biểu thức đó để đảm bảo tính chính xác.

Ví dụ:

Lưu ý, các điều kiện, dù là biểu thức toán học hay ký tự, đều phải được đặt trong dấu nháy kép.

2. Hàm đếm với điều kiện

a) Hàm Countif

Ý nghĩa: Đếm số lượng ô thỏa mãn điều kiện đã xác định trong phạm vi dữ liệu.

Cú pháp: Countif(range, criteria)

Trong đó:

- range: Vùng dữ liệu chứa các giá trị cần được đếm hoặc thống kê.

- criteria: Điều kiện áp dụng để đếm các giá trị.

Lưu ý:

- Khi sử dụng dấu phẩy để phân tách phần nghìn trong dữ liệu số, hãy đảm bảo rằng điều kiện bạn nhập phản ánh chính xác giá trị dấu phẩy như trong bảng dữ liệu.

Ví dụ:

b) Hàm Countifs

Ý nghĩa: Đếm số ô thỏa mãn hơn một điều kiện trong phạm vi dữ liệu đã chỉ định.

Cú pháp: Countif(criteria_range1, criteria1, criteria_range2, criteria2,…)

Trong đó:

- criteria_range1, criteria_range2,…: Các vùng dữ liệu chứa các điều kiện cần được đếm.

- criteria1, criteria2,…: Các điều kiện cần áp dụng cho từng vùng dữ liệu tương ứng với criteria_range1, criteria_range2,…

Ví dụ:

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

a) Hàm Dmax

Ý nghĩa: Hàm Dmax trả về giá trị lớn nhất trong một phạm vi dữ liệu với các điều kiện xác định.

Cú pháp: =DMAX(database, field, criteria)

Trong đó:

- database: Danh sách hoặc cơ sở dữ liệu có liên quan, bao gồm cả các tiêu đề cột. Nếu không có tiêu đề cột trong database, kết quả sẽ không chính xác.

- field: Trường (cột) mà bạn muốn lấy giá trị lớn nhất. Bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép, hoặc sử dụng số để đại diện cho vị trí cột (1 cho cột đầu tiên, 2 cho cột thứ hai,…) hoặc chỉ định ô chứa tiêu đề cột (ví dụ: B3, C1…).

- criteria: phạm vi ô chứa điều kiện, bạn có thể chọn bất kỳ vùng dữ liệu nào miễn sao phạm vi đó chứa ít nhất một Tiêu đề cột và ô dưới tiêu đề cột chứa điều kiện cho cột, với tiêu đề cột phải trùng khớp với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

- Nên đặt phạm vi điều kiện criteria trên trang tính để đảm bảo khi thêm dữ liệu, phạm vi chứa điều kiện không bị thay đổi.

- Phạm vi điều kiện cần được tách biệt và không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

- criteria phải bao gồm ít nhất một Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

b) Hàm Dmin

Ý nghĩa: Hàm Dmin trả về giá trị nhỏ nhất trong một phạm vi dữ liệu, dựa trên điều kiện đã chỉ định.

Cú pháp: =DMIN(database, field, criteria)

Trong đó:

- database: Danh sách hoặc cơ sở dữ liệu có liên quan, bao gồm cả các tiêu đề cột. Nếu trong database không có tiêu đề cột, kết quả trả về sẽ không chính xác.

- field: Trường (cột) mà bạn muốn lấy giá trị lớn nhất. Bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép, hoặc chỉ định số tương ứng với vị trí cột trong database: 1 cho cột đầu tiên, 2 cho cột thứ hai,… Hoặc bạn cũng có thể nhập ô chứa tiêu đề cột cần sử dụng, ví dụ như B3, C1,…

- criteria: Là phạm vi ô chứa điều kiện, bạn có thể chọn bất kỳ vùng dữ liệu nào miễn sao phạm vi đó có ít nhất một tiêu đề cột và ô dưới tiêu đề cột phải chứa điều kiện cho cột, với tiêu đề cột phải trùng khớp với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

- Nên đặt phạm vi điều kiện criteria trên một trang tính riêng biệt để đảm bảo khi thêm dữ liệu mới, phạm vi điều kiện không bị thay đổi.

- Phạm vi điều kiện cần được tách biệt và không chèn lên danh sách hay cơ sở dữ liệu đang được xử lý.

- criteria bắt buộc phải bao gồm ít nhất một Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

Ví dụ:

Hàm Dmax trả về giá trị số lớn nhất trong một trường dữ liệu, vì vậy nếu giá trị không phải là dữ liệu số, Excel sẽ yêu cầu bạn nhập lại dữ liệu đúng định dạng.

Bài viết trên đây đã giới thiệu đến các bạn cách sử dụng một số hàm thống kê có điều kiện trong Excel, hy vọng sẽ mang lại những thông tin hữu ích và giúp các bạn đạt được thành công. Chúc các bạn học tập tốt!