excel-mod-ii-t1-top2-exerc-cios-resolvido

Prévia do material em texto

Menu
EXERCÍCIOS 2
Ex.1
		Nome	Idade	Departamento	Salário	Telefone
		Alberto da Silva	25	Vendas	R$ 850.00	555-1902		Campo	Soma (BDSOMA)
		Antônio dos Santos	32	Administração	R$ 1,200.00	555-1117		Salário	R$ 3,550.00
		Fabiana Rossi	40	Administração	R$ 2,000.00	555-8929
		Horácio Almeida	31	Recursos Humanos	R$ 1,350.00	555-8907
		João Pereira	35	Vendas	R$ 1,500.00	555-7814
		Márcia Souza	26	Vendas	R$ 600.00	555-9800
		Maria José Costa	22	Vendas	R$ 600.00	555-6629
		Mário Oliveira	54	Diretoria	R$ 4,500.00	555-1237
		Roberto Albuquerque	29	Administração	R$ 1,200.00	555-8273
		Sílvia Pires	23	Vendas	R$ 600.00	555-8664
		CRITÉRIOS
		Nome	Idade	Departamento	Salário	Telefone
			>=23	Vendas
EXERCÍCIO 01
Qual é a soma de salários dos funcionários com 23 anos ou mais do departamento de Vendas?
Ex.2
		Nome	Idade	Departamento	Salário	Telefone
		Alberto da Silva	25	Vendas	R$ 850.00	3222-2058		Campo	Média (BDMÉDIA)
		Antônio dos Santos	32	Administração	R$ 1,200.00	2121-5024		Salário	R$ 1,533.33
		Fabiana Rossi	40	Administração	R$ 2,000.00	3326-2026
		Horácio Almeida	31	Recursos Humanos	R$ 1,350.00	4452-5023		Campo	Máximo (BDMÁX)
		João Pereira	35	Vendas	R$ 1,500.00	2130-2251		Salário	R$ 4,500.00
		Márcia Souza	26	Vendas	R$ 600.00	4333-2023
		Maria José Costa	22	Vendas	R$ 600.00	5152-2021		Campo	Mínimo (BDMÍN)
		Mário Oliveira	54	Diretoria	R$ 4,500.00	3002-5032		Salário	R$ 600.00
		Roberto Albuquerque	29	Administração	R$ 1,200.00	2563-3245
		Sílvia Pires	23	Vendas	R$ 600.00	2265-3065
		CRITÉRIOS
		Nome	Idade	Departamento	Salário	Telefone
			>22
EXERCÍCIO 02
1) Qual é a média de salários dos funcionários maiores de 22 anos?
2) Qual é o maior salário entre os funcionários maiores de 22 anos?
3) Qual é o menor salário entre os funcionários maiores de 22 anos?
Ex.3
		Árvore	Altura (cm)	Idade	Rendimento	Lucro
		Maçã	280	20	14	R$ 105.00
		Pera	220	12	10	R$ 96.00
		Maçã	230	14	9	R$ 105.00
		Maçã	240	0	10	R$ 75.00
		Pera	190	8	8	R$ 77.00
		Maçã	220	11	6	R$ 45.00
		1 - Quantas macieiras estão entre uma altura de 200 e 260 (cm) e que possuem a idade registrada?					CRITÉRIOS
							Árvore	Altura (cm)	Idade	Rendimento	Lucro	Altura (cm)
		2					Maçã	>200	>1			<260
		2 - Quantas árvores possuem um rendimento maior ou igual a 10 frutas?				3 - Qual o lucro total delas?			CRITÉRIOS
									Rendimento
		3				R$ 276.00			>=10
EXERCÍCIO 03
Utilize a função BDCONTAR para responder as questões relacionadas a este banco de dados.
Dica: nomeie os intervalos para facilitar na utilização da função.
Ex.4
		Nome	Qt itens	Estado		CRITÉRIOS				Lista de estados
		Alberto da Silva	40	SP		Nome	Qt itens	Estado		AL
		Antônio dos Santos	22	SP				SP		MG
		Fabiana Rossi	15	RJ						RJ
		Horácio Almeida		MG		Vendas por estado				RS
		João Pereira	46	SC		4				SC
		Márcia Souza	52	SC						SP
		Maria José Costa		RJ						TO
		Mário Oliveira	34	TO
		Roberto Albuquerque		SC
		Sílvia Pires	22	AL
		Marcos Almeida	30	SC
		João Jones Santos	56	SP
		Almir Tavares	55	SP
		Julia Amarantes		SC
		Renan Remon	64	RJ
EXERCÍCIO 04
1) Crie uma lista de estados na célula H9.
2) Utilizando a função BDCONTARA, retorne a quantidade de vendas realizadas em cada estado.
Ex.5
		Nome	Endereço	CEP	Cidade	Estado	Telefone		Nome
		Amélia Lima Silva	Al. Hortencias 109	14570-258	Americana	SP	11-7568-9924		Luiz Carlos Monteiro
		Carlos Santos	R. Argentina 89	02505-852	São Paulo	SP	11-8546-8520
		Debora Ribeiro	Pça D. Pedro II 67	21450-140	Rio de Janeiro	RJ	21-4525-8500		Endereço
		Dilma Tadeu Santos	Trav. Particular 143	58789-212	Vila Velha	ES	51-4567-5193		Av. Roma 909
		Janice Raquel	Av Sol 78	01245-524	São Paulo	SP	11-4562-8877
		Jefferson Santos	Pça XV Novembro 12	11245-002	Campinas	SP	11-7895-0302		CEP	Cidade
		Jose Carlos Macoratti	Rua Projetada 100	75046-789	Brasília	DF	61-7859-5566		35420-789	Belo Horizonte
		Luiz Carlos Monteiro	Av. Roma 909	35420-789	Belo Horizonte	MG	31-3254-0658
		Marcos Lima	R. Venezuela 34	00109-502	São Paulo	SP	11-3254-6600		Estado	Telefone
		Maria Silva Sá	R. Peru 123	13452-023	Limeira	SP	11-4587-0299		MG	31-3254-0658
		Marinalva	Av. Tuíti	5789-5552	São Paulo	SP	11-2521-1123
		Miriam Estela	Av. México 100	01104-789	Santos	SP	11-2589-6644
		Roberto Santos	Av. São Luiz 55	85462-545	Curitiba	PR	81-6547-8833
		Marinalva	R. Equador 89	65402-456	Salvador	BA	41-7895-1544
		Ullian Nascimento	Pça do Carmo 44	98756-254	Porto Alegre	RS	91-6548-2088
EXERCÍCIO 05
1) Crie uma lista na célula I8 com os nomes cadastrados neste banco de dados.
2) Utilizando a função BDEXTRAIR, retorne o endereço, CEP, cidade, estado e telefone correspondente ao nome selecionado.
Ex.6
		Veículo	Cliente	Placa		Consultar dados do cliente por:
		Chevrolet Onix	Alice Alicia	DDC9576		Veículo	Cliente	Placa
		Hyundai HB20	Sophia Santiago	BOD2233			Santos Melo
		Ford Fiesta	Miguel Antunes	CAD1234
		Renault Sandero	Franciele Soares Melo	DAD2122			Resultado da Pesquisa
		Volkswagen up!	Leandro de Souza	FAB3015		Veículo	Toyota Etios
		Fiat Palio	Paulo Estrela Franco	DUC0155		Cliente	Santos Melo
		Fiat Punto	Mariana Gomes Goes	MAN7633		Placa	NAT0122
		Toyota Etios	Santos Melo	NAT0122
		Volkswagen Fox	Juliano Alencar	BOB2233
		Ford Ka	Yuri Paulino Santos	JUI2256
		Volkswagen Gol	Danilo Druque 	LLM2445
		Peugeot 208	Thiago Silva 	TUT2233
		Fiat Uno	Maria Mariana Santos	RIR0105
		Fiat Strada	Daniele Esquivel	MFM2233
		Citroën C3	Carlos Souza Manoel	CTB0133
		Volkswagen Golf	José Josias Melato	RST0544
EXERCÍCIO 06
Por meio da tabela "Consultar dados do cliente por:" serão pesquisados dados específicos do banco de dados. Através dele, retorne o veículo, o cliente e a placa correspondente.
Atenção: Utilize intervalos nomeados e a função SEERRO (quando o dado não for encontrado no BD, não será retornado nada).
Ex.7
		Análise de Vendas
		Loja	Gênero	Vendedor
			Infantil
			Resultado da Pesquisa
		Total de Vendas:	R$ 238.60
		Média de Vendas:	R$ 119.30
		Maior Venda:	R$ 199.00
		Menor Venda:	R$ 39.60
EXERCÍCIO 07
Banco de Dados
/xl/drawings/drawing8.xml#Ex.7.1!A1Tabelas
/xl/drawings/drawing8.xml#Ex.7.2!A11) Faça o hiperlink das caixas "Banco de Dados" e "Tabelas" com as planilhas Ex.7.1 e Ex.7.2, respectivamente;
2) Crie um menu suspenso com as informações da planilha "Ex.7.2", nos campos da loja, gênero e vendedor, facilitando a seleção dos critérios na tabela "Análise de Vendas";
3) Use as funções de banco de dados correspondentes ao título de cada linha da tabela "Resultado da Pesquisa".
Ex.7.1
		Título do Livro	Preço	Gênero	Quantidade	Loja	Vendedor	Vendas
		A Águia Pousou	R$ 34.68	Filosofia, Religiões-Cristianismo	5	Sul	Alberto da Silva	R$ 173.40
		A Marca do Zorro	R$ 34.99	Literatura Estrangeira-Romances	6	Dois	Antônio dos Santos	R$ 209.94
		A Menina e o Porquinho	R$ 19.90	Infantil	10	Azul	Fabiana Rossi	R$ 199.00
		As Pontes de Madison	R$ 24.00	Drama/Romance	3	Azul	Horácio Almeida	R$ 72.00
		Beleza Negra	R$ 29.00	Autobiografia	3	Azul	João Pereira	R$ 87.00
		Ben-Hur: Uma História dos Tempos de Cristo	R$ 32.00	Literatura	5	Sul	Márcia Souza	R$ 160.00
		Cem Anos de Solidão	R$ 42.90	Literatura Estrangeira-Romances	8	Sul	Maria José Costa	R$ 343.20
		Como Eu Era Antes de Você	R$ 26.20	Drama/Romance 	9	Dois	Mário Oliveira	R$ 235.80
		Don Quixote	R$ 54.90	Literatura Estrangeira	12	Dois	Roberto Albuquerque	R$ 658.80
		Ela, a Feiticeira	R$ 32.90	Literatura Estrangeira - Romances	2	Sul	Sílvia Pires	R$ 65.80
		Era uma Vez em Watership Down	R$ 32.90	Romances	1	Azul	Alberto da Silva	R$ 32.90
		Harry Potter e a Câmara Secreta	R$ 19.90	Literatura Juvenil	20	Sul	Alberto da Silva	R$ 398.00
		Harry Potter e a Ordem da Fênix	R$ 19.90	Literatura Juvenil	20	Dois	Horácio Almeida	R$ 398.00
		Harry Potter e a Pedra Filosofal	R$ 19.90	Literatura Juvenil	20	Sul	João Pereira	R$ 398.00Harry Potter e as Relíquias da Morte	R$ 19.90	Literatura Juvenil	20	Dois	João Pereira	R$ 398.00
		Harry Potter e o Cálice de Fogo	R$ 19.90	Literatura Juvenil	20	Azul	Maria José Costa	R$ 398.00
		Harry Potter e o Enigma do Príncipe	R$ 19.90	Literatura Juvenil	25	Azul	Mário Oliveira	R$ 497.50
		Harry Potter e o Prisioneiro de Azkaban	R$ 19.90	Literatura Juvenil	13	Dois	Mário Oliveira	R$ 258.70
		Heidi	R$ 9.90	Infantil	4	Dois	Fabiana Rossi	R$ 39.60
		Lolita	R$ 43.90	Literatura Estrangeira-Romances	5	Sul	Antônio dos Santos	R$ 219.50
		Meu Filho, Meu Tesouro	R$ 25.90	Literatura Juvenil	5	Dois	João Pereira	R$ 129.50
		O Alquimista	R$ 14.90	Literatura Brasileira-Romances	4	Azul	Antônio dos Santos	R$ 59.60
		O Apanhador no Campo de Centeio	R$ 44.99	Literatura Estrangeira	12	Dois	Horácio Almeida	R$ 539.88
		O Caso dos Dez Negrinhos	R$ 37.99	História em quadrinhos - Mangá	13	Dois	Fabiana Rossi	R$ 493.87
		O Código Da Vinci	R$ 35.47	Literatura Estrangeira	22	Sul	João Pereira	R$ 780.34
		O Diário de Anne Frank	R$ 39.90	Autobiografia	12	Azul	Márcia Souza	R$ 478.80
		O Hobbit	R$ 29.90	Literatura Estrangeira	18	Azul	Márcia Souza	R$ 538.20
		O Leão, a Feiticeira e o Guarda-Roupa	R$ 29.90	Infanto-Juvenil	17	Sul	Roberto Albuquerque	R$ 508.30
		O Nome da Rosa	R$ 60.50	Literatura Estrangeira-Romances	5	Sul	Fabiana Rossi	R$ 302.50
		O Pequeno Príncipe	R$ 9.90	Juvenil	30	Sul	João Pereira	R$ 297.00
		O Relatório Hite sobre sexualidade feminina	R$ 59.99	Psicologia & Psicanalise	2	Azul	Roberto Albuquerque	R$ 119.98
		O Senhor dos Anéis	R$ 29.90	Literatura Estrangeira-Ficção Fantasiosa	13	Dois	Roberto Albuquerque	R$ 388.70
		O Sonho da Câmara Vermelha	R$ 29.90	Literatura Estrangeira - Ficção Fantasiosa	4	Dois	João Pereira	R$ 119.60
		Pense e Enriqueça	R$ 39.90	Auto Ajuda	1	Sul	Roberto Albuquerque	R$ 39.90
		Um Conto de Duas Cidades	R$ 56.90	Literatura Estrangeira-Romances	3	Azul	Fabiana Rossi	R$ 170.70
		Um Safado em Dublin	R$ 17.99	Literatura Estrangeira-Ficção Cientifica	3	Azul	Fabiana Rossi	R$ 53.97
EXERCÍCIO 07.1
Banco de Dados
Ex.7.2
		Vendedores		Loja		Gênero
		Alberto da Silva		Azul		Auto Ajuda
		Antônio dos Santos		Dois		Autobiografia
		Fabiana Rossi		Sul		Drama/Romance
		Horácio Almeida				Drama/Romance 
		João Pereira				Filosofia, Religiões-Cristianismo
		Márcia Souza				História em quadrinhos - Mangá
		Maria José Costa				Infantil
		Mário Oliveira				Infanto-Juvenil
		Roberto Albuquerque				Juvenil
		Sílvia Pires				Literatura Estrangeira
						Literatura
						Literatura Brasileira-Romances
						Literatura Estrangeira
						Literatura Estrangeira - Ficção Fantasiosa
						Literatura Estrangeira - Romances
						Literatura Estrangeira-Ficção Cientifica
						Literatura Estrangeira-Ficção Fantasiosa
						Literatura Estrangeira-Romances
						Literatura Juvenil
						Psicologia & Psicanalise
						Romances
EXERCÍCIO 07.2
Tabelas
Ex.8
		Consulta de disponibilidade
		Título do Livro
		A Marca do Zorro
		Resultado da Consulta
		Código	Aluno	Última Retirada	Devolução	Disponibilidade
		1002	Antônio dos Santos	1/2/18		Alugado
