Hàm IF lồng ghép, kết hợp nhiều điều kiện trong một công thức duy nhất

Chia sẻ bài viết này:
  • 4
  •  
  •  
  •  
  •  
  •  
  •   
  •   

Trong 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 nhiều điều kiện trong Excel và đưa ra các ví dụ về những hàm IF lồng nhau hay được dùng trong công việc.

Nếu được hỏi chức năng nào trong Excel mà bạn dùng nhiều nhất, bạn sẽ trả lời như thế nào? Với rất nhiều người, đó chính là chức năng hàm IF trong Excel. Công thức hàm IF nếu chỉ có một điều kiện sẽ rất dễ viết. Nhưng nếu số liệu của bạn cần phải được kiểm tra bằng thuật toán phức tạp với rất nhiều điều kiện? Trong trường hợp này, bạn có thể sử dụng nhiều chức năng IF trong một công thức, và công thức hàm IF lúc này được gọi 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 điều kiện và nhận được các giá trị khác nhau phụ thuộc và kết quả kiểm tra với duy nhất một công thức.

Trong các phiên bản Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể kết hợp đến 64 điều kiện trong một công thức. Nhưng với Excel 2003 trở về trước, công thức chỉ có tối đa 7 điều kiện.

Trong bài viết này, bạn sẽ thấy những ví dụ về hàm IF lồng nhau cùng với phần giải thích chi tiết về cấu trúc và logic của công thức hàm IF đó.

Xem thêm:

CÔNG THỨC HÀM IF LỒNG NHAU CƠ BẢN   

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

  • Excellent: trên 249
  • Good: từ 200 đến 249
  • Satisfactory: từ 150 đến 199
  • Poor: dưới 150

Chúng ta hãy cùng viết hàm IF lồng nau 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 của chúng ta 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 LOGIC HÀM IF LỒNG NHAU:

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(B2>249, “Excellent”,

=IF(B2>=200, “Good”,

=IF(B2>150, “Satisfactory”, “Poor”)))

Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.

Với khoá học này học viên sẽ nắm được: :

  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

Thực tế, công thức yêu cầu Excel đánh giá xem điều kiện đầu tiên của công thức hàm IF có logic không, nếu điều kiện logic, Excel sẽ trả về giá trị mà bạn cung cấp khi điều kiện được đáp ứng. Nếu điều kiện đầu tiên không được đáp ứng, kiểm tra điều kiện 2 và tiếp tục.

IF(kiểm tra if B2>=249, if đúng – trả “Excellent”, nếu không

IF(kiểm tra if B2>=200, if đúng – trả “Good”, nếu không

IF(kiểm tra if B2>150, if đúng – trả “Satisfactory”, if sai –

Trả ” Poor “)))

Thứ tự của điều kiện rất quan trọng

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á.

HÀM IF LỒNG NHAU BAO GỒM PHÉP TÍNH

Yêu cầu như sau: 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 tuỳ 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 to 10 $20
11 to 19 $18
20 to 49 $16
50 to 100 $13
Over 101 $12

Công việc này có thể được thực hiện bằng cách dùng hàm IF lồng nhau. Cách hiểu tương tự như ví dụ 1, điểm khác biệt duy nhất là bạn nhân số lượng hàng cụ thể với giá trị được trả bởi hàm IF (ví dụ: giá tiền tương ứng mỗi đơn vị hàng)

Giả sử người dùng đã nhập số lượng hàng vào ô B8, công thức 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à kết 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, bạn 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ì đặ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, “”)))))

Hoặc bạn có thể thêm điều kiện IF nữa giúp chặn trên, chặ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ị cụm từ “out of the range”. Ví dụ:

=IF(OR(B8>200,B8<1), “Qty. out of range”, B8*IF(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 lồng nhau được miêu tả bên trên có trong mọi phiên bản Excel từ 2016-2000. Trong Excel 2016 thuộc Office 365, bạn cũng có thể dùng hàm IF theo cách như trên.

Những người làm Excel chuyên nghiệp đã quen dùng công thức Mảng (array), thì hàm này có thể làm tương tự như những hàm IF lồng nhau ở bên trên. Tuy hiểu công thức array khó hơn rất nhiều, chưa nói đến việc viết ra, hàm Mảng có một ưu điểm – bạn có thể dùng một dãy ô có chứa điều kiện bạn cần, không cần viết từng điều kiện. Điểm này giúp công thức linh hoạt hơn, và nếu người dùng của bạn cần thay đổi một điều kiện bất kì hay thêm điều kiện mới, bạn chỉ cần cập nhật lại dãy ô trong công thức của mình.

HÀM IF LỒNG NHAU, NHỮNG ĐIỀU CẦN NHỚ

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ớ 3 điều cơ bản sau:

  1. 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.
  2. 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.
  3. 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.

THAY THẾ CHO HÀM IF LỒNG NHAU TRONG EXCEL

Đế tránh khỏi mức giới hạn 7 điều kiện trong hàm IF ở những phiên bản Excel cũ và giúp công thức của bạn trở nên ngắn gọn và nhanh hơn, bạn có thể chọn những phương pháp sau thay thế cho hàm IF với nhiều điều kiện.

  1. Để kiểm tra nhiều điều kiện, sử dụng hàm LOOKUP, VLOOKUP, INDEX/MATCH hoặc CHOOSE.
  2. Dùng IF kết hợp với OR/AND
  3. Dùng công thức array
  4. Dùng hàm CONCATENATEphương pháp nối chuỗi

Giống như các hàm Excel khác, hàm CONCATENATE có thể bao gồm 30 điều kiện trong các phiên bản Excel cũ và 255 điều kiện trong bản Excel  2016 – 2007.

Ví dụ, để trả các kết quả khác nhau dựa vào giá trị trong ô B2, bạn 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”, “”) & IF(B2=”b”, “Good”, “”) & IF(B2=”c”, “Poor “, “”) & IF(B2=”d”, “Poor “, “”)

Xem thêm:

Cách tính hoa hồng bán hàng bằng hàm IF lồng nhau trên Excel

Hướng dẫn cách lồng ghép hàm, viết hàm lồng nhau trong Excel

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Hãy tham gia ngay tại địa chỉ:


Chia sẻ bài viết này:
  • 4
  •  
  •  
  •  
  •  
  •  
  •   
  •