[컴활 1급] 기준점으로부터의 상대 위치, 엑셀 OFFSET 함수

아직 컴퓨터활용능력 1급의 엑셀 재무 함수가 종료되지는 않았습니다. 하지만 엑셀 수학 함수와 엑셀 재무 함수들만을 배우고 있을려니 머리가 깨어질것 같네요. 따분하기도 하구요. 우리 오늘은 엑셀 조회및 참조 함수를 배워보기로 하겠습니다. 컴퓨터활용능력 1급의 첫번째 조회및 참조 함수 OFFSET 함수를 배워봅니다. OFFSET 함수는 기준점을 잡고 상대 위치를 계산하여 2차 범위를 설정하는 함수입니다.

엑셀 OFFSET 함수 기본 설명

설명 : 엑셀 OFFSET 함수는 첫번째로 전달되는 기준 셀(범위)에서 행 수와 열 수를 지정하여 옵셋(이동) 위치 시작점을 잡고 여기를 2차 기준점으로 높이와 폭을 전달하여 옵셋(이동) 범위의 사이즈를 조절 할 수 있습니다.
구문 : OFFSET( reference, rows, cols, [height], [width] )
인수 :
    reference : 초기 전달되는 기준 셀(범위)입니다.
    rows : reference 범위 인수의 왼쪽 상단에서 부터의 행 수.
    cols : reference 범위 인수의 왼쪽 상단에서 부터의 열 수.
    [height] : 옵션 인수 입니다. 반환되는 범위의 높이 입니다. 생략한다면 높이는 reference 인수의 높이와 동일합니다.
    [width] : 옵션 인수 입니다. 반환되는 범위의 폭 입니다. 생략한다면 폭은 reference 인수의 폭과 동일합니다.
   
    노트1 : rows, cols 인수는 시작 위치 보다 아래와 오른쪽이라면 양수 , 시작 위치보다 왼쪽과 위쪽이라면 음수로 사용합니다.

엑셀 OFFSET 함수 사용 예제

[그림1] 컴활 1급 실기 엑셀 OFFSET 함수 사용 예제

사용 예제1 : =OFFSET(A3, 3, 1)
사용 결과1 : 1056

엑셀 OFFSET 함수는 A3 셀을 기준 위치로 잡고 행 방향으로 3행, 열 방향으로 1열을 이동합니다. height 와 width 는 생략합니다.
A3 라는 단일 셀을 기준 위치로 전달하였고 폭과 너비가 생략되었습니다. 따라서 결과는 reference 범위의 폭과 너비가 동일해 집니다. 단일셀이 결과로 반환되는 것입니다. A3 셀에서 3행 아래로, 1열 우측으로 이동하면 B6 셀이 됩니다. 그 셀의 값이 결과로 반환되는 것입니다.

[그림2] 컴활 1급 실기 엑셀 OFFSET 함수 사용 예제2

사용 예제2 : {=OFFSET(A3, 3, 1, 1, 5)}
사용 결과2 : 1056    1261    1280    1734    1644

엑셀 OFFSET 함수로 A3 셀을 기준으로 잡고 3행 1열 이동하였습니다. B6 셀이 됩니다. 이 위치를 2차 기준점으로 [height] (높이) 1, [width] (폭) 5를 설정하여 사이즈를 조절하였습니다. B6:F6 가 범위로 설정되는 것입니다. 이 범위의 값들이 결과로 표시됩니다. 엑셀 배열 수식을 사용하려면 마지막에는 CTRL+SHIFT+ENTER 로 함수식을 종료해 주어야 합니다.

[그림3] 컴활 1급 실기 엑셀 OFFSET 함수 사용 예제3

사용 예제3 : {=OFFSET(B3:F3, 3, 0)}
사용 결과3 : 1056    1261    1280    1734    1644

엑셀 OFFSET 함수로 B3:F3 의 범위를 기준 위치로 최초 전달합니다. 이후 3행과 0열 이동을 옵셋으로 설정하였습니다. reference 범위의 왼쪽 상단인 B3 셀에서 3행 아래로 이동합니다. B6 셀이 됩니다. cols 는 0으로 설정되어 열 이동은 하지않습니다. 폭과 너비도 생략되어 최조 범위인 reference 인수의 폭과 너비의 값이 결과로 표시됩니다. 배열 수식이 되는 것입니다.  수식 종료시는 CTRL+SHIFT+ENTER로 확인해 주셔야 바른 배열 수식이 됩니다.

[그림4] 컴활 1급 실기 엑셀 OFFSET 함수 사용 예제4

사용 예제4 : =AVERAGE(OFFSET(F3, 1, -3, 7))
사용 결과4 : 1075.428571

엑셀 OFFSET 함수를 활용한 예제 입니다. 먼저 내부의 OFFSET 함수를 살펴봅니다. F3이 reference 인수로 전달되어 기준 위치로 설정됩니다. 단일 셀이기에 [height], [width] 인수가 생략된다면 표시 크기 단일 셀입니다. 1 이 rows 인수로 설정되어 기준 위치에서 1행 아래로 내려오고 -3 이 cols 인수로 전달되어 왼쪽으로 3열 이동합니다. 2차 기준셀은 C4 가 됩니다. 이 셀을 다시 기준으로 잡고 [height] 인수로 7이 전달되어 C4:C10 범위의 7개 셀이 AVERAGE 함수로 전달되는 것입니다. [width] 인수는 생략되어 reference 크기의 1로 설정됩니다. 결국 2주의 평균이 구해지는 것이지요. OFFSET 함수로 반환된 배열값은 AVERAGE 함수로 직접 입력되기에 배열 수식을 사용할 필요는 없습니다.
이 예제에서 확인 하실 것은 -3 의 cols 인수입니다. 여기서 음수는 왼쪽을 의미합니다. rows 인수였다면 음수는 위쪽을 의미하지요. cols 와 rows 인수는 음수가 사용 가능하다는 것 기억하십시오.

참고1 : 위 예제에서 cols 인수가 -6으로 설정되어 수식 적용된다면 이는 #REF! 오류를 발생합니다. F3셀에서 왼쪽으로 6열은 스프레드시트의 범위를 벋어나기 때문입니다. 참고하시기 바랍니다.

컴퓨터활용능력 1급의 대표적인 찾기와 참조 함수인 엑셀 OFFSET 함수였습니다. 엑셀 찾기와 참조 함수가 그런 목적의 함수들이지만 이 함수는 홀로 사용되기보다는 마지막 예제와 같이 다른 함수들과 함께 사용되는 경우가 많습니다.

마늘빵.