Como combinar várias instruções if no Excel

Question

Estou tentando combinar várias instruções If (mais de 64) em uma célula. Eu não sou muito fluente no Excel, então posso até estar fazendo isso errado.

O que estou tentando realizar é isso:

Se [(Cell 1 = X) AND (Cell 2 = Y) then Cell 3 = Z]
ou
Se [(Cell 1 = X) AND (Cell 2 = B) the Cell 3 = C]

Eu tenho 100 possibilidades para a célula 1 e 2 possibilidades para a célula 2. A combinação das células 1 e 2 produzirá uma resposta específica na célula 3.

Espero que isso faça sentido.

Até agora eu tentei a seguinte instrução If, que funciona, mas é muito longa (maior que 64).

=IF(AND(B6="AF001.1",C6="US"),"5.5",
IF(AND(B6="AF010",C6="US"),"17.2",
IF(AND(B6="Z284.1",C6="US"),"8.5",
IF(AND(B6="Z284.2",C6="US"),"17.8",  
IF(AND(B6="AF016.1",C6="US"),"7.5",
IF(AND(B6="AF016.2",C6="US"),"7.5",
IF(AND(B6="AF016.3",C6="US"),"7.5",
IF(AND(B6="AF016.4",C6="US"),"7.5",
IF(AND(B6="AF016.5",C6="US"),"7.5",
IF(AND(B6="AF016.6",C6="US"),"7.5",
IF(AND(B6="AF016.7",C6="US"),"7.5",
IF(AND(B6="AF016.8",C6="US"),"7.5",
IF(AND(B6="AF016.9",C6="US"),"7.5",
IF(AND(B6="AF016.10",C6="US"),"7.5",
IF(AND(B6="AF016.11",C6="US"),"7.5",
IF(AND(B6="AF016.12",C6="US"),"7.5",
IF(AND(B6="AF016.13",C6="US"),"7.5",
IF(AND(B6="ST001",C6="US"),"6",
IF(AND(B6="ST002.2",C6="ALL"),"36.5",
IF(AND(B6="ST002.3",C6="ALL"),"36.5",
IF(AND(B6="ST002.4",C6="ALL"),"36.5",
IF(AND(B6="ST002.5",C6="ALL"),"36.5",
IF(AND(B6="ST002.6",C6="ALL"),"36.5",
IF(AND(B6="ST002.7",C6="ALL"),"36.5",
IF(AND(B6="ST002.8",C6="ALL"),"36.5",
IF(AND(B6="ST002.9",C6="ALL"),"36.5",
IF(AND(B6="ST002.10",C6="ALL"),"36.5",
IF(AND(B6="ST002.11",C6="ALL"),"36.5",
IF(AND(B6="ST002.12",C6="ALL"),"36.5",
IF(AND(B6="ST003.1",C6="ALL"),"36.5",
IF(AND(B6="ST003.2",C6="ALL"),"36.5",
IF(AND(B6="ST003.3",C6="ALL"),"36.5",
IF(AND(B6="ST003.4",C6="ALL"),"36.5",
IF(AND(B6="ST003.5",C6="ALL"),"36.5",
IF(AND(B6="ST003.6",C6="ALL"),"36.5",
IF(AND(B6="ST003.7",C6="ALL"),"36.5",
IF(AND(B6="ST003.8",C6="ALL"),"36.5",
IF(AND(B6="ST003.9",C6="ALL"),"36.5",
IF(AND(B6="ST003.10",C6="ALL"),"36.5",
IF(AND(B6="ST003.11",C6="ALL"),"36.5",
IF(AND(B6="ST003.12",C6="ALL"),"36.5",
IF(AND(B6="ST004.1",C6="US"),"11.5",
IF(AND(B6="ST004.2",C6="US"),"11.5",
IF(AND(B6="ST004.3",C6="US"),"11.5",
IF(AND(B6="ST004.4",C6="US"),"11.5",
IF(AND(B6="ST005",C6="ALL"),"21.5",
IF(AND(B6="ST006.1",C6="ALL"),"80",
IF(AND(B6="ST006.2",C6="ALL"),"80",
IF(AND(B6="ST006.3",C6="ALL"),"80",
IF(AND(B6="ST006.4",C6="ALL"),"80",
IF(AND(B6="ST006.5",C6="ALL"),"80",
IF(AND(B6="ST006.6",C6="ALL"),"80",
IF(AND(B6="ST006.7",C6="ALL"),"80",
IF(AND(B6="ST006.8",C6="ALL"),"80",
IF(AND(B6="ST006.9",C6="ALL"),"80",
IF(AND(B6="ST006.10",C6="ALL"),"80",
IF(AND(B6="ST006.11",C6="ALL"),"80",
IF(AND(B6="ST006.12",C6="ALL"),"80",
IF(AND(B6="ST006.13",C6="ALL"),"80",
IF(AND(B6="ST006.14",C6="ALL"),"80",
IF(AND(B6="ST006.15",C6="ALL"),"80",
IF(AND(B6="ST006.16",C6="ALL"),"80",
IF(AND(B6="ST006.17",C6="ALL"),"80",
IF(AND(B6="ST006.18",C6="ALL"),"80",
IF(AND(B6="ST007",C6="ALL"),"19",
IF(AND(B6="ST008.1",C6="US"),"12.5",
IF(AND(B6="ST008.2",C6="US"),"12.5",
IF(AND(B6="ST008.3",C6="US"),"12.5",
IF(AND(B6="ST008.4",C6="US"),"12.5",
IF(AND(B6="ST009.1",C6="ALL"),"108",
IF(AND(B6="ST009.2",C6="ALL"),"108",
IF(AND(B6="ST009.3",C6="ALL"),"108",
IF(AND(B6="ST009.4",C6="ALL"),"108",
IF(AND(B6="ST009.5",C6="ALL"),"108",
IF(AND(B6="ST009.6",C6="ALL"),"108",
IF(AND(B6="ST009.7",C6="ALL"),"108",
IF(AND(B6="ST009.8",C6="ALL"),"108",
IF(AND(B6="ST009.9",C6="ALL"),"108",
IF(AND(B6="ST009.10",C6="ALL"),"108",
IF(AND(B6="ST009.11",C6="ALL"),"108",
IF(AND(B6="ST009.12",C6="ALL"),"108",
IF(AND(B6="ST009.13",C6="ALL"),"108",
IF(AND(B6="ST009.14",C6="ALL"),"108",
IF(AND(B6="ST009.15",C6="ALL"),"108",
IF(AND(B6="ST009.16",C6="ALL"),"108",
IF(AND(B6="ST009.17",C6="ALL"),"108",
IF(AND(B6="ST009.18",C6="ALL"),"108",
IF(AND(B6="ST009.19",C6="ALL"),"108",
IF(AND(B6="ST009.20",C6="ALL"),"108",
IF(AND(B6="ST009.21",C6="ALL"),"108",
IF(AND(B6="ST009.22",C6="ALL"),"108",
IF(AND(B6="ST009.23",C6="ALL"),"108",
IF(AND(B6="ST009.24",C6="ALL"),"108",
IF(AND(B6="ST009.25",C6="ALL"),"108",
IF(AND(B6="ST009.26",C6="ALL"),"108",
IF(AND(B6="ST009.27",C6="ALL"),"108",
IF(AND(B6="ST009.28",C6="ALL"),"108",
IF(AND(B6="ST010",C6="US"),"5.3",
IF(AND(B6="ST010",C6="ALL"),"6.8",
IF(AND(B6="ST008.1",C6="ALL"),"14",
IF(AND(B6="ST008.2",C6="ALL"),"14",
IF(AND(B6="ST008.3",C6="ALL"),"14",
IF(AND(B6="ST008.4",C6="ALL"),"14",
IF(AND(B6="ST004.1",C6="ALL"),"13",
IF(AND(B6="ST004.2",C6="ALL"),"13",
IF(AND(B6="ST004.3",C6="ALL"),"13",
IF(AND(B6="ST004.4",C6="ALL"),"13",
IF(AND(B6="ST001",C6="ALL"),"7.5",
IF(AND(B6="AF016.1",C6="ALL"),"9",
IF(AND(B6="AF016.2",C6="ALL"),"9",
IF(AND(B6="AF016.3",C6="ALL"),"9",
IF(AND(B6="AF016.4",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.7",C6="ALL"),"9",
IF(AND(B6="AF016.8",C6="ALL"),"9",
IF(AND(B6="AF016.9",C6="ALL"),"9",
IF(AND(B6="AF016.10",C6="ALL"),"9",
IF(AND(B6="AF016.11",C6="ALL"),"9",
IF(AND(B6="AF016.12",C6="ALL"),"9",
IF(AND(B6="AF016.13",C6="ALL"),"9",
IF(AND(B6="Z284.1",C6="ALL"),"9.5",
IF(AND(B6="AF001.1",C6="ALL"),"7"
)))))))))))))))))))))))))))))))

Eu gostaria de receber ajuda. Claramente eu preciso disso, lol!

    

Answer 1

Seria muito melhor criar uma tabela de pesquisa com três colunas:

1. The 100 possibilities for Cell 1.
2. The 100 answers for Cell 1 and possibility 1 of Cell 2.
3. The 100 answers for Cell 1 and possibility 2 of Cell 2.

Em seguida, use VLOOKUP () para pesquisar a célula 1 e retornar a resposta da coluna 2 ou coluna 3 da tabela de pesquisa, dependendo do valor da célula 2.

Veja um exemplo com apenas 10 linhas:

AfunçãoVLOOKUP()localizaovalordacélula1nacoluna1databeladepesquisaeretornaovalorcorrespondentedacoluna2oucoluna3,usandoumacláusulaIF()queverificaovalordacélula2paradecidiracoluna.

AfórmulaemB6é:

=VLOOKUP(A2,D2:F11,IF(B2="Value 1",2,3),FALSE)

Você pode ocultar a tabela de pesquisa ou movê-la para outra planilha, se necessário.

Aqui estão mais informações sobre VLOOKUP () e IF () . Você também deve verificar a Ajuda do Excel.

    

Answer 2

Primeiro, deixe-me segundo sentimento de Brandon Ibbotson: Eu sinto muito que você teve que colocar para fora o esforço para chegar a algo que monstruoso.

Agora, você precisa remover sua lógica de negócios de sua declaração if e colocá-la em algum lugar de manutenção e, em seguida, usar uma função de pesquisa (relativamente) simples para recuperar o valor correto. Sério, o que você fará se os valores de ST008.6 mudarem?

O que você deve fazer é:

Em uma nova planilha, configure uma tabela. Coloque seus valores B do lado (na coluna A, iniciando na linha 2) e seus valores C na parte superior da linha 1. Preencha os valores de destino nos pontos de cruz. Classifique-o pela coluna A.

         US    CA    ALL
AF001.1  5.5         7
AF010   17.2
...etc...
Z284.1   8.5         9.5

Configure uma segunda tabela que converta seus rótulos de primeira linha em números de coluna:

US      2
CA      3
ALL     4

Observe que os números de peça, ou o que quer que eles sejam, são da coluna 1; nós não precisamos de uma definição para isso nesta tabela de pesquisa.

Em terceiro lugar, defina nomes para essas duas tabelas. Isso não é estritamente necessário, mas torna nossas fórmulas mais fáceis de escrever e as tabelas mais fáceis de reutilizar, se você precisar delas para outras fórmulas posteriormente.

• realce a primeira tabela (excluindo a linha superior)
• clique com o botão direito do mouse e escolha "Definir nome ..."
• dê um nome razoável (vou chamá-lo de XREF para este exemplo)
• faça o mesmo para a segunda tabela (eu chamo de CLKP)

Por fim, na sua planilha primária, substitua a declaração horrível por uma ou duas vlookup:

=VLOOKUP(B6,XREF,VLOOKUP(C6,CLKP,2,FALSE),FALSE)

Isso é encontrar o valor na célula B6 onde quer que exista na primeira coluna do nosso intervalo XREF e, em seguida, converter o valor em C6 para o número da coluna que precisamos e, em seguida, selecionar o valor desse item nessa coluna. na célula atual.