Nội dung
Trong bài viết này, bạn sẽ tìm thấy hai cách nhanh chóng để thay đổi màu của một ô dựa trên giá trị của nó trong Excel 2013, 2010 và 2007. Ngoài ra, bạn sẽ học cách sử dụng công thức trong Excel để thay đổi màu của các ô trống hoặc các ô có lỗi công thức.
Mọi người đều biết rằng để thay đổi màu tô của một ô hoặc toàn bộ phạm vi trong Excel, chỉ cần nhấp vào nút Tô màu (Tô màu). Nhưng điều gì sẽ xảy ra nếu bạn cần thay đổi màu tô của tất cả các ô chứa một giá trị nhất định? Hơn nữa, điều gì sẽ xảy ra nếu bạn muốn màu tô của mỗi ô tự động thay đổi khi nội dung của ô đó thay đổi? Hơn nữa trong bài viết, bạn sẽ tìm thấy câu trả lời cho những câu hỏi này và nhận được một số mẹo hữu ích sẽ giúp bạn chọn phương pháp phù hợp để giải quyết từng vấn đề cụ thể.
Cách thay đổi động màu của một ô trong Excel dựa trên giá trị của nó
Màu tô sẽ thay đổi tùy thuộc vào giá trị của ô.
Vấn đề: Bạn có một bảng hoặc phạm vi dữ liệu và bạn muốn thay đổi màu tô của các ô dựa trên giá trị của chúng. Hơn nữa, điều cần thiết là màu này thay đổi một cách linh động, phản ánh những thay đổi trong dữ liệu trong các ô.
Phán quyết: Sử dụng định dạng có điều kiện trong Excel để đánh dấu các giá trị lớn hơn X, nhỏ hơn Y hoặc giữa X và Y.
Giả sử bạn có một danh sách giá xăng ở các tiểu bang khác nhau và bạn muốn giá cao hơn $ 3.7, được đánh dấu bằng màu đỏ và nhỏ hơn hoặc bằng $ 3.45 - màu xanh lá.
Lưu ý: Ảnh chụp màn hình cho ví dụ này được chụp trong Excel 2010, tuy nhiên, trong Excel 2007 và 2013, các nút, hộp thoại và cài đặt sẽ hoàn toàn giống nhau hoặc hơi khác một chút.
Vì vậy, đây là những gì bạn cần làm từng bước:
- Chọn bảng hoặc phạm vi mà bạn muốn thay đổi màu tô của ô. Trong ví dụ này, chúng tôi đánh dấu $ B $ 2: $ H $ 10 (tiêu đề cột và cột đầu tiên chứa tên của các trạng thái không được chọn).
- Nhấn vào Trang Chủ (Trang chủ), trong phần Phong cách (Kiểu) bấm Định dạng có điều kiện (Định dạng có điều kiện)> Luật mới (Tạo quy tắc).
- Ở đầu hộp thoại Quy tắc định dạng mới (Tạo Quy tắc Định dạng) trong trường Chọn một loại quy tắc (Chọn loại quy tắc) chọn Chỉ định dạng các ô chứa (Chỉ định dạng các ô có chứa).
- Ở cuối hộp thoại trong hộp Chỉ định dạng ô với (Chỉ định dạng các ô đáp ứng điều kiện sau) Đặt các điều kiện cho quy tắc. Chúng tôi chỉ chọn định dạng các ô với điều kiện: Giá trị ô (giá trị ô) - lớn hơn (hơn) - 3.7như trong hình bên dưới.Sau đó nhấn nút Kích thước máy (Định dạng) để chọn màu tô sẽ được áp dụng nếu điều kiện cụ thể được đáp ứng.
- Trong hộp thoại xuất hiện Format Cells (Định dạng ô) Điền (Tô màu) và chọn một màu (chúng tôi đã chọn màu đỏ) và nhấp vào OK.
- Sau đó, bạn sẽ trở lại cửa sổ Quy tắc định dạng mới (Tạo quy tắc định dạng) ở đâu trong trường Xem trước (Mẫu) sẽ hiển thị một mẫu định dạng của bạn. Nếu bạn hài lòng, hãy nhấp vào OK.
Sau đó nhấn nút Kích thước máy (Định dạng) để chọn màu tô sẽ được áp dụng nếu điều kiện cụ thể được đáp ứng.
Kết quả của cài đặt định dạng của bạn sẽ giống như sau:
Vì chúng ta cần thiết lập một điều kiện khác cho phép chúng ta thay đổi màu tô thành màu xanh lục cho các ô có giá trị nhỏ hơn hoặc bằng 3.45, sau đó nhấn lại nút Luật mới (Tạo Quy tắc) và lặp lại các bước từ 3 đến 6, thiết lập quy tắc mong muốn. Dưới đây là ví dụ về quy tắc định dạng có điều kiện thứ hai mà chúng tôi đã tạo:
Khi mọi thứ đã sẵn sàng - hãy nhấp vào OK. Giờ đây, bạn có một bảng được định dạng độc đáo cho phép bạn xem nhanh giá xăng tối đa và tối thiểu ở các tiểu bang khác nhau. Tốt cho họ ở đó, ở Texas! 🙂
Mẹo: Theo cách tương tự, bạn có thể thay đổi màu phông chữ tùy thuộc vào giá trị của ô. Để làm điều này, chỉ cần mở tab Font (Phông chữ) trong hộp thoại Format Cells (Định dạng ô) như chúng ta đã làm trong bước 5 và chọn màu phông chữ mong muốn.
Cách đặt màu ô không đổi dựa trên giá trị hiện tại của nó
Sau khi được đặt, màu tô sẽ không thay đổi, bất kể nội dung của ô thay đổi như thế nào trong tương lai.
Vấn đề: Bạn muốn điều chỉnh màu của một ô dựa trên giá trị hiện tại của nó và bạn muốn màu tô được giữ nguyên ngay cả khi giá trị của ô thay đổi.
Phán quyết: Tìm tất cả các ô có một giá trị cụ thể (hoặc các giá trị) bằng công cụ Tìm tất cả (Tìm tất cả) và sau đó thay đổi định dạng của các ô được tìm thấy bằng cách sử dụng hộp thoại Format Cells (định dạng ô).
Đây là một trong những tác vụ hiếm hoi mà không có lời giải thích trong tệp trợ giúp Excel, diễn đàn hoặc blog và không có giải pháp trực tiếp. Và điều này là dễ hiểu, vì nhiệm vụ này không phải là điển hình. Chưa hết, nếu bạn cần thay đổi màu tô ô vĩnh viễn, nghĩa là một lần và mãi mãi (hoặc cho đến khi bạn thay đổi nó theo cách thủ công), hãy làm theo các bước sau.
Tìm và chọn tất cả các ô đáp ứng một điều kiện nhất định
Một số trường hợp có thể xảy ra ở đây, tùy thuộc vào loại giá trị bạn đang tìm kiếm.
Ví dụ: nếu bạn muốn tô màu các ô với một giá trị cụ thể, 50, 100 or 3.4 - sau đó trên tab Trang Chủ (Trang chủ) trong phần Chỉnh sửa (Chỉnh sửa) bấm vào Tìm Chọn (Tìm và tô sáng)> Tìm kiếm (Tìm thấy).
Nhập giá trị mong muốn và nhấp vào Tìm tất cả (Tìm tất cả).
Mẹo: Ở bên phải của hộp thoại Tìm và Thay thế (Tìm và Thay thế) có một nút Các lựa chọn (Tùy chọn), bằng cách nhấn, bạn sẽ có quyền truy cập vào một số cài đặt tìm kiếm nâng cao, chẳng hạn như Trường hợp trận đấu (phân biệt chữ hoa chữ thường) và Khớp toàn bộ nội dung ô (Toàn bộ ô). Bạn có thể sử dụng các ký tự đại diện như dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào hoặc dấu chấm hỏi (?) Để khớp với bất kỳ ký tự đơn nào.
Về ví dụ trước, nếu chúng ta cần tìm tất cả giá xăng từ 3.7 đến 3.799, sau đó chúng tôi sẽ đặt các tiêu chí tìm kiếm sau:
Bây giờ hãy nhấp vào bất kỳ mục nào được tìm thấy ở cuối hộp thoại Tìm và Thay thế (Tìm và Thay thế) và nhấp vào Ctrl + Ađể đánh dấu tất cả các mục tìm thấy. Sau đó nhấn nút Fermer (Đóng).
Đây là cách bạn có thể chọn tất cả các ô có giá trị nhất định (các giá trị) bằng cách sử dụng tùy chọn Tìm tất cả (Tìm tất cả) trong Excel.
Tuy nhiên, trên thực tế, chúng ta cần tìm tất cả các giá xăng vượt $ 3.7. Thật không may, công cụ Tìm và Thay thế (Tìm và Thay thế) không thể giúp chúng tôi điều này.
Thay đổi màu tô của các ô đã chọn bằng hộp thoại định dạng ô
Bây giờ bạn đã chọn tất cả các ô có giá trị đã cho (hoặc các giá trị), chúng tôi vừa thực hiện việc này với công cụ Tìm và Thay thế (Tìm và thay thế). Tất cả những gì bạn phải làm là đặt màu tô cho các ô đã chọn.
Mở hộp thoại Format Cells (Định dạng ô) theo bất kỳ cách nào trong 3 cách:
- nhấn Ctrl + 1.
- bằng cách nhấp vào bất kỳ ô đã chọn nào bằng nút chuột phải và chọn mục từ menu ngữ cảnh Format Cells (định dạng ô).
- chuyển hướng Trang Chủ (Trang chủ)> Tế bào. Tế bào. (ô)> Kích thước máy (Định dạng)> Format Cells (định dạng ô).
Tiếp theo, điều chỉnh các tùy chọn định dạng theo ý muốn. Lần này, chúng tôi sẽ đặt màu tô thành màu cam, chỉ để thay đổi 🙂
Nếu bạn chỉ muốn thay đổi màu tô mà không chạm vào phần còn lại của các tùy chọn định dạng, bạn có thể chỉ cần nhấp vào nút Tô màu (Tô màu) và chọn màu bạn thích.
Đây là kết quả của những thay đổi định dạng của chúng tôi trong Excel:
Không giống như phương pháp trước (với định dạng có điều kiện), màu tô được đặt theo cách này sẽ không bao giờ tự thay đổi mà bạn không biết, bất kể giá trị thay đổi như thế nào.
Thay đổi màu tô cho các ô đặc biệt (trống, có lỗi trong công thức)
Như trong ví dụ trước, bạn có thể thay đổi màu tô của các ô cụ thể theo hai cách: động và tĩnh.
Sử dụng công thức để thay đổi màu tô của các ô đặc biệt trong Excel
Màu của ô sẽ tự động thay đổi tùy thuộc vào giá trị của ô.
Rất có thể bạn sẽ sử dụng phương pháp này để giải quyết vấn đề trong 99% trường hợp, tức là việc điền vào các ô sẽ thay đổi phù hợp với điều kiện bạn đã chỉ định.
Ví dụ, chúng ta hãy lấy lại bảng giá xăng, nhưng lần này chúng ta sẽ thêm một vài trạng thái nữa và làm cho một số ô trống. Bây giờ, hãy xem cách bạn có thể tìm thấy những ô trống này và thay đổi màu tô của chúng.
- Trên tab Nâng cao Trang Chủ (Trang chủ) trong phần Phong cách (Kiểu) bấm Định dạng có điều kiện (Định dạng có điều kiện)> Luật mới (Tạo quy tắc). Giống như trong bước thứ 2 của ví dụ Cách thay đổi động màu của một ô dựa trên giá trị của nó.
- Trong hộp thoại Quy tắc định dạng mới (Tạo quy tắc định dạng) chọn một tùy chọn Sử dụng công thức để xác định ô nào để định dạng (Sử dụng công thức để xác định ô cần định dạng). Tiến sâu hơn vào lĩnh vực này Định dạng các giá trị trong đó công thức này đúng (Định dạng các giá trị mà công thức sau là đúng) nhập một trong các công thức:
- để thay đổi điền vào các ô trống
=ISBLANK()=ЕПУСТО() - để thay đổi cách tô bóng của các ô chứa công thức trả về lỗi
=ISERROR()=ЕОШИБКА()
Vì chúng ta muốn thay đổi màu của các ô trống, chúng ta cần hàm đầu tiên. Nhập nó, sau đó đặt con trỏ giữa dấu ngoặc và nhấp vào biểu tượng chọn phạm vi ở phía bên phải của dòng (hoặc nhập phạm vi mong muốn theo cách thủ công):
=ISBLANK(B2:H12)=ЕПУСТО(B2:H12) - để thay đổi điền vào các ô trống
- Ấn nút Kích thước máy (Định dạng), chọn màu tô mong muốn trên tab Điền (Điền), và sau đó nhấp vào OK. Hướng dẫn chi tiết được đưa ra trong bước 5 của ví dụ "Cách thay đổi động màu của một ô dựa trên giá trị của nó." Một mẫu định dạng có điều kiện mà bạn thiết lập sẽ trông giống như sau:
- Nếu bạn hài lòng với màu sắc, hãy nhấp vào OK. Bạn sẽ thấy quy tắc đã tạo sẽ được áp dụng ngay lập tức cho bảng như thế nào.
Thay đổi màu tô của các ô đặc biệt một cách tĩnh
Sau khi được định cấu hình, phần điền sẽ không thay đổi, bất kể giá trị của ô là bao nhiêu.
Nếu bạn muốn đặt màu tô vĩnh viễn cho các ô trống hoặc ô có công thức có lỗi, hãy sử dụng phương pháp này:
- Chọn một bảng hoặc phạm vi và nhấp vào F5để mở hộp thoại Go To (Nhảy), sau đó nhấn nút Reduct ® (Điểm nổi bật).
- Trong hộp thoại Chuyển đến Đặc biệt (Chọn một nhóm ô) chọn tùy chọn khoảng trống (Ô trống) để chọn tất cả các ô trống.Nếu bạn muốn đánh dấu các ô chứa công thức có lỗi, hãy chọn tùy chọn Công thức (công thức)> lỗi (Sai lầm). Như bạn có thể thấy trong hình trên, có rất nhiều cài đặt khác có sẵn cho bạn.
- Cuối cùng, thay đổi màu tô của các ô đã chọn hoặc đặt bất kỳ tùy chọn định dạng nào khác bằng cách sử dụng hộp thoại Format Cells (Định dạng ô), như được mô tả trong Thay đổi màu tô của ô đã chọn.
Nếu bạn muốn đánh dấu các ô chứa công thức có lỗi, hãy chọn tùy chọn Công thức (công thức)> lỗi (Sai lầm). Như bạn có thể thấy trong hình trên, có rất nhiều cài đặt khác có sẵn cho bạn.Đừng quên rằng cài đặt định dạng được thực hiện theo cách này sẽ được giữ nguyên ngay cả khi các ô trống được lấp đầy bởi các giá trị hoặc các lỗi trong công thức được sửa chữa. Thật khó để tưởng tượng rằng ai đó có thể cần phải đi theo cách này, ngoại trừ mục đích của thử nghiệm 🙂