Osiągnięto zagnieżdżony limit IF w programie Excel

Czy ktoś zna sposób na skrócenie tej formuły programu Excel, ponieważ osiągnąłem zagnieżdżony limit „JEŻELI”?

=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",IF(Sheet!$L2=Sheet1!I2,"Y",
 IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"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",""))))))))))))

Uwaga: do mojej odpowiedzi poniżej podeszliśmy tak, jak w przypadku cyfrowych układów logicznych, w których warunki są oceniane równolegle. W takim przypadku musisz wyraźnie określić każdy warunek. To była całkowita nadmierna zabójstwo tutaj; o wiele bardziej skomplikowane niż to konieczne.

Podejścia przedstawione w odpowiedzi Thiliny R wykorzystują fakt, że Excel ocenia formuły i zakresy w sekwencji, więc Excel już wykonuje większość pracy, ograniczając możliwe warunki podobne do zagnieżdżonych IFs w pytaniu.

Obaj wymyśliliśmy logikę OR przedstawioną poniżej jako moje ostatnie podejście. Pozostawię tę odpowiedź na jakąkolwiek wartość edukacyjną lub nowatorską, jaką może zapewnić. Ale odpowiedź Thiliny R zawiera praktyczne rozwiązania, a mój głos to Metoda 1: WYSZUKIWANIE.

Nie jest jasne, czy możesz używać formuły, która jest bardziej restrykcyjna niż to konieczne. Użyję tutaj krótkiej notacji, aby uprościć dyskusję. Pominę nazwy arkuszy i odniosę się do testów równości w następujący sposób: jeśli to prawda G2=I2, po prostu to wywołam G2. Jeśli to nieprawda, że ​​G2 = I2, wywołam to Not-G2. Dzięki tej stenografii zagnieżdżone IF mogą być wyrażone w następujący sposób:

    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.

Jeśli potrzebujesz takich zasad, możesz to zrobić za pomocą jednego IF i całej logiki AND i OR. W skrócie wyglądałoby to tak:

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

Wypełnienie faktycznej formuły wyglądałoby następująco:

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

(Nie mogę zagwarantować, że literówka się tam nie poślizgnęła). Długie formuły są bardzo trudne do wprowadzenia i utrzymania. Pomocne może być użycie komórek pomocniczych do fragmentów formuły.

Jeśli tak naprawdę nie potrzebujesz reguł, oto kilka prostszych przypadków:

• Jeśli nie potrzebujesz tej sekwencji, a zależy ci tylko na tym, że jedna i tylko jedna komórka jest równa I2, oto sposób na wykonanie tej samej logiki za pomocą jednego 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","")

  Każdy zestaw nawiasów zawiera jeden z twoich testów. Jeśli równość jest prawdą, to daje wartość 1, jeśli nie jest prawdą, wartość będzie 0. W ten sposób Excel reprezentuje prawdę i fałsz.

  Wyniki wszystkich tych testów są sumowane. Jeśli suma jest 1, oznacza to, że jeden i tylko jeden test był prawdziwy.

• Jeśli w rzeczywistości zależy Ci tylko na tym, że przynajmniej jeden z tych przypadków jest prawdziwy, możesz użyć prostego LUB:

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

Zauważ, że dodałem podziały linii i dodatkowe spacje we wszystkich formułach, aby logika była łatwiejsza do zobaczenia. Jeśli chcesz skopiować i wkleić, musisz je usunąć.

Zasadniczo chcesz wiedzieć, czy którakolwiek komórka G2:R2w jednym arkuszu ma taką samą wartość jak komórka I2w innym arkuszu.

Można to zrobić na kilka sposobów. Najłatwiej przychodzi na to, ponieważ sprawdzane wartości znajdują się w ciągłym zakresie:

Metoda 1: HLOOKUP / VLOOKUP

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

Ta funkcja sprawdza wartość w komórce I2na jednym arkuszu i sprawdza, czy znajduje się ona w drugim arkuszu w zakresie G2:R2. Jeśli HLOOKUPznajdzie co najmniej jedną wartość w zakresie G2:R2pasującym do komórki I2w drugim arkuszu, zwraca Ylub w przeciwnym razie nie zwraca wartości.

Używałem, HLOOKUPponieważ podany zakres był zasięgiem poziomym. Jeśli zakres jest zakresem pionowym, możesz użyć VLOOKUPzamiast tego.

Jeśli z jakiegokolwiek powodu wartości nie mieszczą się w ciągłym zakresie, możesz użyć pozostałych 2 poniższych metod.

Oto inny sposób:

Metoda 2: SKONCENTROWANIE

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

Zasadniczo tworzy to duży ciąg wszystkich wartości w komórkach G2:R2i sprawdza, czy jest w tym wartość w komórce I2drugiego arkusza. Jeśli tak, zwraca, w Yprzeciwnym razie nie zwraca wartości.

Zauważ, że ponieważ ta metoda tworzy duży ciąg ze wszystkich danych w zakresie komórek, które należy wyszukać, w zależności od rodzaju danych w tych komórkach, może zwrócić Yniepoprawnie. Na przykład: Jeśli Sheet1!I2ma wartość 123i Sheet!G2ma wartość 12i Sheet!H2ma wartość 34, ta metoda będzie nadal wyświetlana, Yponieważ „duży ciąg” będzie zawierał „1234 ...”, a wartość 123będzie w tym ciągu.

Jeszcze innym sposobem na to jest:

Metoda 3: Operator boolowski - LUB

[@ fixer1234 wspomniał o tym pierwszy]

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

Wykorzystuje operator boolowski, ORaby sprawdzić, czy którakolwiek z wartości z zakresu G2:R2znajduje się w komórce I2drugiego arkusza. Jeśli operator boolowski znajdzie co najmniej jedną wartość pasującą do komórki I2w drugim arkuszu, zwraca Ylub w przeciwnym razie nie zwraca wartości.

Metoda 4: Rozłóż wzór

1. Jeśli chcesz tylko używać IFwarunków (z jakiegoś powodu nie mogę sobie wyobrazić), możesz rozłożyć IFwarunki na wiele komórek, a następnie użyć wyniku tych komórek, aby ustalić ostateczny wynik. Na przykład: możesz mieć kilka IFwarunków w jednej komórce i możesz użyć wyniku tego w innej komórce wraz z kilkoma innymi IFwarunkami i tak dalej.

W komórce A1arkusza możesz mieć następujące elementy :

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

Następnie w komórce A2arkusza mogą znajdować się następujące elementy :

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

Wreszcie możesz mieć następujące komórki w komórce A3arkusza:

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

Metoda 5: VBA

Użyj VBA, jeśli nie masz nic przeciwko, aby utworzyć niestandardową funkcję zdefiniowaną przez użytkownika, w której możesz mieć tyle zagnieżdżonych Ifinstrukcji.

Uwaga: Dane wyjściowe obu tych formuł są takie same, jak podana formuła.