Excel 수식： 셀에 여러 값 중 하나는 포함하되 다른 값은 제외하기

두 개의 값 목록이 있다고 가정할 때, 셀 B3 에 범위 E3:E5 의 값 중 하나가 포함되어 있지만 아래 스크린샷과 같이 범위 F3:F4 의 값은 전혀 포함되지 않도록 확인하고자 합니다. 이 자습서에서는 Excel 에서 이러한 작업을 빠르게 처리할 수 있는 수식을 제공하고 수식의 인수를 설명합니다。

일반 수식：

인수

반환 값：

이 수식은 1 또는 0 를 반환합니다. 셀에 포함되어야 하는 값 중 하나가 포함되어 있고 동시에 제외되어야 하는 값을 전혀 포함하지 않을 경우 1 를 반환하며, 그렇지 않으면 0 를 반환합니다. 이 수식에서 1 및 0 는 논리값 TRUE 와 FALSE 처럼 처리됩니다。

수식 작동 방식

셀 B3 에 범위 E3:E5 의 값 중 하나가 포함되어 있고 동시에 범위 F3:F4 의 값을 제외하려면 다음 수식을 사용하세요。

다음을 누르세요：Enter키를 눌러 확인 결과를 얻습니다。

설명

파트 1：(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)는 셀에 E3:E5 범위의 값이 포함되어 있는지 확인합니다。

SEARCH함수： SEARCH 함수는 한 텍스트 문자열 내에서 다른 문자열의 첫 번째 문자 위치를 반환합니다。 SEARCH 함수가 일치하는 텍스트를 찾으면 상대적 위치를 반환하고， 찾지 못하면 #VALUE！ 오류를 반환합니다。 예를 들어， 여기서 수식SEARCH($E$3:$E$5,B3)은 셀 B3 에서 범위 E3:E5 의 각 값을 검색하여 셀 B3 내에서 각 텍스트 문자열의 위치를 반환합니다。 이는 다음과 같은 배열 결과를 반환합니다：{1;7;12}.

ISNUMBER 함수: ISNUMBER 함수는 셀이 숫자일 경우 TRUE 를 반환합니다。 따라서ISNUMBER(SEARCH($E$3:$E$5,B3))은 SEARCH 함수가 3 개의 숫자를 찾았으므로 {true,true,true}와 같은 배열 결과를 반환합니다。

--ISNUMBER(SEARCH($E$3:$E$5,B3))는 TRUE 값을 1 로 변환하고 FALSE 값을 0 으로 변환하므로， 이 수식은 배열 결과를 다음과 같이 변경합니다：{1;1;1}.

SUMPRODUCT함수： 여러 범위를 곱하거나 배열을 합산하여 곱의 합계를 반환하는 데 사용됩니다。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))은 1+1+1=3 를 반환합니다。

마지막으로 왼쪽 수식SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))와 0 을 비교하여 왼쪽 수식의 결과가 0 보다 크기만 하면 결과는 TRUE 가 되며, 그렇지 않으면 FALSE 를 반환합니다. 여기서는 TRUE 를 반환합니다。

파트 2：(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)는 셀에 F3:F4 범위의 값이 포함되지 않는지 확인합니다。

수식SEARCH($F$3:$F$4,B3)은 셀 B3 에서 범위 E3:E5 의 각 값을 검색하여 셀 B3 내에서 각 텍스트 문자열의 위치를 반환합니다。 이는 다음과 같은 배열 결과를 반환합니다：{#VALUE!;#VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3))은 SEARCH 함수가 0 개의 숫자를 찾았으므로 다음과 같은 배열 결과를 반환합니다：{false;false}.

--ISNUMBER(SEARCH($F$3:$F$4,B3))는 TRUE 값을 1 로 변환하고 FALSE 값을 0 로 변환하므로， 이 수식은 배열 결과를 다음과 같이 변경합니다：{0;0 }.

SUMPRODUCT함수： 여러 범위를 곱하거나 배열을 합산하여 곱의 합계를 반환하는 데 사용됩니다。 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))은 0+0=0 를 반환합니다。

마지막으로 왼쪽 수식SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))와 0 을 비교하여 왼쪽 수식의 결과가 0 와 같기만 하면 결과는 TRUE 를 반환하며, 그렇지 않으면 FALSE 를 반환합니다. 여기서는 TRUE 를 반환합니다。

파트 3: 두 수식 곱하기

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

이 수식에서 1 및 0 는 논리값 TRUE 와 FALSE 처럼 처리됩니다。

샘플 파일

샘플 파일 다운로드 클릭

관련 수식

• 셀에 특정 텍스트가 포함되어 있는지 확인하기
  셀에 범위 A 의 일부 텍스트는 포함되지만 범위 B 의 텍스트는 포함되지 않도록 확인하려면 Excel 에서 COUNT， SEARCH 및 AND 함수를 결합한 배열 수식을 사용할 수 있습니다。
• 셀에 여러 값 중 하나가 포함되어 있는지 확인하기
  이 자습서에서는 Excel 에서 셀에 여러 값 중 하나가 포함되어 있는지 확인하는 수식을 제공하고， 수식의 인수와 작동 방식을 설명합니다。
• 셀에 특정 값 중 하나가 포함되어 있는지 확인하기
  Excel 에서 열 E 에 값 목록이 있고, 열 B 의 셀에 열 E 의 모든 값이 포함되어 있는지 확인하여 TRUE 또는 FALSE 를 반환하려고 합니다。
• 셀에 숫자가 포함되어 있는지 확인하기
  때로는 셀에 숫자 문자가 포함되어 있는지 확인해야 할 경우가 있습니다. 이 자습서에서는 셀에 숫자가 포함되어 있으면 TRUE 를, 그렇지 않으면 FALSE 를 반환하는 수식을 제공합니다。