Pro tip: Opbevar ændrede betingelser i Excel's betingede format-funktion

Excel's betingede formateringsfunktion tillader on-the-fly formatering. For eksempel kan du fremhæve poster, når en forfaldsdato er den aktuelle dato - eller du kan fremhæve poster for en bestemt projektleder. Hvis din regel ændres, skal du dog opdatere reglen. Hvis dette sker ofte, har du muligvis brug for en mere fleksibel løsning.

For at følge denne artikel kan du arbejde med ethvert simpelt datasæt eller downloade eksemplet .xls eller .xlsx-projektmappe.

Problemet

Normalt justeres betingede formater, når du indtaster og opdaterer værdier. Det er let at tillade forventede ændringer - det er, hvad funktionen gør! Men hvad sker der, når betingelsen snarere end dataene ændres? Lad os overveje et simpelt datasæt, der fremhæver poster for projektets leder. Hvis lederen skifter, skal du opdatere de betingede regler. Det er ikke en stor ting at opdatere en regel én gang. Det er imidlertid kedeligt at opdatere disse regler ofte eller opdatere flere regler på en gang!

Det enkle datasæt i figur A er en forenklet version af, hvad du måtte støde på. En simpel betinget regel, = $ A2 = "Bill", opdaterer poster for projektets leder, Bill. Hvis du ændrer navnet, fjerner reglen markeringen. Her bliver tingene lidt vanskelige. Når du tildeler posten igen, ønsker du, at fremhævningen forsvinder, som vist i figur A. Posten tilhører ikke længere Bill, projektlederen. Sådan fungerer betinget formatering. På den anden side, hvis John er den nye projektleder, skal du opdatere den underliggende regel, fordi du ønsker, at fremhævningen skal forblive. Højdepunktet tilhører projektlederen, ikke udelukkende Bill eller John.

Figur A

En betinget regel fremhæver poster for Bill; når du ændrer navnet, forsvinder højdepunktet.

I en perfekt verden opdaterer du simpelthen reglen = $ A2 = "John" og fortsætter. Når der ofte sker ændringer, skal du evaluere opsætningen igen. Projektlederpositionen er betingelsen, ikke navnet på den person, der tilfældigvis er projektleder på et givet tidspunkt. At imødekomme denne ændrede tilstand i dit datasæt og efterfølgende, i din betingede regel, er den bedste løsning. Det kan du muligvis gøre ved at tilføje en kolonne for hver persons position i teamet og bruge denne kolonne til denne bestemte regel. At gøre det i den virkelige verden er imidlertid ikke altid en mulighed.

En henvisende løsning

Forsøg ikke at blive for hængende på det enkle og (meget) foragtede eksempel. Eksemplet datasæt er ikke vigtigt - den mulighed, jeg tilbyder er. Når det ikke er muligt at ændre datasættet (for at inkludere en mere stabil tilstand), kan du henvise til den ændrede tilstand på arket som vist i figur B.

Figur B

Henvisning til den ændrede tilstand i arket.

Som du kan se, har jeg indtastet projektlederens navn i celle A2. Lad os nu indtaste en regel, der matcher dataene i cellen:

  1. Vælg det datasæt, du vil formatere - A6: B9.
  2. På fanen Hjem skal du klikke på Betinget formatering i gruppen Styles. I Excel 2003 skal du vælge Betinget formatering i menuen Format og springe til trin 5.
  3. Vælg Ny regel på rullelisten.
  4. I den øverste rude skal du vælge Brug en formel til at bestemme, hvilke celler der skal formateres.
  5. Indtast formlen = $ A6 = $ A $ 2 I Excel 2003 skal du vælge Formel Er fra rullemenuen, inden du indtaster formlen. Match den absolutte og relative henvisning nøjagtigt. Du vil have, at Excel skal overveje alle rækkerne i det valgte datasæt, men reglen skal altid referere til den tekst, du matcher i celle A2.
  6. Klik på Format. Vælg en farve på fanen Udfyld (Mønstre i tidligere versioner) og klik på OK. På dette tidspunkt kan du få vist dit format ( figur C ).
  7. Klik på OK for at vende tilbage til arket.

Fig

På dette tidspunkt kan du se et eksempel på dit format.

