Baixe o app para aproveitar ainda mais
Prévia do material em texto
Curso Microsoft Excel 365 ______________________________________________________________________________________________ Desenvolvido por – Genivaldo Gomes de Souza Março de 2023 Manual Curso – Excel 365 1 Sumário CURSO DE EXCEL 365 2 Conceitos e Atalhos 3 Atalhos 5 Formatação e Formatação Condicional 10 Validação de Dados 18 Tabela 22 Formulas Básicas 24 Operadores 24 SOMA 26 ARREDONDAR 28 MAIOR e MENOR 31 CONCATENAR 31 Fórmula ESQUERDA 32 Fórmula DIREITA 33 Fórmula MAIÚSCULA 33 Construindo as fórmulas 33 Fórmula Concatenar – CONCATENAR 34 Finalizando o código 35 Exemplos de uso da função CONCATENAR 36 Concatenar para combinar células 37 Concatenar utilizando texto 37 Concatenar com operador “&” 37 Problemas comuns ao usar Concatenar no Excel 37 Funções 38 SE e PROCV 38 SEERRO 42 CONT.SES e SOMASES 46 Função CONT.SES 46 Função SOMASES (): 48 Tabela Dinâmica 51 Gráficos 62 Conclusão – Curso De Excel Office 365 73 2 Manual Curso – Excel 365 CURSO DE EXCEL 365 Esse é um curso de Excel Office 365 onde vamos abordar os assuntos que você precisa saber para dar os primeiros passos! Resumo Nesse curso vamos abordar algumas funcionalidades básicas que você precisa conhecer mostrando alguns conceitos, ferramentas e funções básicas do Excel para que você possa começar a utilizá-lo em sua rotina de trabalho se tornando mais produtivo. Sabemos que, pelo menos o nível básico de Excel, é exigido em diversas áreas e com esse curso esperamos te ajudar a ter um melhor domínio da ferramenta na hora da prática e para isso, vamos utilizar como objeto de estudos, a criação de uma planilha de controle de gastos pessoais, para que você comece a aprender a utilizar o Excel com um assunto bem comum no seu dia a dia, cuidar do seu dinheiro e organizar os seus gastos!!! Vamos te ajudar a dar os primeiros passos no Excel já dentro do Office 365, disponível atualmente para os servidores públicos, onde temos as atualizações mais recentes da Microsoft para o Excel! Abaixo nós temos os conteúdos que vamos abordar durante o nosso curso: Conceitos introdutórios Atalhos Formatação data, número, moeda e conversão padrão brasileiro Formatação condicional Validação de dados Tabelas Fórmulas básicas: a) OPERADORES b) SOMA c) MÉDIA d) ARRED e) MAIOR / MENOR f) ESQUERDA / DIREITA g) CONCATENAR Funções: a) SE b) PROCV c) SEERRO d) CONT.SES e) SOMASES Tabela dinâmica Gráficos Manual Curso – Excel 365 3 Conceitos e Atalhos O que vamos aprender na primeira aula do curso de Excel? Vamos ver uma apresentação geral do Excel, atalhos e preenchimento relâmpago. Quando falamos sobre aprender Excel a prática é sempre importante se você só ler ou assistir aos vídeos o seu aprendizado não será tão efetivo. Temos um arquivo das aulas para te ajudar a praticar e entender todos os conceitos que vamos te passar aqui recomendo que baixe eles. Assim que você abrir o arquivo vai ver a planilha a ementa das aulas. Aqui está a lista de tudo o que será ensinado em cada aula desse curso. Ementa do curso. 4 Manual Curso – Excel 365 Vamos aproveitar esta imagem que mostra a ementa para falar do primeiro conceito em Excel que você precisa saber, o conceito de planilha: Nós chamamos cada arquivo em Excel de pasta de trabalho, então o arquivo da aula é uma pasta de trabalho chamada “Curso de Excel - 365” dentro dessa pasta de trabalho nós vamos encontrar diversas planilhas. Nesse exemplo temos uma planilha chamada Aula01, outra chamada Aula02, Aula 03 e etc... Clicando em cada planilha você encontra várias informações, cada planilha tem seu conjunto de informações que podem estar ligadas ou não. Você pode criar na pasta quantas planilhas julgar necessário para organizar e analisar informações. Linhas e Colunas: Repare que cada planilha é dividida em linhas e colunas. As colunas são representadas por letras, como você sabe temos 26 letras no alfabeto. Elas seguem de A até Z, quando chega em Z o Excel começa a repetir o alfabeto da seguinte forma AA, AB, AC até AZ, depois BA, BB, BC e assim sucessivamente. Representação das colunas. As linhas são representadas por números indo de 0 a 1.048.567, ou seja, o Excel tem uma capacidade enorme de guardar informações! Outro conceito importante é o de célula: O que é célula no Excel? A célula é justamente o encontro de uma linha com uma coluna, neste ponto temos o que chamamos de célula no Excel. Célula. Podemos encontrá-la pelo nome que aparece quando clicamos nela (P15), por estar na junção entre a coluna P e a linha n°15. Manual Curso – Excel 365 5 Sei que esses conceitos são básicos, mas é importante esclarecer todos eles para servir como base para as próximas aulas. Outro conceito importante é o de guia: O que são as Guias? Em cada planilha podemos encontrar na parte superior as Guias do Excel. Temos as guias essenciais que são as que não precisam ser habilitadas, as guias de Arquivo, Página inicial, inserir, Layout da Página, Dados, etc. Página inicial. Para que servem as guias? O Excel tem várias ferramentas, ele divide essas ferramentas em grupos como se fossem caixinhas de ferramentas. Para cada caixa ele dá um nome que faz referência com o que tem dentro dela. Por exemplo, na guia exibir você encontra todas as opções de ferramentas para dar zoom, abrir nova janela, métodos de exibição, quebra de página, etc. Gui exibir. Até aqui foi passada uma apresentação geral importante, agora vamos falar sobre os Atalhos Conforme você for aprendendo o Excel vai perceber que os atalhos vão te fazer ganhar tempo, ficando menos dependente do mouse. Quanto antes você se acostumar com o uso dos atalhos, melhor para você! Logo nessa aula vou te passar alguns atalhos importantes para que você comece a adquirir um bom hábito. Ctrl + Pg Dn (PageDown) – Mantenha o Ctrl pressionado e depois pressione Pg Dn, este atalho serve para mudar de planilha, nesse caso para a planilha da direita dentro da pasta. Ctrl + Pg Up (PageUp) – Mantenha o Ctrl pressionado e depois pressione Pg Up, como no atalho acima ele muda de planilha dentro da pasta, mas agora para a planilha da esquerda. 6 Manual Curso – Excel 365 Obs. Caso o atalho não esteja funcionando no seu teclado, tente pressionar a tecla Fn em conjunto com a tecla Ctrl. Em alguns teclados ou notebooks é possível que essa tecla permita habilitar algumas outras teclas que ficam nos mesmos botões. Trabalhando com tabelas: Para podermos trabalhar com tabelas de uma forma eficiente também temos atalhos importantes: Tabela. Vamos supor que você tenha uma tabela como essa acima, porém gigante com muitas informações. Você precisa trabalhar com essas informações indo e voltando do início ao fim da tabela, existem atalhos muito usados e importantes para esta tarefa! Ctrl + seta para baixo = Este atalho serve para você chegar na última célula preenchida da tabela sem precisar passar célula por célula. Ctrl + seta para a direita = Este atalho tem a mesma ideia do anterior, mas te leva para a última célula preenchida da tabela para o lado direito. Manual Curso – Excel 365 7 Da mesma forma podemos usar o Ctrl + seta para cima ou Ctrl + seta para a esquerda que farão o mesmo, apenas alterando as direções de uma forma bem intuitiva. Estes mesmos comandos podem ter outra função! Se você estiver em uma célula vazia e quiser chegar a primeira célula preenchida, estes mesmos comandos vão te ajudar a encontrá-la se houver uma célula preenchida na direção que você escolheu. Por exemplo: Se você quiser descobrir qual o número da última linha da sua planilha: Clique na primeira célula em branco da coluna A e aperte Ctrl + seta para baixo, pronto você chegará na célula n°1048567. Também temos uma variação desse comando que é Ctrl + Shift+ Seta: Este comando também percorre as células preenchidas, porém, ele faz isso selecionando todas elas. Ctrl+shift+seta. 8 Manual Curso – Excel 365 Se você quiser selecionar todas as células de uma coluna para poder copiar ou mudar o formato, esse atalho vai te poupar muito tempo! Selecione a primeira célula da sequência que você quer e aperte Ctrl + Shift + Seta na direção desejada. Selecionando toda a planilha: Se você quiser selecionar toda a tabela vou te dar duas opções: Vá para a primeira célula da tabela e aperte Ctrl + Shift + Seta para baixo, depois Ctrl + Shift + Seta para a direita. Ou em qualquer célula dentro da tabela aperte Ctrl + T, pronto toda a tabela será selecionada! Esses são atalhos essenciais que você já pode se acostumar a usar. Ao longo das aulas vamos incluir mais atalhos importantes. O último tema que vamos abordar nesta aula é o preenchimento relâmpago: Para que serve? O preenchimento relâmpago serve para fazer o Excel replicar uma lógica que nós já aplicamos em um conjunto de células, vou te dar um exemplo: Na tabela abaixo temos uma lista com o código, a movimentação de conta e o ação, essas informações estão todas separadas por colunas, veja: Tabela de exemplo. Se fosse para juntar essas informações isso com certeza daria muito trabalho sem uma fórmula ou atalho, hoje vou te ensinar a fazer isso com o preenchimento relâmpago. Na coluna Exemplo relâmpago, escreva como quer que o Excel junte as informações das colunas. Aperte Ctrl + E para replicar a lógica para as demais células. Manual Curso – Excel 365 9 Agora veja que o Excel identificou a lógica da informação que você utilizou e a replicou para as demais! E ele vai fazer isso em toda a planilha te poupando muito tempo, é só você começar a digitar a informação que quer separar das demais. Preenchimento relâmpago. Observe a última coluna da tabela. Além de juntarmos as informações, ainda acrescentamos o traço como separador. Importante: Se não funcionar de primeira, pode ser que o Excel não tenha entendido sua lógica. Minha sugestão é que você escreva como exemplo as primeiras três células ao invés de só a primeira e depois aperte o atalho, isso provavelmente já vai resolver o problema. 10 Manual Curso – Excel 365 Formatação e Formatação Condicional Nessa aula vamos falar sobre Formatação e Formatação Condicional, dois temas importantíssimos! Vamos começar explicando o que é FORMATAÇÃO. Antes de começarmos, vamos converter o Excel 365 online para o padrão de formatação brasileiro e para isso você deve clicar em Arquivo > Opções > Configurações de Formato Regional Na janela seguinte, escolha o formato regional Português (Brasil) Pronto! Agora as formatações de data, numero e moeda estarão no padrão brasileiro. Voltando... Formatação é o modo como você mostra uma informação no Excel, por exemplo: Manual Curso – Excel 365 11 Na tabela abaixo os mesmos números foram formatados como Geral, como Moeda e como Porcentagem. Comparação. Este é um exemplo de uma formatação de número, também podemos ver nessa tabela uma formatação de borda e preenchimento. A primeira linha da coluna está com uma formatação de cor diferente, com a letra branca, fundo azul e em negrito também para destacar ainda mais o cabeçalho. E a tabela está com bordas. Podemos colocá-las só na parte de fora ou a opção todas as bordas que vai colocar a borda tanto fora quanto dentro. Bordas. 12 Manual Curso – Excel 365 Esses são alguns exemplos, mas temos uma infinidade de possibilidades dentro do Excel. Encontramos as opções de formatação dentro da guia Página inicial. Guia página inicial. Dentro de Página inicial temos algumas subdivisões. A primeira chama-se FONTE seguida de ALINHAMENTO, NÚMERO e ESTILO. Número: Quando nós digitamos um número no Excel, ele pode significar qualquer coisa. Com a formatação de número você restringe essas opções deixando mais fácil para quem está vendo a planilha saber que se trata de uma moeda ou porcentagem. Temos várias opções de formatação para se explorar dentro de Número. Formatação de número Algumas possuem diferenças puramente visuais como no caso de Moeda e Contábil. Manual Curso – Excel 365 13 Moeda e contábil. Formatando como Data: Quando você formata uma coluna como Data e depois muda para a formatação Geral, vai notar que aparece um valor na célula diferente da data que estava lá. O que é esse valor? É um número aleatório? Não! É que o Excel sempre lê um número por trás de qualquer data, com esse número ele faz os cálculos referente as datas que usamos. Se você trocar a formatação de Geral para Data novamente, repare que a data anterior vai ser restabelecida sem problemas. Mas de onde vem esse número? Se você colocar o número 1 e formatar como data vai surgir à data de 01/01/1900. Significa que no Excel essa contagem teve início em 01/01/1900 e a partir daí vai se somando 1 a cada dia. O número 2 refere-se à data de 02/01/1900 e assim sucessivamente até hoje. Datas no Excel. Vou te ensinar mais um atalho para quando você precisar fazer formatações! Esse atalho se chama Ctrl + 1, se não funcionar para você tente Ctrl + Fn + 1. Vai aparecer esta caixa com opções de formatação para números, alinhamento, borda, preenchimento e proteção. 14 Manual Curso – Excel 365 Formatar células. Clicando em moedas, por exemplo, você consegue: escolher a quantidade de casas decimais, O símbolo da moeda usada, se o valor é negativo, vai aparecer com o sinal de menos ou se vai ficar vermelho, etc. Agora que você já sabe o significado de Formatação, fica mais fácil explicar o que é a formatação condicional. Formatação condicional é formatar uma célula caso uma condição prévia seja atendida. Imagine que eu quero destacar todos os clientes que são Novos! Quero que fiquem com uma cor mais destacada na tabela. Como eu faço? Primeiro selecione o grupo de células que você quer formatar. Manual Curso – Excel 365 15 Formatação condicional. Vá em Página inicial → Formatação condicional. Dentro de formatação condicional temos cinco grandes grupos como na imagem abaixo. Formatação condicional. Clique em Regras de Realce das Células → É Igual a… 16 Manual Curso – Excel 365 É igual a… Vai aparecer um retângulo onde você vai colocar qual a palavra que deve ser destacada e também várias opções de como será o destaque. Pode escolher letra vermelha escuro com fundo vermelho, letra amarela escura com fundo amarelo, etc. Cor da célula. O resultado fica bonito e dinâmico, e se você trocar a palavra Antigo por Novo ele vai aplicar automaticamente a formatação ou retirá-la. Manual Curso – Excel 365 17 Formatação condicional. Podemos também fazer essa formatação para números usando a opção é maior que ou é menor que. 18 Manual Curso – Excel 365 Validação de Dados Nessa aula do nosso curso de Excel nós vamos falar sobre Validação de Dados e Tabelas, que são dois recursos muito importantes e muito úteis dentro do Excel. Você vai notar que vai utilizá-los quase sempre quando for criar um projeto ou fazer uma análise, pois eles vão facilitar a sua vida! Nós temos alguns tipos de validação de dados, como Lista Condicional, validação de dados personalizada, entre outras. Mas hoje eu quero te mostrar como essa validação de dados, que é uma restrição de informações, pode te ajudar a evitar problemas. Como assim problemas? Imagine que mais de uma pessoa faça o uso da sua planilha para inserir informações. Você não vai querer que essa pessoa escreva uma categoria errada por exemplo, o que vai influenciar na sua análise de dados. Imagine que você tem apenas 4 categorias e a pessoa por algum descuido quer acrescentar uma quinta categoria, ou até mesmo escreve errado uma delas. O Excel vaiaceitar, mas na hora de fazer suas análises você vai notar algumas inconsistências na sua análise, inclusive pode visualizar 2 palavras iguais (pois pode ter um espaço vazio em uma delas, seja no início, no meio ou no fim). E com isso você fica com mais informações do que devia e acaba não tendo sua análise correta. Por esse motivo é que temos a validação de dados, para restringir esses valores “errados” e evitar problemas futuros. Base de dados Nessa tabela nós temos algumas informações, mas na parte de categoria (tabela a direita) temos apenas 4 delas. A ideia aqui é restringir apenas a essas 4 informações, da forma em que estão nas células da coluna H. Então por mais que o usuário tente escrever algo fora do que está na lista, ou até mesmo escrever diferente do que está ali, o Excel não vai aceitar. Manual Curso – Excel 365 19 Assim você garante que as informações serão sempre essas e não vão mudar, dessa forma sua análise vai ficar correta levando em conta apenas as 4 categorias. Como fazer para inserir a validação de dados? O primeiro passo é selecionar as células nas quais vamos restringir essas informações, que nesse caso são as células B2 até B15. Em seguida vamos até a guia Dados e vamos selecionar a opção Validação de Dados. Validação de dados OBS: Pode ser que o ícone de validação de dados esteja menor no seu computador dependendo do tamanho do monitor que você tem, então é possível que esteja menor para se adequar ao tamanho da tela. Selecionando o intervalo da lista Feito isso vai aparecer uma janela para que você possa configurar a validação de dados. Nesse caso como já temos uma lista de informações, basta selecionar Lista em Permitir. E em Fonte você pode selecionar o intervalo que contém as informações que você vai restringir, que no nosso caso são as informações das células H2 até H5. Depois basta clicar em OK que vamos ter a validação aplicada. Com isso temos 2 pontos importantes acontecendo. 20 Manual Curso – Excel 365 O primeiro ponto é que agora o Excel só vai aceitar que o usuário escreva exatamente uma das opções que você tem nessa lista, caso contrário ele vai mostrar o erro abaixo: Erro ao inserir valores fora da lista OBS: Nas outras aulas de validação de dados que colocamos os links, nós falamos sobre as guias de Mensagem de Entrega e Alerta de erro. Que são opções para você mudar essa mensagem para facilitar o entendimento do usuário. Assim ele sabe o porquê essa mensagem está aparecendo. O segundo ponto importante é que agora todas as células que você aplicou a validação de dados, elas possuem uma setinha, e ao clicar nessa seta você vai visualizar a lista de opções que tem para selecionar. Célula com validação de dados com lista Dessa forma o usuário não precisa nem perder tempo escrevendo, ele pode simplesmente selecionar a opção desejada. Isso além de deixar o processo mais rápido evita que ocorram erros de digitação, assim você garante que as informações serão sempre iguais e não vai ter divergência na hora de fazer suas análises. Para finalizar você deve ter notado que dentro da validação de dados não temos apenas a opção de lista, temos algumas outras opções para restringir as informações. Manual Curso – Excel 365 21 Tipos de validação de dados Entre elas vamos poder restringir o tamanho do texto para garantir que a quantidade de caracteres não ultrapasse um valor, podemos restringir datas para evitar que os usuários coloquem texto, e assim por diante. A ideia é restringir as informações para que não seja possível inserir algo diferente do que foi estabelecido, assim você não tem problema na hora de fazer suas análises e acabar encontrando algumas inconsistências. 22 Manual Curso – Excel 365 Tabela Esse é um recurso muito útil dentro do Excel e vai fazer com que você ganhe muito tempo também, porque ele vai automatizar algumas ações para você. Primeiro vamos transformar nosso intervalo de dados em uma tabela. Para isso basta selecionar todo o intervalo, depois ir até a guia Página Inicial e selecionar a opção Formatar como Tabela. Opção de formatar como tabela Ao clicar nessa opção você vai notar que o Excel te dá várias opções já prontas de alguns estilos de tabela, então já pode escolher o que mais gostar ou que mais se adapta ao tema que está utilizando. OBS: Você também tem a opção de personalizar um estilo de tabela caso queira fazer algumas modificações. Feito isso a primeira mudança que você vai observar além do layout que mudou de acordo com o que escolheu é que agora temos setas nos cabeçalhos da nossa tabela. Filtros da tabela Essas setas são filtros, então você pode de forma rápida e fácil filtrar informações das colunas para facilitar a sua visualização e análise dos seus dados. Manual Curso – Excel 365 23 Dessa forma não tem que ficar caçando informações de forma manual para fazer comparações ou análises, pode filtrar quais informações deseja visualizar. Lembrando que você pode filtrar mais de uma coluna ao mesmo tempo, assim terá uma análise bem mais detalhada do que precisa visualizar. Além dessa facilidade temos uma outra que é muito importante, que ao adicionar novas informações abaixo da tabela, elas já vão se tornar parte da tabela e vão puxar toda a formatação das células acima. Isso quer dizer que você não vai precisar ficar formatando os dados sempre que colocar uma nova informação. Validação de dados na nova linha inserida na tabela Aqui eu completei as linhas 14 e 15 que já estavam com a formatação de tabela e comecei a inserir uma nova informação na linha 16. Com isso o próprio Excel já tornou essa linha parte da tabela e já puxou todas as formatações das células acima, inclusive a formatação condicional. Então não precisa atualizar sua formatação condicional ou qualquer outra formatação que tenha na tabela, seja de moeda, data, porcentagem… Por isso é tão importante formatar seus dados como tabela, pois isso vai automatizar seus dados para que você não tenha que refazer nenhuma formatação e ainda facilita na hora de visualizar seus dados. Agora você já pode aplicar tanto a formatação de tabela quanto a validação de dados nas suas planilhas para evitar erros e se tornar mais produtivo! Sempre começo recomendando que vocês baixem o material da aula, não só para acompanhar, mas também para praticar depois. Vou deixar o material da aula no Curso De Excel Office 365! 24 Manual Curso – Excel 365 Formulas Básicas Nesta aula vou te apresentar as formulas básicas do Excel! Como utilizá-las? Quais as principais funções e como trabalhar com os operadores do Excel? Se este é o seu primeiro contato ou se você deseja revisar o conteúdo, esta aula vai esclarecer suas dúvidas! Operadores O que são os operadores no Excel e quando devemos usá-los? Os operadores são símbolos que nos permitem calcular sem o uso de fórmulas. Vamos usar uma tabela de Faturamento mensal para exemplificar. Faturamento. Utilizando os numeradores vamos fazer uma soma com os valores dessa tabela. Passo a passo: Primeiro você deve colocar o sinal de igual (=) e digitar o nome da primeira célula que se pretende somar ou clique na célula → depois coloque o sinal de (+) → escreva o nome da segunda célula → pressione o enter. Quando fizer isso, vai notar que o Excel pinta os nomes na função da mesma cor da célula correspondente, isso é bom porque você consegue associar todos os nomes as células que está calculando de uma forma muito visual. Manual Curso – Excel 365 25 Identificação por cores. Outra vantagem é que se você modificar o valor de uma das células usadas para esta soma, o resultado irá se atualizar automaticamente. Como nem todos os operadores seguem iguais aos da matemática, vou deixar aqui para você uma tabelinha com as principais operações. Operadores. Agora imagine que ao invésde querer somar os dois primeiros valores da tabela, você precisasse somar todos eles. Imagine que essa tabela fosse maior, com mais de 500 linhas, por exemplo. Seria muito trabalhoso ter que fazer este cálculo utilizando os numeradores, clicando célula por célula. Para isso temos as funções! Vamos falar sobre elas, que servem justamente para essas situações em que definitivamente não compensa usar os operadores. 26 Manual Curso – Excel 365 SOMA Como você começa a usar uma função dentro do Excel? O primeiro passo é clicar em uma célula e colocar o sinal de igual (=) quando você digita o sinal de igual o Excel entende que você vai utilizar uma função. Se você escrever a palavra soma ou média, para o Excel se trata apenas de um texto. Agora se usar o sinal de igual seguido por soma ou média, o Excel enxerga como uma função e já abre uma caixa de opções para você. Lista de funções. Quando essa lista da imagem acima aparecer, você tem duas opções, pode terminar de escrever o nome da função e abrir parênteses ou pode usar a tecla TAB que vai completar para você o nome da função. Funções principais: =SOMA (Argumentos1; Argumento2…) Após colocar o sinal de igual e escrever o nome da função dentro dos parênteses temos o que chamamos de argumentos que no caso da função soma são os números que queremos somar. Como queremos somar vários números de forma rápida, não compensa clicar ou escrever célula por célula. Aqui você pode após abrir os parênteses → clicar no primeiro número a ser somado e arrastar até o último ou clicar no primeiro valor a ser somado → aperte shift + Ctrl + seta para baixo. Esse atalho irá selecionar todas as células que estiverem preenchidas! Manual Curso – Excel 365 27 Atalho shift + Ctrl + seta para baixo. Observe que dentro dos parênteses aparece (B2:B13) o sinal de igual entre os nomes das células significa que ele está somando todos os valores a partir de B2 até B13. Após fechar parênteses e dar enter aparece o valor total (R$ 316.924). Para calcular a média o procedimento é o mesmo usado na soma, a diferença é que você vai usar depois do sinal de igual o nome média =MÉDIA (), com acento! OBS: É importante colocar os acentos na hora de escrever o nome das funções, pois com o Excel em português ele precisa da escrita correta para encontrar a função e fazer com que ela funcione corretamente. O contrário da operação de soma é a subtração, é importante dizer que não temos uma função específica para a subtração, multiplicação ou divisão. Então podemos conseguir o resultado usando os operadores mesmo: Subtração: = (celula1 – celula2) Multiplicação: = (celula1 * celula2) Divisão: = (celula1 / celula2) 28 Manual Curso – Excel 365 ARREDONDAR O que é a função arredondar Excel? Mas, afinal, para que serve a função arredondar Excel? O próprio termo já nos adianta um pouco de suas utilidades. Basicamente a função é usada para obtermos números melhores e mais fáceis de se trabalhar no dia a dia. Então, vamos pensar em um exemplo para tudo ficar mais claro, ok? Considere que você trabalha em uma fabricante de geladeiras. O estoque da empresa indica que, atualmente, existem 517,82 produtos disponíveis. Mas percebe que essa é uma numeração quebrada? O ideal é que o número não tenha casas decimais. Como Usar a Função Arredondar Excel? A transformação dos números quebrados em inteiros, felizmente, pode ser feita por meio da função ARRED, que representa o arredondamento científico. Esse tipo de aproximação nós aprendemos na escola. Vamos relembrá-lo, então? Números que, depois da vírgula, tenham outros números menores que 5, arredondam para baixo. Exemplo: 3,1 vira 3. Já os números que tenham, depois da vírgula, outros números maiores do que 5 arredondam para cima. Exemplo: 8,7 vira 9. Voltando ao nosso exemplo da geladeira, o 517,82 será transformado em 518, certo? A função ARRED (função para arredondar no Excel) Nesse caso, a fórmula é composta pelo seguinte: =ARRED (num;núm_digitos). Função ARRED A abreviação “núm.” representa justamente o número que você quer arredondar na planilha. E a abreviação “núm_digitos” nada mais é do que a quantidade de casas decimais que você deseja ter. Manual Curso – Excel 365 29 O exemplo da geladeira ficaria assim na fórmula: =ARRED(517,82;0). O resultado vira 518. Como Usar a Função Arredondar Para Cima? Até aqui, já vimos bastante coisa sobre a função arredondar Excel, certo? Mas há ainda mais conteúdo pela frente… Chegou a hora de descobrir como usar a função arredondar para cima Excel. A fórmula é assim: =ARREDONDAR.PARA.CIMA(núm;núm_digitos). Arredondar para cima A lógica de preenchimento é a mesma da função ARRED, ou seja, em “núm”, você coloca o valor que deseja arredondar. Já em “núm_digitos”, preencha quantas casas decimais é preciso que o novo valor fique. Como Usar a Função Arredondar Para Baixo? A fórmula da função arredondar para baixo Excel fica assim: =ARREDONDAR.PARA.CIMA(núm;núm_digitos). Arredondar para baixo Esses dois últimos casos de fórmulas (para cima e para baixo) têm uma nomenclatura bem extensa. Mas saiba que você não precisa digitar absolutamente tudo. Basta colocar apenas uma parte que, logo, o Excel exibirá uma caixinha abaixo da linha com as opções de arredondar. Assim, é possível ganhar tempo e agilidade nas suas tarefas. Não há análise que fazer. As funções simplesmente “jogam” os valores ou para cima ou para baixo. Como aprender mais sobre função arredondar Excel? A teoria, agora, você já sabe. Deixamos em suas mãos! O passo, neste momento, é treinar e treinar bastante. Sim, todo mundo que sabe demais do programa passou e, ainda, passa horas aplicando fórmulas. Sendo assim, não deixe de praticar, pelo menos, alguns minutinhos por dia. 30 Manual Curso – Excel 365 Manual Curso – Excel 365 31 MAIOR e MENOR Imagine que você queira saber o maior ou o menor valor de uma lista, seja por uma pesquisa ou porque precisa montar um ranking. Essa fórmula resolve o problema. Argumentos da função. O primeiro argumento da fórmula é a matriz, ou seja, a lista com os números onde vamos pesquisar qual o maior valor entre eles. O segundo argumento é o K, aqui você vai indicar o número do ranking. Você quer que a função retorne o primeiro maior valor? O segundo maior? Funciona da mesma forma para a função Menor, a diferença é que vai te resultar qual o primeiro, segundo ou o terceiro menor valor da lista. CONCATENAR Vamos mostrar como utilizar a fórmula Concatenar, além de explicar também as fórmulas ESQUERDA e DIREITA com exemplos! O que é a fórmula CONCATENAR? É uma fórmula de Texto do Excel. Ela é utilizada para concatenar (juntar) textos com outros textos, ou até mesmo textos com números. É uma fórmula muito útil principalmente quando há a necessidade de gerar códigos para pessoas ou produtos. Quando utilizar a fórmula Concatenar? Essa fórmula será utilizada sempre que o usuário precisar fazer a junção de textos com textos ou textos com números. No exemplo que vamos ver vamos criar um código através das informações de uma tabela inicial que tem o número, nome e profissão de cada pessoa. 32 Manual Curso – Excel 365 Tabela – Fórmula de textos Além da fórmula CONCATENAR, vamos utilizar, para a criação do código, mais três fórmulas: MAIÚSCULA, ESQUERDA e DIREITA. É possível observar que o código criado não permite identificar o nome da pessoa ou profissão e isso pode ser utilizado para mascarar os dados reais. Além disso, pode facilitar na hora de uma busca ou de algum procedimento dentro de uma tabela com muitas informações. Como utilizar a fórmula CONCATENAR? Como nosso objetivo é criar um código para cada um dos funcionários temos que, primeiramente, analisar como foi construído o código. Esse código foi construído através de uma segundatabela que contém partes da nossa primeira tabela, sendo elas: Nome, Sobrenome, Profissão e Número. Cada uma dessas partes foi criada de uma forma. Abaixo podemos ver como elas foram criadas: Nome – Para a criação da primeira parte do código foram utilizadas as duas primeiras letras do nome do(a) cantor(a). Sobrenome – Neste caso foram utilizadas as duas últimas letras do sobrenome do(a) cantor(a). Profissão – Este é similar ao nome, foram utilizadas as duas primeiras letras da profissão. Número – Neste caso, são utilizados três zeros e, em seguida, o número atribuído a cada cantor(a). Para que possamos criar esse código precisamos aprender algumas fórmulas, vamos iniciar com a fórmula ESQUERDA. Fórmula ESQUERDA A fórmula ESQUERDA é utilizada para pegar uma quantidade x de caracteres, números, símbolos, letras… que iniciam da esquerda para a direita, ou seja, os primeiros caracteres de um texto, por exemplo. Essa fórmula precisa de apenas dois argumentos: o primeiro deles é o texto, ou seja, de onde serão obtidos os caracteres e em seguida um número, que corresponde a quantos caracteres serão extraídos do texto. Manual Curso – Excel 365 33 Fórmula ESQUERDA Fórmula DIREITA A outra fórmula que será utilizada é a DIREITA, que funciona de forma oposta a fórmula anterior. Em vez de pegarmos os primeiros caracteres de um texto vamos pegar os últimos. Essa fórmula também precisa de dois argumentos: texto, onde serão extraídos os caracteres; número de caracteres a ser extraído. Fórmula Direita Com essas duas fórmulas já podemos preencher as três primeiras colunas da nossa segunda tabela. Ao fazer isso, teremos na primeira coluna o resultado Lu, na segunda na e na terceira Ca. No entanto, podemos observar que o nosso código é composto por todas as letras maiúsculas e seguida de um número. Por isso, precisamos de uma fórmula que transforme todas as letras em MAIÚSCULA. Tabela Base para códigos Fórmula MAIÚSCULA Essa fórmula é exatamente o nome do que precisamos que ela faça, isto é, transformar o texto em MAIÚSCULA. É uma fórmula bem simples e possui apenas um argumento que é o texto. Texto esse que será transformado em maiúscula. Vamos incluir nossas fórmulas ESQUERDA e DIREITA dentro dessa outra função. Com isso, teremos apenas os textos já extraídos transformados em maiúscula, exatamente como queremos no código. Construindo as fórmulas Na célula E3 temos a seguinte fórmula: 34 Manual Curso – Excel 365 Fórmula ESQUERDA Essa fórmula está pegando os dois primeiros caracteres da célula B3, que contém o nome Luan Santana. Como queremos que as letras fiquem todas maiúsculas temos que acrescentar a fórmula MAIÚSCULA antes da nossa fórmula ESQUERDA. Ou seja, teremos a fórmula ESQUERDA como argumento da fórmula MAIÚSCULA. Fórmula Maiúscula e Esquerda Agora podemos repetir o mesmo procedimento para as outras colunas. Lembrando que na segunda coluna iremos utilizar a fórmula DIREITA, pois vamos pegar os dois últimos caracteres do sobrenome de cada cantor. Tabela Base para códigos Feito isso, já temos quase nosso código completo, faltando apenas a última coluna da segunda tabela para podermos criar nosso código. Fórmula Concatenar – CONCATENAR Para criar a última coluna da nossa segunda tabela vamos utilizar a fórmula CONCATENAR. Essa fórmula não tem uma quantidade específica de argumentos, a quantidade será definida pela quantidade de textos e/ou números que o usuário queira concatenar. Vamos utilizar os três zeros “000” e o número de cada cantor como argumentos dessa função. Fórmula CONCATENAR * *Você pode usar tanto CONCAT quanto CONCATENAR que o resultado será o mesmo! Para isso vamos utilizar a seguinte fórmula: Fórmula CONCAT Manual Curso – Excel 365 35 O primeiro argumento que temos é o “000”, com aspas, pois como nosso argumento precisa ser um texto (o Excel entende que alguma informação é texto quando estão entre aspas duplas). Em seguida, vamos selecionar a célula que contém o número do cantor, que é A3. Feito isso, teremos todas as nossas colunas preenchidas. Tabela Base para códigos Arrastando para baixo Para preencher linhas debaixo basta selecionar a segunda coluna, clicar no canto inferior direito da última coluna selecionada (que terá um quadrado verde e o ponteiro do mouse será alterado para uma cruz) e arrastar para baixo. Tabela Base para códigos Tabela Base para códigos Finalizando o código Agora temos todas as nossas informações preenchidas na segunda tabela, faltando somente criarmos o código final. Esse código é simplesmente a junção de todas as informações contidas na segunda tabela que acabamos de construir. 36 Manual Curso – Excel 365 Código construído Para juntar textos vamos utilizar novamente à fórmula CONCATENAR, no entanto agora vamos utilizar quatro argumentos, que são as quatro informações de cada cantor. Fórmula CONCAT Dessa forma, estaremos concatenando as informações das células E3, F3, G3 e H3. O resultado final ficará assim: Trechos concatenados Para preencher os outros códigos, vamos novamente selecionar a célula que contém a fórmula e arrastar para baixo. Tabelas finalizadas Exemplos de uso da função CONCATENAR Realmente a função concatenar é muito mais comum e, consequentemente, útil do que se imagina. Além de poder usá-la para inserir códigos de produtos e, então, ter os nomes das mercadorias, é possível aplicá-la em outras situações. Veja mais alguns exemplos sobre concatenar intervalos no Excel: Manual Curso – Excel 365 37 junção de nome e sobrenome: a função concatenar é excelente para juntar as informações de nome e sobrenome (disponíveis em colunas diferentes). Algo bastante comum, por exemplo, na rotina de profissionais de RH e, também, de professores. média de notas — falando em professores, mais uma utilidade da concatenar é fazer o agrupamento de notas, de diferentes colunas, em uma terceira coluna. Isso já facilita e otimiza bastante o fechamento de bimestre ou semestre. junção de intervalos de datas — outra utilidade da função concatenar é agrupar datas diferentes, inseridas em colunas distintas. Por exemplo, você escreve 1/1/2022 na coluna A, 1/1/2021 na coluna B. E na coluna C, aplicando a função em pauta, fica 1/1/2022 – 1/1/2021. Concatenar para combinar células De forma geral, para combinar células, é preciso aplicar a fórmula pronta concatenar. Ela fica exatamente assim: =CONCATENAR(células em que estão as informações que você deseja usar). Dependendo da sua atividade, pode ser que existam mais alguns passos na aplicação da fórmula. Por isso, é importante ler ou reler o parágrafo “como utilizar a fórmula CONCAT”. Concatenar utilizando texto A função texto concatenar te permite fazer vários ajustes extremamente necessários aos seus documentos. Basicamente, há duas formas de fazer isso. A primeira é adicionando aspas + espaço entre os textos. Então ficaria assim: =CONCATENAR(A2, ” “, A3) A segunda forma é a de apenas adicionar um espaço, logo após o argumento texto. Concatenar com operador “&” Já para concatenar com &, basta colocar os valores e o & no meio. Por exemplo: =(B1 & B2) O resultado será o mesmo que =CONCATENAR (B1;B2). Problemas comuns ao usar Concatenar no Excel De acordo com a própria Microsoft, os erros mais comuns sobre concatenar Excel são os seguintes: aspas que aprecem na cadeia de resultados — acontece porque a vírgula entre os argumentos acabou sendo omitida. A solução é não usar aspas em caso de números; palavras que aparecem misturadas — também pode acontecer as palavras aparecerem todas misturadas. Se isso for o seu caso, dá para adicionar aspas duplas entre os textos ou, ainda, colocar um espaço a mais, após o argumento de texto; termo #NOME — acontece quando falta aspas em um dos argumentos de texto. https://www.hashtagtreinamentos.com/mediase-como-fazer38 Manual Curso – Excel 365 Funções SE e PROCV Agora vamos abordar duas funções muito importantes do Excel que são: função SE Excel e função PROCV Excel! Você provavelmente já ouviu falar delas não é mesmo? Então eu vou te ensinar a utilizá-las, pois são funções que você vai usar muito nos seus projetos de Excel! Vamos começar pela função SE, essa é uma função condicional, ou seja, ela vai analisar uma condição para retornar um resultado. Então vamos fazer uma comparação e de acordo com essa comparação podemos ter 2 respostas, a resposta caso a comparação seja verdadeira e a resposta caso a comparação seja falsa. Tabela de dados Nesse exemplo aqui nós vamos classificar os produtos de acordo com uma segunda tabela, que tem a venda e o status para cada valor. Então se a venda for acima de 35.000 temos um produto ouro, caso contrário temos um produto prata. Argumentos da função SE Essa é a estrutura dessa função, então o primeiro argumento da função é o nosso teste lógico, que nada mais é do que a nossa comparação. O segundo argumento é o resultado que teremos caso o nosso teste seja verdadeiro e o terceiro argumento é o resultado caso o teste seja falso. Então vamos partir para as comparações da nossa tabela para poder classificar nossas vendas em Produto Ouro e Produto Prata. Manual Curso – Excel 365 39 Classificando o produto com a função SE IMPORTANTE: Como nós só temos 2 possíveis resultados para essa classificação, só precisamos de um teste lógico, pois um valor se não é maior do que o outro, ele só pode ser igual ou menor. Então assim já temos uma divisão para o resultado verdadeiro ou falso. Aqui estamos verificando se o valor da célula B2 é maior (>) do que 35.000. Se esse valor for de fato maior vamos inserir o texto “Produto Ouro” na célula, caso contrário ele será igual ou menor do que 35.000. Nesse caso será classificado como “Produto Prata”, dessa forma conseguimos fazer essa classificação de forma bem rápida e simples! Após pressionar enter você já vai ver o resultado na célula e pode copiar a fórmula para as demais células. Resultado da função SE Lembrando que como estamos utilizando uma função, ela é automática. Isso quer dizer que se você alterar o primeiro valor para 37.000 por exemplo. O próprio Excel já vai alterar o status para Produto Ouro, o mesmo vale para qualquer outro valor que você altere. Então além de ser uma função fácil e muito útil ela é automática, o que facilita na hora de ajustar suas informações! 40 Manual Curso – Excel 365 Agora vamos para outra função muito importante que é a função PROCV! Essa é uma função muito falada e é muito importante para fazer uma busca de informações na vertical para evitar que você faça isso de forma manual. Só que temos alguns detalhes nessa função, o primeiro deles é que ela busca de cima para baixo e da esquerda para a direita. Como funciona a busca da função PROCV Por que estou falando isso? Você vai notar que se formos buscar a venda ou o status com base no produto está tudo certo. Mas imagine que você tenha o valor da venda e quer saber qual é o produto. Isso não será possível, pois o PROCV só consegue retornar as informações que estão à direita da coluna que tem a informação. Então não poderá trazer a informação de produto, a não ser que você troque essa coluna de lugar e a coloque a direita da coluna Venda. Estou dizendo isso porque é muito comum as pessoas tentarem procurar qualquer informação a partir de qualquer coluna, e você vai se deparar com esse problema. Uma das opções é trocar a coluna de lugar, mas geralmente isso não é muito usual, fora que você acaba trocando informações o que pode prejudicar na hora da análise ou até mesmo de outro. Sabendo disso fica mais fácil entender como essa função funciona! Manual Curso – Excel 365 41 Argumentos da função PROCV Essa função possui 4 argumentos, sendo que o último deles não é obrigatório: Valor procurado – É a informação que você está procurando na tabela, que é a coluna que vai fazer a busca, ou seja, o seu resultado só pode estar à direita dessa coluna; Matriz tabela – É o intervalo em que você vai buscar as informações, lembrando que vamos sempre partir da coluna da busca até a coluna que possua o que queremos retornar; Núm índice – É o número da coluna que vai trazer o resultado, levando em consideração o intervalo selecionado. Isso quer dizer que a contagem de colunas começa da primeira coluna selecionada e não a primeira coluna da tabela; Procurar intervalo – Esse é um argumento que não é obrigatório, mas quando colocamos 0 ele faz uma busca exata, ou seja, vai buscar exatamente o que está dentro do valor procurado. Já quando colocamos 1 ele vai fazer uma busca aproximada, mas em 99% dos casos vamos deixar 0 ou não vamos mexer que é o padrão sempre a busca exata! Conhecendo os argumentos, podemos dar início ao nosso exemplo! Produto a ser procurado para retornar Venda e Status Nós vamos querer trazer tanto a venda quanto o status do produto através da função PROCV. PROCV para busca da venda Aqui estamos procurando o produto que está na célula H7 que é o H9356582, dentro do intervalo que vai da célula A2 até a célula C11. E vamos retornar a informação que está na segunda coluna. Temos que a primeira coluna é produto, a segunda é venda e a terceira é status. Então para essa primeira função vamos retornar o status do produto. Agora podemos fazer o mesmo para retornar o status! Só que aqui você tem que tomar MUITO CUIDADO, pois não basta apenas arrastar a fórmula para célula abaixo. 42 Manual Curso – Excel 365 Por que não? Se você fizer isso tanto o seu valor procurado quanto o intervalo da sua matriz serão deslocados 1 linha para baixo. Então já vai estar procurando a informação errada, dentro do intervalo errado, isso é muito importante, principalmente quando vai aplicar o PROCV em várias células. Precisa fazer o trancamento para que a função funcione corretamente! PROCV para busca do status Veja que tanto o intervalo quanto o valor procurado são os mesmos, a única alteração que fizemos foi no núm índice, que é a coluna que vamos trazer o resultado. Como agora queremos o status do produto, vamos retornar uma informação da terceira coluna! Busca automática utilizando a validação de dados Aqui temos o nosso resultado. Veja que ao lado da célula de produto nós utilizamos uma validação de dados para não ter que ficar escrevendo todos os códigos dos produtos e facilitar na hora da busca! SEERRO O SEGREDO PARA TRATAR QUALQUER ERRO NO EXCEL Alguma vez, você já viu o erro chamado “#N/D” no Excel? Saiba que é comum aparecer equívocos dessa forma e de outras diferentes. Nessas horas, é fundamental saber como lidar e tratar esses erros. É por isso que vamos falar sobre a função SEERRO. Você vai ver que ela é fundamental para tratar os erros e, assim, deixar a sua planilha perfeita. O que é a fórmula SEERRO? SEERRO é uma fórmula do Excel utilizada para não mostrar os erros — de forma poluída — encontrados na planilha. Esses equívocos podem ser em relação a números e textos. Sintaxe da função Vale sempre lembrar que o termo sintaxe significa a fórmula da função, ok? Nesse caso, então, da SEERRO, a sintaxe fica assim: =SEERRO(valor; valor_se_erro). Os componentes da função têm os significados litados abaixo: valor — representa o valor a ser retornado, se o próprio valor não for um erro; https://www.hashtagtreinamentos.com/trancamento-no-excel https://www.hashtagtreinamentos.com/validacao-de-dados-excel Manual Curso – Excel 365 43 valor_se_erro — é o valor que aparece, caso o próprio valor corresponda a um erro (esse é um componente opcional). Quando utilizar a função SEERRO? Essa fórmula é muito utilizada para evitar com que o Excel mostre erros por conta de problemas. Os equívocos podem ocorrer, por exemplo, em umafórmula em que não foi encontrado um dado, entre outras opções que resultem em algum erro. Já a grande finalidade dessa fórmula é que a planilha do usuário não fique poluída com determinadas informações de erro. É exatamente por isso que, por meio da SEERRO, dá para trocar esse texto de erro por um texto qualquer. Ou, ainda, por um número qualquer para mascarar esse problema. Portanto, essa fórmula é mais do que útil para tratar esses erros. Exemplo 1: Preço Unitário Imagine que você trabalha no controle de estoque de um supermercado, ok? Por isso, diariamente, você lida com planilhas em que há listas de produtos e seus respectivos preços. Ao utilizar a função SEERRO, dá para procurar um determinado valor. Se ele não existir, então, é possível pedir que o Excel indique isso de outra forma com a frase “deu erro”. Exemplo 2: Notas de alunos A mesma lógica segue para o exemplo de notas dos alunos. Com o SEERRO, você pode pedir para os erros serem indicados de outra forma. Então, se o cálculo das notas dos alunos estiver errado, o Excel pode mostrar, por exemplo, uma célula vazia. Como utilizar essa fórmula? Para mostrar o funcionamento dessa fórmula vamos utilizar a análise de uma fórmula já construída que irá gerar um erro para exemplificar melhor e verificar o tratamento desses erros. Primeiramente vamos começar com uma tabela que possui a fórmula PROCV. 44 Manual Curso – Excel 365 Tabela contendo a fórmula PROCV para procurar um vendedor Neste caso temos uma tabela com o nome dos vendedores e suas respectivas vendas. Na célula E1 temos o funcionário que estamos procurando e por fim na célula E2 temos a fórmula PROCV para verificar o valor da venda que esse vendedor fez. É possível observar que estamos tentando buscar pelo nome Cláudio na lista de vendedores e retornar o valor da vende que ele fez, no entanto nessa lista não temos esse nome, portanto o Excel retorna esse erro #N/D. O que vamos fazer agora é tratar esse erro para evitar de mostrá-lo quando o vendedor não for encontrado. Para isso vamos utilizar a fórmula SEERRO. Argumentos da fórmula SEERRO Essa é uma fórmula bem simples que possui apenas dois argumentos. O primeiro deles é a fórmula normal que já estamos utilizando, que neste caso é o PROCV, enquanto o segundo argumento é o que vamos retornar caso esse valor retorne um erro. Para facilitar a visualização vamos escrever a mesma fórmula de PROCV na célula abaixo utilizando a fórmula SEERRO e caso retorne um erro vamos simplesmente escrever na célula ERRO. Manual Curso – Excel 365 45 Utilizando a fórmula SEERRO para tratar o ND do PROCV Veja que temos a mesma fórmula, mas uma delas possui a fórmula SEERRO para tratar esse erro e retornar esse texto, outra opção seria inserir o texto “Não Encontrado” para que o usuário saiba que o nome do vendedor não foi encontrado ao invés de ter o símbolo do erro que pode confundi-lo caso não tenha muito conhecimento em Excel. Alterando o texto que será mostrado ao usuário em caso de ND no PROCV Outra opção que é muito comum é colocar apenas duas aspas duplas “” que significa vazio, ou seja, caso tenhamos um erro a célula simplesmente ficará vazia sem qualquer informação. Lembrando que a fórmula SEERRO poderá ser utilizada com qualquer outra fórmula, e caso retorne algum erro já temos como tratar esse erro. Dependendo do caso pode ser interessante deixar apenas a célula vazia ou então informar ao usuário com uma mensagem sobre o erro. Como usar SEERRO com PROCV? Você sabia que é possível combinar as funções SEERRO com PROCV? Vale relembrar que a PROCV é usada para fazer uma espécie de varredura na planilha e, com isso, apontar erros. Já a SEERRO mostra os equívocos de outra forma, por exemplo, com um resultado igual a O ou com a célula vazia. Isso possibilita uma planilha “mais limpa”. Faça uma planilha com informações sobre: nomes de funcionários, datas e valores de vendas. Em seguida, aplique a função PROCV. Se você estiver procurando um nome que não existe, a PROCV vai retornar um erro, por exemplo: #N/D (não disponível). 46 Manual Curso – Excel 365 Como isso fica muito feio, na planilha, aplicamos a SEERRO. Ela possibilita deixar a célula em branco ou com uma frase específica, como: “erro”. Além disso, a SEERRO pode fazer um novo cálculo (segunda opção, caso a primeira não dê certo). CONT.SES e SOMASES Nesta aula vou te ensinar duas funções importantíssimas para quem pretende trabalhar com Excel, são as funções CONT.SES e SOMASES. Vou deixar o material da aula disponível para download, é muito importante colocar em prática o aprendizado das aulas. Vamos utilizar a tabela abaixo para responder às perguntas que separamos como exemplo. Tabela – Controle de estoque. Para esclarecer o uso das funções vamos responder a quatro questões com um passo a passo simples! Função CONT.SES O objetivo da função CONT.SES é contar quantas vezes um ou mais critérios aparecem nas linhas de uma coluna. Por exemplo: Manual Curso – Excel 365 47 Qual é a quantidade de produtos fabricados na Alemanha? Para nós conseguirmos saber a quantidade de produtos fabricados na Alemanha, precisamos olhar para a coluna que corresponde as fábricas e contar em quantas células a palavra Alemanha se repete, basicamente é isso que a função vai fazer para nós. OBS: Vale ressaltar que essa é uma função de contagem, então vamos contar as informações em linhas, não vamos fazer a soma de cada item, vamos apenas verificar quantas vezes o critério se repete. Para facilitar a compreensão temos que aprender dois conceitos importantes, o conceito de critério e o conceito de intervalo de critério. Conceito de critério: Critério é o valor que estamos procurando, queremos que a função identifique “Alemanha” e conte quantas vezes a palavra aparece, então a palavra “Alemanha” é o nosso critério. Intervalo de critério: É onde a função vai procurar o critério, ou seja, onde a função vai procurar a palavra Alemanha, neste caso vai ser na coluna “fábricas”. =CONT.SES (intervalo_critérios1; critérios1; …) Passo a Passo: Vamos preencher o intervalo de critério com a coluna que representa as Fábricas (B:B); coloque ponto e vírgula para passar para o próximo argumento; Insira o critério “Alemanha” e aperte o enter; OBS: Os textos sempre devem ser colocados entre aspas duplas, para que o Excel entenda que se trata de um texto. Exemplo: =CONT.SES (B:B; “Alemanha”) O resultado é 6, existem 6 produtos nesta lista fabricados na Alemanha. Como seis é um valor pequeno, nós conseguimos contar facilmente apenas olhando a tabela, mas e se a tabela fosse maior? Uma tabela maior com o critério se repetindo mais de 50 ou 100 vezes deixaria essa tarefa muito difícil sem o uso de uma função. Outra vantagem do CONT.SES é poder analisar com mais de um critério, veja: Qual a quantidade de produtos fabricados na França da marca Nike? 48 Manual Curso – Excel 365 Adicionando critérios. Observe que eu tenho dois critérios, o primeiro é França que tem como intervalo de critério a coluna das fábricas (B:B). O segundo é Nike que tem como intervalo de critério a coluna das Marca (D:D). Depois que você completar o primeiro intervalo de critério e o primeiro critério coloque um ponto e vírgula e a função abre a possibilidade para colocar mais critérios! =CONT.SES (intervalo_critérios1; critérios1; intervalo_critérios2; critérios2…) Vamos substituir os argumentos da função pelos critérios e valores que precisamos analisar. =CONT.SES (B:B; “França”; D:D; “Nike”) A função vai resultar apenas a contagem dos produtos que obedecem aos dois critérios, ou seja, os produtos produzidos na França da marca Nike. O resultado é 1, temos nessa planilha apenas 1 produto produzido na França da marca Nike. Função SOMASES (): O objetivo da função SOMASES é fazer a soma dos valores de várias células seum ou mais critérios forem atendidos. Nessa função, além de informarmos o intervalo de critério e o critério, nós vamos ter que informar o intervalo de soma. Vamos somar a quantidade de produtos em estoque, produzidos no Brasil, da marca Microsoft. Qual é a quantidade em estoque de produtos fabricados no Brasil da Microsoft? Manual Curso – Excel 365 49 Na nossa tabela só tem 1 produto fabricado no Brasil e produzido pela Microsoft, diferente da função CONT.SES aqui a função SOMASES não retorna 1 como resposta, irá retornar à quantidade em estoque deste único produto, o estoque é de 1,285… Resultado. Se modificarmos uma das linhas para que mais de um produto atenda aos critérios, o resultado será a soma em estoque de todas as linhas que atenderem aos critérios. Resultado. Neste exemplo o resultado é a soma dos dois valores em estoque que atendem aos critérios (R=2087). O primeiro argumento que a função SOMASES pede é a coluna com os valores que serão somados, neste caso a coluna E (E:E representa a colunar toda). Primeiro argumento Depois disso a função segue a mesma lógica da CONT.SES pedindo para completar o intervalo do critério e critério, dando a opção de trabalhar com mais de um critério também. 50 Manual Curso – Excel 365 Fórmula completa. =SOMASES(E:E;B:B;”Brasil”;D:D;”Microsoft”) Então temos no primeiro argumento a coluna a ser somada, no segundo a coluna onde iremos pesquisar o primeiro critério que é “Brasil” seguido da coluna onde vamos encontrar o segundo critério que é a marca “Microsoft”. Lembrando as cores dentro das fórmulas ajudam a associar o que tem na função com as colunas da tabela. Assim fica mais fácil identificar qual é a seleção que foi feita. Última questão, vencimentos: Qual a quantidade em estoque que vence entre os dias 01/01/2028 e 31/12/2029? Vencimento – fórmula. =SOMASES(E:E;C:C;”>=01/01/2028”;C:C;”<=31/12/2029”) Essa questão segue a mesma linha de raciocínio da anterior, com uma atenção especial no momento de determinar os critérios. O intervalo do critério está não apenas como maior (>) que a data de 01/01/2028, está como maior ou igual (>=), dessa maneira incluímos na análise o próprio dia 01/01/2028, da mesma forma ocorre com o último critério. Então todas as datas que estão entre os dias 01/01/2028 e 31/12/2029, incluindo os dias indicados como parâmetro, terão seu estoque somado por essa função. Manual Curso – Excel 365 51 IMPORTANTE: Veja que nesse caso de comparação nós utilizamos os símbolos >= e <= com a data, só que todos eles estão entre aspas duplas. Nesse caso vamos ter que colocar dessa maneira para que o Excel entenda essa comparação. Com isso vamos ter o resultado igual 10606, que corresponde exatamente a quantidade que vence dentro desse período. Tabela Dinâmica A tabela dinâmica é um recurso valioso do Excel, possibilita fazer analises bem elaboradas de forma objetiva e rápida. Geralmente para fazer análises em planilhas temos que usar fórmulas e funções. O interessante da tabela dinâmica é nos possibilitar fazer analises sem o uso das funções. Vamos aprender esse recurso que pode te salvar na hora de fazer uma análise rápida no trabalho! Você vai conseguir otimizar seu tempo e mostrar o resultado de um jeito profissional e bonito. Vou usar uma tabela de estoque para nossos exemplos. Tabela de estoque. Essa tabela possui um registro que contém as marcas dos produtos, divisão, origem, lançamento e estoque. É um tipo de tabela comum em empresas e com ela, você consegue fazer algumas análises rápidas. Esses levantamentos ajudam na hora de tomar decisões ou para você ou para os líderes das empresas. O que podemos notar analisando esta tabela? Quando um produto está em falta; Quando o produto está parado há muito tempo em estoque; Se o produto é nacional ou exportado; Qual marca está vendendo mais, etc. 52 Manual Curso – Excel 365 Em cima dessas análises você pode decidir: Fazer uma promoção como estratégia; Observar problemas de transporte ou importação; Negociar com determinada marca uma compra maior por um preço mais atraente; Se antecipar a problemas. Como você pode ver, a quantidade de análises é enorme! É aí que está à força do Excel! Com a tabela dinâmica nós não precisamos ficar passando e repassando a tabela inteira para fazer as análises. Com alguns cliques nós resolvemos isso! Selecione uma célula da sua planilha → com a célula selecionada vá na guia inserir. Dentro da guia inserir temos a opção tabela dinâmica. Inserir tabela. Após clicar em tabela dinâmica vai aparecer uma janela. Nessa janela, além de mostrar de onde estamos pegando as informações, também aparece a opção para criar a tabela em uma Nova planilha ou na atual (planilha existente). Manual Curso – Excel 365 53 Nova planilha Nesse exemplo vou clicar em nova planilha. Ele vai criar uma planilha ao lado da original e levar os dados no formato de tabela dinâmica. Tabela dinâmica. 54 Manual Curso – Excel 365 Você vai notar que a princípio não temos nenhuma informação sendo mostrada, mas calma que vamos resolver isso de forma simples e fácil! Ao clicar dentro do espaço da tabela dinâmica como na foto acima se abre um “campo da tabela dinâmica” do lado direito. Nesse campo vão estar todas as informações da nossa tabela de estoque. Mas agora nós podemos dividir essas informações entre os diferentes campos da tabela e isso faz toda a diferença nas nossas análises. Coloquei os campos em que podemos dividir as informações coloridos na imagem abaixo: Campos da tabela dinâmica. Você pode clicar em uma das informações que correspondem as colunas da tabela de estoque e arrastar para um dos quatro campos. Vamos colocar a informação de estoque no campo de valores, por exemplo. Manual Curso – Excel 365 55 Estoque. No campo ∑ Valores vai aparecer Soma de Estoque, ou seja, ele está pegando todos os valores que tem na coluna de estoque e somando. Fazendo isso no espaço da planilha dinâmica irá aparecer o valor correspondente a soma do estoque. Soma do estoque total. Imagine que eu queira analisar o estoque a partir da origem, para isso vou clicar em origem e arrastar para o campo de Linhas. 56 Manual Curso – Excel 365 Origem e estoque Soma por país Observe que a tabela ainda te dá o total geral, mas separa por linha as origens e mostra suas somas separadamente. Você já consegue ver, por exemplo, que o total do estoque de produtos franceses é um pouco maior do que os outros. Também podemos colocar a origem no campo das colunas. Países nas colunas. A diferença agora é que as origens podem ser vistas separadas por colunas e não por linhas. Você deve organizar da forma que ficar mais visual e prático para você. Manual Curso – Excel 365 57 Percebeu que para ter essas informações você não precisou utilizar a função SOMA ou SOMASES? Viu como é bem mais fácil fazer essas análises utilizando a tabela dinâmica? Agora vamos clicar e arrastar as marcas para dentro do campo de linhas. Os campos ficarão distribuídos como na imagem abaixo. Marca, origem e estoque. E na tabela foi acrescido as marcas. Marca, origem e estoque. https://www.hashtagtreinamentos.com/formula-somases 58 Manual Curso – Excel 365 Observe que cada coluna representa uma das origens e em cada linha da tabela está sendo calculado o total do estoque considerando a marca. Mas e se eu quiser fazer uma análise por data? Para isso primeiro vamos retirar a marca e a origem dos campos. Para retirar você só precisa clicar em cima da marca, arrastar e soltar para fora do campo, faça o mesmo com a origem. Vamos colocar Lançamento que corresponde às datas dentro do campo de linhas. Fazendo essa mudança você já consegue perceber que os produtos mais recentes estão com o estoque maisbaixo. Isso pode acontecer por diversos motivos: Podem ser produtos lançados recentemente; Pode não ter sido distribuído da melhor forma, etc. Podemos também ver se temos produtos empacados nas vendas. Para fazer uma análise mais profunda clique ao lado de qualquer ano e vai se abrir uma análise por trimestre, clicando no sinal de mais (+) dentro de trimestre vai se abrir uma análise por mês. Análise por ano, trimestre e mês. Note que em 2011 temos lançamentos que ainda estão com o estoque alto! Manual Curso – Excel 365 59 Qual é o produto que está este tanto de tempo em estoque? Vamos analisar colocando os produtos dentro de linhas. Como vamos fazer esta análise por ano podemos retirar do campo de linhas o trimestre e o lançamento que está representando os meses. Feito isso vamos colocar a Marca dentro do ano. Agora se eu clicar no sinal de mais (+) dentro do ano eu consigo ver qual a marca está com maior estoque. Valor total por marca. Nesse caso é o Giorgio Armani, sabendo disso podemos decidir sobre esse produto especificamente. Mais uma dica de um recurso importante que podemos usar é o campo do filtro. Utilizando o filtro! Arraste para dentro do campo de filtro a origem. Filtro. Agora conseguimos olhar apenas as informações de estoque do Brasil, da China, ou da França. O filtro é um recurso ótimo para análises mais detalhadas, mas existe um recurso que funciona da mesma forma, só que com um toque mais impressionador. 60 Manual Curso – Excel 365 Esse recurso se chama segmentação de dados e eu não poderia deixar de mostrar aqui para vocês! Segmentação de dados. Vamos retirar a origem do campo de filtro e substituir por uma segmentação de dados. Observe que clicando na área da sua tabela dinâmica aparecem duas novas guias específicas para a tabela dinâmica. Guias para tabela dinâmica. São as guias “análise de tabela dinâmica” e “Design“. Clique na guia análise de tabela dinâmica e na opção inserir segmentação de dados. Irá aparecer um campo em que você seleciona tudo que quer que apareça na sua segmentação como na imagem abaixo. Segmentação de dados. Vou continuar marcando o campo origem para poder comparar com o filtro usado anteriormente. Marquei a origem e dei ok, agora a origem está em forma de segmentação de dados. Apareceram botões que correspondem a cada país e que podemos selecionar toda vez que quisermos mudar a análise. https://www.hashtagtreinamentos.com/linha-do-tempo-grafico-dinamico Manual Curso – Excel 365 61 Botões – segmentação de dados. Os botões fazem o mesmo papel do filtro, porem com uma apresentação mais profissional e bonita! Você pode colocar a caixa com os botões onde achar mais intuitivo e ter uma apresentação rápida e eficaz! Essa pesquisa poderia levar muito mais tempo para ser feita e não terminaria com esse visual. Resultado da segmentação de dados. Agora você já sabe que a tabela dinâmica é uma ferramenta muito importante do Excel e te permite fazer diversas análises de uma forma fácil e rápida. Então mesmo que não tenha muito conhecimento das funções, você pode criar diversas análises sem precisar fazer nenhum cálculo! 62 Manual Curso – Excel 365 Gráficos Qual é o objetivo de utilizar gráficos no Excel? Nosso objetivo trabalhando com gráficos é representar informações de uma forma mais visual, facilitando a compreensão para quem está vendo. Vamos exemplificar o uso dos gráficos através de três planilhas nessa aula. A primeira planilha é a de Comparação e ranking. Tabela de comparação e ranking. Qual a melhor forma de representar as informações dessa planilha? Quem vendeu mais? Quem vendeu menos? Existem duas formas de criar os gráficos, uma é mais simples, mas não funciona sempre e a outra mais trabalhosa para fazer, mas que sempre vai funcionar. No primeiro exemplo vou usar a forma mais simples de fazer e no segundo a mais trabalhosa. Geralmente é melhor usar a segunda forma, mas vou mostrar ambas e você pode decidir qual é a melhor para você. Primeira forma de criar o gráfico. A primeira forma de fazer é selecionando todas as informações da tabela que você quer que apareça no gráfico → vá na guia inserir → escolha o gráfico. Manual Curso – Excel 365 63 Primeira forma de criar o gráfico. Na guia inserir existem diversos tipos de gráficos, nessa aula vamos ver os mais básicos e mais usados que funcionam em todas as versões do Excel. Existem vários gráficos mais elaborados que estes, vou deixar um link com opções mais avançadas se quiser aprender ainda mais. Dito isso vamos voltar ao nosso exemplo: Após selecionar as informações e ir à guia inserir, escolha o gráfico de colunas agrupadas. Gráfico de colunas. Observe que a primeira coluna que representa as vendas do Marcelo mostra que ele vendeu aproximadamente 8 mil, observando a tabela podemos confirmar que o valor exato de vendas do Marcelo foi de 7.973. Ou seja, o gráfico não altera em nada os valores da tabela, só mostra melhor os resultados. https://www.hashtagtreinamentos.com/graficos-no-excel https://www.hashtagtreinamentos.com/graficos-no-excel 64 Manual Curso – Excel 365 Esse é o objetivo do gráfico! Deixar mais claro e mais visual! Após criar o gráfico, temos diversos elementos que podemos tratar separadamente. Elementos para formatar: Existe o título com o nome VENDAS, você pode clicar sobre ele e deletar se achar que vai melhorar o resultado. Tem o eixo x na horizontal que pode ter números ou textos, mas que geralmente fica com os textos… O eixo y, possui os valores que correspondem as vendas dos funcionários. Eixo x e y. Temos as linhas de grade: Você pode deletar as linhas de grade como foi feito na imagem acima ou você pode deixar para ajudar na identificação dos valores das colunas. O objetivo das linhas de grade é ajudar a associar os valores das colunas com o índice do eixo y. Manual Curso – Excel 365 65 Linhas de grade. Se quiser retirar as linhas de grade é simples! Clique sobre uma das linhas e todas elas ficarão selecionadas, depois é só apertar delete e todas vão sair, o mesmo vale para o título e os nomes. Você pode organizar seu gráfico como achar mais conveniente. Mas, como fazer para adicionar elementos no gráfico? Para adicionar elementos novos ou até mesmo colocar de volta algum que você retirou, também é muito simples. Observe que, quando você clica no gráfico, aparece um sinal de mais (+) no canto superior direito. Clicando no sinal de (+) vão aparecer diversas opções para inserir, pode inserir as linhas de grade novamente ou colocar rótulos de dados, por exemplo. O rótulo de dados serve para indicar direto na coluna o valor que ela representa. O valor exato e não o aproximado como quando você compara com o eixo y. Veja na imagem abaixo. 66 Manual Curso – Excel 365 Rótulos de dados. Se você já colocou o rótulo de dados, vai notar que o eixo y já não é necessário. Nesse caso pode retirar o eixo y clicando sobre os valores e apertando delete, isso vai deixar seu gráfico mais limpo. Você também pode mudar a cor das colunas → clique sobre uma das colunas → todas ficarão selecionadas → clique com o botão direito do mouse → preenchimento → escolha a cor desejada. Cores das colunas. Se quiser colorir apenas uma das colunas também é possível → clique sobre uma das colunas uma vez → todas ficarão selecionadas → clique novamente sobre ela → pronto, agora você consegue definir a cor apenas dessa coluna. Manual Curso – Excel 365 67 A segunda planilha que vamos trabalhar mostra a evolução dos valores de vendas ao longo do tempo. Evolução de valores ao longo do tempo. Lembra que te falei que existem duas formas de criar os gráficos? Então, neste exemplo vou te mostrar a segunda forma de fazer. O primeiro passoé clicar em uma célula qualquer fora da sua tabela → depois clique na guia inserir → selecione o gráfico desejado (neste caso será o gráfico de linhas) → após clicar vai aparecer um gráfico em branco. OBS: Como criamos o gráfico a partir de uma célula em branco, então o gráfico está ainda sem informações para completar os eixos. Para inserir as informações dos eixos no gráfico temos também duas opções: Primeira opção: Observe que ao clicar no gráfico aparecem duas novas guias chamadas Design do Gráfico e Formatar. Em Design do Gráfico clique em selecionar dados. Selecionar dados. 68 Manual Curso – Excel 365 A segunda opção para inserir as informações de forma manual é clicar com o botão direito do mouse em cima do gráfico, dessa forma também aparece a opção selecionar dados. Selecionar dados. Clicando em Selecionar dados vai abrir esta caixinha → Clique em Adicionar do lado esquerdo. Adicionar valores. Após clicar em adicionar vai aparecer outra caixa que pede duas informações. O nome da série, ou seja, o nome do conjunto de informações que você está adicionando ao gráfico, você pode escrever o nome da série de forma manual ou clicar na célula que corresponde a esse nome. Manual Curso – Excel 365 69 Editar série. Em valores da série você vai colocar todos os valores de vendas da sua tabela como na imagem abaixo. Agora conseguimos ver a linha do gráfico. Gráfico de vendas. Vamos repetir o procedimento para incluir as informações de devoluções da tabela no gráfico. Para isso aperte o ok → Clique em Adicionar novamente na caixinha da esquerda → complete o nome da série e os valores da série com os dados de devolução da sua tabela. 70 Manual Curso – Excel 365 Gráfico de vendas e devoluções. Observe que quando inserimos informações do lado direito completamos o eixo X e que o lado esquerdo vai definir o eixo Y. O eixo X (lado direito) está numerado de 1 a 12, vamos trocar essa informação pelas datas da tabela e deixar o gráfico mais coerente, afinal as vendas e devoluções estão sendo medidas por intervalo de tempo. Clique com o botão direito em cima do gráfico e vá novamente em selecionar dados, do lado direito clique em editar, em seguida selecione na tabela todas as datas e dê enter. Comparação de vendas ao longo do tempo. O gráfico de linhas é um ótimo aliado na hora de mostrar a evolução ao longo do tempo! Também fica muito claro os momentos em que as vendas e devoluções estavam próximas, uma clara indicação de prejuízo ou de lucro se a linha azul estiver muito acima da abóbora. O nosso último caso também é muito comum e você precisa conhecer! A tabela que vamos usar mostra as vendas de um mercado por categoria. Somando-se todos os valores das categorias temos o valor total (100%). Manual Curso – Excel 365 71 Partes de um total. Para visualizar esses resultados vamos utilizar o gráfico de pizza. Selecione a linha correspondente as categorias e ao faturamento → vá em inserir e escolha o gráfico de pizza. Gráfico de pizza. Clicando no mais (+) podemos acrescentar o rótulo de dados e não só isso, podemos inserir o nome da categoria e a porcentagem de cada fatia. Clique com o botão direito do mouse em cima do rótulo de dados que acabamos de inserir → vá em Formatar rótulos de dados. 72 Manual Curso – Excel 365 Selecione a porcentagem e o nome da categoria. Dessa forma você não precisa mais ficar olhando as legendas tentando associá-las as fatias do gráfico, pode excluir a legenda. Pode escolher se vai mostrar o valor total, a porcentagem ou mostrar ambos. Gráfico de pizza. Veja como a visualização de dados fica muito melhor e mais clara dessa forma. Fica muito melhor do que analisar linhas e colunas cheias de informações. Manual Curso – Excel 365 73 Considerações finais Ao longo deste, exploramos uma ampla gama de conceitos, atalhos e funcionalidades que o programa tem a oferecer. Aprendemos desde os fundamentos básicos, como formatação e formatação condicional, até funções mais avançadas, como CONCATENAR, PROCV e Tabela Dinâmica, uma ferramenta poderosa para resumir e analisar grandes volumes de dados. Com elas, conseguimos extrair insights valiosos e criar visualizações dinâmicas de forma simples e eficiente. O Curso de Excel 365 foi o pontapé inicial, fornecendo uma base sólida para utilizar essa poderosa ferramenta de forma eficiente. Esperamos que as habilidades e conhecimentos adquiridos durante este curso permitam a cada participante enfrentar desafios profissionais com confiança e excelência. Agradecemos a participação de todos e desejamos sucesso em suas jornadas no universo do Excel 365. Continue praticando e explorando as possibilidades deste programa incrível, pois o conhecimento adquirido aqui é apenas o começo de uma jornada de descobertas e conquistas. Este manual foi desenvolvido como material complementar para o Curso de Excel 365 em parceria com EMASP (Escola Municipal de Administração Pública de São Paulo). Espero que tenham gostado das aulas! Um abraço e até mais Instrutor: Genivaldo Gomes de Souza
Compartilhar