Apostila Excel 3

Prévia do material em texto

EXCEL AVANÇADO 
 
EXCEL AVANÇADO 
 
 
 
2 
 
SUMÁRIO 
1- USO DO CAMPO CALCULADO NA EXTRAÇÃO DE DADOS 3 
2- O USO DE SUBTOTAIS 5 
3- FUNÇÕES DE BASE DE DADOS 9 
4- UTILIZAÇÃO DE TABELA DINÂMICA 11 
5- ATUALIZAR UMA TABELA DINÂMICA 20 
6- ALTERAR OUTROS ELEMENTOS DA TABELA DINÂMICA 23 
7- ALTERAR EXIBIÇÃO DE VALORES 28 
8- RELATÓRIOS BASEADOS EM DETALHE DE VALORES 33 
9- AS DEZ MAIS 37 
REFERÊNCIAS 
 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
3 
 
1- USO DO CAMPO CALCULADO NA EXTRAÇÃO DE DADOS 
 
Algumas vezes, a fonte de dados não contém um campo (ou coluna) necessário 
para a análise. Por exemplo, a fonte de dados pode conter campos com valores de 
Vendas e Lucro, mas não para Raio de lucro. Se este for o caso, é possível criar um 
campo calculado para o Raio de lucro usando os dados dos campos Vendas e 
Lucro. 
Este tópico demonstra como criar um campo calculado simples usando um exemplo. 
Etapa 1: criar o campo calculado 
1. Em uma planilha no Tableau, selecione Análise > Criar campo calculado. 
2. No Editor de cálculo aberto, forneça um nome para o campo calculado. 
Neste exemplo, o campo calculado é chamado Raio de lucro. 
Etapa 2: inserir uma fórmula 
1. No Editor de cálculo, insira uma fórmula. 
Este exemplo usa a seguinte fórmula: 
SUM([Profit])/SUM([Sales]) 
As fórmulas usam uma combinação de funções, campos e operadores. Para 
saber mais sobre como criar fórmulas no Tableau, consulte Formatação de 
cálculos no Tableau e Funções no Tableau. 
2. Ao terminar, clique em OK. 
O novo campo calculado é adicionado ao painel Dados. Se o novo campo 
computar dados quantitativos, ele será adicionado às Medidas. Se computar 
dados qualitativos, ele será adicionado às Dimensões. 
https://help.tableau.com/current/pro/desktop/pt-br/functions_operators.htm
https://help.tableau.com/current/pro/desktop/pt-br/functions_operators.htm
https://help.tableau.com/current/pro/desktop/pt-br/functions_operators.htm
https://help.tableau.com/current/pro/desktop/pt-br/functions.htm
EXCEL AVANÇADO 
 
 
 
4 
 
Agora você está pronto para usar o campo calculado na exibição. 
Verifique seu trabalho! Veja como criar um campo calculado simples em ação: 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
5 
 
2- O USO DE SUBTOTAIS 
 
Retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais 
fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, 
na guia Dados no aplicativo de desktop do Excel. Assim que a lista de subtotais for 
criada, você poderá modificá-la editando a função SUBTOTAL. 
Sintaxe 
SUBTOTAL(núm_função,ref1,[ref2],...]) 
A sintaxe da função SUBTOTAL tem os seguintes argumentos: 
 Núm_função Obrigatório. O número 1-11 ou 101-111 que especifica a função a 
ser usada para o subtotal. 1-11 inclui linhas ocultas manualmente, enquanto 101-111 
as exclui; células filtradas sempre são excluídas. 
Núm_função 
(inclui valores ocultos) 
Núm_função 
(ignora valores ocultos) 
Função 
1 101 MÉDIA 
2 102 CONTA 
3 103 CONT.VALORES 
4 104 MÁX 
5 105 MÍN 
6 106 MULT 
7 107 DESVPAD 
8 108 DESVPADP 
9 109 SOMA 
EXCEL AVANÇADO 
 
 
 
6 
 
Núm_função 
(inclui valores ocultos) 
Núm_função 
(ignora valores ocultos) 
Função 
10 110 VAR 
11 111 VARP 
 Ref1 Obrigatório. O primeiro intervalo nomeado ou referência cujo subtotal você 
deseja. 
 Ref2,... Opcional. Intervalos nomeados ou referências de 2 a 254 cujo subtotal 
você deseja. 
Comentários 
 Se existirem outros subtotais dentro de ref1; ref2,… (ou subtotais aninhados), esses 
subtotais aninhados serão ignorados para evitar dupla contagem. 
 Para as constantes núm_função de 1 a 11, a função SUBTOTAL inclui os valores de 
linhas ocultas pelo comando Ocultar Linhas no submenu Ocultar e Exibir do 
comando Formato no grupo Células, na guia Página Inicial no aplicativo de 
desktop do Excel. Use essas constantes para subtotalizar números ocultos e não 
ocultos em uma lista. Para as constantes núm_função de 101 a 111, a função 
SUBTOTAL ignora valores de linhas ocultos pelo comando Ocultar Linhas. Use 
essas constantes para subtotalizar somente números não ocultos em uma lista. 
 A função SUBTOTAL ignora as linhas não incluídas no resultado de um filtro, 
independentemente de qual valor de núm_função seja utilizado. 
 A função SUBTOTAL foi projetada para colunas de dados ou intervalos verticais. Ela 
não foi projetada para linhas de dados nem intervalos horizontais. Por exemplo, 
quando você subtotaliza um intervalo horizontal usando uma núm_função de 101 ou 
maior, como SUBTOTAL(109,B2:G2), ocultar uma coluna não afeta o subtotal. 
Porém, ocultar uma linha em um subtotal de um intervalo vertical afeta o subtotal. 
 Se qualquer uma das referências for uma referência 3D, o subTOTAL retornará o 
#VALUE! valor de erro. 
EXCEL AVANÇADO 
 
 
 
7 
 
Exemplo 
Copie os dados de exemplo da tabela a seguir e cole-os na célula A1 de uma nova 
planilha do Excel. Para as fórmulas mostrarem resultados, selecione-as, pressione 
F2 e pressione Enter. Se precisar, você poderá ajustar as larguras das colunas para 
ver todos os dados. 
Dados 
120 
10 
150 
23 
Fórmula Descrição Resultado 
=SUBTOTAL(9,A2:A5) A soma do subtotal das células 
A2:A5, usando 9 como primeiro 
argumento. 
303 
=SUBTOTAL(1,A2:A5) A média do subtotal das células 
A2:A5, usando 1 como primeiro 
argumento. 
75,75 
Observações 
A função SUBTOTAL sempre requer um argumento numérico (1 a 11, 101 a 111) 
como primeiro argumento. Esse argumento numérico é aplicado ao subtotal dos 
valores (intervalos de células, intervalos nomeados) que são especificados como 
os argumentos seguintes. 
 
