51
PLANILHA ELETRÔNICA EXCEL Euclides Braga MALHEIROS 1 Para uma melhor organização didática, esta apostila está subdividida em aulas, sendo que para cada aula é feita uma abordagem de alguns tópicos, e então é apresentada uma lista de exercícios referente a esses tópicos. AULA 1 1.1. Introdução ao Excel O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office, que possui recursos para a edição de planilhas eletrônicas, com muita facilidade para fazer cálculos, gráficos, etc. As características principais do Excel são: Cada ambiente de trabalho é considerada como uma pasta de trabalho, que contem várias planilhas (tabelas). A lista de planilhas de uma pasta de trabalho é mostrada na base da área de trabalho, e é denominada guia. Cada planilha é um conjunto finito de linhas e colunas, em forma de tabela. As linhas são numeradas (1, 2, 3, ... , 65536) e as colunas letradas (A, B, C, ..., AA, AB , AC, ... AZ, BA, BB, BC, ... BZ, ... ... IV). Ou seja, uma tabela de 256 x 65536. 1 Departamento de Ciências Exatas – FCAV/UNESP, Campus de Jaboticabal, 14884-900 Jaboticabal SP Guia Barra de fórmulas Célula selecionada

PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

  • Upload
    doannhu

  • View
    250

  • Download
    0

Embed Size (px)

Citation preview

Page 1: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

PLANILHA ELETRÔNICA EXCEL

Euclides Braga MALHEIROS1 Para uma melhor organização didática, esta apostila está subdividida em aulas, sendo que para cada aula é feita uma abordagem de alguns tópicos, e então é apresentada uma lista de exercícios referente a esses tópicos. AULA 1

1.1. Introdução ao Excel O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office, que possui recursos para a edição de planilhas eletrônicas, com muita facilidade para fazer cálculos, gráficos, etc.

As características principais do Excel são:

��Cada ambiente de trabalho é considerada como uma pasta de trabalho, que contem várias planilhas (tabelas). A lista de planilhas de uma pasta de trabalho é mostrada na base da área de trabalho, e é denominada guia.

��Cada planilha é um conjunto finito de linhas e colunas, em forma de tabela. As linhas são numeradas (1, 2, 3, ... , 65536) e as colunas letradas (A, B, C, ..., AA, AB , AC, ... AZ, BA, BB, BC, ... BZ, ... ... IV). Ou seja, uma tabela de 256 x 65536.

1 Departamento de Ciências Exatas – FCAV/UNESP, Campus de Jaboticabal, 14884-900 Jaboticabal SP

Guia

Barra de fórmulas

Célula selecionada

Page 2: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

2

Cada retângulo do quadriculado é denominado célula e é referenciada pela letra da coluna seguido pelo número da linha

Exemplo: A6 refere-se à célula da coluna A e linha 6.

Ao digitar o conteúdo de uma célula, os valores numéricos são alinhados automaticamente à direita e os não numéricos à esquerda.

As datas, como na maioria dos aplicativos, são armazenadas como um número, que é o número de dias da data especificada até o último dia do século XIX (31/12/1899).

Por exemplo. Se você digitar a data 31/12/2000, e formatar esta célula como número, terá o valor 36891, que é o número de dias de 31/12/1899 a 31/12/2000.

31/12/1899 31/12/2000

1.2. Ponteiros do Excel

� Obtido quando se aponta o cursor dentro da planilha. Usado para a seleção de

célula(s)

A faixa selecionada é apresenta como se segue:

A faixa selecionada é representada pela célula do canto superior esquerdo e a do canto inferior direito, separadas por : (dois pontos).

Ex.: A faixa da figura anterior é: B3:D6

Para selecionar uma faixa com células não consecutivas, basta usar a tecla CTRL, exemplo:

Especificado como: A1:A6;C1:C6

36891 dias

Primeira célula da faixa

Alça da faixa

Borda da faixa

Page 3: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

3

ê Obtido quando se aponta o cursor para a borda da seleção. Usado para mover a faixa selecionada.

9 Obtido quando se aponta o cursor para a alça da seleção. Usado para copiar a faixa selecionada.

Obs.: Dois cliques implica no ajuste automático da largura da coluna, com base no elemento mais largo.

Obs.: Dois cliques implica no ajuste automático da altura da linha (altura padrão).

1.3. Outras operações na planilha:

��Edição de uma célula: Dois cliques na célula ou um clique na barra de fórmulas entra no modo edição.

��Movimentação pela planilha:

° Shift seguido de uma das setas (����, ����, ���� ou ����) - Move na direção especificada até encontrar uma célula na situação contrária (ocupada ou desocupada)

° CTRL + Home - Move para o início da planilha

° CTRL + End - Move para o final da planilha

��Alinhamento do conteúdo das células: Ferramentas apropriadas

��Formatação de células numéricas: Selecione a opção de menu:

° FORMATAR / CÉLULAS / NÚMEROS, e então as opções desejadas,

° ou as ferramentas apropriadas

��Cor de fonte e preenchimento: Ferramentas apropriadas:

Obtido quando se aponta o cursor entre os números de linhas. Usado para alterar manualmente altura de linhas.

Obtido quando se aponta o cursor entre as letras de colunas. Usado para alterar manualmente largura de colunas.

Valores monetários Valores em porcentagem Separação dos milhares Aumenta casas decimais Diminui casas decimais

Cor do preenchimento Cor da fonte

Page 4: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

4

��Edição de fórmulas: ° Selecione a célula que conterá o resultado da fómula. ° Digite "=" ° Defina a fórmula que pode conter: Referências a outras células, Operadores,

Constantes, Funções, etc. (Os operadores aritméticos são +, -, *, / e ^, para adição, subtração, multiplicação, divisão e potenciação, respectivamente)

° Tecle ENTER Edição de funções:

° Selecione a ferramenta Funções ° Selecione a categoria da função ° Selecione a função ° Selecione o atributo da função (uma célula ou uma faixa de células) ° Tecle ENTER

Agumas exemplos de funções:

Categoria estatística: Média, Mínimo, Máximo, Média, Variância, etc.

Categoria lógica: Função SE.

Sintaxe: SE(teste_lógico;valor_se_verdadeiro,valor_se_falso)

Categoria data e hora: ANO(data) - retorna o ano da data especificada;

AGORA() - retorna a data do sistema

��Tipos de endereçamento: Em uma fórmula, a referência a outra célula pode ter um endereçamento Relativo, Absoluto ou Misto.

° Endereçamento Relativo. Quando na fórmula, é levado em consideração a relação da célula referenciada com a célula origem.

Exemplo:

Neste caso, a fórmula da célula C1 tem endereçamentos relativos, ou seja: A segunda célula à esquerda multiplicada pela primeira à esquerda.

° Endereçamento Aboluto. Quando na fórmula, é levado em consideração a célula referenciada.

Exemplo:

Page 5: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

5

Neste caso, a fórmula da célula D3 tem endereçamento relativo para o primeiro termo do produto e endereçamento absoluto para o segundo (uma célula à esquerda multiplicada pela célula C1). A notação $C$1 indica: fixando-se a coluna C e fixando-se a linha 1. As alterações no tipo de endereçamento podem ser obtidas com o uso da tecla F4.

° Endereçamento Misto. Quando a célula referenciada tem referência relativa para a linha/coluna e absoluta para a coluna/linha.

Exemplo:

Neste caso, a fórmula da célula B2 tem endereçamento misto. Multiplica o elemento da coluna A, linha relativa, pelo elemento da linha 1, coluna relativa.

Page 6: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

6

Lista de Exercícios do Excel – LISTA 1

1. Digitar a planilha abaixo:

ESTÂNCIA IRIS PRODUÇÃO DE REBANHO

VACA PAI D. NASC. D. PARTO SEXO PR. LEITE PR.GORD. 2 1 04/02/93 18/08/95 M 3556,66 323,12 1 3 15/02/93 22/08/95 F 3865,35 327,37 4 5 07/03/93 18/10/95 F 3936,77 329,68 3 2 12/ 03/93 07/11/95 F 4353,14 320,62 5 5 29/03/93 15/10/95 M 4035,01 288,72

10 5 05/04/93 20/11/95 M 4441,66 285,31 8 2 10/04/93 29/11/95 F 3681,1 327,99 9 1 22/04/93 22/10/95 M 3621 285,74 6 4 03/05/93 06/11/95 M 3992,49 306,53 8 2 22/04/93 17/12/95 F 4351,99 288,42

1.1. Modificar o título de ESTÂNCIA IRIS para ESTÂNCIA ARCO-IRIS

1.2. Movimentar para o final da planilha

1.3. Movimentar para o início da planilha

1.4. Movimentar para o final da primeira linha da planilha

1.5. Movimentar para o início da primeira linha da planilha

1.6. Incluir duas colunas:

% GORDURA = PROD. DE GORDURA/PROD. DE LEITE * 100;

IDADE AO PARTO = DATA DO PARTO - DATA DO NASCIMENTO

1.7. Centralizar os Dados das colunas VACA, PAI e SEXO.

1.8. Formatar as colunas PR.LEITE e PR.GORD. COM 2 casas decimais e % GORD. e ID.PARTO COM 4 casas

1.9. Ajustar a largura das colunas

1.10. Colocar bordas criando tipo de uma tabela

1.11. Colocar cores de forma a melhorar a apresentação

1.12. Salvar a planilha, no drive A:, abaixo da raiz, com o nome P1.XLS

Page 7: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

7

2. Acessar a Pasta 2 e digitar a planilha abaixo: DOLAR DO DIA: 0,95

PRODUTO Nº ITENS PREÇO UNIT. VAL. ESTOQUE PREÇO UNIT. VAL. ESTOQUE EM R$ EM R$ EM US$ EM US$

LAPIS 50 1,25 CANETA 120 3,4 BORRACHA 200 0,75 DISQUETE 45 8,5

2.1. Preencher as células vazias usando expressões apropriadas e a opção de cópia

2.2. Inserir 5 linhas em branco antes da planilha

2.3. Excluir uma das linhas em branco

2.4. Colocar o título “BALANÇO DA LIVRARIA XTX” em uma caixa de texto, com moldura sombreada e cor vermelha

