Достигнут вложенный лимит IF в Excel

Question

Кто-нибудь знает способ сократить эту формулу Excel, так как я достиг вложенного предела "IF"?

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

Answer 1

Примечание: мой ответ ниже был приближен к цифровым логическим схемам, в которых условия оцениваются параллельно. В этом случае вам необходимо явно указать каждое условие. Это было полным перебором здесь; путь сложнее, чем необходимо.

Подходы, показанные в ответе Thilina R, используют тот факт, что Excel последовательно оценивает формулы и диапазоны, поэтому Excel уже выполняет большую часть работы по ограничению возможных условий, аналогичных вложенным IF в вопросе.

Мы оба придумали логику ИЛИ, показанную как мой последний подход, ниже. Я оставлю этот ответ для любой образовательной или новаторской ценности. Но ответ thilina R содержит практические решения, и мой голос - Метод 1: HLOOKUP.

---

Не ясно, возможно, вы используете формулу, которая является более строгой, чем необходимо. Я буду использовать несколько сокращенных обозначений здесь, чтобы упростить обсуждение. Я опущу имена листов и буду ссылаться на тесты на равенство следующим образом: если это правда, что G2=I2 , я просто назову это G2 . Если неверно, что G2 = I2, я назову это Not-G2 . С этим сокращением ваши вложенные IF могут быть выражены так:

    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.

Если это правила, которые вам нужны, вы можете сделать это с помощью одного IF и всей логики AND и OR. Это выглядит примерно так:

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

Заполнение фактической формулы будет выглядеть так:

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

(Я не могу гарантировать, что опечатка не проскользнула туда). Формулы, которые долго, очень трудно вводить и поддерживать. Может быть полезно использовать вспомогательные ячейки для частей формулы.

Если это на самом деле не те правила, которые вам нужны, вот несколько простых случаев:

• Если вам не нужна эта последовательность, и вам нужно только, чтобы одна и только одна ячейка равнялась I2, вот способ сделать ту же логику с одним 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","")

  Каждый набор скобок содержит один из ваших тестов. Если равенство истинно, оно выдает значение 1 , если не верно, значение будет 0 . Вот как Excel представляет истину и ложь.

  Результаты всех этих испытаний суммируются. Если сумма равна 1 , это означает, что один и только один из тестов был верным.

• Если на самом деле вас волнует только то, что хотя бы один из этих случаев является истинным, вы можете использовать простое ИЛИ:

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

Обратите внимание, что я добавил разрывы строк и дополнительные пробелы во всех формулах, чтобы логику было легче увидеть. Если вы хотите скопировать и вставить, вам нужно будет удалить их.

Answer 2

В основном вы хотите знать, имеет ли какая-либо из ячеек G2:R2 на одном листе то же значение, что и ячейка I2 на другом листе.

Есть несколько способов сделать это. Самый простой способ, который приходит на ум - это то, что проверяемые значения находятся в непрерывном диапазоне:

Способ 1: HLOOKUP/VLOOKUP

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

При этом используется поиск значения в ячейке I2 на одном листе и проверка его на другом листе в диапазоне G2:R2 . Если HLOOKUP находит хотя бы одно значение в диапазоне G2:R2 которое соответствует ячейке I2 на другом листе, он возвращает Y или иначе не возвращает значение.

Я использовал HLOOKUP поскольку предоставленный диапазон был горизонтальным. Если диапазон вертикальный, вы можете использовать вместо него VLOOKUP .

Если значения не находятся в непрерывном диапазоне по какой-либо причине, вы можете использовать другие 2 метода ниже.

Вот еще один способ:

Способ 2: КОНКАТЕНАТ

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

В основном это создает большую строку из всех значений в ячейках G2:R2 и проверяет, находится ли значение в ячейке I2 другого листа. Если это так, он возвращает Y противном случае он не возвращает значение.

Обратите внимание, что, поскольку этот метод создает большую строку из всех данных в диапазоне ячеек, которые необходимо найти, в зависимости от типа данных в этих ячейках, он может вернуть Y неправильно. Например: если Sheet1!I2 имеет значение 123 и Sheet!G2 имеет значение 12 и Sheet!H2 имеет значение 34 , этот метод будет по-прежнему отображать Y поскольку "большая строка" будет содержать «1234 ...», а значение 123 будет в этой строке.

Еще один способ сделать это:

Способ 3: логический оператор - ИЛИ

[@ fixer1234 упомянул это первым]

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

При этом используется логический оператор OR чтобы проверить, находятся ли какие-либо значения в диапазоне G2:R2 в ячейке I2 на другом листе. Если логический оператор находит хотя бы одно значение, соответствующее ячейке I2 на другом листе, он возвращает Y или иначе не возвращает значение.

Способ 4: разложить формулу

1. Если вы хотите использовать только условия IF (по некоторым причинам, которые я не могу себе представить), вы можете распределить условия IF по нескольким ячейкам, а затем использовать результаты этих ячеек для определения окончательного результата. Например: у вас может быть несколько условий IF в одной ячейке, и вы можете использовать результат этого в другой ячейке вместе с несколькими другими условиями IF и так далее.

В ячейке A1 листа может быть следующее:

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

И тогда, вы можете иметь следующее в ячейке A2 листа:

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

Наконец, в ячейке A3 листа может быть следующее:

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

Метод 5: VBA

Используйте VBA, если вам удобно, чтобы создать пользовательскую функцию, определяемую пользователем, в которой вы можете иметь как можно больше вложенных операторов If .

Примечание. Вывод обеих этих формул аналогичен предоставленной вами формуле.