Somase com Desloc e marcação das células selecionadas

Planilha de controle de ligações Excel VBA
Planilha de controle de ligações Excel VBA
30 de junho de 2013
Gráfico de mapa no Excel 2013
Gráfico de mapa no Excel 2013
21 de julho de 2013

Somase com desloc capa

Somase com desloc e marcação das céluas selecionadas

Objetivo: Demonstrar como fazer no Excel a seleção de colunas diferentes em somas condicionais e marcar as células que foram selecionadas para a soma.

Somase com desloc capa

Neste artigo vou demonstrar como criar uma planilha aonde sejam utilizados critérios de soma com a função somase variando a coluna utilizando a função desloc.

Para incrementar um pouco o seu uso fiz também uma formatação condicional avançada no Excel que faz com que as células selecionadas nos critérios sejam destacadas na planilha em cores diferentes. Veja como foi feito.

  • Faça o download da planilha em Download da planilha clicando no botão GUT PPT para acompanhar o exemplo.
  •  Clique na célula B4 da planilha Linha e Coluna, veja que é liberada uma lista suspensa de validação de dados. Veja neste vídeo como fazer: http://guiadoexcel.com.br/validacao-de-dados-excel
  • Esta lista de dados está apontando para a planilha Parâmetros aonde há na coluna B uma lista com os meses.

SomaseComDeslocFormatacao3

  • Uma lista semelhante existe também para as filiais selecionáveis na célula B5 na planilha Linha e coluna.
  • Clique na célula B7 e note a seguinte fórmula que vou explicar:

SomaseComDeslocFormatacao4

  • A função SOMASE é explicada neste vídeo http://guiadoexcel.com.br/funcao-de-soma-condicional-no-excel-somase, caso não a conheça veja o vídeo e retorne para acompanhar o restante da função.
  • A função DESLOC realiza o deslocamento de uma célula ou intervalo em relação a colunas, células, altura ou largura. Neste exemplo deslocaremos as colunas.

Parâmetros da função DESLOC:

ref: referência de células, no caso foi selecionada o intervalo $F:$F.

lins: passado como 0, deslocaria células para baixo ou para cima de um intervalo, exemplo caso a referência fosse A1 e o parâmetro lins fosse 1, deslocaria a referência para A2 ou seja uma linha para baixo.

cols: quantidade de colunas deslocada para a esquerda ou direita. Exemplo para a referência $F:$F e caso o parâmetro cols fosse 1, deslocaria para $G:$G, ou seja, uma coluna para a direita. No nosso exemplo utilizamos CORRESP que vou explicar em seguida.

[altura]: opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.

[largura]: opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.

  • Como disse em cols foi utilizada a função CORRESP para informar a quantidade de colunas que seriam deslocadas á direita ou á esquerda, vejamos ela:

Parâmetros da função CORRESP:

valor procurado: o valor que será localizado dentro da matriz. No caso $B$4 que se refere ao mês.

matriz procurada: o local aonde serão localizados os valores. No caso $F$1:$Q$1 que se refere aos meses da planilha.

tipo da correspondência: para uma correspondência exata é passado 0, para correspondências aproximadas passar -1 para uma aproximação para baixo e 1 para uma aproximação para um número imediatamente superior. No caso 0 para uma correspondência exata.

Desta forma a função CORRESP está apontando para a coluna dentro do intervalo selecionado retornando para janeiro = 1, fevereiro = 2 e assim por diante. Como a referência da função DESLOC está para F:F, ou seja janeiro, então temos que reduzir 1 da função CORRESP de modo que aponte para a coluna correta, pois caso fosse colocado janeiro ele apontaria para 1 e deslocaria uma coluna á direita ocasionando um erro e apontando para a coluna fevereiro.

SomaseComDeslocFormatacao5

Agora vamos á formatação condicional que faz com que as colunas e linhas da planilha que se referem as seleções realizadas sejam destacadas.

