Atingiu o limite IF aninhado no Excel

Question 1

Observação:Minha resposta abaixo foi abordada da mesma forma que você faria com circuitos lógicos digitais, onde as condições são avaliadas em paralelo. Nesse caso, você precisa fornecer explicitamente todas as condições. Isso foi um exagero total aqui; muito mais complicado do que o necessário.

As abordagens mostradas na resposta de thilina R aproveitam o fato de o Excel avaliar fórmulas e intervalos em sequência, portanto, o Excel já está fazendo a maior parte do trabalho de limitar condições possíveis semelhantes aos IFs aninhados na pergunta.

Nós dois criamos a lógica OR mostrada como minha última abordagem abaixo. Deixarei esta resposta por qualquer valor educacional ou inovador que ela possa fornecer. Mas a resposta de Thilina R contém as soluções práticas, e meu voto é Método 1: HLOOKUP.

Não está claro se você pode estar usando uma fórmula mais restritiva do que o necessário. Usarei uma pequena notação abreviada aqui para simplificar a discussão. Deixarei de fora os nomes das planilhas e me referirei aos testes de igualdade desta forma: Se for verdade G2=I2, vou apenas chamá-lo de G2. Se for falso que G2 = I2, vou chamá-lo de Not-G2. Com esta abreviação, seus IFs aninhados podem ser expressos assim:

    G2 
or: H2 and Not-G2
or: I2 and Not-G2 and Not-H2
or: J2 and Not-G2 and Not-H2 and Not-I2
etc.

Se essas são as regras que você precisa, você pode fazer isso com um IF e toda aquela lógica AND e OR. Ficaria assim resumidamente:

=IF(OR(G2,AND(H2,Not-G2),AND(I2,Not-G2,Not-H2),...),"Y","")

O preenchimento da fórmula real ficaria assim:

=IF(OR(Sheet!$G$2=Sheet1!I2,
       AND(Sheet!$H2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2),
       AND(Sheet!$I2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2),
       AND(Sheet!$J2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2),
       AND(Sheet!$K2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2),
       AND(Sheet!$L2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2),
       AND(Sheet!$M2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2),
       AND(Sheet!$N2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2),
       AND(Sheet!$O2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2),
       AND(Sheet!$P2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2),
       AND(Sheet!$Q2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2),
       AND(Sheet!$R2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2,Sheet!$Q2<>Sheet1!I2)),"Y","")

(Não posso garantir que não tenha ocorrido um erro de digitação). Fórmulas tão longas são muito difíceis de inserir e manter. Pode ser útil usar células auxiliares para partes da fórmula.

Se essas não são realmente as regras que você precisa, aqui estão alguns casos mais simples:

Se você não precisa dessa sequência e apenas se importa que qualquer uma, e apenas uma, célula seja igual a I2, aqui está uma maneira de fazer a mesma lógica com um IF:

=IF((Sheet!$G$2=Sheet1!I2)+(Sheet!$H2=Sheet1!I2)+(Sheet!$I2=Sheet1!I2)+ (Sheet!$J2=Sheet1!I2)+(Sheet!$K2=Sheet1!I2)+(Sheet!$L2=Sheet1!I2)+ (Sheet!$M2=Sheet1!I2)+(Sheet!$N2=Sheet1!I2)+(Sheet!$O2=Sheet1!I2)+ (Sheet!$P2=Sheet1!I2)+(Sheet!$Q2=Sheet1!I2)+ (Sheet!$R2=Sheet1!I2)=1,"Y","")

Cada conjunto de parênteses contém um de seus testes. Se a igualdade for verdadeira, ela produz um valor de 1; se não for verdadeira, o valor será 0. É assim que o Excel representa o verdadeiro e o falso.

Os resultados de todos esses testes são somados. Se a soma for 1, significa que um e apenas um dos testes foi verdadeiro.
Se, na realidade, você só se importa se pelo menos um desses casos for verdadeiro, você poderia usar um simples OR:

=IF(OR(Sheet!$G$2=Sheet1!I2,Sheet!$H2=Sheet1!I2,Sheet!$I2=Sheet1!I2, Sheet!$J2=Sheet1!I2,Sheet!$K2=Sheet1!I2,Sheet!$L2=Sheet1!I2, Sheet!$M2=Sheet1!I2,Sheet!$N2=Sheet1!I2,Sheet!$O2=Sheet1!I2, Sheet!$P2=Sheet1!I2,Sheet!$Q2=Sheet1!I2,Sheet!$R2=Sheet1!I2),"Y","")

Observe que adicionei quebras de linha e espaços extras em todas as fórmulas para que a lógica seja mais fácil de ver. Se quiser copiar e colar, você precisará removê-los.

