Hướng dẫn định dạng có điều kiện Icon Sets, Data Bars và Color Scales

Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn chi tiết cách sử dụng Icon Sets, Data Bars và Color Scales của nhóm định dạng có điều kiện trong Excel 2016, 2013, 2010 và 2007. Đồng thời sẽ giúp bạn mở rộng việc sử dụng các định dạng có điều kiện này và áp dụng các biểu tượng dựa trên giá trị của một ô khác.

Xem nhanh

Định dạng có điều kiện của Icon Sets trong Excel

Các biểu tượng định dạng có điều kiện của Excel sẽ giúp bạn biểu diễn dữ liệu của mình một cách trực quan bằng mũi tên, hình dạng, dấu check, cờ, đánh giá theo sao và các biểu tượng khác.

Bạn dùng bộ biểu tượng cho dữ liệu của mình bằng cách nhấp vào Conditional Formatting>Icon Sets, và các biểu tượng xuất hiện bên trong các ô được chọn ngay.

Trong hình ở trên, bạn có thể xem bảng liệt kê chi tiêu gia đình với vòng tròn màu xanh lá cây biểu thị giá trị cao nhất và màu đỏ cho giá trị thấp hơn. Ta không thể nói rằng Excel đã giải thích dữ liệu theo cách ta muốn, vì vậy chúng ta hãy tùy chỉnh việc thiết lập biểu tượng một chút. Để thực hiện việc này, hãy nhấp More Rules… bên dưới danh sách tập hợp biểu tượng để mở hộp thoại New Formatting Rule.

Từ đây bạn có thể chọn các biểu tượng khác và gán chúng cho các giá trị khác nhau. Ta chọn các biểu tượng tương tự nhưng chỉ định chúng theo một cách khác:

Mẹo:

Để thay đổi thứ tự của các biểu tượng, nhấp vào nút Reverse Icon Order.
Để ẩn các giá trị của các ô, hãy chọn hộp Show Icon Only.
Để gán các biểu tượng dựa trên giá trị của một ô thay vì chỉ định một số hoặc phần trăm, hãy gõ địa chỉ của ô trong ô Value hoặc nhấp vào biểu tượng cửa sổ thu gọn để chọn một ô, như thể hiện trong hình bên dưới.

Xem thêm: thủ thuật excel nâng cao hay nhất mọi thời đại

Cách áp dụng một tập hợp biểu tượng Excel dựa trên giá trị của các ô:

Một ý kiến chung là các biểu tượng trong định dạng có điều kiện của Excel chỉ có thể được sử dụng để định dạng các ô dựa trên giá trị của chính chúng. Nhưng đây là một ý tưởng sai. Với chỉ một chút sáng tạo, bạn có thể gán các biểu tượng tùy thuộc vào giá trị của các ô khác trong một hàng hoặc dựa trên giá trị của ô khác, như thể hiện trong các ví dụ sau đây.

Ví dụ 1. Áp dụng một bộ biểu tượng dựa trên các ô khác trong một hàng (khoảng trống và không khoảng trống)

Giả sử bạn có một danh sách các sản phẩm bao gồm hàng trong kho, hàng đi giao và các chi tiết khác. Những gì bạn muốn là thêm các biểu tượng khác nhau vào đầu mỗi hàng phụ thuộc vào việc các ô trong cùng một hàng trống hay không. Nghĩa là bạn sẽ thêm đánh dấu vào Cột A nếu tất cả các ô trong một hàng có dữ liệu, dấu chấm than nếu một số ô trống, và biểu tượng chéo nếu tất cả ô trong một hàng trống. Để sử dụng biểu tượng Excel được thiết lập theo cách này, thực hiện các bước sau:

Thêm một cột trống ở phía trước bảng của bạn (Cột A).
Sao chép công thức sau trên Cột A: = COUNTBLANK (B2: F2), trong đó F là cột cuối cùng trong bảng của bạn. Công thức này sẽ đếm số ô trống trong mỗi hàng.
Áp dụng quy tắc đặt biểu tượng định dạng có điều kiện cho Cột A bằng cách nhấp vào More Rules…, như đã giải thích ở trên.
Trong hộp thoại New Formatting Rule , hãy làm như sau:

Nhấp vào nút Reverse Icon Order để thay đổi thứ tự của các biểu tượng.
Đánh dấu hộp Show Icon Only.
Đối với biểu tượng “X”, đặt> = 5 (trong đó 5 là số cột trong bảng của bạn, ngoại trừ cột đầu tiên).
Đối với biểu tượng “!”, đặt> = 1.
Đặt Type thành Number cho cả hai biểu tượng.

Bạn có thể xác minh các cài đặt trong hình dưới đây:

Và đây là kết quả

Tìm hiểu ngay: khóa học excel nâng cao Hà Nội

Ví dụ 2. Thêm các biểu tượng dựa trên giá trị của một ô khác

