Excel Intermediário - EDAPedap.diadema.sp.gov.br/edap/images/Materiais/Apostila_Excel... · O Excel dispõe de muitos recursos para a realização das tarefas mais básicos de estatística

PREFEITURA DE DIADEMA

SECRETARIA DE GESTÃO DE PESSOAS ESCOLA DIADEMA DE ADMINISTRAÇÃO PÚBLICA

Escola Diadema de Administração Pública - Rua Oriente Monti, 28 - Centro - Diadema - SP - CEP: 09910-250 - Tel.: 4053-5246 / 4053-5249 - [email protected]

Excel Intermediário


2 Escola Diadema de Administração Pública - Rua Oriente Monti, 28 - Centro - Diadema - SP - CEP: 09910-250 - Tel.: 4053-5246 / 4053-5249 - [email protected]

Apostila elaborada por: ROCHA JR, Adálio Saraiva

Bibliografia:

Copyright © 2018: Prefeitura de Diadema/Secretaria de Gestão de Pessoas/Escola Diadema de Administração Pública

Permitida a reprodução total ou parcial desta obra desde que sem fins lucrativos e citada a fonte.



Introdução

Em muitos estudos que realizamos, sejam com o fim de adquirir novos conhecimentos ou de

resolver problemas técnicos do trabalho, observamos e tiramos medidas da natureza, da organização

social, dos acontecimentos ou dos fatos históricos. Esses trabalhos, normalmente, coletam, elaboram e

reapresentam dados, para mostrar padrões e relações que se consideram significativos para a

explicação da realidade. Assim, pegamos dados brutos e os lapidamos para outras formas mais

elaboradas, em gráficos, tabelas, cruzamentos ou modelos.

Consideremos uma pesquisa em que já se realizou o trabalho de coleta de dados e agora é

necessário consolidar as informações. Para isso, o pesquisador provavelmente se valerá de médias,

percentis, desvio padrão e agrupamentos das medidas. Em um exemplo típico, após coletar as notas de

avaliações aplicadas aos alunos de uma escola, será preciso fazer médias do resultado para ter uma

avaliação de conjunto do desempenho dos alunos. O professor pode verificar se há muito muitas notas

diferentes da média; nesse caso, ele calculará o desvio padrão. Ele pode também agrupar as notas em

conceitos: insatisfatório, regular, bom e ótimo. O cruzamento de dados servirá para saber se há alguma

relação entre as notas de uma matéria e outra ou, caso tenha outras informações dos alunos, descobrir

em que medida as notas dos alunos se relacionam com hábito de leitura, local de moradia ou

escolaridade dos pais. Os resultados serão mais bem comunicados se eles se apresentarem visualmente

em gráficos, como os de proporcionalidade, de evolução temporal ou da curva normal.

O Excel dispõe de muitos recursos para a realização das tarefas mais básicos de estatística e

tratamento de dados. Ele é um aplicativo genérico que poderá resolver muito bem esses problemas

mais elementares, desde que a quantidade de dados não seja muito excessiva, pois há elevado consumo

de memória do computador. Se houver a necessidade de se armazenar um grande volume de

informações, utiliza-se normalmente um gerenciador de banco de dados. Nesse caso, desenvolvem-se

sistemas mais complexos, controlados por programas que irão processar e apresentar os dados

automaticamente aos seus usuários. Por conta dessa complexidade, tal serviço é ocupado por pessoas

especializadas, como programadores e analistas. Em contraponto, o Excel é muito útil exatamente em

tarefas em que se quer um trabalho rápido, com pouca dificuldade de execução, de boa apresentação e

de baixo custo.

Nosso curso irá explorar a utilização do Excel para solucionar alguns problemas comuns na

manipulação e apresentação de informações que costumam aparecer em trabalhos cotidianos de

muitos profissionais. É, portanto, um curso que não foca em conhecimentos práticos para um



departamento específico ou profissão particular. Os exemplos utilizados, no entanto, são extraídos de

situações concretas de determinadas serviços. Caberá ao cursando relacionar e aplicar às suas próprias

tarefas.

Conteúdo do curso:

Módulo 1: Fórmulas e funções.

Módulo 2: Revisão da construção de gráficos.

Módulo 3: Formatação condicional.

Módulo 4: Tabela dinâmica.



Módulo 1 - Fórmulas e funções Coletando os dados

Utilizaremos a Tabela 1 como exemplo. Ela foi aproveitada e adaptada com acréscimos de dados

da apostila de um Projeto de Ensino de Estatística Descritiva da Universidade de São Paulo: Aprender

Fazendo Estatística de autoria de Drª Terezinha Aparecida Guedes et al. Encontra-se disponível no site

http://www.each.usp.br/rvicente/Guedes_etal_Estatistica_Descritiva.pdf. Acessado em 1/3/2018.

Carregue para o Excel a partir do arquivo Tabela1.xlsx.

As variáveis que compõem o questionário são:

Sexo: com categorias (1) se masculino e (2) se feminino.

Nascimento: idade em anos.

Altura: altura em metros e centímetros.

Peso: peso em quilos.

Estado Civil: estado civil com categorias (1) se solteiro, (2) se casado e (3) se separado.

Irmãos: número de irmãos.

Transporte: meio de transporte mais utilizado com categorias (1) de coletivo e (2) se

próprio.

Procedência: município de procedência com categorias (1) se Maringá, (2) se outro

município do Paraná e (3) se de outro Estado.

Trabalho: relação do trabalho com o curso com categorias (1) não trabalho, (2)

completamente relacionado, (3) parcialmente relacionado e (4) não relacionado.

Informação: meio de informação mais utilizado com categorias (1) se TV, (2) jornal, (3)

rádio, (4) revista e (5) internet.

Disciplinas: número de disciplinas reprovadas no 1º ano da UEM.

Nota 1º Mês.

Nota 2º Mês.

Nota 3º Mês.

Nota 4º Mês.

http://www.each.usp.br/rvicente/Guedes_etal_Estatistica_Descritiva.pdf



Tabela 01 – Informações sobre sexo, idade (anos), altura (metro e centímetro), peso (kg), estado

civil, número de irmãos, transporte, procedência, relação do trabalho com o curso de Estatística, meio

de informação e número de disciplinas reprovadas dos alunos da disciplina Inferência Estatística do

Curso de Estatística da UEM - 21/3/2005.

Nú

mer

o

Co

d. S

exo

Nas

cim

ento

Alt

ura

Pes

o

Co

d. E

stad

o C

ivil

Irm

ãos

Co

d. T

ran

spo

rte

Co

d. P

roce

dên

cia

Co

d. T

rab

alh

o

Co

d. I

nfo

rmaç

ão

Dis

cip

linas

No

ta 1

º m

ês

No

ta 2

º m

ês

No

ta 3

º m

ês

No

ta 4

º m

ês

1 2 11/7/1998 1,6 58 1 1 2 1 4 1 2 6 4 7 7

