Álvaro Caldas’ Info

Função SOMASE e duas colunas para cálculo

Posted on: 27 janeiro 2008

No Microsoft Excel, pode-se somar os valores com base num determinado critério, como, por exemplo, a necessidade de somar todas as vendas que se referem a determinado vendedor, como na planilha abaixo.

PlanDetalheVendas

Para isso, toma-se como ferramenta a Função SOMASE, que tem a seguinte sintaxe:

SOMASE(intervalo;critérios;intervalo_soma)

  • Intervalo é o intervalo de células que se deseja tomar por base dos critérios.
  • Critérios são na forma de um número, expressão ou texto, que definem quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como o valor 32 (32 ou “32″), maior que 32 (”>32″), ou igual ao texto maçãs (”maçãs”).
  • Intervalo_soma são as células que serão realmente somadas, satisfeitos os critérios.

Comentários da Ajuda do Excel (F1):

  • As células em intervalo_soma são somadas somente se suas células correspondentes em intervalo coincidirem com os critérios estipulados.
  • Se intervalo_soma for omitido, as células em intervalo serão somadas.
  • O Microsoft Excel fornece funções adicionais que podem ser usadas para analisar seus dados com base em uma condição. Por exemplo, para contar o número de ocorrências de uma seqüência de caracteres de texto ou um número contido em um intervalo de células, use a função CONT.SE. Para que a fórmula retorne um entre dois valores com base em uma condição, como uma bonificação de vendas baseada em um valor de vendas especificado, use a função SE.

Para o caso apresentado, utiliza-se uma outra tabela, na mesma ou em outra planilha, na mesma pasta de trabalho ou em outra, como a que se segue:

 PlanResumoVendas

Neste caso, observando que os dados para resumo estão em nova planilha, mas no mesmo arquivo (Vendas.xls), as fórmulas que conterão nas células da planilha ‘Resumo Vendas’ poderão, apresentando a Função SOMASE, ser construídas, passo-a-passo, da seguinte forma:

PASSO 1º: Construir a primeira fórmula em B2 para inicialmente somar todos as vendas da planilha ‘Detalhe Vendas’, referente a primeira coluna de vendas de Jan (coluna B) que sejam do primeiro Vendedor (Marcos na célula A2 de ‘Resumo Vendas’). O raciocínio para construir a fórmula será:

Observar a primeira coluna de vendedores (A2 até A11) e aquela linha que for referente ao vendedor Marcos, terá a célula correspondente na coluna vendas sendo somada (B2 até B11).

A primeira parte da fórmula na célula B2 conterá, então, o seguinte:

=SOMASE(‘Detalhe Vendas’!$A$2:$A$11;A2;‘Detalhe Vendas’!$B$2:$B$11)

Onde:

  • ‘Detalhe Vendas’!$A$2:$A$11 é o intervalo dos vendedores que será tomado por base para verificar que linha atenderá o critério. Como o nome da planilha Detalhe Vendas tem espaço entre as palavras, coloca-se o apóstrofo para indicar onde começa e termina o nome da planilha e, depois, indica-se com ponto de exclamação. Logo a seguir, vem a indicação do intervalo, que será referência absoluta, quando for copiado para células abaixo.
  • A2 é a célula onde tem o primeiro critério na planilha ‘Resumo Vendas’. Quando não indicado de que planilha é uma célula, o Excel entende se tratar da planilha corrente, no caso, a fórmula se encontra em célula da planilha Resumo de Vendas. É uma referência relativa, pois na cópia da fórmula para as células abaixo, deve ser mudada para cada linha respectiva, para conter o novo critério (o nome do novo vendedor como base pra somatória).
  • ‘Detalhe Vendas’!$B$2:$B$11 é o intervalo soma que será calculado, quando, para isso, a linha que se tratar correspondente à célula do intervalo dos vendedores satisfizer o critério, ou seja, quando na célula tiver o conteúdo Marcos (primeiro vendedor), as células do intervalo soma respectiva terá seu valor acrescido pela função, no final, somando as vendas de Marcos no mês de Fev (coluna B). Como essa célula será copiada, usa-se a referência absoluta para que o Excel não mude o intervalo soma, pois este sempre será usado para os cálculos.

PASSO 2º: Construir o restante da fórmula em B2 para acrescer na soma todos as vendas da planilha ‘Detalhe Vendas’, referente tanto à primeira coluna de vendas (Jan) como também a segunda (Fev) que sejam do primeiro Vendedor (Marcos).

