6 hàm điều kiện làm cho bảng tính Excel thông minh hơn

Nhiều người sử dụng Excel cho các bảng cơ bản, nhưng bạn cần phải làm nhiều hơn là chỉ tính toán những phép tính đơn giản. Nếu công thức của bạn trở nên phức tạp, các hàm điều kiện là lựa chọn thông minh hơn, được ẩn giấu ngay trong tầm mắt. Chúng xử lý logic phức tạp và tự động hóa các quyết định mà không yêu cầu bạn phải trải qua một quá trình học tập khó khăn.

6. Hàm IF và IFS: Xử lý các quyết định đơn giản và phức tạp

Hàm IF là công cụ ra quyết định cơ bản nhất của Excel. Nó đánh giá một điều kiện và trả về một giá trị nếu điều kiện đúng, một giá trị khác nếu điều kiện sai - giống như Excel hỏi "nếu thì sao?" và phản hồi tương ứng.

Đây là một ví dụ thực tế. Giả sử bạn đang quản lý dữ liệu hiệu suất của nhân viên và cần tự động phân loại xếp hạng. Bất kỳ ai có điểm trên 3,5 sẽ được đánh dấu là "Satisfactory", trong khi những người khác sẽ được đánh dấu là "Needs Improvement". Bạn có thể sử dụng hàm IF như được hiển thị trong công thức sau để xử lý việc này trên hàng trăm nhân viên.

=IF(C2>3.5, "Satisfactory", "Needs Improvement")

Nhưng còn trường hợp nhiều kết quả thì sao, vì các câu lệnh IF lồng nhau truyền thống có thể trở nên vô cùng khó khăn, như minh họa bên dưới.

=IF(C2>=4.5, "Excellent", IF(C2>=3.5, "Good", IF(C2>=2.5, "Average", "Poor")))

Đó là lúc IFS giúp bạn an tâm. Hàm IFS quản lý hiệu quả nhiều tình huống. Để tính toán tiền thưởng cho nhân viên dựa trên bậc hiệu suất, bạn có thể sử dụng hàm IFS như trong công thức sau.

=IFS(AC2>=4, "$5,000", AC2>=3, "$3,000", AC2>=2, "$1,000", TRUE, "$0")

5. Hàm SWITCH: Đơn giản hóa việc so khớp giá trị

Hàm SWITCH là một lựa chọn tốt hơn khi bạn cần so khớp giá trị chính xác, vì nó không liên quan đến logic lớn hơn hoặc nhỏ hơn; thay vào đó, nó liên quan đến việc tra cứu trực tiếp. Mỗi cặp giá trị-kết quả hoạt động như một mục từ điển, giúp cú pháp của nó trở nên đơn giản.

=SWITCH(lookup_value, value1, result1, value2, result2, default_result)

Hãy xem xét mã phòng ban của nhân viên trong bảng tính nhân sự. Nếu bạn sử dụng hàm IF, công thức sẽ trở nên dài dòng và phức tạp, như được hiển thị trong ví dụ sau.

=IF(B2="HR", "Human Resources", IF(B2="IT", "Information Technology", IF(B2="FIN", "Finance", "Unknown")))

Thay vào đó, bạn có thể sử dụng SWITCH để xử lý hiệu quả hơn.

=SWITCH(Q2:Q3004, "Sales", "S", "Production", "P", "IT/IS", "IT", "Unknown")

4. Hàm CHOOSE: Chọn giá trị theo vị trí

Hàm CHOOSE hoạt động giống như một danh sách được đánh số; bạn cung cấp số vị trí, và nó trả về giá trị tương ứng từ các tùy chọn được xác định trước. Đây là cách Excel muốn nói: "Cho tôi mục số 3 trong danh sách này."

Cú pháp rất đơn giản. Đối số đầu tiên là số vị trí, theo sau là danh sách các giá trị của bạn. Tiếp tục với ví dụ về bảng tính nhân viên, bạn có thể sử dụng hàm này để chuyển đổi điểm số thành mô tả dễ đọc:

=CHOOSE(AB2:AB3004, "Very Poor", "Poor", "Satisfactory", "Good", "Very Good")

Công thức này lấy số trong cột AB và trả về điểm số tương ứng của nhân viên. Vị trí một cho kết quả "Very Poor", vị trí hai cho kết quả "Poor", v.v...

Hàm CHOOSE đặc biệt hữu ích với báo cáo hàng quý. Bạn có thể chuyển đổi số quý thành nhãn phù hợp cho biểu đồ và bài thuyết trình:

=CHOOSE(E2, "Q1 2024", "Q2 2024", "Q3 2024", "Q4 2024")

3. Hàm AND và OR: Xây dựng Logic Phức tạp

Các hàm AND và OR xử lý những tình huống đa điều kiện mà các câu lệnh IF đơn lẻ không thể xử lý. AND yêu cầu mọi điều kiện đều phải đúng. Ví dụ, điều kiện thăng chức của nhân viên có thể yêu cầu xem xét cả thời gian công tác trên 2 năm và xếp hạng hiệu suất trên 3,5, như thể hiện trong công thức sau.