Você pode calcular automaticamente subtotais e totais gerais em uma lista para uma 
coluna usando o comando Subtotal . 
Importante: Não há suporte para subtotais nas tabelas do Excel. O comando 
subtotal será exibido em cinza se você estiver trabalhando com uma tabela do Excel. 
https://support.office.com/pt-br/article/vis%C3%A3o-geral-de-tabelas-do-excel-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
EXCEL AVANÇADO 
 
 
 
8 
 
Para adicionar subtotais em uma tabela, primeiro você deve converter a tabela em 
um intervalo de dados normal e, em seguida, adicionar o SUBTOTAL. Observe que 
isso removerá toda a funcionalidade de tabela dos dados, exceto a formatação de 
tabela. Ou você pode criar uma tabela dinâmica. 
Quando você insere subtotais: 
 Subtotais são calculadas com um função resumo, como sum ou Average, 
usando a função SUBTOTAL. Você pode exibir mais de um tipo de função de 
resumo para cada coluna. 
 Totais gerais são derivadas de área de detalhes, não dos valores dos subtotais. 
Por exemplo, se você usar a função de resumo média , a linha total geral exibe uma 
média de todas as linhas de detalhes na lista, não uma média dos valores nas linhas 
de subtotais. 
 
Se a pasta de trabalho estiver definida como calcular fórmulas automaticamente, o 
comando subtotal recalculará os valores de subtotal e total geral automaticamente à 
medida que você editar os dados de detalhes. O comando subtotal também destaca 
a lista para que você possa exibir e ocultar as linhas de detalhes para cada subtotal. 
 
 
 
https://support.office.com/pt-br/article/criar-uma-tabela-din%C3%A2mica-para-analisar-dados-da-planilha-a9a84538-bfe9-40a9-a8e9-f99134456576
EXCEL AVANÇADO 
 
 
 
9 
 
3- FUNÇÕES DE BASE DE DADOS 
 
Clique numa das ligações da lista seguinte para ver ajuda detalhada acerca da 
função. 
Função Descrição 
Função BDMÉDIA Devolve a média das entradas da base de dados 
selecionada 
Função BDCONTAR Conta as células que contêm números numa base de dados 
Função 
BDCONTAR.VAL 
Conta as células que não estão em branco numa base de 
dados 
Função BDOBTER Extrai de uma base de dados um único registo que 
corresponde aoscritérios especificados 
Função BDMÁX Devolve o valor máximo das entradas da base de dados 
selecionada 
Função BDMÍN Devolve o valor mínimo das entradas da base de dados 
selecionada 
Função BDMULTIPL Multiplica os valores de um determinado campo de registos 
que correspondem aos critérios numa base de dados 
Função 
BDDESVPAD 
Calcula o desvio-padrão com base numa amostra das 
entradas da base de dados selecionada 
Função 
BDDESVPADP 
Calcula o desvio-padrão com base na população total das 
entradas da base de dados selecionada 
Função BDSOMA Soma os números na coluna de campo dos registos de 
base de dados que correspondem aos critérios 
Função BDVAR Calcula a variância com base numa amostra das entradas 
da base de dados selecionada 
Função BDVARP Calcula a variância com base na população total das 
https://support.office.com/pt-pt/article/bdm%C3%A9dia-fun%C3%A7%C3%A3o-bdm%C3%A9dia-a6a2d5ac-4b4b-48cd-a1d8-7b37834e5aee
https://support.office.com/pt-pt/article/bdcontar-fun%C3%A7%C3%A3o-bdcontar-c1fc7b93-fb0d-4d8d-97db-8d5f076eaeb1
https://support.office.com/pt-pt/article/bdcontar-val-fun%C3%A7%C3%A3o-bdcontar-val-00232a6d-5a66-4a01-a25b-c1653fda1244
https://support.office.com/pt-pt/article/bdcontar-val-fun%C3%A7%C3%A3o-bdcontar-val-00232a6d-5a66-4a01-a25b-c1653fda1244
https://support.office.com/pt-pt/article/bdobter-fun%C3%A7%C3%A3o-bdobter-455568bf-4eef-45f7-90f0-ec250d00892e
https://support.office.com/pt-pt/article/bdm%C3%A1x-fun%C3%A7%C3%A3o-bdm%C3%A1x-f4e8209d-8958-4c3d-a1ee-6351665d41c2
https://support.office.com/pt-pt/article/bdm%C3%ADn-fun%C3%A7%C3%A3o-bdm%C3%ADn-4ae6f1d9-1f26-40f1-a783-6dc3680192a3
https://support.office.com/pt-pt/article/bdmultipl-fun%C3%A7%C3%A3o-bdmultipl-4f96b13e-d49c-47a7-b769-22f6d017cb31
https://support.office.com/pt-pt/article/bddesvpad-fun%C3%A7%C3%A3o-bddesvpad-026b8c73-616d-4b5e-b072-241871c4ab96
https://support.office.com/pt-pt/article/bddesvpad-fun%C3%A7%C3%A3o-bddesvpad-026b8c73-616d-4b5e-b072-241871c4ab96
https://support.office.com/pt-pt/article/bddesvpadp-fun%C3%A7%C3%A3o-bddesvpadp-04b78995-da03-4813-bbd9-d74fd0f5d94b
https://support.office.com/pt-pt/article/bddesvpadp-fun%C3%A7%C3%A3o-bddesvpadp-04b78995-da03-4813-bbd9-d74fd0f5d94b
https://support.office.com/pt-pt/article/bdsoma-fun%C3%A7%C3%A3o-bdsoma-53181285-0c4b-4f5a-aaa3-529a322be41b
https://support.office.com/pt-pt/article/bdvar-fun%C3%A7%C3%A3o-bdvar-d6747ca9-99c7-48bb-996e-9d7af00f3ed1
https://support.office.com/pt-pt/article/bdvarp-fun%C3%A7%C3%A3o-bdvarp-eb0ba387-9cb7-45c8-81e9-0394912502fc
EXCEL AVANÇADO 
 
 
 
10 
 
Função Descrição 
entradas da base de dados selecionada 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
11 
 
4- UTILIZAÇÃO DE TABELA DINÂMICA 
 
