Trong bài viết này, 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, đổi màu ô excel theo điều kiện. 

Công thức định dạng có điều kiện dựa trên giá trị một ô, đổi màu ô excel theo điều kiệ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 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 2016, 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:

1. 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?

2. Chọn Conditional formatting > New Rule…

3. Trong New Formatting Rule window, chọn Use a formula to determine which cells to format.
4. Nhập công thức vào hộp tương ứng.
5. Nhấp vào nút Format… để chọn định dạng tùy chỉnh của bạn.

6. 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.

7. Đả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 (đổi ô màu excel 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, đổi ô màu excel theo điều kiện 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:

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 khác: SEARCH () Bạn sử dụng nó theo cách này:

=SEARCH(“Worldwide”, $D2)>0– định dạng các ô hoặc hàng nếu một ô tương ứng trong cột D chứa từ ” Worldwide “. Công thức này sẽ tìm tất cả các ô đó, bất kể vị trí của văn bản tìm kiếm nằm trong một ô, ví dụ như ” “Ships Worldwide”, “Worldwide, except for…” ” vv

=SEARCH(“Worldwide”, $D2)>1 – đổ bóng các ô hoặc hàng nếu nội dung của ô bắt đầu bằng văn bản tìm kiếm.

Công thức Excel để làm nổi bật các phần trùng lặp

Nếu nhiệm vụ của bạn là định dạng các ô có giá trị trùng lặp, bạn có dùng các nguyên tắc được xác định trước có sẵn dưới Conditional formatting > Highlight Cells Rules > Duplicate Value.

Tuy nhiên, trong một số trường hợp, dữ liệu sẽ tốt hơn nếu bạn tô màu các cột đã chọn hoặc toàn bộ hàng khi có giá trị trùng lặp xảy ra trong một cột khác. Trong trường hợp này, bạn sẽ cần phải sử dụng lại công thức định dạng điều kiện Excel, và lần này chúng ta sẽ sử dụng hàm COUNTIF . Như bạn đã biết, hàm Excel này đếm số ô trong phạm vi chỉ định đáp ứng một tiêu chí duy nhất.

Làm nổi bật dữ liệu trùng lặp bao  gồm lần đầu xuất hiện

=COUNTIF($A$2:$A$10,$A2)>1 – công thức này tìm các giá trị trùng lặp trong phạm vi được chỉ định trong Cột A (A2: A10 trong trường hợp này), bao gồm cả lần xuất hiện đầu tiên.

Nếu bạn chọn áp dụng quy tắc cho toàn bộ bảng, toàn bộ hàng sẽ được định dạng, như bạn thấy trong dưới đây. Tôi đã quyết định thay đổi màu phông chữ.

Làm nổi bật dữ liệu trùng lặp không bao  gồm lần đầu xuất hiện

Để bỏ qua lần xuất hiện đầu tiên và chỉ làm nổi bật các giá trị trùng lặp tiếp theo, sử dụng công thức này: =COUNTIF($A$2:$A2,$A2)>1

Làm nổi bật các dữ liệu trùng liên tiếp trong Excel

Nếu bạn chỉ muốn làm nổi bật các giá trị trùng lặp trên các hàng liên tiếp, bạn có thể thực hiện việc này theo cách sau. Phương pháp này hoạt động cho bất kỳ loại dữ liệu nào: số, giá trị văn bản và ngày tháng.

• Chọn cột mà bạn muốn làm nổi bật các bản sao, không chọn tiêu đề cột .
• Tạo một quy tắc định dạng có điều kiện bằng các công thức đơn giản sau:
  Quy tắc 1 (xanh dương): =$A1=$A2– nêu bật lần hai và tất cả các lần xuất hiện tiếp theo, nếu có.
• Quy tắc 2 (xanh lá cây): =$A2=$A3– làm nổi bật lần xuất hiện lần đầu tiên .

Trong các công thức trên, A là cột mà bạn muốn kiểm tra, $ A1 là tiêu đề cột, $ A2 là ô đầu tiên với dữ liệu.

Quan trọng! Để các công thức hoạt động chính xác, điều quan trọng là Quy tắc 1 làm nổi bật lần lặp lại thứ 2 và tất cả các lần lặp lại tiếp theo nên là quy tắc đầu tiên trong danh sách, đặc biệt nếu bạn đang sử dụng hai màu khác nhau.

Đánh dấu các hàng trùng

Nếu bạn muốn áp dụng định dạng có điều kiện khi giá trị trùng xảy ra trong hai cột trở lên, bạn sẽ cần phải thêm một cột phụ vào bảng của mình, trong đó bạn nối các giá trị từ các cột chính bằng một công thức đơn giản như thế này =A2&B2. Sau đó bạn áp dụng một quy tắc bằng cách sử dụng biến thể của công thức COUNTIF cho các phần lặp (có hoặc không xảy ra lần xuất hiện thứ nhất). Đương nhiên, bạn có thể ẩn cột bổ sung sau khi tạo quy tắc.

Ngoài ra, bạn có thể sử dụng hàm COUNTIFS nhiều điều kiện trong một công thức. Trong trường hợp đó, bạn sẽ không cần cột trợ giúp.

Trong ví dụ này, để làm nổi bật các hàng trùng lặp với lần xuất hiện đầu tiên , hãy tạo ra một quy tắc với công thức sau:

=COUNTIFS($A$2:$A$11, $A2, $B$2:$B$11, $B2)>1

Để làm nổi bật các hàng trùng lặp mà không có lần xuất hiện đầu tiên , sử dụng công thức này:

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1

So sánh hai cột trùng lặp

Một trong những nhiệm vụ thường gặp nhất trong Excel là kiểm tra 2 cột cho các giá trị trùng lặp – tức là tìm và đánh dấu các giá trị tồn tại trong cả hai cột. Để làm điều này, bạn sẽ cần phải tạo ra một quy tắc Excel định dạng có điều kiện cho mỗi cột với sự kết hợp của hàm  =ISERROR()và =MATCH()

Đối với Cột A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE

Đối với Cột B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE

Chú thích. Để các công thức có điều kiện như vậy hoạt động chính xác, điều rất quan trọng là bạn áp dụng các quy tắc cho toàn bộ các cột, ví dụ =$A:$Avà =$B:$B.

Bạn có thể xem ví dụ về cách sử dụng thực tế trong hình sau đây để làm nổi bật các cột trùng lặp trong Cột E và F.

Công thức để làm nổi bật các giá trị ở trên hoặc dưới trung bình

Khi bạn làm việc với một số dữ liệu số, hàm AVERAGE () có thể có ích để định dạng các ô có giá trị thấp hơn hoặc cao hơn mức trung bình trong một cột.

Ví dụ: bạn có thể sử dụng công thức =$E2<AVERAGE($E$2:$E$8) để định dạng có điều kiện các hàng có số bán thấp hơn mức trung bình, như thể hiện trong hình bên dưới. Nếu bạn đang tìm kiếm điều ngược lại, tức là để đánh dấu các sản phẩm thực hiện trên mức trung bình, thay thế “<” bằng “>” trong công thức: =$E2>AVERAGE($E$2:$E$8).

Cách làm nổi bật giá trị gần nhất trong Excel

Ví dụ 1. Tìm giá trị gần nhất, bao gồm kết hợp chính xác

Trong ví dụ của chúng ta, chúng ta sẽ tìm và đánh dấu số gần nhất với số không. Nếu tập dữ liệu chứa một hoặc nhiều số không, tất cả chúng sẽ được đánh dấu. Nếu không có 0, thì giá trị gần nhất với nó, dương hoặc âm, sẽ được đánh dấu.

Trước hết, bạn cần phải nhập công thức sau vào bất kỳ ô trống nào trong bảng tính của bạn, bạn sẽ có thể ẩn ô đó sau, nếu cần. Công thức tìm thấy số trong một dãy nhất định gần nhất với số bạn chỉ định và trả về giá trị tuyệt đối của số đó (giá trị tuyệt đối là số không có dấu âm):

=MIN(ABS(B2:D13-(0)))

Trong công thức trên, B2: D13 là dải ô của bạn và 0 là số mà bạn muốn tìm kiếm kết hợp gần nhất. Ví dụ: nếu bạn đang tìm kiếm một giá trị gần nhất với 5, công thức sẽ thay đổi thành:=MIN(ABS(B2:D13-(5)))

Chú thích. Đây là một công thức mảng , vì vậy bạn cần phải nhấn Ctrl + Shift + Enter thay vì gõ Enter đơn giản để hoàn thành nó.

Và bây giờ, bạn tạo một quy tắc định dạng có điều kiện với công thức sau, trong đó B3 là ô trên cùng bên phải trong phạm vi của bạn và $ C $ 2 trong ô bằng công thức mảng trên:

=OR(B3=0-$C$2,B3=0+$C$2)

Hãy chú ý đến việc sử dụng tham chiếu tuyệt đối trong địa chỉ của ô có chứa công thức mảng ($C$2), bởi vì ô này là hằng số. Ngoài ra, bạn cần phải thay thế 0 bằng số mà bạn muốn làm nổi bật kết hợp gần nhất. Ví dụ: nếu chúng ta muốn làm nổi bật giá trị gần nhất đến 5, công thức sẽ thay đổi thành:=OR(B3=5-$C$2,B3=5+$C$2)

Ví dụ 2. Làm nổi bật một giá trị gần nhất với giá trị nhất định, nhưng không khớp hoàn toàn

Trong trường hợp bạn không muốn làm nổi bật một kết hợp chính xác, bạn cần một công thức mảng khác nhau sẽ tìm giá trị gần nhất nhưng bỏ qua kết hợp chính xác.

Ví dụ: công thức mảng sau tìm giá trị gần với 0 nhất trong phạm vi được chỉ định, nhưng bỏ qua các giá trị không, nếu có:

=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))

