OFFSET 함수

셀 또는 범위에서 지정한 수의 행과 열로 구성되는 범위에 대한 참조를 반환한다.
 단일 셀 또는 셀 범위이며, 반환되는 행 및 열수를 지정할 수 있다

OFFSET(reference,rows,cols,height,width)
OFFSET(기준셀, 이동할 행갯수, 이동할 열개수,구하려는 참조영역의 높이, 구하려는 참조영역의 넓이)

-->

=OFFSET(기준셀,
            기준셀에서 아래로 몇칸이동,
            기준셀에서 오른쪽으로 몇칸이동,
            이동한행에서부터 아래로 몇개의 셀선택,
            이동한열에서부터 오른쪽으로 몇개의셀선택)

행이 음수이면 당연히 위로 이동하는것이고,

열이 음수면 왼쪽으로 이동한다

높이나 너비인수를 쓰지않으면  1, 즉  셀 한개만을 선택된다.

 

기준셀에서 행번호, 열번호에 적힌 만큼 이동해서 영역높이와 영역넓이를 구한다.

위 그림에서 [F5] 혹은[F5:G9]를 구하려면

[F5] =Offset(B2,3,4)

[F5:G9]=Offset(B2,3,4,5,2)

사용예 : 이름정의, 조건부서식, 유동적 그래프그리기, 각종컨트롤 범위지정, 피벗테이블의 유동적 지정 등등 다수

 

* 유동적으로 변하는 데이터 영역을 참조할 경우 수정이 최소화
ex) 각 운송업체들의 월정산

월 정산 금액에 대한 집계는 
=SUM(F6:F8) 으로 계산될수 있지만   범위가 수정될 경우
기존 수식을 =SUM(F6:F9) 와 같이 수정해 줘야 합니다.

월별 운송 업체 변경이 많은때  (즉, 9행에 새로운 운송업체 데이터가 입력될 경우) 
=SUM(OFFSET($F$5, 0, 0, COUNTA(F:F) – 1, 1))

Offset함수의 세 번째 인수는 행 개수를 의미
COUNTA(F:F)-1 은 F 열의 입력되어 있는 셀 개수를 구해 1 을 빼는 수식이므로 
                            추가로 데이터가 입력돼도 항상 옳은 계산 결과를 돌려줍니다.

<이름정의로 특정열 지정 >

ex) 

 

 

 

 

 

 

 

 

 

* 차트와 연관하여 사용하면 
데이터가 입력될 때마다 차트에 자동 추가가능

 
년월 : =OFFSET($A$2, 0, 0, COUNTA($A$2:$A$100), 1)
점수 : =OFFSET($B$2, 0, 0, COUNTA($B$2:$B$100), 1)

원본데이터를 A2:B22 ,  선그래프를 그린 다음, 차트를 선택하고
오른클릭하면 뜨는 단축메뉴중 “원본 데이터” 메뉴 명령을 선택한 후 
값 : =파일명!점수     X축 레이블 : =파일명!년월

 

 

* Offset함수를 통해서 피벗테이블을 동적으로 구성하는 방법

시트명 : 매출액 
 

     이름정의를 사용하여 매출액자료전체 설정
 
   매출액자료=OFFSET($A$2,,,COUNTA($A$2:$A$65536),5)

 

피벗테이블을 실행하여 2단계 자료의 범위설정시 : 매출액자료로 사용   

--> 피벗테이블 생성완료 

결과적으로 피벗테이블은 완성한후에도
아래 그림처럼 자료를 2일치 더 입력하면, 피벗테이블에 새로운 값이 반영됨.

 

<참조>

중급:Offset함수의 응용(피벗테이블) (Excel Community Center/엑셀의 모든것)
엑셀 [이름 정의] [이름 만들기]|
Offset 함수에 대해.. |