2 2 16/5/1992 1,65 59 1 2 1 3 1 4 0 6 6 5 7

3 2 22/8/2000 1,64 55 1 2 2 1 1 1 0 7 5 6 6

4 2 5/10/1993 1,73 60 1 2 1 2 4 1 2 6 7 6 8

5 1 17/2/1983 1,76 83 2 6 1 2 4 1 2 5 6 7 5

6 2 27/8/1998 1,62 58 1 2 1 2 4 3 5 6 5 8 7

7 2 25/10/1989 1,72 70 1 3 1 1 1 1 0 7 8 6 7

8 1 9/12/1995 1,71 62 3 2 2 2 4 5 2 8 9 8 9

9 2 9/7/1998 1,63 63 1 2 2 1 1 1 1 5 6 7 8

10 1 12/6/1998 1,79 75 1 2 2 3 1 5 2 6 7 7 6

11 1 4/7/1998 1,82 66 1 1 2 3 1 1 2 7 5 8 9

12 2 23/4/1988 1,68 46 1 3 2 2 3 1 4 7 8 8 7

13 2 2/7/2000 1,69 64 1 1 2 1 3 1 0 6 8 7 9

14 1 6/10/1981 1,82 80 2 2 2 1 4 1 3 6 7 8 8

15 1 8/11/1993 1,83 62 1 1 2 2 4 1 2 7 6 8 9

16 2 8/11/1998 1,63 68 1 2 1 1 1 1 2 5 7 6 7

17 1 28/2/1997 1,71 80 1 2 1 1 4 5 0 9 6 8 7

18 1 20/3/1993 1,8 82 2 1 2 2 4 5 3 8 5 9 7

19 2 24/11/1994 1,62 55 1 2 2 1 1 2 2 6 7 6 8

20 1 15/10/1999 1,74 58 1 2 2 1 2 1 3 8 9 7 9

21 2 1/9/1997 1,55 65 1 1 2 1 1 1 1 6 5 5 8

22 1 24/12/1996 1,73 62 1 0 2 1 1 2 4 9 10 10 10

Fonte: Departamento de Estatística (DES)/UEM.

Em um estudo de campo, os elementos objetivos da realidade que examinamos são diferentes

e, portanto, podem ter características, medidas ou contagens também diferentes. Falamos de variáveis

para nos referir a esses atributos observados que podem ter seus valores obtidos e comparados entre os



vários objetos identificados. Essas informações colhidas diretamente das observações do pesquisador

irão constituir os dados brutos.

As variáveis podem ter: 1) valores quantitativos, medidos em uma escala de intervalos e

quantidades finitos ou infinitos; 2) valores categorizados, qualitativos, não comparáveis em escalas

ordenadas, pois são elementos distintos agregados por uma regra formadora de um conjunto. Exemplos

das primeiras variáveis: peso e altura. Das segundas: sexo e estado civil de uma pessoa.

Não se discute que o preparo de um bom instrumento, como um questionário, é imprescindível

para o sucesso em uma pesquisa de levantamento de dados; por isso, na maioria das vezes, há a justa

preocupação com a qualidade das questões e dos dados que se quer buscar. Nem sempre, porém, a

importância da formatação do questionário é lembrada. A depender disso, poderemos economizar (ou

não) tempo, recursos materiais, facilitar a digitação e, ainda, diminuir significativamente erros de

transcrição. Trata-se, por isso, de um cuidado valioso para a compilação fidedigna dos dados de um

meio para outro, como na digitação de um questionário registrado em papel para o armazenamento no

HD de um computador.

Nesse processo, buscamos evitar erros comuns do digitador, que pode não perceber a notação

correta do questionário ou teclar um dado diferente do que foi coletado. Para minimizar tais falhas,

costuma-se codificar esses dados, preferencialmente em números, para que se facilite o trabalho – um

teclado numérico é mais rápido e fácil de digitar – e se economize tempo em cliques do mouse e

digitações extensas que se valham do teclado alfanumérico do computador. Assim, teclar o código 1

para masculino ou 2 para feminino é mais fácil do que digitar Masculino/Feminino, M/F, ou ainda usar o

mouse para clicar em alguma dessas opções na tela de algum aplicativo do computador.

Bloqueando entradas inválidas

A adoção de regras de entrada na digitação serve como um bom recurso minimizador de falhas.

No Excel isso é simples de fazer; siga o exemplo:

1 – Acrescente algumas linhas no final da tabela.

2 – Posicione na coluna da variável Cod.Sexo.

3 – Na aba Dados, clique no botão:

4 – Escolha “Validação de Dados...” e digite, nas abas da janela, as opções como a seguir:



Cálculo de anos e idades

No registro da idade, podemos usar a data de nascimento como alternativa à digitação do

número de anos. Além da vantagem de preservarmos informações para reuso dos dados em outras

pesquisas, poderemos recalcular a idade a qualquer tempo. Para obtermos a idade a partir de uma

dada, precisamos de uma data de referência, que pode ser a atual ou outra data-base fixa, como a que

foi coletada os dados.

Calculando a idade em anos na tabela exemplo:

1 - Crie uma nova coluna para cálculo da idade atual na tabela, ao lado da coluna Nascimento:

2 – Insira uma das funções abaixo, onde D7 é o endereço da célula ao lado. Copie a fórmula para o

restante das células da coluna. A função HOJE() irá retornar a data atual. A função

ARREDONDAR.PARA.BAIXO() , com zero decimais, fará o Excel considerar a idade se completar somente

no aniversário e não à data mais próxima ao aniversário.

=ARREDONDAR.PARA.BAIXO( (HOJE()-D7)/365,25; 0) ou =ARREDONDAR.PARA.BAIXO(FRAÇÃOANO(HOJE();D7); 0)



3 – Para criar uma data de referência, basta trocar a função HOJE() por uma célula fora da tabela com a

data da pesquisa. Não se esqueça de fixar o endereço. A célula ficará com uma fórmula como a seguir:

=ARREDONDAR.PARA.BAIXO( ($A$5-D7)/365,25; 0) ou =ARREDONDAR.PARA.BAIXO( FRAÇÃOANO($A$5; D7); 0)

Retornando à descrição dos códigos para a tabela:

Se, por um lado, a tabela codificada é mais fácil de digitar e armazenar, ela é, por outro lado,

mais difícil de ler durante o trabalho e estudo das informações, podendo predispor, desta vez, a erros de

interpretação. Além disso, não torna prática a tabulação de dados e a elaboração de gráficos no Excel.

Para mostrarmos a descrição dos dados no lugar dos códigos, utilizaremos a função PROCV(), cuja

sintaxe e argumentos são mostrados a seguir. Essa função fará uma procura de um valor em uma matriz

de células da planilha e retornará o valor da linha encontrada com a coluna referenciada pelo índice

indicado na função. O último argumento informará à função se queremos uma correspondência

aproximada na busca.

PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; [procurar_intervalo])

Praticando como o exemplo da tabela:

1 – Crie uma coluna adjacente à coluna Sexo.

