Baixe o app para aproveitar ainda mais
Prévia do material em texto
Trainning Education Services ® - Sede: Av. Paulista, 2006, 16° andar – SP São Paulo (11) 3171-2002 - Rio de Janeiro (21) 4063-6518 - Brasília (61) 4063-7766 - Belo Horizonte (31) 4063- 8187 Vitória (27) 4062-9192 - Curitiba (41) 4063-8083 - Porto Alegre (51) 4063-9474 - Recife (81) 3092-6500 - Salvador (71) 4062-9323 – Manaus (92) 3642-1539 – Cuiabá (65) 4052-9232 – Fortaleza (85) 4062-9171 – Goiânia (62) 3942-5919 – Natal (84) 3201-4110 www.trainning.com.br - comercial@trainning.com.br Sumário Funções: Estatística. Texto. Data e Hora. Lógica. Pesquisa e Auditoria de Fórmulas. Informações. Assistente de Soma Condicional. Financeiras: Fórmulas e Funções Financeiras. Cálculos Financeiros – Utilizando-se de Funções. Importação de Dados para o Excel: Importar arquivos de texto para o Excel. Importar arquivos de dados para o Excel. Criar consulta à base de dados. Vincular dados do Excel no Access. Consultar para obter dados de uma página da Web. Filtro, Classificação e Filtro Avançado: Aplicar o AutoFiltro. Aplicar o filtro a mais de um campo. Classificar com o AutoFiltro. Classificação Avançada. Classificação por lista personalizada. Classificação por cores. Filtro avançado. Filtro avançado com critérios múltiplos. 2 Formatos Condicionais Avançados: Formatos Condicionais. Funções de Banco de Dados: Utilizando as funções BDSOMA e BDMÉDIA. o Função BDSOMA. o Função BDMÉDIA. Utilizando as funções BDMÁX e BDMÍN. o Função BDMÁX. o Função BDMÍN. Utilizando a função BDEXTRAIR. Utilizando a função BDCONTARA. Tabela Dinâmica: Atribuindo Campos. Removendo Campos de uma Tabela Dinâmica. Atualizando informações em uma Tabela Dinâmica. Mudando o layout de uma Tabela Dinâmica. Formatando Tabelas Dinâmica. Ocultando ou Mostrando Itens em um Campo. Agrupando Dados da Tabela Dinâmica. Ocultando e Mostrando Detalhes. Campos Calculados. Criando Nossos Campos Calculados. Modificando ou Excluindo Campos Calculados. Criando Gráficos Dinâmicos. Funções de Banco de Dados: Banco de Dados: o Função BDSOMA. o Função BDMÉDIA. o Função BDMÁX. o Função BDMÍN. o Função BDCONTARA. 3 Criando e Gerenciando Cenários: Exibindo Cenários. Editando Cenários. Excluindo Cenários. Criando um Resumo dos Cenários. Atalho para Cenários. Atingir Meta: Como efetuar o Atingir Meta. Solver: Salvando como um Cenário os Valores Variáveis do Solver. Criando um Relatório no Solver. Auditoria: Como efetuar a Auditoria. Modos de Exibição: Configurando e Criando Modos de Exibição. Criando Formulários Personalizados: Criar planilhas combinando controles para automatização ds planilhas. Combinar esses controles com macros e outros recursos. Objetos no VBA: Acessando o VBA. Projeto – VBAProject. Propriedades. Módulos. Objetos. Métodos e Propriedades. Métodos. Localizando Procedimentos. Variáveis de Memória. Adicionando Comentários ao Código. Como Definir Variáveis. 4 Declarando Variáveis de Memória. Tipos de Dados. Escopo das Variáveis. Criando Funções: Função NovaData. Função Valor Imposto. Função Premio. Interligando Sub-Rotina com Function. 5 Microsof Excel 2013 Avançado Carga Horária: 24 horas. Disponível em Português. Objetivos: Este curso destina-se a usuários que já utilizam o Microsoft Excel e que necessitam automatizar tarefas, tais como Validação de Dados, Exibição de Cenários, Trabalhar com mais Funções e principalmente com Banco de Dados. Aborda ainda o desenvolvimento de Aplicações e Automatização de Tarefas através de Macros. Traz uma breve introdução à Linguagem de Programação VBA (Visual Basic for Application). Pré-requistos: Ter participado do Curso Microsoft Excel Básico ou ter conhecimentos específicos. Funções Estatísticas do Excel Visão Geral O Excel e as estatísticas – noções básicas Use o Excel para gerenciar seus dados estatísticos. Vamos começar pelo começo: criar uma fórmula no Excel usando funções estatísticas é tão fácil quanto usar qualquer outra função. Se você souber como usar as funções do Excel e conhecer um pouco de estatística, estará pronto para prosseguir. Usar o Excel para fazer cálculos estatísticos diários pode economizar bastante tempo e trabalho. Esta lição explicará as noções básicas das razões de usar o Excel e apresentará uma revisão rápida de como criar fórmulas no Excel. 6 Observação - Não confunda funções com fórmulas: uma função é apenas o nome, como, por exemplo, DESVPAD ou TENDÊNCIA. Uma fórmula é uma função em ação, com argumentos incluídos, como, por exemplo, =MÉDIA(A1:A33). Por que o Excel? Use as funções estatísticas do Excel no escritório, em sala de aula e na fábrica. As aplicações práticas de estatística no Excel são variadas. Talvez você tenha os seus próprios motivos para querer usar funções estatísticas no Excel. Veja alguns exemplos para aguçar a sua curiosidade: Um gerente de vendas pode querer projetar as vendas do próximo trimestre (função TENDÊNCIA). Um professor pode querer atribuir graduações em uma curva com base na média das notas (MÉDIA ou MED, ou até MODO). Um fabricante responsável pela qualidade do produto pode estar interessado na quantidade de itens que está dentro dos limites de qualidade aceitáveis (DESVPADP ou VAR). Um pesquisador pode precisar saber quantas respostas dadas em uma pesquisa ocorrem em um intervalo de respostas específico (FREQUÊNCIA). 7 Variância em ação Um gerente de vendas usa a função VAR para analisar as estimativas de vendas de três vendedores. Suponha que um gerente de vendas esteja analisando os totais de vendas de três vendedores diferentes para comparar os seus desempenhos. Uma das várias funções estatísticas que o gerente poderia usar é a variância (VAR). A variância mede a diferença existente entre os valores individuais dos dados. Dados com variância baixa contêm valores que são idênticos ou semelhantes: 6, 7, 6, 6, 7. Dados com variância alta contêm valores que não são semelhantes: 598, 1, 134, 5, 92. Você poderá tentar a função de variância na sessão de exercícios. Como construir uma fórmula estatística Uma fórmula usada para descobrir a variância com base em um intervalo; nesse caso, da célula B8 à B75. 8 Se você pode usar qualquer função do Excel, pode usar uma função estatística. Elas são criadas da mesma forma: 1. Sempre comece com um sinal de igual (=). 2. Depois o nome da função. 3. E por último os argumentos entre parênteses. Em fórmulas estatísticas, os argumentos costumam ser um intervalo, como você pode observar na imagem acima. Quando o Excel não é suficiente Há situações em que o Excel não é tão eficiente — por exemplo, se você costuma fazer pesquisa em laboratório. Se você estiver fazendo análises estatísticas pesadas, é possível que o uso do Excel não seja suficiente para atender às suas necessidades. Por exemplo, se você deseja fazer análise de regressão, o Excel requer que os valores x estejam em um bloco único (colunas ou linhas adjacentes), o que talvez não seja conveniente para você. Nesses casos, convém usar um pacote de estatísticas dedicado que aborde uma gama de opções de análise estatística e de funções relacionadas. Alguns pacotes também exibem resultados adicionais associados a uma análise específica. Usuários típicos desses tipos de programas são pessoas que trabalham em departamentos estatísticos de instituições acadêmicas ou em laboratórios de pesquisa. Muitos desses programas de análise estatística estão à venda no mercado. Além disso, há suplementos do Excel criados por terceiros para lidar com situações especiais. 9 Alguns deles podem ser encontradosna seção Office Marketplace do site Microsoft Office Online. Crie fórmulas eficientes Para obter êxito no Excel, você precisa saber como criar fórmulas eficientes. Se o Excel é tão bom para fazer estatísticas, por que nem todas as pessoas o utilizam? Bem, uma razão é que às vezes é difícil saber o que há lá. A outra razão é que as pessoas podem obter erros ao tentar encontrar os resultados desejados. Esta lição aborda alguns dos problemas que você pode encontrar ao criar fórmulas no Excel e como evitá-los. Qual é o problema? Existem várias mensagens de erro diferentes que poderão ser exibidas se você criar fórmulas incorretamente. 10 A construção de fórmulas eficientes no Excel geralmente faz com que você aumente o seu conhecimento no uso de funções e evite cometer erros comuns. O importante é usar a função certa e saber como criar uma fórmula adequada. O uso de uma fórmula criada incorretamente, ou da função errada, geralmente resulta em uma resposta incorreta. Esses problemas são encontrados por qualquer pessoa que trabalha no Excel, não apenas por aquelas que usam funções estatísticas. Para obter noções básicas sobre como criar fórmulas no Excel, consulte o curso "Encontrar funções e inserir argumentos". Como resolver o problema Vá a guia Fórmulas, grupo Biblioteca de Funções e, em seguida, clique em Inserir Função. A caixa de diálogo Inserir Função Como você pode observar na imagem abaixo, é possível escolher o tipo de função desejada e depois escolher uma função específica na lista. Também é possível obter uma descrição da função, bem como solicitar ajuda sobre ela. 11 Observação - Se você não conseguir encontrar a função desejada em uma determinada categoria, tente uma categoria relacionada. Por exemplo, observe que a função de média (MÉDIA) está listada na categoria Estatística, não em Matemática e Trigonometria, mas a função de número aleatório (ALEATÓRIO) está listada em Matemática e Trigonometria. Se você estiver em dúvida, consulte o link da ajuda para obter maiores informações. Tópicos da Ajuda do Excel e a caixa de diálogo Inserir Função podem ajudá-lo a aprender a criar fórmulas eficientes. 12 A importância da ortografia A área abaixo da curva até a letra z mostra a probabilidade de obter z. Esses valores também estão disponíveis em tabelas estatísticas. Agora observe a Distribuição Normal, que é uma curva simétrica, suave, cuja forma lembra um sino. Se você medir a altura de uma amostra de plantas que crescem sob as mesmas condições, a distribuição das alturas se aproximaria à curva Normal. Há duas funções no Excel para a Distribuição Normal: DIST.NORM e DIST.NORMP. Se você errar a ortografia do nome da função (um erro bastante comum), obterá uma resposta incorreta ou nenhuma resposta. Na sessão prática, você usará a função DIST.NORMP, que calcula probabilidades associadas à Distribuição Normal Padrão. A curva de Distribuição Normal Padrão está centralizada em 0 (a média). A área abaixo da curva é 1. A probabilidade está espalhada de forma consistente com desvio padrão da Distribuição Normal Padrão de valor 1. Isso significa que cerca de 95% da probabilidade ocorre entre -2 e 2. Se você quiser saber o grau de probabilidade à esquerda de z na curva, a fórmula seria =DIST.NORMP(z). Você pode usar essa função, em vez de uma tabela de probabilidade de Distribuição Normal Padrão. 13 Problemas mais comuns Alguns erros comuns que ocorrem em planilhas. Veja alguns dos erros mais comuns ao construir fórmulas Esquecer o sinal de igual (=) no início da fórmula. Inserir um espaço antes do sinal de igual (=). Inserir dados no formato errado (por exemplo, como texto em vez de como números). Selecionar o intervalo de dados errado. Esses problemas podem ser facilmente evitados. Se, no entanto, você tiver inserido algum dado errado na fórmula, o Excel poderá retornar um resultado incorreto, como #VALOR!, conforme exibido na figura à esquerda. Uma lista completa de tipos de erros e soluções de erros está disponível nos tópicos da Ajuda. Qual função usar? 14 Você é um especialista em Excel, mas ainda se confunde com algumas funções estatísticas? Você já é um perito em criar fórmulas no Excel. Já sabe como começar do zero com o sinal de igual fundamental e também como solucionar problemas de algumas mensagens de erro recebidas. O que mais poderia causar problemas? Ainda é possível obter resultados incorretos? Escolher a função certa a ser usada nem sempre é tão fácil quanto parece. Talvez os seus conhecimentos de estatística estejam um pouco enferrujados ou talvez você não tenha percebido que havia tantas funções relacionadas disponíveis. Você ainda obtém a resposta errada? Há quatro funções de variância relacionadas disponíveis no Excel. Use a caixa de diálogo Inserir Função para encontrar mais informações sobre cada uma delas. Se você não souber qual função usar, o Excel poderá contribuir com vastos tópicos de ajuda. Você precisa ter noções de estatística suficientes para poder analisar seus dados. Mesmo quando você acha que sabe qual função estatística usar, nem tudo é tão simples assim. Há muitas funções no Excel com nomes semelhantes e que são relacionados entre si. Talvez você tenha escolhido uma função com atribuições diferentes da desejava. Para saber se você fez a escolha certa: Verifique a finalidade de cada função individual na caixa de diálogo Inserir Função e use o link para os tópicos daAjuda. 15 Qual função? Uma amostra da população (à esquerda) comparada a uma população inteira (à direita). Outro exemplo de função estatística que tem mais de uma opção a escolher no Excel é o desvio padrão. (Você usará essa opção na sessão prática.) As funções disponíveis no Excel são: DESVPAD. DESVPADA. DESVPADP. DESVPADPA. A diferença entre essas funções deve-se parcialmente às estatísticas e à forma como o Excel as calcula. DESVPADP e DESVPADPA usam populações inteiras, enquanto DESVPADP e DESVPADPA funcionam em amostras de populações. Essa é uma diferença estatística. DESVPADA e DESVPADPA reconhecerão texto VERDADEIRO e FALSO (ou valores lógicos), bem como numerais. DESVPAD and DESVPADP reconhecerão apenas valores numéricos. A função escolhida dependerá dos seus dados. Você só tem valores numéricos? Você tem dados para uma população inteira ou apenas para uma amostra? 16 Melhores resultados no Excel 2013 A função de variância é mais precisa para números muito altos no Excel 2013. Algumas funções fornecerão resultados diferentes no Excel 2013, em comparação com versões anteriores do Excel, pois várias funções foram aprimoradas. Um procedimento de duas etapas agora é usado, aumentando a precisão dos resultados. Não entre em pânico. As diferenças nos resultados devem ser observadas principalmente em casos extremos e raros. “Estatística” em “Fórmulas/Mais Funções” Vá a guia Fórmulas, grupo Biblioteca de Funções e, em seguida, clique em Mais Funções, Estatística desta forma você terá acesso a lista das funções desta categoria. Utilizando Funções Estatísticas Propósito: Utilizar as Funções Estatísticas do MS Excel 2013. 17 Meta: Abra a pasta de trabalho “Prêmio.xlsx”. Calcular a maior e a menor venda entre os vendedores. Calcular a média de vendas dos vendedores. Calcular o número de vendedores que não efetuaram vendas. Calcular o número de vendedores que efetuaram vendas. Calcular o número de vendedores premiados com o primeiro lugar em vendas. Calcular o total de vendedores por departamento. Calcular o número de vendedores premiados com o 1º lugar em vendas por departamento. Calcular a média de vendas por departamento. Calcular a média de vendas pelos vendedores não premiadoscom o primeiro lugar por departamento. Uma loja possui três departamentos de vendas: Informática, Eletrodomésticos e Livraria. Cada departamento tem a sua equipe de vendas. Com a finalidade de motivar os vendedores, a loja resolveu distribuir um prêmio no valor de R$ 450.000,00 para o(s) vendedor(es) que ficou(aram) em 1º lugar no volume de vendas. O objetivo é efetuar os seguintes controles: Exibir o ranking de vendas, ou seja, calcular a colocação de cada um levando em consideração a venda individual em relação ao conjunto de vendas dos vendedores; Exibir a maior e a menor venda efetuada considerando todos os departamentos; Calcular a média de vendas considerando todos os departamentos; Exibir o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o número de vendedores que efetuaram vendas e quantos ficaram em 1º lugar em vendas; Por departamento, calcular o número de vendedores, quantos ficaram em 1º lugar em vendas, calcular a média de vendas e a média de vendas dos vendedores não ganhadores da gratificação. Para resolver essa situação você vai trabalhar com a pasta de trabalho Gratificação.xlsx. Nessa pasta você encontrará duas planilhas: VENDAS e RELATÓRIO. 18 Função MÁXIMO - Essa função retorna o valor máximo entre um conjunto de valores numéricos informado. Sintaxe: MÁXIMO(núm1;núm2;...) Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos cujo valor máximo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 a 255 valores numéricos. 19 1. Abra a pasta de trabalho Prêmio.xlsx. 2. Selecione a planilha RELATÓRIO e clique na célula B2. Nessa célula você deve calcular o valor máximo das vendas que estão na faixa C3:C22 da planilha VENDAS. 3. Digite =MÁXIMO(. 4. Selecione a planilha VENDAS e, com o mouse selecione a faixa C3:C22. 5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÁXIMO(VENDAS!C3:C22). Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a fórmula está sendo colocada na planilha RELATÓRIO, é colocado o nome da planilha seguido pelo ponto de exclamação na identificação da região informada. Outra forma de selecionar a função MÁXIMO é através da ferramenta que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de igual (=) antes de selecionar a função. Função MÍNIMO – Essa função retorna o valor mínimo entre um conjunto de valores numéricos informado. Sintaxe: MÍNIMO(núm1;núm2;...) Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos cujo valor mínimo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 a 255 valores numéricos. 1. Selecione a planilha RELATÓRIO e clique na célula B3. Nessa célula você vai calcular o valor mínimo das vendas que estão na faixa C3:C22 da planilha VENDAS. 2. Digite =MÍNIMO(. 3. Selecione a planilha VENDAS e, com o mouse selecione a faixa C3:C22. 4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÍNIMO(C3:C22). Outra forma de selecionar a função MÍNIMO é através da ferramenta que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de igual (=) antes de selecionar a função. 20 Função MÉDIA – Essa função retorna a média aritmética entre um conjunto de valores numéricos informado. Sintaxe: MÉDIA(núm1;núm2;...) Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos para os quais desejamos calcular a média aritmética. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 a 255 valores numéricos. 1. Selecione a planilha RELATÓRIO e clique na célula B4. Nessa célula você vai calcular a média aritmética das vendas que estão na faixa de C3:C22 da planilha VENDAS. 2. Digite =MÉDIA(. 3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÉDIA(VENDAS!C3:C22). Outra forma de selecionar a função MÉDIA é através da ferramenta que se encontra na guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal de igual (=) antes de selecionar a função. Função ORDEM.EQ – Essa função retorna a posição de um número em uma lista de números. A presença de números com a mesma posição irá interferir na ordem dos números subsequentes. Por exemplo, em uma lista de números inteiros classificados em ordem crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3. Sintaxe: ORDEM.EQ(número; referência; [ordem]) Argumentos: o Número: número cuja posição se quer encontar em uma lista de números; o Referência: lista de números onde o número informado será posicionado; o Ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionará o número considerando a lista de números ordenada de forma descendente; se for qualquer valor diferente de zero, o Excel posicionará o número considerando a lista de números ordenada de forma ascendente. 21 1. Selecione a planilha VENDASe clique na célula D3. 2. Você vai urilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3 dentro da faixa de vendas C3:C22. 3. Digite a fórmula =ORDEM.EQ(C3;$C$3:$C$22). 4. Está fórmula deve sofrer uma mudança para =SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"") para evitar ocorrência de erro durante o retorno da posição do número na lista de números. Observe que a faixa de células C3:C22 foi fixada na fórmula, pois na cópia para as células de baixo essa faixa deve continuar sendo a mesma. 5. Pressione a tecla ENTER e copie a fórmula até a célula D22. Não se preocupe com as informações de erro que aparecem nas células D8 e D17. Esse erro é devido a não existir um valor de venda nas células C8 e C17. Mais adiante, nesse mesmo capítulo, esse erro será tratado de forma que não apareça a mensagem. Função CONT.VALORES – Essa função conta o número de células não vazias em um intervalo informado. O conjunto de células pode conter qualquer tipo de informação. Sintaxe: CONT.VALORES(intervalo1;[intervalo2]; ...) Argumentos: o Intervalo1;[intervalo2];...: faixas de células que devem ser contadas; 6. Você vai contar o número total de vendedores da loja. Para isso conte quantas células contém os nomes dos vendedores. 7. Selecione a planilha RELATÓRIO e clique na célula B7. 8. Digite =CONT.VALORES(. 9. Como os nomes dos vendedores estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células A3:A22. 10. Pressione a tecla ENTER e você terá a fórmula: =CONT.VALORES(VENDAS!A3:A22). Função CONTAR.VAZIO – Essa função conta o número de células vazias em um intervalo. Células que contenham valor nulo não são contadas. Sintaxe: CONTAR.VAZIO(intervalo) Argumentos: o argumento intervalo representa a faixa de células que devem ser contadas. 22 1. Agora você vai contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da coluna VALOR DE VENDA que estão vazias. 2. Selecione a planilha RELATÓRIO e clique na célula B8. 3. Digite =CONTAR.VAZIO(. 4. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22. 5. Pressione a tecla ENTER e você terá a fórmula: =CONTAR.VAZIO(VENDAS!C3:C22). Função CONT.NÚM – Essa função conta o número de células que contêm números em um intervalo informado. Serão contadas as células que contenham números, datas ou escritos entre aspas. Sintaxe: CONT.NÚM(intervalo1;[intervalo2]; ...) Argumentos: o Intervalo1;[intervalo2];...:faixas de células que devem ser contadas; 1. Agora você vai contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna VALOR DE VENDA que estão preenchidas com números. 2. Selecione a planilha RELATÓRIO e clique na célula B9. 3. Digite =CONT.NÚM(. 4. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22. 5. Pressione a tecla ENTER e você terá a fórmula: =CONT.NÚM(VENDAS!C3:C2) Função CONT.SE – Essa função conta o número de ocorrências de uma determinada condição em um intervalo de células informado. Sintaxe: CONT.SE(intervalo; critério) Argumentos: o Intervalo1: intervalo de células que será considerado; o Critério: condição que será procurada no intervalo de células; 1. Você vai contar quantos são os vendedores ganhadores da gratificação, ou seja, aqueles que se encontram em 1º lugar na colocação de vendas. 2. Selecione a planilha RELATÓRIO e clique na célula B10. 3. Digite a fórmula: =CONT.SE(. 23 4. Como você quer verificar se o vendedor está na classificação de 1º, o intervalo de células considerado deve ser o da coluna CLASSIFICAÇÃO da planilha VENDAS. 5. Selecione a planilha VENDAS e, com o mouse clicado, selecione o intervalo D3:D22. 6. Digite ; (ponto-e-vírgula). 7. Digite 1. 8. Pressione a tecla ENTER e você terá a seguinte fórmula: =CONT.SE(VENDAS!D3:D22;1). 9. Agora você vai contar quantos vendedores são do departamento de Informática no intervalo de células B3:B22 da planilha VENDAS. Clique na célula E2 da planilha RELATÓRIO. 10. Digite a fórmula: =CONT.SE(VENDAS!B3:B22;”Informática”). Como a palavra Informática, que é o critério para a função, está na célula D1 da planilha RELATÓRIO, a fórmula também poderia ser escrita da seguinte forma: =CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1). Função CONT.SES – Essa função aplica critérios em vários intervalos e conta o número de vezes que todos os critérios são verdadeiros. Sintaxe: CONT.SES(intervalo1;critério1;intervalo2;critério;...) Argumentos: o Intervalo1: intervalo de células onde será procurado o critério 1; o Critério1: condição que será procurada no intervalo de células intervalo1; o Intervalo2: intervalo de células onde será procurado o critério2; o Critério2: condição que será procurada no intervalo de células intervalo2. Observação: A função CONT.SES pode ter até 127 pares de intervalos/critérios. 1. Você vai calcular quantos são osvendedores ganhadores da gratificação do departamento de Informática. Veja que agora você precisa considerar duas condições: o vendedor está classificado como 1º e trabalha no departamento de Informática. 2. Selecione a planilha RELATÓRIO eclique na célula E3. 3. Digite a fórmula: =CONT.SES(. 4. Você deve procurar pela palavra “Informática”. Selecione a planilha VENDAS e selecione o intervalo B3:B2. Digite ; (ponto-e-vírgula) e, na planilha RELATÓRIO, clique na célula D1. Digite ; (ponto-e-vírgula). 5. Agora você vai procurar pelo número 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite ; (ponto-e-vírgula) e digite 1. 24 6. Pressione a tecla ENTER e teremos a fórmula: =CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1). Função MÉDIASE – Tem como objetivo calcular a média aritmética dos valores indicados de acordo com um determinado critério ou condição. Sintaxe: MÉDIASE(intervalo; critérios; intervalo_média) Argumentos: o Intervalo: intervalo de células onde o critério será procurado; o Critério: condição para definir quais valores serão considerados para o cálculo da média; o Intervalo_média: intervalo de células que será considerado para calcular a média. 1. Calcule agora a média de vendas para o departamento de Informática. Selecione a célula E4 da planilha RELATÓRIO e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito para as fórmulas anteriores, para construir a expressão: =MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22). Função MÉDIASES – Tem como objetivo calcular a média aritmética em um intervalo atendendo a vários critérios. Sintaxe: MÉDIASES(intervalo_média_critério1;critério1;intervalo_critério2;critério2;...) Argumentos: o Intervalo_média: intervalo de valores que serão considerados para o cálculo da média. o Intervalo_critério1: intervalo de células onde o critério1 será procurado; o Critério1: condição para definir quais valores serão considerados para o cálculo da média; o Intervalo_critério2: intervalo de células onde o critério2 será procurado; o Critério2: condição para definir quais valores serão considerados para o cálculo da média. 25 1. Calcular a média aritmética das vendas dos vendedores do departamento de Informática e que não são ganhadores da Gratificação. Na célula E5 da planilha RELATÓRIO digite a fórmula: =MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;” <>1”). Os critérios informados nessa fórmula são os seguintes: VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da média; VENDAS!B3:B22 – intervalo onde será procutado o primeiro critério (Departamento); RELATÓRIO!D1 – endereço da célula que contém o primeiro critério (Informática); VENDAS!D3:D22 – intervalo onde será procurado o segundo critério (Classificação); 2. Preencha, utilizando o mesmo raciocínio utilizado para as informações do departamento de Informática, os quadros relativos aos departamentos de Eletrodomésticos e Livraria da planilha RELATÓRIOS. 3. No final você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO. 26 Funções de TEXTO As funções de texto do Excel são ferramentas bastante úteis no tratamento das informações. Em muitos casos essas informações são provenientes de outros bancos de dados e nem sempre são exibidas da forma mais adequada. Através da utilização das funções de texto do Excel esses problemas são resolvidos obtendo a visualização correta dos dados. 27 Clique na guia Fórmulas, no grupo Biblioteca de Funções, comando Texto. Utilizando-se das funções de TEXTO – ARRUMAR, DIREITA, ESQUERDA, EXT.TEXTO, MAIÚSCULA, MINÚSCULA e PRI.MAIÚSUCULA. Função ARRUMAR Situação comum, principalmente para quem trabalha com importação de dados que vêm de outros sistemas é ter que fazer uma limpeza destes. Dentre as limpezas que são necessárias, algumas já comentei neste blog, outras vou comentar e uma bem frequente é a retirada de espaços em branco em textos de células. Para isso, o Excel disponibiliza a função ARRUMAR. Segundo a própria Microsoft: Remove todos os espaços do texto exceto os espaços únicos entre palavras. Use ARRUMAR no texto que recebeu de outro aplicativo que pode ter espaçamento irregular. A função ARRUMAR foi desenvolvida para aparar o caractere de espaço ASCII de 7 bits (valor 32) do texto. No conjunto de caracteres Unicode, há um caractere de espaço adicional denominado caractere de espaço incondicional, o qual possui um valor decimal de 160. Esse caractere é comumente utilizado em páginas da Web como entidade HTML, . Por si própria, a função ARRUMAR não remove o caractere de espaço incondicional. A sintaxe da função ARRUMAR tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento). Sintaxe ARRUMAR(texto) A sintaxe da função ARRUMAR tem os seguintes argumentos: Texto – Obrigatório. O texto do qual se deseja remover espaços. Exemplo: 28 É muito comum ao receber arquivos de outrem e no momento de fazer uma comparação com algum valor nele existente, algo estranho acontece. Por exemplo, a imagem abaixo: Comparação inconsistente Reparem na fórmula que é colocada. Porque o resultadonão é o esperado? O fato é que há espaços em branco depois do nome “Brasil” em A1, o que faz a comparação ser inválida. É o tipo de erro que é bastante complicado de identificar. A correção abaixo, que acrescente a função ARRUMAR resolve o problema: Comparação usando a função ARRUMAR Por isso, quando se trabalha com campos de texto e tratamento sobre estes, recomenda- se limpar os dados com a função ARRUMAR para ter certeza que não haverão grandes problemas mais adiante. Utilizando a função ARRUMAR Propósito: Utilizar a função ARRUMAR. Meta: Abrir a pasta de trabalho Rodízio.xlsx. Eliminar os espaços em branco no início das células da coluna MARCA da planilha Dados. 29 Desde 1997 está em vigor, na cidade de São Paulo – SP, o rodízio municipal de veículos, com o propósito de melhorar as condições ambientais e também de reduzir os congestionamentos nas principais vias da cidade nos horários de maior movimento. Os automóveis e caminhões não podem circular em uma determinada região, denominada Centro Expandido, nos períodos de 07:00 – 10:00 horas e 17:00 – 20:00 horas todos os dias, de segunda a sexta-feira, seguindo uma tabela de acordo com o final da placa do veículo. Assim: Veículos com finais de placa 1 e 2 Não circulam às segundas-feiras Veículos com finais de placa 3 e 4 Não circulam às terças-feiras Veículos com finais de placa 5 e 6 Não circulam às quartas-feiras Veículos com finais de placa 7 e 8 Não circulam às quintas-feiras Veículos com finais de placa 9 e 0 Não circulam às sextas-feiras Recebmeos uma pasta contendo os dados de vários veículos e devemos, então, verificar na data corrente quais veículos podem circular livremente e quais devem obedecer ao rodízio. Além disso, os dados chegaram de forma não muito adequada, com espaços em branco indevidos no início dos campos, letras minúsculas ao invés de maiúsculas e outros acertos que devem ser feitos. Para essa tarefa vamos utilizar as funções de texto do Excel, além de funções já estudadas, como SE e PROCV. Função ARRUMAR – Essa função tem como objetivo eliminar os espaços em branco à esquerda e a à direita do texto. Sintaxe: ARRUMAR(texto). Argumentos: texto: texto do qual se deseja remover espaços à direita ou à esquerda. 1. Abra a pasta de trabalho Rodízio.xlsx. 2. Selecione a planilha DADOS. 30 3. Você vai arrumar a coluna referente a MARCA para eliminar os espaços em branco no início de cada célula. Esse ajuste será efetuado na planilha INTERMEDIÁRIA. Selecione a planilha INTERMEDIÁRIA e clique na célula A2. 4. O texto onde se deseja eliminar os espaços indevidos está na célula A2 da planilha DADOS. Portanto, digite a seguinte a seguinte fórmula: =ARRUMAR(DADOS!A2). 5. Copia a fórmula até a célula A18. 31 Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA Propósito: Utilizar as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA. Meta: Converter para letras maiúsculas os conteúdos das células referentes à coluna PLACA da planilha DADOS. Converter para letras minúsculas os conteúdos das células referentes à coluna COMENTÁRIOS da planilha DADOS. Função MAIÚSCULA – Essa função tem como objetivo converter o texto para caracteres maiúsculos. Sintaxe: MAIÚSCULA(texto). Argumentos: texto: texto que se deseja converter para maiúsculas. Função MINÚSCULA – Essa função tem como objetivo converter o texto para caracteres minúsculos. Sintaxe: MINÚSCULA(texto). Argumentos: texto: texto que se deseja converter para minúsculas. Função PRI.MINÚSCULA – Essa função tem como objetivo colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia de texto. 32 Sintaxe: PRI.MINÚSCULA(texto). Argumentos: texto: texto que se deseja converter parcialmente para maiúsculas. 1. A coluna PLACA da planilha DADOS está mostrando as placas dos automóveis com a identificação das letras em caracters minúsculos, Você vai modificar para que apareçam em letras maiúsculas. Selecione a planilha INTERMEDIÁRIA, clique na célula B2 e digite a fórmula: =MAIÚSCULA(DADOS!B2). 2. Copie a fórmula para todos os automóveis. 3. Agora você vai converter os valores da coluna COMENTÁRIOS da planilha DADOS para minúsculas. Ainda na planilha INTERMEDIÁRIA clique na célula C2 e digite a fórmula: =MINÚSCULA(DADOS!C2). 4. Copie a fórmula para todos os automóveis. 5. Os nomes dos proprietários dos automóveis estão digitados em letras minúsculas. Vamos corrigir esse erro, colocando todas as iniciais em maiúsculas. Ainda na planilha INTERMEDIÁRIA clique na célula D2 e digite a fórmula: =PRI.MAIÚSCULA(DADOS!D2). 6. Copie a fórmula para todos os proprietários. 33 Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO Propósito: Utilizar as funções ESQUERDA, DIREITA e EXT.TEXTO. Meta: Retornar as letras das placas dos automóveis. Retornar as duas últimas letras das placas dos automóveis. Retornar o último número das placas dos automóveis. Função ESQUERDA – Essa função tem como objetivo retornar os primeiros n caracteres à esquerda de uma cadeia de texto. Sintaxe: ESQUERDA(texto;[número_caracteres]). Argumentos: o texto: texto de onde se deseja extrair os caracteres à esquerda. o número_caracteres: argumento opcional que indica o número de caracteres à esquerda que se deseja extrair do texto. Se omitido, trará o primeiro caractere à esquerda. 34 Função DIREITA – Essa função tem como objetivo retornar os últimos n caracteres à direita de uma cadeia de texto. Sintaxe: DIREITA(texto;[número_caracteres]). Argumentos: o texto: texto de onde se deseja extrair os caracteres à direita. o número_caracteres: argumento opcional que indica o número de caracteres à direita que se deseja extrair do texto. Se omitido, trará o último caractere à direita. Função EXT.TEXTO – Essa função tem como objetivo retornar os n caracteres à partir de uma determinada posição em uma cadeia de texto. Sintaxe: EXT.TEXTO(texto;posição;número_caracteres). Argumentos: o texto: texto de onde se deseja extrair os caracteres. o posição: posição do primeiro caractere a ser extraído do texto. o número_caracteres: número de caracteres que devem ser extraídos a partir da posição do primeiro caractere considerado. 1. Você vai extrair as letras das placas do automóveis. Para isso, clique na célula E2 da planilha INTERMEDIÁRIA, e digite: =ESQUERDA(B2;3). 2. Copie a fórmula para todos os automóveis. 3. Agora você vai extrair as duas últimas letras das placas dos automóveis, considerando que se deseja trazer, a partir da posição 2, os dois caracteres do conteúdo das células que estão na coluna PLACA da planilha INTERMEDIÁRIA. Clique na célula F2 e digite: =EXT.TEXTO(E2;2;2). 4. Copie a fórmula para todos os automóveis. 5. Extraia o último das placas dos automóveis. Para isso, clique na célula G2 da planilha INTERMEDIÁRIA, e digite: =DIREITA(B2;1). 6. Copie a fórmula para todos os automóveis. 7. Para completar a tarefa você deve preencher a planilha RODÍZIO. Nessa planilha você vai ter a indicação dos automóveis que estão ou não na data informada. Copie somente os valores das colunas PLACA e ÚLTIMO NÚMERO DA PLACA da planilha INTERMEDIÁRIA para as colunas PLACA e FINAL DA PLACA da planilha RODÍZIO. Copie somente os valores, e não fórmulas. 8. Na planilha RODÍZIO clique na célula B2 e digite a fórmula para mostrar o dia de hoje: =HOJE(). 35 9. Você vai colocar o nome do dia da semana correspondente à data de hoje na célula D2. Para isso você precisa procurar pelo número do dia da semana na tabela Dia da Semana através das funções DIA.DA.SEMANA e PROCV. Clique na célula D2 e digite: =PROCV(DIA.DA.SEMANA(B2;2);L3:M9;2). 10. Preencha a coluna DIA DO RODÍZIO com o dia dasemana encontrado na tabela Rodízio a partir do final da placa do automóvel. Clique na célula C5 e digite a fórmula: =PROCV(B5;$I$3:$J$8;2). 11. Copie a fórmula para todos os automóveis. 12. Agora você vai preencher a coluna MENSAGEM informando se o veículo está ou não no rodízio na data considerada. Clique na célula D5 e digite a fórmula: =SE(C5=$D$2;”Carro no rodízio”;”Livre para circular”). 13. Formate condicionalmente essa coluna para que mostre a mensagem “Carro no rodízio” em vermelho. Funções de DATA e HORA As funções de data e hora do Microsoft Excel permitem que você execute cálculos de planilha rápida e precisamente. Por exemplo, se você usar sua planilha para calcular a folha de pagamento mensal de sua empresa, poderia usar a função HORA() para determinar o número de horas trabalhadas diariamente e a função DIA.DA.SEMANA() para determinar se os empregados devem receber de acordo com o salário padrão (para o período de segunda a sexta-feira) ou a uma taxa de horas extras (para sábados e domingos). Clique na guia Fórmulas, no grupo Biblioteca de Funções, comando Data e Hora. 36 Utilizando as funções de DATAS e HORAS Propósito: Formatar datas. Utilizar as funções de datas e horas. Efetuar cálculos com datas e horas. Meta: Formatar uma data para que seja exibido o dia da semana. Formatar uma data para que seja exibido o mês. Formatar uma data para que seja exibido o dia, mês e ano. Utilizar as funções de data para exibir a data de hoje, o ano, dia e mês de uma data, o número do dia dia da semana. Projetar dias úteis a partir de uma data. Calcular o número de dias úteis entre duas datas. Projetar dias corridos a partir de uma data. Calcular o número de dias corridos entre duas datas. Calcular o número de horas e minutos entre dois horários. Aplicar formatação condicional nas planilhas. Em muitas planilhas é necessário o trabalho com datas e horas, efetuando cálculos e projeções. O MS Excel 2013 oferece várias funções e opções de formatação que podem ser aplicadas para as mais variadas necessidades. Formatação de DATAS 1. Abra a pasta de trabalho Datas Formato.xlsx. 37 2. Copie o conteúdo da coluna A para as demais colunas. 3. Na coluna B você vai exibir o número serial que representa a data. Esse número é o número de dias transcorridos entre a data 01/01/1900 e a data considerada. Selecione o intervalo B2:B32. 4. Na guia Página Inicial, grupo Número, clique na seta do comando Formato de número. 38 5. Selecione Geral. 6. Na coluna C você vai mostrar o nome do dia da semana da data armazenada. Selecione o intervalo C2:C32. 7. Na guia Página Inicial, clique no grupo Número. 8. No grupo Número, selecione a categoria Personalizado. 9. Apague o que está no campo Tipo e digite dddd. 39 O que é informado nesse campo determina o formato do dado selecionado. Para o caso de datas, considerando o dia da data, temos os seguintes formatos personalizados: D Dias que são formados por um algarismo são exibidos com um algarismo. DD Dias que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial. DDD Exibe o nome do dia de forma abreviada, através das três primeiras letras. DDDD Exibe o nome do dia por extenso. 10. Clique em OK. 11. Agora você vai exibir a coluna D o nome do mês por extenso. Selecione o intervalo D2:D32. 12. Clique na seta do grupo Número da guia Página Inicial. 13. Na guia Número, selecione a categoria Personalizado. 14. Apague o que está no campo Tipo e digite mmmm. 40 Para o caso de datas, considerando o mês da data, temos os seguintes formatos personalizados: M Meses que são formados por um algarismo são exibidos com um algarismo. MM Meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial. MMM Exibe o nome do mês de forma abreviada, através das três primeiras letras. MMMM Exibe o nome do mês por extenso. 15. Clique em OK. Agora você vai mostrar a data por extenso. Por exemplo: para a data 01/01/2010, deverá ser exibido sexta-feira, 01 de janeiro de 2010. 16. No campo Tipo digite o seguinte: dddd”, “dd” de “mmmm” de “aaa. No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano e a representação aaa formata a data para que apareçam os quatro dígitos do ano. 17. Clique em OK. 41 Você deve ter em mente a diferença entre conteúdo e formato. Conteúdo é o dado armazenado na célula, e Formato é a forma como ele está sendo exibido. Quando você efetua cálculos com células formatadas, é o conteúdo que será trabalhado, e não o formato. Funções de DATA Função HOJE – A função HOJE retorna a data de hoje formatada como data. Essa função não possui argumentos. No entanto deve-se digitar os parênteses. Sintaxe: HOJE(). Função ANO – Essa função retorna o ano da data informada. O ano é retornado como um inteiro no intervalo de 1900 a 9999. Sintaxe: ANO(data_informada). Argumentos: data_informada: data que se deseja retornar o ano. 42 Função MÊS – Essa função retorna o número do mês da data informada. O mês é retornado como um inteiro no intervalo de 1 a 12. Sintaxe: MÊS(data_informada). Argumentos: data_informada: data que se deseja retornar o mês. Função DIA – Essa função retorna o dia da data informada. O dia é retornado como um inteiro no intervalo de 1 a 31. Sintaxe: DIA(data_informada). Argumentos: data_informada: data que se deseja retornar o dia. Função DIA.DA.SEMANA – Essa função retorna o número do dia da semana da data informada. O dia é retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. Sintaxe: DIA.DA.SEMANA(data_informada;tipo). Argumentos: o data_informada: data que se deseja retornar o número do dia da semana. o tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores: TIPO VALOR RETORNADO 1 ou omitido Números 1 (domingo) a 7 (sábado) 2 Números 1 (segunda-feira) a 7 (domingo) 3 Números 0 (segunda-feira) a 6 (domingo) 11 Números 1 (segunda-feira) a 7 (domingo) 12 Números 1 (terça-feira) a 7 (segunda-feira) 13 Números 1 (quarta-feira) a 7 (terça-feira) 14 Números 1 (quinta-feira) a 7 (quarta-feira) 15 Números 1 (sexta-feira) a 7 (quinta-feira) 16 Números 1 (sábado) a 7 (sexta-feira) 17 Números 1 (domingo) a 7 (sábado) 1. Abra a pasta de trabalho Datas Funções.xlsx. 43 2. Clique na célula B1. 3. Coloque nessa célula a função para mostrar o dia da data de hoje. Digite: =HOJE(). 4. Clique na célula B2. 5. Digite nessa célula a função para mostrar o dia da data de hoje. Digite: =DIA(B1). 6. Clique na célula B3. 7. Digite nessa célula a função para mostrar o número do mês da data de hoje. Digite: =MÊS(B1). 8. Clique na célula B4. 9. Digite nessa célula a função para mostrar o ano da data de hoje. =ANO(B1). 10. Clique na célula B5. 11. Digite nessa célula a função para mostrar o número do dia da semana da data de hoje, considerando 1 como domingo e 7 como sábado. Digite: =DIA.DA.SEMANA(B1). Função DIATRABALHO.INTL – Essa função retorna os dias úteis a projetar a partir de uma data informada, considerando como é calculado o fim de semana e os feriados que possam ocorrer no período. Sintaxe: DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados). Argumentos: o data_inicial: data a partir da qual se deseja projetar dias úteis. o dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor zero gera uma data inicial. 44o fim_de_semana: argumento opcional. Indica que dias da semana são considerados como pertencentes ao final de semana. Pode ter os seguintes valores: FIM_DE_SEMANA DIAS DE FIM DE SEMANA 1 ou omitido Sábado, Domingo 2 Domingo, Segunda 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas Domingo 12 Apenas Segunda-feira 13 Apenas Terça-feira 14 Apenas Quarta-feira 15 Apenas Quinta-feira 16 Apenas Sexta-feira 17 Apenas Sábado feriados: argumento opcional que é um conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis. 1. Abra a pasta de trabalho Caixa.xlsx. 2. Nela você encontra duas planilhas LANÇAMENTOS e FERIADOS. Na planilha LANÇAMENTOS você fará a projeção dos dias úteis para o mês de Abril/2010. 45 3. Clique na célula B3 e digite a data 01/04/2010. 4. Formate a célula B3 para que apareça somente o nome do mês. 5. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março. Essa data é o dia anterior da data 01/04/2010 que está armazenada na célula B3, ou seja, é o conteúdo da célula B3-1. Clique na célula A6 e digite: =DIATRABALHO.INTL(B3-1;. 6. Como se deseja obter o primeiro dia útil a partir dessa data, digite 1;. 7. Nesta atividade o final de semana é composto por sábado e domingo. Logo, o parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;. 8. Na planilha FERIADOS, na coluna A, estão os feriados do mês de abril de 2010. Selecione a planilha FERIADOS e clique sobre a indicação da coluna A. Marque a coluna inteira para evitar refazer as fórmulas caso seja inserido algum feriado não previsto. Pressione ENTER e terá a seguinte fórmula na célula: =DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A). 9. Agora você vai calcular o próximo dia útil a partir da célula A6. Clique na célula A7 e digite a seguinte fórmula: =DIATRABALHO.INTL(A6;1;1;Feriados!A:A). 10. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2010. 46 11. Feche a pasta de trabalho Caixa,xlsx. Seu trabalho neste momento é calcular o salário que deve ser pago por uma empresa aos seus estagiários. Esse controle é efetuado através da pasta de trabalho Pagamento Estagiários.xlsx. 1. Abra a pasta de trabalho Pagamento Estagiários.xlsx. 2. O primeiro passo é calcular quantos são os dias de vigência do contrato, ou seja, quantos dias corridos existem entre a data de início e a data de término do contrato. Para saber quantos dias existem entre duas datas, basta subtrair a data inicial da data final. Dessa forma, selecione a célula F4 e digite a fórmula: +D4-C4. 3. Copie a fórmula para todos os estagiários. 4. No entanto, o salário é pago considerando o número de dias a trabalhar no período, ou seja, o número de dias úteis do contrato, e não o número de dias corridos. Clique na célula G4 e digite a fórmula: =DIATRABALHO.INTL(C4;D4;1;Feriados!A:A). 47 Onde: C4 Data inicial. D4 Data final. 1 Final de semana formado por sábado e domingo. Feriados!A:A Lista de feriados no ano de 2010. 5. Copie a fórmula para todos os estagiários. 6. Agora calcule quanto cada estagiário deve receber, sabendo que o valor pago por hora está armazenado na célula C14. Clique na célula H4 e digite a fórmula: =E4*G4*$C$14. 7. Copie a fórmula para todos os estagiários. 8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi então solicitado que uma nova coluna fosse inserida na planilha informando quantos dias corridos deveriam ser acrescidos aos contratos. Sua tarefa agora é inserir essa coluna e efetuar o cálculo das novas datas de término dos contratos para que o cálculo dos salários fiquem corretos. Insira duas novas colunas na planilha antes da coluna Carga horária diária: 48 9. Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor aparecer uma data, formate os valores como números. 10. Se você tiver uma data e somar um número a essa data, você terá a data correspondente à data inicial mais o número de dias somado. Portanto, para calcular a data efetiva de término do contrato, temos que adicionar o número de dias de prorrogação do contrato à data de término do contrato. Clique na célula F4 e digite a fórmula: =D4+E4. 11. Copie a fórmula para todos os estagiários. 12. Agora é necessário recalcular os dias de vigência do contrato e os dias a trabalhar no período, pois a data final do contrato é a nova data calculada, que se encontra na coluna Data de término efetiva. Refaça as fórmulas e confira o resultado. 13. Feche a pasta de trabalho Pagamento Estagiários.xlsx. 14. Agora você vai efetuar cálculos com horas. Abra a pasta de trabalho Atividades e horas.xlsx. Nessa pasta você encontra duas planilhas: CONTROLE e FERIADOS. Na planilha CONTROLE você vai calcular quanto o funcionário deve receber em função do tempo trabalhado nos dias úteis de fevereio de 2010 e do valor que deve receber por hora. 49 15. Clique na célula B2 e digite: 01/02/2010. 16. Formate a célula para exibir o nome do mês. 17. A partir da célula A5 preencha até a célula A23 com os dias úteis de fevereiro. Utilize o mesmo raciocínio do exercício anterior. Os feriados de fevereiro estão na planilha Feriados. 18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de copiar os valores, e não as fórmulas. 19. Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana. 20. Calcule agora o tempo trabalhado em cada dia. Esse tempo é calculado subtraindo o horário de entrada do horário de saída. Para isso, clique na célula E5 e digite: =D5-C5. 21. Copie a fórmula para todo o intervalo. Função HORA – Essa função retorna a hora de um valor de tempo. A hora é retornada como um inteiro, variando de 0 (12:00 A.M.) a 23 (11:00 P.M.). Sintaxe: HORA(valor_tempo). Argumentos: valor_tempo: horário que contém a hora que desejamos encontrar. 50 Função MINUTO – Essa função retorna os minutos de um valor de tempo. O valor retornado é um número inteiro que varia de 0 a 59. Sintaxe: MINUTO(valor_tempo). Argumentos: valor_tempo: horário que contém os minutos que desejamos encontrar. 1. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a seguinte fórmula: =HORA(E5). 2. Copie a fórmula para todos os dias. Se o valor para número de horas estiver aparecendo como um valor do tipo hora, formate para que seja exibido como número. 3. Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite a seguinte fórmula: =MINUTO(E5). 4. Copie a fórmula para todos os dias. Se o valor para número de minutos estiver aparecendo como um valor do tipo hora, formate para que seja exibido como número. 5. Para calcular o valor a pagar você deve multiplicar o número de horas pelo valor por hora e somar com a multiplicação do número de minutos pelo valor por hora dividido por 60. Clique na célula I5 e digite a fórmula: =F5*$I$2+G5*$I$2/60. 6. Copie a fórmula para todos os dias. 7. Totalize o valor a pagar na célula I24. 51 Formatação Condicional A formatação condicional permite que os dados sejam exibidos na planilha de uma forma mais objetiva. Você pode, por exemplo, modificar a cor da fonte ou da célula para que os dados que satisfazem critérios estbelecidos sejam exibidos. 1. Na coluna Dia da semana você vai exibir em vermelho todos os dias que forem segunda-feira. No entanto você deve lembrar que o valor que está armazenado na célula é uma data, e não o nome do dia da semana. Para obter o número do dia da semana da data armazenada, voc~e deve utilizar a função DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23.2. Na guia Página Inicial, grupo Estilo, clique em Formatação Condocional e, no menu apresentado, selecione a Nova Regra. 52 3. Na caixa de diálogo Nova Regra de Formatação, selecione Usar uma fórmula para determinar quais células devem ser formatadas. 4. No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula: =DIA.DA.SEMANA(B5;1)=2. Lembre-se que a função DIA.DA.SEMANA traz como retorno o número do dia da semana, de acordo com o tipo de semana indicado na função. No caso o argumento para tipo foi passado como 1, ou seja, domingo é o dia 1, segunda-feira é o dia 2 e assim por diante. 5. Clique no botçao Formatar e escolha a cor vermelha e negrito. 53 6. Clique em OK e, novamente em OK. 7. Feche a pasta de trabalho Atividades e horas.xlsx. Utilizando as funções LÓGICAS Propósito: 54 Utilizar as funções lógicas do Excel 2013. Meta: Calcular o valor da gratificação para cada um dos vendedores ganhadores. Atribuir um conceito de acordo com o valor das vendas de cada vendedor. Distribuir uma gratificação extra entre os vendedores que não alcançaram o primeiro lugar de acordo com o valor de suas vendas. Tratar adequadamente os erros que possam ocorrer em planilhas. Para completar o preenchimento da planilha VENDAS da pasta de trabalho Gratificação.xlsx, você ainda tem algumas tarefas a realizar: Distribuir a gratificação que a empresa oferece aos vendedores colocados em primeiro lugar; Atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-o com a meta a ser atingida individualmente; Distribuir uma gratificação extra aos vendedores que não se classificaram em primeiro lugar, mas que também se destacaram de acordo com alguns critérios estabelecidos. Função SE – Essa função tem como objetivo efetuar testes condicionais com valores e fórmulas permitindo a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro. Sintaxe: o SE(teste_lógico;valor_se_verdadeiro;valor_se_falso). Argumentos: o Teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro; o Valor_se_falso: o que fazer se o resultado do teste_lógico for Falso. 1. Selecione a planilha VENDAS da pasta de trabalho Prêmio.xlsx. Você deve verificar na célula E3 se o vendedor está em 1º lugar na classificação e, se estiver, calcular e exibir a sua gratificação. Se o vendedor não estiver em 1º lugar, o valor que devem ser colocado na célula é zero. Lembre-se que a gratificação total deve ser dividido igualmente entre os vendedores ganhadores. 55 O raciocínio que vamos seguir é os eguinte: a) SE classificação = 1. b) ENTÃO dividir o valor da gratificação pelo número de ganhadores e exibir na célula. c) SENÃO exibir o valor 0 na célula. 2. Clique na célula E3 e digite a fórmula: =SE(D3=1;$B$1/RELATÓRIO!$B$10;0). Obeserve que na célula D3 está a classificação do vendedor, na célula B1 está o valor da gratificação total e na célula B10 da planilha RELATÓRIO está o número de ganhadores. 3. Copie a fórmula para todos os vendedores. Não se preocupe com os erros apontados bas células E8 e E17. Mais adiante você vai modificar as fórmulas para tratar adequadamente esses erros. 4. Agora você vai colocar uma mensagem para cada vendedor de acordo com os seguintes critérios: CONCEITO CONDIÇÃO EXCELENTE Colocação do vendedor igual a 1. ÓTIMO Valor das vendas do vendedor maior do que a meta. BOM Valor das vendas do vendedor igual à meta. PODE MELHORAR Nenhuma das condições anteriores. Observe que existem quatro condições a serem verificadas. Isso não será possível através de uma única função SE. Nesse caso, você terá que aninhar funções SE para resolver o problema. Até 64 funções SE podem ser aninhadas no Excel 2013. O raciocínio que você deve seguir é o seguinte: SE colocação do vendedor = 1 ENTÃO atribuir conceito “EXCELENTE” SENÃO SE valor das vendas maior do que a meta ENTÃO atribuir conceito “ÓTIMO” SENÃO SE valor das vendas igual à meta ENTÃO atribuir conceito “BOM” SENÃO atribuir conceito “PODE MELHORAR” 56 5. Clique na célula F3 e digite a seguinte fórmula: =SE(D3=1;”EXCELENTE”;SE(C3>$D$1;”ÓTIMO”;SE(C3=$D$1;”BOM”;”PRODE MELHORAR”))). 6. Copie a fórmula para todos os vendedores. 7. Feche a pasta de trabalho Prêmio.xlsx. Função E – Essa função retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros. Sintaxe: o E(lógico;lógico2;...). Argumentos: o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Lógico2: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. Em uma função E você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas para avaliar. Função OU – Essa função retorna o valor Verdadeiro se pelo menos um de seus argumentos for Verdadeiro. Sintaxe: o E(lógico;lógico2;...). Argumentos: o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o Lógico2: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. Em uma função OU você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas para avaliar. As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções que executam testes lógicos, como por exemplo, em conjunto com a função SE. 1. Abra a pasta de trabalho Estado Civil.xlsx. Você vai verificar a diferença dos resultados entre as funções E e OU. 57 2. Clique na célula D3. Nela deve-se exibir o valor VERDADEIRO se a pessoa for solteira e sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser exibido o valor FALSO. 3. Digite a fórmula: =E(B3=”Solteiro(a)”;C3>=22). 4. Copie a fórmula para todas as pessoas. 58 5. Clique na célula E3. Nela deve-se exibir o valor de VERDADEIRO se a pessoa for solteira ou se sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser exibido o valor FALSO. 6. Digite a fórmula: =OU(B3=”Solteiro(a)”;C3>=22). 7. Copie a fórmula para todas as pessoas. Note que no caso da função E, o resultado só é verdadeiro se as duas condições forem verdadeiras. No caso da função OU, o resultado é verdadeiro se pelo menos uma das condições for verdadeira, e somente será falso se as duas condições forem falsas. 59 8. Feche a pasta de trabalho Estado Civil.xlsx e abra a pasta de trabalho Prêmio.xlsx. 9. A empresa resolveu premiar também os funcionários que obtiveram ÓTIMO com uma viagem para Natal(RN) e os funcionários que venderam menos ou o valor dameta, mas que venderam mais do que R$ 300.000,00, com um jantar. Considere o seguinte raciocínio: SE conceito = ÓTIMO ENTÃO prêmio extra = Viagem para Natal(RN) SENÃO SE vendas <= meta E vendas > 300000 ENTÃO prêmio extra = Jantar 10. Clique na célula G3 e digite a fórmula: =SE(F3=”ÓTIMO”;”Viagem para Natal”;SE(E(C3<=$D$1;C3)300000);”Jantar”;””)). Observe que se nenhuma das condições forem Verdadeiras, o valor “” será colocado na célula, ou seja, a célula ficará vazia para evitar que seja exibida a mensagem FALSO. 11. Copie a fórmula para todos os vendedores. Função SEERRO – Essa função tem como objetivo retornar um valor especificado se uma fórmula gerar um erro. Se não existir erro, a função retorna o resultado da fórmula. Sintaxe: o SEERRO(fórmula;valor-se_erro). Argumentos: o Fórmula: fórmula que se deseja verificar quanto a erros; o Lógico2: valor a ser retornado se a fórmula gerar um erro. Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? OU #NULO!. 12. As fórmulas da coluna CLASSIFICAÇÃO estãogerando erros nos casos em que a célula correspondete a venda está vazia. Como é um fato que pode acontecer, é aconselhável não mostrar a mensagem de erro gerada pelo MS Excel, mas sim armazenar na célula um valor mais adequado. Nesse caso, se existir o erro na célula, você deve deixá-la vazia, ou seja, armazenar “”. Clique na célula D3 e modifque a fórmula para: =SEERRO(ORDEM.EQ(C3;$C$3:$C$22);’’). 60 Observe que todos os erros da planilha foram solucuinados. É necessário saber qual valor deve-se colocar na célula nos casos de erro para não ocasionar novos erros. 13. Feche a pasta de trabalho Prêmio.xlsx. Funções de PESQUISA As funções de PESQUISA do Excel são poderosas ferramentas que permitem a busca de dados de forma rápida, muitas vezes evitando o uso de várias funções SE aninhadas. Utilizando as funções PROCV e PROCH Propósito: Utilizar as funções PROCV e PROCH do Excel 2013. Meta: Abrir a pasta de trabalho Relatório de Vendas.xlsx. Através da utilização da função PROCV, preencher a planilha RELATÓRIO com os nomes dos funcionários, comissões, salário fixo e imposto. Calcular o valor a receber para cada funcionário. Através da utilização da função PROCH, preencher a coluna Classificação faixas com as devidas faixas. 61 Função PROCV – A função PROCV permite procurar por um valor na primeira coluna de um intervalo de células e trazer outro valor da mesma linha em outra coluna especificada. Sintaxe: o PROCV(valor_procurado;intervalo_células;núm_coluna,[procurar_interv alo]). Argumentos: o valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode ser um valor ou uma referência. o intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira coluna de intervalo_células são os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas. o núm_coluna: número da coluna do intervalo de células que contém a informação que deve ser retornada. o procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de forma o valor que será procurado na primeira coluna do intervalo, se a correspondência deverá ser exata ou aproximada. Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste caso, os valores da primeira coluna do intervalo deverão estar classificados em ordem ascendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for FALSO, a correspondência se dará de forma exta. Neste caso, os valores da primeira coluna do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com o valor_procurado, será considerado o primeiro encontrado. 1. Abra a pasta de trabalho Relatório de Vendas.xlsx e selecione a planilha RELATÓRIO. 62 2. Preencha a coluna NOME DO FUNCIONÁRIO com os nomes dos funcionários, procurando-os na planilha SUPORTE. Clique na célula B2 e digite a fórmula: =PROCV(A2;. Você iniciou a digitação da função PROCV informando o primeirio argumento, que é o que se deseja procurar. No caso, você deve procurar pelo Código do Funcionário na planilha SUPORTE. 3. Selecione a planilha SUPORTE. Com o mouse pressionado, selecione o intervalo de A2:B23. Essa região é a que contém o valor a ser procurado (código do funcionário, que está na primeira coluna do intervalo) e o valor a ser retornado (o nome do funcionário, que está na segunda coluna do intervalo). A fórmula fica da seguinte forma: =PROCV(A2;Suporte!$A$2:$B$23;. Esse argumento indica qual intervalo de células será considerado para a procura e retorno dos dados. Observe que o intervalo A2:B23 da planilha SUPORTE foi fixado, pois não pode ser modificado quando você copiar a fórmula para as células de baixo. Outra forma de evitar que os endereços sejam modificados na cópia é atribuir um nome ao intervalo. 4. Digite 2 e pressione a tecla ENTER. O parâmetro 2 indica que o valor que se deseja que retorne é o que está na coluna 2 do intervalo de células indicado. 63 =PROCV(A2;Suporte!$A$2:$B$23;2) 5. Copie a fórmula para todos os funcionários. 6. Agora calcule o valor da comissão, salário fixo e impostos para cada funcionário. Esses valores estão em SUPORTE e dependem do valor da venda efetuada pelo funcionário. Atribua um nome à região da planilha SUPORTE onde será procurado o valor da venda e retornados os valores desejados para facilitar a digitação das fórmulas. Selecione a planilha SUPORTE e selecione o intervalo E2:H7. 7. Na Caixa de Nome (a esquerda da Barra de Fórmulas) digite Valor_Vendas e clique em OK. 64 8. Você vai procurar pelo valor da venda na região Valor_Vendas e trazer o percentual de comissão. Esse percentual é obtido pela procura do valor da venda na região Valor_Vendas e trazendo o conteúdo da célula que está na coluna 2. O valor da comissão dev ser calculado como sendo o percentual de comissão multiplicado pelo valor da venda. Selecione a planilha RELATÓRIO. Clique na célula D2 e digite a seguinte fórmula: =PROCV(C2;Valor_Vendas;2)*C2. 9. Copie a fórmula para todos os vendedores. 10. Agora você vai preencher a coluna Salário Fixo com o valor do salário de cada vendedor, Mais uma vez o valor da venda deve ser procurado no interbalo Valor_Vendas. A função PROCV deve retornar o valor do salário fixo que está na coluna 3 do intervalo. Clique na célula E2 e digite a seguinte fórmula: PROCV(C2;Valor_Vendas;3). 65 11. O cálculo do imposto, nessa empresa, é feito sobre o valor das comissões. Agora você vai calcular quanto de imposto cada funcionário deve pagar. Para isso você deve procurar pelo percentual de imposto de cada um no intervalo Valor_Vendas. A função PROCV deve retornar o valor do salário fixo que está na coluna 3 do intervalo. Clique na célula E2 e digite a seguinte fórmula: =PROCV(C2;Valor_Vendas;3). 66 12. Calcule o valor a receber para cada funcionário como sendo: comissão + salário fixo – imposto. 67 Função PROCH – A função PROCH permite procurar por um valor na primeira linha de um intervalo de células e trazer outro valor da mesma coluna em outra linha especificada. Sintaxe: o PROCVH(valor_procurado;intervalo_células;núm_linha,[procurar_interv alo]). Argumentos: o valor_procurado: valor a ser procurado na primeira linha do intervalo de células. Pode ser um valor ou uma referência. o intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira linha de intervalo_células são os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas. o núm_linha: número da linha do intervalo de células que contém a informação que deve ser retornada. o procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de forma o valor que será procurado na primeira linha do intervalo, se a correspondência deverá ser exata ou aproximada. 68 Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exta ou aproximada. Neste caso, os valores da primeira linha do intervalo deverão estar classificados em ordem ascendente, ou a função poderá retornar valores indevidos. Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Neste caso, os valores da primeira linha do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com o valor_procurado, será considerado o primeiro encontrado. A diferença entre as funções PROCV e PROCH é que a primeira efetua a procurade forma vertical, e a segunda de forma horizontal. Você pode utilizar qualwuer uma deas duas, dependendo da disposição do intervalo de células adotada. 1. Você vai preencher a coluna Classifcação faixas de acordo com as vendas de cada funcionário. Os valores a serem colocados nessa coluna estão na planilha SUPORTE. Selecione a planilha SUPORTE e selecione o intervalo de células F10:I11. Nomeie esse intervalo como Faixas. 2. Na Caixa de Nome (a esquerda da Barra de Fórmulas) digite Faixas e clique em OK. 3. Selecione a planilha RELATÓRIO. Clique na célula H2 e digite a seguinte fórmula: =PROCH(C2;Faixas;2). 4. Copie a fórmula para todos os vendedores. 69 Utilizando as funções CORRESP e ÍNDICE Propósito: Deslocar e rotacionar um objeto pelo teclado. Utilizar as funções CORRESP e ÍNDICE. Meta: Calcular os valores de vendas máximo e mínimo. Identificar a posição desses valores dentro da planilha de vendas. Identificar os nomes dos vendedores responsáveis pelos valores máximo e mínimo de vendas. Função CORRESP – Essa função traz como resultado a posição em que o valor procurado encontra-se no intervalo de células indicado, que deve ser formado por uma linha de células ou por uma coluna de células. Sintaxe: o CORRESP(valor_procurado;intervalo;[correspondência]). 70 Argumentos: o valor_procurado: argumento obrigatório, sendo o valor que desejamos procurar no intervalo de células e obter sua posição relativa dentro dele. o intervalo: argumento obrigatório, sendo o intervalo de células que estão sendo pesquisadas. Esse intervalo deve ser uma linha de células ou uma coluna de células. o correspondência: argumento opcional que especifica como o valor será encontrado. Pode ser: 1 ou omitido: localiza o maior valor que é menor ou igual a valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem crescente. 0: localiza o primeiro valor que é exatamente igual ao valor_procurado. Os dados no intervalo podem estar em qualquer ordem. -1: localiza o menor valor que é maior ou igual ao valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem decrescentes. 1. Agora você vai verificar em que local da planilha se encontram os valores para maior e menor venda. Selecione a planilha CONTROLE. 2. Calcule a maior e menor venda na coluna VALOR. Clique na célula E5 e digite a seguinte fórmula: =CORRESP(. 3. Você vai procurar pelo valor da maior venda, que está na célula D5. Continue a fórmula digitando D5, a mesma recebe =CORRESP(D5;. 4. Agora você vai informar o intervalo de células onde será procurado o valor da venda. Esse intervalo é C2:C23 da planilha RELATÓRIO. Continue a fórmula: =CORRESP(D5;Relatório!C2:C23;. 5. Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será 0. A fórmula final é a seguinte: =CORRESP(D5;Relatório!C2:C23;0). 71 6. Repita os procedimentos para encontrar a posição da menor venda na planilha RELATÓRIO. Função ÍNDICE – Essa função o conteúdo de uma célula através do fornecimento de sua posição dentro do intervalo de células. Sintaxe: o ÍNDICE(intervalo;linha[coluna]). Argumentos: o intervalo: argumento obrigatório, sendo o intervalo de células que contém o dado a ser retornado. o intervalo: argumento obrigatório, sendo o número da linha do intervalo onde está o dado a ser retornado. o coluna: argumento opcional, sendo o número da coluna do intervalo onde está o dados a ser retornado. 72 1. Agora você deve retornar os nomes dos vendedores que efetuaram a maior e menor venda. Clique na célula F5 da planilha CONTROLE e digite a seguinte fórmula: =ÍNDICE(. 2. Você deve informar o intervalo de células que contém o nome do vendedor que está sendo procurado. Para tanto digite a seguinte fórmula: =ÍNDICE(Relatório!B2:B23;. 3. Agora você deve informar em que linha desse intervalo encontra-se o dado desejado. Pela função CORRESP você determinou, anteriormente, o número da linha onde está a maior venda. Portanto, o parâmetro que você deve informar na função é exatamente o resultado da função CORRESP, que se encontra na célula E5 da planilha CONTROLE. A fórmula completa fica então: =ÍNDICE(Relatório!B2:B23;Controle!E5). 4. Utilize os mesmos procedimentos para trazer o nome vendedor que efetuou a menor venda. Auditoria de Fórmulas Muitas vezes é necessário executar auditoria em fórmulas inseridas em suas planilhas. Muitas vezes as fórmulas podem gerar erros ou mesmo retornarem valores que não são os esperados. O MS Excel 2013 fornece ferramentas bastante úteis para que você possa localizar e investigar as causas dos erros e determinar soluções. Essas ferramentas incluem o rastreamento de precedentes e dependentes de uma célula, rastreamento de erros, exibição e depuração de fórmulas. Todas essas ferramentas estão presentes na guia Fórmulas, grupo Auditoria de Fórmulas. 73 Rastreando os precedentes de uma célula Propósito: Rastrear as células precedentes de uma célula. Meta: Abrir a pasta de trabalho Faculdade.xlsx. Rastrear as células precedentes das células da planilha BOLETIM. Rastrear as células precedentes das células da planilha RESUMO. A ferramenta Rastrear Precedentes permite verificar quais células afetam o valor da célula selecionada no momento, ou seja, de que célula aquela que se encontra selecionada depende para o cálculo de seu valor. 1. Abra a pasta de trabalho Faculdade.xlsx. 2. Selecione a planilha BOLETIM. 3. Clique na célula I5. 4. Clique na ferramenta Rastrear Precedentes, do grupo Auditoria de Fórmulas, da guia Fórmulas. 5. Veja que uma seta vem da célula K2 e outra vem da célula H5, ou seja, o resultado da fórmula contida na célula I5 depende dos valores das células K2 e H5. Isso é mesmo verdade, pois a célula I5 contém a fórmula: =SE(H5<=25%*$K$2;”Faltas Ok”;!”Faltas acima do limite”). 74 Se você clicar duas vezes em cima da seta, a célula precedente fica selecionada. 6. Selecione a planilha RESUMO. 7. Clique na célula B14. 8. Clique na ferramentas de Rastrear Precedentes. A linha pontilhada indica que a célula ou células precedentes encontram-se em outra planilha. 75 9. Clique duas vezes sobre a linha pontilhada. A janela exibe a origem das células precedentes, com a indicação da pasta, planilha e endereço de célula ou intervalo. Para acessar a origem desejada, basta selecioná-la e clicar no botão Ok. 10. Removas as setas selecionando a ferramenta Remover Setas. Você deve remover as setas das duas planilhas. Rastreando os dependentes de uma célula Propósito: Rastrear as células dependentes de uma célula. Meta: Rastrear as células dependentes das células da planilha BOLETIM. Rastrear as células dependentes das células da planilha RESUMO. A ferramenta Rastrear Dependentes permite verificar quais célukas são afetadas pelo valor da célula selecionada no momento. 1. Selecione a planilha BOLETIM e clique na célula I5. 2. Clique na ferramenta Rastrear Dependentes, do grupo Auditoria de Fórmulas, da guia Fórmulas. 3. Veja que uma seta vai para a célula K5 e uma linha pontilhada vai para outra planilha. 76 A primeira seta, indicando a célula K5, mostra que a fórmula =SE(E(G5="Média OK";I5="Faltas OK");"Aprovado";"Reprovado") depende do valor da célula I5. A seta indicada pela linha pontilhada e apontando para outra planilha, mostra que as células B8, B9 e B10 da planilha RESUMO dependem do valor da célula I5. Podemos verificar essas referências cliucando duas vezes sobre a linha pontilhada. 4. Remova as setas da planilha. Rastreando erros em fórmulas Propósito: Rastrear erros em fórmulas. Meta: Gerar um erro na fórmula que calcula a média de um aluno.
Compartilhar