Se alcanzó el límite IF anidado en Excel

Question 1

Nota:Mi respuesta a continuación se abordó de la misma manera que lo haría con los circuitos lógicos digitales, donde las condiciones se evalúan en paralelo. En ese caso, debe prever explícitamente todas las condiciones. Eso fue una exageración total aquí; mucho más complicado de lo necesario.

Los enfoques mostrados en la respuesta de Thilina R aprovechan el hecho de que Excel evalúa fórmulas y rangos en secuencia, por lo que Excel ya está haciendo la mayor parte del trabajo de limitar posibles condiciones similares a los IF anidados en la pregunta.

A ambos se nos ocurrió la lógica OR que se muestra como mi último enfoque, a continuación. Dejaré esta respuesta por cualquier valor educativo o novedoso que pueda aportar. Pero la respuesta de Thilina R contiene soluciones prácticas y mi voto es el Método 1: BUSCARH.

No está claro si podría estar utilizando una fórmula más restrictiva de lo necesario. Usaré aquí una pequeña notación abreviada para simplificar la discusión. Dejaré de lado los nombres de las hojas y me referiré a las pruebas de igualdad de esta manera: Si es cierto G2=I2, simplemente lo llamaré G2. Si es falso que G2=I2, lo llamaré Not-G2. Con esta abreviatura, sus IF anidados se pueden expresar así:

    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.

Si esas son las reglas que necesita, puede hacerlo con un SI y toda esa lógica Y y O. En resumen, se vería así:

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

Completar la fórmula real se vería así:

=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","")

(No puedo garantizar que no haya habido un error tipográfico allí). Fórmulas tan largas son muy difíciles de introducir y mantener. Puede resultar útil utilizar celdas auxiliares para partes de la fórmula.

Si esas no son realmente las reglas que necesita, aquí hay algunos casos más simples:

Si no necesita esa secuencia y solo le importa que una celda, y solo una, sea igual a I2, aquí tiene una manera de hacer la misma lógica con un 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éntesis contiene una de sus pruebas. Si la igualdad es verdadera, produce un valor de 1, si no es verdadera, el valor será 0. Así es como Excel representa verdadero y falso.

Los resultados de todas estas pruebas se suman. Si la suma es 1, significa que una y sólo una de las pruebas fue verdadera.
Si, en realidad, sólo te importa si al menos uno de esos casos es cierto, puedes usar un simple 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","")

Tenga en cuenta que agregué saltos de línea y espacios adicionales dentro de todas las fórmulas para que la lógica sea más fácil de ver. Si desea copiar y pegar, deberá eliminarlos.

Answer

Nota:Mi respuesta a continuación se abordó de la misma manera que lo haría con los circuitos lógicos digitales, donde las condiciones se evalúan en paralelo. En ese caso, debe prever explícitamente todas las condiciones. Eso fue una exageración total aquí; mucho más complicado de lo necesario.

Los enfoques mostrados en la respuesta de Thilina R aprovechan el hecho de que Excel evalúa fórmulas y rangos en secuencia, por lo que Excel ya está haciendo la mayor parte del trabajo de limitar posibles condiciones similares a los IF anidados en la pregunta.

A ambos se nos ocurrió la lógica OR que se muestra como mi último enfoque, a continuación. Dejaré esta respuesta por cualquier valor educativo o novedoso que pueda aportar. Pero la respuesta de Thilina R contiene soluciones prácticas y mi voto es el Método 1: BUSCARH.

No está claro si podría estar utilizando una fórmula más restrictiva de lo necesario. Usaré aquí una pequeña notación abreviada para simplificar la discusión. Dejaré de lado los nombres de las hojas y me referiré a las pruebas de igualdad de esta manera: Si es cierto G2=I2, simplemente lo llamaré G2. Si es falso que G2=I2, lo llamaré Not-G2. Con esta abreviatura, sus IF anidados se pueden expresar así:

    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.

Si esas son las reglas que necesita, puede hacerlo con un SI y toda esa lógica Y y O. En resumen, se vería así:

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