2 – Insira a fórmula PROCV em uma célula da tabela na coluna que foi inserida. Coloque a célula que

possui o valor de busca e, a seguir, as células que contêm os códigos de tradução. Repare que os

endereços da linha e da coluna devem ser precedidos pelo $ que fixará o endereço do intervalo de

busca e evitará que o Excel atualize a sua referência quando copiarmos a fórmula para as outras células.

Em nosso exemplo, os códigos estão sendo procurados numa outra planilha chamada Códigos, que deve

também ser referenciada.

=PROCV(C7; Códigos!$A$2:$B$3;2; FALSO) 3 – Copie a célula para as outras da mesma coluna. Média aritmética

Algumas das operações do Excel já são bem conhecidas. Mesmo assim, muitos dos seus usos são

pouco explorados. Nesse rol, está uma das funções mais comuns que serve para tirar médias: =MÉDIA(),

que tem como argumento um ou vários intervalos de células. Para escolher os intervalos com ajuda do

mouse, digite a função na célula destino, segure a tecla Ctrl (ou tecle; após cada seleção) e escolha os



vários intervalos de células. Cada um deles é transcrito para dentro da fórmula como argumento. Como

no Exemplo a seguir:

=MÉDIA(F7:F29;G7:G29) Média aritmética ponderada

Este tipo de média é utilizado quando se precisa aumentar o peso de uma parte dos dados no

resultado final. Num exemplo típico, temos as últimas notas mensais de uma disciplina que poderiam

influenciar mais, com maior peso, na nota final. Cálculos desse tipo também são comuns quando se

considera o peso como frequência em um agrupamento de dados. Assim, se a frequência for 0, não

entra na média; se for 5, será considerada 5 vezes.

Claro que se a média for calculada entre poucas células ou se precisamos fazer somente alguma

operação na célula antes do cálculo, pode ser mais prático operar diretamente as células. Exemplo:

Calcular a média ponderada de notas dos 1º ao 4º mês, considerando pesos 1, 1, 2 e 3, respectivamente.

1 – Acrescente uma nova coluna ao lado das notas dos dois bimestres para receber os resultados

2 – Insira a fórmula abaixo onde a coluna R é a coluna do primeiro mês e S a do segundo.

=(R7+S7+T7*2+U7*3)/6

Quando utilizamos muitos valores de dados, ou quando queremos ajustar os pesos sem mexer

na fórmula, a média ponderada pode ser calculada com o uso da combinação de funções, como no

exemplo abaixo, que irá multiplicar um intervalo de valores das notas mensais de uma linha,

respectivamente pelo intervalo células onde estão os pesos para cada mês e, após isso, somar o produto

destas células. Como divisor, temos a soma das células que estão no intervalo dos pesos.

Praticando:

1 – Nas colunas das notas, digite os pesos na linha acima dos títulos. No nosso exemplo, ele se encontra

nas células R5 a U5

2 – Na coluna de células adjacente as notas, escolha a primeira para digitar a média, como no exemplo a

seguir:

=SOMARPRODUTO(R7:U7;$R$5:$U$5)/SOMA($R$7:$U$7) 3 – Copie a fórmula para o restante das células da coluna. O intervalo de células onde estão os pesos foi fixado com ‘$’ para que não tivesse o seu endereço atualizado durante a cópia.



Média aritmética condicional

Em alguns casos, precisamos do cálculo da média aritmética de dados que estão em

determinadas condições ou que precisam ser divididos em categorias.

Nesses casos, podemos utilizar MÉDIASE() ou MÉDIASES() que considerará para o cálculo da

média somente se os dados estiverem sob determinadas condições, especificadas como argumentos da

função.

Usamos a primeira função quando temos o intervalo de valores e uma condição baseada nos

próprios valores. Exemplo: a média de todas as notas maiores ou iguais a 5.

A segunda função é utilizada quando os valores estão em um intervalo de células e as condições

se baseiam em outro intervalo. Exemplo: altura média, em um intervalo de células, e descrição dos

homens e das mulheres em outro intervalo.

Modelos de fórmulas dos exemplos citados:

Notas maiores ou iguais a 5

=MÉDIASE(Q7:Q29;">=5") Altura média homens:

=MÉDIASES(H7:H29; C7:C29; "=1") Altura média mulheres:

=MÉDIASES(H7:H29; C7:C29; "=2")

Mediana

A mediana é o valor central de uma amostra de dados ordenados. Ela é importante quando se

quer diminuir a influência de dados extremos, com valores muito altos ou muito baixos, em uma

amostra. Consideremos a seguinte sequência de números: 17; 19; 21; 25; 29; 30; 31; 35; 150. A média é

39,6 e a mediana é 29. Podemos reparar que, nesse caso, a mediana 29 representa um valor mais

central em relação à quantidade de itens da amostra do que a média. A função de cálculo do Excel é

mostrada no exemplo a seguir e tem argumentos semelhantes à função média:

=MED(F7:F29)

Desvio padrão

O desvio padrão serve para calcular a dispersão de um conjunto de dados com medidas

quantitativas. Se considerarmos a média de uma amostra de dados, buscaremos uma medida



centralizadora em torno da qual estão distribuídos os dados. Com o desvio padrão, por outro lado,

saberemos o quanto esses dados estão dispersos com valores distantes da média. Podemos imaginar

uma aplicação prática: um gerente de produção de uma fábrica recebeu muitas reclamações de

irregularidade em seus produtos, pois muitas embalagens possuíam peso inferior a 80 g informado no

rótulo do produto. Para saber, ele resolveu pegar uma amostra de 50 embalagens para conferir a

pesagem. Fez uma média e obteve 80. Portanto, não encontrou problemas com esse cálculo. Para saber

mais, resolveu calcular o desvio padrão e obteve 3,5 g.

Para calcular o desvio padrão de uma amostra de dados, utilize a fórmula =DESVPAD.N() e para

toda a população =DESVPADP(). A unidade do desvio padrão tem a mesma dimensão das medidas

efetuadas. Se dividirmos o desvio padrão pela média dos valores considerados, obteremos um cálculo

relativo, que chamamos de coeficiente de variância. Veja os exemplos a seguir:

Desvio padrão:

=DESVPADP(H7:H28) Coeficiente de variância:

=DESVPADP(H7:H28)/MÉDIA(H7:H28) Contando itens de uma célula

Consideremos o caso de um evento que geralmente ocorre poucas vezes dentro de um grande

número de possibilidades, mas que pode pontualmente aumentar a frequência em algumas situações.

Como registraríamos esses dados se não queremos reservar um número grande de células para

comportar o máximo de possibilidades? Em um exemplo concreto: precisamos registrar a data das

faltas dos alunos de uma turma e não queremos guardar na planilha uma célula para cada dia de aula

com a marcação da presença ou falta do aluno. Para solucionarmos o problema, adicionamos as datas

das faltas em uma única célula, separando-as com algum sinal, como o ponto e vírgula ou o espaço

