Excel

Hai cách thay đổi màu nền ô tính trong Excel dựa trên giá trị ô tính

Trong bài này, bạn sẽ tìm thấy 2 cách để thay đổi màu nên ô tính dựa trên giá trị ô tính trong Excel 2016, 2013, và 2010. Và bạn cũng sẽ học được cách sử dụng công thức Excel để thay đổi màu của ô tính trống hoặc những ô tính bị lỗi công thức.

Mọi người biết cách thay đổi màu nền của một ô tính hoặc một vùng dữ liệu trong Excel một cách đơn giản bằng việc chọn nút “Fill colorFill color icon . Nhưng làm thế nào nếu bạn muốn thay đổi màu nền của tất cả những ô tính với một giá trị cụ thể? Trong bài viết này bạn sẽ tìm thấy câu trả lời cho câu hỏi này và học được một số mẹo hữu ích giúp bạn chọn đúng phương pháp cho các trường hợp cụ thể.

Cách thay đổi màu nền ô tính dựa vào giá trị ô tính trong Excel

Màu nền sẽ thay đổi phụ thuộc vào giá trị của ô tính.

Nhiệm vụ: Bạn có một bảng hoặc vùng dữ liệu, và bạn muốn thay đổi màu nền của các ô tính dựa vào giá trị của ô tính. Bạn cũng muốn màu nền thay đổi linh hoạt phản ánh sự thay đổi của dữ liệu.

Giải pháp: Bạn cần sử dụng tính năng “Conditional formatting” của Excel để làm nổi bật các giá trị như lớn hơn X, nhỏ hơn Y hoặc trong khoảng X và Y.

Giả sử bạn có bảng giá gas tại các vùng khác nhau và bạn muốn những ô giá lớn hơn 3.7 USD sẽ có màu đỏ và bằng hoặc nhỏ hơn 3.45 USD sẽ có màu xanh dương.

A table listing gasoline prices in different states

Lưu ý: Hình ảnh cho ví dụ này là của Excel 2010 tuy nhiên mọi hộp thoại và cài đặt cũng tương tự trong Excel 2016 và 2013.

Và đây là các bước thực hiện:

    1. Chọn bảng hoặc vùng dữ liệu mà bạn muốn thay đổi màu nền của ô tính. Trong ví dụ này, chúng ta đã chọn $B$2:$H$10.
    2. Chọn tab Home, nhóm Styles, và chọn Conditional Formatting > New Rule….

      On the Home tab, in the Styles group, click Conditional Formatting - New Rule….

    3. Trong hộp thoại New Formatting Rule, chọn “Format only cells that contain” trong khung “Select a Rule Type” tại phần trên của hộp thoại.
    4. Tại phần dưới của hộp thoại  “Format Only Cells with section“, chọn điều kiện. Chúng ta chọn để định dạng cho những ô tính có giá trị lớn hơn 3.7, ta chọn Cell Value – greater than – 3.7, như bạn nhìn thấy tại hình bên dưới.

      Select 'Format only cells that contain' and set the rule conditions.

      Rồi chọn nút Format… để chọn màu nền cho ô tính khi thỏa mãn điều kiện bên trên.

5. Trong hộp thoại Format Cells, chọn tab Fill và chọn màu bạn muốn, trường hợp của chúng ta là màu đỏ nhạt, và chọn OK.In the Format Cells dialog box, on the Fill tab, select the background color of your choice.

 

6. Bây giờ bạn sẽ quay lại cửa sổ New Formatting Rule và xem trước sự thay đổi tại khung Preview. Nếu mọi thứ ổn, nhấn nút OK.

The preview of format changes is displayed in the Preview box.

Kết quả định dạng của bạn sẽ nhìn như hình bên dưới:

The background color of selected cells is changed based on cell values.

Tiếp theo chúng ta muốn áp dụng thêm một điều kiện, ví dụ thay đổi màu nền của ô tính với giá trị nhỏ hơn hoặc bằng 3.45 sang màu xanh dương, chọn lại New Rule  và lặp lại từ bước số 3 – 6 để chọn điều kiện. Và kết quả sẽ như hình bên dưới:

A rule to change the background of cells with values equal to or less than 3.45 to the green color

Khi bạn hoàn thành, chọn OK. Cái bạn có bây giờ là một bảng tính được định dạng rất đẹp và bạn có thể thấy dễ dàng thấy giá trị lớn nhất và nhỏ nhất tại các vùng khác nhau. Texas là nơi tuyệt vời nhất 🙂

The background color is changed based on 2 conditional formatting rules.

