Hướng dẫn auto change color in excel - tự động đổi màu trong excel

Hello,

Nội dung chính Show

Cách thay đổi màu của ô dựa trên giá trị trong excel một cách linh hoạt
Cách thay đổi vĩnh viễn màu của ô dựa trên giá trị hiện tại của nó
Tìm và chọn tất cả các ô đáp ứng một điều kiện nhất định
Thay đổi màu nền của các ô được chọn bằng hộp thoại "Định dạng ô"
Thay đổi màu nền cho các ô đặc biệt (chỗ trống, với lỗi công thức)
Sử dụng công thức Excel để thay đổi màu nền của các ô đặc biệt
Thay đổi màu nền của các ô đặc biệt một cách thống trị
Làm thế nào để có được hầu hết các excel và làm cho các nhiệm vụ thử thách dễ dàng
Bạn cũng có thể quan tâm

Cột E không có ngày. Cột F không, và cột H. trong phần sau tôi giả sử bạn muốn sử dụng ngày trong cột F. Thay đổi công thức để sử dụng H nếu bạn muốn. & NBSP;

Chọn ô A2
Nhấp vào định dạng có điều kiện trên ruy băng nhàConditional Formatting on the Home ribbon
click & nbsp; quy tắc mớiNew Rule
click & nbsp; sử dụng một công thức để xác định định dạng ô nàoUse a formula to determine which cells to format
Nhấp vào hộp công thức và nhập công thức = $ f2
=$F2
Nhấp vào nút Định dạng và chọn màu đỏ
Đóng tất cả các hộp thoại

Lặp lại các bước và sử dụng công thức

=$F2=Today()

và chọn một đường viền màu vàng và đường viền màu đỏ.

Tiếp theo, chọn ô A2 một lần nữa, nhấp vào họa sĩ định dạng trong ruy băng nhà, sau đó chọn tất cả các ô khác bên dưới ô A2.

cái đó có giúp ích không?

Trong bài viết này, bạn sẽ tìm thấy hai cách nhanh chóng để thay đổi màu nền của các ô dựa trên giá trị trong Excel 2016, 2013 và 2010. Ngoài ra, bạn sẽ học cách sử dụng các công thức Excel để thay đổi màu của các ô hoặc ô trống có lỗi công thức .

Mọi người đều biết rằng việc thay đổi màu nền của một ô đơn hoặc một phạm vi dữ liệu trong Excel rất dễ dàng khi nhấp vào nút Fill Color. Nhưng nếu bạn muốn thay đổi màu nền của tất cả các ô với một giá trị nhất định thì sao? Hơn nữa, điều gì sẽ xảy ra nếu bạn muốn màu nền sẽ tự động thay đổi cùng với các thay đổi của giá trị ô? Hơn nữa trong bài viết này, bạn sẽ tìm thấy câu trả lời cho những câu hỏi này và tìm hiểu một vài mẹo hữu ích sẽ giúp bạn chọn phương pháp phù hợp cho từng nhiệm vụ cụ thể.

button . But what if you want to change the background color of all cells with a certain value? Moreover, what if you want the background color to change automatically along with the cell value's changes? Further in this article you will find answers to these questions and learn a couple of useful tips that will help you choose the right method for each particular task.

Thay đổi màu nền của các ô dựa trên giá trị (động) - màu nền sẽ tự động thay đổi khi giá trị ô thay đổi.
Thay đổi màu của một ô dựa trên giá trị hiện tại của nó (theo định nghĩa) - sau khi được đặt, màu nền sẽ không thay đổi cho dù giá trị của ô thay đổi như thế nào.
Thay đổi màu sắc của các ô đặc biệt - khoảng trống, với lỗi, với các công thức.

Cách thay đổi màu của ô dựa trên giá trị trong excel một cách linh hoạt

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

Nhiệm vụ: Bạn có một bảng hoặc phạm vi dữ liệu và bạn muốn thay đổi màu nền của các ô dựa trên các giá trị ô. Ngoài ra, bạn muốn màu thay đổi phản ánh động các thay đổi dữ liệu.: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.