2.5. Colocar bordas e cores a seu critério

2.6. Gravar a planilha com o nome P2.XLS

2.7. Sair do EXCEL

Page 8: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

8

AULA 2

2.1. Funções (continuação) Além das funções apresentadas na aula anterior, existem muitas outras funções disponíveis no Excel. Para esta aula serão usadas as funções: a) Função SOMA: Use SOMA(faixa de valores) ou a ferramenta da barra de

ferramentas padrão. b) Função para gerar números aleatórios: A função ALEATÓRIO( ) gera um número

aleatório entre 0 e 1. c) Tomadas de decisão com 2 alternativas. Para as tomadas de decisão com apenas 2

alternativa (verdadeiro ou falso) usa-se a função SE , que tem a sintaxe:

SE(Teste_Lógico; Valor_se_Verdadeiros, Valor_se_Falso). d) Tomadas de decisão com 3 ou mais alternativas. Quando o número de alternativas

for maior que 2, pode-se duas opções:

Considere o exemplo: A partir da Média de um aluno, obter a situação que pode ser: Reprovado se 0≤Média<3; Recuperação se 3≤Média<5 e Aprovado se Média≥5

Esquematicamente, pode-se representar:

0 3 5

c.1) Funções SE's encaixadas.

Neste caso, um primeiro SE testa se Média é <3, se for verdadeiro o resultado é "reprovado" e se for falso faz-se um novo SE para testar se Média é <5 ou não. Veja como fica na prática:

Reprovado Recuperação

Aprovado

Média

Page 9: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

9

c.2) Função PROCV.

A função PROCV (Procura Valores) tem a sintaxe:

PROCV(valor_procurado;matriz_tabela; num_índice_coluna; ...) Para usar esta função deve-se digitar uma Tabela de alternativas na tomada de decisões, ver exemplo a seguir. O primeiro argumento da função (valor_procurado) especifica a célula, a partir da qual será tomada a decisão. O segundo argumento (matriz_tabela) especifica o Tabela de alternativas (observe que o endereçamento é absoluto). O terceiro argumento (num_índice_coluna) especifica a coluna da Tabela de alternativas que contem o valor a ser retornado.

2.2. Classificação de registros (linhas)

Para classificar os registros (linhas) de uma planilha em relação a uma das variáveis (colunas), pode-se usar:

a) As ferramentas da barra de ferramentas Padrão. Estas ferramentas classificam pela primeira coluna da faixa. Neste caso, a faixa selecionada não pode conter a linha de títulos de colunas.

b) Usando as opções de Menu: Dados / Classificar, que classifica pela coluna selecionada, podendo ter até 3 chaves de classificação. Neste caso, a faixa selecionada pode conter ou não a linha de títulos de colunas, o que deve ser especificado na Caixa de Diálogos. Se a linha de títulos de colunas fizer parte da faixa, as colunas são referenciadas pelos seus títulos, caso contrário, pela letra das colunas.

Page 10: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

10

2.3. Divisão de janelas.

Para dividir janelas, use as opções de Menu: Janela / Dividir. A divisão é feita acima e à esquerda da célula selecionada. Para remover a divisão use Janela / Remover Divisão. a) Se a célula selecionada for da primeira coluna da planilha, divide em duas janelas, no

sentido horizontal. b) Se a célula selecionada for da primeira linha da planilha, divide em duas janelas, no

sentido vertical. c) Se a célula selecionada não for da primeira linha e nem da primeira coluna da planilha,

divide em quatro janelas, acima e à esquerda da célula selecionada.

2.4. Bordas, alinhamentos e mesclagem. Estes efeitos estão disponíveis nas opções de Menu: Formatar / Células. a) Bordas: Selecione as células que terão bordas e use Formatar / Células / Borda, e

então selecione a cor, o estilo e a(s) posição(ões) da(s) borda(s). b) Alinhamentos: Selecione as células a serem alinhadas e use Formatar / Células /

Alinhamentos, e então selecione os alinhamentos (vertical e horizontal) e a direção. c) Mesclagem: Selecione as células a serem mescladas e use Formatar / Células /

Alinhamentos, e então selecione a opção Mesclar células.

5.) Endereçamento misto.

Além dos endereçamentos relativos e absolutos exemplificados na aula anterior, existem situações em que se precisa de um endereçamento misto. Para exemplificar, veja como se pode completar uma Tabela com as tabuadas de 2 a 5.

Observe que no primeiro termo do produto, fixa-se a coluna A (verde) e não a linha e no segundo termo fixa-se a linha 1 (amarela) e não a coluna.

Page 11: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

11

EXCEL 5.0 SEGUNDA AULA 1. Carregar a pasta de Trabalho AULA2.XLS, que se encontra no drive A. Na planilha Exerc1 encontram-se os dados:

VENDAS DO PRIMEIRO SEMESTRE

VENDEDOR JAN FEV MAR ABR MAI JUN TOTAL COMIS.

LÚCIA FLÁVIA FERREIRA 1200,34 1000,32 1324,29 1431,56 1254,65 1345,00 JUSTINO CAXIAS DURÃO 930,29 921,34 1088,34 985,10 850,12 CESAR AUGUSTO GENÉSIO 1422,15 1324,12 1250,25 957,75 1125,45 OFÉLIA REBOLSAS 772,19 1043,54 878,47 985,14 1030,50 985,12 PEDRO JOSÉ CANELA 1426,95 1140,45 1200,55 1250,85 BENEDITO FISHER 1368,69 1139,23 854,90 1020,25 852,25 925,75 ISOLINA RÚBIA PALITO 1010,26 1098,41 1144,85 1250,20 1245,45 1258,65 DARIO CARNEIRO PACHECO 1336,05 863,60 839,18 950,50 1005,15 995,45 ROSALINA PEREIRA 1189,73 1413,87 1382,75 1358,85 1224,00 1352,45

TOTAL

VENDA MÍNIMA NO SEMESTRE : VENDA MÁXIMA NO SEMESTRE: MÉDIA GERAL DE VENDAS: TOTAL DE VENDAS: TOTAL DE COMISSÕES:

• Colocar cores e bordas de forma a melhorar a visualização. • Completar a planilha. A comissão será 5% se TOTAL < 6.000,00; 6% se 6.000,00 ≤

TOTAL ≤ 6.500,00; e 7% se o TOTAL > 6.500,00 (Use a função PROCV). • Colocar os vendedores em ordem decrescente do TOTAL DE VENDAS. Para

TOTAIS iguais, em ordem alfabética dos Vendedores. • Gravar a Planilha. • Dividir a janela - sentido horizontal, deixando a linha com títulos das colunas em

uma outra janela. • Remover a divisão de janelas

2. Numa nova Planilha, digitar:

I. NOM

ES Pr.1 Pr.2 Pr.3 Média Situação

II. JOSE 5.00 4,50 3,00 PEDRO 8,00 7,50 2,50 ANTONIO 3,50 6,00 2,00

• Completar a planilha (Use SE's encaixados). ° Média é a Média aritmética das 3 provas ° Situação = Reprovado se 0≤Média<3 , Situação= Recuperação se 3≤Média<5 e

Situação= Aprovado se Média≥5. • Colocar os nomes das provas verticalmente, de baixo para cima.

Page 12: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

12

• Centralizar horizontalmente e verticalmente as Palavras NOMES e SITUAÇÃO • Centralizar as palavras NOMES e TOTAIS horizontalmente e verticalmente. Use

FORMATAR/CÉLULAS/ALINHAMENTO. 3. Numa nova planilha digitar:

Nº gerado Variância dos Nºs

anteriores 1 2 3 4 5

• Completar a planilha ( para gerar os números use a função aleatório( ) ) 4. Numa nova planilha digitar: 1 2 3 4 5 6 7 8 9 10

1 2 3 4 5 6 7 8 9

10 • Completar a planilha como uma tabela de multiplicação.

Page 13: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

13

AULA 3

3.1. Formatação condicional de células. Para formatar células (fonte, cor, estilo, bordas, etc.) dependendo de condições, selecione a faixa que deseja formatar e use a opção de Menu: Formatar/Formatação Condicional, e então selecione a condição e a formatação desejada. Quando se tem múltiplas condições, basta especificar a primeira condição, a tecla Adicionar, a outra condição, e assim por diante.

3.2. Formatação automática de tabela Formata faixa de células em forma de tabelas (efeitos visuais) usando alguns modelos especiais. a. Selecione a faixa de células a ser formatada b. Selecione a opção Formatar / Autoformatação c. Selecione o formato de Tabela desejado.

3.3. Ocultar e reexibir colunas.

Para ocultar colunas selecione pelo menos uma célula da coluna e a opção de menu: Formatar/Coluna/Ocultar, que as colunas serão ocultas. Para reexibí-las, basta selecionar uma faixa de células que contenha as colunas a serem reexibidas e a opção Formatar/Coluna/Reexibir.

3.4. Mesclar e centralizar.

A opção de mesclar e centralizar horizontalmente uma faixa de células selecionadas, pode ser feita pela opção Formatar/Células já estudada ou a ferramenta mescla e centraliza ( ) da Barra de Ferramentas Formatação.

3.5. Colar especial

Quando se copia de um local para outro (opção copiar / colar) uma célula que contem uma expressão com referências a outras células, a copia é feita da expressão com as referências. Com a opção Colar especial, pode-se determinar se vai copiar a expressão, o valor da expressão, formatos, e outras opções. Para usar esta opção: a) selecione a faixa a ser copiada (origem) e jogue para a área de transferência (copiar), b) Selecione a primeira célula do local para onde será copiado (destino), c) Selecione Editar / Colar especial, d) Selecione a alternativa desejada, e então OK

Esta opção permite inclusive transpor a faixa de valores. As linhas passam em colunas e as colunas em linhas.

3.6. Configuração da planilha para impressão.

Page 14: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

14

Para imprimir uma planilha, antes deve-se configurá-la. Para isso, selecione a ferramenta Configura impressão ( ) da Barra de Ferramentas Padrão, e então a opção Configurar. Após estas seleções, pode-se configurar: a Página, as Margens, o Cabeçalho/Rodapé ou a Planilha. Em cada uma das opções, escolha as alternativas de interesse.

