Configurarea unei formule de formatare condiționată care utilizează o referință mixtă. Majoritatea formulelor de formatare condiționată necesită o referință absolută. Dar această foaie de calcul pentru urmărirea camioanelor într-o curte necesită
Urmăriți videoclipul
- Anderson caută o modalitate de a putea copia blocuri de date care conțin formatare condiționată mixtă
- Există o modalitate de a elimina semnele de dolar după configurarea formatării condiționate?
- Nu - nu fără a introduce zeci de reguli noi
- Soluția mea: celule de ajutor care utilizează referințe relative pentru a înlocui referința mixtă în formatarea condiționată
- Alte tehnici din acest episod:
- Dacă aveți patru reguli de formatare condiționată, configurați primele 3 și apoi faceți ca a patra regulă să fie culoarea implicită
- Outake # 1: Apăsați F2 pentru a opri Excel să introducă referințe de celule în dialogul de formatare condiționată
- Executare # 2: configurarea formatării condiționate
Transcriere video
Aflați Excel din Podcast Episodul 2105: Copierea formatului condiționat cu referințe mixte
Hei, bine ai revenit pe netcast. Asta va fi una complicată astăzi. Ieri făceam un seminar și una dintre persoanele din seminar, Anderson, a avut o foaie de calcul interesantă cu o problemă. Bine, iar Anderson gestionează o curte - remorcile sosesc și remorcile trebuie descărcate în termen de trei zile. În regulă, așa este - începe, știi, a fost ziua, acestea au fost remorcile care au sosit și apoi a format o configurare condiționată care, odată ce descărcarea remorcii, se schimbă în albastru. Odată ce ceva este albastru, totul este grozav. Dar apoi vrea să coloreze lucrurile cu cod. Dacă ceva a sosit astăzi sau ieri, acesta devine codificat ca verde. Deci, astăzi este 29 iunie 2017, așa că s-a ajuns ieri și tot ce nu este descărcat este verde, dar când are mai mult de o zi,vrem să evidențiem lucrurile ca fiind galbene și când are mai mult de două zile, acestea sunt problemele pe care vrem să le evidențiem ca roșii. Și nu este asta, știi, aceasta este o fișă de lucru pentru a gestiona întreaga curte, nu? Nu este că există o foaie pentru lucrurile care au sosit pe 26 și alta pentru 27 și alta pentru 28. Și știți că dificultatea este pe măsură ce apare o nouă zi, fie ei copiază ziua precedentă până aici, fie aici.fie copiază ziua precedentă până aici, fie aici.fie copiază ziua precedentă până aici, fie aici.
Bine acum, scopul acestui videoclip nu este despre cum să configurați această formatare condiționată. Așa că voi trece mai repede la această problemă, dar dacă sunteți interesat de cum să configurați această formatare condiționată, voi pune versiunea nespeed up ca extragere la sfârșitul videoclipului.
Bine, așa că suntem. Mai rapid, puteți urmări la final pentru a vedea cum funcționează. Doar să faci un test aici, CTRL; se va schimba în albastru. Dacă acest lucru se întoarce la 6/26, se va schimba în roșu și dacă este astăzi, nu funcționează. Așa este, pentru că iată ce voi face, a patra mea regulă verde a sosit astăzi sau ieri, o voi folosi doar ca implicit. Dacă niciuna dintre aceste alte trei reguli nu este adevărată, atunci va fi verde că îmi va da o regulă mai puțin cu care trebuie să mă ocup aici, bine?
Bine, așa că suntem acum în punctul în care avem în esență problema lui Anderson. Voi introduce 25.06.2017, toate vor deveni roșii, cu excepția celor care au fost descărcate. Și acum viața merge mai departe, este a doua zi. Avem niște trailere pe 6/26 și așa că Anderson copiază aceste date, lipiți aici, formatați Coloana AutoFit, și acesta va fi Trailer 15. Faceți clic pentru a copia în jos și pentru a crește, scăpați de cele care au sosit. Și așa a sosit astăzi, așa că toate ar trebui să devină verzi, dar nu devin verzi. De ce nu se înverzesc? Nu devin verzi, deoarece aceste formule, aceste formule de formatare condiționată chiar aici, ne vom uita la acestea. Acestea sunt codificate greu pentru a utiliza $ A $ 1. Oh, e foarte rău.
Bine, deci să încercăm să îmbunătățim lucrurile aici. Primul lucru pe care îl pot face, voi scăpa de toate acestea și mă voi întoarce la acest set de date original și voi fi un pic mai deștept la a doua trecere și voi spune că nu trebuie să-l blocăm în coloana A. Voi scăpa de acel semn $. Cu alte cuvinte, va fi întotdeauna coloana din stânga noastră, așa că va fi o referință mixtă, dar trebuie întotdeauna să indicăm $ 1. Vom edita această regulă, dați clic pe OK. Bine acum, odată cu acea schimbare când am copiat în dreapta și am introdus date noi, precum data de astăzi, funcționează. Bine, deci este minunat. Viața va fi grozavă pe 26/6 și viața va fi grozavă pe 27/6. Bine, funcționează grozav. Dar acum ne confruntăm cu problema în care rămânem fără spațiu pe pagină și astfel ceea ce Anderson a făcut este să scadă,în esență, începe un nou rând și se lipesc și acesta ar fi 6/28, dar nu devine verde.
De ce nu devine verde? Nu devine verde pentru că a trebuit să folosesc încă $ pentru a reveni la 1. Bine, așa că acum iată enigma, iată problema. Ce faci acum? Și vorbesc serios, ce faci acum? Vreau să aud în comentariile YouTube ce ați face acum.
Știi, așa că, uite, există un argument potrivit căruia este bine, ne-am putea opri chiar aici, deoarece folosind A $ 1, am reușit așa, viața este ușoară în Ziua 1, copiați în Ziua 2, viața este grozavă . Ziua 3 viața este grozavă. Abia în fiecare a 4-a zi când copiem aici, Anderson ar trebui să intre și să configureze formatarea condiționată, să o editeze, să editeze regula, să schimbe 1 pentru a fi 18. Faceți clic pe OK, editați această regulă și modificați 1 ca să fie 18. Faceți clic pe OK, faceți clic pe OK. Bine, deci Ziua 4, acea mică copie de ajustare pentru Ziua 5, copiere pentru Ziua 6 și apoi copiere pentru Ziua 7. Faceți acești pași din nou. Dar hei, să recunoaștem. Această foaie de lucru a fost configurată acum șase luni cu aceste reguli de formatare condiționată și trebuie doar să funcționeze. Nu este nevoie să intrăm și să facem formatare condiționată din nou și din nou și din nou.
Prima mea reacție a fost că mă voi preface ca aceasta este o foaie de calcul în care am câteva formule aici și acele formule au fost construite cu referințe absolute, dar am nevoie de acele formule pentru a putea fi copiate peste sau în jos și să fiu relativă în copie - atât când copiez aici, cât și când copiez aici. În regulă, și pentru ca acest lucru să funcționeze, voi folosi referințe absolute atunci când configurez lucrurile, dar apoi voi folosi Găsiți și înlocuiți, Ctrl H. Și să spunem să scăpăm de acele referințe relative, schimbați fiecare $ A $ 1 la A1, Înlocuiți toate, faceți clic pe Închidere și acum acest bloc, toate aceste formule sunt diferite până la capăt, copiați, lipiți și lipiți și va funcționa. Va fi relativ. Așa că am spus, bine, ei bine, asta trebuie să facem. Trebuie să scoatem acei dolari din formulă.Așa că aveam de gând să scriu o macro care să-mi permită să editez fiecare dintre aceste reguli de formatare condiționată. Bine, și înainte să scriu acea macro, urma să înregistrez macrocomanda schimbării unei reguli de formatare condiționată, dar nu este faptul că există 14 reguli de formatare condiționată aici. Aici nu este vorba nici de regulile de formatare condiționată 14 * 3, 42. Aici există doar 3 reguli de formatare condiționată și aplicăm aceste 3 reguli de formatare condiționată pentru o gamă de celule.Aici sunt doar 3 reguli de formatare condiționată și aplicăm aceste 3 reguli de formatare condiționată pentru o gamă de celule.Aici sunt doar 3 reguli de formatare condiționată și aplicăm aceste 3 reguli de formatare condiționată pentru o gamă de celule.
Deci, dacă aș schimba acest lucru, primul lucru pe care ar trebui să-l fac este să iau aceste 3 reguli de formatare condiționată și să le fac 42 de reguli de formatare condiționate. Și apoi, încep să mă înfund, deoarece Anderson va copia 42 de reguli noi și apoi 42 de reguli noi. Și pe parcursul unei foi de hârtie, cu probabil 15 zile, va introduce peste 600 de reguli, 600 de formate diferite și asta va fi oribil. În cele din urmă, veți atinge prea multe reguli de formatare, fără a menționa că va fi greu de configurat, chiar dacă avem o macro pentru a o configura. Va fi greu de montat.
Bine, deci ce facem? Iată ce am venit și vreau să aud dacă aveți ceva mai bun decât atât. I-am spus lui Anderson, i-am spus: „Știi, uite că e destul de simplu. Toate acestea se uită la un singur calcul și acel calcul este = AZI- data care este în stânga mea ”. Și nu ar fi grozav, dacă am putea avea acel răspuns într-o mică coloană de ajutor de aici, în dreapta. Și, de fapt, nu trebuie să folosim deloc $, ci doar vom pune toate acele celule până la capăt cu acea formulă simplă.
Văd privirea de pe fața lui Anderson, el nu vrea ca lucrurile în plus să fie șterse, dar este în regulă. Putem ascunde, ascunde asta mai târziu, astfel încât să revenim în aceste celule și să intrăm în formatarea noastră condiționată. Întregul TODAY-A1 pur și simplu va indica C3 și va fi o referință relativă. Cu alte cuvinte, indiferent de celula în care ne aflăm, vom căuta întotdeauna în celula din dreapta, faceți clic pe OK, scrieți la aceasta, faceți clic pe OK. Vrem să ascundem aceste date aici, așa că voi intra și CTRL 1. Voi folosi cele trei puncte și virgule - ;;;, faceți clic pe OK. Voi face exact același lucru acolo. Voi apăsa F4, voi repeta ultima acțiune.
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.
Bine, așa că suntem. Mai rapid, puteți urmări la final pentru a vedea cum funcționează. Doar făcând un test aici. CTRL; se va schimba în albastru. Dacă acest lucru revine la 6/26, se va schimba în roșu. Și dacă este astăzi, nu funcționează. Așa este, pentru că iată ce voi face. A patra mea regulă, verde a sosit astăzi sau ieri, o voi folosi doar ca implicită. Dacă niciuna dintre aceste alte trei reguli nu este adevărată, atunci va fi verde că îmi va da o regulă mai puțin de care trebuie să mă ocup aici. Bine.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2105.xlsx
