Controle as alterações nas células de fórmula do Excel. Você pode mostrar quais itens mudaram apenas como resultado da mudança de certas células de entrada?
Assistir vídeo
- Rastrear alterações no Excel é um pouco bizarro.
- O objetivo é rastrear quais células de fórmula no Excel mudam.
- Salvar como para salvar a pasta de trabalho como XLSM.
- Alterar a segurança da macro.
- Grave uma macro para descobrir o código para configurar a formatação condicional para números diferentes de 2.
- Escolha a formatação desejada.
- Grave outra macro para aprender como remover CF da planilha.
- Na macro, adicione um loop para cada planilha.
- Adicione uma instrução IF para evitar que seja executado no Título.
- Adicione um loop para verificar cada célula da fórmula.
- Adicione Formatação Condicional para ver se o valor da célula no momento da execução da macro.
- Volte para o Excel.
- Adicione uma forma. Atribua a macro à forma.
- Clique na forma para executar a macro.
- Dica bônus: arrastando um módulo VBA para uma nova pasta de trabalho.
Transcrição de vídeo
Learn Excel from Podcast, Episode 2059: Excel Track Changes (in Formula results)
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Pergunta de hoje enviada de Montreal sobre mudanças de pista. Acompanhe as mudanças, certo. Então aqui está o que temos. Temos 4 células de entrada e um monte de células de fórmula que dependem dessas células de entrada. E se eu ligasse, irei voltar para a guia Review, ativar Realçar alterações, controlar as alterações durante a edição, clicar em OK, certo. E eles me avisaram que precisam salvar a pasta de trabalho e que as macros não podem ser usadas em pastas de trabalho compartilhadas. Você sabe disso? Este é o problema quando você rastreia mudanças, elas compartilham a pasta de trabalho e há um monte de coisas que não podem acontecer em pastas de trabalho compartilhadas, você sabe, como macros e um monte de outras coisas. Mas vamos apenas dar uma olhada em como o controle de alterações funciona no Excel hoje.
Vamos pegar este 2 e mudar de 2 para 22, e pegar esse 4 e mudar de 4 para 44. Tudo bem, e você vê, o que eles notaram nas mudanças de trilha é que essas duas células mudaram, tudo bem, aqueles triângulos roxos estão a trilha real muda. Todas essas coisas vermelhas, isso não acontece, mas eu apenas ilustrei que todas essas células vermelhas estão mudando e as mudanças de trilha não dizem nada sobre essas mudanças, certo? Então, apenas diz, essas duas células foram alteradas, mas todas as outras células também foram alteradas. E então a pergunta de Montreal é: há uma maneira de as mudanças de faixa realmente nos mostrarem tudo que mudou, não apenas essas células de entrada mudaram?
Tudo bem, então, a primeira coisa que temos que fazer é desligar o controle de alterações embutido no Excel. E então, há uma maneira de conseguirmos - podemos construir nosso próprio sistema de mudanças de rastreamento que nos permitirá ver todas as células da fórmula que mudaram? Tudo bem, então a Etapa 1 e esta etapa são a etapa mais importante, não pule esta. Olhe para o seu arquivo, seu arquivo é denominado algo XLSX, você tem que salvar isto: Arquivo, Salvar como, Como uma pasta de trabalho habilitada para macro ou nada disso funcionará. Você tem que clicar com o botão direito do mouse, Personalizar a Faixa de Opções, ativar o Desenvolvedor, assim que chegar ao Desenvolvedor, vá para Segurança de Macro, mude esta configuração - aquela que diz que não vamos deixar as macros rodarem ou nem mesmo contar você que eles estão lá para este cenário. Você tem que seguir essas duas etapas. Já fiz essas duas etapas. Vivo todos os dias com essas duas etapas.Já corrigido, mas se você é novo em macros, isso é novo para você. E então, precisamos descobrir que tipo de formatação você deseja. Tudo bem, então vou apenas escolher algumas células aqui, vou gravar uma macro que é chamada HowToCFRed, não vou atribuir a uma tecla de atalho porque isso nunca vai ser executado novamente. Estou apenas gravando o código para descobrir como funciona a formatação condicional. E entraremos em Início, Formatação condicional, Células de destaque que não são iguais a - Então, mais regras, células de formato não iguais a - Vê isso? Não está no menu suspenso original, mas se você entrar aqui, não é igual a 2, e escolher o formato. Essa é a parte importante. Vou escolher um fundo vermelho. Você escolhe a cor que quiser aqui, certo? Vá até Mais cores, escolha outro vermelho,vá em Custom, escolha algum outro vermelho, certo? Essa é a beleza do gravador de macro, eles vão nos dar um vermelho perfeito ou azul ou o que você quiser. Tudo bem, clique em OK. E então, vamos parar de gravar, certo. Novamente, o objetivo disso é apenas ver qual é o código dos formatos condicionais.
Vou para Macros, Como formatar condicional vermelho e editar. Tudo bem, então aqui estão as partes importantes deste código. Eu posso ver que eles estão adicionando um formato condicional usando o xlNotEqual e é difícil citá-lo para não ser igual a 2. E então estamos mudando o interior da célula para essa cor.
Tudo bem, eu também preciso descobrir como excluir toda a formatação condicional da planilha. Então, de volta ao Excel, Grave outra Macro, Como deletar todas as condicionais, OK. Venha aqui para a guia Home, vá para Formatação condicional, Clear Rule de toda a planilha, Stop Recording e veremos esse código. Ótimo, é uma macro de uma linha. E eu até gosto aqui que a maneira que eles fazem para a planilha inteira é apenas se referindo às células. Em outras palavras, todas as células da planilha ativa.
Agora, eu preciso tornar essa macro, a macro gravada, um pouco mais genérica. E eu escrevi muitos livros sobre como fazer VBA no Excel e vídeos sobre como fazer VBA no Excel, e aqui está o simples: você precisa ser capaz de gravar uma macro como esta, mas então, adicione cerca de cinco ou seis linhas para poder tornar a macro suficientemente genérica.
E vou falar sobre essas linhas, certo. Portanto, a primeira coisa que quero fazer é dizer: quero examinar a pasta de trabalho ativa, examinar todas as planilhas. Então, para cada planilha, WS é a variável do objeto, vou passar por todas as planilhas. E a pessoa de Montreal disse: “Ei, há uma folha na qual eu não quero que isso aconteça”. Portanto, se o WS.Name, com o nome do ponto da planilha, não for igual ao Título, faremos o código na macro. Este é o nome da planilha: .Cells.FormatConditions.Delete. Então, vamos percorrer cada indivíduo da planilha, exceto o título e excluir todas as condições de formato, então vamos percorrer cada célula da planilha, mas não todas as células, apenas as células que têm fórmulas . Se não tiver uma fórmula, então eu nãoNão preciso formatá-lo porque não vai mudar. Cell.FormatConditions.Add, é diretamente da macro, embora a macro gravada diga Seleção - não quero ter de selecioná-la, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo que não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo que não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS. não vai mudar. Cell.FormatConditions.Add, vem diretamente da macro, embora a macro gravada diga Seleção - não quero ter de selecioná-la, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.não vai mudar. Cell.FormatConditions.Add, é diretamente da macro, embora a macro gravada diga Seleção - não quero ter de selecioná-la, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo que não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo que não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.isso vem diretamente da macro, embora a macro gravada diga Seleção - não quero ter de selecioná-la, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.isso vem diretamente da macro, embora a macro gravada diga Seleção - não quero ter de selecioná-la, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo que não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo que não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.Não quero ter que selecioná-lo, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.Não quero ter que selecioná-lo, então direi apenas Célula, que é cada célula individual. Vamos usar o xlNotEqual e em vez da Fórmula: = ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.= ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo que não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo que não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.= ”=” 2 que é o que o código gravado fez ali, concatenei o que quer que esteja naquela célula. Portanto, verifique se não é igual ao valor atual. Portanto, se a célula atualmente tem 2, estamos dizendo que não é igual a 2. Se a célula atualmente tem 16,5, estamos dizendo que não é igual a 16,5. E então o resto é apenas macro gravada diretamente, macro gravada, macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.macro gravada, macro gravada. Tudo isso é de uma macro gravada. Termine este If com um End If. Termine isso com um Próximo WS.
Tudo bem, então eu tenho uma macro chamada ApplyCF. Volte ao Excel e adicione uma forma. É fácil ter uma forma aqui: Insert, eu sempre escolho um retângulo arredondado, digite Reset To Current Values. Vamos aplicar Home, o centro e o centro para torná-lo um pouco maior. Eu amo o brilho. Suponho que você ache bobo ver que não está lá, o brilho, a configuração de que gosto não está lá, então sempre vou para Layout de página e efeitos e escolho o segundo. E então, quando volto ao formato, posso escolher um que realmente tenha um pouco de brilho. Pra mim eu acho legal, acho que vale a pena. Clique com o botão direito, Atribuir Macro e diga AplicarCF, clique em OK. Tudo bem, e então o que isso fará é quando eu clicar nele, ele percorrerá todas essas páginas, encontrará todas as células de fórmula e configurará uma formatação condicional que diz: Se essas células não forem iguais a 7,mude a cor, certo? É isso aí. É tão rápido, aconteceu tão rápido. BAM! Está feito. E agora, observe se eu mudar este para 11, todas aquelas células mudaram. Agora, se voltar para 1, ahh, as cores mudaram. Portanto, qualquer que seja o valor, quando mudamos - se eu mudar essa célula, todas essas células mudam. Se eu mudar essa célula, todas essas células mudam. Se eu mudar essa célula, todas essas células mudam.todas essas células mudam.todas essas células mudam.
Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.
Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.
Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.
Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.
Bem, ei, quero agradecer por você passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2059.xlsm
