Trong Excel, mặc dù hàm IF rất cần thiết cho các bài kiểm tra logic cơ bản nhưng các điều kiện phức tạp thường yêu cầu các câu lệnh IF lồng nhau để nâng cao khả năng xử lý dữ liệu. Trong hướng dẫn toàn diện này, chúng tôi sẽ trình bày chi tiết các khái niệm cơ bản về IF lồng nhau, từ cú pháp đến ứng dụng thực tế, bao gồm cả sự kết hợp của IF lồng nhau với các điều kiện AND/OR. Ngoài ra, chúng tôi sẽ chia sẻ cách cải thiện khả năng đọc của các hàm IF lồng nhau cũng như một số mẹo về IF lồng nhau và khám phá các lựa chọn thay thế mạnh mẽ như VLOOKUP, IFS, v.v. để làm cho các phép toán logic phức tạp dễ sử dụng hơn và hiệu quả hơn.

• Cú pháp IF lồng nhau
• Ví dụ thực tế về IF lồng nhau
• Lồng nhau nếu có điều kiện AND/OR

• Làm cho IF lồng nhau dễ đọc
• Thứ tự của các hàm IF lồng nhau
• Số và văn bản phải được xử lý khác nhau
• Hạn chế của IF lồng nhau

• Sử dụng hàm VLOOKUP
• Sử dụng IFS
• Sử dụng CHỌN và MATCH

Hàm IF trong Excel so với các câu lệnh IF lồng nhau

Hàm IF và các câu lệnh IF lồng nhau trong Excel phục vụ các mục đích tương tự nhưng khác nhau đáng kể về độ phức tạp và ứng dụng của chúng.

Sử dụng IF lồng nhau

Phần này trình bày cách sử dụng cơ bản của câu lệnh IF lồng nhau trong Excel, bao gồm cú pháp, ví dụ thực tế và cách sử dụng chúng với điều kiện AND hoặc OR.

Cú pháp IF lồng nhau

Hiểu cú pháp của hàm là nền tảng để ứng dụng hàm đúng và hiệu quả trong Excel. Hãy bắt đầu với cú pháp của các câu lệnh if lồng nhau.

cú pháp:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Lập luận:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Hãy nhớ rằng, trong cấu trúc IF lồng nhau, mỗi điều kiện tiếp theo chỉ được đánh giá nếu tất cả các điều kiện trước đó là FALSE. Việc kiểm tra tuần tự này rất quan trọng để hiểu cách hoạt động của các IF lồng nhau.

Ví dụ thực tế về IF lồng nhau

Bây giờ, hãy đi sâu vào cách sử dụng IF lồng nhau với hai ví dụ thực tế.

Ví dụ 1: Hệ thống chấm điểm

Như được hiển thị trong ảnh chụp màn hình bên dưới, giả sử bạn có một danh sách điểm của học sinh và muốn chấm điểm dựa trên những điểm này. Bạn có thể sử dụng IF lồng nhau để hoàn thành nhiệm vụ này.

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả. Sau đó kéo Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

Ví dụ 2: Tính hoa hồng bán hàng

Hãy tưởng tượng một tình huống trong đó các đại diện bán hàng nhận được tỷ lệ hoa hồng khác nhau dựa trên thành tích bán hàng của họ. Như được hiển thị trong ảnh chụp màn hình bên dưới, bạn muốn tính hoa hồng của nhân viên bán hàng dựa trên các ngưỡng bán hàng khác nhau này và các câu lệnh IF lồng nhau có thể giúp bạn thực hiện điều này.

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả. Sau đó kéo Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Lồng nhau nếu có điều kiện AND/OR

Trong phần này, tôi sửa đổi ví dụ đầu tiên ở trên "hệ thống chấm điểm" để trình bày cách kết hợp điều kiện IF lồng nhau với điều kiện AND hoặc OR trong Excel. Trong ví dụ chấm điểm sửa đổi, tôi đã đưa ra một điều kiện bổ sung dựa trên "Tỷ lệ đi học".

Sử dụng if lồng nhau với điều kiện AND

