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

Thống kê số liệu là bước quan trọng giúp bạn hoạch định trong quá trình kinh doanh của mình. Trong quá trình tính toán bạn không thể không sử dụng các hàm thống kê nhanh trong Excel. Ở bài viết này giúp các bạn tìm hiểu các hàm thống kê đó.

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

Với hàm tính trung bình có điều kiện có 2 dạng:

– Tính trung bình với 1 điều kiện: Averageif

– Tính trung bình với nhiều điện kiện: Averageifs

a) Hàm Averageif

Ý nghĩa: Hàm thực hiện tính trung bình cộng của các giá trị số trong vùng dữ liệu cho trước với một điều kiện nhất định.

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

Trong đó:

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

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

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

Chú ý:

– Giá trị để tính trung bình phải là giá trị số.

– Nếu biểu thức điều kiện không có trong cơ sở dữ liệu => hàm trả về giá trị lỗi #DIV/0!

– Trong biểu thức điều kiện có chứa các kí tự thay thế như ? và * bạn cần đặt dấu ~ trước biểu thức điều kiện đó.

Ví dụ:

Nếu vùng dữ liệu để tính trung bình không phải kiểu dữ liệu số -> Excel không đưa ra thông báo yêu cầu các bạn nhập lại dữ liệu.

b) Hàm Averageifs

Ý nghĩa: Hàm thực hiện tính trung bình cộng của các giá trị số cho trước trong vùng dữ liệu thỏa mãn nhiều hơn 1 điều kiện.

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

Trong đó:

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

– Criteria1, criteria2: Điều kiện tính giá trị trung bình, có thể là ô địa chỉ chứa điều kiện hoặc nhập trực tiếp điều kiện trong công thức, tối đa 127 điều kiện.

– Criteria_range1, criteria_range2: Vùng dữ liệu chứa điều kiện tương ứng với Criteria1, criteria2… tương ứng có tối đa 127 vùng điều kiện.

Chú ý:

– Giá trị để tính trung bình phải là giá trị số.

– Nếu biểu thức điều kiện không có trong cơ sở dữ liệu => hàm trả về giá trị lỗi #DIV/0!

– Trong biểu thức điều kiện có chứa các kí tự thay thế như ? và * bạn cần đặt dấu ~ trước biểu thức điều kiện đó.

Ví dụ:

Các bạn chú ý điều kiện ở dạng biểu thức tính toán hay kí tự đều ở trong dấu nháy kép nhé.

2. Hàm đếm có điều kiện

a) Hàm Countif

Ý nghĩa: Đếm số ô thỏa mãn 1 điều kiện trong phạm vi.

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

Trong đó:

– range: Vùng dữ liệu chứa dữ liệu cần đếm hay thống kê.

– criteria: Điều kiện đếm.

Chú ý:

– Khi bạn sử dụng dấu phẩy để phân cách phần nghìn trong dữ liệu số, chú ý khi viết điều kiện bạn viết đúng giá trị dấu phẩy trong bảng dữ liệu.

Ví dụ:

b) Hàm Countifs

Ý nghĩa: Đếm số ô thỏa mãn nhiều hơn 1 điều kiện trong vùng dữ liệu xác định.

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

Trong đó:

– criteria_range1, criteria_range2,…: Vùng dữ liệu chứa điều kiện cần đếm.

– criteria1, criteria2,…: Điều kiện tương ứng với các vùng dữ liệu 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 vùng dữ liệu với điều kiện cho trước.

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

Trong đó:

– database: danh sách hay cơ sở dữ liệu có liên quan bao gồm cả các Tiêu đề cột. Nếu các trong database không chứa tiêu đề cột kết quả sẽ không đúng.

– field: trường (cột) cần lấy giá trị lớn nhất. Các bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép hay một số thể hiện vị trí cột trong database: 1 cho cột đầu tiên, 2 cho cột thứ 2…, các bạn cũng có thể nhập ô chứa tiêu đề cột cần sử dụng ví dụ B3, C1…

– criteria: là phạm vi ô chứa điều kiện, các bạn có thể chọn bất kỳ 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 trong đó tiêu đề cột trùng với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

– Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

– Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

– criteria bắt buộc phải chứa ít nhấ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 vùng dữ liệu với điều kiện cho trước.

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

Trong đó:

– database: danh sách hay cơ sở dữ liệu có liên quan bao gồm cả các Tiêu đề cột. Nếu các trong database không chứa tiêu đề cột kết quả sẽ không đúng.

– field: trường (cột) cần lấy giá trị lớn nhất. Các bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép hay một số thể hiện vị trí cột trong database: 1 cho cột đầu tiên, 2 cho cột thứ 2…, các bạn cũng có thể nhập ô chứa tiêu đề cột cần sử dụng ví dụ B3, C1…

– criteria: là phạm vi ô chứa điều kiện, các bạn có thể chọn bất kỳ 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 trong đó tiêu đề cột trùng với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

– Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

– Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

– criteria bắt buộc phải chứa ít nhấ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 đưa ra giá trị số lớn nhất trong trường dữ liệu do vậy nếu giá trị không phải dữ liệu số -> Excel yêu cầu bạn nhập lại dữ liệu.

Bài viết trên đây giới thiệu tới các bạn cách sử dụng một số hàm thống kê có chứa điều kiện trong Excel hy vọng giúp ích cho các bạn. Chúc các bạn thành công!