NGHE11_Bai26_SungDungCacHamLogic

Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
1
BÀI 26.
SỬ DỤNG CÁC HÀM LÔGIC
PHẦN 4. CHƯƠNG TRÌNH BẢNG TÍNH EXCEL

Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
2
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
I. Ví dụ về tính toán có điều kiện
Ta thường thực hiện các tính toán khác nhau tùy thuộc vào một điều kiện nào đó có được thoản mãn hay không. Xét các ví dụ sau đây:
1. Ví dụ 1: Giải phương trình bậc 2, nếu <0 kết luận phương trình vô nghiệm, ngược lại tính các nghiệm thông qua hệ số của trình.
2. Ví dụ 2: Trở lại với bài thực hành 2 trong bài 25, giả sử chúng ta tính thuế xuất khẩu của các doanh nghiệp phụ thuộc vào giá trị xuất khẩu. Với điều kiện thuế xuất khẩu bằng 10% giá trị xuất khẩu nếu giá trị đó lớn hơn $1.000.000
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
3
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Có tính thuế XK
không tính thuế XK
Hay thuế XK=0%
Giá trị XK tại ô D4=$1500000, do đó thuế XK tại ô E4=D4*10%
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
4
Xét cách tính thuế xuất khẩu trong ô E4 ở ví dụ trên như sau:
Điều kiện để tính thuế xuất khẩu: Giá trị xuất khẩu ≥ một triệu đô la (D4>=$1.000.000);
Trường hợp 1: Nếu Giá trị xuất khẩu ≥ một triệu đo la (điều kiện được thỏa mãn), công thức tính thuế là: Thuế xuất khẩu=Giá trị xuất khẩu x 10% (E4=D4*10%);
Trường hợp 2: Nếu Giá trị xuất khẩuBÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Tuy nhiên, nếu dữ liệu trong ô D4 thay đổi (giả sử trong tháng tiếp theo giá trị xuất khẩu không đạt $1.000.000), kết quả tính trong ô E4 sẽ không còn đúng nữa.
850000
Giả sử chỉ đạt 850000
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
5
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Như vậy chúng ta phải sử dụng 2 công thức khác nhau tùy theo điều kiện có được thỏa mãn hay không.
Để không sửa đổi công thức tính toán, ta sử dụng hàm lôgic (hàm IF). Với ví dụ trên, tại ô E4 ta có công thức:
=IF(D4>=10^6, D4*10%, D4*0%)
Hay
=IF(D4>=10^6, D4*10%, 0)
Hàm này thực hiện như sau: trước hết điều kiện D4>=10^6 được kiểm tra; nếu điều kiện này được thỏa mãn, chương trình sẽ sử dụng công thức =D4*10% để tính giá trị trong ô E4; ngược lại công thức =D4*0% được sử dụng
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
6
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Ví dụ: giải phương trình bậc hai ax2 +bx+c=0 vô nghiệm nếu =b2 -4ac < 0, ngược lại phương trình có nghiệm. Chúng ta sử dụng hàm IF để thử nhanh phương trình có nghiệm hoặc vô nghiệm tại cột D với các hệ số a, b, c khác nhau.
Trong ví dụ này, điều kiện cần kiểm tra là b2-4ac < 0
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
7
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Trong ví dụ này, điều kiện cần kiểm tra là b2-4ac < 0
Nếu điều kiện thỏa mãn, trong ô tương ứng và trên cùng hàng cần hiển thị nội dung Vô nghiệm;
Ngược lại, nội dung Có nghiệm sẽ được điền vào ô tương ứng.
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
8
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Nhập hàm sau đây vào ô D5:
=IF(B5^2-4*A5*C5<0,"Vô nghiệm","Có nghiệm")
Sau đó kéo thả nút điền xuống các ô phía dưới, kết quả như hình bên dưới.
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
9
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
II. Sử dụng hàm IF
Qua hai ví dụ trên ta thấy rằng có thể sử dụng hàm IF để:
Thực hiện tính toán với 2 công thức khác nhau, phụ thuộc vào việc thỏa mãn hay không thỏa mãn một điều kiện nhất định nào đó;
Điều kiện được phát biểu dưới dạng một phép so sánh có thể nhận được một trong hai giá trị: đúng (khi đk được thỏa mãn) hoặc sai (khi đk không được thỏa mãn).
Cú pháp cho hàm IF như sau:
=IF(Phep_so_sanh, Gia_tri_dung, Gia_tri_sai)
Hàm IF tính Gia_tri_dung khi Phep_so_sanh có giá trị đúng (đk thỏa mãn) và tính Gia_tri_sai khi Phep_so_sanh có giá trị sai.
Gia_tri_dung và Gia_tri_sai có thể là dữ liệu số, dãy kí tự, địa chỉ một ô, công thức,…
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
10
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Ví dụ về phep_so_sanh:
A1>5: so sánh nội dung ô A1 có lớn hơn 5 hay không;
A1A1=“Hà Nội”: Nội dung ô A1 có phải dãy kí tự Hà Nội hay không?.
Ngoài các phép so sánh =, <, >, còn có thể sử dụng các phép so sánh sau đây:
<=: nhỏ hơn hoặc bằng.
>=: lớn hơn hoặc bằng.
<>: không bằng (khác).
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
11
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Ví dụ 3, học sinh thực hành: học sinh làm ví dụ
Lưu ý:
Thứ tự liệt kê trong hàm IF là quan trọng.
Dãy kí tự được bao quanh bởi dấu nháy kép (“ “). Ví dụ: A1=“A”,…
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
12
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
III. Sử dụng các hàm IF lồng nhau
Trong các ví dụ trên, hàm IF chỉ thực hiện tính toán với 2 công thức khác nhau bằng 1 phép so sánh. Trong thực tế, nhiều tình huống không thể so sánh được bằng một điều kiện đơn giản. Chẳng hạn với ví dụ 3 ở trên, nếu không chỉ có hai mà có ba mã ưu tiên là A, B và C thì với một phép so sánh, chỉ có thể khẳng định mã ưu tiên là A hoặc không phải A mà thôi.
Để giải quyết những trường hợp đó cần tách điều kiện thành các nhóm nhỏ hơn và sử dụng nhiều hàm IF lồng nhau.
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
13
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Ví dụ 4: thêm Mã ưu tiên C ở ví dụ 3 và cộng thêm 2 nếu Mã ưu tiên là C.
Nếu Mã ưu tiên=A thì Điểm xét tuyển=Điểm thi+4
Nếu không, so sánh tiếp:
Nếu Mã ưu tiên=C thì Điểm xét tuyển=Điểm thi+2;
ngược lại: Điểm xét tuyển = Điểm thi
=IF(D5=“A”, E5+4, IF(D5=“C”, E5+2, E5))
Ví dụ khác: Ô A1 trên trang tính chứa dữ liệu là điểm thi của 1 học sinh (không nhỏ hơn 5). Nếu điểm đó cao hơn 8.5 thì ta ghi vào ô B1 đánh giá “Giỏi”, từ 7 đến 8 là “Khá” và dưới 7 điểm là “Trung bình”. Hỏi: công thức nhập vào ô nào?. Công thức đó là gì?
Ô B1= IF(A1<7, “Trung bình”, IF(A1>=8.5, “Giỏi”, “Khá”))
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
14
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
IV. Hàm SUMIF
Hàm SUMIF là một dạng nâng cao của hàm IF. Hãy xét ví dụ sau đây:
Ví dụ 5:
- Cột D cho biết có 3 loại vé, cột F cho biết tiền bán vé trong 2 ngày
Tính tổng số tiền bán từng loại vé, ví dụ loại A, cần sử dụng công thức =F11+F14 và tương tự đối với các loại B và M.
Nếu trang tính có dữ liệu bán vé của 30 ngày thì công thức tính sẽ gồm 30 số hạng!.
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
15
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Sử dụng hàm SUMIF để giải quyết vấn đề này. Với ví dụ trên để tính tiền bán vé loại A có thể sử dụng công thức sau:
=SUMIF(D11:D16, ”A”, F11:F16)
Dạng đơn giản nhất của hàm SUMIF có dạng như sau:
=SUMIF(Cot_so_sanh, Tieu_chuan, Cot_lay_tong)
Trong đó: Cot_so_sanh: là một khối (trên 1 cột) có các ô có dữ liệu cần so sánh.
Tieu_chuan: là tiêu chuẩn so sánh
Cot_lay_tong: là khối (trên 1 cột) có các ô tương ứng cần lấy tổng
Hàm SUMIF sẽ cộng dữ liệu ở các ô trong Cot_lay_tong trên các hàng tương ứng với các ô thỏa mãn Tieu_chuan trong Cot_so_sanh.
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
16
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC
Lưu ý:
Trừ giá trị số, các tiêu chuẩn khác phải cho trong cặp dấu nháy kép (“ “). Ví dụ: 32, “>32”, “Hà Nội”, …
Cot_so_sanh và Cot_lay_tong phải có cùng dạng cọt. Có thể thay cả 2 bằng cùng dạng hàng
Người soạn: Nguyễn Thanh Hải
Trường Văn Hóa II
17
BÀI 26. SỬ DỤNG CÁC HÀM LÔGIC