Bài gợi ý này, học tập Excel Online sẽ giải thích cho các bạn cách dùng hàm IF với rất nhiều điều khiếu nại trong Excel và hỗ trợ một số lấy ví dụ về mọi hàm IF lồng nhau phổ biến nhất hay được sử dụng trong công việc. Bạn đang xem: Hàm if nhiều điều kiện trong excel 2007
Nếu được một tín đồ nào đó hỏi rằng tính năng nào vào Excel mà chúng ta dùng các nhất, các bạn sẽ trả lời như thế nào? Trong hầu hết các trường hợp, đó chính là hàm IF trong Excel. Hàm IF nếu như chỉ tất cả một đk sẽ rất dễ dàng và dễ dàng viết. Cơ mà điều gì sẽ xẩy ra nếu tài liệu của bạn phải được kiểm tra bởi thuật toán phức hợp với rất nhiều điều kiện? trong trường phù hợp này, bạn có thể sử dụng nhiều hàm IF vào một công thức, cùng hàm IF hôm nay được điện thoại tư vấn là hàm IF lồng nhau. Ư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 hơn thế một đk và được trả về những giá trị không giống nhau tùy thuộc vào kết quả của những lần khám nghiệm đó với một cách làm duy nhất.
Trong các phiên bản hiện đại hơn của Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể lồng tối đa 64 hàm IF trong một công thức. Vào Excel 2003 về bên trước, chỉ rất có thể sử dụng về tối đa 7 hàm IF lồng nhau.
Trong nội dung bài viết này, bạn sẽ tìm thấy rất nhiều ví dụ về hàm IF lồng nhau cùng rất phần giải thích cụ thể về cấu tạo cú pháp và lô ghích của chúng.
Ví dụ 1: cách làm IF lồng nhau cổ điển/cơ bản
Giả sử bạn có danh sách học viên nằm ở cột A và điểm thi của mình nằm làm việc cột B và bạn có nhu cầu phân loại điểm với những điều kiện sau:Excellent: bên trên 249Good: từ bỏ 200 đến 249Satisfactory: từ bỏ 150 mang đến 199Poor: bên dưới 150Và bây giờ, bọn họ hãy cùng viết một hàm IF lồng nhau dựa trên các tiêu chí trên. Đây là một thời cơ thực hành tốt, ban đầu với gần như điều kiện quan trọng nhất cùng viết hàm IF của doanh nghiệp càng dễ dàng và đơn giản càng tốt. Bí quyết IF lồng nhau được biểu đạt như sau:
=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor “)))
Và kết quả đúng mực sẽ là:
Cách hiểu ngắn gọn xúc tích của hàm IF lồng nhau:
Với những người, hàm IF lồng nhau rất có thể rất cực nhọc hiểu. Dẫu vậy sẽ đơn giản dễ dàng hơn nhiều nếu bạn nhìn nhận chúng theo phong cách này:
Trong thực tế, phần lớn gì công thức thực sự yêu mong Excel có tác dụng là reviews kiểm tra tính ngắn gọn xúc tích của điều kiện thứ nhất của bí quyết hàm IF, nếu điều kiện logic, Excel đã trả về quý giá được cung ứng khi điều kiện được đáp ứng. Nếu điều kiện của hàm If đầu tiên không xúc tích thì thường xuyên kiểm tra điều kiện thứ hai, đk thứ thứ ba,…
Kiểm tra nếu B2 >= 249 thì trả về hiệu quả “Excellent”, nếu không thì khám nghiệm tiếp B2 >=200, nếu như đúng thì trả về kết quả “Good”, nếu không thì bình chọn tiếp B2 >=150, nếu đúng thì “Satisfactory”, ví như sai thì trả về “Poor”
Ví dụ 2. Hàm IF lồng nhau với các phép tính số học
Đây là 1 yêu cầu điển hình nổi bật khác: đối kháng giá đổi khác tùy ở trong vào con số được chỉ định rõ ràng và nhiệm vụ của người sử dụng là viết công thức tính tổng giá bán cho con số mặt hàng ngẫu nhiên và được nhập vào một ô duy nhất định. Nói bí quyết khác, phương pháp của bạn phải kiểm tra nhiều đk và tiến hành các phép tính khác biệt tùy ở trong vào phạm vi số lượng hàng và túi tiền được chỉ định trong bảng sau:
| Số lượng hàng | Giá cả mỗi đối kháng vị |
| 1 – 10 | 20$ |
| 11 – 19 | 18$ |
| 20 – 49 | 16$ |
| 50 – 100 | 13$ |
| Hơn 101 | 12$ |
Công vấn đề này cũng rất có thể được thực hiện bằng cách sử dụng hàm IF lồng nhau. Giải pháp hiểu logic giống như như trong lấy một ví dụ trên, sự khác biệt duy duy nhất là chúng ta nhân số lượng được chỉ định với mức giá trị được trả về bởi các IF lồng nhau (tức là giá tương ứng trên mỗi đơn vị chức năng hàng hóa).
Giả sử người dùng nhập số lượng vào ô B8, công thức sẽ được thế hiện 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ẽ được thể hiện tương tự như sau:
Ví dụ này chỉ trình diễn cách làm tầm thường và cơ bản, nếu khách hàng đã hiểu, bạn có thể dễ dàng thiết lập cấu hình hàm If lồng nhau này tùy nằm trong vào quá trình cụ thể của bạn.
Trong trường hợp: thay vị ấn định sẵn giá trong công thức, chúng ta cũng có thể tham chiếu các ô chứa những giá trị kia (ô B2 đến B6). Cách này sẽ cho phép bạn chỉnh sửa dữ liệu nguồn cơ mà không buộc phải phải cập nhật 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 chúng ta có thể thêm (các) đk IF bổ sung để điều chỉnh giới hạn trên, bên dưới hoặc cả hai đầu của phạm vi số tiền. Khi con số nằm bên cạnh phạm vi, phương pháp sẽ hiện thông báo “out of the range”. Ví dụ:
=IF(OR(B8>200,B8=101,12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”))))))
Các cách làm hàm IF lồng nhau được biểu lộ ở bên trên đều chuyển động trong tất cả các phiên bản Excel năm 2016 – 2000. Vào Excel năm nhâm thìn – Office 365, chúng ta cũng có thể sử dụng hàm IFS theo các cách nêu trên.
Những người dùng Excel chuyên nghiệp hóa đã thân thuộc với công thức mảng, về cơ bản có thể thực hiện công thức này thực hiện điều tương tự như hàm IF lồng nhau mà họ đã thảo luận ở trên. Tuy vậy công thức mảng nặng nề hiểu và khó viết hơn các nhưng nó bao gồm một lợi thế không thể chối gượng nhẹ – chúng ta cũng có thể chỉ định phạm vi ô (một dãy ô) chứa các điều khiếu nại mình yêu cầu thay vì chưng tham chiếu từng điều kiện riêng lẻ. Điều này tạo cho công thức hoạt bát hơn cùng nếu người tiêu dùng thay đổi ngẫu nhiên điều kiện hiện tất cả nào hoặc thêm điều kiện mới, các bạn sẽ chỉ phải update lại hàng ô trong công thức của mình.
Hàm IF lồng nhau trong Excel – mẹo và các thủ thuật
Như bạn vừa thấy, việc thực hiện hàm IF nhiều điều kiện trong Excel không yên cầu các cách thức khoa học rất cao siêu. Các mẹo sau đây sẽ giúp đỡ bạn nâng cao các cách làm IF lồng nhau và ngăn ngừa những lỗi thường gặp:
Giới hạn IF lồng nhau
Trong Excel 2016 – 2007, chúng ta cũng có thể lồng buổi tối đa 64 hàm IF hay phối kết hợp 64 điều kiện. Trong số phiên phiên bản cũ rộng của Excel 2003 trở về trước, có thể sử dụng buổi tối đa 7 hàm IF lồng nhau. Tuy nhiên, chúng ta cũng có thể lồng nhiều điều kiện trong một cách làm không có nghĩa là bạn đề nghị làm như vậy. Xin lưu ý rằng lượt bổ sung cập nhật điều kiện sẽ tạo cho công thức của người tiêu dùng khó hiểu và cạnh tranh khắc phục lỗi hơn. Nếu như công thức của khách hàng có quá nhiều điều khiếu nại được kết hợp, bạn có thể tối ưu hóa nó bằng cách sử dụng một trong những lựa chọn sửa chữa sau đây.
Thứ tự của những điều khiếu nại trong hàm IF lồng nhau khôn xiết quan trọng
Hàm IF lồng nhau vào Excel nhận xét tính xúc tích theo thiết bị tự chúng mở ra trong bí quyết và ngay sau khoản thời gian một trong số điều kiện đánh giá là TRUE, những điều kiện tiếp theo sau đó sẽ không được kiểm tra. Nói phương pháp khác, phương pháp sẽ dừng sau khi công dụng là TRUE.
Hãy coi nó hoạt động như cố nào vào thực tế. Với B2 bằng 274, cách làm IF lồng nhau bên dưới đánh giá tính lô ghích của điều kiện đầu tiên (B2> 249) với trả về “Xuất sắc” vì tác dụng kiểm tra tính logic này là TRUE:
=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor”)))
Bây giờ, hãy đảo ngược trang bị tự của những điều kiện như sau:
=IF(B2>150, “Satisfactory”, IF(B2>200, “Good”, IF(B2>249, “Excellent”, “Poor”)))
Công thức đang kiểm tra đk đầu tiên, bởi vì 274 > 150, công dụng của chất vấn tính logic này cũng trở nên là TRUE. Vì chưng đó, cách làm trả về kết quả “Satisfactory” nhưng mà không cần kiểm tra những điều khiếu nại khác.
Bạn thấy đó, việc đổi khác thứ tự của những hàm IF sẽ biến hóa kết quả:
Đánh giá chỉ tính logic của công thức
Để theo dõi và quan sát dòng xúc tích của bí quyết hàm IF lồng nhau theo mỗi bước một, hãy sử dụng bản lĩnh Evaluate Formula nằm tại tab Formula, trong đội Formula Auditing. Biểu thức được gạch chân là phần sẽ được review và việc nhấp vào nút Evaluate sẽ hiển thị cho bạn tất cả quá trình trong các bước đánh giá.
Ví dụ: việc review tính logic của điều kiện trước tiên trong phương pháp IF lồng nhau được hiển thị trong ảnh bên dưới vẫn như sau: B2 > 249; 274 > 249; TRUE; Excellent.
Cân bởi dấu ngoặc của những hàm IF lồng nhau
Một trong những thách thức chính với hàm IF lồng nhau trong Excel là khớp các cặp vết ngoặc đơn. Nếu vệt ngoặc 1-1 không khớp, phương pháp của bạn sẽ không hoạt động. Dẫu vậy đừng lo, mailinhschool.edu.vn Excel hỗ trợ một số tính năng rất có thể giúp chúng ta cân bằng những dấu ngoặc đối kháng khi chỉnh sửa công thức:
Nếu bạn có rất nhiều hơn một tập hợp các dấu ngoặc đơn, thì các cặp vệt ngoặc đơn sẽ tiến hành tô bằng các màu không giống nhau để vệt ngoặc mở đầu khớp với vệt ngoặc đóng.Khi các bạn đóng vệt ngoặc đơn, Excel sẽ đánh dấu cặp phù hợp. Sẽ có được hiệu ứng in đậm hoặc “nhấp nháy” khi chúng ta di chuyển hẳn qua công thức bằng cách sử dụng các phím mũi tên.
Cách xử lý những văn bản và những số là khác nhau
Khi xây dựng các bài khám nghiệm tính logic cho các hàm IF lồng nhau của bạn, hãy hãy nhờ rằng văn phiên bản và số cần được xử lý theo cách không giống nhau – luôn luôn đặt những giá trị văn bạn dạng trong dấu ngoặc kép nhưng với số thì không:
Đúng: =IF(B2>249, “Excellent”,…)
Sai: =IF(B2>”249″, “Excellent”,…)
Kiểm tra tính súc tích của cách làm thứ hai sẽ trả về FALSE trong cả khi cực hiếm trong B2 > 249 vày 249 là một trong những và “249” là một trong chuỗi số, bọn chúng là hai sản phẩm khác nhau.
Xem thêm: Phần mềm test hiệu năng máy tính, các cách kiểm tra hiệu suất máy tính
Thêm dấu bí quyết hoặc ngắt dòng để gia công cho các IF lồng nhau đọc dễ hơn
Khi xây cất một công thức với rất nhiều điều khiếu nại lồng nhau, bạn cũng có thể làm mang lại tính lô ghích của phương pháp được ví dụ hơn bằng phương pháp tách những hàm IF không giống nhau bằng lốt cách hoặc dấu ngắt dòng. Excel không cân nhắc khoảng cách thừa trong công thức, bởi vì vậy các bạn không cần lo lắng về việc lệch lạc của nó.
Để di chuyển một trong những phần nhất định của công thức sang dòng tiếp theo, chỉ việc nhấp vào nơi bạn muốn chèn ngắt chiếc và nhấn alternative text + Enter. Sau đó, không ngừng mở rộng thanh công thức đến mức quan trọng và cách làm của bạn sẽ trở nên dễ dàng nắm bắt hơn nhiều.
Các lựa chọn sửa chữa thay thế cho IF lồng nhau vào Excel
Để thừa qua số lượng giới hạn của 7 hàm IF lồng nhau trong Excel 2003 và những phiên bạn dạng cũ rộng và để gia công cho cách làm của bạn nhỏ gọn và nhanh hơn, hãy để ý sử dụng các lựa chọn thay thế sửa chữa sau nhé:
Sử dụng IF với những hàm ngắn gọn xúc tích OR/AND.Sử dụng bí quyết mảng (array).Đối với người dùng Excel tất cả kinh nghiệm, phương án thay thế cực tốt để áp dụng nhiều hàm IF lồng nhau là sản xuất một hàm trang tính tùy chỉnh cấu hình bằng VBA.Hàm IF là giữa những hàm thịnh hành nhất vào Excel, hàm này có thể chấp nhận được bạn triển khai so sánh lô-gic thân một giá bán trị với mức giá trị bạn muốn muốn.
Vì vậy, một câu lệnh IF có thể có nhị kết quả. Hiệu quả đầu tiên là giả dụ so sánh của bạn là True, hiệu quả thứ nhì là giả dụ so sánh của khách hàng là False.
Ví dụ: =IF(C2 = Có,1,2) cho biết thêm IF(C2 = Có, thì trả về 1, nếu không thì trả về 2).
Dùng hàm IF, một trong số hàm logic để trả về một cực hiếm nếu điều kiện đúng và trả về giá trị khác nếu đk sai.
IF(logical_test, value_if_true,
Ví dụ:
=IF(A2>B2,"Vượt dự toán","OK")
=IF(A2=B2,B4-A4,"")
| logical_test (bắt buộc) | Điều khiếu nại bạn muốn kiểm tra. |
| value_if_true (Bắt buộc) | Giá trị bạn có nhu cầu trả về nếu tác dụng của logical_test là ĐÚNG. |
| value_if_false (Tùy chọn) | Giá trị bạn có nhu cầu trả về nếu công dụng của logical_test là SAI. |
Các lấy ví dụ hàm IF đối chọi giản
=IF(C2=”Có”,1,2)
Trong ví dụ làm việc trên, ô D2 đến biết: IF(C2 = Có, thì trả về 1, còn nếu như không thì trả về 2)
=IF(C2=1,”Có”,”Không”)
Trong lấy ví dụ này, phương pháp trong ô D2 mang đến biết: IF(C2 = 1, thì trả về Có, nếu không thì trả về Không)Như bạn thấy, hàm IF có thể được thực hiện để đánh chi tiêu văn bạn dạng và giá chỉ trị. Hàm còn hoàn toàn có thể được sử dụng để review lỗi. Bạn sẽ không bị hạn chế chỉ khám nghiệm xem một văn bản có bởi một câu chữ khác hay không và trả về một hiệu quả duy nhất, bạn cũng có thể sử dụng toán tử toán học cùng thực hiện tính toán thêm tùy theo tiêu chí của mình. Bạn có thể lồng các hàm IF với nhau để tiến hành nhiều so sánh.
=IF(C2>B2,”Vượt quá Ngân sách”,”Trong Ngân sách”)
Trong ví dụ ngơi nghỉ trên, hàm IF ở ô D2 cho thấy IF(C2 lớn hơn B2, thì trả về “Vượt quá Ngân sách”, nếu không thì trả về “Trong Ngân sách”)
=IF(C2>B2,C2-B2,0)
Trong hình minh họa sinh sống trên, thay bởi trả về tác dụng dạng văn bản, họ sẽ trả về một phép đo lường và tính toán học. Công thức ở ô E2 cho biết IF(Thực tế to hơn Dự toán ngân sách, thì đem số tiền Dự toán ngân sách chi tiêu Trừ đi số chi phí Thực tế, nếu không thì ko trả về kết quả nào).
=IF(E7=”Có”,F5*0.0825,0)
Trong ví dụ này, công thức trong ô F7 cho biết thêm IF(E7 = “Có”, thì giám sát Tổng Số tiền trong F5 * 8,25%, nếu không thì không tồn tại Thuế bán hàng phải nộp cần trả về 0)
Lưu ý: Nếu bạn muốn sử dụng văn phiên bản trong công thức, bạn cần đưa văn bạn dạng vào trong lốt ngoặc kép (ví dụ: “Văn bản”). Nước ngoài lệ duy nhất là áp dụng TRUE hoặc FALSE, nội dung mà Excel gồm thể auto hiểu.
Sự cầm cố thường gặp
| 0 (không) trong ô | Không gồm đối số nào mang đến đối sốvalue_if_true hoặc value_if_False. Để thấy quý giá trả về đúng, thêm văn bạn dạng đối số vào nhì đối số đó hoặc thêm TRUE hoặc FALSE vào đối số. |
| #NAME? vào ô | Lỗi này thường có nghĩa là công thức viết không nên chính tả . |
Bạn bắt buộc thêm trợ giúp?
Bạn luôn hoàn toàn có thể hỏi một chuyên gia trong cộng đồng Kỹ thuật Excel hoặc thừa nhận sự cung ứng trong
Cộng đồng trả lời.
