정수에 가까운 숫자 찾기(3)

4. aggregate 함수를 적용할 경우

min함수 대신에 aggregate함수를 이용할 수도 있습니다.

 

가. AGGREGATE 함수를 이용한 수식

Aggregate함수의 구문은 배열형인 경우

AGGREGATE(function_num, options, array, [k])입니다.

 

function_num은 함수의 번호로 배열형이므로 최솟값에 해당하는 함수 번호는 5 - MIN이 아니라 15 - SMALL입니다.

 

options는 오류값을 무시하는 2 또는 3, 6, 7을 사용하면 되는데, 2를 선택하겠습니다.

 

array는 배열인데, 정수가 같은 것 중 차이가 가장 작은 값을 찾아야 하므로

1/(ROUND($B$9:$I$9,0)=ROUND(B$9,0))*(ABS($B$9:$I$9-ROUND(B$9,0)))입니다.

 

1/를 한 것은 False일 경우 #DIV/0! 에러를 발생시켜서 선택에서 제외되도록 하고자 하는 것이며,

 

그다음

(ROUND($B$9:$I$9,0)=ROUND(B$9,0))은 반올림한 값들 중 B9의 반올림한 값과 같으면 TRUE가 되고,

 

(ABS($B$9:$I$9-ROUND(B$9,0)))은 B9셀부터 i9셀까지 원 숫자와 정수값의 차이를 구하는 것입니다.

 

마지막 인수 k값으로는 첫 번째로 작은 값을 찾아야 하므로 1을 입력하고, 괄호를 닫고 엔터키를 누르면 0.45가 구해집니다.

 

B16셀의 채우기 핸들을 i16셀까지 끌어 수식을 복사합니다.

 

E16셀의 값이 0.2인데, E6셀을 클릭한 다음

1/(ROUND($B$9:$I$9,0)=ROUND(B$9,0))*(ABS($B$9:$I$9-ROUND(B$9,0)))를 마우스로 끌어서 선택하고,

 

F9키를 누르면 4번째부터 6번째까지만 정수 5로 같으므로 숫자가 표시되고, 나머지는 FALSE이므로 #DIV/0! 에러가 납니다. 따라서, 0.25, 0.11, 0.0199999999999996중 가장 작은 값 0.0199999999999996≒0.02가 구해집니다.

 

그렇지만 1/가 없다면 FALSE인 경우 0이 되기 때문에 최소값이 0이 되는 문제점이 있습니다. 따라서, 반드시 1/를 해야 합니다.

 

이제 원 숫자와 정수와의 차이가 Aggregate함수로 구한 값과 같은지 확인하려면

=다음에 (abs(b9-round(b9,0)))=을 추가하고 엔터 키를 누르면 됩니다.

 

그러면 TRUE가 반환됩니다.

B16셀의 수식을 i16셀까지 다시 복사하면

 

Round함수를 적용한 경우와 동일한 결과가 구해집니다.

 

나. 조건부 서식 적용

B16셀의 수식을 복사한 후

B9셀에서 i9셀까지 조건부 서식의 수식으로 붙여 넣고, 서식을 녹색으로 지정하면

아래와 같이 되는데, 확인 버튼을 누르

 

주황색이 녹색으로 바뀝니다.

 

홈 탭 - 조건부 서식 - 규칙 관리를 누르면

현재 선택영역인 B9:i9에 규칙이 2개 있습니다. 

그렇지만, 위 규칙이 우선적으로 적용돼서 녹색으로 표시되는 것으로

 

 

아래 규칙을 클릭한 후 위로 올리고

 

확인 버튼을 누르면 녹색이 다시 주황색으로 바뀝니다.

 

다시 규칙관리 메뉴를 누르면 적용된 규칙을 확인할 수 있습니다.

 

정수와 가까운 숫자 찾기(완성)3.xlsx

0.01MB