Formatação condicional da planilha

  • Clique na célula D3 e note a seguinte função: =SE(E3=$B$5;”x”;””) ela demonstra que caso a célula E3 seja igual á B5 então deverá constar nesta célula o valor “x”, e caso contrário o valor branco “”. Esta função está comparando se a filial que está na planilha é a mesma selecionada no filtro.
  • Agora clique na célula D25 e veja a função =SE(F1=$B$4;”x”;””) é realizada uma marcação com x caso o valor da célula F1 seja semelhante ao valor da célula B4 constante no filtro de dados. Ela compara o mês selecionado.
  • Clique sobre a planilha e clique em Página Inicial->Formatação Condicional->Gerenciar Regras
  • Veja que as regras identificam se a célula em questão está marcada com o x e desta forma a destaca caso esteja.

 

SomaseComDeslocFormatacao6

 

Aqui está o truque, quando foi criada a regra note que o $ está somente para a linha e não para a coluna o que significa que a formatação vai variar conforme a coluna, formatar A se A25 estiver x, formatar B se B25 estiver x. De forma semelhante para a segunda regra aonde o que foi travado foi a coluna e não a linha, formatar linha 2 se D2 estiver x, formatar linha 3 se D3 estiver x.

Clicando na segunda planilha “Somente célula” você pode notar que somente a célula que está sendo somada foi selecionada. A condição foi a mesma utilizada na formatação anterior com uma mudança, as regras estão juntas na mesma regra de formatação.

SomaseComDeslocFormatacao7

Clicando em Editar regra você consegue ver a fórmula utilizada: =E($D2=”x”;D$25=”x”), aonde identifica os dois critérios anteriormente explicados, e que terminam na seguinte formatação.

SomaseComDeslocFormatacao8

Então é isso, agradeço por terem acompanhado mais este artigo e até a próxima.

[saiba_mais]

