Como classificar uma tabela sem usar macros do Excel

Imagem
Uma facilidade interessante do Excel é a classificação de tabelas. O Excel permite que tabelas sejam classificadas com várias colunas. Basta marcar a faixa de dados a ser classificada (todas as colunas e todas as linhas) e clicar no ícone CLASSIFICAR na aba Dados. Escolhe-se quais colunas serão utilizadas para classificar e em que ordem (ascendente ou decrescente). Pronto.Agora suponha que queiramos classificar uma tabela usando como argumento uma coluna calculada. Suponha que a cada novo cálculo, queiramos reclassificar a tabela. Fica chato repetir todo o processo acima manualmente a cada nova entrada de dados na tabela. Uma macro pode resolver isso, automatizando o processo. Mas dá para fazer sem macro ? Sim !!

Para classificar uma tabela automaticamente, tudo que temos que fazer é usar a função ORDEM() do Excel.

Essa função recebe os seguintes argumentos
=ordem( número ; coluna ; ascendente_ou_descendente )

número é o valor, dentro da coluna pela qual iremos classificar.

coluna é a faixa (range) de células que contém os valores.

ascendente_ou_descendente é um valor lógico ( falso ou verdadeiro, 0 ou 1) que indica se vai ser em ordem ascendente (1 ou verdadeiro) ou descendente (0 ou falso).

A função vai retornar a “classificação” do número na tabela. Vamos dar um exemplo.

Suponha a seguinte tabela de notas num campeonato de surf.
A…………………… B
Surfista……………. Nota
Joca……………….. 7
Badu……………….. 6
Manso……………… 8
Panela……………… 9
Redondo…………… 4
Peru……………….. 3
Cacetão……………. 5

Para obter a classificação de Joca, a fórmula seria
=ordem( b2 ; $b$2:$b$8; 0 )
No caso, vai dar 3, ou seja, Joca foi o 3º colocado na competição.
Se colocarmos essa fórmula em C2 e copiarmos para c3:c8 vamos ter a classificação de cada competidor.

Legal ! temos a classificação de cada competidor mas eles ainda estão listados fora de ordem. Como fazer para colocá-los do primeiro para o último colocado com o primeiro no topo ?

Para fazer isso, temos que exibir os competidores em outro local na ordem desejada. Nenhuma fórmula do excel é capaz de mudar o conteúdo de posição. Só macros fazem isso. O que podemos fazer é escolher quais entre os valores possíveis será o exibido numa célula. Isso significa que não dá para exibir a tabela de concorrentes e depois mudar a ordem nela mesma só com fórmulas. Vamos exibir em outro local então.

Sendo assim, imagine que em outro lugar da planilha, vamos colocar a “Classificação” do campeonato. Que tal a partir de F2 ?? De F2 até F8, na ordem de primeiro para último (ou seja, descendente de notas obtidas). Aí usamos a função CORRESP() e índice()
Em F2 vamos colocar 1, em f3 2, f4 3, f5 4 e assim por diante até f8 ter 7, que é a última colocação nesse campeonato.

Qual o concorrente que ficou em 1º lugar ?? Ora, é só buscar na tabela de classificação o 1. Para saber em que célula da lista de concorrentes está o que ficou em 1º lugar, basta localizar na coluna C2:C8 onde está o 1. Isso a gente faz com Corresp() assim e vamos colocar em G2

=corresp( f2 ; $C$2:$c$8 ; 0 )

Como funciona função corresp() ? Ela funciona de forma muito semelhante a função procv() só que ela só retorna o número da linha que correspondeu ao código procurado. No caso o que está em F2 que é a posição no ranking. Nesse exemplo, vai retornar 4. Ou seja, o concorrente que obteve a pontuação máxima está na linha 4 da tabela. Copiamos então essa fórmula para G3:G8 e teremos as posições do primeiro até o último colocado. Mas não queremos a posição !! Queremos o nome do cara ! Aí que entra a função ÍNDICE() que nada mais faz do que pegar o n-ésimo elemento de uma matriz.

