Bài viết này, học excel trực tuyến sẽ hướng dẫn bạn cách sử dụng hàm if lồng nhau trong excel để kiểm tra nhiều các điều kiện. Ngoài ra, chúng ta sẽ tìm hiểu một số chức năng khác có thể bị ghi đè!
bạn thường triển khai các quyết định hợp lý của mình trong excel như thế nào? Trong hầu hết các trường hợp, bạn sẽ sử dụng công thức if để kiểm tra điều kiện của mình và nó sẽ trả về một giá trị nếu điều kiện là đúng và một giá trị khác nếu khô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ả, bạn phải lồng nhiều hàm if.
Mặc dù rất phổ biến và dễ sử dụng, 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ố 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 lồng nhau cổ điển ở dạng phổ biến:
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 trong đối số value_if_false của hàm trước đó. mỗi hàm if được đặt trong một tập hợp các dấu ngoặc đơn và tất cả các dấu ngoặc đóng ở cuối công thức.
công thức chung lồng nhau 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à đúng). Để dễ hiểu hơn, câu lệnh if lồng nhau này yêu cầu excel thực hiện những việc sau:
kiểm tra điều kiện 1 (quyết định 1) , nếu đúng – trả về kết quả 1 (kết quả1) , nếu sai –
kiểm tra điều kiện 2 (quyết định 2) , nếu đúng – trả về kết quả 2 (r kết quả 2) , nếu sai –
kiểm tra điều kiện 3 (quyết định 3) , nếu đúng – trả về kết quả 3 (kết quả 3) , nếu sai –
trả về result4 (result4)
ví dụ: tìm hiểu hoa hồng của một số người bán dựa trên doanh số của họ:
trong toán học, việc thay đổi thứ tự của phép cộng không làm thay đổi tổng. trong excel, việc thay đổi thứ tự của 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, điều rất quan trọng là phải căn chỉnh các điều kiện theo hướng chính xác: từ cao nhất đến thấp nhất hoặc thấp nhất đến cao nhất. trong trường hợp này, trước tiên chúng tôi kiểm tra điều kiện “cao nhất”, sau đó là điều kiện “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 trên, kết quả sẽ là sai vì công thức của chúng tôi sẽ dừng lại sau phép thử logic đầu tiên cho bất kỳ giá trị nào lớn hơn 1. Ví dụ: bạn có doanh thu là 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à kết quả là sẽ trả về 3%.
Như bạn có thể thấy, mất nhiều thời gian để xây dựng logic của câu lệnh if lồng nhau đến cuối. Và mặc dù Microsoft Excel cho phép bạn lồng tối đa 64 hàm if trong một công thức, nhưng đó không thực sự là những gì bạn muốn làm trong bảng tính của mình. Vì vậy, nếu bạn (hoặc bất kỳ ai khác) đang xem công thức if lồng nhau của excel và cố gắng tìm ra công thức thực sự của nó, đã đến lúc suy nghĩ lại chiến lược của bạn và có thể chọn một hoặc một công cụ khác trong kho vũ khí của bạn.
nếu được lồng với hoặc / và
.condition
Trong trường hợp bạn cần đánh giá một số tập hợp điều kiện khác nhau, bạn có thể diễn đạt các điều kiện đó với hàm hoặc cũng như với hàm và, lồng các hàm trong câu lệnh if và sau đó lồng các câu lệnh if vào nhau, khá dễ dàng để hiểu. đúng không?
nếu được lồng trong excel với câu lệnh hoặc
bằng cách sử dụng hàm hoặc, bạn có thể kiểm tra hai hoặc nhiều điều kiện khác nhau trong mỗi điều kiện nếu kiểm tra logic của hàm và trả về true nếu có (ít nhất một) đối số hoặc để đánh giá thành true. Để dễ hiểu hơn, chúng ta cùng làm ví dụ sau nhé!
Giả sử bạn có hai cột doanh số bán hàng, doanh số tháng Giêng ở cột b và doanh số tháng hai trong cột c. bạn muốn kiểm tra các số trong cả hai cột và tính hoa hồng dựa trên số cao hơn. Nói cách khác, hãy tạo một công thức với logic sau: nếu doanh số tháng 1 hoặc tháng 2 trên 150 đô la, người bán được hoa hồng 10%, nếu doanh số tháng 1 hoặc tháng 2 trên 100 đô la, người bán nhận được 7% hoa hồng.
Để thực hiện việc này, hãy viết một số câu lệnh như hoặc (b2> = 150, c2> = 150) và đưa chúng vào các hàm if được thảo luận ở trên. kết quả là bạn nhận được công thức này:
= if (o (b2 & gt; = 150, c2 & gt; = 150), 10%, if (o (b2 & gt; = 101, c2 & gt; = 101), 7%, if (o (b2 & gt; = ) 51, c2 & gt; = 51), 5%, nếu (o (b2 & gt; = 11, c2 & gt; = 1), 3%, “”))))
và chúng tôi đã có hoa hồng mà chúng tôi đang tìm kiếm dựa trên số lượng bán hàng lớn nhất:
để biết thêm các ví dụ về công thức, hãy xem if hoặc câu lệnh trong excel
nếu được lồng trong excel với và
.
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 và.
Ví dụ: đối với hoa hồng dựa trên số lượng bán hàng nhỏ hơn, hãy chạy công thức trên và thay thế câu lệnh hoặc bằng câu lệnh và. 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 vượt quá 150 đô la, 7% nếu doanh số tháng 1 và tháng 2 vượt quá 100 đô la, v.v.
= if (y (b2 & gt; = 150, c2 & gt; = 150), 10%, if (y (b2 & gt; = 101, c2 & gt; = 101), 7%, if (y (b2 & gt; =) ) 51, c2 & gt; = 51), 5%, nếu (y (b2 & gt; = 11, c2 & gt; = 1), 3%, “”))))
lồng nhau nếu công thức tính hoa hồng dựa trên số thấp nhất trong cột b và c. nếu bất kỳ cột nào trống, không có hoa hồng vì không có và điều kiện đượ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 (y (b2 & gt; = 150, c2 & gt; = 150), 10%, if (y (b2 & gt; = 101, c2 & gt; = 101), 7%, if (y (b2 & gt; =) ) 51, c2 & gt; = 51), 5%, nếu (y (b2 & gt; = 11, c2 & gt; = 1), 3%, 0%))))
tìm thêm thông tin tại đây: excel nếu có nhiều điều kiện và / hoặc.
vlookup thay vì lồng nhau nếu trong excel
khi nói đến “tỷ lệ”, tức là các phạm vi giá trị số liên tục bao phủ toàn bộ phạm vi, trong hầu hết các trường hợp, bạn có thể sử dụng hàm vlookup thay vì hàm if lồng nhau.
Để bắt đầu, hãy tạo một bảng tra cứu như ảnh chụp màn hình bên dưới. sau đó tạo công thức vlookup với kết quả so khớp 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, chúng ta có công thức sau:
= vlookup (b2, $ f $ 2: $ g $ 5,2, true)
Lưu ý rằng nó sửa chữa table_array với tham chiếu tuyệt đối ($ f $ 2: $ g $ 5) để sao chép chính xác công thức sang 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, bạn đang nói với excel rằng nếu không tìm thấy kết quả phù hợ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. nói cách khác, 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 khớp gầ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, hiện tại 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 (là $ 50 in ví dụ của chúng tôi) và trả về giá trị của cột thứ hai trong cùng một 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, hãy lồng vlookup vào bên trong iferror và xuất ra khi không tìm thấy giá trị tra cứu. ví dụ:
= iferror (vlookup (b2, $ f $ 2: $ g $ 5, 2, true), “ngoài phạm vi”)
một lưu ý quan trọng cần nhớ là để công thức vlookup có kết quả gần nhất, cột đầu tiên của 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!
để biết thêm thông tin, bạn có thể xem: đối sánh chính xác vlookup và đối sánh gần đúng vlookup
câu lệnh ifs ghi đè hàm if lồng nhau
Trong Excel 2016 trở lên, Microsoft đã giới thiệu một hàm đặ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 đa 127 cặp kiểm tra logic / value_if_true và kiểm tra logic đầu tiên đánh giá đúng “thắng”:
ifs (logic_test1, value_if_true1, [logic_test2, value_if_true2]…)
Với cú pháp trên, công thức if lồng nhau có thể được tạo lại như sau:
= ifs (b2> = 150, 10%, b2> = 101, 7%, b2> = 51, 5%, b2> 0, 3%)
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 / value_if_true hợp lý vào cuối công thức sẽ trả về 0 hoặc chuỗi trống (“”) hoặc bất kỳ thứ gì bạn muốn nếu không có kiểm tra logic là đúng:
= if (b2> = 150, 10%, b2> = 101, 7%, b2> = 51, 5%, b2> 0, 3%, true, “”)
công thức của chúng tôi sẽ trả về một ô trống thay vì lỗi # n / a nếu ô tương ứng trong cột b trống, chứa văn bản hoặc là số âm.
lưu ý: cũng giống như ifs lồng nhau, hàm ifs của excel sẽ trả về giá trị tương ứng với điều kiện đầu tiên được đánh giá là true, đó là lý do tại sao thứ tự kiểm tra logic trong công thức ifs là một vấn đề.
để biết thêm thông tin, hãy xem hàm ifs của excel thay vì ifs lồng nhau.
chọn hàm thay vì công thức nếu được lồng 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 select, hàm này trả về một giá trị từ danh sách dựa trên vị trí của nó.
áp dụng cho tập dữ liệu mẫu, chúng tôi có công thức sau:
= select ((b2 & gt; = 1) + (b2 & gt; = 51) + (b2 & gt; = 101) + (b2 & gt; = 150), 3%, 5%, 7%, 10%)
trong đối số đầu tiên ( index_num ), đánh giá tất cả các điều kiện và tổng hợp kết quả. giả sử rằng true tương đương với 1 và false tương đương với 0, bằng cách này, nó sẽ tính toán vị trí của giá trị trả về.
ví dụ: giá trị trong b2 là $ 150. đối với giá trị này, tất cả 4 điều kiện đều đúng, tức là, index_num bằng 4, có nghĩa là giá trị thứ tư được trả về là 10%.
nếu không có kết quả nào là đúng, thì index_num là 0 và công thức trả về #value. sai lầm, điều sai, ngộ nhận. đừng lãng phí thời gian của bạn, hãy chọn trong hàm iferror như thế này:
= iferror (chọn ((b2> = 1) + (b2> = 51) + (b2> = 101) + (b2> = 150), 3%, 5%, 7%, 10% ), “”)
Để biết thêm thông tin, hãy xem hàm select trong excel và các ví dụ thực tế.
hàm switch là cách viết tắt của if lồng nhau trong excel
nếu đó là một tập hợp các giá trị được xác định trước cố định, không theo 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:
thay đổi (biểu thức, giá trị1, kết quả1, giá trị2, kết quả2,…, [mặc định])
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:
= change (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 tham chiếu ô thay vì các giá trị được mã hóa cứng:
= change (c2, $ f $ 2, $ g $ 2, $ f $ 3, $ g $ 3, $ f $ 4, $ g $ 4, $ f $ 5, $ g $ 5, “”)
lưu ý: chặn tất cả các tham chiếu ngoại trừ tham chiếu đầu tiên có dấu $ để ngăn chúng thay đổi khi bạn sao chép công thức sang các ô khác.
lưu ý: chức năng thay đổi chỉ khả dụng trong excel 2016 và các phiên bản mới hơn.
để biết thêm thông tin, bạn có thể tham khảo hàm switch: phiên bản viết tắt của câu lệnh if lồng nhau.
kết hợp nhiều hàm if trong excel
Như tôi đã đề cập trong ví dụ trên, hàm shift chỉ được sử dụng trong excel 2016. Ngoài ra, nếu bạn đang sử dụng 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 toán học, nối (& amp;) hoặc nối hàm!
ví dụ:
= (if (c2 = ”a”, 10%, “”) & if (c2 = ”b”, 7%, “”) & if (c2 = ”c”, 5%, ” “) & if (c2 =” d “, 3%,” “)) * 1
hoặc
= nối (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 được kết quả bằng 1 trong cả hai công thức, sau đó chuyển đổi một chuỗi được trả về bởi công thức nối thành một số. nếu kết quả mong đợi của bạn là văn bản, thì bạn không cần bước nhân này!