Sử dụng hàm IF của Excel khi bạn muốn kiểm tra điều gì đó và hiển thị một kết quả nếu kết quả kiểm tra là Đúng và một kết quả khác nếu kết quả kiểm tra là Sai. Trong Excel 365 còn có hàm IFS, có thể thay thế nhiều hàm IF lồng nhau
GHI CHÚ. Trong nhiều trường hợp, một hàm Excel khác sẽ hiệu quả hơn một công thức IF phức tạp. Nhìn thấy
Giới thiệu - Hàm IF
Hàm IF trong Excel rất hữu ích nếu bạn muốn kiểm tra điều gì đó trên trang tính, sau đó hiển thị kết quả A hoặc B. Video ngắn này trình bày các bước thiết lập công thức IF đơn giản. Có các bước được viết bên dưới
Dòng thời gian video
- 0. 00 Giới thiệu
- 0. 13 Làm bài kiểm tra
- 0. 39 Phiếu đặt hàng
- 0. 55 Công Thức NẾU Đơn Giản
- 1. 39 Kiểm Tra Công Thức
Hàm IF trong Excel hoạt động như thế nào
IF là một trong các hàm Logic trong Microsoft Excel và có 3 phần [đối số] trong cú pháp hàm IF
- kiểm tra logic. KIỂM TRA một cái gì đó, chẳng hạn như giá trị trong một ô
- value_if_true. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE
- value_if_false. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là SAI
Ví dụ 1. Kiểm tra ô trống
Trong ví dụ này, Tổng trong ô E7 sẽ chỉ hiển thị số tiền nếu Số lượng đã được nhập vào ô D7
Đây là 3 phần [đối số] của hàm IF cho ví dụ này
- kiểm tra logic. KIỂM TRA ô D7, để xem nó có trống không [D7=""],
- giá trị nếu đúng. Nếu D7 trống [TRUE], ô có công thức Tổng sẽ không hiển thị gì [""]
- giá trị nếu sai. Nếu D7 không trống [SAI], ô có công thức Tổng sẽ nhân Giá x Số lượng [C7*D7]
Công thức hoàn chỉnh là. =IF[D7="","",C7*D7]
Trong ảnh chụp màn hình bên dưới, ô D7 trống [kết quả TRUE], vì vậy công thức IF trong ô E7 cũng có vẻ trống
Khi ô D7 không trống [kết quả FALSE], phép tính Tổng ô hiển thị giá trị Giá x Số lượng
Ví dụ 2. Hàm IF lồng nhau
Nếu cần thực hiện nhiều kiểm tra logic trong một công thức, bạn có thể kết hợp nhiều hàm IF trong một công thức. Đây được gọi là công thức lồng nhau vì các hàm IF bổ sung hoạt động như các đối số, được lồng trong một hàm IF khác
Một bài kiểm tra logic
Trong Ví dụ 1, công thức IF có một bài kiểm tra logic -- ô Qty [D7] có trống không?
Hai bài kiểm tra logic
Để kiểm tra cả ô Giá và Số lượng, một hàm IF khác được thêm vào công thức hiện có
Điều đó tạo ra một bài kiểm tra logic thứ hai -- ô Giá [C7] có trống không?
- =IF[C7="","", IF[D7="","", C7*D7]
Kết quả cho các tình huống khác nhau được hiển thị trong ảnh chụp màn hình bên dưới
- Ở hàng 7, ô Giá trống, vì vậy ô Tổng cộng có một chuỗi trống [""]
- Ở hàng 8, ô Qty trống, vì vậy ô Tổng cộng có một chuỗi trống [""]
- Ở hàng 9, không có ô nào trống, vì vậy tổng giá được tính [giá x số lượng]
Thứ tự các hàm IF lồng nhau
Trong ví dụ về công thức IF lồng nhau trước đó, có 2 bài kiểm tra logic
- Ô Giá có trống không?
- Ô Qty có trống không?
Những bài kiểm tra đó đều khó như nhau
- Không quan trọng bài kiểm tra nào được thực hiện trước
- Kết quả công thức sẽ giống nhau, với các bài kiểm tra theo một trong hai thứ tự
Các bài kiểm tra khó như thế nào?
Trong một số trường hợp, các bài kiểm tra logic trong công thức IF lồng nhau có các mức độ khó khác nhau
Ví dụ: chúng ta cần xây dựng một công thức trong đó điểm sản phẩm được nhóm thành các loại xếp hạng sau
Danh mục Điểm sản phẩm Xếp hạng Điểm Kiểm tra1Tốt80 trở lên>=802Trung bình60 đến 79B2>=603Kémdưới 60=80,"Tốt",IF[B2>=60,"Trung bình","Kém"]]Điểm trong ô B2 là 86, vượt qua bài kiểm tra logic đầu tiên, vì vậy kết quả là "Tốt"
Kết quả sai. Kiểm tra dễ dàng hơn trước
Tuy nhiên, nếu kiểm tra "Trung bình" đầu tiên trong công thức, kết quả sẽ không chính xác
- =IF[B2>=60,"Trung bình",IF[B2>=80,"Tốt","Kém"]]
Điểm trong ô B2 là 86, vượt qua bài kiểm tra logic đầu tiên, vì vậy kết quả là "Trung bình"
Ghi chú về công thức IF lồng nhau
Dưới đây là một số điều quan trọng cần lưu ý khi xây dựng công thức IF lồng nhau
- Khi xây dựng các công thức IF lồng nhau, thứ tự liệt kê các phép thử có thể quan trọng. Xem phần bên dưới --
- Không lồng quá nhiều hàm IF vào một công thức, nếu không công thức sẽ khó xây dựng chính xác và khó duy trì theo thời gian
- Nhiều công thức IF lồng nhau có thể được thay thế bằng một giải pháp khác, chẳng hạn như công thức VLOOKUP hoặc INDEX và MATCH, để linh hoạt hơn. Xem ví dụ trong phần "", bên dưới
Ví dụ 3. Hàm IF có hộp kiểm
Trong ví dụ này, thuế bán hàng sẽ được áp dụng cho các đơn đặt hàng địa phương. Trên mẫu đơn đặt hàng, có một hộp kiểm được liên kết với ô G11
- Đối với các đơn đặt hàng địa phương, hãy nhấp vào hộp kiểm để thêm dấu kiểm và ô được liên kết hiển thị TRUE
- Nếu dấu kiểm bị xóa, ô được liên kết hiển thị FALSE
Công thức IF trong ô E12 kiểm tra ô được liên kết [G11]l, để xem ô đó có chứa "SAI" không. Nếu vậy, số tiền thuế bằng không
Nếu ô G11 không chứa "SAI", tổng phụ trong ô E10 được nhân với Thuế suất trong ô D12, để hiển thị số tiền thuế
=IF[G11=FALSE,0,E10*D12]
Hàm IFS - Cách hoạt động
Trong Excel 365 còn có hàm IFS, dùng thay cho nhiều hàm IF lồng nhau. Với chức năng IFS
- Bạn có thể kiểm tra nhiều điều kiện để xem chúng có ĐÚNG không
- Công thức trả về kết quả cho điều kiện ĐÚNG đầu tiên
Cú pháp hàm IFS
Hàm IFS có các đối số sau trong cú pháp của nó
- logic_test1. KIỂM TRA một cái gì đó, chẳng hạn như giá trị trong một ô
- value_if_true1. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE
- logic_test2. kiểm tra logic127. [tùy chọn] KIỂM TRA điều gì đó, chẳng hạn như giá trị trong một ô
- value_if_true2. value_if_true127. [Tùy chọn] Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE
Ghi chú. Mặc dù bạn có thể nhập tối đa 127 bài kiểm tra và các giá trị liên quan của chúng, nhưng Microsoft khuyên bạn không nên lồng quá nhiều điều kiện. Giới hạn số lượng bài kiểm tra logic chỉ còn một vài, nếu không sẽ khó xây dựng và duy trì công thức IFS
NẾU Ví dụ 1. Kiểm tra các ô trống
Trong ví dụ này, thay vì sử dụng công thức IF lồng nhau, đây là công thức IFS
Trong ô E9, công thức IFS sau đây kiểm tra 3 ô trong một biểu mẫu đặt hàng, để xem tên sản phẩm, giá và số lượng đã được nhập chưa
- =IFS[COUNTA[B9. D9]=3, C9*D9, B9="","", C9="","không có giá", D9="","không có số lượng"]
Đây là cách thức hoạt động của công thức IFS này
- Đầu tiên, hàm COUNTA đếm các giá trị trong ô B9. D9. Số đó có bằng 3 không?
- Nếu TRUE, nhân giá trị ô giá [C9] với số lượng [D9], để tính tổng giá
- Tiếp theo, ô B9 được kiểm tra xem nó có trống không. Hai dấu ngoặc kép đại diện cho một chuỗi rỗng
- Nếu TRUE, trả về một chuỗi rỗng
- Sau đó, ô C9 được kiểm tra xem nó có trống không
- Nếu TRUE, trả về chuỗi văn bản "không có giá"
- Cuối cùng, ô D9 được kiểm tra xem nó có trống không
- Nếu TRUE, trả về chuỗi văn bản "không có giá"
Dưới đây, bạn có thể xem kết quả cho công thức này, trong các ô E9. E12
- Trong ô E9, kết quả là tổng giá, vì cả 3 ô đều được điền
- Tại ô E10, hàm trả về chuỗi rỗng "", do thiếu tên sản phẩm
- Trong ô E11, kết quả là "không có giá", vì ô giá trống
IFS Ex2. Không có đối số value_if_false
Trong Excel 365, bạn có thể sử dụng hàm IFS, thay vì nhiều hàm IF lồng nhau
- kiểm tra nhiều điều kiện, để xem chúng có ĐÚNG không
- trả về kết quả cho điều kiện TRUE đầu tiên
Tuy nhiên, không giống như hàm IF, hàm IFS không có đối số cuối cùng, đối với value_if_false
Không có đối số đó, làm thế nào các công thức IFS có thể làm như sau?
- kiểm tra 1, value_if_true1,
- kiểm tra 2, value_if_true2,
- value_for_everything_else
Kiểm tra cuối cùng và giá trị
Như một giải pháp thay thế, để mô phỏng đối số value_if_false của hàm IF
- Sử dụng TRUE làm bài kiểm tra logic cuối cùng
- Sử dụng "giá trị cho mọi thứ khác" của bạn làm đối số "value_if_true" cuối cùng
Ví dụ: công thức này có 2 bài kiểm tra logic là Khá và Trung bình
Bất kỳ điểm nào khác, không vượt qua hai bài kiểm tra đầu tiên, sẽ được xếp hạng "Kém", bởi vì họ vượt qua bài kiểm tra cuối cùng
=IFS[
C9>=80,"Tốt",
C9>=60,"Trung bình",
THẬT, "Nghèo"
]
Hàm IF có phải là giải pháp tốt nhất không?
Câu hỏi. Nếu bạn cần kiểm tra xem một hoặc nhiều điều kiện logic là TRUE hay FALSE, sau đó hiển thị kết quả, hàm IF hay hàm IFS luôn là giải pháp tốt nhất để sử dụng?
Trả lời. Không, không thường. Nếu bạn cần làm bất cứ điều gì khác ngoài một phép kiểm tra Đúng hoặc Sai đơn giản, thì các hàm Excel khác sẽ cung cấp giải pháp hiệu quả hơn cho vấn đề
Ví dụ. Hế thống đánh giá
Dưới đây là một ví dụ, trong đó một hàm dễ thiết lập và duy trì hơn so với công thức hàm IF lồng nhau hoặc công thức hàm IFS [trong Excel 365]
Trong một số sách bài tập, bạn có thể có hệ thống xếp hạng, vì vậy bạn có thể thay đổi điểm số thành điểm chữ. Trong ví dụ hiển thị bên dưới, nếu một sản phẩm được kiểm tra và đạt số điểm từ 85 trở lên, sản phẩm đó sẽ nhận được 3 sao -- xếp hạng cao nhất
Công thức IF này, trong ô D2, có thể tính xếp hạng
- =IF[C2>=80,"***",IF[C2>=60,"**","*"]]
Hoặc, trong Excel 365, công thức IFS này có thể tính xếp hạng
- =IFS[C2>=80,"***",C2>=60,"**",C2>=0,"*"]
Tuy nhiên, trong cả hai công thức đó, các mức xếp hạng được mã hóa cứng vào công thức. Điều đó có thể gây ra vấn đề trong tương lai, nếu mức xếp hạng bị thay đổi
- Bạn sẽ phải tìm tất cả các công thức đó và thay đổi tất cả các số cấp độ xếp hạng
- Hoặc, nếu thêm nhiều mức xếp hạng, các công thức sẽ phải được viết lại
Thay vì sử dụng IF hoặc IFS, bạn có thể thiết lập bảng tra cứu xếp hạng, với điểm thấp nhất cho từng xếp hạng và nhóm xếp hạng
Sau đó, sử dụng công thức VLOOKUP này, trong ô D2, để tính xếp hạng
- =VLOOKUP[C2,RatingLookup,2,TRUE]
Trong tương lai, nếu mức xếp hạng thay đổi, chỉ cần thay đổi thông tin trong bảng RatingLookup
- Bạn sẽ KHÔNG phải tìm tất cả các công thức đó và thay đổi tất cả các số cấp độ đánh giá
- Hoặc, nếu thêm nhiều mức xếp hạng, các công thức sẽ KHÔNG phải viết lại
Thêm ví dụ
Dưới đây là một số ví dụ khác, trong đó các công thức khác hiệu quả hơn công thức hàm IF lồng nhau hoặc công thức IFS
- Chuyển đổi điểm của học sinh từ số sang chữ cái -- sử dụng hoặc INDEX/MATCH
- Chọn một mức lãi suất, dựa trên ngày giao dịch -- sử dụng
- Hiển thị một giá trị cụ thể thay vì lỗi -- sử dụng [Excel 2007 trở lên]
Công thức trong bảng Excel được đặt tên
Nếu bản ghi của bạn được lưu trữ trong Bảng Excel có tên, thì bạn có thể sử dụng tham chiếu có cấu trúc trong công thức của mình, thay vì tham chiếu ô thông thường
Ví dụ: công thức sau trong ô E7, để tính tổng giá trong hàng đó
Công thức này sử dụng tên cột của bảng, chẳng hạn như [Giá], thay vì tham chiếu ô thông thường, chẳng hạn như C7
Tham chiếu ô thông thường và Tham chiếu có cấu trúc
Dưới đây là một số lưu ý về tham chiếu bình thường và có cấu trúc
- Tham chiếu ô bình thường có địa chỉ ô, chẳng hạn như C7
- Tham chiếu có thể bao gồm một hoặc hai ký hiệu $, chẳng hạn như $C7, C$7, $C$7
- Dấu $ tạo tham chiếu tuyệt đối đến hàng hoặc cột [sẽ không thay đổi nếu công thức được sao chép sang vị trí khác]
- Không có dấu $, tham chiếu hàng hoặc cột là tương đối [có thể thay đổi nếu công thức được sao chép sang vị trí khác]
- Tham chiếu có cấu trúc có tên trường bên trong dấu ngoặc vuông, chẳng hạn như [Giá] hoặc [Số lượng]
- Tham chiếu có cấu trúc cũng có thể bao gồm tên bảng hoặc ký hiệu @, chẳng hạn như Table1[@Total]
Lấy tệp mẫu
Để làm theo các ví dụ trong hướng dẫn này, hãy tải xuống sổ làm việc IF và IFS mẫu. Tệp nén có định dạng xlsx và không chứa bất kỳ macro nào