Excel- Recursos Avançados

Atividade Síntese 1 2018 Todos os direitos reservados Senac Atividade Síntese Uma distribuidora de livros utiliza alguns recursos avançados em planilhas eletrônicas do Excel para organizar resumir e otimizar o processo de análise das informações Diariamente o Departamento de Vendas da distribuidora insere na planilha dados referentes aos pedidos realizados Fazem parte da planilha de pedidos o número e a data do pedido o nome do vendedor e o valor total de cada pedido Para facilitar a análise desses dados são aplicados os recursos de tabela dinâmica gráfico dinâmico validação de dados e funções para calcular a quantidade de pedidos e o valor total de vendas Ao mesmo tempo o Departamento de Vendas faz o controle das vendas realizadas e do estoque de livros e utiliza para análise de dados recursos como filtro avançado formulários e macro Para o controle de pagamento dos vendedores o Departamento Administrativo da distribuidora elaborou uma pequena planilha para pesquisar o nome e o salário base dos funcionários calcular a comissão INSS saláriofamília valetransporte valerefeição imposto de renda e salário líquido Com base no enunciado e nas imagens a seguir abra o arquivo AtividadePrática2xlsx do Módulo 4 e a partir dos dados digitados realize as seguintes instruções Atividade Síntese 2 2018 Todos os direitos reservados Senac ETAPA 1 Macro Passo 1 Salve o arquivo com seu nome completo como nome do arquivo e formato Pasta de Trabalho Habilitada para Macro do Excel Passo 2 Na planilha Pedidos grave uma Macro de endereço absoluto que digite os seguintes dados na célula A1 digite Excel Recursos Avançados na célula A2 digite seu nome completo Passo 3 Aplique a Macro às planilhas Estoque Vendas e Folha de Pagamento do arquivo Passo 4 Salve as alterações no arquivo Atividade Síntese 3 2018 Todos os direitos reservados Senac ETAPA 2 Pedidos Passo 1 Tabela Dinâmica Utilize os dados da planilha Pedidos e crie uma Tabela Dinâmica em uma nova planilha conforme imagem Atividade Síntese 4 2018 Todos os direitos reservados Senac Passo 2 Gráfico Dinâmico Considere a imagem a seguir e a partir dos dados da Tabela Dinâmica crie um Gráfico Dinâmico que resuma os dados dos vendedores Antônio Maria e Viviane e os respectivos valores de venda Passo 3 Validação de Dados Na planilha Pedidos valide as células F2 e G2 impedindo que os usuários insiram dados inválidos nas células a Valide a célula F2 permitindo a data com os seguintes critérios Data de início 01052018 Data de término 31102018 Utilize o alerta de erro Estilo Parar inserindo o Título Data inválida e a Mensagem de erro Digite uma data entre 01052018 e 31102018 b Valide a célula G2 permitindo a lista com os seguintes critérios Vendedores Ana Maria Antônio Caetano João Júlio Cesar Maria Sandra Viviane Utilize a mensagem de entrada com título Vendedor e a Mensagem Selecione o nome do vendedor Atividade Síntese 5 2018 Todos os direitos reservados Senac Passo 4 Quantidade de Pedidos Utilize uma função para contar as células que contêm a quantidade de pedidos da coluna de registros no banco de dados e que atenda aos critérios especificados na validação Passo 5 Valor Total de Vendas Utilize uma função para calcular a coluna de registros Valor Total no banco de dados que atenda aos critérios especificados na validação Atividade Síntese 6 2018 Todos os direitos reservados Senac ETAPA 3 Estoque Passo 1 Filtro Avançado Crie cinco novas planilhas no arquivo uma para cada um dos Filtros Avançados de A até E a seguir a Título que inicie com a letra C e Valor Unitário menor que 60 b Valor Unitário igual a 33 ou igual a 55 c Categoria igual a Moda d Autor que inicie com a letra P ou Quantidade menor que 10 ou Valor Unitário menor que 20 e Categoria igual a Comunicação e Artes e Valor Unitário maior que 30 e Quantidade maior que 5 Passo 2 Formulário Com base na imagem a seguir crie um formulário avançado com os seguintes itens a Aplique uma cor de fundo ao formulário b Insira botões de formulários com os nomes Livros Autor Quantidade Valor Unitário e Valor Total Atividade Síntese 7 2018 Todos os direitos reservados Senac c Utilize caixa de combinação para permitir a seleção de um livro da lista d Utilize função de pesquisa e procura para localizar o Autor a Quantidade e o Valor Unitário Atenção Quando um usuário selecionar o livro automaticamente deverá retornar o autor a quantidade e o valor unitário do livro selecionado e Crie uma fórmula matemática para calcular o Valor Total do livro selecionado Atividade Síntese 8 2018 Todos os direitos reservados Senac ETAPA 4 Vendas Passo 1 Análise de Dados Utilize uma ferramenta de análise de dados para analisar todos os Valores Unitários dos livros ao mesmo tempo e encontrar uma solução para que o Valor Total dos livros chegue ao valor de R 640000 Salve os resultados encontrados Atividade Síntese 9 2018 Todos os direitos reservados Senac ETAPA 5 Folha de Pagamento Passo 1 Nome do Funcionário Utilize função de pesquisa e referência e pesquise o nome de cada funcionário na planilha TABELAS Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 2 Saláriobase Utilize função de pesquisa e referência e pesquise o saláriobase de cada funcionário na planilha TABELAS Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 3 Comissão Utilize função lógica para verificar Se a Quantidade de Vendas for maior ou igual a 10 o vendedor receberá 15 sobre o Saláriobase caso contrário não receberá nada Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 4 INSS Utilize função lógica e de pesquisa e referência para verificar Se o Saláriobase for maior ou igual a R 200000 pagará R 39000 caso contrário pesquise na tabela INSS e o valor encontrado multiplique pelo Saláriobase Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 5 Saláriofamília Utilize uma fórmula matemática para calcular o Saláriofamília com endereços de célula em vez de valores digitados na sintaxe Atividade Síntese 10 2018 Todos os direitos reservados Senac Passo 6 Valetransporte Utilize uma função lógica para calcular o Valetransporte de modo que Se o funcionário optou por Sim na coluna referente à opção de Vale na planilha Folha de Pagamento multiplicar o percentual de desconto do Vale na planilha TABELAS pelo Saláriobase caso contrário manter o desconto como zero Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 7 Valerefeição Utilize uma função lógica para calcular o Valerefeição de modo que Se o funcionário optou por Sim na coluna referente à opção de Vale na planilha Folha de Pagamento multiplicar o percentual de desconto do Vale na planilha TABELAS pelo Saláriobase caso contrário manter o desconto como zero Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 8 Imposto de Renda IR Utilize uma função lógica para calcular o IR de modo que Se o Saláriobase do funcionário atende à primeira condição de IR na planilha TABELAS descontar o percentual correspondente a essa primeira condição do Saláriobase caso contrário descontar do Saláriobase o percentual correspondente à segunda condição de IR na planilha TABELAS Utilize endereços de célula em vez de valores digitados na sintaxe da função Passo 9 Salário Líquido a Calcule o salário líquido utilizando uma fórmula matemática b Gerencie uma regra de formatação condicional preenchendo as células que contêm valores acima de R 130000 na cor vermelha Após finalizar as etapas você já pode postar a atividade Bom Estudo