Налаштування формули умовного форматування, що використовує змішане посилання. Більшість формул умовного форматування вимагають абсолютного посилання. Але ця електронна таблиця для відстеження вантажівок у дворі вимагає
Переглянути відео
- Андерсон шукає спосіб скопіювати блоки даних, що містять змішане умовне форматування
- Чи є спосіб видалити знаки долара після налаштування умовного форматування?
- Ні - не без введення десятків нових правил
- Моє рішення: допоміжні комірки, які використовують відносні посилання для заміни змішаного посилання в умовному форматуванні
- Інші методи в цьому епізоді:
- Якщо у вас чотири правила умовного форматування, встановіть перші 3, а потім зробіть четверте правило типовим кольором
- Вихід №1: натисніть клавішу F2, щоб зупинити Excel від вставки посилань на комірки в діалоговому вікні умовного форматування
- Вихід №2: налаштування умовного форматування
Стенограма відео
Дізнайтеся Excel з подкасту Епізод 2105: Копіювання умовного формату зі змішаними посиланнями
Привіт, ласкаво просимо до мережі. Сьогодні це буде складно. Я вчора проводив семінар, і у одного з учасників семінару, Андерсона, була цікава електронна таблиця з проблемою. Гаразд, і Андерсон управляє двором - приїжджають причепи, і причепи мають бути вивантажені протягом трьох днів. Гаразд, так це - він починає, ви знаєте, це був день, це були трейлери, які прибули, а потім він налаштував умовне форматування, що після вивантаження трейлера воно змінюється на синій. Як тільки щось стає синім, все чудово. Але тоді він хоче розфарбувати речі. Якщо щось прийшло сьогодні чи вчора, воно отримує кольоровий код, позначений зеленим. Отже, сьогодні 29 червня 2017 року, отже, це прибуло вчора, і все, що не вивантажено, зелене, але коли це не один день,ми хочемо виділити речі жовтим кольором, і коли це більше двох днів, це проблеми, якими ми хочемо виділити речі червоним кольором. І справа не в цьому, знаєте, це один робочий аркуш для управління цілим двором, так? Справа не в тому, що є аркуш для речей, які прибули 26-го, а інший - 27-го і ще один 28-го. І ви знаєте, що складність полягає в тому, що настає новий день, або вони копіюють попередній день сюди або сюди.вони або копіюють попередній день сюди або сюди.вони або копіюють попередній день сюди або сюди.
Гаразд, суть цього відео не в тому, як налаштувати це умовне форматування. Тож я збираюся пройти це швидко, але якщо вас цікавить, як налаштувати це умовне форматування, я в кінці відео поставлю нешвидку версію як вихідну.
Гаразд, отже, ми там. Прискоривши це, ви можете подивитися наприкінці, щоб побачити, як це працює. Просто виконую тест тут, CTRL; зміниться на синій. Якщо це повернеться до 6/26, воно зміниться на червоний, а якщо сьогодні, це не працює. Це правильно, тому що ось що я збираюся зробити, моє четверте правило зеленого кольору прийшло сьогодні чи вчора, я просто буду використовувати це за замовчуванням. Якщо жодне з цих трьох інших правил не відповідає дійсності, тоді це буде зеленим, що дасть мені одне правило менше, з яким мені доведеться мати справу тут, добре?
Гаразд, отже, ми зараз у точці, коли по суті маємо проблему Андерсона. Я збираюся поставити 25.06.2017, вони всі стануть червоними, за винятком тих, які були розвантажені. І зараз життя рухається далі, це наступний день. 26 вересня ми отримали кілька трейлерів, і тому Андерсон копіює ці дані, вставляє їх сюди, відформатує стовпець AutoFit, і це буде трейлер 15. Натисніть, щоб скопіювати це і збільшити, позбутися тих, що надійшли. І ось цей прибув сьогодні, тож усі вони повинні зеленіти, але вони не зеленіють. Чому вони не зеленіють? Вони не зеленіють, тому що ці формули, ці формули умовного форматування прямо тут, ми їх розглянемо. Вони жорстко закодовані, щоб використовувати $ A $ 1. О, це справді погано.
Гаразд, давайте спробуємо покращити ситуацію тут. Перше, що я можу зробити, я позбудусь усіх цих і повернусь до цього оригінального набору даних і буду трохи розумнішим під час другого проходу і скажу, що нам насправді не потрібно замикати його на стовпець А. Я позбудуся цього знака $. Іншими словами, це завжди буде стовпець ліворуч від нас, тому це буде змішане посилання, але нам завжди потрібно вказувати на $ 1. Ми відредагуємо це правило, натисніть OK. Гаразд, з цією однією зміною, коли ми скопіювали праворуч і помістили нові дані, як сьогоднішня дата, це працює. Гаразд, так це чудово. Життя буде чудовим 26.06., А життя буде прекрасним 26.06. Гаразд, чудово працює. Але зараз ми стикаємося з проблемою, коли на нашій сторінці не вистачає місця, і тому те, що робив Андерсон, падає,по суті починає новий рядок і вставляє, і це буде 6/28, але це не стає зеленим.
Чому він не зеленіє? Це не стає зеленим, тому що мені все одно довелося використати долар, щоб повернутися до 1. Добре, і ось тепер ось загадка, ось проблема. Що ти зараз робиш? А я серйозно, що ти зараз робиш? Я хочу почути в коментарях YouTube, що б ви робили зараз.
Ви знаєте, так, привіт, дивіться, є аргумент, що це добре, ми могли б зупинитися прямо тут, тому що, використовуючи 1 долар, ми зробили це таким чином, життя стало легким на 1-й день, скопіюйте на 2-й день, життя чудове . День 3 життя прекрасне. Лише кожен 4-й день, коли ми копіюємо сюди, Андерсону доведеться зайти і налаштувати умовне форматування, відредагувати це, відредагувати правило, змінити 1 на 18. Натисніть кнопку ОК, відредагувати це правило та змінити, щоб 1 було 18. Клацніть OK, натисніть OK. Гаразд, так день 4, ця маленька коригувальна копія на 5-й день, на 6-й день, а потім на 7-й день. Знову виконайте ці дії. Але привіт, погодьмося. Цей аркуш був створений півроку тому з цими умовними правилами форматування, і їм просто потрібно працювати. Нам не потрібно заходити і робити умовне форматування знову і знову і знову.
Моя перша реакція полягала в тому, що я збираюся робити вигляд, що це електронна таблиця, де я маю тут деякі формули, і ці формули були побудовані з абсолютними посиланнями, але мені потрібні ці формули, щоб їх можна було скопіювати вгору або вниз і бути відносними в межах копії - і коли я копіюю сюди, і коли я копіюю сюди. Добре, і для того, щоб це працювало, я буду використовувати абсолютні посилання, коли налаштовую щось, але тоді я буду використовувати Знайти та замінити, Ctrl H. І скажімо, давайте позбудемося цих відносних посилань змінити кожні $ A $ 1 на A1, Замінити все, натиснути Закрити і тепер цей блок, всі ці формули різні до кінця, скопіюйте, вставте та вставте, і це буде працювати. Це буде відносно. Тож я сказав, добре, ну це те, що нам потрібно робити. Нам потрібно вийняти ці $ з формули.І тому я збирався написати макрос, який дозволив би мені редагувати кожне з цих правил умовного форматування. Добре, і до того, як я написав цей макрос, я збирався записати макрос зміни одного правила умовного форматування, але це не те, що тут є 14 правил умовного форматування. Тут справа навіть не в правилах умовного форматування 14 * 3, 42. Тут є лише 3 правила умовного форматування, і ми застосовуємо ці 3 правила умовного форматування до діапазону комірок.Тут є лише 3 правила умовного форматування, і ми застосовуємо ці 3 правила умовного форматування до діапазону комірок.Тут є лише 3 правила умовного форматування, і ми застосовуємо ці 3 правила умовного форматування до діапазону комірок.
Отже, якщо б я змінив це, перше, що мені довелося б зробити, це взяти ці 3 правила умовного форматування і зробити їх 42 умовними правилами форматування. А потім, я починаю журитися, тому що, коли Андерсон копіює звідси сюди, він збирається ввести 42 нові правила, а потім 42 нові правила. І протягом одного аркуша паперу, мабуть, 15 днів, він введе понад 600 правил, 600 різних форматів, і це просто буде жахливо. Врешті-решт ви потрапите на занадто багато правил форматування, не кажучи вже про те, що це буде важко налаштувати, навіть якщо у нас є макрос для його налаштування. Це буде важко налаштувати.
Так, так, що ми робимо? Ось що я придумав, і я хочу почути, якщо у вас є щось краще, ніж це. Я сказав Андерсону, я сказав: “Знаєш, дивись, це досить просто. Всі вони дивляться на один розрахунок, і цей розрахунок = СЬОГОДНІ - дата, яка знаходиться зліва від мене ". І чи не було б круто, якби ми могли отримати цю відповідь у маленькій допоміжній колонці тут праворуч. І насправді нам зовсім не потрібно використовувати $, ми просто покладемо всі ці клітини до кінця за допомогою цієї простої маленької формули.
Я бачу погляд Андерсона на обличчі, він не хоче, щоб зайві речі там стиралися, але це нормально. Ми можемо приховати, приховати це пізніше, тому ми повернемося до цих комірок і перейдемо до нашого умовного форматування. Весь цей СЬОГОДНІ-А1 просто буде вказувати на С3, і це буде відносним посиланням. Отже, іншими словами, незалежно від того, в якій клітині ми знаходимось, ми завжди будемо шукати клітинку праворуч, клацнути ОК, писати в цій, клацнути ОК. Ми хочемо приховати ці дані тут, тому я заходжу і 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
