Phương pháp áp dụng hàm IF cho nhiều điều kiện trong Excel

Bài viết này do Siêu Marketing biên soạn, sẽ giúp bạn hiểu cách sử dụng hàm IF khi có nhiều điều kiện trong Excel. Bạn cũng sẽ tìm thấy một số ví dụ thông dụng của hàm IF được lồng vào nhau mà thường được áp dụng trong các tác vụ hằng ngày.

Khi được hỏi về tính năng trong Excel mà bạn sử dụng thường xuyên nhất, câu trả lời của bạn có thể là gì? Rất có thể, đó sẽ là hàm IF của Excel. Sử dụng hàm IF đơn lẻ là khá đơn giản, nhưng còn khi data yêu cầu bạn phải xử lý với nhiều điều kiện đan xen và phức tạp? Lúc đó, việc sử dụng hàm IF có thể mở rộng thành việc dùng nhiều hàm IF trong một công thức, và đây được biết đến với tên hàm IF lồng. Điểm nổi bật của hàm IF lồng là khả năng kiểm tra nhiều điều kiện và mang lại kết quả phù hợp dựa trên từng kiểm tra, chỉ qua một công thức duy nhất.

Với Excel phiên bản từ 2007 đến Excel 2016 và các bản cập nhật sau này, bạn có thể sử dụng tối đa 64 hàm IF được lồng vào nhau trong một công thức. Với Excel 2003 và các phiên bản trước đó, số hàm IF lồng nhau tối đa là 7.

Bạn sẽ khám phá được một số ví dụ diễn giải chi tiết về cách hàm IF lồng nhau hoạt động cũng như cấu trúc cú pháp của chúng qua bài viết dưới đây.

Ví dụ 1: Mẫu công thức IF lồng ghép cơ bản

Xét một ví dụ bạn có một danh sách tên học sinh ở cột A và điểm số của họ ở cột B, và mục tiêu của bạn là phân loại điểm dựa trên các mức độ như sau:

  • Excellent: điểm cao hơn 249
  • Good: điểm từ 200 đến 249
  • Satisfactory: điểm từ 150 đến 199
  • Poor: điểm dưới 150

Bây giờ, chúng ta hãy thực hiện viết một công thức IF lồng ghép dựa trên các điều kiện đã nêu. Bắt đầu từ điều kiện quan trọng nhất và cố gắng giữ công thức càng đơn giản càng tốt. Công thức IF lồng nhau sẽ như sau:

=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor”)))

Kết quả cho thu được sẽ chính xác như bạn mong đợi.

Mẫu công thức IF lồng ghép cơ bản
Mẫu công thức IF lồng ghép cơ bản

Giải thích logic hàm IF lồng nhau:

Đối với nhiều người, việc hiểu được hàm IF lồng nhau có thể gây khó khăn. Tuy nhiên, quy trình này sẽ trở nên dễ hiểu hơn khi bạn xem xét theo cách sau:

Phương pháp dùng hàm IF cho nhiều điều kiện
Phương pháp dùng hàm IF cho nhiều điều kiện

Thực chất, công thức chỉ yêu cầu Excel thực hiện kiểm tra logic đối với điều kiện đầu tiên trong chuỗi hàm IF, nếu kết quả đúng thì Excel sẽ trả lại giá trị phù hợpTrong trường hợp hàm If đầu tiên không đáp ứng được tính logic, hệ thống sẽ chuyển sang xét điều kiện thứ hai, rồi tiếp đến điều kiện thứ ba và cứ tiếp tục như vậy.

Xem xét liệu B2 >= 249, nếu có, trả về “Xuất sắc”. Ngược lại, kiểm tra xem B2 >=200, nếu đúng, kết quả là “Tốt”. Nhưng nếu chưa, lại tiếp tục với điều kiện B2 >=150, nếu phù hợp thì “Đạt”, còn không thì kết luận là “Kém”

Nested IF Function with Arithmetic Operations Example

Một ví dụ khác thường gặp chính là việc đơn giá có sự thay đổi dựa vào số lượng đặc biệt cụ thể, và bạn cần tạo ra công thức tính toán tổng giá tiền cho bất kỳ số lượng sản phẩm nào, nhập vào một ô duy nhất. Điều này đòi hỏi công thức phải đánh giá nhiều điều kiện và thực hiện các phép tính khác nhau tuỳ thuộc vào số lượng sản phẩm và mức giá cụ thể được liệt kê như sau:

Khoảng số lượng Mức giá mỗi đơn vị
1 – 10 20$
11 – 19 18$
20 – 49 16$
50 – 100 13$
Trên 101 12$

 

