Mục tiêu bài học
- Hiểu rõ chức năng và cách viết của hàm COUNTIF.
- Sử dụng thành thạo hàm COUNTIF để tính số lần thu, chi của từng khoản trong một bảng tính quản lý tài chính.
- Áp dụng hàm COUNTIF để thống kê dữ liệu theo các yêu cầu và điều kiện khác nhau.
Hoạt động khởi động
Để kiểm soát các khoản thu, chi của gia đình, bạn Toàn đã lập một bảng tính như trong Hình 1. Bây giờ, Toàn muốn thống kê số lần thu và chi cho từng khoản mục (ví dụ: nhận lương bao nhiêu lần, đi chợ bao nhiêu lần). Quan trọng hơn, số liệu này phải tự động cập nhật mỗi khi có thay đổi.
Theo em, bạn Toàn làm thế nào để thực hiện được điều này?
Để thực hiện yêu cầu này, bạn Toàn cần sử dụng một hàm trong Excel có chức năng đếm các ô theo một điều kiện cụ thể:
+ Hàm phù hợp nhất chính là hàm COUNTIF. Bằng cách sử dụng hàm này, Toàn có thể đếm số lần xuất hiện của mỗi khoản thu (ví dụ: “Lương”) và mỗi khoản chi (ví dụ: “Ăn”) trong danh sách.
+ Khi sử dụng hàm, kết quả thống kê sẽ tự động cập nhật mỗi khi dữ liệu trong cột khoản thu hoặc khoản chi thay đổi.
1. Giới thiệu hàm COUNTIF
Hàm COUNTIF là một công cụ mạnh mẽ trong Excel, cho phép bạn đếm số ô trong một vùng dữ liệu đáp ứng một tiêu chí (điều kiện) duy nhất mà bạn đặt ra.
Cú pháp của hàm:=COUNTIF(range, criteria)
Trong đó:
- range: Là vùng dữ liệu (khối ô tính) mà bạn muốn kiểm tra và đếm. Ví dụ: B3:B12.
- criteria: Là điều kiện dùng để đếm. Một ô trong range sẽ được đếm nếu dữ liệu chứa trong nó khớp với criteria.
2. Ví dụ sử dụng hàm COUNTIF
Chúng ta sẽ cùng phân tích cách bạn Toàn sử dụng hàm COUNTIF để thống kê số lần thu trong bảng tính của mình.
Để đếm số lần nhận “Lương”, tại ô G2, Toàn nhập công thức: =COUNTIF(B$3:B$12,F2)
B$3:B$12: Là vùng chứa danh sách các khoản thu cần đếm. Dấu $ trước số 3 và 12 (địa chỉ hỗn hợp) có tác dụng cố định các dòng này, để khi sao chép công thức xuống các ô bên dưới, vùng tìm kiếm không bị thay đổi.- F2: Là ô chứa tiêu chí cần đếm (trong trường hợp này là chữ “Lương”). Khi sao chép công thức xuống ô G3, F2 sẽ tự động cập nhật thành F3 để đếm cho “Làm thêm giờ”.
3. Các loại điều kiện trong hàm COUNTIF
Hàm COUNTIF rất linh hoạt và có thể sử dụng nhiều loại điều kiện khác nhau. Dưới đây là bảng tổng hợp một số ví dụ phổ biến:
| Yêu cầu thống kê | Công thức ví dụ | Giải thích |
| Đếm số lần thu có số tiền lớn hơn 5.000.000 | =COUNTIF(D3:D12,">5000") | Điều kiện là một biểu thức so sánh, phải được đặt trong dấu ngoặc kép. |
| Đếm số lần thu từ “Lương cứng” | =COUNTIF(C3:C12,"Lương cứng") | Điều kiện là một chuỗi văn bản chính xác. |
| Đếm số lần bán có từ “Bạch đàn” | =COUNTIF(C3:C12,"Bạch đàn*") | Dấu * là ký tự đại diện cho một chuỗi ký tự bất kỳ. Công thức này sẽ đếm cả “Bạch đàn” và “Bạch đàn, phi lao”. |
Câu hỏi củng cố
1. Tại sao phải sử dụng địa chỉ hỗn hợp B$3:B$12 trong công thức ở Hình 2?
Chúng ta phải sử dụng địa chỉ hỗn hợp B$3:B$12 để cố định vùng dữ liệu cần đếm khi sao chép công thức.
- Khi sao chép công thức từ ô G2 xuống các ô G3, G4, G5…, vùng dữ liệu range (B
3:B12) sẽ được giữ nguyên không thay đổi. - Trong khi đó, địa chỉ ô criteria (F2) là địa chỉ tương đối, sẽ tự động thay đổi thành F3, F4, F5… để lấy đúng điều kiện đếm cho từng dòng.
- Nếu không có dấu $, khi sao chép công thức, vùng dữ liệu sẽ bị trượt xuống (thành B4:B13, B5:B14…), dẫn đến kết quả thống kê bị sai.
Luyện tập
1. Áp dụng kiến thức vừa học vào trang tính Chi tiêu (Hình 1b).
a) Nêu cách làm để đếm số lần chi của từng khoản chi và đặt kết quả vào ô tính tương ứng ở cột G.
Để đếm số lần chi của từng khoản, ta thực hiện như sau:
- Tại ô G2 của trang tính Chi, nhập công thức:
=COUNTIF(B$3:B$14,F2). - Nhấn Enter để nhận kết quả cho khoản chi “Ăn”.
- Sao chép công thức từ ô G2 xuống các ô còn lại trong cột G (từ G3 đến G7) để tính cho các khoản chi khác. Vùng dữ liệu B$3:B$14 sẽ được cố định, trong khi ô điều kiện sẽ tự động cập nhật (F3, F4…).
b) Viết công thức để thực hiện mỗi yêu cầu thống kê dữ liệu chi tiêu ở Hình 4.
- Chi trên 10 triệu đồng/lần:
=COUNTIF(D3:D14,">10000")- Giải thích: Đếm các ô trong vùng D3:D14 (cột Số tiền) có giá trị lớn hơn 10000.
- Chi mua xăng:
=COUNTIF(C3:C14,"Xăng")- Giải thích: Đếm các ô trong vùng C3:C14 (cột Hàng hoá, dịch vụ) có nội dung chính xác là “Xăng”.
- Chi mua gạo:
=COUNTIF(C3:C14,"Gạo*")- Giải thích: Đếm các ô trong vùng C3:C14 có nội dung bắt đầu bằng chữ “Gạo”, ví dụ “Gạo, cá, rau”.
Vận dụng
Thực hành cá nhân
Mở bảng tính QuanLiTaiChinh.xlsx đã tạo ở Bài 6A.
a) Sử dụng hàm COUNTIF để đếm số lần thu và số lần chi theo từng khoản mục tương ứng trong danh sách.
b) và c) Lập thêm bảng thống kê dữ liệu thu nhập và chi tiêu theo các điều kiện nâng cao (tương tự Hình 3 và Hình 4).
d) Tự đặt ra một yêu cầu thống kê cho riêng mình (ví dụ: thống kê số lần mua sách, vở, số lần đi xem phim…) và lập công thức COUNTIF để tìm ra câu trả lời.
Làm việc nhóm
Mở bảng tính QuanLiTaiChinhCLB.xlsx đã tạo ở Bài 6A.
a) Sử dụng hàm COUNTIF để đếm số lần thu, chi của câu lạc bộ theo từng khoản trong danh sách.
b) Cùng nhau thảo luận và xác định các yêu cầu thống kê có ý nghĩa đối với hoạt động của câu lạc bộ (ví dụ: số lần đóng quỹ, số lần chi cho sự kiện…). Sau đó, lập công thức COUNTIF để thực hiện các yêu cầu đó.