Answer

Observação:Minha resposta abaixo foi abordada da mesma forma que você faria com circuitos lógicos digitais, onde as condições são avaliadas em paralelo. Nesse caso, você precisa fornecer explicitamente todas as condições. Isso foi um exagero total aqui; muito mais complicado do que o necessário.

As abordagens mostradas na resposta de thilina R aproveitam o fato de o Excel avaliar fórmulas e intervalos em sequência, portanto, o Excel já está fazendo a maior parte do trabalho de limitar condições possíveis semelhantes aos IFs aninhados na pergunta.

Nós dois criamos a lógica OR mostrada como minha última abordagem abaixo. Deixarei esta resposta por qualquer valor educacional ou inovador que ela possa fornecer. Mas a resposta de Thilina R contém as soluções práticas, e meu voto é Método 1: HLOOKUP.

Não está claro se você pode estar usando uma fórmula mais restritiva do que o necessário. Usarei uma pequena notação abreviada aqui para simplificar a discussão. Deixarei de fora os nomes das planilhas e me referirei aos testes de igualdade desta forma: Se for verdade G2=I2, vou apenas chamá-lo de G2. Se for falso que G2 = I2, vou chamá-lo de Not-G2. Com esta abreviação, seus IFs aninhados podem ser expressos assim:

    G2 
or: H2 and Not-G2
or: I2 and Not-G2 and Not-H2
or: J2 and Not-G2 and Not-H2 and Not-I2
etc.

Se essas são as regras que você precisa, você pode fazer isso com um IF e toda aquela lógica AND e OR. Ficaria assim resumidamente:

=IF(OR(G2,AND(H2,Not-G2),AND(I2,Not-G2,Not-H2),...),"Y","")

O preenchimento da fórmula real ficaria assim:

=IF(OR(Sheet!$G$2=Sheet1!I2,
       AND(Sheet!$H2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2),
       AND(Sheet!$I2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2),
       AND(Sheet!$J2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2),
       AND(Sheet!$K2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2),
       AND(Sheet!$L2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2),
       AND(Sheet!$M2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2),
       AND(Sheet!$N2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2),
       AND(Sheet!$O2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2),
       AND(Sheet!$P2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2),
       AND(Sheet!$Q2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2),
       AND(Sheet!$R2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2,Sheet!$Q2<>Sheet1!I2)),"Y","")

(Não posso garantir que não tenha ocorrido um erro de digitação). Fórmulas tão longas são muito difíceis de inserir e manter. Pode ser útil usar células auxiliares para partes da fórmula.

Se essas não são realmente as regras que você precisa, aqui estão alguns casos mais simples:

Se você não precisa dessa sequência e apenas se importa que qualquer uma, e apenas uma, célula seja igual a I2, aqui está uma maneira de fazer a mesma lógica com um IF:

=IF((Sheet!$G$2=Sheet1!I2)+(Sheet!$H2=Sheet1!I2)+(Sheet!$I2=Sheet1!I2)+ (Sheet!$J2=Sheet1!I2)+(Sheet!$K2=Sheet1!I2)+(Sheet!$L2=Sheet1!I2)+ (Sheet!$M2=Sheet1!I2)+(Sheet!$N2=Sheet1!I2)+(Sheet!$O2=Sheet1!I2)+ (Sheet!$P2=Sheet1!I2)+(Sheet!$Q2=Sheet1!I2)+ (Sheet!$R2=Sheet1!I2)=1,"Y","")

Cada conjunto de parênteses contém um de seus testes. Se a igualdade for verdadeira, ela produz um valor de 1; se não for verdadeira, o valor será 0. É assim que o Excel representa o verdadeiro e o falso.

Os resultados de todos esses testes são somados. Se a soma for 1, significa que um e apenas um dos testes foi verdadeiro.
Se, na realidade, você só se importa se pelo menos um desses casos for verdadeiro, você poderia usar um simples OR:

=IF(OR(Sheet!$G$2=Sheet1!I2,Sheet!$H2=Sheet1!I2,Sheet!$I2=Sheet1!I2, Sheet!$J2=Sheet1!I2,Sheet!$K2=Sheet1!I2,Sheet!$L2=Sheet1!I2, Sheet!$M2=Sheet1!I2,Sheet!$N2=Sheet1!I2,Sheet!$O2=Sheet1!I2, Sheet!$P2=Sheet1!I2,Sheet!$Q2=Sheet1!I2,Sheet!$R2=Sheet1!I2),"Y","")

Observe que adicionei quebras de linha e espaços extras em todas as fórmulas para que a lógica seja mais fácil de ver. Se quiser copiar e colar, você precisará removê-los.