Completar la fórmula real se vería así:

=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","")

(No puedo garantizar que no haya habido un error tipográfico allí). Fórmulas tan largas son muy difíciles de introducir y mantener. Puede resultar útil utilizar celdas auxiliares para partes de la fórmula.

Si esas no son realmente las reglas que necesita, aquí hay algunos casos más simples:

Si no necesita esa secuencia y solo le importa que una celda, y solo una, sea igual a I2, aquí tiene una manera de hacer la misma lógica con un 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éntesis contiene una de sus pruebas. Si la igualdad es verdadera, produce un valor de 1, si no es verdadera, el valor será 0. Así es como Excel representa verdadero y falso.

Los resultados de todas estas pruebas se suman. Si la suma es 1, significa que una y sólo una de las pruebas fue verdadera.
Si, en realidad, sólo te importa si al menos uno de esos casos es cierto, puedes usar un simple 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","")

Tenga en cuenta que agregué saltos de línea y espacios adicionales dentro de todas las fórmulas para que la lógica sea más fácil de ver. Si desea copiar y pegar, deberá eliminarlos.

Question 2

Básicamente, desea saber si alguna de las celdas G2:R2de una hoja tiene el mismo valor que la celda I2de una hoja diferente.

Hay varias formas de hacerlo. La forma más sencilla que me viene a la mente es esta, ya que los valores que está verificando están en un rango continuo:

Método 1: BUSCARH/BUSCARV

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

Este uso busca el valor en la celda I2de una hoja y comprueba si está en la otra hoja del rango G2:R2. Si HLOOKUPencuentra al menos un valor en el rango G2:R2que coincide con la celda I2de la otra hoja, devuelve Yo no devuelve un valor.

Lo he usado HLOOKUPdesde que el rango proporcionado era un rango horizontal. Si el rango es vertical, puede usarlo VLOOKUPen su lugar.

Si los valores sonnoen un rango continuo por cualquier motivo, puede utilizar los otros 2 métodos siguientes.

Aquí hay otra manera:

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")

Básicamente, esto crea una cadena grande de todos los valores en las celdas G2:R2y verifica si el valor en la celda I2de la otra hoja está en esa. Si es así, devuelve; Yde lo contrario, no devuelve ningún valor.

Tenga en cuenta que, dado que este método crea una cadena grande con todos los datos en el rango de celdas que necesita buscar, dependiendo del tipo de datos en esas celdas, podría devolver un resultado Yincorrecto. Por ejemplo: si Sheet1!I2tiene el valor 123y Sheet!G2tiene el valor 12y Sheet!H2tiene el valor 34, este método aún se mostrará Yya que la "cadena grande" contendrá "1234..." y el valor 123estará en esta cadena.

Otra forma más de hacer esto es:

Método 3: operador booleano - O

[@fixer1234 mencionó esto primero]

=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","")

Esto utiliza el operador booleano ORpara verificar si alguno de los valores en el rango G2:R2está en la celda I2de la otra hoja. Si el operador booleano encuentra al menos un valor que coincide con la celda I2de la otra hoja, devuelve Yo no devuelve un valor.

Método 4: Distribuya la fórmula

Si solo desea usar IFcondiciones (por alguna razón que no puedo imaginar), puede distribuir las IFcondiciones en varias celdas y luego usar el resultado de esas celdas para determinar el resultado final. Por ejemplo: podría tener algunas de las IFcondiciones en una celda y puede usar el resultado de esto en otra celda junto con algunas IFcondiciones más, y así sucesivamente.

Podría tener lo siguiente en la celda A1de una hoja:

=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","")))))

Y luego, podrías tener lo siguiente en la celda A2de una hoja:

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

Finalmente, podrías tener lo siguiente en la celda A3de una hoja:

=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

Utilice VBA si se siente cómodo con él para crear una función personalizada definida por el usuario en la que pueda tener tantas Ifdeclaraciones anidadas.

Nota: El resultado de ambas fórmulas es el mismo que el de la fórmula que proporcionó.

Answer