Nếu học sinh đáp ứng cả tiêu chí về điểm số và chuyên cần, các em sẽ được tăng điểm. Ví dụ: một học sinh có điểm từ 60 trở lên và tỷ lệ đi học từ 95% trở lên sẽ được nâng cấp một cấp, chẳng hạn như từ A lên A+, B lên B+, v.v. Tuy nhiên, nếu tỷ lệ đi học dưới 95%, việc chấm điểm sẽ tuân theo tiêu chí dựa trên điểm ban đầu. Trong những trường hợp như vậy, chúng ta cần sử dụng câu lệnh IF lồng nhau với điều kiện AND.

Chọn một ô trống (trong trường hợp này là D2), nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả. Sau đó kéo Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Sử dụng if lồng nhau với điều kiện OR

Trong trường hợp này, điểm của học sinh sẽ được nâng lên một cấp nếu điểm của học sinh đó đạt từ 95 trở lên hoặc nếu tỷ lệ đi học đều từ 95% trở lên. Đây là cách chúng ta có thể thực hiện nó bằng cách sử dụng các điều kiện IF và OR lồng nhau.

Chọn một ô trống (trong trường hợp này là D2), nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả. Sau đó kéo Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Mẹo và thủ thuật cho hàm IF lồng nhau

Phần này bao gồm bốn mẹo và thủ thuật hữu ích cho IF lồng nhau.

Làm cho IF lồng nhau dễ đọc

Một câu lệnh IF lồng nhau điển hình có thể trông nhỏ gọn nhưng khó giải mã.

Trong công thức sau đây, thật khó để xác định nhanh chóng nơi một điều kiện kết thúc và điều kiện khác bắt đầu, đặc biệt khi độ phức tạp tăng lên.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Giải pháp: Thêm ngắt dòng và thụt lề

Để làm cho IF lồng nhau dễ đọc, bạn có thể chia công thức thành nhiều dòng với mỗi IF lồng nhau trên một dòng mới. Trong công thức, chỉ cần đặt con trỏ trước IF và nhấn phím Alt + Enter.

Sau khi phá vỡ công thức trên sẽ hiển thị như sau:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

Định dạng này làm cho nó rõ ràng hơn về vị trí của từng điều kiện và kết quả đầu ra tương ứng, nâng cao khả năng đọc của công thức.

Thứ tự của các hàm IF lồng nhau

Thứ tự của các điều kiện logic trong công thức IF lồng nhau rất quan trọng vì nó xác định cách Excel đánh giá các điều kiện này và do đó ảnh hưởng đến kết quả cuối cùng của công thức.

Công thức đúng

Trong ví dụ về Hệ thống chấm điểm, chúng tôi sử dụng công thức sau để chấm điểm dựa trên điểm số.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Excel đánh giá các điều kiện trong công thức IF lồng nhau một cách tuần tự, từ điều kiện đầu tiên đến điều kiện cuối cùng. Công thức này kiểm tra ngưỡng điểm cao nhất trước tiên (>=90 cho điểm "A"), sau đó chuyển sang ngưỡng thấp hơn. Nó đảm bảo rằng điểm số được so sánh với điểm cao nhất mà nó đủ điều kiện. Nếu điều kiện đầu tiên đúng (A2>=90), nó sẽ trả về "A" và không đánh giá thêm bất kỳ điều kiện nào nữa.

Công thức thứ tự sai

Nếu thứ tự các điều kiện bị đảo ngược, bắt đầu từ ngưỡng thấp nhất, nó sẽ trả về kết quả không chính xác.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

Trong công thức sai này, điểm 95 sẽ ngay lập tức đáp ứng điều kiện đầu tiên B2>=60 và bị gán nhầm điểm “D”.

Số và văn bản phải được xử lý khác nhau

Phần này sẽ chỉ cho bạn cách xử lý số và văn bản khác nhau trong các câu lệnh IF lồng nhau.

Số

Các con số được sử dụng để so sánh số học và tính toán. Trong các câu lệnh IF lồng nhau, bạn có thể so sánh trực tiếp các số bằng cách sử dụng các toán tử như >, <, =, >= và <=.

bản văn

Trong các câu lệnh IF lồng nhau, văn bản phải là đặt trong dấu ngoặc kép. Xem A, B, C, D và F trong công thức sau:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Hạn chế của IF lồng nhau

