Bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách sử dụng các hàm IF lồng nhau trong Excel để kiểm tra nhiều điều kiện. Ngoài ra chúng ta sẽ tìm hiểu một vài hàm khác có thể thay thế nhé!

Bạn thường triển khai các quyết định logic của mình như thế nào trong Excel? Trong hầu hết các trường hợp, bạn sẽ sử dụng một công thức IF để kiểm tra điều kiện của bạn và trả về một giá trị nếu điều kiện được đáp ứng, giá trị khác nếu điều kiện không được đáp ứng. Nhưng, để đánh giá nhiều hơn một điều kiện và trả về các giá trị khác nhau tùy thuộc vào kết quả thì bạn cần lồng nhiều hàm IF vào nhau.

Mặc dù rất phổ biến và dễ sử dụng, tuy nhiên hàm IF lồng nhau trong Excel không phải là cách duy nhất để kiểm tra nhiều điều kiện trong Excel. Trong hướng dẫn này, bạn sẽ tìm thấy một số cách thay thế chắc chắn đáng để khám phá đấy.

Câu lệnh IF lồng nhau

Đây là công thức IF cổ điển lồng nhau trong một dạng chung:

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

Bạn có thể thấy rằng mỗi hàm IF tiếp theo được nhúng vào value_if_false đối số của hàm trước. Mỗi hàm IF được đặt trong tập ngoặc đơn riêng của nó và tất cả các dấu ngoặc đơn đóng ở cuối công thức.

Công thức IF lồng nhau chung này đánh giá 3 điều kiện và trả về 4 kết quả khác nhau (kết quả 4 được trả về nếu không có điều kiện nào là TRUE). Để dễ hiểu hơn thì câu lệnh IF lồng nhau này yêu cầu Excel thực hiện các thao tác sau:

Kiểm tra điều kiện 1(decision1), nếu TRUE – trả lại kết quả 1(result1), nếu SAI —

kiểm tra điều kiện 2(decision2), nếu TRUE – trả về kết quả 2(result2), nếu SAI –

kiểm tra điều kiện 3(decision3), nếu TRUE – trả lại kết quả 3(result3), nếu SAI –

trở về kết quả4(result4)

Ví dụ: hãy tìm hiểu hoa hồng của một số nhân viên bán hàng dựa trên doanh số của họ:

Trong toán học, việc thay đổi thứ tự bổ sung không thay đổi tổng. Trong Excel, việc thay đổi thứ tự các hàm IF sẽ thay đổi kết quả. Tại sao? Bởi vì công thức IF lồng nhau trả về một giá trị tương ứng với điều kiện TRUE đầu tiên. Do đó, trong các câu lệnh IF lồng nhau của bạn, việc sắp xếp các điều kiện theo đúng hướng – cao đến thấp hoặc thấp đến cao là rất quan trọng. Trong trường hợp này, chúng ta kiểm tra điều kiện “cao nhất” trước, sau đó là “cao thứ hai”, v.v.

=IF(B2>=150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, “”))))

Nếu chúng ta sắp xếp các điều kiện theo thứ tự ngược lại, từ dưới lên, kết quả sẽ sai vì công thức của chúng ta sẽ dừng sau thử nghiệm logic đầu tiên cho bất kỳ giá trị nào lớn hơn 1. Giả sử, bạn có doanh thu 100 đô la – lớn hơn 1, ngay lập tức công thức sẽ không kiểm tra các điều kiện khác và trả lại 3% như kết quả.

Như bạn thấy đấy, phải mất khá nhiều thời gian để xây dựng logic của một câu lệnh IF lồng nhau cho đến hết. Và mặc dù Microsoft Excel cho phép lồng tối đa 64 hàm IF trong một công thức, nhưng đó không phải là điều bạn thực sự muốn làm trong bảng tính của mình. Vì vậy, nếu bạn (hoặc ai đó) đang nhìn chằm chằm vào công thức IF lồng nhau của Excel và cố gắng tìm hiểu xem nó thực sự làm gì, đã đến lúc xem xét lại chiến lược của bạn và có thể chọn một công cụ khác trong kho vũ khí của bạn.