branco. A tabela ficará mais compacta e de melhor visualização. Agora temos um segundo problema:

como contar as faltas de cada um dos alunos.

Uma opção possível é contar o número de separadores, ponto e vírgula em nosso exemplo

seguinte, de cada célula e somar mais 1. Caso ela não esteja vazia.

A função ARRUMAR() remove os espaços vazios da célula, NÚM.CARACT() conta o número de

caracteres e a função SUBSTITUIR() troca o separador “por nada”.

=SE(NÚM.CARACT(ARRUMAR(B4))=0;0;NÚM.CARACT(ARRUMAR(B4))-NÚM.CARACT(SUBSTITUIR(B4;";";""))+1)



Módulo 2: Revisão da construção de gráficos

Quando construímos gráficos, estamos a procurar meios com a finalidade de apurar a nossa própria

percepção de números e tabelas ou a comunicar visualmente informações que, de outra forma, lidas diretamente,

necessitariam de um esforço bem maior de abstração. Atualmente, os gráficos são amplamente utilizados para

este fim e contam com abundante ferramentas de elaboração por meio eletrônico que agilizam todo este trabalho.

Mesmo com estas facilidades, os gráficos podem facilmente levar a vieses de seleção ou informação dos

dados. Nestes casos, eles podem ter o efeito oposto ao esclarecimento de um trabalho, levando a

supervalorizações de aspectos da pesquisa ou a sua maquiagem. Um exemplo bem típico: mostrar a variação de

uma parte dos dados em um gráfico temporal em um gráfico de linhas, com apenas os valores dos intervalos

indicados nos eixos, pode dar a impressão de uma grande escala de variação, pois não trouxe a referência das

medidas máximas e mínimas do conjunto de dados.

Existem muitos tipos de gráfico no Excel. Eles podem ser escolhidos na aba Inserir > Tipo de gráfico. Se

selecionarmos os dados previamente, o gráfico poderá ser visualizado imediatamente. Podemos, então, posicioná-

lo no lugar mais adequado da planilha e fazer os ajustes dos seus elementos. No nosso curso, “treinaremos”

algumas técnicas de construção de gráficos e aproveitaremos a oportunidade para acrescentar novas informações

sobre a manipulação e organização de dados em uma planilha.

Alguns tipos de gráficos do Excel:

Gráfico de Pizza

Este gráfico é útil principalmente quando dispomos dados totalizáveis e os queremos comparar

em uma escala, de modo que os 100% da pizza sejam visualizados em fatias correspondentes e

proporcionais aos percentuais medidos. Tomando o exemplo da nossa planilha de dados, acrescente

uma nova coluna para o cálculo do índice de massa corporal (IMC) com as informações de peso e altura:

IMC = Peso / Altura²

Agora, use a fórmula =CONT.SE() para classificar o IMC nas seguintes categorias:

Categoria IMC Modelo de fórmula no Excel

Magro < 18,5 =CONT.SE($J$7:$J$27;"<18,5")

Normal >= 18,5 e < 25 =CONT.SE($J$7:$J$27;">=18,5") - CONT.SE($J$7:$J$27;">=25")

Sobrepeso >=25 e < 30 =CONT.SE($J$7:$J$27;">=25") - CONT.SE($J$7:$J$27;">=30")

Obeso >= 30 =CONT.SE($J$7:$J$27;">=30")



Selecione a tabela e escolha o gráfico de pizza 3D. Para ajustar o gráfico, selecione cada um de

seus elementos com um clique e entre com os seus parâmetros de formatação:

- Título do Gráfico

- Legenda

- Área do gráfico

- Área de plotagem

- Ponto de dados

Experimente modificar as configurações 3D como rotação, material, etc. Gráfico de Colunas

Por dispor cada coluna lado a lado, com altura proporcional aos valores dos dados, este tipo de

gráfico destaca a comparação das categorias entre si. Experimente usar os mesmos dados do exemplo

anterior para construir um gráfico de colunas como o mostrado a seguir, selecionando e formatando

seus elementos gráficos.



Vários outros tipos de gráficos também nos permitem comparar dados dispostos em séries. No

nosso exemplo, ao se criar mais uma série destas informações, uma para masculino e outra feminino,

podemos confrontar os dados de um e de outro com as mesmas categorias de IMC, lado a lado. Veja a

tabela abaixo e, a seguir, como a implementar no Excel:

Masculino Feminino

Magro 0 1

Normal 7 9

Sobrepeso 3 2

Obeso 0 0

Para obter esta tabela a partir dos dados, use a fórmula CONT.SES(), como no exemplo abaixo.

Repare que ao entramos com dois ou três conjuntos de dados e as condições como parâmetros,

fazemos prevalecer uma combinação simultânea das condições.

Masculino Feminino

Magro =CONT.SES($C$7:$C$28;"=1";$J$7:$J$28;"<18,5") =CONT.SES($C$7:$C$28;"=2";$J$7:$J$28;"<18,5")

Normal =CONT.SES($C$7:$C$28;"=1";$J$7:$J$28;">=18,5"; $J$7:$J$28;"<25")

=CONT.SES($C$7:$C$28;"=2";$J$7:$J$28;">=18,5"; $J$7:$J$28;"<25")

Sobrepeso =CONT.SES($C$7:$C$28;"=1";$J$7:$J$28;">=25"; $J$7:$J$28;"<30")

=CONT.SES($C$7:$C$28;"=2";$J$7:$J$28;">=25"; $J$7:$J$28;"<30")

Obeso =CONT.SES($C$7:$C$28;"=1";$J$7:$J$28;">=30") =CONT.SES($C$7:$C$28;"=2";$J$7:$J$28;">=30")

Resultado:



Gráfico de Barras

Este tipo de gráfico é de construção semelhante aos gráficos de colunas, diferindo pela posição

das categorias no eixo X e valores no eixo Y. Estes gráficos são bem utilizados quando os nomes das

categorias forem longos ou quando se quer enfatizar a duração de eventos.

Solteiro 21,71

Casado 32,19

Separado 22,29

Gráfico de linhas

Os gráficos de linha, assim como os de coluna, também servem para mostrar categorias no eixo

horizontal. Eles se aplicam melhor em situações que precisamos indicar tendências e continuidade dos

dados. Assim, ele deve apresentar valores distribuídos uniformemente nos dois eixos. O exemplo a

seguir mostra a evolução das notas dos alunos nos meses em que prossegue a disciplina.

1º 2º 3º 4º

TV 6,3 6,5 6,9 7,6

Jornal 7,5 8,5 8,0 9,0

Rádio 6,0 5,0 8,0 7,0

Revista 6,0 6,0 5,0 7,0

Internet 7,8 6,8 8,0 7,3



Gráficos de dispersão

Na escola secundária, quando fazíamos um plano cartesiano com eixo X e Y, e uma função que

relacionava uma variável com a outra, criávamos vários tipos de gráficos a depender da função. Abaixo

