Excel에서 가장 가까운 값 또는 가장 근접한 값을 찾는 방법

예를 들어, 열 B에 숫자 목록이 있다고 가정하고, 이 중에서 주어진 숫자(예:18)에 가장 가까운 값을 찾아야 한다고 합시다. Excel의 배열 수식을 사용하면 목록을 직접 확인하지 않고도 효율적으로 해당 값을 찾을 수 있습니다.

먼저, 빈 셀을 선택한 다음 아래 수식을 입력하세요. 수식 입력 후에는 반드시 Enter키만 누르는 것이 아니라 Ctrl + Shift + Enter를 동시에 눌러야 합니다. 그래야 이 수식이 배열 수식으로 올바르게 동작합니다.

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

이 방법은 연속된 범위에서 단일로 가장 가까운 숫자를 검색할 때 적합합니다. 수치적 정확성과 정확한 일치가 중요한 대부분의 상황에서 잘 동작합니다. 단, 배열 수식은 자료가 매우 클 경우 시스템 자원을 많이 사용할 수 있습니다. 성능 저하나 #VALUE!와 같은 오류가 발생하면 셀 참조가 올바른지 확인하고 반드시 Ctrl + Shift + Enter를 눌렀는지 점검하세요.

단일로 가까운 값만 필요한 것이 아니라, 목표 값에서 일정 범위(편차 범위) 내에 있는 모든 숫자를 선택해야 할 때도 있습니다. Kutools for Excel의 Select Special Cells 기능을 사용하면 목표 값에서 지정된 차이만큼 떨어진 모든 값을 빠르게 선택할 수 있습니다.

예를 들어 목표 값이18이고, 편차 값을2로 정했다면 범위 내에서16(18-2)부터20(18+2) 사이 모든 값을 선택하고 싶을 수 있습니다. 아래 단계별로 따라해 보세요:

1. 원하는 범위를 선택합니다(예: B3:B22). 이어서 Kutools > 선택 > 특정 셀 선택을 클릭합니다.

2. 특정 셀 선택 대화상자에서:

• 선택 유형(Selection type)에서 셀(Cell)을 선택합니다.
• 에서 지정 유형:
  - 첫 번째 드롭다운 목록을 보다 크거나 같음 로 설정하고 16 박스에 입력합니다.
  - 두 번째 드롭다운을 보다 작거나 같음 로 설정하고 20.

3에 입력합니다. 그런 다음 확인 을 눌러 실행하세요. Kutools가 조건을 충족하는 셀 개수를 알려주고, 아래와 같이 지정한 편차 내의 모든 가까운 값을 강조 표시합니다.

이 방법은 특히 넓은 범위에서 다양한 허용오차로 대량의 인접 값을 빠르게 찾을 때 효과적입니다. 단, 올바른 편차 값 설정이 중요하므로 범위가 너무 좁거나 넓게 설정되면 관련 데이터를 놓치거나 원치 않는 값이 포함될 수 있습니다.

자동화가 필요하거나 여러 시트 또는 대용량 데이터셋에서 맞춤형 근접 값(숫자 또는 텍스트 데이터) 찾기가 필요한 경우, VBA 매크로를 활용하면 효율적이고 유연하게 처리할 수 있습니다. 엑셀이 지정한 목표 값과 후보 값 전체를 자동으로 비교하도록 프로그래밍하면, 가장 가까운 숫자뿐 아니라 텍스트 유사성이 가장 높은 문자열도 추출이 가능합니다.

이 방식은 수동 처리로는 벅찬 큰 범위 또는 반복 작업에서 자동화가 필요할 때 특히 유리합니다. 단, VBA 매크로 사용을 위해 매크로 활성화와 VBA 환경에 대한 기본 이해가 필요합니다. 매크로 실행 전에는 예기치 않은 데이터 손실 예방을 위해 반드시 백업해 두시기 바랍니다.

1. 개발 도구 > Visual Basic을 클릭합니다. Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 눌러 아래 코드를 복사해 붙여넣으세요.

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. 시트로 돌아가 빈 셀에 다음 수식을 입력하세요: =FindClosest(B3:B22, E2) 그리고 Enter키를 눌러 가장 가까운 값을 구합니다.

데이터를 검토하거나 발표할 때, 필터나 정렬 없이도 목표 값에 가장 가까운 값을 시각적으로 식별하면 매우 유용합니다. Excel의 내장 조건부 서식 기능을 사용하면, 목표 값에 가장 근접한 셀을 강조해 한눈에 확인할 수 있습니다. 이 방법은 정확한 값을 반환하지는 않지만 빠른 데이터 분석과 시각적 강조에 매우 효과적입니다.

이 방법의 주된 장점은 원본 데이터를 변경하지 않고도 데이터나 목표 값이 바뀌면 즉시 하이라이트가 동적으로 변경된다는 점입니다. 특히 대시보드, 프레젠테이션, 검토 상황 등에서 가시성이 중요한 경우에 적합합니다. 단, 여러 값이 동일하게 “가까운” 값일 경우 정확도가 떨어질 수 있으며, 검색된 값을 추가 처리로 반환하지는 않습니다.

1. 분석할 셀 범위를 선택합니다(예: B3:B22).

2. 홈 탭에서 조건부 서식 > 새 규칙을 클릭합니다.

3. 대화상자에서 '서식이 지정될 셀을 결정할 수식을 사용'을 선택한 후, 수식 입력란에 아래 수식을 입력하세요.

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. 서식(Format) 버튼을 클릭해 강조 색상을 선택한 후, 확인을 클릭해 규칙을 적용합니다.

이렇게 하면 선택한 범위 내에서 E2의 목표 값에 가장 가깝거나 동일하게 가까운 모든 셀이 강조됩니다.

범위가 넓거나 결과가 예상과 다르다면, 참조가 올바르게 되어 있는지, 절대/상대 참조 사용이 의도대로 적용되어 있는지(대상 셀 및 범위 참조에 $ 기호 사용) 꼭 확인하세요.