terça-feira, 23 de junho de 2015

Filtro Avançado com Macro - Excel

Filtrar dados é exibir informações que atendam certa condição, mas o filtrar avançado permite, além de filtrar uma lista de informações com mais de um critério, a extrair essas informações para outro local da planilha. Este recurso é ideal quando temos que selecionar dados numa lista grande para análises de resultados, porém quando temos que aplicá-lo por diversas vezes o melhor é otimizar essa tarefa através de uma macro

Vamos começar?

1. Abra uma planilha que você queira aplicar o recurso de Filtrar Avançado.

Obs.: no meu caso abri um modelo de planilha disponível no Excel versão 2010 - Relatório de vendas. (Guia Arquivo - Novo - Modelos de Exemplo - Relatório de Vendas - Clicar no botão criar):


2.   Surgirá a planilha conforme abaixo:

  • Bem... planilha já aberta, vamos colocar a mão na massa?
Primeiramente temos que criar uma lista como cabeçalho, que será nossa fonte de aplicação dos filtros. Os cabeçalhos devem ser exatamente iguais, pois é abaixo destes campos que você colocará os critérios, que podem ser fórmulas que retornem valores da lista, pode ser valores utilizando os indicadores >, <, <>, =, como por exemplo >1.

A. Selecione as linhas de 1 a 4 (pelo cabeçalho de linhas):


B. Use o recurso inserir linhas (botão direito do mouse ou CTRL + +)


C. Selecione linha 5 e use o recurso copiar, clique na célula A1 e cole;


Pronto!! Podemos começar a aplicar o Filtrar Avançado.

1. Na Célula A2 digite o produto Filo Mix e na F2 digite <100 (filtraremos as vendas do Tri 4 abaixo de 100);

2. Clique em qualquer célula da planilha e vá à guia Dados e no botão Avançado;

3. Na tela que segue você terá a parametrização do filtro avançado;


  • Como queremos que a lista seja filtrada em outro local clique na ação Copiar para outro local;
  • No primeiro campo Intervalo da lista, selecione a lista de dados: $A$5:$F$282;
  • No segundo campo, Intervalo de critérios, selecione o intervalo de critérios com o cabeçalho: $A$1:$F$2;
  • No campo Copiar para selecione o intervalo desejado. No nosso caso será o $H$5.

Pronto! Terminamos a primeira parte do tutorial, entendendo como funciona o Filtro Avançado.

O problema é que sempre que você for filtrar terá que repetir estas ações. Para resolver este problema vamos fazer uma macro para gravar as ações de filtragem de dados.

Criando uma macro para automatizar o filtro avançado

Como iremos trabalhar com macros neste filtro, o primeiro passo a fazer é habilitar a guia Desenvolvedor:

1.      Clique na guia Desenvolvedor (Caso não a esteja visualizando siga os passos abaixo):
  • É interessante termos a nossa disposição a Guia Desenvolvedor caso queira associar macros a botões. 
A. Para ativar a Guia Desenvolvedor vá a Guia Arquivo (versão 2010, ou Botão Office na versão 2007) ® Opções. Surgirá a janela Opções do Excel como a imagem abaixo:
  • No painel da esquerda ative a opção Personalizar Faixa de Opções;
  • No painel da direita, em Escolher comandos em: clique na seta Drop Down e escolha Todas as Guias;
  • Clique na opção Desenvolvedor e em seguida no Botão Adicionar;
  • Observe se o Desenvolvedor surgiu no painel de Guias de Opções antes de dar ok.
E pronto!! Observe a faixa de opções da nova Guia Desenvolvedor:


2. Clique no botão Gravar Macro;


3. Surgira uma janela Gravar Macro, onde inserimos informações referentes a macro que estaremos gravando;
  • Digite no campo Nome da Macro: Filtrar;
  • Na tecla de atalho digite: A;
  • Em Armazenar Macro em: selecione Esta pasta de trabalho.
  • O campo descrição é opcional, mas ideal para identifica-la caso necessite entrar no Editor de VBA para alterar/editar essa macro.

       4. Clique em OK.

Atenção: a partir deste momento todas as operações que você fizer serão gravadas, por isso não clique em qualquer lugar na planilha.

5.  Clique em uma célula da lista, por exemplo F1, e clique na guia Dados , opção Avançado.
  • Verifique se o intervalo de dados selecionado corresponde á $A$1:$F$282, senão corrija-o.
  • No intervalo de critérios verifique se está apontando para $A$1:$F$2, senão corrija.
  • Marque novamente a opção Copiar para outro local e verifique se o intervalo é $H$5;
  • Clique em OK.

 6. Volte na guia Desenvolvedor e clique em Parar Gravação.


  • Nossa macro já esta funcionando, só que pelo atalho que inserimos o CTRl + Shift + A. Limpem as informações anteriores e Experimentem!!

Para dar mais praticidade e aumentar nossa produtividade, devemos adicionar essa macro a um botão. Vamos lá?

