excel-avancado-aula2

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

Aula2/aula2_avanc1.xls
Calçados
		Loja de Calçados 2
		Mês		Total de
Compra		Total de
Venda		Lucro		Qtde		Análise
do Mês		Código
Funcionário
Destaque		Nome		% Comissão
		Janeiro		R$ 185.88		R$ 220.73		R$ 34.85		62		Ruim		0003		Ana Maria Oliveira		R$ 1.74
		Fevereiro		R$ 183.20		R$ 211.86		R$ 28.66		70		Ruim		0002		Carlos Alberto Menezes		R$ 1.15
		Março		R$ 24.50		R$ 29.40		R$ 4.90		8		Bom Mês		0015		Bruna Mariano		R$ 0.20
		Abril		R$ 70.79		R$ 77.87		R$ 7.08		30		Ruim		0008		Luciano de Barros		R$ 0.42
		Maio		R$ 111.77		R$ 122.95		R$ 11.18		51		Ruim		0003		Ana Maria Oliveira		R$ 0.56
		Junho		R$ - 0		R$ - 0		R$ - 0		0		Bom Mês		0007		Mariana Peres		R$ - 0
		Julho		R$ 23.29		R$ 25.62		R$ 2.33		16		Ruim		0013		Antônio Carlos Camargo		R$ 0.14
		Agosto		R$ 85.40		R$ 120.00		R$ 34.60		38		Bom Mês		0002		Carlos Alberto Menezes		R$ 1.38
		Setembro		R$ 65.78		R$ 81.90		R$ 16.12		13		Bom Mês		0009		Tadeu Maffei		R$ 0.48
		Outubro		-		-		-		-		-		-		-		-
		Novembro		-		-		-		-		-		-		-		-
		Dezembro		-		-		-		-		-		-		-		-
		Tabela de Funcionários								Cálculos
		Código		Nome		Comissão				Salve a pasta com o nome Aula2_Exer2.
		0001		João da Silva Júnior		4%				Em Total de Compra, somar o preço de compra de todos calçados do mês de
janeiro da planilha anterior.
		0002		Carlos Alberto Menezes		4%
		0003		Ana Maria Oliveira		5%				Em Total de Venda, realizar o mesmo cálculo acima, porém somando os
preços de venda.
		0004		Luís dos Santos		6%
		0005		Anderson Medeiros		3%				Em Qtde, realizar o mesmo cálculo acima, porém somando quantidades do
referido mês.
		0006		Juliana Rodrigues		5%
		0007		Mariana Peres		4%				Na coluna Análise do Mês, analisar primeiramente se o mês é janeiro, fevereiro ou março. Caso seja, verificar se o preço de venda é maior ou igual ao preço de compra acrescido de 20%. Sendo, exibir "Bom Mês", senão, "Ruim". Caso sejam os demais meses, fazer a mesma análise, porém com 15%.
		0008		Luciano de Barros		6%
		0009		Tadeu Maffei		3%
		0010		Angélica Costa		3%
		0011		Cláudia Lima		4%				Exibir Precedentes e Dependentes (células utilizadas) nas células que
contêm fórmulas.
		0012		Pedro Cintra		5%
		0013		Antônio Carlos Camargo		6%				No Nome do Funcionário utilizar a função Procv, nomeando o intervalo.
		0014		Fábio Carvalho		5%				Em % Comissão, multiplicar a porcentagem pelo lucro (utilize Procv para a %).
		0015		Bruna Mariano		4%				Inserir mais cinco funcionários utilizando formulário.
										Colocar comentário nas células que contêm fórmulas.
										Nomeie a planilha com o nome "Calçados".
										Os meses classificados como "Ruim" destacar com a cor vermelha.
Os como "Bom Mês", com a cor Azul.
&LAula 2 - Excel Avançado
Vínculo entre planilhas.
SOMASE
SOMASE
SE ENCADEADO COM "OU"
Não sabe colocar o zero na frente?!
Botão direto => Formatar células
Personalizado
Digite 000
Qtde de 0 desejada + 1 zero.
PROCV
Selecione o intervalo de células
Menu Página Inicial => Formatação Condicional
Aula2/aula2_avanc2.xls
Pedidos
		Loja de Calçados - 1º Sem/2014
		Modelo		Tamanho		Código		Marca		Cor		Valor Par		Qtde		Valor Total		Nº Pedido		Mês		Promoção		Preço de 
Venda		Lucro		Qtde Ideal
de Venda		Lucro
Ideal
		5		33		533		Azaléia		Branca		R$ 29.90		8		R$ 239.20		2538		Janeiro		Preço Normal		R$ 35.88		R$ 47.84		10		R$ 60.00				Informe o Modelo:		23
		5		35		535		Azaléia		Azul		R$ 29.90		15		R$ 448.50		2538		Janeiro		Preço Normal		R$ 35.88		R$ 89.70		17		R$ 100.00				Informe o Tamanho:		35
		5		38		538		Azaléia		Preta		R$ 32.90		10		R$ 329.00		2538		Janeiro		Preço Normal		R$ 39.48		R$ 65.80		12		R$ 80.00
		8		34		834		Bibi		Rosa		R$ 48.70		15		R$ 730.50		2540		Fevereiro		Promoção		R$ 56.01		R$ 109.57		16		R$ 120.00				Nº do Pedido		2910
		8		36		836		Bibi		Branca		R$ 48.70		20		R$ 974.00		2540		Fevereiro		Promoção		R$ 56.01		R$ 146.10		23		R$ 170.00				Valor do Par:		R$ 46.59
		8		33		833		Bibi		Branca		R$ 23.50		10		R$ 235.00		2540		Fevereiro		Preço Normal		R$ 28.20		R$ 47.00		13		R$ 60.00
		14		37		1437		Dakota		Preta		R$ 24.50		8		R$ 196.00		2789		Março		Preço Normal		R$ 29.40		R$ 39.20		10		R$ 50.00				Total Pedido:		R$ 10,471.63
		14		39		1439		Dakota		Marrom		R$ 24.50		5		R$ 122.50		2863		Abril		Preço Normal		R$ 26.95		R$ 12.25		8		R$ 20.00				Calçado de
Preço Maior:		R$ 1,557.50
		14		41		1441		Dakota		Beje		R$ 25.00		10		R$ 250.00		2863		Abril		Preço Normal		R$ 27.50		R$ 25.00		14		R$ 35.00				Calçado de
Preço Menor:		R$ 122.50
		20		34		2034		Via Marte		Vemelha		R$ 32.99		14		R$ 461.86		2910		Maio		Promoção		R$ 36.29		R$ 46.19		18		R$ 60.00				Média de
Preços:		R$ 523.58
		20		38		2038		Via Marte		Verde		R$ 32.99		12		R$ 395.88		2910		Maio		Promoção		R$ 36.29		R$ 39.59		15		R$ 50.00
		23		35		2335		Beira Rio		Azul		R$ 45.79		25		R$ 1,144.75		2910		Maio		Promoção		R$ 50.37		R$ 114.48		26		R$ 120.00
		23		38		2338		Beira Rio		Branca		R$ 46.59		19		R$ 885.21		2538		Janeiro		Promoção		R$ 53.58		R$ 132.78		21		R$ 150.00				Informações por Fabricante
		23		40		2340		Beira Rio		Amarela		R$ 46.59		10		R$ 465.90		2538		Janeiro		Preço Normal		R$ 55.91		R$ 93.18		12		R$ 110.00				Marca		Qtde Total		Valor Total
		38		35		3835		Azaléia		Preta		R$ 62.30		25		R$ 1,557.50		2540		Fevereiro		Promoção		R$ 71.65		R$ 233.62		27		R$ 250.00				Azaléia		89		R$ 3,266.19
		38		33		3833		Azaléia		Branca		R$ 21.29		15		R$ 319.35		2863		Abril		Preço Normal		R$ 23.42		R$ 31.94		19		R$ 40.00				Bibi		45		R$ 1,939.50
		38		39		3839		Azaléia		Amarela		R$ 23.29		16		R$ 372.64		3014		Julho		Preço Normal		R$ 25.62		R$ 37.26		19		R$ 45.00				Dakota		23		R$ 568.50
		45		34		4534		Picadilly		Azul		R$ 32.48		13		R$ 422.24		2987		Junho		Promoção		R$ 35.73		R$ 42.22		15		R$ 50.00				Via Marte		26		R$ 857.74
		45		36		4536		Picadilly		Preta		R$ 32.48		20		R$ 649.60		2987		Junho		Promoção		R$ 35.73		R$ 64.96		22		R$ 70.00				Beira Rio		54		R$ 2,495.86
		45		42		4542		Picadilly		Marrom		R$ 34.00		8		R$ 272.00		2987		Junho		Preço Normal		R$ 37.40		R$ 27.20		12		R$ 40.00				Picadilly		41		R$ 1,343.84
		Cálculos
		Na coluna Código, concatenar o tamanho do calçado com o número.
		Na coluna Valor Total, calcular o total do modelo de acordo com a qtde pedida.
		Na célula R7, insira uma função de procura que exiba o número do pedido de acordo com o modelo e tamanho passados respectivamente nas células R4 e R5.
		Fazer o mesmo cálculo acima para exibir o Valor do Modelo na célula R8.
		Na tabela Informações por Fabricante utilizar a função SomaSe.
		Na coluna Promoção verificar se o preço do par é maior do que R$30 e a Qtde comprada é maior do que 10 pares. Se for, exibir "Promoção", senão, "Preço Normal".
		Em Lucro Ideal utilizar o recurso Atingir Meta, onde a célula variar deverá ser da coluna Qtde Ideal.
		Em Preço de Venda verificar se o mês é Janeiro, Fevereiro ou Março. Se for, analisar se é Preço Normal ou não.
Caso seja Promoção, o preço de venda deverá ser o preço do par acrescido de 15%, senão, 20%. Se forem os demais meses, acrescentar 10%.
		Congele o título das colunas.
		Nomeie a planilha com o nome "Pedidos".
		Insira comentário nas células que contêm fórmula explicando o cálculo utilizado.
Para indicar as células utilizadas na fórmula - ou seja - as células precedentes, clique na célula S18 e vá em: Menu Fórmulas / Rastrear Precedentes
Para saber quais contas utilizam a quantidade de calçados, selecione a célula G4 e vá em:
Menu Fórmulas / Rastrear Dependentes
PROCV
MÁXIMO
MÍNIMO
MÉDIA
SOMASE
SE COM E
SE COM OU / SE ENCADEADO
Produtos em Estoque
		Produtos em Estoque																Data		17-Jan-15
		Código		Produto		Unidade
