Afegir un format condicional a Excel us permet aplicar diferents opcions de format a una cel·la o un rang de cel·les que compleixin les condicions específiques que hàgiu establert. Establir aquestes condicions pot ajudar a organitzar el full de càlcul i facilitar l'escaneig. Les opcions de format que podeu utilitzar inclouen canvis de font i de color de fons, estils de lletra, vores de cel·la i afegir format de nombre a les dades.
Excel té opcions integrades per a condicions d'ús habitual, com ara trobar números que siguin més grans o inferiors a un valor determinat o trobar números que estiguin per sobre o per sota del valor mitjà. A més d'aquestes opcions predefinides, també podeu crear regles de format condicional personalitzades mitjançant fórmules d'Excel.
Aquestes instruccions s'apliquen a Excel 2019, 2016, 2013, 2010 i Excel per a Microsoft 365.
Aplicació de diverses condicions a Excel
Podeu aplicar més d'una regla a les mateixes dades per provar condicions diferents. Per exemple, les dades del pressupost poden tenir condicions establertes que apliquen canvis de format quan s'assoleixen determinats nivells de despesa, com ara el 50%, el 75% i el 100%, del pressupost total.
En aquestes circumstàncies, Excel determina primer si les diverses regles entren en conflicte i, si és així, el programa segueix un ordre de precedència establert per determinar quina regla de format condicional s'ha d'aplicar a les dades.
Trobant dades que superin el 25% i els augments del 50%
A l'exemple següent, s'aplicaran dues regles de format condicional personalitzades a l'interval de cel·les B2 a B5.
- La primera regla comprova si les dades de cel·les A2:A5 són superiors al valor corresponent de B2:B5 per més del 25%.
- La segona regla comprova si les mateixes dades a A2:A5 superen el valor corresponent a B2:B5 en més d'un 50%.
Com es pot veure a la imatge de d alt, si alguna de les condicions anteriors és certa, el color de fons de la cel·la o cel·les de l'interval 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 utilitzades per dur a terme aquesta tasca s'introduiran mitjançant el quadre de diàleg Nova regla de format. Comenceu introduint les dades de mostra a les cel·les A1 a C5 com es veu a la imatge de d alt.
A la part final del tutorial afegirem fórmules a les cel·les C2:C4 que mostren el percentatge exacte de diferència entre els valors de les cel·les A2:A5 i B2:B5; això ens permetrà comprovar l'exactitud de les regles de format condicional.
Configuració de regles de format condicional
Primer, aplicarem el format condicional per trobar un augment significatiu del 25 per cent o més.
La funció tindrà aquest aspecte:
=(A2-B2)/A2>25%
- Res alta cel·les B2 a B5 al full de treball.
- Feu clic a la pestanya Inici de la cinta.
- Feu clic a la icona Format condicional a la cinta per obrir el menú desplegable.
-
Trieu Nova regla per obrir el quadre de diàleg Nova regla de format.
- A sota de Seleccioneu un tipus de regla, feu clic a l'última opció: Utilitzeu una fórmula per determinar quines cel·les voleu formatar.
- Escriviu la fórmula indicada més amunt a l'espai de sota Format els valors on aquesta fórmula sigui certa:
- Feu clic al botó Format per obrir el quadre de diàleg. Feu clic a la pestanya Emplenar i trieu un color.
- Feu clic a D'acord per tancar els quadres de diàleg i tornar al full de treball.
- El color de fons de cel·les B3 i B5 hauria de canviar al color que heu seleccionat.
Ara, aplicarem el format condicional per trobar un augment del 50 per cent o més. La fórmula tindrà aquest aspecte:
- Repetiu els cinc primers passos anteriors.
-
Escriviu la fórmula proporcionada més amunt a l'espai de sota Format els valors on aquesta fórmula sigui certa:
- Feu clic al botó Format per obrir el quadre de diàleg. Feu clic a la pestanya Emplenar i trieu un color diferent del que vau fer al conjunt de passos anterior.
- Feu clic a D'acord per tancar els quadres de diàleg i tornar al full de treball.
El color de fons de cel·la B3 hauria de romandre igual, indicant que el percentatge de diferència entre els números de les cel·les A3 iB3 é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 al nou color que heu seleccionat, indicant que el percentatge de diferència entre els números de cel·les A5 i B5 és superior al 50 per cent.
Comprovació de les regles de format condicional
Per verificar que les regles de format condicional introduïdes són correctes, podem introduir fórmules a les cel·les C2:C5 que calcularan el percentatge exacte de diferència entre els nombres dels intervalsA2:A5 i B2:B5.
La fórmula de la cel·la C2 té aquest aspecte:
=(A2-B2)/A2
- Feu clic a cel·la C2 per convertir-la en la cel·la activa.
- Escriviu la fórmula anterior i premeu la tecla Enter al teclat.
- La resposta 10% hauria d'aparèixer a la cel·la C2, indicant que el nombre de la cel·la A2 és un 10% més gran que el nombre de cel·la B2.
- Potser calgui canviar el format a cel·la C2 per mostrar la resposta com a percentatge.
- Utilitzeu el mànec d'emplenament per copiar la fórmula de cel·la C2 a cel·les C3 a C5.
- Les respostes per a cel·les C3 a C5 haurien de ser del 30%, el 25% i el 60%.
Les respostes d'aquestes cel·les mostren que les regles de format condicional són precises, ja que la diferència entre cel·les A3 i B3 és superior a 25 per cent, i la diferència entre cel·les A5 i B5 és superior al 50 per cent.
Cel·la B4 no ha canviat de color perquè la diferència entre les cel·les A4 i B4 és igual 25 per cent, i la nostra regla de format condicional especificava que calia un percentatge superior al 25 per cent perquè canviés el color de fons.
Ordre de preferència per al format condicional
Quan apliqueu diverses regles al mateix rang de dades, Excel determina primer si les regles entren en conflicte. Les regles conflictives són aquelles en què les opcions de format no es poden aplicar ambdues a les mateixes dades.
Al nostre exemple, les regles entren en conflicte, ja que totes dues utilitzen la mateixa opció de format: canviant 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.
Com que una cel·la no pot tenir dos fons de color diferents alhora, Excel ha de saber quina regla de format condicional s'ha d'aplicar.
L'ordre de precedència d'Excel indica que la regla que és més alta a la llista del quadre de diàleg Gestor de regles de format condicional s'aplica primer.
Com es mostra a la imatge de d alt, la segona regla utilitzada en aquest tutorial és més alta a la llista i, per tant, té prioritat sobre la primera. Com a resultat, el color de fons de cel·la B5 és verd.
Per defecte, les regles noves van al capdamunt de la llista; per canviar l'ordre, utilitzeu els botons de fletxa Amunt i avall del quadre de diàleg.
Aplicació de regles no conflictives
Si dues o més regles de format condicional no entren en conflicte, totes dues s'apliquen quan es compleix la condició que cada regla està provant.
Si la primera regla de format condicional del nostre exemple formatés l'interval de cel·les B2:B5 amb una vora taronja en lloc d'un color de fons taronja, les dues regles de format condicional no ho farien conflicte, ja que ambdós formats es poden aplicar sense interferir amb l' altre.
Format condicional vs. format normal
En el cas de conflictes entre les regles de format condicional i les opcions de format aplicades manualment, la regla de format condicional sempre té prioritat i s'aplicarà en lloc de qualsevol opció de format afegida manualment.
