Fórmulas do Excel com várias condições para fazer referência a uma planilha separada

Question

Estou tentando criar uma fórmula para escolher um valor de uma pasta de trabalho com base em várias condições.

Eu tenho uma planilha com vários preços e valores com desconto, dependendo da quantidade, nível de serviço e período de tempo. Eu estou olhando para fazer uma nova pasta de trabalho que faz referência a essa planilha que quando os valores para três variáveis são inseridos, o Excel escolherá o valor correspondente correto.

Não tenho certeza se devo usar if ou etc

Essencialmente, a lógica é:

Se o Nível de serviço for um (4 opções) E o Comprimento do Contrato for b (4 opções) E a Quantidade for c (5 opções) ENTÃO O preço por mês é x (Célula Referenciada)

Não sei qual é a sintaxe e qual fórmula usar. Qualquer ajuda é muito apreciada!

Estou usando o Excel 2013

Muito obrigado,

Andrew

    

Answer 1

Isso pode ser feito de várias maneiras, dependendo da aparência da sua planilha, de como você está selecionando as opções e assim por diante. Com nada para continuar, e sem respostas para mais esclarecimentos, vou apenas dar-lhe um exemplo de como PODE parecer.
(Note que minhas opções de excel usam ponto-e-vírgula em vez de vírgula, para a maioria das pessoas, código de função nesta imagem deve ser: =VLOOKUP(B10,Table1,2)*C10*D10*(1-VLOOKUP(D10,Table3,2)) )

Digamosquesejaassim.Nãoimportaofatodequeelesestãonamesmaplanilha,issopodeserfacilmentealterado.

OquefizaquiéusarValidaçãodedadosparacriaralgumaslistassuspensasparaselecionarserviço,tamanhoequantidade.Emseguida,adicionouumafórmulapararesumiroresultadodasopçõesselecionadas.

Aquisegueumbreveguiasobrecomofazerisso:

PASSO1
Primeirocrieastabelascomosdados.Elesnãoprecisamestarnasmesas.

PASSO2
Crielistassuspensascomvalidaçãodedados.
Selecioneacélulaquedeverepresentaroníveldeserviçoeváparaaguiadataeselecionevalidaçãodedados.Selecione"lista" e adicione a fonte.

Repitaoprocessoparaasoutrasduascaixasdeseleção.
Observação.Paratabelasdecolunaúnica-comoatabela2nesteexemplo-vocêpodesubstituirointervalodeorigempor=INDIRECT("Table2")

PASSO 3
Adicionando tudo junto.

Essa foi a pergunta original e, nesse caso, é simples adicioná-las juntas.
A ideia é apenas multiplicar o campo de opções como

=B10*C10*D10

Mas isso obviamente não funciona nesse caso.

Primeiro, B10 mostra o nome do serviço, não o preço. Então, usamos =VLOOKUP() para ver qual é o preço do serviço.
Sintaxe para VLOOKUP é =VLOOKUP (value, table, col_index, [range_lookup])
O valor para procurar é em B10, a tabela que queremos verificar é "Tabela1" e a coluna com o resultado que queremos é a coluna 2 (Se não for uma tabela, usando B3: C6 funciona tão bem).
Nós substituímos B10 por VLOOKUP(B10,Table1,2) .
Agora temos =VLOOKUP(B10,Table1,2)*C10*D10 que funciona, mas espere, estamos perdendo alguma coisa!

O desconto.
Eu uso o mesmo método com VLOOKUP() para determinar o desconto da quantidade.

VLOOKUP(D10,Table3,2)

retorna o valor 0,02 neste caso. Mas não podemos multiplicar tudo com 0,02, queremos um desconto de 2%. Então, adicionamos (1 - antes, para inverter o% e obter 0,98.
Agora podemos adicioná-lo à função. E o resultado é:

=VLOOKUP(B10,Table1,2)*C10*D10*(1-VLOOKUP(D10,Table3,2))

Espero que este exemplo o ajude no seu caminho com o seu projeto. Boa sorte!

editar
Ah, quase esqueci. Para se referir a um intervalo, tabela ou célula em outra planilha, basta adicionar o nome da planilha e ! na frente dela. Então B2 na Planilha2 seria Sheet2!B2
Boa sorte!