Sette opp en betinget formateringsformel som bruker en blandet referanse. De fleste betingede formateringsformler krever en absolutt referanse. Men dette regnearket for å spore lastebiler i en hage krever
Se på video
- Anderson leter etter en måte å kunne kopiere datablokker som inneholder blandet betinget formatering
- Er det en måte å fjerne dollartegnene når betinget formatering er satt opp?
- Nei - ikke uten å innføre dusinvis av nye regler
- Min løsning: hjelperceller som bruker relative referanser for å erstatte den blandede referansen i betinget formatering
- Andre teknikker i denne episoden:
- Hvis du har fire betingede formateringsregler, setter du opp de første 3 og deretter gjør den fjerde regelen til standardfargen
- Outtake # 1: Trykk F2 for å stoppe Excel fra å sette inn cellereferanser i dialogboksen betinget formatering
- Outtake # 2: sette opp betinget formatering
Videoutskrift
Lær Excel fra Podcast Episode 2105: Kopiering av betinget format med blandede referanser
Hei, velkommen tilbake til netcast. Dette kommer til å bli komplisert i dag. Jeg holdt på med et seminar i går, og en av menneskene på seminaret, Anderson, hadde et interessant regneark med et problem. Greit, og Anderson administrerer et hage - tilhengere ankommer og tilhengere må losses innen tre dager. Greit, så dette er - han begynner, du vet, dette var dagen, dette var trailerne som ankom, og så har han satt opp betinget formatering at når traileren er losset, endres den til blå. Når noe er blått, er alt bra. Men så vil han fargelegge ting. Hvis noe kom i dag eller i går, blir det fargekodet som grønt. Så i dag er 29. juni 2017, så dette kom i går, og alt som ikke er losset er grønt, men når det er mer enn en dag gammelt,vi ønsker å markere ting som gule, og når de er mer enn to dager gamle, er det problemene vi vil markere ting som rødt. Og det er ikke det, vet du, dette er ett regneark for å administrere hele hagen, ikke sant? Det er ikke det at det er et ark for ting som ankom 26. og et annet for det 27. og et annet for det 28.. Og du vet at vanskeligheten er når en ny dag kommer, de kopierer enten forrige dag hit eller ned hit.de kopierer enten forrige dag hit eller ned hit.de kopierer enten forrige dag hit eller ned hit.
Greit nå, poenget med denne videoen handler ikke om hvordan du konfigurerer denne betingede formateringen. Så jeg kommer til å øke hastigheten på dette, men hvis du er interessert i hvordan du konfigurerer denne betingede formateringen, vil jeg sette den ikke-hastige versjonen som et uttak på slutten av videoen.
Ok, så der er vi. Sped det opp, kan du se på slutten for å se hvordan det fungerer. Bare gjør en test her, CTRL; vil endre seg til blått. Hvis dette går tilbake til 6/26, vil det skifte til rødt, og hvis det er i dag, fungerer det ikke. Det stemmer fordi her er hva jeg skal gjøre, min fjerde regel green kom i dag eller i går, jeg skal bare bruke det som standard. Hvis ingen av disse tre andre reglene er sanne, blir det grønt at det vil gi meg en mindre regel som jeg må takle her, ok?
Ok, så vi er nå på det punktet hvor vi egentlig har Andersons problem. Jeg skal legge inn 25.6.2017, disse blir alle røde bortsett fra de som er blitt losset. Og nå går livet videre, det er neste dag. Vi fikk inn noen trailere den 26/26, og så kopierer Anderson disse dataene, limer inn her, formater Kolonn AutoFit, og dette blir Trailer 15. Klikk for å kopiere det ned og øke, bli kvitt de som kom. Og så denne kom i dag, så disse skulle alle bli grønne, men de ble ikke grønne. Hvorfor blir de ikke grønne? De blir ikke grønne fordi disse formlene, disse betingede formateringsformlene her, vi ser på disse. De er hardkodede for å bruke $ A $ 1. Å, det er veldig ille.
Ok, så la oss prøve å forbedre ting her. Det første jeg kan gjøre, jeg skal kvitte meg med alle disse og komme tilbake til dette originale datasettet og være litt smartere på det andre passet og si at vi ikke egentlig trenger å låse det ned til kolonne A. Jeg blir kvitt det $ tegnet. Med andre ord, det kommer alltid til å være kolonnen til venstre for oss, så det kommer til å være en blandet referanse, men vi må alltid peke på $ 1. Vi redigerer denne regelen, klikker OK. Greit nå, med den ene endringen da vi kopierte til høyre og satte inn nye data, som dagens dato, fungerer det. Ok, så dette er flott. Livet kommer til å bli bra den 26/26 og livet kommer til å bli bra den 26/27. Greit, fungerer bra. Men nå støter vi på problemet der vi går tom for plass på siden, og det som Anderson har gjort er å gå ned,starter i utgangspunktet en ny rad og lim, og dette vil være 6/28, men det blir ikke grønt.
Hvorfor blir det ikke grønt? Det blir ikke grønt fordi jeg fremdeles måtte bruke $ for å komme tilbake til 1. Greit, og så nå er her gåten, her er problemet. Hva gjør du nå? Og jeg er seriøs, hva gjør du nå? Jeg vil høre i YouTube-kommentarene hva du ville gjort nå.
Du vet, så hei se, det er et argument argumentert for at dette er bra, vi kan stoppe akkurat her, for ved å bruke A $ 1 gjorde vi det slik, livet er enkelt på dag 1, kopier til dag 2, livet er flott . Dag 3 livet er flott. Det er bare hver fjerde dag når vi kopierer herfra at Anderson måtte gå inn og sette opp betinget formatering, redigere denne, redigere regelen, endre den ene til å være 18. Klikk OK, rediger denne regelen og endre den 1 for å være 18. Klikk OK, klikk OK. Greit, så dag 4, den lille justeringskopien for dag 5, kopier over for dag 6 og kopier deretter over for dag 7. Gjør disse trinnene igjen. Men hei, la oss innse det. Dette regnearket ble satt opp for seks måneder siden med disse betingede formateringsreglene, og de trenger bare å fungere. Vi trenger ikke gå inn og gjøre betinget formatering igjen og igjen og igjen.
Min første reaksjon var at jeg skal late som om dette er et regneark der jeg har noen formler her, og disse formlene ble bygget med absolutte referanser, men jeg trenger disse formlene for å kunne kopieres over eller ned, og være relative i kopien - både når jeg kopierer hit og når jeg kopierer hit. Greit, og for å få det til å fungere, skal jeg bruke absolutte referanser når jeg setter opp ting, men så skal jeg bruke Finn og erstatt, Ctrl H. Og la oss si la oss bli kvitt disse relative referansene, endre hver $ A $ 1 til A1, erstatt alt, klikk Lukk og nå er denne blokken, alle disse formlene er forskjellige helt ned, kopier, lim inn og lim inn, og det vil fungere. Det vil være relativt. Så jeg sa, ok, det er det vi trenger å gjøre. Vi må ta disse $ ut av formelen.Og så skulle jeg skrive en makro som tillater meg å redigere hver av disse betingede formateringsreglene. Greit, og før jeg skrev den makroen, skulle jeg registrere makroen for å endre en betinget formateringsregel, men det er ikke at det er 14 betingede formateringsregler her. Det er ikke engang de 14 * 3, 42 betingede formateringsreglene her. Det er bare tre betingede formateringsregler her, og vi bruker de 3 betingede formateringsreglene til en rekke celler.s bare 3 betingede formateringsregler her, og vi bruker de 3 betingede formateringsreglene til en rekke celler.Det er bare 3 betingede formateringsregler her, og vi bruker de 3 betingede formateringsreglene til en rekke celler.
Så hvis jeg ville endre dette, er det første jeg må gjøre å ta disse 3 betingede formateringsreglene og få dem til å være 42 betingede formateringsregler. Og så begynner jeg å krype fordi når Anderson kopierer herfra og hit, skal han introdusere 42 nye regler og deretter 42 nye regler. Og i løpet av ett ark med sannsynligvis 15 dager, kommer han til å introdusere over 600 regler, 600 forskjellige formater, og det blir bare fryktelig. Du kommer til slutt til å slå den for mange formateringsregelen, for ikke å nevne at det kommer til å være vanskelig å sette opp, selv om vi har en makro for å sette den opp. Det blir vanskelig å sette opp.
Ok, så hva gjør vi? Dette er hva jeg kom på, og jeg vil høre om du har noe bedre enn det. Jeg sa til Anderson, jeg sa: “Du vet, se det er ganske enkelt. Alle disse ser på en beregning, og beregningen er = I DAG - datoen til venstre for meg. ” Og ville det ikke vært kult, hvis vi kunne ha det svaret i en liten hjelpekolonne her til høyre. Og faktisk trenger vi ikke bruke noen $ i det hele tatt, vi legger bare alle disse cellene helt ned med den enkle lille formelen.
Jeg kan se blikket på Andersons ansikt, han vil ikke at de ekstra tingene der ute slettes, men det er greit. Vi kan skjule, skjule det senere, så vi kommer tilbake i disse cellene og går inn i vår betingede formatering. Hele DAG-A1 vil ganske enkelt peke på C3, og det vil være en relativ referanse. Så med andre ord, uansett hvilken celle vi befinner oss i, skal vi alltid se i cellen til høyre, klikk OK, skriv til denne, klikk OK. Vi ønsker å skjule disse dataene her, så jeg går inn og CTRL 1. Jeg skal bruke de tre semikolonene - ;;; Klikk OK. Jeg skal gjøre nøyaktig det samme der. Jeg trykker på F4, gjentar den siste handlingen.
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.
Ok, så der er vi. Sped det opp, kan du se på slutten for å se hvordan det fungerer. Bare gjør en test her. CTRL; vil endre seg til blått. Hvis dette går tilbake til 6/26, vil det skifte til rødt. Og hvis det er i dag, fungerer det ikke. Det stemmer fordi her er hva jeg skal gjøre. Min fjerde regel, grønn ankom i dag eller i går, jeg skal bare bruke den som standard. Hvis ingen av disse tre andre reglene er sanne, vil det være grønt at det vil gi meg en mindre regel som jeg må takle her. Ok.
Last ned fil
Last ned eksempelfilen her: Podcast2105.xlsx