O raciocínio para construir a fórmula será simplificado então:

Basta copiar a mesma fórmula no início, acrescer o indicador soma (+) e colar a fórmula com ajustes que indiquem agora que será considerado as vendas do mês de Fev (coluna C).

A fórmula na célula B2 conterá, finalmente, o seguinte:

=SOMASE(‘Detalhe Vendas’!$A$2:$A$11;A2;‘Detalhe Vendas’!$B$2:$B$11) + SOMASE(‘Detalhe Vendas’!$A$2:$A$11;A2;‘Detalhe Vendas’!$C$2:$C$11)

Onde o acréscimo após o sinal de adição (+), deveu-se pelo fato de a fórmula agora considerar também o mês de fevereiro na contagem das vendas, considerando cada Vendedor.

Passo 3º: Do jeito que a fórmula se encontra, já com as referências absolutas ($) no intervalo e no intervalo soma, pois não poderão ser outros para as outras células, basta realizar a cópia, selecionando a célula B2 da planilha ‘Resumo Vendas’ e:

  • Menu Editar – Copiar (ou CTRL+C)
  • Selecionar as células de destino (B3 até B5)
  • Menu Editar – Colar (ou CTRL+V)

Poderia também, ao selecionar a primeira célular para cópia, posicionar o mouse na alça de preenchimento da célula e clicar e arrastar com o mouse para baixo até a célula pretendida.

PlanResumoVendas-copiando

A planilha, após a cópia da fórmula em B2 para as células abaixo, ficará com os seguintes resultados:

PlanResumoVendas-Resultado 

Carpe Diem.

About these ads

17 Respostas to "Função SOMASE e duas colunas para cálculo"

A planilha ‘Excel’ é sempre um dos programas de mais difícil compreensão, visto que a maioria parece ter mesmo deficiência em matemática, o que acaba complicando o entendimento. Mas aqui está tudo muito bem explicado.

Legal, gostaria de saber se existe como adicionar mais de um intervalo. Ex.: Calcular a soma do campo “intervalo_soma” sendo que só sera aplicado a soma se o CNPJ e os meses forem iguais ??? ou seja, dois intervalos!

Tenho a seguinte Duvida:
Formula =somase(A2:A15;T8;B2:B15),

Na coluna A eu tenho os Nomes Clara – SP, Clara – SF e assim por diante;
Na coluna B em tenho os valores correspondentes;
Na Celula T eu tenho Clara.

Porem como eu tenho varias Claras na Coluna A ele é claro nao soma. Eu gostaria de colocar na propria forma de forma que ele soma-se tudo que inicie com Clara. é possivel?

Mas como coloco dois Critérios ex:
Somase(Intervalo;Critário+um outro Critério; e assim o intervalo de soma)

??

Perfeitamente explicado!
A pagina foi de extremo valor para minhas dúvidas.

Bom Dia Andre,
Fiz o curso de Excel avançado com voces e isto me abriu muitas portas. Só que estou tendo dificuldade em uma Função e gostaria da sua preciosa ajuda.
A função é a “SOMASE” mais a função “E”, que terá que efetuar o seguinte calculo:
Na coluna “A” tenho “varias Datas” no decorrer do Ano. Na coluna “B”, tenho uma serie de valores das respectivas datas.
A função deverá SOMAR se o intervalo compreender uma data maior que uma “data” e Menor de que outra “DATA”, mais ou menos Assim:

=somase(A1:A10;”=31/01/2008;B1:B10)

Jé tentei varias variações mais não deu certo. Por favor peço sua ajuda.

Obrigado pela Atençao dispensada.

TEM QUE SER ASSIM:

=somase(A1:A10;”31/01/2008″;B1:B10)

A DATA COMO SE FOSSE UMA REFERENCIA DE CELULA USANDO ASPAS (“”)

Gostaria de saber como devo digitar uma função onde eu tenho 2 intervalos e 2 criterios diferentes… Por ex: eu quero que somase (“ou cont.se”) a coluna “produto” for igual a “cadeira” e se a coluna “vendedor” for igual a “benedito”. Nesse caso, o resultado seria o nº de cadeiras que o vendedor benedito conseguiu vender no estado da bahia. Se alguem souber o resultado ficarei muito grato.