EM h2 então colocamos a seguinte fórmula

=índice( $a$2:$a$8 ; G2 )
Copiamos essa fórmula para h3:h8 e teremos os nomes dos concorrentes.

QUer a nota ao lado ? É só usar a função índice() novamente só que com a coluna das notas B2:B8.

Aqui tem uma planilha com as fórmulas já prontas para vocês experimentarem

 
Anúncios

Como controlar estoque no Excel ?

ImagemControle de estoque no Excel é uma necessidade muito comum. Todos os dias aparece alguém querendo registrar o que entrou e o que saiu de peças no estoque e quanto que sobrou. O problema é que o número de transações pode variar muito. Pode ser só uma entrada, várias saídas, pode ser só saída, sem entrada, pode só ter entrada e pode não acontecer nada e o estoque ficar inalterado.

O ideal aí é ter dois dados e tentar relacioná-los. O primeiro dado é a relação de produtos e o estoque atual de cada um deles. A melhor estrutura aí é uma simples tabela, cada código, descrição e o estoque atual.

O outro dado é a relação de movimentações: Entradas e saídas de estoque. Nessa relação, a data da transação (quando aconteceu), o código do produto movimentado, e a quantidade entrada ou quantidade saída.

Voltamos a tabela original de produtos e nela vamos somar todas as entradas, todas as saídas. Agora calcular o estoque corrente de cada produto é simples. O estoque inicial, mais as entradas, menos as saídas = estoque atual.

Fiz uma planilha aqui que implementa essa estrutura

Deem uma olhada e vejam o que acham dela.

Controle de cartões de crédito/débito em Excel

Controle de cartões de crédito e débito numa loja. Acontece o tempo todo e é uma necessidade super frequente. Como fazer para controlar o que tem a receber baseado no registro das vendas ? Essa é a pergunta mais completa. Imagine a seguinte situação. Uma pequena loja vende vários produtos com diversos preços diferentes e com modalidades de pagamento diferentes. Vende a vista, em dinheiro, cheques para o dia, cheques pré-datados, duplicatas bancárias, cartões de crédito, cartões de débito, cartões de crédito com parcelamento pela própria loja e com parcelamento pela administradora do cartão. Várias bandeiras de cartão e as vezes até a mesma bandeira em máquinas de POS diferentes.

Cada uma dessas modalidades de pagamento tem um conjunto de características determinas e diversas entre si, tais como :
1 – Prazo para disponibilização do dinheiro. A venda a dinheiro a disponibilização é imediata. O cheque para o dia na verdade levará um ou dois dias para ter o dinheiro disponível. O cheque para 30 dias, o cartão de crédito tem o seu prazo que varia de acordo com a bandeira e as vezes na operadora do POS (Redecard, Cielo, etc). O cartão de débito tem outro prazo (normalmente o dia seguinte) e por aí via.

2 – Taxa de desconto. É um valor ou percentual que é “subtraido” do valor nominal da venda. No exemplo da venda a dinheiro, que não desconta, o cheque pré-datado que será trocado na factoring sofrerá um desconto, o cartão de crédito que cobrar um percentual (taxa de administração) ou o de débito que cobra um valor por transação.

3 – A bandeira/marca da modalidade. Visa, Mastercard, Maestro, VisaEletron, cheque pré, dinheiro, cheque a vista, duplicata. Serve para identificar o tipo de pagamento da transação.

Para atender essa demanda fiz uma planilha em que se registra as vendas, a data, o valor da venda e o tipo de pagamento e uma macro elabora o fluxo de caixa, ou seja, a previsão de recebimentos associados as vendas. A ideia é demonstrar quanto dinheiro irá entrar ao longo dos próximos dias no futuro. A planilha é bem simples. Basta lançar as vendas e depois rodar a macro que fará as contas e os lançamentos. Essa planilha tem alguns itens que precisam ser melhorados e a ideia de postá-la aqui é justamente expô-la a comentários e sugestões. Ao mesmo tempo, vou dar esclarecimentos sobre as dúvidas que aparecem para quem tentar usá-la.

