Baixe o app para aproveitar ainda mais
Prévia do material em texto
BÍBLIA DOBÍBLIA DO EXCELEXCEL GABRIEL VENTURINI DE OLIVEIRA UM DOS MELHORES E-BOOK DIDÁTICOS PARA SE TORNAR UM EXPERT NO EXCEL. APRENDENDO DO ZERO COM QUEM ESTÁ EXECUTANDO A FERRAMENTA NA PRÁTICA. NOVA EDIÇÃO ATUALIZADA BÍBLIA DO EXCEL GABRIEL VENTURINI DE OLIVEIRA DO BÁSICO AO AVANÇADO NOTA EDITORIAL A bíblia do excel foi lançado em 2020 e desde então é reeditado com novos conteúdos em suas versões atualizadas. MICROSOFT EXCEL O Microsoft Office Excel, conhecido também por Office Excel ou somente Excel, é um software da Microsoft. É um programa que gera planilhas, o que antigamente só era feito por papel. Este programa foi lançado em 1985, para a plataforma MAC, da Apple. O programa foi criado para competir com o Lótus 1-2-3, que estava ganhando espaço no mercado. A primeira versão para Windows saiu em 1987, e após este ano, a Microsoft lançou varias novas versões do Excel, em geral a cada dois anos uma nova versão. Quando lançado, o Excel foi alvo de um processo bem polêmico, pois já existia um pacote de softwares com este nome, e já estava registrada a patente. Ficou decidido em julgamento que a Microsoft teria de se referir ao programa como “Microsoft Excel”. Esta decisão foi respeitada por muito pouco tempo pela Microsoft. A empresa resolveu o conflito alguns anos depois, quando comprou a patente de nome “Excel”. O Excel revolucionou as planilhas eletrônicas, e avançou muito mais rápido que a concorrência. O programa ofereceu muitas alterações na interface do usuário, porem, continuou com o padrão de organização em linhas e colunas. O Excel foi o primeiro a permitir modificação de fonte, cor, tamanho, tudo com facilidade para o usuário. Após 2003, o Excel implantou a VBA (Visual Basic for Applications), linguagem de programação criada com base em Visual Basic, e que ajudou as automatizar algumas funções do programa. A VBA trouxe um serio problema, a sussetividade do Excel para vírus de Macro. Este problema foi resolvido quando os antivírus passaram a detectar estes vírus.O Excel permite fazer cálculos matemáticos avançados nas planilhas, este função popularizou ainda mais o programa. MICROSOFT EXCEL Planilhas semelhantes as do Excel servem como base para bancos de dados. É possível adicionar dados a um banco de dados a partir de uma planilha do Excel. O programa também é bastante usado para armazenar dados de compras e vendas de produtos, custos e orçamentos familiares. No Excel 2007, assim como nas outras versões, quando se abre um novo arquivo abrem-se três planilhas (Cada planilha tem 16.384 colunas e 1.048.576 linhas.) Estas fazem parte do mesmo arquivo do programa. Caso alguém fosse preencher todas as linhas e colunas com o Maximo de caracteres possível, o arquivo resultante teria aproximadamente 9.223.090.561.878.070.000 de bytes. É muito difícil alguém chegar a tanto, mas as grandes empresas podem usar a vontade o programa, sem medo de chegar no limite. SUMÁRIO MÓDULO BÁSICO .............................................................................................. 4 MICROSOFT EXCEL ...............................................................................................................4 ALINHAMENTO DE MINDSET .........................................................................................9 INTRODUÇÃO ..........................................................................................................................10 ÁREA DE TRABALHO ...........................................................................................................11 BARRA DE STATUS ...............................................................................................................12 RECORTAR ................................................................................................................................13 PINCEL DE FORMATAÇÃO ................................................................................................14 COLAR .........................................................................................................................................15 FONTE DE CONFIGURAÇÃO .............................................................................................16 CONFIGURAÇÃO DE ALINHAMENTO ..........................................................................17 LOCALIZAR E SELECIONAR ..............................................................................................18 MESCLAR CÉLULAS ..............................................................................................................20 CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO ......................22 CRIANDO, RENOMEANDO E EXCLUINDO ABAS .....................................................28 CÉLULAS, COLUNAS E LINHAS .......................................................................................32 OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS ...............................................34 ALTERANDO ALTURA DE LINHAS E LARGURA DE COLUNAS ........................40 CONGELANDO E DESCONGELANDO LINHAS E COLUNAS ................................44 + MENUS NO EXCEL .............................................................................................................48 PROTEGER COM SENHA SUA PLANILHA ..................................................................51 DICAS BÁSICAS .......................................................................................................................53 FORMATAÇÃO DE CÉLULAS ............................................................................................54 PREENCHIMENTO AUTOMÁTICO DE CÉLULAS .....................................................57 INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS ....................................59 SALVANDO UM ARQUIVO EM PDF ................................................................................66 SINAL DE IGUAL (=) .............................................................................................................68 QUADRO DE FÓRMULAS BÁSICAS ................................................................................69 CRIAÇÃO E DESIGN DE TABELAS ..................................................................................71 HIPERLINK ...............................................................................................................................76 MÓDULO INTERMEDIÁRIO ............................................................................ 86 ÍNDICE CORRESP ...................................................................................................................86 FUNÇÃO MODO .......................................................................................................................92 FUNÇÃO CONTAR NÚMEROS ..........................................................................................93 FUNÇÃO MÁXIMO .................................................................................................................94 FUNÇÃO MÍNIMO ..................................................................................................................95 FUNÇÃO CONT.VALORES ..................................................................................................96 FUNÇÃO CONT.VAZIO .........................................................................................................97 FUNÇÃO DA CONDIÇÃO SE ...............................................................................................98 FUNÇÃO DA CONDIÇÃO E .................................................................................................99 FUNÇÃO DA CONDIÇÃO OU ..............................................................................................100 FUNÇÃO DA CONDIÇÃO SOMA .......................................................................................101 FUNÇÃO DA CONDIÇÃO MÉDIA .....................................................................................102FUNÇÃO DA CONDIÇÃO DATA E HORA ......................................................................103 FUNÇÃO DA CONDIÇÃO & .................................................................................................104 FUNÇÃO DA CONDIÇÃO ROMANO ................................................................................105 FUNÇÃO DA CONDIÇÃO PROCH .....................................................................................106 FUNÇÃO DA CONDIÇÃO SEERRO ..................................................................................108 FUNÇÃO DA CONDIÇÃO BANCO DE DADOS .............................................................111 FUNÇÃO DA CONDIÇÃO PROCV .....................................................................................116 TABELAS DINÂMICAS .........................................................................................................121 FUNÇÕES FINANCEIRAS ....................................................................................................129 MALA DIRETA .........................................................................................................................140 GRÁFICO DINÂMICO ............................................................................................................147 TIPOS DE GRÁFICO ...............................................................................................................154 MÓDULO AVANÇADO ...................................................................................... 162 PERSONALIZAÇÃO DE FORMULÁRIOS .......................................................................162 GRÁFICO DE GANTT .............................................................................................................172 CRIANDO UM DASHBOARD ..............................................................................................177 DASHBOARD EXEMPLO 2 .................................................................................................210 GRÁFICO DE VELOCÍMETRO ...........................................................................................222 ADICIONAR BOTÃO DE MARCRO ..................................................................................240 CALENDÁRIO AUTOMÁTICO ...........................................................................................242 CONTROLE DE ESTOQUE ..................................................................................................245 PLANILHA DE ANÁLISE SWOT .......................................................................................247 GRÁFICO META X REALIZADO .......................................................................................261 MÓDULO BÁSICO 1 Resumo O módulo básico traz uma gama imensa de informação tratando a área de trabalho da ferramenta, contas básicas, inserção de imagens, formas, ícones e Webdings, criação e design de tabelas, utilização de links no aplicativo e sites, tudo que você deve saber da área de trabalho e suas utilidades. Neste exato momento, você com certeza tem algo a resolver no seu trabalho, nos negócios pessoais ou profissionais. Posso garantir que tem uma pilha de coisas a fazer, pessoas, empresas para entrar em contato, mestas a cumprir, cobranças diárias. Mesmo com tudo isso, você resolveu comprar este e-book. O motivo? Acredito que cada pessoa tem seu objetivo próprio, mas o que tem mais em comum com quem deseja aprender utilizar o Excel, assim como eu, você também sente a pressão diária de encontrar soluções que gerem resultados para os outros e para si mesmo. Então terá dias que você não terá vontade de mexer na ferramenta, assim como eu não tive por muito tempo, até tomar uma decisão sem volta, executar ela por inteira, e foi a melhor decisão, pois consegui resolver vários problemas na minha vida pessoal e profissional. Não importa qual a nossa posição profissional, todos somos vendedores e podemos dominar a arte das vendas com o Excel, pois a final, ele nos ensina a definir o foco, pensar maneiras de executar as mais dificieis das estratégias de forma simples, assim criar possibilidades para alavancar a nossa carreira profissional. Então não adiei mais o seu aprendizado e torne-se o profissional que você sempre quis ser. #boraexecutar BÍBLIA DO EXCEL I MÓDULO BÁSICO9 Alinhamento de Mindset ALINHAMENTO DE MINDSET Como de praxe de todos os e-books tem muita teoria para você ler e reler. Porém iremos focar no que você pode evoluir e isso você irá conseguir somente a partir da prática! Então não iremos apresentar parte por parte o menu da área de trabalho e sim, instigar a você mexer em toda a ferramenta, pois somente executando o aplicativo você irá aprender. Arrega-se as mangas e comece visualizando a ferramenta como um todo, clique em todos os botões que você tiver dúvida. Afinal é apenas um software, não irá ocorrer nenhum problema. Sugiro abrir um novo painel (caso você ja estiver utilizando para alguma atividade) e bora executar! A seguir irei apresentar a área de trabalho como um todo e faço um convite a você mexer em todos os botões que esse grande aplicativo nos fornece. BÍBLIA DO EXCEL I MÓDULO BÁSICO10 Introdução INTRODUÇÃO A área de trabalho nada mais é que a pagina inicial mostrada ao se abrir o aplicativo, a qual é composta por uma folha de cálculo em branco com as primeiras linhas e colunas: as linhas, que estão dispostas na horizontal, são unificadas por valores numéricos (1,2,3...); e as colunas, que estão na vertical, são unificadas em ordem alfabética (A,B,C...). Pode ser que seu layout é um pouco diferente, dependendo da versão do Excel). Mas não irá alterar em nada seu aprendizado neste e-book! 1 - Menu Arquivo 2 - Faixa de Opções 3 - Caixa de Nome 4 - Barra de Fórmulas 5 - Barra de Ferramentas de Acesso Rápido 6 - Eixo das Colunas 7 - Eixo das Linhas 8 - Planilhas de Trabalho 9 - Barra de Status 10 - Modos de Visualização 11 - Controle de Zoom 12 - Barras de Rolagem Como supracitei clique em todos os botões e visualize todas suas funções. BÍBLIA DO EXCEL I MÓDULO BÁSICO11 ÁREA DE TRABALHO Área de Trabalho A barra de status na parte inferior dos programas do Office exibe o status das opções selecionadas para serem exibidas na barra de status. Esta funcionalidade é bem útil para o dia a dia com questões relacionadas a contagem de números ou de células! BÍBLIA DO EXCEL I MÓDULO BÁSICO12 BARRA DE STATUS Barra de Status Selecione a área para visualizar o status de contagem Você pode usar o comando Recortar ou o comando copiar para mover ou copiar células, linhas e colunas selecionadas, mas também pode movê-las ou copiá-las usando o mouse. Neste exemplo estou recortando o valor da célula B4 e inserindo para a D4. BÍBLIA DO EXCEL I MÓDULO BÁSICO13 RECORTAR Recortar Selecione a célula B4 Clique no ícone "Tesoura" Clique com o botão direito do mouse e após em colar, ou simplesmente clique no teclado em Ctrl+V O pincel tem por finalidade copiar de forma rápida a formatação de um item do documento para o outro. Ele permite que sejam copiados estilos de formatação de textos e imagens com um simples clique na formatação de origem e por fim na formatação de destino. BÍBLIA DO EXCEL I MÓDULO BÁSICO14 PINCEL DE FORMATAÇÃO Pincel de Formatação Selecione a as células B4 a B6 Clique no ícone de pincel Selecione a área para realizar a formatação Resultado: Funciona parecido com o recorte, porém nesta você pode copiar células de um destino e colar no local desejado sem perder a origem do local. BÍBLIA DO EXCEL I MÓDULO BÁSICO15 COLAR Colar Clique em no ícone Colar Selecione a área que deseja colar as células copiadas Não perca tempo formatando fontes e demais formatações no Excel, é perfeitamente possível padronizar a formatação no Excel. Por padrão do aplicativo ela vem com a fonte Calibri. Como o próprio nome diz, aqui você irá configurar toda sua configuração de fonte, cor, tamanhoetc. Irá definir o layout do texto que irá utilizar. Tipo de Fonte Tamanho da Fonte Um tamanho acima Um tamanho abaixo Negrito Itálico Sublinhado Bordas Cor de Preenchimento Cor da Fonte BÍBLIA DO EXCEL I MÓDULO BÁSICO16 FONTE DE CONFIGURAÇÃO Fonte de Configuração Selecione a célula que contém os dados que você deseja que sejam alinhados e, em seguida, escolha entre as seguintes opções de alinhamento: Alinhamento vertical: clique em Alinhar Acima, ou Alinhar no Meio, ou Alinhar Abaixo. Alinhamento horizontal: clique em Alinhar à Esquerda, ou em Centralizar, ou Alinhar à Direita. Alinhar na parte inferior da célula Alinhar na parte central da célula Alinhar na parte superior da célula Alinhar texto à esquerda da célula Centralizar texto na célula Alinhar texto à direita da célula Diminuir margem entre borda e texto Aumentar margem entre borda e texto Orientação vertical ou diagonal do texto BÍBLIA DO EXCEL I MÓDULO BÁSICO17 CONFIGURAÇÃO DE ALINHAMENTO Configuração de Alinhamento Localizar e Selecionar Como o nome diz "Localiza" uma palavra-chave de sua tabela e a selecione. Exemplo: Localizar: Livros Substituir: Cadernos Fazendo essa funcionalidade você esta dizendo para o excel, localizar o arquivo todas as palavras que contém "livros" e substituir pela palavra "Caderno" Use o comando ir para para localizar rapidamente e selecionar todas as células que contenham tipos específicos de dados, como fórmulas. Além disso, use ir para para localizar apenas as células que atendem a critérios específicos, como a última célula da planilha que contém dados ou formatação. BÍBLIA DO EXCEL I MÓDULO BÁSICO18 Selecione a área que deseja localizar a palavra Clique em Localizar e Selecionar > Localizar LOCALIZAR E SELECIONAR BÍBLIA DO EXCEL I MÓDULO BÁSICO19 Na segunda aba em Substituir Em Localizar digite: livros Em Substituir por: cadernos Clique em Substituir tudo Resultado: Clique em OK LOCALIZAR E SELECIONAR A mesclagem combina duas ou mais células para criar uma nova célula maior. Essa é uma excelente maneira de criar um rótulo que se estende por várias colunas. A mescla de células é de extrema importante para montagem de uma tabela e seu layout, onde você pode centralizar textos, como no exemplo abaixo, verificar a quantidade de produtos vendidos em uma loja de moveis de madeira por semestre. Ele criara a chamada super-célula. Neste exemplo iremos mesclar o título do conjunto de dados "Período de Vendas de Móveis Usados" BÍBLIA DO EXCEL I MÓDULO BÁSICO20 Mesclar Células MESCLAR CÉLULAS BÍBLIA DO EXCEL I MÓDULO BÁSICO21 Selecione o tamanho da quantidade de células que você deseja mesclar Clique em Mesclar e Centralizar Resultado: MESCLAR CÉLULAS Primeiramente iremos criar um arquivo no excel. Digite em seu computador "Excel + Versão" BÍBLIA DO EXCEL I MÓDULO BÁSICO22 Criando, Salvando, Fechando e Abrindo um Arquivo Clique em Excel (a sua versão pode estar outro nome) CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO Resultado: BÍBLIA DO EXCEL I MÓDULO BÁSICO23 Para salvar um arquivo no Excel Clique em "Arquivo" CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO BÍBLIA DO EXCEL I MÓDULO BÁSICO24 Clique em "Salvar Como" Salve o documento no local desejado. Neste caso, estamos salvando na área de trabalho CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO BÍBLIA DO EXCEL I MÓDULO BÁSICO25 Para abrir um arquivo no Excel Clique em no Menu "Arquivo" Iremos abrir o arquivo que criamos como exemplo . Abrir o Excel na sua área de trabalho e clique no documento criado. Neste caso no "Salvando Arquivo" CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO Arquivo aberto conforme setado. BÍBLIA DO EXCEL I MÓDULO BÁSICO26 Resultado: CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO BÍBLIA DO EXCEL I MÓDULO BÁSICO27 Para fechar um arquivo no Excel Clique em no Menu "Arquivo" Iremos fechar o arquivo que criamos acima. Na aba lateral clique em "Fechar" CRIANDO, SALVANDO, FECHANDO E ABRINDO UM ARQUIVO BÍBLIA DO EXCEL I MÓDULO BÁSICO28 Primeiramente iremos criar uma nova "Aba" no Excel, existe diversas maneiras. Iremos demonstrar duas maneiras: Clique no ícone ( + ) Segunda maneira: Clique com o botão direito do mouse e em seguida "Inserir" Criando, Renomeando e Excluindo Abas CRIANDO, RENOMEANDO E EXCLUINDO ABAS BÍBLIA DO EXCEL I MÓDULO BÁSICO29 Clique em "Planilha" e após em "OK" irá ser inserido outra aba, conforme visualização abaixo. CRIANDO, RENOMEANDO E EXCLUINDO ABAS BÍBLIA DO EXCEL I MÓDULO BÁSICO30 Para renomear uma "Aba" no Excel. Clique com o botão direito do mouse e em seguida em "Renomear" Renomeie a aba conforme o desejado CRIANDO, RENOMEANDO E EXCLUINDO ABAS BÍBLIA DO EXCEL I MÓDULO BÁSICO31 Para excluir uma "Aba" no Excel. Clique com o botão direito do mouse e em seguida em "Excluir" Conforme setado a opção "Excluir" note abaixo que aba selecionada foi excluida. CRIANDO, RENOMEANDO E EXCLUINDO ABAS Na área de trabalho do Excel, possui três itens fundamentais para seu conhecimento, a partir do contato e sabendo distinguir a importância de cada, você poderá evoluir muito seu raciocínio junto ao aplicativo. A célula eu diria que é o principal elemento da planilha, pois nela você poderá acrescentar números, textos e diversos elementos, onde será a grande base para seus cálculos, o que tiver informado nele, será reproduzido como um "radar" para as demais funcionalidades de comando. Células, Colunas e Linhas BÍBLIA DO EXCEL I MÓDULO BÁSICO32 Células CÉLULAS, COLUNAS E LINHAS A coluna funciona como uma função que aprendemos no ensino médio. É como se fossemos dizer que a linha é o X e a coluna o Y. Serve como identificação de uma célula dentro de um conjunto de conteúdo. BÍBLIA DO EXCEL I MÓDULO BÁSICO33 Colunas Como o supracitado para coluna, serve para direcionar e identificar dados dentro de um conjunto de conteúdos, que a partir dele, pode-se inserir funções e direcionar onde esta localizado em um conteúdo de uma célula. Linhas CÉLULAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO34 Primeiramente iremos ocultar uma Aba no Excel. Clique com o botão direito do mouse em cima da aba que deseja ocultar e em seguida em "Ocultar" Após clicado em Ocultar, note que a aba não esta mais visível Ocultar e Reexibir Abas, Colunas e Linhas OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO35 Para reexibir a aba ocultada. Clique com o botão direito do mouse e em seguida em "Reexibir" Clique na aba que deseja reexibir e após em "OK" irá aparecer a aba ocultada OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO36 Para ocultar uma coluna. Clique com o botão direito do mouse em cima da coluna que deseja ocultar e em seguida em "Ocultar" Note que a coluna A foi ocultada. OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO37 Para reexibir a coluna ocultada Clique com o botão direito do mouse em cima da coluna que deseja reexibir e em seguida em "Re-exibir" Note que a coluna A tornou-se visível novamente. OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO38 Para ocultar uma linha. Clique com o botão direito do mouse em cima da linha que deseja ocultar e em seguida em "Ocultar" Note que a linha foi ocultada. OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO39 Para reexibir uma linha. Clique com o botão direito do mouse em cima da linha que deseja reexibir e em seguida em "Re-exibir" Note que a linha tornou-se visível novamente. OCULTAR E REEXIBIR ABAS, COLUNAS E LINHAS BÍBLIA DO EXCEL I MÓDULO BÁSICO40 Para alterar a altura da linha Existe diversas maneiras, neste caso, iremos demonstar 2 modos: Conforme imagem abaixo: Com o mouse clique na parte inferior da linha e arraste para baixo ou para cima. Resultado: Alterando Altura de Linhas e Largura de Colunas ALTERANDO ALTURA DE LINHAS E LARGURADE COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO41 Segunda maneira de alterar a altura Clique com o botão direito do mouse em cima da linha desejada e após em "Altura da linha" Em seguida informe o valor desejado e após clique em "OK" Resultado: ALTERANDO ALTURA DE LINHAS E LARGURA DE COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO42 Para alterar a largura da coluna Com o mouse clique na parte lateral da coluna e arraste para esquerda ou direita Resultado: ALTERANDO ALTURA DE LINHAS E LARGURA DE COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO43 Segunda maneira de alterar a largura da coluna Clique com o botão direito do mouse em cima da coluna desejada e após em "Largura da Coluna" Escolha a lagura desejada da coluna e depois clique em OK. Resultado: ALTERANDO ALTURA DE LINHAS E LARGURA DE COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO44 Para congelar uma linha (iremos neste caso Congelar a linha superior). No tópico "Exibir" clique em "Congelar Painéis" e após em "Congelar Linha Superior Note que a linha "1" esta junto com a linha "11". Esta congelada a linha superior conforme setado. Congelando e Descongelando Linhas e Colunas CONGELANDO E DESCONGELANDO LINHAS E COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO45 Para descongelar a linha congelada No tópico "Exibir" clique em "Congelar Painéis" e após em "Descongelar Páineis" Note que a linha "1" esta junto com a linha "2" voltando ao normal. CONGELANDO E DESCONGELANDO LINHAS E COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO46 Para congelar uma coluna (neste exemplo iremos congelar a primeira coluna) No tópico "Exibir" clique em "Congelar Painéis" e após em "Congelar Primeira Coluna" Note que a coluna "A" esta junto com a coluna "H". CONGELANDO E DESCONGELANDO LINHAS E COLUNAS BÍBLIA DO EXCEL I MÓDULO BÁSICO47 Para descongelar a coluna descongelada No tópico "Exibir" clique em "Congelar Painéis" após em "Descongelar Páineis" Note que a coluna "A" esta junto com a coluna "B" voltando ao normal. CONGELANDO E DESCONGELANDO LINHAS E COLUNAS + MENUS NO EXCEL BÍBLIA DO EXCEL I MÓDULO BÁSICO48 + Menus no Excel O menu Inserir, ainda continua no Excel 2016, tem finalidade importante para planilhas. Através do Menu Inserir, você pode inserir tabela dinâmica, imagens, gráficos, linhas, colunas, etc. Esta funcionalidade pode estar oculta em sua área de trabalho para habilitar é necessário ir em no Menu Arquivo > Opções > Barra de Ferramentas de Acesso Rápido, localizar o comando desenvolver e "Adicionar". Através do Menu Desenvolvedor, você pode escrever macros, executar macros, usar comandos XML, usar controles ActiveX, uriar aplicativos para usar com os programas do Microsoft Office, usar controles de formulário no Microsoft Excel, trabalhar com o ShapeSheet no Microsoft Visio, criar novas formas e estênceis no Microsoft Visio. Menu Inserir Menu Desenvolvedor A guia layout de página no Excel, permite que você faça várias modificações, como: temas, layout de impressão, linhas de grade, impressão títulos, entre outros. ... Faça mudanças em sua planilha pelas ferramentas das faixas de opções Página Inicial e Inserir e você vai pode personalizar muito mais, sua planilha Uma das principais utilidades do Excel, é fazer documentos onde trabalhamos com números, cifras e valores. ... No Excel 2016, você encontrará uma guia chamada Fórmulas, onde estão reunidas todas as ferramentas necessárias para criar fórmulas. BÍBLIA DO EXCEL I MÓDULO BÁSICO49 Menu Layout da Página Menu Fórmulas Menu Dados Está disponível nesta guia algumas opções para tratar os dados, como “Validação de Dados” e “Remover Duplicadas”, além das opções para inserir dados de outras fontes, como do Access, Web, etc. + MENUS NO EXCEL Utilizada quando finalizamos uma planilha e precisamos proteger, corrigir erros de ortografia, etc. Menu Revisão É uma das mais importantes guias. Traz funções que alteram a forma de visualizar a planilha. Como o próprio nome diz. Possui dúvida, precisa de ajuda? Clique nos botões da tela que você será direcionado para um campo de pesquisa, para tirar sua dúvida pertencente. Menu Exibir Menu Ajuda BÍBLIA DO EXCEL I MÓDULO BÁSICO50 + MENUS NO EXCEL Proteger com Senha sua Planilha Proteger com senha uma planilha é muito útil para segurar que todas as informações será de propriedade de uma ou mais pessoas. O usuário ao tentar acessar o conteúdo será solicitado uma senha de acesso, somente a partir da senha ela conseguirá abrir a planilha. Você pode inserir senha conforme passos abaixo. Clique no menu arquivo BÍBLIA DO EXCEL I MÓDULO BÁSICO51 PROTEGER COM SENHA SUA PLANILHA Selecione a opção Proteger Pasta de Trabalho Clique em Criptografar com Senha Insira uma senha na caixa Senha e clique em OK. Confirme a senha na caixa Digite a senha novamente e selecione OK. BÍBLIA DO EXCEL I MÓDULO BÁSICO52 PROTEGER COM SENHA SUA PLANILHA As dicas, como o próprio nome diz, são "macetes" que poderá lhe ajudar muito para mexer no aplicativo. Abaixo listei algumas que para mim, depois de muito estudo, percebi que poderia ajudar muito quem iria mexer com o aplicativo. Dicas Básicas BÍBLIA DO EXCEL I MÓDULO BÁSICO53 DICAS BÁSICAS Aqui é possível utilizar a forma científica (E de exponencial) na célula desejada; Texto – Marque esta seleção quando for digitar somente texto e números sem formatação específica; Especial – Nesta opção é possível formatar em qualquer tipo de número desejado e, até mesmo, criar novas formatações BÍBLIA DO EXCEL I MÓDULO BÁSICO54 FORMATAÇÃO DE CÉLULAS Formatação de Células Clique no menu Página Inicial Na seta no canto inferior direito em "Número" Escolha o formato desejado Clique em OK Como percebido na imagem acima, existe vários formatos, vou citar os mais utilizados: Geral: Nesta opção com formato "Geral" é o formato mais utilizado no aplicativo. Na maioria das vezes, os números no formato Geral são exibidos na forma em que são inseridos. Número: Esta opção é utilizado para exibição geral de números, pode ser especificado os números das casas decimais. Porcentagem: A porcentagem multiplica o valor da célula por 100 e exibe o resultado com um símbolo de porcentagem (%). Texto: O formato "Texto" trata o conteúdo de uma célula como texto e exibe esse conteúdo exatamente como digitado, mesmo quando são digitados números. Personalizado: Como o próprio nome diz, você tem a opção de personalizar conforme o desejado. BÍBLIA DO EXCEL I MÓDULO BÁSICO55 FORMATAÇÃO DE CÉLULAS Exemplo: Iremos transformar de formato "Geral" para "Moeda" Observe o o formato da célula alterou-se para o formato de Moeda BÍBLIA DO EXCEL I MÓDULO BÁSICO56 Clique no tópico da Página Inicial e selecione a célula para formatar Na seta no canto inferior direito em "Número" Selecione a opção "Moeda". FORMATAÇÃO DE CÉLULAS O preenchimento automático das células trás muita agilidade ao operador, visto que é somente necessário selecionar o cursor e "Arrasta-la". Vamos para um exemplo para ser mais específico. Digite as células que deseja preencher automaticamente e após utilize o cursor do mouse na parte inferior e arreste para baixo BÍBLIA DO EXCEL I MÓDULO BÁSICO57 Preenchimento Automático de Células Selecione a área que deseja preencher automaticamente Arraste a alça para baixo Preenchimento automático de células PREENCHIMENTO AUTOMÁTICO DE CÉLULAS Agora iremos utilizar outro modo de formato, clique no ícone (+) e após em "Copiar Células" Note que os números se repetiram BÍBLIA DO EXCEL I MÓDULO BÁSICO58 Selecione a área e clique no ícone (+) Selecione a opção Copiar Células Agora iremos utilizar o mesmo modo de preenchimento, porém com meses do ano. Selecione a área que deseja preencher automaticamente Preenchimento automático de células PREENCHIMENTO AUTOMÁTICO DE CÉLULAS Às vezes você está desenvolvendo algum projeto em Excel e sente a necessidade de inserir uma Imagem em uma Célula no Excel como, por exemplo, um logotipo de uma empresa. Abordaremos deforma rápida e clara a inserção de 4 elementos no excel, muito úteis para visualização de gráficos. Abaixo apresentaremos cada um separadamente. Para inserir Imagens, Formas e ícones é no mesmo caminho, no menu: Inserir > Ilustração > escolher a opção desejada. Imagens: Primeiramente iremos inserir uma imagem do próprio computador, após imagens online BÍBLIA DO EXCEL I MÓDULO BÁSICO59 Inserindo Imagens, Formas, ícones e Webdings Clique no menu Inserir Clique em Ilustrações INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Clique em Este Dispositivo Selecione a imagem desejada: BÍBLIA DO EXCEL I MÓDULO BÁSICO60 Clique em Imagens Clique em Inserir INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Imagem inserida conforme demonstrado abaixo: Agora iremos inserir uma imagem online, mesmo procedimento que para inserir uma imagem do computador, somente altera no final, selecione Imagens Online.. BÍBLIA DO EXCEL I MÓDULO BÁSICO61 Clique em Imagens Online INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Selecione a categoria ou digite a desejável Neste caso inserimos este avião, conforme seleção acima BÍBLIA DO EXCEL I MÓDULO BÁSICO62 Neste exemplo selecionamos este avião INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Clique em Ilustrações > Formas > Selecione a forma desejável Formas: BÍBLIA DO EXCEL I MÓDULO BÁSICO63 Desenhe com o cursor do mouse o tamanho da forma inserida INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Ícones: Clique em Ilustrações > Ícones > Selecione o ícone desejável Neste caso inserimos este cão, conforme seleção acima: BÍBLIA DO EXCEL I MÓDULO BÁSICO64 Você pode procurar outros ícones através este campo de pesquisa Selecione o ícone desejado Clique em Inserir Desenhe com o cursor do mouse o tamanho do ícone selecionado INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Webdings: Neste caso inserimos este símbolo, conforme seleção acima BÍBLIA DO EXCEL I MÓDULO BÁSICO65 Webdings são símbolos que podem ser selecionados no excel como forma de ilustrações. Clique em Símbolos Clique no menu Inserir Clique no ícone Símbolo Selecione o Webding desejado INSERINDO IMAGENS, FORMAS, ÍCONES E WEBDINGS Clique no menu Arquivo BÍBLIA DO EXCEL I MÓDULO BÁSICO66 Salvando um Arquivo em PDF Se você tem o Excel instalado no seu computador, pode usá-lo para converter as planilhas para PDF, veja: Clique em Salvar como SALVANDO UM ARQUIVO EM PDF Arquivo transformado de tipo Excel para PDF BÍBLIA DO EXCEL I MÓDULO BÁSICO67 Selecione em Tipo > PDF Clique em Salvar SALVANDO UM ARQUIVO EM PDF Uma fórmula do 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. Em toda aplicação de uma fórmula no Excel você deverá inserir o botão de "igual" (=). Abaixo um exemplo simples para visualizar melhor a funcionalidade deste sinal. BÍBLIA DO EXCEL I MÓDULO BÁSICO68 Sinal de Igual (=) O cursor esta abaixo das linhas G3 e 64. Para subtrairmos devemos, digitar o seguinte: =G3-G4. Após só clicar em enter. Abaixo iremos fazer um exemplo de subtração para exemplificar melhor a fórmula. Assim, na célula G5 irá aparecer o resultado de 874 e mais acima também esta exibida a Fórmula aplicada. SINAL DE IGUAL (=) BÍBLIA DO EXCEL I MÓDULO BÁSICO69 Quadro de Fórmulas Básicas Assim como na escola primária você aprendeu fórmulas básicas e suas aplicabilidades, no Excel não é diferente. Abaixo quadro representando as principais funções que você irá utilizar na ferramenta. Vamos a exemplos básicos de operações envolvendo os sinais acima e como coloca-los em fórmulas. SOMA = H7+H8+J32 Neste exemplo estamos somando os valores existentes nas células HR, H8 e J32. SUBTRAÇÃO = J7-K9 Neste exemplo estamos subtraindo o valor existente na células J7 e K9. QUADRO DE FÓRMULAS BÁSICAS MULTIPLICAÇÃO = I5*G3 Neste exemplo estamos multiplicando o valor existente na células I5 pelo G3. DIVISÃO = J43/R23 Neste exemplo estamos dividindo o valor existente na células J43 pelo R23. RAIZ QUADRADA = RAIZ(T7) Neste exemplo estamos calculando a raiz quadrada do valor existente na célula T7 BÍBLIA DO EXCEL I MÓDULO BÁSICO70 EXPONENCIAÇÃO = A8^2 Neste exemplo estamos calculando a exponenciação do valor existente na célula A8 caso você deseje elevar a potência deseja é somente necessário trocar o (2) por o número desejado! PORCENTAGEM = P6*75% Neste exemplo estamos calculando a porcentagem de quanto é 75% do valor da célula P6. QUADRO DE FÓRMULAS BÁSICAS O Excel oferece vários estilos de tabela predefinidos que você pode usar para formatar rapidamente uma tabela. Se os estilos de tabela predefinidos não atenderem as suas necessidades, você poderá criar e aplicar um estilo de tabela personalizado. Embora seja possível excluir apenas os estilos de tabela personalizados, você poderá remover qualquer estilo de tabela predefinido para que ele não seja mais aplicado a uma tabela. Iremos fazer um exemplo neste banco de dados: BÍBLIA DO EXCEL I MÓDULO BÁSICO71 Criação e Design de Tabelas CRIAÇÃO E DESIGN DE TABELAS IMPORTANTE: Sempre verificar se sua tabela tem cabeçalhos, caso não tiver, desmarque a opção que o excel irá criar uma automaticamente (padrão). BÍBLIA DO EXCEL I MÓDULO BÁSICO72 Clique no menu Inserir Clique em Tabela Dinâmica Clique em OK CRIAÇÃO E DESIGN DE TABELAS Após ter clicado em OK, sua tabela já ganha cores e design conforme seleção da aparência e filtros. Iremos filtrar por Gênero: BÍBLIA DO EXCEL I MÓDULO BÁSICO73 Filtros são importantes, caso você desejar analisar somente um tipo ou comparações de valores (células). Clique no ícone selecionar Selecione a descrição que deseja filtrar CRIAÇÃO E DESIGN DE TABELAS BÍBLIA DO EXCEL I MÓDULO BÁSICO74 Neste caso, aplicaremos o filtro (M) que corresponde a Masculino. Passará demonstrar o resultado da seleção da coluna Masculino: Você pode selecionar o design da sua tabela como bem desejar. Sugerimos deixar em uma cor padrão, caso for apresentar, pois fica com um "tom" mais profissional. No menu Design clique na seta inferior da sua tela CRIAÇÃO E DESIGN DE TABELAS BÍBLIA DO EXCEL I MÓDULO BÁSICO75 Após a seleção, o resultado ficará assim: Clique o design desejado CRIAÇÃO E DESIGN DE TABELAS Hiperlink O hiperlink como o próprio nome diz, você pode criar referência através de link para acessar um conteúdo desejado. Iremos fazer um exemplo com um conteúdo de uma planilha fazendo referencia a outra planilha (internamente, dentro do próprio conteúdo) e depois iremos realizar outro mencionando um URL de site da internet. Exemplo onde contém duas planilhas. BÍBLIA DO EXCEL I MÓDULO BÁSICO76 HIPERLINK Digite: Painel de indicadores. Clique com o botão direito sobre a Forma em seguida em link BÍBLIA DO EXCEL I MÓDULO BÁSICO77 Clique em Inserir Selecione a forma de preferência Renomeie a forma e após clique com o botão direito do mouse e em seguida link Vá na Guia Inserir > Formas e escolha uma Forma de sua preferência. HIPERLINK Na janela Inserir link clique em Colocar Neste Documento. Escolha Painel e clique em OK. BÍBLIA DO EXCEL I MÓDULO BÁSICO78 Para testarmos, clique sobre a Forma Painel de indicadores e verifique se você é direcionado para tal planilha: HIPERLINK BÍBLIA DO EXCEL I MÓDULO BÁSICO79 Para voltarmos para a planilha anterior poderíamos usar também uma forma, mas para efeito de estudo vamos utilizar uma célula. Na célula A2 digite: Base de dados Digite: Base de dados Clique com o botão direito sobre esta célula e escolha link Clique em Link HIPERLINK BÍBLIA DO EXCEL I MÓDULO BÁSICO80 Nesta janela repita os seguintes passos: clique em Colocar Neste Documento. Escolha BASE e clique em OK. Veja que o nome ficou azul e sublinhado. Clique sobre ele para voltar para a planilha Base HIPERLINK BÍBLIA DO EXCEL I MÓDULO BÁSICO81 Resultado: Hiperlink com URL de site da internet. Para finalizarmos, clique sobre quais célula e digite Bíblia do excel.Pressione Enter e clique com o botão direito sobre ela. Vá em link. Clique em Link Digite Bíblia do excel, em seguida clique com botão direito do mouse e vai até link HIPERLINK BÍBLIA DO EXCEL I MÓDULO BÁSICO82 Na janela, escolha: Página da Web ou Arquivo… e em Endereço, cole: https://bibliadoexcel.kpages.online/siteoficial Pressione Ok. Clique sobre o link criado HIPERLINK BÍBLIA DO EXCEL I MÓDULO BÁSICO83 Resultado: HIPERLINK NOTAS 2 Resumo Este módulo aborda como realizar cálculos financeiros, fórmulas, mala direta, gráficos dinâmicos, funções, tudo para você aprimorar seu nível de capacitação na ferramenta. MÓDULO INTERMEDIÁRIO =CORRESP(valor_procurado; matriz_procurada; [tipo_correspondência]) Onde: valor_procurado → O valor que o usuário deve informar para que a função encontre sua posição. matriz_procurada → A área onde está o valor_procurado. [tipo_correspondência] → Argumento opcional, para determinar se o Excel deve procurar o dado deseja com base exatamente o que foi indicado em valor_procurado ou se deve procurar um valor maior ou menor que o indicado. Esta é uma função que dado uma série de células organizadas em uma mesma linha, ou em uma mesma coluna, retorna qual a posição do dado procurado. Vamos entender melhor olhando para os argumentos da função e depois para um exemplo simples. Na tabela abaixo queremos determinar em qual posição o Internacional aparece organizada nos dados. Índice + Corresp BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO86 Corresp Escreva esta tabela como exemplo de aplicação da fórmula ÍNDICE + CORRESP Devemos para isso montar a função na célula G4 da seguinte forma. valor_procurado → Internacional, pois é o dado que queremos definir a posição. matriz_procurada → A área onde estão os times (Corinthians a Ipatinga) onde aparece o dado, ou seja da célula D4:D9) [tipo_correspondência] → 0 (zero), pois é o valor que indica a função que queremos uma correspondência exata, ou seja, queremos procurar exatamente pela posição de Internacional. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO87 Aplique a fórmula e de um Enter Resultado: o nome Internacional foi localizado na segunda posição da tabela ÍNDICE + CORRESP ÍNDICE(matriz; núm_linha; [núm_coluna]) Onde: matriz → área onde estão os dados desejados. núm_linha → o número da linha onde está o dado desejado. [núm_coluna] → o número da coluna onde está o dado desejado. Vamos ao exemplo com a tabela da imagem abaixo com algumas notas de avaliações. Esta função funciona de maneira simples, o usuário indica a área, a linha e a coluna e a formula retorna o dado que esta na posição indicada pelo usuário. Olhando um exemplo mais adiante pode ser que facilite. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO88 Índice Escreva esta tabela como exemplo de aplicação da fórmula ÍNDICE + CORRESP Digamos que queiramos, usando a função índice na célula E2 encontrar a informação correspondente a prova de Português para o Nota 3. Assim nossa função deve ser montada da seguinte maneira. matriz → Toda tabela onde estão os dados das provas, da célula D5:H10) núm_linha → O número da linha que Português está em relação a tabela de dados das provas, O número da linha que Português está em relação a tabela de dados das provas, contanto a partir de B4, Português, está na 3ª posição. [núm_coluna] → O número da coluna onde está o Nota 3 em relação a tabela de dados das provas, 4 = O número da coluna onde está o Dado 3 em relação a tabela de dados das provas. Contando a partir de B4, Dado 3 está na 4ª posição. Montando a função veremos algo deste tipo: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO89 Aplique a fórmula e de um Enter ÍNDICE + CORRESP Utilizando a mesma tabela com os dados fictícios das provas das matérias, digamos que queiramos agora, encontrar o Nota 2 referente ao Inglês. Nossa fórmula deve ser assim: O resultado dessa função nos retorna o número 66 que é a Nota 3 para Português. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO90 Unindo as duas Funções Escreva esta tabela como exemplo de aplicação da fórmula e aplique a fórmula =ÍNDICE(MATRIZ; CORRESP(VALOR_PROCURADO; MATRIZ_PROCURADA; [TIPO_CORRESPONDÊNCIA]); CORRESP(VALOR_PROCURADO; MATRIZ_PROCURADA; [TIPO_CORRESPONDÊNCIA])) ÍNDICE + CORRESP o que pedimos para essa fórmula fazer foi: No intervalo de C7:F11 me retorne o dado que estiver na mesma linha (posição vertical) que Inglês está no intervalo B7:B11 e também na mesma coluna (posição horizontal) que Nota 2 está no intervalo C6:F6. Ou seja, o dado que estiver no intervalo C7:F11 na 3ª linha e 2ª coluna. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO91 Então, nossa fórmula combinada de ÍNDICE e CORRESP deve retorna o valor 91 pois é o indicador Nota 2 referente ao Inglês. Resultado ÍNDICE + CORRESP Retorna o valor que ocorre com mais frequência, ou repetitivo, em uma matriz ou intervalo de dados = MODO(A1:A8) O número que ocorre mais com frequencia neste exemplo é o: 3, entre as células A1:A8. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO92 Função Modo FUNÇÃO MODO Esta opção conta somente às células que contém números. Esta inicia com o sinal de igualdade, em seguida digite as palavras "cont.núm", abra parênteses, selecione os valores, feche os parênteses e pressione a tecla Enter. Note que as células diferentes de números não foram somadas. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO93 Função Contar Números FUNÇÃO CONTAR NÚMEROS BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO94 Função Máximo Identifica o maior valor no intervalo de células selecionadas. Em um intervalo pequeno de células este recurso não parece ter muita utilidade, mas imagine ter que encontrar o maior valor em um intervalo de 10.000 células, seria extremamente útil. Esta inicia com o sinal de igualdade, em seguida digite a palavra "máximo", abra parênteses, selecione os valores, feche os parênteses e pressione a tecla Enter. Ela retornará o maior valor presente no intervalo selecionado. FUNÇÃO MÁXIMO É o oposto da fórmula anterior, encontra o valor mínimo no intervalo de células selecionado. Esta inicia com o sinal de igualdade, em seguida digite a palavra "mín", abra parênteses, selecione os valores, feche os parênteses e pressione a tecla Enter. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO95 Função Mínimo FUNÇÃO MÍNIMO Basicamente, esta fórmula funciona para CONTAR. A grande diferença é que a função CONT.VALORES com tudo, atribuindo o valor 1 para cada célula preenchida. = CONT.VALORES Resultado: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO96 Função Cont.Valores Digite a fórmula e selecione o intervalo do banco de dados FUNÇÃO CONT.VALORES A sintaxe da função contar.vazio tem os seguintes argumentos. O intervalo no qual as células em branco serão contadas. Veja o exemplo abaixo A fórmula contou "0", pois não possui nenhma célula vazia Veja agora: a fórmula contou "2", pois visualizou 2 espaços vazio em branco BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO97 Função Cont.Vazio Digite a fórmula e selecione o intervalo do banco de dados Resultado FUNÇÃO CONT.VAZIO se(teste_lógico;[valor_se_verdadeiro];[valor_se_falso]) condição valor que retornará se a condição for verdadeira valor que retornará se a condição for falsa Exemplo: Controle de notas de Alunos, se for maior ou igual a 7, então o aluno é "APROVADO", se não, é "REPROVADO". BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO98 Função da Condição SE A função SE é uma das funções mais populares do Excel e permite que você faça comparações lógicas entre um valor e aquilo que você espera. Portanto, uma instrução SE pode ter dois resultados. O primeiro resultado é se a comparação for Verdadeira, o segundo se a comparação for Falsa. Resultado: Digite a fórmula, e arraste para baixo selecionando a matriz de intervalo FUNÇÃO DA CONDIÇÃO SE É a junção da formula SE com uma condição a mais. Iremos aproveitar o exemplo da conta da página acima. - se o valor da célula B5 for maior ou igual a 7. - se o valor da célula B5 for menorou igual a 10. Condições nesta fórmula: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO99 Função da Condição E Nesta condição falamos para fórmula apresentar para Verdadeiro, valores => 7 e <10, se não estiver nestas condição apresenta como Falso FUNÇÃO DA CONDIÇÃO E Na formula OU, como o nome diz, deve-se seguir uma condição OU outra, conforme aplicado na regra. Iremos aproveitar o exemplo da conta da página acima. - se o valor da célula B5 for menor do que 5. - se o valor da célula B5 for menor ou igual a 10. Condições nesta fórmula: Resultado: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO100 Função da Condição OU Nesta condição falamos para fórmula apresentar para Verdadeiro, valores <5 ou <=10 se não estiver nestas condição apresenta como Falso FUNÇÃO DA CONDIÇÃO OU Como o próprio nome diz, essa função soma as células conforme seleção RESULTADO: Quadro de sinais de condições para aplicar as fórmulas BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO101 Função da Condição SOMA A aplicação da fórmula mostrou a soma dos valores do banco de dados da matriz B3:G3. Como resultado nos mostrou o resultado de 336. Digite a fórmula e com o cursor do mouse selecione a matriz que deseja somar FUNÇÃO DA CONDIÇÃO SOMA Suponhamos que você desejasse saber qual a média de idade das pessoas da listagem abaixo: Escolha a célula em que deseja ver o resultado da operação e digite a fórmula. Esta inicia com o sinal de igualdade, em seguida digite a palavra "média", abra parênteses, selecione os valores, feche os parênteses e pressione a tecla Enter. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO102 Função da Condição Média Digite a fórmula e com o cursor do mouse selecione a matriz que deseja descobrir a média dos valores Resultado: com a aplicação da fórmula nos mostrou a média dos valores de uma matriz de dados selecionado FUNÇÃO DA CONDIÇÃO MÉDIA Esta aplicabilidade é muito útil para usarmos para verificar data e horas atuais de determinado (dia/mês/ano, assim como horas) Traz o dia atual Aplicando esta fórmula tras o dia, mês e ano, junto com a hora atual que você esta executando a fórmula. Retorna o dia (considerando a fórmula aplicada). 1 2 3 4 5 Retorna o ano (considerando a fórmula aplicada). Retorna o mês (considerando a fórmula aplicada). BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO103 Função da Condição Data e Hora FUNÇÃO DA CONDIÇÃO DATA E HORA Juntar textos, essa função pode ser muito útil para juntar NOME & SOBRENOME. Com a preposição & BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO104 Função da Condição & Aplicando esta condição a fórmula, juntamos os valores encontrado na célula B1 com a C1. Neste exemplo, juntamos os nomes: Carlos & Iora. Resultado: Carlos Iora Digite a fórmula e com o cursor do mouse selecione a matriz que deseja juntar os valores FUNÇÃO DA CONDIÇÃO & Dica muito importante que poucos conhecem, agora é jogo rápido. Indicando a célula onde está o número que deseja transformar e a mágica acontece. Indica o formato de algarismo romano que você deseja. Obs.: a função Romanos no Excel converte até o número 3999. Números maiores ela apresenta o erro #VALOR. =ROMANO BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO105 Função da Condição Romano Digite a fórmula e com o cursor do mouse selecione a matriz que deseja descobrir os valores em números romanos FUNÇÃO DA CONDIÇÃO ROMANO o Proch procura valores nas linhas, ou seja, na horizontal. Assim, serve para encontrar dados em uma linha qualquer de um determinada tabela ou área com base em algum outro dado desta mesma tabela ou área, que deve ser informado pelo usuário. =PROCH(valor_procurado; matriz_tabela; núm_índice_lin; [procurar_intervalo]) 1 2 3 4 valor_procurado → O valor que o usuário deve informar para que a função encontre o dado correspondente. matriz_tabela → A tabela ou área onde estão: o valor_procurado e o dado correspondente que se deseja encontrar. núm_índice_lin → O número da linha onde está o dado que se deseja encontrar. [procurar_intervalo] → Argumento opcional, para determinar se o Excel deve procurar o dado deseja com base exatamente o que foi indicado em valor_procurado ou se deve usá-lo de forma aproximada. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO106 Função da Condição Proch Vamos para um exemplo: Monte esta estrutura de dados, digite a fórmula e dê um Enter. FUNÇÃO DA CONDIÇÃO PROCH A função acima foi inserida na célula C3 de uma planilha qualquer. valor_procurado = C2 → Na célula C2 foi inserido o nome de Irineu, a função irá ler o que está nesta célula e utilizar para fazer a busca. matriz_tabela = B5:J8 → É o intervalo de células onde estão os dados que queremos buscar. núm_índice_lin = 3 → Pois o nome do grupo está na terceira linha em relação a linha de onde está o valor_procurado [procurar_intervalo] = FALSO → Pois queremos que a função se baseie exatamente na palavra Irineu, e não algum valor aproximado. Caso contrário usaríamos VERDADEIRO. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO107 Resultado: Como resultado encontramos a palavra Red Bull, conforme aplicação da fórmula acima. FUNÇÃO DA CONDIÇÃO PROCH Uma situação típica onde utiliza-se muito a função SEERRO no Excel é na função PROCV. Temos uma lista com 8 times de futebol, porém na pesquisa o usuário digitou número 9, como não temos este número na lista o Excel retornar para nós o erro: #N/D, ou seja, não encontrando: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO108 Função da Condição Seerro Monte esta estrutura de dados, digite a fórmula e dê um Enter. Resultado apresentado com erro, o usuário digitou número 9, como não temos este número na lista o Excel retornar para nós o erro: #N/D, ou seja, não encontrando: FUNÇÃO DA CONDIÇÃO SEERRO Para não aparecer este erro, vamos usar a função SEERRO no Excel. Dê dois cliques na célula C3. Depois do o sinal de igual (=) digite SEERRO e pressione TAB para abrir a função. Após o último parênteses (que está fechado) digite ponto e vírgula “;” e entre aspas duplas digite a seguinte mensagem: “Time não encontrado” e pressione Enter para concluir esta célula. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO109 Digite a fórmula acima e dê um Enter. FUNÇÃO DA CONDIÇÃO SEERRO Repita para a célula D3 BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO110 Resultado: ao invés de aparecer #N/D, aplicamos a fórmula para que apareça "Time não encontrado" ficando mais profissional para apresentação e organização do banco de dados. Copie e cole a fórmula para a célula D2 FUNÇÃO DA CONDIÇÃO SEERRO Banco de dados é basicamente um conjunto agrupado e organizado de arquivos e dados sobre uma empresa ou muito mais. ... Podem guardar dados sobre pessoas, clientes, endereços, contas, vendas, etc. Qualquer coisa pode ser organizada em um BD. Todas as funções referentes a banco de dados no Excel iniciam-se com a sigla BD ( =BdSoma(), =BdMédia(), =BdExtrair(), entre outras). Todas as funções possuem a mesma sintaxe: Nome_da_função (nome_banco_de_dados; Campo; Critérios) 1 2 3 Banco_de_dados: A matriz que será feita a análise dos dados. Campo: Representa a coluna que será utilizada na função Critérios: Refere-se ao intervalo de células que possui as condições específicas a serem pesquisadas. Exemplo: Considere esta tabela para analisar BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO111 Função da Condição Banco de Dados Digite estas informações na sua planilha excel para exemplo FUNÇÃO DA CONDIÇÃO BANCO DE DADOS =BDSOMA Digamos que eu queira saber quais foram o total de gols do estado do RS. Para isso usaremos a seguinte fórmula: =BdSoma (B2:D16;3;G2:I3) onde o primeiro campo diz respeito à matriz do banco de dados, o segundo campo diz respeito à coluna onde será somado os valores e no terceiro campo o local onde estão os dados que serão buscados. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO112 Aplique a fórmula acima. Nesta aplicação da fórmula dizemos para a condição a regra de quais foram o total de gols do estado do Rio Grande do Sul, como resultado nos mostrou quea soma dos times do RS foram 92. FUNÇÃO DA CONDIÇÃO BANCO DE DADOS =BDMÍN Esta função têm como objetivo mostrar o menor valor de uma busca, neste caso estamos considerando o menor valor de gols do time do estado da BA. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO113 Aplique a fórmula acima. Nesta aplicação da fórmula dizemos para a condição a regra de qual é o menor número de gol que um time realizou do estado da bahia. Como resultado nos trouxe o resultado de 43. FUNÇÃO DA CONDIÇÃO BANCO DE DADOS =BDMÁX Esta função têm como objetivo mostrar o maior valor de uma busca, neste caso estamos considerando o maior valor de gols do time do estado da BA. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO114 Nesta aplicação da fórmula dizemos para a condição a regra de qual é o maior número de gol que um time realizou do estado da bahia. Como resultado nos trouxe o resultado de 46. Aplique a fórmula acima. FUNÇÃO DA CONDIÇÃO BANCO DE DADOS =BDEXTRAIR Com esta função nós buscaremos registros únicos. Por exemplo: =BdExtrair(B2:D16;3;G2:I3) iremos buscar o número de gols do time do Vasco BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO115 Aplique a fórmula acima. Nesta aplicação da fórmula dizemos para a condição a regra de qual é o valor exato número de gol que o time do Vasco fez. Como resultado nos trouxe o valor de 15. FUNÇÃO DA CONDIÇÃO BANCO DE DADOS Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela. PROCV(valor_procurado;matriz_tabela;núm_índice_coluna) Valor procurado - O valor a ser localizado na primeira coluna da matriz da tabela. O Valor procurado pode ser um valor ou uma referência. Se o valor procurado for menor do que o menor valor na primeira coluna de matriz tabela, o PROCV fornecerá o valor de erro #N/D. Matriz tabela - Duas ou mais colunas de dados. Use uma referência a um intervalo ou nome de intervalo. Os valores na primeira coluna da matriz tabela são aqueles procurados pelo valor procurado. Esses valores podem ser textos, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. núm_índice_coluna - É o número da coluna na tabela matriz a partir do qual o valor correspondente deve ser retornado. Um número índice coluna de 1 retornará o valor na primeira coluna na tabela matriz. Um número índice coluna de 2 retornará o valor na segunda coluna na tabela matriz, e assim por diante. Se número índice coluna for: Menor do que 1, PROCV retornará o valor de erro #VALOR!. Maior que o número de colunas em matriz tabela, PROCV fornecerá o valor de erro #REF!. Procurar intervalo - Um valor lógico que especifica se você deseja que o PROCV localize uma correspondência exata ou aproximada. Se for verdadeiro, uma correspondência aproximada será retornada. Se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor procurado será retornado 1 2 3 4 BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO116 Função da Condição Procv FUNÇÃO DA CONDIÇÃO PROCV Os valores na primeira coluna de matriz tabela devem ser colocados em ordem de classificação crescente, caso contrário, o PROCV poderá não fornecer o valor correto. Você pode colocar os valores em ordem ascendente escolhendo o comando Classificar no menu Dados e selecionando Crescente. Para obter mais informações, consulte Ordens de classificação padrão. Se falso, o PROCV somente localizará uma correspondência exata. Nesse caso, os valores na primeira coluna de tabela matriz não precisam ser classificados. Se houver dois ou mais valores na primeira coluna da tabela matriz que corresponderem ao valor procurado, o primeiro valor encontrado será usado. Se uma correspondência exata não for encontrada, o valor de erro #N/D será retornado. Também podemos, ao invés de digitar a função, procura-la no quadro de funções. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO117 FUNÇÃO DA CONDIÇÃO PROCV Agora vamos usar a função PROCV para ver o que ela faz melhor. Dos dados acima, precisamos saber quanto Andrey tirou no concurso do IBGE. Abaixo está a fórmula de PROCV que retornará a pontuação de matemática de Andrey: = P R O C V ( “ A n d r e y ” ; $ A $ 3 : $ E $ 1 0 ; 2 ; 0 ) No primeiro exemplo de PROCV abaixo, temos uma lista com nomes de incritos na coluna A e notas de diferentes concursos nas colunas B a E. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO118 Exemplo: Encontrando a Nota de um Inscrito em Concurso do IBGE Digite essa estrutura de dados para realizarmos um exemplo FUNÇÃO DA CONDIÇÃO PROCV A fórmula acima possui quatro argumentos: “Andrey”: – este é o valor que estamos pesquisando. $A$3:$E$10 – este é o intervalo de células em que estamos procurando. Lembre-se de que o Excel procura o valor de pesquisa na coluna mais à esquerda. Neste exemplo, ele procuraria o nome Andrey em A3:A10 (que é a coluna mais à esquerda da matriz especificada). 2 – Uma vez que a função aponte o nome de Andrey, ela irá para a segunda coluna da matriz e retornará o valor na mesma linha de Andrey. O valor 2 aqui indica que estamos procurando a pontuação da segunda coluna da matriz especificada. 0 – diz à função PROCV para procurar apenas correspondências exatas.Veja abaixo como a função PROCV funciona neste exemplo:Primeiro, ela procura o valor “Andrey” na coluna mais à esquerda. Indo de cima para baixo e encontra o valor na célula A6. Veja abaixo como a função PROCV funciona neste exemplo: Primeiro, ela procura o valor “Andrey” na coluna mais à esquerda. Indo de cima para baixo e encontra o valor na célula A6. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO119 Com a primeira aplicação da fórmula, localizou a palavra "Andrey". FUNÇÃO DA CONDIÇÃO PROCV Assim que encontra o valor, ela vai para a direita na segunda coluna e busca o valor nele BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO120 Com a segunda aplicação da fórmula, localizou o valor da nota do cursos IBGE, representado pelo valor de 55. FUNÇÃO DA CONDIÇÃO PROCV As tabelas Dinâmicas são tabelas interativas que resumem elevadas quantidades de dados, usando estruturas e métodos de cálculos especializados. Trata-se de uma abordagem simples de um conjunto de ferramentas de análises muito utilizado atualmente, que, dentre outras características permite uma análise multidimensional dos dados. As tabelas Dinâmicas permitem organizar dados e calcular informações resumidas utilizando categorias (campos) e funções resumo (soma, média, dentre outros) Tabelas Dinâmicas BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO121 Podemos encontrar uma parte destinada aos relatórios, ou seja, todos os comandos executados dentro da tabela Dinâmica serão visíveis no centro da tela em formas de gráficos. São as opções sobre a tabela Dinâmica, ou seja, todas as ferramentas que precisamos para corrigir e formatar possíveis erros. 1 2 TABELAS DINÂMICAS Ficam disponíveis os rótulos, ou seja, formas de visualização dentro do relatório. Você poderá incluir todos os rótulos dentro do relatório, ou simplesmente os que você desejar. Podemos encontrar os filtros, ou seja, formas de visualização dos dados dentro do relatório. Você poderá alterar os filtros apenas clicando e arrastando até o filtro desejado. 3 4 BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO122 Tabela Dinâmica - Passo a Passo O primeiro passo é abrir o arquivo com a tabela que você deseja transformar em tabela dinâmica Selecione todos os campos com o Mouse. TABELAS DINÂMICAS Agora, clique na guia Inserir e depois em Tabela Dinâmica. Então surgirá uma nova janela em sua tela. Nela, você poderá alterar os campos que farão parte de sua nova tabela e também você poderá definir o local onde ela será criada. Além dos campos em sua própria planilha, é possível também selecionar fontes externas. Para isso, clique em Usar fontes de dados externa e então escolha a fonte de dados de sua nova tabela Dinâmica. Depois disso, clique em OK. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO123 Clique no menu Inserir e em Tabela Dinâmica TABELAS DINÂMICASSua tabela dinâmica está pronta. A partir de agora, para acessar os valores e dados, clique sobre a tabela e então selecione tudo o que deseja analisar. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO124 Clique no menu Inserir e em Tabela Dinâmica Após você abrir a planilha no local desejado, você pode selecionar as colunas e informações que deseja analisar TABELAS DINÂMICAS Com este tipo de tabela, você poderá comparar os dados facilmente, como no exemplo abaixo, onde foram selecionados os dados de apenas três pessoas. 1 2 3 4 A área de Filtros são apresentados como filtros de relatório de nível superior acima da Tabela. Os campos de área de Colunas são exibidos como Etiquetas de Coluna na parte superior da Tabela. Os campos da área de Linhas são apresentados como Etiquetas de Linha no lado esquerdo da Tabela. Os campos de áreas de Valores são apresentados como valores numéricos resumidos na Tabela Dinâmica. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO125 A Lista de Campos tem uma secção de campos, onde pode escolher os campos que pretende mostrar na Tabela Dinâmica, e uma secção de Áreas (na parte inferior), onde pode ordenar esses campos conforme quiser. TABELAS DINÂMICAS Ainda na barra do lado direito, você poderá mover os dados entre os campos filtros de relatórios, rótulos de coluna, rótulos de linhas e Valores. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO126 Selecione as três pessoas que deseja analisar na tabela, neste caso selecionamos os nome de Taliz, Alex e Jardel Insira o nome "Taliz em Linha" Em valores adicione os três nomes Taliz, Alex e Jardel Resultado: TABELAS DINÂMICAS Para melhor analisar os dados, você pode adicionar filtros para poder reduzir a visualização de dados ou algum conteúdo mais específico. Para isto, clique sobre a guia Rótulos de Linha para que seja aberto o seu menu dropdown, depois vá em Filtros e Valores e escolha o tipo de filtro que deseja aplicar. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO127 Clique em OK Selecione os meses para análise Clique em Filtros de Valores Clique na guia de rótulos de linha Aplicamos o filtro dos meses de Janeiro, Maio e Agosto TABELAS DINÂMICAS Outros tipos de filtros são os Filtros de Rótulos, estes relativos aos rótulos da sua tabela. No nosso exemplo, os rótulos são os meses. Esta função também será bem útil no caso de tabelas com um grande número de dados e informações. Você pode ainda utilizar seus próprios dados como filtros para comparação na tabela. Por exemplo, arraste qualquer um dos dados da aba direita para o campo Filtro de Relatório e então ele aparecerá em cima da sua tabela. Clique para abrir sua guia e então selecione o valor que deseja utilizar como referência. Desta forma, você poderá selecionar um ou vários itens (no caso de nosso exemplo, meses) para filtrar e comparar com os demais dados informados na tabela. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO128 Clique em OK Arraste o nome "Sabrina" para o campo Filtros Clique na guia de filtro de valores Selecione os valores que deseja analisar TABELAS DINÂMICAS FUNÇÕES FINANCEIRAS DO EXCEL EQUIVALEM PRATICAMENTE À CALCULADORA CIENTÍFICA DE BOLSO HP 12C. O Excel é um editor que não nos possibilita o trabalho com juros simples, mas sim, juros compostos. Para isso que foram desenvolvidas as funções financeiras do Excel, ou seja, funções de auxílio para empresas que, por exemplo, devem cuidar de quarenta funcionários ao mesmo tempo. Dentre tantas funções financeiras existentes, vamos falar apenas das principais, ou seja, aquelas funções que são usadas diariamente por empresas e afins. Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO129 Funções Financeiras Função FV (Valor Futuro) FUNÇÕES FINANCEIRAS Decidimos a taxa da tabela, ou seja, a taxa de Juros por período solicitado1 2 Decidimos o Nper da tabela, ou seja, o número total de períodos depagamento em uma anuidade. 3 Decidimos o Pgto da tabela, ou seja, o pagamento feito a cada período,não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e não contém nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá incluir o argumento vp. 4 Podemos decidir o Vp, ou seja, o valor presente ou a soma totalcorrespondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do argumento pgto será obrigatória. 5 Decidimos o tipo da tabela, ou seja, se o número 0 ou 1 indicar as datas devencimento dos pagamentos. Se tipo for omitido, será considerado 0. 6 Descrição do campo BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO130 FUNÇÕES FINANCEIRAS Exemplo para Investimento O exemplo a seguir, mostra como calcular um investimento usando a função VF. Com um depósito inicial de R$ 4.000,00 e depósitos mensais de R$ 500,00 para um período de 2 anos com juros mensais de 0,8%. Copie a tabela e cole na célula A1, da planilha de Excel. Na linha Total, célula B7, cole a seguinte função: =VF(B6;B5;-B4;-B3;0) Obs: Para calcular a função VF, primeiro calcule a taxa de juros, seguida de período, depois o depósito mensal, ou parcelas mensais, e só depois o valor presente. No exemplo acima a função começa com B6, justamente a taxa de juros. A planilha fica mais organizada, mas a fórmula fica com as células invertidas. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO131 Função: VF(taxa,nper,pgto,[vp],[tipo]) Insira a fórmula neste campo, após de um Enter. FUNÇÕES FINANCEIRAS A função NPER do Excel é uma função financeira que retorna o número de períodos de empréstimo ou investimento. Você pode usar a função NPER para obter o número de períodos de pagamento de um empréstimo, dado o valor, a taxa de juros e o montante do pagamento periódico. Função: NPER(taxa,pgto,vp,[vf],[tipe]) BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO132 Função Nper 3 Definimos o Vp da tabela, ou seja, o valor presente ou atual de uma série de pagamentos futuros. Podemos decidir as Taxas, ou seja, a taxa de juros por período.1 Definimos o Pgto, ou seja, o pagamento feito em cada período, não podendo mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxas. 2 4 Podemos definir o Vf da tabela, ou seja, o valor futuro, ou o saldo, quevocê deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). FUNÇÕES FINANCEIRAS 5 Definimos o Tipo da tabela, ou seja, é o número 0 ou 1 e indica as datas devencimento. 6 Uma breve descrição do que significa cada campo, no qual é bem semelhante coma função Vf. A partir daqui vamos ter um breve conhecimento sobre outra função, que é a função Taxa. As funções das células D8, D9 e D10 devem ser copiadas e coladas, respectivamente nas células B8, B9 e B10. Não esqueça de excluir o apóstrofo, antes do sinal de igual se as funções estiverem visíveis. De acordo com a convenção geral de fluxo de caixa, os pagamentos de saída são representados por números negativos e os pagamentos recebidos são representados por números positivos. Isso é visto no exemplo abaixo. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO133 Certifique-se de fornecer taxa consistente com os períodos. Por exemplo, se uma taxa de juros anual é de 10%, use 10%/12 para taxa para obter NPER para retornar períodos em meses. Problema: Comum a função NPER do Excel dá um resultado negativo, quando se espera um positivo. Como Resolver: Esse problema geralmente ocorre quando o valor atual eo pagamento periódico especificado possuem o mesmo sinal aritmético. Se um empréstimo for liquidado, o valor presente deve ser negativo e o pagamento deve ser positivo, ou vice- versa. Este problema pode ser evitado se você garantir que todos os sinais adotem as convenções usuais de sinais de fluxo de caixa . FUNÇÕES FINANCEIRAS Ela 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!. Função: TAXA(NPER;PGTO;VP;VF;TIPO) BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO134 Função Taxa 3 definimos o Vp, ou seja, o valor presente. O valor total correspondente ao valor atual de uma série de pagamentos futuros. Definimos o Nper, ou seja, o número total de períodos de pagamento em uma anuidade. 1 Podemos decidir o Pgto, ou seja, o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf. 2 FUNÇÕES FINANCEIRAS 4 Podemos definir o Vf, ou seja, o valor futuro, ou o saldo, que você desejaobter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). 5 Definimos o Tipo, ou seja, é o número 0 ou 1 e indica as datas devencimento. 6 Uma breve descrição do que significa cada campo. Exemplo: Você fez um financiamento e recebeu R$ 21.000,00. Durante 02 anos, você deverá desembolsa R$ 1.100,00 por mês para fazer seu pagamento. Seu desejo é saber se realmente a financeira está cobrando o valor da taxa de juros acordado que foi 1,9%. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO135 Para isso temos as seguintes informações: VP: R$ 21.000,00 NPER: 24 (convertemos anos para meses) PGTO: – R$ 1.100,00 Coloque essas informações na planilha FUNÇÕES FINANCEIRAS Na célula que deseja o resultado digite =TAXA pressione a tecla TAB e coloque as informações dentro da função: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO136 Pressione Enter para ver se o valor da taxa de juros confere. Se apareceu 2% em vez de 1,9%, clique em <- 0,00 FUNÇÕES FINANCEIRAS Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante. Podemos encontrar a Taxa, ou seja, a taxa de juros por período.1 Encontramos as taxas de Nper, ou seja, o número total de pagamentos pelo empréstimo.2 Função: PGTO(TAXA;NPER;VP;VF;TIPO) BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO137 Função Pagamento 3 Encontramos o Vp, ou seja, valor presente. O valor total presente de uma série de pagamentos futuros. 4 Encontramos o Vf, ou seja, o valor futuro, ou o saldo de caixa, que vocêdeseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). 5 Encontramos o Tipo, ou seja, é o número 0 ou 1 e indica as datas devencimento. FUNÇÕES FINANCEIRAS Exemplo: Você e sua turma da faculdade estão planejando uma viagem de formatura e precisam arrecadar R$ 10.000,00 ao longo de 18 meses. Depositando na conta poupança que rende 0,5% ao mês, vocês pretendem saber quanto precisam depositar todos dos meses para atingir este objetivo. Muito fácil. Primeiro listamos as informações que temos disponíveis: VF : R$ 10.000,00 TAXA: 0,5% NPER: 18 (período e taxa de juros já estão em meses, portanto não precisamos fazer conversão). Coloque as informações disponíveis na planilha. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO138 Na célula que deseja o resultado digite =PGTO, pressione a tecla TAB e coloque as informações na função: PGTO FUNÇÕES FINANCEIRAS Pressione Enter para ver o valor que deverão depositar mensalmente. O resultado ficou negativo justamente pelo fato de ser um dinheiro que deverão desembolsar. PGTO BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO139 FUNÇÕES FINANCEIRAS Vamos seguir como exemplo a seguinte analogia: Você deseja mandar convites para 20 pessoas, querendo colocar o nome de cada pessoa no respectivo convite. Você deverá criar um modelo e uma tabela com os 20 nomes. Logo após você deverá criar uma Mala Direta no Word. A Mala Direta nos permite criar "etiquetas" ou "rótulos" para facilitar o trabalho no vínculo do Excel entre o Word. Em outras palavras Mala Direta significa Mailing (abreviação de Mailing List, em inglês), banco de dados onde se armazenam dados de consumidores (nome, endereços, características do consumidor, entre outros) para serem utilizados em marketing direto, tais como telemarketing e correio eletrônico. Lembre-se que antes devemos salvar a tabela criada na extensão .xls. Com isso, vamos poder importá-la para o Word sem maiores problemas. Seu objetivo é criar a estrutura do banco de dados que será utilizado para a convocação dos recém aprovados no concurso público da nossa Instituição. Ao iniciar a Mala direta você verá as seguintes opções: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO140 Mala Direta MALA DIRETA Podemos encontrar a opção Cartas, ou seja, a opção que simula uma ocorrência de carta, podendo ser colocada em um envelope com o seu destino. 1 Encontramos a opção Envelopes, que ao contrário da opção cartas, faz um envelope onde podemos rotular como destinatário e remetente. 2 3 Encontramos a opção Diretório, ou seja, o assistente nos cria um diretório para que possamos trabalhar melhor com as extensões. 4 Podemos selecionar um Documento Normal do Word, ou seja, com a extensão,doc BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO141 Podemos encontrar as Mensagens de E-mail, ou seja, mensagens que podemos enviar pelo correio eletrônico com o destinatário de e-mail, cópias, dentre outros. Podemos selecionar um documento de Etiquetas, ou seja, apenas criar um rótulo para o documento já criado dentro do Excel. 5 6 7 Esteja talvez a opção mais utilizada no Mala Direta, que é o Assistentepasso a passo para conseguirmos montar uma Mala Direta explicando passo por passo. MALA DIRETA Para criar sua mala direta, vá até a guia Correspondências e clique no botão Iniciar Mala Direta. Em seguida clique em Assistente de Mala Direta Passo a Passo.... BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO142 Agora vamos para a execução: Então o painel do assistente da mala direta se abrirá á direta do Word, seleciona a opção Cartas e clique em Próxima: Documento Inicial Selecione o campo Cartas Clique em Próxima: Documento Inicial MALA DIRETA No próximo passo você deverá escolher uma forma para criar o conteúdo do texto da mala direta. Existem três opções: Usar documento atual (para criar seu próprio texto), Iniciar com base em um modelo (utilizar os modelos personalizados) e Iniciar com base em documento. Seguiremos com a opção Iniciar com base em um modelo, logo você deverá escolher um modelo previamente personalizado que atenda suas necessidades e seguir para o próximo passo. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO143 Clique em Próxima: Selecione os destinatários Clique em OK. Clique em Selecionar o modelo.. Selecione Iniciar com base em um modelo Clique em Documento em Branco MALA DIRETA No passo seguinte você definirá como os destinatários serão utilizados na mala direta. Existem três opções: Usar uma lista existente, Selecionar nos contatos do Outlook e Digitar uma nova lista. Para criar sua própria lista de destinatários selecione a opção Digitar uma nova lista e siga os procedimentos. Inserir dados BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO144 Clique em Adicionar Selecione Digitar uma nova lista Clique em Criar Clique em Personalizar Colunas Clique em Próxima: Escreva Carta Digite o nome do documento MALA DIRETA Depois de criar a lista de destinatários da mala direta, você deverá salvá-la em uma pasta segura. BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO145 Clique em OK MALA DIRETA No próximo passo você já poderá incluir os campos de mesclagem posicionando o mouse no local desejado e clicando em Inserir campo de Mesclagem no documento da mala direta. E por fim clique em Visualizar Resultados para que os campos mostrem as informações cadastradas na lista de destinatário: BÍBLIA DO EXCEL I MÓDULO INTERMEDIÁRIO146 Clique em Inserir Campo de Mesclagem Informe o número que deseja visualizar Clique em visualizar resultados MALA DIRETA A criação de tabelas dinâmicas
Compartilhar