if là một hàm phổ biến và được nhiều người sử dụng trong quá trình làm việc trong excel. tuy nhiên, để giải các bài toán đa dạng và phức tạp hơn, chúng ta cần sử dụng hàm if với nhiều điều kiện lồng nhau. Hãy cùng dienmay xanh chia sẻ cách sử dụng hàm if để lồng nhiều điều kiện khác nhau một cách dễ dàng nhất nhé!
i. cách lồng nhiều hàm if
.
Dưới đây là một ví dụ điển hình về lồng nhiều hàm if với các điều kiện. giả sử bạn có một danh sách các sinh viên trong cột a và điểm số tương ứng của họ trong cột b. chúng tôi muốn phân loại theo các điều kiện sau:
- xuất sắc: hơn 249
- tốt: từ 200 đến 249
- trung bình: 150 đến 199
- yếu: dưới 150
- đối với excel 2007 đến 2016, bạn có thể kết hợp 64 điều kiện. với phiên bản 2003, chỉ 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 một hàm if lồng nhau.
- nếu công thức có nhiều hơn 5 hàm if, thì công thức sau có thể được tối ưu hóa.
- các giá trị trong điều kiện phải là số
- điều kiện theo thứ tự tăng dần và liên tục
- dr – loại bỏ trùng lặp
- mtw – thuật sĩ tham gia bảng
- cr – kết hợp các hàng.
hãy viết một hàm if lồng nhau dựa trên các tiêu chí trên. đây là cơ hội để thực hành bắt đầu với các thuật ngữ quan trọng nhất, 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 như sau:
= if (b2 & gt; 249, “xuất sắc”, nếu (b2 & gt; = 200, “tốt”, nếu (b2 & gt; 150, “trung bình”, “yếu”)))
đối với nhiều người, các hàm if lồng nhau có thể gây nhầm lẫn. Bạn sẽ dễ hiểu hơn nếu hàm if được lồng như thế này:
= if (b2> 249, “xuất sắc”,
= if (b2> = 200, “tốt”,
= if (b2> 150, “trung bình”, “yếu”)))
Trên thực tế, công thức yêu cầu excel đánh giá nếu điều kiện đầu tiên của công thức hàm if là đúng, nếu điều kiện là logic, nó sẽ trả về giá trị bạn muốn khi điều kiện được đáp ứng. nếu điều kiện đầu tiên không hợp lệ, hàm sẽ tiếp tục kiểm tra 2 điều kiện còn lại.
if (kiểm tra nếu b2> = 249, nếu đúng – trả về “ xuất sắc “, nếu không
if (kiểm tra nếu b2> = 200, nếu đúng – trả về “ tốt “, nếu không
if (kiểm tra nếu b2> 150, nếu đúng – trả về “ trung bình “, nếu sai –
trả về “ yếu “))) lưu ý : thứ tự điều kiện quan trọng
Bạn có thể thấy rằng điều kiện quan trọng nhất sẽ được viết dưới dạng ưu tiên đầu tiên. bởi vì excel sẽ kiểm tra điều kiện theo thứ tự xuất hiện trong công thức và ngay sau khi điều kiện được đáp ứng, điều kiện sau đó sẽ không được đánh giá hoặc kiểm tra.
xem thêm: hàm sumif
ii. lồng nhau nếu hàm bao gồm phép tính
ví dụ 1 : Giá của hàng hóa sẽ thay đổi tùy thuộc vào 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 nên kiểm tra các điều kiện và thực hiện tính toán dựa trên số hàng và giá được tính như vậy:
Công việc này có thể được thực hiện bằng cách sử dụng hàm if lồng nhau, tương tự như ví dụ trước, nhưng sự khác biệt là bạn phải nhân số hàng cụ thể với giá trị được trả bởi hàm if (giá tương ứng trên mỗi đơn vị).
Giả sử người dùng đã nhập số hàng vào ô b8, công thức sẽ là:
= b8 * if (b8> = 101, 12, if (b8> = 50, 13, if (b8> = 20, 16, if (b8> = 11, 18, if (b8> = ) 1, 20, “”))))))
kết quả sẽ như thế này:
Ví dụ này chỉ dạy các hàm if lồng nhau cơ bản, nếu hiểu chi tiết về ví dụ này, bạn có thể áp dụng nó và nhiều bài toán khác trong công việc của mình.
ví dụ 2 : Thay vì chỉ định giá của mặt hàng 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, “”))))))
Bạn có thể thêm điều kiện hàm if giúp khóa giá trị trên, dưới hoặc hai điểm cực trị của phạm vi. khi số lượng nằm ngoài phạm vi, công thức sẽ hiển thị thuật ngữ “ngoài phạm vi”. ví dụ:
= 101,12, nếu (b8> = 50, 13, nếu (b8> = 20, 16, nếu (b8> = 11, 18, nếu (b8> = 1, 20,“ ”))) )))))
Công thức hàm if đa điều kiện được mô tả ở trên có sẵn trong tất cả các phiên bản excel từ 2000-2016. 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. lưu ý :
iii. một số hàm kết hợp với hàm if
.
sử dụng và hoặc
Ví dụ, khi tính toán tiền thưởng cho mỗi nhân viên biết rằng nếu một nhân viên ở bộ phận kỹ thuật hoặc một nhân viên trong bộ phận CNTT, thì tiền thưởng là 800 đô la. các trường hợp còn lại được thưởng $ 780.
vì vậy trong ô d2, chúng tôi có một công thức kết hợp và và hoặc như sau:
= if (y (hoặc (b2 = “that”, b2 = “Technical”), c2 = “woman”), 800,780)
hàm nối / ghép nối
Dưới đây là một ví dụ về cách sử dụng hàm nối / ghép nối, giả sử bạn có danh sách sinh viên trong cột a và điểm tương ứng của họ trong cột b. được phân loại như sau:
= nối (if (b2 = ”a”, “xuất sắc”, “”), nếu (b2 = ”b”, “tốt”, “”), nếu (b2 = ”c”, “ trung bình ”,“ ”), nếu (b2 =” d ”,“ yếu ”,“ ”))
nối nhiều chuỗi
Tương tự như ví dụ trước, chúng tôi có công thức sau:
= if (b2 = “a”, “xuất sắc”, “”) & amp; if (b2 = “b”, “good”, “”) & amp; if (b2 = “c”, “trung bình”, “”) & amp; if (b2 = “d”, “kém”, “”)
iv. một số hàm lồng nhiều điều kiện khác
hàm if
Lợi thế của việc sử dụng hàm ifs là bạn có thể nhập 1 chuỗi điều kiện vào 1 công thức. kết quả tuân theo từng điều kiện sẽ được áp dụng khi điều kiện là đúng, giúp công thức dễ đọc và dễ viết hơn.
ví dụ: nếu bạn muốn nhận phần trăm chiết khấu mà mỗi người dùng nhận được dựa trên số lượng giấy phép của họ, bằng cách sử dụng hàm ifs, công thức sẽ giống như sau:
= if (b2> 50, 40, b2> 40, 35, b2> 30, 30, b2> 20, 20, b2> 10, 15, b2> 5, 5, true, 0)
nếu bạn thay thế nó bằng một hàm if lồng nhau, công thức sẽ giống như sau:
= if (b2> 50, 40, if (b2> 40, 35, if (b2> 30, 30, if (b2> 20, 20, if (b2> 10, 15, if (b2 >)); 5, 5, 0)))))))
chức năng tìm kiếm
Không phải lúc nào chúng ta cũng sử dụng hàm vlookup, chỉ trong những điều kiện nhất định, chúng ta mới có thể sử dụng hàm vlookup thay vì hàm if lồng nhau. cụ thể:
title: xác định mức thưởng dựa trên điểm kpi mà mỗi nhân viên đạt được?
vì vậy chúng tôi có công thức trong ô e3:
= vlookup (d3, $ i $ 3: $ j $ 10, 2,1)
thay đổi chức năng
Hàm chuyển đổi
so sánh một biểu thức với một danh sách các giá trị và trả về kết quả phù hợp đầu tiên. nếu không tìm thấy kết quả phù hợp nào, giá trị mặc định có thể được trả về.
thay đổi cấu trúc chức năng:
= change (expression, value1, result1, [default hoặc value2, result2],… [default hoặc value3, result3]) lưu ý: chức năng thay đổi chỉ được hỗ trợ bởi phiên bản microsoft excel 2019 và microsoft 365
Để hiểu rõ hơn, hãy xem một ví dụ: Giả sử bạn có một số chữ viết tắt và bạn muốn trả lại tên đầy đủ của chúng:
khi sử dụng switch trong excel:
nếu bạn sử dụng hàm if, sẽ mất nhiều thời gian hơn để viết:
Khi chúng ta sử dụng hàm switch, một hàm rất hữu ích trong quá trình sử dụng excel trong công việc, nó sẽ là một trợ thủ đắc lực cho chúng ta.
Trên đây là bài viết chia sẻ đến các bạn cách sử dụng hàm if để lồng nhiều điều kiện khác nhau dễ hiểu hơn trong excel. Tôi hy vọng thông tin này sẽ giúp bạn sử dụng hàm if với nhiều điều kiện lồng nhau. cảm ơn vì đã đọc bài viết.