Excel Avançado 2

Prévia do material em texto

Tema 02
Aplicar Conceitos 
Dinâmicos do Excel
Excel - Nível Avançado
2
Durante a conversa sobre esse tema, utilizaremos alguns recursos que são extremamente 
utilizados no Excel por usuários que já possuem algum conhecimento avançado. Não 
desvalorize o que aprendeu nos cursos de Introdução ao Excel e Excel Intermediário. Eles 
são pré-requisitos para conhecer os próximos recursos.
Assista todos os vídeos e explicações com muita tranquilidade. Caso tenha alguma 
dificuldade, pause o vídeo e faça novamente os exercícios. Para acessá-los, entre nas 
telas interativas do curso.
Excel - Nível Avançado
Excel - Nível Avançado
3
Anotações
O gráfico é uma ilustração visual de dados ou valores numéri-
cos de uma planilha. É possível vincular os dados de uma planilha 
para a representação em um gráfico e a cada alteração dos dados 
o gráfico é modificado (Andrade, 2011). 
Exemplo de Gráfico
Exemplo de Minigráfico
2.1. Gráficos 
O Excel possui diversos tipos 
de gráficos e minigráficos. 
Estudaremos os principais 
modelos.
Excel - Nível Avançado
Excel - Nível Avançado
4
O gráfico é composto por diversas partes . É possível editar e 
formatar cada uma delas.
 Veremos quais são a partir de um exemplo. 
1. Área do Gráfico: espaço total do gráfico.
2. Área de plotagem: representação em barras, linhas, pizza 
ou outros.
3. Eixo vertical (valor): o eixo da ordenadas (y). Série respon-
sável pela entrada das legendas.
4. Eixo horizontal (categoria): valores que determinam o 
eixo das abcissas (x). Rótulo das categorias.
5. Legenda: área que identifica os rótulos de cada dado do 
gráfico.
6. Título: texto que nomeia o gráfico.
1
2
3
4
5
6
Como criar um gráfico?
Para criar um gráfico no Excel 
é muito simples. Você pode 
até mesmo criá-lo e depois 
modificar conforme sua 
necessidade. 
Excel - Nível Avançado
Excel - Nível Avançado
5
AnotaçõesNa guia Inserir, temos o grupo Gráficos:
• Passo 1: Selecione toda a planilha (Ctrl+Shift+*).
Fique Atento
Utilize o arquivo
 Gráficos e Tipos de Gráficos.xlsx 
