Excel
Básico
Professor: Eduardo Mureb Santos
Sumário
1 – Introdução ............................................................................................................................... 3
2 – Apresentando o Excel .............................................................................................................. 3
2.1 – Menus ............................................................................................................................... 3
2.2 – Conceitos Básicos ............................................................................................................. 6
3 – Fórmulas .................................................................................................................................. 8
3.1 – Conceitos básicos ............................................................................................................. 8
3.2 – Operadores de Texto ...................................................................................................... 11
4 – Funções.................................................................................................................................. 12
4.1 – Função Soma .................................................................................................................. 12
4.2 – Usando o assistente de função ...................................................................................... 12
4.3 – Função Máximo .............................................................................................................. 13
4.4 – Função Mínimo............................................................................................................... 14
4.5 – Função Média ................................................................................................................. 14
4.6 – Função Data de Hoje ...................................................................................................... 15
4.7 – Função SE ....................................................................................................................... 15
4.8 – Função PROCV ................................................................................................................ 17
5 – Gráficos.................................................................................................................................. 18
5.1 – Tabelas............................................................................................................................ 18
5.2 – Criando e editando Gráficos ........................................................................................... 19
6 – Tabelas Dinâmicas ................................................................................................................. 23
6.1 – Conceitos Básicos ........................................................................................................... 23
6.2 – Campos de uma Tabela Dinâmica .................................................................................. 23
6.3 – Construindo uma Tabela Dinâmica ................................................................................ 24
6.4 – Criando gráficos a partir de uma Tabela Dinâmica ........................................................ 26
7 – Outras Funcionalidades ......................................................................................................... 27
7.1 – Congelar Painéis ............................................................................................................. 27
7.2 – Ferramenta Classificar .................................................................................................... 29
1 – Introdução
O software Excel, que faz parte do pacote de programas do Microsoft Office, é um
programa dedicado a criação de planilhas de cálculos, além de fornecer gráficos, função de
banco de dados e outros. Podem ser criadas planilhas de cálculos para orçamentos, previsões e
planejamentos para investimentos futuros, diversos tipos de tabelas, controle de gastos,
controle de caixa, etc.
Existem hoje diferentes versões do Excel, e, à medida em que uma nova versão é
disponibilizada, novas funcionalidades são criadas com o objetivo de atender novas demandas
dos usuários. Entretanto, as funcionalidades e comandos básicos permanecem inalterados,
fazendo com que usuários de versões anteriores não fiquem “perdidos” em versões mais atuais.
O objetivo deste material consiste em introduzir o programa para novos usuários,
facilitando o aprendizado e servindo de guia para futuros trabalhos no programa.
Nota: As orientações contidas neste curso são válidas para qualquer versão do Microsoft
Excel (5.0, 97, 2000, XP, 2003, 2007 e 2013).
2 – Apresentando o Excel
Na imagem abaixo o layout principal do Excel 2013 é apresentado, com os seus
principais botões. Vale lembrar que para as versões mais antigas do Excel estes botões se
encontram na mesma posição, mas com layout um pouco diferente.
1 – Botão do Office e acesso rápido
2 – Menus
3 – Barra de fórmulas
4 – Nome da Célula
5 – Célula (A1)
6 – Planilhas
7 – Botões de visualização (normal, layout da página e da quebra de página)
8 – Botão de Zoom
2.1 – Menus
Como mostrado acima, existem 7 menus pré-definidos que em geral agrupam funções
semelhantes. Eles podem ser personalizados de acordo com o que o usuário deseja,
selecionando a seguinte opção: Arquivos >> Opções >> Personalizar Faixa de Opções:
Abaixo segue a descrição geral de cada menu:
Página Inicial:
Apresenta os principais botões de formatação de texto e da planilha (tipo e tamanho de
fonte, copiar e colar, formatar célula, inserir linhas e colunas, filtrar, classificar, etc.)
Inserir:
O menu Inserir permite ao usuário inserir diversas informações, tanto internas como
externas, tais como gráficos, imagens, tabelas, símbolos, etc.
Layout da Página:
É no menu Layout que é possível editar margens, orientação e tamanho da folha, inserir
quebras de texto, definir o que vai ser impresso, alterar largura e altura da célula, etc.
Fórmulas:
No menu Fórmulas se pode localizar comandos de gerenciamento dos nomes das
células, rastreamento de precedentes e dependentes e todas as funções e fórmulas que são
inseridas visando automatizar a pasta de tralho.
Dados:
No menu Dados se pode criar conexões com outras fontes, tais como: Access, Web e
outros tipos de conexões que o Excel oferece suporte. Adicionalmente, gerencia os dados,
criando filtros, classificando em ordem crescente/decrescente, removendo dados duplicados,
etc.
Revisão:
No menu Revisão, encontram-se comandos de comentários numa célula e revisão
ortográfica, além de comandos para proteger a planilha e a pasta de trabalho.
Exibição:
No menu Exibição, são encontradas as ferramentas de zoom, os botões de linhas de
grade, barra de fórmula e títulos, além do modo de exibição da pasta de trabalho e do botão de
gerenciamento de Macros.
2.2 – Conceitos Básicos
2.2.1 - Linha, Coluna e Célula
Ao abrir o Microsoft Excel é apresentada uma janela com três planilhas – Plan1, Plan2 e
Plan3. A planilha selecionada por padrão é a planilha Plan1, uma planilha vazia, onde existem
linhas e colunas dispostas de tal forma que podemos inserir informações dentro deste espaço
(célula).
Linha - dentro do Excel as linhas são identificadas com números no canto esquerdo da
tela, que vai de 0 a 1.048.576 (nas versões do Excel mais recentes).
Coluna - as colunas são identificadas com letras de A a Z e combinações de letras (AB,
AC, etc) até totalizarem 16.384 colunas.
Célula Ativa - é a célula exibida com uma borda em verde (ou negrito em versões
anteriores), indicando que a célula está selecionada. Os próximos dados digitados serão
inseridos nesta célula, ou o próximo comando escolhido será aplicado nesta célula. Se for
selecionada mais de uma célula, a primeira é a célula ativa, e as demais são destacadas.
2.2.2 - Manipulando planilhas
Renomeando uma planilha: Clique com o botão direito do mouse sobre o nome da
planilha (Por exemplo, “Plan1”) e no menu clique em Renomear (ou dê um clique duplo em cima
do “Plan1”). Basta digitar o novo nome e teclar ENTER.
Inserindo uma planilha: Clique com o botão direito do mouse sobre o nome de uma das
planilhas existentes, clique em “Inserir” e clique no ícone “Planilha”. Outra forma é clicar no
ícone “+” ao lado do nome das planilhas.
Movendo uma planilha: Clique sobre o nome da planilha a ser movida e arraste para
uma nova posição.
Excluindo uma planilha: Clique com o botão direito do mouse sobre o nome da planilha
a ser excluída e clique em Excluir.
2.2.3 - Manipulando linhas e colunas
Quando se cria uma tabela, pode-se deparar com a necessidade de inserir ou excluir
linhas e/ou colunas.
Excluindo linha/coluna: clique com o botão direito do mouse na linha ou coluna que se
deseja excluir e selecionar a opção Excluir. Para excluir usando o teclado, clicar Ctrl + - .
Inserindo linha/coluna: selecione a linha/coluna em que se deseja adicionar com o
botão direito do mouse e clique em Inserir. Para inserir usando o teclado, clicar Ctrl + Shift + +.
Na tabela abaixo são exibidos alguns comandos básicos pra quem prefere selecionar
células usando o teclado em vez do mouse:
Para Selecionar
Mover e selecionar uma célula acima, abaixo, à esquerda e à direita
Estender a seleção
Estender a seleção em um bloco
Estender a seleção para o início da linha
Estender a seleção para o fim da linha
Selecionar linhas inteiras
Selecionar colunas inteiras
Pressione
Teclas de setas de direção
SHIFT + teclas de direção
CTRL + SHIFT + teclas de setas
SHIFT + HOME
SHIFT + END
SHIFT + Barra de Espaço
CRTL + Barra de Espaço
2.2.4 - Manipulando células: Copiar e Colar
Para copiar e colar uma célula ou um intervalo de células, basta fazer: 1) marcar a célula
ou intervalo de células a ser copiado; 2) copiar para a memória, utilizando Ctrl + C ou o comando
Editar >> Copiar; 3) posicionar o cursor na célula onde o conteúdo deve ser copiado e 4) colar o
conteúdo utilizando Ctrl + V ou o comando Editar >> Colar.
2.2.5 - Manipulando células: Copiando dados dentro de uma linha ou coluna
Selecione as células que contêm os dados que você deseja copiar; depois arraste a alça
de preenchimento (veja abaixo) pelas células que você deseja preencher e, em seguida, solte o
botão do mouse.
Os valores ou as fórmulas existentes nas células que estão sendo preenchidas serão
substituídas.
Alça de
Preenchimento
Para preencher a célula ativa com o conteúdo da célula posicionada acima, pressione
Ctrl + D. Para preencher com o conteúdo da célula posicionada à esquerda, pressione Ctrl + R.
Se arrastar a alça de preenchimento para cima ou para a esquerda de uma seleção, e parar sobre
as células selecionadas sem ultrapassar a primeira coluna ou a linha superior, você excluirá os
dados da seleção.
3 – Fórmulas
3.1 – Conceitos básicos
Para construir as fórmulas do Excel, utiliza-se os seguintes operadores:
1. Sinais de Operações
+
Adição
Subtração
*
Multiplicação
/
Divisão
%
Percentagem
=
Igualdade
^
Exponenciação
2. Sinais de Condição
>
Maior que
<
Menor que
<>
Diferente de
>=
Maior e igual a
<= Menor e igual a
=
Igual a
Para realizar operações entre células, basta que se faça referência ao nome da célula
que o valor será adicionado sempre que ela for mencionada. Exemplo:
Observações: Toda fórmula no Excel sempre começa com sinal de igual para que o
programa entenda que é um cálculo e não um texto qualquer que se está digitando.
Não é usual colocar o valor das parcelas na célula como, por exemplo, =150+200, porque
estes valores podem sofrer alterações e o resultado não seria atualizado, sendo necessária a
correção da fórmula depois. Use sempre os endereços de célula (A1 e B1 como no exemplo),
pois assim, alterando-se os valores da célula o resultado será atualizado automaticamente.
As fórmulas podem se referenciar a outras células na mesma planilha (por exemplo: B3,
D4, etc), a células em outras planilhas da mesma pasta de trabalho ou a células em planilhas em
outras pastas de trabalho.
3.1.1 – Precedência de operadores
Para se realizar cálculos com mais de um operador, deve-se levar em conta que existe
uma ordem que o Excel realiza as operações. Ele funciona da forma abaixo:
Precedência de Operações
1º Raiz e potenciação
2º Multiplicação e Divisão
3º Adição e Subtração
O Excel faz os cálculos nesta ordem e não na ordem digitada.
3.1.2 – Usando o Autopreenchimento
É possível usar o auto preenchimento para copiar qualquer fórmula criada. Este
processo economiza muito tempo e é um dos principais recursos do Excel. Para isso basta criar
a primeira fórmula na célula desejada (normalmente a primeira da sua lista de valores), depois
clicar sobre ela e levar o ponteiro do mouse até o canto inferior direito. Após o cursor mudar
para uma cruz preta e fina, clicar e arrastar para copiar para outros itens da lista.
Arrastar para copiar a fórmula
3.1.3 – Endereços Absolutos e Relativos
Os endereços de células podem ser divididos em 2 tipos. Os endereços relativos como o
nome diz são referenciados pela posição da célula dentro da planilha. Quando copiados para
outras células estes endereços mudam para manter relação com sua posição original. Veja o
exemplo:
Fórmula Digitada na F1
Use o
autopreenchimento e
arraste para as demais
células
Para a célula
selecionada, as
referências mudaram
aumentando o número
da linha
Para endereços absolutos, o mesmo não muda quando a fórmula é copiada para outra
célula (procedimento de travar células). Para isso coloca-se um cifrão ($) antes da letra e outro
antes do número da célula. Por exemplo: $A$1. Use este endereço em valores de índice, que
serão usados para cálculos em toda a tabela de dados e que normalmente ficam em separado
na planilha. Exemplo:
Veja que o endereço
da célula não mudou;
ficou “travada” em B1
3.2 – Operadores de Texto
O operador de texto & é utilizado para concatenar texto. Por exemplo, se o nome do
contribuinte estiver na coluna A e o sobrenome na coluna B, pode-se utilizar o operador &, para
gerar o nome completo na coluna C. Para isso existe a seguinte fórmula:
=A1 & “ “ & B1
Observe que se utiliza um espaço em branco entre as aspas ( " " ). Este espaço em branco
é utilizado para que o sobrenome não fique " colado " com o nome.
Operador de Referência: Os operadores de referência combinam intervalos de células
para cálculos. Existem os operadores de intervalo, que produzem uma referência a todas as
células entre duas referências, incluindo ambas, como por exemplo: SOMA(A1:A30). Esta
fórmula irá retornar a soma de todos os valores contidos na faixa de célula A1 até A30.
Já o operador de união é utilizado para "unir" vários intervalos de células, de tal forma
que os mesmos sejam tratados como um único intervalo. Por exemplo, para se somar os valores
dos intervalos C10:C20, mais os valores do intervalo M5:M15 mais o valor da célula Z5 utiliza-se
a seguinte fórmula: = SOMA(C10:C20; M5:M15; Z5).
4 – Funções
Uma função é uma fórmula especial, pré-definida, que toma um ou mais valores (os
parâmetros), executa uma operação e produz um valor ou valores. As funções podem ser usadas
isoladamente ou como bloco de construção de outras fórmulas. Segue a descrição e o
funcionamento de algumas funções:
4.1 – Função Soma
Ex: =SOMA(B1:B10)
A função irá somar todos os valores que se encontram no endereço B1 até o endereço
B10. Os dois pontos indicam até, ou seja, soma de B1 até B10. A fórmula será sempre a mesma,
só mudará os endereços dos valores que você deseja somar. Outro exemplo:
Neste exemplo estamos somando todos os valores do endereço A1 até o endereço D1.
A fórmula seria digitada como no exemplo, e ao teclar ENTER o valor apareceria.
Outra maneira de você somar é utilizando o Botão da Autosoma.
Veja o exemplo:
Para trabalhar com o botão da Autosoma você deve fazer o seguinte:
1. Selecionar os valores que desejar somar.
2. Depois clique no Botão da Autosoma e ele mostrará o resultado.
4.2 – Usando o assistente de função
O assistente de função auxilia na montagem de uma função, colocando o nome da
função corretamente, abrindo e fechando o parênteses, mostrando também explicações sobre
a montagem e o resultado prévio do cálculo.
Para usar o assistente, clique no botão colar função.
Surgirá então o seguinte quadro:
Escolha a categoria e a função desejada e depois clique OK. Caso queira uma explicação
sobre o uso da função, clique no botão ajuda ( ? ) que está no canto inferior esquerdo do quadro.
Como exemplo vamos escolher a função SOMA. Veja o quadro que aparece em seguida:
No quadro acima ele soma o intervalo que vai do A1 até o D1 na caixa Num1 (para
selecionar o intervalo basta clicar na caixa antes do sinal de =). Na frente aparecem os valores
deste intervalo (5,4,6,9). A caixa Num2 é possível colocar mais um intervalo de células, ou seja
podemos somar vários intervalos de células intercalados. Para outras funções basta também
apenas selecionar a faixa de células.
4.3 – Função Máximo
Mostra o valor máximo de uma faixa de células.
Exemplo: Suponha que deseja saber qual a maior idade entre homens em uma tabela
de dados. Veja a fórmula no exemplo abaixo:
(A1:E1) – refere-se ao endereço dos valores onde você deseja ver qual é o maior valor.
No caso a resposta seria 63.
4.4 – Função Mínimo
Mostra o valor mínimo de uma faixa de células.
Exemplo: Suponha que deseja saber qual adulto tem o menor peso em uma tabela de
dados. Veja a fórmula no exemplo abaixo:
(A1:E1) – refere-se ao endereço dos valores onde você deseja ver qual é o menor valor.
No caso a resposta seria 75.
4.5 – Função Média
Calcula a média de uma faixa de valores.
Exemplo: Suponha que deseja saber qual a média de notas de uma prova numa tabela
de dados. Veja o exemplo abaixo:
(A1:E1) – refere-se notas da prova onde você deseja ver qual é a média. No caso a
resposta seria 6,8.
4.6 – Função Data de Hoje
Esta fórmula insere a data automática em uma planilha. Veja o exemplo
Esta fórmula é digitada conforme acima. Você só precisa colocar o cursor no local onde
deseja que fique a data e digitar =HOJE() e ela colocará automaticamente a data do sistema.
4.7 – Função SE
A função SE do Excel é sempre utilizada quando o valor de uma célula pode variar. A
tarefa da função SE é comparar o valor da célula com um critério estabelecido e retornar dois
resultados. Um se a comparação for verdadeira e outro se a comparação for falsa.
Exemplo:
5+2 > 8 – comparação falsa
2*3 = 6 – comparação verdadeira
(2+8) * 2 > 5+6 – comparação verdadeira.
Estas comparações são bem simples e conhecidas. No Excel funciona da mesma forma,
mas compara-se uma célula (ou seja, seu conteúdo) com um determinado valor.
No exemplo a seguir o candidato é reprovado caso sua média seja menor que 6. Sendo
assim, 6 é o critério para aprovação. Então compara-se a nota do candidato com 6. Se a
comparação retornar verdadeiro, este estará aprovado, caso contrário estará reprovado.
A forma de montar a Função é a seguinte:
=SE (Comparação ; Resultado caso Verdadeiro ; Resultado caso Falso)
A função SE para este exemplo ficaria assim:
= SE (D2 > 6 ; “APROVADO” ; “REPROVADO”)
Logo, no exemplo acima o resultado da média do candidato Roberto é REPROVADO, pois
sua média é menor do que 6 o que faz com que a comparação retorne falso.
Para os demais, o resultado é APROVADO, já que suas médias são maiores que 6,
fazendo com que a comparação seja verdadeira.
Obs: o sinal de ponto e vírgula ( ; ) é obrigatório na função e serve para separar as 3
partes da mesma. Toda vez que usar um texto em qualquer função o mesmo deve vir dentro de
aspas como as palavras aprovado e reprovado. Quando usar números ou endereços de células
(p. ex: D2) não há necessidade de aspas.
4.7.1 – Função SE composta
Quando existe a possibilidade de o valor da célula conter mais de dois resultados, é
necessário criar a função SE composta de mais outra. No exemplo a seguir o candidato que tiver
média menor que 4 está reprovado, o candidato que tiver média maior que 6 estará aprovado,
e o candidato que tiver um média entre 4 e 6 fará uma nova prova.
Note portanto que podemos ter 3 situações para o resultado das notas do aluno. É
necessário então criarmos duas funções SE, para realizar duas comparações, sobrando uma
última alternativa para o retorno da resposta. Exemplo:
=SE (D2 > 6; “APROVADO”; SE(D2 < 4; “REPROVADO”; “NOVA PROVA” ))
No exemplo acima a primeira comparação, sendo verdadeira, retorna a resposta
APROVADO para o candidato; caso seja falsa, será realizada uma nova função para comparar
novamente o valor da célula, pois ainda não se pode afirmar que o aluno está reprovado ou se
fará uma nova prova. A segunda comparação retorna APROVADO caso a comparação D2 < 4 seja
verdadeira. E retorna NOVA PROVA caso a comparação seja falsa, pois se D2 não é nem maior
que 6 (1ª comparação) e nem menor que 4 só pode estar entre 4 e 6, fazendo com que o aluno
faça uma nova prova.
4.8 – Função PROCV
A função PROCV procura um determinado valor numa Tabela Matriz.
Suponha que exista uma planilha onde se controla a entrada e a saída de clientes de um
hotel. Nela você deseja colocar o nome, tipo de quarto e o valor do quarto. Você deseja que, ao
digitar o tipo do quarto, automaticamente apareça o valor do mesmo.
Na verdade você terá que criar 2 tabelas:
Na 1ª Primeira tabela será colocado o tipo de quarto das Classe e o seu valor;
Na 2ª Segunda tabela será inserido o nome do cliente.
Veja o exemplo:
Onde:
=PROCV – é o nome da fórmula;
B14 – refere-se ao endereço do valor procurado, ou seja, o tipo de quarto que você
digitou;
$A$2:$B$7 – refere-se ao endereço absoluto da Tabela, ou seja, o endereço da tabela
onde contém os dados que você procura, no caso, o valor dos quartos. O endereço da tabela
matriz sempre deve ser absoluto (com o $), para que permaneça para as demais células (ou seja,
os hóspedes);
2 – refere-se ao número do índice de coluna, ou seja, o número da coluna onde estão os
dados que devem ser retornados; no caso, o valor dos quartos.
5 – Gráficos
O Excel oferece uma ferramenta de criação de gráficos de vários modelos diferentes.
Veremos a seguir alguns tipos mais comuns deles.
5.1 – Tabelas
Para fazer um gráfico, deve-se primeiro criar uma tabela organizada com os dados que
se deseja trabalhar.
Existem várias maneiras de criar gráficos. Veja o exemplo abaixo, onde o processo se
inicia na criação de uma tabela com os dados necessários:
Tabela de Receitas e Lucros
Mês
Receita (R$)
Lucro (R$)
Janeiro
1.000,00
750,00
Fevereiro
2.000,00
800,00
Março
800,00
630,00
Abril
3.500,00
200,00
Maio
950,00
840,00
Junho
1.200,00
750,00
Julho
2.800,00
600,00
Agosto
2.500,00
650,00
Setembro
3.000,00
550,00
Outubro
3.100,00
950,00
Novembro
2.900,00
740,00
Dezembro
2.700,00
800,00
Em seguida, devem ser selecionados os dados que se deseja inserir no gráfico. Para
marcar vários dados ao mesmo tempo, basta clicar na primeira célula superior à esquerda e
deslocar arrastando o mouse até a última célula, inferior direita, mantendo o botão esquerdo
do mouse pressionado, como mostrado na figura.
5.2 – Criando e editando Gráficos
Para criar um gráfico que relacione as receitas e lucros em cada mês, deve-se selecionar
um tipo de gráfico adequado, no menu Inserir. Existem vários tipos de gráficos, como gráficos
de barra, linha, 2D, 3D, etc. No Excel 2013 existe ainda uma funcionalidade dentro do menu
Inserir que recomenda o tipo de gráfico a ser usado. As ferramentas de criação e edição de
gráficos são as sinalizadas abaixo:
Com os dados acima, pode-se fazer um gráfico de colunas 2D, conforme imagem abaixo:
Quando se cria um gráfico, um novo menu, chamado Ferramentas de Gráfico, é exibido,
justamente para se trabalhar com ele. Pode-se alterar o Design do gráfico, alterar os dados
selecionados e até mesmo o tipo de gráfico no sub menu Design, representado na figura abaixo:
Existem dois sub menus no menu Ferramentas de Gráfico: Design (no Excel 2007,
chamado de Layout) e Formatar. O primeiro é o mais usado na formatação de um gráfico. Podese modificar todo o layout do gráfico com relação a eixos, linhas de grade, editar a legenda e
títulos, modificar/acrescentar dados ao gráfico e muito mais. Já o sub menu Formatar serve para
trabalhar com a forma das letras, cores, estilos, etc.
Se quiser, por exemplo, saber a representatividade de cada semestre na composição da
receita, o gráfico de pizza pode ser mais indicado:
No exemplo acima, somou-se a receita dos meses que compõem o 1º semestre e dos
meses que compõem o 2º. Em seguida, calculou-se o % de cada semestre no total da receita
anual. Feito isto, seleciona-se os dados e cria-se um gráfico de pizza:
A partir do gráfico criado, pode-se editar primeiramente seu título, criando na caixa de
texto localizada acima do gráfico. Clicando com o botão direito na área do gráfico, temos várias
opções para deixar o gráfico de acordo com a necessidade do usuário:
Clicando em “Selecionar Dados”, pode-se editar a legenda e alterar as séries de dados
que compõem o gráfico.
6 – Tabelas Dinâmicas
6.1 – Conceitos Básicos
Com tabelas dinâmicas se podem criar imediatamente resumos de uma lista Excel ou de
uma base de dados externa. As tabelas dinâmicas permitem cruzar dados, resumindo dados de
registos em dois ou mais caminhos combinando valores de diferentes campos. Para sua
construção, deve-se tomar alguns cuidados, tais como:
• Deve-se identificar cada uma das colunas com um nome (label) porque o Excel irá fazer
com que cada label seja o nome de campo na Tabela Dinâmica;
• Dado que a Tabela Dinâmica cria os totais baseados nos campos que se tem na tabela,
deve-se remover quaisquer totais automáticos da lista de dados;
• O Excel usa toda a lista, incluindo células escondidas. Se não se quer que os dados
escondidos não constem da Tabela Dinâmica, deve-se filtrar a lista de dados usando o Filtro
Avançado;
• O Excel permite criar uma Tabela Dinâmica que contenha um máximo de 8.000 itens.
6.2 – Campos de uma Tabela Dinâmica
Campos são importados para a Tabela Dinâmica arrastando-os para uma das quatro
diferentes áreas: Página, Coluna, Linha e Dados. Apesar de se poder adicionar um campo a
qualquer uma das áreas da Tabela Dinâmica, o posicionamento determina o layout da Tabela.
Uma das características interessantes das Tabelas Dinâmicas é que o Excel insere
automaticamente a linha e a coluna permitindo indicar o total para os valores nas linhas e
colunas correspondentes da Tabela Dinâmica.
As tabelas dinâmicas permitem, assim, organizar dados e calcular informação resumida
utilizando categorias (campos) e funções resumo (soma, média, etc.), permitindo analisar dados
rapidamente.
6.3 – Construindo uma Tabela Dinâmica
Agora segue o passo-a-passo para construir uma tabela dinâmica a partir dos dados
abaixo:
Ir em Inserir >> Tabela Dinâmica. Os dados podem ser oriundos de uma tabela existente
no próprio Excel ou então de uma conexão externa. Para o primeiro caso, deve-se selecionar o
intervalo onde estão os dados a serem utilizados na tabela dinâmica, conforme figura abaixo (os
dados devem conter cabeçalhos, ou seja, devem ter algum “título” para que facilite a
visualização e criação da tabela dinâmica). Já dados de uma conexão externa podem ser dados
oriundos do Microsoft Access, SQL Server, etc., o que não é de escopo deste material.
Selecionar os dados a
serem utilizados na
tabela dinâmica
Pode-se escolher se a tabela
dinâmica será criada em
uma nova sheet (planilha)
ou na mesma onde se
encontram os dados
Escolhida a opção de criar a tabela dinâmica na mesma planilha, após clicar em Concluir,
a estrutura da tabela dinâmica aparecerá com todos os seus campos (veja abaixo).
Feito isto, basta, agora, arrastar os campos (colunas) para que se crie a visão que se
deseja visualizar. No exemplo abaixo, a visão desejada é ver o valor das vendas e o total de
unidades vendidas por Estado:
Campos que devem ser
arrastados para se criar a
visão desejada
Pode-se, ainda, acrescentar ou retirar níveis de análise na tabela Dinâmica. No exemplo
abaixo, foi inserido o item “Representante” por Estado. Agora, pode-se visualizar o quanto cada
representante vendeu em cada Estado de atuação (independente do ano da venda).
Campo
“Representante” foi
adicionado para ser
visualizado nas linhas
6.4 – Criando gráficos a partir de uma Tabela Dinâmica
A partir de uma tabela dinâmica já criada, é bastante simples montar gráficos utilizando
seus campos. Suponha que se deseje criar um gráfico a partir dos dados abaixo:
Campo
“Representante” foi,
agora, adicionado
para ser visualizado
nas colunas
Note que, desta vez, o campo “Representante” foi colocado para ser visualizado nas
colunas, e não mais nas linhas. Primeiro, deve-se selecionar a área que contém os dados a serem
utilizados como insumo para o gráfico: I10:K19. Feito isto, ir em Inserir >> Gráfico (conforme
figura abaixo). Neste caso, será usado o gráfico de colunas. Notar que a coluna L mostra o total
por linha; logo não será usado para construção do gráfico (a menos que se deseja visualizar o
total por Estado juntamente com sua quebra por representante).
Gráfico de colunas
selecionado na aba
Inserir
7 – Outras Funcionalidades
7.1 – Congelar Painéis
Essa ferramenta é muito útil caso queira bloquear painéis ou visualizar duas áreas
diferentes de uma planilha para melhor trabalhar com um grande volume de números ou
informações.
Seu funcionamento é bem simples. Dentro de sua planilha, vá na janela Exibição. Nela,
temos o ícone congelar painéis. Ele se divide em três categorias, como se vê na imagem abaixo:
Congelar Painéis, congelar linha superior e congelar primeira coluna.
No caso da primeira opção, você tem de definir até onde você quer congelar. Digamos
que queremos congelar até a célula A15. Clique nessa célula e depois em congelar painéis. Agora
você verá que uma pequena linha foi criada no documento.
As células acima dessa linha ainda são editáveis, porém não descem mais com a rolagem,
enquanto as linhas abaixo da mesma se comportam de maneira normal.
Quando se quer apenas a primeira linha ou coluna, basta usar as duas outras opções,
respectivamente. Independente da escolha feita por você, para desativar o congelamento de
painéis, selecione a opção “Descongelar Painéis”, como mostra a imagem abaixo.
Uma dica que pode vir a te ajudar é a ferramenta dividir. Ela, ao contrário da congelar,
permite que você continue com a rolagem dentro da tabela, como se fossem duas planilhas
diferentes. Ao clicar na opção, duas linhas, uma horizontal e uma vertical, serão criadas na
planilha.
Barras que dividem
a planilha,
facilitando a
visualização
Elas são ajustáveis ao de acordo com a necessidade do usuário, com isso cada parte da
planilha tem sua autonomia própria. Caso queria que se crie duas planilhas apenas com a
numeração lateral diferente, pegue o mouse e arraste a linha vertical até o canto da tela para
que desapareça, ou clique duas vezes sobre ela. Faça o mesmo efeito caso queira que as letras
superiores tenham autonomia, dessa vez com a linha horizontal.
7.2 – Ferramenta Classificar
O Excel possui uma ferramenta de classificação que permite organizar de forma simples
os dados da planilha. Adicionalmente, facilita a localização das informações que se deseja
pesquisar, principalmente quando existem muitos dados.
Supondo que exista a tabela abaixo que identifica os nomes dos funcionários com a
cidade onde nasceram e a renda mensal, pode-se ver que ela não está organizada
alfabeticamente. Para ordenar os itens em ordem alfabética, selecione as três células referentes
aos rótulos (Nome Funcionário, Cidade e Renda). Feito isso, localize na aba Página Inicial (ou
Início) do Excel a opção Classificar e Filtrar, clique sobre ela e selecione a opção Filtro.
Perceba que ao lado do nome dos rótulos foi adicionado um botão. Clicando nesse botão
você terá acesso às opções de classificação e filtro. Através desse menu você poderá classificar
os dados por ordem alfabética, numérica, ou aplicar filtros que farão diversas verificações como,
por exemplo “É igual a…”, “É diferente de…” etc.
Classificar em
ordem alfabética
(de A a Z)
Como vemos na imagem acima, foi escolhida a opção de classificações de A a Z. Isso
significa que automaticamente o Excel irá mudar de lugar todas as células, e deixá-las ordenadas
alfabeticamente, como na imagem abaixo.
Organização por
ordem alfabética
pelo nome do
funcionário
Nesta apostila usou-se a classificação por ordem alfabética. No entanto, pode-se
também usar a classificação pela renda, onde os dados seriam organizados numericamente do
menor para o maior, do maior para o menor ou através de outras opções de filtro. Para fazer
essa classificação, basta clicar sobre o botão ao lado do rótulo da coluna numérica, que no nosso
exemplo é o RENDA, e aplicar a classificação desejada.
Vale lembrar que quando se ordena uma coluna, a que está relacionada a ela também
é ordenada. Na nossa planilha de exemplo, quando se classifica por ordem alfabética, tanto os
produtos quanto os valores de renda são reorganizados. Portanto, não há necessidade de
classificar as duas colunas, pois uma “leva a outra”.