Cách Đặt Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Đóng Băng 1 Dòng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Excel là một trong những công cụ bảng tính mạnh mẽ nhất hiện nay, không chỉ giúp người dùng thực hiện các phép tính đơn giản mà còn hỗ trợ thực hiện các thao tác phức tạp như phân tích dữ liệu, lập báo cáo, và tự động hoá quy trình làm việc. Một trong những tính năng quan trọng và thường được sử dụng nhất trong Excel là đặt điều kiện (Conditional Formatting, Conditional Statements). Việc hiểu và thành thạo cách đặt điều kiện sẽ giúp bạn:

• Nâng cao khả năng trực quan hoá dữ liệu: Tô màu, biểu tượng, hoặc định dạng dữ liệu dựa trên tiêu chí nhất định.
• Kiểm tra và xác thực dữ liệu: Ngăn ngừa lỗi nhập liệu, phát hiện giá trị bất thường.
• Tự động hoá các quy trình: Sử dụng hàm IF, COUNTIF, SUMIF, và các hàm logic khác để tính toán linh hoạt.

Bài viết này sẽ cung cấp một hướng dẫn chi tiết, từ các khái niệm cơ bản đến các kỹ thuật nâng cao, nhằm giúp bạn nắm vững và áp dụng hiệu quả cách đặt điều kiện trong Excel. Nội dung sẽ được chia thành các phần chính:

1. Hiểu về Đặt Điều Kiện trong Excel
2. Định Dạng Có Điều Kiện (Conditional Formatting)
3. Các quy tắc cơ bản
4. Sử dụng công thức tùy chỉnh
5. Định dạng dựa trên biểu tượng và thanh dữ liệu
6. Hàm Logic và Hàm Điều Kiện
7. IF, IFS, SWITCH
8. Hàm COUNTIF, SUMIF, AVERAGEIF, và các hàm đa điều kiện
9. Kỹ Thuật Nâng Cao
10. Data Validation kết hợp với điều kiện
11. Sử dụng hàm LAMBDA và LET
12. Đặt điều kiện trong PivotTable và Power Query
13. Thực Hành Thực Tế
14. Các ví dụ thực tế trong quản lý bán hàng, tài chính, và nhân sự
15. Mẹo và Thủ Thuật
16. Tối ưu hoá hiệu suất
17. Giải quyết lỗi thường gặp

Hãy cùng bắt đầu hành trình khám phá cách đặt điều kiện trong Excel nhé!

Có thể bạn quan tâm: Cách Chỉnh Về 1 Trang Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Hiểu Về Đặt Điều Kiện Trong Excel

1.1. Định Nghĩa Cơ Bản

Đặt điều kiện trong Excel có thể hiểu là việc áp dụng một tiêu chí (condition) để quyết định cách hiển thị hoặc tính toán dữ liệu. Có hai khía cạnh chính:

• Định dạng có điều kiện (Conditional Formatting): Thay đổi màu sắc, font, hoặc biểu tượng của ô dựa trên giá trị hoặc công thức.
• Hàm điều kiện (Conditional Functions): Sử dụng các hàm logic để trả về giá trị khác nhau tùy thuộc vào điều kiện.

1.2. Tại Sao Cần Đặt Điều Kiện?

• Trực quan hoá: Khi dữ liệu lớn, việc tô màu các ô vượt ngưỡng giúp người dùng nhanh chóng nhận diện xu hướng.
• Kiểm tra tính hợp lệ: Ngăn người dùng nhập sai định dạng, ví dụ ngày sinh phải lớn hơn ngày hiện tại.
• Tự động hoá báo cáo: Tự động tính tổng, trung bình chỉ cho những dòng thỏa mãn tiêu chí nhất định.

Có thể bạn quan tâm: Cách Điểm Danh Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Định Dạng Có Điều Kiện (Conditional Formatting)

2.1. Truy Cập Vào Công Cụ

1. Chọn vùng dữ liệu cần áp dụng.
2. Vào thẻ Home → Conditional Formatting.
3. Chọn một trong các mục: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, hoặc New Rule để tùy chỉnh.

2.2. Các Quy Tắc Cơ Bản

2.2.1. Highlight Cells Rules

