Neste artigo você aprenderá 21 técnicas, dicas e truques de Excel Avançado no Excel.
Diferenças de Dados
Nesta dica você aprenderá como encontrar diferenças de dados no Excel.
Veja abaixo que temos duas listas, Financeiro e Contábil e queremos encontrar as diferenças entre os valores.
Uma forma de fazer isso é usando a formatação condicional
Clique em Página Inicial->Formatação Condicional->Regra de Realce das Células->Valores Duplicados
Em valores Duplicados altere para Exclusivos.
O resultado será em então uma lista com as diferenças destacadas entre eles, pois ele exibirá apenas os valores exclusivos.
Juntar Arquivos no Excel
Nesta dica você pode juntar arquivos no Excel à partir de pastas de trabalho e planilhas.
No nosso exemplo temos uma lista de arquivos em uma pasta com várias pastas de trabalho que tem várias planilhas com os mesmos cabeçalhos.
Esta é a estrutura das planilhas.
Clique em Dados->De Arquivo->Da Pasta e selecione a pasta com os arquivos.
Na tela em seguida clique em Transformar Dados.
Clique em Content e selecione no botão com as setas.
Em Combinar os arquivos selecione a pasta principal para trazer todos os dados das planilhas.
Remova as outras colunas e clique nas setinhas na coluna para extrair os dados.
A tela seguinte ficará desta forma e clique nas setas para extrair os dados.
Na tela seguinte você tem os dados desta forma, já extraídos
Clique em primeira linha como cabeçalho.
Clique na coluna e em excluir erros, pois temos linhas com valores incorretos na coluna por conta da conversão de texto como valor por conta dos cabeçalhos.
Clique em Fechar e Carregar e selecione Apenas Criar Conexão.
Clique em Tabela e clique em OK para extrair os dados da planilha.
O resultado que temos é a exportação dos dados das planilha combinadas conforme temos abaixo.
Preenchimento Relâmpago
O preenchimento relâmpago permite que você realize extração de dados automaticamente à partir de exemplos.
Na lista acima temos dados na qual gostaríamos de extrair o CPF, DATA E NOTA.
Para isso digite um exemplo dos dados e pressione CTRL+E.
Por este atalho o Excel irá usar como exemplo este campo e irá preencher o campo de CPF.
Faça o mesmo com a data e a nota e você terá os dados extraídos conforme abaixo.
Outro exemplo que pode ser feito é criar um texto usando dados de várias colunas, veja abaixo.
No exemplo usamos uma função para demonstrar que não é tão simples criar com fórmulas.
Mas você pode digitar o exemplo diretamente e pressionar CTRL+E que os dados serão extraídos automaticamente conforme abaixo.
Lista Personalizada no Excel
Você também pode criar listas personalizadas no Excel, como temos para os dias da semana ou meses por exemplo.
Digamos que tenha uma lista de filiais e gostaria de digitar uma e arrastar para preencher as outras.
Para isso digite em Arquivo->Opções e selecione Avançado.
Rode a barra até o final e clique em Editar Listas Personalizadas…
Pode digitar as listas sendo cada item em uma linha diferente ou importar diretamente á partir do Excel.
Feito isso cliique em Adicionar e pronto.
Digite qualquer nome da sua lista e clique no canto para arrastar.
Ao arrastar você terá sua lista preenchida automaticamente.
Usando Janelas no Excel
Algo também interessante é a utilização de janelas no Excel para poder ver os dados das planilhas e navegar entre elas simultaneamente.
Para isso clique em Exibir e Exibir Lado a Lado.
Na tela seguinte selecione a forma como deseja Organizar as janelas, se lado a lado, na horizontal, na vertical ou em cascata.
No exemplo temos então Lado a Lado.
E com isso, você pode ver os dados da mesma planilha ou de planilhas diferentes ao mesmo tempo para verificar ou comparar dados.
Segmentação de Dados em Tabelas
A segmentação de dados permite que façamos filtros automáticos e visuais de dados.
Selecione a tabela e clique em Inserir->Segmentação de dados.
Na segmentação de dados você selecione quais os itens que irá desejar como filtro na tabela.
Na tela seguinte você terá então os filtros visuais criados automaticamente e também poderá alterar configurações de cores, tamanho e bloqueio para que ela não se movimente ao alterar a coluna por exemplo.
Analisar Dados – Ideas Excel
O Ideas do Excel realiza análises rápidas de dados usando inteligência artificial.
Veja os dados abaixo das vendas. Vamos ligar o recurso do Ideas para analisar estes dados.
Clique em Arquivo e mude o idioma para inglês, pois este recurso só existe no Excel em inglês.
Em seguida clique em Ideas e veja que automaticamente irá aparecer a guia de análise de dados na direita.
Serão exibidas sugestões e você também pode escrever uma pergunta sobre os seus dados.
Para aplicar clique no botão abaixo da sugestão e ela será aplicada imediatamente em uma nova planilha.
Veja mais sobre o Ideas no vídeo abaixo:
Atalhos no Excel
Uma forma de aumentar a sua produtividade é trabalhar com atalhos no Excel. Esta simples ação é capaz de aumentar consideravelmente a velocidade com que você alimenta e produz suas planilhas.
O motivo para aumentar esta velocidade é o fato de evitar o uso do mouse, que acaba sendo um grande vilão para a velocidade.
Veja mais detalhes e um e-book com 220 atalhos no Excel: https://www.guiadoexcel.com.br/220-atalhos-do-excel-para-aumentar-a-sua-produtividade/
Matrizes Dinâmicas no Excel
As matrizes dinâmicas vieram para revolucionar o Excel, mas o que muda?
Desde a forma como se referencias as células, até o modo como resolvemos problemas complexos de matriciais podem ser muitas resolvidos agora com funções que contém um único parâmetro.
Veja no exemplo abaixo, nesta temos uma seleção de vários dados à partir de uma única célula.
Isto geraria um erro de #Valor em outras versões do Excel, mas no 365 nós temos o retorno destes dados.
Veja que temos então os dados retornados à partir de uma única célula e isso muda tudo quanto ao uso do Excel.
No exemplo abaixo selecionamos várias linhas e colunas e temos os dados retornados automaticamente.
Vieram também outras funções como a função Classificar, que permite retornar a classificação dos dados automaticamente.
No exemplo abaixo usamos a função ÚNICO aonde temos o retorno de valores únicos automaticamente.
Outra função poderosa que veio com o 365 é a função Filtro, aonde temos o retorno de dados conforme filtragem dos dados.
Veja nesta função como usar o filtro de forma avançada no Excel.
Procv com PowerQuery no Excel
O Power Query permite que você faça a diversos tratamentos de dados, inclusive a combinação de dados.
No nosso exemplo abaixo temos a Fonte e de dados e um De, Para aonde temos a chave e o destino desejado.
O objetivo é que os dados sejam substituídos da coluna Comunicação pelo De Para.
Clique então na tabela de dados e em Dados->Importar Tabela para a origem.
Faça o mesmo clicando na tabela De->Para para que elas sejam importadas para o Power Query.
Clique então em Mesclar Consultas e selecione a coluna de Comunicação e a tabela de comunicação do De Para para que ela seja ligada.
Com isso temos então uma ligação dos dados pela chave e o retorno desta coluna que pode ser selecionada como Para.
Após isso pode exportar os dados da coluna tDados clicando na consulta e selecionando a Tabela e em OK para exportar os dados na planilha.
Veja que temos então o resultado na planilha abaixo.
Minigráficos no Excel
Os minigráficos tem como finalidade permitir uma rápida análise de dados de uma linha, demonstrando visualmente aonde os valores se sobressaem de forma positiva ou negativa.
Para usar, crie uma coluna aonde irão estar os minigráficos. Clique em Inserir->minigráficos.
Selecione o intervalo de dados com a entrada de dados e o intervalo de local aonde serão criados.
Com isso temos então o mini gráfico criado conforme abaixo.
No relatório temos então a planilha pronta com os minigráficos conforme temos e também com várias configurações.
Estes minigráficos podem ser feitos também com outro formato que é o de linha.
Emojis no Excel
Também é possível que sejam inclusos emojis no Excel à partir do Windows.
Basta clicar a tecla Windows + .
Irá aparecer a lista de emojis disponíveis no Excel. Veja um exemplo.
Na tabela acima temos uma tabela com os dados dos meses. Pode aplicar o emoji na formatação da célula.
Clique na coluna em Gerenciador de Regras de Formatação Condicional.
Adicione a regra clicando em nova regra e adicione a regra que deseja, no nosso caso aplicamos uma regra aonde verificamos se o valor é menor do que a média.
Aplique então clicando em Windows + . para retornar uma lista de emojis e colocar na formatação conforme desejar.
O resultado que temos então é a aplicação de emojis conforme abaixo, lembrando que pode ser também alicado diretamente nas células.
Formatar Células Personalizado
Com a formatação de células personalizada você conseguirá realizar o conteúdo de células com textos com visuais diferentes, textos, cores, formatação diferente para números positivos, negativos, zeros e textos, pode aplicar inclusive fórmulas condicionais diretamente na formatação personalizada.
Veja mais neste link: https://www.guiadoexcel.com.br/como-usar-formatar-celulas-personalizado-no-excel/
teste
Atalhos de Formatação Personalizado
O Excel possui muitos atalhos e alguns deles são para formatação personalizada.
Isso facilita bastante as coisas, evitando que tenha que usar o mouse.
Os que principalmente recomendo são os abaixo que são os mais comuns:
- CTRL+~ : Número Geral
- CTRL+$ : Formato de Moeda
- CTRL+% : Percentual
- CTRL+^ : Número Científico
- CTRL+# : Formato de data
- CTRL+@ : Formato de hora
- CTRL+1 : Abre o formato personalizado de células no Excel
Atingir Meta no Excel
O atingir meta é um recurso do Excel que permite que você simule dados de forma automática.
Vejamos um exemplo. Nela nós temos um cálculo de ponto de equilíbrio aonde há
Para isso clique na guia Dados e Em Atingir Meta.
Selecione a célula aonde há a fórmula, nela temos a função que irá definir a célula e em Para valor digite 0 e clique em Alternando célula e selecione a célula Qtde.
Temos abaixo então o resultado, veja que o valor da quantidade foi alterado para alcançar o valor 0 no resultado, chegando então ao ponto de equilíbrio.
No final temos então os valores calculados automaticamente, sem a necessidade de digitar manualmente até chegar no valor 0.
Download Planilha Técnicas Dicas e Truques de Excel Avançado
Clique no botão abaixo para realizar o download da planilha de técnicas e truques de Excel Avançado com exemplo de dados:
