Excel에서 중첩된 IF 제한에 도달했습니다.

Question 1

메모:아래의 내 대답은 조건이 병렬로 평가되는 디지털 논리 회로와 같은 방식으로 접근되었습니다. 이 경우 모든 조건을 명시적으로 제공해야 합니다. 여기서는 완전히 과잉이었습니다. 필요한 것보다 훨씬 더 복잡합니다.

thilina R의 답변에 표시된 접근 방식은 Excel이 수식과 범위를 순서대로 평가한다는 사실을 활용하므로 Excel은 이미 질문의 중첩 IF와 유사한 가능한 조건을 제한하는 대부분의 작업을 수행하고 있습니다.

우리 둘 다 아래의 마지막 접근 방식으로 표시된 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이 참과 거짓을 나타내는 방식입니다.

이러한 모든 테스트의 결과가 합산됩니다. 합계가 이면 1테스트 중 하나만 참이라는 의미입니다.
실제로 해당 사례 중 하나 이상이 참인지에만 관심이 있는 경우 간단한 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","")

논리를 더 쉽게 볼 수 있도록 모든 수식 내에 줄 바꿈과 추가 공백을 추가했습니다. 복사하여 붙여넣으려면 해당 항목을 제거해야 합니다.

Answer

메모:아래의 내 대답은 조건이 병렬로 평가되는 디지털 논리 회로와 같은 방식으로 접근되었습니다. 이 경우 모든 조건을 명시적으로 제공해야 합니다. 여기서는 완전히 과잉이었습니다. 필요한 것보다 훨씬 더 복잡합니다.

thilina R의 답변에 표시된 접근 방식은 Excel이 수식과 범위를 순서대로 평가한다는 사실을 활용하므로 Excel은 이미 질문의 중첩 IF와 유사한 가능한 조건을 제한하는 대부분의 작업을 수행하고 있습니다.

우리 둘 다 아래의 마지막 접근 방식으로 표시된 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이 참과 거짓을 나타내는 방식입니다.

이러한 모든 테스트의 결과가 합산됩니다. 합계가 이면 1테스트 중 하나만 참이라는 의미입니다.
실제로 해당 사례 중 하나 이상이 참인지에만 관심이 있는 경우 간단한 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","")

논리를 더 쉽게 볼 수 있도록 모든 수식 내에 줄 바꿈과 추가 공백을 추가했습니다. 복사하여 붙여넣으려면 해당 항목을 제거해야 합니다.

Question 2

기본적으로 한 시트의 셀 중 하나라도 다른 시트의 G2:R2셀과 동일한 값을 가지고 있는지 알고 싶습니다 .I2

이를 수행하는 방법에는 여러 가지가 있습니다. 확인하는 값이 연속적인 범위에 있기 때문에 가장 쉽게 생각할 수 있는 방법은 다음과 같습니다.

방법 1: HLOOKUP/VLOOKUP

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

이 기능은 한 시트의 셀 값을 조회 I2하고 해당 값이 범위의 다른 시트에 있는지 확인합니다 G2:R2. HLOOKUP범위에서 다른 시트의 G2:R2셀과 일치하는 값을 하나 이상 찾으면 값을 반환 하거나 그렇지 않으면 값을 반환하지 않습니다.I2Y

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이 있는 경우 "큰 문자열"에 "1234..."가 포함되고 값이 이 문자열에 있으므로 34이 메서드는 계속 표시됩니다 .Y123

이를 수행하는 또 다른 방법은 다음과 같습니다.

방법 3: 부울 연산자 - OR

[@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셀과 일치하는 값을 하나 이상 찾으면 값을 반환 하거나 그렇지 않으면 값을 반환하지 않습니다.I2Y

방법 4: 공식을 펼치세요

조건 만 사용하려는 경우 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.

참고: 이 두 수식의 출력은 제공한 수식과 동일합니다.

Answer