Excel Somar com condição - Célula Colorida

[ppeterk]

Prezados, bom dia!

 

Tenho uma planilha simples, onde há vários valores nas células. Gostaria de fazer uma soma total, mas que a fórmula somasse, somente, os valores que não estão coloridos. Gostaria de avaliar a possibilidade de criar sem VBA, apenas com fórmula.

 

Observação: À medida que o dia vai passando, eu vou alterando a cor de vermelho para preto. Logo, ele passa a somar esses novas células/valores que estão de preto.

 

É possível? Planilha em anexo.

Soma com condição.xlsx

[Kleber Peters]

Cara, que eu saiba formatação não é critério para o Excel. Ele analisa o conteúdo da célula na fórmula.

 

O que pode ser feito, pra fazer com fórmula, é acrescentar uma coluna indicando qual a cor da célula lateral, tipo na coluna C colocar V para o que for vermelho e P para o que for preto. 

 

Daí, na soma, você usa a fórmula:

 

=SOMASE(C2:C8;"=p";B2:B8)

 

Ele somará de B2 até B8 o que estiver preenchido como P no intervalo C2 até C8.

 

Assim, quando mudar a cor, acrescenta um "P" na coluna ao lado.

 

Não é o mais elegante, mas é como sei fazer usando fórmula. 

[ppeterk]

@Kleber Peters Compreendo, meu caro!

 

Consegui montar a planilha com VBA. No entanto, gostaria que ele considerasse alguns critérios, além da cor, exemplo:

 

Some todos os vermelhos que sejam do fornecedor A

Some todos os vermelhos que sejam do fornecedor B

 

Em anexo, exemplo já com a macro da cor funcionando. Se puderem me ajudar com esse segundo critério de considerar o fornecedor.

 

Att,

 

Soma com condição - VBA.rar

[OreiaG]

No lugar desta linha

  

 If rCell.Font.ColorIndex = rCor Then

 

Coloque esta.
 

  If rCell.Font.ColorIndex = rCor And Application.Caller.Offset(, -1).Value = rCell.Offset(, -1).Value Then

 

[ppeterk]

@OreiaG Perfeito, meu caro!

 

Não estava conseguindo criar esse "And". Ajudou muito!

 

Uma dúvida: Ao invés de fixar a comparação, onde eu mando ele comparar a célula anterior com a coluna anterior, há como eu colocar como um argumento solicitado na função? Ou seja, o usuário vai definir a célula e a coluna que utilizará como critério!

 

If rCell.Font.ColorIndex = rCor And Application.Caller.Offset(, -1).Value = rCell.Offset(, -1).Value Then

 

[OreiaG]

Não entendi. Você pode mostrar exemplos?

[ppeterk]

@OreiaG Bom dia!

 

Desculpe não explicar claramente. A linha que você me encaminhou pra substituir, já define quantas casas à esquerda a função/macro vai voltar - Offset(, -1).Value 

No entanto, eu queria uma função que o usuário escolhesse/definisse qual coluna e qual célula a função vai usar como comparação. Tipo:

 

"Application.Caller.Cells('Definida/escolhida pelo usuário").Value" = rCell.Cells('Definida/escolhida pelo usuário").Value

 

Espero ter explicado corretamente.

[OreiaG]

8 horas atrás, ppeterk disse:

No entanto, eu queria uma função que o usuário escolhesse/definisse qual coluna e qual célula a função vai usar como comparação.

 

Ok, Offset(, -1).Value entendi que nessa expressão você quer que uma variável seja colocada no lugar do -1, sendo que essa variável indicará quantas colunas à esquerda ou à direita a função irá verificar. E seriam duas variáveis, V1 e V2, uma para cada Offset.

A sua ideia é que as variáveis trabalhem dessa forma ou você prefere indicar diretamente as colunas a serem verificadas, ex. H e M ?

 

Resta ainda outra dúvida, porque acima você colocou qual coluna e qual célula que aparentemente é redundante pois se você indica a célula, ex. K25, essa indicação já contém a coluna e a linha, e por isso não precisaria uma variável para a coluna, bastaria a variável com o endereço da célula. Seria isso?

[ppeterk]

@OreiaG, Bom dia!

 

Quanto a sua primeira afirmação, ele está corretíssima. São duas variáveis. A questão, no entanto, é que essas variáveis seriam fornecidas pelo usuário, na hora de montar a fórmula.

Exemplo de como seria o preenchimento da fórmula: 

 

=SOMACOR(A2;M2:M13;"Critério1";"Intervalo de Critério1";"Critério2";"Intervalo de Critério2";VERDADEIRO)

 

Sobre a segunda afirmação, realmente, não precisaria da redundância (Lembrando que, para preencher o "Intervalo de Critério1" e/ou "Intervalo de Critério2", o usuário teria que selecionar a coluna ou conjunto de dados).

 

Anexei uma planilha com exemplos reais.

 

 

 

Soma com condição - VBA.rar

[OreiaG]

3 horas atrás, ppeterk disse:

Anexei uma planilha com exemplos reais.

 

Nessa estrutura da planilha que você anexou as alterações abaixo devem atender.

 

