Nastavitev formule pogojnega formatiranja, ki uporablja mešani sklic. Večina formul pogojnega oblikovanja zahteva absolutno referenco. Toda ta preglednica za sledenje tovornjakom na dvorišču zahteva
Oglejte si video
- Anderson išče način, kako bi lahko kopiral bloke podatkov, ki vsebujejo mešano pogojno oblikovanje
- Ali obstaja možnost odstranitve znakov za dolar, ko je nastavljeno pogojno oblikovanje?
- Ne - ne brez uvedbe desetin novih pravil
- Moja rešitev: pomožne celice, ki uporabljajo relativne reference za nadomestitev mešane reference v pogojnem oblikovanju
- Druge tehnike v tej epizodi:
- Če imate štiri pravila pogojnega oblikovanja, nastavite prva 3, nato pa naj bo četrto pravilo privzeta barva
- Izhod # 1: Pritisnite F2, da Excel ne bo več vstavljal sklicev na celice v pogojnem pogovornem oknu za oblikovanje
- Izhod # 2: nastavitev pogojnega oblikovanja
Video zapis
Naučite se Excel iz podcasta Epizoda 2105: Kopiranje pogojne oblike z mešanimi referencami
Hej, dobrodošli nazaj v oddaji. Danes bo to zapleteno. Včeraj sem delal seminar in eden od ljudi na seminarju Anderson je imel zanimivo preglednico s težavo. V redu, Anderson pa upravlja dvorišče - prikolice pridejo in prikolice je treba razložiti v treh dneh. V redu, torej to je - on začne, saj veste, to je bil dan, to so bile prikolice, ki so prispele, nato pa ima nastavljeno pogojno oblikovanje, da ko se prikolica razloži, se spremeni v modro. Ko je nekaj modro, je vse super. Potem pa želi stvari pobarvati. Če je nekaj prispelo danes ali včeraj, je barvno označeno kot zeleno. Torej, danes je 29. junij 2017, torej smo prispeli včeraj in vse, kar ni raztovorjeno, je zeleno, ko pa je staro več kot en dan,stvari želimo poudariti kot rumeno in ko je stara več kot dva dni, so to težave, ki jih želimo poudariti kot rdeče. In ni to, saj veste, to je en delovni list za upravljanje celotnega dvorišča, kajne? Saj ni, da obstaja list za stvari, ki so prispele 26. in še ena 27. in še ena 28. dne. In veste, da je težava, ko prihaja nov dan, prejšnji dan bodisi kopirajo sem ali tu.bodisi prejšnji dan kopirajo sem ali pa sem.bodisi prejšnji dan kopirajo sem ali pa sem.
Zdaj, bistvo tega videoposnetka ni v tem, kako nastaviti to pogojno oblikovanje. Torej bom s tem pospešil, če pa vas zanima, kako nastaviti to pogojno oblikovanje, bom na koncu videoposnetka dal nehitro različico kot izhod.
V redu, torej smo. Ko je to hitro, lahko na koncu gledate, kako to deluje. Samo test tukaj, CTRL; se spremeni v modro. Če se to vrne na 26. 6., se spremeni v rdeče in če je danes, ne deluje. Tako je, ker tukaj bom naredil, moje četrto pravilo zeleno je prispelo danes ali včeraj, to bom le uporabil kot privzeto. Če nobeno od teh treh pravil ne drži, potem bo zeleno, da mi bo dalo eno pravilo manj, s katerim se moram spoprijeti tukaj, v redu?
V redu, zdaj smo na točki, ko imamo v bistvu Andersonovo težavo. Dala bom 25.6.2017, vsi bodo rdeči, razen tistih, ki so bili razloženi. In zdaj gre življenje naprej, naslednji dan je. 26. 6. smo dobili nekaj napovednikov, zato Anderson kopira te podatke, jih prilepi sem, oblikuje stolpec AutoFit in to bo Prikolica 15. Kliknite, če želite to kopirati in povečati, da se znebite prispelih. In ta je danes prispel danes, zato bi morali vsi postati zeleni, ne pa zeleni. Zakaj ne postanejo zelene? Ne postanejo zelene, ker te formule, te pogojne formule za oblikovanje tukaj, si jih bomo še ogledali. Težko so kodirani za uporabo 1 A $. Oh, to je res slabo.
V redu, poskusimo stvari izboljšati tukaj. Prva stvar, ki jo lahko naredim, se bom znebil vseh teh in se vrnil k temu prvotnemu naboru podatkov, pri drugem prehodu pa bom nekoliko pametnejši in rekel, da ga v resnici ni treba zapreti v stolpec A. Znebil se bom znaka $. Z drugimi besedami, to bo vedno stolpec levo od nas, tako da bo to mešana referenca, vendar moramo vedno pokazati na 1 $. To pravilo bomo uredili, kliknite V redu. Zdaj, s to eno spremembo, ko smo kopirali na desno in vnesli nove podatke, na primer današnji datum, deluje. V redu, to je super. Življenje bo čudovito 26. 6. in življenje bo 6. 6. 27. V redu, odlično deluje. Zdaj pa naletimo na problem, ko nam na strani zmanjka prostora in tako to, kar je počel Anderson, upada,v bistvu začne novo vrstico in prilepi in to bi bilo 6. 6., vendar ne postane zeleno.
Zakaj ne postane zeleno? Ne postane zeleno, ker sem še vedno moral uporabiti $, da sem se vrnil na 1. V redu, zdaj je tukaj uganka, tukaj je težava. Kaj počneš sedaj? In resno mislim, kaj počneš zdaj? V komentarjih YouTube želim slišati, kaj bi storili zdaj.
Veste, torej, glejte, obstaja argument, da je to dobro, lahko bi se ustavili tukaj, ker smo z uporabo 1 ameriškega dolarja naredili tako, na 1. dan je življenje enostavno, kopiranje na 2. dan, življenje je super . Tretji dan življenje je super. Šele vsak 4. dan, ko kopiramo tukaj, bi Anderson moral vstopiti in nastaviti pogojno oblikovanje, urediti to, urediti pravilo, spremeniti 1 na 18. Kliknite V redu, urediti to pravilo in spremeniti, da je 1 18. Kliknite V redu, kliknite V redu. V redu, torej 4. dan, tisto majhno prilagoditveno kopijo za 5. dan, za 6. dan in nato za 7. dan. Ponovite te korake. Ampak hej, priznajmo se. Ta delovni list je bil pripravljen pred šestimi meseci s temi pravili pogojnega oblikovanja in morajo samo delovati. Ni nam treba vnašati in izvajati pogojnega formatiranja znova in znova in znova.
Moja prva reakcija je bila, da se bom pretvarjal, da je to preglednica, kjer imam tukaj nekaj formul in te formule so bile zgrajene z absolutnimi referencami, vendar potrebujem te formule, da jih lahko kopiram nad ali navzdol in so relativne znotraj kopije - tako ko kopiram sem, kot ko kopiram sem. V redu, in da bi to delovalo, bom pri nastavitvi stvari uporabil absolutne reference, nato pa bom uporabil funkcijo Poišči in zamenjaj, Ctrl H. In recimo, znebimo se teh relativnih referenc, spremenite vsakih 1 $ A $ 1 v A1, Zamenjaj vse, kliknite Zapri in zdaj ta blok, vse te formule se razlikujejo do konca, kopirajte, prilepite in prilepite in bo delovalo. Relativno bo. Tako sem rekel, v redu, no, to moramo storiti. Te formule moramo vzeti iz formule.In tako sem nameraval napisati makro, ki bi mi omogočil urejanje vsakega od teh pogojnih pravil za oblikovanje. V redu, in preden sem napisal ta makro, sem posnel makro spremembe enega pravila pogojnega oblikovanja, vendar ne, da tukaj obstaja 14 pravil pogojnega oblikovanja. Tu ne gre niti za pravila pogojnega oblikovanja 14 * 3, 42. Tu so samo 3 pravila pogojnega oblikovanja in ta tri pravila pogojnega oblikovanja uporabljamo za vrsto celic.s tukaj imamo samo 3 pravila pogojnega oblikovanja in ta tri pravila pogojnega oblikovanja uporabljamo za vrsto celic.Tu imamo samo 3 pravila pogojnega oblikovanja in ta tri pravila pogojnega oblikovanja uporabljamo za vrsto celic.
Torej, če bi to spremenil, bi najprej moral sprejeti ta tri pravila pogojnega oblikovanja in jih določiti kot 42 pogojnih pravil oblikovanja. In potem se začnem ježati, ker bo Anderson, kopira od tu do sem, uvedel 42 novih pravil in nato 42 novih pravil. V enem listu papirja z verjetno 15 dnevi bo uvedel več kot 600 pravil, 600 različnih formatov in to bo preprosto grozljivo. Sčasoma boste zadeli preveč pravil o oblikovanju, da ne omenjamo, da ga bo težko nastaviti, tudi če imamo makro, da ga nastavimo. Težko bo postaviti.
V redu, kaj bomo torej storili? Tukaj sem si izmislil in želim slišati, če imate kaj boljšega od tega. Rekel sem Andersonu, rekel sem: "Veste, poglejte, to je precej preprosto. Vsi ti gledajo en izračun in ta izračun je = DANES - datum, ki je levo od mene. " In ali ne bi bilo v redu, če bi lahko dobili ta odgovor v majhnem stolpcu s pomočjo tukaj na desni. In pravzaprav nam sploh ni treba uporabiti nobenega $, vse te celice bomo preprosto postavili do konca s to preprosto majhno formulo.
Vidim pogled Andersonovega obraza, noče, da se odvečne stvari izbrišejo, ampak to je v redu. To lahko kasneje skrijemo, skrijemo, tako da se vrnemo v te celice in gremo v svoje pogojno oblikovanje. Ta celotni DANES-A1 bo preprosto kazal na C3 in to bo relativna referenca. Z drugimi besedami, ne glede na celico, v kateri smo, bomo vedno gledali v celico na desni, kliknite V redu, napišite to, kliknite V redu. Te podatke želimo skriti tukaj, zato bom vstopil in CTRL 1. Uporabil bom tri podpičja - ;;;, kliknite V redu. Tam bom naredil popolnoma isto stvar. Pritisnite F4, ponovite zadnje dejanje.
Zdaj je čudno, da se moram prepričati, da je ta majhen delček, ta prazen stolpec del celotne stvari. Zato bi rad samo dodal svetlo sivo, da bi se spomnil, da bom moral kopirati in prilepiti sivo. Torej, tukaj je zdaj naš test. Izbral bom to CTRL C in potem bom dejansko prilepil tja in prilepil tja in prilepil sem. V redu, veliki testi gredo na 6/26, pojdite na 6/27, spremenite to na rumeno, pridite na 6/28, naj se spremeni v zeleno. Čudovito!
Torej, zdaj, ko deluje, smo v bistvu zamenjali to mešano referenčno pogojno oblikovanje z relativno referenco in morali bi biti razmeroma sorazmerno dobri.
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.
V redu, torej smo. Ko je to hitro, lahko na koncu gledate, kako to deluje. Tukaj samo delam test. CTRL; se spremeni v modro. Če se to vrne na 26. 6., se spremeni v rdečo. In če je danes, ne deluje. Tako je, ker tukaj bom naredil nekaj. Moje četrto pravilo, zeleno je prispelo danes ali včeraj, to bom samo uporabil kot privzeto. Če nobeno od teh treh pravil ne drži, potem bo zeleno, da mi bo dalo eno pravilo manj, s katerim se moram tukaj spoprijeti. Vredu.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2105.xlsx