Mẹo: Bạn có thể dùng cách này để thay đổi màu chữ dựa vào giá trị ô tính. Để làm việc này bạn chọn tab Font trong hộp thoại Format Cells mà chúng ta đã nói đến tại bước 5 và chọn màu chữ bạn muốn.

The font color is changed based on 2 conditional formatting rules.

Các thay đổi vĩnh viễn màu nền ô tính dựa vào giá trị

Khi được cài đặt, màu nền sẽ không thay đổi khi giá trị ô tính có thể thay đổi trong tương lai.

Nhiệm vụ:Bạn muốn màu của ô tính dựa vào giá trị hiện tại của nó và muốn màu sẽ giữ nguyên khi giá trị ô tính bị thay đổi.

Giải pháp: Tìm tất cả những ô tính có giá trị cụ thể sử dụng chức năng Find All của Excel hoặc add-in Select Special Cells và sau đó thay đổi định dạng của những ô tìm được bằng tính năng Format Cells.

Đây là một trong số những tính năng hiếm hoi không có trong phần trợ giúp của Excel, các diễn đàn và blog không có giải pháp cho điều này. Cũng dễ hiểu thôi, vì tính năng này không phổ biến. Và nếu bạn vẫn muốn thay đổi màu nền của ô tính một cách cố định một lần và mãi mãi, bạn hãy làm theo các bước sau đây:

Tìm và chọn tất cả các ô tính thỏa mãn điều kiện cụ thể

Có thể có những trường hợp phụ thuộc vào giá trị bạn đang tìm kiếm.

Nếu bạn muốn tìm những ô có giá trị cụ thể, ví dụ: 50, 100 hoặc 3.4, chọn tab Home, group Editing, và chọn Find Select > Find….

Enter the needed values and click the Find All button.

Mẹo: Chọn nút Options phía bên tay phải của hộp thoại Find and Replace để sử dụng những tính năng nâng cao, chẳng hạn “Match Case” và “Match entire cell content“. Bạn có thể sử dụng những kí tự đại diện, chẳng hạn dấu sao (*) để tìm một chuỗi các kí tự hoặc dấu hỏi (?) để tìm một kí tự bất kì.

Trong ví dụ trước, nếu chúng ta cần tìm tất cả giá gas giữa 3.7 và 3.799, chúng ta sẽ dùng điều kiện tìm kiếm sau đây:

Use the wildcard character to find all gas prices between 3.7 and 3.799

Bây giờ chọn bất kì một mục đã tìm thấy tại phần dưới của hộp thoại Find and Replace sau đó nhấn Ctrl + A để chọn toàn bộ. Sau đó chọn nút Close.

Select all found items and click the Close button.

Đây là cách để chọn tất cả các ô tính có giá trị cụ thể bằng cách dùng chức năng  Find All trong Excel.

Tuy nhiên, cái chúng ta thực sự cần là tìm tất cả giá gas lớn hơn 3.7 và không may là tính năng Find and Replace không cho phép làm những điều như vậy.

May mắn thay, có một công cụ khác có thể thực hiện những điều kiện phức tạp. Add-in Select Special Cells giúp bạn tìm tất cả các giá trị trong một khoảng xác định, ví dụ: giữa -1 và 45, lấy giá tị lớn nhất/ nhỏ nhất trong một cột, hàng hoặc vùng, tìm những ô tính theo màu chữ, màu nền và nhiều tính năng khác.

 

Bạn chọn nút Select by Value trên thanh ribbon và chỉ ra điều kiện tìm kiến trong khung add-in’s, trong ví dụ này, chúng ta đang tìm giá trị lớn hơn 3.7. Chọn nút Select và trong giây lát bạn sẽ có kết quả như dưới đây:

Select all values within a particular range using Select Special Cells add-in.

Nếu bạn muốn thử add-in Select Special Cells, bạn có thể tải bản dùng thử tại đây.

Thay đổi màu nền của ô tính được chọn sử dụng hộp thoại “Format Cells”

Hiện tại, tất cả các ô tính với giá trị cụ thể đã được chọn (sử dụng Find and Replace hoặc add-in Select Special Cells) việc còn lại của bạn là chọn màu nền của những ô đã chọn để thay đổi.

Mở hộp thoại Format Cells bằng cách nhấn Ctrl + 1 (bạn cũng có thể nhấn chuột phải vào một ô bất kì trong các ô đã chọn sau đó chọn “Format Cells…” từ menu sổ xuống, hoặc chọn Home tab > Cells group > Format > Format Cells…) và thay đổi định dạng mà bạn muốn. Chúng ta sẽ thay đổi màu nền sang màu vàng tại thời điểm này.

Change the background color of selected cells using the Format Cells dialog.

Nếu bạn chỉ muốn thay đổi màu nền và không thay đổi các định dạng khác, thì bạn chỉ cần chọn nút Fill color và chọn màu bạn muốn.

