Usando função SOMA no Excel - Excel VBA para Negócios

Olá Pessoal!

Neste post veremos como usar a função Soma do Excel para somar linhas, colunas e células separadas.

Hoje estou inaugurando o meu canal no youtube com a primeira vídeo aula e gostaria de saber a opinião de todos. Seu comentário e sugestão é de grande valor! Abaixo link do vídeo:

Estou disponibilizando o artigo abaixo para aqueles que gostam de acompanhar um passo a passo.

Para esse exemplo vamos utilizar a planilha abaixo como exemplo:

Para ter acesso a planilha de exemplo, baixe o arquivo em Excel no link abaixo:

Função Soma no Excel

Iremos calcular na coluna J o total das despesas por mês, por trimestre e por ano. Na linha 16 iremos calcular o valor trimestral e anual para cada tipo de despesa listada na linha 2. Todos os cálculos serão feitos utilizando a função Soma.

Para facilitar, vamos separar essa explicação em duas partes:

• Soma horizontal
• Soma Vertical
• Soma com seleção de células diversas

Soma Horizontal

Vamos começar somando as despesas mensais por mês, com o intuito de descobrir o total gasto por mês de uma determinada família. A Soma será feita na coluna J onde iremos escrever a seguinte função =SOMA(C4:I4)

Após pressionar a tecla Enter você terá como resultado a somo dos valores das células C4 até I4 no total de 2.248,40

Para repetir a soma da linha J4 para os outros meses do ano (célula J5 até J15) iremos utilizar a função Arrastar do Excel. Basta clicar no canto inferior direito da célula J4 e arrastar para baixo até a célula J15

Note que o Excel irá, automaticamente, somar todas as linhas com o resultado total de despesas por mês.

Soma Vertical

Depois de concluirmos a soma horizontal das despesas mensais, podemos agora realizar a soma total de cada mês criando assim um total trimestral e anual para cada despesas individualmente, assim como um total trimestral e anual com base na coluna Total de Despesas

Para iniciar a soma iremos selecionar a célula C16 e inserir a seguinte função: =SOMA(C4:C15) e vamos arrastar a fórmula da célula C16 para a células D16 até J16. Seguem abaixo resultados:

Passo 1 - inserindo na célula C16 a função: =SOMA(C4:C15) 

Passo 2 - Arrastando formula da célula C16 até J16

Passo 3 - Resultado da SOMA Total Anual (linha 16)

Soma por célula

Agora iremos criar as funções de SOMA para calcular o total de Despesas Mensais para o primeiro e segundo semestre do ano. Segue passo a passo:

• Somando as despesas mensais do Primeiro Semestre

Passo 1 - Inserindo na célula C18 a função =SOMA(C4;C5;C6;C7;C8;C9)

*Como as células para cálculo são sequenciais, para esse caso poderia ser utilizado também a função soma na mesma lógica do total anual =SOMA(C4:C9)

Passo 2 - Arrastando formula da célula C18 até J18

  

• Somando as despesas mensais do Segundo Semestre

Passo 1 - Inserindo na célula C20 a função =SOMA(C10;C11;C12;C13;C14;C15)

*Como as células para cálculo são sequenciais, para esse caso poderia ser utilizado também a função soma na mesma lógica do total anual =SOMA(C10:C15)

Passo 2 - Arrastando formula da célula C18 até J18

• Resultado do total do primeiro e segundo trimestre ( linhas 18 e 20)

Abaixo podemos ver o resultado final das somas realizadas na planilha de despesas mensais. 

Podemos analisar a planilha por tipo de despesas, por mês, por ano e por semestre. Ou seja, temos uma planilha completa para analise.

Espero que esse post lhe ajude a elaborar planilhas em seu trabalho ou sua em casa. Qualquer dúvida ou sugestão, deixe seu comentário.

Um grande abraço.

Leia também:

Como mostrar a guia Desenvolvedor – Excel Avançado (Macros)

Localizar e substituir valor em uma célula da planilha

Usando a formatação condicional no Excel

Olá Pessoal,

Hoje, você aprenderá como usar a formação condicional do Excel para facilitar a identificação de valores em sua planilha com case em alguns critérios, sendo eles: 5 primeiros valores, valores duplicados e valores menores que 1000.

O que é a formatação condicional?

É um recurso muito útil do Excel para aplicar cores a células com determinados valores de sua planilha. Após criada as regras e se os valores sofrerem qualquer alteração, a formatação se ajustará automaticamente, pois a formatação condicional é dinâmica.

Em resumo, a formatação condicional irá destacar os valores definidos nas regras criadas, facilitando na hora de analisar sua dados e informações. Vou dar um exemplo simples:

Vamos supor que você tenha uma grande diversidade de despesas mensais em sua empresa e você gostaria de descobrir rapidamente quais são os 5 maiores valores que elevaram seus custos administrativos.