3.7. Funções cujo resultado ocupa mais que uma célula.

Existem funções em que o resultado ocupa mais que uma célula, como as funções matriciais, etc. Para estas o uso destas funções:

a) Selecione a faixa de células onde sairá o resultado.

b) Selecione a função com respectivos argumentos.

c) Clique CTRL + SHIFT + ENTER.

Exemplo dessas funções:

• FREQÜÊNCIA(faixa de dados;limites Inferiores das classes)

• MATRIZ.INVERSO(matriz a inverter)

• MATRIZ.MULT(Matriz1; Matriz2)

Exemplo: Obter a freqüência de valores nas classes indicadas. A B C D E F

1 Idade dos alunos 2 22 18 18 24 30 23 3 20 37 17 18 26 18 4 31 28 18 24 36 20 5 19 28 19 21 38 30 6 23 27 21 22 26 19 7 8 Classes L.I.C Freq. 9 -20 -

10 20-25 20 11 25-30 25 12 30-35 30 13 35-40 35 14

a) selecione a faixa de células onde sairá o resultado. D9:D13

b) Selecione a função com respectivos argumentos. Freqüencia(A2:F6;C9:C13)

c) Clique CTRL + SHIFT + ENTER.

Page 15: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

15

EXCEL 5.0 TERCEIRA AULA 1. Carregar a pasta de trabalho, do disco do drive A, com o nome AULA3.XLS. Na

Planilha Exerc1 encontra-se a Tabela:

INSCRITOS DATA DO PESO ID. ATUAL ANO DO SEMESTRE CLASSIF

NASCIM. (em Kg) (em anos) NASCIM. DO NASC

ANTONIO GERÔNIMO DA SILVA 10/02/75 78,00

PEDRO PAULO SARAFIM 15/09/73 76,50

TERSÍLIO DE JUSUS 01/11/65 89,50

ROSARIO DA PENHA 03/08/72 59,45

MARCIO CRUZ E SILVA 17/04/83 61,50

JOSÉ FRANCISCO DE SOUSA 25/03/71 77,30

BENEDITO SILVA DOS REIS 20/07/85 90,00

ROBERTO FURLAN DA SILVA 11/08/73 92,80

JOAQUIM ROMA 22/04/74 80,20

PAULO PEDRO SOUZA 30/12/79 72,40

BENTO ROSALINO 15/06/71 85,90

MÉDIA

1.1. Inserir uma coluna, antes da coluna IDADE ATUAL (em anos), para os IDADE ATUAL (em dias).

1.2. Completar a planilha, sabendo-se que a CLASSIFICAÇÃO é: Grupo1 se ID.Anos<20, Grupo2 se 20≤ID.Anos<25 e, Grupo3 se ID.Anos ≥25.

1.3. Colocar o Peso em Vermelho, se Peso≥80. 1.4. Colocar, na CLASSIF., o Grupo1 em vermelho, Grupo2 em verde e Grupo3 em azul. 1.5. Colocar cores e bordas de forma a melhorar a apresentação da planilha (use

formatação automática). 1.6. Inserir 2 linhas antes da Planilha. 1.7. Colocar os INSCRITOS em ordem decrescente de idade e, para pessoas com a

mesma idade, em ordem alfabética. 1.8. Ocultar colunas de forma a visualizar somente os nomes e a Idade (em anos). 1.9. Mostrar novamente as colunas ocultas. 1.10. Colocar o título “RELAÇÃO DE INSCRITOS PARA SEGURANÇA” centralizado

na largura da Tabela. 1.11. Copiar para A Planilha 2 apenas a coluna de NOMES, seguida pela coluna de Idade

(em anos). 1.12. Imprimir a planilha, modo paisagem, com ampliação adequada e colocando como

Cabeçalho: AULA DE EXCEL 5.0 DATA E Nº DA PÁGINA

e como Rodapé:

------ FCAV/UNESP - Campus de Jaboticabal ----- 1.13. Visualizar a impressão da Planilha. 1.14. Salvar a Planilha com o nome PRONTA.XLS

Page 16: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

16

2. Acessar a Planilha Exerc2, onde se encontram os dados a seguir:

Número da Observação

X1 X2

Produto dos desvios:

( )( )X X X X1 1 2 2− −

X12

1 2,92 14,10 2 2,75 15,08 3 2,78 16,90 4 2,73 13,50 5 2,45 14,57 6 2,89 16,00 7 2,98 9,40 8 2,78 15,66 9 2,32 10,76 10 2,87 13,94

Médias Soma

X1: Quantidade de N no solo X2: Quantidade de terra retida

a) Complete as células da tabela usando expressões e endereçamentos apropriados. b) Calcular a tabela de freqüências para X2, nas classes: [9,11), [11,13), ... [17,19). 3. Na Planilha Plan3, digite as matrizes:

A=���

���

10910

765432

, B=���

���

612523

a) Calcule o determinante da Matriz A

b) Calcule o produto A.B

c) Calcule a matriz inversa da matriz A.

d) Resolver o sistema de equações lineares (AX=B). Lembre-se que X=A-1B.

2x – 5y + 4z = 4

3x – 4y + 2z = 1

4x + 2y + 3z = 17

Page 17: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

17

AULA 4 - Gráficos no EXCEL. O EXCEL permite a edição de uma grande variedade de tipos de gráficos, com muita facilidade. Para iniciar a edição de um gráfico pode-se usar a ferramenta apropriada ( ) da Barra de Ferramentas Formatação, ou a opção de menu: Inserir / Gráfico. 4.1. Gráficos tipo dispersão e linhas Os gráficos tipo linha e tipo dispersão são muito parecidos, diferindo basicamente que, no de Dispersão, os valores de X e de Y são numéricos e, no Tipo Linhas, os valores de X são não numéricos e os de Y numéricos. Para exemplificar a edição destes gráficos, consideramos o exemplo: Exemplo 1. Representar graficamente a evolução do pol%, para 2 variedades de cana-de-açúcar, ao longo do tempo.

Dados: Tempo em dias

Variedades 0 30 60 90 120 150 Varied.1 12,47 15,19 15,02 15,54 18,53 15,76 Varied.2 12,92 14,49 13,4 13,68 16,26 14,78 Gráfico a ser editado:

Passo 1. Entrada dos dados na planilha.

Os dados podem ser dispostos em linhas ou colunas, no caso de linhas, cada linha deve conter uma seqüência de informações. No exemplo a seguir, na linha 1 encontra-se a seqüência dos tempos (Valores de X), na linha 2 a seqüência de valores correspondentes à variedade 1 (Valores de Y1) e, na linha 3, a seqüência de valores correspondentes à variedade 2 (Valores de Y2).

Gráfico de Dispersão

10,0

12,0

14,0

16,0

18,0

20,0

0 30 60 90 120 150

Tempo em dias

Pol

%

Varied.1

Varied.2

Page 18: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

18

Na primeira coluna encontram-se os títulos das seqüências Y. Deixar em branco o título da seqüência de valores de X.

Passo 2. Inicie a edição do gráfico.

Selecione a faixa de dados (A1:G3) e use a Ferramenta apropriada ou a opção de menu Inserir / Gráfico, que aparecerá a caixa de diálogos: Nesta caixa selecione o tipo e o subtipo do gráfico. Os subtipos, para a maioria dos tipos de gráficos, como: Colunas, Barras, Linha, Dispersão, Área, etc., são dispostos em três colunas, e, cada coluna tem uma peculiaridade, ou seja: ��1a coluna - seqüências independentes ��2a coluna - seqüências acumuladas ��3a coluna - porcentagens acumuladas Nas linhas, em uma mesma coluna, mudam apenas características de apresentação. No caso, selecione Tipo Dispersão, e a 3a opção da 1a coluna (pontos ligados com linhas segmentadas), e então a opção Avançar.

Passo 3. Intervalo de dados e forma da organização dos dados.

Informe o intervalo de dados e se as seqüências de dados estão organizados em linha ou em colunas.

No caso, o intervalo é $A$1:$G$3 e a organização em linhas, e então a opção Avançar. Passo 4. Propriedades do gráfico.

Page 19: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

19

Neste momento pode-se definir: Títulos, Eixos, Linhas de Grade, Legenda e Rótulos de Dados, ou não definir nada e deixar para um próximo momento.

No exemplo, digite apenas os títulos, e então a opção Avançar.

Passo 5. Local do gráfico.

O gráfico pode ser feito como: ��Numa nova Planilha (abre uma nova planilha só para o gráfico). ��Um objeto na planilha (cria um objeto na planilha em uso).

No exemplo, selecione a segunda opção.

Passo 6. Dimensões do objeto e fontes do gráfico.

Altere as dimensões do gráfico. Sugestão: próximo do tamanho da tela. Altere a fonte. Sugestão: A mesma fonte do texto onde vai inserir o gráfico, com tamanho

8 ou 9.

Passo 7. Elementos do gráfico.

Para a edição de qualquer elemento disponível no gráfico, dê dois cliques no elemento e escolha as opções desejadas; Para a inclusão de elementos não disponíveis, use o Botão de atalho do mouse sobre a área do gráfico, e escolha a opção: Opções do gráfico.

a) Formatação do eixo vertical (y). ��Duplo clique no eixo (eixo de valores (y))

° Em Padrão selecione: Cor: Azul; espessura ; Tipo de marca de escala principal: Cruz (Cruzando o eixo); Tipo de marca de escala secundária: Externa (Externa ao eixo); Rótulos de marcas de escala: Próximo ao eixo.

° Em Escala digite: mínimo: 10; máximo: 20; escala principal: 2; escala secundária: 1.

° Em Fonte selecione: Tamanho: 8; Cor: azul. ° Em Número selecione: Categoria: número; casas decimais: 1. ° Em Alinhamento selecione: Automático. ° Conclua a formatação do eixo com a opção OK.

Observação: Algumas vezes, a escala precisa ser redefinida.

b) Formatação do eixo horizontal (x). Siga os mesmos passos usados na formatação do eixo y, observando as especificidades do mesmo.

c) Formatação da área de plotagem. Dê dois cliques na área de plotagem e selecione: Borda: Nenhuma; Área: Nenhuma.