Galera.. esquece a questão “que o vendedor conseguiu vender no estado da bahia…”.. O resultado que quero é somente o nº de cadeiras que o vendedor benedito conseguiu vender… to quebrando tanto a cabeça com o excel que ja nao to mais falando nada com nada.. hehhee… vlw

PREZADOS,

Alguém poderia me informar como somar os valores janeiro na situação ativa? P. EX: A soma o valor(C) referente a janeiro(A) ativo (B). Resultado 1100

A B C D F
1 MÊS SITUACAO VALOR JAN ARQUIVO JAN ATIVO
2 JANEIRO ARQUIVO 100
3 ABRIL ARQUIVO 200
4 MAIO ATIVO 300
5 JANEIRO ATIVO 400
6 ABRIL ATIVO 500
7 MAIO ATIVO 600
8 JANEIRO ATIVO 700

Para se fazer o calculo em que tenha que utilizar 2 planilhas e 2 criterios se utiliza a formula

=SOMASE(B24:L27;”NEW14″;F24:F27)+SOMASE(B24:L27;”NEW29″;F24:F27)

Neste caso, eu estou utilizando 2 criterios no mesmo intervalo como se ve

B24:L27 NO 1 SOMASE E TAMBEM NO SEGUNDO SOMASE.

A mesma coisa quando se quer utilizar 2 intervalos em planilhas diferentes e 2 criterios.

Utilizaria-se 4 somase

2 para a primeira planilha para os 2 primeiros critérios
2 para a segunda planilha para os 2 segundos critérios.

preciso da função somase com duas condições

exemplo: vendas

produto quantidade para loja
anel 1 1
anel 2 3
anel 2 3
brinco 1 2

no caso sei fazer a somase para somar somente o que vendi de anel. porem preciso somar somente os aneis que vendi para loja 3. no caso foram 4

no total vendi 5 aneis mas quero saber somente o que vendi para loja 3

desde ja agradeço que me ajudar

Valor Vencimento Situação
15.265,31 19/01/10 PAGO
1.862,89 15/01/10 PAGO
600,00 15/01/10 PAGO
1.980,81 15/01/10 PAGO
2.481,38 12/01/10 PAGO
2.811,92 12/01/10 PAGO
13.256,66 15/01/10 CANCEL.
5.495,51 19/01/10 PAGO
2.871,96 14/01/10 PAGO
30.593,32 14/01/10 PAGO
4.300,00 19/01/10 PAGO
150,00 BAIXA PAGO
8.157,45 29/01/10 PAGO
4.408,16 02/02/10 PAGO

Preciso se alguem puder me ajudar, preciso somar os valores qu estao com o status PAGO e no dia 19/01.

como faco?

Eu tenho uma planilha no excel 2007, com a função somases. numa celula ela soma todos os valores deste cliente, e na outra celula os valores quitados. segue a formula numa celula:=SOMASE(DADOS!$C$5:$C$505;M12;DADOS!$G$5:$G$505)
noa outra celula: Valor quitado=SOMASES(Tabela4[Valor];Tabela4[Cliente];B11;Tabela4[Quitado];”S”), queria usar estes criterios no excel 2003, qual seria os procedimentos.

joaquim

Bom dia.
Estou com problema para utilizar o cont.se, porém a duvida está relacionada a duas condições em dois intervalos. Primeiro tenho um intervalo com 5565 municipios, que tem o código de cada estado. Preciso saber quantos municipios atingiu uma 80% de determinado….. para isso, preciso comprar os codigos do estado dos 5565 municipios e saber se esses municipios atigiral 80% ou mais, consolidando tudo em uma outra planilha por estado. Alguém pode me ajudar?

Boa noite!
estou com um problema em uma planilha, tenho listados varias datas na coluna A, na coluna B o responsável por um determinado gasto, na coluna C o valor total desse gasto.

O que eu necessito:
usar uma formula que some todos os gastos entre o dia 01 e 12 desse mês, 13 a 15, 16 a 27 e 28 a 31.
Me ajudem por favor, estou no desespero fazer isso no “manual” é muito dificil… kkk.

Help!!! Tenho duas planilhas e preciso soma-las, sendo que o total ficará na planilha 2. Como faço? (Obs> Trata-se de planilha orçamentária e para impressão precisei dividi-la em 2) Desde já agradeço a a ajuda. forte abraço. Paula

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

janeiro 2008
D S T Q Q S S
    fev »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Estatísticas

  • 127,869 visitas
Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

%d blogueiros gostam disto: