Các hàm thống kê có điều kiện trong excel – Hàm Countif, Sumif, Countifs, Sumifs trong Excel
A. Hàm Countif:
1. Chức năng của hàm Countif
Hàm Countif dùng để đếm các ô trong vùng dữ liệu theo một điều kiện.
2. Công thức hàm Countif
Countif (range, criterial)
Giải thích:
- range – Vùng điều kiện chứa điều kiện cần đếm
- criterial – Điều kiện. Điều kiện có thể là số, biểu thức, tham chiếu ô hay chuỗi văn bản để xác định ô sẽ được đếm. Có thể kết hợp các ký tự đặt biệt trong điều kiện: ? đại diện 1 ký tự, * đại diện nhiều ký tự.
3. Ví dụ về hàm Countif trong Excel
Cho bảng Dữ liệu sau. Hãy thống kê số học sinh trong lớp theo bảng Xếp loại học sinh.
Áp dụng hàm Countif vào trường hợp này, ta có Kết quả tại ô C17 như sau:
C17 = Countif (E5:E14,”Giỏi”)
Trong đó:
- E5:E14 là vùng dữ liệu, chứa dữ liệu để đếm.
- “Giỏi” là điều kiện (Criterial)
Hàm Countif sẽ đếm tất cả những ô thỏa mãn điều kiện là “Giỏi” trong vùng dữ liệu từ E5 đến E14 ngược lại sẽ không tính.
Tương tự cho các ô còn lại với trường hợp đếm số học sinh Khá, Trung Bình, Yếu.
C18 = COUNTIF(E5:E14,”Khá”)
C19 = COUNTIF(E5:E14,”Trung Bình”)
C20 = COUNTIF(E5:E14,”Yếu”)
Kết quả dùng hàm Countif để thống kê số Học sinh theo cột Xếp loạiB. Hàm Countifs:
1. Chức năng của hàm Countifs
Hàm Countifs tương tự như hàm Countif, nhưng đếm tổng số ô theo nhiều điều kiện cùng một lúc trong vùng dữ liệu.
2. Công thức hàm Countifs
Countifs (range 1, criteria 1, range 2, criteria 2, range 3, criteria 3, …)
Giải thích:
- range 1, range 2, range 3, …: Vùng điều kiện 1 chứa điều kiện 1, vùng điều kiện 2 chứa điều kiện 2, vùng điều kiện 3 chứa điều kiện 3,…
- criteria 1, criteria 2, criteria 3, …: điều kiện 1, điều kiện 2, điều kiện 3, … tương ứng với các vùng điều kiện ở trên.
3. Ví dụ về hàm Countifs trong Excel
Cho bảng Dữ liệu sau. Hãy thống kê:
- TH1: Số học sinh Nam có học lực Giỏi tại B8.
- TH2: Số học sinh Nữ có Điểm >= 5 tại ô B9.
Áp dụng hàm Countifs vào TH1, ta có Kết quả tại ô B8 như sau: = Countifs (B2:B6,”X“,D2:D6,”Giỏi“) => Kết quả trả về là 2 (học sinh)
Trong đó:
- B2:B6 là vùng dữ liệu cột Nam, “X” điều kiện là Nam
- D2:D6 là vùng dữ liệu cột Học lực, “Giỏi” là điều kiện
Áp dụng hàm Countifs vào TH2, ta có Kết quả tại ô B9 như sau: = Countifs (C2:C6,”X“,E2:E6,”>=5“) => Kết quả trả về là 1 (học sinh)
Trong đó:
- C2:C6 là vùng dữ liệu cột Nữ, “X” điều kiện là Nữ
- E2:E6 là vùng dữ liệu cột Điểm, “>=5” là điều kiện
Kết quả dùng hàm Countifs để thống kê
C. Hàm Sumif:
Hàm Sumif là một trong những hàm thống kê có điều kiện trong Excel, được sử dụng khá phổ biến trong Excel, được ứng dụng vào học tập, công việc văn phòng,… Trong bài viết này chúng tôi sẽ hướng dẫn các bạn hiểu rõ nhất về cú pháp cũng như cách sử dụng của hàm Sumif trong Excel.
1. Chức năng của hàm Sumif trong Excel
Hàm Sumif dùng để tính tổng theo một điều kiện. Tính tổng cho các ô trong vùng cần tính tổng (Sum_range) thỏa mãn điều kiện (Criteria) theo vùng điều kiện (range).
2. Cú pháp của hàm Sumif trong Excel
Sumif (range, criteria, sum_range)
Sumif (Vùng chứa dữ liệu so sánh với điều kiện, Điều kiện, Vùng cần tính tổng)
Giải thích:
- Range: Vùng chứa dữ liệu so sánh với điều kiện (Criteria)
- Criteria: Điều kiện
- Sum_range: Vùng cần tính tổng
3. Ví dụ hàm Sumif trong Excel
Ví dụ 1: Cho bảng tính như mẫu bên dưới. Hãy tính tổng lương cho Phòng Ban là “Kế hoạch” tại ô E15.
Áp dụng hàm Sumif để tính tổng lương cho phòng ban Kế hoạch tại ô E15 ta có. E15=SUMIF(D5:D12,”Kế hoạch”,E5:E12)
Trong công thức này:
- D5:D12 – Vùng chứa dữ liệu cần so sánh với điều kiện: Kế hoạch
- “Kế hoạch” – Điều kiện (Criteria)
- E5:E12 – Vùng cần tính tổng
Kết quả sau khi sử dụng hàm Sumif thống kê như sau:
D. Hàm Sumifs:
Hàm Sumifs là hàm thống kê theo nhiều điều kiện trong Excel, được sử dụng khá phổ biến trong Excel, được ứng dụng vào học tập, công việc văn phòng,… Trong bài viết này chúng tôi sẽ hướng dẫn các bạn hiểu rõ nhất về cú pháp cũng như cách sử dụng của hàm Sumifs trong Excel.
1. Chức năng của hàm Sumifs trong Excel
Hàm Sumifs dùng để tính tổng theo nhiều điều kiện. Tính tổng cho các ô trong vùng cần tính tổng (Sum_range) thỏa mãn điều kiện 1 (Criteria 1) theo vùng điều kiện 1 (range 1), điều kiện 2 (Criteria 2) theo vùng điều kiện 2 (range 2),… Chỉ áp dụng cho điều kiện và.
2. Cú pháp của hàm Sumif trong Excel
Sumif (sum_range, range 1, criteria 1, [range 2, criteria 2], … )
Giải thích:
- Sum_range: Vùng cần tính tổng
- Range 1: Vùng chứa dữ liệu so sánh với điều kiện 1 (Criteria 1)
- Criteria 1: Điều kiện 1
- Range 2: Vùng chứa dữ liệu so sánh với điều kiện 2 (Criteria 2)
- Criteria 2: Điều kiện 2
3. Ví dụ hàm Sumifs trong Excel
Ví dụ 1: Cho bảng tính như mẫu bên dưới. Hãy tính tổng thành tiền cho sản phẩm “Laptop” có Loại “A” tại ô C14
Áp dụng hàm Sumifs để tính tổng thành tiền cho sản phẩm “Laptop” có Loại “A” tại ô C14=SUMIFS(E2:E7,A2:A7,”A”,B2:B7,”Laptop”)
Trong công thức này:
- E2:E7: Vùng cần tính tổng
- A2:A7: Vùng chứa dữ liệu cần so sánh với điều kiện 1: “A”
- “A”: Điều kiện 1 (Criteria 1)
- B2:B7: Vùng chứa dữ liệu cần so sánh với điều kiện 2: “Laptop”
- “Laptop”: Điều kiện 2
Kết quả sau khi sử dụng hàm Sumifs thống kê như sau:
