Hướng dẫn giải thích cách sử dụng hàm IF lồng nhau trong Excel để kiểm tra nhiều điều kiện. Bạn cũng sẽ tìm hiểu một vài hàm khác có thể là lựa chọn thay thế tốt cho việc sử dụng công thức lồng nhau trong Excel.
Làm thế nào để bạn thường triển khai logic ra quyết định trong bảng tính Excel của mình? 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 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ả, bạn lồng nhiều IF vào nhau.
Mặc dù rất phổ biến, câu lệnh IF lồng nhau 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ố lựa chọn thay thế chắc chắn đáng để khám phá.
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:
NẾU(điều kiện1, kết quả1, NẾU(điều kiện2, kết quả2, NẾU(điều kiện3, kết quả3, kết quả4)))
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ó, nhưng 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 của chúng tôi đá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). Được dịch sang ngôn ngữ của con người, 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ện1, nếu TRUE – trả lại kết quả1, nếu SAI –
kiểm tra điều kiện2, nếu TRUE – trả về result2, nếu SAI –
kiểm tra điều kiện3, nếu TRUE – trả lại kết quả3, nếu SAI –
trở về kết quả4
Ví dụ: hãy tìm hiểu hoa hồng cho một số người bán dựa trên số lượng bán hàng họ đã thực hiện:
| Uỷ ban | Bán hàng |
| 3% | $ 1 – $ 50 |
| 5% | $ 51 – $ 100 |
| 7% | $ 101 – $ 150 |
| 10% | Hơn 150 đô la |
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 của chúng tôi, chúng tôi 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 tôi 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 tôi 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ử, chúng tôi có doanh thu 100 đô la – lớn hơn 1, do đó, 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, phải mất khá nhiều suy nghĩ để 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 đang 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.
Để biết thêm thông tin, xin vui lòng xem Câu lệnh IF lồng nhau.
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.
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. Để xem nó thực sự hoạt động như thế nào, hãy xem xét ví dụ sau.
Giả sử, bạn có hai cột bán hàng, giả sử 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, và vì vậy trên.
Để hoàn thành nó, 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 bài kiểm tra logic của 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à có hoa hồng được chỉ định 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 IF HOẶC.
IF lồng nhau trong Excel với câu lệnh AND
Nếu các bài kiểm tra logic của bạn bao gồm nhiều điều kiện và tất cả các điều kiện đó sẽ đánh giá thành TRUE, hãy thể hiện chúng bằng cách sử dụng Hàm AND.
Ví dụ: để chỉ định 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%, ""))))
Kết quả là, công thức IF lồng nhau của chúng tôi 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ì IFs lồng nhau.
Để bắt đầu, hãy tạo một bảng tham chiếu như hiển thị trong ảnh chụp màn hình bên dưới. Và sau đó, xây dựng một công thức Vlookup với trận đấu gần đúng, tức là với phạm vi_lookup đối số được đặt thành TRUE.
Giả sử giá trị tra cứu là B2 và bảng tham chiếu là F2: G5, công thức sẽ như sau:
=VLOOKUP(B2,$F$2:$G$5,2,TRUE)
Xin lưu ý rằng chúng tôi sửa bảng_array với các tham chiếu tuyệt đối ($ F $ 2: $ G $ 5) cho công thức sao chép chính xá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 của bạn thành TRUE, bạn nói với Excel để tìm kiếm phù hợp nhất – nếu không tìm thấy kết quả khớp chính xác, hãy trả về giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu. Kết quả 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 ở giữa.
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 và Vlookup với kết quả khớp chính xác sẽ trả về lỗi # N / A trong trường hợp này. Nhưng Vlookup với kết quả gần đúng 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 tôi 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ì trống? Trong trường hợp này, công thức Vlookup sẽ trả về lỗi # N / A. Nếu đó không phải là những gì bạn thực sự muốn, 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")
Lưu ý quan trọng! Để công thức Vlookup có kết quả gần đúng hoạt động chính xác, cột đầu tiên trong bảng tra cứu phải được sắp xếp trong thứ tự tăng dần, từ nhỏ nhất đến lớn nhất.
Để biết thêm thông tin, xin vui lòng xem Kết hợp chính xác VLOOKUP so với trận đấu 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 – chức năng 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 “thắng”:
IFS (logic_test1, value_if_true1, [logical_test2, value_if_true2]…)
Theo cú pháp trên, công thức IF lồng nhau của chúng tôi 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%)
Vui lòng lưu ý rằng hàm IFS trả về lỗi # N / A nếu không có điều kiện được chỉ định nào được đáp ứng. Để tránh điều này, bạn có thể thêm một kiểm tra 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, "")
Do đó, công thức của chúng tôi sẽ trả về một chuỗi trống (ô 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 hoặc số âm.
Ghi chú. Giống như IF lồng nhau, hàm IFS của Excel 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.
CHỌN thay vì 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 một công thức trong Excel là sử dụng hàm CHOOSE, được thiết kế để 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 của chúng tôi, công thức có dạng 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à 4thứ tự giá trị được trả lại, là 10%.
=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 CHỌN Excel với các ví dụ về công thức.
Hàm SWITCH là một dạng ngắn gọn của IF lồng nhau trong Excel
Trong các trường hợp khi 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ệ, hàm SWITCH có thể là một thay thế nhỏ gọn cho các câu lệnh IF lồng nhau phức tạp:
SWITCH (biểu thức, value1, result1, value2, result2, tầm, [default])
Hàm SWITCH đánh giá biểu hiện chống lại một danh sách giá trị và trả lại kết quả tương ứng với trận đấu đầu tiên được tìm thấy.
Trong trường hợp, bạn muốn tính toán hoa hồng dựa trên các lớp sau, 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ư hiển thị trong ả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, "")
Xin lưu ý rằng chúng tôi 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:
Ghi chú. Hàm SWITCH chỉ khả dụng trong Excel 2016 trở lên.
Để biết thêm thông tin, xin vui lòng 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ư đã đề cập trong ví dụ trước, hàm SWITCH chỉ được giới thiệu trong Excel 2016. Để xử lý các tác vụ tương tự trong các phiên bản Excel 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.
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 tôi nhân kết quả với 1 trong cả hai công thức. Nó được thực hiện để 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ì hoạt động nhân không cần thiết.
Để biết thêm thông tin, xin vui lòng xem Hàm CONCATENATE trong Excel.
Bạn có thể thấy rằng Microsoft Excel cung cấp một số lựa chọn thay thế tốt cho các công thức IF lồng nhau và hy vọng hướng dẫn này đã cung cấp cho bạn một số manh mối về cách tận dụng chúng trong bảng tính của bạn. Để xem kỹ hơn các ví dụ được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để Excel lồng nhau nếu báo cáo. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
Bạn cũng có thể quan tâm:
Source link
The post Các câu lệnh IF lồng nhau của Excel – ví dụ, thực tiễn tốt nhất và các lựa chọn thay thế appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cac-cau-lenh-if-long-nhau-cua-excel-vi-du-thuc-tien-tot-nhat-va-cac-lua-chon-thay-the.html
Không có nhận xét nào:
Đăng nhận xét