뛰어나다

첫 번째 일치 셀 가져오기 포함

Get First Match Cell Contains

Excel 수식: 첫 번째 일치 셀 가져오기 포함일반 공식 |_+_| 요약

여러 항목 중 하나에 대해 셀을 확인하고 목록에서 찾은 첫 번째 일치 항목을 반환하려면 SEARCH 또는 FIND를 사용하여 일치 항목을 찾는 INDEX / MATCH 수식을 사용할 수 있습니다. 표시된 예에서 C5의 공식은 다음과 같습니다.



{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}

여기서 '사물'은 명명된 범위 E5:E9.

Excel에서 날짜에 1 년 추가

참고: 이것은 배열 수식 Control + Shift + Enter로 입력해야 합니다.





설명

이 예에는 'things'(E5:E9)라는 명명된 범위의 색상 목록이 있습니다. B열의 텍스트에 이러한 색상이 포함되어 있는지 확인하려고 합니다. 그렇다면 발견된 첫 번째 색상의 이름을 반환하고 싶습니다.

내부에서 외부로 작업하는 이 공식은 다음을 사용합니다. ISNUMBER 함수 그리고 검색 기능 다음과 같이 'things'에 나열된 각 색상에 대해 B5의 텍스트를 검색하려면:



 
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,B5)),0))}

이 표현식은 공식( 여기에서 자세히 설명 ) 단일 부분 문자열에 대한 셀을 확인합니다. 셀에 하위 문자열이 포함된 경우 표현식은 TRUE를 반환합니다. 그렇지 않은 경우 표현식은 FALSE를 반환합니다.

우리가 이 SEARCH를 줄 때 물건의 목록 (한 가지 대신) 결과 배열을 반환합니다. 발견된 각 색상은 숫자 위치를 생성하고 찾을 수 없는 색상은 오류를 생성합니다.

 
 ISNUMBER ( SEARCH (things,B5)

그런 다음 ISNUMBER 함수는 결과를 TRUE / FALSE 값으로 변환합니다. 모든 숫자는 TRUE가 되고 오류(찾을 수 없음)는 FALSE가 됩니다. 결과는 다음과 같은 배열입니다.

 
{#VALUE!#VALUE!20#VALUE!#VALUE!}

이 배열은 매치 기능 배열 인수로. 조회 값은 TRUE이고 일치 유형은 정확히 일치하도록 0으로 설정됩니다. 일치하는 색상이 있는 경우 MATCH는 TRUE가 처음 발견된 위치를 반환합니다. 이 값은 인덱스 기능 행 번호로, 명명된 범위 'things'가 배열로 제공됩니다. 일치하는 항목이 하나 이상 있으면 INDEX는 해당 위치의 색상을 반환합니다. 일치하는 항목이 없으면 이 수식은 #N/A 오류를 반환합니다.

하드 코딩된 값 사용

이 예에서 'things'와 같은 외부 명명된 범위를 설정하지 않으려면 다음과 같이 값을 수식에 '배열 상수'로 하드 코딩할 수 있습니다.

Excel에 3D 클러스터형 세로 막 대형 차트 삽입
 
{FALSEFALSETRUEFALSEFALSE}

셀에서 첫 번째 일치 항목 가져오기

여기의 언어는 매우 혼란스럽지만 위의 공식은 찾을 것들의 목록 . 대신 찾은 첫 번째 일치 항목을 반환하려면 테스트 중인 세포에서 , 다음과 같은 공식을 시도할 수 있습니다.

 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

이 버전의 수식에서 MATCH 함수는 다음 스니펫의 결과를 찾도록 설정됩니다.

 
= INDEX (things, MATCH ( AGGREGATE (15,6, SEARCH (things,A1),1), SEARCH (things,A1),0))

사용하는 집계 함수 SEARCH에서 반환된 결과에서 최소값을 가져옵니다. 들어오는 배열에 오류가 포함될 가능성이 있기 때문에 여기에서 AGGREGATE가 필요하고(물건을 찾지 못하면 SEARCH에서 반환됨) 이러한 오류를 무시하고 여전히 최소 숫자 값을 제공하는 함수가 필요합니다.

AGGREGATE의 결과는 SEARCH에서 반환된 동일한 배열과 함께 조회 값으로 MATCH에 직접 반환됩니다. 최종 결과는 항목 목록에서 찾은 첫 번째 일치 항목이 아니라 셀에서 발견된 첫 번째 일치 항목입니다.

저자 데이브 브런스


^