Funções com Excel: operações matemáticas e filtros

Classificando os dados no Excel - Apresentação

Olá! Sou o Roberto Sabino, instrutor na Alura, e vamos falar novamente de Excel!

#acessibilidade: sou um homem de pele clara, cabelo e barba escuros, com alguns fios grisalhos. Estou usando uma camiseta lisa e escura. Ao fundo, uma parede azul. À sua frente um microfone de espuma preta.

Algo bastante notável nos depoimentos dos cursos de Excel é a frase "eu já trabalhava com Excel, mas aprendi várias coisas interessantes nesse curso, uma dica nova, etc.".

Continuaremos nesse ritmo, evoluindo no Excel! Vamos trabalhar com diversas funcionalidades bastante úteis, além de fornecer várias dicas para o trabalho com essa ferramenta.

Ou seja, este curso é para você que trabalha com Excel no dia a dia e precisa aprender um pouco mais sobre o programa, querendo conhecer alguns detalhes de funções, funções condicionais, detalhes de formatação e maneiras mais rápidas de formatar uma planilha. Assim, alcançaremos:

• Aumento de produtividade
• Otimização das tarefas
• Melhoria do desempenho

A ideia é que o curso seja prático. Para isso, vamos analisar juntos um estudo de caso de um e-commerce que está começando a controlar seus produtos em uma planilha Excel. Acompanhando essa empresa, aprenderemos tudo de forma leve e tranquila.

Este curso foi planejado para que você tenha uma aprendizagem ativa, podendo aplicar os conhecimentos adquiridos no seu dia a dia. A ideia é que as informações já possam ser usadas no seu trabalho, na faculdade ou na sua casa.

Você terá acesso ao fórum, onde poderá tirar suas dúvidas e também ajudar outras pessoas estudantes a esclarecerem suas dúvidas.

Estamos com tudo pronto para iniciar o curso. Vamos para a aula?!

Classificando os dados no Excel - Classificação de dados

Iniciamos mais uma etapa de nosso processo. Isso porque já demos um passo inicial, no qual montamos as funcionalidades mais básicas de uma planilha para o nosso cliente, a Meteora E-commerce.

Com o que trabalharemos?

Temos dois produtos desse primeiro passo. O primeiro é uma lista de produtos, contendo os nomes dos produtos, categorias, preço unitário, preço com desconto, quantidade e valor total, com a qual aprendemos também a fazer algumas contas.

O segundo produto é uma segunda lista com tabela. Com ela, aprendemos o que são tabelas, seus conceitos e diferenças.

Nosso cliente optou por não utilizar tabelas, pois algumas pessoas no escritório não estavam familiarizadas com o Excel, e as referências estruturadas poderiam ser um problema.

Relembrando: o que é uma referência estruturada?

Por exemplo, na coluna E da nossa lista, temos o nosso preço com desconto. Para calcular esse valor, aprendemos a realizar a seguinte operação: o preço unitário menos o preço unitário vezes o desconto — tudo isso entre parênteses:

= D4 - (D4 * $I$4)

Nessa fórmula, temos duas referências relativas: as ocorrências de D4. Já vimos que, se arrastarmos essa função, ela se deslocará.

Já o elemento SIS4, chamamos de referência absoluta porque, se deslocarmos essa função, as colunas e as linhas não se deslocarão.

Contudo, quando utilizamos uma tabela, isso fica um pouco diferente e pode confundir quem está iniciando no Excel. Para calcular o mesmo valor de preço com desconto, usamos a seguinte fórmula:

= [@Preço Unitário]]-([@[Preço Unitário]] * SIS4)

É exatamente a mesma fórmula, mas usando referências estruturadas e, também, uma referência absoluta (SIS4).

Sabendo que temos as duas opções, o cliente optou por usar a sem tabela. Logo, daremos mais ênfase a essa opção, mas mostraremos algumas funcionalidades da opção com tabela também, para compararmos e sabermos como usar. Caso o cliente decida usar a planilha com tabela, nós conseguiremos converter rapidamente.

Além disso, criamos um gráfico dinâmico, que vamos analisar melhor mais adiante:

Classificação e ordenação de dados

Outra atualização do passo 1 em relação ao atual é que adicionamos mais produtos. Por conta disso, o cliente disse que está difícil encontrar itens na lista, pois ela não possui uma ordem específica.

