최초 작성일: 2000-07-14
최종 수정일: 2026-09-29
조회수: 24 회
작성자: 권현욱 (엑셀러)
강의 제목: OFFSET 함수 응용

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

아주 오래 전에(찾아보니 X0024강좌군요) Offset() 함수에 대해 설명을 드렸는데 근래 들어 이 함수에 대해 질문하시는 분이 자주 계시는군요. 해서 Remind해 보는 의미에서, 그리고 이렇게도 사용할 수 있다는 것을 보여드리기 위해 새로운 것을 한번 다루어 보도록 합니다.

예전에 UNO21님이 만드신 예제를 사용하려고 아무리 찾아보아도 어디에 뒀는지 보이지 않아서 기억을 더듬어 비슷하게 만들어 보았습니다.

'Offset 함수가 뭐지?' 하는 분은 X0024 강좌를 다시 읽어보고 오셔야 이해가 빠릅니다.

쉽게 설명을 드리면 "특정 위치를 기준으로 지정한 행 또는 열 수만큼 떨어진 곳에 있는 특정 영역을 표시하는 함수이며, Offset(특정위치, 행수, 열수, 구하려는 행수, 구하려는 열수)와 같은 형태로 사용을 합니다.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

OFFSET 함수 응용

핵심 요약

OFFSET은 기준 위치에서 지정한 행·열만큼 이동한 뒤 원하는 크기의 범위를 반환합니다.
SUM이나 AVERAGE와 OFFSET을 결합하면 스피너 값에 따라 계산 대상 행·열의 크기를 동적으로 바꿀 수 있습니다.
예제에서는 VBA로 선택 범위의 색상을 함께 변경해 스피너와 계산 영역의 관계를 시각적으로 확인합니다.

현재 시트의 아무 셀에나 가서 =sum(offset(b36,6,1,2,2))라고 입력하고 엔터키를 쳐 보세요. 아래와 같이 37,000이라는 값이 나옵니다. 아니 이 값은 도대체 어디서 떨어진 것일까요?

37000	=SUM(OFFSET(B36,6,1,2,2))

세상에 원인없는 결과는 없습니다. B36셀을 기준으로 하여 행방향으로 6행, 열 방향으로 1열 이동하면 C42 셀이 되겠고... 여기서 다시 행방향으로 2행 X 열방향으로 2열의 영역을 구하는 것이니까 아래의 표에서 빨간 네모로 둘러싼 부분의 합계를 구해주는 것입니다.

오늘 강좌 파일은 셀의 주소가 중요하므로 행이나 열을 삽입/삭제하지 마시기 바랍니다. 물론 강좌 내용을 다 이해하시고 나서는 행이나 열을 삽입/삭제하여 에러가 나더라도 수정을 하시면 되겠지만…

아래의 표에서 스피너(회전자)들의 버튼을 눌러 값을 증가 또는 감소시켜 가며 각 셀들이 어떻게 유기적으로 연결되어 값이 변하는지 잘 살펴보시기 바랍니다.

월선택		8	합계	255,747	=SUM(OFFSET(C42,0,0,D38,D39))
브랜드선택		3	평균	10621.881	=AVERAGE(OFFSET(C41,0,0,D38,D39))

	아이오페	라네즈	마몽드	헤라	설화수	리리코스	미쟝센	브랜드	계산종류
1월	10,000	8,500	7,650	8,330	7,497	10,200	10327.5
2월	7,300	6,205	5584.5	6080.9	5472.81	7,446	7539.075
3월	13,000	11,050	9,945	10,829	9746.1	13,260	13425.75
4월	15,000	12,750	11,475	12,495	11245.5	15,300	15491.25
5월	20,000	17,000	15,300	16,660	14,994	20,400	20,655
6월	10,000	8,500	7,650	8,330	7,497	10,200	10327.5
7월	10,000	8,500	7,650	8,330	7,497	10,200	10327.5
8월	12,500	10,625	9562.5	10412.5	9371.25	12,750	12909.375
9월	30,000	25,500	22,950	24,990	22,491	30,600	30982.5
10월	31,000	26,350	23,715	25,823	23240.7	31,620	32015.25
11월	33,000	28,050	25,245	27,489	24740.1	33,660	34080.75
12월	28,000	23,800	21,420	23,324	20991.6	28,560	28,917

