Bài 3. Hàm điều kiện IF (tiếp theo)
Câu E3.14
Cho bảng dữ liệu hình 1, hãy thực hiện các yêu cầu sau:
1)Tại ô D3 nhập công thức có hàm IF lồng nhau như sau:
=IF(C3>=30, 150000, IF(C3>=15, 180000, 200000))
Xác định kết quả trả về tại ô D3.
2) Nếu sao chép công thức ô D3 vào các ô trong khối D4thì kết quả nhận được tại các ô trong khối này là gì?
Lời giải chi tiết:
Kết quả thực hiện:
Câu E3.15
Cho bảng dữ liệu về doanh số bán hàng như Hình 2:
1) Cột Giảm giá được tính giá trị theo quy tắc: Nếu số lượng từ 15 trở lên thì giá trị giảm 5%; Nếu số lượng từ 10 đến 15 thì giá trị giảm 5%; Nếu không giảm giá. Hãy điền vào cột giảm giá trong các cột có hàm IF lồng nhau sau: =IF(C3>=15,..., IF(C3>=10,...,...))
2) Ô F9 điền tổng thành tiền theo quy tắc: Nếu doanh số là 300 triệu thì không tính đến dưới 10 triệu VNĐ; Nếu tổng thành tiền dưới 200 triệu VNĐ thì giảm 5% và tổng thành tiền từ 200 triệu VNĐ trở lên thì giảm 10%. Hãy điền vào cột Thưởng thành tiền từ cột tổng cộng.
=IF(F8>=..., 10, IF(F8>=..., 5, 0))
Lời giải chi tiết:
1. Cột Giảm giá:
=IF(C3>=15, 0.05*D3, IF(C3>=10, 0.02*D3, 0))
2. Cột F9: Thường doanh số và Thành tiền:
=IF(F9>=10*10^6, F9*0.02, IF(F9>=5*10^6, F9*0.01, 0))
Câu E3.16
Cho bảng điểm môn học và quy tắc quy đổi điểm số sang điểm chữ như hình 3:
1.Nếu sử dụng các hàm IF lồng nhau để điền điểm chữ cho các ô trong khối ô D3, ta cần sử dụng bao nhiêu hàm IF lồng nhau? Vì sao?
2. Hoàn thành công thức có các hàm IF lồng nhau tại ô D3 để điền điểm chữ cho học sinh đầu tiên:
=IF(C3=10, "A", IF(C3>=8, "B", IF(C3>=6, "C", IF(C3>=4, "D", "F"))))
3. Theo một cách làm khác, hoàn thành công thức có các hàm IF lồng nhau tại ô D3 để điền điểm chữ cho học sinh đầu tiên:
=IF(C3>=9, "F", IF(C3>=8, "D", IF(C3>=6, "C", IF(C3>=4, "B", "A"))))
Lời giải chi tiết:
1.Sử dụng bốn hàm IF lồng nhau vì có năm loại điểm chữ và mỗi điểm hệ 10 tương ứng với một trong năm loại điểm chữ này:
=IF(C3>=8.5, "A", IF(C3>=7.5, "B", IF(C3>=5.5, "C", IF(C3>=3.4, "D", "F"))))
2.Bốn chỗ chấm cần điền lần lượt là 8.5, 7.5, 5.5, 3.4 để được công thức là:
=IF(C3>=8.5, "A", IF(C3>=7.5, "B", IF(C3>=5.5, "C", IF(C3>=3.4, "D", "F"))))
3.Bốn chỗ chấm cần điền lần lượt là 4, 5.5, 7.5, và 8.5 để được công thức là:
=IF(C3>=4, "F", IF(C3>=5.5, "D", IF(C3>=7.5, "C", IF(C3>=8.5, "B", "A"))))
Câu E3.17
Cho danh sách xét học bổng và quy tắc xếp loại học bổng như Hình 4:
Trong đó, kết quả học tập và rèn luyện được xếp loại như sau: A, B, C.
1. Để điền giá trị cột Loại học bổng cho khối E3, cần sử dụng bao nhiêu hàm IF lồng nhau để đáp ứng các điều kiện? Vì sao?
2. Biểu thức điều kiện AND(C3="Xuất sắc", D3="Xuất sắc") có giá trị là gì? Nếu ý nghĩa của biểu thức này.
3. Biểu thức điều kiện OR(C3="Khá", D3="Khá") có giá trị là gì? Nếu ý nghĩa của biểu thức này.
4. Sử dụng hai biểu thức điều kiện trong yêu cầu 2) và 3) để lập công thức hàm IF lồng nhau hoàn chỉnh vào cột Loại học bổng cho khối E3.
5. Phân tích cách thực hiện và kết quả sau sao chép công thức có chứa hàm IF lồng nhau đã lập tại yêu cầu 4) xuống các ô trong khối E4.
Lời giải chi tiết:
1. Ta cần sử dụng 3 hàm IF lồng nhau. Vì có 3 mức xếp loại học bổng (A, B, C) nên cần có 3 điều kiện để kiểm tra từng mức này. Nếu điều kiện đầu tiên không đúng thì tiếp tục kiểm tra điều kiện thứ hai và cuối cùng là điều kiện thứ ba.
2. Biểu thức này có giá trị là TRUE nếu cả hai điều kiện C3 là "Xuất sắc" và D3 là "Xuất sắc" đều đúng. Ý nghĩa của biểu thức này là kiểm tra xem học sinh có đạt loại "Xuất sắc" cả về học tập (C3) và rèn luyện (D3) hay không.
3. Biểu thức này có giá trị là TRUE nếu ít nhất một trong hai điều kiện C3 là "Khá" hoặc D3 là "Khá" đúng. Ý nghĩa của biểu thức này là kiểm tra xem học sinh có đạt loại "Khá" ít nhất ở một trong hai tiêu chí học tập (C3) hoặc rèn luyện (D3) hay không.
4. Công thức lập được là:
=IF(AND(C3>=“Xuất sắc”, D3="Xuất sắc"), "A", IF(OR(C3="Khá", D3="Khá"), "C", "B"))
5. Khi sao chép công thức xuống các ô E4, Excel sẽ tự động điều chỉnh các tham chiếu ô (C3, D3) theo hàng tương ứng. Kết quả sau khi sao chép sẽ điền loại học bổng chính xác cho mỗi học sinh dựa trên các giá trị ở cột C và D.
Câu E3.18
(Thực hành): Với bảng dữ liệu nhập hàng như Hình 2 trong Bài E3.5, hãy thực hiện các yêu cầu sau:
1.Tính giá trị cột Thành tiền theo quy tắc:
Thành tiền = Đơn giá * Số lượng
2.Tính giá trị cột Chiết khấu theo quy tắc:
Mặt hàng có số lượng từ 40 trở lên thì chiết khấu 15% của Thành tiền; Nếu số lượng từ 20 đến dưới 40 thì chiết khấu 10% của Thành tiền; Nếu số lượng từ 10 đến dưới 20 thì chiết khấu 5% của Thành tiền; Còn lại không chiết khấu.
3.Thêm một cột Khuyến mãi tháng ngay bên phải cột Chiết khấu và tính giá trị theo quy tắc:
Nếu mặt hàng có số lượng từ 30 trở lên thì Khuyến mãi tháng bằng 10% của Thành tiền.
Nếu mặt hàng quá có số lượng từ 50 trở lên thì Khuyến mãi tháng bằng 15% của Thành tiền.
Còn lại Khuyến mãi tháng bằng 0.
4.Tính giá trị cột Tiền thanh toán theo quy tắc:
Tiền thanh toán = Thành tiền - Chiết khấu - Khuyến mãi tháng.
Lời giải chi tiết:
Hướng dẫn:
1.Tính giá trị cột Thành tiền theo quy tắc:
Thành tiền = Đơn giá * Số lượng. Ví dụ, nếu ô đơn giá là B2 và số lượng là C2, công thức sẽ là: =B2*C2
2.Tính giá trị cột Chiết khấu theo quy tắc:
Sử dụng hàm IF để tính chiết khấu dựa trên số lượng:
=IF(C2>=40, B2*C2*0.15, IF(C2>=20, B2*C2*0.10, IF(C2>=10, B2*C2*0.05, 0)))
3.Thêm một cột Khuyến mãi tháng ngay bên phải cột Chiết khấu và tính giá trị theo quy tắc:
Sử dụng hàm IF để tính khuyến mãi tháng:
=IF(C2>=50, B2*C2*0.15, IF(C2>=30, B2*C2*0.10, 0))
4.Tính giá trị cột Tiền thanh toán theo quy tắc:
Tiền thanh toán = Thành tiền - Chiết khấu - Khuyến mãi tháng. Công thức sẽ là:
=B2*C2 - D2 - E2
Câu E3.19
(Thực hành): Hình 5 là kết quả hội diễn văn nghệ chào mừng 20/11 – Bảng A.
Hãy thực hiện lần lượt các yêu cầu sau:
1.Tạo bảng tính, định dạng và nhập dữ liệu như hình 5.
2.Tính giá trị cột Tổng điểm theo quy tắc:
Tổng điểm = (GK2 + KG3) / 3
Trong đó, GK là điểm trung bình cộng của 5 giám khảo tại các ô từ GK1 đến GK5, lấy giá trị điểm tại cột Khán giả. Kết quả Tổng điểm làm tròn đến một chữ số phần thập phân.
3.Điền giá trị cột Xếp giải theo quy tắc:
Ghi "A" nếu tổng điểm từ 80 trở lên; Ghi "B" nếu tổng điểm từ 70 đến dưới 80; Ghi "C" nếu tổng điểm từ 60 đến dưới 70; Còn lại để trống (nghĩa là không xếp giải).
Lời giải chi tiết:
Hướng dẫn:
1.Tạo bảng tính, định dạng và nhập dữ liệu như hình 5.
2.Tính giá trị cột Tổng điểm theo quy tắc:
=ROUND((GK2 + KG3) / 3, 1)
3.Điền giá trị cột Xếp giải theo quy tắc:
=IF(GK2>=80, "A", IF(GK2>=70, "B", IF(GK2>=60, "C", "")))
Câu E3.20
(Thực hành) Hình 6 là bảng dữ liệu sử dụng để tính lương tháng cho các nhân viên của một công ty.
Hãy thực hiện lần lượt các yêu cầu sau:
1.Tạo bảng tính, định dạng và nhập dữ liệu như Hình 6.
2.Điền giá trị cột Hệ số theo quy cho từ C14.
3.Điền giá trị cột Lương tháng theo quy tắc:
Lương tháng = Số ngày công * Lương ngày * Hệ số
Trong đó, Lương ngày là giá trị từ cột G13.
4.Điền giá trị cột Phụ cấp tương ứng với chức vụ cho trong khối từ C14.
5.Tính giá trị cột Tiền thưởng theo quy tắc:
Nếu số ngày công từ 25 ngày trở lên thì thưởng 1.000.000 VND;
Nếu số ngày công từ 20 đến dưới 25 ngày thì thưởng 500.000 VND;
Còn lại không có tiền thưởng.
6.Tính giá trị cột Thực lĩnh theo quy tắc:
Thực lĩnh = Lương tháng + Phụ cấp + Tiền thưởng.
Phương pháp giải:
Học sinh tự thực hành theo yêu cầu.