Si afegiu format condicional a Excel, podeu aplicar diferents opcions de format a una cel·la o rang de cel·les que compleixin les condicions específiques que hàgiu establert. Les opcions de format només s'apliquen quan les cel·les seleccionades compleixen aquestes condicions.
Les opcions de format que es poden aplicar inclouen canvis de tipus de lletra i de fons, estils de lletra, límits de cel·les i afegint el format de número a les dades.
Excel ha tingut diverses opcions integrades per a les condicions d'ús comú, com ara trobar números que siguin superiors o inferiors a un cert valor o que trobin números que estan per sobre o per sota del valor mitjà.
A més d'aquestes opcions preestablertes, també és possible crear regles de format condicional personalitzades utilitzant fórmules d'Excel per provar condicions específiques de l'usuari.
01 de 04Aplicació de diverses regles en Excel
Es poden aplicar més d'una regla a les mateixes dades per provar diferents condicions. Per exemple, les dades de pressupost poden tenir condicions establertes que apliquin els canvis de format quan es gasten determinats nivells, com ara el 50%, el 75% i el 100%, del pressupost total.
En aquestes circumstàncies, Excel primer determina si les diverses regles entren en conflicte i, en aquest cas, el programa segueix un ordre establert de prioritat per determinar quina regla de format condicional s'aplica a les dades.
S'estan cercant dades que superin el 25% i el 50%
En el següent exemple, s'aplicaran dues regles de format condicional personalitzades al rang de cel·les B2 a B5.
- La primera regla comprova si les dades a cel·les A2 a A5 és superior al valor corresponent a B2 a B5 en més del 25%.
- La segona regla comprova si les mateixes dades a A2: A5 excedeix el valor corresponent a B2: B5 en més del 50%.
Com es pot veure a la imatge de dalt, si alguna de les condicions anteriors és certa, el color de fons de la cel·la o les cel·les del rang B1: B4 canviarà.
- Per a les dades on la diferència és superior al 25%, el color de fons de la cel·la canviarà a verd.
- Si la diferència és superior al 50%, el color de fons de la cel·la canviarà a vermell.
Les regles que s'utilitzen per realitzar aquesta tasca s'introdueixen mitjançant el format condicional Nova norma de formatació Caixa de diàleg. Comenceu per introduir les dades d'exemple a les cel·les A1 a C5 tal com es veu a la imatge de dalt
A la tercer pas del tutorial afegirà fórmules a les cel·les C2: C4 que mostren la diferència de percentatge exacta entre els valors de les cel·les A2: A5 i B2: B5 per comprovar la precisió de les regles de format condicional.
02 de 04Configuració de regles de format condicional
Com s'ha esmentat, s'introduiran les regles de format condicional que controlen les dues condicions utilitzant el format condicional Nova norma de formatació Caixa de diàleg. Començarem per configurar el format personalitzat condicional:
Formatació condicional per trobar un augment superior al 25 per cent
= (A2-B2) / A2> 25%
- Ressaltar cel·les B2 a B5 en el full de càlcul.
- Feu clic al Pàgina d'inici del cinta.
- Feu clic al Formatació condicional icona a la cinta per obrir el menú desplegable.
- Trieu Nova regla per obrir el Nova norma de formatació Caixa de diàleg.
- De la Estil: quadre, seleccioneu el Clàssic opció.
- Al segon menú desplegable, feu clic a l'última opcióUtilitzeu una fórmula per determinar quines cel·les formatear.
- Escriviu el fórmula proporcionat anteriorment a l'espai proporcionat.
- Feu clic al Format amb: desplegable i seleccioneu el Rellotge lluminós amb text vermell fosc opció.
- Feu clic D'acord per tancar els quadres de diàleg i tornar al full de treball.
- En aquest punt, el color de fons de cel·les B3 i B5 hauria de ser verd.
Formatació condicional per trobar més d'un 50 per cent d'augment
= (A2-B2) / A2> 50%
Ressaltar cel·les B2 a B5 en el full de càlcul.
- Feu clic al Pàgina d'inici del cinta.
- Feu clic al Formatació condicional icona a la cinta per obrir el menú desplegable.
- Trieu Nova regla per obrir el Nova norma de formatació Caixa de diàleg.
- De la Estil: quadre, seleccioneu el Clàssic opció.
- Al segon menú desplegable, feu clic a l'última opcióUtilitzeu una fórmula per determinar quines cel·les formatear.
- Escriviu el fórmula proporcionat anteriorment a l'espai proporcionat.
- Feu clic al Format amb: desplegable i seleccioneu el Rellotge verd clar amb text verd fosc opció.
- Feu clic D'acord per tancar els quadres de diàleg i tornar al full de treball.
El color de fons de cel·la B3 encara hauria de ser verd indicant que la diferència de percentatge entre els nombres a cel·les A3 i B3 és superior al 25 per cent, però inferior o igual al 50 per cent. El color de fons de cel·la B5 hauria de canviar a vermell que indica que la diferència de percentatge entre els números a cel·les A5 i B5 és superior al 50 per cent.
03 de 04Comprovació de les regles de format condicional
Per comprovar que les regles de format condicional introduïdes són correctes, podem introduir fórmules en cel·les C2: C5 que calcularà la diferència de percentatge exacta entre els nombres dels intervals A2: A5 i B2: B5.
= (A2-B2) / A2
- Fer clic a cel·la C2 per convertir-lo en la cel·la activa.
- Escriviu la fórmula anterior i premeu la tecla Entra tecla al teclat.
- La resposta hauria d'aparèixer el 10% cel·la C2, indicant que el número en cel·la A2 és un 10% més gran que el número a cel·la B2.
- Pot ser necessari canviar el format a cel·la C2 per mostrar la resposta com un percentatge.
- Utilitzar el omplir el mànec per copiar la fórmula de cel·la C2 a cel·les C3 a C5.
- Les respostes per a cel·les C3 a C5 hauria de ser del 30%, del 25% i del 60%.
Les respostes en aquestes cel·les mostren que les regles de format condicional creades són correctes, ja que la diferència entre cel·les A3 i B3 és superior al 25 per cent i la diferència entre cel·les A5 i B5 és superior al 50 per cent.
Cèl·lula B4 no va canviar de color perquè la diferència entre cel·les A4 i B4 equival al 25 per cent, i la nostra regla de format condicional especifica que es requereix un percentatge superior al 25 per cent perquè el color del fons canviï a verd.
04 de 04Ordre de precedència per al format condicional
Quan s'apliquen diverses regles al mateix rang de dades, Excel primer determina si les regles entren en conflicte. Les regles conflictives són aquelles on no es poden aplicar les opcions de format de cada regla per a les mateixes dades.
En l'exemple utilitzat en aquest tutorial, el conflicte de regles ja que ambdues regles utilitzen la mateixa opció de format: la de canviar el color de la cel·la de fons.
En la situació en què la segona regla és certa (la diferència de valor és superior al 50 per cent entre dues cel·les), la primera regla (la diferència de valor és superior al 25 per cent) també és certa.
Ordre de precedència d'Excel
Atès que una cel·la no pot tenir un fons vermell i verd alhora, Excel necessita saber quina regla de format condicional hauria d'aplicar.
La regla que s'aplica està determinada per l'ordre de prioritat d'Excel, que estableix que la regla que és més alta a la llista del quadre de diàleg Administrador de normes de format condicional té prioritat.
Com es pot veure a la imatge anterior, la segona regla utilitzada en aquest tutorial és més alta a la llista i, per tant, té prioritat sobre la primera regla. Com a resultat, el color de fons de la cel·la B5 es canvia a color vermell.
De manera predeterminada, les noves regles s'afegeixen a la part superior de la llista i, per tant, tenen una major prioritat. Per canviar l 'ordre de precedència, useu el Amunt i avall botons de fletxa al quadre de diàleg tal com s'identifica a la imatge de dalt.
Aplicació de regles no conflictives
Si dues o més regles de format condicional no entren en conflicte, s'apliquen ambdues quan la condició que cada regla prova és vertader.
Si la primera regla de format condicional en el nostre exemple formatava el rang de cel·les B2: B5 amb una vora verda en comptes d'un color de fons verd, les dues regles de format condicional no entraran en conflicte ja que els dos formats es poden aplicar sense interferir amb l'altre.
Com a resultat, cel·la B5 tindria una vora verda i un color de fons vermell, ja que la diferència entre els nombres en cel·les A5 i B5 és superior al 25 i al 50 per cent.
Formatació condicional versus formatació regular
En el cas dels conflictes entre les regles de format condicional i les opcions de format manualment aplicades, la regla de format condicional sempre té prioritat i s'aplicarà en comptes de les opcions de format afegides manualment.
Si es va aplicar inicialment un color de fons groc a cel·les B2 a B5 en l'exemple, una vegada que s'han afegit les regles de format condicional, només cel·les B2 i B4 quedaria groc. Com que les regles de format condicional introduïdes s'apliquen a les cel·les B3 i B5, els seus colors de fons canviaran de groc a verd i vermell, respectivament.
