Format condicional en Excel es pot considerar una utilitat valuosa que pot ajudar a millorar visualment l'anàlisi i la presentació de dades al full de càlcul aplicant un format particular basat en regles o condicions predefinides. Aquesta publicació compartirà com podeu configurar el format condicional a Excel per a les dates. També hem inclòs exemples detallats que podeu provar mentre llegiu la guia.
Com configurar el format condicional a Excel per a les dates
El format condicional no només ajuda a ressaltar les cel·les mitjançant codis de color, sinó que també dóna format a les files, columnes i cel·les que consisteixen en dates d'una manera que es pot considerar visualment significativa i atractiva.
- Predefinit
- Usuari definit
- Creeu una regla de format personalitzada
- Destacant Vacances a Excel
- Format condicional de dates basat en múltiples condicions
- Destacant les properes dates i retards
Assegureu-vos de revisar amb cura els detalls a Excel. Ofereix moltes opcions, algunes de les quals poden estar fora de l'abast d'aquesta guia.
1] Regles de data de format condicional predefinides amb Dates
El format condicional amb dates pot ser beneficiós mentre es tracten conjunts de dades que inclouen informació relacionada amb la data. Excel ofereix gairebé deu opcions diferents per formatar cel·les en funció de les dates, tal com es mostra a continuació:
- Per formatar una cel·la amb la data com a Tipus de dades, feu clic a Inici > Format condicional > Ressalta les regles de cel·les > S'ha produït una data
- Les opcions de format condicional es poden triar als menús desplegables de la finestra següent. El primer desplegable ens permet seleccionar l'interval de dies, i les opcions de format es poden triar des del segon.
- Feu clic D'acord per aplicar les regles de format escollides.
2] Regles de data de format condicional definides per l'usuari amb Dates
A més d'un conjunt de regles predefinides, Excel també ofereix l'opció de personalitzar aquestes regles en funció dels requisits de l'usuari. Hi ha diverses maneres de configurar les personalitzacions, tal com es mostra a continuació:
Utilitzant el format personalitzat
- Obriu el Una data que passa finestra de format seguint els passos esmentats anteriorment.
- Després de seleccionar l'interval diari, seleccioneu Format personalitzat des del menú desplegable d'opcions de format.
- El Finestra de format de cel·les apareixerà a causa de l'anterior, on podem triar el color, la vora, el tipus de lletra i els efectes que escollim i després fer clic a D'acord per aplicar els canvis.
Crea noves regles
Aquest mètode implica crear una regla completament nova per al format condicional, a diferència del mètode anterior, on podem triar entre unes quantes regles predefinides i crear només un format personalitzat. La creació d'una nova regla implica:
- Seleccionar les cel·les a les quals s'ha d'aplicar el format.
- A la Casa Pestanya, fes clic Format condicional > Nova regla , que hauria d'obrir el Nova finestra de regla de format.
- A l'esmentada finestra, podem triar entre les disponibles Tipus de regles , seguit d'editar la descripció de la regla per configurar els criteris per al format condicional.
- Un cop finalitzat, el Format Es pot fer clic al botó a l'extrem inferior dret de la finestra per triar el format de la regla esmentada.
- A la Format de cel·les finestra que s'obre, podem escollir el tipus de lletra, la vora, el color de la cel·la, els efectes, etc., segons l'elecció i fer clic a D'acord una vegada en aquesta finestra, seguit del Nova regla de format Finestra per aplicar els canvis.
Nota: Les regles de format es poden utilitzar per crear i aplicar més d'una regla per al mateix conjunt de dades en funció de diferents condicions/criteris.
Llegeix: El filtre de dates d'Excel no s'agrupa per mes
3] Com crear una regla de format personalitzada amb dates
A l'exemple anterior, vam navegar pels passos per crear una nova regla per al format condicional basant-se en criteris ja establerts. Excel també permet aplicar aquestes regles de format segons criteris definits per l'usuari.
Per exemple, suposem que hem de ressaltar les cel·les amb dates superiors a 30 dies o 60 dies. En aquest cas, el mateix també es pot implementar aplicant fórmules de data i funcions disponibles a Excel, tal com es mostra a continuació:
A] Ressalteu les dates actuals i les anteriors
Sota Seleccioneu una nova regla a l'opció Formatat condicional, trieu Utilitzeu una fórmula per determinar quina cel·la formatar , escriviu:
=$H4=AVUI () per destacar l'actualitat
=$H4>AVUI () per destacar els dies futurs
=$H4
=$H4<=(AVUI ()-30) per destacar dates que tenen més de 30 dies d'antiguitat.
=$H4<=(AVUI ()-60) per destacar dates que tenen més de 60 dies.
La fórmula anterior comprova l'interval de dates seleccionat per a la data actual i, un cop es trobi la coincidència, canvieu el color de fons a Porpra tal com s'ha seleccionat a l'opció Format. El $ utilitzat al costat H significa que la columna romandria constant, però les files serien variables.
- Un cop el Descripció de la regla s'esmenta, el tipus de lletra, els colors i els efectes es poden triar fent clic al botó Format.
- Fer clic a D'acord a la finestra Format de cel·les, seguit del mateix a la finestra Nova regla, perquè els canvis siguin efectius.
B] Ressaltar en funció d'un interval de dates definit per l'usuari
Es poden aplicar mètodes similars per donar format als intervals de dates especificats pels usuaris. Per exemple, si hem de destacar les dates que tenen més de 30 dies però menys de 45 dies, la funció TODAY() es pot utilitzar en combinació amb un operador lògic com AND per especificar els criteris que impliquen dues condicions:
- Seleccioneu les cel·les a les quals cal aplicar el format i obriu la finestra Nova regla.
- En el Editeu la descripció de la regla camp, introduïu la fórmula següent,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
La fórmula anterior utilitza el I operador de ressaltar en groc les dates que tenen més de 30 dies però menys de 45 dies per a tota la selecció, a partir de la cel·la núm. H2.
C] Caps de setmana destacats
En el mateix interval, utilitzant la fórmula WEEKDAY(), també es poden destacar les dates que cauen els caps de setmana:
- Seleccioneu les cel·les a les quals cal aplicar el format i obriu la finestra Nova regla.
- En el Editeu la descripció de la regla camp, introduïu la fórmula següent,
=WEEKDAY($H2, 2)>5
El sintaxi de la fórmula del dia de la setmana es pot representar com WEEKDAY(serial_number, [return_type]), on serial_number representa el número de cel·la de la data que estem intentant comprovar (SA1 en aquest cas).
El tipus_retorn El paràmetre representa el tipus de la setmana que comença de dilluns (1) a diumenge (7), considerant el dia d'inici de la setmana com a dilluns (representat per 1) i l'últim dia de la setmana com a diumenge (representat per 7).
Tanmateix, return_types són opcionals a la fórmula i es representen en terceres claus. >5 s'utilitza per destacar dissabte(6) i diumenge(7) per al tipus de retorn especificat.
D] Ressaltar files en funció d'una data específica
Aquesta opció pot ser útil quan es tracten grans quantitats de dades en què cal destacar una data concreta. El mateix es pot aconseguir determinant el valor numèric de la data o utilitzant DATEVALUE() funció. El valor numèric s'ha de determinar inicialment per aplicar el format condicional a una data específica, ja que Excel emmagatzema permanentment les dates com a números de sèrie seqüencials. Per trobar el valor numèric d'una data específica,
- Feu clic amb el botó dret a la cel·la amb la data, feu clic a Format de cel·les > Nombre, i després tria General . Es mostrarà un valor numèric de 5 dígits a la cel·la, que es pot anotar per a referència futura.
- Feu clic a Cancel·la un cop anotat el número, ja que no hem de canviar el format de data de la cel·la.
- Fer clic a Format condicional>Nova regla> Seleccioneu una nova regla > Utilitzeu una fórmula per determinar quina cel·la formatar i escriviu la fórmula següent,
=$G5=45249
plantilla de planta excel
- Utilitzant el DATEVALUE() funció, també es pot fer el mateix introduint la fórmula esmentada a continuació,
=$G5=DATEVALUE(“11/19/2023”)
El $ utilitzat al costat de C significa que la columna romandria constant, però les files serien variables.
Nota: La seqüència de números de sèrie d'aquestes dates a Excel comença a partir de l'1 st gener de 1900, com a número de sèrie 1. Per tant, si considerem la data 19-nov-2023, es numeraria com a 45249 ja que són 45248 dies després de l'1. st gener de 1900.
Llegeix: Com convertir el número de sèrie en data a Microsoft Excel
4] Destacant Vacances a Excel
Com que les vacances poden variar en funció de la demografia, Excel no té cap funció integrada per destacar els dies festius. Per tant, per identificar les vacances, hem de tenir les dates registrades en una columna diferent i aplicar-hi comtef fórmula per comprovar l'aparició de les dates de vacances al full de dades.
Per exemple, tenim unes quantes dates esmentades a la columna D i una llista de vacances a la columna G. Mitjançant el format condicional, podem destacar les vacances en VERMELL a la columna D, si n'hi ha, comparant-les amb les dates de vacances esmentades a la columna G. utilitzant els passos esmentats a continuació:
- Obriu el Nova regla quadre de diàleg i tria Utilitzeu una fórmula per determinar quines cel·les voleu formatar sota Seleccioneu el tipus de regla.
- Introduïu la fórmula =COMPTARSI($G:$G,$D3)
- Fer clic a Format > Omplir i seleccioneu el color VERMELL de la paleta i feu clic a D'acord .
El countif() comprova l'ocurrència de les dates esmentades a la cel·la G3 – G5 amb les dates esmentades a la columna D a partir de la cel·la D3.
5] Format condicional de dates basat en múltiples condicions
Suposem un full de treball on tenim queixes sobre diversos articles per a la llar. El full inclou les dates en què s'han presentat les queixes i les dates de venciment i tancament, si escau.
Utilitzant el format condicional, marquem les queixes BLANQUES per a les que han estat TANCADAS, VERMELLAS per a les que han passat la data de venciment però encara no tancades, i BLAU per a les que encara han arribat a la data de venciment.
El procés implicaria la creació de dues regles de format condicional per a les mateixes dades, una per a aquelles que encara no han arribat a la data de venciment i una altra per a aquelles que han superat la data de venciment. Per fer-ho,
- Obriu la finestra Nova regla de format condicional i seleccioneu Utilitzeu una fórmula per determinar quines cel·les voleu formatar sota Seleccioneu un tipus de regla.
- Introduïu la fórmula esmentada a continuació
=AND($I10="",$H10>$K)
- Feu clic Format , seleccioneu el color BLAU de la paleta i feu clic a D'acord.
- Crea un altre Nova regla amb el mateix tipus de regla i introduïu la fórmula esmentada a continuació
=AND($I10="",$H10<$K)
- Feu clic a Format, seleccioneu el color VERMELL de la paleta i feu clic a D'acord.
En aquesta fórmula, primer comprovem si la columna Data tancada està en BLANC o no per determinar que la reclamació encara està oberta. I a continuació, comproveu si la DATA DE VENCIment és més gran que la data actual o inferior per determinar si la data de venciment s'acosta ($H10>$K ) o superat ($H10<$K ) . La data de la cel·la K6 representa la data actual.
6] Destacant les properes dates i retards
Per entendre com podem destacar els retards respecte a les dates en un full d'Excel, considerem un exemple en què tenim una llista d'existències amb els seus noms i dates de caducitat. Mitjançant el format condicional, si hem de marcar en VERD els que han de caducar i en VERMELL els que ja han caducat,
- Seleccioneu l'interval de dades i obriu el fitxer Nova regla quadre de diàleg.
- Tria Formateu només les cel·les que continguin sota Seleccioneu el tipus de regla
- En Edita la descripció de la regla , tria Valor de la cel·la , Més gran que , =Ara()+30 en els desplegables respectius.
- Fer clic a Format, seleccioneu VERD de la paleta de colors , i feu clic D'acord .
- De nou, obriu a Nova regla i tria el mateix Tipus de regla, com més amunt.
- En Edita la descripció de la regla , tria Valor de la cel·la , Menys que , =Ara () en els desplegables respectius.
- Fer clic a Format i seleccioneu VERMELL des de la paleta de colors i feu clic D'acord .
Conclusió
Aquest tutorial té com a objectiu cobrir de manera exhaustiva les opcions i funcions que es poden utilitzar per aplicar el format condicional als fulls de treball Excel basats en dates. Pot ajudar a entendre com aplicar regles de format per millorar la visualització i l'anàlisi de dades. Dominar el format condicional també pot ajudar a identificar tendències, comprovar els terminis i establir prioritats de tasques, ajudant a prendre decisions informades.
Llegeix : Com ressalteu les files amb dates mitjançant el format condicional a Excel
Com puc crear un format condicional a Excel basat en dates?
Primer, seleccioneu les vostres cel·les de data per crear un format condicional a Excel basat en les dates. Feu clic a 'Inici' > 'Format condicional' > 'Nova regla'. Trieu 'Format cel·les que contenen', seleccioneu 'Dates que es produeixen' a la Regla, trieu els criteris de data específics i, a continuació, configureu el format preferit fent clic a 'Format'. Sol·liciteu prement 'D'acord'. Això destaca ràpidament les dates clau de les vostres dades.
Com puc donar format condicional a les dates de venciment a Excel?
En primer lloc, seleccioneu les vostres cel·les amb dates per donar format condicional a les dates de venciment a Excel. A continuació, feu clic a 'Format condicional' a la pestanya 'Inici'. Trieu 'Regla nova', seleccioneu 'Format només les cel·les que continguin', establiu la regla a 'Valor de la cel·la' i 'menys de' i introduïu la vostra data de venciment o una fórmula. Feu clic a 'Format', trieu el vostre estil de format i premeu 'D'acord' per aplicar-lo.
