Een formule voor voorwaardelijke opmaak instellen die een gemengde verwijzing gebruikt. De meeste formules voor voorwaardelijke opmaak vereisen een absolute verwijzing. Maar deze spreadsheet om vrachtwagens op een erf te volgen vereist
Bekijk video
- Anderson is op zoek naar een manier om gegevensblokken met gemengde voorwaardelijke opmaak te kunnen kopiëren
- Is er een manier om de dollartekens te verwijderen nadat de voorwaardelijke opmaak is ingesteld?
- Nee - niet zonder tientallen nieuwe regels te introduceren
- Mijn oplossing: helpercellen die relatieve verwijzingen gebruiken om de gemengde verwijzing in voorwaardelijke opmaak te vervangen
- Andere technieken in deze aflevering:
- Als u vier voorwaardelijke opmaakregels heeft, stelt u de eerste drie in en stelt u vervolgens de vierde regel in als standaardkleur
- Uitvoering # 1: Druk op F2 om te voorkomen dat Excel celverwijzingen invoegt in het dialoogvenster voor voorwaardelijke opmaak
- Outtake # 2: voorwaardelijke opmaak instellen
Videotranscriptie
Leer Excel van Podcast-aflevering 2105: voorwaardelijk formaat kopiëren met gemengde verwijzingen
Hé, welkom terug bij de netcast. Dit wordt vandaag een gecompliceerde. Ik deed gisteren een seminar en een van de mensen in het seminar, Anderson, had een interessant spreadsheet met een probleem. Oké, en Anderson beheert een werf - trailers komen aan en trailers moeten binnen drie dagen worden gelost. Oké, dus dit is - hij begint, weet je, dit was de dag, dit waren de trailers die aankwamen en dan heeft hij een voorwaardelijke opmaak ingesteld dat zodra de trailer is gelost, deze in blauw verandert. Als iets eenmaal blauw is, is alles geweldig. Maar dan wil hij dingen een kleurcode geven. Als er vandaag of gisteren iets is aangekomen, krijgt het een kleurcode als groen. Dus vandaag is het 29 juni 2017 dus dit is gisteren aangekomen en alles wat niet gelost is is groen maar als het meer dan een dag oud is,we willen dingen geel markeren en als het meer dan twee dagen oud is, zijn dat de problemen die we dingen als rood willen markeren. En het is niet dat, weet je, dit is een werkblad om de hele tuin te beheren, toch? Het is niet zo dat er een blad is voor dingen die op de 26e zijn aangekomen en nog een voor de 27e en nog een voor de 28e. En je weet dat de moeilijkheid is dat als er een nieuwe dag komt, ze ofwel de vorige dag kopiëren naar hier of naar beneden.ze kopiëren ofwel de vorige dag naar hier of naar beneden.ze kopiëren ofwel de vorige dag naar hier of naar beneden.
Oké, het punt van deze video gaat niet over het instellen van deze voorwaardelijke opmaak. Dus ik ga dit versnellen, maar als je geïnteresseerd bent in het instellen van deze voorwaardelijke opmaak, zal ik de versie zonder versnelling als outtake aan het einde van de video plaatsen.
Oké, daar zijn we dan. Versneld dat, je kunt aan het einde kijken om te zien hoe dat werkt. Doe gewoon een test hier, CTRL; verandert in blauw. Als dit teruggaat naar 6/26, verandert het in rood en als het vandaag is, werkt het niet. Dat klopt, want hier is wat ik ga doen, mijn vierde regel groen is vandaag of gisteren aangekomen, ik ga dat gewoon als standaard gebruiken. Als geen van deze andere drie regels waar is, wordt het groen dat het me een regel minder geeft waar ik hier mee te maken heb, oké?
Oké, dus we zijn nu op het punt waar we in wezen Anderson's probleem hebben. Ik ga 25-6-2017 plaatsen, deze worden allemaal rood behalve degene die zijn gelost. En nu gaat het leven verder, het is de volgende dag. We hebben een aantal trailers binnen op 6/26 en dus kopieert Anderson deze gegevens, plak hier, formatteert Column AutoFit, en dit wordt Trailer 15. Klik om dat naar beneden te kopiëren en op te hogen, verwijder degenen die zijn aangekomen. En dus is deze vandaag aangekomen, dus deze zouden allemaal groen moeten worden, maar ze worden niet groen. Waarom worden ze niet groen? Ze worden niet groen omdat deze formules, deze formules voor voorwaardelijke opmaak hier, we zullen deze bekijken. Ze zijn hard gecodeerd om $ A $ 1 te gebruiken. Oh, dat is echt erg.
Oké, dus laten we proberen de dingen hier te verbeteren. Het eerste wat ik kan doen, is dat ik die allemaal kwijtraak en terugkom naar deze originele dataset en een beetje slimmer zijn bij de tweede doorgang en zeggen dat we het niet echt hoeven te vergrendelen tot kolom A. Ik zal dat $ -teken verwijderen. Met andere woorden, het zal altijd de kolom links van ons zijn, dus dat wordt een gemengde verwijzing, maar we moeten altijd naar de $ 1 wijzen. We zullen deze regel bewerken, klik op OK. Oké, met die ene wijziging toen we naar rechts kopieerden en nieuwe gegevens invoegen, zoals de datum van vandaag, werkt het. Oké, dus dit is geweldig. Het leven wordt geweldig op 26 juni en het leven wordt geweldig op 27 juni. Oké, werkt prima. Maar nu komen we het probleem tegen waarbij we te weinig ruimte op de pagina hebben en dus wat Anderson heeft gedaan, gaat naar beneden,begint in wezen een nieuwe rij en plakt en dit zou 6/28 zijn, maar het wordt niet groen.
Waarom wordt het niet groen? Het wordt niet groen omdat ik nog steeds de $ moest gebruiken om weer bij de 1 te komen. Oké, en nu is hier het raadsel, hier is het probleem. Wat doe je nu? En ik meen het, wat doe je nu? Ik wil in de YouTube-reacties horen wat je nu zou doen.
Weet je, dus kijk, er is een argument gemaakt dat dit goed is, we zouden hier kunnen stoppen, want door de A $ 1 te gebruiken, hebben we het zo gemaakt, het leven is gemakkelijk op dag 1, kopieer naar dag 2, het leven is geweldig . Dag 3 is het leven geweldig. Het is pas elke 4e dag dat we hier naar beneden kopiëren dat Anderson naar binnen zou moeten gaan en voorwaardelijke opmaak moet instellen, deze moet bewerken, de regel moet wijzigen, die 1 moet wijzigen in 18. Klik op OK, bewerk deze regel en verander die 1 in zijn 18. Klik op OK, klik op OK. Oké, dus dag 4, die kleine aanpassing kopieer voor dag 5, kopieer voor dag 6 en kopieer dan voor dag 7. Voer die stappen opnieuw uit. Maar goed, laten we eerlijk zijn. Dit werkblad is zes maanden geleden opgesteld met deze voorwaardelijke opmaakregels en ze hoeven alleen maar te werken. We hoeven niet steeds opnieuw voorwaardelijke opmaak te doen.
Mijn eerste reactie was dat ik ga doen alsof dit een spreadsheet is waar ik een aantal formules hier heb en die formules zijn gebouwd met absolute verwijzingen, maar ik heb die formules nodig om te kunnen worden gekopieerd of naar beneden, en om relatief te zijn binnen de kopie - zowel wanneer ik naar hier kopieer als wanneer ik naar hier kopieer. Oké, en om dat te laten werken, ga ik absolute verwijzingen gebruiken wanneer ik dingen instel, maar dan ga ik Zoeken en vervangen gebruiken, Ctrl H.En laten we zeggen, laten we die relatieve verwijzingen verwijderen, verander elke $ A $ 1 in A1, Alles vervangen, klik op Sluiten en nu is dit blok, al deze formules zijn helemaal anders, kopieer, plak en plak en het zal werken. Het zal relatief zijn. Dus ik zei: oké, dat is wat we moeten doen. We moeten die $ uit de formule halen.En dus ging ik een macro schrijven waarmee ik elk van deze voorwaardelijke opmaakregels kon bewerken. Oké, en voordat ik die macro schreef, wilde ik de macro opnemen van het wijzigen van één regel voor voorwaardelijke opmaak, maar het is niet zo dat er hier 14 regels voor voorwaardelijke opmaak zijn. Het gaat hier niet eens om de 14 * 3, 42 regels voor voorwaardelijke opmaak. Er zijn hier slechts 3 regels voor voorwaardelijke opmaak en we passen die 3 regels voor voorwaardelijke opmaak toe op een reeks cellen.s slechts 3 regels voor voorwaardelijke opmaak hier en we passen die 3 regels voor voorwaardelijke opmaak toe op een reeks cellen.s slechts 3 regels voor voorwaardelijke opmaak hier en we passen die 3 regels voor voorwaardelijke opmaak toe op een reeks cellen.
Dus als ik dit zou veranderen, zou ik eerst deze 3 regels voor voorwaardelijke opmaak moeten nemen en er 42 regels voor voorwaardelijke opmaak van moeten maken. En dan begin ik ineenkrimpen, want zoals Anderson van hier naar hier kopieert, gaat hij 42 nieuwe regels introduceren en daarna 42 nieuwe regels. En in de loop van één vel papier met waarschijnlijk 15 dagen, zal hij meer dan 600 regels introduceren, 600 verschillende formaten en dat wordt gewoon verschrikkelijk. Je zult uiteindelijk de te veel opmaakregel raken, om nog maar te zwijgen van het feit dat het moeilijk zal worden om in te stellen, zelfs als we een macro hebben om het in te stellen. Het wordt moeilijk om op te zetten.
Oké, dus wat moeten we doen? Dit is wat ik bedacht en ik wil horen of je iets beters hebt dan dat. Ik zei tegen Anderson, ik zei: "Weet je, kijk, het is vrij simpel. Deze kijken allemaal naar één berekening en die berekening is = VANDAAG - de datum links van mij. " En zou het niet gaaf zijn als we dat antwoord zouden kunnen hebben in een kleine hulpkolom hier aan de rechterkant. En in feite hoeven we helemaal geen $ te gebruiken, we zullen gewoon al die cellen helemaal neerzetten met die eenvoudige kleine formule.
Ik kan de blik naar Anderson's gezicht zien, hij wil niet dat dat extra spul daarbuiten wordt gewist, maar dat is oké. We kunnen ons verbergen, dat later verbergen, zodat we terugkomen in deze cellen en onze voorwaardelijke opmaak ingaan. Die hele TODAY-A1 zal gewoon naar C3 wijzen en dat wordt een relatieve referentie. Met andere woorden, in welke cel we ook zitten, we zullen altijd in de cel aan de rechterkant kijken, klik op OK, schrijf hier en klik op OK. We willen deze gegevens hier verbergen, dus ik ga naar en CTRL 1. Ik ga de drie puntkomma's gebruiken - ;;;, klik op OK. Ik ga daar precies hetzelfde doen. Ik druk op F4, herhaal de laatste actie.
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é, daar zijn we dan. Versneld dat, je kunt aan het einde kijken om te zien hoe dat werkt. Doe hier gewoon een test. CTRL; verandert in blauw. Als dit teruggaat naar 6/26, verandert het in rood. En als het vandaag is, werkt het niet. Dat klopt, want hier is wat ik ga doen. Mijn vierde regel, groen is vandaag of gisteren aangekomen, ik ga dat gewoon als standaard gebruiken. Als geen van deze andere drie regels waar is, wordt het groen dat het me een regel minder geeft waar ik hier mee te maken heb. Oké.
Download bestand
Download het voorbeeldbestand hier: Podcast2105.xlsx
