OFFSET, INDIRECT 함수

 

엑셀 초급자에게는 다소 어려워보이는 함수 2가지를 설명하겠습니다.
어려워 보여도 발전하려면 알아야 하는 함수에 속합니다.

 

1. OFFSET  함수 개념

 

기준 셀로 부터, 지정한 행, 열만큼 떨어진 위치에 있는 값들을 참조해서 출력해 주는 함수입니다.

OFFSET( 기준점,    행,  열,   [높이], [너비] )

A1셀에    "=OFFSET(A1, 3, 1)" 이런 수식을 입력했다고 합시다.

3개의 인수 "A1, 3, 1"을 잘 보세요.

A1셀을 시작점으로 하여, 3행, 1열만큼 떨어진 위치에 있는 값을 가져오라는 의미입니다.

A1에서 3행, 1열 이동하면 B4셀입니다. 따라서, XYZ를 반환합니다.

1개의 셀 뿐만 아니라, 범위(range)를 반환할 수도 있습니다.

아래 예에서, OFFSET(A1, 3, 5, 4, 3) 이라고 하면,
A1셀에서 부터, 3행, 5열 만큼 떨어진 지점(F4)에서부터, 4행, 3열 크기의 범위를 반환하라는 의미입니다.

OFFSET 함수의 반환값은 아래와 같습니다.

 

 

2. 기준점과 거리

OFFSET 함수를 사용할 때는, 무조건 기준점이 있다는 점을 같이 떠올려야 합니다.
OFFSET 함수는 "기준점"에서  몇 행, 몇 열 떨어져 있는 영역의 값(들)을 참조하는 함수입니다.

"기준점"은 기준이 되는 셀입니다. 
A1 일 수도 있고, 어떤 셀이든 상관없습니다.


OFFSET 사용시, 기억할 것.

(1) 수식을 입력하는 곳
(2) 기준점
(3) 기준점에서 데이터까지  거리

3.   높이(height), 너비(width) 인수

 

함수 프로토타입  OFFSET( 기준점,    행,  열,   [높이(height)], [너비(width)] ) 에서, 4번째, 5번째 인수는 선택적입니다.

지정하지 않으면, 1이 자동으로 들어갑니다.
높이 1, 너비가 1 이라는 것은 1개의 셀을 의미합니다.

height는 참조하고 싶은 행의 개수, width는 참조하고 싶은 열의 개수입니다.

아래 그림에서, 회색 범위는 4개 행, 2개 열의 크기입니다.

높이와 너비로 표현한다면, 높이가 4,  너비가 2 라고 합니다.

4.  OFFSET 함수와 COUNTA 함수로 동적범위 만들기

자주 사용되는 OFFSET 사례 중 하나입니다.
동적범위는  데이터가 추가되거나 삭제되더라도 수식에 자동반영되는 범위입니다.

OFFSET()과 COUNTA() 를 이용하여 동적범위를 만들때 다음과 같이 만듭니다.

=OFFSET( 기준점, , , COUNTA(행 범위), COUNTA(열 범위))

 

예를 들면,
=OFFSET($B$1, , , COUNTA(B:B), 1)

이 수식의 인수들을 설명하면, 

첫번째 인수 :  B1셀이 기준점
두번째, 세번째 인수 :  지정하지 않으면, 0이 자동으로 지정됨.  기준점에서 부터 참조하겠다는 의미임
네번째 인수(height) :    B:B는 B열 전체를 지정하는 주소표기 방식입니다.
                     COUNTA(B:B)는 B열에서 빈칸이 아닌 셀 갯수를 셉니다.
                     참조하려는 범위의 행 갯수를 결정합니다. 
                     B:B가 B열 전체이므로 데이터가 추가되면 COUNTA()로 반영됩니다.
                    참조 범위가 추가/삭제에 의해 변할 수 있기 때문에, 동적범위가 됩니다.
다섯번째 인수(width) :  1은 폭(width)은 가로방향의 크기가 1개 열이라는 의미

B열에 있는 연속된 데이터들이 모두 E1셀 밑으로 나타나게 됩니다. 

 

5. 문자열 주소로 참조하는 INDIRECT 함수

 

(1)  INDIRECT 함수 개념

 

문자열 조합으로 셀주소를 만들어, 참조할 수 있는 함수입니다.
일반적으로 특정 범위를 참조할 때는 문자열을 사용하면 안됩니다.
예를 들어,

C2에서 D2까지의 범위(range)를 참조하는 수식을 만들 때,
=C1:D2
이렇게 해야지,  쌍따옴표를 붙여서 "=C1:D2" 이렇게 해서는 참조가 안됩니다.
이것은 단순히  문자열일 뿐입니다.

이런 경우, 문자열로 만들어진 주소를 인수로 집어넣어서 그 범위를 참조할 수 있게 하는 함수가
INDIRECT() 입니다.


F1:G4 범위에 있는 데이터를 참조하는 함수를 INDIRECT()로 만들고 싶다면,

INDIRECT("F1:G4")  이렇게 합니다.
쌍따옴표를 붙여서 문자열로 만들었다는 특징을 기억하세요.

결과는 그 문자열을 범위 주소로 변환해서 참조하여 출력해줍니다.

쌍따옴표를 사용하지 않으면 문자열이 아니기 때문에 오류가 발생합니다.
=INDIRECT(F1:G4) 이렇게 하면 오류가 발생합니다.

6.  문자열 조합으로 셀주소 만들기

 

문자열 연결은 항상 & 기호를 이용하면 됩니다.
문자열로 참조하려는 셀주소, 범위 주소를 만드는 사례를 살펴봅시다.

(1)   문자와 숫자를 조합하여 셀주소를 만듭니다.

A1 이라는 셀주소는 "A"&"1" 하면 됩니다. 즉, "A1"이 됩니다.
대부분의 함수에서 인수에 셀주소 A1을 입력할 때는 문자열이 아닌  A1을 입력하지, "A1"을 입력하면 에러가 납니다.
INDIRECT에서는 반대입니다.

(2) sheet명을 셀 주소에 연결한 셀 주소(또는 범위)를 만듭니다.

데이터가 여러 sheet에 분산된 경우에는 sheet명을 셀주소에 붙여주어야 하는데, 문자열로 결합하면 쉽습니다.
예를 들어,
데이터가 "홍길동1", "홍길동2", "홍길동3" sheet에 나누어져 있고,
각 sheet의 A1:A10 범위에 있다고 합시다.
데이터는 모두 숫자인데, 이 모든 숫자들의 평균을 구하고 싶다면 수식을 어떻게 만들까요?

AVERAGE( INDIRECT("홍길동1!"&"A1:A10"), INDIRECT("홍길동2!"&"A1:A10"), INDIRECT("홍길동3!"&"A1:A10"))

 

이렇게 하면 됩니다.