Para que a gestão de uma empresa seja feita de forma eficiente, é preciso 
organizar bem as informações, além de gerar relatórios que facilitem as suas 
tomadas de decisão. Por isso, prezado aluno, ensinamos como criar a sua 
própria tabela dinâmica. Confira! 
Mas, afinal, o que é uma tabela dinâmica? É um resumo dos seus dados, 
agrupados em um gráfico que permite gerar relatórios e explorar tendências 
com base em suas informações. 
As tabelas dinâmicas são particularmente úteis se as tabelas de Excel que você usa 
na sua empresa tiverem linhas ou colunas longas com os valores necessários para 
acompanhar as somas e comparar facilmente entre si. 
Em outras palavras, elas extraem significado dessa confusão aparentemente infinita 
de números na tela. E, mais especificamente, permitem agrupar seus dados de 
maneiras diferentes para que você possa tirar conclusões úteis com mais facilidade. 
A parte ―dinâmica‖ dessas tabelas deriva do fato de que é possível visualizar os 
dados nelas de uma perspectiva diferente. Ou seja, você reorganiza eles para poder 
revelar informações úteis para o seu negócio a partir deles. 
Já sabe como usar o Excel e quer saber mais sobre esse recurso? Siga conosco e 
aprenda com o passo a passo que separamos logo abaixo! 
Qual é a utilidade da tabela dinâmica do Excel? 
A tabela dinâmica é formada por linhas, colunas e campos de dados e 
páginas. Esses componentes podem ser movidos e ajudam você a expandir, 
isolar, somar e agrupar os dados específicos em tempo real. 
Assim, essa tabela permite que você visualize as diferenças em um conjunto muito 
grande de informações. Por isso, é muito útil para organizar uma grande quantidade 
https://rockcontent.com/blog/como-usar-excel/
EXCEL AVANÇADO 
 
 
 
12 
 
de dados nas planilhas usadas para traçar as métricas de marketing e vendas, por 
exemplo, entre outras da sua empresa. 
Apontamos a seguir os principais benefícios de usar tabela dinâmica. Veja só! 
É fácil de usar 
Uma tabela dinâmica usa um campo de dados especificado pelo usuário e converte 
o cabeçalho de cada coluna em uma opção mais fácil de ser gerenciada. Colunas 
contendo dados podem ser removidas, adicionadas ou movidas facilmente. 
Aqui, resumos amigáveis e informativos podem ser criados a partir de longas 
planilhas de dados brutos. Eles podem ser resumidos de várias maneiras, incluindo 
frequências e médias. 
Como pode ver, uma vantagem das tabelas dinâmicas é que são fáceis de usar. 
Você pode resumir facilmente os dados ao arrastar as colunas para seções 
diferentes da tabela. As colunas também podem ser reorganizadas como você 
desejar com um clique no mouse. 
Simplifica a análise de dados 
Com a ajuda das tabelas dinâmicas do Excel, é possível manipular grandes 
quantidades de dados de uma só vez. 
Essas tabelas permitem que você obtenha uma grande quantidade deles e trabalhe 
de forma que visualize apenas alguns campos de dados. Isso ajuda na análise de 
dados, mesmo em grande quantidade. 
Ajuda no resumo de dados 
Outro benefício importante das tabelas dinâmicas é que elas ajudam a resumir os 
dados de maneira rápida e fácil. 
https://rockcontent.com/blog/metricas-de-marketing-de-conteudo/
https://inteligencia.rockcontent.com/futuro-analise-de-dados/
https://inteligencia.rockcontent.com/futuro-analise-de-dados/
https://inteligencia.rockcontent.com/futuro-analise-de-dados/
EXCEL AVANÇADO 
 
 
 
13 
 
A tabela ajuda a fazer um resumo conciso de milhares de linhas e colunas de dados 
não organizados. Com a ajuda dessas tabelas, você pode resumir grandes 
quantidades de informações em um pequeno espaço. 
Os dados podem ser resumidos em um formato simples e que é facilmente 
compreensível. Você pode organizar as linhas e colunas dos dados de acordo com 
suas necessidades, bem como rotulá-las e classificá-las da maneira que quiser. 
Permite criar padrões de dados 
As tabelas dinâmicas do Excel ajudam você a criar tabelas personalizadas a partir 
de grandes grupos de dados. Manipulá-los dessa maneira ajudará a localizar 
padrões recorrentes neles. Isso, por sua vez, ajudará na previsão precisa de 
dados. 
Possibilita a criação de relatórios rapidamente 
Um dos recursos mais relevantes das tabelas dinâmicas do Excel é a possibilidade 
de criar relatórios de maneira eficiente. Isso economiza as longas e extenuantes 
horas que você precisa gastar para criar relatórios manualmente. Além disso, a 
tabela também ajuda a fornecer links para fontes externas no relatório criado. 
Favorece a tomada rápida de decisões 
Uma tabela dinâmica pode ser considerada uma valiosa ferramenta de relatórios do 
Excel, pois permite que os usuários analisem facilmente os dados e tomem decisões 
rápidas. Isso serve como uma enorme vantagem no mundo industrial ou 
do marketing, em que é crucial tomar decisões precisas e rápidas. 
Como montar uma tabela dinâmica no Excel? 
Se ainda está se sentindo um pouco confuso sobre o que as tabelas dinâmicas 
realmente fazem, não se preocupe. 
https://rockcontent.com/blog/analise-preditiva/
https://rockcontent.com/blog/analise-preditiva/
https://rockcontent.com/blog/analise-preditiva/
https://materiais.rockcontent.com/relatorios-de-marketinghttps://materiais.rockcontent.com/relatorios-de-marketing
https://rockcontent.com/blog/o-que-e-marketing/
EXCEL AVANÇADO 
 
 
 
14 
 
Esse é um daqueles recursos que é muito mais fácil de entender quando o vemos 
em ação. Por isso, aqui estão os 6 passos que você deve seguir para criar uma 
tabela dinâmica. 
1. Insira seus dados em um intervalo de linhas e colunas 
Todas as tabelas dinâmicas do Excel começam com uma tabela básica em que 
todos os seus dados estão armazenados. Para criar essa tabela, basta inserir seus 
valores em um conjunto específico de linhas e colunas. Use a linha superior ou 
a coluna mais acima para categorizar seus valores pelo que eles representam. 
Por exemplo, para criar uma tabela do Excel de dados de desempenho da postagem 
do blog em sua estratégia de Marketing de Conteúdo, você pode ter: 
 uma coluna que liste cada ―URL‖; 
 uma que liste ―título da postagem‖ de cada URL; 
 uma listando ―visualizações até a data‖; 
 e assim por diante. 
 
2. Classifique seus dados por um atributo específico 
https://support.office.com/pt-br/article/criar-e-formatar-tabelas-e81aa349-b006-4f8a-9806-5af9df0ac664
https://rockcontent.com/blog/marketing-de-conteudo/
EXCEL AVANÇADO 
 
 
 
15 
 