Hãy nhớ nhấn Ctrl + Shift + Enter sau khi bạn gõ xong công thức mảng.

Công thức định dạng có điều kiện giống như trong ví dụ trên:

=OR(B3=0-$C$2,B3=0+$C$2)

Tuy nhiên, vì công thức mảng của chúng ta trong ô C2 bỏ qua kết hợp chính xác, quy tắc định dạng có điều kiện sẽ bỏ qua các giá trị không và làm nổi bật giá trị 0.003 là kết hợp gần nhất.

Nếu bạn muốn tìm giá trị gần nhất với một số khác trong bảng tính Excel của bạn, chỉ cần thay thế “0” bằng số bạn muốn cả trong mảng và các công thức định dạng có điều kiện.

Tại sao định dạng có điều kiện Excel của tôi không hoạt động đúng?

Nếu quy tắc định dạng có điều kiện của bạn không hoạt động như mong đợi, mặc dù công thức dường như đúng, đừng buồn bã! Rất có thể nó không phải là do một số lỗi lạ trong Excel, mà vì do một sai lầm nhỏ, không nhận ra ngay được. Vui lòng thử 6 bước khắc phục sự cố đơn giản dưới đây và tôi chắc chắn bạn sẽ nhận được công thức của mình để làm việc:

1. Sử dụng các địa chỉ ô tuyệt đối và tương đối chính xác. Rất khó để có thể suy ra một nguyên tắc chung sẽ hoạt động trong 100% trường hợp. Nhưng thường thì bạn sẽ sử dụng một cột tuyệt đối (với $) và hàng tương đối (không có $) trong các tham chiếu ô của bạn, ví dụ =$A1>1.