GUT PPT
[ads image=”https://www.guiadoexcel.com.br/wp-content/uploads/sites/866/2012/08/banner_topoGuia1.png” url=”http://www.cursovba.com.br/” alt=”Texto alternativo” side=”left”]

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

17 Comentários

  1. Douglas disse:

    Boa tarde, se eu fosse montar com uma base dessa forma, como ficaria a formula ??

    Filial Valor Mês
    Filial 1 1000 Janeiro
    Filial 2 2000 Janeiro
    Filial 3 3000 Janeiro
    Filial4 4000 Janeiro
    Filial 1 1500 Fevereiro
    Filial 2 2500 Fevereiro
    Filial 3 3500 Fevereiro
    Filial 4 4500 Fevereiro
    Filial 1 2000 Março
    Filial 2 3000 Março
    Filial 3 4000 Março
    Filial 4 5000 Março

  2. Douglas disse:

    Bom dia Marcos,
    Era isso mesmo que precisava, muito obrigado pela ajuda, e parabéns pelo site !!!

    Abraço

  3. Márcio disse:

    Duvida!! existe alguma formula que faz o somatorio de uma lista de funcionarios com o setor! Exemplo: tenho uma planilha com varios funcionarios (padaria 10 func – caixa 100 func cada um tem horas positivas e negativas) que somar as hora negativas em um campo e positivas em outro

  4. JOEL disse:

    Bom dia!

    Marcos, lí seus artigos sobre explicação da função somase, e é ótimo, consegui utilizar, mas tenho uma duvida, gostaria de somar por exemplo um critério da plan 1 cedula f2 somar até plan 20 cedula f2 e somar por exemplo um total de faturamento dela que esta na cedula h42 de todo esse intervalo de planilhas.
    Tem como fazer?
    Desde já agradeço!

  5. Marcelo disse:

    Show de bola, procurei muito por isso!! Obrigado!

  6. Paulo Hatakeyama disse:

    MARCOS, AQUI É O PAULO, TUDO BEM ?
    TENHO UM RELATÓRIO DE PEDIDOS, ONDE PRECISO CALCULAR O SALDO DO ESTOQUE A CADA VEZ QUE APARECE UM PEDIDO, PORÉM ESSE SALDO TEM QUE APARECER NA MESMA COLUNA ONDE ESTÁ O TOTAL DO ESTOQUE
    EXEMPLO: NA COLUNA G3 E G4, TENHO OS PEDIDOS, E NA COLUNA H2, O TOTAL DO ESTOQUE.
    NA COLUNA H3,QUERO QUE APAREÇA O SALDO DE H2-G3, E NA COLUNA H4, O SALDO DE H3-G4.
    PODERIA ME DAR UMA DICA SE TEM ALGUMA FUNÇÃO QUE FAÇA ISSO OU UM CÓDIGO VBA ?

    GRATO
    PAULO

  7. Marco disse:

    oi Marcos,

    como faço para utlizar o SOMASE com DESLOC para ir somando diversas colunas? Minha fórmula é =SUM(OFFSET(‘[P&L PUB.xlsx]P&L by month’!$C$59;;;;1)) e queria que aumentando a largura do DESLOC a fórmula somasse todas as colunas 1 até o final. A solução que já achei para SOMASE em diversas colunas seria fazer vários SOMASE, mas isso não resolve meu problema.

    Obrigado e parabéns pelo blog!!

    • Marcos Rieper disse:

      Olá Marco,

      A ideia é esta mesmo de usar o OFFSET, mas veja, é necessário que o endereço que está colocando no INDIRETO, seja IGUAL ao endereço que é gerado pelo Excel quando você digita = e clica no intervalo que gostaria. Talvez seja um erro de estar montando incorretamente o endereço utilizado pela função.

      Abraço
      Marcos Rieper

  8. MM21 disse:

    Olá Marcos, boa tarde.

    Existe alguma forma de criar um SOMASES que entenda o valor procurado em duas colunas, se não achar na primeira (A), ele procura na segunda (B), então seria um intervalo critério com OU ou CORRESP, mas não consegui validar essa tentativa.

    Exemplo:

    Vendedor Loja Trimestre Mês QTD Valor
    (Coluna A) (Coluna B) (Coluna C) (Coluna D) (Coluna E) (Coluna F)
    Alan Zona Sul 1º Tri Janeiro 32 R$ 3.200
    Alan Zona Norte 3º Tri Julho 41 R$ 4.100
    Bruno Zona Sul 1º Tri Janeiro 57 R$ 5.700
    Bruno Zona Norte 3º Tri Julho 64 R$ 6.400

    É possível fazer um SOMASES que entenda:

    Intervalo Soma = Coluna F
    Intervalo Critério = Coluna A OU B
    Critério = uma lista suspensa que tenha o nome do vendedor + loja
    Intervalo Critério 2 = Coluna C OU D
    Critério 2 = uma lista suspensa que tenha o trimestre + mês

    Espero que você leia minha mensagem e me ajude!

    Obrigado!!

    • Marcos Rieper disse:

      Olá Mariane,

      Acho que o mais fácil nesta situação é usar uma função SE que verifique se a soma da primeira coluna é maior que 0 somar esta, senão somar a outra, usando dois somases, um para cada coluna, além do somase na verificação.

      Abraço
      Marcos Rieper

  9. TARSO DE MELO disse:

    muito bom, tenho aprendido bastante com suas aulas! obrigado!

  10. Ester disse:

    Marcos, bom dia.

    Era isso mesmo que eu precisava, muito obrigada pelos esclarecimentos.

    Abraço.

  11. andre disse:

    Tenho um problema a resolver.
    Tenho valores em uma planilha na matriz: J5:U489
    No título das colunas J4:U4 tenho: “Realizado” ou “A Realizar”
    Nas linhas G1:G489 tenho os códigos: “5004”, “9001”; “1014”, “5100”.

    Preciso somas os valores na matriz J5:U489, quando o título da coluna for “Realizado” e o código da linha for “5004”.
    Como proceder?

  12. Ronaldo disse:

    Rapaz, ficou ótima sua planilha. Nunca tinha usado o desloc, bem interessante.
    Estou precisando de algo parecido, tentei adaptar sua planilha mas não consegui, veja se você pode me ajudar, por favor.
    Imagine nessa sua planilha que cada uma das filiais tivesse uma data de início de contrato diferente uma da outra. Por exemplo,
    Filial 1 = setembro
    Filial 2 = fevereiro
    Filial 3 = abril
    etc
    Como eu faria para somar, por exemplo, todos os números da filial 1 a partir de setembro? Ou da filial 2 a partir de fevereiro?
    E ainda considerando que antes do contrato haviam também compras/vendas, porém sem contrato e esses dados anteriores não devem ser incluídos na soma. Ou seja, eu não posso simplesmente colocar pra somar a linha toda, porque não são células vazias.
    Tentei SOMASES, mas o Excel não permite colocar critérios nas linhas e colunas na mesma fórmula.
    Me ajuda! kkkk
    Obrigado!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.