Postavljanje formule uvjetnog oblikovanja koja koristi mješovitu referencu. Većina formula za uvjetno formatiranje zahtijeva apsolutnu referencu. Ali ova proračunska tablica za praćenje kamiona u dvorištu zahtijeva
Gledaj video
- Anderson traži način da može kopirati blokove podataka koji sadrže mješovito uvjetno oblikovanje
- Postoji li način uklanjanja znakova dolara nakon postavljanja uvjetnog oblikovanja?
- Ne - ne bez uvođenja desetaka novih pravila
- Moje rješenje: pomoćne stanice koje koriste relativne reference kako bi zamijenile mješovitu referencu u uvjetnom oblikovanju
- Ostale tehnike u ovoj epizodi:
- Ako imate četiri pravila uvjetnog oblikovanja, postavite prva 3, a zatim neka četvrto pravilo bude zadana boja
- Izlaz # 1: Pritisnite F2 da biste zaustavili Excel da u dijaloškom okviru uvjetnog formatiranja ubacuje reference ćelija
- Izlaz # 2: postavljanje uvjetnog oblikovanja
Prijepis videozapisa
Naučite Excel iz podcasta Epizoda 2105: Kopiranje uvjetnog formata s miješanim referencama
Hej, dobrodošao natrag na netcast. Ovo će danas biti složeno. Jučer sam radio seminar i jedan od ljudi na seminaru, Anderson, imao je zanimljivu proračunsku tablicu s problemom. U redu, i Anderson upravlja dvorištem - stižu prikolice i prikolice se moraju iskrcati u roku od tri dana. U redu, dakle ovo je - započinje, znate, ovo je bio dan, to su bile najave koje su stigle, a zatim je postavljeno uvjetno formatiranje da se nakon napuštanja prikolice promijeni u plavu. Jednom kad je nešto plavo, sve je super. Ali onda, želi obojati stvari. Ako je nešto stiglo danas ili jučer, boja se kodira kao zelena. Dakle, danas je 29. lipnja 2017. Dakle, ovo je stiglo jučer i sve što nije istovareno je zeleno, ali kad je staro više od jednog dana,želimo stvari istaknuti kao žutu, a kada je stara više od dva dana, to su problemi koje želimo istaknuti kao crvene. I nije to, znate, ovo je jedan radni list za upravljanje cijelim dvorištem, zar ne? Nije da postoji list za stvari koje su stigle 26., a drugi 27. i još jedan 28. I znate kako je poteškoća kako dolazi novi dan, ili prepisuju prethodni dan ovdje ili dolje ovdje.ili kopiraju prethodni dan ovdje ili ovdje dolje.ili kopiraju prethodni dan ovdje ili ovdje dolje.
U redu, poanta ovog videozapisa nije u tome kako postaviti ovo uvjetno formatiranje. Stoga ću proći kroz ovo, ali ako vas zanima kako postaviti ovo uvjetno formatiranje, stavit ću ne ubrzanu verziju kao izlaz na kraju videozapisa.
Dobro, eto nas. Ubrzavši to, na kraju možete pogledati kako to funkcionira. Samo radim test ovdje, CTRL; promijenit će se u plavu. Ako se ovo vrati na 26. 6., promijenit će se u crvenu, a ako je danas, ne radi. Točno, jer evo što ću učiniti, moje četvrto pravilo zeleno stiglo je danas ili jučer, samo ću to koristiti kao zadano. Ako niti jedno od ova tri pravila nije istina, tada će biti zeleno što će mi dati jedno pravilo manje s kojim moram ovdje riješiti, u redu?
Dobro, sad smo na mjestu gdje u osnovi imamo Andersonov problem. Stavit ću 25.6.2017., Svi će oni postati crveni, osim onih koji su istovareni. A sada život ide dalje, sljedeći je dan. Dobili smo neke prikolice 26. 6. i tako Anderson kopira ove podatke, zalijepi ovdje, formatira Automatsko postavljanje stupca i to će biti Prikolica 15. Kliknite da biste to kopirali i povećali, riješite se pristiglih. I tako je ovaj stigao danas, pa bi svi trebali postati zeleni, ali ne zeleni. Zašto ne postanu zelene? Ne postaju zelene jer ćemo ove formule, ove formule za uvjetno formatiranje upravo pogledati. Oni su teško kodirani da koriste 1 A $ 1. Oh, to je stvarno loše.
U redu, pokušajmo ovdje poboljšati stvari. Prvo što mogu učiniti, riješit ću se svih tih i vratiti se ovom izvornom skupu podataka, a pri drugom prolazu biti malo pametniji i reći da ga zapravo ne trebamo zaključati na stupac A. Riješit ću se tog znaka $. Drugim riječima, to će uvijek biti stupac s lijeve strane od nas, pa će to biti mješovita referenca, ali uvijek moramo ukazati na $ 1. Uredit ćemo ovo pravilo, kliknite U redu. U redu sada, s tom jednom promjenom kada smo kopirali udesno i stavili nove podatke, poput današnjeg datuma, to djeluje. U redu, pa ovo je sjajno. Život će biti sjajan 26. 6., a život će 6. 6. 27. U redu, super radi. Ali sada nailazimo na problem gdje nam ponestaje prostora na stranici, pa ono što je Anderson radio pada,u osnovi započinje novi red i zalijepi, a to bi bilo 6/28, ali ne postaje zeleno.
Zašto ne postaje zeleno? Ne postaje zeleno jer sam još uvijek trebao upotrijebiti $ da bih se vratio na 1. U redu, i evo sad zagonetke, evo problema. Što radiš sada? I ozbiljna sam, čime se sada baviš? Želim čuti u komentarima na YouTubeu što biste sada učinili.
Znate, pa, hej, pogledajte, postoji argument da je ovo dobro, mogli bismo se zaustaviti ovdje, jer koristeći A $ 1, napravili smo to tako, život je lak na 1. dan, preslikajte na 2. dan, život je sjajan . Treći dan život je sjajan. Tek svaki četvrti dan kada ovdje kopiramo Anderson bi morao ući i postaviti uvjetno formatiranje, urediti ovo, urediti pravilo, promijeniti to 1 na 18. Kliknite U redu, urediti ovo pravilo i promijeniti da 1 bude 18. Kliknite U redu, kliknite U redu. U redu, dakle, 4. dan, ta mala kopija prilagodbe za 5. dan, za 6. dan, a zatim za 7. dan. Ponovite te korake. Ali hej, priznajmo. Ovaj je radni list postavljen prije šest mjeseci s ovim pravilima uvjetnog oblikovanja i oni samo trebaju raditi. Ne trebamo ulaziti i raditi uvjetno formatiranje iznova i iznova i iznova.
Moja prva reakcija bila je da ću se pretvarati da je ovo proračunska tablica u kojoj ovdje imam neke formule i one su izrađene s apsolutnim referencama, ali trebam te formule da bih ih mogao kopirati preko ili dolje i biti relativan u kopiji - i kad kopiram ovdje, i kad kopiram ovdje. U redu, i da bih to funkcionirao, koristit ću apsolutne reference kad postavljam stvari, ali tada ću koristiti Pronađi i zamijeni, Ctrl H. I recimo da se riješimo tih relativnih referenci, promijenite svaki $ A $ 1 u A1, Zamijeni sve, kliknite Zatvori i sada ovaj blok, sve ove formule su različite do kraja, kopirajte, zalijepite i zalijepite i to će raditi. Bit će relativno. Pa sam rekao, u redu, pa to je ono što moramo učiniti. Moramo izvaditi tih dolara iz formule.I tako sam namjeravao napisati makronaredbu koja će mi omogućiti uređivanje svakog od ovih uvjeta uvjetnog oblikovanja. U redu, i prije nego što sam napisao tu makronaredbu, zabilježio sam makronaredbu promjene jednog pravila uvjetnog oblikovanja, ali nije da ovdje postoji 14 pravila uvjetnog oblikovanja. Ovdje se čak ne radi ni o uvjetima uvjetnog oblikovanja 14 * 3, 42. Ovdje postoje samo 3 pravila uvjetnog oblikovanja i mi primjenjujemo ta 3 pravila uvjetnog oblikovanja na niz ćelija.s ovdje imamo samo 3 pravila uvjetnog oblikovanja i mi primjenjujemo ta 3 pravila uvjetnog oblikovanja na niz ćelija.s ovdje imamo samo 3 pravila uvjetnog oblikovanja i mi primjenjujemo ta 3 pravila uvjetnog oblikovanja na niz ćelija.
Dakle, ako bih ovo promijenio, prvo što bih morao učiniti je uzeti ova 3 pravila uvjetnog oblikovanja i učiniti da to budu 42 pravila uvjetnog oblikovanja. A onda, počinjem jezati, jer dok Anderson kopira odavde do ovdje, uvest će 42 nova pravila, a zatim 42 nova pravila. I tijekom jednog lista papira s vjerojatno 15 dana, uvest će preko 600 pravila, 600 različitih formata i to će biti užasno. Na kraju ćete pogoditi previše pravila o formatiranju, a da ne spominjemo da će to biti teško postaviti čak i ako imamo makronaredbu za postavljanje. Bit će teško postaviti.
U redu, pa što da radimo? Evo što sam smislio i želim čuti imate li nešto bolje od toga. Rekao sam Andersonu, rekao sam, “Znaš, vidi to je prilično jednostavno. Svi oni gledaju jedan izračun i taj je izračun = DANAS - datum koji je lijevo od mene. " I ne bi li bilo cool, kad bismo taj odgovor mogli dobiti u maloj pomoćnoj koloni ovdje desno. I zapravo, uopće ne moramo koristiti $, samo ćemo sve te stanice staviti dolje s tom jednostavnom malenom formulom.
Vidim pogled Andersonova lica, ne želi da se te dodatne stvari izbrišu, ali to je u redu. To možemo sakriti, sakriti kasnije, pa se vratimo u ove ćelije i pređemo na svoje uvjetno oblikovanje. Cijeli taj DANAS-A1 jednostavno će ukazivati na C3 i to će biti relativna referenca. Drugim riječima, u kojoj god ćeliji da se nalazimo, uvijek ćemo je gledati udesno, kliknite U redu, napišite ovu, kliknite U redu. Želimo sakriti ove podatke ovdje, pa ću ući i CTRL 1. Upotrijebit ću tri točke sa zarezom - ;;;, kliknite U redu. I ja ću tamo raditi potpuno istu stvar. Pritisnite F4, ponovit ću posljednju akciju.
Čudna stvar ovdje je da se moram uvjeriti da je taj mali dio, ovaj prazni stupac dio cijele stvari. Stoga želim tamo dodati svijetlo sivu boju kako bih se podsjetio da ću, kad kopiram i zalijepiti, morati uključiti sivu boju. U redu, evo sad našeg testa. Odabrat ću ovaj CTRL C i tada ću zapravo zalijepiti tamo i zalijepiti tamo i zalijepiti ovdje. U redu, veliki testovi idu na 6/26, idu na 6/27, mijenjaju ovo u žuto, dolaze na 6/28, trebalo bi da se mijenja u zeleno. Lijep!
U redu, tako da sada to funkcionira, u osnovi smo zamijenili tu mješovitu referencu uvjetnog oblikovanja s relativnom referencom i trebali bismo biti relativno, relativno 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.
Dobro, eto nas. Ubrzavši to, na kraju možete pogledati kako to funkcionira. Samo radim test ovdje. CTRL; promijenit će se u plavu. Ako se ovo vrati na 26. 6., promijenit će se u crvenu. A ako je danas, ne ide. Točno, jer evo što ću učiniti. Moje četvrto pravilo, zeleno je stiglo danas ili jučer, samo ću ga koristiti kao zadano. Ako niti jedno od ova ostala tri pravila nije istina, tada će biti zeleno što će mi dati jedno pravilo manje s kojim se ovdje moram nositi. U redu.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2105.xlsx