Giải pháp: Bạn cần sử dụng định dạng có điều kiện Excel để làm nổi bật các giá trị lớn hơn X, nhỏ hơn Y hoặc giữa X và Y.: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and 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á lớn hơn 3,7 USD có màu đỏ và bằng hoặc nhỏ hơn 3,45 USD có màu xanh lá cây.

Lưu ý: Các ảnh chụp màn hình cho ví dụ này đã được chụp trong Excel 2010, tuy nhiên các nút, hộp thoại và cài đặt là giống nhau hoặc gần giống nhau trong Excel 2016 và Excel 2013. The screenshots for this example were captured in Excel 2010, however the buttons, dialogs and settings are the same or nearly the same in Excel 2016 and Excel 2013.

Được rồi, đây là những gì bạn làm từng bước:

Chọn bảng hoặc phạm vi nơi bạn muốn thay đổi màu nền của các ô. Trong ví dụ này, chúng tôi đã chọn $ 2 $ 2: $ H $ 10 (tên cột và cột đầu tiên liệt kê các tên trạng thái được loại trừ khỏi lựa chọn).
Điều hướng đến Tab Home, Nhóm Styles và chọn Định dạng có điều kiện> Quy tắc mới.
Trong hộp thoại định dạng mới, chọn "Chỉ định dạng các ô có chứa" trong "Chọn một loại quy tắc" ở phần trên của hộp thoại.Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
Ở phần dưới của hộp thoại dưới "Định dạng chỉ các ô có phần", đặt các điều kiện quy tắc. Chúng tôi chọn định dạng chỉ các ô có giá trị ô - lớn hơn - 3.7, như bạn có thể thấy trong ảnh chụp màn hình bên dưới. Sau đó nhấp vào nút định dạng, để chọn màu nền nào để áp dụng khi đáp ứng điều kiện trên.Cell Value - greater than - 3.7, as you can see in the screenshot below.
Then click the Format… button to choose what background color to apply when the above condition is met.
Trong hộp thoại Định dạng ô, chuyển sang tab điền và chọn màu bạn chọn, màu đỏ trong trường hợp của chúng tôi và nhấp vào OK.Fill tab and select the color of your choice, the reddish color in our case, and click OK.
Bây giờ bạn đã trở lại cửa sổ quy tắc định dạng mới và bản xem trước của các thay đổi định dạng của bạn được hiển thị trong hộp xem trước. Nếu mọi thứ đều ổn, hãy nhấp vào nút OK. Kết quả của định dạng của bạn sẽ trông tương tự như thế này:
The result of your formatting will look similar to this:
Vì chúng ta cần áp dụng thêm một điều kiện, tức là thay đổi nền của các ô có giá trị bằng hoặc nhỏ hơn 3,45 thành màu xanh lá cây, hãy nhấp vào nút Quy tắc mới và lặp lại các bước 3 - 6 Cài đặt điều kiện yêu cầu. Dưới đây là bản xem trước của quy tắc định dạng có điều kiện thứ hai của chúng tôi:New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule:
Khi bạn xong, nhấp vào nút OK. Những gì bạn có bây giờ là một bảng được định dạng độc đáo cho phép bạn thấy giá khí cao nhất và thấp nhất trên các tiểu bang khác nhau trong nháy mắt. May mắn là họ đang ở Texas :)
Mẹo: Bạn có thể sử dụng cùng một phương thức để thay đổi màu phông chữ dựa trên giá trị của ô. Để làm điều này, chỉ cần chuyển sang tab Phông chữ trong hộp thoại Định dạng ô mà chúng ta đã thảo luận trong Bước 5 và chọn màu phông chữ ưa thích của bạn. You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.

Cách thay đổi vĩnh viễn màu của ô dựa trên giá trị hiện tại của nó

Sau khi được đặt, màu nền sẽ không thay đổi cho dù nội dung của ô có thể thay đổi như thế nào trong tương lai.

Nhiệm vụ: Bạn muốn tô màu một ô dựa trên giá trị hiện tại của nó và mong muốn màu nền vẫn giữ nguyên ngay cả khi giá trị của ô thay đổi.: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value's changes.

