Formatação condicional

Seguem algumas dicas de formatação condicional.

 

Há uma série de formas de pintar as células segundo regras de cores, ícones, etc.

Por exemplo, realçar quem tem valor igual a 1:

 

Página Inicial -> formatação condicional -> realçar regras das células -> É igual a

 

 

Resultado:

Para valores duplicados, selecionar o range desejado e ir em Página Inicial -> formatação condicional -> realçar regras das células -> valores duplicados.

Resultado:

 

E assim sucessivamente, há uma infinidade de regras à disposição.

 

A parte mais avançada e difícil é criar fórmulas personalizadas.
Primeiro, escolher o range onde a regra será aplicada.
=$C$6:$D$15

 

Coloquei a fórmula a seguir.
=SE($C6=”b”;1;0)
O que confunde muito é a forma de travar a célula:
C6: sem travamento
$C6 : trava a coluna C
C$6: trava a linha 6
$C$6: trava a linha e coluna
Para escolher a opção desejada, é só imaginar que é uma fórmula da planilha, que vai ser arrastada no range selecionado.

 

Segue exemplo aqui.

Anúncios

Segmentação de dados

Segmentação de dados é um truque interessante, para quem monta dashboards.

O primeiro passo é criar uma tabela dinâmica, ou gráfico dinâmico.

 

 

Depois, Inserir -> segmentação de dados.

 

 

E escolher o que vai aparecer no menu.

 

O resultado é um gráfico com um menu visual.
Vale somente do Excel 2007 em diante.

 

Download de exemplo aqui.

Multiplicador de shapes

Segue aqui uma rotina que copia e cola um shape segundo um padrão retangular.

O shape deve estar selecionado, ao rodar a macro.
Define-se o número de linhas e colunas, e o espaçamento entre shapes.

Deve-se agrupar shapes (selecionar shapes, botão direito, agrupar), quando há uma composição de shapes formando um desenho.

O VBA é basicamente uma automação do ctrl+c + ctrl +v:
.Copy
ActiveSheet.Paste
E depois um redirecionamento da posição do shape.
.Left = x0 + (i – 1) * space
.Top = y0 + (j – 1) * space
Uma aplicação possível?
Brincar de Escher, como no post a seguir.

https://ideiasesquecidas.com/2015/11/23/escher-e-aspirantes-a-escher/

 

Att

Mensagem na célula do Excel

Para mostrar a mensagem quando seleciona a célula, como a figura abaixo,

 

Ir em Dados -> Validação de Dados…

Na tela que aparecer, aba “Mensagem de entrada”, é só definir o título e a mensagem a ser mostrada.

 

Vide arquivo aqui.

 

Macro para verificar valores únicos e repetidos de uma lista

A macro (aqui) recebe uma lista e retorna duas: os valores únicos e os valores repetidos.

 

Existe uma forma eficiente de fazer isto, com o objeto dicionário do VBA.

 

Highlights:

‘Dicionário para a lista única
Dim dict As New Scripting.Dictionary

‘Armazena os itens repetidos
Dim dictRepet As New Scripting.Dictionary

 

 

O loop a seguir verifica se os valores são repetidos e adiciona.

A chave é a propriedade  dict.Exists(‘valor’)
‘Adiciona valores ao dicionario
For i = 1 To nl

If Not dict.Exists(listaOriginal(i, 1)) Then
‘Se nao existe, adiciona
dict.Add Key:=listaOriginal(i, 1), Item:=i
Else
‘Se já existe, coloca na lista de repetidos
dictRepet(listaOriginal(i, 1)) = 1
End If

Next i

É mais eficiente do que fazer esta verificação com um loop for, por exemplo.

 

 

 

Como animar objetos utilizando VBA

Pergunta: quem ganha a corrida?

Link para download da planilha: https://drive.google.com/open?id=1A6qdTISpCiUhEgXAygWpZbeK34Cz-cqD

 

A lógica é a seguinte.

A figura do elefante de bicicleta é um shape, que tem o nome de “Imagem 2”. Basta clicar no shape e ver o nome no painel.

O shape tem uma propriedade, chamada “Left”. Qual a posição, da esquerda para a direita.

Exemplo, atribuo 10 unidades à left.

Shapes(“Imagem 2”).left = 10

 

 

Para criar um efeito de animação, vou incrementando o Left por um loop (for ou while)

 

While posicaoElef < maxLeft And posicaoMenino < maxLeft

‘Elefante

With ActiveSheet.Shapes(“Imagem 2”)

posicaoElef = .Left + passoElef ‘O passo controla o quão rápido este anda

.Left = posicaoElef

DoEvents

End With

Wend

 

O comando DoEvents serve para fazer um refresh da tela.

 

 

Com o menino, é a mesma coisa, mas mudando outros parâmetros.

 

Dá para fazer outras brincadeiras, como deixar a velocidade aleatória.

 

 

Como calcular uma data menos 20 h?

Tenho um campo no formato data hora (04/09/2018 00:00)

Quero calcular este campo menos 20 horas.

A fórmula é “célula – 20/24”, conforme abaixo.

 

 

Resultado:

 

A lógica é que as datas são armazenadas como números no excel.
O dia 04/09/2018 = número 43.347

E 1 unidade de data = 1 dia.
O dia 05/09/2018 = número 43.348

Portanto,
1 hora = 1 / 24 dia (porque um dia tem 24h)

1 min = 1 / (24 * 60) ( porque um dia tem 24 h, e cada hora tem 60 min)

1 seg = 1 / (24* 60 *60)

Para subtrair 20h ao campo, devo subtrair o número 20 /24.

 

---

Bônus:

Porque 04/09/2018 = 43.347?

Porque são 43.347 dias a contar de 01/01/1900.

Se você colocar a data 01/01/1900 e subtrair 100 no excel, vai dar erro.

Fazer contas com datas é difícil, porque tem anos bissextos.
E também porque tem uma exceção à regra de bissextos a cada 100 anos.
E outra exceção de bissextos a cada 400 anos – e o ano 1900 cai exatamente na exceção da exceção.

Para entender mais, vide link a seguir.

https://ideiasesquecidas.com/2014/05/25/anos-bissextos-e-bugs-de-excel