contingut
En aquest article, trobareu dues maneres ràpides de canviar el color d'una cel·la en funció del seu valor a Excel 2013, 2010 i 2007. A més, aprendreu a utilitzar fórmules a Excel per canviar el color de les cel·les buides. o cel·les amb errors de fórmula.
Tothom sap que per canviar el color d'ompliment d'una sola cel·la o d'un rang sencer a Excel, només cal que feu clic al botó Color d’ompliment (Color de farciment). Però, què passa si necessiteu canviar el color de farciment de totes les cel·les que contenen un valor determinat? A més, què passa si voleu que el color de farciment de cada cel·la canviï automàticament a mesura que canvia el contingut d'aquesta cel·la? Més endavant en l'article trobareu respostes a aquestes preguntes i obtindreu un parell de consells útils que us ajudaran a triar el mètode adequat per resoldre cada problema específic.
Com canviar dinàmicament el color d'una cel·la a Excel en funció del seu valor
El color de farciment canviarà en funció del valor de la cel·la.
Problema: Teniu una taula o un rang de dades i voleu canviar el color de farciment de les cel·les en funció dels seus valors. A més, és necessari que aquest color canviï dinàmicament, reflectint els canvis en les dades de les cel·les.
Decisió: Utilitzeu el format condicional a Excel per ressaltar valors superiors a X, inferiors a Y o entre X i Y.
Suposem que teniu una llista de preus del gas en diferents estats i voleu que els preus siguin més alts $ 3.7, es van destacar en vermell i més petits o iguals $ 3.45 – verd.
Nota: Les captures de pantalla d'aquest exemple es van fer a Excel 2010, però, a Excel 2007 i 2013, els botons, els diàlegs i la configuració seran exactament iguals o lleugerament diferents.
Per tant, aquí teniu el que heu de fer pas a pas:
- Seleccioneu la taula o l'interval en què voleu canviar el color d'emplenament de la cel·la. En aquest exemple, destaquem $B$2:$H$10 (els encapçalaments de les columnes i la primera columna que conté els noms dels estats no estan seleccionats).
- Feu clic al Home (Inici), a la secció Estils (Estils) feu clic Formatació condicional (Format condicional) > Noves regles (Crear una regla).
- A la part superior del quadre de diàleg Nova regla de format (Crea una regla de format) al camp Seleccioneu un tipus de regla (Seleccioneu el tipus de regla) seleccioneu Formateu només les cel·les que continguin (Format només les cel·les que continguin).
- A la part inferior del quadre de diàleg del quadre Format només cel·les amb (Formateu només les cel·les que compleixin la condició següent) Estableix les condicions per a la regla. Triem formatar només les cel·les amb la condició: Valor de la cel·la (valor de la cel·la) - més que (més) - 3.7tal com es mostra a la figura següent.A continuació, premeu el botó mida (Format) per seleccionar quin color de farciment s'ha d'aplicar si es compleix la condició especificada.
- Al quadre de diàleg que apareix Format de cel·les pestanya (Format cel·les). Omplir (Omplir) i triar un color (nosaltres vam triar vermellós) i fer clic OK.
- Després d'això, tornareu a la finestra Nova regla de format (Creació d'una regla de format) on al camp Vista prèvia (Mostra) mostrarà una mostra del vostre format. Si estàs satisfet, fes clic OK.
A continuació, premeu el botó mida (Format) per seleccionar quin color de farciment s'ha d'aplicar si es compleix la condició especificada.
El resultat de la configuració del format tindrà un aspecte semblant a això:
Com que hem de configurar una altra condició que ens permeti canviar el color de farciment a verd per a cel·les amb valors inferiors o iguals a 3.45i, a continuació, torneu a prémer el botó Noves regles (Crea una regla) i repetiu els passos del 3 al 6, establint la regla desitjada. A continuació es mostra una mostra de la segona regla de format condicional que hem creat:
Quan tot estigui llest, feu clic OK. Ara teniu una taula ben formatada que us permet veure d'un cop d'ull els preus màxims i mínims del gas en diferents estats. Bé per ells allà, a Texas! 🙂
Consell: De la mateixa manera, podeu canviar el color de la font en funció del valor de la cel·la. Per fer-ho, només cal que obriu la pestanya Font (Tipus de lletra) al quadre de diàleg Format de cel·les (Format de cel·la) tal com vam fer al pas 5 i seleccioneu el color de font desitjat.
Com establir un color de cel·la constant en funció del seu valor actual
Un cop establert, el color de farciment no canviarà, per molt que canviï el contingut de la cel·la en el futur.
Problema: Voleu ajustar el color d'una cel·la en funció del seu valor actual i voleu que el color de farciment es mantingui igual fins i tot quan canviï el valor de la cel·la.
Decisió: Trobeu totes les cel·les amb un valor (o valors) específics amb l'eina Troba-ho tot (Cerca-ho tot) i després canvieu el format de les cel·les trobades mitjançant el quadre de diàleg Format de cel·les (format cel·la).
Aquesta és una d'aquelles tasques poc freqüents per a les quals no hi ha explicació als fitxers d'ajuda d'Excel, fòrums o blocs, i per a les quals no hi ha solució directa. I això és comprensible, ja que aquesta tasca no és típica. I, tanmateix, si necessiteu canviar el color d'ompliment de la cel·la de manera permanent, és a dir, d'una vegada per totes (o fins que el canvieu manualment), seguiu aquests passos.
Cerqueu i seleccioneu totes les cel·les que compleixin una condició determinada
Aquí són possibles diversos escenaris, depenent del tipus de valor que cerqueu.
Si voleu pintar cel·les amb un valor específic, per exemple, 50, 100 or 3.4 - després a la pestanya Home (Inici) a la secció Edició (Edició) feu clic Cerca Selecciona (Cerca i destaca) > Cercar (Trobar).
Introduïu el valor desitjat i feu clic Troba-ho tot (Troba-ho tot).
Consell: A la part dreta del quadre de diàleg Cerca i reemplaça (Cerca i substitueix) hi ha un botó opcions (Opcions), prement el qual tindreu accés a una sèrie de paràmetres de cerca avançada, com ara Cas de correspondència (distingeix entre majúscules i minúscules) i Coincideix amb tot el contingut de la cel·la (Cèl·lula sencera). Podeu utilitzar caràcters comodins com ara l'asterisc (*) per fer coincidir qualsevol cadena de caràcters o el signe d'interrogació (?) per fer coincidir qualsevol caràcter.
Pel que fa a l'exemple anterior, si hem de trobar tots els preus de la gasolina de 3.7 a 3.799, llavors establirem els següents criteris de cerca:
Ara feu clic a qualsevol dels elements trobats a la part inferior del quadre de diàleg Cerca i reemplaça (Cerca i substitueix) i fes clic Ctrl + Aper destacar totes les entrades trobades. Després d'això, premeu el botó Cerrar (Tanca).
Així és com podeu seleccionar totes les cel·les amb un valor determinat (valors) mitjançant l'opció Troba-ho tot (Troba-ho tot) a Excel.
No obstant això, en realitat, hem de trobar tots els preus de la gasolina que superin $ 3.7. Malauradament l'eina Cerca i reemplaça (Trobar i substituir) no ens pot ajudar amb això.
Canvieu els colors de farciment de les cel·les seleccionades mitjançant el quadre de diàleg Format de cel·les
Ara teniu totes les cel·les amb el valor (o valors) donats seleccionades, ho hem fet amb l'eina Cerca i reemplaça (Cerca i substitueix). Tot el que heu de fer és establir el color de farciment de les cel·les seleccionades.
Obre un quadre de diàleg Format de cel·les (Format de cel·la) de qualsevol de les 3 maneres següents:
- premsat Ctrl + 1.
- fent clic a qualsevol cel·la seleccionada amb el botó dret del ratolí i seleccionant l'element del menú contextual Format de cel·les (format cel·la).
- llengüeta Home (Inici) > Cèl · lules. Cèl · lules. (cèl·lules) > mida (Format) > Format de cel·les (format cel·la).
A continuació, ajusteu les opcions de format com vulgueu. Aquesta vegada posarem el color de farciment en taronja, només per variar 🙂
Si voleu canviar només el color de farciment sense tocar la resta d'opcions de format, només podeu fer clic al botó Color d’ompliment (Color d'ompliment) i tria el color que t'agradi.
Aquí teniu el resultat dels nostres canvis de format a Excel:
A diferència del mètode anterior (amb format condicional), el color de farciment establert d'aquesta manera mai no canviarà sense el vostre coneixement, sense importar com canviïn els valors.
Canvieu el color de farciment per a cel·les especials (buides, amb un error a la fórmula)
Com a l'exemple anterior, podeu canviar el color de farciment de cel·les específiques de dues maneres: de forma dinàmica i estàtica.
Utilitzeu una fórmula per canviar el color de farciment de cel·les especials a Excel
El color de la cel·la canviarà automàticament en funció del valor de la cel·la.
El més probable és que utilitzeu aquest mètode per resoldre el problema en el 99% dels casos, és a dir, l'ompliment de les cel·les canviarà d'acord amb la condició que heu especificat.
Per exemple, tornem a prendre la taula de preus de la gasolina, però aquesta vegada afegirem un parell d'estats més i deixarem algunes cel·les buides. Ara mireu com podeu trobar aquestes cel·les buides i canviar-ne el color de farciment.
- A la pestanya Avançat Home (Inici) a la secció Estils (Estils) feu clic Formatació condicional (Format condicional) > Noves regles (Crear una regla). Igual que al segon pas de l'exemple Com canviar dinàmicament el color d'una cel·la en funció del seu valor.
- Al quadre de diàleg Nova regla de format (Creeu una regla de format) seleccioneu una opció Utilitzeu una fórmula per determinar quines cel·les formatar (Utilitzeu una fórmula per determinar quines cel·les voleu formatar). Més endins al camp Formateu els valors on aquesta fórmula és certa (Format valors per als quals la fórmula següent és certa) introduïu una de les fórmules:
- per canviar l'ompliment de cel·les buides
=ISBLANK()=ЕПУСТО() - per canviar l'ombrejat de les cel·les que contenen fórmules que retornen un error
=ISERROR()=ЕОШИБКА()
Com que volem canviar el color de les cel·les buides, necessitem la primera funció. Introduïu-lo, col·loqueu el cursor entre els claudàtors i feu clic a la icona de selecció d'interval a la part dreta de la línia (o escriviu l'interval desitjat manualment):
=ISBLANK(B2:H12)=ЕПУСТО(B2:H12) - per canviar l'ompliment de cel·les buides
- premsa mida (Format), seleccioneu el color de farciment desitjat a la pestanya Omplir (Emplenar) i després feu clic OK. Al pas 5 de l'exemple "Com canviar dinàmicament el color d'una cel·la en funció del seu valor" es donen instruccions detallades. Una mostra del format condicional que configureu tindrà un aspecte semblant a això:
- Si estàs satisfet amb el color, fes clic OK. Veureu com la regla creada s'aplicarà immediatament a la taula.
Canvieu el color de farciment de les cel·les especials de manera estàtica
Un cop configurat, el farciment es mantindrà sense canvis, independentment del valor de la cel·la.
Si voleu establir un color de farciment permanent per a cel·les buides o cel·les amb fórmules que continguin errors, utilitzeu aquest mètode:
- Seleccioneu una taula o rang i feu clic F5per obrir el diàleg Anar a (Saltar) i després premeu el botó Especial (Destacat).
- Al quadre de diàleg Vés a Especial (Seleccioneu un grup de cel·les) marqueu l'opció espais en blanc (Cèl·lules en blanc) per seleccionar totes les cel·les buides.Si voleu ressaltar les cel·les que contenen fórmules amb errors, marqueu l'opció Fórmules (fórmules) > errors (Errades). Com podeu veure a la imatge de dalt, hi ha moltes altres configuracions disponibles.
- Finalment, canvieu el farciment de les cel·les seleccionades o definiu qualsevol altra opció de format mitjançant el quadre de diàleg Format de cel·les (Format de cel·les), tal com es descriu a Canvi de l'emplenament de les cel·les seleccionades.
Si voleu ressaltar les cel·les que contenen fórmules amb errors, marqueu l'opció Fórmules (fórmules) > errors (Errades). Com podeu veure a la imatge de dalt, hi ha moltes altres configuracions disponibles.No oblideu que la configuració de format feta d'aquesta manera es conservarà fins i tot quan les cel·les buides s'omplen amb valors o es corregin els errors en les fórmules. És difícil imaginar que algú hagi d'anar per aquest camí, excepte per als propòsits de l'experiment 🙂