Configurando uma fórmula de formatação condicional que usa uma referência mista. A maioria das fórmulas de formatação condicional requer uma referência absoluta. Mas esta planilha para rastrear caminhões em um pátio requer
Assistir vídeo
- Anderson está procurando uma maneira de copiar blocos de dados contendo formatação condicional mista
- Existe uma maneira de remover os cifrões depois que a formatação condicional é configurada?
- Não - não sem introduzir dezenas de novas regras
- Minha solução: células auxiliares que usam referências relativas para substituir a referência mista na formatação condicional
- Outras técnicas neste episódio:
- Se você tiver quatro regras de formatação condicional, configure as 3 primeiras e, em seguida, faça com que a quarta regra seja a cor padrão
- Outtake # 1: Pressione F2 para parar o Excel de inserir referências de células na caixa de diálogo de formatação condicional
- Outtake # 2: configurar a formatação condicional
Transcrição de vídeo
Aprenda Excel com o Podcast Episódio 2105: Copiando formato condicional com referências mistas
Ei, bem-vindo de volta ao netcast. Isso vai ser complicado hoje. Eu estava dando um seminário ontem e uma das pessoas no seminário, Anderson, tinha uma planilha interessante com um problema. Tudo bem, e Anderson gerencia um pátio - os reboques chegam e os reboques devem ser descarregados em três dias. Tudo bem, então este é - ele começa, você sabe, este era o dia, esses foram os trailers que chegaram e então ele configurou a formatação condicional que, assim que o trailer for descarregado, ele muda para azul. Uma vez que algo está azul, tudo está ótimo. Mas ele quer codificar as coisas por cores. Se algo chegou hoje ou ontem, ele é codificado por cores como verde. Hoje é 29 de junho de 2017, chegou ontem e tudo o que não foi descarregado está verde, mas quando tem mais de um dia,queremos destacar as coisas como amarelas e, quando tiver mais de dois dias, esses são os problemas que queremos destacar como vermelhas. E não é isso né, essa é uma planilha pra cuidar do quintal todo né? Não é que haja uma folha para as coisas que chegaram dia 26 e outra para o dia 27 e outra para o dia 28. E você sabe que a dificuldade é que quando um novo dia chega, eles copiam o dia anterior para cá ou para cá.eles copiam o dia anterior aqui ou aqui embaixo.eles copiam o dia anterior aqui ou aqui embaixo.
Tudo bem agora, o objetivo deste vídeo não é sobre como configurar essa formatação condicional. Vou acelerar isso, mas se você estiver interessado em como configurar essa formatação condicional, colocarei a versão não acelerada como saída no final do vídeo.
Ok, então aí estamos. Acelere, você pode assistir no final para ver como funciona. Só fazendo um teste aqui, CTRL; mudará para azul. Se voltar a 26/6, mudará para vermelho e se for hoje, não está funcionando. Isso mesmo porque aqui está o que vou fazer, minha quarta regra verde chegou hoje ou ontem, vou apenas usar isso como o padrão. Se nenhuma dessas outras três regras for verdadeira, então será verde que me dará uma regra a menos que eu tenho que lidar aqui, certo?
Ok, agora estamos no ponto em que essencialmente temos o problema de Anderson. Vou colocar em 25/06/2017, todos eles ficarão vermelhos, exceto os que foram descarregados. E agora a vida segue em frente, é o dia seguinte. Conseguimos alguns trailers em 26/6 e o Anderson copia esses dados, cole aqui, formate o Autoajuste da coluna, e este será o Trailer 15. Clique para copiar e incrementar, livre-se dos que chegaram. E esse aqui chegou hoje, então todos deveriam ficar verdes, mas não estão ficando verdes. Por que eles não estão ficando verdes? Eles não estão ficando verdes porque essas fórmulas, essas fórmulas de formatação condicional bem aqui, veremos isso. Eles são codificados para usar $ A $ 1. Oh, isso é muito ruim.
Tudo bem, então vamos tentar melhorar as coisas aqui. A primeira coisa que posso fazer é livrar-me de tudo isso e voltar a este conjunto de dados original e ser um pouco mais inteligente na segunda passagem e dizer que realmente não precisamos bloqueá-lo na coluna A. Vou me livrar desse sinal $. Em outras palavras, sempre será a coluna à esquerda de nós, então essa será uma referência mista, mas sempre temos que apontar para $ 1. Vamos editar esta regra, clique em OK. Tudo bem agora, com aquela mudança quando copiamos à direita e colocamos novos dados, como a data de hoje, funciona. Ok, isso é ótimo. A vida será ótima em 26/06 e a vida será ótima em 27/06. Tudo bem, funcionando muito bem. Mas agora encontramos o problema de ficar sem espaço na página e, portanto, o que Anderson tem feito é diminuir,essencialmente começa uma nova linha e cola e isso seria 28/06, mas não está ficando verde.
Por que não está ficando verde? Não está ficando verde porque eu ainda tive que usar $ para voltar para o 1. Tudo bem, então aqui está o enigma, aqui está o problema. O que você faz agora? E estou falando sério, o que você faz agora? Quero ouvir nos comentários do YouTube o que você faria agora.
Você sabe, então olha, há um argumento de que isso é bom, poderíamos parar por aqui porque usando o A $ 1, fizemos dessa forma, a vida é fácil no dia 1, copie para o dia 2, a vida é ótima . Dia 3 a vida é ótima. É apenas a cada 4 dias quando copiamos aqui que Anderson teria que entrar e configurar a formatação condicional, editar este, editar a regra, alterar 1 para 18. Clique em OK, editar esta regra e alterar 1 para 18. Clique em OK e em OK. Tudo bem, então no dia 4, aquele pequeno ajuste copie para o dia 5, copie para o dia 6 e depois copie para o dia 7. Faça essas etapas novamente. Mas, vamos enfrentá-lo. Esta planilha foi configurada há seis meses com essas regras de formatação condicional e elas só precisam funcionar. Não precisamos fazer a formatação condicional repetidamente.
Minha primeira reação foi fingir que esta é uma planilha onde tenho algumas fórmulas aqui e essas fórmulas foram construídas com referências absolutas, mas eu preciso que essas fórmulas possam ser copiadas acima ou abaixo, e sejam relativas dentro da cópia - tanto quando copio para aqui como quando copio para aqui. Tudo bem, e para fazer isso funcionar, vou usar referências absolutas quando configurar as coisas, mas então vou usar Localizar e Substituir, Ctrl H. E digamos que vamos nos livrar dessas referências relativas, mude a cada $ A $ 1 para A1, Substitua tudo, clique em Fechar e agora neste bloco, todas essas fórmulas são diferentes em todo o caminho, copie, cole e cole e funcionará. Vai ser relativo. Então eu disse, tudo bem, isso é o que precisamos fazer. Precisamos tirar esses $ da fórmula.E então eu iria escrever uma macro que me permitiria editar cada uma dessas regras de formatação condicional. Tudo bem, e antes de escrever essa macro, eu ia gravar a macro de alteração de uma regra de formatação condicional, mas não é que haja 14 regras de formatação condicional aqui. Não é nem mesmo para as 14 * 3, 42 regras de formatação condicional aqui. Existem apenas 3 regras de formatação condicional aqui e estamos aplicando essas 3 regras de formatação condicional a um intervalo de células.Há apenas 3 regras de formatação condicional aqui e estamos aplicando essas 3 regras de formatação condicional a um intervalo de células.Há apenas 3 regras de formatação condicional aqui e estamos aplicando essas 3 regras de formatação condicional a um intervalo de células.
Então, se eu mudasse isso, a primeira coisa que eu teria que fazer é pegar essas 3 regras de formatação condicional e torná-las 42 regras de formatação condicional. E então, estou começando a me encolher porque conforme Anderson copia daqui para cá, ele vai introduzir 42 novas regras e depois 42 novas regras. E ao longo de uma folha de papel com provavelmente 15 dias, ele vai introduzir mais de 600 regras, 600 formatos diferentes e isso vai ser horrível. Eventualmente, você vai chegar a muitas regras de formatação, sem mencionar que vai ser difícil de configurar, mesmo se tivermos uma macro para configurá-la. Vai ser difícil configurar.
Tudo bem, então o que vamos fazer? Aqui está o que eu inventei e quero saber se você tem algo melhor do que isso. Eu disse a Anderson, eu disse: “Sabe, veja, é bem simples. Todos esses estão olhando para um cálculo e esse cálculo é = TODAY- a data que está à minha esquerda. ” E não seria legal se pudéssemos ter essa resposta em uma pequena coluna de ajuda aqui à direita. E, na verdade, não precisamos usar nenhum $, apenas colocaremos todas essas células no mínimo com aquela pequena fórmula simples.
Eu posso ver a cara do Anderson, ele não quer que aquela coisa extra apagada mas tudo bem. Podemos ocultar, ocultar isso mais tarde para que possamos voltar a essas células e entrar em nossa formatação condicional. Todo aquele TODAY-A1 vai simplesmente apontar para C3 e isso vai ser uma referência relativa. Em outras palavras, seja qual for a célula em que estivermos, sempre olharemos na célula à direita, clique em OK, escreva nesta, clique em OK. Queremos ocultar esses dados aqui, então vou entrar e CTRL 1. Vou usar os três pontos-e-vírgulas - ;;;, clique em OK. Vou fazer exatamente a mesma coisa lá. Vou pressionar F4, repetir a última ação.
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.
Ok, então aí estamos. Acelere, você pode assistir no final para ver como funciona. Só estou fazendo um teste aqui. CTRL; mudará para azul. Se voltar para 26/06, ele mudará para vermelho. E se for hoje, não está funcionando. Isso mesmo, porque aqui está o que vou fazer. Minha quarta regra, o verde chegou hoje ou ontem, vou apenas usar isso como padrão. Se nenhuma dessas outras três regras for verdadeira, então será verde que me dê uma regra a menos com a qual tenho que lidar aqui. Tudo bem.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2105.xlsx
