Trong nội dung bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách sử dụng hàm IF với khá nhiều điều khiếu nại trong Excel và đưa ra những ví dụ về những hàm IF lồng nhau thường được sử dụng trong công việc.
Bạn đang xem: Sử dụng hàm if nhiều điều kiện trong excel
Nếu được hỏi tác dụng nào trong Excel mà các bạn dùng nhiều nhất, các bạn sẽ trả lời như vậy nào? Với tương đối nhiều người, đó chính là chức năng hàm IF những điều kiện trong Excel. Cách làm hàm IF nếu như chỉ bao gồm một điều kiện sẽ rất dễ viết. Nhưng mà nếu số liệu của bạn phải được kiểm tra bằng thuật toán phức hợp với không hề ít điều kiện? vào trường phù hợp này, chúng ta cũng có thể sử dụng nhiều chức năng IF trong một công thức, và bí quyết hàm IF lúc này được gọi là hàm IF lồng nhau tốt hàm if and. Ưu điểm lớn nhất của hàm IF lồng nhau là nó sẽ có thể chấp nhận được bạn chất vấn nhiều điều kiện và dìm được những giá trị khác nhau nhờ vào và công dụng kiểm tra với độc nhất một công thức.
Trong các phiên bạn dạng Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể kết hợp cho 64 đk trong một công thức. Tuy nhiên với Excel 2003 về bên trước, bí quyết chỉ gồm tối đa 7 điều kiện.
Trong nội dung bài viết này, các bạn sẽ thấy gần như ví dụ về hàm IF lồng nhau (hàm if những điều kiện) cùng với phần giải thích chi tiết về kết cấu và lô ghích của bí quyết hàm IF đó.
Xem nhanh
CÔNG THỨC HÀM IF LỒNG NHAU CƠ BẢN
CÔNG THỨC HÀM IF LỒNG NHAU CƠ BẢN Dưới đây là một ví dụ nổi bật về hàm IF với nhiều điều kiện. Trả sử bạn có một danh sách học viên ở cột A và điểm số tương ứng ở cột B, bạn có nhu cầu phân các loại ra theo những điều kiện sau:
Chúng ta hãy thuộc viết hàm IF lồng nau dựa trên những tiêu chuẩn trên. Hãy coi phía trên là cơ hội thực hành, ban đầu từ đều điều kiện quan trọng đặc biệt nhất với viết công thức càng dễ dàng và đơn giản càng tốt. Cách làm hàm IF lồng nhau của bọn họ sẽ như sau:
=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor “)))
Và đấy là kết quả:
CÁCH HIỂU súc tích HÀM IF LỒNG NHAU:
Với các người, hàm IF lồng nhau có thể khó hiểu. Cơ mà sẽ dễ dàng hơn nếu bạn nhìn phương pháp hàm IF lồng nhau trên theo phong cách này:
=IF(B2>249, “Excellent”,
=IF(B2>=200, “Good”,
=IF(B2>150, “Satisfactory”, “Poor”)))
Thực tế, cách làm yêu mong Excel review xem điều kiện đầu tiên của công thức hàm IF có xúc tích không, nếu đk logic, Excel vẫn trả về giá trị nhưng bạn cung cấp khi điều kiện được đáp ứng. Trường hợp điều kiện trước tiên không được đáp ứng, kiểm tra điều kiện 2 với tiếp tục.
IF(kiểm tra if B2>=249, if đúng – trả “Excellent”, nếu như không
IF(kiểm tra if B2>=200, if đúng – trả “Good”, giả dụ không
IF(kiểm tra if B2>150, if đúng – trả “Satisfactory”, if sai –
Trả ” Poor “)))
Thứ từ bỏ của điều kiện rất quan lại trọng
Từ lấy ví dụ như trên, hoàn toàn có thể thấy điều kiện đặc biệt nhất sẽ tiến hành viết đầu tiên. Bởi vì Excel vẫn kiểm tra đk theo sản phẩm tự mở ra trong công thức, và ngay trong lúc một điều kiện được đáp ứng, điều kiện tiếp đến sẽ không được đánh giá.
HÀM IF LỒNG NHAU BAO GỒM PHÉP TÍNHYêu cầu như sau: chi phí hàng hoá sẽ đổi khác phụ thuộc vào số lượng cụ thể. Và bạn có nhu cầu viết một công thức đo lường và tính toán tổng giá với con số hàng hoá bất cứ và đưa vào một trong những ô tuyệt nhất định. Nói biện pháp khác, công thức của khách hàng cần kiểm tra những điều khiếu nại và thực hiện phép tính tuỳ vào số lượng hàng và túi tiền được tính như sau:
| Số lượng hàng | Giá cả mỗi solo vị |
| 1 to lớn 10 | $20 |
| 11 to 19 | $18 |
| 20 khổng lồ 49 | $16 |
| 50 to 100 | $13 |
| Over 101 | $12 |
Công bài toán này rất có thể được thực hiện bằng cách dùng hàm IF lồng nhau. Giải pháp hiểu tựa như như ví dụ như 1, điểm khác hoàn toàn duy độc nhất là các bạn nhân số lượng hàng cụ thể với quý hiếm được trả vị hàm IF (ví dụ: giá chỉ tiền tương ứng mỗi đơn vị chức năng hàng)
Giả sử người tiêu dùng đã nhập con số hàng vào ô B8, phương pháp sẽ như sau:
=B8*IF(B8>=101, 12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”)))))
Và hiệu quả sẽ tương tự như thế này:
Ví dụ này chỉ hướng dẫn cách làm cơ bản, nếu bạn đã hiểu, chúng ta cũng có thể áp dụng hàm IF lồng nhau này vào những công việc của bạn.
Ví dụ, thay vì chưng đặt sẵn chi phí hàng hoá vào công thức, chúng ta có thể dẫn tới những ô gồm chứa quý hiếm này (ô B2 cho B6). Cách thức này để giúp bạn thay đổi nguồn dữ liệu đầu vào mà lại không cần biến hóa 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 đk IF nữa giúp chặn trên, ngăn dưới hoặc cả 2 đầu của khoảng giá trị. Khi số lượng không nằm trong khoảng giá trị, công thức sẽ hiện thị nhiều từ “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ông thức hàm IF nhiều điều kiện được miêu tả trên có trong phần nhiều phiên phiên bản Excel tự 2016-2000. Vào Excel năm nhâm thìn thuộc Office 365, bạn cũng có thể dùng hàm IF theo phong cách như trên.
Những người làm Excel chuyên nghiệp hóa đã thân quen dùng cách làm Mảng (array), thì hàm này có thể làm tương tự như như số đông hàm IF lồng nhau ở bên trên. Tuy hiểu cách làm array khó hơn siêu nhiều, chưa nói đến việc viết ra, hàm Mảng gồm một điểm mạnh – chúng ta cũng có thể dùng một dãy ô gồm chứa điều kiện bạn cần, không buộc phải viết từng điều kiện. Điểm này giúp bí quyết linh hoạt hơn, cùng nếu fan dùng của người tiêu dùng cần biến hóa một đk bất kì tuyệt thêm đk mới, các bạn chỉ cần update lại hàng ô trong bí quyết của mình.
HÀM IF LỒNG NHAU, NHỮNG ĐIỀU CẦN NHỚNhư chúng ta vừa thấy, dùng hàm IF nhiều đk trong Excel không yên cầu phương pháp, công thức cao siêu. Để nâng cao công thức hàm IF lồng nhau và né tránh những lỗi thông thường, hãy luôn luôn nhớ 3 điều cơ bản sau:
trong Excel 2016-2007, chúng ta có thể kết phù hợp 64 điều kiện. Trong các phiên bản cũ rộng từ Excel 2003 về trước, bạn có thể dùng buổi tối đa 7 điều kiện. Luôn nghĩ tới lắp thêm tự các điều kiện trong công thức hàm IF lồng nhau – ví như điều kiện trước tiên đúng, những điều kiện sau sẽ không được kiểm tra. Nếu bí quyết của bạn có tương đối nhiều hơn 5 tác dụng hàm IF, các công thức sau đây rất có thể sẽ buổi tối ưu hơn.THAY THẾ cho HÀM IF LỒNG NHAU vào EXCELĐế tránh khỏi mức số lượng giới hạn 7 điều kiện trong hàm IF ở hầu như phiên bản Excel cũ và giúp công thức của doanh nghiệp trở đề xuất ngắn gọn và cấp tốc hơn, bạn có thể chọn những cách thức sau sửa chữa cho hàm IF những điều kiện.
sử dụng IF kết phù hợp với OR/AND Dùng bí quyết arrayGiống như các hàm Excel khác, hàm CONCATENATE bao gồm thể bao gồm 30 điều kiện trong số phiên bản Excel cũ với 255 điều kiện trong phiên bản Excel năm nhâm thìn – 2007.
Ví dụ, để trả các kết quả khác nhau dựa vào giá trị vào ô B2, chúng ta cũng có thể dùng một trong số những công thức sau:
Hàm IF lồng nhau:
=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor “)))
Hàm CONCATENATE:
=CONCATENATE(IF(C1=”a”, “Excellent”, “”), IF(C1=”b”, “Good”, “”), IF(C1=”c”, “Poor “, “”))
Chức năng nối chuỗi:
=IF(B2=”a”, “Excellent”, “”) và IF(B2=”b”, “Good”, “”) và IF(B2=”c”, “Poor “, “”) & IF(B2=”d”, “Poor “, “”)
Excel cho emcanbaove.edu.vn 365 Excel mang lại emcanbaove.edu.vn 365 dành đến m&#x
E1;y Mac Excel mang lại web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web phầm mềm Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Windows Phone 10 Excel Starter 2010 xem th&#x
EA;m...&#x
CD;t hơn
Hàm IF có thể chấp nhận được bạn tạo so sánh lô-gic thân một cực hiếm và một quý hiếm dự kiến bằng cách kiểm tra điều kiện, rồi trả về tác dụng nếu True tốt False.
=IF(Điều gì đấy là True, thì làm gì, còn nếu như không thì làm cái gi khác)
Vì vậy, một câu lệnh IF có thể có nhì kết quả. Hiệu quả đầu tiên là nếu như so sánh của khách hàng là True, tác dụng thứ nhì là ví như so sánh của chúng ta là False.
Câu lệnh IF cực kỳ mạnh mẽ, đồng thời tạo cơ sở cho nhiều mô hình bảng tính nhưng bọn chúng cũng là lý do cốt lõi của không ít sự ráng bảng tính. Xuất sắc nhất, bạn nên vận dụng câu lệnh IF cho những điều kiện buổi tối thiểu, chẳng hạn như Nam/Nữ, Có/Không/Có thể cơ mà đôi khi, bao gồm thể các bạn sẽ cần reviews các kịch phiên bản phức tạp hơn yêu cầu lồng* 3 hàm IF với nhau.
* "Lồng" ám chỉ vấn đề thực hiện phối kết hợp nhiều hàm với nhau vào một công thức.
Chi ngày tiết kỹ thuật
Dùng hàm IF, một trong những hàm logic để trả về một quý giá nếu điều kiện đúng cùng trả về quý hiếm khác nếu điều kiện sai.
Cú pháp
IF(logical_test, value_if_true,
Ví dụ:
=IF(A2>B2,"Vượt dự toán","OK")
=IF(A2=B2,B4-A4,"")
| Tên đối số | Mô tả |
| logical_test (bắt buộc) | Điều kiện 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 hiệu quả của logical_test là ĐÚNG. |
| value_if_false (tùy chọn) | Giá trị bạn muốn trả về nếu kết quả của logical_test là SAI. |
Ghi chú
Dù Excel có thể chấp nhận được bạn lồng cho 64 hàm IF khác biệt thì các bạn cũng kiêng kị như vậy. Trên sao?
Nhiều câu lệnh IF vẫn yêu cầu bạn quan tâm đến rất lâu để xây dựng đúng chuẩn và bảo đảm an toàn rằng lô-gic của câu lệnh rất có thể tính toán đúng chuẩn qua từng điều kiện cho đến khi kết thúc. Nếu bạn không lồng công thức đúng chuẩn 100 % thì tài năng 75 % là công thức bao gồm thể vận động được dẫu vậy 25 % đã trả về kết quả không hy vọng muốn. Hết sức tiếc, năng lực bạn tìm ra tại sao của 25 % chính là rất nhỏ.
Nhiều câu lệnh IF hoàn toàn có thể rất khó để duy trì, nhất là khi bạn quay trở về một không bao lâu sau đó và nỗ lực tìm hiểu điều mà chúng ta hoặc tệ hơn là một người khác, đang cố gắng thực hiện.
Nếu bạn chạm chán một câu lệnh IF mà có vẻ như kéo dài mãi thì chúng ta nên tạm ngưng và coi lại kế hoạch của mình.
Hãy xem cách tương xứng để tạo nên một câu lệnh IF được lồng phức tạp sử dụng nhiều hàm IF và thời khắc cần để nhận ra rằng đã tới lúc sử dụng một chính sách khác trong kho Excel của bạn.
Ví dụ
Dưới đó là một ví dụ về một câu lệnh IF được lồng tương đối chuẩn chỉnh để biến đổi các điểm kiểm soát của học viên thành điểm theo vần âm tương đương.
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Câu lệnh IF được lồng phức hợp này theo đúng lô-gic dễ hiểu:
Nếu Điểm bình chọn (trong ô D2) to hơn 89 thì học tập viên nhận điểm A
Nếu Điểm Kiểm tra lớn hơn 79 thì học viên dấn điểm B
Nếu Điểm Kiểm tra lớn hơn 69 thì học viên nhấn điểm C
Nếu Điểm Kiểm tra lớn hơn 59 thì học viên thừa nhận điểm D
Các trường hợp khác học tập viên thừa nhận điểm F
Ví dụ ví dụ này tương đối bình an vì không có chức năng mà sự đối sánh tương quan giữa điểm kiểm tra và điểm theo vần âm sẽ biến đổi nên sẽ không còn yêu cầu gia hạn nhiều. Tuy thế còn một vấn đề nữa – điều gì đang xảy ra nếu khách hàng cần phân đoạn những điểm giữa A+, A cùng A- (cùng những điểm khác)? Bây giờ, bạn phải viết lại câu lệnh IF tư điều kiện của bản thân thành câu lệnh tất cả 12 điều kiện! lúc này, công thức của bạn sẽ có dạng như sau:
=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
Câu lệnh này vẫn đúng chuẩn về mặt công dụng và sẽ chuyển động như dự con kiến nhưng rất có thể mất nhiều thời gian để viết và nhiều thời gian hơn để khám nghiệm nhằm đảm bảo rằng câu lệnh thực hiện được hầu hết gì bạn muốn muốn. Một vụ việc hiển nhiên không giống là các bạn đã phải nhập điểm với điểm theo chữ cái tương đương bằng phương pháp thủ công. Điều gì đang xảy ra khi chúng ta vô tình chế tác lỗi tiến công máy? Bây giờ, hãy tưởng tượng chúng ta đang cố gắng thực hiện điều đó 64 lần với các điều kiện tinh vi hơn! vớ nhiên, điều này là có thể nhưng các bạn có thực sự hy vọng mất công sức như vậy và chạm chán những lỗi thực sự khó phát hiện có thể gặp mặt không?
Mẹo: Mọi hàm trong Excel hồ hết yêu cầu dấu ngoặc tròn mở và dấu ngoặc tròn đóng (). Excel sẽ nỗ lực giúp bạn xác minh điểm bước đầu và kết thúc bằng cách tô màu cho các phần không giống nhau trong công thức khi bạn chỉnh sửa công thức. Ví dụ: nếu bạn có nhu cầu chỉnh sửa công thức bên trên, lúc bạn di chuyển con trỏ qua mỗi vệt ngoặc tròn đóng góp “)”, lốt ngoặc tròn mở tương ứng sẽ đưa sang thuộc màu. Điều này hoàn toàn có thể đặc biệt hữu ích trong số công thức tinh vi lồng nhau lúc bạn cố gắng xác định liệu mình bao gồm đủ dấu ngoặc tròn tương ứng chưa.
Ví dụ té sung
Dưới đấy là ví dụ rất thông dụng khi đo lường và thống kê Tiền hoa hồng bán hàng dựa theo các mức doanh thu đạt được.
=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
Công thức này cho biết IF(C9 to hơn 15.000 thì trả về trăng tròn %, IF(C9 lớn hơn 12.500 thì trả về 17,5 %, v.v...
Mặc cho dù ví dụ này khá kiểu như với ví dụ như về Điểm trước đó nhưng bí quyết này vẫn là 1 ví dụ tuyệt vời và hoàn hảo nhất về việc bảo trì các câu lệnh IF mập khó khăn thế nào – bạn cần làm những gì nếu tổ chức của chính bản thân mình quyết định thêm các mức độ bồi thường mới và thậm chí còn có thể thay đổi các giá bán trị xác suất hoặc đô la hiện nay có? các bạn sẽ phải phụ trách xử lý nhiều công việc!
Mẹo: Bạn rất có thể chèn ngắt cái vào thanh cách làm để dễ đọc các công thức nhiều năm hơn. Chỉ việc nhấn ALT+ENTER trước văn bản bạn có nhu cầu ngắt sang chiếc mới.
Dưới đó là ví dụ về kịch phiên bản tiền huê hồng với lô-gic không theo thứ tự:
Bạn hoàn toàn có thể nhìn thấy lỗi không? đối chiếu thứ tự của những so sánh lợi nhuận cho lấy một ví dụ trước đó. Đây là cách đối chiếu nào? chủ yếu xác, máy tự này từ bỏ thấp mang lại cao ($5.000 mang lại $15.000), ko thể so sánh được. Nhưng tại sao đó lại là vụ việc quan trọng? Đây là vấn đề quan trọng vì phương pháp không thể chuyển nhận xét đầu tiên cho phần lớn giá trị trên $5.000. Hãy tưởng tượng chúng ta có lợi nhuận $12.500 – câu lệnh IF đã trả về 10 % do câu lệnh to hơn $5.000 cùng sẽ tạm dừng ở đó. Điều này rất có thể rất nghiêm trọng bởi vì trong không hề ít tình huống, những loại lỗi này sẽ không được thông báo cho đến khi chúng gây tác động tiêu cực. Do vậy, lúc biết rằng có một vài bẫy cực kỳ nghiêm trọng với những câu lệnh IF phức tạp lồng nhau, bạn cũng có thể làm gì? Trong phần đông các trường hợp, chúng ta có thể sử dụng hàm VLOOKUP thay vì chưng tạo công thức tinh vi bằng hàm IF. Khi sử dụng hàm VLOOKUP, trước tiên, bạn cần tạo bảng tham chiếu sau:
=VLOOKUP(C2,C5:D17,2,TRUE)
Công thức này cho biết có thể tìm quý hiếm ở ô C2 vào dải ô C5:C17. Ví như tìm thấy giá trị thì trả về giá bán trị tương xứng từ và một hàng vào cột D.
=VLOOKUP(B9,B2:C6,2,TRUE)
Tương tự, công thức này tìm quý giá ở ô B9 vào dải ô B2:B22. Ví như tìm thấy cực hiếm thì trả về giá bán trị khớp ứng từ và một hàng trong cột C
Lưu ý: Cả nhị hàm VLOOKUP này đều áp dụng tham đối TRUE nghỉ ngơi cuối công thức, nghĩa là cửa hàng chúng tôi muốn những hàm này tra cứu một giá trị kha khá khớp. Nói cách khác, hàm vẫn khớp những giá trị đúng chuẩn trong bảng tra cứu cũng như mọi quý hiếm nằm trong vòng này. Trong trường đúng theo này, bạn phải sắp xếp những bảng tra cứu vãn theo trang bị tự Tăng dần, từ nhỏ dại nhất đến lớn nhất.
Hàm VLOOKUP được trình bày chi tiết hơn nhiều ở đây,nhưng điều này chắc hẳn rằng đơn giản hơn nhiều so cùng với câu lệnh IF lồng phức hợp 12 mức! gồm những tiện ích khác ít rõ ràng hơn như sau:
Bảng tham chiếu VLOOKUP rất rõ ràng và dễ dàng xem.
Có thể dễ dàng dàng update các cực hiếm trong bảng và bạn không khi nào phải chỉnh sửa công thức nếu các điều kiện chũm đổi.
Nếu bạn không thích mọi tín đồ xem hoặc can thiệp vào bảng tham chiếu của mình, chỉ việc đặt bảng tham chiếu vào một trong những trang tính khác.
Bạn gồm biết?
Hiện bao gồm một hàm IFS hoàn toàn có thể thay thế các câu lệnh IF lồng nhau bởi một hàm duy nhất. Do vậy, thay vì ví dụ của shop chúng tôi về những điểm lúc đầu chứa 4 hàm IF lồng nhau:
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Bạn hoàn toàn có thể tạo ra sức thức dễ dàng hơn nhiều chỉ với cùng một hàm IFS duy nhất:
=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")
Hàm IFS siêu tuyệt vì các bạn không buộc phải phải lo ngại về tất cả các câu lệnh IF với dấu ngoặc tròn đó.
Xem thêm: Hướng Dẫn Cách Cố Định Nhiều Dòng Trong Excel 2010, Cách Cố Định Một Dòng, Nhiều Dòng Trong Excel
Lưu ý: Tính năng này chỉ khả dụng nếu như khách hàng có một đk emcanbaove.edu.vn 365. Nếu như khách hàng là emcanbaove.edu.vn 365người đăng ký, hãy đảm bảo an toàn rằng chúng ta có phiên bản Office bắt đầu nhất.Mua hoặc dùng thử emcanbaove.edu.vn 365
Bạn nên thêm trợ giúp?
Bạn luôn hoàn toàn có thể hỏi một chuyên viên trong cộng đồng Kỹ thuật Excel hoặc dấn sự hỗ trợ trong
Cộng đồng trả lời.
Chủ đề liên quan
Video: Hàm IF cải thiện Hàm IFS (các emcanbaove.edu.vn 365, Excel năm 2016 và bắt đầu hơn) Hàm COUNTIF đã đếm những giá trị dựa vào một tiêu chí đơn nhất Hàm COUNTIFS đã đếm các giá trị dựa trên nhiều tiêu chí Hàm SUMIF sẽ tính tổng các giá trị dựa vào một tiêu chí đơn lẻ Hàm SUMIFS công thêm tổng những giá trị dựa vào nhiều tiêu chuẩn Hàm and Hàm OR Hàm VLOOKUP Tổng quan liêu về các công thức trong Excel giải pháp tránh bí quyết bị lỗi Phát hiện lỗi trong phương pháp Hàm lô-gic Excel hàm (theo máy tự bảng chữ cái) Excel hàm (theo thể loại)