Acrescente as colunas Offset(,-2) ao critério, veja abaixo.

 If rCell.Font.ColorIndex = rCor And _
  Application.Caller.Offset(, -1).Value = rCell.Offset(, -1).Value And _
   Application.Caller.Offset(, -2).Value = rCell.Offset(, -2).Value Then

 

E em C10 coloque a fórmula abaixo e arraste.

=SOMACOR(A$2;M$2:M$13;VERDADEIRO)

 

[ppeterk]

@OreiaG boa tarde, meu caro!

 

Peço perdão, mas acho que eu não consegui explicar ainda.

 

Eu havia conseguido nesse formato:

 

If rCell.Font.ColorIndex = rCor And _
  Application.Caller.Offset(, -1).Value = rCell.Offset(, -1).Value And _
   Application.Caller.Offset(, -2).Value = rCell.Offset(, -2).Value Then

 

Mas esse formato acima, apesar de atender perfeitamente, não é o que procuro:

 

O que eu desejo, na verdade, é que o usuário que vai montar a fórmula defina, na própria fórmula, as informações que serão comparadas.

 

Seria algo, mais ou menos, assim: 

 

=SOMACOR(A2;M2:M13;"Critério1";"Intervalo de Critério1";"Critério2";"Intervalo de Critério2";VERDADEIRO)

 

A sua função define no VBA que, sempre que eu fizer a fórmula, ela vai voltar 1 ou 2 casas, (Offset(, -1) ou Offset(, -2)), mas o meu real desejo é que o usuário, escrevendo a fórmula, escolha quais as células e informações serão comparadas.

 

Novamente, peço desculpas por não conseguir deixar muito claro e, desde já, agradeço!

[OreiaG]

Talvez facilite o entendimento se, como exemplo, você montar essa fórmula =SOMACOR(A2;M2:M13;"Critério1";"Intervalo de Critério1";"Critério2";"Intervalo de Critério2";VERDADEIRO) com base no lay out da planilha que você anexou por último.

Ou seja, como seria a fórmula se o lay fosse o da planilha anexada.

A matriz da busca que seria colocada na fórmula seria M2:M13? Essa quantidade de linhas é constante ou pode variar?

O que seria colocado no lugar das 4 variáveis "Critério1", "Critério1", "Intervalo de Critério1" e "Intervalo de Critério2" ?

 

E também ajudaria se você montasse na planilha outras alternativas de lay possíveis de ocorrer.

Por exemplo, em relação à coluna em que estão as fórmulas, você colocou as letras A, B, C, ... na primeira coluna à esquerda e os textos Recebimento/Pagamento estão na segunda coluna à esquerda. Se essas posições relativas poderão mudar e por isso você quer indicar essas posições variáveis na fórmula, então informe como seria essa indicação, se seria um número inteiro "n" para indicar "n" colunas à esquerda (por exemplo o usuário colocaria o valor 5 na fórmula) ou se você quer indicar direto a coluna (por exemplo coluna K), conforme perguntei em mensagem anterior.

Se você conseguir montar na planilha uma alternativa desse tipo e aí coloque como ficaria a fórmula nessa alternativa.

 

De forma parecida para as posições relativas da outra tabela, em que os valores somados estão na coluna M, e se essa coluna poderá variar então informe como você quer referenciar essa coluna na fórmula.

Se as letras A, B, C, ...que estão na primeira coluna à esquerda (coluna L) podem mudar, então informe como você quer referenciar essa coluna das letras, idem para a coluna com os textos Recebimento/Pagamento.

 

Ou seja, precisamos de exemplos reais para definir as expressões "Critério1", "Critério1", "Intervalo de Critério1" e "Intervalo de Critério2", para em seguida ajustarmos o código de acordo.

 

 

[ppeterk]

@OreiaG, entendi perfeitamente o que você quis dizer!

 

Montei a fórmula na planilha e coloquei em formato de texto pra você entender o que eu quero. 

 

Criei 2 abas, com 2 modelos/exemplos, para que, dessa forma, possa ficar mais claro.

 

Acredito que, baseado nessa fórmula, como você mesmo disse, poderemos ajustar o código.

 

Agradeço a paciência.

 

Soma com condição - VBA.rar

[OreiaG]

2 horas atrás, ppeterk disse:

Criei 2 abas, com 2 modelos/exemplos, para que, dessa forma, possa ficar mais claro.

 

As alterações que eu sugeri no post #10 funcionam corretamente também nesses dois exemplos que você criou.

 

No 1º Exemplo utilize exatamente a fórmula que eu sugeri no post #10  >>> =SOMACOR(A$2;M$2:M$13;VERDADEIRO)

 

E no 2º Exemplo utilize a fórmula e altere somente os parâmetros em vermelho >>> =SOMACOR(A$13;I$2:I$17;VERDADEIRO)

 

obs. no 2º Exemplo os seus resultados esperados em D5:D7 e D9 não estão corretos

 

[ppeterk]

@OreiaG Agradeço sua paciência, meu caro!

 

Suas alterações funcionam mesmo. Mas eu precisava que os parâmetros fossem passado na fórmula, como eu exemplifiquei post #13. Eu preciso que o usuário defina os critérios/parâmetros na hora de digitar a fórmula

 

Não sei se estou conseguindo explicar, mas agradeço sua atenção.