=IF(AND(C2>2, D2>3.5), "Eligible", "Not Eligible")

Nhưng OR lại có cách tiếp cận ngược lại - nó chỉ cần một điều kiện là đúng. Giả sử bạn phải kiểm tra điều kiện thưởng; chỉ những nhân viên đang làm việc mới có thể đủ điều kiện nếu họ có hiệu suất làm việc tốt:

=IF(OR(K2="Active", AC2>=1), "Bonus Qualified", "No Bonus")

2. Hàm SUMIFS, COUNTIFS và AVERAGEIFS: Phân tích dữ liệu có điều kiện

3 hàm này chuyển đổi dữ liệu thô thành thông tin chi tiết hữu ích bằng cách áp dụng đồng thời nhiều điều kiện. Chúng có thể được sử dụng để phân tích các tập dữ liệu lớn mà không cần bảng tổng hợp.

Ví dụ, một nhân viên Nhân sự muốn phân tích tổng lương của các phòng ban cụ thể, số lượng nhân viên cho một số cấp bậc công việc nhất định hoặc xếp hạng hiệu suất trung bình theo nhóm. Các hàm này xử lý việc phân tích đa tiêu chí như vậy một cách dễ dàng.

Hàm SUMIFS cộng các giá trị đáp ứng nhiều tiêu chí. Khi tính tổng lương cho nhân viên phòng IT có hơn 3 năm kinh nghiệm, công thức trở thành:

=SUMIFS(E:E, B:B, "IT", D:D, ">3")

Cú pháp tuân theo một mẫu logic: Tổng phạm vi, phạm vi tiêu chí 1, tiêu chí 1, phạm vi tiêu chí 2, tiêu chí 2. Bạn có thể thêm tối đa 127 cặp điều kiện cho các phép tính cụ thể.

Mặt khác, hàm COUNTIFS đếm các ô đáp ứng nhiều điều kiện. Để xác định số lượng nhân viên tiếp thị có điểm đánh giá hiệu suất trên 4,0, chúng ta sẽ sử dụng công thức sau.

=COUNTIFS(B:B, "Marketing", F:F, ">4")

Công thức này xác định những người có hiệu suất cao theo từng phòng ban, giúp xác định các ứng viên tiềm năng được thăng chức hoặc nhu cầu đào tạo trong toàn tổ chức.

Hàm AVERAGEIFS tính toán điểm trung bình dựa trên nhiều tiêu chí. Để tìm mức lương trung bình của nhân viên cấp cao trong ngành tài chính, chúng ta sẽ sử dụng:

=AVERAGEIFS(E:E, B:B, "Finance", C:C, "Senior")

Công thức trên tính mức lương trung bình của nhân viên cấp cao trong phòng Tài chính.

1. Hàm IFERROR và IFNA: Ngăn chặn các thông báo lỗi khó chịu

Không có gì làm hỏng một bảng tính chuyên nghiệp tệ như lỗi #DIV/0! hoặc #N/A nằm rải rác trong dữ liệu. Các hàm IFERROR và IFNA khuyến khích bạn khắc phục những lỗi Excel thường gặp và thay thế các thông báo lỗi khó chịu bằng những giải pháp thay thế có ý nghĩa hơn.

Hàm IFERROR sẽ bắt lỗi và thay thế giá trị bạn đã chọn. Ví dụ, khi tính tỷ lệ hiệu suất của nhân viên, phép chia cho số 0 sẽ tạo ra lỗi. Thay vì hiển thị #DIV/0!, bạn có thể chọn hiển thị thông tin hữu ích hơn:

=IFERROR(C2/D2, "No Data Available")

Công thức này thử phép chia nhưng sẽ hiển thị "No Data Available" nếu xảy ra lỗi, đây là cách trình bày ngắn gọn hơn.

Mặt khác, hàm IFNA đặc biệt nhắm đến lỗi #N/A từ các hàm tra cứu. Khi chúng ta tìm kiếm thông tin bằng hàm VLOOKUP, dữ liệu bị thiếu sẽ tạo ra lỗi #N/A. Để tránh điều này, chúng ta có thể sử dụng hàm IFNA như trong công thức sau.

=IFNA(VLOOKUP(A2, A2:AG3004, 4, FALSE), "Employee Not Found")

Công thức này tìm kiếm ID nhân viên trong cột A2 trong phạm vi danh sách nhân viên của bạn và trả về tên từ cột thứ tư. Khi ID nhân viên không tồn tại trong cơ sở dữ liệu của bạn, thay vì hiển thị lỗi #N/A, nó sẽ hiển thị thông báo thân thiện với người dùng "Employee Not Found".

Điểm khác biệt chính là hàm IFERROR nắm bắt mọi thứ, chẳng hạn như lỗi chia, lỗi tham chiếu và lỗi tra cứu. IFNA chỉ xử lý lỗi #N/A, cho phép các loại lỗi khác hiển thị bình thường.