Excel: Com base no valor da célula na coluna A (três valores possíveis) e célula na coluna B (inteiro), determine o valor da célula C

Question

Estou tentando resolver um problema da seguinte forma:

1. Coluna A: um dos três valores possíveis. {maçãs, laranjas, peras}

2. Coluna B: um inteiro.

3. O valor da coluna C deve ser calculado com base nas duas colunas anteriores.

Para a coluna B, é necessário entre um intervalo de números. Por exemplo. (1-19, 20-99). Assim, para maçãs, se o número estiver entre 1 - 19, o valor deve ser 12, se o número estiver entre 20 e 99, o valor deve ser 4, maior que isso, o valor deve ser 18.

Os intervalos e valores a serem enviados são diferentes para cada um dos frutos.

No momento, o melhor que posso fazer é uma longa cadeia de instruções if aninhadas, o que não acho ótimo.

=IF(I74=Reference!$A$2,(IF(AND(H74>=1,H74<=19),Reference!$B$4,IF(AND(H74>=20,H74<=149), Reference!$C$4,IF(H74>=150,Reference!$D$4,"NaN")))),(IF(AND(H74>=1,H74<=19),Reference!$B$8,IF(AND(H74>=19,H74<=99), Reference!$C$8, IF(H74>=100, Reference!$D$8, "NaN")))))

Eu realmente aprecio qualquer ajuda. Obrigado.

    

Answer 1

Você deve ter uma tabela de referência descrevendo a quantidade limite de cada item e classificar a quantidade DESCENDING .

Aqui você vai:

=INDEX($C$2:$C$4, MATCH(1, (F2 = $A$2:$A$4) * (G2 > $B$2:$B$4), 0))

e use CTRL + Desloc + Insira ao invés de Digite para inserir a Fórmula da Matriz.

Explicação

$C$2:$C$4 é o intervalo de referência de preços.

Como temos dois critérios para procurar, use MATCH(1, (criteria1) * (criteria2) * (...), 0) . A fórmula é análoga a MATCH(TRUE, criteria1 AND criteria2 AND ...) .

Então, basicamente, a consulta MATCH para o nome do item na tabela e o limite de quantidade menor que o valor da quantidade.

No meu exemplo, só coloco apples na tabela de referência. Você deve adicionar seu oranges e pears a você mesmo, obviamente.