☰

Достигнут предел вложенных ЕСЛИ в Excel

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
Достигнут предел вложенных ЕСЛИ в Excel

Кто-нибудь знает, как сократить эту формулу Excel, поскольку я достиг предела вложенных «ЕСЛИ»?

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

решение1

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

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

Мы оба пришли к логике OR, показанной ниже в качестве моего последнего подхода. Я оставлю этот ответ для любой образовательной или новизны, которую он может предоставить. Но ответ 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 представляет true и false.

    Результаты всех этих тестов суммируются. Если сумма равна 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","")

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

решение2

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

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

Метод 1: ГПР/ВПР

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

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

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

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

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

Метод 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операторов.

Примечание: результат обеих этих формул совпадает с результатом формулы, которую вы предоставили.

Связанный контент