Trong bài viết này, học tập Excel Online vẫn hướng dẫn giải 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à toàn bộ hàng dựa trên các giá trị các bạn chỉ định hoặc phụ thuộc 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ó đk dựa trên giá trị một ô, thay đổi màu sắc ô excel theo điều kiện
Công thức để so sánh những 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 các giá trị sống trên hoặc dưới trung bình
Công thức format có đk dựa trên quý hiếm 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 khẳng định trước của Excel chủ yếu nhằm mục đích định dạng các ô dựa vào 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 ô excel có điều kiện
Nếu bạn có nhu cầu áp dụng định hình có điều kiện dựa trên một ô khác 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 sử dụng công thức Excel.
Cách tạo thành quy tắc format có đk sử dụng công thức
Như vẫn đề 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, chức năng định dạng có đk nằm sống Home tab > Styles . Trong Excel 2003, bạn có thể tìm thấy nó dưới menu Format .
Vì vậy, bạn cấu hình thiết lập một quy tắc format có đk dựa bên trên một công thức theo phong cách này:
Chọn những ô mà bạn muốn định dạng. Bạn có 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 muốn áp dụng định dạng tất cả điều kiện của chính mình cho nhiều hàng.Mẹo: nếu như bạn dự định sẽ nhập thêm những dữ liệu trong tương lai và bạn muốn quy tắc định dạng bao gồm điều kiện tự động hóa áp dụng cho các mục nhập mới, chúng ta có thể:
biến đổi một dãy các ô qua 1 bảng ( Insert tab> Table ). Trong trường vừa lòng này, định dạng có đk sẽ được tự động hóa áp dụng cho toàn bộ các sản phẩm mới. Chọn một số hàng trống dưới dữ liệu của bạn, lấy 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…
vào New Formatting Rule window, lựa chọn Use a formula lớn 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… để 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 có điều kiện), hãy dấn F2 với sau đó dịch rời đến vị trí quan trọng trong cách làm bằng những phím mũi tên. Nếu khách hàng dùng mũi tên mà không nhấn F2 , một dãy sẽ được chèn vào phương pháp thay bởi chỉ di chuyển con trỏ chèn. Để thêm một tham chiếu ô nhất định vào công thức, thừa nhận F2 một đợt nữa và tiếp đến 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 đã hiểu phương pháp tạo và vận dụng định dạng có điều kiện 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, họ hãy liên tục và xem phương pháp sử dụng các công thức Excel khác nhau trong thực tế:
Công thức nhằm so sánh các giá trị (số và văn bản)
Như bạn đã biết emcanbaove.edu.vn Excel cung cấp một số ít những quy tắc sẵn dùng làm định dạng các ô có giá trị phệ hơn, bé dại hơn hoặc bằng giá trị mà các 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 bao gồm điều kiện các cột một mực hoặc cục bộ hàng dựa trên quý giá của một ô trong một cột khác. Vào trường hợp này, bạn sử dụng những công thức tương tự:
Hình bên dưới đây cho thấy thêm một lấy một ví dụ về Công thức khủng hơn, làm nổi bật các tên thành phầm trong cột A nếu con số hàng tồn kho (cột C) to hơn 0. Hãy để ý rằng phương pháp chỉ vận dụng cho cột A ($ A $ 2: $ A $ 8). Nhưng nếu khách hàng chọn toàn bộ bảng (trong trường vừa lòng này, $ A $ 2: $ E $ 8), vấn đề đó sẽ làm trông rất nổi bật các hàng dựa trên giá trị vào cột C.
Bằng biện pháp tương tự, chúng ta có thể tạo ra một quy tắc định hình có điều kiện để so sánh những giá trị của hai ô. Ví dụ:
=$A2$B2 – định dạng những ô hoặc các hàng trường hợp một giá trị trong cột A không hệt như trong cột B.
Như chúng ta thấy trong 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ý giá vă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 khách hàng dựa bên trên 2 hoặc các đ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 đk đều được đáp ứng | =AND($B2 |
Trong hình dưới đây, công ty chúng tôi sử dụng công thức =AND($C2>0, $D2=”Worldwide”) để biến đổ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 vận tải trên toàn nhân loại <Worldwide> (Cột D). Hãy để ý rằng bí quyết này vận động với các quý giá 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, tía 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ơ bản bạn áp dụng trong Excel. Bây giờ họ hãy chú ý một ví dụ phức hợp hơn mà lại thú vị rộng nhiều.
Định dạng có điều kiện cho ô trống và không trống
Tôi suy nghĩ mọi bạn đều biết làm nạm nào để định dạng những ô trống trống với không trống trong Excel – bạn chỉ cần tạo một luật lệ mới cho các ô ” “Format only cells that contain “ và lựa chọn một trong nhị ô 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 một mực nếu một ô tương ứng trong một cột không giống trống hoặc không trống? Trong trường hòa hợp này, bạn sẽ cần yêu cầu sử dụng những công thức Excel một đợt nữa:
Công thức cho khoảng tầm trống : =$B2=””– định dạng các ô / sản phẩm đã lựa chọn nếu ô tương ứng trong Cột B trống.
Công thức đến không khoảng tầm trống : =$B2””– định dạng những ô / mặt hàng đã chọn nếu một ô khớp ứng trong Cột B ko được nhằm trống.
Chú thích. Các bí quyết trên sẽ thao tác làm việc cho những ô “hiện ra” trống hoặc không trống. Nếu các 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à chúng ta không muốn các ô bởi 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ới không trống, tương ứng.
Và đấy là một ví dụ như 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ử các bạn có một cột (B) là ” Date of Sale ” với một cột khác (C) ” Delivery” “. Hai cột này chỉ có mức giá trị lúc hàng đang được chào bán và giao nhận. Vì vậy, bạn có nhu cầu toàn cỗ hàng gửi sang màu da cam khi chúng ta đã bán hàng; Và khi 1 vật phẩm được giao, một hàng tương xứng sẽ đưa sang blue color lá cây. Để đạt đượ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 những công thức sau:
hàng cam (một ô vào cột B không rỗng): =$B2”” những hàng blue color lục (các ô vào cột B với 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 thiết bị hai lên trên thuộc và chọn hộp kiểm Stop if true bên cạnh luật lệ này:
Trong ngôi trường hợp rõ ràng này, tùy chọn “Stop if true ” thực thụ không đề nghị thiết, cùng quy tắc sẽ thao tác làm việc bất nói có hay là không có nó. Bạn hoàn toàn có thể đánh lốt vào hộp này chỉ như một biện pháp phòng ngừa bổ sung, vào 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 chợt với bất kỳ trường vừa lòng nào hiện có.
Công thức Excel thao tác với các giá trị văn bản
Nếu bạn có nhu cầu áp dụng định dạng bao gồm đ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 đựng một từ tốt nhất định, chúng ta có thể sử dụng một công thức đơn giản 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, 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 , bạn sẽ cần một hàm Excel khác: SEARCH () Bạn áp dụng nó theo cách này:
=SEARCH(“Worldwide”, $D2)>0– định dạng những ô hoặc hàng ví như một ô tương ứng trong cột D đựng từ ” Worldwide “. Công thức này đang tìm tất cả các ô đó, bất kể vị trí của văn bản tìm kiếm bên trong một ô, ví như ” “Ships Worldwide”, “Worldwide, except for…” ” vv
=SEARCH(“Worldwide”, $D2)>1 – đổ bóng những ô hoặc hàng nếu văn bản của ô ban đầ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 người sử dụng là định dạng các ô có giá trị trùng lặp, chúng ta có dùng những nguyên tắc được khẳng định trước gồm sẵn dưới Conditional formatting > Highlight Cells Rules > Duplicate Value.
Tuy nhiên, trong một số trường hợp, tài liệu sẽ xuất sắc hơn nếu như bạn tô màu những cột đã lựa 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 thích hợp này, bạn sẽ cần phải thực hiện lại bí quyết định dạng đk 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 chuẩn duy nhất.
Làm nổi bật dữ liệu đụng hàng 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ị giống nhau trong phạm vi được hướng đẫn trong Cột A (A2: A10 vào trường hợp này), bao hàm cả lần xuất hiện đầu tiên.
Nếu chúng ta chọn áp dụng quy tắc cho toàn cục bảng, toàn thể hàng sẽ tiến hành định dạng, như bạn thấy trong dưới đây. Tôi vẫn quyết định chuyển đổi màu fonts chữ.
Để bỏ qua mất lần xuất hiện thứ nhất và chỉ làm nổi bật các quý hiếm 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 chúng ta chỉ muốn làm rất nổi bật các giá trị trùng lặp trên các hàng liên tiếp, chúng ta có thể thực hiện câu hỏi này theo cách sau. Phương pháp này vận động cho bất kỳ loại tài liệu nào: số, giá trị 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 sao, không lựa chọn tiêu đề cột . Tạo nên một quy tắc định dạng 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 tiếp theo, giả dụ có. Phép tắc 2 (xanh lá cây): =$A2=$A3– làm khá 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 có nhu cầu kiểm tra, $ A1 là title cột, $ A2 là ô trước 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 đặc biệt là phép tắc 1 làm khá nổi bật lần tái diễn thứ 2 và tất cả 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 nhiều người đang sử dụng nhị màu không giống nhau.
Nếu bạn có nhu cầu áp dụng định dạng có đk khi quý hiếm trùng xảy ra trong hai cột trở lên, các bạn sẽ cần buộc phải thêm một cột phụ vào bảng của mình, trong số ấy bạn nối những giá trị từ các cột chính bằng một công thức đơn giản và dễ dàng như cầm cố này =A2&B2. Sau kia bạn vận dụng một quy tắc bằng cách sử dụng thay đổi thể của cách làm COUNTIF cho những phần lặp (có hoặc không xảy ra lần mở ra 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, 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òa hợp đó, các bạn sẽ không đề nghị cột trợ giúp.
Trong 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 thành một quy tắc với cách làm 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 có lần mở ra đầu tiên , thực hiện 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 gặp mặt nhất trong Excel là kiểm soát 2 cột cho những giá trị trùng lặp – tức là tìm và lưu lại các quý hiếm tồn tại trong cả nhì cột. Để làm đ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 gồm điều kiện cho mỗi cột cùng với sự phối 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 đặc biệt quan trọng là các bạn áp dụng những quy tắc cho tổng thể 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 tiễn trong hình sau đây để làm nổi bật các cột đụng hàng trong Cột E và F.
Công thức để triển khai nổi bật những giá trị sống 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ể có ích để định dạng những ô có mức giá trị thấp hơn hoặc cao hơn mức trung bình trong một cột.
Ví dụ: chúng ta 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 giá trị gần nhất, bao hàm kết hợp chính xác
Trong lấy ví dụ của chúng ta, họ sẽ tìm và đánh dấu số sớm nhất với số không. Nếu tập tài liệu chứa một hoặc các số không, tất cả chúng sẽ được đánh dấu. Nếu không tồn tại 0, thì giá 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 cách làm sau vào ngẫu nhiên ô trống nào trong bảng tính của bạn, các bạn sẽ có thể ẩn ô đó sau, ví như cần. Công thức tìm thấy số trong một dãy nhất định sớm nhất với số bạn chỉ định với trả về giá trị tuyệt vời của số kia (giá trị hoàn hảo là số không tồn tại dấu âm):
=MIN(ABS(B2:D13-(0)))
Trong phương pháp 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 hợp gần nhất. Ví dụ: nếu bạn đang search kiếm một giá bán trị gần nhất với 5, công thức 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ày vậy bạn phải nhấn Ctrl + Shift + Enter thay vì gõ Enter đơn giản để ngừng nó.
Và bây giờ, các bạn tạo một quy tắc format có đk với bí quyết sau, trong đó B3 là ô trên thuộc bên bắt buộc trong phạm vi của người sử dụng và $ C $ 2 vào ô bằng 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 sử dụng tham chiếu tuyệt vời và hoàn hảo nhất trong add của ô bao gồm 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 vậy 0 thông qua số mà bạn muốn 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 nổi bật giá trị gần nhất đến 5, cách làm sẽ thay đổi thành:=OR(B3=5-$C$2,B3=5+$C$2)
Ví dụ 2. Làm khá nổi bật một cực hiếm gần nhất với cái giá trị nhất định, nhưng lại không khớp hoàn toàn
Trong trường đúng theo 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ác nhau sẽ tìm giá chỉ trị gần nhất nhưng bỏ qua kết hợp chính xác.
Ví dụ: phương pháp mảng sau tìm giá trị gần cùng với 0 độc nhất trong phạm vi được chỉ định, nhưng quăng quật qua các giá trị không, giả dụ có:
=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))
Hãy lưu giữ 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 có điều kiện y như trong lấy một 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 họ trong ô C2 quăng quật qua kết hợp chính xác, quy tắc định dạng có điều kiện sẽ quăng quật qua các giá trị không với 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 muốn tìm giá chỉ trị gần nhất với một số khác vào bảng tính Excel của bạn, chỉ việc thay núm “0” bởi số bạn có nhu cầu cả trong mảng và các công thức định dạng bao gồm điều kiện.
Tại sao format có điều kiện Excel của mình không chuyển động đúng?
Nếu luật lệ định dạng tất cả điều kiện của khách hàng không hoạt động như mong muốn đợi, tuy nhiên công thức bên cạnh đó đúng, đừng bi thiết bã! Rất có thể nó không phải là do một vài lỗi kỳ lạ trong Excel, nhưng vì vày một sai lạc nhỏ, không phâ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 bạn sẽ nhận được công thức của mình để làm cho việc:
Sử dụng các địa chỉ cửa hàng ô tuyệt đối hoàn hảo và kha khá chính xác. Rất khó để hoàn toàn có thể suy ra một hiệ tượng chung sẽ hoạt động trong 100% ngôi trường hợp. Nhưng hay thì các bạn sẽ sử dụng một cột tuyệt đối hoàn hảo (với $) và hàng kha khá (không tất cả $) trong những tham chiếu ô của bạn, ví dụ =$A1>1.Hãy lưu giữ rằng những công thức =A1=1, =$A$1=1và =A$1=1 sẽ cho tác dụng khác nhau. Nếu bạn không chắc chắn là cái nào là đúng trong các trường hợp của bạn, bạn có thể thử tất cả.
Xác minh phạm vi áp dụng. Kiểm tra xem nguyên tắc định dạng có điều kiện của chúng ta có vận 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 có nhu cầu định dạng nhưng mà không bao gồm các title cột. Viết cách làm cho ô bên trên cùng mặt 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 ô phía bên trên cùng phía bên trái trong phạm vi áp dụng. Vì vậy, luôn luôn luôn viết cách làm định dạng bao gồm điều kiện của chúng ta cho dòng đầu tiên với dữ liệu.Ví dụ: nếu dữ liệu của bạn bắt đầu ở mặt hàng 2, chúng ta gõ =A$2=10 sẽ khắc ghi các ô có giá trị bằng 10 trong tất cả các hàng . Một không nên lầm thịnh hành là luôn luôn sử dụng một tham chiếu đến số 1 tiên (ví dụ =A$1=10). Hãy ghi nhớ rằng, bạn xem thêm hàng 1 trong các công thức chỉ khi bảng của người sử dụng không có tiêu đề cùng dữ liệu của doanh nghiệp thực sự ban đầu trong sản phẩm 1. Vết hiệu rõ ràng nhất của trường vừa lòng này là lúc công thức hoạt động, nhưng những định dạng quý hiếm không lộ diện đúng nơi phải thiết.
Xem thêm: Cách tìm những trang đã thích trên facebook bằng điện thoại, 2 cách xem các trang đã thích trên facebook
Và cuối cùng, nếu khách hàng đã thử tất cả các bước nhưng quy tắc định dạng bao gồm điều kiện của công ty vẫn không chuyển động chính xác, hãy viết mang đến tôi một dòng trong nhấn xét và bọn họ sẽ cố gắng tìm hiểu nó cùng mọi người trong nhà