exemplos da função exponencial e logarítmica. Use, respectivamente, o endereço das células com ^2

para indicar o expoente quadrado e a função =log10() para cálculo do logaritmo na base 10.

x x^2

-5 25

-4 16

-3 9

-2 4

-1 1

0 0

1 1

2 4

3 9

4 16

5 25

x log10

1 0,00

2 0,30

3 0,48

4 0,60

5 0,70

6 0,78

7 0,85

8 0,90

9 0,95

10 1,00

Muitas medidas que fazemos estão numa escala logarítmica, como a intensidade sonora,

medida de pH, escala Richter de terremotos, etc. Podemos modificar os eixos de referência do gráfico

para que estas medidas sejam melhor representadas e comparadas. Mudando o eixo X do gráfico acima

obteríamos uma reta. Experimente clicando no elemento eixo X do gráfico com o botão direito do

mouse. Na janela, escolha Opções do Eixo e a seguir marque a opção Escala logarítmica.



x log10

1 0,00

2 0,30

3 0,48

4 0,60

5 0,70

6 0,78

7 0,85

8 0,90

9 0,95

10 1,00

Os gráficos de dispersão são muito uteis quando se precisa representar a dependência entre

dados numéricos presentes nos eixos. Estes normalmente irão demonstrar os valores medidos e

projetar os valores intermediários quando os pontos estão ligados. Eles não devem ser utilizados, por

isto, para representar categorias no eixo X.

Para exercício, use a tabela abaixo que mede, hipoteticamente, o consumo diário de

combustível de um veículo de taxi durante o mês de fevereiro.

Consumo do veículo no mês de fevereiro de 2018

Início da medida

47883

Consumo Teórico em km/l

8

Motorista Data Dia útil Odômetro Distância Teórico Real Gasto Rodado João 01/02/2017 1 48150 267 33,38 33,90 33,90 267

Maria 02/02/2017 2 48452 302 37,75 34,75 68,65 569

Pedro 03/02/2017 3 48756 304 38,00 39,00 107,65 873

João 06/02/2017 4 49056 300 37,50 43,50 151,15 1173

Pedro 07/02/2017 5 49350 294 36,75 40,75 191,90 1467

Maria 08/02/2017 6 49644 294 36,75 35,75 227,65 1761

João 09/02/2017 7 49914 270 33,75 41,75 269,40 2031

Pedro 10/02/2017 8 50238 324 40,50 49,50 318,90 2355

João 13/02/2017 9 50527 289 36,13 42,13 361,03 2644

Maria 14/02/2017 10 50844 317 39,63 49,63 410,65 2961

Pedro 15/02/2017 11 51173 329 41,13 49,13 459,78 3290

Maria 16/02/2017 12 51471 298 37,25 40,25 500,03 3588

João 17/02/2017 13 51805 334 41,75 46,75 546,78 3922

Pedro 20/02/2017 14 52105 300 37,50 40,50 587,28 4222

Maria 21/02/2017 15 52396 291 36,38 38,38 625,65 4513

João 22/02/2017 16 52734 338 42,25 40,25 665,90 4851

Maria 23/02/2017 17 53034 300 37,50 41,50 707,40 5151

Pedro 24/02/2017 18 53352 318 39,75 44,75 752,15 5469

João 27/02/2017 19 53624 272 34,00 35,00 787,15 5741



Formate as datas para mostrar somente os dias/meses no eixo X. Use as colunas com o consumo

teórico em uma série e o consumo real em outra série. O gráfico deve parecer com o ilustrado abaixo:

Neste exemplo, vemos que o gráfico poderá ficar com a leitura mais difícil quando somente os

valores de consumo são plotados, sem unir os pontos. Para fazer isto e manter a representação

fidedigna, precisamos desconsiderar os fins de semana que não constam na tabela. Usando apenas o dia

de trabalho do mês como eixo X, selecione um novo gráfico de dispersão com linhas suaves para

descobrir e demonstrar a relação de algum evento relacionado às datas promoveu a variação no

consumo.

Gráfico de áreas

Os dados que normalmente se representam em colunas ou linhas podem ser dispostos em

gráficos de área, quando não há problemas na sobreposição das séries. Os gráficos de área são bem

indicados para exibir valores que se acumulam. Como demonstração, construa a tabela a seguir com o



consumo real do carro durante os dias da semana. Na elaboração do gráfico, considere somente as

semanas que tiveram todos os dias rodados.

Dias\Semanas 1ª 2ª 3ª 4ª 5ª

segunda-feira

43,50 42,13 40,50 35,00

terça-feira

40,75 49,63 38,38 quarta-feira 33,90 35,75 49,13 40,25 quinta-feira 34,75 41,75 40,25 41,50 sexta-feira 39,00 49,50 46,75 44,75

Selecione as colunas dos dias da semana e 2ª, 3ª, 4ª da semana do mês. Use a tecla Ctrl e o

mouse para selecionar colunas alternadas. A seguir, insira um gráfico de barra 3D. O gráfico resultante

deve ser semelhante ao mostrado abaixo:

Sobrepondo gráficos

É possível formatar as séries de um gráfico, criando um novo eixo para a escala e também

alterando o tipo de gráfico para outro. Para elaborar um gráfico semelhante ao que é mostrado abaixo,

siga os passos seguintes:



1 – Crie um gráfico de colunas com o gasto acumulado e os km rodados da tabela

2 – Clique com o botão direito na série “rodado” e no menu escolha Formatar Série de Dados

3 – Em opções da série, escolha Eixo Secundário

4 – Clique novamente com o botão direita na série “rodado” escolha a opção Alterar Tipo de Gráfico da

Série. Na janela, escolha o tipo linha para a série.

Histogramas

Histogramas são funções que medem a frequência de dados contados nos intervalos, ou blocos,

classificados a partir de uma série de dados. Em um gráfico representativo da função, o histograma é,

portanto, a distribuição das medidas feitas em intervalos de classes no eixo X com as suas frequências

apresentadas no eixo Y. Normalmente se elabora um histograma com gráficos de colunas ou barras.

Os histogramas são muito utilizados em analise de dados para controle de qualidade em

indústria, no estudo de variáveis econômicas e em observações de interesse biológico.

Para uso no Excel, é necessário ativar um pacote de ferramentas de análise estatística. Siga os

seguintes passos:

1 – Escolha o menu Arquivo > Opções

2 – Na janela que abre selecione como a seguir:



3 – Clique em Ir...

Com o recurso do histograma instalado, podemos utilizar

1 – Como no nosso cálculo de frequências utilizaremos a coluna de consumo real do combustível,

devemos indicar os intervalos que cobrem estes números que vão do valor mínimo 33,90 até o máximo

49,63. Escolha um local na planilha e digite as células com os blocos de intervalo: 35; 40; 45; 50

1 – Na aba Dados, escolha Análise de Dados

2 – Selecione Histograma na janela

3 – Selecione os intervalos de entrada dos dados e os intervalos de entrada e do bloco. Complete o

