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