Quando tiver todos os dados que deseja inserir em sua planilha do Excel, você 
deverá ordená-los de alguma forma, para facilitar o gerenciamento depois de 
transformá-los em uma tabela dinâmica. 
Para classificá-los, clique na guia ―dados‖ na barra de navegação superior e 
selecione a função ―ordenar‖ . Na janela exibida, você pode optar por classificar 
seus dados por qualquer coluna e ordem. 
Para classificar sua planilha do Excel por ―visualizações até a data‖, por exemplo, 
selecione o título dessa coluna em ―coluna‖ e, em seguida, selecione se deseja 
ordenar suas postagens de menor para maior ou de maior para menor. 
 
3. Realce suas células para criar sua tabela dinâmica 
Depois de inserir os dados na sua planilha do Excel e classificá-los como desejar, 
realce as células que gostaria de resumir em uma tabela dinâmica. Clique em 
―inserir‖ ao longo da navegação superior e selecione o ícone ―tabela dinâmica‖. 
Você também pode clicar em qualquer lugar da sua planilha, selecionar ―tabela 
dinâmica‖ e inserir manualmente o intervalo de células que deseja incluir na tabela 
dinâmica. 
EXCEL AVANÇADO 
 
 
 
16 
 
Isso abrirá uma caixa de opções, em que, além de definir seu intervalo de 
células, você poderá selecionar se deve ou não iniciar essa tabela dinâmica em 
uma nova planilha, ou mantê-la na planilha existente. Se abrir uma nova planilha, 
poderá navegar e sair dela na parte inferior da pasta de trabalho do Excel. Depois de 
escolhido, clique em ―OK‖. 
Como alternativa, você pode realçar suas células, selecionar ―tabelas dinâmicas 
recomendadas‖, à direita do ícone ―tabela dinâmica‖, e abrir uma tabela dinâmica 
com sugestões predefinidas sobre como organizar cada linha e coluna. 
 
4. Arraste e solte os campos na área ―campos de página‖ 
Depois de concluir a Etapa 1, o Excel criará uma tabela dinâmica em branco para 
você. Seu próximo passo é arrastar e soltar um campo — rotulado de acordo com 
os nomes das colunas na sua planilha — para a posição desejada. 
EXCEL AVANÇADO 
 
 
 
17 
 
 
Isso determinará por qual identificador exclusivo — título da postagem de blog, 
nome do produto e assim por diante — a tabela dinâmica organizará seus dados. 
 
5. Arraste e solte um campo nas áreas ―campos de linha‖ e ―campos de dados‖ 
Depois de estabelecer como você vai organizar seus dados, o próximo passo é 
adicionar alguns valores, arrastando para os ―campos de linha‖ e ―campos de 
dados‖. 
https://comunidade.rockcontent.com/titulos-para-blog/
EXCEL AVANÇADO 
 
 
 
18 
 
 
Continuando com o exemplo de dados de blogs, digamos que você queira resumir 
as visualizações das postagens do blog por título. Para fazer isso, basta arrastar o 
campo ―visualizações‖. 
6. Ajuste seus cálculos 
A soma de um determinado valor será calculada por padrão, mas você pode 
facilmente alterar isso para algo como média, máxima ou mínima, dependendo do 
que deseja calcular. 
Em um Mac, você pode fazer isso ao clicar no pequeno ―i‖ ao lado de um valor na 
área ―valores‖, selecionar a opção desejada e clicar em ―OK‖. Depois de fazer a 
seleção, sua tabela dinâmica será atualizada de acordo. 
Se você estiver usando um PC, será necessário clicar no pequeno triângulo invertido 
e selecionar os itens que você quer que apareçam na tabela. 
https://rockcontent.com/blog/como-criar-um-blog/
EXCEL AVANÇADO 
 
 
 
19 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
20 
 
5- ATUALIZAR UMA TABELA DINÂMICA 
 
A qualquer momento, você pode clicar em Atualizar para atualizar os dados em 
Tabelas Dinâmicas na pasta de trabalho. Você pode atualizar os dados de Tabelas 
Dinâmicas conectadas a dados externos, como um banco de dados (SQL Server, 
Oracle, Access ou outro), cubo do Analysis Services, feed de dados e muitas outras 
fontes. Você também pode atualizar dados de uma tabela de origem na mesma 
pasta de trabalho ou em outra. E você pode definir a pasta de trabalho para atualizar 
os dados da Tabela Dinâmica automaticamente quando você a abrir. 
Por padrão, as tabelas dinâmicas não são atualizadas automaticamente, mas você 
pode especificar que a tabela dinâmica seja atualizada automaticamente quando 
você abrir a pasta de trabalho que contém a tabela dinâmica. 
Versões mais recentesOffice 2010Web 
Atualizar manualmente 
1. Clique em qualquer lugar da Tabela Dinâmica para mostrar as Ferramentas de 
Tabela Dinâmica na faixa de opções. 
 
2. Clique em Análise > Atualizar ou pressione Alt+F5. 
 
Dica: Para atualizar todas as tabelas dinâmicas em sua pasta de trabalho de uma 
vez, clique em analisar > atualizar tudo. 
javascript:
javascript:
javascript:
javascript:
EXCEL AVANÇADO 
 
 
 
21 
 
Se a atualização levar mais tempo que o esperado, clique em Análise > 
seta Atualizar > Atualizar Status para verificar o status da atualização. 
Para interromper a atualização, clique em Cancelar Atualização. 
Impedir que as larguras de coluna e a formatação de célula sejam ajustadas 
Se as larguras das colunas e a formatação das células de seus dados se ajustarem 
quando você atualizar os dados da Tabela Dinâmica e você não quer que isso 
aconteça, verifique se as seguintes opções estão marcadas: 
1. Clique em Análise > Opções. 
 
2. Na guia Layout e Formato, marque as caixas Ajustar automaticamente a largura 
das colunas ao atualizar e Preservar a formatação da célula ao atualizar. 
Atualizar os dados automaticamente ao abrir a pasta de trabalho 
1. Clique em qualquer lugar da Tabela Dinâmica para mostrar as Ferramentas de 
Tabela Dinâmica na faixa de opções. 
 
2. Clique em Análise > Opções. 
 
EXCEL AVANÇADO 
 
 
 
22 
 
3. Na guia dados , marque a caixa atualizar dados ao abrir o arquivo . 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
23 
 
6- ALTERAR OUTROS ELEMENTOS DA TABELA DINÂMICA 
 