IF lồng nhau với điều kiện OR / AND

Trong trường hợp bạn cần đánh giá một vài tập hợp các điều kiện khác nhau, bạn có thể biểu thị các điều kiện đó bằng hàm OR cũng như hàm AND, lồng các hàm bên trong các câu lệnh IF và sau đó lồng các câu lệnh IF vào nhau, khá dễ hiểu đúng không?

IF lồng nhau trong Excel với các câu lệnh OR

Bằng cách sử dụng hàm OR bạn có thể kiểm tra hai hoặc nhiều điều kiện khác nhau trong kiểm tra logic của từng hàm IF và trả về TRUE nếu có (ít nhất một) đối số OR ước tính thành TRUE. Để dễ hiểu hơn thì chúng ta cùng làm ví dụ sau nhé!

Giả sử, bạn có hai cột bán hàng, doanh số tháng 1 ở cột B và doanh số tháng 2 ở cột C. Bạn muốn kiểm tra các số trong cả hai cột và tính toán hoa hồng dựa trên số cao hơn. Nói cách khác, bạn xây dựng một công thức với logic sau: nếu doanh số tháng 1 hoặc tháng 2 lớn hơn 150 đô la, người bán nhận được 10% hoa hồng, nếu doanh số tháng 1 hoặc tháng 2 lớn hơn 100 đô la, thì người bán nhận được 7% hoa hồng.

Để làm bài này, hãy viết một vài câu lệnh OF như OR (B2> = 150, C2> = 150) và lồng chúng vào các hàm IF đã thảo luận ở trên. Kết quả là bạn có được công thức này:

=IF(OR(B2>=150, C2>=150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=11, C2>=1), 3%, “”))))

Và ta đã có hoa hồng cần tìm dựa trên số tiền bán hàng cao hơn:

Để biết thêm ví dụ về công thức, vui lòng xem câu lệnh IF OR trong Excel

IF lồng nhau trong Excel với câu lệnh AND

Nếu bạn muốn kết quả bao gồm nhiều điều kiện, hãy thể hiện chúng bằng cách sử dụng Hàm AND.

Ví dụ: để biết hoa hồng dựa trên số lượng bán hàng thấp hơn, hãy thực hiện công thức trên và thay thế câu lệnh OR bằng câu lệnh AND. Nói cách khác, bạn yêu cầu Excel chỉ trả lại 10% nếu doanh số tháng 1 và tháng 2 lớn hơn 150 đô la, 7% nếu doanh số tháng 1 và tháng 2 lớn hơn 100 đô la, v.v.

=IF(AND(B2>=150, C2>=150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=11, C2>=1), 3%, “”))))

Công thức IF lồng nhau đã tính toán hoa hồng dựa trên số thấp hơn trong các cột B và C. Nếu một trong hai cột trống, không có hoa hồng nào cả vì không có điều kiện AND nào được đáp ứng:

Nếu bạn muốn trả về 0% thay vì các ô trống, hãy thay thế một chuỗi trống (” “) trong đối số cuối cùng bằng 0%:

=IF(AND(B2>=150, C2>=150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=11, C2>=1), 3%, 0%))))

Tìm thêm thông tin tại đây: Excel IF với nhiều điều kiện AND / OR.

VLOOKUP thay vì IF lồng nhau trong Excel

Khi bạn đang xử lý “thang đo”, tức là các phạm vi giá trị số liên tục cùng nhau bao trùm toàn bộ phạm vi, trong hầu hết các trường hợp, bạn có thể sử dụng Chức năng VLOOKUP thay vì các hàm IF lồng nhau.

Để bắt đầu, bạn hãy tạo một bảng tham chiếu như ảnh chụp màn hình bên dưới. Sau đó, xây dựng một công thức Vlookup với đối sánh gần đúng, tức là với range_lookup được đặt thành TRUE.