1.      Clique na guia Inserir e selecione o botão Formas e escolha a forma de Retângulo e desenhe-a ao lado dos critérios, mais ou menos na coluna H;
2.      Selecione a forma criada e Digite Filtrar (botão direito do mouse - editar texto).


3.  Clique com o botão direito sobre o retângulo e selecione a opção Atribuir Macro.
4. Na janela que segue selecione a macro criada por nós anteriormente e que possui o nome Filtrar.


5. Pronto!! Para testar troque opções de filtro (por exemplo outro produto ou outros valores) e clique no botão filtrar!

Observação: Para refinar nosso trabalho tente criar uma macro que limpe os dados filtrados após serem analisados, antes de aplicar outro filtro. Essa opção é importante para não “misturar” dados filtrados anteriormente por outros critérios. Exemplo: posso aplicar filtro com um critério que em retorne uma lista maior que dados anteriores.

Qualquer dúvida é só entrar em contato!!

Até breve!!

quinta-feira, 4 de junho de 2015

Teclas de função - MS Excel

Olá!!
Mais um conjunto de atalhos e funções que as Teclas de função de nosso teclado nos oferece e que pode também aumentar nossa produtividade no uso das planilhas em Excel.

Tecla
Descrição
F1
Exibe o painel de tarefas da Ajuda do Microsoft Office Excel.
CTRL+F1 exibe ou oculta a Faixa de Opções, um componente da Interface de usuário do Microsoft Office Fluent.
ALT+F1 cria um gráfico dos dados no intervalo atual.
ALT+SHIFT+F1 insere uma nova planilha.
F2
Edita a célula ativa e posiciona o ponto de inserção no fim do conteúdo da célula. Ele também move o ponto de inserção para a Barra de Fórmulas para edição em uma célula desativada.
SHIFT+F2 adiciona ou edita um comentário de célula.
CTRL+F2 exibe a janela Visualizar Impressão.
F3
Exibe a caixa de diálogo Colar Nome.
SHIFT+F3 exibe a caixa de diálogo Inserir Função.
F4
Repete o último comando ou ação, se possível.
CTRL+F4 fecha a janela da pasta de trabalho selecionada.
F5
Exibe a caixa de diálogo Ir para.
CTRL+F5 restaura o tamanho da janela da pasta de trabalho selecionada.
F6
Alterna entre a planilha, a Faixa de Opções, o painel de tarefas e os controles de zoom. Em uma planilha que foi dividida (menu Exibir, comando Gerenciar Esta JanelaCongelar PainéisDividir Janela), F6 inclui os painéis divididos ao alternar entre painéis e a área da Faixa de Opções.
SHIFT+F6 alterna entre a planilha, os controles de zoom, o painel de tarefas e a Faixa de Opções.
CTRL+F6 alterna para a próxima janela da pasta de trabalho quando mais de uma janela da pasta de trabalho é aberta.
F7
Exibe a caixa de diálogo Verificar ortografia para verificar a ortografia na planilha ativa ou no intervalo selecionado.
CTRL+F7 executa o comando Mover na janela da pasta de trabalho quando ela não está maximizada. Use as teclas de direção para mover a janela e, quando terminar, pressione ENTER ou ESC para cancelar.
F8
Ativa ou desativa o modo estendido. Nesse modo, Seleção Estendida aparece na linha de status e as teclas de direção estendem a seleção.
SHIFT+F8 permite adicionar uma célula não adjacente ou um intervalo a uma seleção de células, utilizando as teclas de direção.
CTRL+F8 executa o comando Tamanho (no menu Controle da janela da pasta de trabalho), quando uma pasta de trabalho não está maximizada.
ALT+F8 exibe a caixa de diálogo Macro para criar, executar, editar ou excluir uma macro.
F9
Calcula todas as planilhas em todas as pastas de trabalho abertas.
SHIFT+F9 calcula a planilha ativa.
CTRL+ALT+F9 calcula todas as planilhas em todas as pastas de trabalho abertas, independentemente de elas terem sido ou não alteradas desde o último cálculo.
CTRL+ALT+SHIFT+F9 verifica novamente as fórmulas dependentes e depois calcula todas as células em todas as pastas de trabalho abertas, inclusive as células que não estão marcadas para serem calculadas.
CTRL+F9 minimiza a janela da pasta de trabalho para um ícone.
F10
Ativa e desativa as dicas de tecla.
SHIFT+F10 exibe o menu de atalho para um item selecionado.
ALT+SHIFT+F10 exibe o menu ou a mensagem de uma marca inteligente. Se mais de uma marca inteligente estiver presente, alterna para a marca inteligente seguinte e exibe seu menu ou sua mensagem.
CTRL+F10 maximiza ou restaura a janela da pasta de trabalho selecionada.
F11
Cria um gráfico dos dados no intervalo selecionado.
SHIFT+F11 insere uma nova planilha.
ALT+F11 abre o Editor do Microsoft Visual Basic, no qual você pode criar uma macro utilizando o VBA (Visual Basic for Applications).
F12
Exibe a caixa de diálogo Salvar Como.

 Copiado do site: https://support.office.com/pt-br/

