Exc el 3 – Form a t a çõ e s sim ple s e
c o n di c io n a is, O rd e n a ç õ e s, Filtro s,
S u b t o t a is e V a lid a ç õ e s
Microsoft Excel Exercício 3
1. Numa folha de Excel introduza os seguintes dados:
A B C D E F G
1 1º Trim. 2º Trim. 3º Trim. 4º Trim. Total %
2 Vendas 100000 120000 140000
3 Custos 35000 50000 60000
4 LUCRO BRUTO
5 Despesas:
6 Salários 25000 25000 25000
7 Despesas gerais 3750 4000 5000
8 Distribuição 5000 6000 7000
9 Impostos
10 TOTAL DESPESAS
11 LUCRO
2. Grave o livro de trabalho com o nome Excel_3.
3. Preencha os valores relativamente ao 4º trimestre, sabendo que:
a) As vendas aumentaram 12% relativamente ao 3º trimestre (soluções na última
página).
b) Os custos foram de 85.000 €.
c) Os salários diminuíram 5% relativamente ao trimestre anterior.
d) As despesas gerais foram iguais à média dos 3 trimestres anteriores.
e) Os custos de distribuição representam 5% das vendas.
f) Os impostos (no 4º trimestre) representam 10% do valor das vendas.
g) Complete as células referentes aos totais (coluna F).
4. Nas células B9:D9 crie fórmulas para calcular os impostos nos 3 primeiros trimestres do
ano, sabendo que, em cada mês, correspondem a 10% das vendas efetuadas.
5. Complete as linhas 4, 10 e 11. Sabe-se que:
a) Lucro Bruto = Vendas - Custos
b) Total Despesas = Salários + Desp. Gerais + Distribuição + Impostos
c) Lucro = Lucro Bruto - Total Despesas
6. Formate as células da tabela da seguinte forma:
a) Todos os carateres deverão ser do estilo Times New Roman com tamanho 10
b) Ajuste a largura da coluna A à largura do texto
c) A largura das colunas B, C, D, E e F deverá ser de 11 (Menu Format> Column>
Width=11)
d) Na linha 1 de cabeçalho, o texto deverá ter o tamanho 12, estar centrado e em
bold
e) Os valores numéricos (colunas B, C, D, E e F) deverão ser formatados de modo a
apresentarem o separador dos milhares, duas casas decimais e o símbolo de euro.
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 1/9
Microsoft Excel Exercício 3
f) Todos os dados das linhas 4, 10 e 11 deverão estar em bold
g) Altere o nome da folha em que está a trabalhar para “Vendas”.
7. Selecione as células A13, A14, A15 e A16 e escreva, respeitando os formatos
apresentados na figura da última página, Vendas:, Valor Médio, Valor Máximo e Valor
Mínimo, respetivamente. Introduza as fórmulas correspondentes (relativas aos 4
trimestres das Vendas) nas células B14, B15 e B16.
8. Calcule em G3 o peso dos custos e em G6:G9 o peso de cada uma das 4 despesas,
relativamente ao total de vendas. Formate estas 5 células como percentagem e com
duas casas decimais.
Home > Format > Cells...> Number > Category = Percentage > Decimal Places = 2
9. Formatação de limites e sombreados da tabela. Selecione a área A1:G11 e faça:
Home > Format > Cells… > Border > Style …
a) Em Style escolha um risco fino e carregue em Presets > Inside ficando a tabela
com linhas verticais e horizontais finas
b) Ainda em Style, escolha um risco largo para o contorno exterior da tabela,
carregando em Presets > Outline. Faça OK.
c) Formate as células referentes ao Lucro Bruto, Total Despesas e Lucro com a cor
de fundo cinzenta (Selecione as áreas A4:G4, A10:G10 e A11:G11
simultaneamente)
Home > Format > Format Cell > Fill > Pattern Color = cinza claro. OK.
10. Altere o nome da folha 2 para Vendedores.
11. Na folha Vendedores introduza os seguintes dados:
A B C D E F G H
1 Vendas
2 Nome dos Vendedores 1º Trim. 2º Trim. 3º Trim. 4º Trim. Total Bónus %
3 Daniela Ferraz 12.900,00€ 19.000,00€ 27.500,00€ 20.050,00€
4 Sónia Costa 40.000,00€ 39.850,00€ 48.000,00€ 49.150,00€
5 João Ramos 9.600,00€ 17.900,00€ 21.500,00€ 35.050,00€
6 Pedro Dias 37.500,00€ 43.600,00€ 43.350,00€ 52.550,00€
7 Totais
a) O título Vendas deverá ser introduzido na célula A1, ficar centrado ao longo da
área da tabela (A1:H1) e em bold. Para abranger a área pedida, faça:
Menu Format > Cells… > Alignment > Text Control = Merge Cells
b) Na linha 2 o texto também deve ficar centrado e em bold.
c) Os dados numéricos da área (B3:G7) devem deverão ser formatados de modo a
apresentarem o separador dos milhares e o símbolo de euro.
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 2/9
Microsoft Excel Exercício 3
d) Na célula A7 o texto deverá ser a bold e alinhado à direita.
e) Calcule os totais das vendas por trimestre e por vendedor e formate-os a bold.
Selecione simultaneamente as áreas F3:F6 e B7:F7
Carregue no botão de AutoSum
f) Escreva Bónus na célula G10, e 5% na célula H10.
12. Selecione os dados e os nomes dos vendedores da tabela (A2:F6) e ordene por ordem
alfabética e de forma ascendente de nomes, mantendo assim a correspondência dos
valores já inseridos.
Menu Data > Sort > Sort by = Nome dos Vendedores
13. É atribuído um bónus de 5% aos vendedores com vendas totais superiores a 125.000€.
Preencha as células G3:G6 com fórmulas que utilizem a célula H10 como endereço misto
(H$10), e atribuam o valor 0 (zero) aos vendedores não bonificados.
14. Na coluna H, determine o peso das vendas de cada vendedor no total das vendas da
empresa, utilizando a fórmula com endereço absoluto ($F$7). Altere a coluna utilizando o
formato de percentagem com 2 casas decimais.
Nota: se o ícones relativos ao AutoFormat e aos formulários (Form) não estiverem
disponíveis na Quick Access Toolbar faça:
a) Aceda ao Office Button e a Excel Options
b) Selecione Customize, All Commands
c) Procure o símbolo de AutoFormat, selecione e faça Add
d) Repita o procedimento anterior para o Form
15. Numa nova folha do Excel, que deve ter o nome Despesas, preencha as células A1:F1
com os seguintes títulos: Vendedor, Trimestre, Alojamento, Gasolina, Outros e Total.
Selecione a área A1:E9 e recorrendo a um formulário introduza os dados na tabela.
Introduza na coluna F as fórmulas que permitem calcular o valor Total para cada
vendedor. Formate em seguida a tabela com AutoFormat > Classic 3
A B C D E F
1 Vendedor Trimestre Alojamento Gasolina Outros Total
2 Pedro Dias 1º 375,00€ 140,00€ 60,00€ 575,00€
3 Sónia Costa 1º 400,00€ 235,00€ 240,00€ 875,00€
4 Daniela Ferraz 2º 425,00€ 115,00€ 64,00€ 605,00€
5 João Ramos 2º 425,00€ 181,00€ 325,00€ 931,00€
6 Sónia Costa 2º 375,00€ 330,00€ 255,00€ 960,00€
7 Pedro Dias 2º 500,00€ 195,00€ 125,00€ 820,00€
8 João Ramos 1º 525,00€ 315,00€ 175,00€ 1015,00€
9 Daniela Ferraz 1º 490,00€ 270,00€ 390,00€ 1150,00€
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 3/9
Microsoft Excel Exercício 3
16. Posicione-se numa das células da tabela e ordene os dados por Vendedor e por
Trimestre.
Menu Data > Sort
A B C D E F
1 Vendedor Trimestre Alojamento Gasolina Outros Total
2 Daniela Ferraz 1º 490,00€ 270,00€ 390,00€ 1150,00€
3 Daniela Ferraz 2º 425,00€ 115,00€ 64,00€ 604,00€
4 João Ramos 1º 525,00€ 315,00€ 175,00€ 1015,00€
5 João Ramos 2º 425,00€ 181,00€ 325,00€ 931,00€
6 Pedro Dias 1º 375,00€ 140,00€ 60,00€ 575,00€
7 Pedro Dias 2º 500,00€ 195,00€ 125,00€ 820,00€
8 Sónia Costa 1º 400,00€ 235,00€ 240,00€ 875,00€
9 Sónia Costa 2º 375,00€ 330,00€ 255,00€ 960,00€
17. Introduza um filtro na tabela de forma a obter os dados dos vendedores que
apresentaram despesas totais superiores a 1000 €.
Selecione uma célula da tabela
Menu Data > Filter
Carregue na seta: Total > Number Filters > Custom Filter
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 4/9
Microsoft Excel Exercício 3
Vendedor Trimestre Alojamento Gasolina Outros Total
Daniela Ferraz 1º 490,00€ 270,00€ 390,00€ 1150,00€
João Ramos 1º 525,00€ 315,00€ 175,00€ 1015,00€
18. Pretende-se saber quais os dois vendedores que apresentaram menores despesas.
Carregue na seta: Total > (Top 10...)
Vendedor Trimestre Alojamento Gasolina Outros Total
Daniela Ferraz 2º 425,00€ 115,00€ 64,00€ 604,00€
Pedro Dias 1º 375,00€ 140,00€ 60,00€ 575,00€
19. Retire todos os filtros da tabela Menu Data > Filter
20. Pretende-se que sejam apresentados subtotais do valor das Despesas Totais, por
Trimestre.
Para efetuar esta operação deve posicionar-se numa das células da tabela e ordenar por
Trimestre.
Nos subtotais, introduza somatórios na coluna Total. Menu Data > Subtotal ...
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 5/9
Microsoft Excel Exercício 3
1 Vendedor Trimestre Alojamento Gasolina Outros Total
2 Daniela Ferraz 1º 490,00€ 270,00€ 390,00€ 1.150,00€
3 João Ramos 1º 525,00€ 315,00€ 175,00€ 1.015,00€
4 Pedro Dias 1º 375,00€ 140,00€ 60,00€ 575,00€
5 Sónia Costa 1º 400,00€ 235,00€ 240,00€ 875,00€
6 1º Total 3.615,00€
8 Daniela Ferraz 2º 425,00€ 115,00€ 64,00€ 604,00€
7 João Ramos 2º 425,00€ 181,00€ 325,00€ 931,00€
9 Pedro Dias 2º 500,00€ 195,00€ 125,00€ 820,00€
10 Sónia Costa 2º 375,00€ 330,00€ 255,00€ 960,00€
11 2º Total 3.315,00€
12 Grand Total 6.930,00€
21. Esconda os dados dos vendedores, deixando apenas visíveis os totais por trimestre e o
global. Pressione com o rato na caixa lateral (sinal + ou – ), referente a cada um dos
subtotais.
Vendedor Trimestre Alojamento Gasolina Outros Total
1º Total 3.615,00€
2º Total 3.315,00€
Grand Total 6.930,00€
Soluções:
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 6/9
Microsoft Excel Exercício 3
22. Abra o ficheiro de trabalho Excel_3a.xls
23. Selecione a folha Notas1 e preencha a célula I3 sabendo que a avaliação prática
representa 60% da nota final, sendo os restantes 40% para qualquer um dos exames.
Após criar a fórmula em I3 arraste-a para as restantes células.
Nota: deve, em primeiro lugar, verificar se o exame de recurso foi efetuado ou não
=IF( H3< >” ” ; . . . )
Se a condição se verificar, a média é feita com a nota do recurso ( H3 ), caso
contrário será com a nota do exame em ( G3 ).
24. Na coluna J crie uma fórmula que calcule a situação final do aluno, APROVADO ou
REPROVADO consoante a nota final. O aluno é Reprovado se obtiver Nota Final inferior a
9.5
Nota: Antes de copiar a fórmula de J3 para as restantes células J4:J32 pretende-se
que a palavra REPROVADO apareça escrita a vermelho; para o efeito, posicione-
se em J3 e faça:
Home > Conditional Formatting > New Rule > Format only cells that contain
Selecione as opções de acordo com a figura
A seguir carregue em Format
Selecione a cor pretendida
Faça Ok
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 7/9
Microsoft Excel Exercício 3
25. O conjunto das células F3:H32 deverão ser validadas para que só admitam valores
pertencentes ao intervalo [0;20], de modo a que se o utilizador tentar introduzir valores que
não pertençam ao intervalo referido apareça uma caixa de erro, informando quais os valores
permitidos.
Selecione F3:H32 e faça:
Data > Data Validation > Settings
Preencha de acordo com a figura, mas sem carregar ainda em OK
De seguida carregue no separador Error Alert
Preencha de modo a obter uma caixa de mensagem de erro de acordo com a figura.
26. As células da coluna C devem estar validadas para aceitar apenas as letras M ou F.
Selecione C3:C32 e faça:
Data > Data Validation > Settings e preencha:
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 8/9
Microsoft Excel Exercício 3
27. Preencha as células I34, I35 e I36 utilizando as funções Count( ) e CountIF( ).
Nº Total de Alunos: 30
Nº de Aprovados: 24
Nº de Reprovados: 6
28. Utilizando a função SUMIF() construa uma fórmula na célula I37 que forneça a
quantidade total de faltas dadas pelos alunos reprovados. O resultado previsto é 46.
29. Na folha de cálculo Pesquisa, insira nas células C8 e C10 as fórmulas que forneçam a
informação de qual a Nota e Situação, respectivamente, do aluno cujo número tiver sido
inserido na célula C6. Sugestão: utilize a função VLOOKUP.
Formatações simples e condicionais, Ordenações, Filtros, Subtotais e Validações Pág. 9/9