Phần này liệt kê một số hạn chế và nhược điểm của IF lồng nhau.

Độ phức tạp và dễ đọc:

Mặc dù Excel cho phép bạn lồng tối đa 64 hàm IF khác nhau nhưng bạn không nên làm như vậy. Càng nhiều cấp độ lồng nhau, công thức càng trở nên phức tạp. Điều này có thể dẫn đến các công thức khó đọc, khó hiểu và khó duy trì.

Dễ bị lỗi:

Hơn nữa, các câu lệnh IF lồng nhau phức tạp có thể dễ mắc lỗi và khó gỡ lỗi hoặc sửa đổi.

Khó mở rộng hoặc mở rộng quy mô:

Nếu logic của bạn thay đổi hoặc bạn cần thêm nhiều điều kiện hơn thì các IF được lồng sâu có thể khó sửa đổi hoặc mở rộng.

Hiểu những hạn chế này là chìa khóa để sử dụng câu lệnh IF lồng nhau một cách hiệu quả trong Excel. Thông thường, việc kết hợp các IF lồng nhau với các chức năng khác hoặc tìm kiếm các phương pháp thay thế có thể dẫn đến các giải pháp hiệu quả và dễ bảo trì hơn.

Các lựa chọn thay thế cho IF lồng nhau

Phần này liệt kê một số hàm trong Excel có thể được sử dụng thay thế cho các câu lệnh IF lồng nhau.

Sử dụng hàm VLOOKUP

Bạn có thể sử dụng hàm VLOOKUP thay vì các câu lệnh IF lồng nhau để thực hiện hai ví dụ thực tế trên. Đây là cách bạn có thể làm điều đó:

Ví dụ 1: Hệ thống chấm điểm bằng VLOOKUP

Ở đây tôi sẽ hướng dẫn cách sử dụng VLOOKUP để chấm điểm dựa trên điểm số.

Bước 1: Tạo bảng tra cứu điểm

Trước tiên, bạn cần tạo một bảng tra cứu (chẳng hạn như E1:F6 trong trường hợp này) cho khoảng điểm và các cấp độ tương ứng. Chú thích: Điểm ở cột đầu tiên của bảng phải được sắp xếp theo thứ tự tăng dần.

Bước 2: Áp dụng hàm VLOOKUP để chấm điểm

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn nút đăng ký hạng mục thi chìa khóa để đạt được lớp một. Chọn ô công thức này và kéo nó Điền vào Xử lý xuống để lấy các điểm còn lại.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Ví dụ 2: Tính hoa hồng bán hàng với VLOOKUP

Bạn cũng có thể sử dụng VLOOKUP để thực hiện tính toán hoa hồng bán hàng trong Excel. Hãy làm như sau.

Bước 1: Tạo bảng tra cứu điểm

Đầu tiên, bạn cần tạo bảng tra cứu doanh số và tỷ lệ hoa hồng tương ứng, chẳng hạn như E2:F4 trong trường hợp này. Chú thích: Doanh số ở cột đầu tiên của bảng phải được sắp xếp theo thứ tự tăng dần.

Bước 2: Áp dụng hàm VLOOKUP để chấm điểm

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn phím Enter để nhận hoa hồng đầu tiên. Chọn ô công thức này và kéo Fill Handle của nó xuống để nhận phần còn lại của kết quả.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Sử dụng IFS

Sản phẩm hàm IFS đơn giản hóa quy trình bằng cách loại bỏ nhu cầu lồng nhau và làm cho các công thức dễ đọc và quản lý hơn. Nó tăng cường khả năng đọc và hợp lý hóa việc xử lý nhiều kiểm tra có điều kiện. Để sử dụng hàm IFS, hãy đảm bảo bạn đang sử dụng Excel 2019 trở lên hoặc có đăng ký Office 365. Hãy xem nó có thể được áp dụng như thế nào trong các ví dụ thực tế.

Ví dụ 1: Hệ thống chấm điểm với IFS

Giả sử tiêu chí chấm điểm giống như trước, hàm IFS có thể được sử dụng như sau:

Chọn một ô trống, chẳng hạn như C2, nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả đầu tiên. Chọn ô kết quả này và kéo nó Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

