Sąlyginio formatavimo formulės, kurioje naudojama mišri nuoroda, nustatymas. Daugumai sąlyginio formatavimo formulių reikalinga absoliuti nuoroda. Bet šiai skaičiuoklei reikia stebėti sunkvežimius kieme
Žiūrėti video
- Andersonas ieško būdų, kaip būtų galima nukopijuoti duomenų blokus, kuriuose yra mišrus sąlyginis formatavimas
- Ar yra būdas pašalinti dolerio ženklus, kai bus nustatytas sąlyginis formatavimas?
- Ne - neįvedus dešimčių naujų taisyklių
- Mano sprendimas: pagalbinės ląstelės, kurios naudoja santykines nuorodas, kad pakeistų mišrią nuorodą sąlyginiame formatavime
- Kiti šio epizodo metodai:
- Jei turite keturias sąlyginio formatavimo taisykles, nustatykite pirmąsias 3, o ketvirtąją taisyklę nustatykite kaip numatytąją spalvą
- 1 rezultatas: paspauskite F2, kad sustabdytumėte „Excel“ įterpti langelių nuorodas į sąlyginio formatavimo dialogą
- 2 vykdymas: sąlyginio formatavimo nustatymas
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“ serijos 2105: Sąlyginio formato kopijavimas su mišriomis nuorodomis
Ei, sveiki sugrįžę į internetinę transliaciją. Šiandien tai bus sudėtinga. Aš dariau vakar seminarą, o vienas iš seminaro žmonių Andersonas turėjo įdomią skaičiuoklę su problema. Gerai, o Andersonas tvarko kiemą - atvyksta priekabos, o priekabas reikia iškrauti per tris dienas. Gerai, taip yra - jis pradeda savo veiklą, žinai, tai buvo diena, tai buvo priekabos, kurios atkeliavo, ir tada jis nustatė sąlyginį formatavimą, kad, kai priekaba bus iškrauta, ji pasikeis į mėlyną. Kai kažkas yra mėlyna, viskas yra puiku. Bet tada jis nori nuspalvinti dalykus. Jei kažkas atkeliavo šiandien ar vakar, spalva žymima kaip žalia. Taigi šiandien yra 2017 m. Birželio 29 d., Taigi, tai atėjo vakar ir viskas, kas nėra iškrauta, yra žalia, bet kai ji yra daugiau nei vienos dienosnorime paryškinti dalykus geltonai, o kai jam daugiau nei dvi dienos, tai yra problemos, kurias norime pabrėžti raudonai. Ir ne tai, žinote, tai yra vienas darbalapis, skirtas tvarkyti visą kiemą, tiesa? Nėra taip, kad yra lapas, skirtas daiktams, kurie atvyko 26 d., O kitas - 27 d., O kitas - 28 d. Ir jūs žinote, kad sunku, nes ateina nauja diena, jie arba kopijuoja ankstesnę dieną čia arba žemyn.jie arba kopijuoja praėjusią dieną čia arba žemyn.jie arba kopijuoja praėjusią dieną čia arba žemyn.
Gerai, šio vaizdo įrašo esmė nėra apie tai, kaip nustatyti šį sąlyginį formatavimą. Taigi aš tai paspartinsiu, bet jei jus domina, kaip nustatyti šį sąlyginį formatavimą, vaizdo įrašo pabaigoje aš pateiksiu nespartintą versiją.
Gerai, taigi mes čia. Paspartinęs, galų gale galite pažiūrėti, kaip tai veikia. Tiesiog atlieku testą čia, CTRL; pasikeis į mėlyną. Jei tai grįš į 6/26, jis pasikeis į raudoną, o jei šiandien - neveikia. Teisingai, nes štai ką aš darysiu, mano ketvirtoji taisyklė žalia atsirado šiandien arba vakar, aš tiesiog naudosiu tai kaip numatytąjį. Jei nė viena iš šių trijų kitų taisyklių nėra teisinga, tai bus žalia, kad tai duos man dar vieną taisyklę, su kuria man tenka elgtis, gerai?
Gerai, todėl dabar esame toje vietoje, kur iš esmės turime Andersono problemą. Aš ketinu įdėti 2017-06-25, visi jie taps raudoni, išskyrus tuos, kurie buvo iškrauti. Ir dabar gyvenimas juda toliau, tai kita diena. Mes gavome keletą priekabų 6/26 dienomis, taigi Andersonas nukopijuoja šiuos duomenis, įklijuoja čia, suformatuoja stulpelį „AutoFit“ ir tai bus „Priekaba 15.“. Spustelėkite, jei norite nukopijuoti tą žemyn ir palaipsniui, atsikratyti atvykusių. Taigi šitas atkeliavo šiandien, todėl visi jie turėtų tapti žaliais, bet jie netampa žaliais. Kodėl jie netampa žali? Jie netampa žaliais, nes šios formulės, šios sąlyginio formatavimo formulės čia, mes jas apžvelgsime. Jie yra užkoduoti naudoti $ A $ 1. O, tai tikrai blogai.
Gerai, todėl pabandykime čia patobulinti dalykus. Pirmas dalykas, kurį galiu padaryti, aš atsikratysiu visų tų dalykų ir grįšiu prie šio pirminio duomenų rinkinio ir būsiu šiek tiek protingesnis antrojo leidimo metu ir sakysiu, kad mums jo tikrai nereikia užrakinti A stulpelyje. Aš atsikratysiu to $ ženklo. Kitaip tariant, tai visada bus stulpelis kairėje iš mūsų, taigi tai bus mišri nuoroda, tačiau mes visada turime nurodyti į 1 USD. Redaguosime šią taisyklę, spustelėkite Gerai. Gerai, atlikus tą vieną pakeitimą, kai nukopijavome į dešinę ir įdėjome naujus duomenis, pvz., Šiandienos datą, jie veikia. Gerai, todėl tai puiku. Gyvenimas bus puikus 6/26 dieną, o gyvenimas bus puikus 26/27 dieną. Gerai, puikiai dirbi. Bet dabar susiduriame su problema, kai trūksta vietos puslapyje, todėl tai, ką darė Andersonas, krinta,iš esmės pradeda naują eilę ir įklijuoja ir tai būtų 6/28, bet ji netampa žalia.
Kodėl jis netampa žalias? Jis netampa žaliu, nes vis tiek turėjau naudoti $, kad grįžčiau į 1. Gerai, taigi dabar čia yra mįslė, čia yra problema. Ką tu darai dabar? Aš rimtai, ką tu darai dabar? „YouTube“ komentaruose noriu išgirsti, ką darytumėte dabar.
Žinai, taigi, žiūrėk, pateiktas argumentas, kad tai yra gerai, mes galime čia sustoti, nes naudodami A $ 1 mes tai padarėme, 1 dieną gyvenimas yra lengvas, nukopijuokite į 2 dieną, gyvenimas yra puikus . 3 dienos gyvenimas yra puikus. Tik kas ketvirtą dieną, kai čia nukopijuojame, Andersonui teks įeiti ir nustatyti sąlyginį formatavimą, redaguoti šį, taisyti taisyklę, pakeisti 1 į 18. Spustelėkite Gerai, redaguokite šią taisyklę ir pakeiskite, kad 1 būtų 18. Spustelėkite Gerai, spustelėkite Gerai. Gerai, taigi 4 diena, ta maža koregavimo kopija per 5 dieną, nukopijuokite per 6 dieną ir po to per 7 dieną. Atlikite tuos veiksmus dar kartą. Bet ei, pripažinkime. Šis darbalapis buvo sukurtas prieš šešis mėnesius su šiomis sąlyginio formatavimo taisyklėmis ir jie tiesiog turi veikti. Mums nereikia eiti į sąlyginį formatavimą vėl ir vėl ir vėl.
Pirmoji mano reakcija buvo tai, kad aš apsimesiu, jog tai yra skaičiuoklė, kurioje aš turiu keletą formulių, o šios formulės buvo sukurtos su absoliučiomis nuorodomis, bet man reikia, kad šias formules būtų galima nukopijuoti virš ar žemyn ir kad jos būtų santykinės kopijoje - ir kai kopijuoju čia, ir kai kopijuoju čia. Gerai, ir norėdamas tai pasiekti, aš naudosiu absoliučias nuorodas, kai nustatysiu dalykus, bet tada naudosiu „Rasti ir pakeisti“, „Ctrl H.“. Tarkime, atsikratykime tų santykinių nuorodų, pakeiskite kiekvieną $ A $ 1 į A1, Pakeiskite viską, spustelėkite Uždaryti ir dabar šį bloką, visos šios formulės yra skirtingos iki galo, nukopijuokite, įklijuokite ir įklijuokite ir tai veiks. Tai bus santykinis. Taigi aš pasakiau, gerai, gerai, ką mes turime padaryti. Mes turime išimti tuos dolerius iš formulės.Taigi aš ketinau parašyti makrokomandą, kuri leistų man redaguoti kiekvieną iš šių sąlyginio formatavimo taisyklių. Gerai, ir prieš parašydamas tą makrokomandą įrašysiu vienos sąlyginio formatavimo taisyklės pakeitimo makrokomandą, bet ne tai, kad čia yra 14 sąlyginio formatavimo taisyklių. Čia net netaikomos 14 * 3, 42 sąlyginio formatavimo taisyklės. Čia yra tik 3 sąlyginio formatavimo taisyklės ir šias 3 sąlyginio formatavimo taisykles taikome langelių diapazonui.Čia yra tik 3 sąlyginio formatavimo taisyklės ir šias 3 sąlyginio formatavimo taisykles taikome langelių diapazonui.Čia yra tik 3 sąlyginio formatavimo taisyklės ir šias 3 sąlyginio formatavimo taisykles taikome langelių diapazonui.
Taigi, jei aš tai pakeisčiau, pirmiausia turėčiau padaryti šias 3 sąlyginio formatavimo taisykles ir paversti jas 42 sąlyginio formatavimo taisyklėmis. Tada aš pradedu suklusti, nes kai Andersonas kopijuoja iš čia į čia, jis įves 42 naujas taisykles ir 42 naujas taisykles. Per vieną popieriaus lapą, turbūt 15 dienų, jis pristatys daugiau nei 600 taisyklių, 600 skirtingų formatų ir tai bus tiesiog siaubinga. Galų gale jūs pateksite į per daug formatavimo taisyklių dalyką, jau nekalbant apie tai, kad jį bus sunku nustatyti, net jei turime makrokomandą. Tai bus sunku nustatyti.
Teisingai, ką mes darome? Štai ką aš sugalvojau ir noriu išgirsti, ar turite ką nors geresnio. Aš pasakiau Andersonui ir sakiau: „Žinai, žiūrėk, tai gana paprasta. Visa tai yra vienas skaičiavimas ir tas skaičiavimas yra = TODAY- data, kuri yra kairėje nuo manęs “. Argi nebūtų šaunu, jei galėtume atsakyti į mažą pagalbinę skiltį čia, dešinėje. Tiesą sakant, mums visai nereikia naudoti jokių dolerių, mes tiesiog įdėsime visas tas langelius iki galo naudodami tą paprastą mažą formulę.
Matau Andersono veidą, jis nenori, kad papildomi daiktai būtų ištrinti, bet viskas gerai. Mes galime tai paslėpti, paslėpti vėliau, kad grįžtume į šias langelius ir pereitume į savo sąlyginį formatavimą. Visa ši diena TODAY-A1 paprasčiausiai bus nukreipta į C3 ir tai bus santykinė nuoroda. Kitaip tariant, kad ir kurioje ląstelėje būtų, mes visada ieškosime langelio dešinėje, spustelėkite Gerai, parašykite šiame, spustelėkite Gerai. Mes norime paslėpti šiuos duomenis čia, todėl eisiu ir CTRL 1. Aš naudosiu tris kabliataškius - ;;;, spustelėkite Gerai. Aš ten darysiu tą patį. Paspausiu F4, pakartosiu paskutinį veiksmą.
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.
Gerai, taigi mes čia. Paspartinęs, galų gale galite pažiūrėti, kaip tai veikia. Tiesiog darau testą čia. CTRL; pasikeis į mėlyną. Jei tai grįš į 6/26, jis pasikeis į raudoną. Ir jei tai šiandien, tai neveikia. Teisingai, nes štai ką aš darysiu. Ketvirtoji mano taisyklė - žalia spalva atsirado šiandien arba vakar. Aš tai naudosiu kaip numatytąjį. Jei nė viena iš šių trijų kitų taisyklių nėra teisinga, tai bus žalia, kad tai suteiks man dar vieną taisyklę, su kuria man tenka susidurti. Gerai.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2105.xlsx
