가장 근접한 일치 값 조회

Excel 에서 숫자 데이터 집합 내에서 조회 값과 가장 근접한 값을 찾으려면INDEX,MATCH,ABS,및 MIN 함수를 함께 사용할 수 있습니다。

Excel 에서 가장 근접한 값을 찾는 방법은 무엇인가요？

상기 예시처럼$20,000 의 목표 실적과 가장 근접한 실적을 올린 영업사원을 확인하려면 INDEX， MATCH， ABS 및 MIN 함수를 조합한 수식이 다음과 같은 방식으로 도움을 줍니다。 ABS 함수는 각 영업사원의 실적과 판매 목표 간 차이값을 모두 양수로 변환한 후， MIN 함수가 가장 작은 차이값(즉， 가장 근접한 일치)를 찾습니다。 이제 MATCH 함수를 사용해 가장 근접한 일치 항목의 위치를 파악하고， INDEX 함수로 해당 위치의 값을 검색할 수 있습니다。

일반 구문

=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))

√ 참고： 이는 배열 수식으로， 입력 시Ctrl+Shift+Enter를 눌러야 합니다。

다음과 같이$20,000 의 목표 실적과 가장 근접한 실적을 올린 영업사원을 확인하려면 아래 수식을 셀 F5 에 복사하거나 입력한 후Ctrl+Shift+Enter를 눌러 결과를 얻으세요。

=INDEX()B5:B10,MATCH(MIN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))

또는 수식을 동적으로 만들기 위해 셀 참조를 사용하세요：

=INDEX()B5:B10,MATCH(MIN(ABS()))C5:C10-F4)),ABS(C5:C10-F4),0))

수식 설명

=INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4):부분C5:C10-F4은 범위C5:C10의 각 판매 실적과$20,000셀F4에 있는 목표 판매 실적 간의 차이값 전체를 다음과 같은 배열로 가져옵니다： {-4322;2451;6931；-1113;6591；-4782}。 ABS 함수는 모든 음수를 양수로 변환하여 다음과 같이 만듭니다：{4322;2451;6931;1113;6591;4782}.
• MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):MIN 함수는 배열{4322;2451;6931;1113;6591;4782}에서 가장 작은 숫자를 찾습니다。 이는 가장 작은 차이， 즉 가장 근접한 일치 항목을 의미합니다。 따라서 함수는1113을 반환합니다。
• MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0)=MATCH()1113,{4322;2451;6931;1113;6591;4782},0):match_type 0은 MATCH 함수가 배열1113내에서 정확히 일치하는 숫자{4322;2451;6931;1113;6591;4782}의 위치를 찾도록 강제합니다。 함수는 해당 숫자가4번째 위치에 있으므로4번째 위치를 반환합니다。
• INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))= INDEX()B5:B10,4):INDEX 함수는 이름 범위4의B5:B10에서Bale값을 반환합니다。

관련 함수

관련 수식