Настройка формулы условного форматирования, использующей смешанную ссылку. Большинство формул условного форматирования требуют абсолютной ссылки. Но для этой таблицы для отслеживания грузовиков во дворе требуется
Смотреть видео
- Андерсон ищет способ копировать блоки данных, содержащие смешанное условное форматирование.
- Есть ли способ удалить знаки доллара после установки условного форматирования?
- Нет - не без введения десятков новых правил
- Мое решение: вспомогательные ячейки, использующие относительные ссылки для замены смешанной ссылки в условном форматировании
- Другие техники в этом эпизоде:
- Если у вас есть четыре правила условного форматирования, настройте первые 3, а затем сделайте четвертое правило цветом по умолчанию.
- Выход №1: нажмите F2, чтобы Excel не вставлял ссылки на ячейки в диалоговом окне условного форматирования.
- Вывод # 2: настройка условного форматирования
Стенограмма видео
Изучите Excel из эпизода подкаста 2105: Копирование условного формата со смешанными ссылками
Привет, добро пожаловать обратно в сетевую трансляцию. Сегодня это будет непросто. Вчера я проводил семинар, и у одного из участников семинара, Андерсона, была интересная таблица с проблемой. Хорошо, и Андерсон управляет верфью - трейлеры прибывают, и трейлеры нужно разгрузить в течение трех дней. Хорошо, так вот - он начинает, вы знаете, это был день, это были трейлеры, которые прибыли, а затем он установил условное форматирование, которое после выгрузки трейлера меняет цвет на синий. Если что-то посинело, все отлично. Но потом он хочет раскрасить вещи. Если что-то прибыло сегодня или вчера, оно получает зеленый цвет. Итак, сегодня 29 июня 2017 года, поэтому он был доставлен вчера, и все, что не выгружено, зеленое, но когда ему больше одного дня,мы хотим выделить объекты желтым цветом, а когда ему больше двух дней, это проблемы, которые мы хотим выделить красным. И дело не в том, что это одна таблица для управления всем двором, верно? Дело не в том, что есть лист для вещей, которые прибыли 26-го, другой для 27-го и еще один для 28-го. И вы знаете, что трудность в том, что когда наступает новый день, они либо копируют предыдущий, либо сюда, либо вниз.они либо копируют предыдущий день сюда, либо сюда.они либо копируют предыдущий день сюда, либо сюда.
Хорошо, сейчас смысл этого видео не в том, как настроить это условное форматирование. Итак, я собираюсь ускорить это, но если вам интересно, как настроить это условное форматирование, я помещу версию без ускорения как отрывок в конце видео.
Ладно, вот и мы. Ускорьте это, вы можете посмотреть в конце, чтобы увидеть, как это работает. Просто провожу здесь тест, CTRL; изменится на синий. Если это вернется к 6/26, он станет красным, а если сегодня, это не работает. Это верно, потому что вот что я собираюсь сделать, мое четвертое зеленое правило появилось сегодня или вчера, я просто собираюсь использовать его по умолчанию. Если ни одно из этих трех других правил не является верным, то это будет зеленым, потому что оно даст мне на одно правило меньше, с которым я должен иметь дело здесь, хорошо?
Итак, мы подошли к тому моменту, когда, по сути, возникла проблема Андерсона. Собираюсь поставить 25.06.2017, они все станут красными кроме тех, что были выгружены. А теперь жизнь продолжается, наступил следующий день. У нас есть несколько трейлеров 26.06, и поэтому Андерсон копирует эти данные, вставляет сюда, форматирует столбец AutoFit, и это будет трейлер 15. Нажмите, чтобы скопировать его вниз и увеличить, избавьтесь от тех, которые прибыли. И вот этот прибыл сегодня, так что все они должны стать зелеными, но они не станут зелеными. Почему они не становятся зелеными? Они не становятся зелеными, потому что эти формулы, эти формулы условного форматирования прямо здесь, мы рассмотрим их. Они жестко запрограммированы на использование $ A $ 1. О, это действительно плохо.
Хорошо, давай попробуем здесь улучшить. Первое, что я могу сделать, я собираюсь избавиться от всего этого, вернуться к исходному набору данных, быть немного умнее на втором проходе и сказать, что нам действительно не нужно привязывать его к столбцу A. Я избавлюсь от этого знака $. Другими словами, это всегда будет столбец слева от нас, так что это будет смешанная ссылка, но мы всегда должны указывать на $ 1. Отредактируем это правило, жмем ОК. Хорошо, теперь, с одним изменением, когда мы скопировали вправо и вставили новые данные, например сегодняшнюю дату, это работает. Хорошо, это здорово. Жизнь будет прекрасна 26 июня и жизнь будет прекрасна 27 июня. Хорошо, отлично работает. Но теперь мы сталкиваемся с проблемой, когда у нас заканчивается место на странице, поэтому то, что делал Андерсон, падает,по сути, начинает новую строку и вставляет ее, и это будет 6/28, но он не станет зеленым.
Почему не становится зеленым? Он не становится зеленым, потому что мне все еще пришлось использовать $, чтобы вернуться к 1. Хорошо, теперь вот загадка, вот в чем проблема. Чем вы сейчас занимаетесь? И я серьезно, что ты теперь делаешь? Я хочу услышать в комментариях на YouTube, что бы вы сделали сейчас.
Знаете, послушайте, есть аргумент, что это хорошо, мы могли бы остановиться прямо здесь, потому что, используя A $ 1, мы сделали это таким образом, жизнь легка в День 1, скопируйте в День 2, жизнь прекрасна . День 3 жизнь прекрасна. Только каждые 4 дня, когда мы копируем сюда, Андерсон должен был бы войти и настроить условное форматирование, отредактировать это, отредактировать правило, изменить 1 на 18. Нажмите OK, отредактируйте это правило и измените это 1 на 18. Щелкните OK, щелкните OK. Итак, День 4, скопируйте эту небольшую копию для Дня 5, скопируйте для Дня 6, а затем скопируйте для Дня 7. Повторите эти шаги снова. Но посмотрим правде в глаза. Этот рабочий лист был настроен шесть месяцев назад с этими правилами условного форматирования, и они просто должны работать. Нам не нужно снова, и снова, и снова выполнять условное форматирование.
My first reaction was I'm going to pretend like this is a spreadsheet where I have some formulas here and those formulas were built with absolute references but I need those formulas to be able to be copied over or down, and be relative within the copy - both when I copy to here and when I copy to here. Alright, and in order to get that to work, I'm going to use absolute references when I set things up but then I'm going to use Find and Replace, Ctrl H. And let’s say let's get rid of those relative references, change every $A$1 to A1, Replace All, click Close and now this block, all of these formulas are different all the way down, copy, paste and paste and it will work. It will be relative. So I said, alright, well that's what we need to do. We need to take those $ out of the formula. And so I was going to write a macro that would allow me to edit each one of these conditional formatting rules. Alright, and before I wrote that macro I was going to record the macro of changing one conditional formatting rule, but it's not that there are 14 conditional formatting rules here. It's not even to the 14*3, 42 conditional formatting rules here. There's only 3 conditional formatting rules here and we're applying those 3 conditional formatting rules to a range of cells.
Итак, если бы я изменил это, первое, что мне нужно было бы сделать, это взять эти 3 правила условного форматирования и сделать их 42 правилами условного форматирования. А потом меня начинает передергивать, потому что, копируя Андерсон отсюда сюда, он собирается ввести 42 новых правила, а затем 42 новых правила. И в течение одного листа бумаги примерно за 15 дней он собирается ввести более 600 правил, 600 различных форматов, и это будет просто ужасно. В конечном итоге вы столкнетесь со слишком большим количеством правил форматирования, не говоря уже о том, что его будет сложно настроить, даже если у нас есть макрос для его настройки. Это будет сложно настроить.
Al right, so what do we do? Here's what I came up with and I want to hear if you have something better than that. I said to Anderson, I said, “You know, look it's pretty simple. All of these are looking at one calculation and that calculation is =TODAY- the date that’s to the left of me.” And wouldn’t it be cool, if we could have that answer in a little helper column over here to the right. And in fact, we don't have to use any $ at all, we'll just put all of those cells all the way down with that simple little formula.
I can see the look at Anderson’s face, he doesn't want that extra stuff out there erased but that's okay. We can hide, hide that later so we come back into these cells and go into our conditional formatting. That whole TODAY-A1 is simply going to be pointing to C3 and that's going to be a relative reference. So in other words, whatever cell we're in we're always going to look in the cell to the right, click OK, write at this one, click OK. We want to hide this data over here so I'll go in and CTRL 1. I'm going to use the three semicolons - ;;;,click OK. I’m going to do the exact same thing there. I’ll press F4, repeat the last action.
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
