Cách sử dụng hàm conditional formatting

Excel có một công cụ định dạng có kiều kiện [Condititional Formatting] khá là đầy đủ cho nhu cầu của người dùng. Tuy nhiên các kiểu định dạng có sẵn của Excel không đủ để đáp ứng yêu cầu của bạn, Excel hỗ trợ người dùng có thể dễ dàng tạo quy tắc mới.

Các kiểu quy tắc định dạng có sẵn trong Conditional Formatting

Vị trí của Conditional Formatting là nằm trong thẻ Home. Để cài đặt định dạng cho một vùng /cột /dòng dữ liệu, bạn chọn [bôi đen] vùng dữ liệu đó. Chọn thẻ Home [1] và nhấn vào biểu tượng Conditional Formatting [2].

Highlight cells Rules - Quy tắc đánh dấu mầu ô theo quy tắc giá trị

• Greater than: Giá trị lớn hơn.
• Less than: Giá nhỉ nhỏ hơn.
• Between: Giá trị trong khoảng.
• Equal to: Giá trị bằng.
• Text that Contains: Giá trị chứa một chuỗi ký tự nào đó.
• A Date Occciring: Giá trị chứa ngày tháng định sẵn.
• Duplicate Value: Giá trị trùng lặp.

Ví dụ bảng trên bạn tìm các giá trị doanh thu lớn hơn 4.000.000.000 và bôi mầu ô đó =>  Bạn chọn Greater than. Mục giá trị so sánh bạn gõ số  4.000.000.000 [1]. Mục định dạng bôi mầu [2], bạn có thể lựa chọn các định dạng có sẵn của Excel hoặc tùy chỉnh bằng cách chọn Custom Format.

Sau khi lựa chọn xong các tùy chọn bạn nhấn nút OK. Và kết quả là các ô chứa giá trị lớn hơn 4.000.000.000 được bôi mầu.

Top /Botton Rules – Quy tắc xác định ô theo thứ hạng

Excel xác định thứ hạng của ô trong vùng dữ liệu và định dạng ô đó.

• Top 10 Items: Định dạng 10 ô có giá trị lớn nhất.
• Top 10 %: Định dạng 10% số lượng ô có giá trị lớn nhất.
• Bottom 10 Items: Định dạng 10 ô có giá nhị nhỏ nhất.
• Bottom 10%: Định dạng 10% số lượng ô có giá trị nhỏ nhất.
• Above Average: Định dạng ô lớn hơn giá trị trung bình của vùng dữ liệu.
• Below Average: Định dạng ô nhỏ hơn giá trị trung bình của vùng dữ liệu.

Ví dụ bảng trên, bạn cần định dạng mầu các ô nhỏ hơn giá trị trung bình của vùng dữ liệu, bạn chọn Below Average. Cửa sổ tùy chọn định dạng hiện lên, bạn có thể lựa chọn các định dạng có sẵn của Excel hoặc tùy chỉnh bằng cách chọn Custom Format. Rồi nhấn nút OK.

Data Bar – Định dạng mức độ lớn nhỏ của từng giá trị

Với định dạng này, mức độ lớn nhỏ của từng ô trong vùng dữ liệu sẽ được phân định bằng việc bôi màu dài hay ngắn.

Color Scale – Quy tắc xắp xếp dữ liệu theo thứ tự tăng giảm theo độ đậm nhạt của mầu sắc

Quy tắc này sẽ sắp xếp thứ tự của từng ô trong chuỗi và bôi mầu theo định dạng mà người dùng lựa chọn.

Icon Set – Thêm biểu tượng vào ô giá trị

Với kiểu mẫu định dạng này, Excel phân nhóm dữ liệu bằng biểu tượng đặc biệt như mũi tên, đèn giao thông,..

Tạo một quy tắc định dạng mới trong Excel

Nếu như các kiểu quy tắc định dạng có sẵn vẫn chưa đáp ứng được nhu cầu của báo cáo, bạn có thể thiết lập định dạng mới bằng cách:

Bước 1: Bạn chọn [bôi đen] vùng dữ liệu cần đổi màu. Trên thẻ Home [1], bạn nhấn vào biểu tượng Conditional Formatting [2] và chọn New Rule [3].

Bước 2: Cửa sổ New Formatting Rule hiện ra.

[1] Select a Rule Type: chọn kiểu phân loại dữ liệu mà bạn cần thực hiện.

• Format all cells based on their values: Bôi màu nền tất cả các ô dựa trên giá trị của vùng dữ liệu.
• Format only cells that contain: Bôi màu nền ô mà chứa một dữ liệu nào đó.
• Format only top or bottom ranked values: Bôi màu các ô xếp hạng đầu hoặc cuối trong vùng dữ liệu chọn.
• Format only values that are above or below average: Bôi màu nền các ô có giá trị trên hoặc dưới giá trị trung bình.
• Format only unique or duplicate values: Bôi màu các ô có giá trị duy nhất hoặc bị trùng trong vùng dữ liệu đã chọn.
• Use a formula to determine which cells to format: Sử dụng công thức khác để tô màu.

[2] Edit the Rule Description: Tùy chọn thiết lập mô tả cho kiểu phân loại dữ liệu mà bạn chọn ở trên.

Với kiểu thiết lập trên, Thủ Thuật Phần Mềm đã cài đặt định dạng cho số lớn nhất mầu càng cam nhạt và đậm dần cho đến số nhỏ nhất. Kết quả thu được như sau:

Xóa và quản lý quy tắc định dạng

Để kiểm tra và quản lý quy tắc định dạng, bạn vào Conditional Formatting và chọn Manage Rules. Cửa sổ Manage sẽ hiện ra, dựa vào cửa sổ này bạn có thể biết được ô đang áp dụng quy tắc điều kiện nào. Ở đây bạn có thể bổ sung thêm định dạng, chỉnh /sửa định dạng và xóa định dạng.

Để xóa toàn bộ định dạng của Conditional Formatting, bạn không thể chỉnh bằng Fill [bôi mầu ô ], mà phải xóa định dạng đã cài đặt bằng cách:

Bước 1: Bạn chỉ bôi đen vùng dữ liệu mà cần xóa định dạng. Trên thẻ Home [1], bạn nhấn vào biểu tượng Conditional Formatting [2] và chọn Clear Rules [3].

Trong đó: Clear rules from selected Cells là xóa định dạng ở ô đang được chọn.

Clear rules from entire Sheet là xóa toàn bộ định dạng ở trang tính đang mở.

Trên đây Thủ Thuật Phần Mềm đã hướng dẫn bạn cách sử dụng công cụ định dạng có điều kiện Conditional Formatting trong Excel. Chúc các bạn thành công!.