EXERCÍCIO 08
1) Faça o hiperlink da caixa "Banco de Dados" com a planilha Ex.8.1;
2) Nomeie os intervalos das tabelas de banco de dados e critério;
2) Crie um menu suspenso com as informações da planilha "Ex.8.1" no campo "Título do Livro" para localizar o critério na tabela "Consulta de disponibilidade".
3) Use a função de banco de dados para retornar os dados pedidos na tabela "Resultado da Consulta".
4) Insira uma fórmula para tratamento de erros, pois como a devolução de alguns livros ainda não aconteceu, o campo devolução deverá ficar vazio.
5) Insira uma formatação condicional para demonstrar, com mais intensidade, quando um livro está disponível ou não.
Dica: As informações necessárias estão na planilha "Ex.8.1". 
Banco de Dados
/xl/drawings/drawing11.xml#Ex.8.1!A1
Ex.8.1
		Código	Título do Livro	Gênero	Campi	Aluno	Última Retirada	Devolução	Disponibilidade
		1001	A Águia Pousou	 Filosofia, Religiões-Cristianismo	Memorial	Alberto da Silva	1/24/18		Alugado
		1002	A Marca do Zorro	Literatura Estrangeira-Romances	Vila Prudente	Antônio dos Santos	1/2/18		Alugado
		1003	A Menina e o Porquinho	Infantil	Vila Maria	Fabiana Rossi	1/23/18		Alugado
		1004	As Pontes de Madison	Drama/Romance	Vergueiro	Horácio Almeida	1/13/18	1/20/18	Disponível
		1005	Beleza Negra	Autobiografia	Santo amaro	João Pereira	1/2/18	1/9/18	Disponível
		1006	Ben-Hur: Uma História dos Tempos de Cristo	Literatura	Vila Prudente	Márcia Souza	1/25/18		Alugado
		1007	Cem Anos de Solidão	Literatura Estrangeira-Romances	Memorial	Maria José Costa	1/16/18	1/23/18	Disponível
		1008	Como Eu Era Antes de Você	Drama/Romance 	Vila Maria	Mário Oliveira	1/16/18	1/23/18	Disponível
		1009	Don Quixote	Literatura Estrangeira	Vila Prudente	Roberto Albuquerque	1/8/18	1/15/18	Disponível
		1010	Ela, a Feiticeira	Literatura Estrangeira - Romances	Vergueiro	Sílvia Pires	1/18/18		Alugado
		1011	Era uma Vez em Watership Down	Romances	Memorial	Alberto da Silva	1/7/18		Alugado
		1012	Harry Potter e a Câmara Secreta	Literatura Juvenil	Vila Maria	Alberto da Silva	1/16/18	1/23/18	Disponível
		1013	Harry Potter e a Ordem da Fênix	Literatura Juvenil	Vergueiro	Horácio Almeida	1/23/18	1/30/18	Disponível
		1014	Harry Potter e a Pedra Filosofal	Literatura Juvenil	Vila Prudente	João Pereira	1/22/18	1/29/18	Disponível
		1015	Harry Potter e as Relíquias da Morte	Literatura Juvenil	Memorial	João Pereira	1/18/18		Alugado
		1016	Harry Potter e o Cálice de Fogo	Literatura Juvenil	Vila Prudente	Maria José Costa	1/11/18		Alugado
		1017	Harry Potter e o Enigma do Príncipe	Literatura Juvenil	Vergueiro	Mário Oliveira	1/2/18	1/9/18	Disponível
		1018	Harry Potter e o Prisioneiro de Azkaban	Literatura Juvenil	Santo amaro	Mário Oliveira	1/8/18		Alugado
		1019	Heidi	Infantil	Vergueiro	Fabiana Rossi	1/4/18	1/11/18	Disponível
		1020	Lolita	Literatura Estrangeira-Romances	Vila Prudente	Antônio dos Santos	1/13/18	1/20/18	Disponível
		1021	Meu Filho, Meu Tesouro	Literatura Juvenil	Memorial	João Pereira	1/1/18	1/8/18	Disponível
		1022	O Alquimista	Literatura Brasileira-Romances	Vila Maria	Antônio dos Santos	1/4/18	1/11/18	Disponível
		1023	O Apanhador no Campo de Centeio	Literatura Estrangeira	Santo amaro	Horácio Almeida	1/13/18		Alugado
		1024	O Caso dos Dez Negrinhos	História em quadrinhos - Mangá	Vila Prudente	Fabiana Rossi	1/17/18	1/24/18	Disponível
		1025	O Código Da Vinci	Literatura Estrangeira	Santo amaro	João Pereira	1/17/18	1/24/18	Disponível
		1026	O Diário de Anne Frank	Autobiografia	Vergueiro	Márcia Souza	1/19/18	1/26/18	Disponível
		1027	O Hobbit	Literatura Estrangeira	Vila Prudente	Márcia Souza	1/8/18	1/15/18	Disponível
		1028	O Leão, a Feiticeira e o Guarda-Roupa	Infanto-Juvenil	Vergueiro	Roberto Albuquerque	1/24/18		Alugado
		1029	O Nome da Rosa	Literatura Estrangeira-Romances	Memorial	Fabiana Rossi	1/2/18	1/9/18	Disponível
		1030	O Pequeno Príncipe	Juvenil	Vila Prudente	João Pereira	1/13/18	1/20/18	Disponível
		1031	O Relatório Hite sobre sexualidade feminina	Psicologia & Psicanalise	Vila Maria	Roberto Albuquerque	1/22/18	1/29/18	Disponível
		1032	O Senhor dos Anéis	Literatura Estrangeira-Ficção Fantasiosa	Vila Prudente	Roberto Albuquerque	1/11/18		Alugado
		1033	O Sonho da Câmara Vermelha	Literatura Estrangeira - Ficção Fantasiosa	Vergueiro	João Pereira	1/18/18	1/25/18	Disponível
		1034	Pense e Enriqueça	Auto Ajuda	Vila Prudente	Roberto Albuquerque	1/10/18	1/17/18	Disponível
		1035	Um Conto de Duas Cidades	Literatura Estrangeira-Romances	Vila Maria	Fabiana Rossi	1/6/18		Alugado
		1036	Um Safado em Dublin	Literatura Estrangeira-Ficção Cientifica	Vila Prudente	Fabiana Rossi	1/24/18	1/31/18	Disponível
EXERCÍCIO 08.1
BANCO DE DADOS
image1.png
image2.png
image3.pngimage4.png
image5.png
image6.png
image7.png
image8.jpg
image9.jpg