Excel: como simplificar uma crescente fórmula aninhada IF?

Question

Eu tenho um número de fórmulas IF AND, e elas atingirão o limite. Qual é a melhor solução de reposição?

EG.

IF(AND(AC9=1,AC4=(AB4+7)),1&"b",
IF(AND(AC9=2,AC4=(AB4+7)),2&"b",
IF(AND(AC9=3,AC4=(AB4+7)),3&"b",
IF(AND(AC9=4,AC4=(AB4+7)),4&"b",
IF(AND(AC9=5,AC4=(AB4+7)),5&"b",)))))

ou mais simples

IF(AB18=1,(100%+AB17)*($D$271*AB16),
IF(AB18=2,(100%+AB17)*($E$271*AB16),
IF(AB18=3,(100%+AB17)*($F$271*AB16),
IF(AB18=4,(100%+AB17)*($G$271*AB16),
IF(AB18=5,(100%+AB17)*($H$271*AB16),)))))

Todas as variáveis são mantidas em várias linhas A linha superior é numerada como 1,2,3,4, etc.

As funções aninhadas funcionam bem, mas o número de opções que posso oferecer é limitado. Existem funções específicas para as quais podemos recorrer nestas situações?

    

Answer 1

Uma abordagem é transformar isso em uma pesquisa. Aqui está a essência com um exemplo semelhante ao seu primeiro problema.

Mantive tudo no início da planilha para simplificar. Seu AC9 é meu C1. Seu AC4 é meu D1. Acabei de colocar um resultado em D1 igual a AB4+7 (acabei de inventar qual seria esse resultado).

VocêdesejaatribuirvaloresseAC9forumnúmeroemumdeterminadointervaloeAC4forigualaumdeterminadovalor.EumantenhoorequisitoANDconcatenandoosdois.

AscolunasFeGcontêmumatabeladepesquisa.Vocêpodeexpandiratabelaconformenecessário.Emumcasocomoesse,vocêpoderiausarumafórmulareplicadaparapreencheratabelaconformenecessário.Porexemplo,suafórmulaF1poderiaser:

=ROW()&$AB$4+7

CopiarnacolunausariaonúmerodalinhaparaincrementarseuvalordeAC9dedestinoeconcatená-locomovalordedestinodeAB4+7.

AcolunaGcontémovalorquevocêdeseja.Entãovocêpoderiausarumafórmulacomo:

=ROW()&"b"

Copiar a coluna para baixo preencherá seus valores de resultado.

Para fazer a pesquisa, minha fórmula na A1 é:

=IFERROR(VLOOKUP($C$1&$D$1,$F$1:$G$5,2,0),"")

Isso cria um valor de pesquisa concatenando os valores atuais de C1 e D1 em minha planilha e comparando-os com os valores da coluna F, procurando uma correspondência exata. Se encontrar uma correspondência, ela puxa o valor associado da coluna G.

Se não houver correspondência, a pesquisa produzirá um erro. Embrulhar o VLOOKUP com IFERROR atribui um resultado nulo se houver um erro (sem correspondência).

Seu segundo exemplo nem requer concatenação, apenas uma tabela de consulta contendo os números para comparar AB18, e o cálculo associado como resultado.

    

Answer 2

O fixer123 pode ter fornecido a melhor solução para você usando um vlookup contra uma tabela.

Outra abordagem possível, se você quiser usar uma versão semelhante de sua fórmula e não puder ter uma tabela de pesquisa, pode estar usando muito perto da mesma fórmula que você tinha

=IF(AND(AC9<=5,AC4=(AB4+7)),AC9&"b")

Seu outro exemplo que você pode conseguir com algo como isso, que determina o endereço da célula para multiplicar por

=IF(AB18<=5,(100%+AB17)*(INDIRECT(ADDRESS(271,AB18+3))*AB16))