Tô Màu Có Điều Kiện Thông Minh Trong Excel (conditional formatting)

Khi ta làm việc với Excel đặc biệt với những con số thì ta cần nổi bật những giá trị quan trọng, để nếu có một vấn đề bất thường thì ta có thể quan sát thấy và kịp thời sửa chữa. Và giải pháp đưa ra là ta sẽ Tô Màu Có Điều Kiện Trong Excel (Conditional Formatting) theo những điều kiện cho trước, và cách thức tô màu này sẽ hoạt động tự động chứ không phải tô từng ô một.

7 Cách Tô Màu Có Điều Kiện Thông Minh Trong Excel (conditional Formatting)

1.Tô màu theo doanh số cố định

Nếu mình có doanh số trung bình là 3000/tháng bây giờ để quan sát thống kê từng tháng và lời lỗ như thế nào thì mình sẽ tô màu những tháng có danh số nhỏ hơn 3000.

Để thực hiện bạn cần có bản doanh số, đầu tiên bạn bôi đen bản doanh số -> Home -> Conditional Formatting -> New Rule.

Cửa sổ New formatting Rule hiện ra, bạn chọn Use a formula to determine which cells to format trong ô Format bạn nhập công thức =kích vô ô đầu tiên của doanh số bấm F4 ba lần để loại bỏ dấu $ phía trước <=3000 -> Format -> fill chọn màu -> ok.

Nếu bạn muốn tô màu cả phần tháng của doanh thu đó thì bạn bôi đen cả phần tháng vẫn chọn vào Home -> Conditional Formatting -> New Rule -> trong này bạn chọn Use a formula to determine which cells to format trong ô Format, bạn nhập công thức =kích vào ô đầu tiên của doanh thu bấm F4 hai lần <=3000 vào Format -> Fill chọn màu -> ok.

2.Tô màu theo doanh số động 

Với cách này mình có thể làm nổi bật được khoản doanh số mà bạn muốn kiểm tra.

Đầu tiên bạn cũng bôi đen cột doanh số vào Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format, bạn dùng cú pháp =kích vô ô đầu tiên của doanh số bấm F4 3 lần <= kích vô ô điều tiện mà mình sẽ thay đổi doanh số làm nổi bật tại ô này -> Format -> Fill chọn màu -> OK.

Bây giờ bạn thay đổi giá trị tại ô điều kiện thì bản doanh số tự động tô màu theo điều kiện của bạn.

3.Tô màu theo khách hàng

Mình sẽ kiểm tra khách hàng mới trong năm 2020 và làm nổi bật khách hàng đó lên nếu khách hàng đó không có trong danh sách khách hàng năm 2019.

Để thực hiện bạn bôi đen toàn bộ cột doanh thu trong năm 2020 ,chọn vào Home -> Conditional Formatting -> New Rule -> chọn Use a formula to determine which cells to format trong ô Format bạn sử dụng hàm =Countif(kích vào cột khách hàng cũ của năm 2019;kích vào ô chứa khách hàng đầu tiên của năm 2020 sau đó nhấn F4 2 lần)=0 vào Format -> Fill chọn màu -> ok.  

VD:=countif($B:$B;$E6)=0

Ngoài nắm thông tin khách hàng mới thì bạn cũng phải nắm thông tin khách hàng cũ để khuyến mãi hay tri ân khách hàng bây giờ mình sẽ hướng dẫn bạn làm nổi bật thông tin khách hàng cũ.

Đầu tiên bạn bôi đen cột khách hàng năm 2020, sau đó vào Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format bạn vẫn dùng hàm Countif với cú pháp =Countif(kích vô cột khách hàng cũ;kích vô ô đầu tiên của khách hàng mới 2020 bấm F4 hai lần)>0 tiếp theo vào Format -> Fill chọn màu -> ok.

Nếu bạn muốn tô màu cho cả phần tháng thì thay vì bôi đen doanh thu thì bạn bôi đen cả phần tháng và phần doanh thu và các bước còn lại làm tương tự. Tô Màu Có Điều Kiện Trong Excel (Conditional Formatting)

4.Tô màu những ngày cuối tuần (Thứ 7 + Chủ Nhật)

Để làm nổi bật những ngày cuối tuần cụ thể là thứ 7 và chủ nhật thì bạn nên tô màu cho hai ngày này.

Đầu tiên bạn phải bôi đen phần dữ liệu ngày và thứ trong bản -> Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format sử dụng hàm Or với cú pháp =or(weekday(kích vô ô đầu tiên của cột ngày bấm F4 hai lần)= 1;weekday(kích vô ô đầu tiên của cột ngày nhấn F4 hai lần)=7 vào Format -> Fill chọn màu -> ok.  Tức là hàm weekday sẽ trả về số của các ngày trong tuần 1 là chủ nhật và 7 là thứ 7 nên nếu ô nào đó trong bản bằng 1 hoặc bằng 7 thì sẽ được tô màu.

Bây giờ mình sẽ nâng cao hơn là thứ 7 tô một màu chủ nhật tô một màu thì bạn cũng bôi đen phần dữ liệu chọn Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format sử dụng hàm weekday với cú pháp =weekday(kích vô ô đầu tiên của dữ liệu ngày bấm F4 hai lần)=7 vào Format -> Fill chọn màu -> ok. Như vậy mình đã tô màu cho thứ bảy.

Còn lại với chủ nhật bạn cũng làm tương tự như thứ 7 đó là bôi đen phần dữ liệu chọn Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format, sử dụng hàm weekday với cú pháp =weekday(kích vô ô đầu tiên của dữ liệu ngày bấm F4 hai lần)=1 vào Format -> Fill chọn màu -> ok. Chủ nhật được hàm weekday trả về bằng 1.

5.Tô màu những ô từ năm này tới năm khác

Bây giờ mình muốn tô màu ô của một khoản thời gian. Với dữ liệu của này mình muốn tô màu từ năm 2016 đến năm 2018 thì mình sử dụng hàm And.

Để thực hiện, đầu tiên bạn cũng bôi đen phần dữ liệu chọn vào Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format sử dụng hàm And với cú pháp =and(year(kích vô ô đầu tiên của dữ liệu bấm F4 ba lần)>=2016;year(kích vô ô đầu tiên của dữ liệu bấm F4 ba lần)<=2018) -> Format -> Fill chọn màu -> ok.

6.Tô màu những ô theo khoảng thời gian bên ngoài

Tô màu trong khoảng thời gian tính bằng ngày thì mình cũng bôi đen vùng dữ liệu chọn vào Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format ta dùng hàm and:  =and(kích vào ô đầu tiên của vùng dữ liệu nhấn F4 3 lần >=kích vô ô ngày bắt đầu của khoảng cần tô màu;kích vô ô đầu tiên của vùng dữ liệu nhấn F4 3 lần <= kích vô ô ngày kết thúc của khoảng tô màu) -> Format -> Fill chọn màu -> ok.

7.Tô màu những sản phẩm hết hạn

Để tô màu những sản phẩm hết hạn bạn bôi đen phần dữ liệu vào Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format trong ô Format bạn gõ =kích vào ô đầu tiên của vùng dữ liệu sau đó nhấn F4 hai lần <=Today() -> Format -> Fill chọn màu -> ok.  Hàm Today sẽ trả về ngày hôm nay là ngày bao nhiêu và dùng ngày hôm nay để so sánh.

Ngoài những điều cơ bản trên, hãy dành thời gian tìm hiểu thêm về các quy tắc cũng như định dạng, bởi đây là một chức năng vô cùng hữu hiệu cũng như cần thiết khi làm việc với Excel.

Theo dichvudanhvanban.com