[Office365] 엑셀 OFFSET 함수 활용법: 동적 범위 자동 설정하기

엑셀 OFFSET 함수는 데이터 범위를 자동으로 조정할 수 있습니다.

---

 

엑셀을 사용하면 데이터가 계속 늘어날 수밖에 없습니다.

매번 범위를 수동으로 바꿔주기 번거로울때가 있을 텐데요 특히 차트나 함수에서 참조 범위가 고정되어 있으면 새로 입력한 데이터가 반영되지 않아 불편한 상황이 발생하기도 합니다.

이럴 때 유용하게 쓸 수 있는 함수가 바로 OFFSET 함수입니다. OFFSET 셀을 기준으로 일정한 행과 열만큼 이동하여 원하는 범위를 지정할 수 있고, 다른 함수와 결합하면 동적으로 범위를 자동 설정할 수 있습니다.

이 글을 통해서 OFFSET 함수의 기본 개념과 동적 범위 실전 활용법에 대해 알아보겠습니다.

 

OFFSET 함수의 기본 개념

OFFSET 함수는 기준이 되는 셀에서 지정한 만큼 행(row)과 열(column)을 이동해 원하는 셀이나 범위를 반환합니다.

기본 구조는 아래와 같습니다.

=OFFSET(참조, 행, 열, [높이], [너비])

• 참조(Reference): 기준이 되는 시작 셀
• 행(Row): 이동할 행의 수(양수는 아래로, 음수는 위로)
• 열(Column): 이동할 열의 수 (양수는 오른쪽, 음수는 왼쪽)
• 높이(Height): 반환할 범위의 행 개수
• 너비(Width): 반환할 범위의 열 개수

=OFFSET(A1, 2, 1) 

A1에서 기준으로 2행 아래, 1열 오른쪽 셀 참조 이렇게 볼 수 있습니다. 결과는 B3셀입니다.

 

동적 범위 설정

OFFSET 함수의 진면목은 다른 함수와 결함함ㄹ 때 발휘됩니다.

특히 COUNTA 함수와 함께 쓰면 데이터 개수에 따라 범위가 자동으로 늘어나거나 줄어들 게 만들 수 있습니다.

 

자동으로 늘어나는 데이터 범위

1. A열에 판매량 데이터를 입력한다고 가정하겠습니다.

2. 데이터는 A2부터 시작하며, 매일 새로운 값이 추가됩니다.

3. 범위를 자동으로 지정하려면 다음과 같이 설정합니다.

 

=OFFSET($A$2,0,0, COUNTA($A:$A)-1,1)

$A$2: 기준 셀이 됩니다.

0,0: 기준에서 이동하지 않습니다.

COUNTA($A:$A)-1: A열 데이터 개수를 세어 높이를 지정(제목 행 제외)

1: 너비는 1열

이렇게 하면 A열에 값이 추가될 때마다 범위가 자동으로 확장됩니다.

 

동적 차트 만들기

1. 이름 관리자에서 위 공식을 입력해 판매량 범위라는 이름을 정의합니다.

2. 차트 데이터 범위를 =판매량범위로 설정합니다.

3. 새로운 값 입력 시 차트가 자동으로 업데이트됩니다.

 

주의사항

OFFSET은 휘발성 함수라데이터가 많을 경우 속도가 느려질 수 있습니다.

가능하다면 INDEX 함수로 대체하는 방법도 고려할 수 있습니다.

동적 범위 설정에서는 OFFSET이 가장 직관적입니다.

 

그럼 추가적으로 조금 더 알아보도록 하겠습니다.

1. OFFSET 함수는 어떤 상황에서 가장 유용하게 사용할 수 있을까요?
   
   • OFFSET 함수는 데이터가 지속적으로 늘어나거나 줄어드는 상황에서 범위를 자동으로 조정할 때 가장 유용합니다
   • 매일 매출 데이터를 기록할 때, 수동으로 범위를 바꿔주지 않아도 OFFSET을 활용하면 새로 입력된 데이터가 자동으로 포함됩니다.
   • 또한 차트, 조건부 서식, 합계 계산 등 여러 곳에서 활용할 수 있습니다. 
   • 단순 참조보다는 유연한 범위 지정이 필요할 때 OFFSET 이 특히 강력합니다.
