Trong bài viết này, học excel trực tuyến sẽ chỉ cho bạn cách sử dụng hàm if với nhiều điều kiện trong excel và đưa ra ví dụ về các hàm if lồng nhau này strong> thường được sử dụng tại nơi làm việc.
Nếu được hỏi bạn sử dụng hàm excel nào nhiều nhất, bạn sẽ trả lời như thế nào? đối với nhiều người, đó là hàm if nhiều điều kiện trong excel. công thức hàm if nếu chỉ có một điều kiện sẽ rất dễ viết. nhưng điều gì sẽ xảy ra nếu các chỉ số của bạn cần được kiểm tra bằng một thuật toán phức tạp với nhiều điều kiện? trong trường hợp này, bạn có thể sử dụng nhiều hàm if trong một công thức và công thức hàm if hiện được gọi là hàm if lồng nhau hoặc hàm if và . Ưu điểm lớn nhất của hàm if lồng nhau là nó sẽ cho phép bạn kiểm tra nhiều điều kiện và nhận các giá trị khác nhau tùy thuộc vào kết quả kiểm tra với một công thức duy nhất.
Trong các phiên bản Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể kết hợp tối đa 64 điều kiện trong một công thức. nhưng với excel 2003 trở về trước, công thức chỉ có thể có tối đa 7 điều kiện.
Trong bài viết này, bạn sẽ thấy các ví dụ về hàm if lồng nhau (hàm if đa điều kiện) cùng với giải thích chi tiết về cấu trúc và logic của công thức hàm if trong đó.
xem thêm:
- học excel từ cơ bản đến nâng cao
- xuất sắc: trên 249
- tốt: từ 200 đến 249
- đạt yêu cầu: từ 150 đến 199
- kém: dưới 150
- trong excel 2016-2007, bạn có thể kết hợp 64 điều kiện. Trong các phiên bản trước của excel 2003 trở về trước, bạn có thể sử dụng tối đa 7 điều kiện.
- luôn nghĩ về thứ tự của các điều kiện trong công thức if lồng nhau: nếu điều kiện đầu tiên là đúng, các điều kiện tiếp theo sẽ không được kiểm tra.
- nếu công thức của bạn có nhiều hơn 5 hàm if, thì các công thức sau có thể tối ưu hơn.
- Để kiểm tra nhiều điều kiện, hãy sử dụng find, vsearch, index / match hoặc select.
- sử dụng if kết hợp với hoặc / và
- sử dụng công thức mảng
- sử dụng hàm nối và nối chuỗi
Dưới đây là một ví dụ điển hình về hàm if với nhiều điều kiện. Giả sử bạn có danh sách học sinh ở cột a và điểm tương ứng ở cột b, bạn muốn sắp xếp chúng theo các điều kiện sau:
hãy viết một hàm if lồng nhau dựa trên các tiêu chí trên. Hãy coi đây là cơ hội để thực hành, bắt đầu với những thuật ngữ quan trọng nhất và viết công thức càng đơn giản càng tốt. công thức hàm if lồng nhau của chúng ta sẽ trông như thế này:
= if (b2 & gt; 249, “xuất sắc”, nếu (b2 & gt; = 200, “tốt”, nếu (b2 & gt; 150, “đạt yêu cầu”, “kém”)))
và đây là kết quả:
cách hiểu logic hàm nếu được lồng vào nhau:
Đối với nhiều người, các hàm if lồng nhau có thể gây nhầm lẫn. nhưng sẽ dễ dàng hơn nếu bạn nhìn vào công thức hàm if lồng nhau ở trên như thế này:
= if (b2> 249, “xuất sắc”,
= if (b2> = 200, “tốt”,
= if (b2> 150, “đạt yêu cầu”, “kém”)))
Công thức thực sự ra lệnh cho excel đánh giá xem điều kiện đầu tiên của công thức hàm if là logic hay không, nếu điều kiện là logic thì excel sẽ trả về giá trị bạn đã cung cấp khi điều kiện được đáp ứng. nếu điều kiện đầu tiên không được đáp ứng, hãy kiểm tra điều kiện 2 và tiếp tục.
if (kiểm tra xem b2> = 249, nếu true trả về “xuất sắc”, nếu không
if (kiểm tra xem b2> = 200, nếu đúng – trả về “ok”, nếu không
if (kiểm tra xem b2> 150, nếu đúng – trả về “thành công”, nếu sai –
thanh toán “kém”)))
Thứ tự của các điều kiện rất quan trọng
Trong ví dụ trên, bạn có thể thấy rằng điều kiện quan trọng nhất sẽ được viết trước. bởi vì excel sẽ kiểm tra các điều kiện theo thứ tự chúng xuất hiện trong công thức và ngay sau khi một điều kiện được đáp ứng, điều kiện tiếp theo sẽ không được đánh giá.
các yêu cầu như sau: giá của các sản phẩm sẽ thay đổi tùy theo số lượng cụ thể. và bạn muốn viết một công thức tính tổng giá của bất kỳ số lượng hàng hóa nào và đặt nó vào một ô nhất định. Nói cách khác, công thức của bạn cần kiểm tra các điều kiện và thực hiện phép tính tùy thuộc vào số lượng hàng và giá được tính như vậy:
điều này có thể được thực hiện bằng cách sử dụng các hàm if lồng nhau. cách giải thích tương tự như ví dụ 1, sự khác biệt duy nhất là bạn nhân số lượng hàng cụ thể với giá trị được trả về bởi hàm if (ví dụ: giá tương ứng trên mỗi đơn vị hàng)
Giả sử người dùng đã nhập số hàng vào ô b8, công thức sẽ giống như sau:
= b8 * if (b8> = 101, 12, if (b8> = 50, 13, if (b8> = 20, 16, if (b8> = 11, 18, if (b8> = 1, 20 )))), “”)))))))
và kết quả sẽ như thế này:
Ví dụ này chỉ là một hướng dẫn cơ bản, nếu bạn hiểu nó, bạn có thể áp dụng hàm if lồng nhau này vào công việc của mình.
Ví dụ: thay vì giá hàng hóa được đặt trước trong công thức, bạn có thể điều hướng đến các ô chứa giá trị này (ô b2 đến b6). phương pháp này sẽ giúp bạn thay đổi nguồn dữ liệu đầu vào mà không cần thay đổi công thức.
= b8 * if (b8> = 101, b6, if (b8> = 50, b5, if (b8> = 20, b4, if (b8> = 11, b3, if (b8> = 1, b2) )), “”)))))))
Hoặc bạn có thể thêm một điều kiện if khác để chặn phần trên, phần dưới hoặc cả hai đầu của dải ô. khi số không nằm trong phạm vi, công thức sẽ hiển thị cụm từ “ngoài phạm vi”. ví dụ:
= if (o (b8 & gt; 200, b8 & lt; 1), “qty out of range”, b8 * if (b8 & gt; = 101,12, if (b8 & gt; = 50, 13, if (b8 & gt; =) 20, 16, nếu (b8> = 11, 18, nếu (b8> = 1, 20, “”))))))
Công thức
đa điều kiện nếu hàm if được mô tả ở trên có sẵn trong tất cả các phiên bản excel từ 2016-2000. Trong Office 365 excel 2016, bạn cũng có thể sử dụng hàm if theo cách tương tự như trên.
các chuyên gia excel đã quen với việc sử dụng công thức mảng, hàm này có thể làm tương tự như các hàm if lồng nhau ở trên. Mặc dù công thức mảng khó hiểu hơn nhiều, chưa nói đến cách viết, các hàm mảng có một lợi thế: Bạn có thể sử dụng một dải ô chứa điều kiện bạn cần mà không cần phải nhập từng điều kiện. điều này làm cho công thức linh hoạt hơn và nếu người dùng của bạn cần thay đổi bất kỳ điều kiện nào hoặc thêm điều kiện mới, bạn có thể chỉ cần cập nhật phạm vi ô trong công thức của mình.
Như bạn vừa thấy, việc sử dụng hàm if với nhiều điều kiện trong excel không yêu cầu bất kỳ phương pháp hoặc công thức nâng cao nào. Để cải thiện các công thức if lồng nhau và tránh những lỗi thường gặp, hãy luôn nhớ 3 điều cơ bản sau:
Để tránh giới hạn 7 điều kiện trong hàm if trong các phiên bản trước của excel và để làm cho công thức của bạn ngắn hơn và nhanh hơn, bạn có thể chọn các phương pháp sau để thay thế hàm if bằng nhiều điều kiện. >
cũng giống như các hàm excel khác, hàm nối có thể bao gồm 30 điều kiện trong các phiên bản excel cũ hơn và 255 điều kiện trong excel 2016 – 2007.
ví dụ: để nhận được các kết quả khác nhau tùy thuộc vào giá trị của ô b2, bạn có thể sử dụng một trong các công thức sau:
hàm if lồng nhau:
= if (b2 & gt; 249, “xuất sắc”, nếu (b2 & gt; = 200, “tốt”, nếu (b2 & gt; 150, “đạt yêu cầu”, “kém”)))
hàm nối:
= concatenate (nếu (c1 = ”a”, “xuất sắc”, “”), nếu (c1 = ”b”, “tốt”, “”), nếu (c1 = ”c”, “kém”, “”))
hàm chuỗi:
= if (b2 = ”a”, “xuất sắc”, “”) & amp; if (b2 = “b”, “good”, “”) & amp; if (b2 = “c”, “kém”, “”) & amp; if (b2 = “d”, “kém”, “”)
kiến thức bạn đang xem thuộc khóa học excel cơ bản đến nâng cao để học excel trực tuyến. khóa học này cung cấp cho bạn kiến thức đầy đủ và có hệ thống về các hàm, công cụ trong excel, các ứng dụng của excel trong công việc, v.v. Tham gia ngay tại: Học Excel Online