Change the background color of selected cells by clicking the Fill color button.

Dưới đây là kết quả sau khi đã thay đổi:

The backgrounds color of selected cells is changed permanently, regardless of the cell value's changes.

Không giống như các làm sử dụng “Conditional Formatting“, màu nền được đặt theo cách này sẽ không bao giờ thay đổi ngay cả khi giá trị ô tính bị thay đổi.

Thay đổi màu nền cho những ô tính đặc biệt (trống, hoặc lỗi công thức)

Như ví dụ trước, bạn có thể thay đổi màu nền của những ô tính đặc biệt theo 2 cách: động hoặc tĩnh.

Dùng công thức Excel để thay đổi màu nền của ô tính đặc biệt

Màu nền ô tính sẽ thay đổi tự động dựa vào giá trị ô tính.

Phương pháp này cung cấp một giải pháp mà rất có thể bạn sẽ cần trong 99% trường hợp, tức là màu nền của các ô sẽ thay đổi theo các điều kiện bạn đặt.

Chúng ta sẽ sử dụng lại bảng giá gas, nhưng lần này sẽ có thêm một vài vùng miền và một số ô tính bị trống. Hãy xem cách bạn có thể phát hiện những ô tính trống và thay đổi màu nền của chúng.

  1. Tại tab Home, trong group Styles, chọn Conditional Formatting > New Rule… (Xem tại bước 2 ở phần Các thay đổi màu nền của ô tính dựa vào giá trị một cách linh hoạt).
  2. Tại hộp thoại “New Formatting Rule“, chọn tùy chọn “Use a formula to determine which cells to format“. Sau đó nhập một công thức dưới đây vào ô “Format values where this formula is true“:
    • =IsBlank()– để thay đổi màu nền của những ô tính trống.
    • =IsError() – để thay đổi màu nền của những ô tính chứa công thức trả về lỗi.

    Chúng ta đang muốn thay đổi màu nền cho các ô tính trống, nhập công thức =IsBlank(), rồi đặt trỏ chuột vào giữa dấu đóng mở ngoặc đơn và chọn nút Collapse Dialog Collapse Dialog icon ở phía bên tay phải của cửa sổ để chọn một vùng ô tính, hoặc bạn có thể gõ thủ công vùng ô tính, ví dụ: =IsBlank(B2:H12).

    Enter the formula and select a range of cells.

  3. Chọn nút Format… và chọn màu cần thay đổi ở tab Fill (chi tiết xem tại bước 5 của phần “Thay đổi màu nền ô tính dựa vào giá trị một cách linh hoạt”) và chọn OK. Bản xem trước của quy tắc định dạng có điều kiện sẽ như bên dưới:

    A rule to change the background color of blank cells using a formula

  4. Nếu bạn thấy màu đã phù hợp, chọn nút OK và bạn sẽ thấy sự thay đổi ngay lập tức tại bảng tính.

    Click the OK button and you'll see the changes immediately applied to your table.

Thay đổi màu nền của những ô tính đặc biệt theo cách tĩnh.

Khi đã thay đổi, màu nền sẽ được giữ nguyên cho dù giá trị của ô tính được thay đổi.

Nếu bạn muốn thay đổi vĩnh viễn màu của các ô tính trống hoặc ô tính bị  lỗi công thức, hãy làm theo các bước sau đây.

  1. Chọn bảng tính hoặc vùng dữ liệu rồi nhấn F5 để mở hộp thoại “Go To“, và chọn nút “Special…“.

    Open the

  2. Tại hộp thoại “Go to Special“, chọn tùy chọn Blanks để chọn tất cả các ô tính trống.

    Check the Blanks radio button to select all empty cells.

    Nếu bạn muốn chọn các ô tính chứa công thức bị lỗi, chọn Formulas > Errors. Như bạn có thể thấy tại hình bên trên, sẽ có một số tùy chọn khác cho bạn.

  3. Và cuối cùng, change the background of selected cells, or make any other format customizations using the thay đổi màu nền của các ô tính được chọn, hoặc tùy chỉnh các định dạng khác sử dụng hộp thoại “Format Cells” như đã miêu tả tại phần Thay đổi màu nền của các ô tính đã chọn.

Hãy nhớ rằng, định dạng được thay đổi bằng cách này sẽ được giữ nguyên ngay cả khi những ô tính trống của bạn được điền dữ liệu hoặc những công thức bị lỗi đã được sửa đúng. Tất nhiên, không thể hiểu được tại sao một ai đó lại muốn có tính năng này, có thể cho mục đích lưu lại lịch sử 🙂

 

Close
Close