• Selecione a coluna onde estão os valores a serem analisados

• Clique na guia Inicio > Estilos > Formatação Condicional > Regras de Primeiros/Últimos > 10 primeiros itens

• Na janela abaixo insira 5 (cinco maiores valores) e  com Preenchimento Verde e Texto Verde Escuro e clique em OK.

• O resultado da formatação condicional será as despesas Aluguel, Anuncio e publicidade, Telefone, Treinamentos e Viagens

Existem outras formas de encontrar os Top 5 no Excel e a ferramente de formatação condicional é uma delas, a diferença é que com ela você consegue colocar efeitos visuais em suas planilhas.

Tipos de Regras que podem ser criadas

Existe outros tipos de regras que você do criar na formatação condicional, sendo elas:

• Formatar todas as células com base em seus respectivos valores
• Formatar apenas células que contenham
• Formatar apenas os primeiros ou último valores (demonstrada acima)
• Formatar apenas valores acima ou abaixo da média
• Formatar apenas valores exclusivos ou duplicados
• Usar uma fórmula para determinar quais células devem ser formatadas

Como citado no inicio do artigo ainda valor exemplificar o uso das regras para valores duplicados e células que contenham valores maior que 1000.

Aplicando formatação condicional para itens duplicados

Para esse exemplo não iremos limpar a formatação já incluída para os 5 primeiros valores do exemplo anterior, portanto agora vamos seguir os passos abaixo:

• Selecione a coluna B ou melhor a coluna descrição

• Clique na guia Inicio > Estilos > Formatação Condicional > Realçar Regras das Células > Valores Duplicados

• Na janela abaixo insira células que contêm Duplicados e com Preenchimento Vermelho Claro e Texto Vermelho Escuro e clique em OK

• O resultado da formatação condicional será as descrições Condomínio, Consultoria Externa, Veiculo

Aplicando formatação condicional para valores menores que 1000

Novamente vamos manter as formatações já incluídas anteriormente para os 5 primeiros valores do exemplo anterior e para os itens duplicados na coluna descrição, portanto agora vamos seguir os passos abaixo para realçar os valores maiores que 1000:

• Selecione a coluna C ou melhor a coluna Valor

• Clique na guia Inicio > Estilos > Formatação Condicional > Realçar Regras das Células > É menor que

• Na janela abaixo insira células que são menos do que 1000 e com Texto Vermelho e clique em OK

• O resultado da formatação condicional serão os itens abaixo selecionados e com a cor de fonte vermelha

Existem diversas outras formas e métodos para aplicar regras de formatação condicionais. Agora você precisa praticar o uso dessa ferramenta em seu dia a dia para melhor o uso dela. 

Use o Excel, experimente essa ferramente de formatação condicional e encontre o que melhor funciona para você!

Caso tenha alguma dúvida, sugestão ou comentário fique a vontade.

Para acesso a planilha de exemplo, deixe seu e-mail nos comentários.

Grande Abraço,

Vagston Bueno

Leia também:

Tabela Dinâmica - Pra que serve e como criar?

Como usar a função SE no Excel

Filtro avançado no Excel com execução via Macro

Olá Pessoal,

Nesse post vou mostrar como criar uma filtro avançado no Excel utilizando a gravação de macro do Excel para executar o filtro quantas vezes for necessário ao clique de uma botão.

Não se preocupem que não vamos trabalhar com programação em VBA nesse post, mas antes de seguir essa leitura é importante que já tenha lido ou tenha conhecimento das ferramentas explicadas nos artigos abaixo listados:

Como mostrar a guia Desenvolvedor

Como criar sua primeira Macro utilizando o VBA no Excel

Como dito acima vamos trabalhar com uma Macro para executar o filtro avançado automaticamente, portanto é obrigatória que já esteja com sua Guia Desenvolvedor devidamente habilitada no Excel.

O que é filtro avançado?

É um recurso do Excel que permite filtrar uma lista de dados com mais de um critério, ou seja, irá exibir apenas os dados que atendem a condição de filtro criada e os demais serão ocultados. Essa ferramenta é excelente para utilizar em planilhas onde existe uma lista muito grande de itens.

Como Aplicar

Para aplicar o filtro avançado vou utilizar como exemplo uma lista de produtos cadastrados na guia Cadastro. A planilha terá três colunas, sendo elas: Código do Produto, Descrição e Valor Unitário. 

*Os valores e produtos foram criados apenas para usar como exemplo, ou seja, não tem nenhuma referência com o valor comercial.

Para iniciar, no lado esquerdo da planilha atual vamos duplicar o cabeçalho duas vezes, deixando algumas linhas em branco (F2 até F8) para inserir os dados que vamos utilizar para os nossos futuros filtros. Sua planilha ficará igual a imagem abaixo:

