Innhold
I denne artikkelen finner du to raske måter å endre fargen på en celle basert på verdien i Excel 2013, 2010 og 2007. Du vil også lære hvordan du bruker formler i Excel for å endre fargen på tomme celler eller celler med formelfeil.
Alle vet at for å endre fyllfargen til en enkelt celle eller et helt område i Excel, klikker du bare på knappen Fyll farge (Fyll farge). Men hva om du trenger å endre fyllfargen til alle celler som inneholder en bestemt verdi? Dessuten, hva om du vil at fyllfargen til hver celle skal endres automatisk når innholdet i den cellen endres? Videre i artikkelen finner du svar på disse spørsmålene og får et par nyttige tips som vil hjelpe deg å velge riktig metode for å løse hvert enkelt problem.
Hvordan endre fargen på en celle dynamisk i Excel basert på verdien
Fyllfargen vil endres avhengig av verdien til cellen.
problem: Du har en tabell eller et dataområde, og du vil endre fyllfargen til cellene basert på verdiene deres. Dessuten er det nødvendig at denne fargen endres dynamisk, noe som gjenspeiler endringer i dataene i cellene.
Beslutning: Bruk betinget formatering i Excel for å markere verdier større enn X, mindre enn Y eller mellom X og Y.
La oss si at du har en liste over gasspriser i forskjellige stater, og du vil ha priser som er høyere enn $ 3.7, ble uthevet i rødt, og mindre eller lik $ 3.45 – grønn.
OBS: Skjermbildene for dette eksemplet ble tatt i Excel 2010, men i Excel 2007 og 2013 vil knappene, dialogboksene og innstillingene være nøyaktig like eller litt forskjellige.
Så her er hva du trenger å gjøre trinn for trinn:
- Velg tabellen eller området der du vil endre cellefyllfargen. I dette eksemplet fremhever vi $B$2:$H$10 (kolonneoverskrifter og den første kolonnen som inneholder navnene på statene er ikke valgt).
- Klikk på Hjemprodukt (Hjem), i seksjonen Stiler (Stiler) klikk Betinget formatering (Betinget formatering) > Nye regler (Lag en regel).
- Øverst i dialogboksen Ny formateringsregel (Opprett formateringsregel) i feltet Velg en regeltype (Velg regeltype) velg Formater bare celler som inneholder (Formater bare celler som inneholder).
- Nederst i dialogboksen i boksen Formater kun celler med (Formater bare celler som oppfyller følgende betingelse) Angi betingelsene for regelen. Vi velger å formatere bare celler med betingelsen: Celleverdi (celleverdi) – større enn (mer) - 3.7som vist i figuren nedenfor.Trykk deretter på knappen Størrelse (Format) for å velge hvilken fyllfarge som skal brukes hvis den angitte betingelsen er oppfylt.
- I dialogboksen som vises Formater celler (Formater celler)-fanen Fyll (Fyll) og velg en farge (vi valgte rødlig) og klikk OK.
- Etter det kommer du tilbake til vinduet Ny formateringsregel (Opprette en formateringsregel) hvor i feltet Forhåndsvisning (Sample) viser et eksempel på formateringen din. Hvis du er fornøyd, klikk OK.
Trykk deretter på knappen Størrelse (Format) for å velge hvilken fyllfarge som skal brukes hvis den angitte betingelsen er oppfylt.
Resultatet av formateringsinnstillingene dine vil se omtrent slik ut:
Siden vi må sette opp en annen betingelse som lar oss endre fyllfargen til grønn for celler med verdier mindre enn eller lik 3.45, og trykk deretter på knappen igjen Nye regler (Opprett regel) og gjenta trinn 3 til 6, og still inn ønsket regel. Nedenfor er et eksempel på den andre betingede formateringsregelen vi opprettet:
Når alt er klart – klikk OK. Du har nå en pent formatert tabell som lar deg se maksimums- og minimumspriser for gass i forskjellige stater med et øyeblikk. Bra for dem der, i Texas! 🙂
Tips: På samme måte kan du endre skriftfargen avhengig av verdien til cellen. For å gjøre dette, åpne bare fanen Font (Skrift) i dialogboksen Formater celler (Cell Format) som vi gjorde i trinn 5 og velg ønsket skriftfarge.
Hvordan sette en konstant cellefarge basert på gjeldende verdi
Når den er angitt, vil ikke fyllfargen endres, uansett hvordan innholdet i cellen endres i fremtiden.
problem: Du vil justere fargen på en celle basert på gjeldende verdi, og du vil at fyllfargen skal forbli den samme selv når cellens verdi endres.
Beslutning: Finn alle celler med en bestemt verdi (eller verdier) ved hjelp av verktøyet Finn alle (Finn alle) og endre deretter formatet til cellene som ble funnet ved hjelp av dialogboksen Formater celler (celleformat).
Dette er en av de sjeldne oppgavene som det ikke er noen forklaring på i Excel-hjelpefilene, fora eller blogger, og som det ikke finnes noen direkte løsning på. Og dette er forståelig, siden denne oppgaven ikke er typisk. Og likevel, hvis du trenger å endre cellefyllfargen permanent, det vil si en gang for alle (eller til du endrer den manuelt), følg disse trinnene.
Finn og velg alle celler som oppfyller en gitt betingelse
Flere scenarier er mulige her, avhengig av hvilken type verdi du ser etter.
Hvis du vil farge celler med en bestemt verdi, for eksempel, 50, 100 or 3.4 – deretter på fanen Hjemprodukt (Hjem) i seksjonen Redigering (Rediger) klikk Finn Velg (Finn og fremhev) > Finn (Finne).
Skriv inn ønsket verdi og klikk Finn alle (Finn alle).
Tips: På høyre side av dialogboksen Finn og erstatt (Finn og erstatt) det er en knapp alternativer (Alternativer), ved å trykke vil du få tilgang til en rekke avanserte søkeinnstillinger, som f.eks Match sak (skiller mellom store og små bokstaver) og Match hele celleinnholdet (Hele cellen). Du kan bruke jokertegn som stjernen (*) for å matche en hvilken som helst streng med tegn, eller spørsmålstegnet (?) for å matche et enkelt tegn.
Angående forrige eksempel, hvis vi trenger å finne alle bensinpriser fra 3.7 til 3.799, vil vi angi følgende søkekriterier:
Klikk nå på et av elementene som ble funnet nederst i dialogboksen Finn og erstatt (Finn og erstatt) og klikk Ctrl + Afor å markere alle funnet oppføringer. Trykk deretter på knappen Fermer (Lukk).
Slik kan du velge alle celler med en gitt verdi (verdier) ved å bruke alternativet Finn alle (Finn alle) i Excel.
Men i virkeligheten må vi finne alle bensinpriser som overstiger $ 3.7. Dessverre verktøyet Finn og erstatt (Finn og erstatt) kan ikke hjelpe oss med dette.
Endre fyllfargene til valgte celler ved å bruke dialogboksen Formater celler
Du har nå valgt alle cellene med den gitte verdien (eller verdiene), vi gjorde dette med verktøyet Finn og erstatt (Finn og erstatt). Alt du trenger å gjøre er å angi fyllfargen for de valgte cellene.
Åpne en dialogboks Formater celler (celleformat) på en av tre måter:
- trykke Ctrl + 1.
- ved å klikke på en valgt celle med høyre museknapp og velge elementet fra kontekstmenyen Formater celler (celleformat).
- tab Hjemprodukt (Hjem) > Celler. Celler. (celler) > Størrelse (Format) > Formater celler (celleformat).
Deretter justerer du formateringsalternativene som du vil. Denne gangen setter vi fyllfargen til oransje, bare for en forandring 🙂
Hvis du bare vil endre fyllfargen uten å berøre resten av formateringsalternativene, kan du ganske enkelt klikke på knappen Fyll farge (Fyllfarge) og velg fargen du liker.
Her er resultatet av formateringsendringene våre i Excel:
I motsetning til den forrige metoden (med betinget formatering), vil fyllfargesettet på denne måten aldri endre seg selv uten din viten, uansett hvordan verdiene endres.
Endre fyllfargen for spesielle celler (tomme, med en feil i formelen)
Som i forrige eksempel kan du endre fyllfargen til bestemte celler på to måter: dynamisk og statisk.
Bruk en formel for å endre fyllfargen til spesielle celler i Excel
Cellefargen vil endres automatisk avhengig av verdien til cellen.
Du vil mest sannsynlig bruke denne metoden for å løse problemet i 99% av tilfellene, det vil si at fyllingen av cellene vil endres i samsvar med tilstanden du spesifiserte.
La oss for eksempel ta bensinpristabellen igjen, men denne gangen vil vi legge til et par stater til, og gjøre noen celler tomme. Se nå hvordan du kan finne disse tomme cellene og endre fyllfargen deres.
- I kategorien Avansert Hjemprodukt (Hjem) i seksjonen Stiler (Stiler) klikk Betinget formatering (Betinget formatering) > Nye regler (Lag en regel). Akkurat som i det andre trinnet i eksemplet Hvordan endre fargen på en celle dynamisk basert på verdien.
- I dialogboksen Ny formateringsregel (Opprett en formateringsregel) velg et alternativ Bruk en formel for å finne ut hvilke celler å formatere (Bruk en formel for å bestemme hvilke celler som skal formateres). Lenger inn i feltet Formater verdier der denne formelen er sann (Formatverdier som følgende formel er sann for) skriv inn en av formlene:
- for å endre fyllingen av tomme celler
=ISBLANK()=ЕПУСТО() - for å endre skyggeleggingen av celler som inneholder formler som returnerer en feil
=ISERROR()=ЕОШИБКА()
Siden vi ønsker å endre fargen på tomme celler, trenger vi den første funksjonen. Skriv inn den, plasser deretter markøren mellom parentesene og klikk på områdevalgikonet på høyre side av linjen (eller skriv inn ønsket område manuelt):
=ISBLANK(B2:H12)=ЕПУСТО(B2:H12) - for å endre fyllingen av tomme celler
- Klikk på knappen Størrelse (Format), velg ønsket fyllfarge på fanen Fyll (Fyll), og klikk deretter OK. Detaljerte instruksjoner er gitt i trinn 5 i eksempelet "Hvordan endrer du fargen på en celle dynamisk basert på verdien." Et eksempel på den betingede formateringen du setter opp vil se omtrent slik ut:
- Hvis du er fornøyd med fargen, klikk OK. Du vil se hvordan den opprettede regelen umiddelbart vil bli brukt på tabellen.
Endre fyllfargen til spesielle celler statisk
Når den er konfigurert, vil fyllingen forbli uendret, uavhengig av verdien til cellen.
Hvis du vil angi en permanent fyllfarge for tomme celler eller celler med formler som inneholder feil, bruk denne metoden:
- Velg en tabell eller et område og klikk F5for å åpne dialogen gå til (Hopp), og trykk deretter på knappen Spesiell (Fremheve).
- I dialogboksen Gå til Special (Velg en gruppe med celler) merk av for alternativet blanks (Blanke celler) for å velge alle tomme celler.Hvis du vil markere celler som inneholder formler med feil, merker du av for alternativet Formler (formler) > feil (Feil). Som du kan se på bildet ovenfor, er det mange andre innstillinger tilgjengelig for deg.
- Til slutt, endre fyllingen av de valgte cellene eller angi andre formateringsalternativer ved hjelp av dialogboksen Formater celler (Formater celler), som beskrevet i Endre fyllingen av valgte celler.
Hvis du vil markere celler som inneholder formler med feil, merker du av for alternativet Formler (formler) > feil (Feil). Som du kan se på bildet ovenfor, er det mange andre innstillinger tilgjengelig for deg.Ikke glem at formateringsinnstillinger gjort på denne måten vil bli bevart selv når tomme celler er fylt med verdier eller feil i formler blir rettet. Det er vanskelig å forestille seg at noen kan trenge å gå denne veien, bortsett fra eksperimentets formål 🙂