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

Trong bài viết bạn sẽ học được cách nhanh chóng thay đổi màu nền của toàn bộ dòng dựa vào giá trị của một ô tính trong bảng tính Excel. Các mẹo và công thức đối với giá trị số hoặc văn bản.

Bài trước chúng ta đã thảo luận về cách thay đổi màu nền của ô tính dựa vào giá trị. Trong bài viết này bạn sẽ học được cách đổ màu cả dòng trong Excle 2016, 2013 và 2010 dựa vào giá trị một ô tính, và cũng tìm hiểu một số mẹo và công thức để làm việc với ô tính chứa số và văn bản

• Thay đổi màu nền của dòng dựa vào chữ số trong một ô tính
• Áp dụng một vài quy tắc định dạng với mức độ ưu tiên được chỉ định
• Thay đổi màu nền dòng dựa vào văn bản trong ô tính
• Thay đổi màu nền ô tính dựa vào giá trị của ô tính khác
• Thay đổi màu nền của dòng dựa vào nhiều điều kiện

Cách thay đổi màu nền của dòng dựa vào chữ số trong một ô tính

Giả sử, bạn có một bảng đơn hàng của công ty như sau:

Bạn muốn đổ màu khác nhau cho các dòng dựa vào giá trị trong cột Qty. để nhanh chóng thấy được những đơn hàng quan trọng. Điều này có thể được thực hiện dễ dàng bằng tính năng Conditional Formatting trong Excel.

1. Bắt đầu bằng việc chọn những ô mà bạn muốn thay đổi màu nền.
2. Tạo quy tắc định dạng mới bằng cách chọn Conditional Formatting > New Rule… trên tab Home. 

   Thay vì C2, bạn có thể nhập một ô tính chứa giá trị mà bạn muốn kiểm tra và nhập một số bạn cần thay cho 4. Và tất nhiên, bạn có thể dùng dấu nhỏ hơn (<)  hoặc bằng (=) để công thức của bạn trông như =$C2<4 and =$C2=4.

   Còn nữa, chú ý dấu $ trước địa chỉ ô tính, bạn cần dùng nó để giữ nguyên kí tự cột khi công thức được sao chép qua các dòng khác. Thực ra, nó là thủ thuật để áp dụng định dạng cho cả dòng dựa vào giá trị ô tính đã chọn.

3. Chọn nút “Format…” và chọn tab Fill để chọn màu nền. Nếu màu mặc đình là chưa đủ, chọn nút “More Colors…” để chọn một màu và nhấn OK hai lần. 

   Bạn cũng có thể sử dụng những tùy chọn định dạng khác, như màu chữ hoặc đường viền trong những tab khác của hộp thoại Format Cells.

4. Bản xem trước quy tắc định dạng của bạn sẽ như bên dưới:
5. Nếu đó là thứ bạn muốn và bạn thích màu này, chọn OK để áp dụng định dạng mới của bạn.Bây giờ, nếu giá trị trong cột Qty. lớn hơn 4, thì toàn bộ dòng đó sẽ có màu xanh dương.

Như bạn thấy, việc thay đổi màu của dòng dựa vào chữ số trong một ô tính rất dễ dàng trong Excel. Sau này, bạn sẽ thấy nhiều công thức và mẹo cho những trường hợp phức tạp hơn.

Các áp dụng một số quy tắc theo mức độ ưu tiên

Trong ví dụ trước, bạn có thể muốn làm nổi bật các dòng có giá trị khác nhau trong cột Qty. bằng các màu khác nhau. Ví dụ, bạn có thể thêm một quy tắc để đổ màu những dòng có số lượng 10 hoặc lớn hơn, giả sử, màu hồng. Trong trường hợp này, dùng công thức =$C2>9, và sau khi quy tắc định dạng thứ 2 được tạo, cài đặt mức độ ưu tiên cho các quy tắc để tất cả các quy tắc sẽ làm việc.