Giải pháp: Tìm tất cả các ô có giá trị hoặc giá trị nhất định bằng cách sử dụng Excel's Tìm tất cả chức năng hoặc chọn bổ trợ các ô đặc biệt, sau đó thay đổi định dạng của các ô tìm thấy bằng tính năng của các ô định dạng.: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

Đây là một trong những nhiệm vụ hiếm hoi không được đề cập trong các tệp, diễn đàn và blog trợ giúp Excel và không có giải pháp đơn giản. Và điều này là dễ hiểu, bởi vì nhiệm vụ này không phải là điển hình. Tuy nhiên, nếu bạn cần thay đổi màu nền của các ô tĩnh, tức là một lần và mãi mãi trừ khi bạn thay đổi nó một cách thủ công, hãy tiến hành 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

Có thể có một số kịch bản có thể tùy thuộc vào loại giá trị bạn đang tìm kiếm.

Nếu bạn cần tô màu các ô với một giá trị cụ thể, ví dụ: 50, 100 hoặc 3.4, đi đến tab Home, nhóm chỉnh sửa và nhấp vào Tìm Chọn> Tìm.

Nhập các giá trị cần thiết và nhấp vào nút Tìm tất cả.Find All button.

Mẹo: Nhấp vào nút Tùy chọn ở phần bên phải của hộp thoại Tìm và thay thế để có được một số tùy chọn tìm kiếm nâng cao, chẳng hạn như "Trường hợp khớp" và "khớp toàn bộ nội dung ô". Bạn có thể sử dụng các ký tự ký tự đại diện, chẳng hạn như dấu hoa thị (*) để tìm bất kỳ chuỗi ký tự hoặc dấu câu hỏi nào (?) Để tìm bất kỳ ký tự nào. Click the Options button in the right-hand part of the Find and Replace dialog to get a number of advanced search options, such as "Match Case" and "Match entire cell content". You can use wildcard characters, such as an asterisk (*) to find any string of characters or a question mark (?) to find any single character.

Trong ví dụ trước của chúng tôi, nếu chúng tôi cần tìm tất cả giá khí từ 3,7 đến 3,799, chúng tôi sẽ chỉ định các tiêu chí tìm kiếm sau:

Bây giờ chọn bất kỳ mục nào được tìm thấy ở phần dưới của cửa sổ hộp thoại Tìm và thay thế bằng cách nhấp vào nó và sau đó nhấn Ctrl + A để chọn tất cả các mục được tìm thấy. Sau đó, nhấp vào nút Đóng.Ctrl + A to select all found entries. After that click the Close button.

Đây là cách bạn chọn tất cả các ô có (các) giá trị nhất định bằng cách sử dụng tất cả các hàm trong excel.

Tuy nhiên, những gì chúng ta thực sự cần là tìm thấy tất cả giá khí cao hơn 3,7 và hộp thoại tìm và thay thế đáng tiếc của Excel không cho phép những thứ như vậy.

May mắn thay, có một công cụ khác có thể xử lý các điều kiện phức tạp như vậy. Các ô bổ trợ chọn đặc biệt chọn cho phép bạn tìm thấy tất cả các giá trị trong một phạm vi được chỉ định, ví dụ: Trong khoảng từ -1 đến 45, nhận được giá trị tối đa / tối thiểu trong một cột, hàng hoặc phạm vi, tìm ô bằng màu phông chữ, lấp đầy màu sắc và nhiều hơn nữa.

Bạn nhấp vào nút Chọn By By Giá trị trên Ribbon và sau đó chỉ định tiêu chí tìm kiếm của bạn trên ngăn bổ trợ, trong ví dụ của chúng tôi, chúng tôi đang tìm kiếm các giá trị lớn hơn 3,7. Nhấp vào nút Chọn và trong một giây, bạn sẽ có kết quả như thế này:

Nếu bạn quan tâm để thử bổ trợ các ô đặc biệt chọn, bạn có thể tải xuống phiên bản đánh giá tại đây.Select Special Cells add-in, you can download an evaluation version here.