Giả sử giá trị tra cứu là B2 và bảng tham chiếu là F2: G5, ta có công thức sau:

=VLOOKUP(B2,$F$2:$G$5,2,TRUE)

Lưu ý rằng hãy sửa table_array với các tham chiếu tuyệt đối ($F$2:$G$5) để sao chép chính xác công thức vào các ô khác:

Bằng cách đặt đối số cuối cùng của công thức Vlookup thành TRUE, nghĩa là bạn đang  nói với Excel rằng nếu không tìm thấy kết quả khớp chính xác thì hãy trả về giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu. Nói một cách dễ hiểu hơn là công thức của bạn sẽ không chỉ khớp với các giá trị chính xác trong bảng tra cứu mà còn bất kỳ giá trị nào nằm ở gần nó nhất.

Ví dụ: giá trị tra cứu trong B3 là $95. Số này không tồn tại trong bảng tra cứu, lúc này  Vlookup với kết quả khớp chính xác sẽ trả về lỗi #N/A, nhưng Vlookup với kết quả gần đúng sẽ tiếp tục tìm kiếm cho đến khi tìm thấy giá trị gần nhất nhỏ hơn giá trị tra cứu (trong ví dụ của chúng ta là $50) và trả về giá trị từ cột thứ hai trong cùng hàng (là 5%).

Nhưng nếu giá trị tra cứu nhỏ hơn số nhỏ nhất trong bảng tra cứu hoặc ô tra cứu thì sao? Trong trường hợp này, công thức Vlookup sẽ trả về lỗi #N/A, nếu bạn không thích  thì hãy lồng VLOOKUP vào bên trong IFERROR và cung cấp giá trị cho đầu ra khi không tìm thấy giá trị tra cứu. Ví dụ:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), “Outside range”)

Một lưu ý quan trọng bạn cần phải nhớ là để công thức Vlookup có kết quả gần đúng nhất, cột đầu tiên trong bảng tra cứu phải được sắp xếp theo thứ tự tăng dần, từ nhỏ nhất đến lớn nhất nhé!

Để biết thêm thông tin, bạn có thể xem: Đối sánh chính xác VLOOKUP so với đối sánh gần đúng VLOOKUP

Câu lệnh IFS thay thế cho hàm IF lồng nhau

Trong Excel 2016 và các phiên bản mới hơn, Microsoft đã giới thiệu một chức năng đặc biệt để đánh giá nhiều điều kiện – công thức IFS.

Một công thức IFS có thể xử lý tới 127 kiểm tra logic/value_if_true các cặp và bài kiểm tra logic đầu tiên đánh giá TRUE “wins”:

IFS (logic_test1, value_if_true1, [logical_test2, value_if_true2]…)

Với cú pháp trên, công thức IF lồng nhau có thể được xây dựng lại theo cách này:

=IFS(B2>=150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Bạn lưu ý rằng hàm IFS sẽ trả về lỗi #N/A nếu không có điều kiện nào được đáp ứng. Để tránh điều này, bạn có thể thêm một  logic/value_if_true đến cuối công thức của bạn sẽ trả về 0 hoặc chuỗi rỗng (“”) hoặc bất kỳ giá trị nào bạn muốn nếu không có phép thử logic nào trước đó là TRUE:

=IFS(B2>=150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, “”)

Công thức của chúng ta sẽ trả về một ô trống thay vì lỗi #N/A nếu một ô tương ứng trong cột B trống hoặc chứa văn bản hay là số âm.

Lưu ý: Giống như IF lồng nhau, hàm IFS của Excel sẽ trả về một giá trị tương ứng với điều kiện đầu tiên ước tính thành TRUE, đó là lý do tại sao thứ tự kiểm tra logic trong công thức IFS có vấn đề.

Để biết thêm thông tin, xin vui lòng xem Hàm IFS của Excel thay vì IF lồng nhau.

Hàm CHOOSE thay cho công thức IF lồng nhau trong Excel

Một cách khác để kiểm tra nhiều điều kiện trong Excel là sử dụng hàm CHOOSE, hàm này sẽ trả về một giá trị từ danh sách dựa trên vị trí của giá trị đó.

Áp dụng cho tập dữ liệu mẫu, ta có công thức như sau:

=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>=150), 3%, 5%, 7%, 10%)