Hãy nhớ rằng các công thức =A1=1, =$A$1=1và =A$1=1 sẽ cho kết quả khác nhau. Nếu bạn không chắc chắn cái nào là đúng trong trường hợp của bạn, bạn có thể thử tất cả.

2. Xác minh phạm vi áp dụng. Kiểm tra xem quy tắc định dạng có điều kiện của bạn có áp dụng cho đúng phạm vi của ô. Một quy tắc là – chọn tất cả các ô / hàng bạn muốn định dạng nhưng không bao gồm các tiêu đề cột.
3. Viết công thức cho ô trên cùng bên trái. Trong các quy tắc định dạng có điều kiện, tham chiếu ô liên quan đến ô phía trên cùng bên trái trong phạm vi áp dụng. Vì vậy, luôn luôn viết công thức định dạng có điều kiện của bạn cho dòng thứ nhất với dữ liệu.

Ví dụ: nếu dữ liệu của bạn bắt đầu ở hàng 2, bạn gõ =A$2=10 sẽ đánh dấu các ô có giá trị bằng 10 trong tất cả các hàng . Một sai lầm phổ biến là luôn luôn sử dụng một tham chiếu đến hàng đầu tiên (ví dụ =A$1=10). Hãy nhớ rằng, bạn tham khảo hàng 1 trong công thức chỉ khi bảng của bạn không có tiêu đề và dữ liệu của bạn thực sự bắt đầu trong hàng 1. Dấu hiệu rõ ràng nhất của trường hợp này là khi công thức hoạt động, nhưng các định dạng giá trị không xuất hiện đúng nơi cần thiết.

4. Kiểm tra quy tắc bạn đã tạo. Kiểm tra lại quy tắc trong Conditional Formatting Rules. Đôi khi, không có lý do gì cả, Microsoft Excel bóp méo quy tắc bạn vừa tạo ra. Vì vậy, nếu quy tắc không hoạt động, hãy chuyển đến Conditional Formatting > Manage Rules và kiểm tra cả công thức và phạm vi áp dụng. Nếu bạn đã sao chép công thức từ web hoặc một số nguồn bên ngoài khác, đảm bảo sử dụng dấu nháy .
5. Điều chỉnh các tham chiếu ô khi sao chép quy tắc. Nếu bạn sao chép định dạng có điều kiện Excel bằng Format Painter, đừng quên điều chỉnh tất cả các tham chiếu ô trong công thức.
6. Chia công thức phức tạp thành các phần tử đơn giản. Nếu bạn sử dụng một công thức Excel phức tạp bao gồm một số hàm khác nhau, chia thành các phần tử đơn giản và xác minh từng hàm một.

Và cuối cùng, nếu bạn đã thử tất cả các bước nhưng quy tắc định dạng có điều kiện của bạn vẫn không hoạt động chính xác, hãy viết cho tôi một dòng trong nhận xét và chúng ta sẽ cố gắng tìm hiểu nó cùng nhau 🙂

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online

Nguồn: Ablebits, dịch và biên tập bởi blog.hocexcel.online