Segatud viidet kasutava tingimusliku vormindamise valemi seadistamine. Enamik tingimusliku vormindamise valemeid nõuab absoluutset viidet. Kuid see arvutustabel hoovis veoautode jälgimiseks nõuab
Vaata videot
- Anderson otsib viisi, kuidas saaks segatud tingimusvormingut sisaldavate andmete plokke kopeerida
- Kas on võimalik dollari märke eemaldada, kui tingimuslik vormindamine on seadistatud?
- Ei - mitte kümneid uusi reegleid kehtestamata
- Minu lahendus: abirakud, mis kasutavad suhtelisi viiteid tingimusvormingus segatud viite asendamiseks
- Muud selle episoodi tehnikad:
- Kui teil on neli tingimusliku vormindamise reeglit, seadistage esimesed 3 ja määrake seejärel neljas reegel vaikevärviks
- 1. ülesanne: vajutage klahvi F2, et peatada Exceli lahtriviidete sisestamine tingimusliku vormindamise dialoogi
- Ülesanne nr 2: tingimusliku vormindamise seadistamine
Video ärakiri
Õppige Exceli Podcast Episode 2105-st: tingimusliku vormingu kopeerimine segaviidetega
Tere, tere tulemast tagasi netisaate juurde. Täna saab see olema keeruline. Ma tegin eile seminari ja ühel seminari inimestest Andersonil oli huvitav probleemiga tabel. Olgu, Anderson haldab õue - saabuvad haagised ja haagised tuleb kolme päeva jooksul maha laadida. Hästi, nii see on - ta alustab, tead, see oli see päev, need olid saabunud haagised ja siis on tal seadistatud tingimuslik vormindamine, et kui haagis on maha laaditud, muutub see siniseks. Kui midagi on sinist, on kõik suurepärane. Kuid siis tahab ta asju värvida. Kui midagi saabus täna või eile, saab selle värvikoodiks roheline. Nii et täna on 29. juuni 2017, nii et see saabus eile ja kõik, mida ei laadita, on roheline, kuid kui see on rohkem kui üks päev vana,tahame esile tõsta asju kollasena ja kui see on rohkem kui kaks päeva vana, on need probleemid, mida me tahame punase värvina esile tuua. Ja see pole nii, et teate, see on üks tööleht kogu õue haldamiseks, eks? Asi pole selles, et seal oleks leht lehtede jaoks, mis saabusid 26. ja teine 27. kuupäevaks ning teine 28. kuupäevaks. Ja teate, et raskused on uue päeva saabumisel, kas nad kopeerivad eelmise päeva siia või alla siia.nad kas kopeerivad eelmise päeva siia või siia alla.nad kas kopeerivad eelmise päeva siia või siia alla.
Olgu, selle video mõte ei seisne selles, kuidas seda tingimuslikku vormingut seadistada. Nii et lähen selle kiiremini läbi, kuid kui teid huvitab selle tingimusliku vorminduse seadistamine, siis panen video lõppu kiirendamata versiooni väljundiks.
Olgu, nii me seal oleme. Selle kiirendamiseks saate lõpus vaadata, kuidas see töötab. Lihtsalt siin testi tegemine, CTRL; muutub siniseks. Kui see läheb tagasi seisule 6/26, muutub see punaseks ja kui see on täna, siis see ei tööta. See on õige, sest siin on see, mida ma teen, minu neljas reegliroheline saabus täna või eile, ma lihtsalt kasutan seda vaikimisi. Kui ükski neist kolmest muust reeglist ei vasta tõele, on roheline, et see annab mulle ühe reegli vähem, millega ma pean siin tegelema, eks?
Okei, nii et oleme nüüd selles kohas, kus meil on sisuliselt Andersoni probleem. Panen sisse 25.06.2017, need kõik muutuvad punaseks, välja arvatud need, mis on tühjaks laaditud. Ja nüüd läheb elu edasi, see on järgmine päev. Meil on mõned treilerid sisse 6/26 ja nii kopeerib Anderson need andmed, kleebib siia, vormindab veeru AutoFit ja see saab olema Treiler 15. Minge klõpsake selle allapoole kopeerimiseks ja juurdekasvuks, vabanege saabunutest. Ja see see saabus täna, nii et need kõik peaksid roheliseks muutuma, kuid nad ei muutu roheliseks. Miks nad ei muutu roheliseks? Nad ei muutu roheliseks, sest need valemid, need tingimusliku vormindamise valemid siin, me vaatame neid. Neil on USD A $ 1 kasutamiseks raske kood. Oh, see on tõesti halb.
Hästi, nii et proovime siin asju paremaks muuta. Esimese asjana saan kõigist neist lahti ja tulen selle algse andmekogumi juurde tagasi ning olen teisel läbimisel natuke targem ja ütlen, et meil pole tegelikult vaja seda veergu A lukustada. Ma saan sellest $ märgist lahti. Teisisõnu, see on alati veerg meist vasakul, nii et see on segatud viide, kuid me peame alati osutama dollarile 1. Muudame seda reeglit, klõpsake nuppu OK. Olgu, selle ühe muudatusega, kui kopeerisime paremale ja panime uued andmed, nagu tänane kuupäev, see töötab. Okei, nii et see on suurepärane. Elu saab olema suurepärane 26.06. Ja elu saab olema 6/27. Hästi, töötab suurepäraselt. Kuid nüüd satume probleemi juurde, kus lehel saab ruumi otsa ja nii see, mida Anderson on teinud, langeb,sisuliselt alustab uut rida ja kleebib ja see oleks 6/28, kuid see ei muutu roheliseks.
Miks see ei muutu roheliseks? See ei muutu roheliseks, sest ma pidin ikkagi kasutama dollarit, et jõuda tagasi 1. Heasse, ja nüüd on siin probleem, siin on probleem. Mis sa nüüd teed? Ja ma olen tõsine, mida sa nüüd teed? Tahan YouTube'i kommentaarides kuulda, mida teeksite praegu.
Tead, nii et hei vaata, on esitatud argument, et see on hea, võiksime siin peatuda, sest A $ 1 kasutades tegime selle nii, elu on 1. päeval lihtne, kopeerige üle teisele päevale, elu on suurepärane . 3. päeva elu on suurepärane. Alles igal neljandal päeval, kui siia alla kopeerime, peaks Anderson minema sisse ja seadistama tingimusliku vormingu, muutma seda, muutma reeglit, muutma selle väärtuseks 18. Klõpsake nuppu OK, muutke seda reeglit ja muutke see 1 18. Klõpsake nuppu OK, klõpsake nuppu OK. Hästi, nii et 4. päev, see väike korrigeerimiskoopia 5. päevaks, 6. päevaks ja seejärel 7. päevaks. Tehke need toimingud uuesti. Aga hei, olgem ausad. See tööleht loodi kuus kuud tagasi nende tingimusliku vormindamise reeglitega ja need peavad lihtsalt töötama. Me ei pea uuesti sisse minema ja tingimuslikku vormindamist tegema.
Minu esimene reaktsioon oli see, et ma teesklen, nagu oleks see arvutustabel, kus mul on siin mõned valemid ja need valemid olid ehitatud absoluutsete viidetega, kuid mul on vaja neid valemeid, et neid saaks kopeerida üle või alla ja olla koopias suhteline - nii siia kopeerimisel kui ka siia kopeerimisel. Olgu, ja selleks, et see toimiks, kasutan asjade seadistamisel absoluutseid viiteid, kuid siis kasutan leidmise ja asendamise klahvi Ctrl H. Ja oletame, et laseme neist suhtelistest viidetest lahti, muutke iga $ A $ 1 väärtuseks A1, asendage kõik, klõpsake nuppu Sule ja nüüd see plokk, kõik need valemid on lõpuni erinevad, kopeerige, kleepige ja kleepige ja see töötab. See on suhteline. Nii et ma ütlesin, olgu, seda me peame tegema. Me peame need $ valemist välja võtma.Ja nii kavatsesingi kirjutada makro, mis võimaldaks mul kõiki neid tingimusliku vormindamise reegleid redigeerida. Hästi, ja enne selle makro kirjutamist salvestasin ühe tingimusliku vormindamise reegli muutmise makro, kuid see pole nii, et siin oleks 14 tingimusliku vormindamise reeglit. See pole isegi siin 14 * 3, 42 tingimusliku vormindamise reeglite järgi. Siin on ainult 3 tingimusliku vormindamise reeglit ja me rakendame neid 3 tingimusliku vormindamise reeglit lahtrivahemikule.Siin on ainult 3 tingimusliku vormindamise reeglit ja me rakendame neid 3 tingimusliku vormindamise reeglit lahtrivahemikule.Siin on ainult 3 tingimusliku vormindamise reeglit ja me rakendame neid 3 tingimusliku vormindamise reeglit lahtrivahemikule.
Nii et kui ma seda muudaksin, peaksin esimese asjana võtma need 3 tingimusliku vormindamise reeglit ja muutma neist 42 tingimusliku vormindamise reeglit. Ja siis hakkan ma kripeldama, sest kui Anderson siit siia kopeerib, tutvustab ta 42 uut reeglit ja seejärel 42 uut reeglit. Ja ühe arvatavasti 15 päevase paberilehe jooksul tutvustab ta üle 600 reegli, 600 erinevat formaati ja see saab olema lihtsalt jube. Lõpuks tabate liiga palju vormindamisreegleid, rääkimata sellest, et selle seadistamine on keeruline, isegi kui meil on selle seadistamiseks makro. Selle seadistamine on keeruline.
Al, nii, mida me siis teeme? Siin ma mõtlesin välja ja tahan kuulda, kas teil on midagi paremat. Ma ütlesin Andersonile ja ütlesin: „Tead, vaata, see on üsna lihtne. Kõik need vaatavad ühte arvutust ja see arvutus on = TÄNA - kuupäev, mis jääb minust vasakule. " Ja kas poleks lahe, kui meil oleks see vastus väikeses abikolonnis siin paremal. Ja tegelikult ei pea me üldse dollareid kasutama, vaid lihtsalt paneme kõik need lahtrid selle lihtsa väikese valemiga lõpuni alla.
Ma näen Andersoni näo pilku, ta ei taha, et see lisakraam seal kustutatakse, aga see on okei. Me võime seda hiljem peita ja varjata, et tulla tagasi nendesse lahtritesse ja minna tingimuslikku vormindusse. Kogu see TÄNA-A1 osutab lihtsalt C3-le ja see on suhteline viide. Teisisõnu, vaatame ükskõik millises lahtris me alati paremale lahtrisse, klõpsake nuppu OK, kirjutage see, klõpsake nuppu OK. Tahame need andmed siia peita, nii et lähen sisse ja klahvikombinatsiooni CTRL 1. Kasutan kolme semikooloni - ;;;, klõpsake nuppu OK. Ma teen seal täpselt sama asja. Vajutan klahvi F4, kordan viimast toimingut.
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.
Olgu, nii me seal oleme. Selle kiirendamiseks saate lõpus vaadata, kuidas see töötab. Lihtsalt siin testi tegemine. CTRL; muutub siniseks. Kui see läheb tagasi seisule 6/26, muutub see punaseks. Ja kui see on täna, siis see ei tööta. See on õige, sest siin ma teen, mida ma teen. Minu neljas reegel, roheline saabus täna või eile, hakkan seda lihtsalt vaikimisi kasutama. Kui ükski neist kolmest muust reeglist ei vasta tõele, on roheline, et see annab mulle ühe reegli vähem, millega ma siin tegelema pean. Hästi.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2105.xlsx
