BÀI 2: CÁC HÀM TÍNH TOÁN THEO ĐIỀU KIỆN

MÔN HỌC: TIN HỌC – KHỐI 10

1. MỤC TIÊU CHUYÊN ĐỀ

– Biết được ý nghĩa của các hàm tính toán có điều kiện của phần mềm bảng tính Excel.

– Vận dụng được một số hàm tính toán theo điều kiện để tạo ra các dữ liệu thống kê, phục vụ cho các dự án học tập.

2. NỘI DUNG NGHIÊN CỨU

– Trong chuyên đề này, bài viết sẽ giới thiệu đến các em học sinh các hàm đếm bao gồm Counta, Countif, Countifs; các hàm tính tổng Sumif và trung bình cộng Averageif; hàm lấy giá trị lớn nhất Maxifs và nhỏ nhất Minifs.

– Đặt vấn đề: trong thực tế, em có thể gặp tình huống cần thực hiện các tính toán  theo điều kiện trên một bảng dữ liệu, ví dụ tính điểm trung bình môn Tiếng Anh của các bạn nam trong lớp.

Ví dụ trong hình: Cần tính được ở vị trí các ô màu vàng: số thí sinh theo khối lớp, tính trung bình điểm thi và tính điểm cao, thấp nhất trong các thí sinh theo khối; đếm số thí sinh được chọn…là các yêu cầu cần sử dụng tính toán theo điều kiện.

a) Hàm Counta

– Để tính số thí sinh được chọn đi thi cấp tỉnh, có thể dùng công thức =COUNTA (G3:G14). Công thức này sẽ trả về giá trị 3, là số lượng ô tính có chứa dữ liệu trong khối ô G3:G14.

– Hàm Counta được sử dụng để đếm số lượng ô tính có chứa dữ liệu trong một khối ô.

– Dữ liệu có thể ở dạng số, ngày tháng, văn bản, công thức,…

– Hàm Counta có dạng: =Counta(Khối)

– Trong đó Khối là khối cần thống kê có bao nhiêu ô tính có chứa dữ liệu trong ô.

b) Hàm Countif

– Giả sử cần thống kê có bao nhiêu thí sinh Nam trong cuộc thi, ta có thể dùng hàm countif đế đếm. Điều kiện cần xét ở đây: thí sinh là Nam.

– Có thể viết công thức tại ô D16 như hình bên dưới: =countif(D3:D15,”Nam”). Kết quả trả về: 3

– Như vậy hàm Countif có dạng: =countif(Vùng xem xét, Điều kiện)

– Trong đó: vùng xem xét là vùng chứa dữ liệu của cột Giới tính mà chứa điều kiện (là Nam, hoặc là Nữ); điều kiện là điều kiện để đếm đó là “Nam”).

c) Hàm Countifs

– Giả sử cần thống kê số lượng HS là nữ có điểm thi từ 75 điểm trở lên, như vậy hàm countif chưa thể sử dụng được, do có đến 2 điều kiện đồng thời xảy ra: là “Nữ” và điểm thi >=75.

– Do đó khi cần thống kê nhưng điều kiện xét từ 2 điều kiện trở lên, ta sử dụng hàm countifs. Ví dụ: =countifs(D13:D15,”Nữ”,F13:F15,”>=75”) . Kết quả trả về: 8

– Hàm Countifs có dạng: =Countifs (Vùng xem xét 1, điều kiện 1, [ vùng xem xét 2], [điều kiện 2],…)

– Trong đó: 

• Cặp khối ô được xem xét thỏa điều kiện thứ 1.
• Vùng xem xét 1 và điều kiện 1: cặp khối ô được xem xét thỏa điều kiện thứ 1 (bắt buộc có).
• Vùng xem xét 2, điều kiện 2: cặp khối ô được xem xét thỏa điều kiện thứ 2 (không bắt buộc có).

d) Hàm Sumif

– Đặt vấn đề: Cần thống kê tại ô J18: Tổng điểm thí sinh dự thi có điểm thi từ 75 điểm trở lên?

– Nếu hàm Sum chỉ tính tổng, thì hàm Sumif sẽ tính tổng dựa theo điều kiện xét.

– Ví dụ: gõ công thức tại ô J18: =sumif(F3:F14,”>=75”). Kết quả 650.

