Konfigurera en villkorlig formateringsformel som använder en blandad referens. De flesta villkorliga formleringsformler kräver en absolut referens. Men det här kalkylarket för att spåra lastbilar i en trädgård kräver
Kolla på video
- Anderson letar efter ett sätt att kunna kopiera datablock som innehåller blandad villkorlig formatering
- Finns det ett sätt att ta bort dollartecknen när villkorlig formatering har ställts in?
- Nej - inte utan att införa dussintals nya regler
- Min lösning: hjälpceller som använder relativa referenser för att ersätta den blandade referensen i villkorlig formatering
- Andra tekniker i detta avsnitt:
- Om du har fyra villkorliga formateringsregler, ställ in de första 3 och gör sedan den fjärde regeln som standardfärg
- Utgång # 1: Tryck på F2 för att stoppa Excel från att infoga cellreferenser i dialogrutan för villkorlig formatering
- Uttag # 2: ställa in villkorlig formatering
Videoutskrift
Lär dig Excel från Podcast avsnitt 2105: Kopiera villkorligt format med blandade referenser
Hej, välkommen tillbaka till netcast. Detta kommer att bli komplicerat idag. Jag gjorde ett seminarium igår och en av folket på seminariet, Anderson, hade ett intressant kalkylark med ett problem. Okej, och Anderson hanterar en gård - släpvagnar anländer och släpvagnar måste lossas inom tre dagar. Okej, så det här är - han börjar, du vet, det var dagen, det var släpvagnarna som anlände och sedan har han ställt in villkorlig formatering som när släpvagnen lossas ändras den till blå. När något är blått är allt bra. Men då vill han färgkoda saker. Om något kom fram idag eller igår blir det färgkodat som grönt. Så idag är den 29 juni 2017 så det anlände igår och allt som inte lossas är grönt men när det är mer än en dag gammalt,vi vill markera saker som gula och när det är mer än två dagar gammalt är det problemen vi vill markera saker som röda. Och det är inte det, du vet, det här är ett kalkylblad för att hantera hela gården, eller hur? Det är inte så att det finns ett ark för saker som anlände den 26 och en annan för den 27: e och en annan för den 28: e. Och du vet att svårigheten är när en ny dag kommer, de antingen kopierar föregående dag hit eller ner hit.de kopierar antingen föregående dag hit eller ner hit.de kopierar antingen föregående dag hit eller ner hit.
Okej nu, poängen med den här videon handlar inte om hur man ställer in denna villkorliga formatering. Så jag kommer att påskynda det här, men om du är intresserad av hur du ställer in den här villkorliga formateringen kommer jag att lägga till den oskyndade versionen som ett uttag i slutet av videon.
Okej, så där är vi. Påskyndat, du kan titta på i slutet för att se hur det fungerar. Gör bara ett test här, CTRL; ändras till blått. Om detta går tillbaka till 26/26 ändras det till rött och om det är idag fungerar det inte. Det stämmer för här är vad jag ska göra, min fjärde regel green anlände idag eller igår, jag ska bara använda det som standard. Om ingen av dessa andra tre regler är sanna kommer det att bli grönt att det ger mig en mindre regel som jag måste hantera här, okej?
Okej, så vi är nu på den punkt där vi i huvudsak har Andersons problem. Jag ska sätta in 2017-06-25, dessa blir alla röda förutom de som har lossats. Och nu går livet vidare, det är nästa dag. Vi fick in några trailers den 6/26 och så kopierar Anderson dessa data, klistrar in här, formaterar Column AutoFit, så kommer det att vara Trailer 15. Klicka för att kopiera nedåt och öka, bli av med de som anlände. Och så kom den här idag, så alla borde bli gröna men de blir inte gröna. Varför blir de inte gröna? De blir inte gröna eftersom dessa formler, dessa villkorliga formateringsformler här, vi kommer att titta på dessa. De är hårdkodade för att använda $ A $ 1. Åh, det är riktigt dåligt.
Okej, så låt oss försöka förbättra saker här. Det första jag kan göra, jag kommer att bli av med alla dessa och komma tillbaka till den här ursprungliga datamängden och vara lite smartare vid det andra passet och säga att vi inte riktigt behöver låsa ner det till kolumn A. Jag blir av med det $ -tecknet. Med andra ord kommer det alltid att vara kolumnen till vänster om oss så det blir en blandad referens men vi måste alltid peka på $ 1. Vi redigerar den här regeln, klickar på OK. Okej nu, med den här ändringen när vi kopierade till höger och lade in nya data i, som dagens datum, fungerar det. Okej, så det här är fantastiskt. Livet kommer att bli bra den 26/26 och livet kommer att bli fantastiskt den 26/27. Okej, fungerar bra. Men nu stöter vi på problemet där vi får slut på plats på sidan och så vad Anderson har gjort är att gå ner,startar i huvudsak en ny rad och klistrar in och detta skulle vara 6/28 men det blir inte grönt.
Varför blir det inte grönt? Det blir inte grönt eftersom jag fortfarande var tvungen att använda $ för att komma tillbaka till 1. Okej, och nu är det här problemet, här är problemet. Vad gör du nu? Och jag menar allvar, vad gör du nu? Jag vill höra i YouTube-kommentarerna vad du skulle göra nu.
Du vet, så hej titta, det finns ett argument som gör att det här är bra, vi kan sluta här för att genom att använda A $ 1 gjorde vi det så, livet är enkelt på dag 1, kopiera till dag 2, livet är fantastiskt . Dag 3-livet är fantastiskt. Det är bara var fjärde dag när vi kopierar här ner att Anderson måste gå in och konfigurera villkorlig formatering, redigera den här, redigera regeln, ändra den 1 till att vara 18. Klicka på OK, redigera den här regeln och ändra den 1 för att vara 18. Klicka på OK, klicka på OK. Okej, så dag 4, den lilla justeringskopian för dag 5, kopiera över för dag 6 och kopiera sedan över för dag 7. Gör dessa steg igen. Men hej, låt oss inse det. Det här kalkylbladet skapades för sex månader sedan med dessa villkorliga formateringsregler och de behöver bara fungera. Vi behöver inte gå in och göra villkorlig formatering igen och igen och igen.
Min första reaktion var att jag ska låtsas som att detta är ett kalkylark där jag har några formler här och dessa formler byggdes med absoluta referenser men jag behöver dessa formler för att kunna kopieras över eller ner och vara relativa i kopian - både när jag kopierar hit och när jag kopierar hit. Okej, och för att få det att fungera kommer jag att använda absoluta referenser när jag ställer in saker men då ska jag använda Sök och ersätt, Ctrl H. Och låt oss säga att vi ska bli av med dessa relativa referenser, ändra varje $ A $ 1 till A1, Byt ut alla, klicka på Stäng och nu är det här blocket, alla dessa formler är olika hela vägen ner, kopiera, klistra in och klistra in och det fungerar. Det kommer att vara relativt. Så jag sa, okej, det är vad vi behöver göra. Vi måste ta ut dessa $ ur formeln.Och så skulle jag skriva ett makro som skulle tillåta mig att redigera var och en av dessa villkorliga formateringsregler. Okej, och innan jag skrev det makrot skulle jag spela in makrot för att ändra en villkorlig formateringsregel, men det är inte så att det finns 14 villkorliga formateringsregler här. Det är inte ens de 14 * 3, 42 villkorliga formateringsreglerna här. Det finns bara 3 villkorliga formateringsregler här och vi tillämpar dessa 3 villkorliga formateringsregler på ett antal celler.s bara 3 villkorliga formateringsregler här och vi tillämpar dessa 3 villkorliga formateringsregler på ett antal celler.s bara 3 villkorliga formateringsregler här och vi tillämpar dessa 3 villkorliga formateringsregler på ett antal celler.
Så om jag skulle ändra detta är det första jag måste göra att ta dessa tre villkorliga formateringsregler och göra dem till 42 villkorliga formateringsregler. Och sedan börjar jag krypa för eftersom Anderson kopierar härifrån till här kommer han att införa 42 nya regler och sedan 42 nya regler. Och under ett pappersark med troligen 15 dagar kommer han att införa över 600 regler, 600 olika format och det blir bara hemskt. Du kommer så småningom att slå på alltför många formateringsregler, för att inte tala om att det kommer att bli svårt att ställa in även om vi har ett makro för att ställa in det. Det kommer att bli svårt att ställa in.
Okej, så vad gör vi? Här är vad jag kom på och jag vill höra om du har något bättre än så. Jag sa till Anderson, jag sa, ”Du vet, se det är ganska enkelt. Alla dessa tittar på en beräkning och den beräkningen är = I DAG - det datum som är till vänster om mig. ” Och skulle det inte vara häftigt, om vi kunde ha det svaret i en liten hjälpkolumn här till höger. Och i själva verket behöver vi inte använda någon $ alls, vi lägger bara alla dessa celler hela vägen ner med den enkla lilla formeln.
Jag kan se utseendet på Andersons ansikte, han vill inte att de extra sakerna där ute raderas men det är okej. Vi kan dölja, dölja det senare så att vi kommer tillbaka in i dessa celler och går in i vår villkorliga formatering. Hela DAG-A1 kommer helt enkelt att peka på C3 och det kommer att bli en relativ referens. Så med andra ord, oavsett vilken cell vi befinner oss i kommer vi alltid att titta i cellen till höger, klicka på OK, skriv på den här, klicka på OK. Vi vill dölja dessa data här så jag går in och CTRL 1. Jag ska använda de tre semikolonerna - ;;; klicka på OK. Jag ska göra exakt samma sak där. Jag trycker på F4, upprepar den senaste åtgärden.
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.
Okej, så där är vi. Påskyndat, du kan titta på i slutet för att se hur det fungerar. Gör bara ett test här. CTRL; ändras till blått. Om detta går tillbaka till 6/26 ändras det till rött. Och om det är idag fungerar det inte. Det stämmer för här är vad jag ska göra. Min fjärde regel, grön anlände idag eller igår, jag ska bara använda det som standard. Om ingen av dessa andra tre regler är sanna kommer det att bli grönt att det ger mig en mindre regel som jag måste hantera här. OK.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2105.xlsx
