Định dạng có điều kiện Google Sheet: tô màu cả dòng, ngày, công thức

Muốn tô màu cả dòng theo giá trị một ô, bạn chọn vùng cả dòng (ví dụ A2:H1000), vào Định dạng > Định dạng có điều kiện, chọn Công thức tuỳ chỉnh là và nhập công thức khoá cột nhưng không khoá dòng, kiểu =$H2="Quá hạn". Đó là toàn bộ bí quyết của định dạng có điều kiện google sheet: dấu $ đặt trước chữ cái cột để mọi ô trong dòng đều nhìn về đúng cột trạng thái, còn số dòng để trống để quy tắc chạy xuống từng dòng. Trong bài này tôi đi từ các quy tắc có sẵn, sang công thức tuỳ chỉnh, cảnh báo theo ngày, rồi đến những lỗi làm quy tắc "đúng mà không tô".

Định dạng có điều kiện trong Google Sheet là gì?

Định dạng có điều kiện là tính năng tự đổi màu nền, màu chữ hoặc kiểu chữ của ô khi ô đó thoả một quy tắc bạn đặt ra. Dữ liệu không bị sửa, chỉ phần hiển thị thay đổi — nghĩa là bạn vẫn lọc, vẫn tính tổng bình thường.

Cách mở: bôi đen vùng cần áp dụng, vào menu Định dạng > Định dạng có điều kiện, bảng điều khiển sẽ hiện ở bên phải. Ba thứ cần để ý trong bảng này:

• Áp dụng cho dải ô: vùng mà quy tắc có hiệu lực. Nhập tay được, ví dụ A2:H1000 hoặc F2:F.
• Quy tắc định dạng: điều kiện (ô trống, lớn hơn, chứa chữ, công thức tuỳ chỉnh…) và màu áp dụng khi điều kiện đúng.
• Thứ tự quy tắc: một vùng có thể có nhiều quy tắc. Quy tắc nằm trên được ưu tiên — nếu một ô thoả cả hai, màu của quy tắc trên sẽ đè lên. Kéo thả để đổi thứ tự.

Tài liệu chính thức của Google về tính năng này nằm ở trang trợ giúp Google Docs Editors, bạn có thể xem thêm nếu giao diện trên máy bạn khác đôi chút.

Các quy tắc có sẵn: lớn hơn, chứa chữ, trùng, thang màu

Với phần lớn nhu cầu hằng ngày, bạn chưa cần viết công thức — danh sách quy tắc có sẵn đã đủ. Trong bảng điều khiển có hai thẻ: Màu đơn (một màu khi thoả điều kiện) và Thang màu (màu đậm nhạt theo độ lớn của số).

Vài ví dụ tôi hay gặp ở các file quản lý của doanh nghiệp nhỏ:

• Tồn kho dưới 10 tô đỏ: chọn cột tồn F2:F, điều kiện Nhỏ hơn, giá trị 10, chọn nền đỏ nhạt. Thủ kho mở file là thấy ngay hàng nào sắp hết.
• Cột TrangThai chứa "Huỷ" tô xám: chọn H2:H, điều kiện Văn bản chứa, nhập Huỷ. Lưu ý điều kiện này không phân biệt hoa thường nhưng có phân biệt dấu tiếng Việt, nên "Huy" và "Huỷ" là hai chuỗi khác nhau.
• Thang màu cho doanh số theo nhân viên: chọn cột doanh số, sang thẻ Thang màu, để mặc định min – max. Người xem nhìn ra ai cao ai thấp mà không cần sắp xếp lại bảng.
• Ô trống: điều kiện Ô trống tô vàng cho các cột bắt buộc nhập, dùng như một kiểu nhắc nhập liệu.

Hạn chế của nhóm quy tắc có sẵn: chúng chỉ nhìn vào chính ô đang xét. Muốn ô ở cột A đổi màu vì cột H ghi "Quá hạn", bắt buộc phải dùng công thức tuỳ chỉnh.

Công thức định dạng có điều kiện trong Google Sheet

Công thức định dạng có điều kiện trong Google Sheet được viết cho ô trên cùng bên trái của vùng áp dụng, sau đó Google tự suy ra cho các ô còn lại giống hệt cách bạn kéo công thức xuống. Hiểu được câu này là hiểu 80% phần khó.

Các bước:

1. Bôi đen vùng cần tô, ví dụ F2:F500.
2. Vào Định dạng > Định dạng có điều kiện.
3. Ở ô "Định dạng ô nếu", chọn Công thức tuỳ chỉnh là.
4. Nhập công thức bắt đầu bằng dấu = và trả về ĐÚNG/SAI, ví dụ =$F2<10.
5. Chọn màu nền rồi bấm Xong.

Vì vùng bắt đầu từ dòng 2 nên công thức viết theo dòng 2. Nếu vùng của bạn bắt đầu từ F1 thì công thức phải là =$F1<10, viết $F2 sẽ lệch một dòng.