Depois de criar uma Tabela Dinâmica, você poderá alterar o intervalo de seus dados 
de origem. Por exemplo, é possível expandir os dados de origem para incluir mais 
linhas de dados. Entretanto, se os dados de origem forem substancialmente 
alterados — como a presença de mais ou menos colunas, considere a criação de 
uma nova Tabela Dinâmica. 
Você pode alterar a fonte de dados de uma tabela dinâmica para outra tabela do 
Excel ou um intervalo de células, ou alterar para uma fonte de dados externa 
diferente. 
Versões mais recentesOffice 2007 – 2010Web 
1. Clique na relatório de Tabela Dinâmica. 
2. Na guia analisar , no grupo dados , clique em Alterar fonte de dadose, em 
seguida, clique em Alterar fonte de dados. 
A caixa de diálogo Alterar fonte de dados da tabela dinâmica é exibida. 
3. Siga um destes procedimentos:o Para alterar a fonte de dados de uma tabela dinâmica para outra tabela do Excel ou 
um intervalo de células, clique em selecionar uma tabela ou intervaloe, em 
seguida, insira a primeira célula na caixa de texto tabela/intervalo e clique em OK . 
 
javascript:
javascript:
javascript:
EXCEL AVANÇADO 
 
 
 
24 
 
o Para usar uma conexão diferente, faça o seguinte: 
i. Clique em selecionar um usar uma fonte de dados externae clique em escolher 
conexão. 
 
A caixa de diálogo conexões existentes será exibida. 
ii. Na lista suspensa Mostrar na parte superior da caixa de diálogo, selecione a 
categoria de conexões para a qual você deseja escolher uma conexão ou 
selecione todas as conexões existentes (que é o padrão). 
iii. Selecione uma conexão na caixa de listagem selecionar uma conexão e, em 
seguida, clique em abrir. E se a sua conexão não estiver listada? 
Observação: Se você escolher uma conexão da categoria conexões nesta pasta 
de trabalho , você será reutilizar ou compartilhar uma conexão existente. Se você 
escolher uma conexão da categoria arquivos de conexão na rede ou arquivos de 
conexão neste computador , o arquivo de conexão será copiado para a pasta de 
trabalho como uma nova conexão de pasta de trabalho e, em seguida, usado como a 
nova conexão para a tabela dinâmica relatório. 
Para obter mais informações, consulte gerenciar conexões com dados em uma pasta 
de trabalho. 
iv. Clique em OK. 
E se a sua conexão não estiver listada? 
https://support.office.com/pt-br/article/alterar-os-dados-de-origem-de-uma-tabela-din%C3%A2mica-afd93524-f7de-432c-84d0-3896fbbc2577#bkmk_connectionnotlisted
https://support.office.com/pt-br/article/criar-editar-e-gerenciar-as-conex%C3%B5es-aos-dados-externos-89d44137-f18d-49cf-953d-d22a2eea2d46
https://support.office.com/pt-br/article/criar-editar-e-gerenciar-as-conex%C3%B5es-aos-dados-externos-89d44137-f18d-49cf-953d-d22a2eea2d46
https://support.office.com/pt-br/article/criar-editar-e-gerenciar-as-conex%C3%B5es-aos-dados-externos-89d44137-f18d-49cf-953d-d22a2eea2d46
EXCEL AVANÇADO 
 
 
 
25 
 
Se a sua conexão não estiver listada na caixa de diálogo conexões existentes , 
clique em Procurar maise procure a fonte de dados à qual você deseja se conectar 
na caixa de diálogo selecionar fonte de dados . Clique em nova fonte de dados, 
se for apropriado, siga as etapas no Assistente para conexão de dadose, em 
seguida, retorne para a caixa de diálogo selecionar fonte de dados . 
 
Se a sua Tabela Dinâmica é baseada em uma conexão com um intervalo ou uma 
tabela no Modelo de Dados, você pode escolhar outra conexão ou tabela de Modelo 
de Dados na guia Tabelas. Porém, se sua Tabela Dinâmica é baseada no Modelo 
de Dados de Pasta de Trabalho, não é possível mudar a fonte de dados. 
https://support.office.com/pt-br/article/powerpivot-an%C3%A1lise-e-modelagem-de-dados-sofisticadas-no-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045
https://support.office.com/pt-br/article/powerpivot-an%C3%A1lise-e-modelagem-de-dados-sofisticadas-no-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045
https://support.office.com/pt-br/article/powerpivot-an%C3%A1lise-e-modelagem-de-dados-sofisticadas-no-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045
https://support.office.com/pt-br/article/use-tabelas-m%C3%BAltiplas-para-criar-uma-tabela-din%C3%A2mica-b5e3ff48-2921-4e29-be15-511e09b5cf2d
https://support.office.com/pt-br/article/use-tabelas-m%C3%BAltiplas-para-criar-uma-tabela-din%C3%A2mica-b5e3ff48-2921-4e29-be15-511e09b5cf2d
https://support.office.com/pt-br/article/use-tabelas-m%C3%BAltiplas-para-criar-uma-tabela-din%C3%A2mica-b5e3ff48-2921-4e29-be15-511e09b5cf2d
EXCEL AVANÇADO 
 
 
 
26 
 
 
1. Selecione a conexão desejada e clique em Abrir. 
2. Clique em Apenas Criar Conexão. 
 
3. Clique em Propriedades e clique na guia Definição. 
EXCEL AVANÇADO 
 
 
 
27 
 
 
4. Se seu arquivo de conexão. odc foi movido, navegue até o novo local na 
caixa Arquivo de conexão. 
5. Se for necessário alterar valores na caixa Cadeia de conexão, contate o 
administrador de seu banco de dados. 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
28 
 
7- ALTERAR EXIBIÇÃO DE VALORES 
 
Por padrão, o Microsoft Office Excel determina os valores mínimo e máximo de 
escala do eixo vertical (valor) eixo, também conhecida como eixo y, quando você 
cria um gráfico. No entanto, você pode personalizar a escala para atender melhor às 
suas necessidades. Quando os valores que são plotados no gráfico abrangem uma 
gama muito grande, você também pode alterar o eixo dos valores em uma escala 
logarítmica, também conhecida como escala log. 
Para alterar a escala de outros eixos em um gráfico, confira Alterar a escala do eixo 
horizontal (categoria) em um gráfico ou Alterar a escala do eixo de profundidade 
(série) em um gráfico. 
1. Em um gráfico, clique no eixo de valor que você deseja alterar ou execute o seguinte 
procedimento para selecionar o eixo de uma lista de elementos do gráfico: 
a. Clique em qualquer lugar no gráfico. 
Isso exibe as Ferramentas de gráfico, adicionando as guias Design e formato. 
b. Na guia Formatar, no grupo Seleção Atual, clique na seta ao lado da 
caixa Elementos do Gráfico e clique em Eixo Vertical (Valor). 
 