Thay đổi màu nền của các ô được chọn bằng hộp thoại "Định dạng ô"

Bây giờ tất cả các ô có giá trị hoặc giá trị được chỉ định đã được chọn (bằng cách sử dụng tìm và thay thế hoặc chọn bổ trợ ô đặc biệt) Những gì còn lại để bạn làm là buộc màu nền của các ô đã chọn thay đổi khi giá trị thay đổi.

Mở hộp thoại Định dạng ô bằng cách nhấn Ctrl + 1 (bạn cũng có thể nhấp chuột phải vào bất kỳ ô nào được chọn và chọn "Định dạng ô. thực hiện tất cả các thay đổi định dạng bạn muốn. Chúng tôi sẽ chọn thay đổi màu nền màu cam lần này, chỉ để thay đổi :)Format Cells dialog by pressing Ctrl + 1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :)

Nếu bạn chỉ muốn thay đổi màu nền mà không có bất kỳ thay đổi định dạng nào khác, thì bạn chỉ cần nhấp vào nút điền và chọn màu theo ý thích của bạn.

Đây là kết quả của các thay đổi định dạng của chúng tôi trong Excel:

Không giống như kỹ thuật trước đó với định dạng có điều kiện, màu nền được đặt theo cách này sẽ không bao giờ thay đổi nữa nếu không có thông báo của bạn, bất kể giá trị thay đổi như thế nào.

Thay đổi màu nền cho các ô đặc biệt (chỗ trống, với lỗi công thức)

Giống như trong ví dụ trước, bạn có thể thay đổi màu nền của các ô đặc biệt theo hai cách, tự động và tĩnh.

Sử dụng công thức Excel để thay đổi màu nền của các ô đặc biệt

Màu của ô sẽ tự động thay đổi dựa trên giá trị của ô.

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 tôi sẽ sử dụng bảng giá khí một lần nữa làm ví dụ, nhưng lần này, một vài trạng thái nữa được bao gồm và một số ô trống. Xem cách bạn có thể phát hiện các ô trống đó và thay đổi màu nền của chúng.

Trên tab Home, trong nhóm Styles, nhấp vào định dạng có điều kiện> Quy tắc mới (xem Bước 2 về cách tự động thay đổi màu ô dựa trên giá trị cho hướng dẫn từng bước).
Trong hộp thoại "Quy tắc định dạng mới", chọn tùy chọn "Sử dụng công thức để xác định chính ô nào để định dạng". Sau đó nhập một trong các công thức sau trong trường "Giá trị định dạng trong đó công thức này là đúng":
- = IsBlank ()- Để thay đổi màu nền của các ô trống.- to change the background color of blank cells.
- = IsError () - Để thay đổi màu nền của các ô với các công thức trả về lỗi. - to change the background color of cells with formulas that return errors.
Vì chúng tôi quan tâm đến việc thay đổi màu của các ô trống, hãy nhập công thức = isblank (), sau đó đặt con trỏ giữa các dấu ngoặc đơn và nhấp vào nút hộp thoại sụp đổ ở phần bên phải của cửa sổ để chọn một phạm vi ô hoặc bạn Có thể nhập phạm vi theo cách thủ công, ví dụ: =IsBlank(B2:H12).=IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button
in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12).
Nhấp vào nút Định dạng và chọn màu nền cần thiết trên tab Fill (để biết hướng dẫn chi tiết, xem Bước 5 của "Cách thay đổi màu di động dựa trên giá trị") và sau đó nhấp vào OK.Format… button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.
Bản xem trước của quy tắc định dạng có điều kiện của bạn sẽ trông tương tự như thế này:
Nếu bạn hài lòng với màu, nhấp vào nút OK và bạn sẽ thấy các thay đổi được áp dụng ngay lập tức cho bảng của bạn.

Thay đổi màu nền của các ô đặc biệt một cách thống trị

Sau khi thay đổi, màu nền sẽ vẫn giữ nguyên, bất kể thay đổi của các giá trị ô.

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