restante das opões como mostrado abaixo:



4 – O resultado é mostrado a seguir:

Bloco Freqüência

35 3

40 3

45 10

50 4

Mais 0



Módulo 3: Formatação Condicional

A formatação condicional no Excel permite estipular regras que modificam cores, tamanho de

fontes, ícones ou outros atributos de forma das células da planilha. Como as informações para estas

regras são modificadas durante o trabalho com os dados, o resultado se aplicará dinamicamente e as

células se formatarão automaticamente. Isto é especialmente útil quando se quer destacar informações

dependentes de outras e que servem de alerta para decisões de acréscimos ou correções de dados

durante um trabalho.

Alguns modelos de formatação condicional mais comuns estão disponíveis na aba do menu

Início. Para experimentar, consideraremos que o professor da turma exemplo considera notas

reprovativas as que forem abaixo 5 e que precisa destacar em vermelho estas notas. Para isto fazer isto

na tabela 1 :

1 – Selecione as notas

2 – Acione a opção “Realçar Regras das Células” -> “É Menor do que...”

3 – Indique a nota 5 como parâmetro na opção da janela. Conforme mostra a figura abaixo:



Experimente agora, adicionar novas regras de formatação para o intervalo de células: amarelo

para as notas entre 5 – 6,99 e verde para notas 6,99 e acima. Repare que é possível ter várias regras

para um mesmo intervalo.

Noutro tipo de regra, oferecida pelo menu de Formatação Condiciona do Excel, destaca-se a

classificação de uma série de células. Como exemplo, vamos classificar os 10 alunos mais altos da turma

da Tabela 1:

1 – Selecione a coluna das alturas dos alunos

2 – Ative as opções do menu “Formatação Condicional” -> “Regra de Primeiros / Últimos” -> “10

Primeiros Itens...”

Escolha uma formatação padrão ou “Formato Personalizado” para ajustar as cores e os realces.

Mude a cor de fundo para uma cor de sua preferência na janela.



Gerenciando Regras

Para gerenciar as várias regras que já foram criadas, escolha a opção de menu “Gerenciar

Regras...” Uma janela como a seguinte será exibida. No controle de seleção “Mostrar regras de

formatação para:”, escolha o intervalo para toda a planilha.

Aproveitaremos para fazer alguns ajustes de um exercício anterior: clique no botão “Editar

Regra” e edite as regras do valor da nota do intervalo de 5 a 6,99 para que o destaque amarelo fique

com a nota >=5 e <7. Edite também a regra para que as notas verdes fiquem no intervalo >=7.

Experimente agora criar uma barra de dados para a idade. O máximo da escala é ajustado

automaticamente para o maior valor encontrado. Podemos também configurar para mostrar outro

valor, como na tela seguir, exibida após a edição da regra correspondente. A formatação da idade é

graduada para que permaneça numa escala de 0 a 100.



Para formatar a coluna do IMC, usaremos como exemplo o preenchimento total da célula em

uma escala de cores. Esta opção se encontra no menu “Formatação Condicional” -> “Escala de Cor”. A

escala do IMC normalmente varia de modo crescente ou decrescente entre vermelho e verde, passando

pelo amarelo. Neste caso, o destaque vermelho fica entre os extremos e o verde no centro. É

recomendável usar cores com tons claros para não ofuscar o texto das células. Como fazer:

1 – Selecione a coluna do IMC

2 – Acione no menu “Formatação Condicional” -> “Escala de Cor” -> “Mais Regras...”

3 – No “Estilo de Formatação” da janela, escolha a opção “Escala Tricolor”

4 – Para as colunas “Mínimo”, “Ponto Médio” e “Máximo”, escolha as opções conforme a figura abaixo :

Também é possível acrescentar uma escala com ícones neste mesmo conjunto de células, assim

teremos uma segunda regra de formatação usada simultaneamente à primeira. Para fazer isto

funcionar, primeiro conclua esta regra fechando a janela no botão OK. Faça o mesmo caminho até

chegar nesta mesma janela. Modifique o “Estilo de Formatação” para “Conjunto de Ícones” e o estilo de

Ícones para . Edite a regra para que fique como na janela mostrada abaixo:



Confira o resultado da formatação em um fragmento da tabela:

Construindo as suas próprias regras

Para criar novas regras, utilize diretamente a opção do menu: “Nova Regra...”. Um caminho

alternativo para esta mesma tarefa é com o uso da opção deste menu “Gerenciar Regras...”. Seguiremos

por este meio para exemplificar a construção de novas regras na coluna “Sexo” da tabela 1. Passo a

passo:

1 – Selecione os dados da coluna que indicam o sexo.

2 – Escolha opção “Gerenciar Regras” para exibir a janela seguinte:



3 – Clique no botão “Nova Regra” para exibir a janela abaixo, escolha a opção indicada e digite a fórmula

=C7=1 que condicionará a formatação para o código 1, ou seja, se for do sexo masculino. Formate a

visualização. Utilizaremos a cor azul para masculino e vermelho para feminino, conforme o padrão de

cores tradicionalmente usado.

4 – Clique OK e crie nova regra, repetindo as operações e a formatação para o sexo feminino.

5 – Confira se o resultado é semelhante ao mostrado abaixo e clique em aplicar para ver o resultado na

planilha, sem fechar a janela. Se o resultado não for o esperado, clique em Editar Regra para refazer a

formatação ou OK para sair.



Formatando linhas ou colunas alternadamente

Muitas vezes precisamos destacar uma linha ou coluna inteira para evitar que ela se confunda

com uma outra adjacente. Para obter este efeito, devemos considerar a formatação para a linha/coluna

conforme ela seja par ou ímpar. Os passos são semelhantes ao descrito anteriormente, mas devemos

utilizar as fórmulas para linhas ou colunas, respectivamente como se seguem:

= MOD (LIN(); 2)=0 = MOD (COL(); 2)=0

A função MOD() traz o resto da divisão para dividendo e divisor, fornecido como seus

argumentos. O dividendo é a referência da própria célula que está sendo formatada. Para as linhas, é

retornada pela função LIN() e para as colunas pela função COL(). Se o resto da divisão for zero, a linha é

par.

Encontrando valores duplicados

Quando necessitamos encontrar valores duplicados dentro de uma lista, podemos nos valer de

uma opção específica do Excel dentro das disponíveis no menu de “Formatação Condicional”. Para

testar esta funcionalidade, siga os passos:

1 – Abra uma nova planilha

2 – Posicione em uma de suas células e digite a fórmula abaixo para criar números aleatórios entre dois

valores:

=ALEATÓRIOENTRE(1;20)

3 – Copie esta célula para as 20 seguintes da mesma coluna.



4 – Selecione estas células, copie (Ctrl-C) e Cole Especial Somente Valores. Esta operação é necessária

para que se evite o cálculo de um novo número aleatório cada vez que a célula for atualizada.

5 – Escolha a opção de menu do Excel: Formatação Condicional > Realçar Regras das Células >

