구간 표에서 근사값 일치시키기

계층 표는 연속적인 값을 여러 구간으로 나눕니다. 세율 구간, 무게별 배송비, 수량 할인, 점수별 문자 등급 등이 그 예입니다.

가능한 모든 값마다 행을 만드는 것이 아니라 각 구간의 시작 임계값만 저장합니다. 점수 87에 대한 정확한 항목은 없지만, 80에서 시작하는 구간에 속합니다.

이때 근사 일치가 유용합니다. 정확한 값이 반드시 있어야 하는 대신 올바른 구간을 찾아줍니다.

지금까지는 MATCH(value, range, 0)을 사용해 정확히 일치하는 값을 찾았습니다. 세 번째 인수 0은 "이 값을 정확히 찾고, 찾지 못하면 #N/A를 반환"한다는 뜻입니다.

계층을 조회할 때는 대신 일치 유형 1을 사용합니다. 이 유형은 조회 값보다 작거나 같은 값 중 가장 큰 값을 찾습니다. 계층 조회가 작동해야 하는 방식과 정확히 같습니다.

한 가지 확실한 규칙이 있습니다. 일치 유형 1을 사용할 때는 임계값 목록을 오름차순으로 정렬해야 합니다.

등급 표를 생각해 보세요. E열에는 하한 임계값이 오름차순으로 정렬되어 있습니다: 0, 60, 70, 80, 90. F열에는 레이블이 있습니다: F, D, C, B, A.

0~59점은 F, 60~69점은 D이며, 나머지도 같은 방식으로 정해집니다. 모든 점수를 저장하는 것이 아니라 각 구간의 시작점만 저장합니다.

G1의 점수가 주어졌을 때 문자 등급을 반환하는 것이 목표입니다.

근사 MATCH를 사용해 점수가 어느 구간에 속하는지 찾습니다. 점수가 87일 때 MATCH(G1, E2:E6, 1)은 87 이하인 가장 큰 임계값을 찾습니다.

임계값은 0, 60, 70, 80, 90입니다. 87을 넘지 않는 가장 큰 값은 80이며 위치 4에 있습니다. 따라서 MATCH는 4를 반환합니다.

이 위치가 올바른 구간을 가리키므로, 87 자체가 목록에 없어도 문제없이 조회할 수 있습니다.

이제 위치를 레이블 열 F2:F6의 INDEX에 전달합니다.

INDEX(F2:F6, MATCH(G1, E2:E6, 1))은 위치 4를 받아 네 번째 레이블인 "B"를 반환합니다.

따라서 점수 87은 올바르게 B 등급으로 변환됩니다. G1을 95로 바꾸면 MATCH는 5를 반환하여 "A"가 되고, 55로 바꾸면 MATCH는 1을 반환하여 "F"가 됩니다.

근사 MATCH(유형 1)는 조회 범위를 오름차순으로 정렬해야 합니다. 데이터가 점점 커진다고 가정하고, 조회 값을 지나치는 즉시 검색을 멈춥니다.

임계값의 순서가 뒤섞여 있으면 MATCH가 너무 일찍 멈춰 잘못된 위치를 반환할 수 있습니다. 오류가 표시되지 않아 조용히 잘못된 결과가 나올 수도 있습니다. 계층 조회를 사용하기 전에 항상 임계값 열을 작은 값부터 큰 값 순서로 정렬하세요.

XLOOKUP도 근사 일치를 지원합니다. 다섯 번째 인수인 일치 모드에는 "정확히 일치하는 항목 또는 그다음으로 작은 항목"을 뜻하는 -1을 지정할 수 있으며, 계층 표에 안성맞춤입니다.

이렇게 하면 G1 이하인 가장 큰 임계값을 찾아 일치하는 레이블을 반환하므로 INDEX가 필요하지 않습니다. 구간 조회에서는 INDEX-MATCH보다 읽고 이해하기 쉬운 경우가 많습니다.

이번에는 수량 할인의 예를 살펴보겠습니다. E열의 임계값(주문 수량)은 0, 10, 50, 100입니다. F열의 할인율은 0%, 5%, 10%, 15%입니다.

• 7개 주문: 7 이하인 가장 큰 임계값은 0이며, 위치 1이므로 0%를 반환합니다.
• 60개 주문: 60 이하인 가장 큰 임계값은 50이며, 위치 3이므로 10%를 반환합니다.
• 200개 주문: 200 이하인 가장 큰 임계값은 100이며, 위치 4이므로 15%를 반환합니다.

수량에 관계없이 하나의 수식으로 처리할 수 있습니다.

값이 모든 임계값보다 작으면 어떻게 될까요? 근사 MATCH는 해당 값 이하인 값이 없으므로 #N/A를 반환합니다.

이를 방지하려면 첫 번째 임계값이 하한을 포함하도록 하세요(대개 0으로 설정합니다). 또는 수식을 IFERROR로 감싸 입력이 범위를 벗어났을 때 명확한 메시지를 표시하세요.

계층 표에서 다음과 같은 함정을 주의하세요.

• 정렬되지 않은 임계값: 잘못되었지만 오류 없이 반환되는 결과의 가장 큰 원인입니다.
• 일치 유형 0 사용: 정확히 일치하는 값만 요구하므로 중간 값에 대해 #N/A를 반환합니다.
• 시작점 대신 구간 끝점 저장: 유형 1의 MATCH는 각 구간의 상한이 아니라 하한을 요구합니다.
• 텍스트로 저장된 임계값: 숫자가 텍스트로 저장되면 비교가 제대로 되지 않으므로 숫자 형식으로 유지하세요.

근사 일치와 양방향 기법을 결합할 수 있습니다. 무게 구간(행)과 지역 구간(열)에 따른 배송비를 생각해 보세요.

근사 MATCH(유형 1)를 하나 사용해 무게 행을 찾고, 다른 하나를 사용해 지역 열을 찾은 다음 두 위치를 INDEX에 전달합니다. 두 축 모두 임계값이 정렬되어 있으므로 각 MATCH가 올바른 구간을 찾습니다.

이렇게 하면 INDEX-MATCH-MATCH와 계층 논리를 결합해 다양한 요율 표를 만들 수 있습니다.

계층 및 구간 조회에서는 다음을 기억하세요.

• 각 구간의 하한 임계값을 저장하고 오름차순으로 정렬합니다.
• MATCH(value, thresholds, 1)을 사용해 구간 위치를 찾습니다(입력값 이하인 가장 큰 값).
• INDEX(labels, ...)로 감싸 구간을 반환하거나, 같은 결과를 얻기 위해 XLOOKUP(..., -1)을 사용합니다.

0 임계값으로 하한을 포함하거나 IFERROR를 사용해 범위를 벗어난 입력을 처리하고, 임계값을 정렬하지 않은 상태로 두지 마세요.