d) Formatação da linhas de grade. Dê dois cliques em uma das linhas de grade e selecione: Estilo: Tracejado; Cor: Cinza.

e) Formatação das linhas do gráfico.

Page 20: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

20

Dê dois cliques em uma das linhas do gráfico (por exemplo - Varied 1) e defina: Em Padrões/Linha escolha: o estilo (contínua ou tracejada, etc.), a cor e a espessura da linha. Em Padrões/Marcador escolha: o estilo (uma das formas do marcador), a cor do primeiro plano (cor do contorno), a cor do segundo plano (cor do preenchimento), o tamanho (tamanho do marcador) e a opção sombra (com ou sem). f) Colocar linhas de grade para o eixo X ��Clique com o Botão de Atalho do mouse na área do gráfico ��Selecione Opções do Gráfico ��Em Linhas de grade/ Eixo do valores X, selecione Linhas de grade principais. ��Selecione OK g) Formate as linhas de grade para X, no mesmo padrão das linhas de grade para Y

4.2. Gráficos tipo colunas ou barras Os gráficos tipo colunas e tipo barras são muito parecidos, diferindo basicamente a disposição das colunas, sendo na vertical ou na horizontal, respectivamente. Para exemplificar a edição destes gráficos, vamos editar um gráfico barras para os dados de produção de três espécies de uma cultura, cultivada em quatro locais. Ver dados a seguir: Dados:

LOCAIS ESPÉCIES

Loc.1 Loc.2 Loc.3 Loc.4 Esp.1 125,50 132,80 165,60 135,00 Esp.2 132,30 140,70 147,30 137,50 Esp.3 122,10 125,00 110,50 108,20 Gráfico desejado: Passo 1. Entrada de dados na planilha.

Entre com os dados na forma:

Produção por local

0,0

40,0

80,0

120,0

160,0

200,0

Loc.1 Loc.2 Loc.3 Loc.4

Locais

Pro

duçã

o

Esp.1 Esp.2 Esp.3

Page 21: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

21

Passo 2. Inicie a edição do gráfico.

��Selecione a opção de Menu: Inserir / Gráfico ��Selecione o Tipo e subtipo do gráfico: Tipo Colunas e subtipo da 1a.Coluna e 1a.

Linha do assistente.

Passo 3. Intervalo de dados e forma da organização dos dados.

��Intervalo: $A$1:$E$4 ��Organização: Em linhas

Passo 4. Propriedades do gráfico.

��Edite apenas os títulos

Passo 5. Local do gráfico.

��Um objeto na planilha

Passo 6. Dimensões e fontes.

��Use as sugestões do exercício anterior

Passo 7. Elementos do gráfico.

��Formate os eixos, a área de plotagem, as linhas de grade, de acordo com o gráfico desejado.

��Para formatar as cores e detalhes de uma seqüência de colunas, dê clique duplo na mesma e então escolha os efeitos desejados.

Altere o tipo do gráfico para o seguinte: ��Dê um duplo clique na área do gráfico ��Tipo do gráfico (1a coluna e 3a linha)

Loc.1Loc.2

Loc.3Loc.4

Esp.3

Esp.1

Esp.2

0,0

40,0

80,0

120,0

160,0

200,0

Pro

duçã

o

Locais

Espé

cies

Produção por local

Page 22: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

22

��Observe que a Espécie 3 apresenta valores menores, dificultando a visualização. Para trazer esta seqüência para a frente, de duplo clique em uma das seqüências de colunas e, em Ordem da seqüência, mova a Espec. 3 como sendo a 1a

��Para mudar as perspectivas de visualização, ° c

lique em uma das paredes do gráfico. ° c

lique em um dos cantos da parede ° c

om o cursor na forma +, arraste o canto para obter a perspectiva desejada. 4.3. Gráficos tipo pizza. Os gráficos tipo pizza são comuns na prática. Para exemplificar a edição destes gráficos, vamos editar um gráfico tipo pizza para os dados de uma pesquisa de preferência de votos para 4 candidatos a prefeitura de uma cidade. Dados: Candidatos Preferência

Cand.1 750 Cand.2 350 Cand.3 980 Cand.4 700

Gráfico desejado: Passo 1. Entrada de dados na planilha.

Entre com os dados da forma:

Passo 2. Inicie a edição do gráfico.

��Selecione a opção de Menu: Inserir / Gráfico

Page 23: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

23

��Selecione o Tipo e subtipo do gráfico: Tipo Pizza e subtipo da 1a Linha e 2a Coluna do assistente.

Passo 3. Intervalo de dados e forma da organização dos dados.

��Intervalo: $A$1:$B$2 ��Organização: Em linhas

Passo 4. Propriedades do gráfico.

��Edite apenas os títulos

Passo 5. Local do gráfico.

��Numa nova planilha

Passo 6. Efeitos de 3 dimensões do gráfico.

��Dê um duplo clique na área do gráfico ��Escolha a opção Exibição 3-D, e então aparecerá a caixa de opções: Escolha: Elevação 40, Rotação 90 e Altura 50.

Passo 7. Destacar fatias.

��Selecione a fatia (um clique seleciona a seqüência, um segundo clique seleciona a fatia), e então arreaste-a.

Com este conjunto de informações, qualquer outro gráfico pode ser editado sem maiores dificuldades.

Determina a elevação pretendida (use os botões ou valores)

Determina a rotação pretendida. A primeira fatia inicia na posição escolhida, as demais na seqüência, sentido anti-horário.

Altura deter-mina a espes-sura da pizza

Page 24: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

24

EXCEL 5.0 QUARTA AULA III. Digitar a planilha no EXCEL e fazer os gráficos apresentados abaixo.

MESES VENDEDOR JAN FEV MAR ABR MAI

LÚCIA 120,00 100,00 132,45 142,28 143,17 DARIO 118,79 141,36 138,28 132,62 139,89 BENEDITO 136,87 113,97 85,41 115,37 108,43 TOTAIS 375,66 355,33 356,14 390,27 391,49

GRÁFICO DE LINHAS

80

90

100

110

120

130

140

150

JAN FEV MAR ABR MAI

MESES

VE

ND

AS

EM

RE

AIS

LÚCIA

DARIO

BENEDITO

JAN FEV MAR ABR MAI0

20

40

60

80

100

120

140

160

VE

ND

AS

EM

R$

JAN FEV MAR ABR MAI

MESES

GRÁFICO DE BARRAS

LÚCIA

DARIO

BENEDITO

GRÁFICO TIPO PIZZA

375,66

355,33

356,14

390,27

391,49 JAN

FEV

MAR

ABR

MAI

Page 25: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

25

AULA 5 – Gráficos (continuação) 5.1. Gráficos com linhas de tendência O Excel permite editar gráfico incluindo curvas ajustadas (linhas de tendência). Para exemplificar, considere o exemplo: Exemplo 1. Representar graficamente a parábola (polinomial do 2o grau) que se ajusta aos dados de produção da Variedade 1 (V1) de cana-de-açúcar, ao longo do tempo. Dados:

Tempo em dias Variedades 0 30 60 90 120 150

V1 11,82 14,86 13,84 15,53 15,49 15,82 V2 12,47 15,19 15,02 15,54 18,53 15,76 V3 12,92 14,49 13,40 13,68 16,26 14,78 V4 11,96 14,71 14,98 15,25 16,21 15,53 V5 11,05 13,18 14,61 14,88 16,51 16,36

Gráfico a ser editado:

Passo 1. Gráfico de dispersão

Faça um gráfico de dispersão dos pontos (Y=V1 e X= Tempo). Siga os passos descritos na aula anterior.

Passo 2. Inclusão da linha de tendência

Clique, botão de atalho do mouse, na seqüência de pontos, e então selecione: �� Adicionar Linha de tendência �� Em Tipo, selecione: Polinomial, ordem 2; �� Em Opções, selecione:

Exibir equação no gráfico e Exibir valor de R-quadrado no gráfico.

Passo 3. Faça os ajustes finais (cores, linhas, etc.)

5.2. Gráficos com barras de erros

Ajuste de Curvas

y = -0.0002x2 + 0.0509x + 11.66R2 = 0.967

10.00

11.00

12.00

13.00

14.00

15.00

16.00

17.00

0 20 40 60 80 100 120 140 160

Tempo (em dias)

Page 26: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

26

O Excel permite editar gráfico incluindo barras de erros. Para exemplificar, considere o exemplo: Exemplo 2. Representar graficamente as médias das variedades ao longo do tempo, com os desvios padrão em cada tempo. Dados: Mesmos dados do exemplo anterior, acrescentando uma seqüência (linha) para a média e uma para Desvio Padrão (DP), ou seja:

Média 12.04 14.09 14.37 14.98 16.60 15.65

DP 0.63 0.91 0.64 0.69 1.02 0.51

Gráfico a ser editado:

Média +/- DP

10.00

12.00

14.00

16.00

18.00

0 20 40 60 80 100 120 140 160

Tempo (em dias)

Pro

duçã

o

Passo 1. Gráfico de dispersão

Faça um gráfico de dispersão, com linhas segmentadas, Y=Média e X= Tempo. Siga os passos descritos na aula anterior.

Passo 2. Inclusão das barras de erros

Clique duplo na seqüência de pontos, e então selecione: �� Barras de erros Y. Com essa opção aparecerá a caixa de diálogos:

Nesta caixa, selecione o tipo de barras de erros e a faixa de valores a ser somada e subtraída às médias, que no caso é a faixa de valores com os desvios padrão.

Page 27: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

27

5.3. Gráficos personalizados Exemplo 3. Representar graficamente as médias dos tratamentos (barras) e indicar os resultados do teste de Tukey, como os dados a seguir: Dados:

Média Tukey 1 10.0 A 2 12.0 A 3 16.0 B 4 14.0 A 5 21.0 C

Médias dos tratamentos

AA

BA

C

0.0

5.0

10.0

15.0

20.0

25.0

1 2 3 4 5

Tratamentos

Méd

ias

Passo 1. Gráfico de colunas

Faça um gráfico de colunas, com as médias dos tratamentos. Siga os passos descritos na aula anterior.

