Постављање формуле условног форматирања која користи мешовиту референцу. Већина формула за условно форматирање захтева апсолутну референцу. Али ова табела за праћење камиона у дворишту захтева
Погледајте видео
- Андерсон тражи начин да може да копира блокове података који садрже мешовито условно форматирање
- Постоји ли начин уклањања знакова долара када се постави условно форматирање?
- Не - не без увођења десетина нових правила
- Моје решење: помоћне ћелије које користе релативне референце да замене мешовиту референцу у условном форматирању
- Остале технике у овој епизоди:
- Ако имате четири правила условног форматирања, подесите прва 3, а затим нека четврто правило буде подразумевана боја
- Излаз # 1: Притисните Ф2 да бисте спречили Екцел да убацује референце на ћелије у дијалог условног форматирања
- Оуттаке # 2: постављање условног форматирања
Видео транскрипт
Научите Екцел из Подцаст епизоде 2105: Копирање условног формата са мешовитим референцама
Хеј, добродошао назад на нетцаст. Ово ће данас бити сложено. Јуче сам држао семинар и један од људи на семинару, Андерсон, имао је занимљиву табелу са проблемом. У реду, Андерсон управља двориштем - стижу приколице и приколице се морају истоварити у року од три дана. У реду, дакле ово је - он започиње, знате, ово је био дан, то су биле приколице које су стигле, а затим је подесио условно форматирање да се након што се приколица истовари, она промени у плаву. Једном кад је нешто плаво, све је сјајно. Али онда, жели да обоји ствари. Ако је нешто стигло данас или јуче, боја се кодира као зелена. Дакле, данас је 29. јун 2017. године, па је ово стигло јуче и све што није истоварено је зелено, али када је старо више од једног дана,желимо ствари истакнути жутом бојом, а када је стара више од два дана, то су проблеми које желимо истакнути црвеном бојом. И није то, знате, ово је један радни лист за управљање целим двориштем, зар не? Није да постоји лист за ствари које су стигле 26. и још један 27. и још један 28. И знате како је потешкоћа како долази нови дан, или преписују претходни дан овде или доле овде.или преписују претходни дан овде или доле овде.или преписују претходни дан овде или доле овде.
У реду, поента овог видео снимка није у томе како да подесите ово условно форматирање. Дакле, проћи ћу кроз ово, али ако вас занима како да подесите ово условно форматирање, на крају видео снимка ставићу верзију без убрзања.
Ок, ето нас. Убрзавши то, на крају можете погледати како бисте видели како то функционише. Само радим тест овде, ЦТРЛ; промениће се у плаву. Ако се ово врати на 6/26, промениће се у црвену, а ако је данас, не ради. Тачно, јер ево шта ћу да урадим, моје четврто правило зелено стигло је данас или јуче, само ћу то користити као подразумевано. Ако ниједно од ова друга три правила није тачно, онда ће бити зелено што ће ми дати једно правило мање са којим морам овде да се бавим, у реду?
У реду, сада смо на месту где у суштини имамо Андерсонов проблем. Ставићу 25.6.2017, сви ће постати црвени осим оних који су истоварени. А сада живот иде даље, сутрадан је. Добили смо неке приколице 26.6. И тако Андерсон копира ове податке, налепи овде, форматирајте колону АутоФит, и ово ће бити приколица 15. Идите да бисте то копирали и повећали, решите се пристиглих. И тако је овај стигао данас, па би сви требали постати зелени, али не зелени. Зашто не постану зелени? Не постају зелене јер ћемо ове формуле, ове формуле условног форматирања управо погледати. Они су тешко кодирани да користе 1 А $ 1. Ох, то је стварно лоше.
Па, хајде да покушамо да побољшамо ствари овде. Прво што могу да урадим, ослободићу се свих тих и вратићу се овом оригиналном скупу података, а при другом пролазу ћу бити мало паметнији и рећи да то заправо не треба закључавати на колону А. Решићу се тог знака $. Другим речима, то ће увек бити колона лево од нас, па ће то бити мешовита референца, али увек морамо да укажемо на $ 1. Уредићемо ово правило, кликните на ОК. У реду сада, уз ту једну промену када смо копирали удесно и ставили нове податке, као што је данашњи датум, то функционише. Ок, па ово је сјајно. Живот ће бити сјајан 26.6., А живот 27.6. У реду, одлично радим. Али сада наилазимо на проблем где нам понестаје простора на страници и тако оно што је Андерсон радио пада,у суштини започиње нови ред и пасте, а ово би било 6/28, али не постаје зелено.
Зашто не постаје зелено? Не постаје зелено јер сам још увек морао да употребим $ да бих се вратио на 1. У реду, и ето, сад је овде загонетка, ево проблема. Шта радиш тренутно? И озбиљна сам, чиме се сада бавиш? Желим да чујем у ИоуТубе коментарима шта бисте сада радили.
Знате, па, хеј, погледајте, постоји аргумент да је ово добро, могли бисмо се зауставити управо овде јер смо помоћу А $ 1 направили такав начин, живот је лак на 1. дан, копирајте на 2. дан, живот је сјајан . Трећи живот је сјајан. Тек сваког четвртог дана када овде копирамо Андерсон би морао да уђе и постави условно форматирање, измијени ово, уреди правило, промијени да 1 буде 18. Кликните ОК, уредите ово правило и промијените да 1 буде 18. Кликните ОК, кликните ОК. У реду, дакле, 4. дан, та мала копија за прилагођавање за 5. дан, за 6. дан, а затим за 7. дан. Поновите те кораке. Али хеј, признајмо. Овај радни лист је постављен пре шест месеци са овим правилима условног форматирања и они само треба да раде. Не треба да улазимо и радимо условно форматирање изнова и изнова и изнова.
Моја прва реакција је била да ћу се претварати да је ово прорачунска табела у којој овде имам неке формуле и те формуле су направљене са апсолутним референцама, али требају ми те формуле да могу да се копирају преко или надоле и буду релативне у копији - и када копирам овде, и када копирам овде. У реду, и да би то функционисало, користићу апсолутне референце када постављам ствари, али онда ћу користити Пронађи и замени, Цтрл Х. И рецимо да се отарасимо тих релативних референци, промените сваки $ А $ 1 у А1, Замените све, кликните Затвори и сада овај блок, све ове формуле су различите до краја, копирајте, налепите и налепите и то ће функционисати. Биће релативно. Па сам рекао, у реду, па то је оно што морамо учинити. Морамо да извадимо тих $ из формуле.И тако сам хтео да напишем макро који ће ми омогућити да уређујем свако од ових условних правила форматирања. У реду, и пре него што сам написао тај макро, забележићу макро промене једног правила условног форматирања, али није да овде постоји 14 условних правила форматирања. Овде се чак не ради ни о условима условног форматирања 14 * 3, 42. Овде постоје само 3 правила условног форматирања и ми примењујемо та 3 правила условног форматирања на низ ћелија.Овде имамо само 3 правила условног форматирања и ми примењујемо та 3 правила условног форматирања на низ ћелија.Овде имамо само 3 правила условног форматирања и ми примењујемо та 3 правила условног форматирања на низ ћелија.
Дакле, ако бих ово променио, прво што бих морао да урадим је да узмем ова 3 правила условног форматирања и поставим да буду 42 правила условног форматирања. А онда, почињем да се грозим, јер док Андерсон копира одавде до овде, увешће 42 нова правила, а затим 42 нова правила. И током једног листа папира са вероватно 15 дана, увешће преко 600 правила, 600 различитих формата и то ће бити ужасно. На крају ћете погодити превише правила о форматирању, а да не помињемо да ће то бити тешко поставити чак и ако имамо макро за подешавање. Биће тешко поставити.
У реду, па шта да радимо? Ево шта сам смислио и желим да чујем да ли имате нешто боље од тога. Рекао сам Андерсону, рекао сам, „Знате, изгледа прилично је једноставно. Сви они гледају један прорачун и тај прорачун је = ДАНАС - датум који је лево од мене. “ И не би ли било цоол, кад бисмо могли добити тај одговор у малој помоћној колони овде десно. И у ствари, уопште не морамо да користимо $, само ћемо све те ћелије потпуно спустити помоћу те једноставне мале формуле.
Видим поглед Андерсоновог лица, он не жели да се те додатне ствари избришу, али то је у реду. То можемо сакрити, сакрити касније, па се вратимо у ове ћелије и пређемо на условно форматирање. Цео тај ДАНАС-А1 једноставно ће указивати на Ц3 и то ће бити релативна референца. Другим речима, у којој год ћелији да се налазимо, увек ћемо је гледати удесно, кликните на ОК, напишите на ову, кликните на ОК. Желимо да сакријемо ове податке овде, па ћу ући и ЦТРЛ 1. Користићу три тачке са зарезом - ;;;, кликните на ОК. И тамо ћу радити потпуно исту ствар. Притиснућу Ф4, поновити последњу акцију.
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.
Ок, ето нас. Убрзавши то, на крају можете погледати како бисте видели како то функционише. Само радим тест овде. ЦТРЛ; промениће се у плаву. Ако се ово врати на 6/26, промениће се у црвену. А ако је данас, не иде. Тачно, јер ево шта ћу учинити. Моје четврто правило, зелено је стигло данас или јуче, само ћу то користити као подразумевано. Ако ниједно од ова друга три правила није тачно, биће зелено што ће ми дати једно правило мање са којим морам овде да се бавим. У реду.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2105.клск