Como funciona a planilha

A planilha tem várias abas. Como dito, lança-se apenas na aba de OPERAÇÔES, que seriam as vendas. Com alguma imaginação é possível lançar compras também. Deixo o desafio aqui.

Na aba de operações, a planilha tem as seguintes colunas
Número da venda -> serve para identificar a transação. pode ser o número da nota fiscal, do cupom fiscal ou do pedido. É preciso ter um número para poder identificar o recebimento e associá-lo a venda.
Bandeira -> é a o tipo de cartão de crédito que será utilizado. Esse tipo é pré-cadastrado na aba de cartões que veremos adiante. Para cada tipo de pagamento, se cria uma bandeira. Pode-se criar quantas bandeiras quiser. As variantes de bandeira associada a operadoras implicam em criação de uma bandeira. Por exemplo, Visa quando recebido pela redecard tem uma taxa de 4%. Já quando Visa é recebido pela Cielo tem taxa de 3,8%, por exemplo. Se isso acontecer, é preciso criar duas “bandeiras” tais como Visa Rede e Visa Cielo.
Data -> é a data em que a venda aconteceu e a partir da qual será calculado o prazo (ou prazos) para recebimento.
Valor -> é fácil de .. alguém advinha o que significa ?
Taxa -> é a taxa de “desconto” que será aplicada a venda. Essa coluna é preenchida automaticamente pela fórmula da planilha. Não a modifique-a
Prazo -> é o prazo previsto para recebimento do valor da venda. Também é calculado automaticamente
Número de parcelas -> é informado para cada venda. Vendas a vista ou no cartão de débito/crédito simples tem uma parcela só.

A aba de cartões contém os parâmetros que serão usados para o preenchimento automático das colunas TAXA e PRAZO da aba anterior. Essa tabela só precisa ser informada uma vez no início do uso. Informe todas as modalidades de pagamento que você tiver. Use a imaginação. Nessa tabela você terá as seguintes colunas :

Bandeira -> é o “código” do tipo de pagamento que o identifica. Não pode ter dois iguais
Taxa -> é o % de desconto aplicado sobre as vendas. Use a imaginação aqui quando for para pagamentos. desafio
Prazo -> é o prazo padrão para recebimento associado as vendas daquela modalidade , em dias

As planilhas de FLUXO e de Resumo são calculadas automaticamente pela macro acionada pelo botão “gera fluxo” na aba operações. Você lança os dados em operações, aperta o botão e pronto, as abas fluxo e resumo são criadas.

Na aba de fluxo vai aparecer os valores a receber em ordem cronológica, mencionando a venda original. Observe que se uma venda foi parcelada em 3 vezes, ela aparecerá 3 vezes, em datas diferentes, conforme os parâmetros de prazo da aba de cartões.

A aba de resumo é uma tabela dinãmica que usa os dados de fluxo. nela você pode fazer sumários por tipo de cartão, por prazo, por data simplesmente arrastando os campos conforme sua necessidade.

Como levar dados selecionados de uma planilha para outra

Uma das técnicas mais úteis do Excel são os autofiltros. Nesse vídeo abaixo … tem uma dica de como funcionam os autofiltros. A vantagem dessa técnica é que é fácil e rápido de usar. Basta marcar a base de dados e selecionar o filtro desejado. Em conjunto com a função subtotal() pode-se facilmente obter somas, contagens, maiores e menores valores apenas dos registros que atenderam aquele filtro.

Apesar de muito útil, prática e rápida, essa técnica tem algumas desvantagens. Se for necessário usar o valor de uma célula para filtrar os dados, essa técnica já não funciona. Outra desvantagem é que a manipulação de múltiplos filtros ao mesmo tempo pode confundir o usuário e dar resultados inesperados.

Existe uma alternativa a essa técnica simples. Essa alternativa requer um pouco mais de fórmulas mas pode ser útil em alguns casos. Para ilustrar essa técnica, eu fiz uma planilha que pode ser baixada aqui