Question 2

Basicamente, você deseja saber se alguma das células G2:R2de uma planilha tem o mesmo valor que uma célula I2de uma planilha diferente.

Há várias maneiras de fazer isso. A maneira mais fácil que vem à mente é esta, já que os valores que você está verificando estão em um intervalo contínuo:

Método 1: PROCH/PROCV

=IF(ISERROR(HLOOKUP(Sheet1!I2,Sheet!G2:R2,1,FALSE)),"","Y")

Isso pesquisa o valor na célula I2de uma planilha e verifica se ele está na outra planilha do intervalo G2:R2. Se HLOOKUPencontrar pelo menos um valor no intervalo G2:R2que corresponda à célula I2da outra planilha, ele retornará You não retornará um valor.

Eu usei HLOOKUPdesde que o intervalo fornecido era um intervalo horizontal. Se o intervalo for vertical, você poderá usá-lo VLOOKUP.

Se os valores foremnãoem uma faixa contínua por qualquer motivo, você pode usar os outros 2 métodos abaixo.

Aqui está outra maneira:

Método 2: CONCATENAR

=IF(ISERROR(FIND(Sheet1!I2,CONCATENATE(Sheet!G2,Sheet!H2,Sheet!I2,Sheet!J2,Sheet!K2,Sheet!L2,Sheet!M2,Sheet!N2,Sheet!O2,Sheet!P2,Sheet!Q2,Sheet!R2))),"","Y")

Isso basicamente faz uma grande string com todos os valores das células G2:R2e verifica se o valor da célula I2da outra planilha está nela. Se for, ele retorna, Ycaso contrário, não retorna um valor.

Observe que, como esse método cria uma grande string com todos os dados no intervalo de células que você precisa pesquisar, dependendo do tipo de dados nessas células, ele pode retornar Yincorretamente. Por exemplo: Se Sheet1!I2has the value 123and Sheet!G2has the value 12and Sheet!H2has the value 34, este método ainda será exibido, Ypois a "string grande" conterá "1234..." e o valor 123estará nesta string.

Ainda outra maneira de fazer isso é:

Método 3: operador booleano - OR

[@fixer1234 mencionou isso primeiro]

=IF(OR((Sheet!G2=Sheet1!I2), (Sheet!H2=Sheet1!I2),(Sheet!I2=Sheet1!I2), (Sheet!J2=Sheet1!I2), (Sheet!K2=Sheet1!I2), (Sheet!L2=Sheet1!I2), (Sheet!M2=Sheet1!I2), (Sheet!N2=Sheet1!I2), (Sheet!O2=Sheet1!I2), (Sheet!P2=Sheet1!I2), (Sheet!Q2=Sheet!I2), (Sheet!R2=Sheet1!I2) ),"Y","")

Isso usa o operador booleano ORpara verificar se algum dos valores do intervalo G2:R2está na célula I2da outra planilha. Se o operador booleano encontrar pelo menos um valor que corresponda à célula I2da outra planilha, ele retornará You não retornará um valor.

Método 4: distribua a fórmula

Se você quiser usar apenas IFcondições (por algum motivo que não consigo imaginar), poderá espalhar as IFcondições em várias células e usar o resultado dessas células para determinar o resultado final. Por exemplo: você pode ter algumas IFcondições em uma célula e pode usar o resultado disso em outra célula junto com mais algumas IFcondições e assim por diante.

Você poderia ter o seguinte na célula A1de uma planilha:

=IF(Sheet!$G$2=Sheet1!I2,"Y",IF(Sheet!$H2=Sheet1!I2,"Y", IF(Sheet!$I2=Sheet1!I2,"Y",IF(Sheet!$J2=Sheet1!I2,"Y", IF(Sheet!$K2=Sheet1!I2,"Y","")))))

E então, você poderia ter o seguinte na célula A2de uma planilha:

=IF(A1="Y", "Y", IF(Sheet!$L2=Sheet1!I2,"Y", IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"Y", ""))))

Finalmente, você poderia ter o seguinte na célula A3de uma planilha:

=IF(A2="Y","Y",IF(Sheet!$O2=Sheet1!I2,"Y",IF(Sheet!$P2=Sheet1!I2,"Y", IF(Sheet!$Q2=Sheet1!I2,"Y",IF(Sheet!$R2=Sheet1!I2,"Y","")))))

Método 5: VBA

Use o VBA se você se sentir confortável com ele para criar uma função personalizada definida pelo usuário na qual você pode ter tantas Ifinstruções aninhadas.

Nota: A saída de ambas as fórmulas é igual à fórmula que você forneceu.

Answer