Baixe o app para aproveitar ainda mais
Prévia do material em texto
i CURSO DE EXCEL AVANÇADO Curso de Excel Avançado C U R SO D E EX C EL A V A N Ç A D O 1 Sumário Aula 1 – Introdução ao curso ...................................................................................................... 4 Aula 2 - Operadores de texto e referências (absoluta, relativa e mista) .................................... 5 Operadores aritméticos .......................................................................................................... 5 Operadores de comparação .................................................................................................... 5 Operador de concatenação de texto ...................................................................................... 6 Operadores de referência ....................................................................................................... 6 A ordem em que o Excel efetua operações em fórmulas ....................................................... 6 Ordem de cálculo .................................................................................................................... 6 Precedência de operadores .................................................................................................... 7 Uso de parâmetros .................................................................................................................. 7 Referências .............................................................................................................................. 8 Aula 3 – Porcentagem ................................................................................................................. 9 Aula 4 – Relação entre planilhas ............................................................................................... 10 Aula 5 – Função “SE” ................................................................................................................. 10 Utilidade da função: .............................................................................................................. 10 Aula 6 – Formatação condicional .............................................................................................. 12 Aula 7 - Função “concatenar” ................................................................................................... 14 Aula 8 - Função “soma se” ........................................................................................................ 14 Aula 10 - Função “e” e “ou” ...................................................................................................... 15 Aula 11 – Congelar painéis ........................................................................................................ 16 Aula 12 – Filtros ......................................................................................................................... 16 Aula 13 – Função “subtotal” ..................................................................................................... 17 Aula 14 – Validação de dados ................................................................................................... 18 Aula 15 – Funções ref. a tempo ................................................................................................ 18 Função Data: ......................................................................................................................... 18 Função Hora: ......................................................................................................................... 18 Função Hoje: ......................................................................................................................... 19 Função Agora: ....................................................................................................................... 19 Função Mês: .......................................................................................................................... 19 Função Dias da semana: ........................................................................................................ 20 Função Ano:........................................................................................................................... 20 Aula 16 – Função “cont.valores” ............................................................................................... 20 Aula 17 – Função “cont se” ....................................................................................................... 21 Aula 18 – Função “cont ses” ...................................................................................................... 21 Aula 19 – Função “escolher” ..................................................................................................... 22 C U R SO D E EX C EL A V A N Ç A D O 2 Aula 20 – Função “arred” .......................................................................................................... 22 Aula 21 – Tabela dinâmica ........................................................................................................ 23 Aula 22 – Gráfico dinâmico ....................................................................................................... 23 Aula 23 – Gráfico de velocímetro .............................................................................................. 23 Aula 24 – Ferramentas de dados ............................................................................................... 24 Texto para colunas ................................................................................................................ 24 Preenchimento relâmpago.................................................................................................... 24 Remover duplicatas ............................................................................................................... 24 Consolidar ............................................................................................................................. 24 Teste de hipóteses ................................................................................................................ 24 Aula 25 –Filtro de dados avançado ........................................................................................... 25 Aula 26 – Inserir Filtros ............................................................................................................. 25 Segmentação de dados: ........................................................................................................ 25 Aula 27 – Função “Procv” e “proch” ......................................................................................... 26 Função PROCV ....................................................................................................................... 26 Função PROCH ....................................................................................................................... 26 Aula 28 – Função “desloc” e “CORRESP” .................................................................................. 26 DESLOC .................................................................................................................................. 26 CORRESP ................................................................................................................................ 28 Aula 29 – Função “índice” ......................................................................................................... 28 Aula 30 – Função “média” e “med” .......................................................................................... 28 Função Média ........................................................................................................................ 28 Função Med ........................................................................................................................... 29 ...............................................................................................................................................29 Aula 31 – Função “pgto” ........................................................................................................... 29 Aula 32 – Função “vf” e “VP” .................................................................................................... 30 Função “VF” ........................................................................................................................... 30 Função “VP” .......................................................................................................................... 30 Aula 33 – Função “taxa” ............................................................................................................ 31 Aula 34 – Função “Nper” ........................................................................................................... 32 Aula 35 – Minigráficos ............................................................................................................... 32 Aula 36 – Guia desenvolvedor (códigos) ................................................................................... 33 MACROS: ............................................................................................................................... 33 Aula 37 – Guia desenvolvedor (códigos) ................................................................................... 33 Visual Basic for Applications - VBA ........................................................................................ 33 Aula 38 – Guia desenvolvedor (controles) ................................................................................ 62 C U R SO D E EX C EL A V A N Ç A D O 3 Controles de formulários – Modo Design e Inserir (botão, caixa de combinação e caixa de seleção) ................................................................................................................................. 62 Aula 39 – Guia desenvolvedor (controles) ................................................................................ 63 Controles de formulários – Inserir (botão de rotação, caixa de listagem e botão de opção) ............................................................................................................................................... 63 Aula 40 – Guia desenvolvedor (controles) ................................................................................ 63 Controles de Formulários – Inserir (caixa de grupo, rótulo e barra de rolagem) ................. 63 Aula 41 – Solver ......................................................................................................................... 64 Como carregar o programa suplementar do Solver.............................................................. 64 Definir e solucionar um problema com SOLVER ................................................................... 64 Aula 42 – Solver ......................................................................................................................... 66 Etapas das soluções de avaliação do Solver .......................................................................... 66 Alterar a forma como o Solver localiza soluções .................................................................. 66 Salvar ou carregar um modelo de problema ........................................................................ 67 Métodos de solução usados pelo Solver ............................................................................... 67 Aula 43 – Solver ......................................................................................................................... 67 Aula 44 – Função “indireto” ...................................................................................................... 68 Aula 45 – Função “BDEXTRAIR” e buscar Imagens.................................................................... 68 Função BDEXTRAIR ................................................................................................................ 68 Buscar Imagens ..................................................................................................................... 69 Aula 46 – Power pivot ............................................................................................................... 69 Aula 47 – Power View ............................................................................................................... 70 Aula 48 – Dashboard ................................................................................................................. 71 Aula 49 –Mapas interativos....................................................................................................... 71 Aula 50 – Power Map ................................................................................................................ 71 C U R SO D E EX C EL A V A N Ç A D O 4 AULA 1 – INTRODUÇÃO AO CURSO Iniciamos informando que este curso está organizado em 50 vídeo-aulas. E cada aula abordará um tema específico. Para facilitar seu aprendizado, você poderá acompanhar por essa apostila os conceitos principais de cada aula e verificar alguns exercícios. É importante citar que este curso é um curso de Excel avançado. Ou seja, partimos da premissa que você já conhece as funcionalidades básicas do Excel. Caso você tenha dúvidas sobre as funções básicas do programa, estas dúvidas devem ser esclarecidas no curso de Excel Básico. Assim como dúvidas sobre programação VBA devem ser esclarecidas em um curso de Visual Basic. As dúvidas sobre o conteúdo deste curso poderão ser esclarecidas de duas formas: Nas aulas ao vivo com o professor, que ocorrerão conforme programação disponível na agenda do curso; ou Através do fórum de notícias, onde você poderá consultar dúvidas já esclarecidas ou postar uma dúvida para que o professor lhe responda em momento oportuno. As referências bibliográficas utilizadas para elaboração deste curso são: MANZANO, A. L. N. G.; Microsoft Office EXCEL 2003 – Práticas Gerenciais, série Negócios & Tecnologia. Editora Érica, 2006, Taubaté-SP. BRAULE, R.; Estatística Aplicada com Excel para cursos de administração e economia. 11ª ed. Editora Elsevier, 2001, Rio de Janeiro-RJ. MARTELI, R.; e BARROS, M. S. M.; Excel 2013 avançado. SENAC, 2013, São Paulo-SP. Além de conteúdo disponível na página de suporte da Microsoft para o pacote Office: https://support.office.com/. https://support.office.com/ C U R SO D E EX C EL A V A N Ç A D O 5 AULA 2 - OPERADORES DE TEXTO E REFERÊNCIAS (ABSOLUTA, RELATIVA E MISTA) Há quatro diferentes tipos de operadores de cálculo: aritmético, comparação, concatenação de texto e referência. Operadores aritméticos Para efetuar operações matemáticas básicas, como adição, subtração ou multiplicação, combinar números e produzir resultados numéricos, use estes operadores aritméticos. Operador Aritmético Significado Exemplo + (sinal de mais) Adição 3+3 – (sinal de menos) Subtração Negação 3–1–1 * (asterisco) Multiplicação 3*3 / (sinal de divisão) Divisão 3/3 % (sinal de porcentagem) Porcentagem 20% ^ (acento circunflexo) Exponenciação 3^2 Operadores de comparação Você pode comparar dois valores, usando os operadores a seguir. Quando dois valores são comparados usando esses operadores, o resultado será um valor lógico, VERDADEIRO ou FALSO. Operador de Comparação Significado Exemplo = (sinal de igual) Igual a A1=B1 > (sinal de maior que) Maior que A1>B1 < (sinal de menor que) Menor que A1<B1 >= (sinal de maior ou igual a) Maior ou igual a A1>B1 <= (sinal de menor ou igual a) Menor ou igual a A1<B1 <> (sinal de diferente de) Diferente de A1<>B1 C U R SO D E EX C EL A V A N Ç A D O 6 Operador de concatenação de texto Use o 'E' comercial (&) para associar, ou concatenar,uma ou mais sequências de caracteres de texto para produzir um único texto. Operador de Texto Significado Exemplo & (E comercial) Conecta, ou concatena dois valores para produzir um valor de texto contínuo. ("North"&"wind") Operadores de referência Combine intervalos de células para cálculos com estes operadores. Operador de Referência Significado Exemplo : (dois-pontos) Operador de intervalo, que produz uma referência para todas as células entre duas referências, incluindo as duas referências. B5:B15 , (vírgula) Operador de união, que combina diversas referências em uma referência. SOMA(B5:B15,D5:D15) (espaço) Operador de interseção, que produz uma referência a células comuns a duas referências. B7:D7 C6:C8 A ordem em que o Excel efetua operações em fórmulas Em alguns casos, a ordem na qual o cálculo é executado pode afetar o valor retornado da fórmula. Então é importante compreender como a ordem é determinada e como você pode alterar a ordem para obter o resultado desejado. Ordem de cálculo As fórmulas calculam valores segundo uma ordem específica. Uma fórmula no Excel sempre começa com um sinal de igual (=). O sinal de igual informa ao Excel que os caracteres a seguir constituem uma fórmula. Depois do sinal de igual estão os elementos a serem calculados (os operandos), que são separados por operadores de C U R SO D E EX C EL A V A N Ç A D O 7 cálculo. O Excel calcula a fórmula da esquerda para a direita, de acordo com uma ordem específica para cada operador da fórmula. Precedência de operadores Se você combinar vários operadores em uma única fórmula, o Excel executará as operações na ordem mostrada na tabela a seguir. Se uma fórmula contiver operadores com a mesma precedência — por exemplo, se uma fórmula contiver um operador de multiplicação e divisão — o Excel avaliará os operadores da esquerda para a direita. Operador Descrição : (dois-pontos) (espaço simples) , (vírgula) Operadores de referência – Negação (como em –1) % Porcentagem ^ Exponenciação * e / Multiplicação e divisão + e – Adição e subtração & Conecta duas sequências de texto (concatenação) = < > <= >= <> Comparação Uso de parâmetros Para alterar a ordem da avaliação, coloque entre parênteses a parte da fórmula a ser calculado primeiro. Por exemplo, a fórmula a seguir retorna 11 porque o Excel calcula a multiplicação antes da adição. A fórmula multiplica 2 por 3 e, em seguida, soma 5 ao resultado. =5+2*3 Por outro lado, se você usar parênteses para alterar a sintaxe, o Excel somará 5 e 2 e, em seguida, multiplicará o resultado por 3 para produzir 21. =(5+2)*3 C U R SO D E EX C EL A V A N Ç A D O 8 No exemplo abaixo, os parênteses na primeira parte da fórmula forçam o Excel a calcular B4+25 primeiro e, em seguida, dividir o resultado pela soma dos valores nas células D5, E5 e F5. =(B4+25)/SOMA(D5:F5) Referências Ao criar uma fórmula simples ou uma fórmula que usa uma função, você pode fazer referência aos dados das células de uma planilha incluindo referências de célula nos argumentos da fórmula. Por exemplo, quando você insere ou seleciona a referência de célula A2, a fórmula usa o valor dessa célula para calcular o resultado. Você também pode fazer referência a um intervalo de células. Por padrão, uma referência de célula é relativa. Por exemplo, quando você se referir a célula A2 da célula C2, você está realmente fazendo referência a uma célula que está duas colunas para a esquerda (C menos R) e na mesma linha (2). Uma fórmula que contém uma referência de célula relativa alterações enquanto você copiá-lo de uma célula para outra. Relativa • Muda a Linha; e • Muda a coluna Absoluta • Não muda linha; e • Não muda a coluna Mista • Muda a linha, mas não muda a coluna; ou • Muda a coluna, mas não muda a linha Referência C U R SO D E EX C EL A V A N Ç A D O 9 Como exemplo, se você copiar a fórmula = B2 + A2 de célula C2 em D2, a fórmula em D2 ajusta uma linha para baixo e fica = A3 + B3. Se você quiser manter a referência da célula original neste exemplo, quando você a copiar, faça a referência de célula absoluta precedendo as colunas (A e B) e a linha (2) com um cifrão ($). Em seguida, quando você copiar a fórmula (= $A$ 2 + $B$ 2) de C2 em D2, a fórmula permanece exatamente o mesmo. Em casos menos frequentes, você pode querer tornar a referência à célula "mista", precedendo o valor da coluna ou da linha com um cifrão para "bloquear" a coluna ou a linha (por exemplo, $A2 ou B$3). Para alterar o tipo de referência à célula: 1. Selecione a célula que contém a fórmula. 2. Na barra de fórmulas , selecione a referência que você deseja alterar. 3. Pressione F4 para alternar entre os tipos de referências. A tabela a seguir resume como um tipo de referência será atualizado caso uma fórmula que contenha a referência seja copiada duas células para baixo e duas células para a direita. Para a fórmula sendo copiada: Se a referência for: É alterada para: $A$1 (coluna absoluta e linha absoluta) $A$1 (a referência é absoluta) A$1 (coluna relativa e linha absoluta) C$1 (a referência é mista) $A1 (coluna absoluta e linha relativa) $A3 (a referência é mista) A1 (coluna relativa e linha relativa) C3 (a referência é relativa) AULA 3 – PORCENTAGEM Conteúdo disponível apenas na vídeo-aula. C U R SO D E EX C EL A V A N Ç A D O 10 AULA 4 – RELAÇÃO ENTRE PLANILHAS Há dois tipos de relações entre planilhas, as relações: I. Entre planilhas de um mesmo arquivo II. Entre planilhas de arquivos diferentes Para relações entre planilhas do mesmo arquivo basta usar referências. E para criar relações entre planilhas de arquivos diferentes, você pode seguir o seguinte processo: a) Clique em Dados> Relações. Se Relações estiverem esmaecidas, isso significa que a sua pasta de trabalho contém apenas uma tabela. b) Na caixa Gerenciar Relações, clique em Nova. c) Na caixa Criar Relação, clique na seta de Tabela e selecione uma tabela na lista. Em uma relação de muitos-para-um, essa tabela deve estar no lado muitos. Usando nosso exemplo de cliente e inteligência de dados temporais, você escolheria a tabela de vendas dos clientes primeiro, porque é provável que ocorram muitas vendas em um determinado dia. Para Coluna (Estrangeira), selecione a coluna que contém os dados relacionados à Coluna Relacionada (Principal). Por exemplo, se você tinha uma coluna de datas em ambas às tabelas, agora você escolheria essa coluna. Para Tabela Relacionada, selecione uma tabela que tenha pelo menos uma coluna de dados relacionada à tabela que você acabou de selecionar para Tabela. Para Coluna Relacionada (Primária), selecione uma coluna que tenha valores exclusivos correspondentes aos valores da coluna selecionada para Coluna. d) Clique em OK. AULA 5 – FUNÇÃO “SE” Utilidade da função: Basicamente o papel desta função é verificar se uma “condição” é satisfeita e retornar um valor “verdadeiro” ou “falso” (MANZANO, 2006). C U R SO D E EX C EL A V A N Ç A D O 11 Onde: Fórmula: Observação: para indicar um determinado valor é um texto e não número ou nome de fórmula, deve se inserir este valor entre aspas “x”. A função SE pode ser utilizada subsequentemente para realização de testes lógicos simultâneos. Ou seja, um teste após o outro em uma mesma fórmula. Sempre que você se depara com uma dúvida, você tem no mínimo duas respostas possíveis. Quando você utiliza a função “SE” o Excel tem duas alternativas de resposta. Mas existem perguntas que possuem três ou mais respostas, e com o uso simultâneo da função “se” você pode fazer com que o Excel possa lhe responder até 7 alternativasDIFERENTES, conforme podemos observar na figura abaixo. •É o teste lógico verificado pela fórmula, sendo base para a resposta verdadeira ou falsacondição •É a resposta positiva à verificação do teste lógicoverdadeiro •É a resposta negativa à verificação do teste lógicofalso C U R SO D E EX C EL A V A N Ç A D O 12 AULA 6 – FORMATAÇÃO CONDICIONAL A formatação condicional formata as células selecionadas com base em uma fórmula. Essa ferramenta é uma solução bastante interessante para personalizar sua planilha e facilitar a visualização das informações Para aplicar formatação condicional: a) Abra a página que contém a Exibição de Dados à qual deseja aplicar formatação condicional. b) Clique com o botão direito do mouse na Exibição de Dados e, em seguida, clique em Formatação Condicional no menu de atalho. DICA: Você também pode clicar na Exibição de Dados, clicar na seta Imagem do botão no canto superior direito, para mostrar a lista Tarefas Comuns de Exibição de Dados e, em seguida, clicar em Formatação Condicional. Para ocultar a lista, clique na seta novamente. C U R SO D E EX C EL A V A N Ç A D O 13 c) O painel de tarefas Formatação Condicional é aberto. d) Na Exibição de Dados, selecione qualquer célula (exceto o cabeçalho), como a marca HTML, o valor de dados ou o intervalo de texto do campo que deseja formatar. e) Depois de você selecionar os dados que deseja formatar, o botão Criar do painel de tarefas Formatação Condicional fica disponível. OBSERVAÇÃO: Caso o botão Criar não fique disponível, não será possível aplicar formatação condicional à seleção. O botão Criar só fica disponível após a seleção de um intervalo que possa receber formatação condicional. f) Clique em Criar e, em seguida, em Aplicar formatação. OBSERVAÇÃO: A opção: Aplicar formatação aplica um estilo a uma marca HTML ou a um valor de dados selecionado que atenda a determinados critérios. A opção: Mostrar conteúdo ou Ocultar conteúdo altera a visibilidade de uma marca HTML ou valor de dados selecionado que atenda determinados critérios, Por exemplo, se você quiser criar e exibir um ícone Novo para conteúdo adicionado recentemente, crie uma condição que exiba o ícone caso o conteúdo tenha sido adicionado dentro de um número especificado de dias. g) Na caixa de diálogo Critérios de Condição, clique em qualquer lugar da primeira linha para adicionar uma nova cláusula e, em seguida, siga estes procedimentos: h) Em Nome do Campo, clique na seta e, em seguida, no campo desejado na lista. i) Em Comparação, clique na seta e, em seguida, na comparação desejada. Em Valor, clique na seta e, em seguida, em Mais Campos. Na caixa de diálogo Mais Campos, clique no campo desejado e em OK. OBSERVAÇÃO: Usando os operadores E e OU e adicionando cláusulas, você pode criar critérios que especifiquem várias condições. Agora que você criou a condição, pode aplicar a formatação. j) Na caixa de diálogo Critérios de Condição, clique em OK. k) Na caixa de diálogo Modificar Estilo, escolha as opções para criar o estilo da formatação condicional. l) Em Categoria, clique em Fonte. Na lista espessura da fonte, clique em negrito. m) Em Categoria, clique em Plano de Fundo. Na lista cor do plano de fundo, clique em Amarelo. C U R SO D E EX C EL A V A N Ç A D O 14 Como a Exibição de Dados é de dados dinâmicos, você vê imediatamente a formatação condicional aplicada no modo Design. AULA 7 - FUNÇÃO “CONCATENAR” A função concatenar é bastante utilizado para unir informações alfanuméricas. Ou seja, CONCATENAR é umas das funções de texto, para unir duas ou mais cadeias de texto em uma única cadeia. AULA 8 – FUNÇÃO “SOMA SE” A função “SOMA SE” é utilizada para realização de uma soma (adição) mediante a realização de testes lógicos simultâneos C U R SO D E EX C EL A V A N Ç A D O 15 AULA 9 – FUNÇÃO “MÉDIA SE” A função “MÉDIA SE” é muito similar à função SOMA SE, e é utilizada para realização de média mediante a realização de testes lógicos simultâneos. AULA 10 – FUNÇÃO “E” E “OU” C U R SO D E EX C EL A V A N Ç A D O 16 AULA 11 – CONGELAR PAINÉIS Para manter a área de uma planilha sempre visível enquanto rola para outra área da planilha, você pode bloquear linhas ou colunas específicas, congelando ou dividindo painéis. O exemplo mais comum é manter os rótulos de linha e coluna sempre visíveis durante a rolagem. Uma linha sólida indica que a linha 1 está congelada para manter os rótulos das colunas no mesmo lugar durante a rolagem. Ao congelar painéis, você mantém linhas ou colunas específicas visíveis durante a rolagem na planilha. Ao dividir painéis, você cria áreas separadas da planilha e pode rolar a planilha dentro de uma delas, enquanto as linhas e colunas da outra área permanecem visíveis. AULA 12 – FILTROS Inserir filtros em uma planilha permite que você aplique filtros à sua tabela, deixando visíveis apenas as linhas que possuam o conteúdo filtrado. Esta função oferece uma série de recursos para manipular os dados de sua planilha. Além da filtragem convencional, você pode filtrar por texto, número, ou cor. E utilizando a opção avançado você pode também copiar a seleção para outra área da planilha. Existem 3 formas de congelar painéis C U R SO D E EX C EL A V A N Ç A D O 17 AULA 13 – FUNÇÃO “SUBTOTAL” A função SUBTOTAL retorna um subtotal em uma lista ou em um banco de dados. Há dois modos de se utilizar a função subtotal: a) A primeira é através da fórmula para uso em uma linha específica. b) A segunda é através da estrutura de tópicos. A função SUBTOTAL ignora as linhas não incluídas no resultado de um filtro, independentemente de qual valor de núm_função seja utilizado. A função SUBTOTAL foi projetada para colunas de dados ou intervalos verticais. Ela não foi projetada para linhas de dados nem intervalos horizontais. C U R SO D E EX C EL A V A N Ç A D O 18 AULA 14 – VALIDAÇÃO DE DADOS A validação de dados é um recurso do Excel que você pode usar para definir restrições em quais dados podem ou devem ser inseridos em uma célula. Você pode configurar a validação de dados para impedir que os usuários insiram dados inválidos. Se preferir, pode permitir que os usuários inserissem dados inválidos, mas avisá-los quando tentarem digitar esse tipo de dados na célula. Também pode fornecer mensagens para definir a entrada esperada para a célula, além de instruções para ajudar os usuários a corrigir erros. AULA 15 – FUNÇÕES REF. A TEMPO Função Data: A função DATA é utilizada para unir três valores separados e combiná-los para formar uma data. Função Hora: 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.). C U R SO D E EX C EL A V A N Ç A D O 19 Função Hoje: Retorna o número de série da data atual. O número de série é o código de data/hora usado pelo Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser inserida, o Excel irá transformar o formato da célula em Data. Se quiser exibir o número de série, será necessário alterar o formato das células para Geral ou Número. A função HOJE é útil quando você precisa ter a data atual exibida em uma planilha, independentemente de quando a pasta de trabalho for aberta. Função Agora: Retorna o número de série da data e da hora atual. Se o formato da célula era Geral antes de a função ter sido inserida, o Excel transformará o formato dessa célula para que ele corresponda aomesmo formato de data e hora de suas configurações regionais. Função Mês: Retorna o mês de uma data, representado por um número de série. O mês é fornecido como um inteiro, variando de 1 (janeiro) a 12 (dezembro). Como a Microsoft Excel armazena datas como números de série sequenciais, elas podem ser usadas em cálculos. Por padrão, 1º de janeiro de 1900 é o número de série 1 e 1º de janeiro de 2008 é o número de série 39448 porque está 39.448 dias após 1º de janeiro de 1900. C U R SO D E EX C EL A V A N Ç A D O 20 Função Dias da Semana: Retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. Função Ano: Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de 1900-9999. AULA 16 – FUNÇÃO “CONT.VALORES” A função CONT.VALORES conta o número de células que não estão vazias em um intervalo. C U R SO D E EX C EL A V A N Ç A D O 21 AULA 17 – FUNÇÃO “CONT SE” A função CONT.SE é uma das funções estatísticas utilizadas para contar o número de células que atendem a um critério; por exemplo, para contar o número de vezes que uma cidade específica aparece em uma lista de clientes. AULA 18 – FUNÇÃO “CONT SES” A função CONT.SES aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são atendidos. Onde: a) intervalo_critérios1 - Obrigatório. O primeiro intervalo no qual avaliar os critérios associados. b) critérios1 - Obrigatório. Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B4, "maçãs" ou "32". c) intervalo_critérios2, critérios2, ... - Opcional. Intervalos adicionais e seus critérios associados. Até 127 intervalo/critérios pares são permitidos. C U R SO D E EX C EL A V A N Ç A D O 22 AULA 19 – FUNÇÃO “ESCOLHER” A função ESCOLHER é utilizado para selecionar um valor entre 254 valores que se baseie no número de índice. Por exemplo, se do valor1 até o valor7 forem os números da semana, ESCOLHER retorna um dos dias quando um número entre 1 e 7 for usado como núm_índice. A função ESCOLHER tem os seguintes argumentos: a) núm_índice - Obrigatório. Especifica o argumento de valor selecionado. Núm_índice deve ser um número entre 1 e 254, ou uma fórmula ou referência a uma célula que contenha um número entre 1 e 254. Se núm_índice for 1, ESCOLHER retornará valor1; se for 2, ESCOLHER retornará valor2; e assim por diante. Se núm_índice for menor do que 1 ou maior do que o número do último valor na lista, ESCOLHER retornará o valor de erro #VALOR!. Se núm_índice for uma fração, ela será truncada para o menor inteiro antes de ser usada. b) valor1, valor2,... - Valor1 é obrigatório, valores subsequentes são opcionais. AULA 20 – FUNÇÃO “ARRED” A função ARRED arredonda um valor para um número especificado de dígitos. Por exemplo, se a célula A1 contiver 23,7825 e você quiser arredondar esse valor para duas casas decimais, poderá usar a seguinte fórmula: C U R SO D E EX C EL A V A N Ç A D O 23 O resultado dessa função é 23,78. AULA 21 – TABELA DINÂMICA Usando um relatório de Tabela Dinâmica, você pode resumir, analisar, explorar e apresentar um resumo dos dados da sua planilha ou de uma fonte dos dados externos. Um relatório de Tabela Dinâmica será especialmente útil quando se deseja analisar totais relacionados, especialmente quando há uma longa lista de valores a serem somados e dados agregados ou subtotais podem ajudar a verificar os dados de perspectivas diferentes e comparar valores de dados semelhantes. AULA 22 – GRÁFICO DINÂMICO Pode ser difícil obter uma visão geral quando você possui dados em uma Tabela Dinâmica enorme ou quando você tem um monte de dados complexos E um Gráfico Dinâmico pode ajudá-lo a dar sentido a esses dados. Enquanto um Gráfico Dinâmico mostra a série de dados, as categorias e os eixos de gráfico lhe fornecem os controles de filtragem interativos para que você possa analisar rapidamente um subconjunto dos dados. AULA 23 – GRÁFICO DE VELOCÍMETRO Conteúdo disponível apenas na vídeo-aula. C U R SO D E EX C EL A V A N Ç A D O 24 AULA 24 – FERRAMENTAS DE DADOS O grupo “Ferramentas de Dados” da guia “Dados” nos oferece várias opções para manipulação dos dados de uma planilha. Vamos conhecer cada uma destas opções. Texto para colunas Uma das principais razões para se dividir o texto em colunas separadas é que isto nos permite classificar e filtrar cada uma dessas colunas. Preenchimento relâmpago Ao invés de inserir dados manualmente em uma planilha, você pode usar o recurso de Preenchimento Automático. O Preenchimento Relâmpago é uma novidade do Excel 2013, para preencher os dados com base em um exemplo. Remover Duplicatas Quando você usa o recurso Remover Duplicatas, os dados duplicados são permanentemente excluídos. Antes de excluir essas duplicatas, é uma boa ideia copiar os dados originais em outra planilha para que você não perca nenhuma informação acidentalmente. Consolidar Para resumir e informar os dados de planilhas separadas, você pode consolidar os dados de cada planilha separada em uma planilha (ou planilha mestre). As planilhas podem estar na mesma pasta de trabalho que a planilha mestre ou em outras pastas de trabalho. Teste de hipóteses Usando ferramentas de análise hipotética no Microsoft Office Excel, você pode usar diferentes conjuntos de valores em uma ou mais fórmulas para explorar todos os vários resultados. C U R SO D E EX C EL A V A N Ç A D O 25 Por exemplo, você pode fazer testes de hipóteses para criar dois orçamentos onde cada um pressupõe um determinado nível de receita. Ou, você pode especificar um resultado que você deseja que uma fórmula produza e determine quais conjuntos de valores produzirão esse resultado. AULA 25 – FILTRO DE DADOS AVANÇADO Se você deseja filtrar os dados com critérios mais complexos, como: Tipo= “Produção” OU Vendedor=“Ribeiro” Você poderá utilizar um Filtro Avançado. O comando Avançado trabalha o comando Filtro em vários aspectos diferentes. Pois: Ele exibe a caixa de diálogo Filtro Avançado em vez de um menu de Filtro Automático. Os critérios avançados devem ser digitados em um intervalo de critérios separados na planilha e acima do intervalo de células ou da tabela que deseja filtrar. O Excel usa o intervalo de critérios separados na caixa de diálogo Filtro Avançado como a fonte dos critérios avançados. AULA 26 – INSERIR FILTROS Segmentação de dados: Segmentações de dados são componentes de filtragem fáceis de usar, com um conjunto de botões que permite filtrar rapidamente os dados de um relatório de Tabela Dinâmica, sem a necessidade de abrir listas suspensas para localizar os itens que você deseja filtrar. Além de filtragem rápida, segmentações de dados também indicam o estado de filtragem atual, que torna mais fácil de entender o que exatamente é mostrado em um relatório de tabela dinâmica filtrado. C U R SO D E EX C EL A V A N Ç A D O 26 AULA 27 – FUNÇÃO “PROCV” E “PROCH” Função PROCV A função PROCV é utilizada quando precisar localizar valores em linhas de uma tabela ou de um intervalo. Por exemplo, procurar pelo sobrenome de uma funcionária e encontrar seu número de identificação, ou ainda encontrar seu telefone pesquisando seu sobrenome (como um catálogo de telefone). Função PROCHA função PROCH localiza um valor na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use PROCH quando seus valores de comparação estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. O “H” de PROCH significa "Horizontal”. AULA 28 – FUNÇÃO “DESLOC” E “CORRESP” DESLOC A função DESLOC retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. C U R SO D E EX C EL A V A N Ç A D O 27 A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas. Fórmula: Ref. Obrigatória; É a referência na qual você deseja basear o deslocamento; Ref. deve ser uma referência a uma célula ou intervalo de células adjacentes; caso contrário, DESLOC retornará o valor de erro #VALOR. Lins. Obrigatório; É o número de linhas, acima ou abaixo, a que se deseja que a célula superior esquerda se refira; Lins podem ser positivas (que significa abaixo da referência inicial) ou negativas (acima da referência inicial). Cols. Obrigatório; É o número de colunas, à esquerda ou à direita, a que se deseja que a célula superior esquerda do resultado se refira; Cols. podem ser positivas (que significa abaixo da referência inicial) ou negativas (acima da referência inicial). Altura e Largura Altura Opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo; Largura Opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo. C U R SO D E EX C EL A V A N Ç A D O 28 CORRESP A função CORRESP procura um item especificado em um intervalo de células e, em seguida, retorna à posição relativa do item no intervalo. AULA 29 – FUNÇÃO “ÍNDICE” A função ÍNDICE retorna um valor ou a referência a um valor de dentro de uma tabela ou intervalo. Há duas maneiras de usar a função ÍNDICE: Forma matricial: retorna o valor de uma célula especificada ou uma matriz de células; Formulário de referência: retorna uma referência a células especificadas. AULA 30 – FUNÇÃO “MÉDIA” E “MED” Função Média C U R SO D E EX C EL A V A N Ç A D O 29 Função Med A mediana é o número no centro de um conjunto de números. AULA 31 – FUNÇÃO “PGTO” A função PGTO é uma das funções financeiras do Excel, e calcula o pagamento de um empréstimo de acordo com pagamentos constantes e com uma taxa de juros constante. As funções financeiras são aquelas relacionadas aos cálculos de juros compostos, amortização, valor presente líquido, entre outros. É muito comum entre os administradores e contadores o uso da calculadora HP12c para realização destes cálculos. Porém, o Excel também possui esta funcionalidade. C U R SO D E EX C EL A V A N Ç A D O 30 AULA 32 – FUNÇÃO “VF” E “VP” Função “VF” A função VF calcula o valor futuro de um investimento com base em uma taxa de juros constante. Função “VP” A função VP calcula o valor presente de um empréstimo ou investimento com base em uma taxa de juros constante. C U R SO D E EX C EL A V A N Ç A D O 31 AULA 33 – FUNÇÃO “TAXA” A função TAXA retorna a taxa de juros por período de uma anuidade. C U R SO D E EX C EL A V A N Ç A D O 32 AULA 34 – FUNÇÃO “NPER” A função NPER retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. AULA 35 – MINIGRÁFICOS Um minigráfico é um pequeno gráfico em uma célula de planilha que fornece uma representação visual de dados. Os minigráficos possibilitam criar representações visuais de dados com um único clique, e facilitam muito a compreensão destes dados. Ao contrário de gráficos em uma planilha do Excel, os minigráficos não são objetos. Na verdade, um minigráfico é um pequeno gráfico no plano de fundo de uma célula. C U R SO D E EX C EL A V A N Ç A D O 33 Como um minigráfico é um pequeno gráfico embutido em uma célula, você poderá inserir texto em uma célula e usar o minigráfico como seu plano de fundo como mostrará no exemplo a seguir. AULA 36 – GUIA DESENVOLVEDOR (CÓDIGOS) MACROS: Se você quer automatizar uma tarefa repetitiva no Excel para fazer a tarefa novamente com um único clique. A melhor maneira de se fazer isso é gravando uma macro. No grupo Código da guia Desenvolvedor podemos encontrar entre as opções, os botões de: Macro: Onde é possível visualizar as macros já gravadas; Gravar Macro: Onde é possível gravar uma nova macro; Segurança de Macro: Onde é possível configurar o nível de segurança das macros. AULA 37 – GUIA DESENVOLVEDOR (CÓDIGOS) Visual Basic for Applications - VBA O Visual Basic é uma linguagem de programação produzida pela empresa Microsoft. O Visual Basic for Applications - VBA é uma implementação do Visual Basic incorporada em todos os programas do Microsoft Office. Ou seja, o VBA é um suplemento que permite ao usuário criar programações dentro dos programas do pacote Office, em destaque o Excel. C U R SO D E EX C EL A V A N Ç A D O 34 Abaixo segue as principais funções disponíveis no VBA, basta escolher a versão em inglês da função e adicioná-la ao seu código VBA da seguinte maneira. Categoria Tipo Banco de dados 1 Data e hora 2 Engenharia 3 Estatística 4 Financeira 5 Informações 6 Lógica 7 Matemática 8 Matemática e trigonometria 9 Pesquisa e referência 10 Texto 11 Aqui vai a tabela de equivalências: Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 1 BDCONTAR DCOUNT Conta às células que contêm números em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. 1 BDCONTARA DCOUNTA Conta às células não vazias em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. 1 BDDESVPA DSTDEVP Calcula o desvio padrão de uma população com base na população total, usando os números em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. C U R SO D E EX C EL A V A N Ç A D O 35 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 1 BDEST DSTDEV Estima o desvio padrão de uma população com base em uma amostra, usando os números em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas. 1 BDEXTRAIR DGET Extrai um único valor em uma coluna de uma lista ou de um banco de dados que coincide com as condições especificadas. 1 BDMÁX DMAX Retorna o maior número em uma coluna de uma lista ou de um banco de dados que coincide com as condições especificadas. 1 BDMÉDIA DAVERAGE Calcula a média dos valores em uma coluna de uma lista ou um banco de dados que corresponde às condições especificadas por você. 1 BDMÍN DMIN Retorna o menor número em uma coluna de uma lista ou de um banco de dados que coincide com as condições especificadas. 1 BDMULTIPL DPRODUCT Multiplica os valores em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. 1 BDSOMA DSUM Soma os números em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. 1 BDVARESTDVAR Estima a variância de uma população com base em uma amostra, usando os números em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. C U R SO D E EX C EL A V A N Ç A D O 36 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 1 BDVARP DVARP Calcula a variância de uma população com base na população total usando os números em uma coluna de uma lista ou de um banco de dados que coincidem com as condições especificadas. 2 FRAÇÃOANO YEARFRAC Calcula a fração de ano que representa o número de dias inteiros entre duas datas (data_inicial e data_final). Use a função de planilha FRAÇÃOANO para identificar a proporção dos benefícios ou obrigações de um ano inteiro a serem designados para um determinado termo. 2 DATAM EDATE Retorna um número de série de data que é o número de meses indicado antes ou depois de data_inicial. Use DATAM para calcular datas de liquidação ou datas de vencimento que caem no mesmo dia do mês da data de emissão. 2 DIA.DA.SEMANA WEEKDAY Retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. 2 DIAS360 DAYS360 Retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). 2 DIATRABALHO WORKDAY Retorna um número que representa uma data que é o número indicado de dias úteis antes ou após uma data (a data inicial). Os dias úteis excluem fins de semana e quaisquer datas identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura, horas de entrega esperadas ou o número de dias de trabalho executado. C U R SO D E EX C EL A V A N Ç A D O 37 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 2 DIATRABALHOTOTAL NETWORKDAYS Retorna o número de dias úteis inteiros entre data_inicial e data_final. Dias úteis excluem finais de semana e qualquer data identificada como feriado. Use DIATRABALHOTOTAL para calcular benefícios dos empregados que se acumulam com base no número de dias trabalhados durante um prazo específico. 2 FIMMÊS EOMONTH Retorna o número de série para o último dia do mês, que é o número indicado de meses antes ou depois de data_inicial. Use FIMMÊS para calcular as datas de vencimento que caem no último dia do mês. 2 NÚMSEMANA WEEKNUM Retorna o número que indica onde se encontra a semana, numericamente, em um ano. 3 BESSELI BESSELI Retorna a função de Bessel modificada, que equivale à função de Bessel avaliada por argumentos puramente imaginários. 3 BESSELJ BESSELJ Retorna a função de Bessel. 3 BESSELK BESSELK Retorna a função de Bessel modificada, que equivale às funções de Bessel avaliadas por argumentos puramente imaginários. 3 BESSELY BESSELY Retorna a função de Bessel, também chamada de função de Weber ou de Neumann. 3 BINADEC BIN2DEC Converte um número binário em decimal. 3 BINAHEX BIN2HEX Converte um número binário em hexadecimal. 3 BINAOCT BIN2OCT Converte um número binário em octal. 3 COMPLEXO COMPLEX Converte coeficientes reais e imaginários em números complexos no formato x + yi ou x + yj. C U R SO D E EX C EL A V A N Ç A D O 38 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 3 CONVERTER CONVERT Converte um número de um sistema de medida para outro. Por exemplo, Convert pode traduzir uma tabela de distâncias em milhas para uma tabela de distâncias em quilômetros. 3 DECABIN DEC2BIN Converte um número decimal em binário. 3 DECAHEX DEC2HEX Converte um número decimal em hexadecimal. 3 DECAOCT DEC2OCT Converte um número decimal em octal. 3 DEGRAU GESTEP Retorna 1 se núm = passo; caso contrário, retornará 0. Use esta função para filtrar um conjunto de valores. Por exemplo, somando várias funções DEGRAU é possível calcular a quantidade de valores que excedem um limite. 3 DELTA DELTA Testa se dois valores são iguais. Retorna 1 se núm1= núm2; caso contrário, retornará 0. 3 FUNERRO ERF Retorna a função de erro integrada entre limite_inferior e limite_superior. 3 FUNERROCOMPL ERFC Retorna a função integrada complementar FUNERRO entre o parâmetro especificado e o infinito. 3 HEXABIN HEX2BIN Converte um número hexadecimal em binário. 3 HEXADEC HEX2DEC Converte um número hexadecimal em decimal. 3 HEXAOCT HEX2OCT Converte um número hexadecimal em octal. 3 IMABS IMABS Retorna o valor absoluto (módulo) de um número complexo no formato de texto x + yi ou x + yj. 3 IMAGINÁRIO IMAGINARY Retorna o coeficiente imaginário de um número complexo no formato de texto x + yi ou x + yj. C U R SO D E EX C EL A V A N Ç A D O 39 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 3 IMARG IMARGUMENT Retorna o argumento Beta(Beta), um ângulo expresso em radianos, de modo que: Equação 3 IMCONJ IMCONJUGATE Retorna o conjugado complexo de um número complexo no formato de texto x + yi ou x + yj. 3 IMCOS IMCOS Retorna o cosseno de um número complexo no formato de texto x + yi ou x + yj. 3 IMDIV IMDIV Retorna o quociente de dois números complexos no formato de texto x + yi ou x + yj. 3 IMEXP IMEXP Retorna o exponencial de um número complexo no formato de texto x + yi ou x + yj. 3 IMLN IMLN Retorna o logaritmo natural de um número complexo no formato de texto x + yi ou x + yj. 3 IMLOG10 IMLOG10 Retorna o logaritmo comum (base 10) de um número complexo no formato de texto x + yi ou x + yj. 3 IMLOG2 IMLOG2 Retorna o logaritmo de base 2 de um número complexo no formato de texto x + yi ou x + yj. 3 IMPOT IMPOWER Retorna o número complexo no formato de texto x + yi ou x + yj, elevado a uma potência. 3 IMPROD IMPRODUCT Retorna o produto de 2 a 29 números complexos no formato de texto x + yi ou x + yj. 3 IMRAIZ IMSQRT Retorna a raiz quadrada de um número complexo no formato de texto x + yi ou x + yj. 3 IMREAL IMREAL Retorna o coeficiente real de um número complexo no formato de texto x + yi ou x + yj. 3 IMSENO IMSIN Retorna o seno de um número complexo no formato de texto x + yi ou x + yj. 3 IMSOMA IMSUM Retorna a soma de dois ou mais números complexos no formato de texto x + yi ou x + yj . 3 IMSUBTR IMSUB Retorna a diferença entre dois números complexos no formato de texto x + yi ou x + yj. 3 OCTABIN OCT2BIN Converte um número octal em binário. 3 OCTADEC OCT2DEC Converte um número octal em decimal. 3 OCTAHEX OCT2HEX Converte um número octal em hexadecimal. C U R SO D E EX C EL A V A N Ç A D O 40 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 BETA.ACUM.INV BETAINV Retorna o inverso da função de distribuição cumulativa para uma distribuição beta especificada. Ou seja, se probabilidade = DistBeta(x,…), Beta.Acum.Inv(probabilidade,…) = x. 4 CONT.SE COUNTIF Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios. 4 CONT.SES COUNTIFS Calcula o número de células não vazias em um intervalo que correspondem a múltiplos critérios. 4 CONT.VALORES COUNTA Calcula o número de células não vazias e os valores na lista de argumentos. 4 CONTAR.VAZIO COUNTBLANK Conta o número de células vazias no intervalo especificado. 4 CONTNÚM COUNT Conta quantas células contêm números e conta os números na lista de argumentos. 4 CORREL CORREL Retorna o coeficiente de correlação dos intervalos de célula Arg1 e Arg2. 4 COVAR COVAR Retorna a covariância, a média dos produtos dos desvios para cada par de pontos de dados. 4 CRESCIMENTO GROWTH Calcula o crescimento exponencial previsto usando dados existentes.CRESCIMENTO retorna os valores y para uma série de novos valores x que você especifica usando valores x e y existentes. Você também pode usar a função de planilha CRESCIMENTO para ajustar uma curva exponencial em valores x e y. 4 CRIT.BINOM CRITBINOM Retorna o menor valor para o qual a distribuição binomial cumulativa é maior ou igual ao valor padrão. 4 CURT KURT Retorna a curtose de um conjunto de dados. A curtose caracteriza a capacidade relativa de pico ou de nivelamento de uma distribuição comparada com a distribuição normal. Curtose positiva indica uma distribuição relativamente de pico. Curtose negativa indica uma distribuição relativamente nivelada. 4 DESV.MÉDIO AVEDEV Retorna a média dos desvios absolutos de pontos de dados com relação a sua média. Desv.Médio é uma medida da variabilidade em um conjunto de dados. C U R SO D E EX C EL A V A N Ç A D O 41 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 DESVPAD STDEV Estima o desvio padrão com base em uma amostra. O desvio padrão é uma medida de quanto os valores estão dispersos em relação ao valor médio (a média). 4 DESVPADP STDEVP Calcula o desvio padrão com base na população inteira fornecida como argumentos. O desvio padrão é uma medida de quanto os valores estão dispersos em relação ao valor médio (a média). 4 DESVQ DEVSQ Retorna a soma dos quadrados dos desvios de pontos de dados da média da amostra. 4 DIST.BIN.NEG NEGBINOMDIST Retorna a distribuição binomial negativa. DIST.BIN.NEG retorna a probabilidade de haver núm_f falhas antes do núm_s êxito, quando a probabilidade constante de um êxito é probabilidade_s. Esta função é semelhante à distribuição binomial, a diferença é que o número de êxitos é fixo e o número de tentativas é variável. Como o binomial, as tentativas são consideradas independentes. 4 DIST.HIPERGEOM HYPGEOMDIST Retorna a distribuição hipergeométrica. DIST.HIPERGEOM retorna a probabilidade de um determinado número de sucessos de uma amostra, de acordo com o tamanho da amostra, sucessos da população e tamanho da população. Use DIST.HIPERGEOM para problemas com uma população finita, onde cada observação seja equivalente a um sucesso ou a um fracasso, e onde cada subconjunto de um determinado tamanho seja escolhido com igual probabilidade. 4 DIST.LOGNORMAL LOGNORMDIST Retorna a distribuição lognormal de x, onde In(x) é normalmente distribuído com média de parâmetros e desv_padrão. Use essa função para analisar dados que foram transformados como logaritmos. C U R SO D E EX C EL A V A N Ç A D O 42 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 DIST.NORM NORMDIST Retorna a distribuição normal para a média e o desvio padrão especificado. Esta função é bastante aplicada em estatísticas, incluindo testes de hipóteses. 4 DIST.NORMP NORMSDIST Retorna a função de distribuição cumulativa normal padrão. A distribuição tem uma média igual a 0 (zero) e um desvio padrão de um. Use esta função no lugar de uma tabela de áreas de curva normal padrão. 4 DIST.QUI CHIDIST Retorna a probabilidade unicausal da distribuição qui-quadrada. 4 DISTBETA BETADIST Retorna a função de distribuição cumulativa beta. 4 DISTEXPON EXPONDIST Retorna a distribuição exponencial. Use DISTEXPON para criar um modelo do tempo entre os eventos, como quanto tempo determinado caixa eletrônico leva para liberar o dinheiro. Por exemplo, você pode usar DISTEXPON para determinar a probabilidade de que o processo leve no máximo um minuto. 4 DISTF FDIST Retorna a distribuição de probabilidade F. Você pode usar esta função para determinar se dois conjuntos de dados têm graus de diversidade diferentes. Por exemplo, é possível examinar os resultados dos testes de homens e mulheres que ingressam no 2º grau e determinar se a variabilidade entre as mulheres é diferente daquela encontrada entre os homens. 4 DISTGAMA GAMMADIST Retorna a distribuição gama. Use esta função para estudar variáveis que podem ter uma distribuição inclinada. A distribuição gama costuma ser usada na análise de enfileiramento de mensagens. C U R SO D E EX C EL A V A N Ç A D O 43 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 DISTORÇÃO SKEW Retorna a distorção de uma distribuição. O valor enviesado caracteriza o grau de assimetria de uma distribuição em torno de sua média. Um valor enviesado positivo indica uma distribuição com uma ponta assimétrica que se estende em direção a valores mais positivos. Um valor enviesado negativo indica uma distribuição com uma ponta assimétrica que se estende em direção a valores mais negativos. 4 DISTRBINOM BINOMDIST Retorna a probabilidade de distribuição binomial do termo individual. 4 DISTT TDIST Retorna os pontos percentuais (probabilidade) para a distribuição t de Student, onde o valor numérico (x) é um valor calculado de t para o qual os pontos percentuais devem ser computados. A distribuição t é usada no teste de hipóteses de pequenos conjuntos de dados de amostras. Use esta função em vez de uma tabela de valores críticos para a distribuição t. 4 EPADYX STEYX Retorna o erro padrão do valor-y previsto para cada x da regressão. O erro padrão é uma medida da quantidade de erro na previsão de y para um x individual. 4 FISHER FISHER Retorna a transformação Fisher em x. Essa transformação produz uma função que é normalmente distribuída em vez de distorcida. Use esta função para executar testes de hipóteses no coeficiente de correlação. 4 FISHERINV FISHERINV Retorna o inverso da transformação Fisher. Use esta transformação ao analisar correlações entre intervalos ou matrizes de dados. Se y = FISHER(x), então FISHERINV(y) = x. 4 FREQÜÊNCIA FREQUENCY Calcula a frequência com que valores ocorrem em um intervalo de valores e, em seguida, retorna uma matriz vertical de números. Por exemplo, use FREQUÊNCIA para contar o número de notas de testes dentro de intervalos de notas. Pelo fato de FREQUÊNCIA retornar uma matriz, deve ser inserido como uma fórmula matricial. C U R SO D E EX C EL A V A N Ç A D O 44 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 INCLINAÇÃO SLOPE Retorna a inclinação da linha de regressão linear através de pontos de dados em val_conhecidos_y e val_conhecidos_x. A inclinação é a distância vertical dividida pela distância horizontal entre dois pontos quaisquer na linha, que é a taxa de mudança ao longo da linha de regressão. 4 INT.CONFIANÇA CONFIDENCE Retorna um valor que você pode usar para construir um intervalo de confiança para uma média da população. 4 INTERCEPÇÃO INTERCEPT Calcula o ponto no qual uma linha irá interceptar o eixo y usando valores de x e y existentes. O ponto de interseção é baseado em uma linha de regressão de melhor ajuste plotada pelos valores de x e y conhecidos. Use a função INTERCEPÇÃO quando quiser determinar o valor da variável dependente e a variável independente for 0 (zero). Por exemplo, você pode usar a função INTERCEPÇÃO para prever a resistência elétrica de um metal a 0°C quando os pontos de dados forem medidos em temperatura ambiente ou mais elevados. 4 INV.NORM NORMINV Retorna o inverso da distribuição cumulativa normal para a média especificada e o desvio padrão. 4 INV.NORMP NORMSINV Retorna o inverso da distribuição padrão cumulativa normal. A distribuição tem uma média de zero e um desvio padrão de um. 4 INV.QUI CHIINV Retorna o inverso da probabilidade unicaudal da distribuição qui-quadrada. 4 INVF FINV Retorna o inverso da distribuição de probabilidades F. Se p = DISTF(x,…), então INVF(p,…) = x. 4 INVGAMAGAMMAINV Retorna o inverso da distribuição cumulativa gama. Se p = DISTGAMA(x;…), então INVGAMA(p;…) = x. C U R SO D E EX C EL A V A N Ç A D O 45 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 INVLOG LOGINV Use a distribuição lognormal para analisar dados transformados logaritmicamente. 4 INVT TINV Retorna o valor t da distribuição t de Student como uma função da probabilidade e dos graus de liberdade. 4 LNGAMA GAMMALN Retorna o logaritmo natural da função gama, G(x). 4 MAIOR LARGE Retorna o k-ésimo maior valor em um conjunto de dados. Você pode usar essa função para selecionar um valor com base em sua posição relativa. Por exemplo, você pode usar MAIOR para retornar a maior pontuação ou o terceiro lugar. 4 MÁXIMO MAX Retorna o maior valor em um conjunto de valores. 4 MED MEDIAN Retorna o mediano dos números fornecidos. O mediano é o número no meio de um conjunto de números. 4 MÉDIA AVERAGE Retorna a média aritmética dos argumentos. 4 MÉDIA.GEOMÉTRICA GEOMEAN Retorna a média geométrica de uma matriz ou de um intervalo de dados positivos. Por exemplo, você pode usar MÉDIA.GEOMÉTRICA para calcular o crescimento médio considerando-se juros compostos com taxas variáveis. 4 MÉDIA.HARMÔNICA HARMEAN Retorna a média harmônica de um conjunto de dados. A média harmônica é a recíproca da média aritmética das recíprocas. 4 MÉDIA.INTERNA TRIMMEAN Retorna a média do interior de um conjunto de dados. MÉDIA.INTERNA calcula a média obtida excluindo-se uma porcentagem dos pontos de dados das pontas superior e inferior de um conjunto de dados. Você pode usar esta função quando quiser excluir dados externos à sua análise. C U R SO D E EX C EL A V A N Ç A D O 46 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 MÉDIASE AVERAGEIF Retorna a média (aritmética) de todas as células em um intervalo que satisfazem um determinado critério. 4 MÉDIASES AVERAGEIFS Retorna a média (aritmética) de todas as células que satisfazem múltiplos critérios. 4 MENOR SMALL Retorna o k-ésimo menor valor do conjunto de dados. Use esta função para retornar valores com uma posição específica relativa em um conjunto de dados. 4 MÍNIMO MIN Retorna o menor número em um conjunto de valores. 4 MODO MODE Retorna o valor que ocorre com mais frequência, ou repetitivo, em uma matriz ou intervalo de dados. 4 ORDEM RANK Retorna a posição de um número em uma lista de números. A ordem de um número é seu tamanho em relação a outros valores de uma lista. (Se você fosse classificar a lista, a ordem do número seria a sua posição). 4 ORDEM.PORCENTUA L PERCENTRANK Retorna a ordem de um valor em um conjunto de dados como uma porcentagem do conjunto de dados. Essa função pode ser usada para avaliar a posição relativa de um valor em um conjunto de dados. Por exemplo, você pode usar ORDEM.PORCENTUAL para avaliar a posição de uma nota em um teste de aptidão entre todas as notas do teste. 4 PADRONIZAR STANDARDIZE Retorna um valor normalizado de uma distribuição caracterizada por média e desv_padrão. 4 PEARSON PEARSON Retorna o coeficiente de correlação do momento do produto Pearson, r, um índice sem dimensão que varia de -1,0 a 1,0 inclusive e reflete a extensão de uma relação linear entre dois conjuntos de dados. C U R SO D E EX C EL A V A N Ç A D O 47 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 PERCENTIL PERCENTILE Retorna o percentil k-ésimo dos valores de um intervalo. Você pode usar esta função para estabelecer um limite de aceitação. Por exemplo, pode decidir examinar candidatos com notas superiores ao percentil 90º. 4 PERMUT PERMUT Retorna o número de permutas de um determinado número de objetos que podem ser selecionadas a partir do número de objetos. Uma permuta é qualquer conjunto ou subconjunto de objetos ou eventos em que a ordem interna é significativa. Permutas são diferentes de combinações, nas quais a ordem interna não é significativa. Use esta função para cálculos de probabilidade no estilo de loteria. 4 POISSON POISSON Retorna a distribuição Poisson. Uma aplicação comum da distribuição Poisson é prever o número de eventos em um período específico, como o número de carros que passam por um pedágio por minuto. 4 PREVISÃO FORECAST Calcula, ou prevê um valor futuro usando valores existentes. O valor previsto é um valor de y para um determinado valor de x. Os valores conhecidos são valores de x e de y existentes, e o novo valor é previsto através da regressão linear. Você pode usar esta função para fazer previsões de vendas futuras, inventariar os recursos necessários ou analisar tendências de consumo. 4 PROB PROB Retorna a probabilidade de os valores de um intervalo estar entre dois limites. Se limite_superior não for fornecido, retornará a probabilidade dos valores do intervalo_x serem iguais ao limite_inferior. C U R SO D E EX C EL A V A N Ç A D O 48 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 PROJ.LIN LINEST Calcula as estatísticas de uma linha usando o método “quadrados menores” para calcular uma linha reta que melhor se adapte aos seus dados e retorna uma matriz que descreve a linha. Como essa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula matricial. 4 PROJ.LOG LOGEST Na análise de regressão, calcula uma curva exponencial adequada aos dados e retorna uma matriz de valores que descrevem a curva. Como essa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula matricial. 4 QUARTIL QUARTILE Retorna o quartil do conjunto de dados. Quartis são comumente usados em dados de vendas e de pesquisas para dividir a população em grupos. Por exemplo, você pode usar QUARTIL para descobrir a faixa de 25% maiores rendas em uma população. 4 RQUAD RSQ Retorna o quadrado do coeficiente de correlação do momento do produto de Pearson através dos pontos de dados em val_conhecidos_y e val_conhecidos_x. Para obter mais informações, consulte PEARSON. O valor r ao quadrado pode ser interpretado como a proporção da variância em y que pode ser atribuída à variância em x. 4 TENDÊNCIA TREND Retorna valores em uma tendência linear. Ajusta uma linha (usando o método de quadrados mínimos) às matrizes val_conhecidos_y e val_conhecidos_x. Retorna os valores y nessa linha para a matriz de novos_valores_x que você especificar. 4 TESTE.QUI CHITEST Retorna o teste para independência. 4 TESTEF FTEST Retorna o resultado de um teste F. Um teste F retorna a probabilidade bicaudal de que as variâncias em matriz1 e matriz2 não sejam significativamente diferentes. Use esta função para determinar se duas amostras têm variâncias diferentes. Por exemplo, considerando resultados de testes de escolas particulares e públicas, você pode testar se essas escolas têm níveis diferentes de diversidade de pontuação de teste. C U R SO D E EX C EL A V A N Ç A D O 49 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 4 TESTET TTEST Retorna a probabilidade associada ao teste t de Student. Use TESTET para determinar se duas amostras poderão ser provenientes de duas populações subjacentes que possuem a mesma média. 4 TESTEZ ZTEST Retorna o valor de probabilidade unicaudal de um teste-z. Para uma média de população hipotética, TESTEZ retorna a probabilidade de que a média da população seja maior que a média de observações no conjunto de dados (matriz) — ou seja, a média da amostra. 4 VAR VAR Estima a variância com base em uma amostra. 4 VARP VARP Calcula a variância com base na populaçãointeira. 4 WEIBULL WEIBULL Retorna a distribuição Weibull. Use esta distribuição na análise de confiabilidade, como no cálculo do tempo médio de falha para determinado dispositivo. 5 AMORDEGRC AMORDEGRC Retorna a depreciação para cada período contábil. Esta função é fornecida para o sistema contábil francês. 5 AMORLINC AMORLINC Retorna a depreciação para cada período contábil. Esta função é fornecida para o sistema contábil francês. 5 BD DB Retorna a depreciação de um ativo para um período especificado, usando o método de balanço de declínio fixo. 5 BDD DDB Retorna a depreciação de um ativo com relação a um período especificado usando o método de saldos decrescentes duplos ou qualquer outro método especificado por você. 5 BDV VDB Retorna a depreciação de um ativo para o período que você especificar, incluindo período parcial, usando o método balanço declinante duplo ou algum outro método especificado. BDV é o balanço de declínio variável. C U R SO D E EX C EL A V A N Ç A D O 50 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 5 CUPDATAANT COUPPCD Retorna a data de cupom anterior à data de quitação 5 CUPDATAPRÓX COUPNCD Retorna um número que representa a próxima data de cupom após a data de liquidação. 5 CUPDIAS COUPDAYS Retorna o número de dias no período de cupom que contém a data de liquidação. 5 CUPDIASINLIQ COUPDAYBS Retorna o número de dias do início do período de cupom até a data de liquidação. 5 CUPDIASPRÓX COUPDAYSNC Retorna o número de dias da data de liquidação até a data do próximo cupom. 5 CUPNÚM COUPNUM Retorna o número de cupons pagáveis entre a data de liquidação e a data de vencimento, arredondado para o próximo cupom inteiro. 5 DESC DISC Retorna a taxa de desconto de um título. 5 DPD SLN Retorna a depreciação em linha reta de um ativo durante um período. 5 DURAÇÃO DURATION Retorna a duração de Macauley para um valor nominal assumido de R$ 100. A duração é definida como a média ponderada do valor presente do fluxo de caixa, e é usada para calcular a resposta do preço de um título a alterações no rendimento. 5 EFETIVA EFFECT Retorna a taxa de juros anual efetiva, dados a taxa de juros anual nominal e o número de períodos compostos por ano. 5 ÉPGTO ISPMT Calcula os juros pagos durante um período específico de um investimento. Essa função é fornecida para que haja compatibilidade com o Lotus 1-2-3. 5 IPGTO IPMT Retorna o pagamento de juros para um determinado período de investimento de acordo com pagamentos periódicos e constantes e com uma taxa de juros constante. 5 JUROSACUM ACCRINT Retorna juros acumulados de um título que paga juros periódicos. C U R SO D E EX C EL A V A N Ç A D O 51 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 5 JUROSACUMV ACCRINTM Retorna juros acumulados de um título que paga juros no vencimento. 5 LUCRODESC YIELDDISC Retorna o rendimento anual de um título descontado. 5 LUCROPRIMINC ODDFYIELD Retorna o rendimento de um título com um período inicial incompleto (curto ou longo). 5 LUCROÚLTINC ODDLYIELD Retorna o rendimento de um título com um período final incompleto (curto ou longo). 5 LUCROVENC YIELDMAT Retorna o rendimento anual de um título que paga juros no vencimento. 5 MDURAÇÃO MDURATION Retorna a duração modificada Macauley de um título com um valor par presumido de R$100. 5 MOEDADEC DOLLARDE Converte um preço em formato de moeda, na forma fracionária, em um preço na forma decimal. Use MOEDADEC para converter preços na forma fracionária, como preços de seguros, em decimais. 5 MOEDAFRA DOLLARFR Converte um preço, apresentado na forma decimal, em um preço apresentado na forma fracionária. Use MOEDAFRA para converter preços na forma decimal em frações, como preços de seguros. 5 MTIR MIRR Retorna a taxa interna modificada de retorno de uma série de fluxos de caixa periódicos. MTIR considera o custo do investimento e os juros recebidos com o reinvestimento do caixa. 5 NOMINAL NOMINAL Retorna a taxa de juros anual nominal, dados a taxa efetiva e o número de períodos compostos por ano. 5 NPER NPER Retorna o número de períodos de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante. 5 OTN TBILLEQ Retorna o rendimento de um título equivalente a uma obrigação do Tesouro. C U R SO D E EX C EL A V A N Ç A D O 52 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 5 OTNLUCRO TBILLYIELD Retorna o rendimento de uma obrigação do Tesouro. 5 OTNVALOR TBILLPRICE Retorna o preço por R$ 100,00 de valor nominal de uma obrigação do Tesouro. 5 PGTO PMT Calcula o pagamento de um empréstimo com base em pagamentos constantes e em uma taxa de juros constante. 5 PGTOCAPACUM CUMPRINC Retorna o capital acumulado pago sobre um empréstimo entre início_período e final_período. 5 PGTOJURACUM CUMIPMT Retorna os juros acumulados pagos por um empréstimo entre início_período e final_período. 5 PPGTO PPMT Retorna o pagamento sobre o principal para um determinado período de um investimento baseado em pagamentos periódicos e constantes e em uma taxa de juros constante. 5 PREÇO PRICE Retorna o preço pelo valor nominal R$100 de um título que paga juros periódicos. 5 PREÇODESC PRICEDISC Retorna o preço por valor nominal de R$ 100,00 de um título descontado. 5 PREÇOPRIMINC ODDFPRICE Retorna o preço por valor nominal de R$ 100 de um título que tem um primeiro período incompleto (curto ou longo). 5 PREÇOÚLTINC ODDLPRICE Retorna o preço por valor nominal de R$ 100 de um título que tem um último período de cupom incompleto (curto ou longo). 5 PREÇOVENC PRICEMAT Retorna o preço pelo valor nominal R$100 de um título que paga juros no vencimento. 5 RECEBER RECEIVED Retorna a quantia recebida no vencimento de um título totalmente investido. 5 SDA SYD Retorna a depreciação dos dígitos da soma dos anos de um ativo para um período especificado. C U R SO D E EX C EL A V A N Ç A D O 53 Tipo Função (Português) Função (Inglês) DESCRIÇÃO (da Função no Excel) 5 TAXA RATE Retorna a taxa de juros por período de uma anuidade. TAXA é calculada por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM. 5 TAXAJUROS INTRATE Retorna a taxa de juros de um título totalmente investido. 5 TIR IRR Retorna a taxa interna de retorno de uma sequência de fluxos de caixa representada pelos números em valores. Estes fluxos de caixa não precisam ser iguais como no caso de uma anuidade. Entretanto, os fluxos de caixa devem ser feitos em intervalos regulares, como mensalmente ou anualmente. A taxa interna de retorno é a taxa de juros recebida para um investimento que consiste em pagamentos (valores negativos) e receitas (valores positivos) que ocorrem em períodos regulares. 5 VF FV Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante. 5 VFPLANO FVSCHEDULE Retorna o valor futuro de um capital inicial após a aplicação de uma série de taxas de juros compostos. Use VFPLANO para calcular o valor futuro de um investimento com uma taxa variável ou ajustável. 5 VP PV Retorna o valor presente de um investimento. O valor presente é o valor total de uma série de pagamentos futuros. Por exemplo, quando você empresta dinheiro, o valor do empréstimo é o valor presente para o credor. 5 VPL NPV Calcula o valor líquido atual de um investimento usando uma taxa de desconto e uma série de pagamentos futuros (valores negativos)
Compartilhar