Định dạng có điều kiện cho phép bạn đánh dấu dữ liệu quan trọng và áp dụng định dạng cụ thể cho các ô đáp ứng các tiêu chí nhất định.
Định dạng có điều kiện của Excel là một tính năng mạnh mẽ cho phép bạn thay đổi định dạng của ô dựa trên một điều kiện (hoặc tiêu chí). Định dạng có điều kiện giúp đánh dấu, nhấn mạnh hoặc phân biệt dữ liệu được lưu trữ trong bảng tính Excel.
Tất cả các quy tắc định dạng có điều kiện đều dựa trên logic đơn giản: Nếu các điều kiện là đúng, thì định dạng cụ thể sẽ được áp dụng; nếu các điều kiện sai, thì định dạng sẽ không được áp dụng.
Định dạng có điều kiện giúp bạn đánh dấu dữ liệu quan trọng trong tập dữ liệu, nhấn mạnh các điểm bất thường và trực quan hóa dữ liệu bằng cách sử dụng các thanh dữ liệu, màu sắc và bộ biểu tượng. Trong hướng dẫn này, chúng ta sẽ khám phá cách áp dụng định dạng có điều kiện để đánh dấu các ô hoặc phạm vi ô dựa trên một điều kiện.
Cách áp dụng định dạng có điều kiện trong Excel
Ví dụ: Bạn chịu trách nhiệm quản lý một danh sách hàng tồn kho lớn với một số mặt hàng và số lượng tương ứng của chúng trong kho. Và nếu số lượng trong kho của một mặt hàng cụ thể thấp hơn, giả sử là 50, thì điều quan trọng là bạn cần ghi số đó để có thể bổ sung.
Nếu danh sách khoảng không quảng cáo đó có tập dữ liệu với hàng trăm hàng, thì việc tìm kiếm từng hàng một để xem có bất kỳ số nào trong cột đó thấp hơn '50' sẽ không hiệu quả lắm hay không. Đó là lý do mà tính năng Định dạng có điều kiện trở nên hữu ích. Với một vài cú nhấp chuột, bạn có thể đánh dấu tất cả các giá trị trong cột nhỏ hơn '50'.
Định dạng có điều kiện với các quy tắc đánh dấu
Trong ví dụ của chúng tôi, chúng tôi có một trang tính chứa hồ sơ bán hàng của một số sản phẩm. chúng tôi muốn làm nổi bật số lượng bán hàng dưới 500.
Để làm điều đó, trước tiên hãy chọn phạm vi ô mà bạn muốn áp dụng quy tắc (điều kiện). Trong ví dụ của chúng tôi, chúng tôi muốn đánh dấu số tiền nhỏ hơn 500 trong cột 'Số tiền', vì vậy hãy chọn cột D. Bạn có thể đánh dấu các giá trị trong một phạm vi ô hoặc nhiều phạm vi ô hoặc thậm chí toàn bộ trang tính.
Sau đó, chuyển đến tab ‘Trang chủ’ và nhấp vào ‘Định dạng có điều kiện’. Chọn ‘Quy tắc đánh dấu ô’ từ menu thả xuống và vì chúng tôi muốn tìm ít hơn 500 giá trị, hãy nhấp vào tùy chọn ‘Ít hơn’. Excel cung cấp bảy quy tắc đánh dấu đặt trước. Bạn có thể sử dụng bất kỳ mục nào trong số chúng để làm nổi bật dữ liệu.
Tiếp theo, hộp thoại ‘Ít hơn’ sẽ xuất hiện. Trong đó, nhập ‘500’ vào hộp ‘Định dạng ô ÍT HƠN’ và chọn định dạng cho phần đánh dấu trong menu thả xuống bên cạnh.
Bây giờ, các ô chứa ít hơn 500 giá trị của chúng sẽ được đánh dấu theo định dạng đã chọn.
Hãy xem cách đánh dấu các giá trị chứa một chuỗi văn bản cụ thể. Trong ví dụ sau, chúng tôi muốn làm nổi bật tất cả các nhân viên đến từ New South Wales (NSW).
Để làm điều đó, hãy chuyển đến tab Trang đầu -> Định dạng có Điều kiện -> Đánh dấu Quy tắc Ô -> Văn bản Chứa.
Bạn có thể sử dụng các tùy chọn khác trong menu thả xuống theo chiều ngang ‘Quy tắc ô đánh dấu’ để đánh dấu Giá trị trùng lặp, Ngày, Lớn hơn, Bằng hoặc Giữa các giá trị.
Trong hộp thoại Chứa Văn bản, nhập ‘NSW’ vào hộp và chọn định dạng, rồi nhấp vào ‘OK’.
Kết quả:
Định dạng có điều kiện với các quy tắc Trên cùng / Dưới cùng
Quy tắc Trên cùng / Dưới cùng là một quy tắc định dạng có điều kiện cài sẵn hữu ích khác có sẵn trong Excel. Các quy tắc này cho phép bạn thu hút sự chú ý đến số mục trên cùng (n) số mục trên cùng, số mục (n) dưới cùng, số phần trăm ở dưới cùng (n) hoặc các giá trị ô ở trên trung bình hoặc dưới trung bình.
Giả sử bạn có thành tích điểm của học sinh trong một bảng tính và bạn muốn tìm ra 10 học sinh có thành tích tốt nhất (10 xếp hạng hàng đầu) từ danh sách đó. Với định dạng có điều kiện, bạn có thể đánh dấu 10 điểm hàng đầu hoặc 15 điểm hàng đầu, hoặc bất kỳ (n) số mục hàng đầu nào. Để đánh dấu 10 mục hàng đầu trong một phạm vi ô, trước tiên, hãy chọn phạm vi (Tổng số) trong bảng.
Sau đó, chuyển đến ‘Định dạng có điều kiện’, mở rộng ‘Quy tắc trên cùng / dưới cùng’ và chọn tùy chọn ‘10 mục hàng đầu ..’.
Trong hộp thoại '10 mục hàng đầu', thay đổi số lượng xếp hạng bằng cách sử dụng các mũi tên nhỏ trong trường bên trái. Nếu bạn muốn đánh dấu 20 xếp hạng cao nhất (tổng điểm) trong danh sách điểm của mình, hãy đặt số thành 20. Giá trị mặc định đã là 10, vì vậy chúng tôi giữ nguyên. Chọn định dạng ô trong trường bên phải và nhấp vào 'OK'
10 điểm hàng đầu từ cột ‘Tổng số’ được đánh dấu như hình dưới đây.
Giả sử bạn muốn tìm điểm dưới trung bình trong cột "Bài kiểm tra 1" trong cùng bảng điểm đó. Để làm điều đó, hãy chuyển đến tab Trang đầu -> Định dạng có điều kiện -> Quy tắc trên cùng / dưới cùng -> Dưới mức trung bình.
Bạn có thể thiết lập định dạng có điều kiện của riêng mình thay vì chọn một trong các điều kiện được xác định trước. Để tạo định dạng của riêng bạn, hãy chọn tùy chọn ‘Định dạng tùy chỉnh’ trong cửa sổ ‘Dưới mức trung bình’.
Một cửa sổ Định dạng Ô mới sẽ mở ra, tại đây bạn có thể tùy chỉnh các ô định dạng của mình. Chúng tôi đang chọn màu cam để làm nổi bật các điểm dưới mức trung bình. Sau khi hoàn thành, hãy nhấp vào ‘OK’ hai lần để xem kết quả.
Kết quả:
Áp dụng thanh dữ liệu
Thanh dữ liệu chỉ đơn giản là các thanh ngang trong ô của bạn. Kích thước của thanh có liên quan đến giá trị của ô tương ứng với giá trị của các ô khác trong phạm vi đã chọn. Điều đó có nghĩa là giá trị của thanh ngắn thấp so với các giá trị ô khác và thanh dài có nghĩa là giá trị cao so với các giá trị ô khác.
Đầu tiên, hãy chọn phạm vi ô mà bạn muốn hiển thị bằng các thanh dữ liệu.
Tiếp theo, chuyển đến ‘Định dạng có điều kiện’ trong tab ‘Trang chủ’, sau đó mở rộng ‘Thanh dữ liệu’ trong menu thả xuống và chọn thanh dữ liệu mà bạn lựa chọn.
Sự khác biệt giữa định dạng Thanh dữ liệu và các định dạng khác là nó hiển thị trên tất cả các ô thay vì đáp ứng một điều kiện cụ thể.
Áp dụng thang màu
Thang màu rất giống với thanh dữ liệu vì chúng đều tương quan giá trị của ô riêng lẻ với giá trị của các ô khác trong phạm vi đã chọn. Tuy nhiên, các thanh dữ liệu trực quan hóa giá trị ô bằng độ dài của một thanh, trong khi thang màu thực hiện điều đó với các chuyển màu.
Mỗi tùy chọn thang màu sử dụng các mẫu gradient hai hoặc ba màu. Một màu được phân bổ cho các giá trị cao nhất, một màu khác được phân bổ cho các giá trị thấp nhất và tất cả các giá trị khác ở giữa nhận được sự pha trộn của hai màu đó.
Đối với điều này, chúng tôi sẽ sử dụng cùng một ví dụ mà chúng tôi đã sử dụng cho các thanh dữ liệu. Chọn phạm vi ô, đi tới Trang chủ -> Định dạng có điều kiện -> Thang màu. Sau đó, chọn một dải màu từ menu thả xuống theo chiều ngang của Thang màu.
Khi chúng tôi chọn dải màu đầu tiên, màu đỏ được phân bổ cho giá trị thấp nhất, màu xanh lục được phân bổ cho giá trị cao nhất và tất cả các giá trị ở giữa là màu được phân bổ là sự pha trộn giữa màu đỏ và xanh lục (như hình dưới đây). Vì màu vàng được tạo ra bằng cách kết hợp màu đỏ và xanh lục với cường độ bằng nhau nên các ô có giá trị trung bình sẽ được gán với nó.
Áp dụng bộ biểu tượng
Bộ biểu tượng là một phương pháp khác để trực quan hóa dữ liệu bên trong mỗi ô và tương quan các ô với nhau.
Chọn các ô và nhấp vào Trang chủ -> Định dạng có Điều kiện -> Bộ Biểu tượng. Bạn có thể chọn bất kỳ bộ biểu tượng nào phù hợp hơn với nhu cầu của mình. Đối với ví dụ của chúng tôi, chúng tôi sẽ chọn tùy chọn đầu tiên trong Hướng.
Các mũi tên màu đỏ, vàng và xanh lục tương ứng cho biết các mặt hàng có giá thấp, trung bình hoặc cao.
Xóa định dạng có điều kiện
Để xóa định dạng có điều kiện, hãy quay lại tùy chọn ‘Định dạng có điều kiện’ trong tab ‘Trang chủ’ và nhấp vào ‘Xóa quy tắc’. Sau đó, chọn quy tắc nào bạn muốn xóa.
Chúng tôi sẽ chọn 'Xóa quy tắc khỏi toàn bộ trang tính' để xóa tất cả định dạng có điều kiện khỏi trang tính.
Bây giờ, bạn đã học cách sử dụng định dạng có điều kiện để định dạng các ô với điều kiện đặt trước của Excel.