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. Select Specific Cells 대화 상자에서:

• Selection type(선택 유형) 항목에서 셀(Cell)을 선택합니다.
• 에서 Specific type(지정 유형):
  - 첫 번째 드롭다운 목록을 보다 크거나 같음(Greater than or equal to) 을 선택하고, 16 상자에 입력합니다.
  - 두 번째 드롭다운을 보다 작거나 같음(Less than or equal to) 을 선택한 후, 20.

3에 입력합니다. 그리고 확인(OK) 을 클릭하여 실행합니다. Kutools는 해당 조건을 충족하는 셀 수를 알려주고, 선택된 편차 내의 모든 근접 값을 아래와 같이 강조 표시해줍니다:

이 방법은 넓은 범위에서 여러 개의 근접 값을 한 번에 식별할 때 특히 유용합니다. 단, 편차 값을 명확히 지정하지 않으면, 관련 데이터가 누락되거나 불필요한 값이 포함될 수 있으니 주의하세요.

자동화를 원하거나 여러 시트 또는 큰 데이터셋에서 숫자나 텍스트 데이터에 대해 근접값 찾기를 맞춤형으로 수행하고자 하는 분에게는 VBA 매크로가 매우 효율적이고 유연한 솔루션이 될 수 있습니다. Excel에서 목표값과 후보 값 사이의 차이를 체계적으로 비교하도록 프로그래밍함으로써, 숫자뿐만 아니라 텍스트(문자열 유사도 기준)도 가장 가까운 항목을 찾을 수 있습니다.

이 방법은 자동화 통합이 필요하거나 수작업이 어려운 넓은 범위, 반복 작업 처리 시에 특히 효과적입니다. 다만, VBA 매크로를 사용하려면 매크로 활성화와 VBA 환경에 대한 기초적인 이해가 필요합니다. 매크로 실행 전에는 항상 데이터를 백업해 예기치 않은 데이터 손실을 막으세요.

1. 개발자(Developer) > Visual Basic을 클릭합니다. Microsoft Visual Basic for Applications 창에서 삽입(Insert) > 모듈(Module)을 선택한 뒤, 아래 코드를 복사해 넣으세요:

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. 홈(Home) 탭에서 조건부 서식(Conditional Formatting) > 새 규칙(New Rule)을 클릭하세요.

3. 대화 상자에서 수식을 사용하여 서식 지정할 셀 결정(Use a formula to determine which cells to format )을 선택한 뒤, 수식란에 다음 수식을 입력하세요:

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

4. 서식(Format)을 클릭해 강조 색상을 선택하고, OK를 클릭한 후 다시 한 번 OK를 눌러 규칙을 적용하세요.

이제 선택한 범위 내에서 E2의 목표 값과 가장 가까운 값이 있는 모든 셀이 강조 표시됩니다.

대상 범위가 크거나 예상과 다른 결과가 나타날 경우, 셀 참조가 올바른지, 절대/상대 참조가 의도대로 적용되어 있는지($ 기호로 고정 여부 확인)를 반드시 점검하세요.