2. OFFSET 함수에서 음수를 사용하면 어떻게 될까요?
   • OFFSET 함수는 기준이 되는 셀에서 시작하여 행과 열 단위로 이동한 뒤, 그 위치에서 원하는 크기의 범위를 반환합니다.
   • 기본 문법은 =OFFSET(참조, 행, 열, [높이], [너비])이며, 필수 인자는 참조, 행, 열이고 높이와 너비는 선택사항이 됩니다.
   • 만약 높이와 너비를 지정하지 않으면 단일 셀만 반환됩니다.
   • 이를 통해 단순히 셀 하나를 참조하거나 여러 행, 열을 묶은 범위를 지정할 수 있습니다.
3. OFFSET 함수에서 음수를 사용하면 어떻게 될까요?
   • 행 또는 열에 음수를 입력하면 기준 셀에서 위쪽이나 왼쪽 방향으로 이동합니다.
   • =OFFSET(C5,-2,-1)은 C5 기준으로 2행 위, 1열 왼쪽 셀인 B3을 참조합니다.
   • 이 방식은 데이터가 위쪽에 추가되는 경우에도 유연하게 적용할 수 있습니다.
   • 단, 음수를 사용했을 때 워크시트 범위를 벗어나지 않도록 주의해야 합니다.
4. OFFSET 함수만 단독으로 쓰는 경우와 다른 함수와 결합하는 경우의 차이는 무엇일까요?
   • 단독으로 사용할 때는 트겆ㅇ 위치의 셀이나 범위를 참조하는 용도로 주로 쓰입니다.
   • COUNTA, MATCH, ROW, INDEX 같은 함수와 결합하면 동적으로 변하는 범위를 자동으로 참조할 수 있습니다.
   • 예를 들면 OFFSET + COUNTA 조합은 자동 확장 범위를 만들고, OFFSET + MATCH 조합은 조건에 맞는 데이터를 찾을 수 있습니다.
   • 실무에서는 보통 다른 함수와 함께 사용하는 경우가 더 많습니다.
5. OFFSET 함수와 INDEX 함수는 어떻게 다른가요?
   • INDEX 함수도 특정 위치의 셀을 반환한다는 점에서 비슷하지만, OFFSET은 '위치 이동 후 범위 반환'이고 INDEX는 배열에서 지정 위치 값 반환'이라는 차이가 있습니다.
   • OFFSET은 높이와 너비를 조절해 다차원 범위를 지정할 수 있지만, INDEX는 단일 셀이나 행/열 범위를 반환하는 경우가 가 많습니다. 
   • 또한 INDEX는 비휘발성 함수라 대규모 데이터에서 속도가 빠르다는 장점이 있습니다.
6. OFFSET 함수로 동적 차트를 만드는 방법을 자세하게 설명하면?
   
   • 먼저, 차트에 사용할 데이터가 입력된 열이나 행을 OFFSET 함수로 감쌉니다.
   • A열에 데이터가 있을 경우, 이름 관리자에서 =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)을 입력하여 이름을 정의합니다.
   • 그런 다음 차트의 데이터 범위를 해당 이름으로 지정합니다.
   • 이후 새로운 값이 추가되면 차트가 자동으로 업데이트됩니다. 이 방식은 매출 그래프나 월별 성장 그래프에 자주 활용됩니다.
7. OFFSET 함수는 피벗 테이블에도 적용할 수 있을까요?
   • 가능합니다. OFFSET 함수는 동적 범위를 정의한 뒤, 그 범위를 피벗 테이블 데이터 원본으로 지정하면 됩니다.
   • 이렇게 하면 원본 데이터가 늘어나거나 줄어들 때 피벗 테이블이 자동으로 반영됩니다.
   • 다만, 피벗 테이블을 새로 고침해야 반영되며, OFFSET 범위가 제대로 설정되어야 오류가 발생하지 않습니다.
