Giáo án điện tử chuyên đề Tin học ứng dụng 10 cánh diều Bài 2: Các hàm tính toán theo điều kiện

CHÀO MỪNG CẢ LỚP

ĐẾN BUỔI HỌC NÀY!

KHỞI ĐỘNG

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ụ như tính điểm trung bình môn Tiếng Anh của các bạn nam trong lớp. Em hãy nêu thêm một vài tình huống khác.

Một số tình huống khác cần tính toán theo điều kiện

trên một bảng dữ liệu là:

Tính tổng tiền quyên góp được của HS khối 10.

Tìm bạn nam có điểm thi môn Toán cao nhất.

Tính TB tốc độ chạy của các bạn nữ lớp 10A trong cuộc thi chạy tiếp sức.

Tính kết quả thi tuyển sinh, biết các thí sinh có mã khu vực khác nhau sẽ được cộng điểm ưu tiên khác nhau.

BÀI 2:

CÁC HÀM TÍNH TOÁN

THEO ĐIỀU KIỆN

NỘI DUNG BÀI HỌC

Hàm đếm số ô tính

Hàm tính tổng và tính trung bình cộng

Hàm lấy giá trị lớn nhất, giá trị nhỏ nhất

Thực hành

1

3

2

4

Hàm đếm số ô tính

1

1. Hàm đếm số ô tính

Em hãy thực hiện các yêu cầu sau:

1) Tạo trang tính chứa dữ liệu kết quả cuộc thi giọng hát hay toàn trường (Hình 1).

2) Tại ô tính E16 nhập công thức =COUNTIF(E3:E14, 10). Sau đó lần lượt thay giá trị 10 trong công thức này bằng các giá trị 11 và 12. Em hãy nhận xét về giá trị trả về trong mỗi trường hợp.

3) Tại ô tính D16, em hãy lập công thức để tính số thí sinh “Nam” dự thi.

Hoạt động 1

Hình 1. Kết quả cuộc thi giọng hát hay

1. Hàm đếm số ô tính

Ý nghĩa hàm COUNT: đếm số lượng ô tính chứa dữ liệu dạng số trong một khối ô.

1. Hàm đếm số ô tính

a) Hàm COUNTA

Ý nghĩa: đếm số lượng ô tính có chứa dữ liệu trong một khối ô.

b) Hàm COUNTIF

Ý nghĩa: đếm số lượng các ô tính thỏa mãn một điều kiện.

Cú pháp: COUNTIF(Vùng_xem_xét, Điều_kiện)

Khối ô được xem xét.

Có thể là số, xâu, biểu thức logic hay địa chỉ ô tính. Nếu điều kiện cần đáp ứng là địa chỉ của một ô tính, hàm sẽ sử dụng giá trị hiện tại của ô tính.

b) Hàm COUNTIF

Hình 1. Kết quả cuộc thi giọng hát hay

Hình 2. Ví dụ sử dụng hàm COUNTIF với điều kiện cần đáp ứng là địa chỉ một ô tính

c) Hàm COUNTIFS

Dùng để đếm số lượng ô tính thỏa mãn nhiều hơn một điều kiện.

Hình 1. Kết quả cuộc thi giọng hát hay

c) Hàm COUNTIFS

Cú pháp:

COUNTIFS (Vùng_xem_xét_1, Điều_ kiện_1

[, Vùng_xem_xét_2, Điều_kiện_ 2,...])

Cặp khối ô được xem xét và điều kiện cần đáp ứng đầu tiên (bắt buộc phải có).

Các cặp khối ô được xem xét và điều kiện cần đáp ứng bổ sung (không bắt buộc phải có). Các khối ô bổ sung phải có cùng số hàng và số cột với vùng xem xét đầu tiên.

Lập công thức để:

• Đếm số cầu thủ đội Pháp có trong danh sách.
• Đếm số cầu thủ đội Pháp ghi nhiều hơn 5 bàn thắng.

Ví dụ

Ví dụ

Lập công thức để:

• Đếm số sản phẩm có điểm sáng tạo trên 20.
• Đếm số sản phẩm có tổng điểm trên 80.

Hàm tính tổng và

tính trung bình cộng

2

2. Hàm tính tổng và tính trung bình cộng

Hoạt động 2

Trên bảng dữ liệu thu được từ Hoạt động 1, tại hai ô tính còn trống cạnh nhau, em hãy lần lượt gõ các công thức sau:

=AVERAGE(F3:F14)

=AVERAGEIF(E3:E14, 10, F3:F14)

Em hãy so sánh và nhận xét ý nghĩa của từng kết quả nhận được.

2. Hàm tính tổng và tính trung bình cộng

Ý nghĩa: Các hàm SUMIF và AVERAGEIF được sử dụng để tính tổng và tính trung bình cộng của các ô tính trong một khối ô thỏa mãn một điều kiện nào đó.

2. Hàm tính tổng và tính trung bình cộng

2. Hàm tính tổng và tính trung bình cộng

Khối ô E3:E14 được xem xét để tìm những hàng có giá trị 10 (các hàng: 3, 5, 12), còn khối ô F3:F14 được sử dụng để tính tổng giá trị tại các hàng tìm được.

Giải thích công thức:

2. Hàm tính tổng và tính trung bình cộng

Giải thích công thức:

Khối ô F3:F14 được xem xét để tìm những hàng có giá trị lớn hơn hoặc bằng 80 (các hàng: 4, 7, 8, 10, 12) rồi tính trung bình cộng những giá trị tại các hàng tìm được trong khối F3:F14.

SUMIF(Vùng_xem_xét, Điều_kiện [,Vùng_tính])

AVERAGEIF(Vùng_xem_xét, Điều _kiện [,Vùng_tính])

Vùng_xem_xét: Khối ô được xem xét.

Điều_kiện: Có thể là số, xâu, biểu thức logic, địa chỉ ô tính, hàm sẽ sử dụng giá trị hiện tại của ô tính.

Vùng_tính: Đây là địa chỉ khối ô sẽ được sử dụng để tính tổng hoặc tính trung bình cộng (có thể không có).

Cú pháp

Lập công thức để:

• Đếm tổng số bàn thắng đội tuyển Pháp ghi được.
• Tính tổng số bàn thắng ghi được của các cầu thủ đội tuyển Anh chơi nhiều hơn 10 trận.

Ví dụ

Hàm lấy giá trị lớn nhất,

giá trị nhỏ nhất

3

3. Hàm lấy giá trị lớn nhất, giá trị nhỏ nhất

Hoạt động 3

Trên bảng dữ liệu thu được từ Hoạt động 1, tại hai ô tính còn trống cạnh nhau, em lần lượt gõ các công thức sau:

=MAX(F3:F14)

=MAXIFS(F3:F14, D3:D14, "Nữ")

Em hãy so sánh và nhận xét ý nghĩa của từng kết quả nhận được.

3. Hàm lấy giá trị lớn nhất, giá trị nhỏ nhất

Ý nghĩa: Hàm MAXIFS và MINIFS được sử dụng để tính giá trị lớn nhất và giá trị nhỏ nhất trong các ô tính thỏa mãn các điều kiện nào đó.

Em hãy trình bày cú pháp của các hàm MAXIFS, MINIFS và giải thích ý nghĩa cho một số ví dụ trong SGK.

3. Hàm lấy giá trị lớn nhất, giá trị nhỏ nhất

Khối ô E3:E14 được xem xét để tìm những hàng có giá trị 12 (các hàng 4, 7, 8, 11, 14), còn khối ô F3:F14 được sử dụng để tìm giá trị nhỏ nhất tại cột F (cột "Điểm thi") tương ứng với các hàng tìm được.

Giải thích công thức:

MAXIFS/MINIFS(Vùng_tính, Vùng_xem_xét_1,Điều_kiện_1[,Vùng_xem_xét_2, Điều_kiện_2,...])

Khối ô được sử dụng để xác định giá trị lớn nhất, nhỏ nhất.

Cặp khối ô được xem xét và điều kiện cần thỏa mãn đầu tiên (bắt buộc phải có).

Cặp khối ô được xem xét và điều kiện cần thỏa mãn bổ sung (không bắt buộc phải có).

Cú pháp

Thực hành

4

4. Thực hành

Nhiệm vụ nhóm:

• Dựa vào bảng tính trong Hoạt động 1, em hãy lập công thức để tính:
• 1) Số thí sinh nam khối 10 dự thi có điểm thi lớn hơn 75.
• 2) Điểm trung bình của các thí sinh khối 12.
• 3) Kết quả thi cao nhất, thấp nhất của các thí sinh nam khối 10.

Gợi ý

1) = COUNTIFS(D3:D4, "Nam", E3: E14, 10, F3:F14, ">75")

2) = AVERAGEIF(E3:E4,12, F3:F14)

3) = MAXIFS (F3:F14, D3:D14, "Nam", E3:E14, 10)

= MINIFS (F3:F14, D3:D14, "Nam", E3:E14, 10)

LUYỆN TẬP

Luyện tập (SGK t.r55)

Em hãy xây dựng bảng dữ liệu kết quả kiểm tra chất lượng đầu năm của các bạn trong lớp (gồm các môn: Toán, Ngữ văn, Ngoại ngữ). Từ bảng dữ liệu này, em hãy thống kê những thông tin sau:

1) Số lượng các bạn nam có điểm thi cả ba môn đều từ 8.0 trở lên.

2) Điểm TB môn Ngữ văn của các bạn nữ và điểm TB môn Toán của các bạn nam.

3) Điểm TB môn Ngoại ngữ của các bạn có điểm môn Toán từ 8.0 trở lên.

--------------- Còn tiếp ---------------