가장 가까운 숫자 찾고 동률도 남기기
입력값보다 작거나 큰 후보 중에서 가장 가까운 숫자를 찾아보세요. 후보별 차이를 제곱해 최솟값과 대조하면, 같은 거리만큼 떨어진 후보도 모두 남길 수 있습니다.
입력값 9에 가장 가까운 후보가 8과 10이라면 둘 다 거리가 1입니다. 입력값보다 작은 후보만 찾으면 10을 놓칩니다. JOIN SHEET에서 각 입력값을 모든 후보에 연결하고 두 수의 차이를 제곱하세요. 입력값마다 제곱한 차이의 최솟값을 구한 뒤, 차이의 제곱이 그 최솟값과 같은 후보만 남깁니다. 그러면 8과 10처럼 양쪽에서 같은 거리만큼 떨어진 후보도 모두 찾을 수 있습니다.
작은 쪽으로만 찾으면 놓치는 후보
가상 입력 표 Targets는 2행, 후보 표 References는 3행입니다. 모든 입력값을 모든 후보와 대조할 수 있도록, 불러오기 전에 두 표의 MatchAll 칼럼에 직접 숫자 1을 채웁니다. TargetID와 ReferenceID는 각 원본에서 중복되지 않아야 합니다. 뒤의 작업에서는 후보별 계산 결과를 Candidates, 입력값별 최솟값을 모은 표를 Minimums라는 이름으로 각각 저장합니다.
Targets
| TargetID | Target | MatchAll |
|---|---|---|
| T1 | 9 | 1 |
| T2 | 15 | 1 |
References
| ReferenceID | ReferenceValue | MatchAll |
|---|---|---|
| R1 | 8 | 1 |
| R2 | 10 | 1 |
| R3 | 20 | 1 |
후보 거리와 최소 거리를 각각 저장하기
- Targets와 References를 불러와 MatchAll끼리 ‘일치하는 데이터만’으로 연결합니다. 2×3=6개 후보가 생깁니다. MatchAll 두 칼럼만 숨기고 Target·ReferenceValue는 숫자로 인식되는지 확인합니다.
- ‘결과 정리’에서 ‘계산’을 누릅니다. ‘결과 항목 이름’에는 Delta를 입력하고, ‘계산 방식’은 ‘빼기’, 첫 번째 값은 ReferenceValue, 두 번째 값은 Target으로 선택하세요. 그 아래에 ‘계산’을 추가해 ‘곱하기 · A × B’를 고르고, A와 B를 모두 Delta로 선택해 이름을 Squared로 지정합니다. 차이를 제곱하면 입력값보다 작은 후보와 큰 후보를 같은 기준으로 비교할 수 있습니다.
- 두 계산을 적용한 6행을 Candidates로 Excel 저장합니다. 결과 칼럼은 TargetID·Target·ReferenceID·ReferenceValue·Delta·Squared입니다. 이 전체 후보 파일을 다음 요약 결과로 덮어쓰지 않습니다.
- ‘결과 정리’를 다시 열고 두 계산 아래에 ‘요약’을 추가합니다. ‘그룹화 기준’은 TargetID로 선택하세요. 기본 집계의 ‘집계 방식’을 ‘최솟값’으로 바꾸고 ‘항목’은 Squared, ‘결과 항목 이름’은 MinSquared로 설정합니다. ‘적용’을 누른 뒤 T1=1, T2=25의 2행을 별도 Minimums 파일로 저장합니다.
- 새 작업에 Candidates와 Minimums를 불러옵니다. TargetID끼리 연결하고 Squared와 MinSquared도 연결한 뒤 ‘일치하는 데이터만’을 선택합니다. 입력 ID와 제곱한 차이가 모두 같아야 연결되므로, 각 입력값에서 가장 가까운 후보만 남습니다. Minimums의 TargetID는 숨깁니다.
- 결과 미리보기에서 T1의 R1·R2, T2의 R2·R3 총 4행을 확인해 저장합니다. 후보 저장부터 요약, 재불러오기까지는 각각 직접 진행해야 합니다.
가까운 값이 둘이라면 둘 다 남습니다
T1의 입력값 9에는 8과 10이, T2의 입력값 15에는 10과 20이 남습니다. 각 입력값에서 가장 가까우면서 거리도 같은 후보들입니다. Squared는 거리를 제곱한 값입니다. 부호만 다른 차이를 제곱하면 값이 같아지므로, 입력값보다 큰 후보도 함께 찾을 수 있습니다.
| TargetID | Target | ReferenceID | ReferenceValue | Delta | Squared | MinSquared |
|---|---|---|---|---|---|---|
| T1 | 9 | R1 | 8 | -1 | 1 | 1 |
| T1 | 9 | R2 | 10 | 1 | 1 | 1 |
| T2 | 15 | R2 | 10 | -5 | 25 | 25 |
| T2 | 15 | R3 | 20 | 5 | 25 | 25 |
후보 수와 숫자 정밀도부터 확인하기
처음 연결할 때는 입력값 수×후보 수만큼 결과가 생깁니다. 이 예제는 6행이지만 표가 커지면 결과도 빠르게 늘어나므로 먼저 개수를 가늠해 보세요. 빈 값이나 숫자가 아닌 값은 어떻게 처리할지 정해야 합니다. 소수나 매우 큰 수를 쓴다면 계산 정밀도와 저장 전후의 값도 확인하세요. 가장 가까운 후보가 여러 개여도 하나만 골라야 한다면, 동률일 때 무엇을 택할지 별도 기준이 필요합니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
Find nearest value like LOOKUP? (Reddit r/excel)
입력값보다 작거나 큰 숫자 중에서 가장 가까운 값을 찾는 질문을 참고했습니다. 위 표와 작업 순서는 설명을 위해 별도로 만든 가상 예제이며, 질문자의 JOIN SHEET 이용 후기는 아닙니다.