Na verdade, esquecemos de ensinar ao cliente sobre a classificação de dados. O que é isso?

Podemos, por exemplo, ordenar essa lista por preço unitário, se quisermos. Existem algumas maneiras diferentes de fazer isso. Vamos começar trabalhando sem tabela.

Nesse caso, podemos clicar na coluna "Preço Unitário" e, na guia "Dados", clicar na opção "Classificar", ou seja, colocar em ordem.

Por exemplo, podemos ordenar a lista por ordem de "A a Z" (no caso dos números, isso significa do menor para o maior). Note que, agora, nossa lista está classificada em ordem crescente de preço unitário:

Mas você pode estar se perguntando: está tudo certo? Sim, está.

Vamos classificar essa coluna por ordem decrescente (de Z a A) e vamos observar o produto "Camiseta Lisa". Apesar de clicarmos apenas na coluna que queremos ordenar, todas as outras colunsa são também ordenadas em função do preço unitário.

Então, em primeiro lugar, teríamos o produto mais caro — que já não é mais "Camiseta Lisa", mas "Óculos redondo":

Portanto, podemos classificar dessa forma. Você pode dizer que, normalmente, seleciona todo o intervalo para classficar os dados, o que você também pode fazer.

Classificando intervalos de dados

Mas, há algumas coisas às quais precisamos nos atentar ao realizar essa operação. A primeira coisa é: quando selecionamos dessa maneira, estamos instruindo o Excel acerca da parte que desejamos ordenar. Se selecionarmos todo o intervalo, funcionará corretamente.

Lembram-se que nós havíamos deixado uma linha em branco no início e outra no final? Elas serão úteis agora. Não estamos deixando nenhum dado de fora.

Ao clicar em "Classificar de A a Z" tendo selecionado a coluna "Produto", os produtos serão ordenados conforme a ordem alfabética dos nomes. Essa função sempre irá ordenar a lista pela célula selecionada no momento:

Agora, apertaremos "Tab". Note como, ao fazer isso, a seleção pula para a coluna à direita. Ou seja: a cada vez que apertamos "Tab", ela anda. Se apertarmos "Shift + Tab", ela volta.

A partir daí, a coluna que estiver selecionada quando clicarmos em classificar de "A a Z", ou "Z a A", será o critério de ordenação de todo o intervalo.

Há outro ponto importante: e se quisermos, por exemplo, selecionar só duas colunas, porque desejamos ordenar somente essas duas (como a A e a B)?

Isso costuma ser um grande erro. Se você ordenar somente Produto e Tamanho, todas as outras colunas, como Categoria e Preço Unitário, ficarão fora da ordenação. Nesse caso, o Excel ainda tentará te ajudar dizendo que isso não é uma boa ideia.

Talvez ele permita que você faça isso se selecionar as colunas até o final, porque entenderá que pode fazer isso nesse intervalo.

Mas, vamos observar um detalhe. Antes, selecionamos as duas primeiras colunas a partir dos dados, e não dos títulos. Agora, vamos selecionar corretamente, a partir dos títulos. Em seguida, vamos clicar em "A a Z". Ele ordenará somente as colunas A e B, sem ordenar as outras:

O que aconteceu?

O óculos redondo custa R$ 399,90. O óculos quadrado custa R$ 349,90. Se ordenarmos somente as duas primeiras colunas, estaremos dizendo que a bermuda custa R$ 399,90. Ficou um pouco cara essa bermuda, não é?

Isso aconteceu porque tiramos os nossos registros de sintonia, e não podemos fazer isso. Então, antes de cometer algum erro, vamos pressionar "Ctrl + Z" para desfazer essa ordenação.

Se cometer um grande erro como esse, use "Ctrl + Z" e desfaça imediatamente.

O jeito mais fácil de ordenar, então, é apenas selecionando a coluna de critério e clicando na ordenação.

Mantenha os intervalos sempre contíguos, unidos, e deixe esse espaço em branco, porque ele é muito útil.

Não inclua os totais logo abaixo do final da lista. Se eles estivessem logo abaixo, seriam também incluídos na ordenação, e tudo ficaria errado. Vamos tentar?

Para isso, excluímos a linha final entre os produtos e os totais e ordenamos por preço unitário, de "A a Z". Com isso, o Excel exibe uma mensagem:

Para você fazer isto, todas as células mescladas precisam ser do mesmo tamanho.

O Excel não permitiu essa ação porque temos células mescladas, e ainda bem que as temos! Do contrário, cometeríamos um grande erro.

Se separarmos as células mescladas dos totais e tentarmos ordenar novamente, nossa planilha fica bagunçada. Por quê? Porque selecionamos todo o intervalo, sem deixar um espaço. Precisamos usar o "Ctrl + Z" novamente para desfazer.

Nossa salvação está em uma pequena linha, porque ela nos protege de cometer grandes bobagens.

Ou seja, se essa linha de salvação não existisse, não poderíamos executar a ordenação apenas clicando no item da barra de tarefas. Precisaríamos fazer uma seleção para poder excluir a linha de totais lá de baixo.

E se fosse uma tabela?

Seria muito mais fácil, porque a tabela já possui um item de classificação dentro das próprias colunas (a seta cinza para baixo no canto inferior direito de cada título de coluna).

O melhor é que, ao fazer isso, o sistema já sabe que a linha de totais é uma linha de totais, porque na tabela a linha de totais é toda diferente. E então, não enfrentaríamos problema algum.

Conclusões

Já resolvemos a questão da otimização da busca por itens dessa lista. Por exemplo, se for necessário encontrar algo por categoria, é possível ordenar os dados por categoria.

Suponhamos que queremos saber quais são os produtos com maior preço. Basta ordenar de "Z a A" pela coluna de preço unitário e encontraremos os produtos com maior preço.

Se quisermos saber quais têm menor estoque, ordenamos de A a Z pela coluna Quantidade.

Ou seja, essa é uma ferramenta que pode nos ajudar bastante! Mas, existem outras opções. Continuaremos explorando-as na sequência.

Classificando os dados no Excel - Classificação por níveis

Antes de continuar com as classificações, devemos atender a uma dúvida que o cliente levantou.

Havíamos mostrado a ele uma opção com tabela, que ele escolheu não usar. No entanto, ele nos apontou uma funcionalidade interessante presente na opção com tabela: quando se desliza os dados para baixo, ainda é possível visualizar os nomes das colunas, mesmo que já não apareçam mais os títulos:

Embora seja um recurso específico da tabela, podemos reproduzir algo semelhante na lista de produtos: o "congelamento de painéis".

Congelar Painéis

Congelar Painéis é, basicamente, fixar algumas linhas ou colunas na visualização dessa planilha enquanto rolamos pelo resto dos dados.

No menu superior do Excel, podemos clicar na guia "Exibir" e selecionar a funcionalidade "Congelar Painéis", mais à direita do menu . Temos três opções:

• "Congelar Painéis", com a qual escolhemos as linhas e colunas a fixar;
• "Congelar Linha Superior";
• "Congelar Primeira Coluna".

Se congelarmos a linha superior, ele automaticamente vai manter a linha 1 (que contém o título "Meteora") sempre visível enquanto deslizamos pelos dados.

No nosso caso, esta opção não é útil, pois temos apenas um título, então, não queremos que apenas a linha superior seja congelada. Então, vamos descongelar os painéis.

Para resolver isso, posicionamos a seleção exatamente abaixo de onde queremos congelar, ou seja, a primeira célula que vai se movimentar. Neste caso, a linha 4, onde temos o primeiro produto da lista.

Agora, clicamos em "Congelar Painéis" novamente. Quando fazemos isso, ele automaticamente fixa as linhas acima da que selecionamos: a de título e de nomes das colunas.

Em relação ao indicador de Desconto, precisamos escolher se deixaremos fixo ou não, se o posicionaremos mais para cima, ou colocaremos o congelamento de painéis mais para baixo. Por enquanto podemos deixar assim, pois visualizamos o desconto apenas ocasionalmente, e sabemos que precisamos retornar ao início da planilha se quisermos alterá-lo.

Este recurso facilita muito, inclusive, a classificação, pois conseguimos visualizar os títulos.

Mas, e se quiséssemos congelar, por exemplo, algumas colunas, podemos fazer isso? Sim.

Há um detalhe importante a ressaltar antes: se dermos "Ctrl + Z" nessa planilha, os painéis não são descongelados, pois "congelar painéis" não é uma operação usual que pode ser desfeita com esse atalho. Assim, é preferível fazer isso por meio do menu superior do Excel, clicando em "Exibir > Congelar Painéis > Descongelar Painéis".

