Trang chủ Các hàm trong Excel Hàm IF nâng cao, cách dùng và ví dụ về hàm IF nâng cao.

Hàm IF nâng cao, cách dùng và ví dụ về hàm IF nâng cao.

đăng bởi Nguyễn Danh Tú
9798 lượt xem

Hàm IF nâng cao là hàm giúp kiểm tra đối chiếu với nhiều nhiều điều kiện và trả về kết quả tương ứng. Bằng cách kết hợp nhiều hàm IF lồng nhau, hoặc kết hợp hàm IF với hàm AND, OR khiến cho hàm IF thông thường có thể kiểm tra đối chiếu với nhiều điều kiện.

Trong bài viết này, Học Excel Cơ Bản sẽ giúp bạn tìm hiểu về cú pháp và cách kết hợp hàm IF với những hàm khác như hàm AND, OR,… để kiểm tra nhiều điều kiện.

1.Chức năng của hàm IF nâng cao trong Excel.

Hàm IF là một trong những hàm logic giúp người dùng kiểm tra một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE hoặc trả về một giá trị khác nếu điều kiện là FALSE.

Hàm IF nhiều điều kiện giúp ta tính toán, kiểm tra và đối chiếu nhiều điều kiện khác nhau và trả về kết quả tương ứng.

2.Cú pháp của hàm IF trong Excel.

