Excel Básico - Tratamento de Dados

Prévia do material em texto

Excel BÁSICO
Aula 4
Tratamento de Dados
Prof. Cassiano Isler
2016.2 - Turma 4
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Programa da Aula
Tabela Dinâmica
Classificar
Filtro
Texto para Colunas
Remover Duplicatas
Prof. Cassiano Isler Excel BÁSICO - Aula 4 2 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Bibliografia
GÓMEZ, Luis Alberto. Excel para engenheiros. Visual Books,
2009. Caṕıtulo 1. Dispońıvel na biblioteca UFSC-Joinville.
DIXON, Helen. Excel 2007: Beyond the Manual. Berkeley,
2007. Caṕıtulo 5, 7 e 8 dispońıvel em :
http://dx.doi.org/10.1007/978-1-4302-0389-6
Slides das aulas e material complementar dispońıveis em:
Curso Básico Excel
Prof. Cassiano Isler Excel BÁSICO - Aula 4 3 / 36
http://dx.doi.org/10.1007/978-1-4302-0389-6
http://cursosextensao.paginas.ufsc.br/excel_basico/
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
Construir uma tabela para análise e tratamento de dados
de maneira organizada e mais eficiente.
Selecionar um conjunto de dados incluindo o cabeçalho
das colunas.
Aba “Inserir” → Grupo “Tabelas” → “Tabela Dinâmica”
Prof. Cassiano Isler Excel BÁSICO - Aula 4 4 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
Surge uma janela para confirmação dos dados
selecionados.
É necessário definir o local da nova tabela dinâmica, com
duas alternativas:
Prof. Cassiano Isler Excel BÁSICO - Aula 4 5 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
(1) “Nova Planilha”
Uma planilha é criada e a tabela dinâmica é inserida nela
(opção mais comum).
(2) “Planilha Existente”
É necessário selecionar a célula (“Local”) em que a ta-
bela dinâmica será inserida (essa célula refere-se ao canto
superior esquerdo da tabela dinâmica).
Prof. Cassiano Isler Excel BÁSICO - Aula 4 6 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
No caso de seleção de “Nova Planilha”, surge a seguinte
tela em uma planilha criada.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 7 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
As áreas da tabela dinâmica indicadas correspondem a:
1 Conjunto de células que serão
preenchidas pela tabela dinâmica.
2 Campos posśıveis para as linhas,
colunas e conteúdo da tabela.
3 “Rótulo de Coluna” da tabela.
4 “Rótulo de Linha” da tabela.
5 “Valor” que preenche a tabela.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 8 / 36
1
2
3
4 5
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
Selecione na “Lista de campos da tabela dinâmica” 2 os
campos que preencherão a tabela.
Os campos serão inseridos no área “Valor” 5 .
Prof. Cassiano Isler Excel BÁSICO - Aula 4 9 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
Em seguida, clique e arraste cada campo para áreas de
rótulo de coluna 3 e rótulo de linha 4 .
Um dos campos deve permanecer na área “Valor” 5 ,
que corresponde aos valores que preenchem a tabela, em
correspondência aos rótulos das linhas e colunas.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 10 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Tabela Dinâmica
Para alterar o tipo de valor que preenche a tabela, clique
no ı́cone de lista suspensa ao lado do nome do campo na
área “Valor” 5 e selecione a opção “Configurações do
Campo de Valor...”. Em seguida selecione a função
desejada para os valores (média, soma, desvio padrão etc.).
Prof. Cassiano Isler Excel BÁSICO - Aula 4 11 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Classificar
Ordenar valores nas colunas segundo um critério.
Aba “Dados” → Grupo “Classificar e Filtrar”
→ “Classificar”
Prof. Cassiano Isler Excel BÁSICO - Aula 4 12 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Classificar
Os botões “A Z ↓” e “Z A ↓” são para atalhos para
classificação “do menor para o maior” e “do maior para o
menor”, respectivamente.
Se houver duas colunas selecionadas o Excel ordena
corretamente somente a primeira coluna.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 13 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Classificar
O botão “Classificar” abre uma janela que permite ordenar
múltiplas colunas por diferentes critérios.
Selecione as colunas a classificar (inclusive os cabeçalhos)
e na nova janela é posśıvel “Adicionar ńıvel”. Para cada
ńıvel é necessário definir a “Coluna”, o tipo de ordenação
(“Classificar em”) e o critério de ordenação (“Ordem”).
Prof. Cassiano Isler Excel BÁSICO - Aula 4 14 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Filtro
Selecionar células com base em critérios preestabelecidos.
Selecionar o cabeçalho das colunas em que serão aplicadas
o filtro.
Aba “Dados” → Grupo “Classificar e Filtrar” → “Filtro”
Prof. Cassiano Isler Excel BÁSICO - Aula 4 15 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Filtro
Surge um ı́cone de lista suspensa em cada célula
selecionada.
Ao clicar nesse ı́cone aparecem os critérios de filtragem.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 16 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Filtro
Os critérios mais comuns para filtro são:
(1) Seleção dos valores na caixa de texto clicando nas caixas
de seleção ou por pesquisa de valores.
(2) “Filtros de Número” em que é posśıvel definir o critério.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 17 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Filtro
Também é posśıvel aplicar “Filtro por Cor” caso as células
estejam coloridas.
Para remover o filtro basta clicar novamente no botão de
atalho “Filtro” (atenção pois o filtro de TODAS as
colunas é removido).
Prof. Cassiano Isler Excel BÁSICO - Aula 4 18 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
Dividir a sequência de texto de uma célula em múltiplas
células.
Útil quando existe uma sequência de números separados
por um caractere (por exemplo, ponto-e-v́ırgula).
Aba “Dados” → Grupo “Ferramentas de Dados” →
“Texto para Colunas”
Prof. Cassiano Isler Excel BÁSICO - Aula 4 19 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
Selecione a célula com o conjunto de dados a serem
divididos.
Em “Texto para Colunas” é posśıvel dividir valores
segundo dois critérios.
(1) “Delimitado” para que a divisão seja com base em um
caractere (delimitador).
Prof. Cassiano Isler Excel BÁSICO - Aula 4 20 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
DuplicatasExerćıcios
Texto para Colunas
(1) “Delimitado” para que a divisão seja com base em um
caractere (delimitador).
Clique em “Avançar” e selecione o delimitador (observe
que é posśıvel personalizar o delimitador em “Outros”).
Clique novamente em “Avançar” e defina o formato dos
dados da coluna resultante.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 21 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
(1) “Delimitado” para que a divisão seja com base em um
caractere (delimitador).
Para finalizar, clique em “Concluir”.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 22 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
O outro método de divisão do conteúdo da célula é por
posição dos caracteres.
Novamente, selecione a célula a ser dividida e clique em
“Texto para Colunas”.
(1) “Largura fixa” para que a divisão seja pelo espaço entre
conjunto de caracteres.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 23 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
(1) “Largura fixa” para que a divisão seja pelo espaço entre
conjunto de caracteres.
Clique em “Avançar” e a separação por espaço é definida
automaticamente. Aqui o usuário pode incluir, alterar ou
remover a posição da divisão clicando na “régua” da
“Visualização de Dados”.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 24 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
(1) “Largura fixa” para que a divisão seja pelo espaço entre
conjunto de caracteres.
Clique novamente em “Avançar” e defina o formato dos
dados da coluna resultante.
Para finalizar clique em “Concluir”.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 25 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Texto para Colunas
ATENÇÃO: Quando o recurso “Texto para Colunas” é
utilizado, os dados das colunas à esquerda são sobrescritos.
Para evitar isso, insira novas colunas na planilha clicando
com o botão direito do mouse e selecionando “Inserir...”.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 26 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Remover Duplicatas
Remover valores repetidos em uma sequência de células.
Aba “Dados” → Grupo “Ferramentas de Dados”
→ “Remover Duplicatas”
Prof. Cassiano Isler Excel BÁSICO - Aula 4 27 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Remover Duplicatas
Se houver uma ou mais colunas imediatamente ao lado da
selecionada, surge a opção de expandir a seleção da
remoção de duplicatas.
Se a opção “Continuar com a seleção” for definida, surge
a mesma janela porém com apenas uma coluna para
remoção de valores duplicados.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 28 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Remover Duplicatas
Se houver uma ou mais colunas imediatamente ao lado da
selecionada, surge a opção de expandir a seleção da
remoção de duplicatas.
Se a opção “Expandir seleção” for definida, surge uma
janela para escolha das colunas cujas linhas terão os
valores duplicados removidos.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 29 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(1) A partir dos “Dados de Origem”, construa uma tabela
dinâmica equivalente ao “Relatório da tabela dinâmica”.
1
Adaptado de: http://car.aedb.br/faculdades/eng/Downloads/1˙ano/exe˙macros.doc
Prof. Cassiano Isler Excel BÁSICO - Aula 4 30 / 36
http://car.aedb.br/faculdades/eng/Downloads/1_ano/exe_macros.doc 
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(2) Considere o conjunto de dados a seguir concatenados pelo
sinal de “$”.
Aluno$Prova1$Prova2$Trabalho1$Trabalho2$Trabalho3$Media Prova$Media Trabalho$Media Final
Aluno1$8,0$9,0$7,3$8,5$7,5$
Aluno2$8,5$8,0$7,9$8,6$7,9$
Aluno3$6,7$7,0$7,8$9,0$5,0$
Aluno4$10,0$7,0$7,9$3,0$3,0$
Aluno5$9,0$8,6$8,0$4,0$6,0$
Aluno6$6,9$6,0$6,8$6,0$2,9$
Aluno7$8,7$5,0$3,0$7,7$6,0$
Aluno8$9,3$4,0$9,0$8,5$4,5$
Aluno9$8,0$7,0$3,0$7,9$1,2$
Aluno10$7,0$3,0$6,6$10,0$4,9$
Aluno11$5,0$4,0$4,0$6,0$5,5$
Execute o que se pede a seguir.
a) Calcule a Média de Prova, Média de Trabalho e Média
Final.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 31 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(2) Continuação...
b) Formate a planilha conforme ilustrado abaixo e aplique
formatação condicional para que os valores tenham a cor
alterada segundo as regras na sequência.
0, 0 ≤ Nota < 4, 0→ Vermelho
4, 0 ≤ Nota < 7, 0→ Azul
7, 0 ≤ Nota ≤ 10, 0→ Verde
Prof. Cassiano Isler Excel BÁSICO - Aula 4 32 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(2) Continuação...
c) Classifique os alunos em ordem crescente da Média
Final, Média de Prova e Média de Trabalho
simultaneamente.
d) Filtre os alunos em função da Média Final nos
intervalos da formatação condicional e crie três tabelas
separadas com o mesmo cabeçalho da tabela original e os
alunos agrupados por média.
1
Adaptado de: http://www.petcivil.ufc.br/portal/wp-content/uploads/2012/02/EXCEL.pdf
Prof. Cassiano Isler Excel BÁSICO - Aula 4 33 / 36
http://www.petcivil.ufc.br/portal/wp-content/uploads/2012/02/EXCEL.pdf 
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(3) Construa e formate a tabela a seguir. Em seguida, calcule
os valores do campo “Total” dado pela multiplicação da
coluna “Quantidade” e “Valor Unitário”.
Com base na lista de dados, crie uma ta-
bela dinâmica que mostre o total a rece-
ber de cada cliente, indicando o número
de fatura e o respectivo nome.
1
Adaptado de: http://labesna.com.sapo.pt/Excel/exer˙ex11˙dinamica.pdf
Prof. Cassiano Isler Excel BÁSICO - Aula 4 34 / 36
http://labesna.com.sapo.pt/Excel/exer_ex11_dinamica.pdf 
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(4) Construa e formate a tabela a seguir.
Prof. Cassiano Isler Excel BÁSICO - Aula 4 35 / 36
Programa da
Aula
Bibliografia
Tabela
Dinâmica
Classificar
Filtro
Texto para
Colunas
Remover
Duplicatas
Exerćıcios
Exerćıcios
(4) Continuação...
A partir dessa tabela, crie a tabela dinâmica a seguir.
1
Adaptado de: http://car.aedb.br/faculdades/eng/Downloads/1˙ano/exe˙macros.doc
Prof. Cassiano Isler Excel BÁSICO - Aula 4 36 / 36
http://car.aedb.br/faculdades/eng/Downloads/1_ano/exe_macros.doc
	Programa da Aula
	Bibliografia
	Tabela Dinâmica
	Classificar
	Filtro
	Texto para Colunas
	Remover Duplicatas
	Exercícios