Baixe o app para aproveitar ainda mais
Prévia do material em texto
Curso de Excel Nível Intermediário Gestão e Negócios MINISTÉRIO DE TRABALHO Ministro do Trabalho Helton Yomura Secretário de Políticas Públicas de Emprego Marcos Orlando Menezes Ferreira Diretor do Departamento de Políticas de Empregabilidade Higino Brito Vieira UNIVERSIDADE DE BRASÍLIA Reitora Márcia Abrahão Moura Vice-reitor Enrique Huelva Decana de Pesquisa e Inovação Maria Emília Machado Telles Walter Decana de Extensão Olgamir Amancia Coordenação do Projeto Qualifica Brasil Thérèse Hofmann Gatti Rodrigues da Costa (Coordenadora Geral) Wilsa Maria Ramos Valdir Adilson Steinke Rafael Timóteo de Sousa Jr Instituto Brasileiro de Informação em Ciência e Tecnologia – IBICT Cecília Leite - Diretora Tiago Emmanuel Nunes Braga Realização Instituto de Artes (IDA-UnB), Instituto de Psicologia (IP-UnB), Instituto de Letras (LET-UnB), Departamento de Engenharia Elétrica (ENE – UnB), Departamento de Geografia (GEA – UnB), Faculdade de Ciência da Informação (FCI-UnB). Apoio Secretaria de Educação Profissional e Tecnológica (SETEC-MEC) Gestão de Negócios e Tecnologia da Informação Loureine Rapôso Oliveira Garcez Wellington Lima de Jesus Filho Coordenação da Unidade de Pedagogia Wilsa Maria Ramos Danielle Xabregas Pamplona Nogueira Lívia Veleda Sousa e Melo Coordenação do Núcleo de Produção de Materiais Janaina Angelina Teixeira Autor Pedro Veloso Designer Instrucional Janaina Angelina Teixeira Danielle Xabregas Pamplona Nogueira Nina Cláudia de Assunção Mello Lívia Veleda Sousa e Melo Projeto Gráfico e Diagramação Sanny Caroline Saraiva Sousa Patrícia Fernandes Faria Nathalia Delgado Gomes Ilustradores Ana Maria Silva Sena Pereira Andresa Oliveira Augstroze Aguiar Amanda Morais Silva Camilla Santos Dantas Eugênia Versiani Souza Carvalho Gabriel Victor Alves Meireles João Victor Silva Araújo Equipe de audiovisual João Paulo Biage (Jornalista) Ig Uractan (Produtor Audiovisual e Animador) Maurício Neves (Produtor Audiovisual) Bruno Lara (Jornalista) Raíssa Ferreira (Animadora) Rodrigo Gomes (Fotógrafo e Videografista) Desenvolvimento de Ambiente Virtual de Aprendizagem Osvaldo Corrêa Patrícia Fernandes Faria Ficha Técnica Este trabalho está licenciado com uma Licença Creative Commons - Atribuição-NãoComercial-CompartilhaIgual 4.0 Internacional. Excel - Nível Intermediário Descrição Resultados do Aprendizado Excel Nível Intermediário 3 Depois de estudar este curso, você deve ser capaz de: • Aprender funções condicionais e lógicas para a criação de planilhas. • Organizar planilhas com muitos dados e extrair informações. • Agrupar dados e criar níveis de proteção em uma planilha. Neste curso, vamos aprofundar nossos conhecimentos em Excel. Para nos ajudar nos estudos, vamos conhecer Carlos e Rita, uma dupla que vai nos acompanhar no curso! Competências Conheça as competências mobilizadas no curso: • Seguir rotinas ligadas às atividades financeiras e empresariais. • Utilizar raciocínio lógico matemático para resolução de problemas práticos que envolvam gestão de negócios. • Administrar recursos financeiros, físicos e materiais do ambiente de trabalho. TEMA 01 TEMA 02 TEMA 03 Aumentar a produtividade com o uso de funções essenciais | 20 horas. Trabalhar com vínculos e referências fixas e novas funções | 12 horas. Melhorar a apresentação e a proteção dos dados| 8 horas. Excel Nível Intermediário 4 Olá, trabalhador(a)! Sou Rita, uma especialista em Excel, e estou aqui para atender o convite do meu amigo Carlos, que já sabe algumas coisas do Excel depois de ter feito o curso de Introdução ao Excel. Ela me pediu para ensinar novos comandos e funções desse poderoso software. Olá, gente! Pois é, estou precisando aprender mais. Mas por que você diz que o Excel é tão poderoso assim? Você sabia que o Excel é um dos programas mais populares que existem e é a mais completa ferramenta que dispomos hoje para a criação e uso de planilhas eletrônicas? Veja, então, alguns motivos para você realizar esse curso : • O Excel é um software mundialmente conhecido e utilizado em quase todas as empresas. • Existem várias empresas que estão procurando profissionais com habilidades avançadas com o Excel. Faça uma pesquisa rápida na internet e verá quantas empresas ofertam vagas exigindo que os candidatos saibam manipular bem o Excel. • O Excel será utilizado por muitos e muitos anos. De acordo com o Diretor Executivo da Microsoft, Satya Nadella, o Excel é a melhor ferramenta da Microsoft. • Quanto maior seu conhecimento com o Excel, maior será sua capacidade de elaborar relatórios e automatizar suas tarefas. Ganhará tempo! • O Excel pode desenvolver seu raciocínio lógico e colaborar com seu desenvolvimento profissional e pessoal. • Além de tudo isso, nenhum conhecimento será perdido e seu currículo será valorizado no mercado. Então, aproveite cada tela e vídeo deste este curso e dê o seu melhor. Lembre-se que tudo depende do seu empenho e persistência. Já dizia um grande profissional, “A persistência é o caminho para o êxito” (Charlie Chaplin). Vamos aos estudos? Que legal, Rita! Estou precisando melhorar minhas habilidades para conseguir uma melhor posição no mercado de trabalho. Prepare-se para as muitas perguntas que vou fazer. Tema 01 Aumentar a produtividade com o uso de funções essenciais. Neste tema, vamos estudar como é possível melhorar a produtividade do nosso trabalho quando usamos o Excel. Para isto, vamos aprender funções condicionais e lógicas para a criação de planilhas. É muito importante que exercite os passos a passos indicados e, sempre que necessário, refaça-os. Excel - Nível Intermediário Fixar Excel na Barra de Tarefas Excel Nível Intermediário - Tema 01 6 Ícone do Excel 2010 Microsoft Office 2010 Área de trabalho e barra de ferramentas no Windows Boa pergunta, Carlos. Podemos deixar o ícone do Excel na Área de trabalho ou na Barra de tarefas. Rita, usarei muitas vezes o Excel nos próximos dias, como faço para deixá-lo mais aparente? Calma, Rita! O que é ícone?? E a Área de trabalho e a Barra de tarefas? Ah sim... Vamos por partes. Ícone é o símbolo que representa um programa. No caso do Excel 2010, é este o ícone. Assim que seu computador for iniciado, a tela que aparece é chamada de Área de trabalho. também conhecida como Desktop. A Barra de tarefas fica localizada na parte inferior da área de trabalho. Nela, você poderá ver nela todos os programas que estão abertos ou fixos. Área de trabalho Barra de ferramentas Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 7 Ícone do Excel na barra de ferramentas Vamos lá, Carlos! Siga os passos a seguir... Perceba que ícone do Excel aparecerá na Barra de Tarefas mesmo com o programa fechado. Agora que já incluímos o Excel na Barra de Tarefa, vamos criar um atalho na Área de trabalho, seguindo os passos a seguir e por a “mão na massa”! Tá bem! E como faço para fixar o Excel na Barra de Tarefas e deixar o ícone Excel na Área de trabalho? • PASSO 1: Aperte e segure o botão CRTL+ESC; • PASSO 2: Digite Excel. Logo, aparecerá o ícone do Excel; • PASSO 3: Clique com o botão direito em cima do ícone e selecione a opção Fixar na Barra de Tarefas. Fique Atento Caso esteja utilizando o Windows 8 ou 10, utilize diretamente a caixa de pesquisa e siga a partir do passo 2. Excel - Nível Intermediário Criar Atalho na Área de Trabalho Excel Nível Intermediário - Tema 01 8 Usaremos o Excel 2010 para nossos exemplos. Mas não se preocupe se estiver utilizando as versões2007, 2013 ou 2016. A base deles é praticamente a mesma e os arquivos possuem compatibilidade. Vamos aprofundar nossos estudos na utilização de fórmulas do Excel? Então, abra o Excel e mãos à obra! • PASSO 1: Aperte e segure o botão CRTL+ESC; • PASSO 2: Digite Excel. Logo, aparecerá o ícone do Excel; • PASSO 3: Segure o botão esquerdo do mouse em cima do ícone do Excel e arraste para a área de trabalho. O software possui centenas de funções. Para criarmos familiaridade com as funções, devemos conhecê-las e aplicá-las em nosso dia a dia. Assim como no aprendizado de uma língua estrangeira, um instrumento musical ou um novo curso na Escola do Trabalhador, requer prática e disposição. O Excel possui uma biblioteca de funções que está dividida, desde o Excel 2007, em 12 grupos diferentes: Usadas Recentemente Financeira Lógica Texto Data e Hora Pesquisa e Referência Matemática e Trigonometria Estatística Cubo Informações Compatibilidade Engenharia Usadas Recentemente Financeira Lógica Texto Data e Hora Pesquisa e Referência Matemática e Trigonometria Estatística Cubo Informações Compatibilidade Engenharia Biblioteca de funções do Excel em grupos. É preciso decorar tudo isso? Excel - Nível Intermediário Diferenças Entre Fórmula e Função Excel Nível Intermediário - Tema 01 9 Não se preocupe tanto em gravar todas. Trabalharemos ao longo do curso com as funções mais importantes. Antes de tudo, vamos entender uma diferença importante entre Fórmula e Função? Basicamente, toda fórmula deve começar com o sinal de igualdade (=). Em regra, uma fórmula pode conter números (1, 2, 3 ...), operadores (+, -, *, / ....) e endereços de células. Exemplos de Fórmulas: =A1*3 =A1+A2+A3*12 As funções possuem uma estrutura predefinida pelo Excel. Existe a possibilidade do usuário também criar as funções, mas isso é assunto para o módulo avançado. Exemplos de Funções: =SOMA(A1:C22) =SE(A2>B3;”Certo”;”Errado”) Podemos, assim, dizer que toda Função é uma Fórmula, mas nem toda Fórmula é uma Função. Fórmula Função Fórmula X Função Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 10 Guia Fórmulas – Inserir Função Para ter acesso ao vídeo Apresentando Funções, navegue nas telas interativas na biblioteca do curso. Na Guia Fórmulas é possível acessar as diversas Funções que o Excel possui. Na mesma Guia Fórmulas é possível acessar os grupos das fórmulas disponíveis no Excel. A biblioteca possibilita o uso das funções de forma muito intuitiva. Ao clicar em um grupo, apa- recerão todas as funções. Para auxiliá-lo no estudo, assista ao vídeo 1, que apresenta as diversas formas de utilizar as funções. Dica Atalho: Ao pressionar Shift+F3, também é possível inserir função. Vídeo 1: Apresentação funções Exato! Para entender melhor, siga com seu estudo! Vamos, agora, estudar os sinais de comparação lógica? Toda função no Excel possui uma estrutura, uma forma. Essa forma nós chamaremos de sintaxe da função. E cada parte da função nós chamaremos de argumento da função. Veja o exemplo: Então, toda função tem uma estrutura? Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 11 Sintaxe de uma Função Anotações Excel - Nível Intermediário Sinais de Comparação Lógica Excel Nível Intermediário - Tema 01 12 Em programação, usamos muito o conceito da lógica, que é uma técnica para atingir determinados objetivos dentro de regras predefinidas. No Excel, para criarmos regras para atingir algum resultado, nós podemos utilizar os Sinais de Comparação Lógica. São eles: Vamos fazer alguns testes lógicos? Veja o vídeo 2 sobre Sinais Lógicos. Comparação Lógica Sinal Significado = Igual <> Diferente > Maior < Menor >= Maior ou Igual <= Menor ou Igual Quadro de comparação lógica Vídeo 2: Sinais lógicos Para ter acesso ao vídeo Sinais Lógicos, veja as telas interativas na biblioteca do curso. Excel - Nível Intermediário Atividade de Estudo Excel Nível Intermediário - Tema 01 13 Na verdade, não só números, mas para comparar células, números ou textos. Pois bem, começaremos a trabalhar com duas funções lógicas que futuramente serão essenciais para a combinação com as funções condicionais: SE, CONT.SE, SOMASES e outras. São as funções de texto E e OU. Ao longo do curso temos arquivos de atividades, disponíveis nos materiais do curso para que possamos praticar alguns conceitos e treinar nossas habilidades. Cada atividade possui diversas planilhas com o respectivo gabarito. Se entendi bem, esses sinais servem para comparar números? E como mais podemos utilizar esses sinais? Abra o arquivo Exercício 1. Fique atento, pois neste arquivo há 3 planilhas diferentes. Praticar Exercício 1: 2017 Brasileirão, =OU e =E Para acessar o arquivo referente ao Exercício 1, navegue nas telas interativas ou baixe o arquivo na biblioteca do curso. Tempo de resolução de 3 a 6 minutos. Excel - Nível Intermediário Função OU Excel Nível Intermediário - Tema 01 14 No vídeo 3 vamos conhecer a função OU. Esta função veri- fica se algum argumento é VERDADEIRO e retorna o resultado VERDADEIRO ou FALSO. Retorna FALSO somente se todos os argumentos forem FALSOS. Veja o vídeo 3 sobre função OU. =OU(lógico1;[lógico2];...) Nos detalhes da Função OU OU -> Nome da função. Lógico1 -> Argumento obrigatório da função. Neste item, será onde você irá adicionar a sua comparação lógica utilizando os sinais lógicos. Você poderá comparar células, números ou valores. Vídeo 3: Função OU Fique Atento Ao digitar a função OU em uma célula no Excel, você verá a seguinte sintaxe: =OU(lógico1;[lógico2];...) Vídeos 2, 3 e 4 Acompanhe os vídeos com explicações básicas das funções E e OU. Para ter acesso aos vídeos com com explicações básicas das funções E e OU, acesse as telas interativas na biblioteca do curso. Para ter acesso ao vídeo Função OU, veja as telas interativas na biblioteca do curso. Excel - Nível Intermediário Função E Excel Nível Intermediário - Tema 01 15 [Lógico 2] -> Argumento opcional por estar entre colchetes. Item de comparação lógica. ; -> Separador de argumentos. Agora vamos assistir ao vídeo 4, sobre a função E. Com essa função é possível verificar se um ou mais testes lógicos têm resultado VERDADEIRO OU FALSO. Esta função retorna VERDADEIRO se todos os argumentos forem VERDADEIROS. Veja o vídeo 4 sobre função E. Vídeo 4: Função E Fique Atento Ao digitar a função E em uma célula no Excel, você verá a seguinte sintaxe: =E(lógico1;[lógico2];...) =E(lógico1;[lógico2];...) Nos detalhes da Função OU: E -> Nome da função. Lógico1 -> Argumento obrigatório da função. Neste item, será onde você irá adicionar a sua comparação lógica utilizando os sinais lógicos. Você poderá comparar células, números ou valores. Para ter acesso ao vídeo Função E, veja as telas interativas na biblioteca do curso. Excel - Nível Intermediário Função SE Excel Nível Intermediário - Tema 01 16 Agora, conheceremos uma das funções mais importantes do Excel quando estamos comparando valores. Vamos entender um pouco mais? • Quando se deseja verificar se o teste lógico foi satisfeito usamos a Função SE, podendo assim retornar um valor se o teste for VERDADEIRO e retorna outro valor se teste for FALSO. • Em detalhes, a função SE é muito utilizada para comparar valores e retornar resultados a partir de um teste lógico pré- definido. SE uma condição é verdadeira, o resultado é X, ou se for falso o resultado será Y. [Lógico 2] -> Argumento opcional por estar entre colchetes. Item de comparação lógica. ; -> Separadorde argumentos. Ao digitar a função SE em uma célula no Excel, você verá a seguinte sintaxe: =SE(teste_lógico;[valor_se_verdadeiro]; [valor_se_falso]) Nos detalhes da Função SE: SE -> Nome da função. Teste_lógico -> Argumento obrigatório de comparação lógica. Item que usaremos o mesmo conceito do argumento lógico das funções E e OU. [valor_se_verdadeiro] -> Argumento opcional. Caso Teste_ lógico tenha resultado verdadeiro acontecerá à ação deste argu- mento. Caso não preencha nada nesse argumento o resultado na célula será VERDADEIRO. Anotações Fique Atento Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 17 Exercício 2 - Menções escolares [valor_se_falso] -> Argumento opcional. Caso Teste_lógico tenha resultado falso acontecerá à ação deste argumento. Caso não preencha nada nesse argumento o resultado na célula será VERDADEIRO. ; -> Separador de argumentos. Vamos para um exemplo prático para aplicação da Função SE. Uma professora possui as notas dos alunos e precisa calcular a média das notas e indicar quem foi aprovado e reprovado na matéria. O critério adotado pela escola é ter nota mínima de 5 (cinco) pontos para ser aprovado, caso contrário será reprovado. Dica Não digite espaço(s) entre os argumentos ou o nome da função. Dica Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 18 Abra o arquivo Exercício 2. São 3 planilhas diferentes e o gabarito para a correção no arquivo na planilha. Você pode inserir uma função de diversas maneiras. Vamos utilizar a planilha “Menções Escolares”. O primeiro passo que vamos realizar é o cálculo da média das notas de todos os bimestres. Realize os cálculos a partir da célula F4. Realize apenas a primeira função, clique duas vezes na alça de preenchimento que o Excel preencherá as funções para cada linha. Será que está correto? Compare as respostas com o gabarito da planilha. 1. Pelo atalho Shift+F3 numa célula. 2. No comando fx, localizado ao lado da caixa de nome. Praticar Exercício 2: Função SE – Planilhas Menções Escolares e gab., Empresa 1 e gab. e Empresa 2 gab. Tempo de resolução de 4 a 8 minutos. Relembrar Caso queira revisar ainda mais os conceitos da Alça de preenchimento, consulte o Curso Introdução ao Excel Tema 1. Dica Use a função =MÉDIA. A função ficará da seguinte forma =MÉDIA(B4:B7). Comando fx 3. No comando localizado na Guia Página Inicial, Grupo Edição. Para ter acesso ao arquivo referente ao Exercício 2, acesse as telas interativas ou baixe o arquivo na biblioteca do curso. Atividade de Estudo Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 19 4. Ou digitando em seu teclado. Lembre-se de iniciar a Fórmula com o sinal de = igualdade Guia Página Inicial, Grupo Edição Você pode inserir uma função de diversas maneiras. Na célula G4, teremos a seguinte função: =SE(F4<5;” Reprovado”;”Aprovado”) A ideia sempre é que o Excel facilite nossa vida. Então, pense sempre na construção de uma fórmula que funcionará para demais linhas e/ou colunas. Para isso, clique duas vezes na alça de preenchimento e verá o conceito de cada aluno. Lembre-se que, nos arquivos dos “Exercícios” estão disponi- bilizados a planilha e o gabarito da questão. Veja a solução no vídeo 5 sobre a função SE. Agora, para que o Excel nos retorne o conceito, de acordo com o critério da escola, aplicaremos a função =SE na célula G4. O teste lógico da função SE pode ser construído em Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 20 combinação com os sinais de comparação, ou seja, para célula menor que 5 o argumento será escrito da seguinte maneira: Teste_lógico da Função SE: =SE(F4<5;...) -> Endereço da célula Se esse teste lógico for verdadeiro, o primeiro argumento será “Reprovado”, caso contrário será “Aprovado”. Para concluir o primeiro tema deste curso, vamos fazer a Formatação Condicional. Para isto, assista a revisão no vídeo 6. Dica Toda vez que o retorno da Função for texto, devemos colocar o argumento entre “aspas”. Revisão Formatação Condicional Vídeo 6: Formatação Condicional Para ter acesso ao vídeo Formatação condicional, veja as telas interativas na biblioteca do curso. Vídeo 5: Função SE Para ter acesso ao vídeo Função SE, veja as telas interativas na biblioteca do curso. Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 21 Exercício 2 – Empresa1 Praticar Exercício 2: Empresa 1 Tempo de resolução de 4 a 8 minutos. Nesta atividade, a “Empresa 1” promoverá um aumento para todos os funcionários devido aos bons rendimentos. O critério estabelecido foi: Se o funcionário tiver salário menor ou igual a R$ 1.000,00 este receberá aumento de 40%, caso ele receba mais que R$ 1.000.00 receberá aumento de 30%. Primeiro vamos calcular quanto foi o aumento na coluna C e depois na coluna D faremos a soma. Para começar uma fórmula, usamos o sinal de igualdade. Quando você começar a digitar o SE, o Excel vai te sugerir fun- ções que contenham “SE”. Ao apertar TAB, a função selecionada será a que estiver marcada em azul. Vamos continuar no arquivo Exercício 2. Agora, vamos utilizar a planilha “Empresa 1”, que é uma empresa que sofrerá alguns reajustes salariais em um determi- nado período. Atividade de estudo Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 22 Exercício 2 – Empresa1 Teremos a função =SE(teste_lógico;[valor_se_verdadeiro];[valor_se_falso]) Em negrito, o Excel nos mostra em qual argumento estamos criando. Neste caso, o teste lógico. O teste lógico será em comparação com a célula do salário. Façamos a primeira e depois deixa o Excel trabalhar por nós. Passo a passo Função SE • Teste_lógico: B3<=1000 • Para passar para o próximo argumento utilize ; • Se o teste lógico que criamos for verdadeiro a ação a ser realizada pelo Excel será: • Valor_se_verdadeiro: B3*40% • Digite o separador de argumentos ; • Se o teste lógico que criamos for falso a ação a ser reali- zada pelo Excel será: • Valor_se_falso: B3*30% Do que se trata? • Feche o parêntese. Fique Atento Não é necessário incluir R$ ou (.) no número do teste lógico. Dica Pode ser usado 40% ou 0,40 ou 40/100. Lembre-se que estudamos sobre este assunto no curso Introdução ao Excel Tema 1. Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 23 Pronto! Teremos a função da seguinte maneira: Clique duas vezes na alça de preenchimento que a função se repetirá alterando o número da célula para cada alteração de linha. Na célula D3, abaixo do título “Novo Salário”, utilize o comando AutoSoma, para que seja somado os valores das duas células ao lado. Endereço de célula D3: =SOMA(B3:C3) Clique duas vezes na alça de preenchimento. Alça de preenchimento Fique Atento Não há espaço entre os argumentos. Dica Caso queira revisar ainda mais os conceitos da Alça de preenchimento, consulte o Curso Introdução ao Excel Tema 1. . Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 24 Atividade de estudo Vídeo 7: Resolução Empresa 1 Vídeo Acompanhe a resolução do reajuste dos salários dos funcionários da “Empresa 1” no Vídeo 7. Vamos continuar no arquivo Exercício 2. Agora, vamos utilizar a planilha “Empresa 2”. Nesta empresa, os diretores se reuniram e observaram que alguns materiais antigos precisam ser renovados. Para isso, eles decidiram alguns critérios para doar certos materiais para instituições filantrópicas. Exercício 2 – Empresa2 Praticar Exercício 2: Empresa 2 Tempo de resolução de 4 a 8 minutos. DicaSubtraia o ano atual (2018), pelo ano de fabricação. Ao elaborar a função SE no valor se verdadeiro não se esqueça das “aspas” na palavra Doação. E o valor se falso para que não apareça nada no resultado, abra e feche as aspas “”. Para ter acesso ao vídeo Resolução empresa 1, veja as telas interativas na biblioteca do curso. Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 25 Aninhamento de Funções Vamos agora aprender a incluir funções dentro de funções e dar um passo importante nas nossas habilidades no Excel. Isso se chama aninhar funções. Para criar diferentes testes lógicos e/ou obter variadas respostas. Por exemplo: Caso nas Menções Escolares tivéssemos, além dos conceitos “Aprovado” e “Reprovado”, o conceito “Recuperação”, para quem tirou nota entre 4 e 6 pontos. Uma só função SE não seria capaz de ter três respostas. E para que, de fato, serve isso? O aninhamento das funções permite que várias funções sejam agrupadas dando mais possibilidades de respostas. Isso pode resolver o problema de você ter, por exemplo, diferentes testes lógicos para uma resposta na função, ou mais de duas respostas para o mesmo teste lógico. Na primeira função SE que realizamos tínhamos duas res- postas: “Aprovado” ou “Reprovado”. E se caso tivéssemos além das duas respostas mais uma condição de nota para alunos em “Recuperação”? Para isso, teremos que juntar funções SE, ou seja, aninhar. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 26 Então a regra para você utilizar com precisão a Função SE será: Para a construção de um SE aninhado em outro(s) a regra é sempre criar uma nova função SE no argumento valor_se_falso. Sendo assim, a nossa estrutura básica será: • Caso tenha 2 respostas, por exemplo, “Aprovado” ou “Reprovado”: 1 Função SE • Caso tenha 3 respostas, por exemplo, “Aprovado”, “Recuperação” ou “Reprovado”: 2 Funções SE • Caso tenha 5 repostas: 4 Funções SE • Caso tenha 65 respostas: 64 Funções SE Dica O máximo de aninhamentos possíveis em umas funções é de até 64 níveis, ou seja, você pode usar até 64 funções SE dentro dela mesmo. SE aninhado Fique Atento Não é necessário digitar um novo sinal de igualdade. Lembre-se que o sinal de igualdade serve para começar uma fórmula e não uma nova função. Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 27 Atividade de estudo Agora vamos iniciar as atividades no arquivo Exercício 3. Trata-se de 3 planilhas diferentes, que estão com seus gaba- ritos no mesmo arquivo. Iniciaremos com a planilha “Função SE”. Para iniciar, utilizaremos as regras apresentadas e criaremos duas funções SE aninhadas. Lembre-se que é necessário reali- zar apenas 2 testes lógicos. Caso os dois testes forem falsos, a resposta já será o valor_se_falso do 2º SE. Vídeo 8: Resolução Função SE2 Vídeo Para verificar a resolução, assista o Vídeo 8.Praticar Exercício 3: Função SE Tempo de resolução de 6 a 12 minutos. Para ter acesso ao vídeo Resolução Função SE2, veja as telas interativas na biblioteca do curso. Vamos continuar no arquivo Exercício 3, com a planilha “Cia de Teatro”, que é uma empresa de teatro que possui alguns atores da região que foram premiados após as apresentações de várias peças teatrais. Como tiveram sucesso e um bom valor em caixa, o grupo estabeleceu alguns critérios para remuneração. Praticar Exercício 3: Função SE Tempo de resolução de 10 a 15 minutos. Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 28 Na planilha “CIA de Teatro”, iremos realizar os cálculos para descobrir o valor da premiação de cada ator em duas categorias. A premiação ouro leva em consideração o tempo de empresa que o ator possui. Na premiação prata, a lógica será elaborada na idade do ator. As condições são para a premiação são: Prêmio Ouro: Se o ator tem até 10 anos de empresa receberá premiação de R$ 1.500,00; Se o ator tem até 15 anos de empresa, R$ 2.000,00; E caso tenha acima de 15 anos de empresa, R$ 3.000,00; Cia de Teatro Anotações Prêmio Prata: Até 30 anos de idade: R$ 500,00 Até 35 anos de empresa: R$ 300,00 Acima de 35 anos: R$ 100,00 Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 29 Vídeo 9: Resolução Cia do Teatro Vídeo Para auxiliá-lo nos estudos, veja no vídeo 9 como deve ser feita a premiação na CIA de Teatro. Continuando no arquivo Exercício 3. Verifique a planilha “Escola de Sinais”. Praticar Exercício 3: Função SE Tempo de resolução de 10 a 15 minutos. Escola Sinais Para ter acesso ao vídeo Resolução Cia do teatro, veja as telas interativas na biblioteca do curso. Para acessar o arquivo referente ao Exercício 3, navegue nas telas interativas ou baixe o arquivo na biblioteca do curso. Na planilha “Escola Sinais”, realizaremos os cálculos confor- me as condições abaixo: Para o Resultado: SE Média da nota < 4 “II”. Se Média >=7 “AP”, do contrário “RC”; Nas Olimpíadas Matemática: SE Nota do Trabalho > 5 OU Média > 5 OU Resultado for igual a AP, então “Aluno Participa”, do contrário “Não Participa”; Mensalidade: SE Nota da Prova >= 7, então o valor da mensalidade será de R$ 200, caso contrário R$ 400. Para aninhar a função OU ou a Função E utilizaremos a Excel - Nível Intermediário Excel Nível Intermediário - Tema 01 30 Teste_Lógico SE Teste_Lógico SE =SE(E(lógico1;[lógico2];...);[valor_se_verdadeiro];[valor_se_ falso]) Vídeo 10: Resolução Sinais Vídeo Para verificar a resolução da planilha “Escola Sinais”, assista o vídeo 10. Para ter acesso ao vídeo Resolução sinais, veja as telas interativas na biblioteca do curso. seguinte regra: - Como são funções lógicas elas serão incluídas no teste lógico da função SE. Por exemplo: =SE(OU(lógico1;[lógico2];...);[valor_se_verdadeiro];[va- lor_se_falso]) Excel Nível Intermediário - Tema 01 31 Essa é a intenção. Continue praticando para melhorar sua habilidade e velocidade na criação de funções. Pausa para o café! Não demore tanto, mantenha o foco e tenha certeza que estes conhecimentos podem mudar sua vida! Acredito que estou compreendendo um pouco melhor. Tema 02 Trabalhar com vínculos e referências fixas e novas funções Neste tema, além de aprender novas fórmulas, teremos a oportunidade de utilizar novos comandos no Excel. O objetivo maior é que você ganhe mais independência na criação e formatação das suas planilhas. Excel - Nível Intermediário Fixação de Linha e Coluna Anotações Excel Nível Intermediário - Tema 02 33 Contas de Casa - Momento 1 Contas de Casa - Momento 2 O padrão do Excel é que quando você clicar e arrastar uma fórmula, utilizando a alça de preenchimento, o endereço da célula mudará. Veja o exemplo: Utilizando a alça de preenchimento para mudança em linha de uma fórmula, o Excel acrescentará um número a mais na referência de linha. Ao clicar e arrastar descendo o cursor do mouse, na alça de preenchimento, o endereço das células foi acrescido de uma unidade. Caso feita situação oposta, clicar e arrastar subindo o cursor do mouse, o endereço das células seria reduzido de uma unidade. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 34 Contas de Casa - Momento 3 A outra possibilidade é clicar e arrastar, na alça de preenchi- mento, em colunas: A partir da primeira referência da fórmula, será alterado para a próxima letra do alfabeto, e consequentemente para o ende- reço de célula seguinte. Com a tecla F4 do teclado é possível fixarmos a linha e/ou a coluna. Em um endereço de célula ou num grupo de célula, ao apertar uma vez a tecla F4, fixaremos linhae coluna: =A1 (após apertar uma vez o F4) =$A$1. Linha e coluna estarão com refe- rência fixa, ou seja, se utilizarmos o recurso do preenchimento em série com a alça de preenchimento em linha ou coluna, não será alterado o endereço da célula. Vídeo 11: Referência Fixa Para ter acesso ao vídeo Referência Fixa, veja as telas interativas do tema 2. Dica - Apertar a tecla F4 uma vez: fixa linha e coluna da referência - Apertar a tecla F4 duas vezes: fixa linha da referência - Apertar a tecla F4 três vezes: fixa coluna da referência Vejamos uma demonstração no vídeo 11. Nele, iremos entender um pouco melhor o porquê de utilizar referências fixas ou relativas. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 35 Posso digitar o $ antes ou depois do endereço de uma célula, em vez de apertar F4? Pode também, Carlos. Lembre-se apenas do que irá fixar, se as linhas ou as colunas. Este recurso é muito útil ao realizar um cálculo com uma célula fixa para diversas linhas. Veja o exemplo da figura abaixo. Ao clicar e arrastar a alça de preenchimento da primeira fórmula, as células da coluna F serão modificadas a cada linha. Já o da Coluna I ficará fixo. Célula Absoluta Dica Após selecionar um grupo de células para realizar uma fórmula, exemplo: =A1:C3 e imediatamente apertar a tecla F4 ficará fixa o primeiro endereço de célula e o último =$A$1:$C$3. O Excel dispõe de um recurso que pode facilitar a identifica- ção de valores únicos e a eliminação de células com o mesmo valor. Caso você possua uma planilha com várias informações repe- tidas e deseje excluir tudo que se repete, você pode utilizar o comando Remover Duplicatas, disponível na Guia Dados, grupo Ferramentas de Dados. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 36 Remover Duplicatas Planilha de produtos Janela de remoção de duplicatas Em uma planilha, temos alguns dados repetidos. Para retirar os dados repetidos você deve seguir os passos: • PASSO 1: Selecione toda a planilha (Ctrl+*) • PASSO 2: Clique em remover duplicatas – Guia Dados • PASSO 3: Selecione quais colunas serão consideradas para a exclusão dos dados Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 37 • PASSO 4: Aperte Ok O Excel mostra uma janela de alerta com a quantidade de valores duplicados que foram encontrados na seleção e a quanti- dade de valores únicos (exclusivos). O Microsoft Excel possibilita que uma célula contenha somente valores dispostos em uma lista, em formato dropdown. Veja o exemplo: Resultado final: Janela de aviso remoção de duplicatas Planilha após remover duplicatas Validação de dados Lista Validação de Dados Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 38 Para atribuir uma lista a qualquer célula, você deverá seguir os seguintes passos, também utilizando a Guia Dados: Neste momento você poderá selecionar qual será o conteúdo da sua lista. No caso, selecionaremos o nome de vendedores. • PASSO 1: Selecione a célula que deseja criar a lista • PASSO 2: Na Guia Dados, selecione a opção validação de dados • PASSO 3: Em Permitir, procure a opção “Lista”. • PASSO 4: Selecione o conteúdo da lista Validação de dados Validação de dados seleção Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 39 Dica Você também pode ter uma lista com informações que não estão necessariamente nos valores das células da sua planilha. Caso queira, por exemplo, formar uma lista com as informações SIM e NÃO, na fonte de dados da lista escreva SIM;NÃO Dados lista Validação SIM;NÃO • PASSO 5: Aperte Ok Com isso o conteúdo da célula à qual você atribuiu uma lista de valores só poderá receber um dado VÁLIDO, ou seja, que esteja na lista definida. Excel - Nível Intermediário Função Procv – Procura Vertical Excel Nível Intermediário - Tema 02 40 É uma função que, a partir de um valor procurado, realiza uma busca por ele na primeira coluna da primeira linha de um intervalo de dados. Se encontrar o valor procurado, mostra o conteúdo que se encontra em uma das colunas do intervalo. Se não encontrar o valor procurado, buscará a correspondência na segunda linha, e assim por diante, até chegar à última linha do intervalo. Note que a procura é feita seguindo as linhas do inter- valo e, por isso, a função chama PROCV de PROCura Vertical. No detalhe da Função: 1. Valor_procurado: Célula ou valor de pesquisa que será procurado em sua matriz tabela. 2. Matriz_tabela: Matriz onde constarão as informações que serão procuradas. Pequeno banco de dados. 3. Núm_índice_coluna: número fixo que contém os valores que você obterá como resultado da pesquisa, representando a primeira coluna com o número 1 (da matriz selecionada), coluna 2 (da matriz selecionada) e assim quantas mais você selecionar. 4. [Procurar_intervalo]: Argumento Opcional. Possíveis duas opções nesse argumento. Correspondência aproximada ou correspondência exata. 4.1 Correspondência aproximada: Representado pelo número 1 ou VERDADEIRO. Procura o valor mais próximo na A sintaxe da função é a seguinte: Apesar de algumas limitações que essa função possui, trata-se de uma função que possui muita utilidade pela simplicidade de utilizá-la. =PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;[procurar_intervalo]) 1 2 3 4 Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 41 matriz tabela do argumento valor procurado. 4.2 Correspondência exata: Representado pelo número 0 ou FALSO. Procura exatamente o argumento valor procurado na sua matriz tabela selecionada. Basicamente, em 90% dos casos você utilizará essa correspondência. Iniciaremos agora as atividades do arquivo Exercício 4. Utilizaremos a planilha Exemplo PROCV e a Função será criada na célula B6. Valor_procurado: Este argumento é o valor ou a célula da pesquisa que será realizada na sua matriz (grupo de células), pode ser texto ou número. Neste caso, na célula A6 digitaremos o código da fruta. Observe o valor_procurado indicado pela seta Atividade de Estudo Praticar Exercício 4: PROCV-PROCurar na Vertical Para acessar o arquivo referente ao Exercício 4, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 2. Tempo de resolução de 4 a 8 minutos. PROCV valor procurado Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 42 Matriz_tabela: Indica a base de dados na qual você irá fazer a pesquisa. Nesse argumento, é importante que você selecione apenas os valores da planilha. Desconsidere o título para ter uma pesquisa precisa. No nosso caso digite $D$7:$G$11. Núm_índice_coluna: O argumento é representado por um número. Ao selecionarmos a matriz no argumento anterior, o Excel estabelece um índice para cada coluna que selecionamos. Sendo assim, a primeira coluna selecionada a partir da nossa matriz terá o índice 1, a segunda coluna o índice 2 e, assim, sucessivamente.. Neste exemplo, colocamos os números acima dos títulos apenas para facilitar o entendimento. A matriz encontra-se nas células $D$7:$G$11 (lembre-se, os títulos devem ser excluídos). Se você definir o argumento núm_índice_coluna como 1, obterá como resultado o código da fruta; se definir como 2, o resultado será o nome da fruta e assim por diante. Para continuarmos, digite o número 2 na função. PROCV matriz tabela PROCV Número Índice da Coluna Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 43 [procurar_intervalo]: Este argumento é muito importante, pois define se a pesquisa deve buscar uma correspondência aproximadacom o valor_procurado (indicar VERDADEIRO) ou se deve buscar uma correspondência aproximada (indicar FALSO). Ele é opcional, pois o padrão do Excel é a correspondência aproximada. Utilizaremos para esse exemplo a correspondência exata, pois o código é o único vinculado a uma fruta. Em breve, realizaremos uma atividade para diferenciar melhor a diferença entre as correspondências. Digite FALSO ou 0. PROCV Procurar intervalo Dica Correspondência aproximada: VERDADEIRO ou 1 Correspondência exata: FALSO ou O Pronto, nossa função está completa. Esse erro #N/D significa que o valor de pesquisa não foi encontrado na matriz da sua tabela. Mas quando você digitar algum código na célula A6, que conste na matriz tabela, a função retornará a respectiva fruta. Agora temos bagagem suficiente para realizar várias outras atividades, vamos lá? Mas como assim? Tem um erro na minha função? Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 44 Continuando no arquivo Exercício 4. Verifique a planilha PROCV1. Lembre-se que o gabarito de correção da atividade está no mesmo arquivo da planilha. Inicie a atividade criando validação de dados em lista na célula B3 com todos os códigos da tabela. Depois nas células D3, D4 e D5 criar função =PROCV com pesquisa da célula B3. Atividade de Estudo Praticar Exercício 4: PROCV-PROCurar na Vertical Para acessar o arquivo referente ao Exercício 4, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 2. Tempo de resolução de 5 a 10 minutos. Busca PROCV Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 45 Questão Para Praticar Comando Nos estudos sobre a função PROCV (Procurar na Vertical) aprendemos a sintaxe da Função. Na criação desta função existe um erro. Qual erro é esse? Função: QUESTÕES Feedback Alternativas A O número a ser usado deveria ter sido o 3. Retorne ao assunto FUNÇÃO PROCV e reveja o item 3 (núm_índice_coluna) B A matriz tabela está errada. O correto é de A7:D14 Retorne ao assunto FUNÇÃO PROCV e reveja o item 2 (matriz_tabela) C Não deveria ter sido usado FALSO, e sim o 0 ou VERDADEIRO Retorne ao assunto FUNÇÃO PROCV e reveja o item 4 (procurar_intervalo) D Faltou o espaço entre o nome da função e o parêntese Não há espaço entre nenhuma função e o parêntese. Volte ao Tema 1 e veja a resolução do Exercício 1. E Faltou o argumento valor_procurado Parabéns! Muito bem observado. A função correta é =PROCV(B3;A8:D14;2;FALSO) Caso encontre dificuldade para validar a lista, retorne em “validação de dados”. Lembrando que a fonte é =$A$8:$A$14. Perceba que poderíamos também resolver essa resposta com a função SE, pois temos 7 códigos e precisaríamos de 6 funções SE aninhadas. É muito trabalhoso. Imagina se tivéssemos 60 códigos? Impraticável. Por isso, a Microsoft realiza atualizações no Excel, com o objetivo de atender diversas demandas dos usuários criando diferentes funções e comandos. PROCV correspondência aproximada Abra a planilha “PROCV Verdadeiro”. Agora, entenderemos a conceituação da correspondência aproximada. Na atividade anterior, exploramos como uma correspondência exata retorna o respectivo valor. Caso fosse digitado um código que não existisse na tabela, o retorno da função PROCV, seria #N/D. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 46 Para esta atividade abra a planilha “PROVC Verdadeiro” no arquivo Exercício 4. Nesta atividade, na coluna H faremos uma fórmula básica de multiplicação. Os conceitos serão preenchidos de acordo com o total calcu- lado. Para isso, criamos uma tabela das linhas 22 a 28. • Na célula H6 digitaremos =F6*G6 • Dois cliques na alça de preenchimento Atividade de Estudo Praticar Exercício 4: PROCV-PROCurar na Vertical Para acessar o arquivo referente ao Exercício 4, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 2. Tempo de resolução de 10 a 15 minutos. PROCV Aproximado Tabela PROCV aproximado Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 47 A tabela com a definição dos Conceitos será a referência para que a função PROCV com a correspondência aproximada fun- cione. Quando ele for realizar a pesquisa, funcionará da seguinte maneira: A forma como essa tabela funciona, permite que você possa criar da sua maneira e conforme a necessidade. Sendo assim, nossa função será escrita, na célula I6, da seguinte maneira: =PROCV(H6;$A$23:$B$28;2;VERDADEIRO) Veja algumas dicas no vídeo 12, sobre a função PROCV, utilizando a correspondência aproximada. De 0 até 99,999... Conceito será Péssimo De 100 até 199,999... Conceito será Ruim De 200 até 399,999... Conceito será Razoável De 400 até 699,999... Conceito será Muito bom De 900 até o infinito. Conceito será Excelente Fique Atento Regra Importante! Sua tabela de referência deverá estar classificada do menor para o maior número. Vídeo 12: PROCV correspondência aproximada Para ter acesso ao vídeo PROCV correspondência aproximada, veja as telas interativas do tema 2. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 48 A caixa de nome, se bem utilizada, pode ajudar a organizar suas planilhas. Além disso, pode evitar que você utilize a fixação de células ou grupos de células. Para recordar... A localização da CAIXA DE NOME Caixa de Nome Dica Para definir o nome de uma célula, basta que na seleção de uma célula você altere na caixa de nome a referência da célula E16, por exemplo, para o nome que desejar. Esse nome não pode conter espaços. Caixa de nome Para entender um pouco melhor a Caixa de Nome assista o vídeo 13. Vídeo 13: Caixa de nome Para ter acesso ao vídeo Caixa de nome, veja as telas interativas do tema 2. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 49 Para excluir ou alterar qualquer nome, você deverá utili- zar o gerenciador de nomes, na Guia Fórmulas, grupo Nomes Definidos. No gerenciador de nomes podemos editar, excluir ou criar um novo nome. A referência de nome pode ser utilizada entre várias planilhas desde que dentro da mesma pasta de trabalho e sempre que puder, utilize a Caixa de Nome. Gerenciador de Nomes Gerenciador de Nomes Janela - Gerenciador de Nomes Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 50 A função CONT.SE conta a quantidade de células em um determinado intervalo de acordo com um critério. Vamos agora abrir o arquivo Exercício 5. Ele tem somente 1 planilha e seu gabarito. 1. Intervalo de seleção para contagem 2. Critério de contagem Função Cont.se Atividade de Estudo Sintaxe: =CONT.SE(intervalo;critérios) 1 2 Vamos utilizar de forma prática e ver como de fato funcionará. Praticar Exercício 5: (planilha Conte.SE e SomaSE – gabarito) Para acessar o arquivo referente ao Exercício 5, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 2. Tempo de resolução de 10 a 15 minutos. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 51 Nesta atividade (exercício 5), temos uma planilha com muitos dados, com alguns produtos de uma empresa que vende equipamentos de tecnologia. Vamos entender uma das formas de utilizar a função CONT.SE. A atividade é fazer a contagem de quantas categorias nós temos e qual o total de cada uma delas. • Passo 1: Copie toda a coluna B para a coluna J. Produtos TI Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 52 • Passo 2: Remova as duplicatas na coluna J. • Passo 3: Utilize a função CONT.SE, a partirda célula K2 • Passo 4: =CONT.SE(B:B;J2) Seleção da Coluna B inteira Dica Caso queria representar uma coluna inteira em uma fórmula, selecione uma coluna com o cursor ou digite a letra da coluna duas vezes com os dois pontos entre elas. Exemplo coluna A. Digite A:A. Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 53 A função SOMASE realiza a soma para cada critério informa- do. A diferença entre o CONT.SE será o intervalo onde terá os valores para soma. 1. Intervalo de seleção para soma 2. Critério de soma 3. Intervalo onde estarão os números para soma =SOMASE(B:B;M2;E:E) Função Somase Sintaxe: =SOMASE(intervalo;critérios;[intervalo_soma]) 1 2 Realize as mesmas instruções que fizemos com o CONT.SE • PASSO 1: Copie toda a coluna B para a coluna M • PASSO 2: Remova as duplicatas na coluna M • PASSO 3: O intervalo_soma é a Coluna E. Valor à vista para cada categoria de produto • PASSO 4: Sendo assim, teremos a função na célula N2, Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 02 54 Questão Para Praticar Coman do Considerando a planilha sobre os produtos da empresa de tecnologia nos fizemos algumas cont agens. Você poderia indiciar qual o valor total da categoria Caixa de som? Alterna tivas A R$ 7.545,30 (Resposta errada) B R$ 5.754,30 (Resposta correta) C R$ 2.790,20 (Resposta errada) D R$ 157,20 (Resposta errada) E R$ 2.970,20 (Resposta errada) Feedba ck Resposta Errada: Vamos tentar novamente? Retorne na explicação SOMASE. Resposta Certa: Parabéns! Vamos para o Tema 3. Anotações Excel Nível Intermediário - Tema 02 55 Que bom, Carlos! Mas não vamos parar por aqui. Agora, vamos iniciar o tema 3. Muito bom, Rita! Aprendi muito com esse tema! Tema 03 Melhorar a apresentação e a proteção dos dados. Estamos iniciando o tema 3, o último do nosso curso. Para finalizar este curso vamos aprender a agrupar dados e criar níveis de proteção em uma planilha. Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 57 Filtro Filtro – Guia Página Inicial Filtro – Guia É possível acessar o Filtro ou na Guia Página Inicial, grupo Edição. Ou na Guia Dados, grupo Classificar e Filtrar. Vamos utilizar o Filtro do Excel. Comando muito útil para analisar dados de uma planilha. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 58 Agora iniciaremos os estudos da planilha do arquivo Exercício 6. É simples utilizar o comando Filtro, vamos exercitar? Acesse a planilha “Conceito_IES” (Exercício 6) e selecione uma célula e logo em seguida o comando Filtro. Observe que em cada título aparecerá setas para baixo. Critérios de Classificação da Planilha. Possibilidade de criar filtros específicos para ocultar linhas. Dados únicos da linha. Praticar Exercício 6: (planilha IEB, Questões, Correspondentes de Cambio BCB – sem gabarito). Para acessar o arquivo referente ao Exercício 5, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 2. Tempo de resolução de 10 a 15 minutos. Título com filtro Janela do Filtro Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 59 Veja o vídeo 14, que trata sobre os filtros em uma planilha. Na planilha “Questões” (Exercício 6), disponibilizamos algu- mas questões para que você treine o comando Filtro. Vídeo 14: Filtro Para ter acesso ao vídeo Filtro, veja as telas interativas do tema 3. Questão Para Praticar Comando Para extrair informações das planilhas podemos utilizar os filtros em diversas colunas. Usando o comando Filtro na planilha “Questões” (Exercício 6) verifique quantas Instituições de Ensino Superior atingiram o valor 7 (sete), em São Paulo e indique a opção correta. Alternativa s A 11 (Resposta errada) B 27 (Resposta correta) C 18 (Resposta errada) D 32 (Resposta errada) E N.d.a . (Resposta errada) Feedback Resposta Errada: Assista novamente o vídeo Fil tro e não se esqueça de realizar ao menos dois fi l tros Resposta Certa: Muito bom! Você é quase um usuário do Excel de nível intermediário. Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 60 Subtotal Com o comando Subtotal é possível totalizar em cada agrupamento de dados um subtotal além de um total geral das células selecionadas. Este comando está disponível na Guia Dados, grupo Estrutura de Tópicos. Realizado uma contagem de quantas IES temos por concei- to, teremos o seguinte resultado na planilha Conceito_IES. Vamos continuar utilizando o Exercício 6 para a criação de subtotais. Guia Dados Subtotal por Conceito Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 61 Primeira coisa que devemos ter em mente é qual informação vamos agrupar em nossa planilha. A cada alteração desse agru- pamento o Excel adicionará uma linha com o total agrupado. Esse total pode ser Soma, Contagem, Média e etc. Para realizar um Subtotal, conforme solução do Exercício 6, siga os passos a seguir, no item “3.3 Classificação”. Vamos continuar nossos estudos utilizando as planilhas do arquivo Exercício 6. • PASSO 1: Classifique a coluna que será realizada o Subtotal. Classificação Subtotal classificação Conceito Seleção Subtotal Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 62 • PASSO 3: Na janela, marque a opção que será reali- zada a agrupamento. Neste caso, Conceito e a função Contagem. 1. Agrupa todos os dados e mostra o total geral; 2. Mostra os subtotais; 3. Mostra a planilha inteira, com os subtotais e total geral. Janela Subtotal Níveis Subtotal Botões: Dica Para retirar o nome contagem, utilize o atalho Ctrl+U Na janela Localizar e Substituir, digite em Localizar: Contagem E não digite nada em Substituir Clique em Substituir tudo Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 63 Boa pergunta! Veja os passos a seguir. Eles vão ajudar bastante! Vamos praticar! E como faço para excluir um subtotal? • PASSO 1: Clique no botão subtotal. • PASSO 2: Clique em Remover todos. Ainda no arquivo Exercício 6, abra a planilha “Correspondentes de câmbio BCB” e conforme as instruções e conhecimentos adquiridos anteriormente faça um Subtotal como os indicados a seguir. Atividade de Estudo Praticar Exercício 6: (planilha IEB, Questões, Correspondentes de Câmbio BCB – sem gabarito) Para acessar o arquivo referente ao Exercício 6, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 3. Tempo de resolução de 10 a 15 minutos. Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 64 Veja, agora, as instruções para elaborar o Subtotal: • Criar subtotal das UF. • Subtotal de Correspondente por UF. • Retirar a palavra Contagem. Subtotais Correspondentes BCB Questão Para Praticar Comand o Após realizar o curso Criando um Negócio de Sucesso, da Escola do Trabalhador , você percebeu que existe a possibilidade de abrir uma Correspondente do Banco do Banco Central no estado do Paraná. Para isso, você precisa analisar qual o total de Correspondentes Geral e o no estado do Paraná respectivamente. Após esta verificação indique a opção correta. Alternati vas A 3300 e 200 (Resposta errada) B 3100 e 6 (Resposta correta) C 3154 e 316 (Resposta errada) D 3213 e 208 (Resposta errada) E 3154 e 209 (Resposta errada) Feedbac k Resposta Errada:Retorne na explicação do SUBTOTAL Resposta Certa: Muito Bom! Parabéns!!! Anotações Excel - Nível IntermediárioExcel Nível Intermediário - Tema 03 65 Proteção De Planilha A proteção de planilhas serve para evitar que alguém sem querer ou sem autorização modifique os dados ou funções da planilha. As alterações serão feitas apenas depois de desbloquear com senha. Agora, caso deseje bloquear uma planilha, siga as instruções: Fique Atento É possível desbloquear uma planilha que esteja com senha. Além disso, é possível criar uma macro para desbloquear qualquer planilha. Mas isso é assunto de outro curso (Excel Avançado). • PASSO 1: Na planilha que deseja bloquear, clique em proteger planilha. Proteger Planilha Dica É possível acessar proteger planilha tanto pela Guia Revisão, Proteger Planilha. Ou clicar com o botão direito na planilha e selecionar Proteger Planilha. Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 66 • PASSO 2: Selecione as permissões que o usuário terá após o bloqueio. • PASSO 3: Digite uma senha que lembrará. Não há a função de lembrete de senha e é possível bloquear a planilha sem digitar nenhuma senha; • PASSO 4: Digite novamente sua senha; • PASSO 5: Aperte Ok. Proteger Planilha O padrão do Excel é que todas as células estão marcadas como bloqueadas. Na verdade, apenas quando você proteger a planilha que as células ficarão bloqueadas. Calma, Rita! Como assim? Se todas as células estão bloqueadas, como eu consigo utilizar? Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 67 Caso queira deixar algumas células disponíveis para edição assista ao vídeo 15, que apresenta algumas dicas! Vídeo 15: Desbloqueio de células e Dicas Para ter acesso ao vídeo Desbloqueio de células e Dicas, veja as telas interativas do tema 3. Proteção de Pasta dte Trabalho É possível proteger a pasta de trabalho para que o usuário NÃO realize as seguintes ações: • Ocultar ou reexibir planilhas. • Criar ou excluir planilhas. • Minimizar ou maximizar planilha. • Organizar planilhas em diferentes formatos. Proteção Pasta de trabalho Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 68 Proteção Estrutura: Impossibilita o usuário de ocultar ou reexibir planilhas e criar ou excluir planilhas. Proteção Janela: Impossibilita o usuário de minimizar ou maximizar planilha e organizar planilhas em diferentes formatos Proteger Estrutura e Janelas Permissões Proteção do Arquivo Além disso, é possível proteger o arquivo no comando “Proteger pasta de trabalho”. Siga as instruções a seguir: Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 69 • PASSO 1: Selecione a opção Criptografar com Senha. • PASSO 2: Informe uma senha que seja possível de recor- dar futuramente. • PASSO 3: Digite a senha novamente. • PASSO 4: Aperte ok. 1. Argumento obrigatório da função. Você poderá incluir texto, número ou a célula. 2. Argumento opcional. Criptografar com senha Quando você abrir novamente essa pasta de trabalho, antes de mostrar qualquer informação o Excel vai exigir a senha que você configurou. A função CONCATENAR serve para unir os valores de duas ou mais células. Sintaxe: =CONCATENAR(texto1;[texto2];...) Função Concatenar Dica Não é possível concatenar grupos de células utilizando os dois pontos “:”. Faz- se necessário a utilização do ponto e vírgula “;” para separar os argumentos. É possível concatenar células ou valores utilizando o &. Neste caso, não é necessário iniciar a função concatenar. Exemplo: Concatenar A1 e A2. Digite =A1&A2 Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 70 Funções Esquerda e Direita Funções Arrumar As funções ESQUERDA e DIREITA retornam a quantidade de caracteres específicos de uma célula texto. A sintaxe das duas é a mesma. Sintaxe: =ESQUERDA(texto;[núm_caract]) =DIREITA(texto;[núm_caract]) Exemplo de uso das funções A função arrumar remove os espaços em excesso e mantém apenas o espaço simples que há entre as palavras. Trata-se de uma função bastante útil, pois muitas vezes, temos um banco de dados com cadastros com muitos espaços 5 caracteres, conforme argumento da função DIREITA 7 caracteres, conforme argumento da função ESQUERDA No detalhe das funções ESQUERDA e DIREITA Texto -> Texto fixo que deve estar entre aspas, ou célula que possua um texto. [núm_caract] -> Argumento opcional. Quantidade de carac- teres que será retornado na célula. ; -> Separador de argumentos. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 71 entre as palavras ou com espaços além da palavra. A sintaxe desta função é muito simples. Sintaxe: =ARRUMAR(texto) Criar e Formatar Tabela Atividade de Estudo O Excel possui o recurso excelente para criar tabelas. Ele facilita a utilização dos recursos de Filtro, estilo de tabela e cálculo de subtotal. Além disso, para cada nova informação após a tabela, em linha ou coluna, o Excel já considera que se trata do mesmo banco de dados e aplica a mesma formatação da tabela para o dado novo. Estamos finalizando nosso curso e você deverá criar uma tabela! Abra o arquivo Exercício Final e siga os passos a seguir. Praticar Exercício: Exercício FInal Para acessar o arquivo referente ao Exercício Final, navegue nas telas interativas ou baixe o arquivo na biblioteca do tema 3. Tempo de resolução de 10 a 15 minutos. Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 72 • PASSO 1: Em qualquer célula de valor da tabela, clique na guia Inserir Tabela. • PASSO 2: Deixe marcado que sua tabela possui títulos. • PASSO 3: Aperte Ok. Tabelas Tabela Pronto! Agora temos uma tabela. Perceba que uma nova guia surgiu: a guia Design. Vamos focar em alguns pontos importantes desta guia. Nome da Tabela É possível você dar qualquer nome para tabela, desde que não tenha espaço. Redimensionar Tabela Nesse ponto, você pode alterar os dados da sua tabela. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 73 Retira a linha dos títulos Ponto importante* Opções de formatação e Estilo da tabela Opções de formatação e Estilo da tabela Design – Opções de Estilo de Tabela Design – Estilos de Tabela Em cada coluna será possível realizar um Subtotal com qual- quer uma das funções disponíveis no Excel. Nos estilos de Tabela, você poderá escolher o melhor estilo que desejar de sua tabela, ou personalizar com seu estilo. A tabela apresentará, também, os mesmos botões do filtro, que você poderá classificar e/ou filtrar os dados da tabela con- forme sua necessidade. Esse será o resultado de nossa Tabela. *Esse, sem dúvida, é o ponto mais importante ao se traba- lhar com tabelas. Ao selecionar Linha de totais, você habilita o Excel adicionar a função Subtotal no final de sua tabela. Quando clicar, imediatamente, o Excel irá para o final de sua tabela. Anotações Excel - Nível Intermediário Excel Nível Intermediário - Tema 03 74 Tabela Vamos chegando ao fim do nosso curso. Vamos conhecer um critério importante para contagem dentro da função CONT.SE, assistindo nosso último vídeo, o vídeo 16. Vídeo 16: Dica CONT SE Para ter acesso ao vídeo Dica CONT SE, veja as telas interativas do tema 3. Agradeço, Rita. Continuarei estudando e acredito que terei mais segurança para elaborar melhor minhas planilhas. Espero que vocês tenham aproveitado bastante este curso. Não deixe de continuar os estudos. Em breve, teremos também o curso de Excel Avançado, com novos recursos sendo apresentados. Referência Glossário ANDRADE, Denise de Fátima. Excel2010 Controlado Dados. Santa Cruz do Rio Prado: Viena, 2011. MICROSOFT COPORTATION. Ajuda do Excel, 2007, Microsoft Corporation. Todos os direitos reservados, Versão 2010. SUPORTE OFFICE2016. Disponível em <https://support.office. com/pt-br/article/usar-fun%C3%A7%C3%B5es-aninhadas- em-uma-f%C3%B3rmula-9d7c966d-6030-4cd6-a052- 478d7d844166>.Acesso em: 22 maio 2018. • Alça de preenchimento: Recurso disponível no canto inferior direito de uma célula selecionada. Possível copiar fórmulas e valores para outras células ao clicar e arrastar na alça de preenchimento. • Área de trabalho: Também conhecida como desktop e área principal do computador, onde estão alguns ícones, pastas, programas e arquivos do computador. • Argumentos da função: São os elementos que compõem uma função e que estão sempre entre parênteses ( ). • Barra de Tarefas: Aplicativo que mostra todos as janelas abertas ou fixas. • Ícone: Símbolo de representação de um programa, pasta ou arquivo. • Fórmulas: É o conjunto de valores ou argumentos para que o Excel realize cálculos. • Funções: São fórmulas automáticas pré-definidas. • Sintaxe da função: É a forma da função. • Software: Conjunto de componentes lógicos de um computador ou sistema de processamento de dados; programa, rotina ou conjunto de instruções que controlam o funcionamento de um computador; suporte lógico. Excel tema 01 Excel Tema 02 Excel tema 03
Compartilhar