Guru do Excel e Power BI

Bom dia,

Na planilha em anexo, na guia Marlene, a célula K13 possui uma fórmula com várias funções se(e), onde faz algumas comparações com outras células. Até aqui, está funcionando perfeitamente. Entretanto, agora preciso fazer uma outra condição. Preciso que, caso a célula atenda as condições previstas na atual fórmula e se preencha como Renovação-1 ou Renovação-2, que se verifique uma outra condição que é: se a célula B13 form maior ou igual à G7 e menor ou igual à I7, atenda às condições da fórmula. Caso contrário, o valor será 0,00. Como posso efetuar isso? Lembrando que todas as referências são, na verdade, para os intervalos compreendidos entre ?13:?62, conforme estão nas fórmulas originais.

Detalhando um pouco mais o problema:

A célula K13 analisa primeiramente o conteúdo da célula F13.
Se F13 for “Novo”, analisa o conteúdo da célula E13, e traz o valor correspondente à informação desta no intervalo N13:S13.

SE(E(F13="Novo";E13="2011");N13;SE(E(F13="Novo";E13="2012");O13;SE(E(F13="Novo";E13="2013");P13;SE(E(F13="Novo";E13="2014");Q13;SE(E(F13="Novo";E13="2015");R13;SE(E(F13="Novo";E13="2017");S13


Se F13 for “Migração”, analisa o conteúdo da célula E13, e traz o valor correspondente à informação desta no intervalo N13:S13, dividindo este valor por 2.

SE(E(F13="Migração";E13="2011");N13/2;SE(E(F13="Migração";E13="2012");O13/2;SE(E(F13="Migração";E13="2013");P13/2;SE(E(F13="Migração";E13="2014");Q13/2;SE(E(F13="Migração";E13="2015");R13/2;SE(E(F13="Migração";E13="2017");S13/2

Se F13 for “Renovação-2”, traz o valor de S10, dividido por 2.

SE(E(D13=12;F13="Renovação-2");$S$10/2

Se F13 for “Renovação-1”, traz o valor de S10

SE(E(D13=12;F13="Renovação-1");$S$10

Por fim, se a célula F13 não tiver nenhuma informação, deixa a célula K13 em branco.

SE(F13="";""


Até aqui, a planilha está funcionando perfeitamente. A partir daqui é que eu necessito acrescentar uma outra condição.

Se F13 for “Renovação-1”, e B13 esteja entre o intervalo de datas compreendidos entre as células G7 e I7, traz o valor de S10.
Se F13 for “Renovação-1” e B13 esteja fora do intervalo de datas compreendidos entre as células G7 e I7, a célula K13 deverá ser preenchida com R$ 0,00.

Obs.: O intervalor de datas das células G7 e I7 levará em consideração a “inclusive” a data informada, ou seja, a “>=G7” e “<=I7”.

Espero ter conseguido me fazer entender. Caso haja necessidade de maiores detalhes, por gentileza me informem.

Grato e no aguardo,

Nilton

Bom dia NiltonASantos,

Talvez isso possa te ajudar.

Olá Marcelo,

Te agradeço imensamente pela resposta.

É quase isso. Na verdade, creio que eu acabei esquecendo de citar uma condição. Se o conteúdo informado em E13 for inferior à 2017, a fórmula seria essa:

=SE(E(E13="Novo";D13="2011");M13;SE(E(E13="Novo";D13="2012");N13;SE(E(E13="Novo";D13="2013");O13;SE(E(E13="Novo";D13="2014");P13;SE(E(E13="Novo";D13="2015");Q13;SE(E(E13="Migração";D13="2011");M13/2;SE(E(E13="Migração";D13="2012");N13/2;SE(E(E13="Migração";D13="2013");O13/2;SE(E(E13="Migração";D13="2014");P13/2;SE(E(E13="Migração";D13="2015");Q13/2;SE(E(C13=12;E13="Renovação-1");$R$10;SE(E(C13=12;E13="Renovação-2");$R$10/2;SE(E13="";"")))))))))))))

visto que não levaria em consideração o intervalo de datas. Apenas se o conteúdo informado em E13 for 2017 o excel leva em consideração o intervalo de datas.

Explicando a prática daqui: se o plano vendido for anterior à 2017, ele não precisará ser finalizado no intervalo de data estabelecido. Entretanto, se o plano vendido for 2017 e sua finalização se dê fora do intervalo de data informada, a recepcionista não receberá comissão por esse plano.

Espero ter clareado um pouco mais e me desculpe pelo esquecimento da condição.

Abs.

Nilton

Boa tarde.

Se entendi certo você pode usar essa formula:
=SE(F13="Novo";SE(E13="2011";N13;SE(E13="2012";O13;SE(E13="2013";P13;SE(E13="2014";Q13;SE(E13="2015";R13;SE(E13="2017";S13))))));SE(F13="Migração";SE(E13="2011";N13/2;SE(E13="2012";O13/2;SE(E13="2013";P13/2;SE(E13="2014";Q13/2;SE(E13="2015";R13/2;SE(E13="2017";S13/2))))));SE(F13="Renovação-1";SE(VALOR(E13)>=2017;SE(E(D13=12;B13>=$G$7;B13<=$I$7);$S$10;0);$S$10);SE(E(D13=12;F13="Renovação-2");$S$10/2;SE(F13="";"")))))

Oi Macedo.

Primeiro, peço-lhe desculpas pela digitação incorreta de seu nome.

A fórmula sugerida por você está fazendo com que TODOS os planos intitulados "Migração-1" na célula F13 sejam calculados com o valor de 2017, quando o correto seria "apenas" os planos 2017 sofrerem a influência do intervalo de datas.

Na verdade, a fórmula que eu estou usando atualmente

=SE(E(E13="Novo";D13="2011");M13;SE(E(E13="Novo";D13="2012");N13;SE(E(E13="Novo";D13="2013");O13;SE(E(E13="Novo";D13="2014");P13;SE(E(E13="Novo";D13="2015");Q13;SE(E(E13="Migração";D13="2011");M13/2;SE(E(E13="Migração";D13="2012");N13/2;SE(E(E13="Migração";D13="2013");O13/2;SE(E(E13="Migração";D13="2014");P13/2;SE(E(E13="Migração";D13="2015");Q13/2;SE(E(C13=12;E13="Renovação-1");$R$10;SE(E(C13=12;E13="Renovação-2");$R$10/2;SE(E13="";"")))))))))))))

estava funcionando perfeitamente, pois não havia a necessidade de consultar este intervalo de datas. Agora, em 2017, foi acrescentado esta condição. Então, para os casos (e somente para estes) em que o Plano for "Renovação-1" e 2017, deve ser consultado se a data de Emissão está no intervalo compreendido entre as células G7 e I7. Se estiver no intervalo, preencha com o valor da célula S10. Se estiver fora do intervalo, o valor será R$ 0,00. Entretanto, se o preenchimento da célula E13 seja inferior à 2017, deve seguir as regras impostas na fórmula original.

Att.

Nilton

Boa noite Nilton,

Acho que agora vai:
=SE(E(F13="Novo";E13="2011");N13;SE(E(F13="Novo";E13="2012");O13;SE(E(F13="Novo";E13="2013");P13;SE(E(F13="Novo";E13="2014");Q13;SE(E(F13="Novo";E13="2015");R13;SE(E(F13="Novo";E13="2017");S13;SE(E(F13="Migração";E13="2011");N13/2;SE(E(F13="Migração";E13="2012");O13/2;SE(E(F13="Migração";E13="2013");P13/2;SE(E(F13="Migração";E13="2014");Q13/2;SE(E(F13="Migração";E13="2015");R13/2;SE(E(F13="Migração";E13="2017");S13/2;SE(E(E13="2017";B13>=$G$7;B13<=$I$7;D13=12;F13="Renovação-1");$S$10;SE(E(D13=12;F13="Renovação-2");$S$10/2;SE(F13="";"";SE(E(D13=12;F13="Renovação-1");$S$10;0))))))))))))))))

Oi Macedo...

Ainda não. Dessa vez, além de estar ignorando o intervalo de datas, também está ignorando o "Tipo" (2012, 2015, 2017...)Será que está faltando alguma explicação de minha parte para eu poder me fazer entender melhor? Se for isso, por favor me avise como eu posso exemplificar melhor.

Abs.

Nilton

Boa noite Nilton,

Reparei que a formula que você enviou não bate com as colunas da planilha que você anexou.
Espécie esta na coluna F na planilha, mas na formula esta na E.
=SE(E(E13="Novo";D13="2011");M13;SE(E(E13="Novo";D13="2012");N13;SE(E(E13="Novo";D13="2013");O13;SE(E(E13="Novo";D13="2014");P13;SE(E(E13="Novo";D13="2015");Q13;SE(E(E13="Migração";D13="2011");M13/2;SE(E(E13="Migração";D13="2012");N13/2;SE(E(E13="Migração";D13="2013");O13/2;SE(E(E13="Migração";D13="2014");P13/2;SE(E(E13="Migração";D13="2015");Q13/2;SE(E(C13=12;E13="Renovação-1");$R$10;SE(E(C13=12;E13="Renovação-2");$R$10/2;SE(E13="";"")))))))))))))

Nilton,

veja se atende.

Macedo, realmente, eu havia (infelizmente) enviado a planilha com os endereços errados. Só percebi isso quando você falou. A solução que você enviou é quase isso. Entretanto, ainda está se perdendo em alguns itens. Vou tentar abaixo criar uma lista de possibilidades para ver se fica ainda mais claro.

Exemplo:
Se Espécie for Novo, Tipo for 2015, o valor da Comissão será R$ 29,42 (R13).
Se Espécie for Renovação-1, Tipo for 2013, o valor da Comissão será R$ 26,75 (P13).
Se Espécie for Novo, Tipo for 2017, o valor da Comissão será R$ 29,00 ([/b]S13[/b]).

Entretanto (e aqui começa o problema), se Espécie for Renovação-1, Tipo for 2017, analisa se:
1) A Emissão está no intervalo entre as datas de g7 e i7, o valor da Comissão será R$ 40,00 (S10);
2) A Emissão está "FORA" do intervalo entre as datas de g7 e i7, o valor da Comissão será R$ 0,00;

Como vê, a planilha só deverá analisar a condição "DATA" se Espécie for Renovação-1 "E" Tipo for 2017


Por fim, depois da fórmula estar resolvida (se é funcional), você poderia me explicá-la? Você colocou alguns elementos ali que me deixaram confuso. Vi também que você "desmesclou" as células K13:L13. Elas realmente não podem ficar mescladas?

Por enquanto é isso.

Te agradeço mais uma vez por estar resolvendo esta "bucha".

Abs.

Nilton

Nilson, agora esta mais detalhado, da pra compreender melhor.
Esta ultima planilha quem lhe enviou foi o baddallas, ele usou uma formula matricial (muito melhor que esse montão de SEs e Es ), bom, resumindo: Formula matricial é boa, mas não pode usar em células mescladas e o mais importante: Depois de digitar a formula não pode apenas teclar ENTER, você tem que teclar CTRL + SHIFT + ENTER Se não fizer isso ela não funciona.

Macedo, Baddallas.

Infelizmente, a planilha ainda apresenta resultado errado do esperado. Já refiz e revi todos os endereços aqui, usei CTRL+SHIFT+ENTER e nada. Não consigo ver o que estou fazendo errado.
Estou enviando em anexo uma imagem de como ela se comporta aqui.

Abs.

Nilton

Você poderia explicar em qual célula está o erro e qual seria o resultado esperado? Deve estar faltando pouca coisa para acertarmos.

Certo...

Bem, com base no que expliquei no exemplo anterior, o resultado da célula K13 da imagem deveria ser R$ 29,00, já que a Espécie é Novo e, portanto, não leva em consideração o intervalo de datas. O resultado da imagem (R$ 0,00) estaria certo apenas se, nesse caso, a espécie fosse Renovação-1

Vou pedir desculpas antecipadas e repetir abaixo o exemplo onde tentei explicar o que preciso:

Exemplo:
Se Espécie for Novo, Tipo for 2015, o valor da Comissão será R$ 29,42 (R13).
Se Espécie for Renovação-1, Tipo for 2013, o valor da Comissão será R$ 26,75 (P13).
Se Espécie for Novo, Tipo for 2017, o valor da Comissão será R$ 29,00 ([/b]S13[/b]).

Entretanto (e aqui começa o problema), se Espécie for Renovação-1, Tipo for 2017, analisa se:
1) A Emissão está no intervalo entre as datas de g7 e i7, o valor da Comissão será R$ 40,00 (S10);
2) A Emissão está "FORA" do intervalo entre as datas de g7 e i7, o valor da Comissão será R$ 0,00;

Como vê, a planilha só deverá analisar a condição "DATA" se Espécie for Renovação-1 "E" Tipo for 2017

Se a planilha está funcionando aí para você, com certeza estou fazendo algo errado aqui, mas não consigo visualizar o que seja...

Att..
Nilton

Bom dia...

Depois de, literalmente, virar a noite em cima da planilha e não encontrar onde estou errando, resolvi enviar para vocês uma planilha onde eu, propositalmente, em algumas células ignorei a fórmula e forcei a entrada de valores manualmente. Também grifei em amarelo as linhas onde os planos são do Tipo 2017, na tentativa de mostrar onde a condição de data interfere e onde não interfere. Em casos onde o Tipo foi informado como 2016, aparece como "FALSO", sendo que é exatamente assim que deverá aparecer, pois não existem planos 2016.

Como eu disse anteriormente, essa planilha não mais possui as fórmulas corretas, pois eu queria lhes mostrar quais os reais valores que deveriam aparecer. A linha em branco em cada conjunto foi apenas para separa e dar uma melhor visualização. Na prática, todas as linhas são preenchidas.

Mais uma vez, muito obrigado pela ajuda.

Att.

Nilton

Bom dia Nilton,
Estamos quase lá. Fiz algumas alterações na formula do baddallas.

Bem...

Finalmente, a planilha está funcionando como deveria. Fiz todos os testes e as fórmulas estão calculando corretamente.

Entretanto... hoje, enquanto efetuava os testes, recebi um e-mail comunicando de uma mudança nas regras de venda dos planos. E esta mudança de regra me faz lhes pedir mais uma ajuda.

Até então, a condição do intervalo de data interferiria apenas em casos em que o plano fosse Tipo 2017 e a Espécie fosse Renovação-1. Entretanto, a regra agora foi alterada para que a condição do intervalo de datas interfira em TODOS os tipos, e não só 2017. A Espécie continua sendo apenas Renovação-1.

Essa mudança é muito complicada?

Att.

Nilton

Boa noite Nilton,
Esta modificação deve resolver.
=SE(F13=$N$7;SOMA(--(E13=TEXTO($N$12:$S$12;"#"))*(N13:S13));SE(F13=$N$8;SOMA(--(E13=TEXTO($N$12:$S$12;"#"))*(N13:S13))/2;SE(F13=$N$10;$S$10/2;SE(OU(E13=TEXTO($N$12:$S$12;"#"));SE(E(F13=$N$9;$B13>=$G$7;$B13<=$I$7);$S$10;0);SE(F13="";"";$S$10)))))

Obs.: Não esquecer do CRTL + SHIFT + ENTER

Bom dia,

Caros Macedo e Baddallas. Não os respondi antes por problemas de saúde. Mas vamos aos fatos.

A planilhas funcionou perfeitamente. Agradeço aos dois pela dedicação e respostas. Gostaria apenas de solicitar, se possível, dois esclarecimentos. O primeiro é qual a finalidade da fórmula que encontra-se em N1, visto que a mesma parece não refletir em nenhuma outra célula. E o segundo esclarecimento, poderiam me explicar a fórmula para que, em um futuro, eu possa adaptá-la em alguma necessidade?

Mais uma vez grato,

Nilton

Vamos lá:
-) A fórmula de N1 pelo que entendi não está servindo pra nada mesmo.
- A fórmula realizada faz o seguinte:

1) compara se a espécie digitada na coluna F é igual a Novo (que está na célula N7).
1.1) Caso verdadeiro, compara o ano (coluna E que está em formato de texto) com os anos nas células N12 a S12 (usa a função texto para transformar em texto. Isto retorna uma matriz de 0 ou 1, ou seja, 0 quando o ano não é o correspondente e 1 para o ano correspondente). Esta matriz é multiplicada pela matriz de valores de comissão, retornando uma matriz com a comissão do ano em questão. Com isso, basta somar que encontra-se a comissão desejada.
2) Caso falso, verifica se se a espécie digitada na coluna F é igual a MIgração (que está na célula N8)
2.1) Caso verdadeiro, faz o mesmo procedimento de 1.1. A única diferença é que as comissões foram divididas por 2.
3) Caso falso, verifica se se a espécie digitada na coluna F é igual a Renovação-2 (que está na célula N10).
3.1) Caso verdadeiro, a comissão é o valor de S10 dividido por 2.
4) Caso falso, verifica se o ano corresponde a alguma das células N12 a S12 usando a função OU.
4.1) Caso verdadeiro, verifica se atende as seguintes condições (espécie igual a Renovação-1 (em N9) e as datas atenderem os limites estabelecidos em G7 e I7) ==> se atender, comissão igual ao valor de S10.
5) Caso falso, verifica se o a espécie digitada é vazia. Se sim, retorna vazio, caso contrário retorna a comissão de S10.

Não sei se fui claro o suficiente. É difícil explicar esta fórmula escrevendo.