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.
Des de l'Excel 2007, Excel ha tingut diverses opcions integrades per a les condicions d'ús comú, com trobar números que siguin superiors o inferiors a un determinat valor o que trobin números superiors o inferiors al 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.
Aplicació de diverses regles
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.
Exemple: trobar dades que superin el 25% i 50% s'incrementin amb el format condicional
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 de les cel·les A2 a A5 són més grans que el valor corresponent en B2 a B5 en més d'un 25%.
- la segona norma comprova si les mateixes dades a A2: A5 superen el valor corresponent en B2: B5 en més d'un 50%.
Com es pot veure a la imatge anterior, si alguna de les condicions anteriors és certa, el color de fons de la cel·la o 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 blau.
- si la diferència és superior al 50%, el color de fons de la cel·la canviarà a vermell.
Les regles utilitzades per dur a terme aquesta tasca,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%s'introduirà utilitzant el quadre de diàleg Regla de formatació condicional nou format .
Introduir les dades del tutorial
- Introduïu les dades a les cel·les A1 a C5 tal com es veu a la imatge de dalt
Nota: el pas 3 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.
Configuració de les regles de formatació condicionals
Com s'ha esmentat, s'introdueixen les regles de format condicional que comproven les dues condicions utilitzant el quadre de diàleg Regla de formatació condicional de format condicional.
Establint el format condicional per trobar un augment superior al 25%
- Ressalteu les cel·les B2 a B5 al full de treball.
- Feu clic a la pestanya Inici de la cinta.
- Feu clic a la icona de format condicional de la cinta per obrir el menú desplegable.
- Trieu Nova regla per obrir el quadre de diàleg Regla de format nou que es veu a la imatge de dalt.
- A la part superior del quadre de diàleg, feu clic a l'última opció: feu servir una fórmula per determinar quines cel·les formatear.
- A la part inferior del quadre de diàleg, feu clic als valors Format on aquesta fórmula és certa: línia.
- Escriviu la fórmula : = (A2-B2) / A2> 25% en l'espai proporcionat
- Feu clic al botó Format per obrir el quadre de diàleg Format de cel·les.
- En aquest quadre de diàleg, feu clic a la pestanya Ompli i trieu un color de farciment blau.
- Feu clic a D' acord dues vegades per tancar els quadres de diàleg i tornar al full de treball.
- En aquest punt, el color de fons de les cel·les B3 i B5 ha de ser de color blau.
Establint el format condicional per trobar un augment superior al 50%
- Amb les cel·les B2 a B5 encara seleccionades, repetiu els passos 1 a 6 anteriors.
- Escriviu la fórmula: = (A2-B2) / A2> 50% en l'espai proporcionat.
- Feu clic al botó Format per obrir el quadre de diàleg Format de cel·les.
- Feu clic a la pestanya " Enviament " i seleccioneu un color de farciment vermell.
- Feu clic a D' acord dues vegades per tancar els quadres de diàleg i tornar al full de treball .
- El color de fons de la cel·la B3 encara hauria de ser blau indicant que la diferència de percentatge entre els números de les cel·les A3 i B3 és superior al 25%, però inferior o igual al 50%.
- El color de fons de la cel·la B5 ha de canviar a vermell que indica que la diferència de percentatge entre els números de les cel·les A5 i B5 és superior al 50%.
Comprovació de les regles de format condicional
Càlcul de la diferència%
Per comprovar que les regles de format condicional introduïdes són correctes, podem introduir fórmules a les cel·les C2: C5 que calcularan la diferència de percentatge exacta entre els nombres dels intervals A2: A5 i B2: B5.
- Feu clic a la cel·la C2 per fer-la la cel·la activa.
- Escriviu la fórmula = (A2-B2) / A2 i premeu la tecla Enter al teclat.
- La resposta del 10% hauria d'aparèixer a la cel·la C2, indicant que el número de la cel·la A2 és del 10% més gran que el número de la cel·la B2.
- Pot ser necessari canviar el format a la cel·la C2 per mostrar la resposta com a percentatge.
- Utilitzeu l' identificador de farciment per copiar la fórmula de la cel·la C2 a les cel·les C3 a C5.
- Les respostes per a les cel·les C3 a C5 han de ser: 30%, 25% i 60%.
- Les respostes en aquestes cel·les mostren que les regles de format condicional creades són correctes ja que la diferència entre les cel·les A3 i B3 és superior al 25% i la diferència entre les cel·les A5 i B5 és superior al 50%.
- La cel·la B4 no ha canviat de color, ja que la diferència entre les cel·les A4 i B4 equival al 25%, i la nostra regla de format condicional especifica que es requereix un percentatge superior al 25% perquè el color del fons canviï en blau.
Ordre de preeminència per a les regles de format condicional
Aplicació de regles de format condicional conflictives
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% entre dues cel·les), la primera regla (la diferència de valor és superior al 25%) també és cert.
Ordre de precedència d'Excel
Atès que una cel·la no pot tenir un fons vermell i blau 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 (= (A2-B2) / A2> 50%) és superior 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, utilitzeu els botons de fletxa amunt i avall del 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 (= (A2-B2) / A2> 25%) formateaba el rang de cel·les B2: B5 amb una vora blava en comptes d'un color de fons blau, les dues regles de format condicional no entrarien en conflicte Ambdós formats es poden aplicar sense interferir amb l'altre.
Com a resultat, la cel·la B5 tindria una vora blava i un color de fons vermell, ja que la diferència entre els números de les 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 un color de fons groc es va aplicar inicialment a les cel·les B2 a B5 en l'exemple, una vegada que es van afegir les regles de format condicional, només les cel·les B2 i B4 quedarien grogues.
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 blau i vermell, respectivament.