10  thủ thuật IF nâng cao

mẫu-dữ liệu-nếu-công thức-nâng cao-thủ thuật

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: G23mẫu-dữ liệu-nếu-công thức-nâng cao-thủ thuật

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:

boolean-thay-thế-nếu-mẫu-dữ liệu

Đố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.

quy tắc đầu vào cho định dạng có điều kiện

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.

kết quả của định dạng có điều kiện

 

# 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

  1. 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.
  2. Lập biểu đồ với cả lương ban đầu và cột mới.
  3. Chồng chéo các thanh (hoặc cột) 100%
  4. 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.

làm nổi bật nhân viên có mức lương trên trung bình

Đọ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

Recommended For You

About the Author: hocketoan