In Excel wurde das Limit für verschachtelte IF-Anweisungen erreicht.

Question 1

Notiz:Meine Antwort unten wurde so angegangen, wie Sie es bei digitalen Logikschaltungen tun würden, bei denen Bedingungen parallel ausgewertet werden. In diesem Fall müssen Sie jede Bedingung explizit angeben. Das war hier völlig übertrieben; viel komplizierter als nötig.

Die in der Antwort von Thilina R gezeigten Ansätze nutzen die Tatsache aus, dass Excel Formeln und Bereiche nacheinander auswertet, sodass Excel bereits den Großteil der Arbeit zur Eingrenzung möglicher Bedingungen übernimmt, ähnlich den verschachtelten WENNs in der Frage.

Wir haben beide die ODER-Logik entwickelt, die ich unten als meinen letzten Ansatz zeige. Ich werde diese Antwort so stehen lassen, wie sie für den Lern- oder Neuheitswert sein könnte. Aber die Antwort von Thilina R enthält die praktischen Lösungen, und ich stimme für Methode 1: HLOOKUP.

Es ist nicht klar, ob Sie möglicherweise eine Formel verwenden, die restriktiver als nötig ist. Ich werde hier eine kleine Kurzschreibweise verwenden, um die Diskussion zu vereinfachen. Ich werde die Blattnamen weglassen und mich folgendermaßen auf die Gleichheitstests beziehen: Wenn es wahr ist, dass G2=I2, nenne ich es einfach G2. Wenn es falsch ist, dass G2=I2, nenne ich es Not-G2. Mit dieser Kurzschreibweise können Ihre verschachtelten IFs wie folgt ausgedrückt werden:

    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.

Wenn das die Regeln sind, die Sie brauchen, können Sie es mit einem WENN und der gesamten UND- und ODER-Logik tun. In Kurzform würde es so aussehen:

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

Das Einsetzen der eigentlichen Formel würde folgendermaßen aussehen:

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

(Ich kann nicht garantieren, dass sich hier kein Tippfehler eingeschlichen hat.) So lange Formeln sind sehr schwer einzugeben und zu pflegen. Es kann hilfreich sein, Hilfszellen für Teile der Formel zu verwenden.

Wenn das nicht unbedingt die Regeln sind, die Sie brauchen, hier ein paar einfachere Fälle:

Wenn Sie diese Sequenz nicht benötigen und es Ihnen nur wichtig ist, dass eine beliebige und nur eine Zelle gleich I2 ist, können Sie die gleiche Logik wie folgt mit einem WENN durchführen:

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

Jeder Satz Klammern enthält einen Ihrer Tests. Wenn die Gleichheit wahr ist, ergibt sich der Wert 1, wenn nicht wahr, ist der Wert 0. So stellt Excel wahr und falsch dar.

Die Ergebnisse aller dieser Tests werden addiert. Wenn die Summe ist 1, bedeutet dies, dass genau einer der Tests wahr war.
Wenn es für Sie in Wirklichkeit nur wichtig ist, ob mindestens einer dieser Fälle zutrifft, können Sie ein einfaches ODER verwenden:

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

Beachten Sie, dass ich in allen Formeln Zeilenumbrüche und zusätzliche Leerzeichen hinzugefügt habe, damit die Logik leichter zu erkennen ist. Wenn Sie kopieren und einfügen möchten, müssen Sie diese entfernen.

Answer

Notiz:Meine Antwort unten wurde so angegangen, wie Sie es bei digitalen Logikschaltungen tun würden, bei denen Bedingungen parallel ausgewertet werden. In diesem Fall müssen Sie jede Bedingung explizit angeben. Das war hier völlig übertrieben; viel komplizierter als nötig.

Die in der Antwort von Thilina R gezeigten Ansätze nutzen die Tatsache aus, dass Excel Formeln und Bereiche nacheinander auswertet, sodass Excel bereits den Großteil der Arbeit zur Eingrenzung möglicher Bedingungen übernimmt, ähnlich den verschachtelten WENNs in der Frage.

Wir haben beide die ODER-Logik entwickelt, die ich unten als meinen letzten Ansatz zeige. Ich werde diese Antwort so stehen lassen, wie sie für den Lern- oder Neuheitswert sein könnte. Aber die Antwort von Thilina R enthält die praktischen Lösungen, und ich stimme für Methode 1: HLOOKUP.

Es ist nicht klar, ob Sie möglicherweise eine Formel verwenden, die restriktiver als nötig ist. Ich werde hier eine kleine Kurzschreibweise verwenden, um die Diskussion zu vereinfachen. Ich werde die Blattnamen weglassen und mich folgendermaßen auf die Gleichheitstests beziehen: Wenn es wahr ist, dass G2=I2, nenne ich es einfach G2. Wenn es falsch ist, dass G2=I2, nenne ich es Not-G2. Mit dieser Kurzschreibweise können Ihre verschachtelten IFs wie folgt ausgedrückt werden:

    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.

Wenn das die Regeln sind, die Sie brauchen, können Sie es mit einem WENN und der gesamten UND- und ODER-Logik tun. In Kurzform würde es so aussehen:

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

Das Einsetzen der eigentlichen Formel würde folgendermaßen aussehen:

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

