Opsætning af en betinget formateringsformel, der bruger en blandet reference. De fleste betingede formateringsformler kræver en absolut reference. Men dette regneark til sporing af lastbiler i en have kræver
Se video
- Anderson leder efter en måde at være i stand til at kopiere blokke af data, der indeholder blandet betinget formatering
- Er der en måde at fjerne dollartegnene på, når betinget formatering er oprettet?
- Nej - ikke uden at indføre snesevis af nye regler
- Min løsning: hjælperceller, der bruger relative referencer til at erstatte den blandede reference i betinget formatering
- Andre teknikker i denne episode:
- Hvis du har fire betingede formateringsregler, skal du oprette de første 3 og derefter gøre den fjerde regel til standardfarven
- Outtake # 1: Tryk på F2 for at stoppe Excel i at indsætte cellereferencer i dialogboksen med betinget formatering
- Outtake # 2: opsætning af betinget formatering
Videoudskrift
Lær Excel fra Podcast-afsnit 2105: Kopiering af betinget format med blandede referencer
Hej, velkommen tilbage til netcast. Dette bliver kompliceret i dag. Jeg holdt et seminar i går, og en af folkene på seminaret, Anderson, havde et interessant regneark med et problem. Okay, og Anderson administrerer en gård - trailere ankommer, og trailere skal aflæsses inden for tre dage. Okay, så dette er - han starter, ved du, det var dagen, det var trailerne, der ankom, og så har han opsat betinget formatering, at når traileren er aflæst, skifter den til blå. Når noget er blåt, er alt godt. Men så vil han farve tingene. Hvis noget ankom i dag eller i går, bliver det farvekodet som grønt. Så i dag er den 29. juni 2017, så dette ankom i går, og alt, hvad der ikke losses, er grønt, men når det er mere end en dag gammelt,vi vil fremhæve ting som gule, og når det er mere end to dage gammelt, er det de problemer, vi vil fremhæve ting som rødt. Og det er ikke det, du ved, dette er et regneark til at styre hele haven, ikke? Det er ikke, at der er et ark til ting, der ankom den 26. og et andet til den 27. og et andet til den 28.. Og du ved, at vanskeligheden er, når en ny dag kommer, de kopierer enten den foregående dag til her eller herned.de kopierer enten den foregående dag til her eller ned til her.de kopierer enten den foregående dag til her eller ned til her.
Okay nu, pointen med denne video handler ikke om, hvordan man konfigurerer denne betingede formatering. Så jeg vil komme igennem dette, men hvis du er interesseret i, hvordan du konfigurerer denne betingede formatering, vil jeg sætte den hurtigere op-version som et udtag i slutningen af videoen.
Okay, så der er vi. Sped det op, du kan se i slutningen for at se, hvordan det fungerer. Bare lave en test her, CTRL; skifter til blå. Hvis dette går tilbage til 6/26, skifter det til rødt, og hvis det er i dag, fungerer det ikke. Det er rigtigt, fordi her er hvad jeg skal gøre, min fjerde regel green ankom i dag eller i går, jeg vil bare bruge det som standard. Hvis ingen af disse andre tre regler er sande, bliver det grønt, at det giver mig en mindre regel, som jeg har at gøre med her, okay?
Okay, så vi er nu på det punkt, hvor vi i det væsentlige har Andersons problem. Jeg vil sætte den 6/25/2017, disse bliver alle røde bortset fra dem, der er blevet aflæst. Og nu går livet videre, det er den næste dag. Vi fik nogle trailere ind den 26/26, og så kopierer Anderson disse data, indsæt her, formater Kolonne AutoFit, og dette vil være Trailer 15. Gå klik for at kopiere det ned og forøge, slippe af med dem, der ankom. Og så ankom denne i dag, så disse skulle alle blive grønne, men de bliver ikke grønne. Hvorfor bliver de ikke grønne? De bliver ikke grønne, fordi disse formler, disse betingede formateringsformler lige her, vi ser på disse. De er hårdkodede til at bruge $ A $ 1. Åh, det er virkelig dårligt.
Okay, så lad os prøve at forbedre tingene her. Den første ting jeg kan gøre, jeg vil slippe af med alle dem og komme tilbage til dette originale datasæt og være lidt smartere ved det andet pass og sige, at vi ikke rigtig behøver at låse det ned til kolonne A. Jeg slipper med det $ tegn. Med andre ord vil det altid være kolonnen til venstre for os, så det bliver en blandet reference, men vi skal altid pege på $ 1. Vi redigerer denne regel, klik på OK. Okay nu, med den ene ændring, da vi kopierede til højre og satte nye data i, som dagens dato, fungerer det. Okay, så det er fantastisk. Livet bliver godt den 26/26 og livet bliver godt den 26/27. Okay, det fungerer godt. Men nu løber vi ind i problemet, hvor vi løber tør for plads på siden, og så går Anderson ned,starter i det væsentlige en ny række og pastaer, og dette ville være 6/28, men det bliver ikke grønt.
Hvorfor bliver det ikke grønt? Det bliver ikke grønt, fordi jeg stadig var nødt til at bruge $ til at komme tilbage til 1. Okay, og nu er her gåden, her er problemet. Hvad gør du nu? Og jeg er seriøs, hvad laver du nu? Jeg vil gerne høre i YouTube-kommentarerne, hvad du ville gøre nu.
Du ved, så hej se, der er argumenteret for, at dette er godt, vi kunne stoppe lige her, for ved at bruge A $ 1 gjorde vi det på den måde, livet er let på dag 1, kopier til dag 2, livet er godt . Dag 3 liv er fantastisk. Det er kun hver 4. dag, når vi kopierer herned, at Anderson bliver nødt til at gå ind og oprette betinget formatering, redigere denne, redigere reglen, ændre den 1 til at være 18. Klik på OK, rediger denne regel og rediger den 1, så den skal være 18. Klik på OK, klik på OK. Okay, så dag 4, den lille justeringskopi til dag 5, kopier til dag 6 og kopier derefter til dag 7. Gør disse trin igen. Men hej, lad os indse det. Dette regneark blev oprettet for seks måneder siden med disse betingede formateringsregler, og de skal bare arbejde. Vi behøver ikke at gå ind og foretage betinget formatering igen og igen og igen.
Min første reaktion var, at jeg vil foregive, at dette er et regneark, hvor jeg har nogle formler her, og disse formler blev bygget med absolutte referencer, men jeg har brug for disse formler for at kunne kopieres over eller ned og være relative inden for kopien - både når jeg kopierer til her og når jeg kopierer til her. Okay, og for at få det til at fungere, skal jeg bruge absolutte referencer, når jeg sætter tingene op, men så skal jeg bruge Find og erstat, Ctrl H. Og lad os sige, lad os slippe af med disse relative referencer, skift hver $ A $ 1 til A1, udskift alt, klik på Luk, og nu er denne blok, alle disse formler er forskellige helt ned, kopier, indsæt og indsæt, og det fungerer. Det vil være relativt. Så jeg sagde, okay, det er det, vi skal gøre. Vi er nødt til at tage disse $ ud af formlen.Og så skulle jeg skrive en makro, der gjorde det muligt for mig at redigere hver af disse regler for betinget formatering. Okay, og før jeg skrev denne makro, skulle jeg registrere makroen for at ændre en betinget formateringsregel, men det er ikke, at der er 14 regler for betinget formatering her. Det er ikke engang de 14 * 3, 42 betingede formateringsregler her. Der er kun 3 betingede formateringsregler her, og vi anvender disse 3 betingede formateringsregler til en række celler.s kun 3 betingede formateringsregler her, og vi anvender disse 3 betingede formateringsregler til en række celler.s kun 3 betingede formateringsregler her, og vi anvender disse 3 betingede formateringsregler til en række celler.
Så hvis jeg ville ændre dette, er det første, jeg bliver nødt til at tage disse 3 betingede formateringsregler og gøre dem til 42 betingede formateringsregler. Og så begynder jeg at krybe sammen, for når Anderson kopierer herfra til her, vil han introducere 42 nye regler og derefter 42 nye regler. Og i løbet af et ark papir med sandsynligvis 15 dage vil han indføre over 600 regler, 600 forskellige formater, og det bliver bare forfærdeligt. Du kommer til sidst til at ramme for mange formateringsregel ting, for ikke at nævne at det bliver svært at konfigurere, selvom vi har en makro til at konfigurere den. Det bliver svært at konfigurere.
Okay, så hvad gør vi? Her er hvad jeg kom på, og jeg vil gerne høre, om du har noget bedre end det. Jeg sagde til Anderson, jeg sagde: ”Du ved, se, det er ret simpelt. Alle disse ser på en beregning, og beregningen er = I DAG - den dato, der er til venstre for mig. ” Og ville det ikke være sejt, hvis vi kunne have det svar i en lille hjælpekolonne her til højre. Og faktisk behøver vi slet ikke bruge nogen $, vi lægger bare alle disse celler helt ned med den enkle lille formel.
Jeg kan se kigget på Andersons ansigt, han vil ikke have de ekstra ting derude slettet, men det er okay. Vi kan skjule, skjule det senere, så vi kommer tilbage i disse celler og går ind i vores betingede formatering. Hele DAG-A1 vil simpelthen pege på C3, og det vil være en relativ reference. Så med andre ord, uanset hvilken celle vi befinder os i, vil vi altid se i cellen til højre, klik på OK, skriv til denne, klik på OK. Vi ønsker at skjule disse data herovre, så jeg går ind og CTRL 1. Jeg vil bruge de tre semikoloner - ;;; klik på OK. Jeg vil gøre nøjagtigt det samme der. Jeg trykker på F4, gentager den sidste handling.
Now, the weird thing here is I have to convince myself that this little part, this blank column is part of the whole thing. So I want to just add a light grey there to remind myself that when I copy and paste, I'm going to have to include the grey. Alright, so here's our test now. I will choose this CTRL C and then I'm actually going to paste there and paste there and paste here. Alright, big tests go to 6/26, go to 6/27, change this to yellow, come to 6/28, it should change to green. Beautiful!
Alright, so now it's working, we have essentially replaced that conditional formatting mixed reference with a relative reference and we should be relatively, relatively good to go.
Alright, topics in this episode. We're looking for a way to be able to copy blocks of data containing conditional formatting that essentially is a mixed reference. So, is there some way to remove the $ once the conditional formatting is set up? Well maybe with a macro but you'd be introducing dozens of new rules instead of just one formula applying to a whole block. So my solution was to use helper cells that use relative references and then just use regular references in the conditional formatting.
Other topics in this episode, if you have 4 conditional formatting rules just set the first three and make the fourth rule be the default color. The outtake coming up next is press F2 to stop Excel from inserting cell references in the conditional formatting dialogue and then setting up the conditional formatting dialogue.
Well, I want to thank Anderson for being in my seminar and hopefully, you know, this helps him. I want to thank you for stopping by. We'll see you next time for another netcast from.
I'll take number 1. When you're dealing with conditional formatting in that stupid dialogue box and you need to edit something that's already in there, you better be darn good at clicking in the right spot. Like if I wanted to change that 1 to be 18, and I clicked right there and then press the Right Arrow key then I have to swear because they're inserting cell references instead. Alright, and so many times when I was recording this episode, I clicked in the wrong spot and hit the Right Arrow key or the Left Arrow key or Shift Arrow key and how to back out of it.
Alright, here is the key if this has been driving you crazy for years. Well key number 1, just be perfect about where you click. Right then you don't have any problem at all, life is great but that's not realistic. Here's the whole trick. When you are in this dialogue box, down here in the lower left-hand corner it says that we are in Enter mode and when you're in Enter mode using Left or Right Arrow keys is going to insert cell references for you like that, right? Really, really annoying. But what you want to do is you want to press the F2 key and that changes us from Enter mode to Edit mode. Bingo! Now we can do whatever we want. We can use the Left Arrow key or the Right Arrow key and we're not inserting cells like that.
Alright, next up is I'll take number 2 where I built this original conditional formatting. I showed that in fast motion before here. Just in case you're interested is the slow motion.
So we're going to have a date here. I'm going to put in yesterday's date just or two days ago date so we have something - this is going to work. Alright, and we're going to assume that there's going to be some number of space for trailers to come in. In this case I'll go down to Trailer 14 and then here, we're going to build conditional format. And there are four rules that we want to do. And the first one, the easy one is if there's a date here then we’re going to turn this thing blue, so alt="" O D to get into conditional formatting. I'm going to create a new rule and that new rule is going to be the easy one format, only cells that contain a value that is greater than 0. Then we're going to format this using a blue color like that, click OK, click OK. Alright, first rule done.
Second rule is the thing- the date up in A1, more than one days old. This one is going to be the tricky one and this is where we have to look at a specific cell. So I’m going to have to use a formula and we'll say =TODAY, today will be today’s date minus that date up in A1. If that is>2,>1 then we're going to format it in yellow.
Alright, and I don't know if I need parentheses here, I'm going to just be safe and put the ( ) in and copy that whole thing so I can create the red color. So copy and then we'll add a new rule and rule is going to be if it’s> or =2, we’ll format as red.
Alright, now we have to be careful here. The first thing I want to do is I want to check to see if the thing is filled in. If the thing is filled in, we get the blue, we stop if true. Then the next thing to do, we have to check for the red before we check for the yellow because this formula for yellow is also going to be true on the days when it should be red.
Okay, så der er vi. Sped det op, du kan se i slutningen for at se, hvordan det fungerer. Bare lave en test her. CTRL; skifter til blå. Hvis dette går tilbage til 6/26, skifter det til rødt. Og hvis det er i dag, fungerer det ikke. Det er rigtigt, for her er hvad jeg skal gøre. Min fjerde regel, grøn ankom i dag eller i går, jeg skal bare bruge det som standard. Hvis ingen af disse andre tre regler er sande, bliver det grønt, at det giver mig en mindre regel, som jeg skal håndtere her. I orden.
Download fil
Download eksempelfilen her: Podcast2105.xlsx