Após criado os cabeçalhos, vamos ativar a gravação da Macro para que o filtro avançado seja memorizado para utilização futura. Sigo os passos abaixo:

• Clique na Guia Desenvolvedor
• Depois em Gravar Macro

• Colocar o Nome da Macro como FiltroAV
• Inserir na Descrição a texto: Gerar filtro avançado de produtos
• Clique em OK

Com a Macro em execução vamos finalmente criar o filtro avançado clicando em:

• Guia Dados
• Classificar e Filtrar
• Selecione Avançado

A janela Filtro Avançado será aberta e vamos selecionar no campo Ação a opção Copiar para outro local.

No campo Intervalo da lista vamos selecionar a planilha base que está na colunas A até C: 

Resultado : $A$1:$C$50

No campo Intervalo de critérios vamos selecionar o local onde será inserido os dados para aplicar o filtro, ou seja, de F1:H2 .

Resultado : Cadastro!$F$1:$H$2

No campo Copiar para vamos selecionar somente os cabeçalhos que estão nas células F9 até H9.

Agora clicamos em OK e o Excel irá apresentar o resultado abaixo das células F5, G5 e H5, conforme abaixo:

Antes de continuar com a explicação do filtro avançado, vamos parar a gravação da Macro.

• Clique na Guia Desenvolvedor
• Depois em Parar Gravação

Pronto! Macro salva em seu projeto do VBA.

Agora Vamos inserir um botão na sua planilha para executar a macro do filtro avançado em um clique:

• Na Guia Desenvolvedor clique em Inserir Botão
• Clique na coluna D de sua planilha para criar o botão
• Selecione Macros em: Esta pasta de trabalho
• Clique na Macro FiltrosAV e depois em OK

Agora ao clicar no Botão 1 o Excel irá criar e executar o filtro avançado automaticamente.

Vamos fazer um exemplo para entender melhor como essa ferramenta funciona:

• Na célula G2 vamos digitar a palavra CANETA
• Depois vamos clicar no Botão 1

Você vai notar que irá aparecer todos os produtos que tem a palavra CANETA a partir da célula F10.

Essa ferramente é bem interessante, não é? Você pode utilizar para fazer diversos tipos de filtros. Por exemplo, vamos fazer agora um filtro com base no valor:

• Limpe a célula G2 (CANETA)
• Insira na célula H2 o valor <4
• Pressione o Botão 1

Como pode ser na imagem abaixo o filtro a partir da célula F10 só mostrou os produtos que tem valor menor que R$4,00.

Pronto. Sua planilha com filtro avançado via macro está criada e funcionando perfeitamente. Agora você precisa praticar o uso dessa ferramenta no seu dia a dia e adaptar ela de acordo com suas necessidades. 

Para aqueles que tem interesse em saber como é o código VBA que o Excel gera ao gravar a Macro para realizar essa ação, dentro da planilha Cadastro pressione F11 e confira o código gerado para o filtro avançado.

Antes de fechar sua planilha não esqueça de salvar ela clicando em Salvar Como e selecionando o tipo Pasta de Trabalho Habilitada para Macro do Excel.

Caso tenha alguma duvida ou sugestão, deixe seu comentário.

Grande abraço

Vagston Bueno

Habilitando a guia Desenvolvedor para Macros no Excel

Olá Pessoal,

Esse tópico é destinado as pessoas que já tem algum conhecimento da ferramenta de gravação de macros, porem está com dificuldades para achar a guia Desenvolvedor do Excel.

A guia Desenvolvedor não é exibida por padrão da Microsoft, mas podemos adicioná-la quando desejar fazer ou usar as seguintes funções:

• ·         Escrever macros – Visual Basic;
• ·         Gravar macros;
• ·         Executar macros já criadas;
• ·         Inserir controles de ActiveX;
• ·         Inserir controle de formulários dentro do Excel

Após habilitar a guia, ela permanecerá visível em todos os arquivos de Excel executados em seu computador.

Como habilitar?

Versões:  2016,2013 e 2010.

1. Clique na guia Arquivo.
2. Clique em Opções.
3. Clique em Personalizar Faixa de Opções.
4. Em Personalizar a Faixa de Opções e em Guias Principais, marque a caixa de seleção Desenvolvedor.

Versão: 2007

1.       Clique no Botão do Microsoft Office e em Opções do Excel.
2.  .   Clique em Preencher e marque a caixa de seleção Mostrar guia Desenvolvedor na Faixa de Opções.

Pronto! Guia de desenvolvedor habilitada em seu Microsoft Excel.

Qualquer duvida, deixe um comentário no blog ou no Facebook.

Abraço.

Leia também :

Localizar e substituir valor em uma célula da planilha

Principais fórmulas Excel Português X Excel Inglês