2. Na guia Formatar, no grupo Seleção Atual, clique em Seleção de Formato. 
3. Na caixa de diálogo Formatar Eixo, clique em Opções de Eixo e siga um ou mais 
destes procedimentos: 
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-horizontal-categoria-em-um-gr%C3%A1fico-637897f6-0d51-4ec5-bef9-25d2c83a8450
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-horizontal-categoria-em-um-gr%C3%A1fico-637897f6-0d51-4ec5-bef9-25d2c83a8450
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-horizontal-categoria-em-um-gr%C3%A1fico-637897f6-0d51-4ec5-bef9-25d2c83a8450
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-de-profundidade-s%C3%A9rie-em-um-gr%C3%A1fico-73f15b8c-b81a-4d11-accc-6e95f3d85d14
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-de-profundidade-s%C3%A9rie-em-um-gr%C3%A1fico-73f15b8c-b81a-4d11-accc-6e95f3d85d14
EXCEL AVANÇADO 
 
 
 
29 
 
Importante As seguintes opções de escala estão disponíveis apenas quando um 
eixo de valor é selecionado. 
a. Para alterar o número no qual o eixo vertical (valor) inicia ou termina, para a 
opção mínimo ou máximo, digite um número diferente na caixa mínimo ou na 
caixa máximo. Você pode clicar em Redefinir para colocá-lo novamente para seu 
valor original, se necessário. 
b. Para alterar o intervalo de escala marcas e linhas de grade do gráfico, para a opção 
de unidade principal ou unidade secundária, digite um número diferente na 
caixa unidade principal ou unidade secundária. Você pode clicar 
em Redefinir para colocá-lo novamente para seu valor original, se necessário. 
c. Para inverter a ordem dos valores, marque a caixa de seleção Valores em ordem 
inversa. 
Observação Quando você altera a ordem dos valores no eixo vertical (valor) de 
baixo para cima, os rótulos de categoria no eixo horizontal (categoria) são invertidos 
da parte inferior para a parte superior do gráfico. Da mesma forma, quando você 
altera a ordem das categorias da esquerda para a direita, os rótulos de valor são 
invertidos da esquerda para a direita do gráfico. 
d. Para alterar o eixo de valor logarítmico, marque a caixa de seleção Escala 
logarítmica. 
Observação Uma escala logarítmica não pode ser usada para valores negativos 
ou zero. 
e. Para alterar as unidades de exibição no eixo de valor, na lista Unidades de 
exibição, selecione as unidades desejadas. 
Para mostrar um rótulo que descreva as unidades, marque a caixa de 
seleção Mostrar rótulos das unidades de exibição no gráfico. 
Dica Alterar a unidade de exibição é útil quando os valores do gráfico são números 
grandes que você deseja que sejam exibidos de forma mais curta e legível no eixo. 
Por exemplo, você pode exibirgráficos de valores que variam de 1.000.000 a 
EXCEL AVANÇADO 
 
 
 
30 
 
50.000.000 como 1 a 50 no eixo e mostram um rótulo que indica que as unidades 
são expressas em milhões. 
f. Para alterar o posicionamento das marcas de escala do eixo marcas e etiquetas, em 
marcas de escala, selecione qualquer uma das opções nas caixas tipo 
principal ou secundárias. 
g. Clique na caixa suspensa em rótulos e escolha uma posição de rótulo. 
h. Para alterar o ponto onde deseja que o eixo horizontal (categoria) cross ao eixo 
vertical (valor), em base cruza em, clique em valor do eixo e digite o número 
desejado na caixa de texto. Ou, clique em valor de eixo máximo para especificar 
que o eixo horizontal (categoria) cruza o eixo vertical (valor) no valor mais alto no 
eixo. 
Observação Quando você clica em Valor de eixo máximo, os rótulos de 
categoria são movidos para o lado oposto do gráfico. 
EXCEL AVANÇADO 
 
 
 
31 
 
 
Dicas 
 Quando um gráfico exibe um eixo vertical secundário (valor), você também pode 
alterar a escala do eixo. Para saber mais sobre como exibir um eixo vertical 
secundário, confira Adicionar ou remover um eixo secundário em um gráfico. 
 Gráficos de Dispersão (xy) e gráficos de bolhas mostram valores no eixo horizontal 
(categoria) e o eixo vertical (valor), enquanto os gráficos de linhas mostram valores 
https://support.office.com/pt-br/article/adicionar-ou-remover-um-eixo-secund%C3%A1rio-em-um-gr%C3%A1fico-no-excel-91da1e2f-5db1-41e9-8908-e1a2e14dd5a9
EXCEL AVANÇADO 
 
 
 
32 
 
apenas do eixo vertical (valor). Essa diferença é importante para determinar qual tipo 
de gráfico a ser usado. Porque a escala do gráfico de linhas é horizontal eixo 
(categorias) não pode ser alterado quanto a escala do eixo vertical (valor) que é 
usado no gráfico de dispersão (xy), considere usar um gráfico de dispersão (xy) em 
vez de um gráfico de linhas, se você precisar alterar a escala do que eixo, ou exibi-lo 
como uma escala logarítmica. 
 Após alterar a escala do eixo, convém alterar a maneira como o eixo é formatado. 
Para saber mais, confira Alterar a exibição dos eixos do gráfico. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
https://support.office.com/pt-br/article/alterar-a-exibi%C3%A7%C3%A3o-dos-eixos-do-gr%C3%A1fico-422c97af-1483-4bad-a3db-3a9ef630b5a9
EXCEL AVANÇADO 
 
 
 
33 
 
8- RELATÓRIOS BASEADOS EM DETALHE DE VALORES 
 
Em uma Tabela Dinâmica ou Gráfico Dinâmico, é possível expandir ou recolher em 
qualquer nível de detalhes de dados, e até mesmo para todos os níveis em uma 
operação. Você também pode expandir ou recolher até o nível seguinte de detalhes. 
Por exemplo, a partir de um nível de país/região, você pode expandir para um nível 
de cidade que expande o nível estado/província e cidade. Isso pode economizar 
tempo ao trabalhar com vários níveis de detalhes. Além disso, também é possível 
expandir ou recolher todos os membros de cada campo de uma fonte de dados de 
processamento analítico online (OLAP). 
Também é possível exibir os detalhes usados para agregar o valor em um campo de 
valor. 
 
Expandir ou recolher para diferentes níveis de detalhes 
 
Expandir ou recolher níveis em uma Tabela Dinâmica 
Em uma Tabela Dinâmica, siga um destes procedimentos: 
 Clique no botão expandir ou recolher ao lado do item que você deseja expandir ou 
