Trong bài viết trước, chúng ta đã học cách sử dụng hàm if với số, văn bản và ngày tháng trong excel. tiếp tục bài viết này học excel trực tuyến sẽ hướng dẫn bạn cách sử dụng hàm if kết hợp với các hàm thông dụng khác.
Trong quá trình sử dụng excel, đôi khi bạn sẽ gặp phải những vấn đề phức tạp, bạn cần đánh giá nhiều điều kiện cùng lúc, đồng nghĩa với việc bạn phải xây dựng logic phức tạp hơn bằng cách sử dụng nhiều hàm if trong một công thức. Bài viết này sẽ chứa các ví dụ thực tế để dễ hiểu hơn, ngoài ra bạn cũng sẽ tìm hiểu các kiến thức cơ bản về hàm iferror và ifna!
.
cách sử dụng hàm if trong excel với nhiều điều kiện
Có hai loại hàm if đa điều kiện cơ bản: và và hoặc. do đó, hàm if của bạn phải nhúng và hoặc hoặc hàm tương ứng trong bài kiểm tra logic.
- and-function: Nếu kiểm tra logic của bạn chứa hàm and, microsoft excel trả về true nếu tất cả các điều kiện được đáp ứng; nếu không thì trả về false.
- hoặc hàm: trong trường hợp bạn sử dụng hoặc hàm trong kiểm tra logic, excel sẽ trả về true nếu đáp ứng bất kỳ điều kiện nào; ngược lại trả về false.
- Trong excel 2016, 2013, 2010 và 2007, công thức của bạn không bao gồm hơn 255 đối số và tổng độ dài của công thức không được vượt quá 8192 ký tự.
- Trong excel 2003 trở về trước, bạn có thể sử dụng tối đa 30 đối số và tổng độ dài công thức của bạn không được vượt quá 1024 ký tự.
Để dễ hiểu hơn, hãy xem một số ví dụ có nhiều điều kiện.
ví dụ 1. sử dụng if & amp; và trong excel
Giả sử bạn có một bảng kết quả cho hai điểm kiểm tra. điểm thứ nhất ở cột c phải bằng hoặc lớn hơn 20. điểm thứ hai ở cột d phải bằng hoặc lớn hơn 30. chỉ khi có đủ hai điều kiện trên thì học sinh mới đạt điểm tổng kết. kỳ thi.
Cách dễ nhất để tạo một công thức thích hợp là trước tiên hãy viết điều kiện và sau đó nhúng nó vào đối số logic_test của hàm if của bạn:
điều kiện: và (b2> = 20, c2> = 30)
if / and công thức:
= yes ((y (c2 & gt; = 20, d2 & gt; = 30)), “được chấp thuận”, “không thành công”)
Thật dễ dàng, phải không? công thức trên yêu cầu excel trả về “được chấp thuận” nếu giá trị nằm trong cột c & gt; = 20 và giá trị của cột d & gt; = 30. Nếu không, công thức trả về “fail”. ảnh chụp màn hình sau đây chứng minh rằng hàm if / and trong excel của chúng tôi là chính xác:
Lưu ý rằng Microsoft Excel sẽ kiểm tra tất cả các điều kiện trong hàm và ngay cả khi một trong các điều kiện đã kiểm tra đánh giá là sai. hành vi như vậy hơi bất thường vì trong hầu hết các ngôn ngữ lập trình, điều kiện sau sẽ không được kiểm tra nếu bất kỳ lần kiểm tra nào trước đó trả về sai.
Thật vậy, một công thức if / y rõ ràng là đúng có thể tạo ra lỗi do tính cụ thể này. ví dụ: công thức sau sẽ trả về “lỗi chia cho 0” (# div / 0!) nếu ô a2 bằng 0:
= if (y (a2 & lt; & gt; 0, (1 / a2) & gt; 0,5) ,, “tốt”, “xấu”)
Để tránh điều này, bạn nên sử dụng hàm if lồng nhau:
= if (a2 & lt; & gt; 0, if ((1 / a2) & gt; 0.5, “good”, “bad”), “bad”)
ví dụ 2. sử dụng if với hàm hoặc trong excel
bạn có thể kết hợp if & amp; hoặc theo cách tương tự. tuy nhiên, không giống như công thức if / và được thảo luận ở trên, công thức này trả về true nếu ít nhất một trong các điều kiện được đáp ứng.
sau đó nếu chúng tôi sửa đổi công thức trước đó theo cách sau:
= yes ((o (c2 & gt; = 20, d2 & gt; = 30)), “được chấp thuận”, “không thành công”)
cột e sẽ có dấu “đạt” nếu điểm đầu tiên bằng hoặc lớn hơn 20 hoặc điểm thứ hai bằng hoặc lớn hơn 30.
Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, sinh viên có cơ hội tốt hơn để vượt qua bài kiểm tra cuối cùng trong điều kiện như vậy (Scott đặc biệt không may mắn khi chỉ trượt 1 điểm:)
ví dụ 3. sử dụng if với các hàm và & amp; hoặc
Trong trường hợp bạn cần đánh giá dữ liệu của mình dựa trên một tập hợp nhiều điều kiện, bạn sẽ cần sử dụng cả hai & amp; hoặc đồng thời.
Trong bảng trên, giả sử bạn có các tiêu chí sau để đo lường sự thành công của học sinh:
điều kiện 1: cột c> = 20 và cột d> = 25
điều kiện 2: cột c> = 15 và cột d> = 20
nếu một trong hai điều kiện trên được đáp ứng, bài kiểm tra cuối cùng được coi là đạt; nếu không, nó không thành công.
Công thức này nghe có vẻ phức tạp, nhưng bạn sẽ thấy ngay rằng không phải vậy! bạn chỉ cần diễn đạt hai điều kiện dưới dạng một câu lệnh và đưa chúng vào hàm hoặc vì bạn không cần đáp ứng cả hai điều kiện, chỉ cần khớp:
hoặc (y (c2> = 20, d2> = 25), y (c2> = 15, d2> = 20)
Cuối cùng, việc sử dụng hàm hoặc ở trên thực hiện kiểm tra logic đối với hàm if và cung cấp các đối số value_if_true và value_if_false. Kết quả là bạn sẽ nhận được công thức sau nếu có nhiều và / hoặc điều kiện:
= if (hoặc (y (c2 & gt; = 20, d2 & gt; = 25), y (c2 & gt; = 15, d2 & gt; = 20)), “pass”, “fail”)
Ảnh chụp màn hình sau đây cho thấy rằng chúng tôi đã sử dụng công thức:
Tất nhiên, bạn không thể chỉ sử dụng hai hàm và / hoặc trong công thức excel nhiều điều kiện nhưng bạn có thể sử dụng nhiều hàm logic khác, miễn là: p>
sử dụng nhiều câu lệnh if trong excel (các hàm if lồng nhau)
Nếu bạn cần tạo các công thức logic phức tạp hơn cho dữ liệu của mình, bạn có thể bao gồm các câu lệnh if bổ sung trong các đối số value_if_true và value_if_false của công thức if trong excel của bạn. Nhiều hàm if trong số này được gọi là hàm if và và chúng có thể đặc biệt hữu ích nếu bạn muốn công thức của mình trả về 3 kết quả khác nhau trở lên.
Đây là một ví dụ điển hình: Giả sử bạn không chỉ muốn chấm điểm kết quả của học sinh là đạt / không đạt mà còn xác định tổng điểm là “tốt”, “ đạt yêu cầu ” và “kém”. . ví dụ:
tốt (tốt): 60 trở lên (> = 60)
thành công ( thành công) : 40 đến 60 (& gt; 40 và & lt; 60)
kém (kém): 40 trở xuống (& lt; = 40)
Để bắt đầu, bạn có thể thêm một cột bổ sung (e) với công thức sau để thêm các số trong cột c và d:
= c2 + d2
Bây giờ, hãy viết một hàm if lồng nhau dựa trên các điều kiện trên. bạn nên bắt đầu với điều kiện quan trọng nhất và làm cho các chức năng của bạn càng đơn giản càng tốt. công thức if lồng nhau trông giống như sau:
= if (e2 & gt; = 60, “tốt”, nếu (e2 & gt; 40, “đạt yêu cầu”, “kém”))
Như bạn có thể thấy, một hàm if lồng nhau là đủ trong trường hợp này. tất nhiên bạn có thể lồng nhiều chức năng hơn nếu bạn muốn. ví dụ:
= if (e2 & gt; = 70, “xuất sắc”, nếu (e2 & gt; = 60, “tốt”, nếu (e2 & gt; 40, “đạt yêu cầu”, “kém”)))
Công thức trên thêm một điều kiện: tổng điểm từ 70 điểm trở lên là “xuất sắc” (xuất sắc).
sử dụng excel nếu trong công thức mảng
Giống như các hàm excel khác, nó cũng có thể được sử dụng trong công thức mảng.
Ví dụ: công thức mảng sum / if sau đây cho thấy cách bạn có thể tính tổng các ô trong một phạm vi cụ thể dựa trên một điều kiện nhất định thay vì thêm các giá trị thực tế:
= sum (if (b1: b5 & lt; = 1,1,2))
công thức này sẽ gán một số “điểm” nhất định cho mỗi giá trị trong cột b – nếu một giá trị bằng hoặc nhỏ hơn 1, thì giá trị đó bằng 1 điểm; và 2 điểm được gán cho mỗi giá trị lớn hơn 1. và sau đó hàm tổng thêm kết quả 1 và 2, như thể hiện trong ảnh chụp màn hình sau.
sử dụng excel nếu bạn đang sử dụng công thức mảng
lưu ý: vì đây là công thức mảng, hãy nhớ nhấn ctrl + shift + enter để nhập chính xác!
sử dụng hàm if với các hàm excel khác
Chúng ta vừa thảo luận một số ví dụ về cách sử dụng hàm if trong excel với hàm logic và và hoặc. Bây giờ chúng ta hãy xem xét các hàm excel khác có thể được sử dụng với if!
ví dụ 1. sử dụng if với các hàm tổng, trung bình, tối thiểu và tối đa
Đối với ví dụ về hàm if lồng nhau, chúng tôi viết công thức trả về điểm (xuất sắc, tốt, khá hoặc kém) dựa trên tổng điểm của mỗi học sinh. sau đó chúng tôi thêm một cột mới với công thức để tính tổng điểm trong cột c và d.
nhưng điều gì sẽ xảy ra nếu bảng của bạn có cấu trúc được xác định trước và không cho phép bất kỳ sửa đổi nào? trong trường hợp này, thay vì thêm cột trợ giúp, bạn có thể thêm giá trị trực tiếp vào công thức if của mình, như sau:
= nếu ((c2 + d2) & gt; = 60, “tốt”, nếu ((c2 + d2) = & gt; 40, “đạt yêu cầu”, “kém”))
được, nhưng nếu bảng của bạn chứa nhiều điểm riêng lẻ, chẳng hạn như 5 cột khác nhau trở lên thì sao? thêm nhiều số trực tiếp vào công thức if sẽ làm cho nó cực kỳ lớn. Một giải pháp thay thế là nhúng hàm sum vào logic kiểm tra của if, như sau:
= if (sum (c2: f2)> = 120, “tốt”, nếu (sum (c2: f2)> = 90, “đạt yêu cầu”, “kém”))
Tương tự, bạn có thể sử dụng các hàm excel khác trong công thức if:
vâng rưỡi :
= yes (trung bình (c2: f2) & gt; = 30, “tốt”, nếu (trung bình (c2: f2) & gt; = 25, “đạt yêu cầu”, “kém”))
kết quả sẽ trả về “tốt” nếu điểm trung bình trong cột c: f bằng hoặc lớn hơn 30, “đạt yêu cầu” nếu điểm trung bình từ 29 đến 25 và “kém” nếu nhỏ hơn 25 .
có và tối đa / tối thiểu :
Để tìm điểm cao nhất và thấp nhất, bạn có thể sử dụng hàm max và min tương ứng. Giả sử cột f là cột tổng điểm, các công thức sau sẽ hoạt động:
max: = if (f2 = max ($ f $ 2: $ f $ 10), “kết quả tốt nhất”, “”)
min: = if (f2 = min ($ f $ 2: $ f $ 10), “kết quả kém nhất”, “”)
nếu bạn muốn có kết quả tối thiểu và tối đa trong cùng một cột, bạn có thể lồng một trong các hàm trên vào bên trong hàm kia, ví dụ:
= if (f2 = max ($ f $ 2: $ f $ 10), “kết quả tốt nhất”, nếu (f2 = min ($ f $ 2: $ f $ 10), “kết quả kém nhất”, “”) )
Tương tự, bạn có thể sử dụng hàm if với các hàm trang tính tùy chỉnh của mình. ví dụ: bạn có thể sử dụng nó với các hàm getcellcolor / getcellfontcolor để nhận được các kết quả khác nhau tùy thuộc vào màu của ô.
Ngoài ra, Excel cũng cung cấp một số hàm đặc biệt để phân tích và tính toán dữ liệu dựa trên các điều kiện khác nhau.
Ví dụ: để đếm số lần xuất hiện của giá trị văn bản hoặc số dựa trên một hoặc nhiều điều kiện, bạn có thể sử dụng countif và countifs tương ứng. Để tìm tổng các giá trị dựa trên nhiều điều kiện, hãy sử dụng hàm sumif hoặc sumifs. để tính giá trị trung bình theo một số tiêu chí nhất định, hãy sử dụng Averageif hoặc Averageifs.
ví dụ 2. nếu với hàm isnumber và istext
Bạn có biết cách xác định các ô trống và không trống bằng cách sử dụng hàm isblank? Ngoài ra, microsoft excel cũng cung cấp các hàm tương tự để xác định giá trị số và văn bản: istext và isnumber, hãy xem chúng hoạt động như thế nào:
Đây là ví dụ về hàm if lồng nhau trong excel trả về “văn bản” nếu ô b1 chứa bất kỳ giá trị văn bản nào, “số” nếu ô b1 chứa giá trị số và “trống” nếu ô b1 trống.
= if (istext (b1), “text”, if (isnumber (b1), “number”, if (isblank (b1), “blank”, “”)))
lưu ý: công thức trên trả về “số” cho các giá trị số và ngày. Điều này là do Microsoft Excel lưu trữ ngày tháng dưới dạng số, bắt đầu từ ngày 1 tháng 1 năm 1900, bằng 1.
ví dụ 3. sử dụng kết quả được trả về bởi if trong một hàm excel khác
Đôi khi, bạn có thể đạt được kết quả mong muốn bằng cách nhúng câu lệnh if vào một số hàm excel khác, thay vì sử dụng một hàm khác trong một bài kiểm tra logic.
đây là một cách khác mà bạn có thể sử dụng nối và nếu chúng hoạt động cùng nhau:
= concatenate (“bạn đã làm”, if (c1 & gt; 5, ”tuyệt vời!”, “ổn”))
Bạn có thể không cần bất kỳ lời giải thích nào về cách hoạt động của công thức này, đặc biệt hãy xem ảnh chụp màn hình bên dưới:
if hàm so với iferror và ifna
Các hàm iferror và ifna được sử dụng để phát hiện lỗi trong công thức excel và thay thế chúng bằng một phép tính khác, một giá trị được xác định trước hoặc một tin nhắn văn bản. Trong các phiên bản excel cũ hơn, bạn có thể sử dụng kết hợp if iserror và if isna để thay thế
sự khác biệt là iferror và iserror xử lý tất cả các lỗi excel có thể xảy ra bao gồm #value !, # n / a, #name ?, #ref !, #num !, # div / 0! và #null !. trong khi ifna và isna chỉ chuyên về # n / a.
Đây là ví dụ đơn giản nhất về công thức iferror:
= iferror (b2 / c2, “xin lỗi, đã xảy ra lỗi”)
Như bạn có thể thấy trong ảnh chụp màn hình ở trên, cột d hiển thị thương số của phép chia một giá trị trong cột b cho một giá trị trong cột c. Bạn cũng có thể thấy hai thông báo lỗi trong ô d2 và d5 vì mọi người đều biết rằng bạn không thể chia một số cho số không.
Tuy nhiên, trong một số trường hợp, bạn có thể không muốn bắt tất cả các lỗi mà muốn kiểm tra điều kiện gây ra một lỗi cụ thể. ví dụ: để thay thế lỗi chia cho 0 bằng thông báo của riêng bạn, hãy sử dụng công thức if sau:
= if (c2 = 0, “xin lỗi, đã xảy ra lỗi”, b2 / c2)
Ở trên có liên quan đến việc sử dụng hàm if trong excel. Cảm ơn bạn đã tham gia học excel trực tuyến , hãy tiếp tục theo dõi chúng tôi để cập nhật những kiến thức mới nhất!