엑셀 Excel 가장 가까운 일치 항목 찾는 방법(가장 가까운 값 찾기)

엑셀 작업을 하다 보면 정리된 목록에서 정확하게 일치하는 값을 찾는 경우가 종종 있습니다.

만약 정확하게 일치하는 값이 아닌 목록에서 가장 가까운 값을 찾으려면 어떻게 하면 될까요?

정확하게 일치하는 값을 찾는 것은 쉬울 수 있습니다. 정확하게 일치하는 값이 없는 경우 결과 값이 없으므로 무시하면 됩니다.
하지만 가장 가깝게 일치하는 값을 찾는 경우라면 무조건 결과 값이 있어야 하므로 항상 결과 값을 찾아와야 합니다.

이번 글에서는 엑셀 Excel 가장 가까운 일치 항목 찾는 방법(가장 가까운 값 찾기)에 대해 설명해 보겠습니다.

엑셀에서 가장 가까운 일치 항목을 찾는 방법은 여러가지 있을 수 있습니다. 여기에서 설명하는 내용은 INDEX-MATCH 수식을 사용하여 가장 가까운 가까운 값을 찾는 방법입니다.

1. 아래 엑셀 시트는 일자별 금액을 정리한 파일입니다.
금액 목록에서 특정 금액, 여기서는 30만원에 가장 가까운 금액이 있는 일자를 찾아 보겠습니다.

2. 가장 가까운 값을 찾는 수식은 다음과 같습니다.

{=INDEX(A2:A14,MATCH(MIN(ABS(B2:B14-E2)), ABS(B2:B14-E2),0))}
이 수식은 배열 수식으로 처리해야 하므로 해당 수식에서 Ctrl + Shift + Enter를 눌러 배열 수식으로 지정해 주어야 합니다.
올바른 배열 수식이라면 앞,뒤에 대괄호({,})가 붙게 됩니다. 대괄호({,})를 직접 입력하면 안됩니다.

▶ 앞 단락에서 기술한 Excel에서 가장 가까운 일치 항목을 찾는 방법의 수식에 대해 좀 더 구체적으로 설명해 보겠습니다.

B2:B14-E2 : B2:B14 범위의 각 값에서 E2 값을 뺍니다.
배열 연산이므로 {-175000;-54790;221000;-297900;…..}과 같이 배열 값이 반환 됩니다.
ABS(B2:B14-E2) : ABS 함수는 절대 값을 구하는 함수입니다.
E2의 값과 가장 가까운 값을 찾기 위해 {175000;54790;221000;297900;…..}와 같이 절대 값으로 변환해 줍니다.
MIN(ABS(B2:B14-E2)) : MIN 함수는 최소 값을 구하는 함수입니다.
가장 작은 차이 값을 찾기 위해 MIN 함수를 사용합니다.
MATCH(MIN(ABS(B2:B14-E2)), ABS(B2:B14-E2),0) : 배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치(인덱스) 값을 찾습니다.
INDEX(A2:A14,MATCH(MIN(ABS(B2:B14-E2)), ABS(B2:B14-E2),0)) : MATCH에서 찾은 위치(인덱스) 값에서 일자(A2:A14)를 찾습니다.
만약 가장 가까운 동일한 값이 여러 개 존재한다면 첫 번째 일치하는 항목을 반환하게 됩니다.

엑셀 데이터에서 가장 가까운(근접한) 값을 찾는 방법에 대해 알아보았습니다.

여기에서는 가장 가까운 숫자 값에 대한 날짜를 찾는 방법에 대해 설명했지만, 특정 날짜와 가장 가까운 날짜를 찾는 방법도 동일한 수식으로 찾을 수 있습니다. 엑셀에서 날짜는 숫자 연산이 가능하므로 숫자와 동일한 방법으로 수식이 가능합니다.

임의의 값과 가장 가까운 숫자 값을 찾거나 날짜(일자)를 찾는 수식이 필요한 경우 여기에서 설명한 수식을 참고해 보시기 바랍니다.

♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.