Pro-tips: Plass til endrede forhold i Excel-funksjonen for betinget format

Excels betingede formateringsfunksjon gjør det mulig å fly formatering. For eksempel kan du markere poster når en forfallsdato er gjeldende dato - eller du kan markere poster for en bestemt prosjektleder. Hvis regelen din endres, må du imidlertid oppdatere regelen. Hvis dette skjer ofte, kan det hende du trenger en mer fleksibel løsning.

For å følge denne artikkelen kan du jobbe med alle enkle datasett eller laste ned eksemplet .xls eller .xlsx-arbeidsbok.

Problemet

Vanligvis justeres betingede formater når du legger inn og oppdaterer verdier. Det er lett å tillate forventede endringer - det er det funksjonen gjør! Men hva skjer når tilstanden, i stedet for dataene, endres? La oss vurdere et enkelt datasett som fremhever poster for prosjektlederen. Hvis lederen endres, må du oppdatere betingede regler. Det er ikke så viktig å oppdatere en regel en gang. Det er imidlertid kjedelig å oppdatere disse reglene ofte eller oppdatere flere regler på en gang!

Det enkle datasettet i figur A er en forenklet versjon av hva du kan støte på. En enkel betinget regel, = $ A2 = "Bill", oppdaterer poster for prosjektlederen, Bill. Hvis du endrer navn, fjerner regelen høydepunktet. Her blir ting litt vanskelig. Når du tilordner posten på nytt, vil du at høydepunktet skal forsvinne, som vist i figur A. Rekorden tilhører ikke lenger Bill, prosjektlederen. Slik fungerer betinget formatering. På den annen side, hvis John er den nye prosjektlederen, må du oppdatere den underliggende regelen fordi du vil at høydepunktet skal forbli. Høydepunktet tilhører prosjektlederen, ikke bare Bill eller John.

Figur A

En betinget regel fremhever poster for Bill; Når du endrer navn, forsvinner høydepunktet.

I en perfekt verden vil du ganske enkelt oppdatere regelen, = $ A2 = "John", og fortsette. Når endringer skjer ofte, bør du vurdere oppsettet på nytt. Prosjektlederposisjonen er betingelsen, ikke navnet på personen som til enhver tid er prosjektleder. Det er den beste løsningen å imøtekomme denne endrede tilstanden i datasettet, og deretter i henhold til betingede regel. Du kan gjøre det ved å legge til en kolonne for hver persons posisjon i teamet og bruke den kolonnen for denne bestemte regelen. Å gjøre det i den virkelige verden er imidlertid ikke alltid et alternativ.

En referanseløsning

Prøv å ikke bli for hengt opp på det enkle og (veldig) fortenkte eksemplet. Eksemplet datasett er ikke viktig - alternativet jeg tilbyr er. Når det ikke er mulig å endre datasettet (for å inkludere en mer stabil tilstand), kan du referere til den endrede tilstanden i arket som vist i figur B.

Figur B

Refererer til den endrede tilstanden på arket.

Som du ser har jeg skrevet prosjektlederens navn i celle A2. La oss nå legge inn en regel som samsvarer med dataene i cellen:

  1. Velg datasettet du vil formatere - A6: B9.
  2. Klikk på Betinget formatering i kategorien Stiler på Hjem-fanen. I Excel 2003 velger du Betinget formatering fra Format-menyen og hopper til trinn 5.
  3. Velg Ny regel fra rullegardinlisten.
  4. I toppruten velger du alternativet Bruk en formel for å bestemme hvilke celler som skal formateres.
  5. Skriv inn formelen = $ A6 = $ A $ 2 I Excel 2003 må du velge Formel Is fra rullegardinmenyen før du skriver inn formelen. Match nøyaktig og absolutt. Du vil at Excel skal vurdere alle radene i det valgte datasettet, men regelen skal alltid referere til teksten du matcher i celle A2.
  6. Klikk på Format. Velg en farge på Fyll-fanen (Mønstre i tidligere versjoner) og klikk OK. På dette tidspunktet kan du forhåndsvise formatet ( figur C ).
  7. Klikk OK for å gå tilbake til arket.

Figur C

På dette tidspunktet kan du forhåndsvise formatet.

