Nastavenie vzorca podmieneného formátovania, ktorý používa zmiešanú referenciu. Väčšina vzorcov s podmieneným formátovaním vyžaduje absolútny odkaz. Ale táto tabuľka na sledovanie nákladných vozidiel na záhrade vyžaduje
Pozeraj video
- Anderson hľadá spôsob, ako byť schopný kopírovať bloky údajov obsahujúce zmiešané podmienené formátovanie
- Existuje spôsob, ako odstrániť znaky dolára po nastavení podmieneného formátovania?
- Nie - nie bez zavedenia desiatok nových pravidiel
- Moje riešenie: pomocné bunky, ktoré používajú relatívne odkazy na nahradenie zmiešanej referencie v podmienenom formátovaní
- Ďalšie techniky v tejto epizóde:
- Ak máte štyri pravidlá podmieneného formátovania, nastavte prvé 3 a potom nastavte štvrté pravidlo ako predvolenú farbu
- Outtake # 1: Stlačením klávesu F2 zastavíte Excel v vkladaní odkazov na bunky v dialógovom okne podmieneného formátovania
- Outtake # 2: nastavenie podmieneného formátovania
Prepis videa
Naučte sa Excel z Podcastu Episode 2105: Kopírovanie podmieneného formátu so zmiešanými odkazmi
Ahoj, vitaj späť na netcaste. Dnes to bude komplikované. Včera som robil seminár a jeden z ľudí na seminári, Anderson, mal zaujímavú tabuľku s problémom. Dobre, Anderson spravuje lodenicu - dorazia prívesy a prívesy musia byť vyložené do troch dní. Dobre, tak to je - začína, viete, bol to deň, boli to prívesy, ktoré dorazili, a potom má nastavené podmienené formátovanie, ktoré po vyložení prívesu zmení na modrú. Akonáhle je niečo modré, všetko je super. Ale potom chce veci farebne odlíšiť. Ak niečo dorazilo dnes alebo včera, získa sa farebné označenie ako zelené. Takže dnes je 29. júna 2017, takže toto dorazilo včera a všetko, čo nie je vyložené, je zelené, ale keď má viac ako jeden deň,chceme zvýrazniť veci ako žlté a keď sú to viac ako dva dni, to sú problémy, ktoré chceme zvýrazniť ako červené. A nie je to tak, že viete, toto je jeden pracovný hárok na správu celého dvora, nie? Nie je to tak, že existuje hárok pre veci, ktoré dorazili 26. a ďalší pre 27. a ďalší pre 28. A viete, že ťažkosti sú, keď prichádza nový deň, buď kopírujú predchádzajúci deň sem alebo dole.buď kopírujú predchádzajúci deň sem alebo sem.buď kopírujú predchádzajúci deň sem alebo dole.
V poriadku, teraz nejde o to, ako nastaviť toto podmienené formátovanie. Chystám sa to teda urýchliť, ale ak vás zaujíma, ako nastaviť toto podmienené formátovanie, na konci videa uvediem nespracovanú verziu ako výstup.
Dobre, tak sme tu. Ak to zrýchlite, môžete sa na konci pozrieť, ako to funguje. Práve tu robím test, CTRL; sa zmení na modrú. Ak sa to vráti späť na 26/6, zmení sa to na červenú a ak je to dnes, nefunguje to. Máte pravdu, pretože tu budem robiť to, moje štvrté zelené pravidlo prišlo dnes alebo včera, len ho použijem ako predvolené. Ak žiadne z týchto ďalších troch pravidiel nie je pravdivé, bude mať zelenú farbu, že mi dá o jedno pravidlo menej, s ktorým sa tu musím vyrovnať, dobre?
Dobre, takže sme teraz v bode, keď v podstate máme Andersonov problém. Chystám sa vložiť 25.6.2017, všetky budú mať červenú farbu okrem tých, ktoré boli vyložené. A teraz život ide ďalej, je to nasledujúci deň. Niektoré prívesy sme dostali 26. júna, a tak Anderson skopíruje tieto údaje, vloží ich sem, naformátuje stĺpec AutoFit a toto bude Trailer 15. Kliknutím kliknete na to, aby ste to skopírovali a zvýšili, zbavte sa tých, ktoré prišli. A tak tento dnes dorazil, takže všetky by mali svietiť nazeleno, ale nesvietia nazeleno. Prečo sa neozelenajú? Nesvietia na zeleno, pretože tieto vzorce, tieto vzorce podmieneného formátovania tu, na tieto sa pozrieme. Majú pevne zakódované použitie $ A $ 1. Ach, to je naozaj zlé.
Dobre, tak to skúsme vylepšiť. Prvá vec, ktorú môžem urobiť, je zbaviť sa všetkých, vrátiť sa k tejto pôvodnej množine údajov a pri druhom prechode byť o niečo múdrejší a povedať, že to naozaj nemusíme obmedziť na stĺpec A. Zbavím sa toho znaku $. Inými slovami, vždy to bude stĺpec vľavo od nás, takže to bude zmiešaná referencia, ale vždy musíme ukazovať na $ 1. Toto pravidlo upravíme, kliknite na OK. Dobre, teraz s tou jednou zmenou, keď sme skopírovali doprava a vložili nové údaje, ako je dnešný dátum, to funguje. Dobre, tak toto je skvelé. Život bude skvelý 26. 6. a život bude skvelý 27. 6.. Dobre, funguje to skvele. Teraz však narazíme na problém, keď nám na stránke dôjde miesto, a takže to, čo Anderson robil, ide dole,v podstate začína nový riadok a nalepuje sa, a to by bolo 6/28, ale nezelená.
Prečo nezelená? Nesvieti nazeleno, pretože som musel stále použiť $, aby som sa dostal späť k 1. Dobre, takže teraz je tu hlavolam, tu je problém. Čo teraz robíš? A myslím to vážne, čo robíš teraz? Chcem v komentároch YouTube počuť, čo by ste teraz robili.
Viete, ahoj, pozri, existuje argument, že je to dobré. Mohli by sme sa zastaviť priamo tu, pretože použitím A $ 1 sme to dokázali tak, že život je ľahký v 1. deň, kopírovanie do 2. dňa, život je skvelý . Deň 3 život je skvelý. Je to iba každý 4. deň, keď tu skopírujeme, že by musel vstúpiť Anderson a nastaviť podmienené formátovanie, upraviť toto, upraviť pravidlo, zmeniť číslo 1 na 18. Kliknite na tlačidlo OK, upraviť toto pravidlo a zmeniť číslo 1 na 18. Kliknite na OK, potom na OK. Dobre, takže 4. deň, táto malá úprava sa kopíruje na 5. deň, kopíruje sa na 6. deň a potom sa kopíruje na 7. deň. Zopakujte tieto kroky. Ale hej, priznajme si. Tento pracovný hárok bol založený pred šiestimi mesiacmi s týmito pravidlami podmieneného formátovania a je potrebné, aby fungovali. Nemusíme ísť do toho a robiť podmienené formátovanie znova a znova a znova.
Moja prvá reakcia bola, že budem predstierať, že ide o tabuľku, kde tu mám nejaké vzorce a tie vzorce boli zostavené s absolútnymi odkazmi, ale potrebujem, aby tieto vzorce bolo možné kopírovať cez alebo nadol a byť v kópii relatívne. - aj keď tu skopírujem, aj keď sem skopírujem. Dobre, a aby to fungovalo, budem pri nastavovaní vecí používať absolútne referencie, ale potom použijem Find and Replace, Ctrl H. A povedzme, že sa týchto relatívnych referencií zbavme, zmeniť každých $ A $ 1 na A1, Nahradiť všetko, kliknúť na Zavrieť a teraz na tento blok, všetky tieto vzorce sú úplne odlišné, kopírovať, vkladať a vkladať a bude to fungovať. Bude to relatívne. Takže som povedal, dobre, to je to, čo musíme urobiť. Tieto $ musíme zo vzorca vylúčiť.A tak som sa chystal napísať makro, ktoré by mi umožnilo upraviť každé z týchto pravidiel podmieneného formátovania. Dobre, a než som napísal toto makro, chystal som sa zaznamenať makro zmeny jedného pravidla podmieneného formátovania, ale nejde o to, že tu existuje 14 pravidiel podmieneného formátovania. Nie je to ani tu podľa pravidiel podmieneného formátovania 14 * 3, 42. Existujú tu iba 3 pravidlá podmieneného formátovania a tieto 3 pravidlá podmieneného formátovania aplikujeme na celý rad buniek.tu máme iba 3 pravidlá podmieneného formátovania a tieto 3 pravidlá podmieneného formátovania aplikujeme na celý rad buniek.tu máme iba 3 pravidlá podmieneného formátovania a tieto 3 pravidlá podmieneného formátovania aplikujeme na celý rad buniek.
Ak by som to teda zmenil, prvá vec, ktorú by som musel urobiť, je zobrať tieto 3 pravidlá podmieneného formátovania a urobiť z nich 42 pravidiel podmieneného formátovania. A potom sa začínam krútiť, pretože keď Anderson kopíruje odtiaľto sem, zavedie 42 nových pravidiel a potom 42 nových pravidiel. A v priebehu jedného listu papiera s pravdepodobnosťou 15 dní zavedie viac ako 600 pravidiel, 600 rôznych formátov a to bude len hrozné. Nakoniec narazíte na príliš veľa vecí týkajúcich sa pravidiel formátovania, nehovoriac o tom, že nastavenie bude ťažké, aj keď na jeho nastavenie máme makro. Bude ťažké to zariadiť.
Dobre, tak čo urobíme? Tu je to, s čím som prišiel a chcem počuť, či máte niečo lepšie ako to. Povedal som Andersonovi a povedal som: „Vieš, vyzerá to dosť jednoducho. Všetci sa pozerajú na jeden výpočet a tento výpočet je = DNES - dátum, ktorý je odo mňa vľavo. “ A nebolo by to v pohode, keby sme mohli mať túto odpoveď v malom pomocnom stĺpci tu napravo. A v skutočnosti nemusíme používať vôbec žiadne doláre, všetky tieto bunky jednoducho umiestnime dole pomocou tohto jednoduchého malého vzorca.
Vidím pohľad na Andersonovu tvár, nechce, aby boli tie extra veci vymazané, ale to je v poriadku. Môžeme to skryť, skryť to neskôr, aby sme sa vrátili do týchto buniek a dostali sa do nášho podmieneného formátovania. Celý tento DNES-A1 bude jednoducho smerovať na C3 a bude to relatívny odkaz. Inými slovami, nech už sa nachádzame v ktorejkoľvek bunke, vždy sa pozrieme do bunky vpravo, klikneme na OK, napíšeme na túto, klikneme na OK. Chceme tieto údaje skryť sem, takže vstúpim do CTRL 1. Použijem tri bodkočiarky - ;;;, kliknite na OK. Budem tam robiť presne to isté. Stlačím F4, zopakujem poslednú akciu.
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.
Dobre, tak sme tu. Ak to zrýchlite, môžete sa na konci pozrieť, ako to funguje. Len tu robím test. CTRL; sa zmení na modrú. Ak sa to vráti späť na 26/6, zmení sa to na červenú. A ak je to dnes, nefunguje to. Máte pravdu, pretože tu budem robiť. Moje štvrté pravidlo, zelená dnes alebo včera, použijem ako predvolené. Ak žiadne z týchto ďalších troch pravidiel nie je pravdivé, bude mať zelenú farbu, že mi dá o jedno pravidlo menej, s ktorým sa tu musím vyrovnať. Dobre.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2105.xlsx