Det er mest sandsynligt, at du ønsker at slette den oprindelige regel ved at vælge Administrer regler fra indstillingen Betinget formatering. Vælg derefter dette regneark i rullemenuen Vis formateringsregler til. Vælg den oprindelige regel, og klik derefter på Slet regel. Hvis reglen indeholder andre betingelser, skal du ændre den i stedet for at slette den.

Nu kan du tildele posten igen og ændre projektlederen uden at justere den underliggende regel. Hvis du for eksempel indtaster John i celle A6, forsvinder højdepunktet. Bill er stadig projektlederen, og der er ingen poster for ham i datasættet. Hvis du indtaster John i A2, justeres højdepunktet i overensstemmelse hermed, som vist i figur D. Johns plade (i stedet for Bill's) er nu fremhævet.

Figur D


Tillad betingede ændringer.

Komplicere betingelsen

En mere kompliceret tilstand ændrer ikke denne løsning. Du skal blot finde den komponent, der ændres, indtaste den ændrede værdi i arket og henvise til den komponent i den betingede regels formel. Lad os for eksempel antage, at du vil fremhæve poster, hvor datoen i kolonne B er i forhold til den aktuelle dato, men den relative faktor ændres. Nogle gange vil du fremhæve datoer, der matcher den aktuelle dato. Du ønsker måske også at fremhæve datoer, der er en uge i fremtiden (eller fortiden). Du kan tildele flere betingede højdepunkter, eller du kan henvise til den ændrede værdi. Figur E viser den nødvendige justering af arket - referencedagskomponenten i celle B2.

Figur E

Tilføj en celle til den skiftende dagskomponent.

Lad os tilføje den nye regel:

  1. Vælg det datasæt, du vil formatere - A6: B9.
  2. På fanen Hjem skal du klikke på Betinget formatering i gruppen Styles. I Excel 2003 skal du vælge Betinget formatering i menuen Format og springe til trin 5.
  3. Vælg Ny regel på rullelisten.
  4. I den øverste rude skal du vælge Brug en formel til at bestemme, hvilke celler der skal formateres.
  5. Indtast formlen = $ B6 = DAG () + $ B $ 2 Match den absolutte og relative henvisning nøjagtigt. Hvis du bruger Excel 2003, skal du ikke glemme at vælge Formel Er fra rullemenuen, så du kan indtaste formlen.
  6. Klik på Format. Vælg en farve på fanen Udfyld (eller fanen Mønster i tidligere versioner) og klik på OK. På dette tidspunkt kan du få vist dit format ( figur F ).
  7. Klik på OK for at vende tilbage til arket.

Figur F

Opret en ny formateringsregel.

Figur G viser det resulterende betingede format. Som du kan se, er fremhævningen afhængig af værdien i celle B2. Fem dage fra 10. august er 15. august. Tilsvarende er dagen før 10. august 9. august, og det betingede format opdateres i overensstemmelse hermed. (Hvis du arbejder med den downloadbare eksempelfil, skal du huske, at den aktuelle dato ikke vil være den samme som den dag, jeg skød disse tal, så formatet vil justeres i overensstemmelse hermed.)

Figur G


Et højdepunkt baseret på et skiftende antal dage.

Jeg føjede funktionen TODAY () til B4, så du kunne se den aktuelle dato. Teknikken kræver ikke denne funktion; det er en visuel bekvemmelighed.

Plads til ændring og tilføjelse af fleksibilitet

Hvis din betingede opdatering er en engangsændring, behøver du ikke at justere dit ark - bare opdater reglen. Hvis betingelsen ændres ofte, skal du opdatere disse opdateringer på dit ark for at tilføje en smule fleksibilitet. Ændring af betingelser kan let imødekommes på denne måde, når du bruger Excel's betingede formateringsfunktion.

Send mig dit spørgsmål om Office

Jeg besvarer læsernes spørgsmål, når jeg kan, men der er ingen garanti. Når du kontakter mig, skal du være så specifik som muligt. For eksempel "Fejlfinding i min projektmappe og rettelse af hvad der er galt" får sandsynligvis ikke noget svar, men "Kan du fortælle mig, hvorfor denne formel ikke returnerer de forventede resultater?" magt. Jeg får ikke refunderet TechRepublic for min tid eller ekspertise, og jeg beder heller ikke om et gebyr fra læserne. Du kan kontakte mig på

© Copyright 2020 | mobilegn.com