Essa pasta é simples. Na plan2 existe uma relação de vendas feitas por cliente relacionando os produtos, preços, quantidade, etc. Os dados são inseridos nas colunas de B a N na plan2, registrando as vendas.

O que se quer é … ao digitar na plan1 o nome de um cliente na célula E4 e o código do produto em E5, listar as vendas realizadas desse cliente+produto. Ou seja, vamos usar um filtro com dois critérios, o cliente e o produto. A relação aparecerá na plan1 mesmo, mantendo plan2 intacta.

Como fazer ?

Criamos em plan2 3 colunas auxiliares que serão calculadas automaticamente. A primeira coluna é a coluna O que indicará se o cliente daquela linha é o mesmo cliente colocado em E4 (da plan1). A coluna P indicará se o produto daquela linha é o mesmo produto colocado em E5 (da plan1). Essas duas colunas terão apenas os valores 1 caso seja verdadeiro e 0 caso seja falso. Ou seja, a coluna O terá um 1 para cada linha que tiver o mesmo cliente que desejamos. A coluna P terá um 1 em cada linha que tiver o produto que desejamos.

A coluna Q irá fazer a combinação dos dois critérios, ou seja, ela só terá 1 se O e P tiverem ambas 1. Isso é feito com uma simples multiplicação O8 * P8 só dará 1 se ambos forem 1. Ou seja, só dará 1 se o cliente for o desejado e o produto também. Além de calcularmos se a combinação cliente * produto é a desejada somamos esse resultado ao resultado da linha anterior. No caso da primeira linha (a linha 8) não temos linha anterior. A soma com a linha anterior só acontece a partir da linha 9 em diante.

Esse truque de somar a linha anterior serve para marcar quando acharmos mais um novo cliente * produto que atende ao filtro especificado em E4 e E5. A coluna Q então terá uma “contagem” que muda de número apenas quando encontra uma nova linha que atendeu ao critério.

Muito bem, plan2 agora tem as 3 colunas auxiliares que fizeram o filtro. Toda vez que o valor de Q mudar é porque naquela linha tem um novo cliente * produto que atende ao critério. O que temos que fazer é levar essas linhas, e apenas essas linhas, para plan1. Como fazer isso ?

Em plan1 a primeira coluna contém uma simples relação crescente de números. Para cada linha dessa, vamos procurar na lista de plan2 qual o ítem que corresponde a ela na coluna Q. Ou seja, em que linha da faixa que começa em Q8 aparece o número que está na coluna A ? Ou … perguntando pelo significado, em que linha da coluna Q da plan2 aparece o primeiro cliente * produto que atende o critério desejado ? A coluna B de plan1 contém essa resposta a essa pergunta. Essa resposta é obtida com a função corresp() assim

=CORRESP(A27;Plan2!$Q$8:$Q$17;0)

A27 é o número crescente do itens que atendem ao critério, 1 o primeiro ítem, 2 o segundo ítem e assim por diante, entre A27 e A36

Plan2!$Q$8:$Q$17 é a coluna de plan2, calculada, que nos informa a cada novo número que achamos um cliente * produto desejado

o 0 (zero) usado na corresp() é para garantir que a busca que corresp() irá realizar é “exata”, ou seja, queremos exatamente o primeiro, depois o segundo, depois o terceiro, etc. Quando o item não é encontrado, corresp() retorna #N/D. No exemplo, não existe 3º item que atenda ao critério Mateus e Painço.

Na coluna B de Plan1 teremos então a lista de números de linha de Q8:Q17 da plan2 onde tem os itens desejados. Agora é só trazer os dados da plan2 para plan1. Para fazer isso, usamos a função INDICE() que nada mais faz do que pegar o elemento desejado de uma matriz. INDICE() funciona assim

indice( matriz original ; número da linha ; número da coluna )

índice() retorna o elemento da matriz original que estão na linha x coluna informada.

