Създаване на формула за условно форматиране, която използва смесена препратка. Повечето формули за условно форматиране изискват абсолютна препратка. Но тази електронна таблица за проследяване на камиони в двора изисква
Гледам видео
- Андерсън търси начин да може да копира блокове от данни, съдържащи смесено условно форматиране
- Има ли начин за премахване на знаците за долар, след като е зададено условно форматиране?
- Не - не без въвеждане на десетки нови правила
- Моето решение: помощни клетки, които използват относителни препратки, за да заменят смесената препратка в условно форматиране
- Други техники в този епизод:
- Ако имате четири правила за условно форматиране, настройте първите 3 и след това направете четвъртото правило да бъде цветът по подразбиране
- Изход # 1: Натиснете F2, за да спрете Excel да вмъква препратки към клетки в диалоговия прозорец за условно форматиране
- Outtake # 2: настройка на условно форматиране
Видео транскрипция
Научете Excel от Подкаст Епизод 2105: Копиране на условен формат със смесени препратки
Хей, добре дошъл отново в netcast. Днес това ще бъде сложно. Правих семинар вчера и един от хората в семинара, Андерсън, имаше интересна електронна таблица с проблем. Добре, и Андерсън управлява двор - ремаркетата пристигат и ремаркетата трябва да бъдат разтоварени в рамките на три дни. Добре, това е - той започва, знаете ли, това беше денят, това бяха ремаркетата, които пристигнаха и след това той е настроил условно форматиране, че след като ремаркето се разтовари, то се променя на синьо. След като нещо е синьо, всичко е страхотно. Но тогава той иска да оцвети нещата. Ако нещо е пристигнало днес или вчера, то се оцветява като зелено. Така че днес е 29 юни 2017 г., така че това е пристигнало вчера и всичко, което не е разтоварено, е зелено, но когато е на повече от един ден,искаме да подчертаем нещата като жълто и когато са на повече от два дни, това са проблемите, които искаме да подчертаем нещата като червени. И не е това, знаете ли, това е един работен лист за управление на целия двор, нали? Не че има лист за неща, които са пристигнали на 26-ти и друг за 27-ми и още един за 28-и. И знаете, че трудността е, когато настъпва нов ден, те или копират предишния ден тук или надолу тук.те или копират предишния ден тук или надолу тук.те или копират предишния ден тук или надолу тук.
Добре сега, смисълът на това видео не е как да настроите това условно форматиране. Така че ще ускоря това, но ако се интересувате от това как да настроите това условно форматиране, в края на видеоклипа ще поставя версията, която не е ускорена.
Добре, така че сме. Ускорихте това, можете да гледате в края, за да видите как работи това. Просто правя тест тук, CTRL; ще се промени на синьо. Ако това се върне към 6/26, то ще се промени на червено и ако е днес, не работи. Точно така, защото ето какво ще направя, четвъртото ми правило пристигна зелено днес или вчера, просто ще го използвам по подразбиране. Ако нито едно от тези три правила не е вярно, тогава ще бъде зелено, че ще ми даде едно правило по-малко, с което трябва да се справя тук, нали?
Добре, така че сега сме на мястото, където по същество имаме проблема с Андерсън. Ще сложа в 25.6.2017 г., всички те ще станат червени, с изключение на тези, които са разтоварени. И сега животът върви напред, това е на следващия ден. Имаме няколко трейлъра на 6/26 и така Андерсън копира тези данни, поставя тук, форматира Column AutoFit и това ще бъде Trailer 15. Отидете, за да копирате това надолу и да увеличите, да се отървете от пристигналите. И така, този пристигна днес, така че всички те трябва да станат зелени, но те не стават зелени. Защо не стават зелени? Те не стават зелени, защото тези формули, тези условни формули за форматиране тук, ще ги разгледаме. Те са трудно кодирани да използват $ A $ 1. О, това е наистина лошо.
Добре, така че нека опитаме и подобрим нещата тук. Първото нещо, което мога да направя, ще се отърва от всички тях и ще се върна към този оригинален набор от данни и ще бъда малко по-умен при втория проход и ще кажа, че всъщност не е нужно да го заключваме до колона А. Ще се отърва от знака $. С други думи, това винаги ще бъде колоната вляво от нас, така че това ще бъде смесена препратка, но ние винаги трябва да сочим към $ 1. Ще редактираме това правило, щракнете върху OK. Добре сега, с тази една промяна, когато копирахме вдясно и поставихме нови данни, като днешната дата, тя работи. Добре, значи това е страхотно. Животът ще бъде страхотен на 6/26 и животът ще бъде страхотен на 6/27. Добре, работи чудесно. Но сега се сблъскваме с проблема, при който ни липсва място на страницата и така това, което Андерсън прави, намалява,по същество започва нов ред и поставя и това ще бъде 6/28, но не става зелено.
Защо не става зелено? Не става зелено, защото все пак трябваше да използвам $, за да се върна на 1. Добре, и ето сега е загадката, ето проблема. Какво правиш сега? И аз съм сериозен, какво правиш сега? Искам да чуя в коментарите на YouTube какво бихте направили сега.
Знаете ли, така че хей вижте, има аргумент, че това е добре, бихме могли да спрем точно тук, защото използвайки A $ 1, направихме така, животът е лесен в Ден 1, копирайте в Ден 2, животът е страхотен . Ден 3 животът е страхотен. Едва на всеки 4-ти ден, когато копираме тук, Андерсън ще трябва да влезе и да настрои условно форматиране, да редактира това, да редактира правилото, да промени това 1 на 18. Щракнете върху OK, редактирайте това правило и променете това 1, за да бъде 18. Щракнете върху OK, щракнете върху OK. Добре, така че Ден 4, това малко копиране за корекция за Ден 5, копиране за Ден 6 и след това копиране за Ден 7. Направете тези стъпки отново. Но хей, нека си признаем. Този работен лист е създаден преди шест месеца с тези правила за условно форматиране и те просто трябва да работят. Не е нужно да влизаме и да правим условно форматиране отново и отново и отново.
Първата ми реакция беше, че ще се преструвам, че това е електронна таблица, в която имам някои формули тук и тези формули са изградени с абсолютни препратки, но имам нужда от тези формули, за да мога да ги копирам над или надолу и да бъдат относителни в копието - както когато копирам тук, така и когато копирам тук. Добре, и за да накарам това да работи, ще използвам абсолютни препратки, когато настройвам нещата, но след това ще използвам Намери и замени, Ctrl H. И да кажем, нека се отървем от тези относителни препратки, сменете всеки $ A $ 1 на A1, Заменете всички, щракнете върху Затвори и сега този блок, всички тези формули са различни по целия път надолу, копирайте, поставете и поставете и ще работи. Ще бъде относително. Така че казах, добре, добре, това трябва да направим. Трябва да извадим тези $ от формулата.И така щях да напиша макрос, който ще ми позволи да редактирам всяко едно от тези правила за условно форматиране. Добре, и преди да напиша този макрос, щях да запиша макроса за промяна на едно правило за условно форматиране, но не е, че тук има 14 правила за условно форматиране. Тук не става въпрос дори за правилата за условно форматиране 14 * 3, 42. Тук има само 3 правила за условно форматиране и ние прилагаме тези 3 правила за условно форматиране към набор от клетки.s тук има само 3 правила за условно форматиране и ние прилагаме тези 3 правила за условно форматиране към набор от клетки.s тук има само 3 правила за условно форматиране и ние прилагаме тези 3 правила за условно форматиране към набор от клетки.
Така че, ако бих променил това, първото нещо, което трябва да направя, е да взема тези 3 правила за условно форматиране и да ги направя 42 правила за условно форматиране. И тогава започвам да се свивам, защото докато Андерсън копира от тук насам, той ще въведе 42 нови правила и след това 42 нови правила. И в течение на един лист хартия с вероятно 15 дни, той ще въведе над 600 правила, 600 различни формата и това просто ще бъде ужасно. В крайна сметка ще ударите твърде много правила за форматиране, да не говорим, че ще бъде трудно да се настрои, дори ако имаме макрос, който да го настрои. Ще бъде трудно да се създаде.
Добре де, какво да правим? Ето какво измислих и искам да чуя дали имате нещо по-добро от това. Казах на Андерсън, казах: „Знаете ли, вижте, това е доста просто. Всички те гледат едно изчисление и това изчисление е = ДНЕС - датата, която е вляво от мен. " И не би ли било готино, ако можехме да получим този отговор в малка помощна колона тук вдясно. И всъщност изобщо не трябва да използваме никакви $, просто ще сложим всички тези клетки докрай с тази проста малка формула.
Виждам погледа на лицето на Андерсън, той не иска тези излишни неща да бъдат изтрити, но това е добре. Можем да скрием, скрием това по-късно, за да се върнем в тези клетки и да преминем в нашето условно форматиране. Цялото това ДНЕС-А1 просто ще сочи към С3 и това ще бъде относителна препратка. С други думи, в която и клетка да се намираме, ние винаги ще гледаме в клетката вдясно, щракнете върху OK, напишете в тази, щракнете върху OK. Искаме да скрием тези данни тук, така че ще вляза и CTRL 1. Ще използвам трите точки и запетая - ;;;, щракнете върху OK. Ще направя точно същото там. Ще натисна F4, ще повторя последното действие.
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.
Добре, така че сме. Ускорихте това, можете да гледате в края, за да видите как работи това. Просто правя тест тук. CTRL; ще се промени на синьо. Ако това се върне към 6/26, то ще се промени на червено. И ако е днес, не работи. Точно така, защото ето какво ще направя. Четвъртото ми правило, зелено пристигна днес или вчера, просто ще го използвам по подразбиране. Ако нито едно от тези три правила не е вярно, тогава ще бъде зелено, че ще ми даде едно правило по-малко, с което трябва да се справя тук. Добре.
Свали файл
Изтеглете примерния файл тук: Podcast2105.xlsx
