=AND(B2>=50,C2>=50) chỉ trả về TRUE khi cả hai điều kiện đều đúng. =OR(B2>=50,C2>=50) trả về TRUE khi có ít nhất một điều kiện đúng. Khi đặt trong IF, chúng biến hai điều kiện thành một phép kiểm tra: =IF(AND(B2>=50,C2>=50),"Pass","Fail").
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Exam | Project | Both 50+ | Either 50+ |
| 2 | Ana | 72 | 64 | Pass | Pass |
| 3 | Ben | 45 | 80 | Fail | Pass |
| 4 | Chloe | 58 | 51 | Pass | Pass |
| 5 | Dan | 38 | 42 | Fail | Fail |
| 6 | Eve | 91 | 47 | Fail | Pass |
Ben và Eve đạt một phần và trượt phần còn lại, nên họ trượt ở cột D (AND) và đạt ở cột E (OR). Dan trượt cả hai phần nên trượt ở cả hai cột. Đổi điểm thi của Ben trong B3 thành 55 là bạn ấy đạt luôn ở cột D.
Hàm AND
=AND(logical1, [logical2], ...)
AND nhận tối đa 255 điều kiện và chỉ trả về TRUE nếu tất cả đều là TRUE. Chỉ cần một FALSE là kết quả thành FALSE. Khi đứng riêng, AND hiện TRUE hoặc FALSE trong ô; khi nằm trong IF, nó quyết định IF trả về kết quả nào.
Kiểm tra một số có nằm giữa hai giá trị
Excel không có hàm BETWEEN. Hãy dùng AND với một cận dưới và một cận trên:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sample | Temp | Between 10 and 20 | Status |
| 2 | S1 | 14 | TRUE | OK |
| 3 | S2 | 22 | FALSE | Check |
| 4 | S3 | 10 | TRUE | OK |
| 5 | S4 | 7 | FALSE | Check |
| 6 | S5 | 20 | TRUE | OK |
S3 và S5 nằm đúng tại hai cận và vẫn được tính là trong khoảng, vì phép kiểm tra dùng >= và <=. Muốn loại hai cận ra, hãy dùng > và <. Đặt hai cận vào ô riêng, =AND(B2>=$F$1,B2<=$F$2), giúp bạn đổi khoảng mà không phải sửa công thức. Phép kiểm tra kiểu 10<=B2<=20 không chạy được trong Excel: nó so sánh 10<=B2 (TRUE hoặc FALSE) với 20, mà Excel xếp TRUE và FALSE lớn hơn mọi con số, nên kết quả luôn là FALSE.
Hàm OR
=OR(logical1, [logical2], ...)
OR trả về TRUE nếu có ít nhất một điều kiện là TRUE, và chỉ trả về FALSE khi tất cả đều là FALSE. Đây là cách thường dùng để chấp nhận nhiều giá trị văn bản:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Zone |
| 2 | 1001 | North | Zone 1 |
| 3 | 1002 | East | Zone 2 |
| 4 | 1003 | South | Zone 1 |
| 5 | 1004 | West | Zone 2 |
| 6 | 1005 | south | Zone 1 |
Phép so sánh văn bản không phân biệt chữ hoa chữ thường, nên south viết thường vẫn rơi vào Zone 1. Với một danh sách dài các giá trị được chấp nhận, =OR(B2={"North","South","Central"}) so sánh B2 với từng phần tử của hằng mảng chỉ trong một lần.
NOT và XOR
NOT đảo ngược một giá trị logic: =NOT(TRUE) là FALSE. Hàm này dễ đọc khi điều kiện bạn đang có là điều ngược lại với điều bạn cần: =IF(NOT(C2="Done"),"Follow up","") tương đương với =IF(C2<>"Done","Follow up","").
XOR, có từ Excel 2013, là phép hoặc loại trừ: với hai điều kiện, hàm trả về TRUE khi đúng một trong hai là TRUE, và FALSE khi cả hai cùng TRUE hoặc cùng FALSE. Với nhiều điều kiện hơn, hàm trả về TRUE khi số điều kiện TRUE là số lẻ.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Owner | Status | Open | One check only |
| 2 | Report | Ana | Done | FALSE | FALSE |
| 3 | Budget | Ben | Open | TRUE | FALSE |
| 4 | Slides | Ana | Open | TRUE | TRUE |
| 5 | Survey | Ben | Done | FALSE | TRUE |
| 6 | Plan | Ana | Done | FALSE | FALSE |
Cột E là TRUE với Slides (của Ana nhưng chưa Done) và Survey (đã Done nhưng không phải của Ana), và là FALSE ở những hàng thỏa cả hai hoặc không thỏa điều kiện nào.
Kết hợp AND và OR
Các điều kiện có thể lồng vào nhau: AND trong OR hoặc OR trong AND. Một nhân viên kinh doanh được thưởng khi thuộc khu vực North và hoặc bán được trên 1.000, hoặc ký được khách hàng mới:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | New client | Bonus |
| 2 | Ana | North | 1200 | No | Bonus |
| 3 | Ben | North | 700 | Yes | Bonus |
| 4 | Chloe | South | 1500 | Yes | |
| 5 | Dan | North | 900 | No | |
| 6 | Eve | North | 1100 | Yes | Bonus |
Ana (nhờ doanh số), Ben (nhờ khách hàng mới) và Eve (nhờ cả hai) được thưởng. Chloe thỏa phần OR nhưng thuộc khu vực South, còn Dan ở North nhưng không thỏa điều kiện OR nào.
Luyện tập: miễn phí vận chuyển
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Total | Member | Shipping |
| 2 | 1001 | 35 | Yes |
Đến lượt bạn: Trong D2, hiện "Free" khi tổng đơn hàng ở B2 từ 50 trở lên hoặc khách hàng là thành viên (C2 là "Yes"), ngược lại thì hiện "Paid".
AND và OR trong công thức mảng: dùng * và +
AND và OR gộp mọi thứ được đưa vào thành một giá trị TRUE hoặc FALSE duy nhất. Điều đó đúng ý khi làm việc trên một hàng, nhưng không ổn khi công thức xử lý cả một cột cùng lúc: =AND(B2:B6="North",C2:C6>100) trả về một giá trị chung cho cả năm hàng. Trong công thức mảng (FILTER, SUMPRODUCT, SUM theo điều kiện), hãy đặt từng điều kiện trong ngoặc rồi nhân chúng với nhau để có AND, cộng chúng lại để có OR. TRUE được tính là 1 và FALSE là 0, nên tích chỉ bằng 1 khi cả hai đều đúng, còn tổng ít nhất bằng 1 khi có một điều kiện đúng.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Count | ||
| 2 | Ana | North | 1200 | North and over 1,000 | 2 | |
| 3 | Ben | South | 700 | North or over 1,000 | 4 | |
| 4 | Chloe | North | 900 | |||
| 5 | Dan | South | 1500 | |||
| 6 | Eve | North | 1100 |
F2 đếm được 2 hàng (Ana và Eve), F3 đếm được 4 hàng (tất cả trừ Ben). Ở F3, phần >0 rất quan trọng: một hàng thỏa cả hai điều kiện sẽ cộng thành 2, và phép so sánh biến mọi tổng lớn hơn 0 thành một TRUE. Cũng theo mẫu này, bạn có thể lọc hàng: =FILTER(A2:C6,(B2:B6="North")*(C2:C6>1000)); còn nếu chỉ cần đếm với logic AND thì COUNTIFS làm được mà không cần mảng: =COUNTIFS(B2:B6,"North",C2:C6,">1000"), xem trang COUNTIFS.
Câu hỏi thường gặp
Làm sao để dùng IF với AND trong Excel?
Đặt AND vào phần điều kiện của IF: =IF(AND(B2>=50,C2>=50),"Pass","Fail"). Kết quả chỉ là Pass khi cả B2 và C2 đều từ 50 trở lên.
Làm sao để kiểm tra một số có nằm giữa hai giá trị trong Excel?
Dùng AND với hai phép so sánh: =AND(B2>=10,B2<=20) trả về TRUE với mọi giá trị từ 10 đến 20, tính cả hai đầu. Đặt trong IF: =IF(AND(B2>=10,B2<=20),"In range","Out of range").
AND và OR trong Excel khác nhau thế nào?
AND chỉ trả về TRUE khi mọi điều kiện đều là TRUE. OR trả về TRUE khi có ít nhất một điều kiện là TRUE. =AND(TRUE,FALSE) là FALSE còn =OR(TRUE,FALSE) là TRUE.
Vì sao AND không hoạt động trong công thức FILTER hoặc SUMPRODUCT?
AND và OR gộp cả một vùng thành một giá trị TRUE hoặc FALSE duy nhất, thay vì mỗi hàng một giá trị. Trong công thức mảng, hãy nhân các điều kiện để có AND, (B2:B6="North")*(C2:C6>100), và cộng chúng để có OR.
Có thể dùng AND và OR trong cùng một công thức không?
Có, lồng hàm này vào trong hàm kia: =IF(AND(B2="North",OR(C2>1000,D2="Yes")),"Bonus","") yêu cầu North, cộng thêm doanh số trên 1000 hoặc có Yes ở D2.
