Este fórum está sendo desativado

[dintmktdb]

Olá pessoal.
Estou com uma demanda pra um cliente meu, no qual me deixou em apuros.

Ele tem uma tabela, no qual está em anexo.

Pergunta:

O que eu preciso é o seguinte:
No anexo, tem a segunda tabela com 3 colunas:
Uma onde tem os meses, outra que tem números de pessoas e na terceira vai a fórmula q estou procurando.

E outra tabela com 4 colunas:
Uma pro nivel, outra com o número de revendedores, outra com o percentual e o final a comissão que ganha.

Vou citar o exemplo, para que possa entender melhor minha dúvida.

Preciso que o Excel me responda, quantos reais que irei receber, qdo na rede tiver 120 pessoas (conforme a tabela 2), tendo em conta os valores da tabela 1.
Assim o excel terá que calcular, em qual nível o 120 se encontra, calcular as comissões dos nivels anteriores, seguido a tabela 1. Por exemplo: O valor de venda é de R$100,00. Então seria (R$ 100 x 4) * 7% + (R$ 100 x 16) * 4% + (R$ 100 x 64) * 3% + (R$ 100 x 120) * 3% = Resultado desejado.
Porém na tabela 1, não existe o número 120, então o Excel teria que encontrar automaticamente esse valor e efetuar os cálculos.

Espero que alguém tenha entendido.
Fico no aguardo.

[Jimmy]

Olá,

