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

OFFSET 함수 - 행과 열 이동 후 참조구하기

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

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

 

구문(Syntax)

OFFSET

OFFSET(reference, rows, cols, [height], [width])

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

인수 :

  • reference : 행과 열을 이동하기 위한 기준점(셀 위치)
  • rows : 행방향으로 이동할 거리, 양수는 아래로 이동, 음수는 위로 이동
  • cols : 열방향으로 이동할 거리, 양수는 오른쪽으로 이동, 음수는 왼쪽으로 이동
  • height : 반환되는 참조영역의 높이(생략시 기본값이 1로 지정됨)
  • width : 반환되는 참조영역의 너비(생략시 기본값이 1로 지정됨)
실습용 엑셀파일 다운로드 : 엑셀-OFFSET-함수-행열이동후-참조구하기.xlsx

 

사용 예

1) 행과 열 이동 후 값 가져오기

OFFSET 함수로 [B3] 셀에서 이동하여 이동한 위치에 있는 셀의 값을 가져오려면

OFFSET 함수로 행과 열 이동 후 값 가져오기

[B9] 셀에 다음 수식을 입력합니다.

=OFFSET(B3,2,1)

 

OFFSET 함수로 행과 열 이동 후 값 가져오기

위 그림과 같이 OFFSET 함수는 [B3] 셀로부터

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

이동하여 [C5] 셀에 있는 값 8을 가져옵니다.

 

 

2) 행과 열 이동 후 범위 가져오기

이번에는 셀로 이동 후 범위를 지정하여 출력해보겠습니다.

OFFSET 함수로 행과 열 이동 후 범위 가져오기

[B19] 셀에 다음 수식을 입력합니다.

=OFFSET(B13,2,1,3,2)

 

OFFSET 함수로 행과 열 이동 후 범위 가져오기

위와 같이 OFFSET 함수는 [B13] 셀로부터

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

이동하여 [C15:D17] 범위를 가져와서 [B19:C21] 범위에 출력해 줍니다. 수식에서 4, 5번째 인수인 높이와 너비를 지정하면 범위를 가져올 수 있습니다.

 

※ 위 수식은 결과를 동적배열로 가져오므로 Microsoft 365, 엑셀 2021 이상 버전 사용자가 아니라면 다음과 같이 배열수식으로 입력해야 합니다.

[B19:C21] 범위선택 → 수식 입력(=OFFSET(B13,2,1,3,2)) → [Ctrl+Shift+Enter]

 

* Microsoft 365, 엑셀 2021 이상 버전에서는 동적 배열 수식을 사용할 수 있습니다. 자세한 내용은 다음 링크를 참고하세요.
[엑셀의 새로운 기능, 동적 배열 수식 ]

* 배열수식에 대해서는 다음 링크를 참고하세요.
[엑셀 배열수식 제대로 이해하기]

 

 

3) 행과 열 이동 후 범위의 값 더하기

이번에는 범위를 가져와서 SUM 함수로 범위의 값을 더해 보겠습니다.

OFFSET 함수로 행과 열 이동 후 범위의 값 더하기

[B30] 셀에 다음 수식을 입력합니다.

=SUM(OFFSET(B24,2,1,3,2))

 

OFFSET 함수로 행과 열 이동 후 범위의 값 더하기

아래와 같이 OFFSET 함수는 [B24] 셀로부터

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

이동하여 [C26:D28] 범위를 가져와서 SUM함수로 값을 더해 줍니다.

 

 

4) 행과 열 이동하지 않고 범위의 값 더하기

이번에는 행과 열을 이동하지 않고 SUM 함수로 범위의 값을 더해 보겠습니다.

OFFSET 함수로 행과 열 이동하지 않고 범위의 값 더하기

[B41] 셀에 다음 수식을 입력합니다.

=SUM(OFFSET(B35,0,0,3,2))

 

OFFSET 함수로 행과 열 이동하지 않고 범위의 값 더하기

아래와 같이 OFFSET 함수는 [B35] 셀에서 이동하지 않고(2번째, 3번째 인수가 0이므로 이동하지 않음)

[B35:C37] 범위를 가져와서 SUM 함수로 값을 더해 줍니다.

 


관련 글

OFFSET 함수로 월별 누계 구하기 OFFSET 함수로 월별 누계 구하기 - 엑셀의 OFFSET 함수는 어떤 셀로부터 행과 열을 이동한 후 동적으로 참조를 구해줍니다. 이번 글에서는 참조를  동적으로 구하는 기능을 이용하여 월…

OFFSET 함수로 월별 누계 구하기 더 보기 »

동적범위에 이름정의 엑셀 동적 범위에 이름 정의하기 - 오늘은 이름으로 지정한 범위가 계속 변하는 경우에 필요한 '동적 범위에 이름을 정의'하는 방법을 알아 보겠습니다.   1. 고정된 범위에 이름…

엑셀 동적 범위에 이름 정의하기 더 보기 »

배열수식 이해하기 엑셀 배열수식 제대로 이해하기 - 이번 글에서는 엑셀의 배열수식을 알아보겠습니다. 배열수식을 사용하면 복잡한 문제를 간단히 해결할 수도 있고 여러 방면으로 쓸모가 있지만 이해하기가 쉽지 않고…

엑셀 배열수식 제대로 이해하기 더 보기 »

엑셀 동적 배열 수식 엑셀의 새로운 기능, 동적 배열 수식 - Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다.  동적 배열 수식은…

엑셀의 새로운 기능, 동적 배열 수식 더 보기 »

 

“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에 해당하는 값들만 뽑아낼수 있는 방법이 있는지 궁금합니다!

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: