Trabalho Gerar Uma Planilha Como Base De Dados Para Elaboração De Diversas Tabelas Dinâmicas

[pic 1]

Coordenação Central de Cursos de Extensão

Curso de Excel  - Avançado

2023-1 – Professor Fernando Dreux

2º TRABALHO

OBJETIVO – GERAR UMA PLANILHA COMO BASE DE DADOS PARA ELABORAÇÃO DE DIVERSAS TABELAS DINÂMICAS.

Considerar uma revendedora de automóveis de variados modelos de diversos fabricantes.

Utilizando-se o arquivo Trabalho2.xlsx, executar os seguintes passos para preparação da planilha:

Coluna A – Fabricante

Inserir na célula A2 uma função que pegará os dois primeiros caracteres da célula C2 (ESQUERDA), fará uma pesquisa na planilha Fabricante (PROCX) e retornará com o conteúdo da coluna B da planilha Fabricante.

Coluna B – Tipo

Inserir na célula B2 uma fórmula que pesquisará (SE) os dois primeiros caracteres  da célula C2 (ESQUERDA), e preencherá com NACIONAL ou IMPORTADO.

Coluna C – Modelo

Inserir na célula C2 uma função (PROCV) que com o valor da célula D2, faz uma pesquisa na planilha Modelo e retorna com o conteúdo da coluna C (índice 2).

Coluna D – Placa

Validar a entrada de dados com a lista da coluna F da planilha Modelo.

Coluna E – Vendedor

Validar a entrada de dados com a lista da planilha Vendedor.

Coluna F – Data

Validar a entrada de dados para datas posteriores a 01 de janeiro de 2021.

Coluna G – Desconto

Validar a entrada de dados com a lista da planilha Desconto.

Coluna H – Valor

Inserir na célula H2 uma fórmula que calculará o valor da venda aplicando o desconto dado pelo vendedor ao preço do modelo na planilha Modelo, utilizando as funções INDICE e CORRESP.

Observações Gerais:

1. Todas as fórmulas criadas deverão ser copiadas para as demais células da mesma coluna.
2. Toda vez que existir um endereço de tabela este deverá ser transformado em absoluto para que a fórmula possa ser copiada sem erro ou nomear a tabela.
3. A planilha deverá ser preenchida com pelo menos 45 vendas diferentes.

Após o preenchimento da planilha, gerar as seguintes Tabelas Dinâmicas:

1. Valor das vendas por fabricante, por ano; Guia TD1;
2. Valor das vendas, por modelo, por vendedor (vendedor como campo de filtro); Guia TD2;
3. Valor das vendas por tipo, por fabricante. (campos de linha) Recolher detalhe (fabricante); Guia TD3;
4. Quantidade das vendas por fabricante, por vendedor; Guia TD4;
5. Valor das vendas de 15 de setembro de 2021 até hoje, por vendedor. Agrupando a data por mês e colocando o subtotal por ano. Guia TD5;
6. Valor das vendas, por tipo, que foram realizadas com desconto = 0% (campo de filtro);  Guia TD6;
7. Quantidade de vendas por vendedor, por trimestre; Guia TD7;
8. A partir da Tabela Dinâmica do item 7, mostrar as vendas do vendedor Carlos. Guia TD8;