– Hàm Sumif có dạng: =Sumif(vùng xem xét, điều kiện,[vùng tính]

– Trong đó:

• vùng xem xét: là vùng khối ô cần xét.
• điều kiện: là điều kiện xét.
• vùng tính: có thể có hoặc không, nếu có là vùng cần tính tổng.

– Do vùng tính là vùng chứa khối ô cần xét, nên công thức tại ô J18 không có vùng tính trong công thức. Nhưng nếu yêu cầu cần thống kê là: Tính tổng điểm các thí sinh nữ dự thi. Công thức sẽ đầy đủ:

= sumif(D3:D14,”Nữ”,F13:F14)

e) Hàm Averageif

– Đặt vấn đề: Cần thống kê tại ô J19: Tính  điểm thi trung bình của thí sinh dự thi Khối 12?

– Nếu hàm Average chỉ tính trung bình cộng, thì hàm Averageif sẽ tính trung bình cộng dựa theo điều kiện xét.

– Ví dụ: gõ công thức tại ô J18: =sumif(E3:E14,12,F3:F14). Kết quả 80.

– Hàm Averageif có dạng: =Averageif(vùng xem xét, điều kiện,[vùng tính]

– Trong đó:

• vùng xem xét: là vùng khối ô cần xét.
• điều kiện: là điều kiện xét.
• vùng tính: có thể có hoặc không, nếu có là vùng cần tính trung bình cộng.

f) Hàm Maxifs và Minifs

– Đặt vấn đề: Cần xác định điểm thi cao nhất của HS là nữ khối 12?

– Nếu hàm Max chỉ tìm được giá trị lớn nhất trong khối chọn, thì hàm Maxifs sẽ tìm được giá trị lớn nhất thỏa mãn 1 điều kiện.

– Ví dụ: tại ô J21 cần tính ta gõ vào công thức sau: =Maxifs(F3:F14, E3:E14, 12). Kết quả: 90.

– Hàm Maxifs có dạng: =Maxifs(Vùng cần tính, Vùng xem xét 1,điều kiện 1, [vùng xem xét 2],[điều kiện 2])

– Trong đó:

• Vùng cần tính: là vùng xét lấy giá trị lớn nhất.
• Vùng xem xét 1 và điều kiện 1: cặp khối ô được xem xét thỏa điều kiện thứ 1 (bắt buộc).
• Vùng xem xét 2, điều kiện 2: cặp khối ô được xem xét thỏa điều kiện thứ 2 (không bắt buộc có).

– Tương tự như hàm Maxifs, hàm Minifs có dạng:

=Minifs(Vùng cần tính, Vùng xem xét 1,điều kiện 1, [vùng xem xét 2],[điều kiện 2])

– Ví dụ cần tìm thí sinh Nam có điểm thi thấp nhất ta gõ công thức sau:

=Minifs(F3:F14, D3:D14, “Nam”).

3. QUÁ TRÌNH THỰC HIỆN

– Phương pháp học tập:

• Học sinh thực hiện tạo Bảng tính như mẫu Hình, thực hiện lần lượt các tính toán, thống kê theo trình tự: các cặp hàm counta và countif, countifs; cặp hàm sumif và averageif, cặp hàm maxifs và minifs.
• Làm thêm bài tập SGK phần thực hành để hiểu rõ hơn về dạng câu hỏi yêu cầu để tìm ra công thức phù hợp.

– Tài liệu tham khảo: SGK Chuyên đề học tập Tin học ứng dụng lớp 10 (Cánh diều), tài liệu Học sinh tra cứu trên Internet,…

 – Các bước thực hiện cho học sinh:

• Tham khảo Bài hướng dẫn chuyên đề, SGK, tài liệu …
• Tạo bảng tính mẫu theo yêu cầu
• Thực hiện lại các công thức đã hướng dẫn
• Thực hành các bài tập cuối bài tại SGK Chuyên đề học tập Tin học ứng dụng lớp 10 (Cánh diều) trang 55.

4. KẾT QUẢ ĐẠT ĐƯỢC

– Vận dụng được một số hàm tính toán theo điều kiện để tạo ra các dữ liệu thống kê, phục vụ cho các dự án học tập cho các em trong tương lai.

5. BÀI HỌC KINH NGHIỆM

– Học sinh dễ dàng thực hiện được các bài toán thống kê trong Excel; có thể vận dụng, kết hợp vào các bài toán phức tạp khác để đạt được hiệu quả trong các dự án học tập của mình cũng như có thể tự tạo ra các chương trình tính toán, tạo bảng tính mẫu để sử dụng theo nhu cầu.

6. TÀI LIỆU THAM KHẢO

– Sách giáo khoa Chuyên đề học tập Tin học ứng dụng lớp 10 (Cánh diều).

  Minh Kha