Trong bài viết này, PhanMemFree sẽ hướng dẫn bạn cách định dạng có điều kiện trong Excel, cách sử dụng công thức Excel để định dạng các ô riêng lẻ và toàn bộ hàng dựa trên giá trị được chỉ định của bạn hoặc giá trị ô sẽ khác.
Công thức định dạng có điều kiện dựa trên giá trị ô đơn
Các quy tắc định dạng có điều kiện được xác định trước của Excel chủ yếu nhằm định dạng các ô dựa trên các giá trị hoặc giá trị mà bạn chỉ định.
Nếu bạn muốn áp dụng định dạng có điều kiện dựa trên các ô khác Hoặc định dạng toàn bộ hàng dựa trên giá trị của một ô. Bạn sẽ cần các công thức Excel.
Cách tạo quy tắc định dạng có điều kiện bằng công thức
như đã đề cập Trong các phiên bản mới của Excel 2013, Excel 2010 và Excel 2007, tính năng Định dạng có Điều kiện nằm trên tab Trang đầu> Kiểu. Trong Excel 2003, bạn có thể tìm thấy tính năng này trong menu Định dạng.
Do đó, bạn thiết lập quy tắc định dạng có điều kiện dựa trên công thức như sau:
Lời khuyên: Nếu bạn có kế hoạch bao gồm thông tin bổ sung trong tương lai Và bạn muốn các quy tắc định dạng có điều kiện tự động áp dụng cho các mục mới, bạn có thể:
Lời khuyên: Bất cứ khi nào bạn muốn chỉnh sửa công thức định dạng có điều kiện, hãy nhấn F2 và di chuyển nó đến vị trí mong muốn trong công thức bằng các phím mũi tên. Nếu bạn sử dụng mũi tên mà không nhấn F2, phạm vi sẽ được chèn vào công thức thay vì chỉ di chuyển con trỏ chèn. Để thêm tham chiếu ô vào công thức, hãy nhấn lại F2 và bấm vào ô đó.
Xem thêm: Hướng dẫn Excel cơ bản
Ví dụ về công thức định dạng có điều kiện trong Excel
Bây giờ bạn đã biết cách tạo và áp dụng định dạng có điều kiện trong Excel dựa trên các ô khác, bạn có thể tạo và áp dụng định dạng có điều kiện trong Excel. Hãy xem cách sử dụng các công thức Excel khác nhau trong thực tế:
công thức so sánh giá trị (số và văn bản)
Như bạn đã biết, Microsoft Excel có sẵn một số quy tắc để định dạng ô lớn hơn, nhỏ hơn hoặc bằng giá trị mà bạn chỉ định. (định dạng có điều kiện > Đánh dấu các quy tắc ô). Tuy nhiên, các quy tắc này không áp dụng nếu bạn muốn định dạng có điều kiện các cột hoặc toàn bộ hàng nhất định. dựa trên các giá trị ô trong các cột đẹpc. Trong trường hợp này, bạn sử dụng cùng một công thức:
Hình dưới đây cho thấy một ví dụ. công thức lớn hơn Đánh dấu tên sản phẩm trong cột A nếu số lượng tồn kho (cột C) lớn hơn 0. Lưu ý rằng công thức này chỉ áp dụng cho cột A ($ A $ 2: $ A $ 8) nếu bạn chọn toàn bộ bảng (trong trường hợp này là $ A $ 2 : $). E $ 8) sẽ đánh dấu các hàng dựa trên các giá trị trong cột C.
theo cùng một cách Bạn có thể tạo các quy tắc định dạng có điều kiện để so sánh các giá trị của hai ô, chẳng hạn như:
= $ A2 <$ B2 – Định dạng một ô hoặc hàng nếu giá trị trong cột A nhỏ hơn giá trị tương ứng trong cột B.
= $ A2 = $ B2 – Định dạng ô hoặc hàng nếu giá trị trong cột A và B bằng nhau.
= $ A2 <> $ B2 – Định dạng ô hoặc hàng nếu giá trị trong cột A không giống như trong cột B.
Như bạn có thể thấy trong hình dưới đây. Công thức này hoạt động cho cả giá trị văn bản và số.
quai hàm VÀ VÀ HOẶC
Nếu bạn muốn định dạng bảng Excel của mình dựa trên hai hoặc nhiều điều kiện, hãy sử dụng hàm = AND AND = OR:
Trong hình ảnh dưới đây, chúng tôi sử dụng công thức = VÀ ($ C2> 0, $ D2 = “Toàn cầu”) để thay đổi màu nền của hàng nếu số lượng hàng tồn kho (cột C) lớn hơn 0 và nếu mặt hàng được vận chuyển trên toàn thế giới. [Worldwide] (Cột D). Lưu ý rằng công thức này hoạt động với giá trị văn bản cũng như con số .
Thông thường, bạn có thể sử dụng hai, ba hoặc nhiều điều kiện trong các hàm = AND và = OR.
Dưới đây là một số công thức định dạng có điều kiện cơ bản mà bạn sử dụng trong Excel. Bây giờ chúng ta hãy xem xét một ví dụ phức tạp hơn nhưng thú vị hơn nhiều.
Định dạng có điều kiện cho các ô trống và không trống
Tôi nghĩ rằng mọi người đều biết cách định dạng ô trống và không phải ô trống trong Excel, bạn chỉ cần tạo quy tắc mới cho ô. “Chỉ định dạng ô chứa” và chọn một trong hai ô. không gian hoặc không có khoảng trắng
Nhưng nếu bạn muốn định dạng một ô ở một trong các cột nếu ô tương ứng trong cột kia trống hoặc rỗng, trong trường hợp này, bạn sẽ cần sử dụng lại công thức Excel:
công thức trống rỗng : = $ B2 = ””– Định dạng ô / hàng đã chọn nếu ô tương ứng trong cột B trống.
Công thức cho không khoảng trắng : = $ B2 <> ””– Định dạng các ô / hàng đã chọn Nếu ô tương ứng trong cột B không được để trống
ghi. Công thức trên sẽ hoạt động đối với các ô “hiển thị” trống hoặc không trống. Nếu bạn sử dụng một hàm Excel trả về một chuỗi trống, ví dụ: = if (false, “OK”, “”)Và bạn không muốn ô đó bị coi là ô trống. Sử dụng công thức sau. = isblank (A1) = truehoặc = isblank (A1) = falseđể định dạng ô trống và ô không trống tương ứng.
Và đây là một ví dụ về cách sử dụng công thức trên trong thực tế. Giả sử bạn có một cột (B) “Ngày bán” và một cột (C) “Giao hàng”. Hai cột này chỉ hợp lệ khi các mặt hàng được bán và giao, vì vậy bạn muốn toàn bộ hàng đổi màu cam khi bạn bán và khi giao hàng Các hàng tương ứng sẽ chuyển sang màu xanh lục. để đạt được điều này Bạn cần tạo hai quy tắc định dạng có điều kiện với công thức sau:
Một điều khác bạn cần làm là đưa quy tắc thứ hai lên đầu và chọn hộp kiểm Dừng nếu đúng bên cạnh quy tắc này:
Trong trường hợp này, tùy chọn “dừng nếu đúng” không thực sự cần thiết. Và các quy tắc có thể hoạt động với hoặc không có chúng. Bạn có thể đánh dấu vào ô này để đề phòng. Trong trường hợp bạn thêm một vài quy tắc trong tương lai có thể xung đột với các quy tắc hiện có.
Công thức Excel hoạt động với các giá trị văn bản.
Nếu bạn muốn áp dụng định dạng có điều kiện cho một cột đã chọn khi các ô khác trong cùng một hàng chứa các từ nhất định, Bạn có thể sử dụng một công thức đơn giản như = $ D2 = “Toàn thế giới”(Chúng tôi đã sử dụng một công thức tương tự trong ví dụ trước.) Tuy nhiên, công thức này sẽ hoạt động đối với từ chính xác .
vì sửa một phần từ Bạn sẽ cần sử dụng một hàm Excel khác: SEARCH (), bạn sử dụng nó theo cách này:
= SEARCH (“Trên toàn thế giới”, $ D2)> 0– Định dạng một ô hoặc hàng nếu ô tương ứng trong cột D có chứa từ “Toàn thế giới”. Công thức này sẽ tìm tất cả các ô đó. Bất kể cụm từ tìm kiếm nằm ở đâu trong ô, ví dụ như ”“ Vận chuyển trên toàn thế giới ”,“ Trên toàn thế giới ngoại trừ… ”” v.v.
= SEARCH (“Trên toàn thế giới”, $ D2)> 1 – Che bóng ô hoặc hàng nếu nội dung ô bắt đầu bằng văn bản tìm kiếm.