Bạn cũng có thể áp dụng hàm IF lồng nhau để thực hiện nhiệm vụ này. Logic áp dụng tương tự như ví dụ trước, chỉ khác là bạn cần nhân số lượng với kết quả từ hàm IF lồng nhau (nghĩa là giá phù hợp cho từng đơn vị sản phẩm).

Với số lượng được nhập vào ô B8, công thức sẽ được viết như sau:

=B8*IF(B8>=101, 12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”)))))

Kết quả của công thức này sẽ được hiển thị như sau:

Nested IF Function with Arithmetic Operations
Nested IF Function with Arithmetic Operations

Ví dụ trên mô tả cách làm cơ bản và chung chung, khi bạn đã nắm vững, bạn hoàn toàn có thể điều chỉnh và tùy biến hàm IF này theo đặc thù công việc cụ thể mình đang làm.

Thay vì gán trực tiếp giá vào công thức, bạn hoàn toàn có thể liên kết tới các ô chứa giá trị (từ ô B2 đến B6). Cách này giúp bạn thay đổi dữ liệu gốc mà không cần sửa lạ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, “”)))))

Multiple Condition IF Function in Excel
Multiple Condition IF Function in Excel

Bạn còn có thể thêm vào các điều kiện IF khác để tinh chỉnh ngưỡng trên, dưới hoặc cả hai, giúp phạm vi số liệu được kiểm soát tốt hơn. Nếu số lượng vượt qua các giới hạn này, công thức sẽ báo “nằm ngoài phạm vi”. Ví dụ điển hình như:

=IF(OR(B8>200,B8<1), “out of the range”, (remaining formula…))

“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, “”))))))

Công thức IF lồng nhau với giới hạn cố định
Hình 2: Cú pháp hàm IF lồng ghép với các phép toán số học

Công thức hàm IF lồng ghép nêu trên có khả năng hoạt động trên mọi phiên bản của Excel từ 2000 đến 2016. Với Excel 2016 – Office 365, hàm IFS có thể được áp dụng tương tự.

Những người đã có kinh nghiệm sử dụng Excel ở mức độ chuyên nghiệp, có thể tận dụng công thức mảng, điều này giúp họ thực hiện những tác vụ tương tự như hàm IF lồng nhau mà chúng ta đã đề cập. Công thức mảng có thể phức tạp và khó ghi nhớ hơn nhưng chúng cung cấp một lợi ích không thể phủ nhận: chúng cho phép bạn chỉ định một chuỗi các ô chứa các điều kiện bạn muốn thay vì phải chỉ định từng điều kiện một. Điều này tạo ra tính linh động cao hơn cho công thức và chỉ cần cập nhật chuỗi ô này trong trường hợp bạn muốn thay đổi hoặc thêm điều kiện mới.

Một số mẹo và thủ thuật khi sử dụng hàm IF lồng nhau trong Excel

Một số mẹo dưới đây sẽ hỗ trợ bạn nâng cao hiệu quả sử dụng hàm IF lồng nhau và tránh gặp lỗi thường gặp:

Giới hạn của việc lồng ghép hàm IF

Trong các phiên bản từ Excel 2007 đến 2016, chỉ có thể lồng ghép tối đa 64 điều kiện IF. Đối với Excel 2003 và những phiên bản cũ hơn, số lượng hàm IF lồng ghép được giới hạn ở 7. Dù có khả năng lồng ghép nhiều điều kiện trong một công thức không có nghĩa là bạn nên thực hiện như vậy. Hãy lưu ý, việc thêm điều kiện có thể làm cho công thức của bạn trở nên phức tạp và khó theo dõi khi xảy ra lỗi. Nếu bạn có một công thức với quá nhiều điều kiện kết hợp, hãy cân nhắc sử dụng một số phương án thay thế sau để tối ưu.

Vị trí của điều kiện trong hàm IF lồng ghép là rất cần thiết

Hàm IF lồng nhau trong Excel sẽ kiểm tra từng điều kiện theo thứ tự đã được đặt trong công thức và chỉ cần một điều kiện là TRUE, các điều kiện còn lại sẽ không được xét đến. Cách thức này có nghĩa là công thức sẽ dừng lại ngay khi gặp kết quả TRUE.

Hãy xem xét một ví dụ. Giả sử B2 là 274, công thức IF lồng nhau sau sẽ đánh giá tính logic của điều kiện đầu tiên là (B2> 249) và trả về “Xuất sắc” do kết quả kiểm tra này là TRUE:

=IF(B2>249, “Xuất sắc”, IF(B2>=200, “Tốt”, IF(B2>150, “Khá”, “Yếu”)))

Tuy nhiên, nếu bạn đảo ngược thứ tự của các điều kiện như sau, kết quả sẽ thay đổi:

=IF(B2>150, “Khá”, IF(B2>200, “Tốt”, IF(B2>249, “Xuất sắc”, “Yếu”)))

Điều này chứng tỏ việc điều chỉnh vị trí các hàm IF có thể làm thay đổi kết quả trả về của công thức.

Hình 2: Hàm IF lồng nhau với các phép tính số học
Hình 2: Hàm IF lồng nhau trong Excel – mẹo

Kiểm tra tính hợp lý của biểu thức

Sử dụng chức năng Evaluate Formula từ tab Formula, thuộc nhóm Formula Auditing để kiểm soát từng bước logic trong công thức hàm IF đa cấp. Phần biểu thức đang được kiểm tra sẽ được gạch dưới và bằng cách nhấn vào nút Evaluate, bạn có thể quan sát mọi bước được thực hiện để đánh giá công thức đó.

Ví dụ, như ảnh dưới đây cho thấy, khi kiểm tra biểu thức đầu tiên trong hàm IF đa cấp: B2 > 249; 274 > 249; TRUE; Excellent.

Kiểm tra tính hợp lý của biểu thức
Kiểm tra tính hợp lý của biểu thức

Điều chỉnh việc khớp ngoặc đơn cho hàm IF đa cấp

Khó khăn cơ bản khi làm việc với hàm IF đa cấp trong Excel là phải đảm bảo các cặp ngoặc đơn phải đồng nhất. Nếu không khớp, công thức sẽ không hoạt động. Tuy nhiên, Excel có các chức năng để giúp bạn điều chỉnh việc này trong khi sửa công thức:

  • Khi bạn sử dụng nhiều cặp ngoặc đơn, Excel sẽ tô màu khác nhau để dễ dàng xác định ngoặc mở và ngoặc đóng cùng một cặp.
  • Khi bạn kết thúc ngoặc đơn, Excel sẽ hiện thị cặp ngoặc đó đã khớp. Công thức sẽ có hiệu ứng nhấp nháy hoặc nổi bật lên khi bạn di chuyển qua nó bằng cách sử dụng phím mũi tên.
Balance the parenthesis of nested IF functions
Điều chỉnh việc khớp ngoặc đơn cho hàm IF đa cấp

Phân biệt cách xử lý giữa chữ và số

Khi thiết lập các kiểm tra logic cho hàm IF đa cấp, hãy chú ý rằng chữ và số cần được xử lý khác nhau – giá trị chữ cần đặt trong dấu ngoặc kép, trong khi số thì không:

Đúng: =IF(B2>249, “Excellent”,…)

Sai: =IF(B2>”249″, “Excellent”,…)

Công thức thứ hai khi kiểm tra sẽ cho kết quả FALSE ngay cả khi B2 > 249 vì 249 là số và “249” được coi như chuỗi số, là hai khái niệm không giống nhau.

Sử dụng dấu cách hoặc xuống dòng để tăng khả năng đọc hàm IF đa cấp

Nhằm giúp công thức có nhiều điều kiện lồng nhau trở nên rõ ràng hơn, bạn có thể áp dụng dấu cách hay xuống dòng giữa các hàm IF. Do Excel không quan tâm đến khoảng trắng thừa, nên việc này không gây ra sai lệch trong công thức.

Chỉ cần click vào chỗ mong muốn xuống dòng và ấn Alt + Enter. Sau đó, mở rộng thanh công thức để phần công thức đó dễ nhận biết hơn.

8 Cách dùng hàm IF với nhiều điều kiện trong Excel
8 Cách dùng hàm IF với nhiều điều kiện trong Excel

Phương án thay thế cho việc sử dụng IF lồng nhau trong Excel

Nhìn vào những phương án sau đây để giảm bớt sự phức tạp và tăng tốc độ cho công thức của bạn thay vì dùng tới 7 hàm IF lồng nhau trong Excel 2003 cùng các bản trước:

  • Bạn có thể lựa chọn hàm CHOOSE để xem xét những điều kiện khác nhau và cấp giá trị tương ứng dựa trên các kết quả của việc kiểm tra.
  • Thiết lập một bảng tra cứu và dùng hàm VLOOKUP với chức năng đối sánh gần đúng.
  • Dùng IF kết hợp với các hàm logic OR và AND.
  • Tạo công thức mảng (array).
  • Kết hợp nhiều điều kiện IF sử dụng hàm CONCATENATE hoặc cách nối chuỗi thông thường.
  • Người dùng có kỹ năng Excel nâng cao có thể tạo ra hàm riêng trên trang tính thông qua VBA như là cách thức thay thế lý tưởng để dùng nhiều hàm IF lồng vào nhau.

Xem thêm: Cách tạo hàm IF với điều kiện đa dạng trong một công thức duy nhất

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop