excel avanc aula3

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

Aula3/aula3_avanc1.xls
Alimentos
		Produtos em Estoque - Alimentos - Av.
		
		Código		Produto		Unidade
Medida		Qtde em 
Estoque		Descrição		Preço 
de Custo		Preço 
de Venda		Lucro		Data de 
Validade		Qtde em 
Estoque		Lucro Esperado
		0001		Sal		sc		15		15 Sacos		R$0.50		R$0.80		R$4.50		20-Nov-05		48		R$14.50
		0002		Arroz		sc		20		20 Sacos		R$7.50		R$12.00		R$90.00		5-Aug-05		22		R$100.00
		0003		Feijão		sc		28		28 Sacos		R$1.80		R$2.88		R$30.24		3-Feb-05		37		R$40.24				Unidades de Medida
		0004		Açucar		sc		19		19 Sacos		R$0.60		R$0.96		R$6.84		14-Dec-04		47		R$16.84				cx		Caixa
		0005		Farinha		sc		10		10 Sacos		R$0.80		R$1.28		R$4.80		8-Oct-04		31		R$14.80				dz		Dúzia
		0006		Leite		cx		40		40 Caixas		R$0.90		R$1.44		R$21.60		14-Oct-04		59		R$31.60				lt		Lata
		0007		Óleo		lt		1		1 Lata		R$1.20		R$1.92		R$0.72		25-Jan-05		15		R$10.72				pct		Pacote
		0008		Bolacha		pct		22		22 Pacotes		R$0.70		R$1.12		R$9.24		30-Mar-05		46		R$19.24				sc		Saco
		0009		Refrigerante		un		15		15 Unidades		R$1.10		R$1.76		R$9.90		12-Dec-05		30		R$19.90				un		Unidade
		0010		Iogurte		un		16		16 Unidades		R$1.30		R$2.08		R$12.48		29-Sep-04		29		R$22.48
		0011		Milho		lt		23		23 Latas		R$0.30		R$0.48		R$4.14		8-Feb-05		79		R$14.14
		0012		Ervilha		lt		30		30 Latas		R$0.40		R$0.64		R$7.20		20-Nov-04		72		R$17.20
		0013		Ovo		dz		46		46 Dúzias		R$0.70		R$1.12		R$19.32		10-Oct-04		70		R$29.32
		0014		Adoçante		un		40		40 Unidades		R$0.90		R$1.44		R$21.60		30-Aug-06		59		R$31.60
		0015		Suco		pct		38		38 Pacotes		R$0.30		R$0.48		R$6.84		12-Sep-05		94		R$16.84
		0016		Requeijão		un		25		25 Unidades		R$1.80		R$2.88		R$27.00		20-Nov-04		34		R$37.00
		0017		Café		pct		17		17 Pacotes		R$1.50		R$2.40		R$15.30		8-Apr-06		28		R$25.30
		0018		Achocolatado		un		42		42 Unidades		R$1.90		R$3.04		R$47.88		9-Jul-05		51		R$57.88
		0019		Queijo		un		30		30 Unidades		R$5.40		R$8.64		R$97.20		13-Dec-04		33		R$107.20
		0020		Presunto		un		12		12 Unidades		R$3.80		R$6.08		R$27.36		30-Nov-04		16		R$37.36
		
								Total				R$33.40		R$53.44		R$464.16
								Média				R$1.67		R$2.67		R$23.21
								Maior Valor				R$7.50		R$12.00		R$97.20
								Menor Valor				R$0.30		R$0.48		R$0.72
		
		Cálculos
		O Preço de Venda deverá ser o Preço de Custo acrescido de 60%.
		O Lucro é o Preço de Venda menos o Preço de Custo multiplicado pela quantidade.
		Calcular o total, média, maior valor e menor valor das colunas Preço de Custo e de Venda.
		Para realizar os cálculos acima para a coluna Lucro, nomear esta coluna e utilizar este nome na função.
		Em Lucro Esperado, utilizar o recurso Atingir Meta para calcular a qtde ncessária de venda para se ter R$10 a mais de lucro
		Na célula k5 inserir o valor R$300. Na coluna I, somar o lucro com o valor desta célula. Utilizar o recurso Fixar Célula.
Aula3/aula3_avanc2.xls
Carros 0Km
		Tabela de Preços de Carros 0Km
		
		Código		Modelo		Categoria		Marca		Combustível		Preço (R$)		Classificação		Parcelas
		0001		Palio 1.0 ED		Econômico		Fiat		Alcool		R$14,490.00		Popular		$471.44
		0002		Uno Mille		Econômico		Fiat		Alcool		R$15,248.00		Popular		$496.10
		0003		Fiesta 1.0		Econômico		Ford		Alcool		R$15,475.00		Popular		$503.49
		0004		Ka 1.0		Econômico		Ford		Alcool		R$14,960.00		Popular		$486.73				Filtro
		0005		Corsa Wind 1.0 2p		Econômico		GM		Alcool		R$15,408.00		Popular		$501.31				Código		Modelo		Categoria		Marca		Combustível		Preço (R$)
		0006		Gol 1000 S		Econômico		VW		Alcool		R$15,287.00		Popular		$497.37										Fiat
		0007		Gol 1.8 4p		Econômico		VW		Alcool		R$22,140.00		Luxo		$720.34
						Econômico Total						R$113,008.00				$3,676.78
		0013		Fiorino 1.3		Furgão		Fiat		Gasolina		R$16,954.00		Popular		$551.61
		0015		Courier CLX 1.3		Furgão		Ford		Gasolina		R$17,385.00		Popular		$565.63
		0020		Kombi Furgão		Furgão		VW		Gasolina		R$19,990.00		Luxo		$650.39				Resultado
						Furgão Total						R$54,329.00				$1,767.62
		0008		Marea Weekend HLX		Perua		Fiat		Diesel		R$42,967.00		Luxo		$1,397.95				Código		Modelo		Categoria		Marca		Combustível		Preço (R$)
		0009		Parati GLS 2.0		Perua		VW		Diesel		R$27,459.00		Luxo		$893.39				0007		Fiorino 1.3		Furgão		Fiat		Gasolina		R$16,954.00
						Perua Total						R$70,426.00				$2,291.35
		0010		Ranger XL 2.5		Pick Up		Ford		Flex		R$35,170.00		Luxo		$1,144.27				0011		Marea HLX		Sedan		Fiat		Gasolina		R$40,507.00
		0011		Balzer 2.2		Pick Up		GM		Flex		R$38,232.00		Luxo		$1,243.90				0012		Marea Weekend HLX		Perua		Fiat		Diesel		R$42,967.00
		0012		S10 DLX Diesel 2.5		Pick Up		GM		Flex		R$24,296.00		Luxo		$790.48				0013		Palio 1.0 ED		Econômico		Fiat		Alcool		R$14,490.00
						Pick Up Total						R$97,698.00				$3,178.66
		0014		Marea HLX		Sedan		Fiat		Gasolina		R$40,507.00		Luxo		$1,317.92				0018		Uno Mille		Econômico		Fiat		Alcool		R$15,248.00
		0016		Escort GLX 1.8		Sedan		Ford		Gasolina		R$30,305.00		Luxo		$985.99
		0017		Astra GLS 2.0		Sedan		GM		Gasolina		R$29,640.00		Luxo		$964.35
		0018		Vectra GLS 2.2		Sedan		GM		Gasolina		R$40,709.00		Luxo		$1,324.49
		0019		Corsa 4p		Sedan		GM		Gasolina		R$18,560.00		Luxo		$603.86
						Sedan Total						R$159,721.00				$5,196.61
						Total geral						R$495,182.00				$16,111.01
		
										Total		R$495,182.00
										Média		R$24,759.10
										Maior Valor		R$42,967.00
										Menor Valor		R$14,490.00
		
				Tabela de Combustível						Valor Total por Marca
				Econômico		Alcool				GM		R$166,845.00
				Furgão		Gasolina				Fiat		R$130,166.00
				Perua		Diesel				Ford		R$113,295.00
				Pick Up		Flex				VW		R$84,876.00
				Sedan		Gasolina
		
				Tarefas
				Colunas digitadas: Código, Modelo, Categoria, Marca e Preço
				Para exibir o Combustível, utilizar uma função de procura, sendo que o intervalo a ser utilizado deverá ser nomeado.
				Em Classificação, analisar o preço do carro. Se custar mais do que R$18000, escrever Luxo, senão, Popular.
				Insira mais cinco carros utilizando um formulário.
				Em Valor Total por Marca, utilizar a função SomaSe
				Acrescentar Filtro. Filtrar primeiramente por Marca, após por Categoria e por fim por Combustível. Quando o filtro estiver sendo aplicado, os cálculos deverão ser atualizado de acordo com os dados que estão sendo exibidos (Função Subtotal).
				Na tabela Filtro, indicar qual critério a ser utilizado. Na tabela Resultado, exibir os valores de acordo com o critério passado. Utilizar para isso Filtro Avançado.
				Divida a tabela em níveis, exibindo a Média de Preços por Categoria. Para isso, classifique primeiramente a tabela por Categoria (Crescente) e depois por Preço (Decrescente). Após, utilize o recurso SubTotais.
Utilize esta tabela para o filtro avançado. Entre em Dados=>Filtrar=>Filtro Avançado.
Como para realizar o filtro não foram copiados todos os títulos da tabela, em "Intervalo da lista", estará "$A$3:$F$23".
No "Intervalo de critérios", "$J$8:$O$9" que é esta tabelinha onde digito meu filtro. No meu exemplo, quero que ele exiba somente os carros da Fiat.
Se em "Ação" estiver selecionado "Filtrar lista no local", exibirá o resultado na própria tabela. Como não quero que deixe de exibir todos os dados, eu criei esta tabela chamada "Resultado". Aí selecionamos "Copiar para outro local" e no "Copiar para" indicamos "$J$14:$O$15".
OBS.: Em "Intervalo da lista", "Intervalo de critérios" e "Filtrar lista no local" deve se informar uma selação com as mesmas colunas, nem mais e ne menos colunas.
A tabela está exibindo os dados com o recurso Subtotais ativado, por isso consta a soma dos valores por Categoria. Clicando no "+" ou "-" da coluna à esquerda da janela, ele omite ou não os campos. Para remover o Subtotais: Dados => Subtotais => Remover todos.
Aula3/aula3_avanc3.xls
Pedido
		Pedido de Compra
		
		Quantidade		Produto		Tamanho
Descrição		Preço Unitário		Preço Total
		008		Camiseta Manga Curta		P		Pequeno		R$6.90		R$55.20
		006		Calça Jeans		P		Pequeno		R$12.50		R$75.00
		004		Calça Sarja		G		Grande		R$15.90		R$63.60
		010		Vestido Longo		M		Médio		R$23.45		R$234.50
		009		Mini-Saia		M		Médio		R$13.50		R$121.50
		010		Camisa Manga Longa		G		Grande		R$19.80		R$198.00
		007		Bermuda Masculina		P		Pequeno		R$10.40		R$72.80
		006		Camiseta Infantil		G		Grande		R$6.70		R$40.20
		003		Calça Capri		M		Médio		R$18.70		R$56.10
		018		Vestido Infantil		P		Pequeno		R$12.60		R$226.80
		004		Terno		G		Grande		R$87.30		R$349.20
		007		Bermuda Feminina		M		Médio		R$13.56		R$94.92
		005		Shorts Feminino		P		Pequeno		R$8.40		R$42.00
		006		Shorts Masculino		M		Médio		R$17.30		R$103.80
		
		
										Total Geral		R$1,733.62
										Média Geral		R$123.83
										Maior		R$349.20
										Menor		R$40.20
		
		Área Critério												Tamanhos				Totais
		Quantidade		Produto		Tamanho				Preço Unitário				P		Pequeno		R$50.80
						P								M		Médio		R$86.51
														G		Grande		R$129.70
		
		
		Área Exportação
		Quantidade		Produto		Tamanho				Preço Unitário
		008		Camiseta Manga Curta		P				R$6.90
		006		Calça Jeans		P				R$12.50
		007		Bermuda Masculina		P				R$10.40
		018		Vestido Infantil		P				R$12.60
		005		Shorts Feminino		P				R$8.40
Plan2
		
Plan3
		
Aula3/aula3_avanc4.xls
Vendas
		Vendas
		
		Data		Tipo		Nível do 
Periférico		Região		Vendedor		Valor		Qtde
Vendida		Total		Comissão		Qtde
Vendida		Comissão
 Desejada		Classificação		Descrição do
Periférico
		3-Jan		Periférico		I		Sudeste		Flávio		R$150.00		5		R$750.00		R$37.50		7		R$50.00		Vendas Ruins		Mouse USB
		3-Jan		Computador		V		Sudeste		William		R$2,000.00		8		R$16,000.00		R$1,600.00		10		R$2,000.00		Boas Vendas		Não ser periférico				Tabela de 
