Excel avançado com 10 dicas e truques

Quer conhecer mais algumas dicas e troques do Excel para facilitar a sua via e otimizar o seu trabalho do dia a dia, algumas funções podem não ser conhecidas por pessoas que já usam o Excel por isso gostaria de compartilhar com vocês 10 dias e truques que achei interessante.

Vamos lá !!!

1. Seleção avançada

Passo 1. Marque toda a tabela com os dados que você deseja buscar e selecionar. Depois, na aba “Página Inicial”, escolha a opção “Localizar e Selecionar” e assinale “Localizar…”;

Com os dados selecionados, aperte nos comandos da aba “Página Inicial” em destaque (Foto: Reprodução/Daniel Ribeiro)Com os dados selecionados, aperte nos comandos da aba “Página Inicial” em destaque (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Na janela “Localizar e substituir”, escreva o termo que você deseja encontrar e pressione “Localizar tudo”;

Digite o item que deseja procurar e aperte “Localizar tudo” (Foto: Reprodução/Daniel Ribeiro)Digite o item que deseja procurar e aperte “Localizar tudo” (Foto: Reprodução/Daniel Ribeiro)

Passo 3. Selecione um dos itens exibidos e aperte no teclado “Ctrl” + “A” para selecionar todos os resultados simultaneamente. Em seguida, pressione fechar – as células marcadas continuarão selecionadas;

Clique em um item dos resultados e “Ctrl” + “A” para selecionar todos (Foto: Reprodução/Daniel Ribeiro)Clique em um item dos resultados e “Ctrl” + “A” para selecionar todos (Foto: Reprodução/Daniel Ribeiro)

Passo 4. Através desta seleção é possível apagar, destacar ou copiar os todos os itens selecionados, e ao mesmo tempo.

Com a seleção avançada é possível editar ou apagar todos os itens encontrados (Foto: Reprodução/Daniel Ribeiro)Com a seleção avançada é possível editar ou apagar todos os itens encontrados (Foto: Reprodução/Daniel Ribeiro)

O recurso de transposição do Excel permite converter colunas em linhas e vice-versa. Porém, com a transposição avançada, o usuário utiliza uma fórmula, vinculando os dados da tabela transposta e fazendo com que qualquer modificação dos itens seja replicada.

Passo 1. Selecione o número de células e colunas equivalente ao número de colunas e células que você deseja transpor – por exemplo: se uma tabela é 2X5, selecione células no formato 5X2;

Marque um número de células equivalente a planilha que será transposta (Foto: Reprodução/Daniel Ribeiro)Marque um número de células equivalente a planilha que será transposta (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Ainda com as células marcadas, escreva a função “TRANSPOR(X:Y)” – sendo “X” a primeira célula do canto superior esquerdo da tabela, e “Y” a última célula do canto inferior direito;

Em seguida, escreva “TRANSPOR(X:Y)”, com “X” e “Y” indicando o início e o fim da tabela que será transposta (Foto: Reprodução/Daniel Ribeiro)Em seguida, escreva “TRANSPOR(X:Y)”, com “X” e “Y” indicando o início e o fim da tabela que será transposta (Foto: Reprodução/Daniel Ribeiro)

Passo 3. A tabela será transposta pela função, fixando os valores originais. Contudo, qualquer alteração nos valores da tabela original alterará imediatamente os itens transpostos.

A tabela transposta terá valores fixos e refletirá quaisquer alterações na tabela original (Foto: Reprodução/Daniel Ribeiro)A tabela transposta terá valores fixos e refletirá quaisquer alterações na tabela original (Foto: Reprodução/Daniel Ribeiro)

3. Segmentação de dados

O Excel conta com um filtro especial para tabelas identificadas em sua planilha. A segmentação de dados apresenta macros especiais com os itens da tabela, apresentando resultados em uma excelente disposição visual.

Passo 1. Selecione as células que serão identificados como tabela e, na aba “Inserir”, escolha a opção “Tabela”;

Marque as células e clique nos comandos destacados para inserir uma tabela (Foto: Reprodução/Daniel Ribeiro)Marque as células e clique nos comandos destacados para inserir uma tabela (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Na janela que será aberta, confira a seleção das células que formarão a tabela, marque a opção “Minha tabela tem cabeçalhos” e confirme em “OK”;

Na janela “Criar Tabela”, marque “Minha tabela tem cabeçalhos” e clique em “OK” (Foto: Reprodução/Daniel Ribeiro)Na janela “Criar Tabela”, marque “Minha tabela tem cabeçalhos” e clique em “OK” (Foto: Reprodução/Daniel Ribeiro)

Passo 3. Em seguida, na aba “Design”, clique na opção “Inserir Segmentação de Dados” para ativar o sistema de filtragem dinâmica da tabela criada;

Criada a tabela, aperte o botão “Inserir Segmentação de Dados”, na aba “Design” (Foto: Reprodução/Daniel Ribeiro)Criada a tabela, aperte o botão “Inserir Segmentação de Dados”, na aba “Design” (Foto: Reprodução/Daniel Ribeiro)

Passo 4. Será aberta uma janela com opções de cabeçalhos da tabela criada. Selecione os que forem necessários e pressione “OK”;

Escolha os cabeçalhos das colunas que deseja utilizar (Foto: Reprodução/Daniel Ribeiro)Escolha os cabeçalhos das colunas que deseja utilizar (Foto: Reprodução/Daniel Ribeiro)

Passo 5. Criada a segmentação de dados, cada opção marcada nas janelas dos cabeçalhos irá eliminar as alternativas incompatíveis com as demais. Da mesma forma, os itens selecionados serão apresentados na tabela, enquanto os não marcados permanecerão ocultos.

Cada item marcado será exibido na tabela, enquanto os demais serão ocultados (Foto: Reprodução/Daniel Ribeiro)Cada item marcado será exibido na tabela, enquanto os demais serão ocultados (Foto: Reprodução/Daniel Ribeiro)

4. Gerenciador de Cenário

A apresentação dos dados nas planilhas de Excel facilita a visualização de diferentes resultados. Porém, com o recurso Gerenciador de Cenário é possível alterar variáveis e fazer relatórios com uma escala dos melhores aos piores resultados possíveis.

Passo 1. Em uma planilha de cálculo, selecione os valores variáveis e, na aba “Dados”, clique no botão “Teste de Hipóteses” e, em seguida, na alternativa “Gerenciador de cenários…”;

Marque os valores variáveis na tabele e clique nos comandos em destaque na imagem (Foto: Reprodução/Daniel Ribeiro)Marque os valores variáveis na tabele e clique nos comandos em destaque na imagem (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Será aberta a janela “Editar cenário”. Escreva um nome no campo “Nome do Cenário” e clique em “OK” para continuar a configurar um dos possíveis cenários;

Nomeie o cenário e aperte “OK” (Foto: Reprodução/Daniel Ribeiro)Nomeie o cenário e aperte “OK” (Foto: Reprodução/Daniel Ribeiro)

Passo 3. Em seguida, altere os valores de cada variável, de acordo com as células previamente escolhidas, e conclua em “OK”;

Continue a edição do cenário escolhendo os valores de cada variável e clique “OK” (Foto: Reprodução/Daniel Ribeiro)Continue a edição do cenário escolhendo os valores de cada variável e clique “OK” (Foto: Reprodução/Daniel Ribeiro)

Passo 4. Repita a operação para adicionar outros cenários e, quando estiver satisfeito, selecione um destes, na janela “Gerenciador de Cenários, aperte em “Mostrar” para visualizar o cenário. As fórmulas da planilha incorporarão e operarão imediatamente os valores do cenário;

Selecione um cenário e clique em “Mostrar” para visualizá-lo, bem como o resultado com as fórmulas (Foto: Reprodução/Daniel Ribeiro)Selecione um cenário e clique em “Mostrar” para visualizá-lo, bem como o resultado com as fórmulas (Foto: Reprodução/Daniel Ribeiro)

Passo 5. Ainda na janela “Gerenciador de Cenários”, aperte o botão “Resumir” para fazer uma planilha especial com cada cenário;

A alternativa “Resumir” cria uma planilha com os resultados de todos os cenários (Foto: Reprodução/Daniel Ribeiro)A alternativa “Resumir” cria uma planilha com os resultados de todos os cenários (Foto: Reprodução/Daniel Ribeiro)

Passo 6. O “Resumo do Cenário” será criado em uma nova planilha e apresentará os cenários criados de acordo com a configuração desejada.

O Excel criará o “Resultado do Cenário” em uma nova planilha (Foto: Reprodução/Daniel Ribeiro)O Excel criará o “Resultado do Cenário” em uma nova planilha (Foto: Reprodução/Daniel Ribeiro)

5. Função “CONVERTER”

O Excel conta com um enorme banco de dados de conversão para valores e medidas de temperatura, peso, tamanho etc. Para acessar e utilizar este recurso basta inserir a função “CONVERTER”, convertendo automaticamente os valores.

Passo 1. Digite a fórmula “=CONVERTER(X;” (sem aspas) para exibir a tabela de valores de conversão do Excel – sendo que “X” equivale a célula com o valor original que será escolhido. É importante digitar “;” após identificar a célula para abrir a tabela de valores (na imagem, por exemplo, foi escolhido “números em litros”);

Escreva a função “=CONVERTER(X;” e escolha a categoria de medida que será convertida (Foto: Reprodução/Daniel Ribeiro)Escreva a função “=CONVERTER(X;” e escolha a categoria de medida que será convertida (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Em seguida, com a formula “=CONVERTER(X;”Y”;” – sendo “Y” a medida selecionada – escolha em uma segunda tabela, compatível com a primeira, a fórmula para o qual o valor será convertido;

Após digitar o segundo “;” de “=CONVERTER(X;”Y”;”, escolha a medida em que o valor será convertido (Foto: Reprodução/Daniel Ribeiro)Após digitar o segundo “;” de “=CONVERTER(X;”Y”;”, escolha a medida em que o valor será convertido (Foto: Reprodução/Daniel Ribeiro)

Passo 3. A célula com a fórmula passará a converter automaticamente qualquer valor determinado no primeiro campo, podendo ser replicada e ter medidas alteradas em outras células.

O campo com a fórmula alterará automaticamente os valores da célula previamente escolhido (Foto: Reprodução/Daniel Ribeiro)O campo com a fórmula alterará automaticamente os valores da célula previamente escolhido (Foto: Reprodução/Daniel Ribeiro)

6. Células com conteúdo invisível

Com recursos para ocultar colunas inteiras de planilhas, o Microsoft Excel também oferece um truque para esconder valores de determinadas células.

Passo 1. Clique com o botão direito do mouse sobre a célula que terá a visualização ocultada e, em seguida, escolha a alternativa “Formatar células…”;

Aperte o botão direito do mouse sobre a célula e escolha “Formatar célula...” (Foto: Reprodução/Daniel Ribeiro)Aperte o botão direito do mouse sobre a célula e escolha “Formatar célula...” (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Na janela “Formatar célula”, na aba “Número”, escolha a opção “Personalizado” e, no campo de texto, escreva “;;;” (sem aspas);

Selecione “Personalizado” e digite “;;;” no campo de texto (Foto: Reprodução/Daniel Ribeiro)Selecione “Personalizado” e digite “;;;” no campo de texto (Foto: Reprodução/Daniel Ribeiro)

Passo 3. O conteúdo da célula será ocultado da visualização normal da planilha, exibindo-a como um campo em branco. Porém será possível verificar o seu valor ao consultar a barra de fórmulas do Excel.

Os valores da célula passarão a ser exibidos somente na barra de fórmulas (Foto: Reprodução/Daniel Ribeiro)Os valores da célula passarão a ser exibidos somente na barra de fórmulas (Foto: Reprodução/Daniel Ribeiro)
7. Análise de Dados

O Microsoft Excel tem um pacote de recursos para fazer análise de dados estatísticos mais refinados e específicos. Porém, esta ferramenta se encontra disponível na configuração original do programa.

Passo 1. Para ativar o pacote de Análise de Dados, clique na aba “Arquivos” e, em seguida, na alternativa “Opções”;

Na aba “Arquivo”, aperte em “Opções” (Foto: Reprodução/Daniel Ribeiro)Na aba “Arquivo”, aperte em “Opções” (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Será aberta uma janela com várias opções de configuração do Excel. Na parte “Suplementos”, escolha a opção “Suplementos do Excel”, na caixa de seleção da parte inferior da interface, e conclua em “Ir…”;

Em “Suplementos”, escolhe “Suplementos do Excel” e clique em “Ir...” (Foto: Reprodução/Daniel Ribeiro)Em “Suplementos”, escolhe “Suplementos do Excel” e clique em “Ir...” (Foto: Reprodução/Daniel Ribeiro)

Em seguida, marque “Ferramenta de análise” e clique “OK” (Foto: Reprodução/Daniel Ribeiro)Em seguida, marque “Ferramenta de análise” e clique “OK” (Foto: Reprodução/Daniel Ribeiro)

Passo 4. As opções de Análise de Dados poderão ser acessadas através de um botão na aba “Dados”, bastando escolher uma das várias alternativas para iniciar a configuração dos recursos de estatística.

Na aba “Dados” estará o botão “Análise de Dados” com os sofisticados recursos estatísticos do Excel (Foto: Reprodução/Daniel Ribeiro)Na aba “Dados” estará o botão “Análise de Dados” com os sofisticados recursos estatísticos do Excel (Foto: Reprodução/Daniel Ribeiro)

8. Remover células em branco

Um truque prático e fácil para editar e padronizar planilhas de Excel é a remoção de células em branco. Apesar de não existir um recurso para fazer exatamente esta operação, a alternativa para a adequação é bem útil e versátil.

Passo 1. Em uma planilha com células em branco, selecione suas colunas e digite o comando “Ctrl” + “G” para abrir o recurso “Ir para” e clique em “Especial…”;

Selecione as colunas da planilha, abra “Ir para” com “Ctrl” + “G” e clique em “Especial...” (Foto: Reprodução/Daniel Ribeiro)Selecione as colunas da planilha, abra “Ir para” com “Ctrl” + “G” e clique em “Especial...” (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Na janela “Ir para especial”, selecione a opção “Em branco” e aperte “OK” para selecionar todas as células vazias na seleção da planilha;

Escolha “Em branco” e clique “OK” para selecionar todas as células vazias (Foto: Reprodução/Daniel Ribeiro)Escolha “Em branco” e clique “OK” para selecionar todas as células vazias (Foto: Reprodução/Daniel Ribeiro)

Passo 3. Em seguida, na aba “Página Inicial”, clique no comando “Excluir” e selecione a opção “Excluir Células…”. Na caixa de seleção que será aberta, escolha a opção “Destacar células para cima” para manter a unidade da planilha com campos contínuos.

Na aba “Página Inicial”, clique “Excluir”, “Excluir Células...” e  “Destacar células para cima”  (Foto: Reprodução/Daniel Ribeiro)Na aba “Página Inicial”, clique “Excluir”, “Excluir Células...” e  “Destacar células para cima”  (Foto: Reprodução/Daniel Ribeiro)
9. Filtro Avançado

Com um já sofisticado sistema de filtro para planilhas, o Excel também conta com o recurso de Filtro Avançado que permite formar uma nova tabela com itens extraídos da planilha original.

Passo 1. Na aba “Dados”, selecione “Avançado” para ativar a janela de configuração do Filtro Avançado. É importante também copiar o cabeçalho da planilha para não misturar células e já determinar o termo que será filtrado;

Copie o cabeçalho da planilha com o termo de filtro, e na aba “Dados, clique em “Avançado” (Foto: Reprodução/Daniel Ribeiro)Copie o cabeçalho da planilha com o termo de filtro, e na aba “Dados, clique em “Avançado” (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Na janela Filtro Avançado, determine como “Intervalo da lista” toda a planilha que será utilizada;

Marque toda a planilha como “Intervalo da lista” (Foto: Reprodução/Daniel Ribeiro)Marque toda a planilha como “Intervalo da lista” (Foto: Reprodução/Daniel Ribeiro)

Passo 3. Em seguida, escolha o cabeçalho e o termo de filtragem para o campo “Intervalo de critérios”;

Marque o cabeçalho e o termo de filtro como “Intervalo de critérios” (Foto: Reprodução/Daniel Ribeiro)Marque o cabeçalho e o termo de filtro como “Intervalo de critérios” (Foto: Reprodução/Daniel Ribeiro)

Passo 4. Para concluir o Filtro Avançado, marque a opção “Copiar para um novo local” e, logo abaixo, escolha o campo em “Copiar para:” e aperte “OK”;

Marque “Copiar para um novo local” e escolha a célula em “Copiar para” (Foto: Reprodução/Daniel Ribeiro)Marque “Copiar para um novo local” e escolha a célula em “Copiar para” (Foto: Reprodução/Daniel Ribeiro)

Passo 5. O Filtro Avançado formará uma nova tabela só com as linhas que contenham o termo da filtragem

Uma nova planilha será exibida no campo determinado pelo Filtro Avançado (Foto: Reprodução/Daniel Ribeiro)Uma nova planilha será exibida no campo determinado pelo Filtro Avançado (Foto: Reprodução/Daniel Ribeiro)

Passo 6. Da mesma forma, é possível combinar mais de dois termos e utilizar valores e indicadores numéricos para aprimorar a filtragem.

O Filtro Avançado permite combinar termos e utilizar valores com indicadores numéricos  (Foto: Reprodução/Daniel Ribeiro)O Filtro Avançado permite combinar termos e utilizar valores com indicadores numéricos  (Foto: Reprodução/Daniel Ribeiro)

10. Função “DIATRABALHOTOTAL”

O Excel tem vários recursos para contabilizar dias e horas de trabalho, mas com a fórmula “DIATRABALHOTOTAL” é possível configurar também feriados e folgas.

Passo 1. A fórmula “=DIATRABALHOTOTAL(A;B)” (sem aspas) faz a contabilização automática do total de dias de trabalho – sendo “A” a célula com a data do primeiro dia, e “B” o campo com a data do último dia;

Escreva “=DIATRABALHOTOTAL(A;B)”, com “A” e “B” indicando, respectivamente, o primeiro e o último dia de trabalho (Foto: Reprodução/Daniel Ribeiro)Escreva “=DIATRABALHOTOTAL(A;B)”, com “A” e “B” indicando, respectivamente, o primeiro e o último dia de trabalho (Foto: Reprodução/Daniel Ribeiro)

Passo 2. Porém, esta função conta com uma terceira variante que, além de verificar se a data cai em um final de semana, já subtrai do montante total os dias de trabalho. Para continuar a fórmula, digite “=DIATRABALHOTOTAL(A;B;C:D)” – com C e D indicando a primeira e a última célula dos feriados ou folgas;

Com “=DIATRABALHOTOTAL(A;B;C:D)”, o intervalo “C:D” subtrai feriados da soma total (Foto: Reprodução/Daniel Ribeiro)Com “=DIATRABALHOTOTAL(A;B;C:D)”, o intervalo “C:D” subtrai feriados da soma total (Foto: Reprodução/Daniel Ribeiro)

Passo 3. A função irá apresentar o resultado automaticamente sempre que alguma de suas variantes for alterada.

A formula apresentará imediatamente os resultados  (Foto: Reprodução/Daniel Ribeiro)A formula apresentará imediatamente os resultados  (Foto: Reprodução/Daniel Ribeiro)

 

Fonte: Techtudo

 

Sobre a Webglobe
A Webglobe realiza a gestão da infraestrutura de tecnologia de empresas, alinhando a tecnologia com os negócios dos nossos clientes para gerar melhores resultados.

Nosso fluxo de trabalho é baseado em processos e nossa gestão é apoiada nas boas práticas de ITIL e COBIT, permitindo entregar serviços gerenciados de tecnologia com qualidade, segurança e previsão de investimentos.

Visite: webglobe.com.br