• Greater Than / Less Than: Đánh dấu các giá trị lớn hơn/nhỏ hơn một con số cụ thể.
• Between: Đánh dấu các giá trị nằm trong khoảng.
• Equal To: Đánh dấu các giá trị bằng nhau.
• Text that Contains: Dùng cho dữ liệu dạng văn bản.

2.2.2. Top/Bottom Rules

• Top 10 Items / Bottom 10 Items: Đánh dấu các mục cao nhất/thấp nhất.
• Above Average / Below Average: So sánh với giá trị trung bình của tập dữ liệu.

2.2.3. Data Bars, Color Scales, Icon Sets

• Data Bars: Hiển thị thanh màu trong ô, độ dài tương ứng với giá trị.
• Color Scales: Màu sắc thay đổi dần từ thấp tới cao (ví dụ: xanh → đỏ).
• Icon Sets: Thêm biểu tượng (mũi tên, dấu chấm, v.v.) dựa trên giá trị.

2.3. Sử Dụng Công Thức Tùy Chỉnh

Đây là tính năng mạnh mẽ nhất, cho phép bạn viết công thức logic để quyết định định dạng.

Bước thực hiện:

1. Chọn vùng dữ liệu.
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức (ví dụ: =AND($A2>100,$B2="Hoàn Thành")).
4. Nhấn Format, chọn định dạng mong muốn, rồi OK.

2.3.1. Ví Dụ Thực Tế

• Đánh dấu các đơn hàng chưa thanh toán: =AND($C2="Chưa thanh toán",$D2<TODAY()) (định dạng màu đỏ).
• Nhấn mạnh các nhân viên có doanh thu > 1.000.000: =$E2>1000000.

2.4. Quản Lý Quy Tắc Định Dạng

• Manage Rules: Cho phép xem, chỉnh sửa, xóa hoặc thay đổi thứ tự ưu tiên của các quy tắc.
• Stop If True: Khi bật, Excel sẽ không áp dụng các quy tắc phía dưới nếu quy tắc hiện tại trả về TRUE.

3. Hàm Logic và Hàm Điều Kiện

3.1. Hàm IF Cơ Bản

Cú pháp: IF(logical_test, value_if_true, value_if_false)

=IF(A2>100,"Lớn","Nhỏ")

3.2. Hàm IFS (Nhiều Điều Kiện)

Excel 2026 trở lên hỗ trợ IFS, giúp tránh lồng nhau quá sâu.

=IFS(A2>90,"A",A2>80,"B",A2>70,"C",TRUE,"F")

3.3. Hàm SWITCH

Thay thế cho IFS khi có nhiều giá trị cố định.

=SWITCH(B2,"Nam","Male","Nữ","Female","Khác")

3.4. Hàm COUNTIF, SUMIF, AVERAGEIF

• COUNTIF(range, criteria): Đếm số ô thỏa mãn tiêu chí.
• SUMIF(range, criteria, sum_range): Cộng các giá trị thỏa mãn.
• AVERAGEIF(range, criteria, average_range): Trung bình các giá trị thỏa mãn.

3.4.1. Ví Dụ

• Đếm số đơn hàng trên 500: =COUNTIF(C2:C100,">500")
• Tổng doanh thu của khách hàng “ABC”: =SUMIF(A2:A100,"ABC",D2:D100)

3.5. Hàm Đa Điều Kiện: COUNTIFS, SUMIFS, AVERAGEIFS

Cho phép đặt nhiều tiêu chí đồng thời.

=SUMIFS(D2:D100, A2:A100,"ABC", B2:B100,">=01/01/2026")

4. Kỹ Thuật Nâng Cao

4.1. Data Validation Kết Hợp Điều Kiện

Có thể bạn quan tâm: Cách Đưa Excel Vào Cad: Hướng Dẫn Chi Tiết Từ A Đến Z

Data Validation giúp kiểm soát dữ liệu nhập vào.

• Thiết lập: Data → Data Validation → Allow: List, Custom, etc.
• Ví dụ: Cho phép nhập ngày trong tương lai: =A2>=TODAY()

4.2. Hàm LAMBDA và LET

4.2.1. LAMBDA