8. OFFSET 함수로 특정 구간의 합계를 자동 계산할 수 있을까요?
   • 가능합니다. 예를 들어 최근 7일간 매출 합계를 구하고 싶다면 아래와 같이 작성한 후에 활용할 수 있습니다.
     • =SUM(OFFSET(A2,COUNTA(A:A)-7,0,7,1))
   • 이 함수는 A열에 있는 마지막 7개의 데이터를 합산합니다. 데이터가 늘어나도 항상 최신 7일간만 반영되므로 유용하게 사용할 수 있습니다.
9. OFFSET 함수로 다중 열 범위를 설정하는 방법은?
   
   • OFFSET 너비(Width) 인자를 활용하면 됩니다. A열 부터 C열까지 데이터를 포함하려면 =OFFSET($A$2,0,0,COUNTA($A:$A)-1,3) 작성합니다.
   • 이 공식은 A열 부터 C열 까지 확장된 범위를 반환하므로 여러 열을 동시에 처리할 수 있습니다.
10. OFFSET 함수와 MATCH 함수를 결합하면 어떤 장점이 있을까요?
    • MATCH 함수는 특정 값이 배열에서 몇 번째 위치에 있는지를 알려줍니다.
    • OFFSET과 결합하면 원하는 값이 있는 위치를 자동으로 이동할 수 있습니다.
    • 특정 제품명이 있는 행부터 데이터를 가져오는 범위를 만들 때 유용합니다.
    • 이를 통해 특정 조건을 만족하는 데이터만 동적으로 참조할 수 있습니다.
11. OFFSET 함수에서 #REF! 오류가 나는 이유는 무엇일까요?
    • OFFSET 함수가 지정한 행 또는 열 이동 값이 워크시트 범위를 지정하면 #REF! 오류가 발생합니다.
    • A1을 기준으로 -2행을 지정하면 시트 범위를 넘어가 오류가 발생합니다.
    • 이를 방지하려면 데이터 크기를 고려하여 이동 범위를 설정하거나, IF 함수를 함께 사용하여 안전 장치를 두는 것이 좋습니다.
12. OFFSET 함수로 정의한 이름 범위가 차트에서 동작하지 않는 경우는 어떻게 해결하나요?
    • 차트에 일므 정의를 사용할 때는 반드시 " =시트이름!이름정의" 형식으로 입력해야 합니다.
    • 또한 이름 관리자에서 범위를 제대로 설정했는지 확인해야합니다.
    • 특히 상대 참조가 잘못 들어가면 데이터가 꼬일 수 있으므로 절대 참조($)을 적절히 사용하는 것이 중요합니다.
13. OFFSET 함수와 COUNTA 조합에서 빈 셀 때문에 오류가 나는 경우는 어떻게 해야할까요?
    • COUNTA 함수는 빈 셀을 제외하고 개수를 세지만, 가끔 데이터 중간에 공백이 있으면 어떤 범위 계산이 어긋날 수 있습니다.
    • 이럴 때는 COUNTA 대신 COUNT 함수를 사용하거나, IFERROR 함수를 결합하여 오류를 방지하는 방법도 있을 수 있습니다.
    • 데이터 입력 규칙을 통해 공백이 생기지 않도록 관리하는 것도 좋은 방법입니다.
14. OFFSET 함수로 만든 범위가 너무 많아져 속도가 느려질 때 어떻게 해야할까요?
    
    • OFFSET 함수는 휘발성 함수라 데이터가 많을 경우 성능이 떨어질 수 있습니다.
    • 이럴 때는 INDEX 함수를 대체로 사용하거나, 동적 배열 함수 (FILTER, SEQUENCE 등)를 활용하는 것이 더 효율적입니다.
    • 가능하다면 OFFSET은 소규모 데이터나간단한 자동화 작업에만 사용하는 것이 좋습니다.
