LISTA DE EXERCÍCIOS DE EXCEL
01) Elaborar a planilha abaixo, fazendo-se o que se pede:
Empresa Nacional S/A
Código Produto Jan Fev Mar Total 1º Trim. Máximo Mínimo Média 1 Porca 4.500,00 5.040,00 5.696,00
2 Parafuso 6.250,00 7.000,00 7.910,00 3 Arruela 3.300,00 3.696,00 4.176,00 4 Prego 8.000,00 8.690,00 10.125,00 5 Alicate 4.557,00 5.104,00 5.676,00 6 Martelo 3.260,00 3.640,00 4.113,00 Totais
Código Produto Abr Mai Jun Total 2º Trim. Máximo Mínimo Média 1 Porca 6.265,00 6.954,00 7.858,00
2 Parafuso 8.701,00 9.658,00 10.197,00 3 Arruela 4.569,00 5.099,00 5.769,00 4 Prego 12.341,00 12.365,00 13.969,00 5 Alicate 6.344,00 7.042,00 7.957,00 6 Martelo 4.525,00 5.022,00 5.671,00 Totais
Total do Semestre
FÓRMULAS:
1ª Tabela:
Total 1º Trimestre: soma das vendas dos meses de Jan / Fev / Mar.
Máximo: calcular o maior valor entre os meses de Jan / Fev / Mar.
Mínimo: calcular o menor valor entre os meses de Jan / Fev / Mar.
Média: calcular a média dos valores entre os meses de Jan / Fev / Mar.
2ª Tabela:
Total 2º Trimestre: soma das vendas dos meses de Abr / Mai / Jun.
Máximo: calcular o maior valor entre os meses de Abr / Mai / Jun.
Mínimo: calcular o menor valor entre os meses de Abr / Mai / Jun.
Média: calcular a média dos valores entre os meses de Abr / Mai / Jun.
Totais: soma das colunas de cada mês (1ª e 2ª tabela).
Total do Semestre: soma dos totais de cada trimestre.
02) Elaborar as planilhas abaixo, fazendo-se o que se pede:
Nº NOME Salário Bruto INSS Gratificação INSS R$ Gratificação R$ Salário Líquido 1 Eduardo R$ 853,00 10,00% 9,00%
2 Maria 3 Helena 4 Gabriela 5 Edson 6 Elisangela 7 Regina 8 Paulo
FÓRMULAS
R$ 951,00 R$ 456,00 R$ 500,00 R$ 850,00 R$ 459,00 R$ 478,00 R$ 658,00
9,99%
8,64%
8,50%
8,99%
6,25%
7,12%
5,99%
8,00%
6,00%
6,00%
7,00%
5,00%
5,00%
4,00%
INSS R$: multiplicar Salário Bruto por INSS.
Gratificação R$: multiplicar Salário Bruto por Gratificação.
Salário Líquido: Salário Bruto mais Gratificação R$ menos INSS R$.
Formatar os números para que eles apareçam de acordo com a planilha dada.
03) Elaborar as planilhas abaixo, fazendo-se o que se pede:
Valor do Dólar R$ 2,94 Papelaria Papel Branco
Produtos Caneta Azul Caneta Vermelha Caderno
Régua Lápis Papel Sulfite Tinta Nanquim
FÓRMULAS:
Qtde Preço Unit. Total R$ Total US$
500 R$ 0,15 750 R$ 0,15 250 R$ 10,00 310 R$ 0,50 500 R$ 0,10 1500 R$ 2,50 190 R$ 6,00
Total R$: multiplicar Qtde por Preço Unitário
Total US$: dividir Total R$ por Valor do Dólar – usar $ nas fórmulas Alterar as colunas de acordo com a necessidade.
04) Elaborar a planilha abaixo, fazendo-se o que se pede:
Projeção para o ano de 2003
Receita bruta Jan-Mar Abr-Jun Jul-Set 140.000,00 185.000,00 204.100,00
Out-Dez 240.000,00
Total do Ano
Despesa Líquida Jan-Mar
Salários 20.000,00
Juros 20.000,00
Aluguel 12.000,00
Propaganda 16.100,00 Suprimentos 19.900,00
Diversos 25.000,00
Total do Trim.
Receita líquida Situação
Abr-Jun 26.000,00 15.600,00 20.930,00 28.870,00 39.000,00 32.500,00
Jul-Set 33.800,00 20.280,00 27.209,00 33.631,00 50.700,00 42.250,00
Out-Dez 43.940,00 26.364,00 35.371,70 43.720,30 65.910,00 54.925,00
Total do Ano
FÓRMULAS:
Valor Acumulado do ano de despesas
Total do Ano Receita Bruta: Soma das receitas anuais.
Total do Ano Despesa Líquida: Soma das despesas anuais.
Total do Trimestre: Soma das despesas trimestrais.
Receita Líquida: Receita Bruta menos Total do Trimestre.
Valor Acumulado do ano de despesas: Soma do Total do Ano de Despesas Situação: Se Receita Líquida for menor que R$ 1.000,00, "Prejuízo Total";
Se Receita Líquida for menor que R$ 5.000,00, "Lucro Médio";
Se Receita Líquida for maior que R$ 5.000,00, "Lucro Total'.
=SE(B16<1000;"Prejuízo Total";SE(B16<=5000;"Lucro Médio";SE(B16>5000;"Lucro Total")))
05) Elaborar o banco de dados abaixo, fazendo-se o que se pede:
Nome Endereço Bairro Cidade Estado
Ana Rodovia Anhanguera, km 180 Centro Leme SP
Eduardo Érica
R. Antônio de Castro, 362 R. Tiradentes, 123
São Benedito Centro
Araras Salvador
SP BA Fernanda Av. Orozimbo Maia, 987 Jd. Nova Campinas Campinas SP Gabriela Rodovia Rio/São Paulo, km 77 Praia Grande Ubatuba SP Helena
Katiane Lilian Lucimara Maria Pedro Roberto Rubens Sônia
R. Júlio Mesquita, 66 R. 5, 78
R. Lambarildo Peixe, 812 Av. dos Jequitibas, 11 Av. Ipiranga, 568 R. Sergipe, 765 Av. Limeira, 98 Al. dos Laranjais, 99 R. das Quaresmeiras, 810
Centro Jd. Europa Vila Tubarão Jd. Paulista Ibirapuera Botafogo Belvedere Centro Vila Cláudia
Recife Rio Claro Ribeirão Preto Florianópolis Manaus Campinas Araras
Rio de Janeiro Porto Alegre
PE SP SP SC AM SP SP RJ RS Tatiane
Nome Endereço Bairro Cidade Estado
R. Minas Gerais, 67
Rubens A B
C D E
Parque Industrial Poços de Caldas MG
FÓRMULAS:
A Digite o nome da pessoa a ser procurada.
B =PROCV(B21;A2:E16;2;0) C =PROCV(B21;A2:E16;3;0) D =PROCV(B21;A2:E16;4;0) E =PROCV(B21;A2:E16;5;0)
06) Elaborar a planilha abaixo, e depois, elaborar um gráfico de colunas, como mostrado abaixo:
1 2
A B C
Bolsa de Valores
D E
3 4 5 6 7
Relação de Movimentação Financeira da Semana Dias da Semana
Valor Máximo Valor Mínimo Fechamento Abertura do Pregão 8 Segunda-Feira 24.000,00 22.980,00 23.900,80 23.000,00 9 Terça-Feira
10 Quarta-Feira 11 Quinta-Feira 12 Sexta-Feira
24.120,00 23.014,00 24.019,00 24.240,00 23.129,57 24.139,60 24.361,00 23.254,00 24.260,00 24.483,61 23.361,45 24.381,60
23.115,00 23.230,58 23.346,73 23.463,46
07) Elaborar a planilha abaixo, e depois, elaborar um gráfico de colunas, como mostrado abaixo:
Tabela de Preços Empresa Papelaria Livro Caro
R. Tiradentes, 1234 Porc. De Lucro
Valor do Dólar:
12,50%
3,34
Reais
Araras/SP
Dólar Produto
Borracha Caderno 100 fls Caderno 200 fls Caneta Azul Caneta Vermelha Lapiseira Régua 15 cm Régua 30 cm Giz de Cera Cola Compasso Totais
Estoque Custo Venda Total Custo Venda 500 0,50 0,55
200 2,57 2,70 300 5,00 5,50 1000 0,15 0,25 1000 0,15 0,25 200 3,00 3,50 500 0,25 0,30 500 0,35 0,45 50 6,00 6,50 100 3,14 4,00 100 5,68 6,00
Total
- Aumentar a largura das colunas a seu critério, quando necessário;
- Formatar os números com o símbolo monetário (R$), quando necessário;
-
- FÓRMULAS:
- Total (R$): Venda (R$) * Quantidade em Estoque . - Custo (Dólar): Custo (R$) / Valor do Dólar do Dia.
- Venda (Dólar): Custo (Dólar) * (1 + Porcentagem de Lucro).
- Total (Dólar): Venda (Dólar) * Quantidade em Estoque.
- Totais: somar os totais de todas as colunas.
GRÁFICO
- Fazer o gráfico utilizando-se apenas das colunas seguintes colunas:
Produto, Custo (Reais), Venda (Reais), Custo (Dólar) e Venda (Dólar);
- Selecionar o gráfico de Linhas – Linhas 3D;
- Seqüências em Colunas;
- Preencher os títulos do gráfico como quiser;
- Legenda à Direita;
- Criar o gráfico como nova planilha.
8 Exercício - Bazar
1) Digitar a planilha abaixo:
Figura 1.1 – Planilha sem formatação
2) Cálculos
a) Calcular o Saldo para as duas semanas.
b) Calcular o Total por Semana para as duas semanas.
c) Salvar a planilha com o nome Exercício de Fixação 01, em disquete ou em pasta no servidor.
3) Formatação
a) Alargar a coluna 1.
b) Centralizar o título da planilha entre as colunas e formatar fonte de letra, tamanho da fonte, cor, estilo da fonte, sublinhado, etc.
c) Centralizar os títulos Semana 1 e Semana 2 entre as respectivas colunas e formatar
fonte de letra, tamanho da fonte, cor, estilo da fonte, sublinhado, etc.
d) Centralizar os valores entre as respectivas colunas.
e) Formatar os Totais por Semana. f) Forma final da planilha.
Figura 1.2 – Planilha formatada
4) Salvar a planilha novamente.
9 Exercício – Controle de Estoque
1) Digitar os dados em uma nova guia (Plan2), se acordo com as seguintes coordenadas.
A1 ... Locadora de Games – Sempre Aberta
A2 ... Controle de Estoque – Diário
A4 ... Game
A5 ... Quake
A6 ... Silent Thunder
A7 ... Rise of Robots
A8 ... Doom 2
A9 ... Lone Runner On-Line
A10 ... AfterLife
A11 ... World Circuit 2
A12 ... Dark Forces
A13 ... Fifa Soccer 02
A15 ... Valor Total Alugado
A16 ... Valor Médio Alugado
A17 ... Maior Valor Alugado
A18 ... Menor Valor Alugado
B4 ... Estoque Original
C4 ... Quantidade Alugada
D4 ... Valor Unitário
E4 ... Valor Alugado
F4 ... Saldo Alugado 2) Formatação de texto.
a) Selecionar as células A4:F4.
i) No menu Formatar, selecionar Células.
(1) Clicar a guia Alinhamento.
(a) Na caixa Controle de texto, marcar Retorno automático de texto.
(b) Ainda na mesma guia, na caixa Alinhamento de texto, na opção
Horizontal, selecionar Centro. Na opção Vertical, selecionar Centro. (2) Clicar a guia Fonte.
(a) Em Fonte, selecionar a fonte desejada.
(b) Em Estilo da fonte, clicar a opção Negrito.
(c) Em Tamanho, selecionar o tamanho 11.
(d) Manter as demais opções no formato padrão.
ii) Pressionar o botão Ok. b) Selecionar as células A5:A13.
i) No menu Formatar, selecionar Células.
(1) Clicar a guia Alinhamento.
(a) Em Alinhamento, selecionar a opção Direita. (2) Clicar a guia Fonte.
(a) Em Estilo da fonte, clicar Itálico. c) Selecionar as células A15:A18.
i) No menu Formatar, selecionar Células.
(1) Clicar a guia Alinhamento
(a) Em Alinhamento, selecionar Direita. (2) Clicar a guia Fonte.
(a) Em Estilo da fonte, selecionar Negrito Itálico. d) Selecionar as células D5:F13.
i) No menu Formatar, selecionar Células. (1) Clicar a guia Alinhamento.
(a) Em Alinhamento de texto Horizontal, selecionar Centro. e) Selecionar as faixas de células D5:E13 e E15:E18.
i) No menu Formatar, selecionar Células. (1) Clicar a guia Número.
(a) Em Categoria, selecionar Moeda. (i) Para Casas decimais, fixar 2.
(ii) Usar o símbolo do Real, se já não estiver selecionado.
(iii)No formato para Números negativos, selecionar aquele com o número em vermelho.
(iv)Pressionar o botão Ok. 3) Cálculos
a) Preencher as colunas Estoque Original, Quantidade Alugada e Valor Unitário de acordo com os valores mostrados na Figura 2.3.
i) ii)
Calcular o Valor Alugado. A fórmula é Quantidade Alugada * Valor Unitário.
Calcular o Saldo do Estoque. A fórmula é Estoque Original – Quantidade Alugada.
iii) Copiar as fórmulas acima para as demais células da planilha.
b) Efetuar os cálculos para as células Valor Total Alugado, Valor Médio Alugado, Maior Valor e Menor Valor Alugado.
i) ii)
Para o Valor Total Alugado, utilizar a função de Somatório. Para o Valor Médio Alugado, utilizar a função Média. i
i
i) Para o Maior Valor Alugado, utilizar a função Máximo. iv) Para o Menor Valor Alugado, utilizar a função Mínimo.
4) Forma final da planilha.
Figura 2.3 – Forma final da planilha
5) Salvar a planilha.
10 Exercício – Campeonato de Futebol
1) Digitar a planilha da figura abaixo, ajustando colunas, alinhamentos, fontes, etc.
Figura 3.4 – Planilha de controle de jogos
2) Ocultar grades da planilha
a) No menu Ferramentas, selecionar Opções.
b) Na guia Exibir, desmarcar, em Opções da janela, a caixa Linhas de grade. Esta operação permite limpar a planilha das linhas de grade que formam as células,
facilitando a formatação usando recursos de bordas.
3) Formatação inicial da planilha a) Formatação de títulos.
i) ii)
b) Bordas i) ii)
Centralizar o título geral da planilha entre as células A1 e L1.
Centralizar os títulos Casa e Visitante entre as células A3:B3 e D3:E3, respectivamente.
Selecionar as células A3:B3 e D3:E3. No menu Formatar, selecionar Células. (1) Clicar a guia Borda.
(a) Na caixa Predefinições, selecionar a opção Contorno. Ver figura a seguir.
Figura 3.5 – Caixa Formatar, guia Borda
(2) Clicar a guia Padrões.
(a) Na caixa Sombreamento da célula, selecionar a cor Cinza. iii) Clicar Ok.
iv) Ver figura a seguir.
Figura 3.6 – Caixa Formatar células, guia Padrões
v) Repetir a formatação de Bordas de modo que a planilha fique com a aparência da figura a seguir.
Figura 3.7 – Planilha parcialmente formatada
4) Usar funções
a) A função SE
i)
ii)
A função SE compara grandezas e, a partir desta comparação, permite a tomada de decisões sobre o caminho a seguir. A sintaxe desta função é:
=Se(condição;verdadeiro;falso)
Se a condição for verdadeira, será executado o que está no argumento
Verdadeiro. Caso contrário, o que está em Falso. iii) Completar a planilha conforme a figura a seguir.
Figura 3.8 – Planilha com novos dados
iv) Na célula G4, digitar a função SE, com o seguinte raciocínio:
=SE(B4>D4;”Vitória”;SE(B4=D4;”Empate”;”Derrota”))
v) Isto quer dizer que o placar for maior para o time da casa, então será considerado Vitória, caso contrário, se o placar for igual para ambos os times, será considerado Empate, caso contrário, será considerado Derrota. vi) Copiar a fórmula para as outras células. Ver figura a seguir.
Figura 3.9 – Planilha com os resultados dos jogos
vii) A seguir, inserir uma fórmula definindo o número de pontos atribuídos a cada time, de acordo com os resultados obtidos. Ver figura a seguir.
Figura 3.10 – Planilha com pontos alcançados
b) Funções estatísticas
i) Obter o número de gols a favor e armazenar na célula K4. ii) Obter o número de gols contra e armazenar na célula K5.
iii) Calcular a média de gols a favor e contra e armazenar nas células K6 e
K7 respectivamente. Caso seja necessário, definir duas casas após a vírgula.
iv) Obter o maior número de gols que o time da casa fez e armazenar na célula K8.
v) Repetir o procedimento para obter o menor número de gols do time da casa e armazenar na célula K9.
vi) Conferir na figura a seguir os resultados obtidos e completar os dados adicionais.
Figura 3.11 – Planilha com dados adicionai
vii) A função =CONT.SE
(1) Esta função conta o número de células que não estão vazias em um determinado intervalo de células que corresponde a determinadas condições. Nesta planilha servirá para descobrir quantas Vitórias, Empates
e Derrotas o time da casa obteve. Sua sintaxe é:
=CONT.SE(Intervalo;Critérios)
(a) Posicionar o cursor na célula H12.
(b) Digitar a fórmula: =CONT.SE(G4:G9;”Vitória”).
(c) Para as demais células, H13 e H14, Empates e Derrotas, respectivamente, copiar a fórmula, alterando o critério.
(d) Ver, na figura a seguir, a planilha finalizada.
Figura 3.12 – Planilha finalizada
5) Salvar a planilha.
11 Exercício – Projeção de vendas
Criar uma planilha similar à da figura abaixo.
Figura 4.13 – Nova Planilha
Inserir as fórmulas a seguir.
a) Para criar o acréscimo das vendas mês a mês:
i) ii)
Na célula C6, digitar: =B6*2%+B6. Na célula D6, digitar: =C6*3%+C6. iii) Na célula E6, digitar: =D6*5%+D6. iv) Na célula F6, digitar: =E6*3%+E6. v) Na célula G6, digitar: =F6*4%+F6.
b) Cálculo do Peso Total, Peso Médio, Maior Peso e Menor Peso, usando as devidas funções.
c) Cópia de fórmulas i)
ii)
Selecionar a origem da cópia, isto é, o que se deseja copiar. Neste caso, selecionar o intervalo de células C6:C15.
No menu Editar, selecionar Copiar.
iii) Selecionar o destino da cópia, isto é, para onde serão copiados os dados. Neste caso, o destino é o intervalo de células D6:G15.
d) Alteração de taxas i)
ii)
Posicionar o cursor na célula C6 (que tem a fórmula =B6*2%+B6).
Para alterar a taxa para 6%:
(1) Pressionar a tecla de função F2 (Editar) e digitar o algarismo 6 no lugar do
2.
(2) Pressionar a tecla Enter.
e) Cópia da fórmula para outras células
i) Posicionar na célula C6 (Origem) e selecionar Copiar, no menu
Editar.
ii) Selecionar a faixa D6:G6 (Destino) e selecionar Colar, no menu Editar. iii) A planilha deverá ficar como a da figura a seguir.
Figura 4.14 – Planilha com a taxa modificada
Inserção de colunas
a) Clicar o cabeçalho da coluna B para selecioná-la.
b) No menu Inserir, selecionar Colunas.
c) Inserir um cabeçalho para as Taxas e preencher com as taxas mensais, conforme a figura a seguir.
Figura 4.15 – Planilha com coluna Taxas inserida
Atualização das taxas e cópia relativa de células
a) Posicionar o cursor na célula D6 e alterar a fórmula para =C6*B6+C6. b) Usar esta célula como Origem, executando o comando de copiar.
c) Selecionar a faixa D7:D10 como destino, executando o comando de colar.
d) Selecionar a faixa D6:D10 como origem para copiar.
e) Selecionar a faixa E6:H10 como destino e colar.
f) Observar o efeito do mecanismo de cópia na figura a seguir.
Figura 4.16 – Planilha com células estouradas
g) Quando foi efetuada a cópia da célula D6 para o intervalo D7:D10, os endereços foram trocados, mas, ao mesmo tempo, a relação das taxas e dos valores de cada produto foram mantidas.
h) Quando foi efetuada a cópia da faixa D6:D10 para a faixa de destino, E6:H10, aconteceu uma alteração das taxas nos meses de Setembro até Dezembro, gerando
um resultado incorreto. Na célula E6, a fórmula deveria ser =D6*B6+D6 e não
=D6*C6*D6 como apresentado.
i) A fórmula deverá ser alterada para fixar as taxas e fazer com que as outras
fórmulas busquem as taxas corretas.
i) ii)
Posicionar o cursor na célula D6.
Editar a fórmula, deixando-a com a seguinte sintaxe: =C6*$B6+C6. iii) Copiar a nova fórmula para a faixa de células E6:H10.
iv) Na figura a seguir, visualizar a planilha corrigida.
Ângulo dos títulos
Figura 4.17 – Planilha corrigida
a) Selecionar as células onde estão os meses (Julho a Dezembro).
b) No menu Formatar, selecionar Células. i) Clicar a guia Alinhamento.
(1) Na caixa Orientação, alterar o ângulo para 45 graus. Ver figura a seguir.
Figura 4.18 – Caixa Formatar células, guia Alinhamento
ii) Pressionar o botão Ok. A planilha deverá ficar como a da figura a seguir.
6) Salvar a Planilha