Medida		Qtde em 
Estoque		Qtde e 
Medida		Preço 
de Custo		Preço 
de Venda		Lucro		Data de 
Validade
Status do Vencimento
		0001		Sal		sc		15		15 Sacos		R$ 0.50		R$ 0.90		R$ 6.00		20-Nov-14		Vencido
		0002		Arroz		sc		20		20 Sacos		R$ 7.50		R$ 10.00		R$ 50.00		5-Aug-15		Ok
		0003		Feijão		sc		28		28 Sacos		R$ 1.80		R$ 2.50		R$ 19.60		3-Feb-15		Ok
		0004		Açucar		sc		19		19 Sacos		R$ 0.60		R$ 1.20		R$ 11.40		14-Dec-16		Ok
		0005		Farinha		sc		10		10 Sacos		R$ 0.80		R$ 1.60		R$ 8.00		15-Oct-14		Vencido
		0006		Leite		cx		40		40 Caixas		R$ 0.90		R$ 1.40		R$ 20.00		18-Oct-14		Vencido
		0007		Óleo		lt		8		8 Latas		R$ 1.20		R$ 1.80		R$ 4.80		25-Jan-15		Ok
		0008		Bolacha		pct		22		22 Pacotes		R$ 0.70		R$ 2.00		R$ 28.60		30-Mar-17		Ok
		0009		Refrigerante		un		15		15 Unidades		R$ 1.10		R$ 2.00		R$ 13.50		12-Dec-14		Vencido
		0010		Iogurte		un		16		16 Unidades		R$ 1.30		R$ 1.90		R$ 9.60		29-Dec-15		Ok
		0011		Milho		lt		23		23 Latas		R$ 0.30		R$ 0.80		R$ 11.50		8-Feb-15		Ok
		0012		Ervilha		lt		30		30 Latas		R$ 0.40		R$ 0.90		R$ 15.00		20-Nov-15		Ok
		0013		Ovo		dz		46		46 Dúzias		R$ 0.70		R$ 1.60		R$ 41.40		16-Oct-14		Vencido
		0014		Papel Higiênico		pct		40		40 Pacotes		R$ 2.40		R$ 3.48		R$ 43.20		-		-
		0015		Creme Dental		cx		38		38 Caixas		R$ 0.50		R$ 1.00		R$ 19.00		-		-
		0016		Sabonete		un		25		25 Unidades		R$ 0.20		R$ 0.60		R$ 10.00		-		-
		0017		Xampú		un		17		17 Unidades		R$ 1.50		R$ 3.20		R$ 28.90		-		-
		0018		Creme para Barbear		un		42		42 Unidades		R$ 1.90		R$ 2.60		R$ 29.40		-		-
		0019		Acetona		un		30		30 Unidades		R$ 0.40		R$ 0.80		R$ 12.00		-		-
		0020		Absorvente		pct		12		12 Pacotes		R$ 1.10		R$ 2.00		R$ 10.80		-		-
				Unidades de Medida						Cálculos
				cx		Caixa				Nomeie o intervalo Unidades de Media com o nome "Medidas".
				dz		Dúzia				Na coluna Qtde e Medida, concatenar a Qtde em Estoque com a Unidade de Medida
(use a função Procv para exibir a unidade de medida, utilizando o nome do intervalo).
				lt		Lata
				pct		Pacote				Utilizar o recurso Atingir Meta para saber a quantidade de produtos necessária a ser
vendida para se ter R$2,5 a mais de lucro.
				sc		Saco
				un		Unidade				Na coluna Quantidade de Dias Restantes para o Vencimento, destacar com a cor vermelha
qtde inferior a sete dias.
										Nas células que possuem fórmulas, inserir um comentário indicando qual cálculo foi utilizado e também
indicar quais células foram utilizadas na fórmula.
										Inserir mais cinco registros utilizando formulário.
										Dividir a planilha em duas áreas para facilitar a visualização.
										Classificar os produtos em ordem alfabética.
										Nomeie a planilha com o nome de Produtos em Estoque e a pasta com o nome de Aula2.
&LAula 2 - Excel Avançado
AGORA
CONCATENAÇÃO + PROCV + SE
FORMATAÇÃO CONDICIONAL
Men Página Inicial / Classificar e Filtrar
Para MS Excel 2013: botão direito na barra de botões => Personalizar Barra de Acesso Rápido => Na 1ª caixa de seleção, selecione "Todos os Comandos".
Selecione "Formulário" e adicione-o à direita. OK.
Na planilha, selecione os títulos de sua tabela e clique no botão Formulários que deverá estar na primeira barra ao alto de sua janela.
Adicione mais valores à tabela através da tela que se abrirá.
MS Excel 2013:
Menu Dados => Teste de Hipóteses => Atingir Meta.
Selecionar a tabela
Botão direito sobre ela
Definir nome
Digitar "Medidas"