Trong đối số đầu tiên (index_num), bạn đánh giá tất cả các điều kiện và thêm kết quả. Cho rằng TRUE tương đương với 1 và FALSE thành 0, theo cách này bạn tính được vị trí của giá trị sẽ trả về.

Ví dụ: giá trị trong B2 là $150. Đối với giá trị này, cả 4 điều kiện là TRUE, nghĩa là index_num bằng 4, nghĩa là giá trị thứ tư được trả lại, là 10%.

Nếu không có kết quả nào là TRUE, index_num bằng 0 và công thức trả về #VALUE! lỗi. Đừng rối, hãy gói CHOOSE trong Hàm IFERROR như thế này:

=IFERROR(CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>=150), 3%, 5%, 7%, 10%), “”)

Để biết thêm thông tin, xin vui lòng xem Hàm CHOOSE trong Excel và các ví dụ thực tế.

Hàm SWITCH là một dạng ngắn gọn của IF lồng nhau trong Excel

Nếu bạn đang xử lý một tập hợp các giá trị được xác định trước cố định, không phải tỷ lệ, thay vì các câu lệnh IF lồng nhau phức tạp, bạn có thể sử dụng hàm SWITCH như sau:

SWITCH(expression, value1, result1, value2, result2, …, [default])

Trong trường hợp bạn muốn tính hoa hồng dựa trên các lớp A B C D, thay vì số tiền bán hàng, bạn có thể sử dụng phiên bản nhỏ gọn này của công thức IF lồng nhau trong Excel:

=SWITCH(C2, “A”, 10%, “B”, 7%, “C”, 5%, “D”, 3%, “”)

Hoặc, bạn có thể tạo một bảng tham chiếu như ảnh chụp màn hình bên dưới và sử dụng các tham chiếu ô thay vì các giá trị được mã hóa cứng:

=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, “”)

Lưu ý: bạn hãy khóa tất cả các tham chiếu ngoại trừ tham chiếu đầu tiên bằng dấu $ để ngăn chúng thay đổi khi sao chép công thức sang các ô khác nhé.

Chú ý: Hàm SWITCH chỉ khả dụng trong Excel 2016 trở lên.

Để biết thêm thông tin, bạn có thể xem Hàm SWITCH – dạng rút gọn của câu lệnh IF lồng nhau.

Ghép nhiều hàm IF trong Excel

Như mình đã đề cập trong ví dụ trước, hàm SWITCH chỉ được sử dụng trong Excel 2016. Còn nếu bạn sử dụng Excel phiên bản cũ hơn, bạn có thể kết hợp hai hoặc nhiều câu lệnh IF bằng cách sử dụng toán tử Concatenate (&) hoặc hàm CONCATENATE nhé!

Ví dụ:

=(IF(C2=”a”, 10%, “”) & IF(C2=”b”, 7%, “”) & IF(C2=”c”, 5%, “”) & IF(C2=”d”, 3%, “”))*1

Hoặc là

=CONCATENATE(IF(C2=”a”, 10%, “”), IF(C2=”b”, 7%, “”), IF(C2=”c”, 5%, “”) & IF(C2=”d”, 3%, “”))*1

Như bạn có thể nhận thấy, chúng ta nhận kết quả với 1 trong cả hai công thức, sau đó nó sẽ chuyển đổi một chuỗi được trả về bởi công thức Concatenate thành một số. Nếu đầu ra dự kiến ​​của bạn là văn bản thì bạn không cần bước nhân này nhé!