Giải bài tập Chuyên đề Tin học 10 Bài 2: Các hàm tính toán theo điều kiện
Khởi động trang 50 Chuyên đề Tin học 10: 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. Em hãy nêu thêm một vài tình huống khác.
Trả lời:
Cần đưa ra các bạn có số điểm toán lớn hơn 8, đếm số các bạn có bạn có điểm trung bình lớn hơn 7.0, …
1. Hàm đếm số ô tính
Hoạt động 1 trang 50 Chuyên đề Tin học 10: 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í sainh “Nam” dự thi
Trả lời:
1) Mở phần mềm Excel tạo bảng tính như Hình 1
2) Tại ô tính E16 nhập công thức =COUNTIF(E3:E14,10). Kết quả
Vì hàm =COUNTIF(E3:E14,10) là đếm ở cột E từ hàng 3 đến hàng 14 những thí sinh ở khối 10 → có 3 thí sinh ở khối 10
Sau đó lần lượt thay giá trị 10 trong công thức này bằng các giá trị 11: =COUNTIF(E3:E14,10)
Có 4 thí sinh thuộc khối 11
Tương tự khi gõ hàm =COUNTIF(E3:E14,12) ở ô E16 có kết quả là 5
3) Tại ô tính D16 ta gõ công thức =COUNTIF(D3:D14,”Nam”)
2. Hàm tính tổng và tính trung bình cộng
Hoạt động 2 trang 52 Chuyên đề Tin học 10: 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)
=AVERAGE(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.
Trả lời:
Tại ô F16 ta gõ công thức =AVERAGE(F3:F14), hàm AVERAGE trả về giá trị tính trung bình cộng với vùng tính là F3 đến F14.
Tại ô F17 ta gõ công thức =AVERAGE(E3:E14,10,F3:F14), hàm AVERAGE trả về giá trị tính trung bình cộng với vùng tính là F3 đến F14, điều kiện tính là những hàng từ E3 đến E14 thỏa mãn là học sinh khối 10
3. Hàm lấy giá trị lớn nhất, nhỏ nhất
Hoạt động 3 trang 54 Chuyên đề Tin học 10: 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)
=MAXIF(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.
Trả lời:
Trên bảng dữ liệu từ Hoạt động 1 tại ô tính F18 ta gõ công thức =MAX(F3:F14), hàm MAX trả về giá trị lớn nhất trong cột F từ hàng F3 đến hàng F14
Trên bảng dữ liệu từ Hoạt động 1 tại ô tính F18 ta gõ công thức =MAXIFS(F3:F14,D3:D14,”Nữ”), hàm MAXIFS trả về giá trị lớn nhất trong cột F từ hàng F3 đến hàng F14 với điều kiện ở cột D giới tính là “Nữ” → Kết quả là 90
4. Thực hành
Thực hành trang 55 Chuyên đề Tin học 10: Dựa trên 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.
Trả lời:
1) Trên bảng tính ở Hoạt động 1, ở ô tính trống ta gõ công thức =COUNTIFS(E3:E14,”10″,D3:D14,”Nam”,F3:F14,”>75″)
2) Trên bảng tính ở Hoạt động 1, ở ô tính trống ta gõ công thức
=AVERAGEIFS(F3:F14,E3:E14,”12″)
3) Trên bảng tính ở Hoạt động 1, để hiển thị kết quả thi cao nhất ở ô tính trống ta gõ công thức =MAXIFS(F3:F14,D3:D14,”Nam”,E3:E14,”10”)
Trên bảng tính ở Hoạt động 1, để hiển thị kết quả thi thấp nhất ở ô tính trống ta gõ công thức =MINIFS(F3:F14,D3:D14,”Nam”,E3:E14,”10”)
Luyện tập trang 55 Chuyên đề Tin học 10: 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 có điểm thi cả ba môn đều từ 8.0 trở lên.
2) Điểm trung bình môn Ngữ văn của các bạn nữ và điểm trung bình môn Toán của các bạn Nam.
3) Điểm trung bình môn Ngoại ngữ của các bạn có điểm môn Toán từ 8.0 trở lên.
Trả lời:
Xây dựng bảng tính kết quả kiểm tra chất lượng đầu năm của các bạn trong lớp
1) Để tính số lượng các bạn có điểm thi cả ba môn đều từ 8.0 trở lên, tại ô trống ta gõ công thức sau:
=COUNTIFS(E3:E14,”>=8″,F3:F14,”>=8″,G3:G14,”>=8″)
2) Để tính điểm trung bình môn Ngữ văn của các bạn nữ, tại ô trống ta gõ công thức sau:
=AVERAGEIFS(F3:F14,D3:D14,”Nữ”)
Để tính điểm trung bình môn Toán của các bạn Nam, tại ô trống ta gõ công thức sau:
=AVERAGEIFS(E3:E14,D3:D14,”Nam”)
3) Để tính điểm trung bình môn Ngoại ngữ của các bạn có điểm môn Toán từ 8.0 trở lên.
Tại ô tính trống ta gõ công thức sau:
=AVERAGEIFS(G3:G14,E3:E14,”>=8″)
Vận dụng trang 55 Chuyên đề Tin học 10: Ngày cuối tuần, lớp em tổ chức đi xem phim cùng nhau. Một số bạn muốn đi vào sáng chủ nhật, một số bạn muốn đi vào chiều chủ nhật, một số bạn khác có thể đi cả sáng hay chiều đều được. Em hãy lập một bảng cho các bạn đăng kí thời gian phù hợp và thống kê phương án nào có nhiều bạn lựa chọn nhất.
Trả lời:
Lập bảng tính cho các bạn đăng kí
Tại các ô trống lập bảng tính phụ bao gồm:
Các bạn đăng kí Sáng
Các bạn đăng kí chiều
Các bạn đăng kí cả sáng cả chiều
Thời gian đăng kí nhiều nhất
Tại các ô C15,C16,C17 lần lượt lập công thức đếm số các bạn đăng kí sáng, chiều, cả sáng và chiều, sau đó tính số đăng kí nhiều nhất. Công thức tại các ô lần lượt là:
Ô C15: =COUNTIF(D3:D12,”Sáng”)
Ô C16: =COUNTIF(D3:D12,”Chiều”)
Ô C17: =COUNTIF(D3:D12,”Sáng và chiều”)
Ô C18: =MAX(D13:D15)
Câu hỏi tự kiểm tra
Câu hỏi tự kiểm tra trang 55 Chuyên đề Tin học 10: Cho bảng dữ liệu về số lượng trái cây đã tiêu thụ như hình bên. Em hãy giải thích ý nghĩa các hàm được viết như sau:
1) =COUTNTIF(D2:D13,”>100”)
2)=COUTNTIFS(B2:B13,”Táo”,D2:D13,”>100”)
3)=SUMIF(B2:B13,”Táo”,D2:D13)
4)=MINIFS(D2:D13,B2:B13,”Táo”,C2:C13,”Pháp”)
Trả lời:
1) =COUTNTIF(D2:D13,”>100”)
Đếm các loại trái cây có số lượng lơn hơn 100
2)=COUTNTIFS(B2:B13,”Táo”,D2:D13,”>100”)
Đếm số lượng Táo có số lượng lớn hơn 100
3)=SUMIF(B2:B13,”Táo”,D2:D13)
Tính tổng số lượng Táo
4)=MINIFS(D2:D13,B2:B13,”Táo”,C2:C13,”Pháp”)
Hiển thị số lượng nhỏ nhất của Táo có xuất xứ từ Pháp