Một ví dụ hay dùng khác là bắt mã trùng: chọn cột mã khách hàng B2:B, công thức =COUNTIF($B$2:$B; $B2)>1. Ô nào có mã xuất hiện từ hai lần trở lên sẽ đổi màu. Ở đây $B$2:$B khoá cứng vì vùng đếm phải cố định, còn $B2 thì chỉ khoá cột. Nếu bạn chưa quen họ hàm đếm này, tôi có viết riêng bài về các hàm Google Sheet hay dùng nhất như IF, SUMIF, COUNTIF.

Lưu ý về dấu ngăn cách: tuỳ cài đặt vùng miền của file, tham số hàm ngăn nhau bằng dấu chấm phẩy ; hoặc dấu phẩy ,. Bài này viết theo dấu chấm phẩy; nếu file bạn báo lỗi, đổi sang dấu phẩy là chạy.

Làm sao tô màu cả dòng theo giá trị một ô?

Chọn vùng gồm tất cả các cột của dòng, rồi viết công thức khoá cột chứa điều kiện bằng dấu $. Giả sử bảng theo dõi công việc có dữ liệu từ cột A đến cột H, cột H là TrangThai:

1. Bôi đen A2:H1000 (bắt đầu từ dòng 2 để chừa dòng tiêu đề).
2. Vào Định dạng > Định dạng có điều kiện > Công thức tuỳ chỉnh là.
3. Nhập =$H2="Quá hạn".
4. Chọn màu nền đỏ nhạt, bấm Xong.

Tại sao phải là $H2 mà không phải H2 hay $H$2? Dấu $ trước chữ H khoá cột: ô A2, B2, C2… đều nhìn về cột H của chính dòng đó. Số 2 không khoá nên khi quy tắc chạy xuống dòng 3, nó tự thành $H3. Nếu viết $H$2, cả 999 dòng sẽ cùng nhìn vào một ô H2 — hoặc tô hết, hoặc không tô dòng nào.

Muốn định dạng dòng có điều kiện trong Google Sheet với nhiều tiêu chí, bọc lại bằng AND. Ví dụ ở một cửa hàng vật liệu xây dựng tôi từng dọn file giúp, chủ cửa hàng muốn nhìn ra ngay đơn đang chờ duyệt mà giá trị lớn để ưu tiên gọi điện xác nhận: vùng A2:H1000, công thức =AND($H2="Chờ duyệt"; $G2>5000000) với G là cột thành tiền. Cần "hoặc" thì thay AND bằng OR.

Định dạng có điều kiện ngày tháng trong Google Sheet

Định dạng có điều kiện ngày tháng trong Google Sheet dựa trên hàm TODAY() — hàm này trả về ngày hiện tại và tự cập nhật mỗi khi file mở lại, nên quy tắc viết một lần dùng mãi. Lấy bảng công việc văn phòng làm ví dụ: cột E là HanXuLy, cột H là TrangThai, vùng áp dụng A2:H1000.

Việc quá hạn mà chưa làm xong

Công thức: =AND($E2<>""; $E2<TODAY(); $H2<>"Xong"). Ba vế lần lượt là: có ngày hạn, ngày hạn đã qua, và trạng thái chưa phải "Xong". Vế $E2<>"" rất quan trọng — thiếu nó, các dòng trống phía dưới cũng bị tô đỏ vì ô trống được hiểu là số 0, nhỏ hơn ngày hôm nay.

Sắp đến hạn trong 3 ngày

Công thức: =AND($E2>=TODAY(); $E2-TODAY()<=3), tô màu vàng. Đặt quy tắc này dưới quy tắc quá hạn để màu đỏ được ưu tiên.

Tô cuối tuần trong bảng lịch

Với bảng chấm công hay lịch sản xuất có cột A là ngày: =WEEKDAY($A2;2)>5. Tham số 2 cho biết tuần bắt đầu từ thứ Hai, nên 6 và 7 là thứ Bảy và Chủ nhật.

Điều kiện bắt buộc cho cả nhóm này: ô ngày phải là ngày thật, không phải chữ. Dễ kiểm tra lắm — bấm vào ô, nếu nội dung tự căn sang phải thì đó là ngày; căn trái là chữ. Dữ liệu dán từ phần mềm khác về rất hay bị dạng chữ, khi đó dùng Dữ liệu > Tách văn bản thành các cột hoặc nhập lại định dạng ngày trước khi làm quy tắc.

Bảng tóm tắt công thức mẫu

Bạn có thể chép nguyên các công thức dưới đây, chỉ đổi chữ cái cột cho khớp bảng của mình:

Nhu cầu	Công thức tuỳ chỉnh	Vùng áp dụng
Bắt mã khách / mã hàng bị trùng	=COUNTIF($B$2:$B; $B2)>1	B2:B
Việc quá hạn chưa xong (tô cả dòng)	=AND($E2<>""; $E2<TODAY(); $H2<>"Xong")	A2:H1000
Sắp đến hạn trong 3 ngày	=AND($E2>=TODAY(); $E2-TODAY()<=3)	A2:H1000
Hàng sắp hết trong kho	=$F2<10	F2:F500
Đơn lớn đang chờ duyệt	=AND($H2="Chờ duyệt"; $G2>5000000)	A2:H1000
Tô mờ cả dòng đơn đã huỷ	=$H2="Huỷ"	A2:H1000
Tô ngày cuối tuần trong bảng lịch	=WEEKDAY($A2;2)>5	A2:A400

Lỗi hay gặp khiến quy tắc không chạy

Hầu hết trường hợp "công thức đúng mà không tô màu" rơi vào bảy lỗi dưới đây. Tôi gặp đi gặp lại chúng khi mở file của khách để sửa:

• Thiếu hoặc thừa dấu $. Viết =H2="Quá hạn" (không có $) thì mỗi cột sẽ nhìn sang một cột khác nhau, kết quả tô loang lổ. Viết =$H$2 thì cả vùng dính chung một ô.
• Vùng bắt đầu dòng 1 nhưng công thức viết cho dòng 2. Kết quả là màu bị lệch đúng một dòng — quy tắc tô dòng dưới dòng lẽ ra phải tô. Nhìn lại ô đầu tiên của vùng rồi sửa số dòng trong công thức cho khớp.
• Ngày lưu dạng chữ. So sánh với TODAY() luôn ra SAI. Kiểm tra bằng cách nhìn ô căn trái hay căn phải.
• Chuỗi so sánh không khớp tuyệt đối. Ô ghi "Quá hạn " có dấu cách thừa ở cuối sẽ không bằng "Quá hạn". Dùng =TRIM($H2)="Quá hạn" cho chắc, hoặc tốt hơn là đặt danh sách thả xuống (Dữ liệu > Xác thực dữ liệu) để không ai gõ tay.
• Quy tắc chồng nhau. Hai quy tắc cùng đúng thì chỉ quy tắc nằm trên hiển thị. Kéo quy tắc quan trọng nhất lên đầu danh sách.
• Phủ cả cột làm file chậm. Chọn A:H nghĩa là áp quy tắc cho hàng trăm nghìn dòng, nhất là khi công thức có COUNTIF. Hãy giới hạn vùng theo lượng dữ liệu thực tế. Nếu file đã ì ạch, bạn tham khảo thêm bài về ưu nhược điểm của Google Sheet khi dùng để quản lý công ty.
• Tham chiếu sang sheet khác. Định dạng có điều kiện không nhận trực tiếp kiểu =Sheet2!A1. Phải bọc bằng INDIRECT, ví dụ =$H2=INDIRECT("DanhMuc!$A$1"). Dữ liệu ở file khác thì phải kéo về cùng file trước bằng hàm IMPORTRANGE rồi mới đặt quy tắc.

Mẹo gỡ rối nhanh: chép công thức ra một ô trống bên cạnh dòng dữ liệu tương ứng rồi xem nó trả về TRUE hay FALSE. Nếu ở ngoài ô cũng ra FALSE thì lỗi nằm ở công thức; còn ngoài ô ra TRUE mà vùng vẫn không tô thì lỗi nằm ở vùng áp dụng hoặc thứ tự quy tắc.

Tóm lại và bước tiếp theo

Ba điều cần nhớ: công thức luôn viết cho ô đầu tiên của vùng; dấu $ trước chữ cái cột là chìa khoá để tô cả dòng; quy tắc nằm trên được ưu tiên khi nhiều quy tắc cùng đúng. Nắm ba điều đó, bạn tự xử lý được gần hết tình huống tô màu trong file quản lý.

Bạn nên làm ngay: mở file đang dùng nhất — bảng đơn hàng, bảng công việc hay bảng tồn kho — và tạo đúng hai quy tắc: một quy tắc tô đỏ cả dòng cho việc quá hạn, một quy tắc tô vàng cho việc sắp đến hạn. Làm trên bản sao trước nếu file đang có nhiều người dùng chung. Sau đó mở rộng dần theo bảng công thức mẫu ở trên.

Cũng cần nói thẳng giới hạn: màu sắc chỉ là cảnh báo bằng mắt, nó không gửi thông báo, không ghi lịch sử ai sửa gì, và khi bảng lên vài nghìn dòng với nhiều người nhập cùng lúc thì màu mè cũng không cứu được một file đang quá tải. Lúc đó câu hỏi không còn là tô màu thế nào, mà là có nên chuyển dữ liệu sang một ứng dụng có phân quyền và nhắc việc tự động hay không.

Đọc thêm: hàm QUERY trong Google Sheet để lọc dữ liệu nhiều điều kiện và dùng Google Sheet làm database: khi nào ổn, khi nào phải chuyển.