Queremos o peso bruto, a tara , o liquido bruto, o %l/kg, o %u/kg, e o total liquido. Essas colunas em plan2 são E, F, G, H, I e J respectivamente.

O número da linha foi calculado e está em B, é só aplicar então
=ÍNDICE(Plan2!E$8:E$17;B27;1) para obtermos o peso bruto, ou seja, queremos o elemento cujo número está em b27 da lista de pesos brutos que está na plan2 entre E8:E17

para não encher a planilha com erros #N/D fazemos um pequeno SE() antes assim

=SE(É.NÃO.DISP($B27);””;ÍNDICE(Plan2!E$8:E$17;Plan1!$B27;1))

Ou seja, se b27 for #N/D exibimos um nada “”. Se for um número, exibimos o valor buscado na matriz original.

Nesse exemplo, usamos 2 critérios. Através de adição de mais colunas auxiliares em plan2 a partir da coluna P podemos colocar quantos critérios quisermos. Podemos usar um critério só. É só modificar a coluna Q para se adequar a fórmula.

Como calcular frete no Excel … ou .. com usar várias variáveis para uma busca.

Encontrei essa pergunta desafiante abaixo

Preciso aplicar uma formula de cálculo de frete no excel?
Preciso de ajuda para fazer uma planilha no excel para calcular o frete peso por faixas de Km. Estou usando F =(a+b. X+DI). (1+L)/100, alguém pode me ajudar? Referências:http://www.guiadotrc.com.br/pdfiles/MANUAL.pdf 
http://logisticabr.blog.terra.com.br/2012/07/

A pergunta é de resposta complexa e o link dado dá uma resposta errada mas dá a dica de quais variáveis entram no cálculo do custo do frete.

segundo a fórmula que você colocou, ela tem várias variáves. 

(a+b. X+DI).(1+L)/100

presumo que o ponto signifique multiplicação.

Essa fórmula aparentemente tem 5 variáveis
o que cada uma delas significa ? Bom, cabe a você nos dizer. De qualquer forma, para cada variável, no Excel você vai associar uma célula

= ( ( a1 + a2) * (b1+b2) ) * ( 1 + b3) / 100

Aí você coloca em a1 o valor que cabe a a, em a2 o valor que cabe a b, em b1 o valor que cabe a X e assim por diante.

Segundo o site que você indicou, a fórmula é 
“Onde F é o frete-peso em unidades monetárias, X é a distância da viagem, L é o lucro operacional, a é o custo do tempo de espera durante a carga e a descarga, b é o custo de transferência (em unidades monetárias por tonelada quilômetro), e DI são as despesas indiretas.”

A fórmula está incompleta, por incrível que pareça. mas a descrição dá as dicas. Ele afirma que o frete é calculado levando em consideração :
1 – O tempo de espera durante a carga e a descarga
2 – O custo de transferência por “tonelada quilômetro”, ou seja, quando mais pesada e mais longe for, mais caro.
3 – As despesas indiretas … presumo que sejam coisas como pedágios, taxas e balsas
4 – L é o lucro operacional desejado.

Como na prática se calcula o frete ? Vamos adotar um modelo simples em que apenas o peso e a distância sejam considerados e com apenas um tipo de veículo de carga. Sendo assim alguém diz quanto peso será transportado e qual a distância a ser percorrida. Adiciona a também os pedágios e balsas. A empresa espera ter um certo percentual de lucro pré-definido. Além disso, é informado o tempo de carga e descarga.

Precisamos de um índice que é o custo tonelada.quilômetro do veículo. Especulo que os fabricantes de veículos de carga forneçam essa informação.

O custo da hora parada é o custo da mão-de-obra do motorista (e da equipe que o auxilia se hovuer) + o custo de depreciação do veículo.

Para calcular o custo de depreciação do veículo, pegue o valor do véiculo, some a manutenção dele durante um ano, some o ipva+dpvat e o seguro do veículone divida pelo total de horas que o veículo vai trabalhar durante o ano. Esse será o custo de depreciação do veículo.

Agora vamos calcular o frete