IF (Logical_test[value_if_true][value_if_false])

 Trong đó:

  • Logical_test (bắt buộc): Là một giá trị hay biểu thức logic trả về giá trị TRUE(đúng) hoặc FALSE(sai). Bắt buộc phải có. Đối với tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.
  • Value_if_true (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị TRUE hay nói cách khác là điều kiện thỏa mãn.
  • Value_if_false (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị FALSE hay nói cách khác là điều kiện không thỏa mãn.

Trong một số trường hợp bài toán chứa nhiều điều kiện bạn cần sử dụng thêm hàm AND, OR để kết hợp nhiều điều kiện.

  • Bài toán có nhiều điều kiện, các điều kiện xảy ra đồng thời bạn cần sử dụng thêm hàm AND trong biểu thức điều kiện. Cú pháp hàm AND là: AND(logical1, logical2,…)
  • Bài toán có nhiều điều kiện, giá trị trả về chỉ cần thỏa mãn 1 trong các điều kiện bạn sử dụng thêm hàm OR trong biểu thức điều kiện. Cú pháp hàm OR: OR(logical1, logical2,..).Trong đó logical là các biểu thức điều kiện.

3.Hướng dẫn sử dụng hàm IF nâng cao.

3.1.  Sử dụng hàm IF chỉ chứa 1 điều kiện cần xét.

Ví dụ: Đưa ra kết quả thi tuyển vào lớp 10 dựa vào kết quả thi 3 môn, nếu tổng điểm lớn hơn hoặc bằng 24 thì học sinh thi đỗ, ngược lại thí sinh thi trượt.

Hàm IF nâng cao

Hàm IF nâng cao

Hình 1: Hàm IF nâng cao.

Vậy trong trường hợp này chúng ta sẽ sử dụng hàm IF với điều kiện cơ bản nhất là nếu không đúng thì sai. Ở đây chúng ta sẽ gán cho hàm IF điều kiện là nếu tổng điểm lớn hơn hoặc bằng 24 thì “Đỗ” còn tổng điểm nhỏ hơn 24 thì “Trượt”.

Để xếp loại cho học sinh đầu tiên, tại ô I4, ta nhập công thức: =IF(H4>=24,“Đỗ”,“Trượt”)

Trong đó:

  • H4>=24: Là biếu thức so sánh tổng điểm có lớn hơn hoặc bằng 24 hay không?
  • “Đỗ”: Giá trị trả về của hàm IF nếu biếu thức so sánh trả về là đúng.
  • “Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô I4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hàm IF nâng cao

Hàm IF nâng cao

Hình 2: Hàm IF nâng cao.

3.2. Hàm IF nâng cao kết hợp hàm AND.

Thông thường với những yêu cầu thực tế bạn cần thỏa mãn nhiều điều kiện cùng lúc. Chính vì vậy bạn cần kết hợp hàm AND để đảm bảo các yếu tố điều kiện đều được đảm bảo.

Cú pháp của hàm AND: AND(logical1, logical2,…)

Trong đó logical1 và logical2 là 2 mệnh đề logic.

Kết quả trả về của hàm AND:

  • TRUE: Khi tất cả các mệnh đề logic đều đúng.
  • FALSE: Khi có một mệnh đề logic bất kì trong hàm bị sai.

Chúng ta thường đặt hàm AND trong biểu thức logic của hàm IF để xét điều kiện cho hàm IF.

Ví dụ: Đưa ra kết quả thi tuyển vào lớp 10 dựa vào kết quả thi 3 môn, điều kiện xét tuyển là nếu học sinh đỗ phải đạt tổng điểm 3 môn lớn hơn hoặc bằng 18 điểm và không có môn nào đạt điểm 0.

Hàm IF nâng cao

Hàm IF nâng cao

Hình 3: Hàm IF nâng cao.

Vậy trong trường hợp này chúng ta sẽ sử dụng hàm IF kết hợp hàm AND với điều kiện là tổng điểm >=18 và không có môn nào bị điểm 0.

Để xếp loại cho học sinh đầu tiên, tại ô I4, ta nhập công thức: =IF(AND(H4>=18,E4<>0,F4<>0,G4<>0),“Đỗ”,“Trượt”)

Trong đó:

  • AND(H4>=18,E4<>0,F4<>0,G4<>0): Là biếu thức so sánh với các yếu tố:
    • H4>=18: So sánh tổng điểm có lớn hơn 18 hay không?
    • E4<>0: Kiểm tra điểm toán có khác 0 hay không?
    • F4<>0: Kiểm tra điểm văn có khác 0 hay không?
    • G4<>0: Kiểm tra điểm anh có khác 0 hay không?
  • “Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.
  • “Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô I4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hàm IF nâng cao

Hàm IF nâng cao

Hình 4: Hàm IF nâng cao.

Chú ý: Hàm AND chỉ chả về kết quả TRUE khi tất cả các biểu thực logic bên trong đều được thỏa mãn.

3.3. Hàm IF nâng cao kết hợp hàm OR.

Cú pháp của hàm OR trong Excel: OR(logical1, logical2,…)

Trong đó logical1 và logical2 là 2 mệnh đề logic.

Kết quả trả về của hàm OR:.

  • TRUE: Khi có một mệnh đề logic bất kì trong hàm OR là đúng.
  • FALSE: Khi tất cả các mệnh đề bên trong hàm OR đều sai.

Bạn sử dụng kết hợp các hàm IF và hàm OR theo cách tương tự như với hàm AND ở trên.

Ví dụ: Có bảng danh sách học sinh có Họ và Tên, điểm thi lần 1 và điểm thi lần 2. Hoàn thành cột kết quả với điều kiện là: Nếu điểm thi lần 1 >= 20 hoặc điểm thi lần 2 >=30 thì kết quả là “Đỗ”, ngược lại nếu điểm thi lần 1 <20 và điểm thi lần 2 <30 thì kết quả là “Trượt”.

Kiểm tra điều kiện trong Excel

Hàm IF nâng cao

Hình 5: Hàm IF nâng cao.

Vậy trong trường hợp này chúng ta sẽ sử dụng hàm IF kết hợp hàm OR với điều kiện là điểm thi lần 1 >= 20 hoặc điểm thi lần 2 >=30 thì kết quả là “Đỗ”, ngược lại nếu điểm thi lần 1 <20 và điểm thi lần 2 <30 thì keert quả là “Trượt”

Để xếp loại cho học sinh đầu tiên, tại ô E4, ta nhập công thức: =IF(OR(C4>=20,D4>=30),“Đỗ”,“Trượt”)

Trong đó:

  • OR(C4>=20,D4>=30): Là biếu thức so sánh với các yếu tố:
    • C4>=20: Kiểm tra điểm thi lần 1 có >=20 hay khống?
    • D4>=30: Kiểm tra điểm thi lần 2 có >=30 hay khống?
  • “Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.
  • “Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hàm IF nâng cao

Hàm IF nâng cao

Hình 6: Hàm IF nâng cao.

3.4. Hàm IF nâng cao kết hợp hàm AND và hàm OR.

Trong trường hợp bạn phải đánh giá dữ liệu của mình dựa trên nhiều điều kiện, chúng ta sẽ phải sử dụng cả hai hàm AND và OR cùng một lúc.

Ở những ví dụ trên chúng ta đã nắm được cách sử dụng hàm IF kết hợp với hàm AND và hàm IF kết hợp với hàm OR. Nên ở phần này chúng ta chỉ cần kết hợp 2 hàm này lại để đặt điều kiện cho biểu thức logic sao cho khoa học phù hợp với yếu cầu thực tế của bài toán.

Ví dụ: Bảng danh sách học sinh có Họ và Tên, điểm thi lần 1 và điểm thi lần 2. Hoàn thành cột kết quả với điều kiện là: Nếu điểm thi lần 1 >= 20 và điểm thi lần 2 >=25 hoặc điểm thi lần 1 >= 15 và điểm thi lần 2 >=25 thì kết quả là “Đỗ”, ngược lại thì kết quả là “Trượt”.

Hàm IF nâng cao

Hàm IF nâng cao

Hình 7: Hàm IF nâng cao.

Với điều kiện trên, ta có thể phân tích thành 2 điều kiện nhỏ:

  • Điều kiện 1: Điểm lần 1> = 20 và Điểm lần 2> = 25
  • Điều kiện 2: Điểm lần 1> = 15 và Điểm lần 2> = 20

Điều kiện 1 và điều kiện 2 ta viết bằng hàm AND, cuối cùng sử dụng hàm OR kết hợp 2 kiều kiện trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai). Kết quả là bạn sẽ nhận được công thức IF sau với nhiều điều kiện AND/OR:

Để xếp loại cho học sinh đầu tiên, tại ô E4, ta nhập công thức: =IF(OR(AND(C4>=20,D4>=25),AND(C4>=15,D4>=20)),“Đỗ”,“Trượt”)

Trong đó:

  • OR(AND(C4>=20,D4>=25),AND(C4>=15,D4>=20)): Là biếu thức so sánh với các yếu tố:
    • AND(C4>=20,D4>=25): Tương ứng với điều kiện 1.
    • AND(C4>=15,D4>=20): Tương ứng với điều kiện 2.
  • “Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.
  • “Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.

Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Kết hợp hàm AND và hàm OR

Hàm IF nâng cao

Hình 8: Hàm IF nâng cao.

3.5. Hàm IF lồng nhau.

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ó một danh sách học sinh ở cột B và điểm số tương ứng ở cột C, bạn muốn phân loại ra theo những điều kiện sau:

  • Giỏi: trên 8
  • Khá: từ 6.5 đến 8
  • Trung bình: từ 5 đến 6.5
  • Yếu: dưới 5
Kiểm tra điều kiện trong Excel

Hàm IF nâng cao

Hình 9: Hàm IF nâng cao.

Chúng ta hãy cùng viết hàm IF lồng nhau dựa trên những tiêu chí trên. Hãy coi đây là cơ hội thực hành, bắt đầu từ những điều kiện 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 tại ô D4 của chúng ta sẽ như sau:

=IF(C4<5,”Yếu”,IF(C4<6.5,”Trung Bình”,IF(C4<8,”Khá”,”Giỏi”)))

Với nhiều người, hàm IF lồng nhau có thể khó hiểu. Nhưng sẽ dễ hơn nếu bạn nhìn công thức hàm IF lồng nhau trên theo cách này:

=IF(C4<5,”Yếu”,

= IF(C4<6.5,”Trung Bình”,

= IF(C4<8,”Khá”,”Giỏi”)))

Khi chúng ta lồng nhiều hàm IF, Excel sẽ ưu tiên hàm IF được viết trước, nếu hàm IF ở trước là đúng thì câu lệnh sẽ dừng những câu lệnh IF sau đó sẽ không được xét đến.

IF(kiểm tra if C4<5, if đúng => “Yếu”, nếu if sai thì xét

IF(kiểm tra if C4<6.5, if đúng => “Trung bình”, nếu if sai thì xét

IF(kiểm tra if C4<8, if đúng => “Khá”, if sai =>” Yếu “)))

Sau khi nhập xong công thức cho ô D4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:

Hàm IF lồng nhau trong Excel

Hàm IF nâng cao

Hình 10: Hàm IF nâng cao.

Từ ví dụ trên, có thể thấy điều kiện quan trọng nhất sẽ được viết đầ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 một điều kiện được đáp ứng, điều kiện sau đó sẽ không được đánh giá.

 4. Lưu ý khi sử dụng hàm IF nâng cao trong Excel.

Như bạn vừa thấy, dùng hàm IF nhiều điều kiện trong Excel không đòi hỏi phương pháp, công thức cao siêu. Để cải thiện công thức hàm IF lồng nhau và tránh những lỗi thông thường, hãy luôn nhớ những điều cơ bản sau:

  • Trong Excel 2016-2007, bạn có thể kết hợp 64 điều kiện. Trong các phiên bản cũ hơn từ Excel 2003 về trước, bạn 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 công thức hàm IF lồng nhau – nếu điều kiện đầu tiên đúng, những điều kiện sau sẽ không được kiểm tra.
  • Nếu công thức của bạn có nhiều hơn 5 chức năng hàm IF, các công thức sau đây có thể sẽ tối ưu hơn.
  • Hàm IF không phân biệt chữ hoa và chữ thường. Cũng giống như phần lớn những hàm khác, hàm IF được mặc định không phân biệt chữ hoa hay chữ thường. Điều này có nghĩa rằng các biểu thức logic có chứa ký tự không thể phân biệt được kiểu chữ hoa hay thường trong công thức hàm IF. Ví dụ khi so sánh một ô nào đó với “Hà Nội” thì hàm IF sẽ hiểu Hà Nội, hà nội, HÀ NỘI, … là như nhau.

Tổng kết.

Như vậy với bài viết này, Hocexcelcoban.com đã chia sẻ cho các bạn cách dùng hàm IF nâng cao để kết hợp với hàm AND và hàm OR trong Excel. Nếu có bất kỳ thắc mắc gì có thể comment ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất. Chúc các bạn thành công.

Video hướng dẫn.

Gợi ý học tập mở rộng.

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Các bài viết liên quan:

Hàm IF trong Excel
Hàm IF nhiều điều kiện
Hàm IF trong Google Sheet
Hàm VLOOKUP trong Excel
Cách sử dụng hàm VLOOKUP

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 2 votes
Article Rating

BÀI VIẾT LIÊN QUAN

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x