Valores Duplicados e selecione uma cor de preferência para a formatação.

Para buscar valores que estão em uma lista e podem estar duplicidade em outra lista, podemos

utilizar uma função de busca na formatação condicional para destacar os valores repetidos. Faça passo a

passo:

1 – Crie duas colunas aleatórias, conforme descrição anterior

2 – Selecione a primeira e acione no menu “Formatação Condicional” a opção “Gerenciar Regras”.

Escolha “Nova Regra” > “Usar fórmula para determinar quais células devem ser formatadas”.

3 – Entre com a fórmula indicada abaixo, onde $C$1:$C$20 é o intervalo de busca e deve ter suas células

com referência fixa. "="&A1 é uma expressão textual que soma o sinal “=” à primeira célula que se está

formatando. Repare que a sua referência não é fixa, pois o Excel a usará de modo relativo para calcular

as outras células.

=CONT.SES($C$1:$C$20; "="&A1)>0

4 – Identifique o intervalo no qual a formatação se aplica, como demonstrado a seguir:

Construindo um diagrama de Gantt

O diagrama de Gantt é um gráfico que exibe o tempo decorrido entre o início e o fim da etapa

de um projeto. É bastante usado para gerenciar o planejamento e a execução das várias tarefas

atribuídas as equipes envolvidas no trabalho. O diagrama de Gantt facilita a análise de desempenho,

prioridade das tarefas e identificação de possíveis gargalos e sobrecarga de atividades, fatores que, bem

regulados, permitirão aumento da produtividade e redução dos custos do projeto.



Existem muitos modelos de diagrama de Gantt, muitos deles em aplicativos especializados no

gerenciamento de projetos. Utilizaremos o conhecimento que já temos em formatação condicional para

elaborar nosso modelo. Ele servirá como um exercício do nosso curso e poderá também servir como um

rascunho inicial para quem quiser aperfeiçoar e adaptar para posterior uso prático. A tela a seguir

mostra o resultado que iremos buscar:

Passos para elaboração do diagrama de Gantt:

1 – Digite os rótulos textuais das células do canto superior da tarefa

2 – Digite as datas iniciais nas células F2 e G2. Selecione-as e faça a expansão delas para preencher as

células com as datas seguintes. Caso queira considerar todo o ano, a expansão irá até a coluna NF que

conterá a data 31/12, no nosso exemplo.

3 – Selecione todas as colunas das datas e deixe suas larguras e formatos como mostrado acima. Para

destacar os fins de semana, sábado e domingo, selecione todas as células do intervalo de datas e no

menu da “Formatação Condicional” do Excel, selecione “Gerenciar Regras”. Na janela escolha “Nova

Regra”. Na janela seguinte indique a fórmula que segue e escolha um formato para destacar os fins de

semana. A função OU() do Excel retornará verdadeiro se qualquer um dos argumentos fornecidos for

verdadeiro.



4 – Para realçar os meses alternadamente, com número par, crie uma nova regra com esta mesma

seleção de datas e entre com a fórmula seguinte para destacar os meses pares

=MOD(MÊS(F2);2)=0

5 – Criaremos agora uma regra para as células que se formatarão conforme o intervalo de datas. Para

isto selecione o intervalo. Neste exemplo, usamos a matriz que abrange as células F3 até NF42.

6 – Escolha a opção do menu “Gerenciar Regras” e na janela clique no botão “Nova Regra” para acessar

a janela seguinte:

7 – Para criar a faixa correspondente às datas das Tarefas Projetadas, digite a fórmula condicional de

formatação, como mostrado na figura. A função E() combina as condições de maneira que o seu



resultado será verdadeiro somente se todos os argumentos que ela contém forem verdadeiros. Repare

que indicamos somente a condição para a primeira célula do intervalo das datas e fixamos apenas a

linha F$2. Esta deve ser maior ou igual a $B3, data de início da tarefa projetada, e menor ou igual a $C3,

data de fim da tarefa projetada. Desta vez fixamos apenas a coluna.

8 – Clique no botão Formatar para escolher o padrão de preenchimento da célula. Na janela, escolha

como mostrado abaixo:

9 – Para os intervalos de datas das Tarefas Realizadas, o procedimento é semelhante ao que fizemos

para as Tarefas Planejadas. A fórmula usada é mostrada na figura da janela:



10 – Por fim, precisaremos também formatar as células que são simultaneamente ocupadas pelas datas

das Tarefas Projetadas e Tarefas Realizadas. Elas deverão aparecer no diagrama como células

sobrepostas umas às outras e por isso devemos formatar uma visualização que transmita este efeito. A

fórmula é mostrada na figura:



11 – Confira na ilustração a seguir, as regras que construímos para a planilha do gráfico de Gantt.

Atente-se para a ordem pela qual as regras de formatação são aplicadas na célula. Para ajustar esta

sequência, empregue os botões .



Módulo 4 – Tabela Dinâmica

A Tabela Dinâmica é um recurso do Excel que facilita a consolidação dos dados disponíveis em

forma de tabelas. Estas são automaticamente recalculadas sempre que aqueles sofrem correções ou

adições. As tabelas construídas por este meio são usadas principalmente na análise de dados,

permitindo fazer agrupamentos, totalizações e cruzamentos das variáveis envolvidas. Além do exame

dos dados, as tabelas também ajudam na comunicação das informações, disponibilizando-as em uma

forma mais legível e comunicativa.

Mesmo com esta grande capacidade de reelaboração dos dados, fazer uma tabela dinâmica é

relativamente simples e rápido. Para começar, é preciso o entendimento claro da estrutura da tabela e

de como ela é calculada no Excel.

Média de Peso Rótulos de Coluna

Fora do PR Maringá Outro no PR Rótulos de Linha

Casado

36 30

Separado

22

Solteiro 22 21 28

Os elementos mais claramente perceptíveis na estrutura da tabela são as linhas e as colunas.

Neles as categorias são ordenadas e agrupadas pelo seu nome. Já os valores, podem ser computados

como soma, contagem, média ou outro cálculo agregador dos dados. Eles ficam na interseção

(cruzamento) de linhas e colunas e devem atender simultaneamente as duas condições especificadas

nas linhas e nas colunas. Repare que o valor pode vir de algum outro campo da tabela. Exemplo: uma

tabela com estado civil para as linhas, sexo para colunas e salário para valores.

Cada conjunto de linhas e/ou colunas pode se apresentar em subníveis de agrupamentos, como

mostrado na tabela a seguir:

Colunas

Linhas

Valores



Média de idade pesq. Rótulos de Coluna

Fora do PR Maringá Outro no PR Rótulos de Linha Feminino Masculino Feminino Masculino Feminino Masculino

Casado Internet

25 TV

36

35 Separado

Internet

22 Solteiro

Internet

20

21 Jornal

23 21 Rádio

20 Revista 26

TV

20 20 18 27 33 Criando tabelas dinâmicas

