Công thức Excel để định dạng có điều kiện dựa trên giá trị ô khác
Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn cách định dạng có điều kiện trong Excel, cách sử dụng các công thức Excel để định dạng từng ô và toàn bộ hàng dựa trên các giá trị bạn chỉ định hoặc dựa vào giá trị của một ô khác. Công thức định dạng có điều kiện dựa trên giá trị một ô ...
Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn cách định dạng có điều kiện trong Excel, cách sử dụng các công thức Excel để định dạng từng ô và toàn bộ hàng dựa trên các giá trị bạn chỉ định hoặc dựa vào giá trị của một ô khác.
Công thức định dạng có điều kiện dựa trên giá trị một ô
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 giá trị của chúng hoặc cá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 một ô khác hoặc định dạng toàn bộ hàng dựa trên giá trị của một ô, thì bạn sẽ cần sử dụng công thức Excel.
Cách tạo quy tắc định dạng có điều kiện sử dụng công thức
Như đã đề cập, trong tất cả 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 ở Home tab > Styles . Trong Excel 2003, bạn có thể tìm thấy nó dưới menu Format .
Vì vậy, bạn thiết lập một quy tắc định dạng có điều kiện dựa trên một công thức theo cách này:
- Chọn các ô mà bạn muốn định dạng. Bạn có thể chọn một cột, một vài cột hoặc toàn bộ bảng nếu bạn muốn áp dụng định dạng có điều kiện của mình cho nhiều hàng.
Mẹo: Nếu bạn dự định sẽ nhập thêm nhiều dữ liệu trong tương lai và bạn muốn quy tắc định dạng có điều kiện tự động áp dụng cho các mục nhập mới, bạn có thể:
- Chuyển đổi một dãy các ô sang một bảng ( Insert tab> Table ). Trong trường hợp này, định dạng có điều kiện sẽ được tự động áp dụng cho tất cả các hàng mới.
- Chọn một số hàng trống bên dưới dữ liệu của bạn, ví dụ 100 hàng trống.
Tìm hiểu ngay: Học Excel nâng cao ở đâu?
- Chọn Conditional formatting > New Rule…
- Trong New Formatting Rule window, chọn Use a formula to determine which cells to format.
- Nhập công thức vào hộp tương ứng.
- Nhấp vào nút Format… để chọn định dạng tùy chỉnh của bạn.
- Chuyển đổi giữa các tab Font, Border và Fill và sử dụng với các tùy chọn khác nhau như kiểu phông chữ, màu mẫu và hiệu ứng đổ để thiết lập định dạng phù hợp nhất cho bạn. Nếu bảng màu chuẩn không đủ, hãy nhấp vào More colors… và chọn bất kỳ màu RGB hoặc HSL nào theo ý thích của bạn. Khi hoàn tất, nhấp vào nút OK.
- Đảm bảo phần Preview hiển thị định dạng mà bạn muốn và nếu đồng ý, hãy nhấp vào nút OK để lưu quy tắc. Nếu bạn không hài lòng với bản xem trước định dạng, hãy nhấp vào nút Format … một lần nữa và thực hiện chỉnh sửa.
Mẹo: Bất cứ khi nào bạn cần chỉnh sửa công thức định dạng có điều kiện, hãy nhấn F2 và sau đó di chuyển đến vị trí cần thiết trong công thức bằng các phím mũi tên. Nếu bạn dùng mũi tên mà không nhấn F2 , một dãy sẽ được chèn vào công thức thay vì chỉ di chuyển con trỏ chèn. Để thêm một tham chiếu ô nhất định vào công thức, nhấn F2 một lần nữa và sau đó nhấp vào ô đó.
Xem thêm: Hướng dẫn học Excel cơ bản
Các ví dụ công thức định dạng theo định dạng có điều kiện của Excel
Bây giờ bạn đã biết cách tạo và áp dụng định dạng có điều kiện của Excel dựa trên một ô khác, chúng ta hãy tiếp tục và 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 các giá trị (số và văn bản)
Như bạn đã biết Microsoft Excel cung cấp một số ít các quy tắc sẵn dùng để định dạng các ô có giá trị lớn hơn, nhỏ hơn hoặc bằng giá trị mà bạn chỉ định (Conditional Formatting >Highlight Cells Rules). Tuy nhiên, các quy tắc này không hoạt động nếu bạn muốn định dạng có điều kiện các cột nhất định hoặc toàn bộ hàng dựa trên giá trị của một ô trong một cột khác. Trong trường hợp này, bạn sử dụng các công thức tương tự:
Hình dưới đây cho thấy một ví dụ về Công thức lớn hơn, làm nổi bật các tên sản phẩm trong cột A nếu số lượng hàng tồn kho (cột C) lớn hơn 0. Hãy chú ý rằng công thức chỉ áp dụng cho cột A ($ A $ 2: $ A $ 8). Nhưng nếu bạn chọn toàn bộ bảng (trong trường hợp này, $ A $ 2: $ E $ 8), điều này sẽ làm nổi bật các hàng dựa trên giá trị trong cột C.
Bằng cách tương tự, bạn có thể tạo ra một quy tắc định dạng có điều kiện để so sánh các giá trị của hai ô. Ví dụ:
=$A2<$B2 – Định dạng các ô hoặc các hàng nếu một 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 các hàng nếu các giá trị trong các cột A và B là giống nhau.
=$A2<>$B2 – định dạng các ô hoặc các hàng nếu một giá trị trong cột A không giống như trong cột B.
Như bạn thấy trong hình dưới đây, các công thức dùng cả cho các các giá trị văn bản cũng như cho các con số.
Hàm AND và OR
Nếu bạn muốn định dạng bảng Excel của bạn dựa trên 2 hoặc nhiều điều kiện, thì hãy sử dụng hàm =AND VÀ = OR:
| Điều kiện | Công thức | Miêu tả | |
| Nếu cả hai điều kiện đều được đáp ứng | =AND($B2<$C2, $C2<$D2) | Định dạng các ô nếu giá trị trong cột B nhỏ hơn cột C, và nếu giá trị trong cột C nhỏ hơn trong cột D. | |
| Nếu một trong các điều kiện được đáp ứng | =OR($B2<$C2, $C2<$D2) | Định dạng các ô nếu giá trị trong cột B nhỏ hơn cột C, hoặc nếu giá trị trong cột C nhỏ hơn trong cột D. |
Trong hình dưới đây, chúng tôi sử dụng công thức =AND($C2>0, $D2=”Worldwide”) để thay đổi màu nền của các hàng nếu số lượng hàng tồn kho (Cột C) lớn hơn 0 và nếu sản phẩm được vận chuyển trên toàn thế giới [Worldwide] (Cột D). Hãy lưu ý rằng công thức này hoạt động với các giá trị văn bản cũng như với các con số .
Đương nhiên, bạn có thể sử dụng hai, ba hoặc nhiều điều kiện trong hàm = AND và = OR của bạn.
Đây là những công thức định dạng có điều kiện cơ bản 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 ô trống và không trống
Tôi nghĩ mọi người đều biết làm thế nào để định dạng các ô trống trống và không trống trong Excel – bạn chỉ cần tạo một quy tắc mới cho các ô ” “Format only cells that contain “ và chọn một trong hai ô Blanks hoặc No Blanks. 
Nhưng điều gì sẽ xảy ra nếu bạn muốn định dạng các ô trong một cột nhất định nếu một ô tương ứng trong một cột khác trống hoặc không trống? Trong trường hợp này, bạn sẽ cần phải sử dụng các công thức Excel một lần nữa:
Công thức cho khoảng trống : =$B2=””– định dạng các ô / 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 một ô tương ứng trong Cột B không được để trống.
Chú thích. Các công thức trên sẽ làm việc cho các ô “hiện ra” trống hoặc không trống. Nếu bạn sử dụng một số hàm Excel trả về một chuỗi rỗng, ví dụ =if(false,”OK”, “”), và bạn không muốn các ô như vậy được coi là khoảng trắng, hãy sử dụng các công thức sau đây =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 bạn có thể sử dụng các công thức trên trong thực tế. Giả sử bạn có một cột (B) là ” Date of Sale ” và một cột khác (C) ” Delivery” “. Hai cột này chỉ có giá trị khi hàng đã được bán và giao nhận. Vì vậy, bạn muốn toàn bộ hàng chuyển sang màu da cam khi bạn đã bán hàng; Và khi một vật phẩm được giao, một hàng tương ứng sẽ chuyển sang màu xanh lá cây. Để đạt được điều này, bạn cần phải tạo ra 2 quy tắc định dạng có điều kiện với các công thức sau:
- Hàng cam (một ô trong cột B không rỗng): =$B2<>””
- Các hàng màu xanh lục (các ô trong cột B và cột C không rỗng): =AND($B2<>””, $C2<>””)
Một điều nữa bạn cần làm là đưa quy tắc thứ hai lên trên cùng và chọn hộp kiểm Stop if true bên cạnh quy tắc này: 
Trong trường hợp cụ thể này, tùy chọn “Stop if true ” thực sự không cần thiết, và quy tắc sẽ làm việc bất kể có hay không có nó. Bạn có thể đánh dấu vào hộp này chỉ như một biện pháp phòng ngừa bổ sung, trong trường hợp bạn thêm một vài quy tắc khác trong tương lai có thể xung đột với bất kỳ trường hợp nào hiện có.
Công thức Excel làm việc 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 các cột được chọn khi một ô khác trong cùng một hàng chứa một từ nhất định, bạn có thể sử dụng một công thức đơn giản như =$D2=”Worldwide”(chúng ta đã sử dụng một công thức tương tự trong một trong các ví dụ trước đó). Tuy nhiên, công thức này sẽ làm việc cho từ chính xác .
Đối với từ chỉ đúng một phần , bạn sẽ cần một hàm Excel