Passo 2. Inclusão das letras.

�� Clique duplo na seqüência de colunas. �� Em Rótulos de dados, selecione Mostrar rótulos. �� Troque os rótulos pelas letras.

Page 28: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

28

IV. EXCEL 5.0 QUINTA AULA 1. Digitar, no Excel, os dados apresentados a seguir:

DOSES ANO

0,50 1,00 2,00 3,00 5,00 1997 8,20 19,70 28,60 30,80 40,30 1998 6,60 15,70 25,00 37,80 42,90 1999 9,80 16,00 31,90 40,20 32,60

Média DP

2. Completar as linhas Média e Desvio Padrão (DP). 3. Elaborar os gráficos apresentados a seguir:

DADOS OBSERVADOS NO EXPERIMENTO

0,00

10,00

20,00

30,00

40,00

50,00

0,50 1,00 1,50 2,00 2,50 3,00 3,50 4,00 4,50 5,00

DOSES DO PRODUTO

PR

OD

ÃO

199719981999

VALORES MÉDIOS E DP

0,00

10,00

20,00

30,00

40,00

50,00

0,00 1,00 2,00 3,00 4,00 5,00

DOSES DO PRODUTO

PR

OD

ÃO

Page 29: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

29

2. A partir dos dados a seguir elaborar o gráfico apresentado.

TRATAM. MÉDIA Teste Tukey 1 12 b 2 14 ab 3 11 b 4 16 a

AJUSTE DE EQUAÇÃO DE REGRESSÃO

y = -2,2294x2 + 18,904x - 0,2391R2 = 0,9989

0,00

10,00

20,00

30,00

40,00

50,00

0,50 1,00 1,50 2,00 2,50 3,00 3,50 4,00 4,50 5,00

DOSES

PR

OD

ÃO

0

4

8

12

16

20

1 2 3 4

TRATAMENTOS

PR

OD

ÃO a

ab

a

b

Page 30: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

30

EXCEL 5.0 AULA 6 - REVISÃO 1

1. Abrir o arquivo AULA6a.XLS e acessar a Planilha Exerc1 que contém os dados:

CURSOS ÁREAS SEMESTRE GASTOS (R$)

GASTOS (US$) RECOLH.

AGRO. GRAD P 1.235,00 AGRO. GRAD S 1.458,50 AGRO. MESTR P 1.350,00 AGRO. MESTR S 1.565,00 AGRO. DOUT P 1.680,00 AGRO. DOUT S 1.398,00 ZOOT. GRAD P 1.595,00 ZOOT. GRAD S 1.414,50 ZOOT. MESTR P 1.220,00 ZOOT. MESTR S 1.332,00 ZOOT. DOUT P 1.650,00 ZOOT. DOUT S 1.465,00 MED.VET. GRAD P 1.750,00 MED.VET. GRAD S 1.065,00 MED.VET. MESTR P 1.100,00 MED.VET. MESTR S 1.520,00 MED.VET. DOUT P 1.190,00 MED.VET. DOUT S 1.209,00 Dolar do dia 3,15 NOMES: Nome1 Nome2

• Preencher a coluna GASTOS (US$) usando expressões apropriadas e a opção de cópia. O valor do dólar encontra-se no final da Tabela,

• Preencher a coluna RECOLH., sendo que o recolhimento será 3% se GASTO R$≤ 1.100,00; 4% se 1.100,00 < GASTO R$≤ 1.400,00; e 5% se o R$1.400,00<GASTO≤ R$1.700,00 e 6% se R$> 1.700,00. (Usar a função PROCV),

• Colocar os gastos em vermelho se GASTO R$≤ 1.100,00; azul se 1.100,00 < GASTO R$≤ 1.700,00; e verde se R$> 1.700,00,

• Colocar os valores calculados com 2 casas decimais, • Colocar dados em ordem decrescente do GASTOS (US$), • Colocar cores de forma a melhorar a apresentação da planilha. (Usar formatação

Automática - Lista 2), • Colacar como Cabeçalho:

------ (SEU NOME) ------ e como Rodapé: AVALIAÇÃO DO EXCEL DATA: HORA:

Page 31: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

31

2. Acessar a Planilha Exerc2 que contem os dados:

INSCRITOS DATA DO NASC. SALÁRIO

ANTONIO GERÔNIMO DA SILVA 10/02/75 2.235,00 PEDRO PAULO SARAFIM 15/09/73 1.590,00 TERSÍLIO DE JESUS 01/11/75 680,00 ROSARIO DA PENHA 03/08/72 430,00 MARCIO CRUZ E SILVA 17/04/80 1.265,00 JOSÉ FRANCISCO DE SOUSA 25/03/71 670,00 BENEDITO SILVA DOS REIS 20/07/70 1.340,00 ROBERTO FURLAN DA SILVA 11/08/73 930,00 JOAQUIM ROMA 22/04/74 1.320,00 PAULO PEDRO SOUZA 30/12/72 180,00 BENTO ROSALINO 15/06/71 3.250,00

Obter a freqüências de inscritos por categoria de SALÁRIO, sabendo-se que: Se SALÁRIO<600,00 - Categ.1; se 600,00≤SALÁRIO<1200 - Categ.2; se 1200,00≤SALÁRIO<2000,00 - Categ.3 e se SALÁRIO≥2000,00 - Categ.4. (Usar a função Freqüência). Representar graficamente (histograma), como apresentado a seguir.

3. Na planilha Exerc3, digite as matrizes:

A=

�����

�����

4111133322115432

, B=

�����

�����

55551444113311121111

e) Calcule o determinante da Matriz A,

f) Calcule o produto A.B,

g) Calcule a matriz inversa da matriz A,

h) Resolver o sistema de equações lineares (AX=B). Lembre-se que X=A-1B

2x – 5y + 4z + 2w = 4

3x – 4y + 2z + 5w = 1

4x + 2y + 3z + 1w = 17

1x + 1y + 1z + 1w = 6

012345

Categ1 Categ2 Categ3 Categ4

Page 32: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

32

4. Acessar a Planilha Exerc4, que contem os dados: ANO V. DOSES 0,5 1 2 3 5 1997 8,20 19,70 28,60 30,80 40,30 1998 6,60 15,70 25,00 37,80 42,90 1999 9,80 16,00 31,90 40,20 32,60 Média DP DP – Desvio padrão da Amostra Fazer os gráficos, como objetos na mesma planilha:

PRODUÇÃO POR ANO

0,00

10,00

20,00

30,00

40,00

50,00

0 0,5 1 1,5 2 2,5 3 3,5 4 4,5 5DOSES

PR

OD

ÃO

1997

1998

1999

PRODUÇÃO MÉDIA (3 ANOS)

0,00

10,00

20,00

30,00

40,00

50,00

Doses

Pro

duçã

o

Média 8,20 17,13 28,50 36,27 38,60

0,5 1 2 3 5

Page 33: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

33

VI. AULA 7

7.1. Transpor Faixa:

Para transpor uma faixa de células (as linhas passam a ser colunas e as colunas passam a ser linhas), siga os passos:

a) selecione a faixa de células a ser transposta, b) copie para a área de transferência (Copiar), c) selecione a primeira célula destino, d) selecione Editar / Colar Especial, e) Selecione a opção Transpor, e então OK.

7.2. Renomear Planilhas:

Opção 1: • Duplo Clique no nome da Planilha

• Digitar o novo nome

Opção 2: • Botão de atalho do mouse no nome da Planilha

• Opção Renomear

• Digitar o novo nome

7.3. Aplicações estatísticas

O Excel permite algumas aplicações estatísticas como: Estatísticas descritivas, Histogramas, Testes t, Regressão, etc. Estas ferramentas encontram-se no Suplemento Ferramentas de Análise, que não vem disponibilizado automaticamente.

7.3.1. Disponibilizar o suplemento Ferramentas de Análise.

a) Selecione Ferramentas / suplementos, b) Selecione o suplemento Ferramentas de Análise, c) Ok.

Observação:. Quando este suplemento está disponível, encontra-se no menu a opção Ferramentas /

Análise de dados.

7.3.2. Uso da Ferramenta Análise de dados.

Para exemplificar, considere a planilha:

a) Estatísticas descritivas para Peso

• Selecione Ferramentas / Análise de dados, • Selecione a opção: Estatística descritiva, • Em Intervalo de entrada selecione a faixa $A$2:$U$2,

Page 34: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

34

• Em Agrupado por selecione linhas, • Marque a opção Rótulos na primeira coluna, uma vez que o Título da linha (Peso) foi selecionado

no Intervalo de entrada,

• Em Opções de saída marque: Nova planilha e Resumo estatístico,

• OK. Resultado:

b) Histograma. (Freqüências por intervalos de classes)

Para exemplificar, obtenha o histograma para os dados de Peso, considerando as classes de freqüências: (50,55], (55,60], (60,65], (65,70] (70,75]

• Digite, em alguma linha da planilha, os Pontos Médios das Classes (PMC), que são 52,5; 57,5; 62,5; 67,5 e 72,5. Veja a figura a seguir:

• Selecione Ferramentas / Análise de dados, • Selecione a opção: Histograma, • Em Intervalo de entrada selecione a faixa $A$2:$U$2,

• Em Intervalo de Bloco, selecione a faixa contendo os Pontos Médios de Classes $C$5:$H$5,

Observação: Quando não define o Intervalo de Blocos, o Excel faz, por default, cinco classes de freqüências.

• Em Agrupado por selecione linhas, • Marque a opção Rótulos na primeira coluna, uma vez que o Título da linha (Peso) foi selecionado

no Intervalo de entrada,

Page 35: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

35

• Em Opções de saída marque: Nova planilha e Resultado gráfico,

• OK. Resultado após alguns acertos no gráfico:

c) Regressão linear (Peso=A+B Altura)

Para esta opção os dados precisam estar organizados em colunas, assim sendo, transponha a faixa de dados. Organize os dados na faixa A1:C21

• Selecione Ferramentas / Análise de dados, • Selecione a opção: Regressão, • Em Intervalo Y de entrada selecione a faixa $B$1:$B$21,

• Em Intervalo X de entrada selecione a faixa $C$1:$C$21,