참고: 위 표의 평균 수식 =AVERAGE(OFFSET(C41,0,0,D38,D39))는 기준 셀이 C41이라 첫 행이 머리글 행이 되어, 표시된 평균 10,621.881은 1~7월 자료(21개 값) 기준입니다. 합계 수식처럼 C42를 기준으로 하면 1~8월 자료(24개 값)의 평균 10,656.125가 됩니다. 또한 이 페이지 표의 값으로 =SUM(OFFSET(B36,6,1,2,2))를 계산하면 32,005가 되므로, 본문의 37,000은 원본 파일의 값이며 이 페이지 표의 값과 다를 수 있습니다.

먼저, 월선택 스피너 버튼을 클릭하면 그 옆에 있는 D38 셀의 값이 따라서 변합니다. 이와 동시에 표 내부의 데이터 중에서 4월에 해당되는 자료들의 색깔도 따라서 변하지요? 그 아래에 있는 브랜드선택 스피너를 클릭하면 D39 셀 값이 변동되고 표 내부 데이터 중에서 브랜드의 범위도 변경됩니다(이것은 VBA로 구현한 것입니다).

스피너의 값에 따라 대상 범위 내에 있는 자료들의 색상이 변하도록 하는 부분은 VBA 코딩을 한 것이므로 여기서는 설명드리지는 않습니다만, 그동안 VBA 강좌를 잘 따라오신 분들께는 모듈 시트로 가서 코드를 살펴보시면 별로 어려운 부분은 없을 것입니다.

Option Explicit

Sub ColoringCell()
    Dim rngStart As Range
    Set rngStart = Range("Start")
    
    With ActiveSheet
        If .Spinners("Month").Value > 12 Or .Spinners("Brand") > 7 Then
            MsgBox "더이상 확장할 수 없습니다!", vbCritical, "//Exceller"
            Exit Sub
        End If
    End With
    
    With rngStart.CurrentRegion
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = 1
    End With
    With Range(rngStart, rngStart.Offset(ActiveSheet.Spinners("Month").Value - 1, _
        ActiveSheet.Spinners("Brand").Value - 1))
        .Interior.ColorIndex = 5
        .Font.ColorIndex = 2
    End With
End Sub

많이 응용해 보시기 바랍니다.

다음 시간에 또…

정리 — OFFSET 함수 응용

구분	내용
OFFSET 형식	`Offset(특정위치, 행수, 열수, 구하려는 행수, 구하려는 열수)`
예	`=SUM(OFFSET(B36,6,1,2,2))` — B36에서 6행 1열 이동한 C42부터 2행 2열 범위의 합계
합계	`=SUM(OFFSET(C42,0,0,D38,D39))` — 스피너로 바뀌는 월 수와 브랜드 수만큼의 범위
평균	`=AVERAGE(OFFSET(C41,0,0,D38,D39))` (참고 박스 참조)
VBA	스피너 값에 따라 선택 범위의 색상을 바꾸는 ColoringCell 프로시저

자주 묻는 질문 (FAQ)

Q1. OFFSET 함수는 무엇을 하나요?

특정 위치를 기준으로 지정한 행 수와 열 수만큼 떨어진 곳에 있는 영역을 돌려주는 함수입니다.

Q2. 스피너와 OFFSET을 함께 쓰면 어떤 일이 가능한가요?

스피너로 바뀌는 셀 값을 OFFSET의 구하려는 행 수와 열 수에 넣으면 선택한 월과 브랜드 수에 따라 합계와 평균의 대상 범위가 동적으로 바뀝니다.

Q3. 범위의 색상이 바뀌는 것은 어떻게 구현했나요?

VBA 코드로 구현했습니다. 스피너 값을 이용해 기준 셀에서 해당 크기의 범위를 잡고 배경색과 글자색을 바꿉니다.

마치며

이 예제는 셀 주소가 중요하므로 원본 파일에서 행이나 열을 삽입하거나 삭제하지 말고 OFFSET의 동작을 관찰해 보시기 바랍니다. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.