Home > 엑셀 함수 > 찾기 및 참조영역 함수 > 엑셀함수 OFFSET – 행열이동 후 참조구하기

엑셀함수 OFFSET – 행열이동 후 참조구하기

엑셀에서 OFFSET함수는 이름만 봐도 뭔가 어려워 보이는 함수이다. OFFSET이라는 단어가 주는 모호함 때문일 것이다. OFFSET을 영어 사전에서 찾아보면 “상쇄하다”, “만회하다”, “오프셋인쇄”…? 무슨 뜻이지??? 볼수록 더 미궁에 빠지는 단어이다.

그러나 컴퓨터와 관련된 분야에서는 “어떤 대상으로부터 벗어난 정도, 떨어진 정도”라는 뜻으로 사용된다. OFFSET함수는 어떤 셀로부터 행과 열을 이동한 후 참조를 구해주는데 OFFSET의 뜻을 알고 나면 함수의 사용법이 조금은 쉽게 이해될 것이다.

구문(Syntax)
OFFSET(reference, rows, cols, [height], [width])

셀에서 지정된 수 만큼 행과 열을 이동한 후의 참조를 구해준다.

인수 :

– Reference : 행과 열을 이동하기 위한 기준점(셀 위치)
– rows : 행방향으로 이동할 거리, 양수는 아래로 이동, 음수는 위로 이동
– cols : 열방향으로 이동할 거리, 양수는 오른쪽으로 이동, 음수는 왼쪽으로 이동
– height : 반환되는 참조영역의 높이(생략시 기본값이 1로 지정됨)
– width : 반환되는 참조영역의 너비(생략시 기본값이 1로 지정됨)

 

OFFSET함수의 이해

OFFSET함수를 이해하기 위해 다음 예를 살펴보자.

다음 그림에서 셀 “B3″로부터

  • 아래로 2칸
  • 오른쪽 1칸

이동한 위치는 셀 “C5″이고 이 위치의 값은 8이다.

이것을 OFFSET함수로 다음과 같이 표현할 수 있다.

=OFFSET(B3,2,1) => 8

네번째/다섯번째 인수는 생략되었고 생략시 기본값이 1이므로 이 수식은 다음과 동일하다.

=OFFSET(B3,2,1,1,1) => 8

 

이번에는 네번째, 다섯번째 인수를 넣은 예를 보자.
셀 “B12″로부터

  • 아래로 2칸,
  • 오른쪽 1칸

이동한 위치는 셀 “C14″이고 이 위치부터

  • 높이3칸
  • 너비2칸인

범위는 “C14:D16″이다

이 범위를 더하면 69가 나오는데 이 것을 OFFSET함수로 다음과 같이 표현할 수 있다.

=SUM(OFFSET(B12,2,1,3,2)) => 69

 

OFFSET함수응용 – 누적판매실적 구하기

OFFSET함수를 좀 더 응용해보자. 아래와 같이 월별 판매실적 데이터가 있을 때 특정월까지의 누적판매실적을 구해야 할때 OFFSET함수를 사용할 수 있다.

위의 판매실적 자료에서 7월까지의 서울지역 판매실적 누계는 다음과 같이 구할 수 있다.

=SUM(OFFSET(C5,0,0,D20,1))  => 7,551,000

각각의 인수를 살펴보자

  • 첫번째 인수 C5 : 1월 실적의 위치
  • 두번째 인수 0 : 아래로 0칸(즉 현재 위치 그대로)
  • 세번째 인수 0 : 오른쪽으로 0칸(즉 현재 위치 그대로)

OFFSET(C5,0,0,D20,1) 에서 두번째,세번째가 0이므로 아래로, 오른쪽으로 이동하지 않는다.따라서 참조위치가 C5그대로이며,

이 위치로부터

  • 네번째 인수 D20 : 높이가 7칸(월을 입력받는데 7이면 7칸이므로 7월까지 참조된다)
  • 다섯번째 인수 1 : 너비가 1칸(서울지역만 포함)인

범위가 구해진다.

구해진 범위는 아래 그림과 같다.

이 범위를 SUM하면 7월까지의 누적판매실적 7,551,000원이 구해진다.

 

 

마지막으로 하나만 더 살펴보자.

판매실적 자료에서 7월까지의 전체지역 판매실적 누계는 다음과 같이 구할 수 있다.

=SUM(OFFSET(C5,0,0,D20,3))  => 17,356,000

서울지역 판매실적누적을 구하는 수식과 거의 동일한데 마지막 인수인 너비만 다르다.

서울,경기,충청지역 3군데를 포함해야 하므로 마지막 인수인 너비가 3칸이다.

 

간단히 OFFSET함수의 기능을 살펴보았는데 사실 이 함수가 가진 강력한 기능 중 극히 일부를 본 것이다. 다음에는 OFFSET함수를 이용해서 동적으로 범위를 참조하는 등 다양하게 응용하는 법을 다루어 보고자 한다.

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

 

“엑셀함수 OFFSET – 행열이동 후 참조구하기”의 1개의 댓글

  1. OFFSET함수 관련 질문드립니다!
    vlookup 특성상 값을 찾았을때, 첫 값만 찾아주는 것으로 알고 있습니다!
    ex)
    A 1 55
    A 3 66
    B 5 77
    라고 가정했을때, vlookup을 통해 A라는 값을 만족하는 행에서 3열에 있는 값을 찾아라 했을때, 값이 55가 나오지만
    그 밑에 있는 66은 나오지 않는 것으로 알고 있습니다!
    그래서 이를 해결하기 위해
    Offset함수를 활용하였습니다.
    Offset함수를 활용할경우, vlookup이 찾지 못한 A 3 66 행의 찾고자 하는 값인 66을 찾아줄 수 있게 되더라구요!

    근데 질문이 여기서 생깁니다.!
    이러한 Offset함수를 참조를 위해 반복해서 연이어 사용했을때,
    offset은 A에 해당하는 값이 아니라 그저 특정 위치에서 몇행 몇열을 내려간 값을 구해주는 것이므로

    A 1 55
    A 3 66
    B 5 77
    저는 이 표에서 단지 55와 66의 값만 도출해내고 싶은데
    Offset함수는 끊임없이 밑의 행을 참조하여 B의 값인 77을 도출시키게 됩니다!

    이때, B가 아닌 A에 해당하는 값들만 뽑아낼수 있는 방법이 있는지 궁금합니다!

댓글 남기기

이메일은 공개되지 않습니다.