Giả sử bạn có một danh sách đơn đặt hàng và bạn cần phải thêm cờ màu xanh lá cây vào cột A khi một sản phẩm nhất định được giao, nghĩa là có bất kỳ giá trị nào trong ô tương ứng trong cột D (Ngày giao hàng). Nếu không có giá trị trong cột D, cờ đỏ sẽ được đặt trong cột A.

Thực tế, ví dụ này rất giống với ví dụ trước, chỉ khác là bạn dùng một tập hợp 2-icon dựa trên giá trị trong một cột khác.

1. Và một lần nữa, bạn bắt đầu bằng cách thêm một công thức vào cột A. Lần này chúng ta sẽ sử dụng hàm IF: = IF ($ D2 <> ” “, 3,1). Công thức cho biết đặt 3 trong cột A nếu có bất kỳ giá trị nào trong ô tương ứng trong cột D, nếu không thì đặt 1. Đương nhiên, bạn sẽ cần phải thay thế D bằng cột bạn dùng làm căn cứ định dạng của bạn.

2. Chọn tất cả các ô trong cột A, ngoại trừ tiêu đề cột và tạo quy tắc đặt biểu tượng định dạng có điều kiện bằng cách nhấp vào Conditional Formatting>Icon sets>More rules…

3. Trong hộp thoại New Formatting Rule, hãy chọn các tùy chọn sau:

Nhấp vào nút Reverse Icon Order để thay đổi thứ tự các biểu tượng.
Chọn hộp kiểm Icon Set Only.
Đối với lá cờ xanh, đặt> = 3.
Đối với cờ màu vàng, đặt> 2. Như bạn nhớ, ta không có một lá cờ màu vàng ở bất cứ đâu, vì vậy bạn hãy đặt một điều kiện mà sẽ không bao giờ được đáp ứng, nghĩa là giá trị lớn hơn 2 và nhỏ hơn 3.
Đặt Type thành “Number” cho cả hai biểu tượng.

Và đây là kết quả cho nỗ lực của bạn – các biểu tượng lá cờ màu xanh lục và đỏ được thêm vào cột A dựa trên giá trị trong cột D.

Tìm hiểu thêm: Bộ tài liệu hướng dẫn học Excel cơ bản hay nhất

Định dạng có điều kiện Data Bars

Bạn sử dụng định dạng có điều kiện Data Bars để biểu diễn dữ liệu bên trong một ô. Thanh dài nhất thể hiện giá trị cao nhất, và bạn có thể dễ dàng đoán, các thanh ngắn hơn thể hiện các giá trị nhỏ hơn. Các thanh dữ liệu có thể giúp bạn nhận thấy số lớn và số nhỏ trong bảng tính của bạn, ví dụ như các sản phẩm bán nhiều nhất và ít nhất trong báo cáo bán hàng của bạn.

Bạn áp dụng thanh dữ liệu trong Excel theo cách đã quen thuộc – chỉ cần chọn các ô, bấm Conditional Formatting>Data Bars và chọn loại thanh bạn muốn. Một khi bạn làm điều này, thanh màu sẽ được đặt bên trong các ô được chọn.

Như bạn thấy trong ảnh chụp màn hình ở trên, thanh dữ liệu định dạng theo điều kiện hoạt động rất tốt để hiển thị trực quan các khoản chi tiêu gia đình của bạn.

Nếu bạn muốn áp dụng kiểu thanh dữ liệu của riêng bạn, hãy nhấp vào More Rules… như thường lệ để gọi cửa sổ Edit Formatting Rule, nơi bạn chọn các tùy chọn:

Đặt dấu kiểm tra vào ô Show Bar Only để ẩn giá trị của các ô và chỉ hiển thị thanh màu.
Để chọn loại dữ liệu Minimum và Maximum, nhấp vào mũi tên màu đen nhỏ bên cạnh hộp tương ứng. Mặc dù kiểu Automatic hoạt động tốt trong hầu hết các trường hợp, bạn cũng có thể chọn một số loại dữ liệu khác như phần trăm, số, công thức, v.v.
Thử nghiệm với màu nền, khung viền và hướng thanh và nhấp OK khi bạn hài lòng với Data Bar Preview.

Định dạng có điều kiện Color Scales

Sử dụng định dạng có điều kiện Color Scales, bạn có thể định dạng ô của mình với hai hoặc ba màu theo thang, trong đó các màu sắc khác nhau đại diện cho các giá trị ô khác nhau.

Bạn có thể bắt đầu bằng cách nhấp Conditional Formatting>Color Scales và di chuột qua hình thu nhỏ của các thang màu sẵn có Excel đã tạo cho bạn. Hai màu xanh lá cây và trắng dường như khá đẹp cho ngân sách gia đìnhi:

Nhưng nếu bạn thực sự muốn gây ấn tượng với ai đó, có thời gian và mong muốn thử nghiệm, hãy thử lại More Rules… và trải nghiệm với màu sắc một chút. Trước hết, bạn chọn thang đo 2 màu hoặc 3 màu, sau đó chọn màu của riêng bạn và chỉ định các giá trị tối thiểu, tối đa, và trung bình.