Trong bài viết này, bạn sẽ tìm thấy cách thay đổi màu nền của một hàng dựa trên giá trị trong Google Sheets, Excel và Libre Office Calc. Ngoài ra, bạn sẽ học cách sử dụng các công thức Excel để lấy giá trị trên một ô từ một hàng đã cho và sử dụng cùng một công thức trong định dạng có điều kiện.change the background color of a row based on a value in Google Sheets, Excel and Libre Office Calc. Also, you will learn how to use Excel formulas to get the value on a cell from a given row and use the same formula in Conditional Formatting.
Bạn có thể tìm thấy ví dụ hoàn chỉnh - tất cả các bước và dữ liệu trong các Google Sheet này: Màu hàng thay thế dựa trên giá trị ô
Alternate Row Color based on cell value
Chúng tôi có một bảng hoặc phạm vi dữ liệu và chúng tôi muốn thay đổi màu nền của tất cả các ô trong hàng dựa trên một trong các ô từ hàng này. Ngoài ra, bạn muốn màu thay đổi phản ánh động các thay đổi dữ liệu. Bạn có thể tìm thấy kết quả dưới đây:
Nguồn dữ liệu - Danh sách các quốc gia của lục địa
Bước 1: Sắp xếp dữ liệu trong trang Google
Đầu tiên chúng ta cần sắp xếp dữ liệu dựa trên cột sẽ được sử dụng làm cơ sở cho các giá trị màu của chúng ta - lục địa_code. Điều này có thể được thực hiện bởi:sort the data based on the column which is going to be used as a base for our color values - Continent_Code. This can be done by:
- Chọn phạm vi dữ liệu -
A1:D26
- Dữ liệu
- Sắp xếp phạm vi
- Dữ liệu có hàng tiêu đề
- Sắp xếp theo - lục địa_code - Continent_Code
- Loại
Bây giờ dữ liệu nên được sắp xếp và sẵn sàng cho bước tiếp theo của chúng tôi.
Bước 2: Các hàng nhóm dựa trên giá trị trong một ô
Kỹ thuật này hoàn toàn giống nhau cho cả Excel và Google Sheets. Tiếp theo, chúng tôi sẽ đến các hàng nhóm dựa trên Cột lục địa_code và thêm hai cột trợ giúp. Chúng tôi sẽ sử dụng cột E và kiểm tra có thay đổi cho các giá trị trong Cột lục địa_code - trả về True
và False
.group rows based on column Continent_Code and adding two helper columns. We are going to use column E and check is there a change for the values in Column Continent_Code - returning True
and False
.
Vì vậy, bạn cần sử dụng công thức trong ô E2 và mở rộng nó cho đến khi kết thúc bảng [kết quả là đúng và sai]:
=B1=B2
Tiếp theo là một cột số sẽ bắt đầu với 0 và tăng giá trị lên 1 cho mỗi nhóm với công thức này:
=IF[E2=TRUE,F1,F1+1]
Bây giờ chúng tôi đã có các nhóm sẵn sàng để được tô màu dựa trên cột F.
Bước 3: Định dạng có điều kiện về giá trị ô
Bước cuối cùng là thêm định dạng có điều kiện bằng cách sử dụng công thức sẽ tô màu các nhóm khác nhau trong hai màu:
- số chẵn màu xanh lam
- Những con số lẻ màu xanh lá cây
Điều này có thể được thực hiện bởi:
- Chọn bảng -
A1:D26
- Định dạng
- Định dạng có điều kiện
- Từ các quy tắc định dạngFormat Rules
- Chọn Công thức tùy chỉnh làCustom Formula is
- Nhập công thức:
=NOT[MOD[INDIRECT[ADDRESS[ROW[];6]];2]]
- Thay đổi định dạng nếu cần
Đối với màu xanh lặp lại các bước trước nhưng lần này sử dụng công thức tiếp theo:
=MOD[INDIRECT[ADDRESS[ROW[];6]];2]
và thay đổi định dạng thành màu xanh.
Mẹo: Nếu bạn muốn thêm quy tắc tương tự như hiện tại - thì bạn có thể chọn quy tắc hiện có và sau đó nhấn thêm một quy tắc khác.Add another rule.
Trong bài đăng này, bạn sẽ học cách thay thế màu sắc trong Google Sheets và thêm dải hàng hoặc cột vào bảng dữ liệu của bạn.
Ở đây, một ví dụ về các dải hàng xen kẽ được áp dụng cho một danh sách kiểm tra trong Google Sheets:
Nó rất dễ dàng để thêm các màu hàng xen kẽ trong Google Sheets.
Bước 1:
Làm nổi bật bảng dữ liệu của bạn.
Bước 2:
Chuyển đến Định dạng> Màu sắc xen kẽ
Bước 3:
Chọn một trong các kiểu mặc định và nhấp vào Xong:
Màu sắc và phong cách tùy chỉnh
Có hai hộp kiểm trong thanh bên màu xen kẽ, cho phép bạn bật hoặc tắt các tiêu đề và tắt. Nếu bạn kiểm tra chúng, bạn có thể đặt một màu khác cho các tiêu đề và chân trang.
Bạn cũng có thể đặt màu tùy chỉnh theo các kiểu mặc định và thậm chí lưu bảng màu của bạn như một kiểu tùy chỉnh với nút cộng lớn.
Các hàng xen kẽ động
Làm thế nào để bạn áp dụng các màu sắc thay thế trong bảng Google nếu bảng của bạn động và thay đổi kích thước?
Thật không may, bạn có thể sử dụng công cụ hàng xen kẽ vì nó được áp dụng cho phạm vi tĩnh. Các dải màu xen kẽ được để tại chỗ cho dù có dữ liệu hay không.
Nhưng tin tốt là bạn có thể sử dụng định dạng có điều kiện thường xuyên để đạt được màu sắc xen kẽ động.
Làm nổi bật phạm vi dữ liệu của bạn, mở thanh bên định dạng có điều kiện và đặt quy tắc công thức tùy chỉnh thành:
=AND[NOT[ISBLANK[$B16]],ISODD[ROW[]]]
Lưu ý rằng dấu $ ở phía trước của A, để định dạng có điều kiện được áp dụng cho toàn bộ hàng.
Ngoài ra, quy tắc này đang hoạt động trên một phạm vi bắt đầu từ ô B16 [xem GIF ở trên]. Bạn sẽ muốn thay đổi điều này để phù hợp với ô trên cùng bên trái của phạm vi của bạn.
Công thức này hoạt động bằng cách kiểm tra xem hàng là một hàng được đánh số lẻ và không trống trong cột B, tức là hàng có dữ liệu.
Nếu hàng trống trong cột B, thì định dạng có điều kiện không được áp dụng.
Kỹ thuật này có thể được điều chỉnh theo các cột bằng cách chuyển chức năng hàng sang hàm cột.
Cách thay thế màu sắc trong cột Google Sheets
Thật không may, không có tùy chọn màu sắc xen kẽ tương đương hoạt động trên các cột. Tuy nhiên, vì nó ít phổ biến hơn để làm điều này, có lẽ đó không phải là mất mát lớn.
Nhưng, trong trường hợp bạn muốn tô màu các cột với màu sắc xen kẽ, ở đây, cách thức:
Bước 1:
Làm nổi bật bảng dữ liệu của bạn.
Bước 2:
Chuyển đến Định dạng> Màu sắc xen kẽ
Bước 3:
Chọn một trong các kiểu mặc định và nhấp vào Xong:
Màu sắc và phong cách tùy chỉnh
Có hai hộp kiểm trong thanh bên màu xen kẽ, cho phép bạn bật hoặc tắt các tiêu đề và tắt. Nếu bạn kiểm tra chúng, bạn có thể đặt một màu khác cho các tiêu đề và chân trang.
=ISODD[COLUMN[]]
Bạn cũng có thể đặt màu tùy chỉnh theo các kiểu mặc định và thậm chí lưu bảng màu của bạn như một kiểu tùy chỉnh với nút cộng lớn.
=IF[E2=TRUE,F1,F1+1]
0Các hàng xen kẽ động
Làm thế nào để bạn áp dụng các màu sắc thay thế trong bảng Google nếu bảng của bạn động và thay đổi kích thước?
Thật không may, bạn có thể sử dụng công cụ hàng xen kẽ vì nó được áp dụng cho phạm vi tĩnh. Các dải màu xen kẽ được để tại chỗ cho dù có dữ liệu hay không.
Nhưng tin tốt là bạn có thể sử dụng định dạng có điều kiện thường xuyên để đạt được màu sắc xen kẽ động.