Baixe o app para aproveitar ainda mais
Prévia do material em texto
Microsoft Excel 2016 Avançado Avançado Elaboração: Grupo Digicad de Excel Caro Treinando, O treinamento do Microsoft Excel Avançado tem por objetivo apresentar as fer- ramentas avançadas do aplicativo bem como as principais funções que utiliza- das nas planilhas ajudam a otimizar o tempo na geração de relatórios, manipu- lação de dados e ter uma melhor analise das informações. Para a realização deste treinamento o participante deve ter participado do trei- namento de Excel Básico ou possuir conhecimento similar. Este material de apoio contém uma primeira parte que é uma revisão dos con- ceitos básicos necessários que o participante de apresentar para ter um melhor aproveitamento deste treinamento. Este material deverá ser utilizado durante o treinamento tanto para acompanha- mento das aulas quanto para posterior consulta. Ao final de cada aula haverão exercícios complementares a serem realizados em horários livres ou quando solicitado pelo instrutor. Estes exercícios se encon- tram no final do material identificados de acordo com as respectivas aulas. O intuito dos exercícios é fixar o conteúdo visto durante as aulas. A realização dos exercícios é muito importante para um bom acompanhamento e aproveitamento do curso. Para dúvidas durante ou após o treinamento estará disponível o e-mail: duvidas@digicad.com.br DIREITOS AUTORAIS ADQUIRIDOS PELA DIGICAD INFORMÁTICA E CONSULTORIA LTDA. PROIBIDA A REPRODUÇÃO DOS TEXTOS ORIGI- NAIS, MESMO PARCIAIS, POR QUALQUER PROCESSO, SEM PRÉVIA AUTORIZAÇÃO DA DIGICAD INFORMÁTICA E CONSULTORIA LTDA. Revisão: 04 Setembro/2019 mailto:duvidas@digicad.com.br INFORMAÇÕES AOS ALUNOS Prezado aluno a Digicad tem um imenso prazer em contar com sua presença em nos- sos treinamentos. Esperamos que você tenha um excelente aproveitamento, repli- cando os conhecimentos na área profissional e pessoal. Abaixo algumas informações e procedimentos importantes para um perfeito anda- mento de seu treinamento. • Assinar a lista de presença em todas as aulas em que estiver presente, confe- rindo a grafia do nome, pois assim será impressa no certificado. • Todo atraso e saída antecipada será anotada pelo instrutor na lista de pre- sença. • Desligar ou colocar em modo silencioso seu aparelho celular. • Não comer ou beber em sala de aula. • Haverá, em todas as aulas, um intervalo de 15 minutos para café. • Para a certificação, o aluno deve ter frequência mínima de 80% e aproveita- mento mínimo, na avaliação do instrutor. • Cancelamento de aula em cursos VIP: conforme contrato os alunos de curso VIP devem desmarcar aulas com 12 horas de antecedência para as aulas di- urnas e 6 horas de antecedência para as aulas noturnas, caso contrário as horas agendadas serão consideradas como ministradas. • Garantia de aprendizado: o Repita o curso quantas vezes forem necessárias durante o prazo de 01 ano após o termino do curso. o Escolha uma nova turma que possua vaga disponível e matricule-se um dia antes do seu início; o Pague uma pequena taxa correspondente a 5% do valor atual do curso para cada nova turma; o Não há quantidade estabelecido para as repetições, desde que o mesmo curso, com a mesma versão do software, ainda esteja sendo oferecido pela escola; o Caso o curso tenha sido descontinuado, modificado ou substituído, con- sulte novas condições comerciais com o atendimento da escola e co- nheça nosso programa de reciclagem profissional, mais uma vantagem para o aluno Digicad. • Você terá suporte permanente mesmo após o curso podendo tirar suas dúvi- das pertinentes ao treinamento e obter outras informações pelo e-mail: duvidas@digicad.com.br Agradecemos sua confiança em nossos treinamentos. Atenciosamente Direção mailto:duvidas@digicad.com.br Microsoft Excel – Avançado Pag.: 4 Reprodução Proibida Fone: 4121-6166 SUMÁRIO REVISÃO ........................................................................................................................ 6 Interface do Microsoft Excel ......................................................................................... 6 Modo de Compatibilidade ........................................................................................... 10 Compatibilizando arquivos para versões anteriores ................................................... 10 Versões do Microsoft Excel ........................................................................................ 11 Limites do Microsoft Excel .......................................................................................... 11 Formatos (extensões) de Arquivos ............................................................................. 12 Teclas de Atalho Básicas ........................................................................................... 12 Formatação Básica ..................................................................................................... 13 Operadores ................................................................................................................. 17 Cálculos Básicos ........................................................................................................ 18 Colunas, linhas, células e intervalos ........................................................................... 18 Referências de Células relativas e absolutas ............................................................. 20 Opções de Cálculos ................................................................................................... 21 Conceito de Funções .................................................................................................. 22 Repetir linhas e/ou colunas em várias páginas .......................................................... 24 Congelar Painéis ........................................................................................................ 25 Exibir duas partes de uma planilha ao mesmo tempo ................................................ 26 Gerar Arquivos PDF ou XPS ...................................................................................... 27 Ajuda do Microsoft Excel ............................................................................................ 29 AULA 01........................................................................................................................ 30 Mensagens de Erro .................................................................................................... 30 Trabalhando com Nomes de Células ......................................................................... 30 Funções de Data e Hora............................................................................................. 31 Função MOD .............................................................................................................. 40 Cálculos com Horas ................................................................................................... 41 AULA 2.......................................................................................................................... 44 Funções Lógicas ........................................................................................................ 44 Funções de Informação .............................................................................................. 50 Formatação Condicional ............................................................................................. 51 Formatação Condicional Utilizando Fórmula .............................................................. 55 Função Escolher ......................................................................................................... 58 Funções Matemáticas................................................................................................. 59 Função SEERRO ....................................................................................................... 63 AULA 3..........................................................................................................................65 Funções de Arredondamento ..................................................................................... 65 Planilhas Tridimensionais e Vínculos ......................................................................... 68 Vincular Células entre Arquivos .................................................................................. 70 Filtro Avançado ........................................................................................................... 71 Subtotais ..................................................................................................................... 74 Aplicando o recurso Subtotal ...................................................................................... 74 Estrutura de Tópicos .................................................................................................. 77 Função SUBTOTAL .................................................................................................... 78 Função SUBTOTAL e AutoFiltro ................................................................................ 78 Copiando apenas células visíveis ............................................................................... 79 Validação de Dados ................................................................................................... 80 AULA 4.......................................................................................................................... 90 Importação de Dados ................................................................................................. 90 Importando Arquivos de Texto .................................................................................... 90 Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 5 Importando Dados do Access ..................................................................................... 94 Importando Dados da WEB ........................................................................................ 98 Formatar Base de Dados como Tabela .................................................................... 101 Tabela Dinâmica ....................................................................................................... 102 Campo Calculado em Tabela Dinâmica ................................................................... 110 Gráfico Dinâmico ...................................................................................................... 114 Segmentação de Dados ........................................................................................... 118 Linha do Tempo ........................................................................................................ 122 AULA 5........................................................................................................................ 124 Funções de Texto ..................................................................................................... 124 Funções de Pesquisa e Referência .......................................................................... 129 Aninhando as Funções ÍNDICE e CORRESP .......................................................... 136 Aninhando as Funções PROCV e CORRESP .......................................................... 137 Função ÍNDICE com vários intervalos de referência ................................................ 138 AULA 6........................................................................................................................ 139 Funções de Banco de Dados ................................................................................... 139 Exemplos de Aplicação das Funções de Banco de Dados ...................................... 140 Teste de Hipóteses ................................................................................................... 142 Atingir Meta .............................................................................................................. 142 Tabela de Dados ...................................................................................................... 143 Gerenciador de Cenários.......................................................................................... 144 Funções Financeiras ................................................................................................ 147 Equivalência de Taxas.............................................................................................. 147 Auditoria ................................................................................................................... 151 AULA 7........................................................................................................................ 154 Guia Desenvolvedor ................................................................................................. 154 Formulários ............................................................................................................... 155 Botões de Controles de Formulário .......................................................................... 158 Botões de Controles ActiveX .................................................................................... 162 Bloquear Células de uma Planilha ............................................................................ 166 Proteger Planilhas .................................................................................................... 167 Proteger Pasta de trabalho ....................................................................................... 167 Proteger e compartilhar pasta de trabalho................................................................ 168 Proteger a Pasta de Trabalho com Senha................................................................ 169 Gravação de Macros ................................................................................................ 170 Exemplos de Gravação de Macros ........................................................................... 172 AULA 8........................................................................................................................ 177 Macro para Importação de Dados ............................................................................ 177 Associar macro a um botão na barra de ferramentas ............................................... 180 Personalizar a Barra de Acesso Rápido ................................................................... 181 Personalizar a Faixa de Opções ............................................................................... 183 Importa e Exportar Personalizações ......................................................................... 184 Revisão Geral – Folha de Pagamento ...................................................................... 185 Teclas de Atalho ........................................................................................................ 188 Resumo das Funções ............................................................................................... 193 Anotações .................................................................................................................. 198 Microsoft Excel – Avançado Pag.: 6 Reprodução Proibida Fone: 4121-6166 REVISÃO Esta revisão tem por objetivo ajudar o participante do treinamento de Microsoft Excel Avançado a relembrar alguns conceitos básicos necessários para um bom aproveitamento do treinamento. A revisão poderá e deverá ser consultada pelo participante sempre que julgar necessário. Aproveite o tempo livre para ler e praticar os conceitos aqui apresentados, pois o treinamento terá um melhor aprovei- tamento se o participante tiver um bom conhecimento dos recursos e funções básicas do Excel. Se julgar necessário poderá fazer os exercícios de revisão que se encontram no final do material na parte de Exercícios Complementares e tirar as dúvidas com seu instrutor ou através do e-mail duvidas@digicad.com.br. Interfacedo Microsoft Excel Faixa de Opções Seu novo centro de controle, a Faixa de Opções. Não há muita mudança com relação as versões anteriores. O que você precisa, está mais visível e mais fácil de acessar. Em vez de ter cerca de 30 barras de ferramentas não exibidas e comandos escondidos em menus, você tem uma central de controle — a Faixa de Opções, que reúne os itens essenciais e os torna bastante visuais. Quando experimentar o software, você vai descobrir que os comandos que precisa estão agrupados de maneiras que fazem sentido para você. O que há na Faixa de Opções? As três partes da Faixa de Opções são guias, grupos e comandos. Há três componentes básicos para a Faixa de Opções: 1. Guias – Existem sete guias na parte superior. Cada uma representa tarefas principais executadas no Excel. 2 1 3 mailto:duvidas@digicad.com.br Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 7 2. Grupos – Cada guia tem grupos que mostram itens relacionados reunidos. 3. Comandos – Um comando é um botão, uma caixa para inserir informações ou um menu. Os principais comandos do Excel estão na primeira guia, a guia Página Inicial. Os comandos dessa guia são aqueles que a Microsoft identificou como os mais comumente usados quando as pessoas realizam tarefas básicas com planilhas. Por exemplo, os comandos Colar, Recortar e Copiar são organizados primeiramente na guia Página Inicial, no grupo Área de Transferência. Os comandos de formatação de fonte estão próximos, no grupo Fonte. Comandos para centralizar o texto ou alinhar texto à esquerda ou à direita estão no grupo Alinha- mento e comandos para inserir e excluir células, linhas, colunas e planilhas estão no grupo Células. Os grupos reúnem todos os comandos de que você pode precisar para um tipo específico de tarefa e, durante toda a tarefa, eles permanecem em exibição e disponíveis, em vez de serem ocultados em menus. Esses comandos vitais ficam visíveis acima do seu espaço de trabalho. Veja um exemplo da conveniência: se desejar que o texto seja exibido em várias linhas de uma célula, não é necessário clicar em um comando de um menu, clicar em uma guia de uma caixa de diálogo e, em seguida, em uma opção na caixa de diálogo. Basta clicar no botão Quebrar texto automaticamente no grupo Alinhamento na Página Inicial. Você tentará isso na sessão prática. Mais comandos, mas apenas quando você precisa deles Se criarmos um gráfico clicando em um botão na guia Inserir no grupo Gráficos. Em seguida, as Ferra- mentas de Gráfico ficam disponíveis com as guias Design e Formato. Os comandos na Faixa de Opções são aqueles utilizados com mais frequência. Em vez de mostrar todos os comandos a todo momento, o Excel mostra alguns comandos quando eles podem ser necessários, em resposta a uma ação efetuada por você. Por exemplo, se você não tem um gráfico em sua planilha, os comandos para trabalhar com gráficos não são necessários. Mas depois de criado um gráfico, as Ferramentas de Gráfico aparecem com três guias: Design e For- mato. Nessas guias, você encontrará os comandos necessários para trabalhar com o gráfico. A Faixa de Opções responde à sua ação. Use a guia Design para alterar o tipo de gráfico ou para mover o local do gráfico; e a guia Formato para adicionar cores de preenchimento ou para alterar estilos de linha. Quando concluir o gráfico, clique fora da área do gráfico. As Ferramentas de Gráfico desaparecem. Para reexibi-las, clique dentro do gráfico. As guias reaparecem. Portanto, não se preocupe se não vir todos os comandos necessários em todos os momentos. Execute as primeiras etapas. Em seguida, os comandos necessários estarão visíveis. Microsoft Excel – Avançado Pag.: 8 Reprodução Proibida Fone: 4121-6166 Modo de Exibição do Microsoft Office Backstage O modo de exibição Backstage é uma inovação da interface de usuário do Microsoft Office Fluent e um recurso complementar para a faixa de opções. Acessível com um clique no menu Arquivo, o modo de exibição Backstage é o local onde você abre, salva, imprime, compartilha e gerencia arquivos, bem como define as opções do programa. Usar o modo de exibição Backstage No Microsoft Excel, a guia Arquivo e o modo de exibição Backstage substituem e expandem o menu Arquivo de versões anteriores. O modo de exibição Backstage é o espaço para criar novos arquivos, abrir, salvar e imprimir os existentes, definir opções do Excel e muito mais. 1. Para começar, clique na guia Arquivo e clique em Novo. 2. Você pode clicar em Pasta de Trabalho em Branco ou, para usar um modelo, clique em Mode- los de Exemplo. Você pode escolher um desses modelos ou procurar um diferente no Of- fice.com. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 9 3. No caso de opções de impressão, clique na guia Imprimir. 4. Na guia Salvar e Enviar, há opções para enviar um arquivo por e-mail, por fax, para um site do Microsoft Office SharePoint Server e muito mais. 5. Os botões Salvar, Salvar como, Abrir e Fechar são sempre exibidos na guia Arquivo. 6. Agora clique em Opções na guia Arquivo. Aí estão os comandos que costumavam ficar em Op- ções no menu Ferramentas. Microsoft Excel – Avançado Pag.: 10 Reprodução Proibida Fone: 4121-6166 Modo de Compatibilidade A compatibilidade entre as diferentes versões do programa e seus arquivos, na prática não oferece qual- quer complicação aos usuários, mas alguns pontos devem ser observados. Ao abrir um arquivo com a extensão .xls o Excel abre o arquivos sem alterações de layout devido um conversor implementado no programa e na barra de título será exibida a frase Modo de Compatibili- dade. Compatibilizando arquivos para versões anteriores Para compatibilizar uma Planilha de dados do Microsoft Office Excel para qualquer outra versão, anterior a atual, não são necessárias operações complicadas ou a utilização de plug-ins externos de qualquer ordem. Basta especificar a extensão adequada do arquivo, a partir do comando Salvar Como. Supondo ser necessário salvar uma Planilha do Excel de forma a torná-la compatível com uma versão anterior, proceda da seguinte maneira: 1. Clique no Menu Arquivo. 2. Em seguida clicar na opção Salvar como; 3. Na janela de Salvar como escolha o Tipo de arquivo como Pasta de Trabalho do Excel 97-2003, para compatibilizá-la com as versões anteriores do Excel. 4. Nomeie o arquivo se desejar e clique no botão Salvar. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 11 Versões do Microsoft Excel Ano Versão 1987 Excel 2.0 para Windows - xls 1990 Excel 3.0 - xls 1992 Excel 4.0 – xls 1993 Excel 5.0 (Office 4.2 e 4.3) – xls 1995 Excel 7.0 (Office 95) – xls 1997 Excel 8.0 (Office 97) – xls 1999 Excel 9.0 (Office 2000) - xls 2001 Excel 10.0 (Office XP) – xls 2003 Excel 11.0 (Office 2003) - xls 2007 Excel 12.0 (Office 2007) – xlsm 2010 Excel 14.0 (Office 2010) – xlsm 2013 Excel 15.0 (Office 2013) – xlsm 2016 Excel 16.0 (Office 2016) – xlsm Limites do Microsoft Excel Limite Até Excel 2003 Após Excel 2003 Colunas em uma planilha 256 16.384 Linhas em uma planilha 65.536 1.048.576 Número de Cores Diferentes permitidas em uma Planilha 56 4,3 bilhões Número de Condições de formato condicional 3 Mem. Disponível Número de Níveis de Classificação 3 64 Número de Itens exibidos em uma lista de AutoFiltro 1.024 32.768 Número Total de Caracteres exibidos em uma célula 1.024 32.768 Número total de caracteres por célula que o Excel pode imprimir 1.024 32.768 Número total de estilos de células exclusivos em uma planilha 4.000 65.536 Extensão máxima de uma fórmula, em caracteres 1.024 8.192 Número de níveis aninhados permitido em uma fórmula (SE) 7 64 Número máximo de argumentos em uma fórmula 30 255 Número de caracteres que pode ser armazenadoe exibido em uma célula com um formato de texto 255 32.768 Número de Colunas permitido em uma Tabela Dinâmica 255 16.384 Número de campos exibidos no painel de tarefas da Lista de Campos da Tabela dinâmica 255 16.384 Microsoft Excel – Avançado Pag.: 12 Reprodução Proibida Fone: 4121-6166 Formatos (extensões) de Arquivos Abaixo estão listados os principais formatos dos arquivos do Microsoft Excel com suas respectivas exten- sões: Formato Extensão Pasta de Trabalho .xlsx Pasta de Trabalho do Excel 97-2003 .xls Pasta de Trabalho habilitada para macro .xlsm Pasta de Trabalho Binária .xlsb Modelo .xltx Modelo habilitado para macro .xltm Modelo do Excel 97-2003 .xlt Suplemento .xlam Suplemento do Excel 97-2003 .xla Teclas de Atalho Básicas Teclas de Atalho Descrição CTRL+PAGE DOWN Move para a próxima planilha na pasta de trabalho. CTRL+PAGE UP Move para a planilha anterior na pasta de trabalho. CTRL+HOME Move para o início de uma planilha. PAGE DOWN Move uma tela para baixo na planilha. PAGE UP Move uma tela para cima na planilha. CTRL+TECLAS DE DIREÇÃO Move para a margem da região de dados atual em uma pla- nilha. CTRL + Mouse Seleciona intervalos de células separadamente. SHIFT + Setas de Direção Seleciona as células. F2 Edita o conteúdo de uma célula. Duplo Clique na Guia da Planilha Renomeia a planilha. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 13 Formatação Básica Alinhamentos Especiais Os alinhamentos de células do Excel proporcionam várias soluções para os problemas do dia a dia a se- guir veremos alguns exemplos úteis: Exemplo: Para o alinhamento o título da planilha, “Solicitação de Compras”, selecione da célula A1 até G1 e cli- que na guia Página Inicial, grupo Alinhamento clique no botão Mesclar e Centralizar. Caso deseje remover o recurso de Mesclar e Centralizar basta selecionar a célula mesclada e clicar no- vamente no botão para desativar o recurso. Retorno automático do texto • Selecione a linha (clicando sobre o nº da linha) na qual deseja aplicar o retorno automático do texto; • Clique na Guia Página Inicial, e no grupo Alinhamento, clique sobre o botão Quebrar Texto Automaticamente. Quando utilizado o recurso de Quebrar Texto Automaticamente o Excel faz o retorno automático do texto dentro das células, quando o conteúdo for maior que a célula. D IC A Para fazer uma Quebra manual do texto utilize ALT + ENTER Microsoft Excel – Avançado Pag.: 14 Reprodução Proibida Fone: 4121-6166 Para melhorar podemos ainda clicar nos botões de alinhamento Centralizar e No meio para centralizar o conteúdo dentro das células. Outra maneira de realizar os comandos de alinhamento é clicar no indicador de Caixa de diálogo ( ) do grupo Alinhamento e na janela que se abre marcar as opções desejadas e clique em OK. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 15 Centralizar Seleção O comando Centralizar Seleção produz o mesmo efeito que o comando Mesclar, porém, com uma grande vantagem. Não provoca mensagens de erro como no uso do comando Mesclar. 1. Selecione o intervalo desejado (o que você iria mesclar), clique no botão do grupo Alinhamento. 2. Clique na setinha da caixa de combinação Horizontal e selecione a opção Centralizar Seleção. 3. Após pressionar o botão OK do diálogo acima, você verá o seguinte efeito: A aparência é a do comando Mesclar mas, no entanto, as células continuam existindo. Ou seja, quando você usa o comando mesclar, todas as células do intervalo selecionado passam a ter o nome da primeira. No caso acima, o intervalo A1:G1, passaria a ter o nome A1. Com o uso do comando Centralizar Seleção as células A1, B1, C1, D1, E1, F1 e G1 continuarão a existir. Faça o teste. D IC A Usando o recurso de Centralizar Seleção o efeito é semelhante ao Mesclar e tem mais uma vantagem quando utilizado em funções ou fórmulas com referências a essas células formatadas não dão erro, pois são interpretadas individualmente. Ex: Funções como PROCV, PROCH, ÍNDICE e/ou CORRESP. Microsoft Excel – Avançado Pag.: 16 Reprodução Proibida Fone: 4121-6166 Inserindo número sob a forma de texto O Excel reconhece todo número digitado como número, ou seja, constante numérica válida para fórmulas e cálculos. Se você desejar inserir algum número que não quer inserir como parte do cálculo em uma célula, basta introduzir o número com apostrofo ( ‘ ) antes do valor. Se um valor for digitado desta forma em uma tabela de soma, por exemplo, o Excel não o somará. Estes números foram digitados diretamente sobre uma planilha de soma. Todos os números digitados foram assumidos pelo Excel como constantes numéricas e somados ao valor final: 180. Agora o número 82 foi inserido com o apostrofo antes: ’82. Dessa forma o Excel insere o número normalmente na Planilha, mas ele é considerado uma cons- tante não-numérica, e não é levado em conta para o cálculo do resultado da Planilha: 98. D IC A Pode-se utilizar o apostrofo ( ‘ ) também antes de uma fórmula para que a mesma fique exibida na célula e o Excel não realize o cálculo. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 17 Operadores Operador Exemplo M a te m á ti c o s + (adição) =3+3 ou =A1+B1 – (subtração) =3-3 ou =A1-B1ou Negação (–1) * (multiplicação) =3*3 ou =A1*B1 / (divisão) =3/3 ou =A1/B1 % (porcentagem) 20% ^ (Potenciação) =3^3 ou =A1^B1 ou para raiz =25^(1/2) L ó g ic o s = (igual a) =A1=B1 > (maior do que) =A1>B1 < (menor do que) =A1<B1 >= (maior ou igual a) =A1>=B1 <= (menor ou igual a) =A1<=B1 <> (diferente de) =A1<>B1 E s p e c ia is & (E comercial) ="mal"&" sucedido" ou =soma(A1:B4)&“ horas” Concatena dois valores para produzir um valor de texto contínuo. : (dois-pontos) =SOMA(A2:A100) Operador de intervalo, que produz uma referência para todas as célu- las entre duas referências, incluindo as duas referências ; (ponto e vírgula) Separador de argumentos. Separa os argumentos de uma função. =SOMA(B1;B5;B9) # (Sustenido/Cerquilha) ###### ou #VALOR! Exibido em sequência significa que o a célula é menor que o valor ou a formatação utilizada não é válida. Também pode vir junto a uma mensagem de erro de fórmula. $ (Cifrão) =$A$2*B2 ou =A$2*$B2 Utilizado para indicar que aquele endereço não se alterará quando for copiado para um novo endereço. Representa uma referência Abso- luta, ou seja, uma referência fixa. ( ) (Parênteses) =(100+50)*4 ou =Maior(A2:A10;3) ou (R$ 300.000,00) Representa "prioridade" de "resolução" em uma expressão algébrica. Separa os argumentos de uma função ou representa valores negati- vos no formato contábil. [ ] (Colchetes) [h]:mm - Utilizado para aplicar um formato personalizado a uma cé- lula. { } (Chaves) ={A1:A4*B1:B4} Utilizados em fórmulas matriciais. Não pode ser digitado ele é inserido através das teclas CTRL+SHIFT+ENTER após a digitação da fór- mula. \ (Contra Barra) =ÍNDICE({5\6; 7\8};0;2) - O operador \ é usado em fórmulas matrici- ais. Neste caso, o ; (ponto e vírgula) é um separador de linha e, o operador \ (contra barra) é um separador de colunas. Microsoft Excel – Avançado Pag.: 18 Reprodução Proibida Fone: 4121-6166 Cálculos Básicos Nesta etapa, será feita uma breve revisão e refinamento de como trabalhar em uma planilha com fórmulas básicas, além de apresentar as maneiras de se inserir fórmulas em uma célula. Também será revisado os conceitos básicos de células, intervalos, formatação e operadores. Colunas, linhas, células e intervalos A área de trabalho de uma planilha (do Excel ou qualquer outra) é uma área formada pela interseção de linhas com designações numéricas (do lado esquerdo) e de colunas designadas por letras (no topo da folha).A interseção de uma coluna com uma linha chama-se célula. As células são designadas pela conjunção do nome da coluna com o nome da linha. Numa planilha com milhares de células poderá ser difícil encontrar aquela que pretende. Para isso o Excel possui uma caixa de nome, onde pode digitar diretamente a célula para onde pretende ir (seguido de Enter). Por exemplo, a célula fruto da interseção da coluna B com a linha 4 será cha- mada “B4”. Intervalo é um conjunto células. Ex: B1:B5 (Lê-se: De B1 até B5) Temos que tomar cuidado ao digitar uma fórmula que precise utilizar o operador de intervalo : (dois pontos), pois se digitarmos errado e colocarmos o ; (ponto e vírgula) teremos não um intervalo mas 2 argumentos. Ex.: B1;B5 (Lê-se: B1 e B5) Podemos utilizar o separador de argumentos e o operador de intervalos em uma mesma fórmula, geral- mente em funções como no exemplo abaixo: =SOMA(B1:B5;D1:D5;F1:F5) Na fórmula estamos somando 3 intervalos não consecutivos. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 19 Inserindo fórmulas em uma célula Existem algumas maneiras diferentes de se inserir fórmulas no Excel: • Usando o Auto Soma () para fazer somatórios; • Usando o sinal de igual (=) e inserindo a fórmula manualmente; • Usando o assistente de função ; AutoSoma A AutoSoma é usada quando se é necessário fazer uma somatória de valores contidos em várias células. Por padrão, ao clicar no botão de na barra de ferramentas, a fórmula de soma busca realizar a conta com as células adjacentes a ela, que contêm valores numéricos. Se o intervalo de células sugerido para o somatório estiver incorreto pode-se alterá-lo, bastando para tanto selecionar as células a serem somadas usando o procedimento normal de seleção de células. Através do botão podemos incluir outras funções além da AutoSoma. Para isto, em vez de clicar no botão na barra de ferramentas, clique na seta al ao lado dele e escolha a função desejada. D IC A Para fazer uma AutoSoma pode utilizar o botão de ou a tecla de atalho ALT = Inserindo fórmulas manualmente Uma outra maneira de inserir fórmulas é usando o sinal de igual (=), depois digitando-se os endereços das células intercalados pelos sinais das operações a serem feitas. É uma maneira mais flexível de se montar qualquer fórmula, pois é aberta à inserção de valores, variáveis (endereços de células), e funções para a execução de qualquer cálculo. Microsoft Excel – Avançado Pag.: 20 Reprodução Proibida Fone: 4121-6166 Referências de Células relativas e absolutas Um conceito que precisa ser bem entendido e fixado é o de referência de células relativas e absolutas. É bastante usado este conceito em fórmulas avançadas, bem como no uso da alça de preenchimento para se copiar fórmulas de uma célula para outras. Muitas vezes é necessário fixar o endereço de uma célula em certas fórmulas/funções. Para isto, usa-se endereços de células absolutas, isto é, quando se copia o conteúdo de uma célula para outra, certos endereços na fórmula não se devem alterar, precisam ser fixos, independente do endereço da célula de destino. Por outro lado, quando é necessário que os endereços contidos na fórmula sejam alterados de acordo com a nova posição, usam-se os endereços relativos. Referência Relativa Uma referência relativa de uma célula em uma fórmula/função é simplesmente a combinação do endereço da coluna seguido do endereço da linha. Por exemplo: tem-se na célula D2 a seguinte fórmula: =B2*C2, nesta fórmula todos os endereços das células são relativos, pois quando se copia esta fórmula para a célula D3, esta tornar-se-á =B3*C3, observe que todos os endereços foram atualizados de acordo com a nova posição da fórmula e assim sucessiva- mente para as demais. ! Fórmula e ou funções que possuem célula com endereços relativos, quando são copia-das para outras posições têm seus endereços alterados de acordo com a nova posição. Referência Absoluta Uma referência absoluta de uma célula em uma fórmula/função possui na frente da letra da coluna e/ou na frente do número da linha, o símbolo de $ indicando que aquele endereço não se alterará quando for copiado para um novo endereço. Por exemplo: tem-se na célula E2 a seguinte fórmula: =D2/$D$8, nesta fórmula $D$8 indica que a coluna e a linha estão fixadas. Quando esta fórmula é copiada para a célula E3, ela é alterada para =D3/$D$8, observe que o endereço que possui o $ não foi atualizado, por outro lado, o que não possui, foi, de acordo com a nova posição da fórmula. As referências em uma célula também podem ser Mistas quando utilizamos na mesma fórmulas referên- cias Relativas e Absolutas como no exemplo anterior. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 21 As opções para fixação de uma célula podem ser as seguintes: $C1 Colocar $ antes da letra da coluna para fixar apenas a coluna quando tiver arrastando uma fórmula no sentido horizontal. Neste exemplo somente a coluna C esta fixa. C$1 Colocar $ antes do número da linha para fixar apenas a linha quando tiver arrastando uma fórmula no sentido vertical. Neste exemplo somente a linha 1 esta fixa. $C$1 Colocar $ antes da letra da coluna e antes do número da linha para fixar tanto linha quanto coluna quando tiver arrastando uma fórmula no sentido horizontal e também no vertical. Neste exemplo toda a célula C1 esta fixa. D IC A Fórmula/funções que possuem células com endereços absolutos, quando são copiadas para outras posições, têm os endereços das células com $ inalterados. A tecla de Atalho F4 permite alternar entre as 3 situações previstas, linha, coluna ou am- bas. Opções de Cálculos Em algumas situações como por exemplo, planilha muito grande com diversos cálculos, a mesma pode demorar para processar os dados, pois por padrão, todas as vezes que alteramos o conteúdo de alguma célula, o Excel recalcula toda a planilha automaticamente, o que pode levar um certo tempo dependendo do tamanho da planilha. Para resolver este problema podemos alterar o modo de cálculo de Automático para Manual. Para isso clique na Fórmulas e no grupo Cálculo clique no botão Opções de Cálculo e selecione a opção de- sejada. Se a opção selecionada for a Manual lembre-se que quando for feita a alteração no valor de alguma célula da planilha a mesma não atuali- zará os resultados nas fórmulas automaticamente, você precisara cli- car no comando Calcular Agora que se encontra na guia Fórmulas e no grupo Cálculo. A quantidade do mouse é 15 e o total R$ 180,00 Se a quantidade do mouse for alterada para 30 o total continuará sendo R$ 180,00 pois a Opção de Cálculo foi definida como Manual e nesse caso precisávamos ter clicado no comando Calcular Agora. D IC A Se preferir pode utilizar as seguintes teclas de atalho para atualizar os dados: F9 → Calcula a Pasta de Trabalho Inteira Shift + F9 → Calcula apenas a Planilha Atual (Ativa) Microsoft Excel – Avançado Pag.: 22 Reprodução Proibida Fone: 4121-6166 Conceito de Funções Uma função é um procedimento de cálculo previamente definidos, que determina um resultado. Assim como todo cálculo deve ser iniciada com “=” e de ser seguida de um ou mais parâmetros. = Nome da Função ( Argumentos ) Os argumentos podem ser números, texto, valores lógicos, como VERDADEIRO ou FALSO, matrizes, valores de erro como #N/D ou referências de célula Como dito anteriormente as funções são procedimentos de cálculos pré-definidos que determinam um resultado. As funções podem realizar um cálculo especifico ou simplificar uma fórmula como no exemplo abaixo onde foi simplificada a fórmula da soma das células pela função: Assistente de Função Através do assistente de função, insere-se funções com auxílio passo a passo. Pode-se escolher uma função específica que exista dentrodo Excel, inserindo, quando requisitado pelo assistente, apenas os endereços das células que contêm os valores a serem usados na função, ou então os valores numéricos propriamente ditos. Para utilizar o assistente de funções clique na guia Fórmulas e em seguida clique no botão Inserir Função ou através do botão Inserir Função da Barra de Fórmulas. As funções também podem ser digitadas diretamente na célula. Durante a digitação da função, um pequeno quadro de auxílio para a sintaxe do nome da função poderá ser exibido. Para completar a digitação do nome da função, poderá clicar sobre o nome correspondente, inserindo-o corretamente na célula. D IC A Pode-se também utilizar as setas de direção para selecionar o nome da função e pressionar a tecla TAB para completar a digitação. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 23 Funções Básicas Função Descrição Exemplo SOMA Soma todos os números das células men- cionadas. =SOMA(célula1; célula2; ...) =SOMA(célulainicial:célulafinal) MÁXIMO Retorna o maior valor dentre as células mencionadas. =MÁXIMO(célula1; célula2; ...) =MÁXIMO(célulainicial:célulafinal) MÍNIMO Retorna o menor valor dentre as células mencionadas. =MÍNIMO(célula1; célula2; ...) =MÍNIMO(célulainicial:célulafinal) MÉDIA Retorna a média aritmética dos valores. =MÉDIA(célula1; célula2; ...) =MÉDIA(célulainicial:célulafinal) MAIOR Retorna dentro de um intervalo de dados o maior valor através de um critério especifi- cado na função. =MAIOR(Intervalo;Critério) Critério: Valor a ser retornado 1, 2 , 3... MENOR Retorna dentro de um intervalo de dados o menor valor através de um critério especifi- cado na função. =MENOR(Intervalo;Critério) Critério: Valor a ser retornado 1, 2 , 3... CONT.NÚM Conta quantas células contêm números e também os números na lista de argumen- tos. =CONT.NÚM(intervalo) CONT.VALORES Calcula o número de células não vazias e os valores na lista de argumentos. =CONT.VALORES(Intervalo) CONTAR.VAZIO Conta o número de células vazias no inter- valo especificado. =CONTAR.VAZIO(intervalo) HOJE Retorna a data atual. Esta função fará com que cada vez que você abrir o arquivo a data venha atualizada, você sempre terá o dia correto. =HOJE( ) AGORA Retorna a data e hora atual. Esta função fará com que cada vez que você abrir o ar- quivo a data venha atualizada. =AGORA( ) INT Arredonda o valor para baixo até o número inteiro mais próximo. =INT(Número) =IINT(Fórmula) ARRED Arredonda um número até uma quantidade especificada de dígitos. =ARRED(Número;Nº de dígitos) =ARRED(Fórmula;Nº de dígitos) TRUNCAR Omite a parte fracionária de um número de acordo com quantidade especificada de dí- gitos. =TRUNCAR(Número;Nº de dígitos) =TRUNCAR(Fórmula;Nº de dígitos) MOD Retorna o resto depois de uma divisão de um número por um divisor. O resultado possui o mesmo sinal do divisor. =MOD(Número;Divisor) ALEATÓRIO Retorna um número aleatório entre 0 e 1. Um novo número é retornado toda vez que a planilha é calculada. =ALEATÓRIO( ) Microsoft Excel – Avançado Pag.: 24 Reprodução Proibida Fone: 4121-6166 Repetir linhas e/ou colunas em várias páginas Muitas vezes é necessário repetirmos ou uma linha ou uma coluna em todas as páginas para isso: • Clique na guia Layout de Página, grupo Configurar Página, clique no botão Imprimir Títulos; • Na janela Configurar Página, na guia Planilha, vá até a seção Linhas a repetir na parte supe- rior e clicando no botão de seleção (botão recolher), selecione por exemplo a linha 3. • Selecione Colunas a repetir à esquerda se desejar usar títulos verticais em cada página. • Clique em OK. • As linhas e/ou colunas serão repetidas em todas as páginas da planilha. Abaixo as demais opções desta janela: 1. Área de Impressão - Clique na caixa Imprimir área para selecionar um intervalo de planilha para imprimir e, em seguida, arraste pelas áreas da planilha que você deseja imprimir. 2. Imprimir: • Linhas de Grade - Marque a caixa de seleção Linhas de Grade para imprimir linhas de grade de célula verticais e horizontais nas planilhas. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 25 • Preto e branco - Marque a caixa de seleção Preto e branco se tiver formatado dados com cores, mas estiver utilizando uma impressora monocromática. Se você estiver usando uma impressora colorida, a seleção dessa opção pode reduzir o tempo de impressão. • Qualidade Rascunho – Marque a caixa de seleção Qualidade Rascunho para reduzir o tempo de impressão. Quando essa opção é selecionada, o Microsoft Excel não imprime nem as linhas de grade nem a maior parte dos elementos gráficos. • Títulos de linha e coluna – Marque a caixa de seleção Títulos de linha e coluna para impri- mir números de linha e letras de coluna em um estilo de referência A1 ou linhas e colunas numeradas em um estilo de referência L1C1. 3. Ordem da página – Clique em Abaixo e acima ou Acima e abaixo para controlar a ordem na qual os dados são numerados e impressos quando não couberem em uma página. A figura de exemplo mostra a direção na qual seu documento será impresso quando você selecionar uma dessas opções. 4. Opções – Clique em Opções para definir opções específicas para a impressora selecionada. Para obter maiores informações, consulte a documentação da sua impressora. Congelar Painéis Com o recurso de Congelar Painéis é possível manter os rótulos de linhas e colunas visíveis enquanto rola. • Para congelar o painel horizontal superior, selecione a linha abaixo do lugar onde você de- seja que apareça a divisão. • Para congelar o painel vertical esquerdo, selecione a coluna à direita do lugar onde você de- seja que apareça a divisão. • Para congelar os painéis superior e esquerdo, clique na célula abaixo e à direita do lugar onde você deseja que apareça a divisão. Na guia Exibição, no grupo Janela, clique no botão Congelar Painéis. Para descongelar clique na guia Exibição, no grupo Janela e clique Congelar Painéis e em seguida, no comando Descongelar painéis. Microsoft Excel – Avançado Pag.: 26 Reprodução Proibida Fone: 4121-6166 Exibir duas partes de uma planilha ao mesmo tempo Posicione o cursor no local onde deseja dividir a janela e em seguida, na Guia Exibição, no grupo Ja- nela, Clique no botão Dividir. O exemplo abaixo mostra uma janela dividida: Para voltar ao normal clique na Guia Exibição, no grupo Janela, Clique no botão Dividir. Se a janela estiver dividida, este passo irá remover a divisão. Para voltar ao normal clique na Guia Exibição, no grupo Janela, Clique no botão Dividir. Se a janela estiver dividida, este passo irá remover a divisão. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 27 Gerar Arquivos PDF ou XPS Agora você pode exportar dados para um formato PDF (Portable Document Format) ou XPS (XML Paper Specification) para impressão, lançamento e distribuição por e-mail, desde que você primeiramente instale o Publish como suplemento de PDF ou XPS. A exportação de uma planilha para um arquivo PDF ou XPS permite que você capture informações facilmente distribuído que mantém todas as suas características de formatação, mas não exige que outras pessoas tenham o Excel instalado em seus computadores para imprimir ou revisar sua saída. Será possível exportar para um arquivo de formato PDF ou XPS a partir do Office 2007 somente após a instalação do suplemento. Seguem algumas informações sobre os formatos de arquivo: • PDF (Portable Document Format) o PDF é um formato de arquivo eletrônico de layout fixo que mantém a formatação do documento e habilita o compartilhamento de arquivos. O formato PDF assegura que na exibição ou impressão do arquivo online, seja mantido exatamente o formato planejado e que os dados do arquivo nãopossam ser facilmente copiados ou alterados. O formato PDF também é útil para documentos que serão reproduzidos por métodos de impressão comercial. • XPS (XML Paper Specification) O XPS é um formato de arquivo eletrônico que mantém a formatação do documento e habilita o compartilhamento de arquivos. O formato XPS assegura que na exibição ou impressão do arquivo online, ele mantenha exatamente o formato planejado e que os dados do arquivo não possam ser facilmente copiados ou alterados. Para gerar um arquivo em PDF siga as instruções abaixo: • Clique na planilha a ser exportada; • Clique no Menu Arquivo, em seguida clique em clique na opção Exportar, clique em Criar Documento PDF/XPS e clique no botão Criar PDF/XPS; • Na janela que se abre selecione o local onde será salvo o arquivo. Renome o arquivo; Microsoft Excel – Avançado Pag.: 28 Reprodução Proibida Fone: 4121-6166 • Clique no botão Opções caso queira modificar alguma propriedade; • Clique em OK e em seguida no botão Publicar. Pronto o arquivo será criado. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 29 Ajuda do Microsoft Excel Sempre que você tiver alguma dúvida sobre uma função ou ferramenta do Excel utilize o botão de Ajuda do Microsoft Excel para consultar maiores informações sobre ferramentas, função e visualizar exemplos de aplicação das mesmas. Para utilizar a Ajuda do Microsoft Excel clique no ícone de ajuda da Barra de Títulos ou pressione a tecla F1. Na janela que se abre digite o assunto a ser pesquisado e pressione Enter: Basta agora clicar nos assuntos e ler o conteúdo. Microsoft Excel – Avançado Pag.: 30 Reprodução Proibida Fone: 4121-6166 AULA 01 Mensagens de Erro Mensagem O que ocorreu? #DIV/0! Existe uma fórmula que exibiu este resultado que está utilizando alguma referência para dividir por zero. É preciso corrigir a célula, ou alterar o valor para um número diferente de 0 na célula usada como divisor. #N/D Esta mensagem significa que nenhum valor está disponível neste momento na célula usada como referência ou fonte de dados. Você pode inserir #N/D quando desejar que esta célula não faça cálculos tendo como valor 0, e sim espere até ser digitado algum valor. Enquanto o valor não for digitado na célula referente, o resultado da fórmula será #N/D. Se for algum erro na fórmula, possivelmente está na referência errada. #NOME? Aparece quando se usa um nome estranho ao Excel. Pode ser divido ao fato de você não ter definido o nome antes, ou digitou o nome errado (Exemplo SONA, ao invés de SOMA). Se você está inserindo texto junto com fórmula deve colocá-lo entre aspas. #NULO! Existe sempre quando você se refere a uma interseção de áreas que não se interceptam. Por exemplo: (A10:A20) (A21:D21) #NUM! Está existindo algum problema com algum número digitado. Você digitou algum número, somatória, matriz, que não existe. Por exemplo: =RAIZ(-1). #REF! Ocorre quando você utilizou uma referência errada. #VALOR! Ocorre quando você usa algum tipo estranho de argumento ou operando. Você pode, por exemplo, digitar algo repetido ou algum caractere desconhecido. Trabalhando com Nomes de Células Você pode dar o nome que quiser a uma ou mais células. Selecione a área desejada, clique na Caixa de Nome, digite um nome para a célula ou intervalo e termine pressionando a tecla Enter. Cada nome registrado passa a fazer parte de uma lista na Caixa de Nome, localizada à esquerda da barra de fórmulas. Para selecionar a área correspondente, basta escolher o nome nessa lista. Para editar, excluir ou visualizar nomes de uma Pastra de Trabalho clique na guia Fórmulas e no grupo Nomes Definidos clique na opção Gerenciador de Nomes. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 31 Na janela que se abre surgirão todos os nomes utilizados na Pasta de trabalho. Basta selecionar o nome que deseja alterar ou excluir e acionar o comando correspondente. D IC A Para abrir o Gerenciador de Nomes pode ser utilizada a tecla de atalho CTRL + F3. Funções de Data e Hora O Excel armazena datas como números de série sequenciais para que eles possam ser usados em cál- culos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2016 é o número de série 42.370 porque está 42.370 dias após 1° de janeiro de 1900. Já quando trabalhamos com horas no Excel o mesmo entende que 01:00 é o mesmo que 1/24 do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666... Dessa forma o Excel expressa a hora como uma fração decimal de um dia de 24 horas, obtendo um número entre 0 e 1. Por exemplo para meia-noite é atribuído o valor 0, de forma que meio-dia será 0,5. 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 Microsoft Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =HOJE( ) Microsoft Excel – Avançado Pag.: 32 Reprodução Proibida Fone: 4121-6166 AGORA Retorna o número de série sequencial da data e hora atuais. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =AGORA( ) DATA Retorna o número de série sequencial que representa uma determinada data. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =DATA(ano;mês;dia) DIA Retorna o dia de uma data representado por um número de série. O dia é dado como um inteiro que varia de 1 a 31. =DIA(núm_série) • Núm_série é a data do dia que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. 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). =MÊS(núm_série) • Núm_série é a data do mês que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. ANO Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de 1900-9999. =ANO(núm_série) • Núm_série é a data do ano que você deseja localizar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. DIA.DA.SEMANA Retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando de 1 (do- mingo) a 7 (sábado), por padrão. =DIA.DA.SEMANA(núm_série;[retornar_tipo]) Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 33 • Núm_série - Obrigatório. Um número sequencial que representa a data do dia que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de ou- tras fórmulas ou funções. Retornar_tipo - Opcional. Um número que determina o tipo do valor retornado. Retornar_Tipo Número Retornado 1 ou omitido Números 1 (domingo) a 7 (sábado). Como em versões anteriores do Excel. 2 Números 1 (segunda-feira) a 7 (domingo). 3 Números 0 (segunda-feira) a 6 (domingo). 11 Números 1 (segunda-feira) a 7 (domingo). 12 Números 1 (terça-feira) a 7 (segunda-feira). 13 Números 1 (quarta-feira) a 7 (terça-feira). 14 Números 1 (quinta-feira) a 7 (quarta-feira). 15 Números 1 (sexta-feira) a 7 (quinta-feira). 16 Números 1 (sábado) a 7 (sexta-feira). 17 Números 1 (domingo) a 7 (sábado). No Exemplo acima como utilizamos o padrão da função o 4 (qua), 5 (qui) e 6 (sex). DIAS360 Retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). Use essa função para ajudar no cálculo de pagamentos, se o seu sistema contábil estiver baseado em doze meses de 30 dias. = DIAS360(data_inicial;data_final;[método])• Data_inicial, data_final - Obrigatório. As duas datas entre as quais você deseja saber o número de dias. Se data_inicial ocorrer depois de data_final, a função DIAS360 retornará um número ne- gativo. As datas devem ser inseridas com o uso da função DATA, ou devem ser derivadas dos resultados de outras fórmulas ou funções. • Método - Opcional. Um valor lógico que especifica o método a ser usado cálculo, o americano ou o europeu. Método Definido FALSO ou omitido Método americano (NASD). Se a data inicial for o último dia de um mês, ela se tornará igual ao dia 30 do mesmo mês. Se a data final for o último dia do mês e a data inicial for anterior ao trigésimo dia de um mês, a data final se tornará igual ao dia primeiro do próximo mês. Caso contrário, a data final se tornará igual ao trigésimo dia do mesmo mês. VERDADEIRO Método europeu. A data inicial e final que ocorrer no dia 31 de um mês se tornará igual ao trigésimo dia do mesmo mês. Microsoft Excel – Avançado Pag.: 34 Reprodução Proibida Fone: 4121-6166 ! Em uma função quando o argumento, na sintaxe, aparecer entre colchetes [ ] é porque o mesmo é opcional, ou seja, não é obrigatório informar. Por exemplo: a função =DIAS360(data_inicial;data_final;[método]) onde o argumento [método] é opcional. NÚMSEMANA Retorna o número da semana de uma data específica. Por exemplo, a semana que contém 1 de janeiro é a primeira semana do ano e é numerada semana 1. Há dois sistemas usados para essa função: • Sistema 1 - A semana que contém 1 de janeiro é a primeira semana do ano e é numerada semana 1. • Sistema 2 - A semana que contém a primeira quinta-feira do ano é a primeira semana do ano e é numerada como semana 1. Este sistema é a metodologia especificada na norma ISO 8601, nor- malmente conhecida como sistema europeu de numeração de semanas. =NÚMSEMANA(núm_série;[tipo_retorno]) A sintaxe da função NÚMSEMANA tem os seguintes argumentos: • Núm_série - Obrigatório. Uma data na semana. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. • Tipo_retorno Opcional. É um número que determina em que dia a semana começa. O valor padrão é 1. Retornar_tipo A semana começa em Sistema 1 ou omitido Domingo 1 2 Segunda-feira 1 11 Segunda-feira 1 12 Terça-feira 1 13 Quarta-feira 1 14 Quinta-feira 1 15 Sexta-feira 1 16 Sábado 1 17 Domingo 1 21 Segunda-feira 2 DATAM 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. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 35 =DATAM(data_inicial;meses) A sintaxe da função DATAM tem os seguintes argumentos: • Data_inicial - Obrigatório. Uma data que representa a data inicial. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. • Meses - Obrigatório. O número de meses antes ou depois de data_inicial. Um valor positivo para meses gera uma data futura; um valor negativo gera uma data passada. FIMMÊS Retorna o último dia do mês antes ou depois de um dado número de meses. A sintaxe da função FIMMÊS tem os seguintes argumentos: Data_inicial - Obrigatório. Uma data que representa a data inicial. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Meses - Obrigatório. O número de meses antes ou depois de data_inicial. Um valor positivo para meses gera uma data futura; um valor negativo gera uma data passada. DIATRABALHOTOTAL Retorna o número de dias úteis inteiros entre data_inicial e data_final. Os dias úteis excluem os fins de semana e quaisquer datas identificadas em feriados. Use DIATRABALHOTOTAL para calcular os bene- fícios aos empregados que recebem com base no número de dias trabalhados durante um período espe- cífico. =DIATRABALHOTOTAL(data_inicial;data_final;[feriados]) A sintaxe da função DIATRABALHOTOTAL tem os seguintes argumentos (argumento: um valor que for- nece informações a uma ação, um evento, um método, uma função ou um procedimento.): • Data_inicial Obrigatório. Uma data que representa a data inicial. • Data_final Obrigatório. A data que representa a data final. • Feriados - Opcional. Um intervalo opcional de uma ou mais datas a serem excluídas do calendário de dias de trabalho, como feriados estaduais e federais, e feriados móveis. A lista pode ser um intervalo de células que contém as datas. Em nosso exemplo o intervalo de E2:E16 que contém as datas dos feriados nacionais e regionais foi renomeado para “Feriados” para facilitar a utilização do mesmo na função. Microsoft Excel – Avançado Pag.: 36 Reprodução Proibida Fone: 4121-6166 DIATRABALHO 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. =DIATRABALHO(data_inicial;dias;[feriados]) A sintaxe da função DIATRABALHO tem os seguintes argumentos: • Data_inicial - Obrigatório. Uma data que representa a data inicial. • Dias - Obrigatório. O número de dias úteis antes ou depois de data_inicial. Um valor positivo para gera uma data futura; um valor negativo gera uma data passada. • Feriados - Opcional. Uma lista opcional com uma ou mais datas a serem excluídas do calendário de trabalho, como feriados estaduais, federais e flutuantes. A lista pode ser um intervalo de células que contém as datas. Em nosso exemplo o intervalo de E2:E16 que contem as datas dos feriados nacionais e regionais foi renomeado para “Feriados” para facilitar a utilização do mesmo na função. DIATRABALHOTOTAL.INTL Retorna o número de dias úteis inteiros entre duas datas usando parâmetros para indicar quais e quantos dias são dias de fim de semana. Dias de fim de semana e quaisquer dias especificados como feriados não são considerados como dias úteis. =DIATRABALHOTOTAL.INTL(data_inicial;data_final;[fim de semana];[feriados]) A sintaxe da função DIATRABALHOTOTAL.INTL tem os seguintes argumentos • Data_inicial e data_final Obrigatório. As datas para as quais a diferença deve ser calculada. Data_inicial pode ser anterior, igual ou posterior a data_final. • Fim de semana Opcional. Indica os dias da semana que são dias de fim de semana e que não estão incluídos no número de dias úteis inteiros entre data_inicial e data_final. Fim de semana é um número de fim de semana ou uma cadeia de caracteres que especifica quando ocorrem fins de semana. Valores numéricos de fim de semana indicam os dias de fim de semana a seguir: número de fim de semana Dias de fim de semana javascript:AppendPopup(this,'374228515_2') Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 37 1 ou não especificado Sábado, Domingo 2 Domingo, Segunda 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas domingo 12 Apenas segunda-feira 13 Apenas terça-feira 14 Apenas quarta-feira 15 Apenas quinta-feira 16 Apenas sexta-feira 17 Apenas sábado • Feriados - Opcional. Um conjunto opcional de uma ou mais datas que devem ser excluídas do calendário de dias úteis. Feriados devem ser um intervalo de células que contêm as datas ou uma constante de matriz dos valores de série que representam essas datas. A ordem de datas ou valores de série em feriados podem ser arbitrários. • No exemplo a seguir consideramos apenas o domingo como final de semana. DIATRABALHO.INTL Retorna o número de série da data antes ou depois de um número específico de dias úteis com parâmetros de fim de semanapersonalizados. Parâmetros de fim de semana indicam quais e quantos dias são de fim de semana. Dias de fim de semana e dias especificados como feriados não são considerados dias úteis. =DIATRABALHO.INTL(data_inicial;dias;[fim de semana];[feriados]) A sintaxe da função DIATRABALHO.INTL tem os seguintes: • Data_inicial Obrigatório. A data de início, truncada para que apareça como um número inteiro. • Dias Obrigatório. O número de dias úteis antes ou depois de data_inicial. Um valor positivo gera uma data futura; um valor negativo gera uma data passada; um valor zero gera uma data_inicial. O deslocamento de dia é truncado para que apareça como um número inteiro. • Fim de Semana Opcional. Indica os dias da semana que são de fim de semana e não são consi- derados dias úteis. Fim de Semana é um número de fim de semana ou uma cadeia de caracteres que especificam quando ocorrem os fins de semana. Valores numerais de fim de semana indicam os dias de fim de semana a seguir: Microsoft Excel – Avançado Pag.: 38 Reprodução Proibida Fone: 4121-6166 número de fim de semana Dias de fim de semana 1 ou não especificado Sábado, Domingo 2 Domingo, Segunda 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas domingo 12 Apenas segunda-feira 13 Apenas terça-feira 14 Apenas quarta-feira 15 Apenas quinta-feira 16 Apenas sexta-feira 17 Apenas sábado • Feriados - Opcional. Um conjunto opcional de uma ou mais datas que serão excluídas do calendário de dias úteis. Feriados devem ser um intervalo de células que contenham as datas ou uma constante de matriz dos valores de série que representam essas datas. A ordem de datas ou valores consecutivos em feriados pode ser arbitrária. • No exemplo a seguir consideramos apenas o domingo como final de semana. D IC A Valores de cadeias de caracteres de fim de semana têm sete caracteres de comprimento e cada caractere da cadeia representa um dia da semana, começando pela segunda-feira. 1 representa um dia que não é útil e 0 representa um dia útil. Somente os caracteres 1 e 0 são permitidos na cadeia de caracteres. 1111111 é uma cadeia inválida. Por exemplo, 0010001 resultaria em um fim de semana, ou seja, quarta e domingo. SEG TER QUA QUI SEX SÁB DOM 0 0 1 0 0 0 1 =DIATRABALHOTOTAL.INTL(A2;B2;"0010001";FERIADOS) Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 39 DATADIF A função DATADIF é uma função não documentada do Excel, ou seja, ela existe e funciona muito bem. No entanto ela não faz parte da documentação do Excel. Esta função é muito útil para o cálculo de idades e tempo de serviço. =DATADIF(Data_Inicial; Data_Final; “Tipo”) Onde Tipo pode ser: Tipo Retorna "Y" O número de anos completos no período. "M" O número de meses completos no período. "D" O número de dias no período. "MD" A diferença entre os dias na DataInicial e DataFinal. Os meses e anos das datas são ignorados. "YM" A diferença entre os meses na DataInicial e DataFinal. Os dias e anos das datas são ignorados. "YD" A diferença entre os dias da DataInicial e DataFinal. Os anos das datas são ignorados. D IC A No exemplo acima se for utilizada a fórmula abaixo: =DATADIF(B2;B3;"y")&" anos, "&DATADIF(B2;B3;"ym")&" meses e "&DATADIF(B2;B3;"md")&" dias" Você irá obter: 35 anos, 1 meses e 20 dias 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.). =HORA(núm_série) • Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")). MINUTO Retorna os minutos de um valor de tempo. O minuto é dado como um número inteiro, que vai de 0 a 59. =MINUTO(núm_série) • Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 Microsoft Excel – Avançado Pag.: 40 Reprodução Proibida Fone: 4121-6166 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")). SEGUNDO Retorna os segundos de um valor de hora. O segundo é fornecido como um inteiro no intervalo de 0 a 59. =SEGUNDO(núm_série) • Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")). TEMPO Retorna o número decimal para uma determinada hora. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =TEMPO(hora, minuto, segundo) D IC A Para inserir a data ou a hora atual em uma célula podemos utilizar as teclas de atalho: Ctrl + ; → Inseri a data atual do sistema. Ctrl +Shift + : → Inseri a hora atual do sistema. Função MOD MOD Retorna o resto depois da divisão de um número por um divisor. O resultado possui o mesmo sinal que divisor. =MOD(núm;divisor) • Núm é o número ou a célula para o qual você deseja encontrar o resto. • Divisor é o número pelo qual você deseja dividir o número. Se divisor for 0, Resto retornará o valor de erro #DIV/0!. Exemplos: =Mod(3; 2) é igual a 1 =Mod(-3; 2) é igual a 1 =Mod(3; -2) é igual a -1 Temos uma quantidade total de peças para a montagem de alguns Kits. Sabemos a quantidade de peças por Kit. Usando a fórmula =INT(C4/D2) temos a quantidade de Kits completos que conseguiremos montar. OBS: A função INT descarta os números a direita da vírgula.. Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 41 Agora utilizando a função MOD conseguiremos saber se haverá alguma sobre de peças ou se a quantidade é exata. Para isso utilizamos a fórmula =MOD(C2;D2). Cálculos com Horas Relembrando: quando trabalhamos com horas no Excel o mesmo entende que 01:00 é o mesmo que 1/24 do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666... Dessa forma o Excel expressa a hora como uma fração decimal de um dia de 24 horas, obtendo um número entre 0 e 1. Por exemplo para meia-noite é atribuído o valor 0, de forma que meio-dia será 0,5. Na Célula E4 poderíamos realizado o cálculo das horas trabalhadas com a fórmula =D4-C4 onde D4 é a hora da saída e C4 a hora de entrada. Porém, no caso de um funcionário que entra as 22:00 e sai às 06:00 do dia seguinte. O que fazer para calcular as horas trabalhadas? Se você fizer =Saída – Entrada, o resultado será negativo em alguns casos e, no sistema de datas 1900 o Excel não exibe horas negativas. Uma forma para efetuar este cálculo é usando a função MOD da seguinte forma: =MOD(D4-C4;1) Microsoft Excel – Avançado Pag.: 42 Reprodução Proibida Fone: 4121-6166 Onde o número 1 representa um intervalo de 24:00 e quando o MOD faz a subtração e divide por 1 ele mantém o resultado com o sinal do divisor, nesse caso o valor será positivo. A célula E14 irá armazenar o total de horas da coluna. Foi utilizado o botão Autosoma para obter o resultado igual a 04:10. Está evidente que este valor está “errado”. No entanto, o que está acontecendo é apenas uma questão de formatação pois para o Excel, o dia só tem 24:00 horas portanto, em sua formatação padrão, a cada 24:00 hs, ele começa a contar novamente. Este resultado então serácorrigido simplesmente trocando a formatação. Para isto, clique na Página Inicial, no grupo Número, clique no botão expansão para abrir a janela Formatar Células. Clique na categoria Personalizado e digite no campo Tipo o formato [h]:mm para exibir o total das horas e clique em OK. OBS: Na janela de Formatar células na categoria Hora possui um formato que pode ser utilizado para esta situação basta selecionar o formato 37:30:55. Para calcularmos o Valor a Pagar podemos utilizar duas fórmulas como a seguir: Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 43 Para o Excel, 1 hora é 1/24 avos do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666. Por isso é que conseguimos colocar data e hora em uma única célula. Isto quer dizer que a data corresponde à parte inteira e a hora à parte decimal do número. D IC A Os valores acima podem ser apresentados de diversas formas, bastando para isso apli- car uma Formatação Personalizado na célula conforme exemplos abaixo. Para aplicar a formatação, clique na Página Inicial, no grupo Número, clique no botão expansão para abrir a janela Formatar Células. Clique na categoria Personalizado e digite no campo Tipo o formato desejado para a célula e clique em OK. Exemplos: Exercícios Propostos Para fixar o conteúdo da AULA 01 e tirar possíveis dúvidas realize os exercícios que se encontram na pasta desafios e em caso de dúvidas você poderá tirar com seu instrutor durante o treinamento ou atra- vés do e-mail: duvidas@digicad.com.br Microsoft Excel – Avançado Pag.: 44 Reprodução Proibida Fone: 4121-6166 AULA 2 Funções Lógicas SE Esta é uma função lógica que retorna um valor baseado no teste lógico. A função SE retornará um valor se uma condição que você especificou for considerada VERDADEIRO e um outro valor se essa condição for considerada FALSO. • teste_lógico - Obrigatório. Qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. • valor_se_verdadeiro - Opcional. O valor que você deseja que seja retornado se o argumento teste_lógico for considerado VERDADEIRO. • valor_se_falso - Opcional. O valor que você deseja que seja retornado se o argumento teste_lógico for considerado FALSO. Executando a função SE via assistente: Utilizaremos o exemplo a seguir para determinar a situação de cada vendedor, caso o Total atenda a um determinado critério, ou seja, se o Total for maior ou igual a Meta será exibida a mensagem “Parabéns” caso contrário será exibida a mensagem “Melhorar”. Para facilitar o cálculo podemos nomear a célula D4 para Meta através da Caixa de Nome. Verdadeiro Falso D8>=Meta "Melhorar" "Parabéns" =se(TESTE;VALOR caso verdadeiro;VALOR caso falso) TESTE VALOR caso verdadeiro VALOR caso falso VF Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 45 D IC A O Assistente de funções pode ser utilizado para quaisquer funções do Excel. Nele pode- mos ver uma lista de todas as funções do Excel, uma breve descrição sobre as mesmas e a sintaxe da função. Caso necessite pode solicitar ajuda mais detalhada clicando na opção “Ajuda sobre esta função”. Coloque o cursor na célula que deve receber o resultado da fórmula. Clique no botão (Inserir Função) da barra de fórmulas para abrir o Assistente de função ou clique na guia Fórmulas, no grupo Biblioteca de Funções e clique no botão Inserir Função. Será exibida a janela a seguir: Na lista “selecione uma categoria” escolha Lógico; Na lista “Selecione uma Função” escolha a função SE; Clique no botão OK. A janela a seguir será exibida: Microsoft Excel – Avançado Pag.: 46 Reprodução Proibida Fone: 4121-6166 No campo Teste_lógico insira o teste/comparação que deve ser feito, como por exemplo se o Total for maior ou igual a Meta. No nosso caso a planilha exemplo o teste fica =D8>=Meta; No campo Valor_se_verdadeiro coloque o valor a ser retornado se o teste lógico retornar verdadeiro por exemplo “Parabéns”; No campo Valor_se_falso coloque o valor a ser retornado se o teste lógico retornar falso por exemplo “Melhorar”; Aninhamento da Função SE A função lógica SE pode ser aninhada de forma que uma das respostas seja uma nova função SE. Utilizaremos o exemplo anterior para calcular a comissão de cada vendedor. Os critérios serão: Se o Total for maior ou igual a Meta, o vendedor receberá 2% sobre o Total, se o Total for maior ou igual a R$ 100.000,00, o vendedor receberá comissão de 1% sobre o total, caso contrário o vendedor não ganhará comissão. =se(TESTE 1;VALOR caso verdadeiro T1;se(TESTE 2;VALOR caso verdadeiro T2;VALOR caso falso T2)) Teste 1 VALOR caso verdadeiro teste 2 VALOR caso verdadeiro teste 1 VF Teste 2 VALOR caso falso teste 2 VF VALOR caso falso teste 1 VALOR caso falso teste 1 Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 47 Para realizarmos nosso cálculo utilizaremos a seguinte fórmula: =SE(D8>=Meta;D8*2%;SE(D8>=100000;D8*1%;0)) E Retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. Um uso comum para a função E é expandir a utilidade de outras funções que realizam testes lógicos. Por exemplo, a função SE realiza um teste lógico e, em seguida, retornará um valor se o teste for avaliado como VERDADEIRO e outro valor se o teste for avaliado como FALSO. Usando a função E como argumento teste_lógico da função SE, você pode testar várias condições diferentes em vez de apenas uma. D8>=Meta D8*1% =D8*2% D8>=100000 0 Verdadeiro Verdadeiro Falso Falso =E(teste 1;teste 2;teste 3;teste 4;...) =E(teste 1;teste 2;teste 3;teste 4;...) TESTE 1 TESTE 2 TESTE 3 TESTE 4 FALSO TESTE 1 TESTE 2 TESTE 3 TESTE 4 VERDADEIRO Microsoft Excel – Avançado Pag.: 48 Reprodução Proibida Fone: 4121-6166 A sintaxe da função E tem os seguintes argumentos: • lógico1 - Obrigatório. A primeira condição que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO. • lógico2, ... - Opcional. Condições adicionais que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO, até um máximo de 255 condições. No exemplo a seguir, para calcularmos a comissão, utilizaremos 2 critérios, ou seja, se o Total for maior ou igual a Meta e a Quantidade for maior que 300, o vendedor terá uma comissão de 2% sobre o Total, caso contrário não receberá comissão. A fórmula utilizada na célula E6 para o resultado foi: =SE(E(D6>=Meta;C6>300);D6*2%;0) Observe que neste exemplo a maioria dos vendedores não receberão comissão, pois não atendem aos critérios estabelecidos. OU Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS. Um uso comum para a função OU é expandir a utilidade de outras funções que realizam testes lógicos. Semelhante a função E só que precisa apenas de 1 teste_lógico VERDADEIRO para ser VERDADEIRO. =OU(lógico1; [lógico2], ...) • lógico1 - Obrigatório. A primeira condição que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO. E(D6>=Meta;C6>300) 0 =D6*2% Verdadeiro Falso Microsoft Excel – Avançado Fone: 4121-6166 Reprodução Proibida Pag.: 49 • lógico2, ... - Opcional. Condições adicionais que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO, até um máximo de 255 condições. No exemplo a seguir para calcular a comissão utilizaremos 2 critérios, ou seja, se o Total for maior ou igual a Meta ou a Quantidade for maior que 200, o vendedor uma comissão de 2% sobre o Total, caso contrário não receberá comissão. A fórmula utilizada na célula E6 para o resultado foi: =SE(OU(D6>=Meta;C6>200);D6*2%;0) Neste exemplo
Compartilhar