Vamos supor que queremos congelar a primeira coluna, que contém os nomes dos Produtos.

Para isso, selecionamos a segunda coluna (de Tamanho), que é a primeira que desejamos que se movimente. Repetimos a operação de "Congelar Painéis", e agora a primeira coluna também está congelada.

Independentemente de quanto desloquemos essa planilha para a direita, essa coluna permanecerá congelada:

Isso pode ajudar na visualização, mas também pode causar confusão caso as pessoas que forem usar não saibam disso. Portanto, é importante que todas as pessoas envolvidas saibam usar esse recurso na planilha.

Com isso esclarecido, deixaremos congeladas apenas as linhas de título e rótulos de colunas (1 a 3) para facilitar a visualização.

Classificação em múltiplos níveis

Agora, vejamos a parte de classificação em múltiplos níveis.

Lembre-se que, se deixamos um espaço antes da planilha e outro depois, podemos simplesmente selecionar o rótulo e dizer que queremos ordená-lo na guia "Dados", clicando em "A a Z" ou "Z a A".

No entanto, temos também a opção "Classificar", logo à direita das ordenações. Clicando nela, uma caixa de diálogo se abre para selecionarmos os critérios de classificação. Podemos classificar os dados, por exemplo, por dois itens diferentes.

Vamos supor que queremos classificar, primeiro, por Categoria. Então, selecionamos essa coluna no primeiro campo e, no campo "Ordem", a ordenamos "de A a Z".

Após isso, podemos clicar em "Adicionar Nível" no menu superior dessa janela. Já dentro da Categoria, podemos classificar por Preço Unitário e escolher ordenar "do maior para o menor".

Por que em "Categoria" aparece a ordem de "A a Z" e, em Preço Unitário, "do maior para o menor"?

Porque a Categoria é um texto, alfanumérico, enquanto o Preço Unitário é um valor. Portanto, a classificação é de A a Z para texto e do maior para o menor para valor numérico.

Quando clicarmos em "OK" nessa janela, o Excel fará a classificação automaticamente.

Primeiro, ele realizou a classificação por categoria, pois foi o que solicitamos. Assim, ele definiu "Acessório" como a primeira categoria, pois está ordenando por ordem alfabética.

Dentro da Categoria, o Excel também ordenou por preço unitário. Ou seja, os dados pertencentes a cada categoria também são ordenados por preço, do mais caro para o mais barato dentro desse grupo de produtos:

Abaixo disso, temos a classificação dos produtos pela categoria "Calçado", ordenados dentro do grupo pelo preço unitário, do maior para o menor novamente:

Se clicarmos novamente em um dos cabeçalhos e selecionarmos "Classificar", ele manterá a classificação já feita e nos dará opções para adicionar níveis.

Além disso, podemos mover os níveis para cima ou para baixo, clicando nas setas do menu superior da janela de configuração de classificação.

Suponhamos que, antes de ordenar por preço, queríamos classificar por produto dentro das categorias. Assim, escolhemos classificar por "Produto", "de A a Z", por exemplo, e colocamos essa classificação acima de Preço Unitário.

Existem algumas classificações que podem não fazer muito sentido. Como os preços variam bastante, se classificarmos primeiro por preço, normalmente, as outras classificações não terão relevância.

No nosso caso, faz sentido começar pela Categoria, porque são grupos de produtos. Depois, podemos classificar por Tamanho. Mas, a classificação por Preço Unitário seria ideal. Também poderíamos incluir os Produtos.

Considerando sua base de dados, você pode calcular como precisa classificá-los, por ordem de relevância dessas classificações para você.

Se acharmos que escolhemos uma classificação errada, podemos excluir esse nível clicando em "Excluir Nível" no menu superior. Assim, teremos apenas o que é relevante, facilitando a visualização dos dados.

Há mais algumas funcionalidades de classificação que podemos explorar. Faremos isso no próximo vídeo!

Sobre o curso Funções com Excel: operações matemáticas e filtros

O curso Funções com Excel: operações matemáticas e filtros possui 156 minutos de vídeos, em um total de 41 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Classificando os dados no Excel
• Aplicando filtros no Excel
• Cálculos com condição
• Validação de dados
• Outras funções