Em nosso curso, neste módulo, usaremos para demonstração e exercício a Tabela 1. Para

preparar o seu uso na elaboração das tabelas dinâmicas, crie uma coluna de rotulagem para cada coluna

de código da tabela usando a função =PROCV(). Para produzir uma nova tabela dinâmica, vamos seguir

estes passos:

1 – Selecione a tabela

2 – Clique na aba “Inserir” e no botão de controle “Tabela Dinâmica” para criar uma nova tabela.

Escolha as opções na janela e confirme no botão OK

3 – A seguir, use a janela “Lista de campos da tabela dinâmica” para selecionar os campos de filtro,

linhas, colunas e valores.



4 – A tabela resultante será como a mostrada a seguir:

5 – Experimente criar e aplicar um filtro na tabela selecionando uma ou mais categorias do estado civil.



6 – Para acessar os filtros e opções das linhas ou colunas, clique na caixa de combinação

correspondente:

7 – Clique sobre a tabela dinâmica para visualizar a janela “Lista de campos da tabela dinâmica”. Caso

ela não seja exibida automaticamente, clique com botão direito do mouse e selecione a opção

correspondente do menu.

8 – Nesta janela, arraste o campo “Altura” para a caixa “Valores”. Note que, automaticamente, o Excel

irá sugerir “Soma de Altura”. Queremos o cálculo da média, então, clique neste campo e escolha a

opção de “Configuração do Campo de Valor...” para modificar este tipo de cálculo:

8 – Escolha a opção média e aproveite para ajustar o formato do número, clicando no botão

correspondente para esta ação:



9 – Confira o resultado:

10 – Ajuste agora as configurações de “Mostrar valores como” para “% da coluna”:

11 – Efeito na tabela:



12 – Repare que, na janela “Lista de campos da tabela dinâmica”, aparece automaticamente o campo

“Valores” na caixa “Rótulos de Coluna”. Este campo pode ser arrastado para a área dos “Rótulos de

Linha”. A tabela é refeita para a seguinte estrutura:

Fazendo cruzamentos com os campos das colunas

Para construirmos uma tabela com colunas, semelhante à apresentada no início deste módulo,

siga os passos seguintes:

1 – Selecione a Tabela 1, que serve de exemplo no nosso curso

2 – Insira uma nova tabela dinâmica e a direcione para uma nova planilha

3 – Na janela da lista de campos, configure como abaixo:



4 – Clique no campo da caixa “Valores” para abrir a janela de ajuste da configuração do campo. Escolha

o tipo de cálculo para média e o formato do número para zero casa decimal.

5 – Note que numa tabela dinâmica com subníveis, é possível diminuir/aumentar a exibição dos

detalhes de linhas ou colunas escondendo/mostrando seus subníveis quando se clica respectivamente

nos botões ou da tabela.

6 – O cálculo dos subtotais das colunas pode ser exibido ou não na tabela dinâmica, dependendo da

necessidade do seu trabalho. Para este ajuste, clique sobre os rótulos de cada coluna e escolha a opção

do menu de contexto “Configurações do Campo...”. Uma janela como a seguinte é mostrada na tela do

Excel:



7 – Para configurar toda a tabela escolha, neste menu, “Opções da Tabela Dinâmica...” onde será

acionada a janela:



Ferramentas da tabela dinâmica

Quando se clica dentro da tabela dinâmica, é possível observar no menu superior do Excel a aba “Ferramentas da

Tabela Dinâmica” que abriga duas outras abas: “Opções” e “Design”. Nelas entraremos os principais atalhos para

se modificar as configurações e apresentação das tabelas dinâmicas.

Localize no menu “Fórmulas” para acessar os comandos necessários para criar campos e itens calculados como

mostrado na figura abaixo (Excel 2007) :

Item calculado

Como exemplo, criaremos uma nova categoria para o campo Estado Civil, “Não Casado”, calculado a partir das

categorias usadas como “Solteiro” e “Separado”.

1 – Crie uma nova tabela dinâmica usando a Tabela 1

2 – Selecione Estado Civil para o Rótulo das Linhas

3 – Arraste o campo “Cod.Estado Civil” para Valores e modifique as suas configurações para que seja calculada a

contagem do campo

4 – Clique na área das linhas da tabela dinâmica para que a opção “Item calculado...” fique disponível no menu

5 – Selecione “Item calculado...” para exibir a janela de configuração e preencha os campos como segue:



6 – Após confirmar OK, podemos notar o novo campo nas colunas de dados com o resultado da soma das duas

categorias. Atente para os totais que serão diferentes nestes casos as categorias são somadas mais de uma vez.

Campo calculado

Para o campo calculado, usaremos a Tabela 2 como exemplo e consideraremos a necessidade de se medir a

produtividade dos motoristas pelos quilômetros que eles rodaram no mês. Cada um deles terá uma meta mensal a

ser cumprida. Para informar isso, adicionaremos uma coluna na tabela com o nome “Meta”. Nas últimas linhas da

tabela acrescentaremos os nomes dos motoristas e, na coluna correspondente, a meta do mês. Este número pode

ser variado conforme o desempenho alcançado pelo motorista no mês anterior.

Com a tabela de dados pronta, vamos criar a tabela dinâmica e utilizar um campo calculado para calcular o

percentual da meta atingido por cada motorista. Seguiremos os passos seguintes:

1 – Selecione a tabela de dados e insira uma tabela dinâmica para uma nova planilha, conforme os passos já

descritos em exemplos anteriores.

2 – Configure a Lista de campos da tabela dinâmica para ficar como mostrado a seguir:

3 – Localize o item “Campo Calculado”, na opção “Cálculo” da aba “Ferramentas de Tabela Dinâmica” / “Opções”.

Será exibida uma janela que deve ser configurada como na figura abaixo. Clique no botão “Adicionar” para

registrar o campo calculado. :



4 – Será acrescentada uma nova coluna na tabela dinâmica e um novo campo será adicionado à lista de campos na

caixa de “Valores” com o nome de “Soma de Meta Atingida”. Configure este campo, formatando os seus números

e dando um nome personalizado para ele, de modo que a tabela resultante fique como mostrada a seguir:

Gráficos dinâmicos

Os gráficos dinâmicos são gerados a partir das tabelas dinâmicas. Eles acrescentam a possibilidade de

configurações no próprio gráfico. Para experimentar, siga os passos:

1 – Crie uma nova tabela dinâmica (ou aproveite a anterior desmarcando o campo calculado “% Meta”)

2 – Clique dentro da tabela para ativar a aba “Ferramentas de Tabela Dinâmica”

3 – Localize a opção “Gráfico Dinâmico”

4 – Selecione gráfico de barras para mostrar um gráfico como o da figura:

5 – Use os controles “Data” e “Motorista” para filtrar o gráfico.

Documents

Excel Intermediário - EDAPedap.diadema.sp.gov.br/edap/images/Materiais/Apostila_Excel... · O Excel dispõe de muitos recursos para a realização das tarefas mais básicos de estatística