recolher. 
Observação: Se os botões expandir ou recolher não são visíveis, confira a 
seção mostrar ou ocultar os botões expandir e recolher em uma tabela 
dinâmica neste tópico. 
 Clique duas vezes no item que você deseja expandir ou recolher. 
 Clique com o botão direito do mouse no item, clique em Expandir/Recolher e 
siga um destes procedimentos: 
o Para exibir os detalhes do item atual, clique em Expandir. 
javascript:
javascript:
javascript:
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-d70d7e70-d230-4d45-81db-1f5e39bcb394#bm1c
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-d70d7e70-d230-4d45-81db-1f5e39bcb394#bm1c
EXCEL AVANÇADO 
 
 
 
34 
 
o Para ocultar os detalhes do item atual, clique em Recolher. 
o Para ocultar os detalhes de todos os itens em um campo, clique em Recolher Campo 
Inteiro</c0>. 
o Para exibir os detalhes de todos os itens em um campo, clique em Expandir Campo 
Inteiro. 
o Para ver até o nível seguinte de detalhes, clique em Expandir para "< nome do 
campo>"</c0>. 
o Para ocultar até o nível seguinte de detalhes, clique em Recolher para "< nome do 
campo>". 
Expandir ou recolher níveis em um Gráfico Dinâmico 
Em um Gráfico Dinâmico, clique com o botão direito do mouse</c0> no rótulo da 
categoria que você deseja exibir ou ocultar detalhes do nível, clique 
em Expandir/Recolher e, em seguida, siga um destes procedimentos: 
 Para exibir os detalhes do item atual, clique em Expandir. 
 Para ocultar os detalhes do item atual, clique em Recolher. 
 Para ocultar os detalhes de todos os itens em um campo, clique em Recolher 
Campo Inteiro. 
 Para exibir os detalhes de todos os itens em um campo, clique em Expandir Campo 
Inteiro. 
 Para ver até o nível seguinte de detalhes, clique em Expandir para "< nome do 
campo>". 
 Para ocultar até o nível seguinte de detalhes, clique em Recolher para "< nome do 
campo>". 
Exibir ou ocultar os botões expandir e recolher em uma Tabela Dinâmica 
Os botões expandir e recolher são exibidos por padrão, mas você pode ocultá-los 
(por exemplo, para que eles não apareçam em um relatório impresso). Certifique-se 
EXCEL AVANÇADO 
 
 
 
35 
 
de que esses botões sejam exibidos para poder expandir ou recolher os níveis de 
detalhes no relatório. 
 Em Excel 2016 e Excel 2013: Na guia Analisar, no grupo Exibir, clique nos Botões 
+/- para mostrar ou ocultar os botões expandir e recolher. 
 Em Excel 2010: Na guia Opções, no grupo Exibir, clique nos Botões +/- para 
mostrar ou ocultar os botões expandir e recolher. 
 
 Em Excel 2007: Na guia Opções, no grupo Exibir/Ocultar, clique nos Botões +/-
 para mostrar ou ocultar os botões expandir e recolher. 
Observação: Os botões expandir e recolher estão disponíveis apenas para campos 
com dados de detalhes. 
Exibir ou ocultar detalhes de um campo de valor em uma Tabela Dinâmica 
Por padrão, a opção para exibir detalhes de um campo de valor em uma Tabela 
Dinâmica está ativada. Para impedir que outras pessoas vejam esses dados, você 
pode desativá-lo. 
Exibir detalhes do campo de valor 
 Em uma Tabela Dinâmica, siga um destes procedimentos: 
o Clique com o botão direito do mouse no campo na área de valores na Tabela 
Dinâmica e depois clique em Exibir Detalhes. 
o Clique duas vezes em um campo na área de valores da Tabela Dinâmica. 
Os dados de detalhes em que o campo de valor é baseado são colocados em uma 
nova planilha. 
EXCEL AVANÇADO 
 
 
 
36 
 
Ocultar detalhes do campo de valor 
 Clique com o botão direito do mouse</c0> na guia da planilha com os dados do 
campo de valor e, em seguida, clique em Ocultar ou Excluir. 
Desabilitar ou habilitar a opção para exibir detalhes do campo de valor 
1. Clique em qualquer lugar na Tabela Dinâmica. 
2. Na guia Opções ou Analisar (dependendo da sua versão do Excel) na faixa de 
opções, no grupo Tabela Dinâmica, clique em Opções. 
 
3. Na caixa de diálogo Opções da Tabela Dinâmica, clique na guia Dados. 
4. Em Dados da Tabela Dinâmica</c0>, marque ou desmarque a caixa de 
seleção Habilitar a exibição de detalhes para desabilitar ou habilitar essa opção. 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
37 
 
9- AS DEZ MAIS 
 
1. Seleção avançada 
Com o prático recurso ―Localizar‖, o Microsoft Excel, assim como os demais 
programas do Office, pode encontrar rapidamente itens e termos em um documento. 
Porém, com o objetivo de editar múltiplos dados simultaneamente,o aplicativo 
permite selecionar todos. 
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 
Passo 2. Na janela ―Localizar e substituir‖, escreva o termo que você deseja 
encontrar e pressione ―Localizar tudo‖; 
EXCEL AVANÇADO 
 
 
 
38 
 
 
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; 
 
EXCEL AVANÇADO 
 
 
 
39 
 
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 
2. Transposição avançada 
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; 
EXCEL AVANÇADO 
 
 
 
40 
 
 
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; 
 
EXCEL AVANÇADO 
 
 
 
41 
 
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 
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‖; 
EXCEL AVANÇADO 
 
 
 
42 
 
 
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 
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; 
EXCEL AVANÇADO 
 
 
 
43 
 
 
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 
EXCEL AVANÇADO 
 
 
 
44 
 
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 
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...‖; 
EXCEL AVANÇADO 
 
 
 
45 
 
 
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 
EXCEL AVANÇADO 
 
 
 
46 
 
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 
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; 
EXCEL AVANÇADO 
 
 
 
47 
 
 
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; 
 
EXCEL AVANÇADO 
 
 
 
48 
 
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 
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‖); 
EXCEL AVANÇADO 
 
 
 
49 
 
 
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; 
 
EXCEL AVANÇADO 
 
 
 
50 
 
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 
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. 
Passo1. 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...‖; 
EXCEL AVANÇADO 
 
 
 
51 
 
 
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); 
 
EXCEL AVANÇADO 
 
 
 
52 
 
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 
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‖; 
EXCEL AVANÇADO 
 
 
 
53 
 
 
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 
EXCEL AVANÇADO 
 
 
 
54 
 
Passo 3. Na janela ―Suplementos‖, marque a alternativa ―Ferramentas de análise‖ e 
aperte ―OK‖; 
 
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. 
 
