10 thủ thuật IF nâng cao

Originally posted on 27/03/2022 @ 16:12

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 cấp độ tiếp theo?

Trong bài viết này, bạn sẽ học:

Chỉ một trong hai trong số ba quy tắc loại
Kiểm tra giữa tình trạng với MEDIAN
Thay thế IF lồng nhau bằng hàm ngắn hơn
Sử dụng logic boolean để thay thế các công thức IF
Mảng có hàm IF
Kiểm tra ký tự đại diện với hàm IF
Cách sử dụng công thức IF ở những nơi khác
- Định dạng có điều kiện
- Xác nhận dữ liệu
- Biểu đồ

Dữ liệu mẫu cho các ví dụ

Tất cả các ví dụ trong bài viết này sử dụng dữ liệu mẫu bên dưới. Giả sử nó nằm trong phạm vi C8: G23

Bạn có thể tải xuống dữ liệu này một mình cho mục đích thực hành từ đây.

Bao gồm dữ liệu mẫu cho thực hành, sổ làm việc Excel đã hoàn thành

# 1 – Chỉ một trong những điều kiện

Tình hình

Xác định nhân viên chỉ có một trong hai 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 trình

Hàm XOR sẽ trả về TRUE nếu đầu vào số lẻ là TRUE, ngược lại FALSE.

Vì vậy, XOR của chúng tôi (D8 = ”Nam”, G8 <85000) sẽ hữu ích để kiểm tra chỉ một điều kiện.

Lưu ý: XOR không hoạt động khi bạn chỉ 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 – Hai trong số Ba Séc

Tình hình

Gắn cờ cho nhân viên khi họ đáp ứng bất kỳ hai trong ba điều kiện dưới đây.

Cục là Trang web
Năm tham gia là 2019
Mức lương trên $ 90,000

Công thức

=IF((E8="Website")+(YEAR(F8)=2019)+(G8>90000)>=2,
        "Include", "Exclude")

Giải trình

Bí quyết là trong việc hiểu Excel coi TRUE là 1 và FALSE là 0.

Vì vậy, biểu thức (E8 = ”Trang web”) + (YEAR (F8) = 2019) + (G8> 90000)

sẽ được chuyển đổi 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ó phải là> = 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 MEDIAN cho giữa các tình trạng

Tình hình

Xác định nhân viên đã tham gia từ ngày 1 tháng 1 năm 2019 đến ngày 30 tháng 6 năm 2019.

Công thức

=IF(MEDIAN(F8,DATE(2019,1,1),DATE(2019,6,30))=F8,
        "Review","")

Giải trình

Thông thường, chúng tôi sử dụng hàm AND () để kiểm tra giữa các điều kiện. 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ị ở trên sẽ là ĐÚNG 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 hình

Tính tiền thưởng cho nhân viên dựa trên các quy tắc dưới đây:

1% cho nhân viên Website
3% cho nhân viên Kinh doanh tham gia năm 2018
2% cho những người khác

Công thức

=IFS(E8="Website",1%,
        AND(E8="Sales",YEAR(F8)=2018),3%,
        TRUE,2%)

Giải trình

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 – Logic Boolean để tránh các công thức IF

Tình hình

Tính tiền thưởng cho nhân viên dựa trên các quy tắc dưới đây, nhưng không sử dụng bất kỳ công thức IF nào :

1% cho nhân viên Website
3% cho nhân viên Kinh doanh tham gia năm 2018
2% cho những người khác

Công thức

=2% - (E8="Website")*1% + AND(E8="Sales",YEAR(F8)=2018)*1%

Giải trình

Bạn có thể sử dụng kiểm tra logic boolean để tránh hoàn toàn các công thức IF. Điều này hoạt động tốt khi đầu ra của bạn là số.

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 hình

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 trình

Chúng ta có thể sử dụng các hàm COUNTIFS hoặc MATCH để thực hiện việc này. Tôi thích COUNTIFS hơn.

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 có công thức IF

Tình hình

Tính lương trung bình của nhân viên trang web

Công thức

=MEDIAN(IF(E8:E23="Website",G8:G23))

Giải trình

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 trang web.

Về phần ELSE thì sao? Nó 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 tôi, chúng tôi 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.

Đọc thêm: Tính RANKIFS với Excel

Tình huống 2

Hiển thị tất cả tên của nhân viên “Tài chính” trong một ô, được phân tách bằng dấu phẩy.

Công thức

=TEXTJOIN(",",,IF(E8:E23="Finance",C8:C23,""))

Giải trình

Điều này hoạt động giống như cấu trúc MEDIAN (IF ()). Để biết thêm các ứng dụng của kỹ thuật này, hãy xem Bản đồ rủi ro trong Excel

# 8 – Điều kiện dựa trên ký tự đại diện

Tình hình

Xác định xem tên của nhân viên có chứa các chữ cái bo hay không

Công thức

=IF(COUNTIFS(C8,"*bo*"),"bo person","not a bo person")

Giải trình

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.

Tôi thích COUNTIFS hơn.

COUNTIFS (C8, “* bo *”) sẽ là 1 nếu tên trong C8 có bo trong đó, còn lại là 0.

Phần còn lại là tự giải thích.

# 9 – Công thức IF với Định dạng có Điều kiện

Tình hình

Đánh dấu những nhân viên đáp ứng các điều kiện được chỉ định trong các ô bên dưới.

Qui định

=AND($E8=$J$50,$D8=$J$51)

Giải trình

Khi kiểm tra các quy tắc ở định dạng có điều kiện , bạn không cần sử dụng công thức IF. Chỉ cần sử dụng phần điều kiện của công thức một mình.

Đây là kết quả của quy tắc của chúng tôi.

# 10 – Sử dụng IF với Biểu đồ

Tình hình

Tạo biểu đồ với mức lương của nhân viên, nhưng đánh dấu 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 trong dữ liệu của bạn và sử dụng công thức 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.
Chồng chéo các thanh (hoặc cột) 100%
Màu sắc chúng cho phù hợp.

Công thức

=IF(G8>AVERAGE($G$8:$G$23),G8,NA())

Kết quả

Đây là biểu đồ của tôi trông như thế nào.

Đọc thêm: Cách đánh dấu các điểm quan trọng trên biểu đồ của bạn

Tài nguyên – Tệp & Video

Bao gồm dữ liệu mẫu cho thực hành, sổ làm việc Excel đã hoàn thành

Dữ liệu mẫu cho các ví dụ

# 1 – Chỉ một trong những điều kiện

# 2 – Hai trong số Ba Séc

# 3 – Sử dụng MEDIAN cho giữa các tình trạng

# 4 – Thay thế các hàm IF lồng nhau

# 5 – Logic Boolean để tránh các công thức IF

# 6 – Kiểm tra xem một giá trị có nằm trong danh sách khác không

# 7 – Mảng có công thức IF

# 8 – Điều kiện dựa trên ký tự đại diện

# 9 – Công thức IF với Định dạng có Điều kiện

# 10 – Sử dụng IF với Biểu đồ

Tài nguyên – Tệp & Video

My Lê

thủ thuật định dạng có điều kiện đơn giản

Recommended

Cách chỉnh sửa, đánh giá và sửa lỗi công thức trong Excel

Phương pháp hạch toán tài khoản 353 – Quỹ khen thưởng phúc lợi

Tin mới nhất

Fanpage

Welcome Back!

Retrieve your password