(Ich kann nicht garantieren, dass sich hier kein Tippfehler eingeschlichen hat.) So lange Formeln sind sehr schwer einzugeben und zu pflegen. Es kann hilfreich sein, Hilfszellen für Teile der Formel zu verwenden.

Wenn das nicht unbedingt die Regeln sind, die Sie brauchen, hier ein paar einfachere Fälle:

Wenn Sie diese Sequenz nicht benötigen und es Ihnen nur wichtig ist, dass eine beliebige und nur eine Zelle gleich I2 ist, können Sie die gleiche Logik wie folgt mit einem WENN durchführen:

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

Jeder Satz Klammern enthält einen Ihrer Tests. Wenn die Gleichheit wahr ist, ergibt sich der Wert 1, wenn nicht wahr, ist der Wert 0. So stellt Excel wahr und falsch dar.

Die Ergebnisse aller dieser Tests werden addiert. Wenn die Summe ist 1, bedeutet dies, dass genau einer der Tests wahr war.
Wenn es für Sie in Wirklichkeit nur wichtig ist, ob mindestens einer dieser Fälle zutrifft, können Sie ein einfaches ODER verwenden:

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

Beachten Sie, dass ich in allen Formeln Zeilenumbrüche und zusätzliche Leerzeichen hinzugefügt habe, damit die Logik leichter zu erkennen ist. Wenn Sie kopieren und einfügen möchten, müssen Sie diese entfernen.

Question 2

Grundsätzlich möchten Sie wissen, ob eine der Zellen G2:R2in einem Blatt denselben Wert hat wie eine Zelle I2in einem anderen Blatt.

Es gibt mehrere Möglichkeiten, dies zu tun. Der einfachste Weg, der mir einfällt, ist dieser, da die Werte, die Sie prüfen, in einem kontinuierlichen Bereich liegen:

Methode 1: HLOOKUP/VLOOKUP

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

Dieser sucht den Wert in einer Zelle I2auf einem Blatt und prüft, ob er sich im Bereich des anderen Blatts befindet G2:R2. Wenn HLOOKUPmindestens ein Wert im Bereich gefunden wird G2:R2, der mit der Zelle I2im anderen Blatt übereinstimmt, wird Yein Wert zurückgegeben, andernfalls wird kein Wert zurückgegeben.

Ich habe verwendet HLOOKUP, da der angegebene Bereich ein horizontaler Bereich war. Wenn der Bereich ein vertikaler Bereich ist, können Sie VLOOKUPstattdessen verwenden.

Wenn die Wertenichtin einem kontinuierlichen Bereich liegen, können Sie die beiden anderen Methoden unten verwenden.

Hier ist eine andere Möglichkeit:

Methode 2: VERKETTEN

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

Dies erstellt im Grunde eine große Zeichenfolge aller Werte in den Zellen G2:R2und prüft, ob der Wert in der Zelle I2des anderen Blatts darin enthalten ist. Wenn dies der Fall ist, Ywird ein Wert zurückgegeben, andernfalls nicht.

Beachten Sie, dass diese Methode aus allen Daten im nachzuschlagenden Zellbereich einen großen String erstellt. Abhängig vom Datentyp in diesen Zellen kann es zu Yfalschen Ergebnissen kommen. Beispiel: Wenn Sheet1!I2hat den Wert 123und Sheet!G2hat den Wert 12und Sheet!H2hat den Wert 34, wird diese Methode trotzdem angezeigt, Yda der „große String“ „1234...“ enthält und der Wert 123in diesem String enthalten ist.

Eine weitere Möglichkeit hierfür ist:

Methode 3: Boolescher Operator - ODER

[@fixer1234 hatte dies zuerst erwähnt]

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

Dabei wird der Boolesche Operator verwendet, ORum zu prüfen, ob sich einer der Werte im Bereich G2:R2in der Zelle auf dem anderen Blatt befindet I2. Wenn der Boolesche Operator mindestens einen Wert findet, der mit der Zelle auf dem I2anderen Blatt übereinstimmt, gibt er einen Wert zurück, Yandernfalls gibt er keinen Wert zurück.

Methode 4: Die Formel ausbreiten

Wenn Sie nur IFBedingungen verwenden möchten (aus irgendeinem Grund, den ich mir nicht vorstellen kann), können Sie die IFBedingungen auf mehrere Zellen verteilen und dann das Ergebnis dieser Zellen verwenden, um das Endergebnis zu bestimmen. Beispiel: Sie könnten einige der IFBedingungen in einer Zelle haben und das Ergebnis davon in einer anderen Zelle zusammen mit einigen weiteren IFBedingungen verwenden und so weiter.

A1In einer Zelle eines Blatts könnte Folgendes stehen :

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

Und dann könnten Sie Folgendes in einer Zelle A2eines Blattes haben:

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

A3Schließlich könnten Sie in einer Zelle eines Blattes Folgendes haben :

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

Methode 5: VBA

Wenn Sie damit vertraut sind, verwenden Sie VBA, um eine benutzerdefinierte Funktion zu erstellen, in der Sie beliebig viele verschachtelte IfAnweisungen haben können.

Hinweis: Die Ausgabe dieser beiden Formeln ist identisch mit der von Ihnen angegebenen Formel.

Answer