Observação: funções da versão 2010, poderão sofrer alterações em versões anteriores.
Até daqui a pouco!!

Teclas de Atalho no Excel – Combinação com Ctrl

Como prometi em post anterior, seguem mais atalhos que aumentam a produtividade no Excel:

Quem nunca ficou irritado ao trabalhar com repetições de comandos repetidos com o mouse? Afinal é muito útil para determinadas situações, mas às vezes exige uma precisão que mais atrapalham do que ajudam – principalmente quando se trata do Touchpad do notebook ou de mecanismos que fazemos frequentemente.

Se o programa em que você estiver trabalhando for uma planilha no Excel então! Isso se agrava, porque ficamos o tempo todo mexendo em células, digitando números, dados e fórmulas não é mesmo?

Disponibilizo a seguir alguns atalhos que agilizam nossos trabalhos aumentando nossa produtividade.



Teclas
Descrição
CTRL+SHIFT+(
Exibe novamente as linhas ocultas dentro da seleção.
CTRL+SHIFT+)
Exibe novamente as colunas ocultas dentro da seleção.
CTRL+SHIFT+&
Aplica o contorno às células selecionadas.
CTRL+SHIFT_
Remove o contorno das células selecionadas.
CTRL+SHIFT+^
Aplica o formato de número Exponencial com duas casas decimais.
CTRL+SHIFT+#
Aplica o formato Data com dia, mês e ano.
CTRL+SHIFT+@
Aplica o formato Hora com a hora e os minutos, AM ou PM.
CTRL+SHIFT+!
Aplica o formato Número com duas casas decimais, separador de milhar e sinal de menos (-) para valores negativos.
CTRL+SHIFT+:
Insere a hora atual.
CTRL+SHIFT+"
Copia o valor da célula que está acima da célula ativa para a célula ou a barra de fórmulas.
CTRL+`
Alterna entre a exibição dos valores da célula e a exibição de fórmulas na planilha.
CTRL+'
Copia uma fórmula da célula que está acima da célula ativa para a célula ou a barra de fórmulas.
CTRL+A
Seleciona a planilha inteira.
Se a planilha contiver dados, CTRL+A seleciona a região atual. Pressionar CTRL+A novamente seleciona a região atual e suas linhas de resumo. Pressionar CTRL+A novamente seleciona a planilha inteira.
Quando o ponto de inserção está à direita de um nome de função em uma fórmula, exibe a caixa de diálogo Argumentos da função.
CTRL+SHIFT+A insere os nomes e os parênteses do argumento quando o ponto de inserção está à direita de um nome de função em uma fórmula.
CTRL+N
Aplica ou remove formatação em negrito.
CTRL+C
Copia as células selecionadas.
CTRL+C seguido por outro CTRL+C exibe a Área de Transferência.
CTRL+I
Aplica ou remove formatação em itálico.
CTRL+N
Cria uma nova pasta de trabalho em branco.
CTRL+O
Exibe a caixa de diálogo Abrir para abrir ou localizar um arquivo.
CTRL+SHIFT+O seleciona todas as células que contêm comentários.
CTRL+P
Exibe a caixa de diálogo Imprimir.
CTRL+SHIFT+P abre a caixa de diálogo Formatar Células com a guia Fonte selecionada.
CTRL+R
Usa o comando Preencher à Direita para copiar o conteúdo e o formato da célula mais à esquerda de um intervalo selecionado nas células à direita.
CTRL+B
Salva o arquivo ativo com seu nome de arquivo, local e formato atual.
CTRL+T
Exibe a caixa de diálogo Criar Tabela.
CTRL+S
Aplica ou remove sublinhado.
CTRL+SHIFT+S alterna entre a expansão e a redução da barra de fórmulas.
CTRL+V
Insere o conteúdo da Área de Transferência no ponto de inserção e substitui qualquer seleção. Disponível somente depois de ter recortado ou copiado um objeto, texto ou conteúdo de célula.
CTRL+ALT+V exibe a caixa de diálogo Colar Especial, disponível somente depois que você recortar ou copiar um objeto, textos ou conteúdo de célula em uma planilha ou em outro programa.
CTRL+W
Fecha a janela da pasta de trabalho selecionada.
CTRL+X
Recorta as células selecionadas.
CTRL+Y
Repete o último comando ou ação, se possível.
CTRL+Z
Usa o comando Desfazer para reverter o último comando ou excluir a última entrada digitada.
CTRL+SHIFT+Z usa o comando Desfazer ou Refazer para reverter ou restaurar a correção automática quando Marcas Inteligentes de AutoCorreção são exibidas.

Auxilio do site: https://support.office.com/pt-br/

Até breve!!
Related Posts Plugin for WordPress, Blogger...

Receba Atualizações e novidades por e-mail!

Informar Treinamentos