Nastavení vzorce podmíněného formátování, který používá smíšený odkaz. Většina vzorců podmíněného formátování vyžaduje absolutní odkaz. Ale tato tabulka pro sledování nákladních vozidel ve dvoře vyžaduje
Sledovat video
- Anderson hledá způsob, jak být schopen kopírovat bloky dat obsahující smíšené podmíněné formátování
- Existuje způsob, jak odstranit znaky dolaru po nastavení podmíněného formátování?
- Ne - ne bez zavedení desítek nových pravidel
- Moje řešení: pomocné buňky, které používají relativní odkazy k nahrazení smíšené reference v podmíněném formátování
- Další techniky v této epizodě:
- Pokud máte čtyři pravidla podmíněného formátování, nastavte první 3 a poté nastavte jako výchozí barvu čtvrté pravidlo
- Outtake # 1: Stisknutím klávesy F2 zastavíte Excel v vkládání odkazů na buňky v dialogovém okně podmíněného formátování
- Outtake # 2: nastavení podmíněného formátování
Přepis videa
Naučte se Excel z Podcastu Episode 2105: Kopírování podmíněného formátu se smíšenými odkazy
Hej, vítej zpět na netcastu. Dnes to bude komplikované. Včera jsem dělal seminář a jeden z lidí na semináři, Anderson, měl zajímavou tabulku s problémem. Dobře, Anderson řídí dvůr - dorazí přívěsy a přívěsy musí být vyloženy do tří dnů. Dobře, takže tohle je - začíná, víte, to byl den, to byly přívěsy, které dorazily, a poté má nastaveno podmíněné formátování, které po vyložení přívěsu změní na modrou. Jakmile je něco modré, všechno je skvělé. Ale potom chce věci barevně kódovat. Pokud něco dorazilo dnes nebo včera, bude barevně označeno jako zelené. Dnes je tedy 29. června 2017, takže toto dorazilo včera a vše, co není vyloženo, je zelené, ale když je starší než jeden den,chceme zvýraznit věci žlutě, a když je to více než dva dny staré, to jsou problémy, které chceme zvýraznit červeně. A není to tak, víte, toto je jeden list pro správu celého dvora, že? Není to tak, že existuje list pro věci, které dorazily 26. a další pro 27. a další pro 28. A víte, že obtížnost je, jak přichází nový den, buď kopírují předchozí den sem nebo dolů.buď kopírují předchozí den sem nebo dolů.buď kopírují předchozí den sem nebo dolů.
Dobře, bod tohoto videa není o tom, jak nastavit toto podmíněné formátování. Chystám se to urychlit, ale pokud vás zajímá, jak nastavit toto podmíněné formátování, na konci videa uvedu nezrychlenou verzi jako outtake.
Dobře, tak jsme tady. Zrychlete to, na konci můžete sledovat, jak to funguje. Právě dělám test, CTRL; se změní na modrou. Pokud se to vrátí zpět na 6/26, změní se to na červenou a pokud je to dnes, nefunguje to. To je pravda, protože tady budu dělat, moje čtvrté zelené pravidlo přišlo dnes nebo včera, použiji to jako výchozí. Pokud žádné z těchto dalších tří pravidel není pravdivé, bude to zelené, což mi dá o jedno pravidlo méně, se kterými se zde musím vypořádat, dobře?
Dobře, takže jsme nyní v bodě, kdy v podstatě máme Andersonův problém. Vložím 25. 6. 2017, všechny zčervenají, kromě těch, které byly vyloženy. A teď život jde dál, je to další den. Některé přívěsy jsme dostali 26. června, a tak Anderson zkopíruje tato data, vloží je sem, naformátuje Column AutoFit a bude to Trailer 15. Kliknutím kliknete na to a zkopírujete to a zvýšíte, zbavíte se těch, které dorazily. A tak tento dnes dorazil, takže by se měly všechny zbarvit zeleně, ale nezelená. Proč nezezelenají? Nesvítí zeleně, protože tyto vzorce, tyto vzorce podmíněného formátování právě tady, podíváme se na ně. Jsou pevně zakódovány tak, aby používaly $ A $ 1. To je opravdu špatné.
Dobře, tak se pokusme věci vylepšit zde. První věc, kterou mohu udělat, je zbavit se všech těch, vrátit se k této původní datové sadě a být při druhém průchodu o něco chytřejší a říci, že ji opravdu nemusíme uzamknout na sloupec A. Zbavím se toho znaku $. Jinými slovy, vždy to bude sloupec nalevo od nás, takže to bude smíšený odkaz, ale vždy musíme ukazovat na $ 1. Toto pravidlo upravíme, klikněte na OK. Dobře, nyní, s touto změnou, když jsme zkopírovali doprava a vložili nová data, jako je dnešní datum, to funguje. Dobře, tak to je skvělé. Život bude skvělý 26. 6. a život bude skvělý 26. 6.. Dobře, funguje to skvěle. Ale teď narazíme na problém, kde nám na stránce dochází místo, a tak to, co Anderson dělal, jde dolů,v podstatě začíná nový řádek a vloží, a to by bylo 6/28, ale nezelená.
Proč nezezelená? Nesvítí zeleně, protože i tak jsem musel použít $, abych se vrátil k 1. Dobře, a tak teď je tu hlavolam, tady je problém. Co teď děláš? A myslím to vážně, co děláš teď? Chci slyšet v komentářích na YouTube, co byste teď udělali.
Víte, ahoj, podívejte se, existuje argument, že je to dobré, mohli bychom se zastavit právě tady, protože pomocí A $ 1 jsme to udělali tak, život je v 1. den snadný, kopírujte do 2. dne, život je skvělý . Den 3 je skvělý. Je to jen každý 4. den, kdy sem zkopírujeme, že by Anderson musel vstoupit a nastavit podmíněné formátování, upravit toto, upravit pravidlo, změnit to na 1 na 18. Klikněte na OK, upravit toto pravidlo a změnit to na 1 18. Klikněte na OK, klikněte na OK. Dobře, takže 4. den, tato malá úprava se kopíruje na 5. den, kopíruje se na 6. den a poté se kopíruje na 7. den. Proveďte tyto kroky znovu. Ale hej, přiznejme si to. Tento list byl vytvořen před šesti měsíci s těmito pravidly podmíněného formátování a je potřeba jen pracovat. Nepotřebujeme jít a dělat podmíněné formátování znovu a znovu a znovu.
Moje první reakce byla, že budu předstírat, že se jedná o tabulku, kde zde mám nějaké vzorce a tyto vzorce byly vytvořeny s absolutními odkazy, ale potřebuji, aby tyto vzorce mohly být kopírovány přes nebo dolů a být v kopii relativní - jak při kopírování sem, tak při kopírování sem. Dobře, a aby to fungovalo, budu při nastavování věcí používat absolutní odkazy, ale pak použiji Najít a nahradit, Ctrl H. A řekněme, pojďme se těchto relativních odkazů zbavit, změňte každý $ A $ 1 na A1, Nahradit vše, klikněte na Zavřít a nyní tento blok, všechny tyto vzorce se úplně liší, kopírujte, vkládejte a vkládejte a bude to fungovat. Bude to relativní. Takže jsem řekl, dobře, to je to, co musíme udělat. Musíme tyto $ z vzorce vyjmout.A tak jsem chtěl napsat makro, které by mi umožnilo upravit každé z těchto pravidel podmíněného formátování. Dobře, a než jsem napsal toto makro, chystal jsem se zaznamenat makro změny jednoho pravidla podmíněného formátování, ale není to tak, že zde existuje 14 pravidel podmíněného formátování. Není to ani na pravidla podmíněného formátování 14 * 3, 42 zde. K dispozici jsou pouze 3 pravidla podmíněného formátování a tato 3 pravidla podmíněného formátování aplikujeme na řadu buněk.máme zde pouze 3 pravidla podmíněného formátování a tato 3 pravidla podmíněného formátování aplikujeme na řadu buněk.máme zde pouze 3 pravidla podmíněného formátování a tato 3 pravidla podmíněného formátování aplikujeme na řadu buněk.
Takže pokud bych to změnil, první věc, kterou bych musel udělat, je vzít tato 3 pravidla podmíněného formátování a učinit z nich 42 pravidel podmíněného formátování. A pak se začínám krčit, protože jak Anderson kopíruje odsud sem, zavede 42 nových pravidel a poté 42 nových pravidel. A během jednoho listu papíru s pravděpodobností 15 dnů zavede přes 600 pravidel, 600 různých formátů a to bude hrozné. Nakonec narazíte na příliš mnoho věcí s pravidly formátování, nemluvě o tom, že bude těžké nastavit, i když k nastavení máme makro. Bude těžké to nastavit.
Dobře, tak co budeme dělat? Tady je to, s čím jsem přišel, a chci slyšet, jestli máte něco lepšího. Řekl jsem Andersonovi a řekl: „Víš, podívej, je to docela jednoduché. Všichni se dívají na jeden výpočet a ten výpočet je = DNES - datum, které je ode mne vlevo. “ A nebylo by v pohodě, kdybychom tu odpověď mohli mít v malém pomocném sloupci tady napravo. A ve skutečnosti nemusíme používat vůbec žádné $, prostě všechny tyto buňky dáme úplně dolů pomocí toho jednoduchého malého vzorce.
Vidím pohled na Andersonovu tvář, nechce, aby byly ty extra věci vymazány, ale to je v pořádku. Můžeme to skrýt, skrýt to později, takže se vrátíme do těchto buněk a přejdeme do našeho podmíněného formátování. Celý TODAY-A1 bude jednoduše ukazovat na C3 a bude to relativní reference. Jinými slovy, ať už jsme v jakékoli buňce, vždy se podíváme do buňky vpravo, klikneme na OK, napíšeme na tuto, klikneme na OK. Chceme tato data skrýt sem, takže přejdu dovnitř a CTRL 1. Budu používat tři středníky - ;;;, klikněte na OK. Budu tam dělat přesně to samé. Stisknu F4, zopakuji poslední akci.
Nyní je tu zvláštní věc, že se musím přesvědčit, že tato malá část, tento prázdný sloupec je součástí celé věci. Takže tam chci jen přidat světle šedou, abych si připomněl, že když budu kopírovat a vkládat, budu muset zahrnout šedou. Dobře, takže tady je náš test. Vyberu tento CTRL C a pak tam vlastně vložím a vložím tam a vložím sem. Dobře, velké testy jdou na 6/26, jdou na 6/27, změní to na žlutou, přijdou na 6/28, mělo by se to změnit na zelenou. Krásná!
Dobře, takže teď to funguje, v podstatě jsme nahradili tento podmíněný formátovaný smíšený odkaz relativním odkazem a měli bychom být relativně, relativně dobří.
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.
Dobře, tak jsme tady. Zrychlete to, na konci můžete sledovat, jak to funguje. Jen tady dělám test. CTRL; se změní na modrou. Pokud se to vrátí zpět na 6/26, změní se to na červenou. A pokud je to dnes, nefunguje to. To je pravda, protože tady budu dělat. Moje čtvrté pravidlo, zelená přišla dnes nebo včera, použiji ji jako výchozí. Pokud žádné z těchto dalších tří pravidel není pravdivé, bude to zelené, což mi dá o jedno pravidlo méně, se kterým se zde musím vypořádat. V pořádku.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2105.xlsx