Det er mest sannsynlig at du ønsker å slette den opprinnelige regelen ved å velge Administrer regler fra alternativet Betinget formatering. Velg deretter dette regnearket fra rullegardinmenyen Vis formateringsregler for. Velg den opprinnelige regelen, og klikk deretter på Slett regel. Hvis regelen inkluderer andre forhold, må du endre den i stedet for å slette den.

Nå kan du overføre posten og endre prosjektleder uten å justere den underliggende regelen. Hvis du for eksempel skriver inn John i celle A6, forsvinner høydepunktet. Bill er fremdeles prosjektlederen, og det er ingen poster for ham i datasettet. Hvis du legger inn John i A2, justeres høydepunktet deretter, som vist i figur D. Johns plate (i stedet for Bills) er nå fremhevet.

Figur D


Tillat betingede endringer.

Kompliserer tilstanden

En mer komplisert tilstand endrer ikke denne løsningen. Bare finn komponenten som endres, skriv inn den endrede verdien i arket og referer til den komponenten i betingelsesregelens formel. La oss for eksempel anta at du vil markere poster der datoen i kolonne B er i forhold til gjeldende dato, men den relative faktoren endres. Noen ganger vil du markere datoer som samsvarer med gjeldende dato. Det kan også være lurt å fremheve datoer som er en uke fremover (eller fortiden). Du kan tilordne flere betingede høydepunkter, eller du kan referere til den endrede verdien. Figur E viser nødvendig justering til arket - referansedagskomponenten i celle B2.

Figur E

Legg til en celle for den endrede dagskomponenten.

La oss legge til den nye regelen:

  1. Velg datasettet du vil formatere - A6: B9.
  2. Klikk på Betinget formatering i kategorien Stiler på Hjem-fanen. I Excel 2003 velger du Betinget formatering fra Format-menyen og hopper til trinn 5.
  3. Velg Ny regel fra rullegardinlisten.
  4. I toppruten velger du alternativet Bruk en formel for å bestemme hvilke celler som skal formateres.
  5. Skriv inn formelen = $ B6 = I DAG () + $ B $ 2 Match den absolutte og relative referansen nøyaktig. Hvis du bruker Excel 2003, ikke glem å velge Formel Er fra rullegardinmenyen, slik at du kan angi formelen.
  6. Klikk på Format. Velg en farge på Fyll-fanen (eller Mønster-fanen i tidligere versjoner) og klikk OK. På dette tidspunktet kan du forhåndsvise formatet ( figur F ).
  7. Klikk OK for å gå tilbake til arket.

Figur F

Lag en ny formateringsregel.

Figur G viser det resulterende betingede formatet. Som du ser er høydepunktet avhengig av verdien i celle B2. Fem dager fra 10. august er 15. august. Tilsvarende er dagen før 10. august 9. august, og det betingede formatet oppdateres deretter. (Hvis du jobber med den nedlastbare eksempelfilen, må du huske at den gjeldende datoen ikke vil være den samme dagen jeg har skutt disse tallene, så formatet vil justere seg deretter.)

Figur G


Et høydepunkt basert på et skiftende antall dager.

Jeg la funksjonen TODAY () til B4 slik at du kunne se den faktiske datoen. Teknikken krever ikke denne funksjonen; det er en visuell bekvemmelighet.

Plassering av endring og ekstra fleksibilitet

Hvis den betingede oppdateringen er en engangsendring, trenger du ikke å justere arket - bare oppdater regelen. Hvis tilstanden endres ofte, kan du arbeide disse oppdateringene i arket ditt for å gi litt fleksibilitet. Endring av forhold kan enkelt imøtekommes på denne måten når du bruker Excel's betingede formateringsfunksjon.

Send meg spørsmålet ditt om Office

Jeg svarer på lesernes spørsmål når jeg kan, men det er ingen garanti. Vær så spesifikk som mulig når du kontakter meg. For eksempel vil "Vennligst feilsøke arbeidsboken min og fikse hva som er galt" sannsynligvis ikke få noe svar, men "Kan du fortelle meg hvorfor denne formelen ikke returnerer forventede resultater?" kanskje. Jeg får ikke refundert TechRepublic for min tid eller kompetanse, og ber heller ikke om et gebyr fra leserne. Du kan kontakte meg på

© Copyright 2020 | mobilegn.com