IF là hàm Excel được sử dụng nhiều nhất. Dưới đây là 10 thủ thuật IF nâng cao để đưa công thức của bạn lên “next-level”
#1 – Điều kiện “chỉ có một trong”
Tình huống: Xác định nhân viên chỉ có một trong hai điều kiện giới tính = nam hoặc mức lương dưới 85.000 đô la
Công thức:
=IF(XOR(D8=”Male”,G8<85000),”Include”, “Exclude”)
Giải thích:
Hàm XOR sẽ trả về TRUE nếu một số điều kiện lẻ trả về là TRUE, ngược lại FALSE.
Vì vậy, công thức XOR(D8 = ”Nam”, G8 <85000) sẽ hữu ích để kiểm tra điều kiện chỉ một trong hai.
Lưu ý: XOR không hoạt động khi bạn muốn kiểm tra một trong hai điều kiện khi bạn có nhiều hơn 2 điều kiện. Đối với điều đó, hãy tham khảo thủ thuật tiếp theo.
#2 – Kiểm tra 2 trên 3 điều kiện
Tình huống: Đánh dấu những nhân viên đáp ứng 2 trong 3 điều kiện dưới đây
- – Thuộc bộ phân Website
- – Năm vào làm là 2019
- – Lương trên $90,000
Công thức:
=IF((E8=”Website”)+(YEAR(F8)=2019)+(G8>90000)>=2, “Include”, “Exclude”)
Giải thích:
Excel hiểu TRUE là 1 và FALSE là 0.
Vì vậy, biểu thức (E8=”Website”)+(YEAR (F8)=2019)+(G8>90000) sẽ được chuyển đổi thành một loạt các số 1 và số 0 và được cộng lại, tùy thuộc vào thông tin chi tiết của nhân viên.
Sau đó, chúng ta có thể chỉ cần kiểm tra xem số đó có >= 2 hay không để xem có đáp ứng bất kỳ hai trong ba điều kiện nào không.
#3 – Sử dụng hàm MEDIAN cho điều kiện “trong khoảng”
Tình huống: Xác định những nhân viên gia nhập công ty trong khoảng 1/1/2019 và 30/6/2019
Công thức:
=IF(MEDIAN(F8,DATE(2019,1,1),DATE(2019,6,30))=F8, “Review”,””)
Giải thích:
Thông thường, chúng ta sử dụng hàm AND() để kiểm tra điều kiện “trong khoảng”. Tuy nhiên, bạn cũng có thể sử dụng MEDIAN cho việc này.
Mô hình giống như,
= MEDIAN(giá trị của bạn, trên, dưới) = giá trị của bạn
Giá trị phía trên sẽ là TRUE nếu giá trị của bạn nằm giữa các giá trị trên và dưới.
Ví dụ, =MEDIAN(7, 3,9) = 7 là TRUE.
#4 – Thay thế các hàm IF lồng nhau
Tình huống: Tính lương thưởng nhân viên dựa trên quy luật sau:
- – 1% cho nhân viên Website
- – 3% cho nhân viên Sales gia nhập từ 2018
- – 2% cho các trường hợp còn lại
Công thức:
=IFS(E8=”Website”,1%,AND(E8=”Sales”,YEAR(F8)=2018),3%,TRUE,2%)
Giải thích:
Các hàm IF lồng nhau có thể khó viết và khó duy trì. Đó là lý do tại sao, bạn nên sử dụng hàm IFS() mới được giới thiệu.
Cú pháp cho IFS như sau:
= IFS(điều kiện1, giá trị1, điều kiện2, giá trị2…)
Nhưng IFS() không có tùy chọn ELSE…?
Bạn có thể sử dụng TRUE làm điều kiện cuối cùng để khắc phục điều này.
Trong công thức TRUE ở trên, phần 2% xử lý trường hợp ELSE một cách đẹp mắt.
#5 – Boolean Logic để tránh các công thức IF
Tình huống: Tính lương thưởng nhân viên dựa trên quy luật sau, nhưng không dùng hàm IF:
- – 1% cho nhân viên Website
- – 3% cho nhân viên Sales gia nhập từ 2018
- – 2% cho các trường hợp còn lại
Công thức:
=2% – (E8=”Website”)*1% + AND(E8=”Sales”,YEAR(F8)=2018)*1%
Giải thích:
Bạn có thể sử dụng kiểm tra boolean logic để hoàn toàn tránh các công thức IF
Công thức trên tính tiền thưởng cho nhân viên bằng cách sử dụng khái niệm TRUE=1 & FALSE=0.
Hãy kiểm tra nó cho nhân viên bên dưới:
Đối với Gigi:
- – 2% – (FALSE)*1% + (TRUE)* 1% = 3%
Đối với Curtice:
- – 2% – (FALSE)*1% + (FALSE)*1% = 2%
#6 – Kiểm tra xem một giá trị có nằm trong danh sách khác không
Tình huống: Kiểm tra xem nhân viên có thuộc nhóm hỗ trợ cuộc gọi hay không
(phạm vi: C32:C36)
Công thức:
=IF(COUNTIFS($C$32:$C$36,C8),”On call”,”Not on call”)
Giải thích:
Chúng ta có thể sử dụng các hàm COUNTIFS hoặc MATCH để thực hiện việc này.
Chỉ cần đếm nếu một điểm dữ liệu nhất định nằm trong danh sách khác.
Tại sao chúng ta không kiểm tra >0?
Hãy nhớ rằng, Excel coi bất kỳ số nào khác 0 là TRUE. Vì vậy, chúng ta không cần viết COUNTIFS($C$32:$C$36,C8)>0.
#7 – Mảng trong công thức IF
Tình huống: Tính lương trung bình của nhân viên Website
Công thức:
=MEDIAN(IF(E8:E23=”Website”,G8:G23))
Giải thích:
Khi bạn sử dụng mảng trong công thức IF, nó cũng sẽ trả về một mảng kết quả.
Vì vậy, ví dụ: =IF({TRUE,TRUE,FALSE}, {1, 2, 3}, {“A”, “B”, ”C”}) sẽ trả về {1, 2, “C”}
Chúng ta cũng có thể sử dụng ý tưởng mạnh mẽ này để tính toán mức lương trung bình của nhân viên website.
Về phần ELSE thì sao? Nó bị thiếu không?
Nếu bạn không đề cập đến phần ELSE của công thức IF, nó sẽ chỉ trả về FALSE cho các giá trị đó.
Vì vậy, trong trường hợp của chúng ta, chúng ta nhận được
{FALSE;90700;48950;FALSE;FALSE;107700;… FALSE}
Khi MEDIAN đọc các giá trị đó, nó sẽ bỏ qua FALSEs và tính toán MEDIAN cho phần còn lại.
Tình huống 2: Hiển thị tên tất cả nhân viên Tài chính trong 1 ô, ngăn cách bởi dấu phẩy
Công thức:
=TEXTJOIN(“,”,,IF(E8:E23=”Finance”,C8:C23,””))
Giải thích:
Nó hoạt động tương tự cấu trúc MEDIAN(IF()).
#8 – Điều kiện dựa trên ký tự đại diện
Tình huống: Xác định nếu tên nhân viên chứa ký tự bo
Công thức:
=IF(COUNTIFS(C8,”*bo*”),”bo person”,”not a bo person”)
Giải thích:
Hàm IF không nhận biết được các ký tự đại diện. Nhưng chúng ta có thể sử dụng một trong các hàm nhận biết ký tự đại diện khác bên trong IF để giải quyết vấn đề. Bạn có thể sử dụng XLOOKUP, XMATCH, MATCH, VLOOKUP, COUNTIFS cho việc này.
COUNTIFS(C8, “*bo*”) sẽ là 1 nếu tên trong C8 có bo trong đó, còn lại là 0.
#9 – Hàm IF với Conditional Formatting
Tình huống: Highlight những nhân viên đáp ứng các điều kiện cụ thể trong ô bên dưới
Công thức:
=AND($E8=$J$50,$D8=$J$51)
Giải thích:
Khi kiểm tra các quy tắc ở conditional formatting, bạn không cần sử dụng hàm IF. Chỉ cần sử dụng phần điều kiện của công thức.
Đây là kết quả của chúng ta.
#10 – Sử dụng hàm IF với biểu đồ
Tình huống: Tạo biểu đồ với mức lương của nhân viên, nhưng highlight những nhân viên có mức lương trên mức trung bình bằng một màu khác.
Quy trình:
- Thêm một cột bổ sung vào dữ liệu của bạn và sử dụng hàm IF để kiểm tra xem mức lương của một người có trên mức trung bình hay không.
- Lập biểu đồ với cả lương ban đầu và cột mới.
- Overlap các thanh (hoặc cột) 100%
- Tô màu chúng cho phù hợp.
Công thức:
=IF(G8>AVERAGE($G$8:$G$23),G8,NA())
Kết quả:
Nguồn: Chandoo.org
Xem thêm
Khóa học Microsoft Office Excel, Word, PowerPoint cơ bản
Combo Khóa học Excel For Professional
Cách sử dụng hàm VLOOKUP nâng cao trong Excel
Cách sử dụng hàm SMALL trong Excel