para criarmos alguns gráficos.
Na planilha Série de dados 
vamos criar o primeiro gráfico. 
Perceba que temos a receita 
de alguns cursos em uma série 
histórica.
Como temos uma alteração de 
valores, o gráfico tipo coluna será 
ideal para compararmos esse tipo 
de alteração.
Criando o gráfico:
Excel - Nível Avançado
Excel - Nível Avançado
6
Anotações• Passo 2: Na guia Inserir, grupo gráficos, selecione a pri-
meira opção.
Seu gráfico já está pronto.
Ampliar 
conhecimento
Pode-se criar um gráfico em 
uma nova guia. Para isso você 
deve selecionar sua série de 
dados e apertar a tecla F11.
Excel - Nível Avançado
Excel - Nível Avançado
7
AnotaçõesPara inserir um minigráfico, que é um pequeno gráfico 
dentro de um célula, siga os passos:
• Passo 4: Aperte Ok.
• Passo 3: Selecione a célula que será inserido o minigráfico.
• Passo 2: Na guia Inserir, grupo minigráficos, selecione qual 
o tipo deseja.
• Passo 1: Selecione os valores que são parte do seu 
minigráfico.
Fique Atento
Note que uma nova guia 
está disponível. Você poderá 
configurar esse minigráfico 
conforme a sua necessidade. 
Excel - Nível Avançado
Excel - Nível Avançado
8
Anotações
Qualquer parte do gráfico é editável. Veja a explicação no vídeo 
sobre as possibilidades de formatação de gráficos existentes.
Formatação de Gráfico
Diferenças dos Gráficos
0%
50%
100%
01 02 03 04 05 06
Formatação de gráficos
Para ter acesso ao 
vídeo, acesse: https://
player.vimeo.com/
video/291325023
Veja as diferenças entre os três 
tipos de gráficos de colunas. 
Alguns outros tipos de gráficos 
(como de barras e de linhas) 
funcionam da mesma forma.
Excel - Nível Avançado
Excel - Nível Avançado
9
Anotações
Gráfico de colunas agrupadas: é ideal para comparar dife-
rença de valores, mas não é possível observar o total gerado por 
todos os cursos.
Gráfico de colunas empilhadas: Também serve para analisar 
diferenças. Mas, é ideal para perceber o total gerado em série 
histórica.
Excel - Nível Avançado
Excel - Nível Avançado
10
Anotações
Gráfico de colunas 100% empilhadas: Parecido com colunas 
empilhadas, mas apresenta o resultado em percentual 100% da 
série histórica. Não se observam os valores absolutos.
Gráfico Pizza: ideal para analisar percentual de produtos ou 
serviços de uma só série de valores.
Veremos agora outros tipos de gráficos:
Excel - Nível Avançado
Excel - Nível Avançado
11
Anotações
Gráfico linha: ideal quando se tem uma série histórica grande. 
Mostra tendência de determinada situação, possíveis sazonalida-
des de um evento (quando um mesmo comportamento se repete 
várias vezes, tal como o período de férias de verão).
A Tabela Dinâmica é um recurso que possibilita agrupar dados 
de uma planilha com muitas informações. Com alguns cliques é 
possível criar uma tabela dinâmica para facilitar a análise, a com-
paração e a criação de diversos relatórios. 
2.2 Tabela Dinâmica 
Animação: Tabela dinâmica Para ter acesso ao vídeo, 
veja nas telas interativas 
do curso.
Para ter acesso às Questões para Praticar, 
veja nas telas interativas do curso.
Excel - Nível Avançado
Excel - Nível Avançado
12
AnotaçõesEsta tabela tem o nome de dinâmica, pois é possível alterar 
a sua ordem ou mudar a seleção dos dados só arrastando suas 
partes e, assim, obter outra forma de apresentar os dados.
01 02 03 04
Explorar
Para criar uma Tabela Dinâmica, temos que seguir 4 premissas.
• Premissa número 1 Os dados na sua tabela devem ser organizadas em título 
e valores. Na primeira linha, deve estar o título e abaixo o valor, como se fosse um 
banco de dados vertical.
• Premissa número 2 Mantenha os valores padronizados. Tenha a certeza de 
que na sua planilha os registros sejam escritos da mesma maneira. Lembre-se 
que, para o Excel, Forró(iniciante) é um valor diferente de Forró (iniciante). Tenha 
cuidado com os espaços antes e depois das palavras, eles não são visíveis, mas o 
Excel os considera.
• Premissa número 3 Não tenha células vazias nos títulos. Evite ter células 
vazias nos valores também, pois poderá dificultar sua análise.
• Premissa número 4 Não utilize células mescladas nos títulos e valores.
Utilize o arquivo Tabela Dinâmica.xlsx.
Excel - Nível Avançado
Excel - Nível Avançado
13
Anotações
• Passo 1: Na guia Inserir, grupo tabela, clique em tabela 
dinâmica.
Após a conscientização das 
premissas, utilizaremos a Planilha 
Tabela Dinâmica (do arquivo Tabela 
Dinâmica.xlsx) para criarmos a 
primeira tabela dinâmica. 
Em qualquer célula preenchida 
da sua planilha, siga os passos 
descritos a seguir.
• Passo 2: Na guia inserir, grupo tabela, clique em tabela 
dinâmica.
• Passo 3: Esse, por incrível que pareça é o passo mais difícil 
em criar uma tabela dinâmica. Confira a Tabela/Intervalo 
(o Excel selecionou toda a planilha) e deixe marcado para 
ser criado em uma nova planilha.
• Passo 4: Aperte ok.
Excel - Nível Avançado
Excel - Nível Avançado
14
A base da tabela dinâmica foi criada. Agora, vamos entender 
como fazemos para criar relatórios.
Note que uma nova planilha foi 
criada. Nessa planilha, algumas 
novas ferramentas foram 
habilitadas. 
Layout da tabela dinâmica: Espaço que será destinado para 
os relatórios.
Lista de campos: Inicialmente, são os títulos da planilha base.
Áreas dinâmicas em 4 quadrantes:
Quadrante número 1: Filtros do relatório. Podemos incluir um 
campo que aparecem na Lista de Campos para realizar um ou 
mais filtros.
Quadrante número 2: Rótulo de colunas. A cada nova inclusão 
de um campo o seu relatório será acrescido em maisuma coluna 
para cada um dos valores que aparecem no campo selecionado.
Para ter acesso às 
ferramentas da planilha, 
utilize as telas interativas 
do curso.
Excel - Nível Avançado
Excel - Nível Avançado
15
AnotaçõesQuadrante número 3: Rótulo de linha. A cada nova inclusão de 
um campo o seu relatório será acrescido em mais uma linha para 
cada um dos valores que aparecem no campo selecionado.
Quadrante número 4: Valores. Esse quadrante são os valores, 
números ou até mesmo textos para contagem.
Agora, precisamos saber qual 
informação desejamos extrair 
do nosso banco de dados, por 
exemplo, o valor total pago por 
curso. 
Para criar uma tabela dinâmica 
basta apenas arrastar um 
campo para os quadrantes. Veja 
o exemplo no vídeo a seguir.
Percebeu como é fácil 
manipular os campos e como 
existe a facilidade de fazer e 
refazer os cálculos buscando o 
relatório conforme desejado? 
Tabela dinâmica Para ter acesso ao vídeo, 
entre nas telas interativas 
do curso.
Para ter acesso ao vídeo 
Detalhes da Tabela Dinâmica 
e realizar as Questões para 
Praticar, veja nas telas 
interativas do curso.
Excel - Nível Avançado
Excel - Nível Avançado
16
Anotações
• Passo 1: Selecione o nome de um campo localizado em 
um dos quadrantes (ele ficará colorido) e clique na seta na 
parte direita do nome do campo.
Fique Atento
Não é possível sobrepor uma tabela dinâmica. Caso 
você crie uma tabela dinâmica próxima a outra, o Excel 
apontará o seguinte aviso:
Então, quando for criar uma tabela dinâmica em uma 
planilha já existente, reserve um espaço de algumas 
células para não ocorrer esse aviso.
Uma tabela dinâmica pode ser formatada 
de várias formas. Vamos aprender 
inicialmente como formatar os valores. 
Eu já aprendi que, ao formatar valores na tabela 
dinâmica, o ideal é utilizar o campo presente no 
quadrante e definir qual o tipo de formatação você 
necessita. Assim, a formatação fica preservada 
quando forem feitas alterações na tabela. Veja os 
passos para acessar a formatação nas próximas telas.
Excel - Nível Avançado
Excel - Nível Avançado
17
Anotações• Passo 2: Selecione a opção Configurações do Campo de 
Valor.
• Passo 3: Clique em Formato do Número.
• Passo 4: Pronto, agora estamos no Formatar Células. 
Selecione o formato moeda.
Excel - Nível Avançado
Excel - Nível Avançado
18
Anotações
Observe as Configurações do Campo de Valor
Nesta janela pode-se determinar como a função será aplica-
da no campo deste quadrante. O padrão do Excel é soma para 
números e contagem para textos. 
Além disso, pode-se alterar o nome criado no título desse 
campo.
• As opções de formatação da Tabela Dinâmica surgem com 
as duas novas guias que podem ser exploradas.
• Na guia Design, pode-se alterar o estilo e o layout da 
tabela dinâmica.
Os comandos de Layout podem ser 
utilizados para alterar posição de dados, 
definir se subtotais e totais gerais serão 
exibidos ou não e, em que posição.
Excel - Nível Avançado
Excel - Nível Avançado
19
Anotações
Veja o exemplo e tente reproduzir:
• Passo 1: Selecione a Tabela Dinâmica com os instrutores 
no quadrante Rótulo de Linha e os valores pagos no qua-
drante Valores.
• Passo 2: Na guia Opções, selecione a opção Cálculo, depois 
Campos, Itens e Conjuntos e, então, Campo calculado.
Caso queira inserir informações calculadas a 
partir dos dados existentes em campos, você 
pode utilizar um relatório de tabela dinâmica 
e criar um campo que não existe na planilha 
base. Vamos a um exemplo: na escola de dança 
Vem Requebrar os professores recebem 40% 
da mensalidade dos alunos. Queremos calcular 
os valores que os instrutores receberam. Esse 
cálculo pode ser feito no próprio relatório sem 
necessidade de criar uma coluna na planilha 
com os dados originais. 
Excel - Nível Avançado
Excel - Nível Avançado
20
Anotações• Passo 3: Dê o nome que desejar, Sugestão Comissão.
• Passo 4: Selecione o valor pago e aperte em Inserir campo.
• Passo 5: Multiplique por 40%.
• Passo 6: Aperte Ok. 
Pronto! Agora você já tem um novo 
campo. Pode utilizar todas as operações 
matemáticas e funções aprendidas nos 
cursos anteriores de Excel.
Fique Atento
As alterações feitas no banco de dados usado para 
criar a tabela dinâmica não provocam alteração 
direta nela. É preciso atualizar a tabela dinâmica 
utilizando o botão Atualizar, disponível na guia 
Opção, grupo Dados.
Excel - Nível Avançado
Excel - Nível Avançado
21
Agrupar Dados Na Tabela Dinâmica
Dicas de formatação da 
tabela dinâmica
Para ter acesso ao vídeo, 
entre nas telas interativas 
do curso.
Um recurso muito importante em 
tabela dinâmica é o de agrupar valores 
que contenham datas. Ao criar a tabela 
dinâmica priorizando campos de datas 
sobre outros campos, pode-se agrupá-las 
para melhor visualização.
Veja a tabela dinâmica:
Excel - Nível Avançado
Excel - Nível Avançado
22
Anotações• Ao clicar em uma data, na guia Opções, grupo Agrupar, a 
opção Agrupar Campo ficará disponível.
• Selecione de acordo com as informações que tiver em 
sua planilha. Neste caso, selecionaremos a opção Meses. 
Obteremos o seguinte relatório:
Para ter acesso às Questões para Praticar, 
veja nas telas interativas do curso.
Excel - Nível Avançado
Excel - Nível Avançado
23
AnotaçõesVamos relembrar