EXCEL AVANÇADO 
 
 
 
55 
 
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 
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; 
EXCEL AVANÇADO 
 
 
 
56 
 
 
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. 
 
EXCEL AVANÇADO 
 
 
 
57 
 
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 
Passo 2. Na janela Filtro Avançado, determine como ―Intervalo da lista‖ toda a 
planilha que será utilizada; 
EXCEL AVANÇADO 
 
 
 
58 
 
 
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‖; 
 
EXCEL AVANÇADO 
 
 
 
59 
 
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 
Passo 5. O Filtro Avançado formará uma nova tabela só com as linhas que 
contenham o termo da filtragem 
 
EXCEL AVANÇADO 
 
 
 
60 
 
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 
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; 
EXCEL AVANÇADO 
 
 
 
61 
 
 
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; 
EXCEL AVANÇADO 
 
 
 
62 
 
 
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. 
 
EXCEL AVANÇADO 
 
 
 
63 
 
A formula apresentará imediatamente os resultados — Foto: Reprodução/Daniel 
Ribeiro 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
EXCEL AVANÇADO 
 
 
 
64 
 
REFERÊNCIAS 
 
https://help.tableau.com/current/pro/desktop/pt-
br/calculations_calculatedfields_formulas.htm>acesso em 28/02/2020 
 
https://support.office.com/pt-br/article/subtotal-fun%C3%A7%C3%A3o-
subtotal-7b027003-f060-4ade-9040-e478765b9939>acesso em 28/02/2020 
 
https://support.office.com/pt-br/article/inserir-subtotais-em-uma-lista-de-dados-
em-uma-planilha-7881d256-b4fa-4f81-b71e-b0a3d4a52b3a>acesso em 
28/02/2020 
 
https://support.office.com/pt-pt/article/fun%C3%A7%C3%B5es-de-base-de-
dados-refer%C3%AAncia-ad87e69b-fc20-4d3d-9d52-d7dc023f5c23>acesso 
em 28/02/2020 
 
https://rockcontent.com/blog/tabela-dinamica/>acesso em 28/02/2020 
 
https://support.office.com/pt-br/article/atualizar-os-dados-da-tabela-
din%C3%A2mica-6d24cece-a038-468a-8176-8b6568ca9be2>acesso em 
28/02/2020 
 
https://support.office.com/pt-br/article/alterar-os-dados-de-origem-de-uma-
tabela-din%C3%A2mica-afd93524-f7de-432c-84d0-
3896fbbc2577>acesso em 28/02/2020 
 
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-vertical-valor-
em-um-gr%C3%A1fico-05973661-e56a-4486-a9f3-
f9ce41df0021>acesso em 28/02/2020 
 
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-
d70d7e70-d230-4d45-81db-1f5e39bcb394>acesso em 28/02/2020 
https://help.tableau.com/current/pro/desktop/pt-br/calculations_calculatedfields_formulas.htm%3eacesso
https://help.tableau.com/current/pro/desktop/pt-br/calculations_calculatedfields_formulas.htm%3eacesso
https://support.office.com/pt-br/article/subtotal-fun%C3%A7%C3%A3o-subtotal-7b027003-f060-4ade-9040-e478765b9939%3eacesso
https://support.office.com/pt-br/article/subtotal-fun%C3%A7%C3%A3o-subtotal-7b027003-f060-4ade-9040-e478765b9939%3eacesso
https://support.office.com/pt-br/article/inserir-subtotais-em-uma-lista-de-dados-em-uma-planilha-7881d256-b4fa-4f81-b71e-b0a3d4a52b3a%3eacesso
https://support.office.com/pt-br/article/inserir-subtotais-em-uma-lista-de-dados-em-uma-planilha-7881d256-b4fa-4f81-b71e-b0a3d4a52b3a%3eacesso
https://support.office.com/pt-pt/article/fun%C3%A7%C3%B5es-de-base-de-dados-refer%C3%AAncia-ad87e69b-fc20-4d3d-9d52-d7dc023f5c23%3eacesso
https://support.office.com/pt-pt/article/fun%C3%A7%C3%B5es-de-base-de-dados-refer%C3%AAncia-ad87e69b-fc20-4d3d-9d52-d7dc023f5c23%3eacesso
https://rockcontent.com/blog/tabela-dinamica/%3eacesso
https://support.office.com/pt-br/article/atualizar-os-dados-da-tabela-din%C3%A2mica-6d24cece-a038-468a-8176-8b6568ca9be2%3eacesso
https://support.office.com/pt-br/article/atualizar-os-dados-da-tabela-din%C3%A2mica-6d24cece-a038-468a-8176-8b6568ca9be2%3eacesso
https://support.office.com/pt-br/article/alterar-os-dados-de-origem-de-uma-tabela-din%C3%A2mica-afd93524-f7de-432c-84d0-3896fbbc2577%3eacesso
https://support.office.com/pt-br/article/alterar-os-dados-de-origem-de-uma-tabela-din%C3%A2mica-afd93524-f7de-432c-84d0-3896fbbc2577%3eacesso
https://support.office.com/pt-br/article/alterar-os-dados-de-origem-de-uma-tabela-din%C3%A2mica-afd93524-f7de-432c-84d0-3896fbbc2577%3eacesso
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-vertical-valor-em-um-gr%C3%A1fico-05973661-e56a-4486-a9f3-f9ce41df0021%3eacesso
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-vertical-valor-em-um-gr%C3%A1fico-05973661-e56a-4486-a9f3-f9ce41df0021%3eacesso
https://support.office.com/pt-br/article/alterar-a-escala-do-eixo-vertical-valor-em-um-gr%C3%A1fico-05973661-e56a-4486-a9f3-f9ce41df0021%3eacesso
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-d70d7e70-d230-4d45-81db-1f5e39bcb394%3eacesso
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-d70d7e70-d230-4d45-81db-1f5e39bcb394%3eacesso
https://support.office.com/pt-br/article/expandir-recolher-ou-mostrar-detalhes-em-uma-tabela-din%C3%A2mica-ou-gr%C3%A1fico-din%C3%A2mico-d70d7e70-d230-4d45-81db-1f5e39bcb394%3eacesso
EXCEL AVANÇADO 
 
 
 
65 
 
https://www.techtudo.com.br/dicas-e-tutoriais/2018/08/10-dicas-e-truques-
avancados-de-excel.ghtml>acesso em 28/02/2020 
https://www.techtudo.com.br/dicas-e-tutoriais/2018/08/10-dicas-e-truques-avancados-de-excel.ghtml%3eacesso
https://www.techtudo.com.br/dicas-e-tutoriais/2018/08/10-dicas-e-truques-avancados-de-excel.ghtml%3eacesso