Hàm IF lồng nhiều điều kiện trong Excel dễ hiểu nhất
Cập nhật 1 năm trước
68.620
Hàm IF là 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, để có thể giải nhiều vấn đề đa dạng và phức tạp hơn thì chúng ta cần sử dụng hàm IF lồng nhiều điều kiện. Cùng Điện máy XANH chia sẻ về cách sử dụng hàm IF lồng nhiều điều kiện khác nhau dễ hiểu nhất nhé!
Ví dụ trong bài được thực hiện bởi phiên bản Excel 2016, bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017.
1Làm sao để 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 điều kiện. Giả sử bạn có một danh sách học sinh cột A và điểm số tương ứng ở cột B. Chúng ta muốn phân loại theo những điều kiện như sau:
- Xuất sắc: Trên 249
- Tốt: Từ 200 đến 249
- Trung bình: Từ 150 đến 199
- Yếu: Dưới 150
Hãy cùng viết hàm IF lồng nhau dựa trên những tiêu chí trên. Đây chính là cơ hội thực hành bắt đầu từ những điều kiện 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>249, “Xuất sắc”, IF(B2>=200, “Tốt”, IF(B2>150, “Trung bình”, “Yếu”)))
Với nhiều người hàm IF lồng nhau có thể khó hiểu. Bạn sẽ cảm thấy dễ hiểu hơn nếu hàm IF lồng nhau theo cách này:
=IF(B2>249, “Xuất sắc”,
=IF(B2>=200, “Tốt”,
=IF(B2>150, “Trung bình”, “Yếu”)))
Thực tế, công thức yêu cầu của Excel đánh giá điều kiện đầu tiên của công thức hàm IF có đúng không, nếu điều kiện logic, phù hợp thì Excel sẽ trả về giá trị mà 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ý thì hàm sẽ tiếp tục kiểm tra 2 điều kiện còn lại.
IF(kiểm tra if B2>=249, if đúng – trả về “Xuất sắc”, nếu không
IF(kiểm tra if B2>=200, if đúng – trả về “Tốt“, nếu không
IF(kiểm tra if B2>150, if đúng – trả về “Trung bình”, if sai –
trả về “Yếu“)))
Lưu ý: Thứ tự của điều kiện quan trọng
: Thứ tự của điều kiện quan trọng
Có thể thấy điều kiện quan trọng nhất sẽ được viết ưu tiên đầu tiên. Vì Excel sẽ kiểm tra điều kiện theo thứ tự xuất hiện trong công thức, và ngay khi điều kiện được đáp ứng thì điều kiện sau đó sẽ không được đánh giá và kiểm tra.
Xem thêm: Hàm SUMIF
2Hàm IF lồng nhau bao gồm phép tính
Ví dụ 1: Giá cả hàng hoá sẽ thay đổi phụ thuộc vào số lượng cụ thể. Và bạn muốn viết một công thức tính toán tổng giá với số lượng hàng hoá bất kì và đưa 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 vào số lượng hàng và giá cả được tính như sau:
Số lượng hàng
Giá cả mỗi đơn vị
1 tới 10
$20
11 tới 19
$18
20 tới 49
$16
50 tới 100
$13
Trên 101
$12
Công việc này có thể được thực hiện hàm IF lồng nhau, tương tự như ví dụ ở trên, nhưng điểm khác biệt là bạn phải nhân số lượng hàng cụ thể với giá trị được trả bởi hàm IF (giá tiền tương ứng với mỗi đơn vị hàng).
Giả sử người dùng đã nhập số lượng hàng ở ô 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ẽ tượng tự như sau:
Ví dụ này chỉ hướng dẫn các hàm IF lồng nhau cơ bản, nếu bạn đã hiểu chi tiết về ví dụ này bạn có thể áp dụng và nhiều vấn đề khác trong công việc.
Ví dụ 2: Thay vì bạn đặt sẵn giá cả hàng hoá trong công thức, bạn có thể dẫn tới cá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 chặn trên, chặn dưới hoặc chặn 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ị cụm từ “ngoài phạm vi”. Ví dụ:
=IF(OR(B8>200,B8<1), “số=”” lượng=”” ngoài=”” phạm=”” vi”,=”” b8*if(b8=””>=101,12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”)))))) 1),>
Công thức hàm IF nhiều điều kiện được miêu tả bên trên có trong mọi phiên bản Excel từ 2000-2016. Trong Excel 2016 thuộc Office 365, bạn cũng có thể dùng hàm IF theo cách như trên.
Lưu ý:
- Đố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 thì chỉ có thể dùng tối đa 7 điều kiện.
- Luôn nghĩ tới thứ tự các điều kiện trong hàm IF lồng nhau.
- Nếu công thức có nhiều hơn 5 chức năng hàm IF, công thức sau có thể tối ưu hơn.
3Một số hàm kết hợp với hàm IF
Sử dụng AND, OR
Ví dụ việc tính thưởng cho mỗi nhân viên biết nếu nhân viên nữ thuộc phòng kỹ thuật hoặc nhân viên nữ phòng IT thì thưởng 800$. Các trường hợp còn lại được thưởng 780$.
Khi đó tại ô D2 ta có công thức kết hợp giữa AND và OR như sau:
=IF(AND( OR(B2= “IT”,B2= “Kỹ thuật”), C2= “Nữ”), 800,780)
Hàm CONCAT/CONCATENATE
Dưới đây là ví dụ về cách sử dụng hàm CONCAT/CONCATENATE, giả sử bạn có một danh sách học sinh cột A và điểm số tương ứng ở cột B. Phân loại như sau:
=CONCATENATE(IF(B2=”A”, “Xuất sắc”, “”), IF(B2=”B”, “Tốt”, “”), IF(B2=”C”, “Trung bình”, “”), IF(B2=”D”, “Yếu”, “”))
Nối nhiều chuỗi với nhau
Tương tự như ví dụ của trên, ta có công thức như sau:
=IF(B2=”A”, “Xuất sắc”, “”) & IF(B2=”B”, “Tốt”, “”) & IF(B2=”C”, “Trung bình”, “”) & IF(B2=”D”, “Poor “, “”)
4Một số hàm lồng nhiều điều kiện khác
Hàm IFS
Lợi ích của việc sử dụng hàm IFS là bạn có thể nhập 1 chuỗi điều kiện trong 1 công thức. Kết quả theo sau mỗi điều kiện sẽ được áp dụng khi điều kiện đúng, khiến việc đọc và viết công thức dễ dàng hơn.
Ví dụ: Nếu bạn muốn có số phần trăm giảm giá mà mỗi người sử dụng nhận được dựa trên số lượng giấy phép của họ, khi dùng hàm IFS, công thức sẽ như sau:
=IFS(B2>50, 40, B2>40, 35, B2>30, 30, B2>20, 20, B2>10, 15, B2>5, 5, TRUE, 0)
Nếu thay bằng hàm IF lồng ghép, công thức sẽ như thế này:
=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))))))
Hàm VLOOKUP
Không phải lúc nào chúng ta cũng dùng được hàm VLOOKUP, chỉ trong một số điều kiện nhất định chúng ta mới dụng được hàm VLOOKUP thay thế hàm IF lồng. Cụ thể:
- Giá trị trong các điều kiện phải ở dạng số
- Các điều kiện tuân thủ theo thứ tự tăng dần và có tính liên tục
Đề bài: Xác định mức thưởng dựa trên điểm KPI đạt được của từng nhân viên?
Vậy ta có công thức tại ô E3:
=VLOOKUP(D3, $I$3:$J$10, 2,1)
Hàm SWITCH
Hàm SWITCH là so sánh một biểu thức với một danh sách giá trị và trả kết quả theo giá trị khớp đầu tiên. Nếu không tìm được kết quả phù hợp nào có thể trả về giá trị mặc định.
Cấu trúc của hàm SWITCH:
=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
Để hiểu rõ hơn ta cùng tìm hiểu ví dụ: Giả sử, bạn có một số từ viết tắt và bạn muốn trả lại tên đầy đủ cho chúng:
- DR – Duplicate Remover
- MTW – Merge Tables Wizard
- CR – Combine Rows.
Khi sử dụng SWITCH trong Excel:
Nếu bạn sử dụng hàm IF thì cần nhiều thời gian để nhập hơn:
Khi chúng ta sử dụng hàm SWITCH rất hữu ích trong quá trình sử dụng Excel trong công việc, nó sẽ là trợ thủ đắc lực cho chúng ta.
Trên đây là bài viết chia sẻ cho bạn cách dùng hàm IF lồng nhiều điều kiện khác nhau dễ hiểu nhất trong Excel. Mong rằng những thông tin này sẽ giúp ích cho bạn trong việc sử dụng hàm IF lồng nhiều điều kiện.