Eu não consegui entender a explicação de como funciona a planilha.A planilha tem referências circulares, o que pode indicar erro.
Não entendi onde vai o valor de venda. A coluna quantidade de vendedores tem o valor resultado da multiplicação de um valor de comissão (não entendi a elação entre qtd de revendedores e valor monetário.

Poderia explicar com mais detalhes? Com o que eu consegui entender do problema chutaria que você deve usar a função PROC.

Jimmy San Juan

[dintmktdb]

Olá amigo Jimmy.
Deixa eu tentar explicar melhor.... realmente eu pra explicar tenho uma certa dificuldade...

Na tabela 2, tem um campo vazio (terceira coluna). Ali terá q aparecer um valor monetário, no qual seja o resultado das vendas com a quantidade de vendas da coluna 2. Então teoricamente, na célula C15, viria o 120 x 100 (esse R$100,00 não armazenei em nenhuma célula, apenas "chutei"). Más existe um pequeno problema. Na tabela 1, existe uma relação de níveis, percentuais e qtd de revendedores. Então o resultado deve respeitar isso.. Por exemplo: na celula B15, se refere a quantidade de 120 revendedores, porém na tabela 1 não tem esse número. Então o Excel precisaria encontrar esse número, fazendo os devidos cálculos, até chegar no 120. E assim por diante. Espero poder ter explicado melhor.

Fico no aguardo!

[babdallas]

Realmente sua explicação ficou confusa, mas veja se é o que deseja.

[dintmktdb]

Olá amigos... boa tarde... eu fiz um calculo na calculadora normal, e o primeiro resultado deveria ser 452,00. Talvez se eu explicar como eu fiz o calculo na calculadora fica mais facil a explicação. Vamos lá:
Na célula B15 tem a quantidade de pessoas que preciso saber qto vai gerar de comissão. Então eu sei que o primeiro resultado se refere a 120 pessoas. Então olhando na tabela 1, eu sei que 120 pessoas, ultrapassa o nível 1, 2 e 3, porém não chega no 4. Então o que eu fiz na calculadora? Eu calculei 100 (preço do produto, na celula C13), multipliquei por 4 (primeiro nível) e desse resultado, peguei a comissão de 7% (celula C2). Assim guardei o número, e somei com a mesma conta, porém com o nivel 2 (16 pessoas), e o mesmo fiz no nivel 3. Porém no nível 4 que é 256 pessoas, eu subtrai do nível 3. Então 256 - 64 = 192. Peguei esse numero e multipliquei pelo C13 e fiz o percentual de 3% (C4), aí eu somei o resultado de todos os niveis anteriores com o valor desse ultimo nível, pra chegar as 120 pessoas. E deu o total de R$452,00. Porém não sei fazer isso no excel.
Espero q eu tenha conseguido explicar melhor. Agradeço mais uma vez a grande ajuda de vcs.

[babdallas]

Veja se é isso (a fórmula é matricial, logo aperte Ctrl+Shift+Enter):

Código: Selecionar todos

=$C$13*(SOMARPRODUTO(($B$2:$B$9<B15)*$B$2:$B$9*$C$2:$C$9)+(B15-PROCV(B15;$B$2:$C$9;1;1))*PROCV(B15;$B$2:$C$9;2;1))

[Jimmy]

Ufaaa!!! Agora eu entendi. Bem melhor!!

Gostaria de confirmar algo. Dos 120 iniciais, você considerou 4 deles a um percentual de 7%, depois considerou outros 16 com 4%, e ainda outros 64 com um 3%. Quando calculou o que restou para o grupo 4, você considerou 120 = 64 = 192. Não conheço seu sistema, mas pelo muito pouco que entendi me ocorreu que o cálculo correto poderia ser: 120 - 64 - 16 - 4 = 36 para o grupo 4, e não 192. Note que 192 é um número maior que o próprio 120.

Mesmo que sua descrição do cálculo esteja correta, a resposta deveria ser 668, e não os 454 que você relatou. Acho que isso merece uma revisão de sua parte.

Vou trabalhar numa macro na forma que você descreveu. Se tiver que adaptar, adaptamos.

Por favor, esclareça as dúvidas que relatei acima.

[Jimmy]

Olá,

Insira na célula D15 a seguinte fórmula: =CalcComissao(C13;B15;B2:B9;C2:C9)
onde:
C13 é o preço do produto
B15 é a quantidade de pessoas
B2:B9 é a faixa onde estão as quantidades de revendedores
C2:C9 é a faixa onde estão as comissões.

Na célula vai apenas a chamas, assim como quando você usa uma função nativa do Excel, como SOMA ou MEDIA.

O que está por trás da função vem a seguir.

A função abaixo reproduz o cálculo conforme você descreveu, mas obtém um resultado diferente de 454 para as mesmas condições. Aguardo a sua revisão disso.

Coloque-a em um módulo:
- Tecle ALT-F11. (Abre-se a tela de desenvolvimento)
- Clique no menu INSERIR / MÓDULO
- Copie e cole o código abaixo na grande janela vazia (uma que está escrito (Geral) e (Declaração) na parte de cima.)

Código: Selecionar todos

Function CalcComissao(ValVendas As Variant, QtdVendas, QtdRevend As Range, Percentua As Range)
    Erro = ""
    If QtdRevend.Rows.Count <> Percentua.Rows.Count Or _
       QtdRevend.Columns.Count <> Percentua.Columns.Count Then
       Erro = "A quantida de colunas e linhas das faixas de ""Quantidade de vendedores"" " & _
       "e de ""Percentual"" devem ser as mesmas."
    End If
       
    If (QtdRevend.Rows.Count <> 1 And QtdRevend.Columns.Count <> 1) Or _
       (Percentua.Rows.Count <> 1 And Percentua.Columns.Count <> 1) Then
       Erro = "As faixas de ""Quantidade de vendedores"" e de ""Percentual"" " & _
              "devem ter apenas uma dimensão (ou 1 linha, ou 1 coluna)."
    End If
       
    Aux1 = ""
    If ValVendas.Count <> 1 Then Aux1 = "O ""Valor De Venda"
    If ValVendas.Count <> 1 Then Aux1 = "A ""Quantidade Vendida"
    If Aux1 <> "" Then Erro = Aux1 & """ deve ser apenas 1 célula, e não uma faixa."
    
    If Erro <> "" Then
        MsgBox Erro
        CalcComissao = "ERRO"
    Else
        Total = 0: Anterior = 0
        For Each Cel1 In QtdRevend
            Lin = Cel1.Row - QtdRevend.Row + 1
            Col = Cel1.Column - QtdRevend.Column + 1
            Per = Percentua.Cells(Lin, Col)
            If QtdVendas >= Cel1.Value Then Desconta = 0 _
                                       Else Desconta = Anterior
            Total = Total + ValVendas * Per * (Cel1.Value - Desconta)
            If QtdVendas <= Cel1.Value Then Exit For
            Anterior = Cel1.Value
        Next
        CalcComissao = Total
    End If
End Function

Ela faz algumas consistências (verificações) no início, verificando se os tamanhos das faixas de Quantidade de Revendedores e Percentuais é o mesmo, pois tem que ser, ou se as faixas possuem mais de 1 coluna. Se houver erro, uma mensagem será dada. Caso hajam mais regras a verificar, podemos incluir.

Jimmy San Juan