• Marque a opção Rótulos, uma vez que o Título das colunas (Peso e Altura) foram selecionados nos Intervalos de entrada,

• Em Opções de saída marque: Nova planilha,

• OK.

Page 36: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

36

Resultado:

7.4. Tabelas dinâmicas

O Excel permite a elaboração de Tabelas de uma, duas, três ou mais dimensões, com muita facilidade, a partir de uma Planilha contendo colunas de classificação e colunas de resultados. Na Tabela que será usada para exemplo, encontram-se os GASTOS de uma Faculdade, por CURSO, ÁREA e SEMESTRE. Dados:

CURSO ÁREA SEMESTRE GASTOS AGRO. ADMINISTRAÇÃO P 1635,00 AGRO. ADMINISTRAÇÃO S 1550,00 AGRO. ENSINO P 1358,00 AGRO. ENSINO S 1265,00 AGRO. EXTENSÃO P 1082,00 AGRO. EXTENSÃO S 980,00 AGRO. PESQUISA P 1970,00 AGRO. PESQUISA S 1872,00 BIOLOGIA ADMINISTRAÇÃO P 1150,00 BIOLOGIA ADMINISTRAÇÃO S 1200,00 BIOLOGIA ENSINO P 865,00 BIOLOGIA ENSINO S 920,00 BIOLOGIA EXTENSÃO P 580,00 BIOLOGIA EXTENSÃO S 640,00 BIOLOGIA PESQUISA P 1472,00 BIOLOGIA PESQUISA S 1530,00 MED.VET. ADMINISTRAÇÃO P 1620,00 MED.VET. ADMINISTRAÇÃO S 1650,00 MED.VET. ENSINO P 1332,00 MED.VET. ENSINO S 1365,00 MED.VET. EXTENSÃO P 1044,00

Estatística F p-value

Coeficientes da equação A e B, respectivamente

Page 37: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

37

MED.VET. EXTENSÃO S 1080,00 MED.VET. PESQUISA P 1938,00 MED.VET. PESQUISA S 1972,00 ZOOT. ADMINISTRAÇÃO P 1380,00 ZOOT. ADMINISTRAÇÃO S 1395,00 ZOOT. ENSINO P 1098,00 ZOOT. ENSINO S 1114,00 ZOOT. EXTENSÃO P 816,00 ZOOT. EXTENSÃO S 834,00 ZOOT. PESQUISA P 1707,00 ZOOT. PESQUISA S 1724,00 As colunas CURSO, ÁREA e por SEMESTRE são consideradas classificatórias.

A opção do Excel é: Dados / Tabelas e Gráficos dinâmicos.

Para exemplificar, construir as Tabelas:

Ex.1) Das médias dos gastos por curso (em colunas)

Passos a serem seguidos: a) Selecione a faixa de dados ou posicione o cursor no interior da mesma pois, assim sendo, o Excel

seleciona automaticamente as células limitadas por uma linha e uma coluna em branco, b) Selecione a opção Dados / Tabelas e Gráficos dinâmicos, c) Na primeira caixa de Diálogos selecione:

Banco de dados ou lista do Microsoft Excel Tabela dinâmica e então Avançar,

d) Selecione a faixa e então Avançar, e) Na caixa de diálogos contendo campos para: Página, Linha Coluna e Dados, bem como a lista de nomes

de colunas. Arraste os nomes das colunas para os campos desejados. Para o nosso exemplo, arraste CURSO para o campo Colunas e GASTOS para o campo Dados,

f) Como a Tabela resultante foi feita para totais de gastos (por default), de dois cliques na célula Soma de gastos e escolha a opção desejada. No caso, Média.

Ex.2) Total de de gastos áreas(colunas) e semestre(linhas).

Siga os mesmos passos do exemplo anterior, só que no passo e) arraste ÁREAS para o campo Colunas, SEMESTRE para o campo Linhas e GASTOS para o campo Dados.

Observação: Ao editar uma segunda Tabela Dinâmica, o Excel solicita se as tabelas serão vinculadas ou independentes. Selecione a opção de serem independentes.

Ex.3) Total de de média por curso (páginas), áreas(colunas) e semestre(linhas).

Siga os mesmos passos do exemplo anterior, só que no passo e) arraste CURSO para o campo Página, ÁREAS para o campo Colunas, SEMESTRE para o campo Linhas e GASTOS para o campo Dados.

Na Lista drop-down da página, pode-se selecionar uma Tabela (Áreas x Semestre) das médias dos gastos para todos os cursos ou uma para cada curso.

7.5. Gráficos dinâmicos

O Excel permite a editar de gráficos dinâmicos, com a mesma facilidade que edita Tabelas Dinâmicas.

A opção do Excel é a mesma: Dados / Tabelas e Gráficos dinâmicos.

Os passos a serem seguidos são aproximadamente os mesmos para a edição de Tabelas, ou sejam:

Page 38: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

38

a) Selecione a faixa de dados ou posicione o cursor no interior da mesma pois, assim sendo, o Excel seleciona automaticamente as células limitadas por uma linha e uma coluna em branco,

b) Selecione a opção Dados / Tabelas e Gráficos dinâmicos, c) Na primeira caixa de Diálogos selecione:

Banco de dados ou lista do Microsoft Excel Gráficos dinâmicos e então Avançar,

d) Selecione a faixa e então Avançar, g) Na caixa de diálogos contendo campos para os eixos e para valores, arraste os nomes das colunas para os

campos desejados, h) Como a Tabela resultante foi feita para totais, altere para a estatística desejada.

Page 39: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

39

EXCEL 5.0 SÉTIMA AULA

1. Abrir o arquivo AULA7.XLS e acessar a Planilha Exerc1 que contém os dados: (AC - Uso de Ar Condicionado em horas, KWV eletricidade usada em Kilowat)

X=AC Y=KWH X=AC Y=KWH X=AC Y=KWH X=AC Y=KWH 1,5 35,0 6,0 79,0 6,0 33,0 7,5 62,0 4,5 63,0 13,5 93,0 6,5 78,0 12,0 85,0 5,0 66,0 8,0 66,0 8,0 65,0 6,0 43,0 2,0 17,0 12,5 94,0 7,5 77,0 2,5 57,0 8,5 94,0 7,5 82,0 8,0 75,0 5,0 33,0

a) Colocar os dados em colunas. b) Representar graficamente a dispersão dos pontos Y=f(X). c) Incluir no gráfico a representação da reta (regressão linear) com a e equação e o valor de

R2. d) Obter as estatísticas descritivas básicas para X e Y. Use uma nova planilha. Renomear a

planilha como EST.DESC. e) Obter o histograma para os valores de X e Y.

e.1. Use as classes de freqüências default. Use uma nova planilha. Renomear a planilha como FREQ.DEF.

e.2. Definir as classes de freqüências: a) [0,2), [2,4) .... [10,12) para X. Use uma nova planilha. Renomear a planilha como

como Freq.X. b) [30,40), [40,50), [50,60), [60,70), [70,80), [80,90), [90,100) para Y. Use uma

nova planilha. Renomear a planilha como Freq.Y f) Obter o coeficiente de correlação entre X e Y. Na mesma planilha, a partir da célula

A25. g) Obter a análise de regressão linear simples [KLW=f(AC)]. Use uma nova planilha.

Renomear a planilha como como Reg.Lin. 2. TABELAS DINÂMICAS. Acessar a planilha Exerc2, que contem 4 variáveis (colunas) “CURSOS, AREAS, TRIMESTRE e GASTOS”. a) Faça uma Tabela dinâmica para média dos gastos, por CURSOS (linhas). Nova Planilha

(TABD1). b) Faça uma Tabela dinâmica para soma dos gastos, por ÁREAS (linhas) e CURSOS

(colunas). Nova Planilha (TABD2). c) Faça uma Tabela dinâmica para média dos gastos, por CURSOS (páginas), CURSOS

(linhas) e TRIMESTRES (colunas). Nova Planilha (TABD3). d) Fazer um gráfico de barras para as médias dos gastos, por CURSO. Nova Planilha

(GRAF1). e) Fazer um gráfico de barras para as médias dos gastos, por CURSO e por ÁREA. Nova

Planilha (GRAF2).

Page 40: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

40

AULA 8

8.1. Nomear faixas Para fazer referência a uma faixa de células, pode-se:

��selecionar a faixa, ��usar os endereços das células extremos da faixa, separados por dois pontos. Ex:.

A1:D5, ��usar um nome previamente atribuído à faixa.

Para atribuir nome a uma faixa de células siga os passos: a) selecione a faixa de células, b) na caixa de nomes, início da barra de fórmulas, digite o nome da faixa, e então

ENTER.

8.2. Atingir metas A opção de menu: Ferramentas / Atingir meta do Excel permite obter o resultado específico de uma célula, ajustando o valor de outra célula. Eqüivale a resolver qualquer equação de uma incógnita.

Exemplo. Uma disciplina tem 3 avaliações (Av1, Av2 e Av3), com pesos 2, 3 e 5, respectivamente. Supondo que o aluno tirou 3,0 na primeira avaliação e 5,0 na segunda, quando precisa na terceira para ser aprovado? Representação da situação: A meta a ser atingida é obter o valor da célula C4, de forma que a média (célula C5) seja igual a 5,00.

Solução no Excel: a) Coloque o cursor na célula C5 e defina a fórmula, no caso:

=(B2*C2+B3*C3+B4*C4)/10. Isso resulta numa média de 2,1, que é a média com C4=0.

b) Com o cursor em C5 selecione a opção de menu: Ferramentas / Atingir metas. c) Aparecerá uma caixa de diálogos, onde devemos informar que deseja-se atingir para a

célula C5, o valor 5,0, variando o valor da célula C4. Veja caixa de diálogos a seguir:

d) Selecione OK e terá a solução.

Page 41: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

41

8.3. Preenchimento de seqüências

O Excel permite preencher seqüências de células usando um critério como: progressão aritmética, geométrica, etc. Para preencher seqüências pode-se usar cópia de células ou uma opção de menu.

a) Preenchimento de seqüências usando cópia de células.

O Excel permite o preenchimento de células, usando a opção de copiar células. Definindo-se uma ou duas células de uma seqüência, e copiando a(s) para um seqüência de células subsequentes, resultará:

• caso a 1a célula for uma constante (número ou label), preenche a seqüência com a constante,

• caso a 1a célula for um label seguido de um número, preenche a seqüência repetindo-se o label e aumentando o número,

• caso as duas primeira células forem numéricas, preenche a seqüência como uma progressão aritmética tendo o primeiro número como valor inicial e a diferença entre eles com razão.

• caso a 1a célula for um elemento de uma lista pré definida no Excel, preenche a seqüência com os elementos da lista.

b) Preenchimento de seqüências usando uma opção de menu. Para este tipo de preenchimento siga os passos: • digite o primeiro valor da seqüência, • selecione a faixa de células a serem preenchidas, incluindo o primeiro valor da

seqüência, • selecione a opção de menu: Editar / Preencher / Seqüência, e então aparecerá a caixa

de diálogos: • selecione as opções desejadas, sabendo-se que:

° Seqüência em Linhas ou Colunas. Depende se a faixa a ser preenchidas ser em linhas ou colunas.

° Tipo Linear - progressão aritmética, tendo como valor inicial o primeiro valor da seqüência, e como razão, o valor especificado no campo Incremento, respeitando o limite especificado no campo Limite.

Page 42: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

42

° Tipo Crescimento - progressão geométrica, tendo como valor inicial o primeiro valor da seqüência, e como razão, o valor especificado no campo incremento, respeitando o limite especificado no campo Limite.

° Tipo Data - Seqüência de datas, a partir da data especificada na primeira célula da seqüência, com incremento em dias, meses ou anos, dependendo da opção selecionada em Unidade de data. Respeita o limite especificado no campo Limite.

° Autopreenchimento - Preenchimento automático como quando copia células.

8.4. Uso de listas O Excel possui algumas listas pré-definidas, que podem ser vistas com a opção de menu: Ferramentas / Opções / Listas. Geralmente dias da semana e meses do ano.

a) Criar novas listas

• selecione a opção de menu: Ferramentas / Opções / Listas, • no campo Entrada da lista, digite os elementos da lista. Tecle ENTER após cada

elemeto, • selecione Adicionar.

b) Importar listas

• selecione a faixa de valores a ser importado como lista, • selecione a opção de menu: Ferramentas / Opções / Listas, • selecione Importar.

c) Excluir listas

• selecione a opção de menu: Ferramentas / Opções / Listas, • selecione a lista s ser excluída, • selecione Excluir.

8.5. Proteção (de edição) Para proteger uma planilha ou uma pasta do excel, de edição, ou seja, permitir que outros usuários possam abrir a pasta, visualizar, imprimir planilhas, não permitindo alterar (editar) o conteúdo da mesma, siga os passos: • acesse a planilha, • selecione a opção de menu: Ferramentas / Proteger e uma das opções disponíveis:

° proteger planilha, ° proteger pasta de trabalho, ° proteger e compartilhar pasta de trabalho

• Digite a senha (duas vezes), e então Ok.

8.6. Proteção (de acesso)

Para proteger uma pasta de trabalho (arquivo) do excel, de acesso, ou seja, não permitir que outros usuários possam acessar (abrir) a pasta, siga os passos: • com o arquivo aberto, • selecione a opção de menu: Arquivo / Salvar como, • selecione Ferramentas (ou opções, dependendo da versão do Excel),

Page 43: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

43

• selecione Opções gerais, • Digite a senha (duas vezes), e então Ok.

8.7. Filtros

O Excel permite filtrar registros com determinados critérios, de acordo com o interesse do usuário. Os filtros podem ser Automáticos ou Avançados, dependendo da quantidade de critérios a serem usados.

8.7.1. Filtros automáticos Para exemplificar, considere os dados contendo a relação de FUNCIONÁRIOS de uma empresa com DATA DE NASCIMENTO, ESCOLARIDADE (P - primário, S - Secundário e U - Universitário) e SALÄRIO.

Dados: FUNCIONÁRIOS DATA DE NASC. ESCOLARIDADE SALÁRIO ANTONIO GERÔNIMO DA SILVA 10/02/1975 P 235,00 PEDRO PAULO SARAFIM 15/09/1973 S 590,00 TERSÍLIO DE JUSUS 01/11/1975 U 680,00 ROSARIO DA PENHA 03/08/1972 U 430,00 MARCIO CRUZ E SILVA 17/04/1980 P 265,00 JOSÉ FRANCISCO DE SOUSA 25/03/1971 S 670,00 BENEDITO SILVA DOS REIS 20/07/1970 S 340,00 ROBERTO FURLAN DA SILVA 11/08/1973 U 930,00 JOAQUIM ROMA 22/04/1974 P 320,00 PAULO PEDRO SOUZA 30/12/1972 P 180,00 BENTO ROSALINO 15/06/1971 S 250,00

a) Filtrar os registros dos funcionários com nível Universitário.

• posicione o cursor no interior da tabela (faixa de valores) ou selecione a faixa, • selecione a opção de menu: Dados / Filtrar / Auto filtro. • Observe que os títulos de colunas passam a ser uma lista drop-down. • Na coluna que define o critério, que no caso é ESCOLARIDADE, clique na seta, e

então a opção U. Para voltarem todos os registros, use a opção: Dados / Filtrar / Mostrar todos.

b) Filtrar os registros dos funcionários que nasceram após 1972 e que recebem SÁLÁRIO ENTRE R$300,00 e R$500,00.

• posicione o cursor no interior da tabela (faixa de valores) ou selecione a faixa, • selecione a opção de menu: Dados / Filtrar / Auto filtro. • Observe que os títulos de colunas passam a ser uma lista drop-down. • Na coluna DATA DE NASC. selecione Personalizar, e então:

° é maior ou igual a , 31/12/1972, Ok

Page 44: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

44

• Na coluna SALÁRIO selecione Personalizar, e então: ° é maior do que , R$300,00 é menor do que , R$500,00

8.7.1. Filtros avançados

a) Para o mesmo conjunto de dados, filtrar os registros dos funcionários que nasceram até 1975 e que recebem SÁLÁRIO entre R$300,00 e R$500,00 ou que tenham ESCOLARIDADE P ou S.

Para usar filtro avançado, deve-se

• criar uma tabela de critérios contendo uma linha com os nomes de colunas envolvidas nos critérios, outras linhas definindo os critérios sendo que, critérios da mesma linha, corresponde à opção lógica E, e em linhas distintas a opção lógica OU.

No exemplo, a tabela de critérios é:

• selecione a opção de menu: Dados / Filtrar / Filtro avançado, • na caixa de diálogos, defina a Ação. Para o nosso exemplo use Copiar para outro

local, • defina o intervalo da lista (faixa de dados). No caso A1:D12, • defina o intervalo de critérios. No caso B14:E18, • defina o local dos registros resultantes da filtragem em Copiar para: No caso A20, • Ok

8.8 Subtotais

Para exemplificar o uso de subtotais, consideremos os dados do Funcionários apresentados anteriormente.

Page 45: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

45

Obter a média de salário por ESCOLARIDADE. Siga os passos: • classifique os dados por ESCOLARIDADE, • posicione o cursor no interior da tabela (faixa de valores) ou selecione a faixa, • selecione a opção de menu: Dados / Subtotais, • defina os campos como apresentados a seguir:

• Ok Obs:. Quando os subtotais são apresentados, na frente dos números das linhas da planilha são apresentados os níveis de visualização: 1 - Resultado para todos os registros, 2 - Resultado por grupo de registros, 3 - Todos os registros e resultados. Para excluir os subtotais, selecione: Dados / Subtotais / Remover todos.

Page 46: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

46

EXCEL 5.0 OITAVA AULA 1. Carregar o arquivo AULA8.XLS. Na planilha Exerc1 tem os dados:

NOME PR1 PR2 PR3 MÉDIA ANTONIO GERÔNIMO SA SILVA 3,40 6,50 5,00 PEDRO PAULO SARAFIM 5,60 7,00 4,50 TERESINHA DE JESUS 6,00 5,00 7,00 ROSALINA DA PENHA 6,50 9,00 8,50 MARICOTA CRUZ E SILVA 4,00 4,00 5,00 JOSÉ FRANCISCO DE SOUSA 7,50 3,80 6,00 BENEDITO SILVA DOS REIS 9,00 7,00 7,50 MÉDIA POR PROVA

Média Máximo Mínimo Variância

a) Calcular as MÉDIAS, b) Crie uma coluna para RESULTADO, que pode ser REPROVADO (em vermelho) se

MÉDIA<3.00; RECUPERAÇÃO (em verde) se 3.00≤MÉDIA<5 e REPROVADO (em azul) se MÉDIA≥5.00,

c) Nomear a faixa de notas (B3:D9) como NOTAS e então completar as estatísticas abaixo da tabela.

2. Atingir Meta.

A Ferramenta atingir Metas é usada para resolver qualquer equação com uma variável.

2.1 A partir dos dados a seguir (Planilha 2) calcular: a) O valor da Venda em reais VVR=(pd+ci)*cd(1+lc), b) Suponha que lhe ofertam R$3500,00, sua decisão depende de qual é o lucro que terá

com este valor. Calcule o lucro neste caso,

Preço em Dólar (pd) 1000,00 Cota para Importação (ci) 60,00 Cotação do Dólar (cd) 3,00 Lucro (lc) 20,0% Valor da Venda em Reais (VVR) 2.2 Resolva a equação: Y=2(1+5x)=25 3. Listas: Na Planilha Exerc3:

• Inserir listas para números (PA e PG), datas variando: Dias, Meses e Anos, usando Auto-Preenchimento (EDITAR/PREENCHER/SEQÜÊNCIAS), etc. (FERRAMENTAS/OPÇÕES/LISTAS)

• Importar listas: Criar uma lista com os nomes da Exerc1 (Marcar a faixa na planilha; FERRAMENTAS/OPÇÕES/LISTAS - Importar )

Page 47: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

47

4. Autoformatação e Proteções • A

