Định dạng có điều kiện với công thức trong Excel
Theo mặc định, excel đã cung cấp cho chúng tôi nhiều loại định dạng có điều kiện có sẵn để thực hiện với dữ liệu của chúng tôi, nhưng nếu bất kỳ người dùng nào muốn định dạng được thực hiện trên các tiêu chí hoặc công thức cụ thể, chúng tôi có thể làm như vậy trong tab trang chủ của phần định dạng có điều kiện khi chúng tôi nhấp vào quy tắc mới, chúng tôi sẽ tìm thấy một tùy chọn để định dạng có điều kiện với các công thức nơi chúng tôi có thể viết công thức sẽ xác định các ô cần định dạng.
Định dạng có điều kiện với Công thức không có gì khác ngoài việc thay đổi định dạng của các ô dựa trên điều kiện hoặc tiêu chí do người dùng đưa ra. Bạn có thể đã sử dụng định dạng có điều kiện để đánh dấu giá trị hàng đầu trong phạm vi, đánh dấu các giá trị trùng lặp.
Một điều tốt là chúng ta có các công thức ở định dạng có điều kiện trong excel. Tất cả các công thức phải logic; kết quả sẽ là TRUE hoặc FALSE. Nếu bài kiểm tra logic vượt qua, thì chúng ta sẽ nhận được định dạng có điều kiện và nếu bài kiểm tra logic excel không thành công, thì chúng ta sẽ không nhận được gì.
Tổng quat
Hãy để tôi giới thiệu với bạn định dạng có điều kiện với cửa sổ công thức.
Trong phần dưới đây của bài viết, chúng ta sẽ thấy rất nhiều ví dụ để áp dụng định dạng có điều kiện với công thức.
Làm thế nào để sử dụng định dạng có điều kiện trong Excel với công thức?
# 1 - Đánh dấu các ô có giá trị nhỏ hơn 500
Giả sử bên dưới là các con số bán hàng mà bạn đang làm việc và bên dưới là giá bán cho mỗi đơn vị.
- Bước 1: Đi tới Định dạng có điều kiện và nhấp vào Quy tắc mới.
- Bước 2: Bây giờ chọn “ Sử dụng công thức để xác định ô cần định dạng”.
- Bước 3: Bây giờ, trong “ Định dạng giá trị trong đó công thức là đúng”, hãy áp dụng công thức là A2 <500 và sau đó nhấp vào Định dạng để áp dụng định dạng excel.
- Bước 4: Chọn định dạng theo ý muốn của bạn.
- Bước 5: Bây giờ, bạn có thể xem bản xem trước của định dạng trong hộp xem trước.
- Bước 6: Bây giờ, nhấp vào OK để hoàn tất việc định dạng. Bây giờ nó đã đánh dấu tất cả các ô có số <500.
# 2 - Đánh dấu một ô dựa trên một ô khác
Chúng ta có thể đánh dấu một ô dựa trên một giá trị ô khác. Giả sử chúng ta có dữ liệu Tên sản phẩm và Giá sản phẩm trong hai cột đầu tiên.
Từ bảng trên, chúng ta cần làm nổi bật các sản phẩm nếu giá bán> 220.
- Bước 1: Chọn phạm vi sản phẩm và sau đó, Chuyển đến định dạng có điều kiện và nhấp vào Quy tắc mới.
- Bước 2: Trong công thức, áp dụng công thức là B2> 220.
- Bước 3: Nhấp vào phím Định dạng và áp dụng định dạng theo ý muốn của bạn.
- Bước 5: Bấm OK; chúng tôi đã sẵn sàng định dạng.
# 3 - Đánh dấu tất cả các ô trống trong phạm vi
Giả sử bên dưới là dữ liệu bạn có.
Trong dữ liệu trên, bạn phải đánh dấu tất cả các ô trống hoặc trống. Chúng ta cần sử dụng công thức ISBLANK trong định dạng có điều kiện của Excel.
Áp dụng công thức dưới đây trong phần công thức.
Chọn định dạng theo ý muốn của bạn.
Nhấp vào ok nó sẽ đánh dấu tất cả các ô trống trong phạm vi đã chọn.
# 4 - Sử dụng hàm AND để đánh dấu ô
Hãy xem xét dữ liệu dưới đây cho công thức này.
Nếu khu vực là miền Trung và giá trị bán hàng> 500, chúng tôi cần đánh dấu hàng hoàn chỉnh.
- Bước 1: Chọn toàn bộ dữ liệu trước.
- Bước 2: Ở đây, chúng ta cần sử dụng hàm AND excel vì chúng ta cần kiểm tra hai điều kiện ở đây và cả hai điều kiện phải là TRUE. Áp dụng công thức dưới đây.
- Bước 3: Nhấp vào Định dạng.
- Bước 4: Vào FILL và chọn màu và hiệu ứng cần thiết.
- Bước 5: Nhấp vào OK. Chúng ta sẽ thấy các hàng được đánh dấu nếu cả hai điều kiện đều ĐÚNG.
# 5- Sử dụng Hàm OR để Đánh dấu các Ô
Hãy xem xét dữ liệu dưới đây để làm ví dụ.
Trong phạm vi dữ liệu trên, tôi muốn làm nổi bật tên thành phố Bangalore và Mysore.
- Bước 1: Chọn dữ liệu đầu tiên và chuyển đến Định dạng có điều kiện, sau đó nhấp vào Quy tắc mới.
- Bước 2: Vì chúng ta cần đánh dấu một trong hai ô giá trị, hãy áp dụng hàm OR excel.
- Bước 3: Nhấp vào định dạng và chọn định dạng cần thiết.
- Bước 4: Sau khi định dạng được áp dụng, hãy nhấp vào OK để hoàn thành tác vụ. Một công thức sẽ đánh dấu tất cả các ô có giá trị Bangalore & Mysore.
# 6 - Sử dụng Hàm COUNTIF để đánh dấu các ô
Giả sử bạn đang làm việc với cơ sở dữ liệu khách hàng. Bạn cần xác định tất cả các khách hàng có tên xuất hiện nhiều hơn năm lần trong danh sách. Dưới đây là dữ liệu.
Chọn dữ liệu và áp dụng công thức COUNTIF trong phần công thức.
Nhấp vào định dạng và áp dụng định dạng cần thiết.
Bây giờ công thức sẽ đánh dấu tất cả các tên nếu số đếm nếu nhiều hơn 5.
Amber là cái tên duy nhất xuất hiện hơn 5 lần trong danh sách.
# 7 - Đánh dấu mọi hàng thay thế
Chúng tôi cũng có thể có công thức để đánh dấu mọi hàng thay thế trong excel của dữ liệu. Giả sử bên dưới là dữ liệu bạn đang làm việc.
Chọn dữ liệu và áp dụng công thức dưới đây.
= MOD (ROW (), 2)
Áp dụng cho định dạng theo ý muốn của bạn và nhấp vào OK; chúng ta sẽ có mọi hàng thay thế được đánh dấu bằng công thức.
# 8 - Đánh dấu mọi cột thay thế
Giống như cách chúng ta đã đánh dấu mọi hàng thay thế, chúng ta có thể đánh dấu mọi cột thay thế. Hãy xem xét cùng một dữ liệu cho ví dụ.
Áp dụng công thức dưới đây.
= MOD (COLUMN (), 2)
Sao chép công thức trên và áp dụng nó trong phần công thức của định dạng có điều kiện.
Bấm vào OK; nó sẽ làm nổi bật hàng thay thế.
Nếu bạn muốn bỏ qua cột đầu tiên và đánh dấu từ cột thứ hai, bạn cần sử dụng công thức dưới đây.
Nó sẽ đánh dấu cột thay thế bắt đầu từ cột thứ hai.
Những điều cần ghi nhớ
- Định dạng có điều kiện chỉ chấp nhận các công thức logic có kết quả chỉ là TRUE hoặc FALSE.
- Bản xem trước của định dạng có điều kiện chỉ là một chỉ báo về cách thức định dạng.
- Không bao giờ sử dụng tham chiếu tuyệt đối như trong công thức. Khi bạn đang áp dụng công thức và nếu bạn chọn ô trực tiếp, nó sẽ làm cho nó trở thành một tham chiếu tuyệt đối, bạn cần phải chuyển nó thành một tham chiếu tương đối trong excel.
