메뉴
실무 위키›응용 공식›엑셀 가장 가까운 값 찾기 공식

엑셀 가장 가까운 값 찾기 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

실측 치수와 규격표의 차이가 가장 작은 항목을 자동으로 선택합니다.

예제파일 다운로드
엑셀 가장 가까운 값 찾기 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=INDEX ( 규격 범위, MATCH ( MIN ( ABS ( 규격 범위 - 실측 ) ), ABS ( 규격 범위 - 실측 ), 0 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
규격 범위필수비교할 규격이 세로로 적힌 범위입니다. 규격 목록을 한꺼번에 계산하므로 엑셀 2019 이하에서는 수식을 입력한 뒤 Ctrl + Shift + Enter로 마쳐야 합니다. 같은 거리의 규격이 둘이면 표에서 먼저 나온 값이 선택됩니다.
실측필수비교 기준이 되는 실제 측정값이 들어 있는 셀입니다. 규격이 더 크거나 작아도 차이의 절댓값으로 비교합니다.
B2=INDEX($D$2:$D$6,MATCH(MIN(ABS($D$2:$D$6-A2)),ABS($D$2:$D$6-A2),0))
A
B
C
D
E
1
실측
가까운 규격
규격
2
147
150
120
3
135
4
150
5
165
6
180
7
단위는 mm입니다. 147과 120, 135, 150, 165, 180의 차이를 비교하면 150과의 차이가 3으로 가장 작습니다. 엑셀 2019 이하에서는 수식을 입력한 뒤 Ctrl + Shift + Enter로 마쳐야 합니다.
이 공식이 사용하는 함수
INDEX 함수 MATCH 함수 MIN 함수 ABS 함수

동작 원리

ABS 함수가 실측과 각 규격의 차이를 절댓값으로 바꾸고, MIN 함수와 MATCH 함수로 가장 작은 차이의 위치를 찾아 INDEX 함수가 해당 규격을 가져옵니다.

예제 시트에서 147mm에 가장 가까운 규격을 구하는 B2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

각 규격과 실측 치수의 차이를 구합니다

규격 범위에서 실측값 147을 빼면 -27, -12, 3, 18, 33이 됩니다. ABS 함수로 부호를 없애면 거리만 27, 12, 3, 18, 33으로 남습니다.

=INDEX ( $D$2:$D$6, MATCH ( MIN ( ABS ( $D$2:$D$6 - A2 ) ), ABS ( $D$2:$D$6 - A2 ), 0 ) )
= 27, 12, 3, 18, 33 120부터 180까지 각 규격과 147의 거리입니다.
2

가장 작은 차이를 찾습니다

MIN 함수가 다섯 개의 차이에서 최솟값 3을 남깁니다. 따라서 실측값과 3mm 떨어진 규격이 검색 대상이 됩니다.

=MIN ( ABS ( $D$2:$D$6 - A2 ) )
= 3 147과 150의 차이입니다.
3

최솟값이 있는 위치를 찾습니다

MATCH 함수는 최솟값 3이 거리 목록에서 몇 번째인지 정확히 찾습니다. 마지막 인수 0은 정확히 같은 값을 찾으라는 뜻이며, 결과 3은 규격표의 세 번째 위치를 가리킵니다.

=MATCH ( 3, ABS ( $D$2:$D$6 - A2 ), 0 )
= 3 규격 범위의 세 번째 위치입니다.
4

찾은 위치의 규격을 가져옵니다

INDEX 함수에 규격 범위와 세 번째 위치를 넣으면 D4 셀의 150이 나옵니다. 따라서 B2 셀 결과는 150입니다.

=INDEX ( $D$2:$D$6, 3 )
= 150 147mm보다 큰 규격도 차이가 더 작으면 결과가 될 수 있습니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료