F = ( ( custo hora parada * horas_carga_descarga + pedágios ) + ( custo tonelada.quilometro * tonelada * distância) ) / ( 1 – Lucro ) 

Tem muita variável nessa fórmula mas algumas são parâmetros, como :
custo hora parada 
Custo tonelada.quilometro
Lucro operacional desejado

Coloque os parâmetros em uma faixa a parte, nas células L1, L2, L3

As variáveis que vão mudar de um serviço para outro são:
horas_carga_descarga (em horas decimais)
pedágios (em reais)
tonelada (peso em toneladas)
distãncia (em km)

Vamos colocar em colunas assim a partir da linha 10, 
C10 a horas_carga_descarga
d10 os pedágios e taxas
e10 quantas toneladas
f10 quantos km 

A fórmula para calcula esse frete seria em g10 assim 

= ( ( $L$1 * c10 + d10 ) + ( $L$2 * e10 * f10 ) ) ) / ( 1 – $L$3) 

Esse valor proporcionará o lucro operacional desejado, ou .. margem de contribuição, como se diz., Sobre esse valor ainda incidirão os impostos sobre faturamento. ICMS, PIS, COFINS, CSSL. Some os impostos (pergunte ao contador) e divida o preço líquido por ( 1 – total de impostos) e terá o preço a ser faturado.

Nesse método simples estamos usando apenas o fator peso e distância para calcular o frete. Em algumas situações o volume da carga é mais relevante. Certa vez vi uma empresa que vendia blocos de isopor, ou seja, algo super leve porém que ocupava um volume enorme. Nesse caso não vale a pena calcular o frete baseado no peso * quilômetro mas sim no volume * quilometro. O custo do km rodado será diferente. Numa situação mais complexa o peso, o volume e a distância serão consideradas para cálculo.

Como acionar uma macro quando uma célula é modificada ?

Aqui temos um exemplo de macro que chamada quando uma célula é modificada.

http://www.bokomoko.com.br/index.php?op … info&id=29

Observe que a macro está na aba de exibir código da planilha onde ela irá funcionar. Para acessar a macro clique com o botão direito sobre a aba com o nome da planilha, vá em exibir código e encontrará a sub worksheet_change()

Essa sub é chamada toda vez que uma célula ou faixa de células é modificada manualmente ! Ela não é chamada se uma fórmula modifica o valor.

O parâmetro passado para essa rotina é a faixa de dados que foi modificada. De posse dessa info é possível testar qual parte da planilha foi modificada agir de acordo. Algumas partes da planilha poderão ser monitoradas, ou seja, quando forem modificadas dispara uma ação. Outras partes podem não precisar de monitoração e sua modificação será ignorada.

Como registrar a data e a hora em que uma célula foi alterada

Uma pergunta muito comum é como registrar numa célula a data+hora em que outra célula foi alterada. A primeira solução que vem a mente é usar a função agora(). Acontece que essa função é reavaliada toda vez que QUALQUER célula for modificada posteriormente. O que precisamos é registrar a data+hora em que a célula foi alterada e nunca mais alterar essa data+hora. Para fazer isso vamos usar uma macro.

Eis a macro que faz o que queremos numa versão bem simples que pode ser baixada aqui.

Private Sub Worksheet_Change(ByVal Alvo As Range)
Dim limite_maximo As Integer
limite_maximo = 100 ‘ altere aqui para limitar a última linha
‘ faz nada se mais de uma célula modificada ou se deu delete
If Alvo.Cells.Count > 1 Or IsEmpty(Alvo) Then Exit Sub

If Alvo.Column = 1 And Alvo.Row >= 2 And Alvo.Row <= limite_maximo Then
‘ o if acima garante que a célula modificada está dentro a2:a100

‘ desliga captura do evento change
Application.EnableEvents = False
‘ muda a célula C da linha correspondente
Alvo.Offset(0, 2).Value = Time()
‘ religa a captura de eventos
Application.EnableEvents = True
End If

End Sub

Como funciona essa macro ?

Essa macro tem um nome especial chamada worksheet_change()
Ela é acionada sempre que qualquer célula for modificada manualmente. Ou seja, quando alguém modificar uma célula e teclar ENTER, essa macro será acionada. Ela não será acionada quando copiar/colar, ou quando  um cálculo mudar o valor de uma célula ou quando outra macro mudar o valor da célula. Ela só é acionada se a modificação for feita manualmente.

Além do nome especial, essa macro também é gravada num local especial. Clique-direito sobre o nome da aba que contém a planilha e acione a opção “Exibir código”. Nesse espaço cole a macro acima. Se colocar em outro lugar, a macro não será acionada automaticamente e não vai funcionar.

A sub worksheet_change() recebe como parâmetro a faixa que foi modificada. Observe que a sub recebe uma faixa e não uma simples célula. Isso significa que é possível em alguns casos editar mais de uma célula ao mesmo tempo.

Resumindo até aqui, a sub worksheet_change é acionada quando alguém edita uma célula (ou faixa de células), tem que ser gravada na parte “exibir código” da planilha e passa como parâmetro a faixa que foi modificada.

Agora vamos ao que a macro faz.

A primeira coisa que a macro faz é criar uma variável que definirá quais células serão monitoradas. Esse limite foi definido simplesmente pelo número da linha e pelo número da coluna. No nosso caso, o limite de número de linha será 100. Isso significa que qualquer célula de linha <= (menor ou igual) a 100 será monitorada. Acima desse limite a macro não fará coisa alguma. Além desse limite superior de linha, existe um limite inferior de linha que está fixo em 2 no 2º IF.

Depois de definida a variável que conterá o limite superior, a próxima coisa que a macro faz é testar quantas células foram modificadas. Com a finalidade de simplificar a macro, testamos se mais de uma célula foi modificada contando quantos elementos tem a faixa passada como parâmetro. Se a faixa modificada tiver mais de uma célula ou se for vazia, saímos da macro sem fazer coisa alguma.

Agora vamos testar se a faixa modificada está dentro do limite escolhido. No caso vamos monitorar apenas o que for modificado na coluna A (coluna 1) a partir da 2ª linha até a linha definida pelo limite superior mencionado antes. Fazemos isso usando um IF em que testamos se a coluna (alvo.column) é 1 ( equivalente a coluna A) e se o número da linha (alvo.row) está dentro da faixa entre 2 e o limite. Se estiver, a brincadeira começa. Primeiro desligamos a captura de novas mudanças na planilha, para evitar que a sub worksheet_change() seja chamada enquanto ainda ela mesma ainda está sendo processada. Essa é uma medida de segurança e é efetivada com o comando application.enablevents = false que desliga a captura de eventos. agora vamos registrar na célula ao lado da célula modificada a data e hora. Isso é feito usando a propriedade OFFSET( 0 , 2 ) da célula modificada, que irá deslocar 0 linhas e 2 colunas para a direita, colocando nessa nova célula o valor da data e hora obtidos com a função vba TIME()

Agora é só religar a captura dos eventos e terminar a rotina.

Uma forma mais clara de detectar se a célula em que houve a mudança está na faixa que devemos monitorar é usar a função vba Intersect() assim

sub worksheet_change( byval alvo as range )
dim a_monitorar as range
set a_monitorar = range( “B2:B10” ) ‘ definimos aqui a faixa que queremos monitorar
if Intersect( alvo , a_monitorar ) Is Nothing Then exit sub ‘ se a interseção das faixas for nada, então está fora da faixa a monitorar, saímos da rotina

‘ colocamos aqui o código que queremos fazer
exit
end

Essa técnica de interceptação do evento de modificação da planilha pode ser usado para disparar macros que fazem outras coisas. Por exemplo, pode-se usar esse evento para chamar uma macro que coloca em ordem alfabética uma lista de pessoas numa faixa. Ou … atualizar um gráfico … ou levar os dados de uma parte da planilha para outra. Use a imaginação.