Excel에서 값 목록이 있고 정확히 일치하는 항목을 찾을 수없는 경우 가장 가까운 큰 값 또는 가장 가까운 작은 값을 찾아야합니다. 예를 들어 숫자를 비교하고 범위에서 정확히 일치하는 항목을 찾지 못한 경우 다음으로 크거나 작은 숫자를 사용하고 계속 진행할 수 있습니다.

Countif, Large 및 Small 공식의 조합을 사용하여 출력을 얻을 수 있습니다.

Countif는 셀 범위에서 특정 데이터가 반복되는 횟수를 계산하는 데 사용됩니다.

구문 = COUNTIF (범위, 기준)

Large : 데이터 세트에서 k 번째로 큰 값을 반환합니다.

구문 = LARGE (array, k)

작게 : 데이터 집합에서 k 번째로 작은 값을 반환합니다.

구문 = SMALL (array, k)

가장 가까운 크고 작은 숫자가 무엇인지 이해합시다

난수 1,2,3,5,8,10이있는 목록이 있고 다음을 평가하려면 공식이 필요합니다.

-k가 목록에 있으면 k를 반환합니다.-k가 목록에 없으면 다음으로 높은 값을 반환합니다. 예를 들어, k = 7이면 8을 반환합니다 (가장 가까운 숫자의 경우)

-k가 목록에 없으면 다음으로 작은 값을 반환합니다. 예를 들어, k = 7이면 5를 반환합니다 (가장 가까운 작은 숫자의 경우)

예를 들어, A 열에 임의의 숫자가 있고이 범위에서 값을 찾아야합니다.이 범위는 46을 포함하는 셀 B2에 대해 가장 가까운 작은 값 또는 가장 가까운 큰 값입니다.이 숫자는 범위에서 사용할 수 없습니다. 아래 스크린 샷을 참조하세요

img1

가장 가까운 큰 수를 먼저 계산하는 방법을 살펴 보겠습니다.

다음으로 큰 값인 46 (54)을 수동으로 확인할 수 있습니다.

이제 수식을 사용하여 동일한 결과를 얻으려고합니다. LARGE 및 COUNTIF * 함수의 조합을 사용하여 가장 가까운 큰 숫자를 생성합니다.

기준이 조회 값 (예 : 셀 B2)이있는‘>’기호보다 큼 조회 값과 결합 된 초과 기호는 46보다 큰 5 개의 값 (예 : 54, 57, 61, 74, 83)을 반환합니다.

  • Large & Countif 함수를 병합하면 54가됩니다. 아래 스냅 샷에서 G2 및 G3 셀의 수식을 참조하십시오.

img2

추신 : Countifformula의 B2는 조회 값입니다. 즉, 46 셀 C2에서 가장 가까운 더 큰 * 숫자를 찾는 데 사용되는 공식은

= LARGE ($ A $ 2 : $ A $ 8, COUNTIF ($ A $ 2 : $ A $ 8, “>”& B2))

img3

이제 가장 가까운 작은 수를 계산하는 방법을 살펴 보겠습니다. 다음으로 작은 값인 46을 수동으로 확인할 수 있습니다 (28).

공식을 사용하여 동일한 출력을 얻습니다. SMALL 및 COUNTIF * 함수의 조합을 사용하여 가장 가까운 작은 수를 생성합니다.

기준은 조회 값이있는‘<’보다 작습니다. 즉, 조회 값과 결합 된 셀 B2 미만 부호는 46보다 작은 2 개의 값, 즉 16 & 28을 반환합니다.

  • Small & Countif 함수를 병합하면 28 개가됩니다. 아래 스냅 샷에서 G5 및 G6 셀의 수식을 참조하십시오.

공식이있는 아래 스크린 샷을 참조하십시오.

img4

참고 : CountifFormula의 셀 B2는 조회 값입니다. 즉, 46 셀 D2에서 가장 가까운 작은 * 숫자를 찾는 데 사용되는 수식은

= SMALL ($ A $ 2 : $ A $ 8, COUNTIF ($ A $ 2 : $ A $ 8, “<“& B2))

img5

이것은 우리가 가장 가까운 큰 수를 반환하고 더 작은 수를 벽장하는 방법입니다.