1. Tại tab Home, trong group Styles, chọn Conditional Formatting > Manage Rules… .
2. Chọn “This worksheet” trong mục “Show formatting rules for“. Nếu bạn muốn quản  lý chỉ các quy tắc được áp dụng cho vùng chọn của hiện tại của bạn, chọn “Current Selection“.
3. Chọn quy tắc định dạng bạn muốn áp dụng trước và di chuyển nó lên đầu danh sách sử dụng các phím mũi tên. Kết quả sẽ như sau: 

   Chọn nút OK và các dòng tương ứng sẽ thay đổi màu nền ngay lập tức dựa vào giá trị các ô tính bạn đã chỉ ra trong các công thức.

   

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

Trong bảng tính mẫu của chúng ta, để giúp việc theo dõi đơn hàng dễ dàng hơn, bạn có thể đổ màu các dòng dựa vào giá trị cột Delivery, như sau:

• Nếu một đơn hàng là “Due in X Days”, màu nền của dòng đó sẽ bôi cam;
• Nếu một đơn hàng là “Delivered”, toàn bộ dòng sẽ bôi màu xanh lá;
• Nếu một đơn hàng là “Past Due”, dòng sẽ được bôi màu đỏ.

Thông thường, màu nền của dòng đó sẽ thay đổi khi đơn hàng được cập nhật.

Trong khi công thức ở ví dụ đầu tiên có thể đáp ứng trường hợp “Delivered” and “Past Due”(=$E2="Delivered" and =$E2="Past Due"), nhưng nó sẽ gặp vấn đề khi đơn hàng là “Due in …”. Như bạn thấy, những đơn hàng khác nhau sẽ đến hạn trong 1, 3, 5 hoặc nhiều ngày hơn và công thức ở ví dụ trước sẽ không làm việc bởi vì mục đích của nó là đáp ứng chính xác một giá trị.

Trong trường hợp này, cách tốt nhất là sử dụng công thức =SEARCH như sau =SEARCH("Due in", $E2)>0. Trong công thức, E2 là địa chỉ của ô tính mà bạn muốn dùng làm điều kiện để định dạng, dấu $ được sử dụng để áp dụng công thức cho cả dòng đó, và >0 nghĩa là định dạng sẽ được áp dụng nếu văn bản đưa ra (trong trường hợp này là “Due in“) được tìm thấy. 

Tạo 3 quy tắc theo các bước tại ví dụ đầu tiên, và bạn có được kết quả như bảng tính dưới đây:

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

Thực tế, đây là một biến thể của trường hợp thay đổi màu nền của một dòng. Nhưng thay vì chọn cả bảng, bạn chọn một cột hoặc một vùng nơi bạn muốn thay đổi màu nền ô tính và dùng công thức được mô tả bên trên.

Ví dụ, chúng ta sẽ tạo 3 quy tắc để tô màu chỉ những ô tính trong cột “Order number” dựa vào giá trị của một ô tính khác (trường hợp này là giá trị tại cột Delivery).

Các thay đổi màu nền của dòng dựa vào nhiều điều kiện

Nếu bạn muốn tô màu các dòng dựa vào nhiều giá trị, thì thay vì tạo nhiều quy tắc định dạng bạn có thể sử dụng công thức =OR hoặc =AND để đặt nhiều điều kiện.

For example, we can color the orders due in 1 and 3 days in the reddish color, and those that are due in 5 and 7 days in the yellow color. The formulas are as follow Ví dụ, chúng ta có thể đổ màu những đơn hàng đến hạn trong 1 và 3 ngày thành màu đỏ nhạt, và những đơn đến hạn trong 5 và 7 ngày thành màu vàng:

• =OR($F2="Due in 1 Days", $F2="Due in 3 Days")
• =OR($F2="Due in 5 Days", $F2="Due in 7 Days")

Và bạn có thể sử dụng công thức =AND, giả sử để thay đổi màu nền của các dòng có Qty. bằng hoặc lớn hơn 5 và bằng hoặc nhỏ hơn 10: =AND($D2>=5, $D2<=10)

Thông thường, bạn không bị giới hạn chỉ dùng 2 điều kiện trong công thức, bạn có thể dùng bao nhiêu điều kiện mà bạn muốn, ví dụ =OR($F2="Due in 1 Days", $F2="Due in 3 Days", $F2="Due in 5 Days") và nhiều hơn nữa.