cessar a planilha Exerc4 • Formatar a planilha usando padrões do Excel (FORMATAR / AUTOFORMATAÇÃO) • Proteger o conteúdo da planilha (FERRAMENTAS/PROTEGER) • Proteger a Planilha (desautorizar acesso) (ARQUIVO/SALVAR COMO/OPÇÕES)

5. Trabalhando a Planilha como Banco de Dados • Acessar a planilha Exerc5 • Entrar no modo Banco de Dados: DADOS/FORMULÁRIO • Inserir um novo registro (Obs: em cada registro TAB e SHIFT+TAT caminha pelos

campos, ou ALT+letra em destaque) • Alterar o salário do JOAQUIM para 280,00

6. Filtragem

• Acessar a planilha Exerc6 • Filtro Automático. AUTOFILTRO • Fazer Filtragem

° Nomes que começam com M ou T ° Relação dos Masculinos ° Salários entre 200,00 e 350,00 ° Datas de nascimento anterior a 1980 ou posterior a 1985.

7. Filtragem com opções múltiplas FILTRO AVANÇADO.

• Acessar a planilha Exerc7 • Fazer Filtragem para Salário entre 200 e 300, ou Data de Nascimento anterior a 1980

e Cidade=Jaboticabal. 8. Criação de subtotais

• Acessar a planilha Exerc8 • Criar Subtotais: Média do salário por SEXO • Criar Subtotais: Totais de salário por ESTADO

(DADOS/SUBTOTAIS. Obs: Os dados devem estar classificados pelo campo que agrupa os registros)

Page 48: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

48

9. Referências Externas • Abrir um arquivo novo e criar a planilha Plan1 com os índices econômicos. (Grave -

Arquivo INDICES.XLS e feche)

INDICES ECONOMICOS MÊS ATUAL (MAIO) DOLAR 3,15 SALÁRIO MÍNIMO 240,00 POUPANÇA 0,75

• Abrir um arquivo novo e criar as planilhas: FILIAL1, FILIAL2 e RESUMOS, como segue. VII. Notas: a) O valor do dólar deve ser obtido no arquivo INDICES.XLS

b) Valores da Planilha RESUMO devem ser obtidos de FILIAL1 e FILIAL2. CASA AGRÍCOLA POTY: FILIAL 1 VALOR UNIT. VALOR UNIT. VALOR EST. VALOR EST. ITENS QUNTIDADE (EM US$) (EM R$) (EM US$) (EM R$) SAL 250 3,00 SAL MINERAL 75 9,00 UREIA 200 6,00 TOTAL CASA AGRÍCOLA POTY: FILIAL 2 VAL. UNIT. VAL. UNIT. VALOR EST. VALOR EST. ITENS QUNTIDADE (EM US$) (EM R$) (EM US$) (EM R$) SAL 125 3,00 SAL MINERAL 120 9,00 UREIA 100 6,00

TOTAL RESUMO DAS FILIAIS

FILIAL1 FILIAL2 TOTAL VALOR EST. (EM US$) VALOR EST. (EM R$)

Page 49: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

49

EXCEL 5.0 AULA 9 - REVISÃO 2 1. Abrir o arquivo AULA9.XLS e acessar a Planilha Exerc1 que contém os dados:

(Quantidades de N, P, Ke S no solo e Y (porcentagem de terra retida na peneira 18).

N P K S Y 2,92 0,53 1,40 0,21 17,54 2,75 0,52 1,45 0,22 5,08 2,78 0,51 1,52 0,20 6,90 2,73 0,52 1,53 0,21 19,34 2,45 0,47 1,55 0,20 4,57 2,89 0,48 1,49 0,20 7,28 2,98 0,53 1,52 0,22 9,40 2,78 0,51 1,42 0,20 15,66 2,32 0,48 1,50 0,20 10,76 2,87 0,49 1,51 0,21 13,94 2,51 0,48 1,51 0,19 6,28 2,58 0,50 1,42 0,22 13,28 2,78 0,48 1,46 0,21 14,86 2,80 0,51 1,51 0,21 15,68 3,01 0,54 1,49 0,22 6,80 3,05 0,53 1,53 0,21 18,68 3,04 0,52 1,46 0,20 5,47 3,13 0,51 1,49 0,20 8,76 3,10 0,50 1,42 0,20 9,68 3,18 0,50 1,45 0,21 11,47 3,37 0,46 1,47 0,20 11,41 3,30 0,50 1,48 0,22 9,38 3,35 0,51 1,42 0,20 5,10 3,45 0,47 1,49 0,20 15,01 3,46 0,51 1,45 0,16 27,93 3,48 0,49 1,45 0,19 5,85 3,59 0,48 1,53 0,18 10,38 4,58 0,51 1,44 0,21 12,57 3,51 0,49 1,47 0,21 3,43 3,56 0,52 1,47 0,21 6,13 3,74 0,51 1,44 0,20 7,46 3,71 0,45 1,50 0,21 7,18 3,95 0,47 1,47 0,20 10,59 4,00 0,52 1,46 0,21 13,96 3,90 0,51 1,51 0,20 15,47 3,96 0,45 1,41 0,20 6,48

a) Obter as estatísticas descritivas básicas para Y. Use uma nova planilha. Renomear a

planilha como Item a). b) Obter o histograma para os valores de Y. Use as classes de freqüências default. Use uma

nova planilha. Renomear a planilha como HIST.Y.

Page 50: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

50

c) Obter o histograma para os valores de N. Definir as classes de freqüências: [2,3 a 2,7), [2,7 a 3,1), [3,1 a 3,5), [3,5 a 3,9), [3,9 a 4,3) para N. Use uma nova planilha. Renomear a planilha como como HIST.N.

d) Obter o coeficiente de correlação entre N, P, K, S e Y. Na mesma planilha, a partir da célula G2.

e) Obter a análise de regressão linear simples [Y=f(S)]. Use uma nova planilha. Renomear a planilha como como REG.LIN.

2. Acessar a Planilha Exerc2 que contém os dados: Funcionário Data Nasc. Função Local Salário ANTONIO CARLOS OLIVEIRA 10/02/75 Secretária EEPSG A 551,75 ANTONIO GERÔNIMO DA SILVA 15/09/73 Secretária EEPSG A 550,39 BARTIRA DE JESUS 01/11/65 Professor EEPSG B 907,11 BEATRIZ ALVARENGA 03/08/72 Secretária EEPSG B 551,19 BENEDITO SILVA DOS REIS 17/04/83 Servente EEPSG A 304,37 BENTO ROSALINO 25/03/71 Professor EEPSG A 904,74 JOANA DA CRUZ 20/07/55 Servente EEPSG A 307,15 JOAQUIM ROMA 11/08/73 Professor EEPSG B 900,32 JOSÉ FRANCISCO DE SOUSA 22/04/74 Professor EEPSG A 903,79 JOSEFA ALMIRANTE 30/12/79 Servente EEPSG B 301,28 JÚLIO CESAR GOMES 15/06/71 Secretária EEPSG B 550,72 MARCIO CRUZ E SILVA 21/11/56 Servente EEPSG A 308,28 MARIA APARECIDA MENDES 27/06/75 Servente EEPSG C 302,18 MARICOTA CRUZ E SILVA 13/08/67 Secretária EEPSG B 552,35 PAULO PEDRO SOUZA 15/05/74 Professor EEPSG A 900,65 PEDRO PAULO SARAFIM 26/01/85 Servente EEPSG B 304,21 PRISCILA DE PAULA 03/01/73 Professor EEPSG B 900,80 RENATO BATISTA 30/04/57 Professor EEPSG A 902,31 ROBERTO FURLAN DA SILVA 23/05/75 Professor EEPSG B 901,54 ROSALINA DA PENHA 01/02/76 Secretária EEPSG B 555,73 ROSÁRIA ANTONIA BATISTA 10/10/50 Secretária EEPSG B 557,52 ROSARIO DA PENHA 26/03/73 Secretária EEPSG B 551,56 ROSILENE DA COSTA 02/09/78 Professor EEPSG A 904,75 SIMONE PEDRO ALVES 10/11/80 Professor EEPSG A 907,11 TARSÍLIO DE JUSUS 19/01/55 Professor EEPSG C 907,88 TERESA EVANGELISTA 29/03/85 Servente EEPSG C 300,57 TERESINHA DE JUSUS 07/06/87 Professor EEPSG C 907,33 VALÉRIA SILVA JARDIM 15/08/89 Servente EEPSG A 306,08

a) Inserir uma coluna para idade em anos. b) Inserir uma coluna para Faixa Etária, sendo: FE1 se idade <25 anos, FE2 se 25 ≤ idade

< 35 anos e FE3 se idade ≥ 35 anos c) Faça uma Tabela dinâmica para média dos salários, por Funções (linhas). Nova Planilha

(TABD1). d) Faça uma Tabela dinâmica para média dos salários, por Locais (linhas) e Funções

(colunas). Nova Planilha (TABD2). e) Faça uma Tabela dinâmica para média dos salários, por Faixa Etária (páginas), Locais

(linhas) e Funções (colunas). Nova Planilha (TABD3).

Page 51: PLANILHA ELETRÔNICA EXCEL - jaguar.fcav.unesp.brjaguar.fcav.unesp.br/euclides/AL_2010/G_PD/Exel/Apostila.pdf · O Microsoft EXCEL, ou simplesmente EXCEL é um aplicativo do Office,

51

f) Fazer um gráfico de barras para as médias dos salários, por Função. Nova Planilha (GRAF1).

g) Fazer um gráfico de barras para as médias dos salários, por Local e por Função. Nova Planilha (GRAF2).

3. Acessar a planilha Exerc4 a) Filtrar os arquivos para os funcionários que são Professores e que Nasceram após

25/10/1975. (Use autofiltro) b) Proteger o conteúdo da planilha quanto à edição (use a senha aula9)

4. Acessar a planilha Exerc5 a) Filtrar os arquivos para os funcionários que satisfazem aos critérios:

Data de nascimento anterior a 1/1/1980 ou Salário entre R$350,00 e R$500,00 ou Local=EEPSG A. (Use filtro avançado)

5. Acessar a planilha Exerc6

a)Criar Subtotais: Média do salário por Função 6. Salvar a Planilha com proteção de acesso (Use a senha aula9)