Com utilitzar regles de format condicional personalitzades per a dates en Excel

Si afegiu un format condicional a una cel·la a Excel, podeu aplicar diferents opcions de format, com ara el color, quan les dades d'aquesta cel·la compleixen les condicions que heu establert.

Per facilitar l'ús del format condicional, hi ha disponibles opcions prefixades que cobreixen situacions d'ús comú, com ara:

• Dates
• Dades duplicades
• Valors superiors o inferiors al valor mitjà d'un rang de cel·les

En el cas de les dates, les opcions prefixades permeten consultar les vostres dades per dates properes a la data actual, com ara ahir, demà, la setmana passada o el mes següent.

Si voleu consultar dates que no coincideixin amb les opcions de la llista, podeu personalitzar el format condicional afegint la vostra pròpia fórmula utilitzant una o més de les funcions de dates d'Excel.

01 de 06

S'estan comprovant les dates 30, 60 i 90 dies anteriors

La personalització del format condicional mitjançant fórmules es fa establint una nova regla que segueix l'Excel a l'hora d'avaluar les dades d'una cel·la.

L'exemple pas a pas aquí estableix tres noves regles de format condicional que comprovaran si les dates introduïdes en un rang seleccionat de cel·les passen 30 dies, passats 60 dies o passats 90 dies.

Les fórmules utilitzades en aquestes regles resten un cert nombre de dies a partir de la data actual a les cel·les C1 a C4.

La data actual es calcula mitjançant la funció HORA .

Perquè aquest tutorial funcioni, heu d'introduir dates que queden dins dels paràmetres anteriors.

Nota : Excel aplica el format condicional en l'ordre, de dalt a baix, que les regles s'enumeren al quadre de diàleg Administrador de normes de format condicional tal com es veu a la imatge de dalt.

Tot i que es poden aplicar diverses regles a algunes cel·les, la primera regla que compleix la condició s'aplica a les cel·les.

02 de 06

S'estan comprovant les dates de 30 dies anteriors

1. Ressalteu les cel·les C1 a C4 per seleccionar-les. Aquest és el rang al qual aplicarem les regles de format condicional
2. Feu clic a la pestanya Inici del menú de la cinta .
3. Feu clic a la icona de Format condicional per obrir el menú desplegable.
4. Seleccioneu l'opció Nova regla . Això obre el quadre de diàleg Nova normativa de formatació.
5. Feu clic a l' ús d'una fórmula per determinar quines cel·les formateu l' opció.
6. Introduïu la fórmula següent al quadre que hi ha a sota dels valors de format on aquest valor és l' opció veritable a la part inferior del quadre de diàleg:
   = HOY () - C1> 30
   Aquesta fórmula verifica si les dates de les cel·les C1 a C4 passen més de 30 dies
7. Feu clic al botó Format per obrir el quadre de diàleg Format cel·les.
8. Feu clic a la pestanya " Enllaça " per veure les opcions de color de farciment de fons.
9. Seleccioneu un color de farciment de fons per coincidir amb l'exemple d'aquest tutorial, seleccioneu verd clar.
10. Feu clic a la pestanya Font per veure les opcions del format de lletra
11. A la secció Color, estableixi el color de la font en blanc per a que coincideixi amb aquest tutorial.
12. Feu clic a D' acord dues vegades per tancar el quadre de diàleg i tornar al full de treball.
13. El color de fons de les cel·les C1 a C4 canviarà al color de farciment escollit, encara que no hi hagi dades a les cel·les.

03 de 06

Addició d'una regla per a dates més de 60 dies en venciment

Ús de l'opció Gestionar regles

En lloc de repetir tots els passos anteriors per afegir les dues regles següents, farem ús de l'opció Regles de gestió que ens permetrà afegir les regles addicionals alhora.

1. Ressalteu les cel·les C1 a C4, si cal.
2. Feu clic a la pestanya Inici del menú de la cinta.
3. Feu clic a la icona de Format condicional per obrir el menú desplegable.
4. Trieu l'opció Administrar regles per obrir el quadre de diàleg Administrador de normes de format condicional.
5. Feu clic a l'opció Nova regla a l'extrem superior esquerre del quadre de diàleg
6. Feu clic a Utilitza una fórmula per determinar quines cel·les formateu l' opció de la llista a la part superior del quadre de diàleg.
7. Introduïu la fórmula següent al quadre que hi ha a sota dels valors de format on aquest valor és l' opció veritable a la part inferior del quadre de diàleg :
   = HOY () - C1> 60

   Aquesta fórmula verifica si les dates de les cel·les C1 a C4 són superiors a 60 dies.

8. Feu clic al botó Format per obrir el quadre de diàleg Format cel·les.
9. Feu clic a la pestanya " Enllaça " per veure les opcions de color de farciment de fons.
10. Seleccioneu un color de farciment de fons; Per coincidir amb l'exemple d'aquest tutorial, seleccioneu groc.
11. Feu clic a D' acord dues vegades per tancar el quadre de diàleg i tornar al quadre de diàleg Administrador de normes de format condicional.

04 de 06

Addició d'una regla per a dates més de 90 dies vençuts

1. Repetiu els passos 5 a 7 anteriors per afegir una nova regla.
2. Per utilitzar la fórmula:
   = HOY () - C1> 90
3. Seleccioneu un color de farciment de fons; Per fer coincidir l'exemple d'aquest tutorial, seleccioneu taronja.
4. Estableix el color de la font en blanc per a que coincideixi amb aquest tutorial.
5. Feu clic a D' acord dues vegades per tancar el quadre de diàleg i tornar al quadre de diàleg Administrador de normes de format condicional
6. Feu clic a D' acord per tancar aquest quadre de diàleg i tornar al full de treball .
7. El color de fons de les cel·les C1 a C4 canviarà a l'últim color de farciment escollit.

05 de 06

Prova de les regles de format condicional

Com es pot veure a la imatge de tutorial, podem provar les regles de format condicional a les cel·les C1 a C4 introduint les següents dates:

• La data actual: la cel·la ha de canviar al fons blanc per defecte amb text negre, ja que no s'apliquen cap de les regles de format condicional.
• 40 dies abans de la data actual: la cel·la ha de canviar a un fons verd clar amb text en blanc.
  
  (Nota: Si teniu problemes per calcular aquesta data, com es pot veure a la imatge anterior, la fórmula = HOY () - 40 introduirà la data 40 dies abans de la data actual.)
• 70 dies abans de la data actual: la cel·la ha de canviar a fons groc amb text en blanc.
• 100 dies abans de la data actual: la cel·la ha de canviar a un fons vermell fosc amb text en blanc.

06 de 06

Regles alternatives de format condicional

Si el full de càlcul ja mostra la data actual i la majoria de fulls de treball, una fórmula alternativa a les anteriors pot utilitzar una referència de cel·la a la cel·la on es mostra la data actual en comptes d'utilitzar la funció HORA.

Per exemple, si la data es mostra a la cel·la B4, la fórmula introduïda com a regla per formatear condicionalment dates que tinguin més de 30 dies de venciment podrien ser:

= $ B $ 4> 30

Els signes de dòlar ($) que envolten la referència de cel·la B4 impedeixen que la referència de la cel·la canviï si la regla de format condicional es copia a altres cel·les del full de treball.

Els signes de dòlar creen el que es coneix com una referència de cel·la absoluta .

Si s'ometen els signes de dòlar i es copia la regla de format condicional, la cel·la o cel · les de destinació mostren un #REF. missatge d'error.