15. OFFSET 함수에서 높이와 너비 인자를 생략하면 어떤 결과가 나올까요?
    
    • 높이와 너비를 생략하면 기본 값은 1이 되어 단일 셀만 반환합니다.
    • OFFSET(A1, 2, 1)은 단순히 B3 셀만 참조합니다. 여러 셀 범위를 가져오려면 반드시 높이와 너비를 지정해야합니다.
16. OFFSET 함수를 조건부 서식에서 사용할 수 있을까요?
    
    • 특정 범위에서 마지막 값만 강조하고 싶을 때 OFFSET 활용할 수 있습니다.
    • =A2=OFFSET($A$2,COUNTA($A:$A)-1,0) 같은 공식을 조건부 서식에 넣으면 마지막 데이터가 자동으로 강조됩니다.
    • 이를 통해 동적인 강조 표시가 가능합니다.
17. OFFSET 함수로 이동 평균을 계산할 수 있을까요?
    • 예를 들어 최근 3일 평균을 구하려면 다음과 같이 작성해야합니다.
    • =AVERAGE(OFFSET(A2,COUNTA(A:A)-3,0,3,1)) 이 공식은 마지막 3개의 값을 평균 내어 최근 추세를 분석하는데 활용합니다.
18. OFFSET 함수를 VBA(매크로)와 함께 사용할 수 있을까요?
    • VBA에서도 WorkShhetFunction.Offset을 활용할 수 있습니다.
    • 다만 VBA에서는 범위 객체를 직접 다루는 경우가 많기 때문에, Range.Offset 속성을 더 자주 사용합니다.
    • VBA와 결합하면 더 복잡한 자동화 작업을 구현할 수 있습니다.
19. OFFSET 함수로 다중 시트 간 범위를 자동으로 연결할 수 있을까요?
    • OFFSET 자체는 한 시트 내에서 주로 쓰이지만, 참조 범위를 다른 시트로 지정하면 가능합니다.
    • 예를 들어 =OFFSET(시트2!$A$2,0,0,COUNTA(시트2!$A:$A)-1,1)처럼 작성하면 다른 시트의 동적 범위를 불러올 수 있습니다.
20. OFFSET 함수를 FILTER 함수와 결합할 수 있을까요?
    
    • FILTER 함수로 조건에 맞는 데이터를 추출한 뒤 OFFSET을 사용하면, 조건부로 자동 확장되는 범위를 만들 수 있습니다.
    • 다만 OFFSET은 휘발성 함수이므로 최슨 Excel에서는 FILTER, SEQUENCE, SORT 같은 동적 배열 함수를 우선적으로 사용하는 것이 더 효율적일 수 있습니다.

자 OFFSET 함수는 단순한 셀 참조 이상의 기능을 제공하며, 특히 동적 범위 설정에 강력한 장점을 가지고 있습니다.

COUNTA, MATCH, SUM, AVERAGE 같은 함수와 결합하면 매번 범위를 수정할 필요 없이 자동으로 데이터가 반영됩니다.

다만, 휘발성 함수이므로 데이터가 방대할 경우 성능 정하에 주의해야합니다.

OFFSET의 기본 원리를 이해하고 실무에 맞게 응용한다면, 차트 자동화, 보고서 업데이트, 조건부 서식 등 다양한 영역에서 작업 효율을 크게 높일 수 있습니다.

 

자 이 글을 통해서 OFFSET 함수에 대해서 간략하게 알아봤습니다.

추가로 궁금하신 내용이 있으시다면 하단의 링크를 참고해 주세요

읽어주셔서 감사합니다.

 

https://bit.ly/4mfwu6y

[Office365] 엑셀 COUNTIF 함수로 조건별 개수 세기

 

https://bit.ly/3VMFSUl

[Office365] 엑셀 이름 정의(Name Range) 활용하기

 

https://bit.ly/3IjkuTA

[Office 365] 워드 문서 병합(Mail Merge) 쉽게 따라하기