Trong nội dung bài viết này, học tập Excel Online sẽ hướng dẫn cách định dạng có điều kiện trong Excel, biện pháp sử dụng những công thức Excel để định dạng từng ô và cục bộ hàng dựa trên những giá trị các bạn chỉ định hoặc nhờ vào giá trị của một ô khác, đổi màu ô excel theo điều kiện.
Công thức định hình có điều kiện dựa trên giá trị một ô, chuyển màu ô excel theo điều kiện
Công thức nhằm so sánh các giá trị (số với văn bản)Công thức Excel để triển khai nổi bật những phần trùng lặp
Công thức để làm nổi bật những giá trị làm việc trên hoặc dưới trung bình
Công thức định dạng có điều kiện dựa trên quý giá một ô, thay đổi màu ô excel theo điều kiện
Các quy tắc format có đk được xác minh trước của Excel chủ yếu nhằm định dạng các ô dựa trên giá trị của bọn chúng hoặc những giá trị mà các bạn chỉ định.Bạn đang xem: Tô màu theo điều kiện trong excel
Nếu bạn có nhu cầu áp dụng định hình có đk dựa bên trên một ô không giống hoặc định dạng toàn thể hàng dựa trên giá trị của một ô, thì bạn sẽ cần áp dụng công thức Excel.
Cách sinh sản quy tắc format có đk sử dụng công thức
Như đã đề cập, trong toàn bộ các phiên phiên bản mới của Excel 2016, Excel 2013, Excel 2010 cùng Excel 2007, nhân kiệt định dạng có điều kiện nằm làm việc Home tab > Styles . Trong Excel 2003, chúng ta cũng 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ó đk dựa trên một công thức theo cách này:
Chọn những ô mà bạn muốn định dạng. Bạn bao gồm thể lựa chọn một cột, một vài cột hoặc tổng thể bảng nếu bạn có nhu cầu áp dụng định dạng gồm điều kiện của chính mình cho các hàng.Mẹo: nếu như khách hàng dự định vẫn nhập thêm những dữ liệu sau này và bạn có nhu cầu quy tắc định dạng gồm điều kiện tự động hóa áp dụng cho những mục nhập mới, bạn có thể:
đổi khác một dãy những ô qua một bảng ( Insert tab> Table ). Trong trường phù hợp này, định dạng có điều kiện sẽ được auto áp dụng cho toàn bộ các mặt hàng mới. Lựa chọn một số hàng trống bên dưới dữ liệu của bạn, ví dụ như 100 sản phẩm 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 lớn format. Nhập phương pháp vào hộp tương ứng. Nhấp vào nút Format… để lựa chọn định dạng tùy chỉnh thiết lập của bạn.
Mẹo: Bất cứ bao giờ bạn cần chỉnh sửa công thức định hình có đk (đổi ô màu excel bao gồm điều kiện), hãy nhận F2 với sau đó dịch chuyển đến vị trí cần thiết trong bí quyết bằng những phím mũi tên. Nếu như khách hàng dùng mũi tên nhưng mà không nhấn F2 , một dãy sẽ tiến hành chèn vào bí quyết thay do chỉ di chuyển con trỏ chèn. Để thêm 1 tham chiếu ô khăng khăng vào công thức, dấn F2 một đợt nữa và sau đó nhấp vào ô đó.
Các ví dụ công thức định dạng theo định hình có đk 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 sắc excel theo điều kiện dựa bên trên một ô khác, chúng ta hãy liên tiếp và xem giải pháp sử dụng các công thức Excel khác biệt trong thực tế:
Công thức để so sánh các giá trị (số và văn bản)
Như chúng ta đã biết Microsoft Excel hỗ trợ một số ít những quy tắc sẵn dùng để định dạng các ô có giá trị to hơn, nhỏ hơn hoặc bởi giá trị mà bạn chỉ định (Conditional Formatting >Highlight Cells Rules). Tuy nhiên, những quy tắc này không vận động nếu bạn có nhu cầu định dạng có điều kiện những cột cố định hoặc toàn thể hàng dựa trên quý giá của một ô trong một cột khác. Vào trường thích hợp này, các bạn sử dụng các công thức tương tự:
Hình bên dưới đây cho biết thêm một ví dụ như về Công thức béo hơn, làm khá nổi bật các tên thành phầm trong cột A nếu số lượng hàng tồn kho (cột C) to hơn 0. Hãy chăm chú rằng bí quyết chỉ vận dụng cho cột A ($ A $ 2: $ A $ 8). Nhưng nếu như bạn chọn toàn cục bảng (trong trường thích hợp này, $ A $ 2: $ E $ 8), điều đó sẽ làm rất nổi bật các hàng dựa trên giá trị trong cột C.
Bằng biện pháp tương tự, bạn cũng có thể tạo ra một quy tắc định hình có điều kiện để so sánh các giá trị của nhị ô. Ví dụ:
=$A2$B2 – định dạng các ô hoặc những hàng ví như một quý hiếm trong cột A không hệt như trong cột B.
Như chúng ta thấy vào hình bên dưới đây, những công thức cần sử dụng cả cho các các quý hiếm văn bạn dạng cũng như cho những con số.
Nếu bạn muốn định dạng bảng Excel của khách hàng dựa trên 2 hoặc những điều kiện, thì nên 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 |
Trong hình bên 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 con số hàng tồn kho (Cột C) lớn hơn 0 với nếu thành phầm được đi lại trên toàn trái đất <Worldwide> (Cột D). Hãy lưu ý rằng phương pháp này vận động với các giá trị văn bản cũng như với các con số .
Đương nhiên, chúng ta có thể sử dụng hai, cha hoặc nhiều đk trong hàm = & và = OR của bạn.
Đây là những bí quyết định dạng có đk cơ bạn dạng bạn thực hiện trong Excel. Bây giờ họ hãy chú ý một ví dụ tinh vi hơn mà lại thú vị hơn nhiều.
Định dạng có đk cho ô trống và không trống
Tôi suy nghĩ mọi fan đều biết làm nuốm nào nhằm định dạng những ô trống trống và không trống vào Excel – bạn chỉ việc 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 có nhu cầu định dạng các ô vào một cột khăng khăng nếu một ô tương xứng trong một cột khác trống hoặc ko trống? Trong trường vừa lòng này, bạn sẽ cần đề xuất 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 đã lựa chọn nếu ô tương xứng trong Cột B trống.
Công thức cho không khoảng chừng trống : =$B2””– định dạng những ô / hàng đã lựa chọn nếu một ô tương xứng trong Cột B ko được nhằm 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 ko trống. Nếu các bạn sử dụng một trong những hàm Excel trả về một chuỗi rỗng, ví dụ =if(false,”OK”, “”), và bạn không muốn những ô bởi vậy được xem như là khoảng trắng, hãy sử dụng những công thức sau đây =isblank(A1)=truehoặc =isblank(A1)=falseđể định hình ô trống với không trống, tương ứng.
Và đấy là một lấy một ví dụ về cách chúng ta có thể sử dụng những công thức trên trong thực tế. Giả sử chúng ta có một cột (B) là ” Date of Sale ” và một cột không giống (C) ” Delivery” “. Hai cột này chỉ có giá trị lúc hàng đang được chào 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 đã buôn bán hàng; Và lúc một vật phẩm được giao, một hàng tương xứng sẽ chuyển sang màu xanh lá cây. Để đã có được điều này, bạn phải tạo ra 2 quy tắc định hình có đk 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ây lục (các ô vào cột B và cột C ko rỗng): =AND($B2””, $C2””)Một điều nữa bạn cần làm là gửi quy tắc vật dụng hai lên trên thuộc và chọn hộp kiểm Stop if true bên cạnh quy tắc này:
Trong trường hợp ví dụ này, tùy lựa chọn “Stop if true ” thực thụ không đề xuất thiết, với quy tắc sẽ làm việc bất nhắc có hay là không có nó. Bạn hoàn toàn có thể đánh vệt vào hộp này chỉ như một biện pháp phòng ngừa ngã sung, trong trường hợp các bạn thêm một vài ba quy tắc khác trong tương lai rất có thể xung bất chợt với bất kỳ trường vừa lòng nào hiện tại có.
Công thức Excel thao tác làm việc với những giá trị văn bản
Nếu bạn có nhu cầu áp dụng định dạng có điều kiện cho những cột được chọn khi một ô không giống trong và một hàng đựng một từ duy nhất định, chúng ta có thể sử dụng một công thức dễ dàng và đơn giản như =$D2=”Worldwide”(chúng ta đã áp dụng một công thức tương tự trong một trong các ví dụ trước đó). Tuy nhiên, bí quyết này sẽ thao tác cho từ chủ yếu xác .
Đối với từ chỉ đúng một phần , các bạn sẽ cần một hàm Excel khác: SEARCH () Bạn thực hiện nó theo phong cách này:
=SEARCH(“Worldwide”, $D2)>0– định dạng các ô hoặc hàng giả dụ một ô tương ứng trong cột D chứa từ ” Worldwide “. Công thức này vẫn tìm toàn bộ các ô đó, bất kể vị trí của văn bản tìm kiếm phía trong một ô, ví dụ như ” “Ships Worldwide”, “Worldwide, except for…” ” vv
=SEARCH(“Worldwide”, $D2)>1 – đổ bóng các ô hoặc sản phẩm nếu ngôn từ 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 người tiêu dùng là định dạng những ô có mức giá trị trùng lặp, bạn có dùng những nguyên tắc được khẳng định trước có sẵn dưới Conditional formatting > Highlight Cells Rules > Duplicate Value.
Tuy nhiên, trong một số trong những trường hợp, tài liệu sẽ tốt hơn nếu như bạn tô màu các cột đã chọn hoặc toàn cục hàng khi có mức giá trị trùng lặp xẩy ra trong một cột khác. Trong trường đúng theo này, các bạn sẽ cần phải áp dụng lại cách làm định dạng đk Excel, và lần này bọn họ sẽ sử dụng hàm COUNTIF . Như chúng ta đã biết, hàm Excel này đếm số ô trong phạm vi chỉ định thỏa mãn nhu cầu một tiêu chuẩn duy nhất.
Làm rất 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 – phương pháp này tìm các giá trị trùng lặp trong phạm vi được hướng đẫn trong Cột A (A2: A10 trong trường đúng theo này), bao hàm cả lần lộ diện đầu tiên.
Nếu các bạn chọn vận dụng quy tắc cho toàn bộ bảng, tổng thể hàng sẽ được định dạng, như bạn thấy trong dưới đây. Tôi vẫn quyết định biến đổi màu phông chữ.
Để bỏ lỡ lần xuất hiện thứ nhất và chỉ làm nổi bật các quý giá trùng lặp tiếp theo, thực hiện công thức này: =COUNTIF($A$2:$A2,$A2)>1
Nếu các bạn chỉ ước ao làm khá nổi bật các quý hiếm trùng lặp trên các hàng liên tiếp, chúng ta cũng có thể thực hiện bài toán này theo phong cách sau. Phương pháp này vận động cho bất kỳ loại dữ liệu nào: số, quý hiếm văn bạn dạng 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 lựa chọn tiêu đề cột . Tạo một quy tắc format có đk bằng các công thức dễ dàng sau:Quy tắc 1 (xanh dương): =$A1=$A2– nêu nhảy lần hai và tất cả các lần xuất hiện thêm tiếp theo, giả dụ có. Quy tắc 2 (xanh lá cây): =$A2=$A3– làm rất 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 có nhu cầu kiểm tra, $ A1 là tiêu đề cột, $ A2 là ô trước tiên với dữ liệu.
Quan trọng! Để các công thức chuyển động chính xác, điều đặc trưng là quy tắc 1 làm nổi bật lần lặp lại thứ 2 và toàn bộ các lần lặp lại tiếp theo sau nên là quy tắc thứ nhất trong danh sách, đặc biệt nếu nhiều người đang sử dụng nhì màu khác nhau.
Nếu bạn muốn áp dụng định hình có đk khi giá trị trùng xẩy ra trong hai cột trở lên, các bạn sẽ cần bắt buộc thêm một cột phụ vào bảng của mình, trong các số đó bạn nối các giá trị từ những cột chính bởi một công thức dễ dàng và đơn giản như cụ này =A2&B2. Sau đó bạn áp dụng một quy tắc bằng cách sử dụng vươn lên là thể của phương pháp COUNTIF cho các phần lặp (có hoặc không xẩy ra lần lộ diện thứ nhất). Đương nhiên, bạn cũng có thể ẩn cột bổ sung sau khi tạo nên quy tắc.
Ngoài ra, chúng ta có thể sử dụng hàm COUNTIFS nhiều đk trong một công thức. Trong trường vừa lòng đó, bạn sẽ không cần cột trợ giúp.
Trong lấy ví dụ này, để gia công nổi bật các hàng trùng lặp với lần mở ra đầu tiên , hãy tạo nên một phép tắc với phương pháp sau:
=COUNTIFS($A$2:$A$11, $A2, $B$2:$B$11, $B2)>1
Để làm khá nổi bật các hàng trùng lặp mà không tồn tại lần xuất hiện đầu tiên , áp 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ặpMột giữa những nhiệm vụ thường gặp gỡ nhất trong Excel là chất vấn 2 cột cho những giá trị giống nhau – tức là tìm và khắc ghi các cực hiếm tồn tại trong cả nhị cột. Để làm cho điều này, các bạn sẽ cần phải tạo ra một nguyên tắc Excel định dạng bao gồm điều kiện cho từng cột với sự phối kết hợp của hàm =ISERROR()và =MATCH()
Đối cùng với Cột A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE
Đối cùng với Cột B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE
Chú thích. Để những công thức có điều kiện như vậy vận động chính xác, điều rất quan trọng đặc biệt là chúng ta áp dụng các quy tắc cho toàn bộ các cột, ví dụ =$A:$Avà =$B:$B.
Bạn rất có thể xem ví dụ về phong thái sử dụng thực tiễn trong hình sau đây để gia công nổi bật những cột giống nhau trong Cột E với F.
Công thức để gia công nổi bật những giá trị sinh hoạt trên hoặc bên dưới trung bình
Khi bạn thao tác làm việc với một số dữ liệu số, hàm AVERAGE () có thể hữu dụng để định dạng những ô có mức giá trị thấp rộng hoặc cao hơn mức vừa phải trong một cột.
Ví dụ: bạn cũng có thể sử dụng công thức =$E2” vào công thức: =$E2>AVERAGE($E$2:$E$8).
Ví dụ 1. Tìm quý hiếm gần nhất, bao gồm kết hợp chủ yếu xác
Trong ví dụ của bọn chúng ta, bọn họ sẽ tra cứu và ghi lại số sớm nhất với số không. Nếu tập tài liệu chứa một hoặc những số không, tất cả chúng sẽ tiến hành đánh dấu. Nếu không có 0, thì giá trị sớm nhất với nó, dương hoặc âm, sẽ được đánh dấu.
Trước hết, bạ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, các bạn sẽ có thể ẩn ô kia sau, giả dụ cần. Công thức kiếm tìm thấy số trong một hàng nhất định sớm nhất với số bạn chỉ định cùng trả về giá trị tuyệt vời của số đó (giá trị tuyệt vời nhất là số không có dấu âm):
=MIN(ABS(B2:D13-(0)))
Trong cách làm trên, B2: D13 là dải ô của chúng ta và 0 là số mà bạn có nhu cầu tìm kiếm phối kết hợp gần nhất. Ví dụ: nếu bạn đang kiếm tìm kiếm một giá chỉ trị sớm nhất với 5, bí quyết sẽ chuyển đổ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 phải nhấn Ctrl + Shift + Enter thay vị gõ Enter đơn giản để xong nó.
Và bây giờ, bạn tạo một quy tắc định dạng có đk với công thức sau, trong những số ấy B3 là ô trên cùng bên đề xuất trong phạm vi của bạn và $ C $ 2 trong ô bởi công thức mảng trên:
=OR(B3=0-$C$2,B3=0+$C$2)
Hãy chăm chú đến việc áp dụng tham chiếu hoàn hảo nhất trong add của ô có chứa bí quyết mảng ($C$2), chính vì ô này là hằng số. Ngoài ra, bạn phải thay rứa 0 thông qua số mà bạn muốn làm nổi bật phối kết hợp gần nhất. Ví dụ: nếu bọn họ muốn làm rất nổi bật giá trị gần nhất đến 5, công thức sẽ biến hóa thành:=OR(B3=5-$C$2,B3=5+$C$2)
Ví dụ 2. Làm trông rất nổi bật một quý giá gần nhất với mức giá trị độc nhất vô nhị định, dẫu vậy không khớp trả toàn
Trong trường hòa hợp bạn không thích làm trông rất nổi bật một kết hợp chính xác, bạn phải một phương pháp mảng không giống nhau sẽ tìm giá bán trị sớm nhất nhưng quăng quật qua kết hợp chính xác.
Ví dụ: bí quyết mảng sau tìm quý hiếm gần với 0 nhất trong phạm vi được chỉ định, nhưng bỏ qua những giá trị không, ví như có:
=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))
Hãy ghi nhớ nhấn Ctrl + Shift + Enter sau khi bạn gõ xong xuôi công thức mảng.
Công thức định dạng tất cả điều kiện y hệt như trong ví dụ trên:
=OR(B3=0-$C$2,B3=0+$C$2)
Tuy nhiên, vì bí quyết mảng của họ trong ô C2 vứt qua phối kết hợp chính xác, quy tắc định dạng có điều kiện sẽ vứt qua những giá trị không và làm rất nổi bật giá trị 0.003 là phối kết hợp gần nhất.
Nếu bạn có nhu cầu tìm giá chỉ trị sớm nhất với một vài khác vào bảng tính Excel của bạn, chỉ cần thay vậy “0” bởi số bạn muốn cả vào mảng và những công thức định dạng có điều kiện.
Tại sao định hình có điều kiện Excel của tớ không chuyển động đúng?
Nếu nguyên tắc định dạng gồm điều kiện của chúng ta không chuyển động như ý muốn đợi, mặc dù công thức hình như đúng, đừng bi đát bã! Rất rất có thể nó không hẳn là do một vài lỗi lạ trong Excel, nhưng vì vì một sai lầm nhỏ, không nhận biết ngay được. Vui lòng thử 6 cách khắc phục sự cố đơn giản và dễ dàng dưới đây cùng tôi có thể chắn các bạn sẽ nhận được công thức của bản thân mình để làm việc:
Sử dụng các địa chỉ ô tuyệt đối hoàn hảo và kha khá chính xác. Rất cạnh tranh để hoàn toàn có thể suy ra một cách thức chung sẽ chuyển động trong 100% ngôi trường hợp. Nhưng thường xuyên thì bạn sẽ sử dụng một cột tuyệt vời (với $) cùng hàng tương đối (không gồm $) trong những 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 là cái nào là đúng trong những trường phù hợp của bạn, bạn cũng có thể thử tất cả.
Xác minh phạm vi áp dụng. Kiểm tra xem luật lệ định dạng bao gồm điều kiện của doanh nghiệp có áp dụng cho đúng phạm vi của ô. Một nguyên tắc là – chọn tất cả các ô / hàng bạn muốn định dạng cơ mà không bao hàm các title cột. Viết phương pháp cho ô trên cùng bên trái. Trong các quy tắc định dạng gồm điều kiện, tham chiếu ô liên quan đến ô phía bên trên cùng phía trái trong phạm vi áp dụng. Vì vậy, luôn luôn viết bí quyết định dạng tất cả điều kiện của người tiêu dùng cho dòng đầu tiên với dữ liệu.Ví dụ: nếu dữ liệu của bạn bước đầu ở hàng 2, chúng ta gõ =A$2=10 sẽ lưu lại các ô có giá trị bằng 10 trong tất cả các hàng . Một không đúng lầm thông dụng là luôn luôn áp dụng một tham chiếu đến bậc nhất tiên (ví dụ =A$1=10). Hãy lưu giữ rằng, bạn tìm hiểu thêm hàng một trong các công thức chỉ lúc bảng của doanh nghiệp không tất cả tiêu đề cùng dữ liệu của bạn thực sự bước đầu trong mặt hàng 1. Dấu hiệu cụ thể nhất của trường hợp này là lúc công thức hoạt động, nhưng những định dạng giá trị không xuất hiện thêm đúng nơi đề xuất thiết.
Xem thêm: File excel xem tuổi làm nhà, cách tạo file xem tuổi làm nhà
Và cuối cùng, nếu bạn đã thử vớ cả các bước nhưng quy tắc định dạng bao gồm điều kiện của người tiêu dùng vẫn không chuyển động chính xác, hãy viết mang đến tôi một chiếc trong dìm xét và bọn họ sẽ nỗ lực tìm phát âm nó với mọi người trong nhà