Giới thiệu cách dùng hàm IF lồng ghép trong Excel kèm ví dụ cụ thể


Trong bài viết dưới đây, Siêu Marketing sẽ giới thiệu tới bạn đọc cách áp dụng hàm IF lồng ghép trong Excel để xử lý một loạt các điều kiện khác nhau. Đồng thời, bạn sẽ được khám phá các hàm khác có thể dùng làm phương án thay thế.

Khi bạn muốn thực hiện các phán đoán logic trong Excel, bạn thường làm như thế nào? Đa số, công thức IF sẽ được sử dụng để nhận diện điều kiện đã đề ra, sau đó sẽ trả về một giá trị cụ thể nếu điều kiện đó thỏa mãn, hoặc một giá trị khác nếu không thỏa mãn. Tuy nhiên, nếu bạn muốn phân tích nhiều điều kiện và đề ra nhiều đáp ứng khác nhau tuỳ vào từng kết quả cụ thể, bạn cần phải sử dụng hàm IF đa tầng.

Dù việc sử dụng các hàm IF đa tầng trong Excel khá thông dụng và tương đối dễ tiếp cận, nhưng không phải lúc nào nó cũng là phương pháp duy nhất để xử lý nhiều điều kiện trong Excel. Cùng xem xét một số phương thức thay thế tiềm năng trong hướng dẫn này nhé.

Xem xét về hàm IF đa tầng

Đây là cấu trúc của một công thức IF đa tầng được sử dụng rộng rãi:

IF(điều kiện1, kết quả1, IF(điều kiện2, kết quả2, IF(điều kiện3, kết quả3, kết quả4)))

Bạn sẽ nhận ra mỗi hàm IF tiếp theo được nhúng vào đối số value_if_false của hàm IF ngay trước đó. Tất cả các hàm IF này được bao bọc bởi dấu ngoặc đơn của riêng mình và cuối cùng thì chúng được đóng lại ở phần cuối của công thức.

Để mô tả một cách dễ hiểu hơn, câu lệnh IF đa tầng này yêu cầu Excel thực hiện những bước sau:

Kiểm tra điều kiện 1(decision1), nếu đúng – trả về kết quả 1(result1), nếu sai —

tiếp tục kiểm tra điều kiện 2(decision2), nếu đúng – trả về kết quả 2(result2), nếu không –

sau đó kiểm tra điều kiện 3(decision3), nếu đúng – trả về kết quả 3(result3), nếu không –

cuối cùng là kết quả4(result4)

Chẳng hạn: xét xem hoa hồng của những nhân viên bán hàng dựa vào số lượng bán ra như sau:

Hoa hồng Doanh số
3% $ 1 – $ 50
5% $ 51 – $ 100
7% $ 101 – $ 150
10% Hơn $ 150

Trong toán học, việc thay đổi vị trí các số hạng không làm biến đổi tổng số. Trong khi đó, việc đảo thứ tự các hàm IF trong Excel có thể dẫn đến kết quả khác nhau. Lí do là hàm IF đa tầng sẽ trả về giá trị cho điều kiện đầu tiên mà nó nhận thấy là TRUE. Do đó, việc xếp đặt các điều kiện một cách hợp lý – từ lớn đến nhỏ hoặc ngược lại – trở nên rất quan trọng trong các hàm IF đa tầng. Trong trường hợp này, chúng ta sẽ bắt đầu với điều kiện lớn nhất trước, sau đó giảm dần.

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

1 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
1 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Trường hợp chúng ta xét các điều kiện theo thứ tự từ dưới lên thì kết quả có thể không chính xác do công thức sẽ chỉ xét điều kiện đầu tiên và dừng lại nếu gặp bất kỳ giá trị nào lớn hơn 1. Chẳng hạn, nếu doanh thu là 100 đô la, vốn đã lớn hơn 1, công thức sẽ ngay lập tức kết thúc kiểm tra và kết luận 3% mà không xem xét các điều kiện còn lại.

Như bạn có thể thấy, việc thiết lập logic cho một câu lệnh IF phức tạp có thể tốn khá nhiều thời gian. Dù Excel cho phép chúng ta lồng nhau tới 64 hàm IF trong một công thức, nhưng đây không phải là việc bạn nên thường xuyên thực hiện trong các bảng tính. Do đó, nếu bạn (hoặc ai đó) đang cố gắng phân tích một công thức IF phức tạp trong Excel, có lẽ đã tới lúc bạn nên xem xét lại phương pháp và sử dụng một công cụ khác có sẵn.

IF lồng nhau kết hợp điều kiện OR / AND

Khi cần xem xét nhiều tập hợp điều kiện, hãy thể hiện chúng thông qua việc sử dụng hàm OR hoặc hàm AND. Bạn có thể lồng những hàm này vào trong câu lệnh IF và sau đó lồng những câu lệnh IF này vào nhau, khá dễ hiểu phải không nào?

IF lồng nhau trong Excel kết hợp hàm OR

Với hàm OR, bạn có thể kiểm tra nhiều điều kiện cùng lúc và nếu một trong những điều kiện đó đúng, hàm sẽ trả về TRUE. Cùng xét một ví dụ để rõ hơn nhé.

Giả sử bạn có hai cột dữ liệu về doanh số, với doanh số tháng 1 ở cột B và tháng 2 ở cột C. Bạn muốn kiểm tra và tính hoa hồng dựa vào con số cao hơn trong hai cột. Logic công thức như sau: nếu doanh số của tháng 1 hoặc tháng 2 vượt qua 150 đô la thì hoa hồng là 10%, nếu một trong hai tháng đạt trên 100 đô la thì hoa hồng là 7%.

Sử dụng hàm OR như OR(B2>=150, C2>=150) và lồng nó vào trong công thức IF như đã nói, bạn sẽ có được công thức:

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

Kết quả là bạn có thể xác định được mức hoa hồng dựa vào số tiền bán hàng cao hơn:

2 Câu lệnh IF lồng nahau trong Excel và các ví dụ thực tế
2 Câu lệnh IF lồng nahau trong Excel và các ví dụ thực tế

Để tham khảo thêm, mời bạn xem qua ví dụ về công thức sử dụng câu lệnh IF kết hợp với OR trong Excel.

IF lồng nhau trong Excel sử dụng hàm AND

Nhằm đạt được kết quả khi mọi điều kiện đều đồng thời thỏa mãn, hàm AND là sự lựa chọn phù hợp.

Xét một ví dụ, nếu bạn muốn tính hoa hồng dựa vào doanh số bán hàng nhỏ hơn, hãy áp dụng công thức trước đó và thay đổi từ OR sang AND. Điều này yêu cầu Excel chỉ trả về hoa hồng 10% nếu cả doanh số tháng 1 và tháng 2 đều cao hơn.

Khi doanh số tháng 1 và tháng 2 cùng vượt quá 150 đô la, sẽ nhận được hoa hồng 10%, và nếu chúng vượt quá 100 đô la, sẽ có hoa hồng 7%, và cứ thế tiếp tục.

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

Hệ thống IF lồng nhau này tiến hành tính toán tiền hoa hồng dựa trên giá trị nhỏ hơn giữa cột B và C. Hoa hồng sẽ không được tính nếu một trong hai cột không có dữ liệu, bởi không có điều kiện AND nào được thỏa mãn:

3 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
3 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Nếu mong muốn trả về giá trị 0% thay vì để trống, bạn chỉ cần thay thế chuỗi trống (“”) bằng giá trị 0% trong phần đối số cuối của công thức:

=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%))))

4 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
4 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Tham khảo thêm tại: Excel IF kết hợp nhiều điều kiện AND / OR.

Sử dụng VLOOKUP thay cho IF lồng nhau trong Excel

Trong quá trình xử lý “bảng lương”, tức là các khoảng giá trị số được xác định liên tiếp, bạn có khả năng thay thế sự cần thiết phải dùng IF lồng nhau bằng cách sử dụng hàm VLOOKUP thay thế.

Để thực hiện, hãy bắt đầu bằng việc tạo ra một bảng tham chiếu giống như trong hình minh họa sau đây. Kế đến, tạo một công thức Vlookup với lựa chọn đối sánh gần đúng, bằng cách đặt giá trị range_lookup là TRUE.

Nếu giả sử giá trị cần tra cứu là B2 và bảng tra là F2: G5, công thức như sau sẽ được sử dụng:

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

Chú ý rằng nên điều chỉnh table_array thành các tham chiếu cố định ($F$2:$G$5) để công thức có thể được sao chép chính xác sang các ô khác:

5 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
5 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Khi thiết lập đối số cuối cùng trong công thức Vlookup là TRUE, bạn đang hướng dẫn Excel tìm giá trị gần nhất nhưng không vượt quá giá trị cần tra cứu nếu không có kết quả khớp chính xác. Điều này có nghĩa là công thức không chỉ tìm kiếm các giá trị khớp chặt chẽ với bảng tham khảo mà còn tìm cả những giá trị gần đúng với giá trị đang tìm kiếm.

Đoạn văn này bằng tiếng Việt sẽ được diễn đạt lại như sau:

Chẳng hạn, ta có giá trị cần được tìm kiếm tại ô B3 là $95. Nếu không có số này trong bảng vlookup, thì việc sử dụng Vlookup với tùy chọn trả về kết quả chính xác sẽ dẫn đến lỗi #N/A. Tuy nhiên, nếu chọn Vlookup với mục tiêu tìm giá trị xấp xỉ, hàm sẽ tìm giá trị gần nhất và nhỏ hơn giá trị cần tìm (trong trường hợp này là $50) và sau đó, sẽ cung cấp giá trị liền kề ở cột thứ hai (ở đây là 5%).

Xét nếu giá trị muốn tìm nhỏ hơn giá trị nhỏ nhất trong bảng tra cứu, hoặc ô tìm kiếm là rỗng thì sao? Khi đó, Vlookup sẽ thông báo lỗi #N/A. Để khắc phục, chúng ta có thể sử dụng VLOOKUP bên trong hàm IFERROR và cung cấp một giá trị mặc định cho trường hợp không tìm thấy giá trị. Cụ thể như sau:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), “Không nằm trong phạm vi”)

Điểm quan trọng bạn cần ghi nhớ là bảng tra cứu cần được sắp xếp từ nhỏ đến lớn trong cột đầu tiên để Vlookup có thể hoạt động chính xác nhất.

Bạn có thể tham khảo thêm về sự khác biệt giữa đối sánh chính xác VLOOKUP và đối sánh gần đúng VLOOKUP.

Hàm IFS làm thay thế cho IF lồng nhau

Trong phiên bản Excel 2016 và các bản mới hơn, Microsoft đã giới thiệu chức năng mới có khả năng xử lý nhiều điều kiện là IFS.

Hàm IFS cho phép phân tích tới 127 cặp điều kiện kiểm tra logic/giá trị nếu đúng, với cặp đầu tiên đáp ứng được điều kiện TRUE sẽ được ưu tiên:

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

Áp dụng vào cú pháp này, công thức IF lồng nhau có thể được tái cấu trúc như sau:

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

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

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

Công thức này sẽ trả về giá trị rỗng thay cho lỗi #N/A nếu không tìm thấy giá trị phù hợp nào cho ô trong cột B, hoặc ô đó là trống, chứa văn bản hoặc là một số âm.

Cần nhớ rằng giống như IF lồng nhau, hàm IFS chỉ trả về giá trị cho điều kiện đầu tiên chuyển sang TRUE, và do đó, thứ tự của các kiểm tra logic trong IFS quan trọng.

Xem thêm về Hàm IFS của Excel để thay thế cho IF lồng nhau.

Cách sử dụng hàm CHOOSE thay vì IF lồng nhau trong Excel

Một phương án thay thế khác để kiểm tra hàng loạt điều kiện trong Excel là dùng hàm CHOOSE, vốn trả về giá trị từ danh sách dựa vào vị trí chỉ mục của nó.

Áp dụng cho ví dụ dữ liệu của chúng ta, công thức CHOOSE có thể được viết 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á mỗi điều kiện và cộng kết quả lại với nhau. Với điều kiện TRUE được hiểu là 1 và FALSE là 0, ket quả của các phép kiểm tra sẽ được cộng dồn và ấn định vị trí của giá trị trả về trong danh sách.

Bạn có thể xác định vị trí của giá trị sẽ được chọn thông qua cách sau đây:

Chẳng hạn, giá trị ở ô B2 là $150. Với mức giá này, tổng cộng có 4 điều kiện trở thành ĐÚNG, tức là giá trị của index_num là 4, và kết quả là giá trị thứ tư – 10% sẽ được trả về.

7 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
7 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Nếu không có điều kiện nào đúng, index_num sẽ là 0 và hàm sẽ hiển thị lỗi #VALUE!. Để tránh sự lẫn lộn, bạn nên bọc hàm CHOOSE trong Hàm IFERROR như sau:

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

Xem chi tiết Hàm CHOOSE trong Excel qua các ví dụ ứng dụng cụ thể.

Hàm SWITCH – phiên bản thu gọn của IF lồng ghép trong Excel

Khi bạn cần xử lý tập hợp các giá trị cố định mà không dựa vào tỷ lệ phần trăm, sử dụng hàm SWITCH đơn giản hơn so với việc áp dụng nhiều câu lệnh IF chi tiết:

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

Thay vì dùng phương pháp tính hoa hồng theo số tiền bán hàng, sử dụng công thức như sau đối với các hạng A, B, C, D:

=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ư hình dưới đây và sử dụng tham chiếu ô thay vì nhập trực tiếp giá trị:

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

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

Ghi chú: Khóa tất cả các tham chiếu, trừ tham chiếu đầu tiên, bằng cách sử dụng ký tự $ để chúng không thay đổi khi bạn sao chép công thức vào ô khác.

Lưu ý rằng Hàm SWITCH chỉ hoạt động trên Excel phiên bản 2016 trở lên.

Để tìm hiểu thêm, bạn có thể tham khảo Hàm SWITCH – bản gọn của lệnh IF lồng ghép.

Kết hợp đa hàm IF trong Excel

Như đã nói ở ví dụ trên, hàm SWITCH chỉ dành cho Excel 2016. Nếu bạn dùng phiên bản Excel cũ hơn, hãy ghép nối hai hàm IF trở lên bằng cách dùng toán tử Concatenate (&) hoặc hàm CONCATENATE.

Ví dụ:

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

Hoặc

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

9 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế
9 Câu lệnh IF lồng nhau trong Excel và các ví dụ thực tế

Sẽ chuyển đổi chuỗi từ hàm Concatenate sang số nếu kết quả mong đợi là số. Nếu mong muốn đầu ra là văn bản, không cần thực hiện phép nhân này.

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