Chọn bảng của bạn hoặc một phạm vi và nhấn F5 để mở hộp thoại "Chuyển đến", sau đó nhấp vào nút "Đặc biệt".F5 to open the "Go To" dialog, and then click the "Special…" button.
Trong hộp thoại "Đi đến đặc biệt", hãy kiểm tra nút Radio Blanks để chọn tất cả các ô trống. Nếu bạn muốn làm nổi bật các ô chứa các công thức có lỗi, hãy chọn Công thức> Lỗi. Như bạn có thể thấy trong ảnh chụp màn hình ở trên, một số ít các tùy chọn khác có sẵn cho bạn.select all empty cells.
If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.
Và cuối cùng, thay đổi nền của các ô được chọn hoặc thực hiện bất kỳ tùy chỉnh định dạng nào khác bằng hộp thoại "Định dạng ô" như được mô tả trong việc thay đổi nền của các ô được chọn.Format Cells" dialog as described in Changing the background of selected cells.

Chỉ cần nhớ rằng việc định dạng các thay đổi được thực hiện theo cách này sẽ tồn tại ngay cả khi các ô trống của bạn chứa đầy dữ liệu hoặc lỗi công thức được sửa chữa. Tất nhiên, thật khó để tưởng tượng ra đỉnh đầu tại sao ai đó có thể muốn có nó theo cách này, có thể chỉ vì mục đích lịch sử :)

Làm thế nào để có được hầu hết các excel và làm cho các nhiệm vụ thử thách dễ dàng

Là một người dùng tích cực của Microsoft Excel, bạn biết rằng nó có nhiều tính năng. Một số trong số chúng mà chúng ta biết và yêu thích, những người khác là một bí ẩn hoàn toàn cho một người dùng trung bình và các blog khác nhau, bao gồm cả tài liệu này, đang cố gắng làm sáng tỏ ít nhất một số ánh sáng đối với họ. Nhưng mà! Có một vài nhiệm vụ rất phổ biến mà tất cả chúng ta phải thực hiện hàng ngày và Excel chỉ đơn giản là không cung cấp bất kỳ tính năng hoặc công cụ nào để tự động hóa chúng hoặc làm cho một inch dễ dàng hơn.

Ví dụ: nếu bạn cần kiểm tra 2 bảng tính cho các bản sao hoặc hợp nhất các hàng từ các bảng tính đơn hoặc khác nhau, sẽ mất một loạt các công thức hoặc macro arcane và vẫn không có gì đảm bảo bạn sẽ nhận được kết quả chính xác.

Đó là lý do tại sao một nhóm các nhà phát triển Excel tốt nhất của chúng tôi đã thiết kế và tạo ra hơn 70 tiện ích bổ sung mà chúng tôi gọi là bộ cuối cùng cho Excel. Những công cụ thông minh này xử lý các nhiệm vụ mệt mỏi nhất, khó khăn và dễ bị lỗi nhất trong Excel và đảm bảo kết quả nhanh chóng, gọn gàng và hoàn hảo. Dưới đây là danh sách ngắn chỉ là một số nhiệm vụ mà các tiện ích bổ sung có thể giúp bạn:

Xóa các bản sao và tìm các giá trị duy nhất
Hợp nhất bảng và kết hợp dữ liệu từ các nguồn khác nhau
Kết hợp các hàng trùng lặp thành một
Hợp nhất các ô, hàng và cột
Tìm và thay thế trong tất cả các dữ liệu, trong tất cả các sổ làm việc
Tạo số ngẫu nhiên, mật khẩu và danh sách tùy chỉnh
Và nhiều, nhiều hơn nữa.

Chỉ cần thử các tiện ích bổ sung này và bạn sẽ thấy rằng năng suất excel của bạn sẽ tăng lên tới 50%, ít nhất là!

Đó là tất cả cho bây giờ. Trong bài viết tiếp theo của tôi, chúng tôi sẽ tiếp tục khám phá chủ đề này hơn nữa và bạn sẽ thấy cách bạn có thể nhanh chóng thay đổi màu nền của một hàng dựa trên giá trị ô. Hy vọng sẽ gặp lại bạn trên blog của chúng tôi vào tuần tới!