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

2022-1 – Professor Fernando Dreux

2º TRABALHO

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

Considerar uma empresa de Departamentos com diversos Produtos.

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

Coluna A – DEPARTAMENTO

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

Coluna B – DESC. PRODUTO

Inserir na célula B2 uma fórmula que pesquisará o valor da célula C2 (PROCV) na planilha Produtos e retornará com o conteúdo da coluna B (índice 2).

Coluna C – COD. PRODUTO

Validar a entrada de dados com a lista da coluna D da planilha Produtos.

Coluna D – VENDEDOR

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

Coluna E – DATA DA VENDA

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

Coluna F – DESCONTO

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

Coluna G – VALOR

Inserir na célula G2 uma fórmula que calculará o valor da venda aplicando o desconto dado pelo vendedor ao preço do Produto na planilha Produtos.

Obs:

1. Utilizar as funções INDICE e CORRESP para encontrar o valor do produto na coluna G.

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 Departamento, por ano; Guia TD1;
2. Valor das vendas, por vendedor, por Departamento (Departamento como campo de filtro); Guia TD2;
3. Quantidade das vendas por Departamento, por Descrição do Produto (campos de linha). Recolher o detalhe Produto. Classificar em ordem decrescente de Quantidade; Guia TD3;
4. 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 TD4;
5. Valor das vendas, por Descrição do Produto, que foram realizadas com desconto = 0% (campo de filtro);  Guia TD5;
6. Quantidade de vendas por vendedor, por trimestre; Guia TD6;
7. A partir da TD6, mostrar as vendas do vendedor Carlos. Guia TD7;

...