Ví dụ 2: Tính hoa hồng bán hàng với IFS

Đối với kịch bản tính hoa hồng bán hàng, hàm IFS được áp dụng như sau:

Chọn một ô trống, chẳng hạn như C2, nhập công thức sau và nhấn đăng ký hạng mục thi để có được kết quả đầu tiên. Chọn ô kết quả này và kéo nó Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

Sử dụng CHỌN và MATCH

Cách tiếp cận CHỌN và MATCH có thể hiệu quả hơn và dễ quản lý hơn so với các câu lệnh IF lồng nhau. Phương pháp này đơn giản hóa công thức và giúp việc cập nhật hoặc thay đổi trở nên đơn giản hơn. Dưới đây tôi sẽ trình bày cách sử dụng kết hợp các hàm CHOOSE và MATCH để xử lý hai ví dụ thực tế trong bài viết này.

Ví dụ 1: Hệ thống chấm điểm với CHOOSE và MATCH

Bạn có thể sử dụng kết hợp các hàm CHỌN và MATCH để chấm điểm dựa trên các điểm khác nhau.

Bước 1: Tạo mảng Tra cứu với các giá trị tìm kiếm

Trước tiên, bạn cần tạo một phạm vi ô chứa các giá trị ngưỡng mà MATCH sẽ tìm kiếm, chẳng hạn như $E$2:$E$6 trong trường hợp này. Chú thích: Các số trong phạm vi này phải được sắp xếp theo thứ tự tăng dần để hàm MATCH hoạt động chính xác khi sử dụng loại khớp gần đúng.

Bước 2: Áp dụng CHOOSE và MATCH để chấm điểm

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn nút đăng ký hạng mục thi chìa khóa để đạt được lớp một. Chọn ô công thức này và kéo nó Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Ví dụ 2: Tính hoa hồng bán hàng với IFS

Việc sử dụng kết hợp CHỌN và MATCH để tính toán hoa hồng bán hàng cũng có thể có hiệu quả, đặc biệt khi tỷ lệ hoa hồng dựa trên ngưỡng bán hàng được chỉ định. Hãy xem chúng ta có thể làm như thế nào.

Bước 1: Tạo mảng Tra cứu với các giá trị tìm kiếm

Trước tiên, bạn cần tạo một phạm vi ô chứa các giá trị ngưỡng mà MATCH sẽ tìm kiếm, chẳng hạn như $E$2:$E$4 trong trường hợp này. Chú thích: Các số trong phạm vi này phải được sắp xếp theo thứ tự tăng dần để hàm MATCH hoạt động chính xác khi sử dụng loại khớp gần đúng.

Bước 2: Áp dụng CHOOSE và MATCH để nhận kết quả

Chọn một ô trống (trong trường hợp này là C2), nhập công thức sau và nhấn nút đăng ký hạng mục thi chìa khóa để đạt được lớp một. Chọn ô công thức này và kéo nó Điền vào Xử lý xuống để nhận phần còn lại của kết quả.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Tóm lại, việc nắm vững các câu lệnh IF lồng nhau trong Excel là một kỹ năng có giá trị giúp nâng cao khả năng xử lý các tình huống logic phức tạp của bạn trong quá trình phân tích dữ liệu và ra quyết định. Mặc dù các IF lồng nhau rất hiệu quả đối với các phép toán logic phức tạp nhưng điều quan trọng là phải lưu ý đến những hạn chế của chúng. Các lựa chọn thay thế đơn giản hơn như VLOOKUP, IFS và CHỌN với MATCH có thể cung cấp các giải pháp hợp lý hơn trong một số trường hợp nhất định. Được trang bị những hiểu biết sâu sắc này, giờ đây bạn có thể tự tin áp dụng các kỹ thuật Excel thích hợp nhất cho các tác vụ phân tích dữ liệu của mình, đảm bảo sự rõ ràng, chính xác và hiệu quả trong bảng tính của bạn. Đối với những người mong muốn tìm hiểu sâu hơn về các khả năng của Excel, trang web của chúng tôi tự hào có rất nhiều hướng dẫn. Khám phá thêm các mẹo và thủ thuật Excel tại đây.

Bài viết liên quan