Periféricos
		3-Jan		Computador		V		Sul		Itamar		R$2,500.00		10		R$25,000.00		R$2,500.00		12		R$3,000.00		Boas Vendas		Não ser periférico				Nível		Descrição
		4-Jan		Software		V		Nordeste		Itamar		R$1,000.00		7		R$7,000.00		R$700.00		10		R$1,000.00		Vendas Ruins		Não ser periférico				I		Mouse
		5-Jan		Computador		V		Centro Oeste		William		R$3,000.00		9		R$27,000.00		R$2,700.00		10		R$3,000.00		Boas Vendas		Não ser periférico				II		Teclado
		5-Jan		Periférico		IV		Norte		William		R$1,200.00		14		R$16,800.00		R$840.00		21		R$1,250.00		Boas Vendas		Caixa de Som				III		Microfone
		5-Jan		Periférico		II		Sudeste		Flávio		R$30.00		13		R$390.00		R$19.50		17		R$25.00		Vendas Ruins		Teclado Comum				IV		Caixa de Som
		5-Jan		Software		V		Sul		Itamar		R$800.00		20		R$16,000.00		R$1,600.00		25		R$2,000.00		Boas Vendas		Não ser periférico				V		Não ser periférico
		6-Jan		Computador		V		Sul		Flávio		R$3,000.00		5		R$15,000.00		R$1,500.00		7		R$2,000.00		Vendas Ruins		Não ser periférico
		7-Jan		Computador		V		Nordeste		Flávio		R$1,800.00		19		R$34,200.00		R$3,420.00		22		R$4,000.00		Boas Vendas		Não ser periférico
		7-Jan		Periférico		IV		Nordeste		William		R$95.00		8		R$760.00		R$38.00		11		R$50.00		Vendas Ruins		Caixa de Som
		7-Jan		Computador		V		Norte		Itamar		R$3,200.00		6		R$19,200.00		R$1,920.00		8		R$2,500.00		Boas Vendas		Não ser periférico
		10-Jan		Computador		V		Sudeste		Itamar		R$1,100.00		7		R$7,700.00		R$770.00		11		R$1,200.00		Vendas Ruins		Não ser periférico
		10-Jan		Software		V		Sul		Flávio		R$900.00		6		R$5,400.00		R$540.00		9		R$800.00		Vendas Ruins		Não ser periférico
		11-Jan		Software		V		Centro Oeste		William		R$500.00		12		R$6,000.00		R$600.00		20		R$1,000.00		Vendas Ruins		Não ser periférico
		11-Jan		Periférico		III		Nordeste		Itamar		R$230.00		5		R$1,150.00		R$57.50		6		R$70.00		Vendas Ruins		Microfone
		11-Jan		Periférico		I		Norte		Flávio		R$40.00		3		R$120.00		R$6.00		8		R$15.00		Vendas Ruins		Mouse PS/2
		11-Jan		Computador		V		Sul		Flávio		R$4,000.00		4		R$16,000.00		R$1,600.00		5		R$2,000.00		Boas Vendas		Não ser periférico
		
														Total		R$214,470.00
														Média		R$11,915.00
														Maior Valor		R$34,200.00
														Meno Valor		R$120.00
		
		Critérios
		Data		Tipo		Nível do
Periférico		Região		Vendedor		Valor		Qtde
Vendida		Total		Comissão				Informações por Vendedor
																						Nome		Comissão Total				% do Total		Comissão
Desejada		% Desejada 
do Total
																						Itamar		R$7,547.50				3.52		R$9,770.00		4.56
		Resultados																				Flávio		R$7,123.00				3.32		R$8,890.00		4.15
		Data		Tipo		Nível do
Periférico		Região		Vendedor		Valor		Qtde
Vendida		Total		Comissão				William		R$5,778.00				2.69		R$7,300.00		3.40
Aula3/enunciado_avanc4.doc
Exercícios de Vendas
Colunas digitadas: Data, Tipo, Nível do Periférico, Região, Vendedor, Valor e Quantidade Vendida.
Coluna Total: multiplicação da Qtde com o Valor.
Comissão:
Verificar se o Tipo do Computador é Computador ou Software:
Sendo a condição acima verdadeira, a comissão será 10% do Total.
Sendo falsa,a comissão será 5% do Total.
Qtde Vendida e Comissão Desejada, utilizar o recurso Atingir Meta.
Em Classificação:
Se a região for Sul ou Sudeste, verificar se o Total é superior a R$15000. Caso seja, exibir “Boas Vendas”, senão, exibir “Vendas Ruins”.
Se forem as demais regiões, verificar se o Total é superior a R$8000. Caso seja, exibir “Boas Vendas”, senão exibir “Vendas Ruins”.
Em Qtde Vendida e Comissão desejada, utilizar o Recurso Atingir Meta para calcular a quantidade de produtos que o vendedor terá que vender para ter a comissão desejada por ele.
Em Descrição do Periférico:
De acordo com o Nível do Periférico, buscar a descrição correspondente.
( Caso seja mouse (Nível I), analisar se o valor é:
- Maior do que R$100: exibir USB.
- Entre R$50 e R$100: exibir Serial.
- Menor do que R$50: exibir PS/2.
( Caso seja Teclado (Nível II) analisar se o Valor é superior a R$100. Se for, exibir Wireless, senão, exibir Comum.
( Se não for nem teclado nem mouse, não exibir nada a frente da desrição.
Colocar Autofiltro de modo que os cálculos referentes sejam atualizados.
Utilizar Filtro Avançado nas tabelas Critérios e Resultados.
Utilizar Somase em Informações por Vendedor.
Exibir o Total das colunas Valor, Comissão e Total por Região. Para isso, utilize o recurso SubTotais.