Cho phép tạo hàm tự định nghĩa.

= LAMBDA(x, y, IF(x>y, x, y))(A2, B2)

4.2.2. LET

Giảm việc lặp lại công thức, tăng hiệu suất.

=LET(score, A2, IF(score>=90,"A",IF(score>=80,"B","C")))

4.3. Đặt Điều Kiện Trong PivotTable

• Value Filters: Lọc giá trị > 1000.
• Conditional Formatting: Áp dụng trực tiếp lên PivotTable để tô màu các giá trị cao/thấp.

4.4. Power Query

Trong Power Query, bạn có thể filter rows dựa trên điều kiện, hoặc add custom column sử dụng M code:

= Table.AddColumn(Source, "Status", each if Revenue > 1000000 then "High" else "Low")

5. Thực Hành Thực Tế

5.1. Quản Lý Bán Hàng

Mục tiêu:
– Tô màu đỏ các đơn hàng chưa thanh toán quá 30 ngày.
– Tính tổng doanh thu của các đơn hàng đã hoàn thành.

Công thức Conditional Formatting:

=AND($E2="Chưa thanh toán", TODAY()-$C2>30)

Công thức tổng doanh thu:

=SUMIFS(D2:D100, E2:E100, "Hoàn Thành")

5.2. Quản Lý Nhân Sự

• Điều kiện: Nhân viên có thời gian làm việc > 5 năm và đánh giá > 4.5 sẽ được đánh dấu màu xanh.
• Công thức:

=AND(YEAR(TODAY())-YEAR(H2)>=5, I2>4.5)

5.3. Báo Cáo Tài Chính

• Mục tiêu: Đánh dấu các tháng có chi phí vượt ngân sách.
• Công thức:

=J2>K2 // J: Chi phí thực tế, K: Ngân sách

6. Mẹo và Thủ Thuật

6.1. Tối Ưu Hoá Hiệu Suất

• Giới hạn vùng áp dụng: Đừng áp dụng Conditional Formatting cho toàn bộ cột nếu không cần.
• Sử dụng công thức tham chiếu tuyệt đối ($A$1) để tránh tính lại không cần thiết.
• Xóa các quy tắc không dùng trong Manage Rules.

6.2. Giải Quyết Lỗi Thường Gặp

6.3. Sử Dụng Định Dạng Động Với Bảng Động (Dynamic Tables)

Khi dữ liệu được chuyển thành Table (Ctrl+T), các công thức Conditional Formatting sẽ tự động mở rộng khi thêm hàng mới.

6.4. Xuất Báo Cáo Với Định Dạng

• Copy-Paste Special → Values nếu muốn giữ lại giá trị mà không giữ định dạng.
• Export as PDF để bảo toàn định dạng màu sắc và biểu tượng.

7. Kết Luận

Việc đặt điều kiện trong Excel không chỉ là một kỹ năng cơ bản mà còn là nền tảng để bạn thực hiện các phân tích dữ liệu phức tạp, tạo báo cáo chuyên nghiệp và tự động hoá quy trình làm việc. Từ việc sử dụng Conditional Formatting để trực quan hoá dữ liệu, đến việc áp dụng các hàm logic như IF, IFS, COUNTIF, SUMIF, và thậm chí là hàm LAMBDA hiện đại, bạn có thể biến Excel thành một công cụ mạnh mẽ, đáp ứng hầu hết các nhu cầu kinh doanh.

Hãy thực hành các ví dụ thực tế đã trình bày, kết hợp với các mẹo tối ưu hoá để nâng cao hiệu suất làm việc. Khi đã thành thạo, bạn sẽ nhận ra rằng Excel không chỉ là một bảng tính mà còn là một ngôn ngữ lập trình nhẹ giúp bạn giải quyết vấn đề một cách nhanh chóng và chính xác.

Nếu bạn còn thắc mắc hoặc muốn tìm hiểu sâu hơn về một tính năng nào đó, đừng ngần ngại khám phá tài liệu trợ giúp của Microsoft hoặc tham gia các cộng đồng Excel để chia sẻ và học hỏi. Chúc bạn thành công trong hành trình làm chủ cách đặt điều kiện trong Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.