Excel Avançado
Nesse ambiente serão disponibilizados para os alunos do SENAC - (Curso de Excel Avançado) os arquivos que serão utilizados durante o curso.
Para baixar os arquivos é só clicar no link correspondente a cada arquivo.
Irei reorganizar o nosso material por Capítulos para facilitar.
Capítulo 1
O Excel 365 apresenta uma grande gama de funções, contemplando as mais diversas áreas e necessidades, o que permite ao usuário aprimorar a capacidade de análise e cálculo de dados. Essas funções oferecem poderosas ferramentas, que possibilitam maior precisão em cálculos, eficiência na tomada de decisões e análises consistentes para situações específicas.
Existem várias formas de resolver um mesmo problema no Excel e chegar à mesma solução. O meio utilizado vai depender do grau de conhecimento do software, das preferências quanto à utilização de funções e comandos, da utilização otimizada de procedimentos e de outros fatores. Por esse motivo, devemos salientar que as soluções apresentadas neste livro não pretendem ser as únicas soluções possíveis para as atividades propostas. Dessa forma, dependendo do grau de conhecimento e da experiência do leitor em relação ao Excel, outros meios de solução podem ser implementados.
Estudaremos a seguir as funções mais utilizadas do Excel 2016.
Funções matemáticas
Atividade 1 – Controlando as vendas da loja Tudo em Informática
Objetivo: Utilizar as funções matemáticas do Excel 2016: SOMA, PRODUTO, SOMARPRODUTO, SOMASE e SOMASES.
Tarefas:
Abrir a pasta Tudo em informatica.xlsx.
Somar o número de itens vendidos.
Calcular o preço total de cada produto.
Somar o valor total de vendas.
Somar o valor vendido por filial.
Somar o valor vendido por filial e por mês.
A loja Tudo em Informática possui filiais em dois bairros da cidade de São Paulo: Brooklin e Vila Mariana. O gerente geral solicitou ao gerente financeiro um relatório mostrando a posição de vendas dos itens Mesa para computador, Mouse e Impressora no primeiro trimestre de 2016
O gerente financeiro foi alertado de que no relatório deveriam constar dados referentes a: número de itens vendidos, valor total das vendas, valor total das vendas por filial, valor total das vendas por mês, valor total das vendas da filial da Vila Mariana no mês de fevereiro e valor total das vendas da filial do Brooklin no mês de janeiro.
Vamos ajudar o gerente financeiro a elaborar o relatório solicitado utilizando as funções matemáticas do Excel 365.
Função SOMA
Objetivo: somar os valores numéricos contidos em uma faixa de células.
Sintaxe: SOMA(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem a valores, endereços de células ou faixa de células que desejamos somar.
1. Baixe e abra o arquivo abaixo:
👉 Tudo em Informática2. Vamos calcular o número de itens vendidos pelas filiais no primeiro trimestre de 2016. Clique na célula D19 e digite:
=SOMA(
3. Com o mouse pressionado, selecione a região D3:D16.
4. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=SOMA(D3:D16).
Nota
Você também poderá utilizar a função SOMA por meio da ferramenta ∑, que se encontra na guia Página Inicial, grupo Edição. Caso opte por utilizar essa ferramenta, não digite o sinal = no início da fórmula.
Função MULT
Objetivo: efetuar o produto entre os valores numéricos contidos nas células informadas.
Sintaxe: MULT(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem a valores, endereços de células ou faixa de células que desejamos multiplicar
1. Vamos calcular o valor total de cada item vendido pelas filiais no primeiro trimestre de 2016. Clique na célula F3 e digite:
=MULT(
2. Clique na célula D3, digite ; (ponto e vírgula) e clique na célula E3.
3. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=MULT(D3;E3)
4. Copie a fórmula para todos os itens da planilha.
Função SOMARPRODUTO
Objetivo: multiplicar os componentes das faixas de dados fornecidas e retornar a soma desses produtos.
Sintaxe: SOMARPRODUTO(faixa1;faixa2;...)
Argumentos: os argumentos faixa1, faixa2, ... correspondem às faixas de células que desejamos multiplicar e depois somar.
1. Vamos calcular o valor total das vendas efetuadas pelas filiais no primeiro trimestre de 2015. Nosso objetivo é somar os resultados da multiplicação de quantidade por preço unitário de cada produto. Clique na célula D20 e digite:
=SOMARPRODUTO(
2. Com o mouse, selecione a faixa D3:D16.
3. Digite ; (ponto e vírgula).
4. Com o mouse, selecione a faixa E3:E16.
5. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=SOMARPRODUTO(D3:D16;E3:E16)
A operação realizada corresponde à soma dos itens da coluna Valor Total.
Função SOMASE
Objetivo: efetuar a soma dos valores indicados de acordo com determinado critério ou condição.
Sintaxe: SOMASE(intervalo;critérios;intervalo_soma)
Argumentos:
intervalo: intervalo de células em que o critério será procurado;
critério: condição para definir os valores que serão somados. Esses critérios podem ser: número, expressão, referência de célula, texto ou função. Por exemplo: “<>250”, 100, “Mesa para computador”, G34. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);
intervalo_soma: intervalo de valores que serão somados. Se esse argumento for omitido, serão somadas as células especificadas no argumento intervalo (as mesmas células às quais os critérios são aplicados)
1. Vamos calcular o valor vendido pela filial da Vila Mariana. Clique na célula D21 e digite:
=SOMASE(
2. Com o mouse, selecione a faixa A3:A16.
3. Digite ; (ponto e vírgula).
4. Digite “Vila Mariana”. Digite ; (ponto e vírgula).
5. Com o mouse, selecione a faixa F3:F16.
6. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=SOMASE(A3:A16;“Vila Mariana”;F3:F16)
7. Na célula D22, calcule o valor vendido pela filial do Brooklin. Digite a fórmula:
=SOMASE(A3:A16;“Brooklin”;F3:F16)
8. Na célula D23, calcule o valor das vendas no mês de janeiro. Observe que o critério “Janeiro” deve ser procurado na faixa B3:B16.
=SOMASE(B3:B16;“Janeiro”;F3:F16)
9. Nas células D24 e D25, calcule os valores vendidos para os meses de fevereiro e março
Função SOMASES
Objetivo: efetuar a soma em um intervalo de células atendendo a vários critérios, que serão combinados.
Sintaxe: SOMASES(intervalo_soma;intervalo_critério1;critério1;intervalo_critério2;critério2;...)
Argumentos:
intervalo_soma: intervalo de valores que serão somados;
intervalo_critério1: intervalo de células em que o critério1 será procurado;
critério1: condição para definir os valores que serão somados. Esses critérios podem ser número, expressão, referência de célula, texto ou função. Por exemplo: 120, “Vila Mariana”, “<>100”, A12. O critério deverá ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);
intervalo_critério2: intervalo de células em que o critério2 será procurado;
critério2: condição para definir os valores que serão somados. As mesmas considerações feitas ao critério1 devem ser aplicadas ao critério2 e a todos os outros que possam existir.
Podemos ter até 127 pares de intervalos/critérios.
1. Vamos calcular o valor vendido pela filial da Vila Mariana no mês de fevereiro. Clique na célula D26 e digite:
=SOMASES(
2. Com o mouse, selecione a faixa F3:F16.
3. Digite ; (ponto e vírgula).
4. Com o mouse, selecione a faixa A3:A16.
5. Digite ; (ponto e vírgula).
6. Digite “Vila Mariana”.
7. Digite ; (ponto e vírgula).
8. Com o mouse, selecione a faixa B3:B16.
9. Digite ; (ponto e vírgula).
10. Digite “Fevereiro”.
11. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=SOMASES(F3:F16;A3:A16;“Vila Mariana”;B3:B16;“Fevereiro”)
12. Na célula D27, calcule o valor vendido pela filial do Brooklin no mês de janeiro.
13. Salve as modificações e feche a pasta.
Atividade 2 – Gerando números aleatórios para apostas na Mega-Sena
Objetivo: Utilizar a função matemática ALEATÓRIOENTRE do Excel 2016.
Tarefas:
Abrir a pasta Loteria.xlsx.
Gerar apostas para a Mega-Sena.
Depois de muito jogar na Mega-Sena e não ganhar, Mariana resolveu criar um procedimento para gerar números aleatórios na faixa entre 1 e 60 para serem jogados em uma aposta de 6 números. Vamos ajudá-la a utilizar uma planilha no Excel para essa finalidade.
Função ALEATÓRIOENTRE
Objetivo: gerar um número aleatório inteiro entre os valores especificados. Sempre que a planilha for calculada, um novo valor será gerado.
Sintaxe: ALEATÓRIOENTRE(valor_inferior;valor_superior)
Argumentos:
valor_inferior: argumento obrigatório que corresponde ao menor número inteiro que pode ser gerado pela função;
valor_superior: argumento obrigatório que corresponde ao maior número inteiro que pode ser gerado pela função.
1. Baixe e abra o arquivo abaixo:
As apostas para a Mega-Sena têm de conter números entre 1 e 60. Portanto, vamos utilizar a função para gerar números inteiros dentro dessa faixa.
2. Na célula A4, digite:
=ALEATÓRIOENTRE(1;60)
3. Copie a fórmula para as células B4 a F4. Os números gerados são aleatórios e sempre que a planilha for recalculada a função retornará novos valores. Boa sorte!
4. Salve as modificações e feche a pasta
Funções estatísticas.
Atividade 3 – Verificando o número que foi sorteado com maior frequência nos sorteios da Mega-Sena no período
Objetivo:
Utilizar a função estatística MODO.ÚNICO do Excel 2016.
Tarefas:
Abrir a pasta Mega-sena_2012.xlsx.
Gerar uma aposta com cinco números aleatórios.
Inserir o sexto número na aposta gerada e que corresponda ao valor que foi sorteado com maior frequência nos sorteios desde o início até o presente momento
Mariana resolveu modificar a aposta gerada e colocou como o sexto palpite o número que foi mais sorteado em todos os concursos da Mega-Sena Para isso, vamos utilizar uma planilha com os resultados desses concursos e determinar o número que apareceu com maior frequência.
Função MODO.ÚNICO.
Objetivo: retornar o valor que aparece com maior frequência em um intervalo de dados numéricos.
Sintaxe: MODO.ÚNICO(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem ao conjunto de valores numéricos entre os quais desejamos obter aquele que aparece com maior frequência.
1. Abra a pasta dos resultados da Mega-sena
2. Nas células K2, L2, M2, N2 e O2, utilize a função ALEATÓRIO-ENTRE para gerar os cinco primeiros números na aposta.
3. Na célula P2, digite:
=MODO.ÚNICO(
4. Com o mouse, selecione a faixa C2:Hxxx.
=MODO.ÚNICO(C2:Hxxx)
5. Pressione a tecla Enter.
6. Salve as alterações e feche a pasta.
Pronto! Eis ai os seus números da sorte.
Atividade 4 – Verificando o desempenho dos vendedores da loja Seu Carro É Aqui
Objetivo:
Utilizar as funções estatísticas do Excel 2016: MÁXIMO, MÍNIMO, MAIOR, MENOR, MÉDIA, ORDEM.EQ, CONT.SE, CONT.SES, CONTAR.VAZIO, CONT.NÚM, CONT.VALORES, MÉDIASE e MÉDIASES.
Tarefas:
Abrir a pasta Loja de carros.xlsx.
Calcular a maior e a menor venda entre os vendedores.
Calcular a média de vendas dos vendedores.
Calcular o total de vendedores.
Calcular o número de vendedores que não efetuaram vendas.
Calcular o número de vendedores que efetuaram vendas.
Calcular o número de vendedores premiados com o primeiro lugar em vendas.
Calcular o total de vendedores por departamento
Calcular o número de vendedores premiados com o primeiro lugar em vendas por departamento.
Calcular a média de vendas por departamento.
Calcular a média de vendas pelos vendedores não premiados por departamento.
A loja de automóveis Seu Carro É Aqui comercializa três tipos de veículos: usados, zero-quilômetro e veículos com isenções de impostos para deficientes. Para melhor atender a seus clientes, a loja está dividida em três departamentos de vendas, identificados como Usados, Zero km e Isenções. Em cada um desses departamentos, trabalha uma equipe de vendedores. Além de precisar efetuar o controle das vendas, a loja resolveu adotar uma política de prêmios para motivar os vendedores e para isso estabeleceu metas para as vendas.
Nosso objetivo é efetuar os seguintes controles:
mostrar o ranking de vendas, ou seja, calcular a colocação de cada vendedor levando em consideração a venda individual em relação ao conjunto de vendas dos vendedores;
mostrar a maior e a menor venda efetuada, considerando todos os departamentos;
calcular a média de vendas, considerando todos os departamentos;
mostrar o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o número de vendedores que efetuaram vendas e quantos ficaram em primeiro lugar em vendas;
calcular, por departamento, o número de vendedores, quantos ficaram em primeiro lugar em vendas, a média de vendas e a média de vendas dos não ganhadores do prêmio.
Para resolver essa situação, vamos trabalhar com a pasta Loja de carros.xlsx. Nessa pasta encontraremos duas planilhas: Vendas e Relatório.
Para realizarmos essa atividade, é só baixar e abrir o arquivo abaixo:
Função MÁXIMO
Objetivo: retornar o valor máximo entre um conjunto de valores numéricos informado.
Sintaxe: MÁXIMO(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos cujo valor máximo queremos determinar. Pode-se informar de 1 a 255 valores numéricos
1. Abra a pasta Loja de carros.xlsx.
2. Selecione a planilha Relatório e clique na célula B2. Nessa célula será calculado o valor máximo das vendas que estão na faixa de células C3:C22 da planilha Vendas.
3. Digite:
=MÁXIMO(
4. Selecione a planilha Vendas e, com o mouse pressionado, selecione a faixa C3:C22.
5. Pressione a tecla Enter e você terá a seguinte fórmula na célula B2:
=MÁXIMO(VENDAS!C3:C22)
Observe que, como consideramos a faixa C3:C22 da planilha Vendas e a fórmula está sendo colocada na planilha Relatório, é colocado o nome da planilha seguido pelo ponto de exclamação na identificação da região informada.
Nota
Você também poderá utilizar a função MÁXIMO por meio da ferramenta ∑, que se encontra na guia Página Inicial, grupo Edição. Caso opte por utilizar essa ferramenta, não digite o sinal = no início da fórmula.
Função MÍNIMO
Objetivo: retornar o valor mínimo entre um conjunto de valores numéricos informado.
Sintaxe: MÍNIMO(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor mínimo queremos determinar. Pode-se informar de 1 a 255 valores numéricos.
1. Selecione a planilha Relatório e clique na célula B3. Nessa célula vamos calcular o valor mínimo das vendas que estão na faixa C3:C22 da planilha Vendas.
2. Digite:
=MÍNIMO(
3. Selecione a planilha Vendas e, com o mouse pressionado, selecione a faixa C3:C22.
4. Pressione a tecla Enter e você terá a seguinte fórmula na célula B2:
=MÍNIMO(VENDAS!C3:C22)
Nota
Você também poderá utilizar a função MÍNIMO por meio da ferramenta ∑, que se encontra na guia Página Inicial, grupo Edição. Caso opte por utilizar essa ferramenta, não digite o sinal = no início da fórmula.
Função MÉDIA
Objetivo: retornar a média aritmética entre um conjunto de valores numéricos informado.
Sintaxe: MÉDIA(núm1;núm2;...)
Argumentos: os argumentos núm1, núm2, ... correspondem aos valores numéricos para os quais desejamos calcular a média aritmética. Pode-se informar de 1 a 255 valores numéricos.
1. Selecione a planilha Relatório e clique na célula B4. Nessa célula vamos calcular a média aritmética das vendas que estão na faixa C3:C22 da planilha Vendas.
2. Digite:
=MÉDIA(
3. Selecione a planilha Vendas e, com o mouse pressionado, selecione a faixa C3:C22.
4. Pressione a tecla Enter e você terá a seguinte fórmula na célula B2:
=MÉDIA(VENDAS!C3:C22)
Nota
Você também poderá utilizar a função MÍNIMO por meio da ferramenta ∑, que se encontra na guia Página Inicial, grupo Edição. Caso opte por utilizar essa ferramenta, não digite o sinal = no início da fórmula.
Função ORDEM.EQ
Objetivo: retornar a posição de um número em uma lista de números.
Sintaxe: ORDEM.EQ(número;referência;[ordem])
Argumentos:
número: número cuja posição se quer encontrar em uma lista de números;
referência: lista de números em que o número informado será posicionado;
ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionará o número considerando a lista de números classificada de forma descendente; se for qualquer valor diferente de 0, Excel posicionará o número considerando a lista de números classificada de forma ascendente.
A presença de números com a mesma posição vai interferir na ordem dos números subsequentes. Por exemplo, em uma lista de números inteiros classificados em ordem crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3.
Nosso objetivo neste momento é verificar quais vendedores ocupam o primeiro lugar em vendas.
1. Selecione a planilha Vendas e clique na célula D3.
2. Vamos utilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3 dentro da faixa de vendas C3:C22. Digite a fórmula:
=ORDEM.EQ(C3;$C$3:$C$22)
Como a fórmula será copiada para as células de baixo, é necessário que a faixa de células C3:C22 seja fixada, pois a localização da região em que o valor da venda de cada vendedor será procurada deverá se manter a mesma.
3. Pressione a tecla Enter e copie a fórmula até a célula C22
Nota
Não se preocupe com a informação de erro que aparece na célula D17 (ocasionado pelo fato da célula C17 se encontrar vazia). Adiante, neste capítulo, trataremos esse erro de forma que a mensagem não apareça novamente.
Função CONT.VALORES
Objetivo: contar o número de células não vazias em um intervalo informado. O conjunto de células pode conter qualquer tipo de informação.
Sintaxe: CONT.VALORES(intervalo1; [intervalo2]; ...)
Argumentos: intervalo1;[intervalo2];...: faixas de células que devem ser contadas.
1. Vamos contar o número total de vendedores da loja. Para isso, vamos contar quantas células contêm os nomes dos vendedores (células não vazias). Selecione a planilha Relatório e clique na célula B7.
2. Digite:
=CONT.VALORES(
3. Como os nomes dos vendedores estão na planilha Vendas, selecione a planilha e, com o mouse pressionado, selecione a faixa de células A3:A22.
4. Pressione a tecla Enter e você terá a fórmula:
=CONT.VALORES(VENDAS!A3:A22).
Função CONTAR.VAZIO
Objetivo: contar o número de células vazias em um intervalo informado. Células que contenham valor nulo não serão contadas.
Sintaxe: CONTAR.VAZIO(intervalo)
Argumento: o argumento intervalo representa a faixa de células que devem ser contadas.
1. Vamos agora contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da coluna Valor da venda que estão vazias. Selecione a planilha Relatório e clique na célula B8.
2. Digite.
=CONTAR.VAZIO(
3. Como os valores das vendas estão na planilha Vendas, selecione a planilha e, com o mouse pressionado, selecione a faixa de células C3:C22.
4. Pressione a tecla Enter e você terá a fórmula:
=CONTAR.VAZIO(VENDAS!C3:C22)
Função CONT.NÚM
Objetivo: contar o número de células que contêm números em um intervalo informado. Serão contadas as células que contenham números, datas ou números escritos entre aspas.
Sintaxe: CONT.NÚM(intervalo1; [intervalo2]; ...)
Argumento: intervalo1;[intervalo2];...: faixas de células que devem ser contadas.
1. Vamos agora contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna Valor da venda que estão preenchidas com números. Selecione a planilha Relatório e clique na célula B9.
2. Digite:
=CONT.NÚM(
3. Como os valores das vendas estão na planilha Vendas, selecione a planilha e, com o mouse pressionado, selecione a faixa de células C3:C22.
4. Pressione a tecla Enter e você terá a fórmula:
=CONT.NÚM(VENDAS!C3:C22).
Função CONT.SE
Objetivo: contar o número de ocorrências de uma determinada condição em um intervalo de células informado.
Sintaxe: CONT.SE(intervalo;critério)
Argumentos:
intervalo: intervalo de células que será considerado;
critério: condição que será procurada no intervalo de células.
1. Vamos contar quantos vendedores receberão o prêmio, ou seja, aqueles que se encontram em primeiro lugar na colocação de vendas. Selecione a planilha Relatório e clique na célula B10.
2. Digite:
=CONT.SE(
3. Como queremos verificar se o vendedor está na classificação 1, o intervalo de células considerado deve ser o da coluna Colocação da planilha Vendas. Selecione a planilha Vendas e, com o mouse pressionado, selecione o intervalo D3:D22.
4. Digite ; (ponto e vírgula).
5. Digite 1.
6. Pressione a tecla Enter e você terá a seguinte fórmula na célula:
=CONT.SE(VENDAS!D3:D22;1)
7. Vamos agora contar quantos vendedores são do departamento Usados. Para isso devemos contar o número de ocorrências da palavra usados no intervalo de células B3:B22 da planilha Vendas. Clique na célula E2 da planilha Relatório.
8. Digite a fórmula:
=CONT.SE(VENDAS!B3:B22;“USADOS”)
Como a palavra usados, que é o critério para a função, está na célula D1 da planilha Relatório, a fórmula também poderia ser escrita da seguinte forma:
=CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1)
Função CONT.SES
Objetivo: aplicar critérios a células em vários intervalos e contar o número de vezes que todos os critérios são verdadeiros.
Sintaxe: CONT.SES(intervalo1;critério1;intervalo2;critério2;...)
Argumentos:
intervalo1: intervalo de células em que será procurado o critério1;
critério1: condição que será procurada no intervalo de células intervalo1;
intervalo2: intervalo de células em que será procurado o critério2;
critério2: condição que será procurada no intervalo de células intervalo2.
Podemos ter até 127 pares de intervalos/critérios.
1. Vamos calcular quantos vendedores do departamento Usados serão ganhadores do prêmio. Veja que agora teremos que considerar duas condições: o vendedor ocupa o primeiro lugar em vendas e trabalha no departamento Usados. Selecione a planilha Relatório e clique na célula E3.
2. Digite:
=CONT.SES(
3. Vamos procurar pela palavra usados. Selecione a planilha Vendas e selecione o intervalo B3:B22. Digite ; (ponto e vírgula) e, na planilha Relatório, clique na célula D1. Digite ; (ponto e vírgula).
4. Vamos agora procurar pelo número 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha Vendas e o intervalo D3:D22. Digite ; (ponto e vírgula) e digite 1. Pressione a tecla Enter e teremos a seguinte fórmula:
=CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1)
Função MÉDIASE
Objetivo: calcular a média aritmética dos valores indicados de acordo com determinado critério ou condição.
Sintaxe: MÉDIASE(intervalo; critérios;intervalo_média)
Argumentos:
intervalo: intervalo de células em que o critério será procurado;
critério: condição para definir os valores que serão considerados para o cálculo da média;
intervalo_média: intervalo de células que será considerado para calcular a média
Vamos agora calcular a média de vendas para o departamento Usados. Selecione a célula E4 da planilha Relatório e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito para as fórmulas anteriores, para construir a expressão:
=MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22)
Função MÉDIASES...
Objetivo: calcular a média aritmética em um intervalo de células que satisfaçam vários critérios. As células de um intervalo podem ser adjacentes ou não adjacentes, atendendo a vários critérios.
Sintaxe:
MÉDIASES(intervalo_média;intervalo_critério1;critério1;intervalo_critério2;critério2;...)
Argumentos:
intervalo_média: intervalo de valores que serão considerados para o cálculo da média;
intervalo_critério1: intervalo de células em que o critério1 será procurado;
critério1: condição para definir os valores que serão considerados para o cálculo da média;
intervalo_critério2: intervalo de células em que o critério2 será procurado;
critério2: condição para definir os valores que serão considerados para o cálculo da média.
1. Vamos calcular a média aritmética das vendas dos vendedores do departamento Usados e que não são ganhadores do prêmio. Na célula E5 da planilha Relatório, digite a fórmula:
=MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;“<>1”)
Os critérios informados nessa fórmula são os seguintes:
VENDAS!C3:C22: intervalo de valores que serão considerados no cálculo da média;
VENDAS!B3:B22: intervalo em que será procurado o primeiro critério (departamento);
RELATÓRIO!D1: endereço da célula que contém o primeiro critério (Usados);
VENDAS!D3:D22: intervalo em que será procurado o segundo critério (colocação);
“<>1”: segundo critério, vendedores que não estão na posição 1.
2. Preencha, considerando o mesmo raciocínio utilizado para as informações do departamento Usados, os quadros relativos aos departamentos Zero km e Isenções da planilha Relatórios.
3. Salve as alterações na pasta.
Funções lógicas
Atividade 5 – Premiando os vendedores que ocupam a primeira posição em vendas e estabelecendo conceitos sobre seu desempenho, de acordo com a meta de vendas estipulada.
Objetivo: Utilizar a função SE.
Tarefas:
Calcular o valor do prêmio para cada um dos vendedores ganhadores.
Atribuir um conceito de acordo com o valor das vendas de cada vendedor.
Para completar o preenchimento da planilha Vendas da pasta Loja de carros.xlsx, temos ainda algumas tarefas a realizar:
distribuir o prêmio que a empresa oferece aos vendedores colocados em primeiro lugar;
atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-o com a meta a ser atingida individualmente.
Função SE
Objetivo: efetuar testes condicionais com valores e fórmulas, permitindo a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro.
Sintaxe: SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)
Argumentos:
teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;
valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro;
valor_se_falso: o que fazer se o resultado do teste_lógico for Falso.
1. Selecione a planilha Vendas da pasta Loja de carros.xlsx. Vamos verificar na célula E3 se o vendedor está em primeiro lugar na classificação e, se estiver, calcular e mostrar o seu prêmio. Se o vendedor não estiver em primeiro lugar, o valor que deve ser colocado na célula é 0. Lembre-se de que o prêmio total deve ser dividido igualmente entre os vendedores ganhadores.
O raciocínio que vamos seguir é o seguinte:
SE colocação = 1
ENTÃO dividir o valor do prêmio pelo número de ganhadores e mostrar na célula
SENÃO mostrar o valor 0 na célula
Clique na célula E3 e digite o seguinte:
= SE(D3=1;$B$1/RELATÓRIO!$B$10;0)
Observe que na célula D3 temos a classificação do vendedor, na célula B1 temos o valor do prêmio total e na célula B10 da planilha Relatório temos o número de ganhadores.
2. Copie a fórmula para todos os vendedores. Não se preocupe com o erro apontado na célula E17. Mais adiante vamos modificar as fórmulas para tratar adequadamente esse erro.
3. Vamos agora colocar uma mensagem para cada vendedor de acordo com os seguintes critérios:
Podemos observar que temos quatro condições para verificar. Isso não será possível por meio de uma única função SE. Nesse caso, teremos que aninhar funções SE para resolver o problema. Até 64 funções SE podem ser aninhadas no MS 365.
O raciocínio que devemos seguir é o seguinte:
SE colocação do vendedor = 1
ENTÃO atribuir conceito “EXCELENTE”
SENÃO SE valor das vendas maior do que a meta
ENTÃO atribuir conceito “ÓTIMO”
SENÃO SE valor das vendas igual à meta
ENTÃO atribuir conceito “BOM”
SENÃO atribuir conceito “PODE MELHORAR”
Clique na célula F3 e digite a seguinte fórmula:
=SE(D3=1;“EXCELENTE”;SE(C3>$D$1;“ÓTIMO”;SE(C3=$D$1;“BOM”;“PODE MELHORAR”)))
Você pode utilizar também a Função SES
=SES(D3=1;"Excelente";C3>$D$1;"Ótimo";C3=$D$1;"Bom";C3<$D$1;"Pode Melhorar")
4. Copie a fórmula para todos os vendedores.
5. Salve as alterações e feche a pasta Loja de carros.xlsx.
Atividade 6 – Premiando vendedores que não ocupam o primeiro lugar em vendas, mas que atingiram metas de venda estipuladas.
Objetivo: Utilizar as funções E, OU e SEERRO.
Tarefas:
Distribuir um prêmio extra entre os vendedores que não alcançaram o primeiro lugar de acordo com o valor de suas vendas.
Tratar adequadamente os erros que possam ocorrer em planilhas.
Em virtude do bom resultado que a premiação para os melhores vendedores rendeu à loja de automóveis, o gerente resolveu também premiar aqueles vendedores que não foram os primeiros colocados nas vendas, mas que se destacaram em função das metas estabelecidas. Para isso, estabeleceu os seguintes critérios:
o vendedor que obteve conceito Ótimo recebe como prêmio uma viagem de uma semana com tudo pago para Natal (RN) com direito a acompanhante;
o vendedor que teve um valor de vendas menor ou igual ao valor da meta de vendas estabelecida e maior ou igual a R$ 100.000,00 recebe como prêmio um computador.
Para poder completar nossa planilha, vamos entender a utilização das funções E, OU e SEERRO.
Função E
Objetivo: retornar o valor Verdadeiro se todos os seus argumentos forem verdadeiros.
Sintaxe: E(lógico1;lógico2;...)
Argumentos:
lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;
lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado.
Em uma função E podemos colocar até 255 argumentos, ou seja, podemos ter até 255 condições lógicas para avaliar.
Função OU
Objetivo: retornar o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro.
Sintaxe: OU(lógico1;lógico2;...)
Argumentos:
lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;
lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado.
Em uma função OU, podemos colocar até 255 argumentos, ou seja, podemos ter até 255 condições lógicas para avaliar.
As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções que executam testes lógicos como, por exemplo, em conjunto com a função SE.
1. Abra a pasta Itens.xlsx. Vamos verificar a diferença dos resultados entre as funções E e OU.
👉 Itens
2. Clique na célula C2. Aparecerá o valor VERDADEIRO se o item for Lápis e sua cor for Vermelho. Caso contrário, deverá aparecer o valor FALSO.
3. Digite a fórmula:
=E(A2=“Lápis”;B2=“Vermelho”)
4. Copie a fórmula para todos os itens.
5. Clique na célula E2. Aparecerá o valor Verdadeiro se o item for Lápis ou se sua cor for Vermelho. Caso contrário, deverá aparecer o valor Falso.
6. Digite a fórmula:
=OU(A2=“Lápis”;B2=“Vermelho”)
7. Copie a fórmula para todos os itens.
Note que no caso da função E o resultado só será VERDADEIRO se as duas condições forem verdadeiras. No caso da função OU, o resultado será VERDADEIRO se pelo menos uma das condições for verdadeira e FALSO se as duas condições forem falsas.
8. Feche a pasta Itens.xlsx e abra a pasta Loja de carros.xlsx.
9. Vamos continuar a planilha com a colocação dos prêmios extras. O raciocínio que devemos usar é o seguinte:
SE conceito = ÓTIMO
ENTÃO prêmio extra = viagem para Natal
SENÃO SE vendas <= meta E vendas >= 100000
ENTÃO prêmio extra = computador
SENÃO deixar a célula vazia.
Clique na célula G3 e digite o seguinte:
= SE(F3=“ÓTIMO”;“Viagem para Natal”;SE(E(C3<=$D$1;C3 > 100000);“Computador”;””))
Observe que se nenhuma das condições for verdadeira, o valor “” será colocado na célula, ou seja, deixaremos a célula vazia para evitar que seja mostrada a mensagem FALSO.
10. Copie a fórmula para todos os vendedores.
Função SEERRO
Objetivo: retornar um valor especificado se uma fórmula gerar um erro. Se não existir erro, a função retornará o resultado da fórmula.
Sintaxe: SEERRO(fórmula;valor_se_erro).
Argumentos:
fórmula: fórmula que desejamos verificar em relação a erros;
valor_se_erro: valor a ser retornado se a fórmula gerar um erro.
Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!
1. A fórmula na coluna Colocação está gerando um erro no caso em que a célula correspondente à venda está vazia. Os erros podem ocorrer nas planilhas e devem ser tratados de maneira adequada para que a mensagem exibida não seja a mensagem gerada pelo Excel, muitas vezes de difícil compreensão para o usuário. Além disso, erros em células poderão ocasionar mais erros em resultados de cálculos dependentes. É aconselhável, então, armazenar na célula um valor mais adequado, evitando mensagens de difícil entendimento e outros erros. Nesse caso, se existir o erro na célula, vamos deixá-la vazia, ou seja, vamos armazenar “”. Clique na célula D3 e modifique a fórmula para:
=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);“”).
Copie a fórmula para todas células. Observe que todos os erros da planilha foram solucionados.
É importante saber o valor que deve ser colocado na célula nos casos de erro para não ocasionar novos erros.
2. Feche a pasta Loja de carros.xlsx.
Funções financeiras
Atividade 7 – Calculando o valor futuro de um investimento.
Objetivo: Utilizar a função VF.
Tarefa: Calcular o valor de um investimento de acordo com uma taxa de juros estipulada e pagamentos iguais.
Fábio deseja guardar dinheiro para montar seu consultório assim que se formar em medicina, dentro de 60 meses. O banco lhe oferece uma aplicação que rende 1,5% ao mês. Ele tem disponível R$ 500,00 mensais para essa aplicação. Fábio deseja saber a quantia que terá disponível ao final do curso.
Função VF
Objetivo: retornar o valor futuro de um investimento ao final de um período, de acordo com pagamentos e taxa de juros constantes.
Sintaxe: VF(taxa;nper;pgto;[vp];[tipo])
Argumentos:
taxa: argumento obrigatório que corresponde à taxa de juros mensal;
nper: argumento obrigatório que corresponde ao número de parcelas;
pgto: argumento obrigatório que corresponde ao valor da parcela;
vp: argumento opcional que corresponde ao valor presente;
tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0.
1. Baixe e abra o arquivo abaixo
2. Para saber quanto Fábio terá no final de 60 meses pagando R$ 500,00 por mês com rendimento mensal de 1,5%, vamos utilizar a função VF. Seus argumentos estão nas seguintes células:
Taxa: célula B5
Nper: célula A5
Pgto: célula C5
Preencha essas células com seus respectivos valores.
3. Na célula D5, digite a fórmula:
=VF(B5;A5;C5)
Atividade 8 – Calculando o valor presente de um investimento...
Objetivo: Utilizar a função VP.
Tarefa: Calcular o valor à vista de um bem comprado em parcelas a uma taxa de juros mensal fixa.
Resolvido o problema do investimento para o consultório, Fábio compra uma nova máquina de lavar roupas para o seu apartamento.
Para isso, fez um financiamento na loja por 24 meses, a uma taxa mensal de juros de 3,5%, pagando R$ 78,00 por mês. Após a compra, sua noiva questiona quanto custaria a máquina se fosse comprada à vista.
Função VP.
Objetivo: retornar o valor presente de um investimento ao final de um período, de acordo com pagamentos e taxa de juros constantes.
Sintaxe: VP(taxa;nper;pgto;[vf];[tipo])
Argumentos:
taxa: argumento obrigatório que corresponde à taxa de juros mensal;
nper: argumento obrigatório que corresponde ao número de parcelas;
pgto: argumento obrigatório que corresponde ao valor da parcela;
vf: argumento opcional que corresponde ao valor futuro;
tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0
1. Insira os seguintes valores na planilha de Fábio:
Célula A9: 24
Célula B9: 3,5%
Célula C9: 78,00.
2. Na célula D9, digite a fórmula:
=VP(B9;A9;C9)
Atividade 9 – Calculando o valor da parcela a ser paga para um investimento
Objetivo: Utilizar a função PGTO.
Tarefa: Calcular o valor da parcela fixa de um financiamento a uma taxa de juros constante.
Após o lançamento de um novo modelo de determinado automóvel, a fábrica resolve oferecer aos consumidores o modelo anterior desse automóvel financiado a uma taxa de juros de 0,5% ao mês. Como são automóveis zero-quilômetro, Fábio resolve verificar a oferta. O carro pelo qual se interessa custa R$ 35.000,00 e o financiamento pode ser feito em até 24 meses. Para saber se a parcela caberá em seu orçamento, Fábio utiliza a função PGTO em sua planilha.
Função PGTO
Objetivo: retornar o valor da parcela de um investimento de acordo com pagamentos e taxa de juros constantes.
Sintaxe: PGTO(taxa;nper;vp;[vf];[tipo])
Argumentos:
taxa: argumento obrigatório que corresponde à taxa de juros mensal;
nper: argumento obrigatório que corresponde ao número de parcelas;
vp: argumento obrigatório que corresponde ao valor presente ou principal;
vf: argumento opcional que corresponde ao valor futuro;
tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0
1. Insira os seguintes valores na planilha de Fábio:
Célula A13: 24
Célula B13: 0,5%
Célula D13: 35.000,00.
2. Na célula C13, digite a fórmula:
=PGTO(B13;A13;D13)
Atividade 10 – Calculando o número de parcelas de um investimento a uma taxa de juros e pagamentos constantes.
Objetivo: Utilizar a função NPER.
Tarefa: Calcular o número de parcelas de um investimento considerando que a taxa de juros e o valor das parcelas são constantes.
Fábio pensa bem e acha melhor continuar utilizando sua bicicleta para ir ao trabalho e, em vez de comprar o carro zero-quilômetro, investir a quantia mensal que iria despender nessa compra em uma aplicação que lhe renderia ao final de um período o valor total de R$ 35.000,00 a uma taxa de juros de 2,4% ao mês.
A sua pergunta é: por quantos meses teria que investir R$ 1.551,22 para obter os R$ 35.000,00 desejados?
Função NPER
Objetivo: retornar o número de parcelas para um investimento de acordo com pagamentos e taxa de juros constantes.
Sintaxe: NPER(taxa;pgto;vp;[vf];[tipo])
Argumentos:
taxa: argumento obrigatório que corresponde à taxa de juros mensal;
pgto: argumento obrigatório que corresponde ao valor da parcela;
vp: argumento obrigatório que corresponde ao valor presente ou principal;
vf: argumento opcional que corresponde ao valor futuro;
tipo: argumento opcional que pode ser 0 (pagamento no final do período) ou 1 (pagamento no início do período). Se for omitido, será considerado 0.
1. Insira os seguintes valores na planilha de Fábio:
Célula B17: 2,4%
Célula C17: 1.551,22
Célula D17: 35.000,00.
2. Na célula A17, digite a fórmula:
=NPER(B17;C17;D17)
3. Feche a pasta, salvando seus dados.
Visualização de datas
Atividade 11 – Formatando datas
Objetivo: Formatar datas.
Tarefa: Formatar datas para que mostrem o dia da semana, o mês e o formato dia, mês e ano.
Para o manuseio de datas e horas em nossas planilhas, o Excel oferece uma grande variedade de funções e procedimentos de formatação que possibilitam maior flexibilidade no cálculo de dados desse tipo
Formatação de datas
Muitas vezes é necessário visualizar a data de uma forma diferente como, por exemplo, pelo nome do mês, pelo nome do dia da semana ou mesmo pela data em seu formato completo. Para isso, vamos utilizar os recursos de formatação disponíveis para datas.
Victor trabalha em uma empresa que tem as datas de aniversário de seus clientes cadastradas em uma planilha do Excel no formato dd/mm/aaaa. A empresa solicitou que as datas fossem visualizadas de formas específicas: o dia do mês, o dia da semana, o nome do mês e a data em seu formato completo. Para a realização dessa tarefa, vamos ajudar Victor utilizando procedimentos de formatação personalizada.
1. Baixe e abra o arquivo abaixo
2. Na coluna B, vamos mostrar o número do dia da data armazenada. Selecione o intervalo B2:B15.
3. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
4. Na guia Número, selecione a categoria Personalizado.
5. Apague o que está no campo Tipo e digite dd.
Devemos informar nesse campo o formato desejado para o(s) dado(s) selecionado(s). Na formatação de datas, quando queremos mostrar o número do dia, devemos digitar uma das opções que se seguem:
D: dias formados por um algarismo são exibidos com um algarismo.
DD: dias formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.
DDD: exibe o nome do dia de forma abreviada, usando as três primeiras letras.
DDDD: exibe o nome do dia por extenso.
6. Clique em OK
7. Agora vamos mostrar o nome do dia da semana na coluna C. Para isso, selecione o intervalo C2:C15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
8. Na guia Número, selecione a categoria Personalizado.
9. Apague o que está no campo Tipo e digite dddd. Clique em OK.
10. Vamos agora exibir na coluna D o nome do mês por extenso. Selecione o intervalo D2:D15.
11. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
12. Na guia Número, selecione a categoria Personalizado.
13. Apague o que está no campo Tipo e digite mmmm.
A formatação personalizada para datas em relação à exibição do mês pode ser feita por uma das opções:
M: meses que são formados por um algarismo são exibidos com um algarismo.
MM: meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.
MMM: exibe o nome do mês de forma abreviada usando as três primeiras letras.
MMMM: exibe o nome do mês por extenso.
14. Clique em OK.
15. Vamos agora mostrar a data por extenso. Por exemplo: para a data 21-06-2011, deverá ser mostrado terça-feira, 21 de junho de 2011. Selecione o intervalo E2:E15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
No campo Tipo, digite o seguinte:
dddd”, “dd” de “mmmm” de “aaa
Em que:
dddd: nome do dia da semana.
, ”: coloca um espaço, vírgula, espaço.
dd: número do dia.
“ de ”: coloca espaço, a palavra “de”, espaço.
mmmm: nome do mês por extenso.
aaa: número do ano.
No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano, enquanto as representações aaa ou aaaa formatam a data para que apareçam os quatro dígitos do ano.
16. Clique em OK
Nota
Existe uma grande diferença entre conteúdo e formato. Conteúdo é o valor original do dado armazenado na célula. Formato é como ele está sendo mostrado. Quando efetuarmos cálculos com células formatadas, o conteúdo será considerado, não o formato.
17. Clique na célula C2. Observe que apesar de ser mostrado o nome do dia da semana (sexta-feira), o valor armazenado na célula é 14/01/2011.
18. Feche a pasta salvando seus dados.
Atividade 12 – Funções de data e hora
Objetivos:
Utilizar funções de datas e horas.
Efetuar cálculos com datas e horas.
Tarefas:
A empresa Serviços de Limpeza Ltda. trabalha com um estoque de vassouras que é atualizado diariamente. Em todos os dias úteis entram novas unidades de vassouras e saem outras para serem utilizadas pelos funcionários da empresa em seus serviços terceirizados. Desse modo, a empresa consegue calcular o seu estoque todos os dias. A empresa trabalha de segunda a sexta-feira e todo início de mês já deixa preparada sua planilha de controle com todos os dias úteis daquele mês para poder inserir os dados e calcular seu estoque diário.
A planilha que vamos desenvolver é referente ao mês de maio de 2013. Atente para o fato de que os dias 1º e 30 desse mês serão feriados e no dia 31 não haverá expediente. No início da planilha serão inseridas informações sobre a data atual.
Para resolver essa situação iremos precisar do arquivo abaixo
Função HOJE
Objetivo: retornar a data de hoje formatada como data.
Sintaxe: HOJE()
Essa função não possui argumentos. Apesar disso, devemos digitar os parênteses.
Função DIA
Objetivo: retornar o dia da data informada como um número inteiro no intervalo de 1 a 31
Sintaxe: DIA(data_informada)
Argumento:
data_informada: data que se deseja retornar o dia.
Função MÊS
Objetivo: retornar o número do mês da data informada como um número inteiro no intervalo de 1 a 12.
Sintaxe: MÊS(data_informada)
Argumento:
data_informada: data que se deseja retornar o mês.
Função ANO
Objetivo: retornar o ano da data informada como um número inteiro no intervalo de 1900 a 9999.
Sintaxe: ANO(data_informada)
Argumento:
data_informada: data que se deseja retornar o ano.
Função DIA.DA.SEMANA
Objetivo: retornar o número do dia da semana da data informada como um número inteiro, variando de 1 (domingo) a 7 (sábado), por padrão.
Sintaxe: DIA.DA.SEMANA(data_informada;tipo).
Argumentos:data_informada: data que se deseja retornar o número do dia da semana;
tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores:
Função DIATRABALHO.INTL
Objetivo: retornar os dias úteis a projetar a partir de uma data informada, considerando os dias da semana que compõem o fim de semana e os feriados que possam ocorrer no período.
Sintaxe: DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados)
Argumentos:
data_inicial: data a partir da qual se deseja projetar dias úteis;
dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor 0 gera a data inicial;
fim_de_semana: argumento opcional. Indica os dias da semana que serão considerados como dias que compõem o fim de semana. Pode ter os seguintes valores:
feriados: argumento opcional composto pelo conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis.
1. Na pasta Vassouras.xlsx existem duas planilhas: Estoque e Feriados. Na planilha Estoque vamos projetar todos os dias úteis do mês de abril de 2025 e calcular o estoque diário de vassouras. Na planilha Feriados vamos inserir os dias do mês de abril de 2025 que não deverão ser considerados como dias úteis.
2. Clique na célula B8 e digite a data 01/04/2025, que corresponde ao primeiro dia do mês de abril.
3. Formate a célula B8 para que apareça somente o nome do mês.
4. Vamos agora projetar o primeiro dia útil de maio a partir do último dia do mês de abril. Essa data é o conteúdo da célula B8 – 1. Clique na célula A11 e digite:
=DIATRABALHO.INTL(B8-1;
5. Como desejamos obter o primeiro dia útil a partir dessa data, digite 1; (ponto e vírgula).
6. O nosso fim de semana é composto de sábado e domingo. Logo, o parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;
7. Na planilha Feriados, na coluna A, estão os feriados do mês de abril de 2025. Selecione a planilha Feriados e clique sobre a indicação da coluna A. Vamos marcar a coluna inteira para evitar refazer as fórmulas caso seja inserido algum dia não previsto. Pressione Enter e você terá a seguinte fórmula na célula
=DIATRABALHO.INTL(B8-1;1;1;FERIADOS!A:A)
8. Vamos agora calcular o próximo dia útil a partir da célula A11. Clique na célula A12 e digite a seguinte fórmula:
=DIATRABALHO.INTL(A11;1;1;FERIADOS!A:A)
9. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2025.
10. Copie somente os valores da coluna A para a coluna B e formate as datas para que apareçam os nomes dos dias da semana.
11. Calcule o estoque do primeiro dia como sendo o Estoque Anterior + Quantidade Entrada – Quantidade Saída.
12. Calcule o Estoque Anterior do segundo dia como sendo o estoque final do dia anterior
13. Copie as fórmulas para todos os dias.
14. Feche a pasta Vassouras.xlsx
A empresa ZAP S/A contrata mão de obra terceirizada para alguns serviços administrativos e de contabilidade, além de possuir alguns funcionários contratados. Para a mão de obra terceirizada, ela conta com os serviços de três empresas: Adm Mão de Obra, Contábil Total e Lucas Bros. Essas empresas trabalham por contrato, recebem por hora trabalhada e a carga horária diária varia em cada contrato. No período de 1-1-2013 a 31-7-2013 a empresa ZAP fechou vários contratos com essas empresas. Ao final de julho, precisa emitir um relatório demonstrativo dos pagamentos efetuados aos terceirizados. Para isso, deve calcular os dias trabalhados e o valor a ser pago em cada contrato. As empresas terceirizadas prestam serviços à ZAP de segunda-feira a sábado.
Função DIATRABALHOTOTAL.INTL
Objetivo: retornar o número de dias úteis entre duas datas considerando os fins de semana e feriados no período.
Sintaxe: DIATRABALHOTOTAL.INTL(data_inicial;data_final;fim_de_semana;feriados)
Argumentos:
data_inicial: data a partir da qual se deseja projetar dias úteis;
data_final: data até a qual se deseja projetar dias úteis;
fim_de_semana: argumento opcional. Indica que dias da semana compõem o final de semana. Pode ter os seguintes valores:
feriados: argumento opcional, que é um conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis
2. O valor a ser pago para cada empresa considera os dias úteis trabalhados e não os dias corridos. Vamos então calcular quantos são os dias úteis compreendidos entre a data inicial e a data final dos contratos. Para isso, utilizaremos a planilha Feriados, que se encontra nesta pasta. Atenção: como a empresa ZAP está localizada na cidade de Teresina, os feriados informados referem-se aos feriados municipais, estaduais e federais.
Na célula F6, digite:
=DIATRABALHOTOTAL.INTL(C6;D6;11;FERIADOS!A:A)
Em que:
C6: data inicial.
D6: data final.
11: fim de semana formado somente por domingo.
FERIADOS!A:A: lista de feriados no ano de 2013.
3. Copie a fórmula para todos os contratos.
4. Vamos agora calcular quanto cada contrato deve receber, sabendo que o valor pago por hora está armazenado na célula B3. Clique na célula G6 e digite a fórmula:
=F6*E6*$B$3
5. Copie a fórmula para todos os contratos.
6. Salve as alterações e feche a pasta
A lan house Nethouse & Cia. oferece acesso à internet cobrando determinado valor por hora de utilização. O cálculo é efetuado considerando o tempo exato de utilização, ou seja, todos os minutos são cobrados. No horário da manhã (das 9h às 11h59), a lan house oferece um desconto de 20% no valor da hora de utilização dos computadores. Nosso trabalho é elaborar uma planilha para controlar os valores pagos pelos usuários de um computador no dia 3-6-2013..
Função HORA
Objetivo: retornar a hora de um valor de tempo como um inteiro, variando de 0 (12:00 a.m.) a 23 (11:00 p.m.).
Sintaxe: HORA(valor_tempo)
Argumento:
valor_tempo: horário que contém a hora que desejamos encontrar.
Função MINUTO
Objetivo: retornar os minutos de um valor de tempo como um número inteiro que varia de 0 a 59.
Sintaxe: MINUTO(valor_tempo)
Argumento:
valor_tempo: horário que contém os minutos que desejamos encontrar..
2. Vamos calcular o tempo de utilização de cada usuário. Para isso, devemos subtrair a hora inicial da hora final. Clique na célula D5 e digite:
=C5-B5
3. Copie a fórmula para todos os usuários.
4. Agora vamos calcular o número de horas do tempo total. Clique na célula E5 e digite:
=HORA(D5)
5. Copie a fórmula para todos os usuários.
6. Vamos calcular o número de minutos do tempo total. Clique na célula F5 e digite:
=MINUTO(D5)
7. Copie a fórmula para todos os usuários.
8. Vamos calcular o tempo total em minutos. Clique na célula G5 e digite:
=E5*60+F5
9. Vamos agora calcular o valor do minuto. Lembre-se: se a hora inicial for menor do que 12h o usuário terá um desconto de 20% sobre o valor da hora. Clique na célula H5 e digite:
=SE(HORA(B5)<=11;($B$2*80%)/60;$B$2/60)
10. Agora devemos calcular o valor total a ser pago pelos usuários. Clique na célula I5 e digite:
=G5*H5.
Capítulo 2
Funções de pesquisa
OBJETIVO
Utilizar as funções de pesquisa PROCV, PROCH, CORRESP e ÍNDICE
Funções de pesquisa
As funções de pesquisa do Excel permitem encontrar valores em regiões determinadas de uma planilha de forma rápida e precisa. Na maioria dos casos, substituem a utilização de funções SE aninhadas
Atividade 1 – Utilizando a função PROCV
Objetivo: Utilizar a função PROCV.
Tarefas:
Abrir a pasta Techby.xlsx.
Preencher a planilha com os nomes e valores dos cursos.
Abrir a pasta Descontos.xlsx
Calcular o valor do Imposto de Renda retido na fonte e a contribuição mensal referente ao INSS para os funcionários da empresa Brinquedos & Brincadeiras.
A escola Techby oferece cursos de informática em três de suas filiais: Vila Mariana, Aclimação e Itaim Bibi. Para seu controle, mantém um cadastro com o nome do aluno e o código do curso. Para permitir uma melhor visualização dos dados, o responsável pela área administrativa da escola criou uma planilha com o código, o nome e o valor das mensalidades de cada curso. Nosso objetivo é atualizar a planilha com os dados dos alunos, preenchendo-a com o nome e o valor da mensalidade dos cursos.
Função PROCV
Objetivo: procurar por um valor na primeira coluna de um intervalo de células e retornar um valor da mesma linha em uma coluna especificada.
Sintaxe: PROCV(valor_procurado;intervalo_células;núm_coluna,[procurar_intervalo])
Argumentos:
valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode ser um valor ou uma referência;
intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira coluna de intervalo_células são os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas;
núm_coluna: número da coluna do intervalo de células que contém a informação que deve ser retornada;
procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira coluna do intervalo, se a correspondência deverá ser exata ou aproximada. Se esse argumento for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira coluna do intervalo deverão estar classificados em ordem ascendente, caso contrário, a função poderá retornar valores indevidos. Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Nesse caso, os valores da primeira coluna do intervalo não precisam estar classificados e se houver mais de um valor que coincida com o valor_procurado será considerado o primeiro encontrado.
1. Baixe e abra o arquivo abaixo
👉 Techby
2. Nessa pasta temos duas planilhas: Cadastro, que contém os nomes dos alunos, a filial em que realizaram o curso e o código do curso frequentado, e Tabela, que contém o código, o nome e o valor da mensalidade dos cursos. Vamos preencher a coluna Nome Curso, como o próprio nome indica, com o nome do curso. Para isso, vamos procurar pelo código do curso na tabela da planilha Tabela e trazer o seu nome.
Na célula D4 da planilha Cadastro, digite:
=PROCV(C4;TABELA!$A$2:$B$10;2)
Os argumentos informados são os seguintes:
C4: célula que contém o código do curso e que corresponde ao valor que será procurado na tabela da planilha Tabela.
TABELA!$A$2:$B$10: região em que será procurado o código do curso.
2: coluna que contém o valor que deve ser retornado. No caso, coluna que contém o nome do curso.
3. Copie a fórmula para todos os alunos
4. Clique na célula E4. Vamos agora preencher a coluna Valor da Mensalidade com o valor encontrado na tabela da planilha Tabela. Digite:
=PROCV(C4;TABELA!$A$2:$C$10;3)
Os argumentos são os seguintes:
C4: célula que contém o código do curso e que corresponde ao valor que será procurado na tabela da planilha Tabela.
TABELA!$A$2:$C$10: região em que será procurado o código do curso.
3: coluna que contém o valor que deve ser retornado. No caso, coluna que contém o valor da mensalidade do curso.
5. Copie a fórmula para todos os alunos.
6. Salve as alterações e feche a pasta
A empresa Brinquedos & Brincadeiras controla os descontos mensais de sua folha de pagamentos por meio de uma planilha que calcula mensalmente quanto cada funcionário pagará de Imposto de Renda e INSS. Esses descontos são calculados de acordo com o salário bruto do funcionário. Para o cálculo dos valores em 2013 iremos pesquisar e atualizar as duas tabelas (INSS e de IRPF)
8. Vamos preencher a coluna IR com o valor do Imposto de Renda a ser descontado do funcionário. As alíquotas e parcelas a deduzir estão na tabela IR, mas antes iremos atualizar os dados, para isso basta que você faça uma pesquisa na internet. Para encontrar os valores corretos, vamos procurar pelo valor do salário bruto na primeira coluna dessa tabela. Na célula C4, digite:
= PROCV(B4;$I$4:$J$8;2
9. Se parássemos a fórmula por aqui, teríamos somente a alíquota do imposto. Mas o que deve ser colocado na célula é o valor do imposto, ou seja, o salário bruto multiplicado pela alíquota. Vamos então completar a fórmula multiplicando o valor da alíquota pelo valor do salário bruto:
=PROCV(B4;$I$4:$J$8;2)*B4
10. Copie a fórmula para todos os funcionários.
11. Vamos agora procurar pela parcela a deduzir no cálculo do Imposto de Renda. Clique na célula D4 e digite a fórmula:
=PROCV(B4;$I$4:$K$8;3)
Observe que a região considerada nesse caso é I4:K8, pois o valor que desejamos que retorne é o que está na terceira coluna da tabela IR. Copie a fórmula para todos os funcionários.
12. O desconto real de Imposto de Renda corresponde ao valor calculado com a alíquota menos a parcela a deduzir. Clique na célula E4 e digite:
=C4-D4
13. Copie a fórmula para todos os funcionários.
14. Vamos agora calcular a contribuição para o INSS. Essa contribuição corresponde ao salário bruto multiplicado pela alíquota correspondente.
Nota
Os dados abaixo correspondem a dados antigos, favor considerar os dados atuais
No entanto, se o salário bruto for superior a R$ 4.159,00, o desconto será no valor de R$ 457,49, independentemente do valor do salário. Na célula F4, digite:
=SE(B4>4159;457,49;PROCV(B4;$M$4:$N$6;2)*B4)
O raciocínio utilizado nessa fórmula é o seguinte:
SE salário > R$ 4.159,00 ENTÃO
coloque na célula o valor R$ 457,49
SENÃO procure pelo valor do salário em M4:N6 e retorne o valor da coluna 2 multiplique o valor retornado pelo valor do salário
15. Copie a fórmula para todos os funcionários.
16. Feche a pasta salvando os dados.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Raquel recebeu a incumbência de preencher a planilha CLIENTES com as informações A, B, C ou D de acordo com a renda mensal dos clientes da loja onde trabalha. Os critérios estabelecidos foram os seguintes:
Vamos ajudar Raquel em sua tarefa
Função PROCH
Objetivo: procurar por um valor na primeira linha de um intervalo de células e trazer outro valor da mesma coluna em outra linha especificada.
Sintaxe: PROCH(valor_procurado;intervalo_células;núm_linha,[procurar_intervalo])
Argumentos:
valor_ procurado: valor a ser procurado na primeira linha do intervalo de células. Pode ser um valor ou uma referência;
intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um intervalo ou um nome de intervalo. Os valores na primeira linha de intervalo_células são os valores procurados por valor_ procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas;
núm_linha: número da linha do intervalo de células que contém a informação que deve ser retornada;
procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de que forma o valor será procurado na primeira linha do intervalo, se a correspondência deverá ser exata ou aproximada. Se esse valor for Verdadeiro ou omitido, a correspondência se dará de forma exata ou aproximada. Nesse caso, os valores da primeira linha do intervalo deverão estar classificados em ordem ascendente, caso contrário a função poderá retornar valores indevidos. Se o valor do argumento for Falso, a correspondência se dará de forma exata. Nesse caso, os valores da primeira linha do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com o valor_ procurado, será considerado o primeiro encontrado.
A diferença entre as funções PROCV e PROCH é que a primeira efetua a procura de forma vertical, enquanto a segunda o faz de forma horizontal. Você pode utilizar qualquer uma das duas, dependendo da disposição do intervalo de células adotada.
1. Baixe e abara o arquivo abaixo . Nessa pasta existem duas planilhas: Classificação e Clientes
👉 Classes
2. Vamos preencher a coluna Classificação de acordo com a faixa de renda mensal de cada cliente. Os valores que devem ser colocados nessa coluna estão na planilha Classificação. Selecione a planilha Clientes. Clique na célula C2 e digite a seguinte fórmula:
=PROCH(B2;CLASSIFICAÇÃO!$B$1:$E$2;2)
3. Copie a fórmula para todos os clientes.
Atividade 2 – Utilizando as funções CORRESP e ÍNDICE
Objetivo: Utilizar as funções CORRESP e ÍNDICE.
Tarefas:
Calcular os valores máximo e mínimo para renda mensal.
Identificar a posição desses valores dentro da planilha Clientes.
Identificar os nomes dos clientes que apresentam os valores máximo e mínimo para renda mensal.
Função CORRESP
Objetivo: retornar a posição em que o valor procurado encontra-se no intervalo de células indicado, que deve indicar uma linha ou uma coluna de células.
Sintaxe: CORRESP(valor_procurado;intervalo;[correspondência])
Argumentos:
valor_procurado: argumento obrigatório e que corresponde ao valor que desejamos procurar no intervalo de células e obter sua posição relativa dentro dele;
intervalo: argumento obrigatório e que corresponde ao intervalo de células pesquisadas. Esse intervalo deve ser uma linha ou uma coluna de células;
correspondência: argumento opcional que especifica como o valor será encontrado. Pode ser:
1 ou omitido: Localiza o maior valor que é menor ou igual ao valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem crescente.
0: Localiza o primeiro valor que é exatamente igual ao valor_procurado. Os dados no intervalo podem estar em qualquer ordem.
-1: Localiza o menor valor que é maior ou igual ao valor_procurado. Nesse caso, os dados no intervalo devem estar classificados em ordem decrescente.
1. Vamos verificar em que local da planilha se encontram os valores para a maior e a menor renda mensal. Selecione a planilha CLIENTES.
2. Calcule a maior e a menor renda mensal nas células F2 e F3.
3. Clique na célula G2 e digite:
=CORRESP(
4. Vamos procurar pelo valor da maior renda mensal, que está na célula F2. Continue a fórmula digitando F2;
=CORRESP(F2;
5. Vamos agora informar o intervalo de células em que será procurado o valor da renda mensal. Esse intervalo é B2:B10.
=CORRESP(F2;B2:B10;
6. Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será 0. A fórmula final é a seguinte:
=CORRESP(F2;B2:B10;0)
Observe que o resultado corresponde ao número da linha que o valor procurado ocupa na faixa de dados indicada e não na planilha.
7. Repita os mesmos procedimentos para encontrar a posição da menor renda mensal
Função ÍNDICE
Objetivo: retornar o conteúdo de uma célula pelo fornecimento de sua posição dentro do intervalo de células.
Sintaxe: ÍNDICE(intervalo;linha;[coluna])
Argumentos:
intervalo: argumento obrigatório que corresponde ao intervalo de células que contém o dado a ser retornado;
linha: argumento obrigatório que corresponde ao número da linha do intervalo em que está o dado a ser retornado;
coluna: argumento opcional que corresponde ao número da coluna do intervalo em que está o dado a ser retornado.
1. Vamos agora retornar os nomes dos clientes que possuem a maior e a menor renda mensal. Clique na célula H2 e digite:
=ÍNDICE(
2. Devemos informar o intervalo de células que contém o nome do cliente que está sendo procurado. Para tanto, digite na fórmula:
=ÍNDICE(A2:A10;
3. Agora devemos informar em que linha desse intervalo encontra-se o dado desejado. Pela função CORRESP obtivemos, anteriormente, o número da linha em que está a maior renda mensal. Portanto, o parâmetro que devemos informar na função é exatamente o resultado da função CORRESP, que se encontra na célula G2. A fórmula completa fica então:
=ÍNDICE(A2:A10;G2)
4. Utilize os mesmos procedimentos para trazer o nome do cliente que possui a menor renda mensal
<<<<<<<<<<<<***************************>>>>>>>>>>>>>
Aula do dia 22/05/2025
Classificar e filtrar.
OBJETIVOS
- Trabalhar com diversos tipos de classificações na planilha
- Habilitar a filtragem das células por meio de critérios complexos.
Classificando dados
A classificação em uma lista auxilia a análise de dados. Isso ajuda a visualização e permite uma compreensão mais rápida, o que possibilita que os dados sejam encontrados mais rapidamente. A classificação permite organizar os dados em ordem alfabética, por ordem de data e por ordem numérica. Mas é importante que a tabela possua rótulos descrevendo cada coluna.
Atividade 1 – Classificando dados
Objetivo: Aprender a classificar as informações por diferentes tipos de dados.
Tarefas:
Abrir arquivo de dados.
Classificar as informações por data, texto e número.
Salvar o arquivo.
1.Baixe e abra o arquivo abaixo:
Esse exemplo apresenta uma tabela contendo informações de uma loja que comercializa móveis e utensílios domésticos. Observe que o exemplo traz informações referentes à venda de um produto: a data da venda, o nome do vendedor, o nome do produto, o preço do produto, o setor a que pertence o produto e a matriz em que foi feita a venda.
2. Posicione o cursor em qualquer célula dentro da planilha na coluna B.
3. Na guia Dados, no grupo Classificar e Filtrar, clique em Classificar do Mais Antigo para o Mais Novo.
Esse comando fará com que a tabela seja classificada da data mais antiga para a mais recente.
4. Posicione o cursor em qualquer parte da coluna G.
5. Na guia Dados, no grupo Classificar e Filtrar, clique em Classificar de Z a A.
Nota
Pelo fato de os dados da coluna B serem do tipo data e os da coluna G serem do tipo texto, o nome do botão de comando é alterado. Isso também acontecerá quando os dados forem do tipo número.
6. Posicione o cursor em qualquer parte da coluna E. Na guia Dados, no grupo Classificar e Filtrar, clique em Classificar do Menor para o Maior.
Sua planilha terá a classificação do menor para o maior preço.
7. Posicione o cursor em qualquer parte da coluna B. Na guia Dados, no grupo Classificar e Filtrar, clique em Classificar da Mais Antiga para a Mais Nova.
Sua planilha terá a classificação por data da mais antiga para a mais recente.
8. Salve o seu arquivo.
Atividade 2 – Classificando por mais critérios
Objetivo: Classificar dados por mais de um critério.
Tarefas:
Classificar as informações por matriz e vendedor.
Salvar o arquivo.
Dentro de um mesmo exemplo você pode necessitar que a classificação seja feita por mais de uma coluna. Quando os dados forem reagrupados na segunda coluna, porém, será preciso classificá-los também.
1. Posicione o cursor em qualquer parte da tabela, na guia Dados, no grupo Classificar e Filtrar, e clique em Classificar.
Será apresentada a caixa de diálogo Classificar.
Esse comando permitirá que a classificação aconteça com base em vários critérios ao mesmo tempo.
2. Em Coluna, na opção Classificar por, selecione a opção Matriz.
3. Clique em Adicionar Nível.
Uma nova linha de critério será adicionada, permitindo uma nova classificação.
4. Em Coluna, na opção E depois por, selecione Vendedor e clique no botão OK.
Pronto. Sua planilha estará classificada por Matriz e em seguida por Vendedor.
5. Salve o seu arquivo.
Atividade 3 – Criando uma lista personalizada.
Objetivo: Criar uma nova lista de classificação.
Tarefas:
Excluir nível.
Criar e aplicar uma nova lista de classificação.
Salvar o arquivo.
Suponha que você necessite classificar seu exemplo ordenando as informações dentro da coluna Setor, mas queira indicar que a ordem deve ser esta: sala de estar, dormitórios, colchões, cozinhas e espaço infantil. Utilizando os critérios de classificação vistos até aqui não seria possível. Você poderá, entretanto, inserir uma nova lista de classificação; para tanto, basta seguir estes procedimentos:
1. Posicione o cursor em qualquer parte da tabela. Na guia Dados, no grupo Classificar e Filtrar, clique em Classificar.
Os critérios de classificação anteriormente utilizados serão apresentados, ou seja, a classificação será por Matriz e Vendedor.
2. Clique em Excluir Nível.
3. Em Coluna, na opção Classificar por, selecione Ambiente.
4. Em Coluna, na opção Ordem, selecione Lista Personalizada.
Será exibida a caixa de diálogo Listas Personalizadas.
5. Selecione a opção Nova Lista e clique em Adicionar.
6. Digite as opções pressionando Enter para separar as entradas em cada linha.
Exemplo:
Sala de Estar
Sala de Jantar
Cozinhas
Dormitórios
Colchões
Espaço Infantil
Nota
O texto das entradas deverá ser digitado exatamente como está em sua tabela.
7. Clique em OK duas vezes.
8. Salve e feche o seu arquivo.
Nota
Além das listas personalizadas, o Excel fornece listas personalizadas internas de dia da semana e mês do ano.
Atividade 4 – Classificando por fonte.
Objetivo: Criar uma lista personalizada por cor da fonte.
Tarefas:
Abrir arquivo.
Classificar por fonte.
Salvar o arquivo.
Nesse exemplo você utilizará um novo arquivo, em que as principais vendas estão destacadas em vermelho. O objetivo será classificar primeiro por esses itens e posteriormente pelos demais valores..
Baixe o arquivo abaixo:
As principais vendas de cada vendedor estão em vermelho.
2. Posicione o cursor em qualquer parte da tabela, na guia Dados, no grupo Classificar e Filtrar, e clique em Classificar.
3. Em Coluna, na opção Classificar por, selecione Preço.
4. Em Coluna, na opção Classificar em, selecione Cor da Fonte.
Ao escolher Cor da Fonte, a opção Ordem apresentará duas caixas de seleção.
5. Em Coluna, na opção Ordem, selecione a cor vermelha.
6. Clique em Adicionar Nível.
7. Insira um novo critério classificando por Preço, sendo a ordem do Maior para o Menor, e clique em OK.
8. Salve o seu arquivo.
Atividade 5 – Filtrando dados em uma tabela
Objetivo: Filtrar dados em uma tabela.
Tarefas:
Aplicar o comando AutoFiltro.
Utilizar diferentes critérios nos filtros.
Inserir dados com filtro aplicado.
Reaplicar filtro.
Limpar filtros.
Salvar arquivo.
A filtragem dos dados é uma maneira rápida de localizar um subconjunto de dados em uma tabela. Os dados filtrados exibem somente as linhas que atendem aos critérios (condições que você especifica para limitar os registros que devem ser incluídos no conjunto de resultados de uma consulta ou de um filtro) especificados e ocultam as linhas que você não deseja exibir. Após a aplicação de um filtro é possível reaplicar um novo filtro, obtendo assim novos resultados. Você pode filtrar por mais de uma coluna. Os filtros são aditivos, o que significa que cada filtro adicional baseia-se no filtro atual e ainda reduz o subconjunto de dados apresentados. Você pode limpar o filtro e exibir novamente todos os dados iniciais.
Existem três tipos de filtros: por uma lista de valores, por um formato ou por critérios. Cada um deles é exclusivo para o tipo de dado de cada tabela de coluna.
1. Com o arquivo Vendas e Vendedores.xlsx aberto, certifique-se de que a célula ativa esteja em uma coluna da sua tabela.
Nota
Caso utilize um modelo em que os seus dados não estejam no formato de tabela, você deve selecionar um intervalo de célula que contenha seus dados.
2. Na guia Dados, no grupo Classificar e Filtrar, clique em Filtro.
Nas linhas dos cabeçalhos de sua tabela serão exibidos os botões de filtro, indicando que a opção AutoFiltro está ativa.
Nota
Se a opção AutoFiltro estiver ativa, mas nenhum filtro tiver sido aplicado, ao passar o mouse sobre o botão AutoFiltro será exibida uma mensagem com o nome do cabeçalho e a frase Mostrando Tudo.
3. No cabeçalho em que estão as informações sobre o vendedor, clique no botão AutoFiltro.
Observe que é possível também aplicar classificações.
As opções rápidas para aplicar um filtro levam a três possíveis caminhos: o primeiro é marcar e desmarcar as caixas de seleção para mostrar ou ocultar os valores na coluna de dados, o segundo é a caixa Pesquisar, em que é possível inserir texto ou números que serão pesquisados, ou ainda pode-se utilizar critérios avançados para encontrar valores que atendem a condições específicas.
Inicialmente todos os vendedores são apresentados e selecionados. Caso necessite de um vendedor apenas, você poderá desmarcar todos os outros vendedores que não atendem a sua seleção, mas esse trabalho demandaria tempo em alguns casos. Veja outra maneira de fazer isso.
4. Desmarque a caixa Selecionar Tudo. Todas as caixas também serão desmarcadas.
5. Clique na respectiva caixa do vendedor Abraão Gomes e no botão OK.
Sua planilha exibirá apenas os dados correspondentes a esse vendedor; os demais dados estarão ocultos. Isso fica claro observando, nesse exemplo em particular, que alguns números de linhas que antes eram sequenciais não estão sendo exibidos.
Um pequeno funil é exibido no botão, indicando que está sendo aplicado um filtro. Ao passar o mouse, é exibida a mensagem contendo o nome do cabeçalho e o filtro que está sendo aplicado.
Mesmo com um filtro aplicado, é possível aplicar um filtro em outro cabeçalho.
6. No botão AutoFiltro da coluna Matriz, filtre os dados que correspondam a Alphaville.
Sua planilha deve apresentar uma redução mais acentuada dos itens exibidos para o mesmo vendedor, conforme a figura.
Limpando filtros
7. Para excluir a filtragem, clique no botão AutoFiltro da coluna relativa à Matriz e escolha a opção Limpar Filtro de “Matriz”
Isso fará com que as linhas ocultas sejam reexibidas.
Nota
É possível limpar todos os filtros de uma só vez. Na guia Dados, no grupo Classificar e Filtrar, clique em Limpar.
Mesmo após a aplicação de um filtro é possível inserir novas linhas e informações na tabela.
8. Posicione o cursor na última linha de sua tabela e insira a informação conforme se segue.
Data: 07/01/2023
Vendedor: Paulo Dias
Produto: Cama de Casal Solaris
Preço: R$ 800,00
Setor: Dormitórios
Matriz: Alphaville..
Aplique as formatações se necessário.
Reaplicando filtros
Mesmo após várias inserções ou alterações de dados, você poderá reaplicar o filtro anterior mantendo a exibição de um único vendedor.
9. Na guia Dados, no grupo Classificar e Filtrar, clique em Reaplicar.
Sua tabela apresentará somente os dados relativos ao filtro em vigência, ocultando qualquer inclusão que não corresponda ao filtro.
10. Você pode incluir mais um critério em um filtro existente. Na coluna Vendedor, clique no botão AutoFiltro, selecione o vendedor Paulo Dias e clique em OK.
Sua tabela será alterada e apresentará também os dados referentes a esse vendedor, incluindo a última linha inserida.
11. Limpe o filtro da coluna Vendedor.
12. Salve o seu arquivo.
Atividade 6 – Filtrando dados por meio de critérios.
Objetivo: Aprender a utilizar critérios na pesquisa com o uso dos caracteres * e ?.
Tarefas:
Aplicar filtros na caixa Pesquisar.
Limpar filtros.
Salvar o arquivo.
A caixa Pesquisar está disponível no botão AutoFiltro.
Neste exemplo, você poderá digitar os caracteres que serão filtrados.
Nota
As operações de pesquisa dessa caixa não diferenciam se as letras digitadas são maiúsculas ou minúsculas.
1. No botão AutoFiltro da coluna Vendedor, na caixa Pesquisar, digite a letra c e clique em OK.
O Excel retorna os valores que contêm a letra c em qualquer posição.
2. Limpe os filtros.
3. No botão AutoFiltro da coluna Produtos, na caixa Pesquisar, digite c* e clique em OK. Devido ao uso do asterisco (*), o Excel retorna os valores que contêm a letra c na primeira posição.
4. Limpe os filtros.
5. No botão AutoFiltro da coluna Produtos, na caixa Pesquisar, digite C?n* e clique em OK.
O Excel retornará todos os itens que na primeira posição iniciam com a letra c; o uso do ponto de interrogação (?) faz com que o Excel traga qualquer caractere na segunda posição, mas obrigatoriamente filtra apenas os itens que possuem a letra n na terceira posição e qualquer caractere a partir do asterisco (*).
6. Limpe os filtros.
7. No botão AutoFiltro da coluna Vendedor, na caixa Pesquisar, digite *o e clique em OK. O Excel retornará os valores que terminam com a vogal o.
8. Limpe os filtros.
9. Salve o arquivo.
Atividade 7 – Filtrando dados com mais de um critério
Objetivo: Filtrar dados com mais de um critério para os diferentes tipos de campo.
Tarefas:
Aplicar filtros de texto.
Aplicar filtros de número.
Aplicar filtros de data.
Limpar filtro.
Salvar o arquivo.
O uso de mais de um critério deve ser feito por meio do botão AutoFiltro.
Neste exemplo, você aprenderá a filtrar dados do tipo texto utilizando mais de um critério.
1. No botão AutoFiltro da coluna Vendedor, em Filtros de Texto, escolha a opção É Diferente de...
Na caixa de diálogo Personalizar AutoFiltro, você pode combinar mais de um critério de pesquisa.
2. Clique na seta na segunda caixa de combinação e selecione o vendedor Paulo Dias.
3. Mantenha selecionada a opção E.
A diferença entre essas duas opções é que quando indicamos a opção E, os dois critérios deverão ser respeitados; porém, quando indicamos a opção OU, bastará que apenas um critério atenda aos requisitos indicados.
4. Na linha abaixo, escolha a opção É Diferente de... e na seta da segunda caixa de combinação digite Abraão*. Essa é outra opção para o caso de você não necessitar informar o nome completo do vendedor ou escolher mais de uma opção dentro dos critérios utilizando os caracteres coringas (* e ?).
O Excel apresentará a lista excluindo os vendedores Paulo e Abraão.
5. Limpe o seu filtro.
Os dados do tipo numérico seriam outra opção para aplicar critérios aos filtros.
6. No botão AutoFiltro da coluna Preço, em Filtros de Número, escolha a opção É Maior ou Igual a...
7. Na caixa de diálogo Personalizar AutoFiltro, com a opção é maior ou igual a selecionada, digite 500 e clique em OK.
Sua tabela filtrará todos os dados com valor igual ou superior a R$ 500,00.
8. Limpe o filtro.
Outra maneira de trabalhar um filtro consiste em trabalhar um intervalo.
9. No botão AutoFiltro da coluna Preço, em Filtros de Número, escolha a opção Está Entre...
10. Na caixa de diálogo Personalizar AutoFiltro, digite 500 para a primeira linha.
11. Mantenha selecionada a opção E.
12. Na segunda linha, digite 800 e clique em OK.
Os dados exibidos filtrarão somente os resultados que atendam ao critério dentro do intervalo especificado.
13. Limpe o filtro.
Você pode filtrar os primeiros ou os últimos itens de uma tabela por valor ou pelos percentuais que representam na lista. Para isso, siga os passos:
14. No botão AutoFiltro da coluna Preço, em Filtros de Número, escolha a opção 10 primeiros....
15. Na caixa de diálogo AutoFiltro – 10 primeiros, em Mostrar, escolha Primeiros na primeira caixa de combinação.
16. Digite 5 na segunda caixa de combinação e mantenha a opção Itens, na terceira caixa de combinação.
17. Clique em OK.
O Excel apresentará na lista os cinco principais valores, porém mostrará sete linhas, pois a sexta e a sétima possuem o mesmo valor da quinta linha.
18. Limpe o filtro.
Caso necessite aplicar um filtro levando em conta um intervalo específico de datas, siga a sequência abaixo:
19. No botão AutoFiltro da coluna Data, em Filtros de Data, escolha a opção Está Entre...
A caixa de diálogo Personalizar AutoFiltro é exibida apresentando itens semelhantes aos que você viu até aqui. Contudo, existem botões que possibilitam abrir um pequeno calendário, facilitando assim a escolha das respectivas datas
Nesse exemplo, você escolherá os itens que correspondem às vendas da terceira semana de janeiro de 2016.
20. Digite ou escolha no calendário a primeira data do intervalo que corresponde a 16/01/2016.
21. Digite ou escolha no calendário a segunda data do intervalo que corresponde a 20/01/2016 e clique em OK.
O Excel trará a lista que corresponde aos itens vendidos na respectiva semana.
22. Limpe o filtro e salve o seu arquivo.
<<<<<<<<<<<<***************************>>>>>>>>>>>>>
Aula do dia 23/05/2025
Atividade 8 – Filtrando com critérios avançados.
Objetivo: Aprender a utilizar critérios na pesquisa com o uso dos caracteres * e ?.
Tarefas:
Aplicar filtros na caixa Pesquisar.
Limpar filtros.
Salvar o arquivo.
Os critérios avançados funcionam de uma maneira diferente do que foi visto com o uso do AutoFiltro.
A filtragem de dados é baseada na digitação de dados de sua planilha, que, de uma forma lógica, são indicados como critérios para essa filtragem.
Você utilizará um exemplo em que temos as vendas, os produtos e os setores do mês de janeiro de uma loja de móveis.
1. Baixe e abra o arquivo abaixo
A tabela traz a lista de produtos, o total das vendas de janeiro dos respectivos produtos e o setor a que cada produto pertence.
Duas planilhas estão disponíveis no exemplo:
A primeira, Vendas Geral Janeiro, exibe a tabela com os dados.
Na segunda, Filtros Avançados, estarão os critérios de pesquisa. Para facilitar o entendimento, parte dela já está preenchida.
Para facilitar o seu trabalho, você deve renomear o intervalo que corresponde à lista de dados.
2. Selecione o intervalo A1:C30.
3. Na caixa de nome, digite Intervalo.
4. Vá para a planilha Filtros Avançados.
Existem vários itens que serão utilizados nesse exemplo.
Nota
Para inserir os critérios, é importante que haja um rótulo correspondente ao da planilha de dados.
Filtrando com um critério e utilizando caracteres coringas
O primeiro exemplo indica que deverão ser filtrados os itens de produtos que iniciem com a letra E. Siga os seguintes passos:
5. Na célula A3, digite =“=E*” e pressione Enter. O Excel retornará =E*.
6. Crie uma nova planilha, com o nome de Filtro 1.
Mantenha-se na planilha Filtro 1 e
7. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
É exibida a caixa de diálogo Filtro avançado.
Na opção Ação, você pode filtrar os dados na planilha em que está a sua tabela ou indicar um novo local com os critérios de pesquisa na própria planilha ou em outra, como você está fazendo.
A opção Intervalo da lista refere-se à área que corresponde aos dados principais de sua planilha. Você nomeou como Intervalo.
A opção Intervalo de critérios indica onde estão os critérios para filtragem digitados. No seu exemplo, você está inserindo esses critérios na planilha Filtros Avançados.
A opção Copiar para indica onde serão apresentados os itens que correspondem à filtragem. Eles podem ser exibidos na mesma planilha onde estão os dados ou em outra planilha. Como exemplo, você vai criar uma planilha para cada filtro.
8. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
9. Em Intervalo da lista digite Intervalo (esse nome corresponde ao endereço A1:C30 da planilha Vendas Geral Janeiro).
10. Em Intervalo de critérios, selecione o intervalo A2:A3 da planilha Filtros Avançados.
11. Em Copiar para, selecione o intervalo A1 da planilha Filtro 1
12. Clique em OK. Faça ajustes necessários nas colunas para exibir os dados corretamente.
O Excel filtrará na planilha uma lista que corresponde aos produtos que iniciam pela letra E.
Filtrando com critério para um registro específico
Seguindo o raciocínio, você pode filtrar um registro específico.
1. Vá para a planilha Filtros Avançados.
2. Na célula A7, digite =“=Colchão Garibaldi Mola Pillow Unifase” e pressione Enter.
3. Crie uma nova planilha com o nome Filtro 2.
4. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
5. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
6. Em Intervalo da lista digite Intervalo.
7. Em Intervalo de critérios, selecione o intervalo A6:A7 da planilha Filtros Avançados.
8. Em Copiar para, selecione o intervalo A1 da planilha Filtro 2.
9. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibirá a filtragem com o respectivo registro.
Filtrando com mais de um critério em uma coluna
Você pode filtrar mais de um produto. Nesse caso, a filtragem dos dados deve indicar uma condição em que mais de um critério possa atender à filtragem. Como se trata de um mesmo campo, um ou outro pode ser verdadeiro. O Excel interpreta essa filtragem como lógica booleana. Por exemplo, Produto = “A” OU Produto = “B”, bastando apenas um critério ser verdadeiro para atender à expressão....
Siga os passos:
1. Vá para a planilha Filtros Avançados.
2. Na célula A11, digite =“=Colchão Garibaldi Mola Pillow Unifase” e pressione Enter.
3. Na célula A12, digite =“=Colchão Donnatelo Mola Donnel” e pressione Enter.
4. Crie uma nova planilha com o nome Filtro 3.
5. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
6. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
7. Em Intervalo da lista, digite Intervalo.
8. Em Intervalo de critérios, selecione o intervalo A10:A12 da planilha Filtros Avançados.
9. Em Copiar para, selecione o intervalo A1 da planilha Filtro 3.
10. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibirá a filtragem com o respectivo registro.
Filtrando com critério numérico
Para digitar os critérios nos números não é necessário o sinal de igual (=). Nesse exemplo, você deve filtrar os registros que possuem o valor de.venda maior que R$ 1000,00.
Siga os passos:
1. Vá para a planilha Filtros Avançados.
2. Na célula A16, digite >1000 e pressione Enter.
3. Crie uma nova planilha com o nome Filtro 4.
4. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
5. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
6. Em Intervalo da lista digite Intervalo.
7. Em Intervalo de critérios, selecione o intervalo A15:A16 da planilha Filtros Avançados.
8. Em Copiar para, selecione o intervalo A1 da planilha Filtro 4.
9. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibirá a filtragem com o respectivo registro.
Filtrando com vários conjuntos de critério para a mesma coluna.
Quando você filtrar um conjunto de registros em uma mesma coluna, a lógica booleana interpretará como verdadeiros os dois critérios. Por exemplo, Venda > 5 e Venda < 2. Neste exemplo, você utilizará o filtro para trazer os registros em que o valor de venda está entre R$ 500,00 e R$ 800,00.
Siga os passos:
1. Vá para a planilha Filtros Avançados.
2. Na célula A20, digite >=500 e pressione Enter.
3. Na célula B20, digite <=800 e pressione Enter.
4. Crie uma nova planilha com o nome Filtro 5
5. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
6. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
7. Em Intervalo da lista, digite Intervalo.
8. Em Intervalo de critérios, selecione o intervalo A19:B20 da planilha Filtros Avançados.
9. Em Copiar para, selecione o intervalo A1 da planilha Filtro 5.
10. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibe a filtragem com o respectivo registro
Filtrando um conjunto de critérios com diferentes colunas
Você pode utilizar diferentes colunas, e nesse exemplo você deve filtrar os registros que tenham o valor de venda menor que R$ 500,00 e pertençam ao setor de dormitórios.
Siga os passos:
1. Vá para a planilha Filtros Avançados.
2. Na célula A25, digite <500 e pressione Enter.
3. Na célula B25, digite =“=Dormitórios” e pressione Enter.
4. Crie uma nova planilha com o nome Filtro 6.
5. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
6. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
7. Em Intervalo da lista, digite Intervalo.
8. Em Intervalo de critérios, selecione o intervalo A24:B25 da planilha Filtros Avançados.
9. Em Copiar para, selecione o intervalo A1 da planilha Filtro 6.
10. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibirá a filtragem com o respectivo registro
Filtrando um conjunto de critérios utilizando uma fórmula
Ao trabalhar com fórmulas e funções, você deve ter o cuidado de mencionar as planilhas de onde obtém seus dados. Neste exemplo, você vai exibir os valores de venda que são maiores que a média de todos os preços.
Siga os passos:
1. Vá para a planilha Filtros Avançados.
2. Na célula A29, digite =’Vendas Geral Janeiro’!B2>MÉDIA(‘Vendas Geral Janeiro’!$B$2:$B$30) e pressione Enter. Caso tenha digitado corretamente, o Excel retornará o status Verdadeiro.
3. Crie uma nova planilha com o nome Filtro 7.
4. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado.
5. Na caixa de diálogo Filtro Avançado, em Ação, selecione a opção Copiar para outro local.
6. Em Intervalo da lista, digite Intervalo.
7. Em Intervalo de critérios, selecione o intervalo A28:A29 da planilha Filtros Avançados.
8. Em Copiar para, selecione o intervalo A1 da planilha Filtro 7.
9. Clique em OK e faça os ajustes necessários na largura das colunas.
O Excel exibirá a filtragem com o respectivo registro.
Visualização de datas
Formatando datas
Objetivo: Formatar datas.
Tarefa: Formatar datas para que mostrem o dia da semana, o mês e o formato dia, mês e ano.
Para o manuseio de datas e horas em nossas planilhas, o Excel oferece uma grande variedade de funções e procedimentos de formatação que possibilitam maior flexibilidade no cálculo de dados desse tipo
Formatação de datas
Muitas vezes é necessário visualizar a data de uma forma diferente como, por exemplo, pelo nome do mês, pelo nome do dia da semana ou mesmo pela data em seu formato completo. Para isso, vamos utilizar os recursos de formatação disponíveis para datas.
Victor trabalha em uma empresa que tem as datas de aniversário de seus clientes cadastradas em uma planilha do Excel no formato dd/mm/aaaa. A empresa solicitou que as datas fossem visualizadas de formas específicas: o dia do mês, o dia da semana, o nome do mês e a data em seu formato completo. Para a realização dessa tarefa, vamos ajudar Victor utilizando procedimentos de formatação personalizada.
1. Baixe e abra o arquivo abaixo
2. Na coluna B, vamos mostrar o número do dia da data armazenada. Selecione o intervalo B2:B15.
3. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
4. Na guia Número, selecione a categoria Personalizado.
5. Apague o que está no campo Tipo e digite dd.
Devemos informar nesse campo o formato desejado para o(s) dado(s) selecionado(s). Na formatação de datas, quando queremos mostrar o número do dia, devemos digitar uma das opções que se seguem:
D: dias formados por um algarismo são exibidos com um algarismo.
DD: dias formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.
DDD: exibe o nome do dia de forma abreviada, usando as três primeiras letras.
DDDD: exibe o nome do dia por extenso.
6. Clique em OK
7. Agora vamos mostrar o nome do dia da semana na coluna C. Para isso, selecione o intervalo C2:C15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
8. Na guia Número, selecione a categoria Personalizado.
9. Apague o que está no campo Tipo e digite dddd. Clique em OK.
10. Vamos agora exibir na coluna D o nome do mês por extenso. Selecione o intervalo D2:D15.
11. Clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
12. Na guia Número, selecione a categoria Personalizado.
13. Apague o que está no campo Tipo e digite mmmm.
A formatação personalizada para datas em relação à exibição do mês pode ser feita por uma das opções:
M: meses que são formados por um algarismo são exibidos com um algarismo.
MM: meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.
MMM: exibe o nome do mês de forma abreviada usando as três primeiras letras.
MMMM: exibe o nome do mês por extenso.
14. Clique em OK.
15. Vamos agora mostrar a data por extenso. Por exemplo: para a data 14-07-2023, deverá ser mostrado sexta-feira, 14 de junlho de 2023. Selecione o intervalo E2:E15 e clique no iniciador da caixa de diálogo do grupo Número da guia Página Inicial.
No campo Tipo, digite o seguinte:
dddd”, “dd” de “mmmm” de “aaa
Em que:
dddd: nome do dia da semana.
, ”: coloca um espaço, vírgula, espaço.
dd: número do dia.
“ de ”: coloca espaço, a palavra “de”, espaço.
mmmm: nome do mês por extenso.
aaa: número do ano.
No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano, enquanto as representações aaa ou aaaa formatam a data para que apareçam os quatro dígitos do ano.
16. Clique em OK
Nota
Existe uma grande diferença entre conteúdo e formato. Conteúdo é o valor original do dado armazenado na célula. Formato é como ele está sendo mostrado. Quando efetuarmos cálculos com células formatadas, o conteúdo será considerado, não o formato.
17. Clique na célula C2. Observe que apesar de ser mostrado o nome do dia da semana (sexta-feira), o valor armazenado na célula é 14/07/2023.
18. Feche a pasta salvando seus dados.
Funções de data e hora
Objetivos:
Utilizar funções de datas e horas.
Efetuar cálculos com datas e horas.
Tarefas:
A empresa Serviços de Limpeza Ltda. trabalha com um estoque de vassouras que é atualizado diariamente. Em todos os dias úteis entram novas unidades de vassouras e saem outras para serem utilizadas pelos funcionários da empresa em seus serviços terceirizados. Desse modo, a empresa consegue calcular o seu estoque todos os dias. A empresa trabalha de segunda a sexta-feira e todo início de mês já deixa preparada sua planilha de controle com todos os dias úteis daquele mês para poder inserir os dados e calcular seu estoque diário.
A planilha que vamos desenvolver é referente ao mês de abril de 2025. Atente-se para os feriados desse mês . No início da planilha serão inseridas informações sobre a data atual.
Para resolver essa situação iremos precisar do arquivo abaixo
Função HOJE
Objetivo: retornar a data de hoje formatada como data.
Sintaxe: HOJE()
Essa função não possui argumentos. Apesar disso, devemos digitar os parênteses.
Função DIA
Objetivo: retornar o dia da data informada como um número inteiro no intervalo de 1 a 31
Sintaxe: DIA(data_informada)
Argumento:
data_informada: data que se deseja retornar o dia.
Função MÊS
Objetivo: retornar o número do mês da data informada como um número inteiro no intervalo de 1 a 12.
Sintaxe: MÊS(data_informada)
Argumento:
data_informada: data que se deseja retornar o mês.
Função ANO
Objetivo: retornar o ano da data informada como um número inteiro no intervalo de 1900 a 9999.
Sintaxe: ANO(data_informada)
Argumento:
data_informada: data que se deseja retornar o ano.
Função DIA.DA.SEMANA
Objetivo: retornar o número do dia da semana da data informada como um número inteiro, variando de 1 (domingo) a 7 (sábado), por padrão.
Sintaxe: DIA.DA.SEMANA(data_informada;tipo).
Argumentos:data_informada: data que se deseja retornar o número do dia da semana;
tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores:
Função DIATRABALHO.INTL
Objetivo: retornar os dias úteis a projetar a partir de uma data informada, considerando os dias da semana que compõem o fim de semana e os feriados que possam ocorrer no período.
Sintaxe: DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados)
Argumentos:
data_inicial: data a partir da qual se deseja projetar dias úteis;
dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor 0 gera a data inicial;
fim_de_semana: argumento opcional. Indica os dias da semana que serão considerados como dias que compõem o fim de semana. Pode ter os seguintes valores:
feriados: argumento opcional composto pelo conjunto de uma ou mais datas que serão excluídas do calendário de dias úteis.
1. Na pasta Vassouras.xlsx existem duas planilhas: Estoque e Feriados. Na planilha Estoque vamos projetar todos os dias úteis do mês de abril de 2025 e calcular o estoque diário de vassouras. Na planilha Feriados vamos inserir os dias do mês de abril de 2025 que não deverão ser considerados como dias úteis.
2. Clique na célula B8 e digite a data 01/04/2025, que corresponde ao primeiro dia do mês de abril.
3. Formate a célula B8 para que apareça somente o nome do mês.
4. Vamos agora projetar o primeiro dia útil de maio a partir do último dia do mês de abril. Essa data é o conteúdo da célula B8 – 1. Clique na célula A11 e digite:
=DIATRABALHO.INTL(B8-1;
5. Como desejamos obter o primeiro dia útil a partir dessa data, digite 1; (ponto e vírgula).
6. O nosso fim de semana é composto de sábado e domingo. Logo, o parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;
7. Na planilha Feriados, na coluna A, estão os feriados do mês de abril de 2025. Selecione a planilha Feriados e clique sobre a indicação da coluna A. Vamos marcar a coluna inteira para evitar refazer as fórmulas caso seja inserido algum dia não previsto. Pressione Enter e você terá a seguinte fórmula na célula
=DIATRABALHO.INTL(B8-1;1;1;FERIADOS!A:A)
8. Vamos agora calcular o próximo dia útil a partir da célula A11. Clique na célula A12 e digite a seguinte fórmula:
=DIATRABALHO.INTL(A11;1;1;FERIADOS!A:A)
9. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2025.
10. Copie somente os valores da coluna A para a coluna B e formate as datas para que apareçam os nomes dos dias da semana.
11. Calcule o estoque do primeiro dia como sendo o Estoque Anterior + Quantidade Entrada – Quantidade Saída.
12. Calcule o Estoque Anterior do segundo dia como sendo o estoque final do dia anterior
13. Copie as fórmulas para todos os dias.
14. Feche a pasta Vassouras.xlsx
<<<<<<<<<<<<***************************>>>>>>>>>>>>>
Aula do dia 26/05/2025
O Hotel Samambaia Tropical efetua o controle de gastos e diárias de seus hóspedes por meio de planilhas. No mês de janeiro a planilha foi preenchida por um funcionário que não tinha muita experiência em digitação, causando a ocorrência de erros em cálculos e dados formatados incorretamente. O gerente do hotel, então, solicitou a outra funcionária que efetuasse as correções necessárias para que a planilha mostrasse os resultados adequadamente. Essa funcionária terá que efetuar os seguintes procedimentos.
Corrigir os nomes dos hóspedes para que sejam exibidos de forma correta, colocando as primeiras letras de nome e sobrenome em maiúsculas.
Eliminar os espaços em branco excedentes da identificação do tipo de acomodação, para que os valores sejam encontrados corretamente na respectiva tabela, por meio da função PROCV.
Transformar os dados referentes a gastos extras em letras maiúsculas.
1. Baixe e abra Abra o arquivo abaixo
👉 Hotel
Função PRI.MAIÚSCULA
Objetivo: colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia de texto.
Sintaxe: PRI.MAIÚSCULA(texto)
Argumento:
texto: texto que se deseja converter parcialmente em maiúsculas.
Função ARRUMAR
Objetivo: eliminar os espaços em branco à esquerda e à direita do texto.
Sintaxe: ARRUMAR(texto)
Argumento:
texto: texto do qual se deseja remover espaços à direita ou à esquerda.
Função MAIÚSCULA
Objetivo: converter o texto em caracteres maiúsculos.
Sintaxe: MAIÚSCULA(texto)
Argumento:
texto: texto que se deseja converter em maiúsculas.
Função MINÚSCULA
Objetivo: converter o texto em caracteres minúsculos.
Sintaxe: MINÚSCULA(texto)
Argumento:
texto: texto que se deseja converter em minúsculas.
Função ESQUERDA.
Objetivo: retornar os primeiros n caracteres à esquerda de uma sequência de texto.
Sintaxe: ESQUERDA(texto;[número_caracteres])
Argumentos:
texto: texto do qual se deseja extrair os caracteres à esquerda;
número_caracteres: argumento opcional que indica o número de caracteres à esquerda que se deseja extrair do texto. Se omitido, trará o primeiro caractere à esquerda.
Função DIREITA
Objetivo: retornar os últimos n caracteres à direita de uma sequência de texto.
Sintaxe: DIREITA(texto;[número_caracteres])
Argumentos:
texto: texto do qual se deseja extrair os caracteres à direita.
número_caracteres: argumento opcional que indica o número de caracteres à direita que se deseja extrair do texto. Se omitido, trará o último caractere à direita.
Função EXT.TEXTO
Objetivo: retornar os n caracteres a partir de determinada posição em uma sequência de texto.
Sintaxe: EXT.TEXTO(texto;posição;número_caracteres)
Argumentos:
texto: texto do qual se deseja extrair os caracteres.
posição: posição do primeiro caractere a ser extraído do texto.
número_caracteres: número de caracteres que devem ser extraídos a partir da posição do primeiro caractere considerado.
<<<<<<<<<<<<***************************>>>>>>>>>>>>>
Aula do dia 27/05/2025
Validação de dados
A validação de dados é utilizada para controlar o tipo de dado a ser inserido em uma célula. Isso impede que dados inválidos sejam digitados na planilha, além de fornecer instruções auxiliando o usuário a corrigir esses erros.
Atividade 1 – Adicionando regras de validação
Objetivo: Aprender a restringir a entrada de dados em uma planilha.
Tarefas:
Abrir um arquivo existente.
Restringir a entrada de dados.
Aplicando uma regra de validação de dados
Agora baixe e abra o arquivo abaixo:
Atividade 1 – Importando arquivos-texto para o Excel
Objetivo: Importar um arquivo.txt para o Excel.
Tarefas:
Abrir o arquivo Vendas.txt.
Fechar o arquivo Vendas.txt.
Importar o arquivo Vendas.txt para uma planilha do Excel
Importando um arquivo-texto
Um arquivo tipo texto (extensão .txt) contém informações não formatadas. Muitas vezes a nossa fonte de dados é um arquivo-texto que apresenta os dados dispostos como se estivessem em uma tabela, onde a primeira linha contém os títulos das colunas e as linhas restantes contêm os dados separados por um caractere delimitador. Cada linha desse arquivo-texto corresponde a um registro, e os caracteres delimitadores informam onde termina uma coluna e inicia outra
É importante ressaltar que estes arquivos-texto não podem apresentar nenhum recurso de formatação. Devem ser formados por texto puro e não podem ter extensões .doc, .docx, .pdf ou qualquer outra extensão criada por outros editores de texto. Se, porventura, forem criados em algum editor de textos que não o bloco de notas, devem ser gravados no formato texto sem formatação, gerando um arquivo com extensão .txt.
A papelaria Realeza teve um problema em seu computador principal e não pôde efetuar o controle de suas vendas em suas planilhas do Excel como de costume. Durante o período de manutenção do equipamento, Roseli, funcionária da loja e responsável pelas vendas, resolveu anotar o movimento no programa Bloco de Notas, pois era o único disponível na máquina que poderia utilizar naquele momento. Para evitar confusões, criou um arquivo e na primeira linha colocou os nomes das colunas da planilha que utiliza no Excel separados por ponto e vírgula e digitou os movimentos efetuados, um em cada linha. As informações também foram separadas por ponto e vírgula.
Quando o computador principal ficou pronto para uso, Roseli copiou o arquivo criado para sua pasta de trabalho. Agora ela necessita importar as informações digitadas para sua planilha no Excel. Vamos ajudá-la.
1. Baixe e abra o arquivo abaixo
No arquivo, temos informações sobre a data da compra, o nome do cliente, o item comprado, o valor unitário e a quantidade.
2. Feche o arquivo Vendas.txt.
3. Abra uma nova pasta de trabalho em branco no Excel.
4. Na guia Dados, no grupo Obter Dados Externos, clique em De Texto.
5. Localize o arquivo Vendas.txt e clique em Importar.
6. O processo de importação do arquivo será iniciado por meio do assistente de importação de texto.
Na janela, temos as seguintes informações:
Delimitado: mostra se as informações estão ou não separadas por algum delimitador, como vírgula, ponto e vírgula, tabulação. Nesse caso, o assistente identificou que os dados estão delimitados.
Largura fixa: no caso de os campos não serem delimitados, mostra se as colunas têm largura fixa, ou seja, se existem espaços entre os campos.
Iniciar importação na linha: permite escolher a partir de que linha a importação será efetuada. O padrão é linha 1
Iniciar importação na linha: permite escolher a partir de que linha a importação será efetuada. O padrão é linha 1.
Origem do arquivo: permite escolher o sistema operacional e o idioma. É recomendável deixar no padrão que foi colocado.
Clique em Avançar.
7. Será mostrada a seguinte janela:
Delimitadores: especifica qual é o delimitador utilizado para separar as informações. No caso, o delimitador utilizado é ponto e vírgula.
Desmarque a opção Tabulação e marque a opção Ponto e vírgula.
Considerar delimitadores consecutivos como um só: essa opção é bastante útil se existirem dois delimitadores em seguida. Se não for selecionada, o fato de existirem dois delimitadores em seguida vai gerar uma coluna em branco na planilha. Se a opção estiver ativada, os dois delimitadores serão considerados um só, evitando as colunas em branco.
Clique em Avançar.
8. A janela permite selecionar cada coluna e definir o formato dos dados.
Caso os formatos não sejam modificados, os dados serão importados no formato Geral, que converte valor numérico em número, valor de data em data e o restante em texto.
Clique em Concluir.
9. Na janela mostrada, selecione a célula A1 da planilha para que seja o local inicial da importação
10. Clique em OK para terminar.
11. A planilha gerada tem uma conexão com o arquivo-texto original e pode ser atualizada pela modificação do conteúdo desse arquivo; no caso, o arquivo Vendas.txt. Abra o arquivo Vendas.txt no bloco de notas e faça as seguintes modificações:
12. Feche o arquivo Vendas.txt, salvando as alterações.
13. Volte à pasta do Excel. Observe que as alterações efetuadas no arquivo-texto ainda não se refletem na planilha. Na guia Dados, no grupo Conexões, clique em Atualizar tudo.
14. Selecione o arquivo Vendas.txt e clique em Importar.
15. Os registros serão atualizados. Salve a pasta como Vendas.xlsx e feche-a.
16. Abra novamente a pasta Vendas.xlsx.
17. Observe o aviso de segurança Habilitar Conteúdo, que aparece acima da barra de fórmulas.
Essa mensagem informa que existem conexões externas com outros arquivos que podem não ser seguras e foram desabilitadas. Quando isso ocorrer, se você souber a origem das conexões e considerá-las seguras, clique em Habilitar Conteúdo. Somente habilitando o conteúdo o vínculo ficará disponível para atualizações.
18. Feche a pasta.
Importando dados de uma página da web.
Objetivo: Importar uma tabela de uma página da internet.
Tarefa: Importar de uma página da internet uma tabela contendo as siglas, os nomes, as capitais e as regiões dos estados brasileiros.
A internet é uma excelente fonte de consulta para nossos trabalhos, e as informações obtidas podem ser inseridas em documentos que serão impressos ou visualizados, guardadas em arquivos para posterior utilização ou utilizadas imediatamente.
Em muitos casos, necessitamos de dados importantes obtidos na internet para que sejam armazenados em planilhas ou bancos de dados.
Daniel está fazendo um projeto em Excel e necessita de uma planilha com as siglas, os nomes, as capitais e as regiões de todos os estados brasileiros. Por meio de uma pesquisa na internet, encontrou um site com todos esses dados. Para evitar a digitação da tabela em uma planilha do Excel, resolveu utilizar o recurso de importação dessa tabela diretamente para uma planilha de sua pasta de trabalho. Vamos verificar como Daniel resolveu o problema. O endereço do site pesquisado é:
https://www.todamateria.com.br/estados-do-brasil/
1. Abra uma nova pasta de trabalho em branco.
2. Clique na ferramenta Da Web do grupo Obter Dados Externos da guia Dados.
3. Acesse o endereço https://www.todamateria.com.br/estados-do-brasil/ no navegador aberto.
4. Clique na setinha ao lado da lista de códigos dos estados para selecioná-la e, a seguir, clique no botão Importar.
5. Selecione a célula A1 e clique em OK.
6. Salve a pasta com o nome Estados.xlsx e feche-a.
Nota
O endereço encontrado para a pesquisa de Daniel pode não estar mais disponível no momento em que você estiver lendo este blog. Como é uma página da internet, pode ter sido modificada, retirada da web ou estar em manutenção
TABELA DINÂMICA
OBJETIVOS
Resumir dados de planilhas utilizando relatórios de tabela dinâmica
Visualizar dados da tabela dinâmica pela utilização de gráficos dinâmicos
Tabela dinâmica
O recurso relatório de tabela dinâmica permite resumir os dados de uma planilha possibilitando que sejam analisados e explorados de forma mais eficiente. Com a tabela dinâmica, podemos rapidamente visualizar os dados filtrados, agrupados e calculados, o que nos permite analisá-los por meio de diferentes perspectivas
Atividade 1 – Criação da tabela dinâmica
Objetivo: Resumir dados de uma planilha utilizando tabela dinâmica.
Tarefas:
Abrir a pasta Controle Bancario.xlsx
Construir um relatório de tabela dinâmica filtrado por grupo e mostrando as descrições dos movimentos e seus valores.
Camila utiliza o Excel para controlar seu movimento financeiro diário. A planilha que Camila utiliza para armazenar os dados dos movimentos efetuados contém os seguintes campos:
Data: data do movimento.
Grupo: as contas são divididas em três grupos: eventual, mensal e diário.
Descrição: descrição do movimento.
Movimento: entrada ou saída.
Banco: banco onde o movimento se realiza. Camila trabalha com quatro bancos: Itaú, Bradesco, Banco do Brasil e HSBC
Para melhor visualizar o movimento financeiro, Camila tem a necessidade de agrupar e resumir os dados por data, grupo, movimento e banco. Vamos ajudá-la na tarefa de construir seus relatórios.
A pasta que contém a planilha utilizada por Camila é a Controle Bancario.xlsx.
1. Baixe e abra o arquivo abaixo
Baixe e abra o arquivo abaixo
Macros interativas
OBJETIVO
Gravar macros por meio do gravador de macros
Você sabe o que é macro? Já ouviu falar pelo menos?
Macro é uma sequência de procedimentos que serão executados com a finalidade de realizar tarefas repetitivas.
As macros podem ser criadas de duas formas:
Pelo gravador de macros: criadas a partir da gravação dos procedimentos realizados pelo usuário na planilha.
Pela linguagem VBA: criadas pela escrita do script da macro, utilizando comandos e funções da linguagem VBA (Visual Basic for Applications)
A gravação de uma macro requer que saibamos exatamente a sequência de procedimentos que deveremos realizar para alcançar nosso objetivo. Para evitar erros como o ocorrido em sala de aula e normalmente é isso como eu faço, e assim é considerado uma boa prática, ou seja, devemos anotar em um papel todos os comandos a serem executados, na ordem a serem executados.
vamos lá
A empresa Confecções Mariska mantém um registro de suas vendas mensais por meio de planilha do Excel. O departamento responsável pela manutenção da planilha recebe do departamento de vendas um relatório contendo o volume de vendas no mês corrente.
A cada mês, Silvana, que efetua a atualização da planilha geral de vendas, tem que transferir os dados do relatório recebido para o seu controle.
Por ser uma tarefa que se repete todos os meses ou todos os dias Silvana decide criar uma macro que execute os procedimentos de transferência, cálculo e formatação dos dados de forma automatizada.
A planilha que contém os registros das vendas mensais ou diários é a planilha Vendas, que se encontra na pasta Mariska-Vendas.xlsx. A planilha que Silvana recebe todos os meses do departamento de vendas tem o nome de Vendas no Mês e está na mesma pasta.
Atividade 1 – Criando macros interativas.
Objetivo: Criar macro interativa utilizando o gravador de macros.
Tarefas:
Criar macro que atualize a planilha Vendas da pasta Mariska-Vendas.xlsx com os dados da planilha Vendas no Mês.
Gravar a pasta Mariska-Vendas no formato de pasta habilitada para macros.
Habilitar as macros no carregamento da planilha Mariska-Vendas.xlsm.
Clicar na célula H4.
Copiar os dados da planilha Vendas no Mês para a planilha Vendas.
Formatar adequadamente a planilha Vendas.
1. Baixe e abra o arquivo abaixo
2. Na guia Exibir, no grupo Macros, clique na seta da ferramenta Macros e escolha a opção Gravar Macro.
O gravador também pode ser acessado clicando na ferramenta Gravar Macro na Barra de Status.
3. A caixa de diálogo se abrirá para que você preencha os campos:
Nome da macro: você pode adotar como nome da macro o padrão sugerido pelo Excel. No entanto, é melhor que atribua um nome que identifique o que a macro executa. Esse nome não pode conter espaços em branco. Digite PreencheVendas.
Tecla de atalho: combinação de teclas que pode ser utilizada para executar a macro. Digite a letra P.
Armazenar macro em: podemos armazenar a macro nas seguintes pastas:
Pasta de trabalho pessoal de macros: a macro ficará gravada no Excel do seu computador e estará disponível em qualquer pasta de trabalho. Neste caso, a macro será gravada na pasta do Excel de nome Personal.xlsb.
Nova pasta de trabalho: a macro ficará disponível em todas as novas pastas criadas.
Esta pasta de trabalho: a macro ficará disponível somente na pasta em que foi gravada. Selecione esta opção.
Descrição: breve descrição da macro.
Clique no botão OK. A partir de agora, todos os seus movimentos na planilha serão gravados.
4. Selecione a planilha Vendas no Mês.
5. Copie a faixa de células B1:B7.
6. Selecione a planilha Vendas.
7. Clique na célula H4.
8. Cole as células copiadas.
9. Clique na célula G3 e, pela alça de preenchimento, preencha o valor da célula G3.
10. Clique na célula B2, mescle e centralize corretamente o conteúdo.
11. Formate corretamente as células H4:H10.
12. Na guia Exibir, no grupo Macros, clique na setinha da ferramenta Macros e escolha a opção Parar gravação.
A gravação também poderá ser interrompida clicando na ferramenta Parar gravação na Barra de Status.
13. Salve a pasta de trabalho. Observe que, ao tentar salvá-la no formato .xlsx, o Excel envia uma mensagem informando que a planilha contém macros e que, se quisermos que sejam gravadas junto com a pasta, é necessário modificar o tipo do arquivo para Pasta de Trabalho Habilitada para Macro do Excel (.xlsm).
Clique em Não e na janela Salvar Como escolha o tipo de arquivo adequado para salvar a pasta (.xlsm), no caso, escolha a opção: Pasta de trabalho Habilitada para Macro do Excel (*.xlsm)
14. Feche a pasta de trabalho.
15. Vamos agora abrir a pasta salva (Mariska-Vendas.xlsm).
Observe o aviso de segurança Habilitar Conteúdo. Quando abrimos uma pasta que contém macro, o Excel interpreta o código como um possível perigo à execução da planilha.
Para poder executar as macros gravadas é necessário habilitar o conteúdo. Clique no botão Habilitar Conteúdo.
Segurança de macro
A partir da versão 2007 do Excel, as configurações para ajuste de segurança de macros foram modificadas para que a sua execução seja mais segura, evitando que códigos maliciosos possam danificar pastas de trabalho, arquivos ou mesmo o próprio sistema operacional.
1. Para acessar a janela que permite escolher o nível de segurança de macro, clique na guia Arquivo e, em seguida, Opções.
2. Na janela apresentada, selecione a opção Central de Confiabilidade e clique em Configurações da Central de Confiabilidade...
Ao clicar no botão Configurações de Macro, você terá as seguintes opções:
Desabilitar todas as macros sem notificação: desabilita todas as macros e impede que sejam executadas. Se for ativada, somente as macros presentes em locais confiáveis poderão ser executadas. Esses locais confiáveis podem ser estabelecidos pelo usuário.
Desabilitar todas as macros com notificação: as macros estão desabilitadas, mas poderão ser habilitadas pela mensagem exibida na.
Área de Mensagem no carregamento da planilha. É a opção recomendada para a maioria das pastas de trabalho do Excel.
Desabilitar todas as macros, exceto as digitalmente assinadas: permite que somente as macros que tenham assinatura digital sejam executadas.
Habilitar todas as macros (não recomendado; códigos possivelmente perigosos podem ser executados): habilita todas as macros, tornando o programa vulnerável a ataques de códigos maliciosos. Essa opção não é recomendada.
Atividade 2 – Executando macros
Objetivo: Executar a macro gravada.
Tarefas:
Executar a macro PreencheVendas pela caixa de diálogo de macros.
Executar a macro PreencheVendas pelas teclas de atalho.
Executar a macro PreencheVendas por meio de um botão criado na Barra de Ferramentas de Acesso Rápido.
Agora que a macro foi criada, vamos aprender a executá-la. Existem várias formas de executar macros. Neste capítulo veremos três maneiras: pela ferramenta Macros, pelas teclas de atalho e por um botão colocado na Barra de Ferramentas de Acesso Rápido.
1. Apague toda a coluna H para retornar à planilha os valores anteriores à execução da macro.
2. Vamos executar a macro pela ferramenta Macros. Na guia Exibir, selecione a ferramenta Macros e clique sobre o nome da macro criada e em Executar.
3. Vamos executar a macro pelas teclas de atalho. Elimine a coluna H e pressione as teclas Ctrl + Shift + P.
4. Vamos agora atribuir a macro a um botão que será colocado na Barra de Ferramentas de Acesso Rápido. Na guia Arquivo, clique em Opções.
5. Clique em Barra de Ferramentas de Acesso Rápido.
6. No quadro Escolher comandos em, selecione Macros. A seguir, clique sobre o nome da macro PreencheVendas e em Adicionar >>.
7. Observe que o nome da macro foi adicionado ao quadro da direita. Se você for utilizar a macro somente quando esta pasta de trabalho estiver aberta, selecione a opção De Mariska-Vendas.xlsm.
8. Para mostrar o botão na Barra de Ferramentas de Acesso Rápido, o Excel vai utilizar um ícone genérico. Vamos escolher um ícone personalizado para representar a macro. Clique sobre o nome da macro no quadro à direita e, depois, no botão Modificar.
Como falado em sala, normalmente eu crio um botão através das formas e atribuo a macro ao botão, mas foi só para mostrar para vocês que podemos usar outras maneiras
9. Nessa janela, podemos escolher o ícone desejado e também modificar o nome da macro para exibição. Escolha um ícone da lista e digite o nome Vendas no Mês. Clique em OK.
10. Observe que podemos, agora, executar a macro clicando no botão criado na Barra de Ferramentas de Acesso Rápido. Quando posicionamos o mouse sobre o ícone, o nome para exibição atribuído à macro é mostrado na Dica de Tela.
Silvana deseja modificar a macro criada para que, além de copiar os dados da planilha Vendas no Mês, os dados do primeiro mês na planilha Vendas sejam transferidos para a planilha Histórico. Dessa forma, ela pretende preservar os dados da planilha e criar um histórico de vendas.
Atividade 3 – Utilizando referências relativas para criar macros
Objetivo: Utilizar referências relativas na criação de macros.
Tarefa: Criar a macro TransfereVendas
Em muitos casos, quando utilizamos macros, desejamos que os dados não sejam posicionados sempre nas mesmas células, mas que ocupem espaços relativos dentro da planilha. É o caso, por exemplo, de transferir os dados de um local para outro. Esses dados transferidos, porém, não deverão ocupar o lugar de informações já existentes; a própria macro deverá encontrar um local vazio e adequado para efetuar a movimentação
A macro que será criada deverá executar os seguintes procedimentos:
Copiar os dados referentes ao primeiro mês da planilha Vendas.
Colar esses dados na primeira coluna vazia da planilha Histórico.
Transferir os dados da planilha Vendas no Mês para a planilha Vendas, formatando-a adequadamente
1. Modifique os dados da planilha Vendas no Mês para os seguintes:
Bermuda 10
Blusa 20
Calça 30
Camiseta 50
Casaco 70
Saia 100
Vestido 150
2. Inicie a gravação da macro TransfereVendas.
3. Selecione a planilha Vendas e recorte os dados de B3:B10.
4. Clique na planilha Histórico.
5. Pressione a tecla F5 (Ir Para) e no campo Referência digite o endereço XFD1. Em seguida, clique em OK.
6. Na guia Exibir ou clique na ferramenta Macros e na opção Usar Referências Relativas. Certifique-se de que a ferramenta esteja selecionada (o ícone da ferramenta fica envolvido por uma leve linha esverdeada)
Na guia desenvolvedor talvez seja mais prática como mostrado na imagem abaixo:
7. Pressione a tecla END, solte e, depois, pressione a tecla Seta à esquerda para selecionar a primeira célula preenchida à esquerda.
8. Pressione a tecla Seta à direita para selecionar a primeira célula vazia.
9. Desative a opção Usar Referências Relativas.
10. Cole os valores recortados
11. Selecione a planilha Vendas.
12. Selecione a região C3:H10 e movimente-a para a região B3:G10.
13. Clique na célula G3 e, pela alça de preenchimento, preencha a célula H3.
14. Selecione a planilha Vendas no Mês e recorte o intervalo B1:B7.
15. Selecione a planilha Vendas, clique na célula H4 e cole o intervalo recortado.
16. Formate a planilha adequadamente e encerre a gravação da macro.
17. Insira novos valores na planilha Vendas no Mês e execute a macro.
18. Salve as alterações e feche a pasta.
Atividade Extra
Ajustes automática da coluna
Clique com o botão direito do mouse na guia planilha 1 e em seguida escolha a opção exibir código como mostrado na imagem abaixo
Após será exibido a caixa abaixo e nela está com a opção geral selecionada, você trocar para Worksheet
Por padrão o VBA automaticamente irá lhe apresentar duas linhas de códigos.
Entre as duas linhas de códigos você irá digitar o seguinte código:
Cells.EntireColumn.AutoFit
Outra opção de você abrir esse módulos é utilizando as teclas de atalho ALT + F11
Se quiser apagar as duas linhas que aparecem e usar todo esse código aqui que vou colocar você pode, inclusive já vai comentado
Sub AutoAjustarColunas()
' Autoajusta todas as colunas da planilha ativa
Cells.EntireColumn.AutoFit
End Sub
Se quiser que o ajuste seja feito somente em uma planilha específica, como por exemplo a planilha chamada "Relatório":
Sub AutoAjustarRelatorio()
Worksheets("Relatório").Cells.EntireColumn.AutoFit
End Sub
Observe que especifiquei no código o nome da planilha
Se quiser que o ajuste ocorra para todas as planilhas do arquivo, use:
Sub AutoAjustarTodasPlanilhas()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.EntireColumn.AutoFit
Next ws
End Sub
Se precisar de ajustes de linhas também (altura automática):
Comentários
Postar um comentário
Deixe seu comentário; isso nos ajudará a lhe ajudar