[엑셀 스킬] Excel 함수 'OFFSET'

Excel은 데이터 조작과 분석에 필수적인 다양한 함수를 제공합니다. 이러한 함수 중 하나인 "OFFSET" 함수는 다양한 분야에서 활용할 수 있는 강력한 도구입니다. 이 블로그 글에서는 Excel의 "OFFSET" 함수의 구체적인 예시와 사용 사례에 대해 알아보겠습니다.

 

"OFFSET" 함수란?

Excel의 "OFFSET" 함수는 다섯 가지 입력을 사용하여 동적 범위를 반환합니다. 이 입력은 (1) 시작 지점, (2) 행 오프셋, (3) 열 오프셋, (4) 행 수, (5) 열 수입니다. "OFFSET" 함수는 변동성 함수로서 대형이거나 복잡한 워크시트에서 성능 문제를 일으킬 수 있습니다.

 

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

시작 지점은 "reference" 인수로 표시되며 단일 셀 또는 셀 범위일 수 있습니다. "rows"와 "cols" 인수는 시작 지점으로부터 오프셋할 셀의 수를 결정합니다. "height"와 "width" 인수는 선택적이며 생성될 범위의 크기를 정의합니다. 생략된 경우, 기본적으로 참조의 높이와 너비로 설정됩니다.

예를 들어, A1에서 시작하여 C5의 값을 참조하려면 참조는 A1, 행 오프셋은 4, 열 오프셋은 2입니다:

=OFFSET(A1, 4, 2) // C5의 참조를 반환합니다.

A1에서 C1:C5의 범위를 참조하려면 참조는 A1, 행 오프셋은 0, 열 오프셋은 2, 높이는 5, 너비는 1입니다:
=OFFSET(A1, 0, 2, 5, 1) // C1:C5의 참조를 반환합니다.

행 오프셋과 열 오프셋에는 음수 값을 사용하여 정상적인 오프셋 방향을 반대로 할 수 있습니다. 음수 열은 왼쪽으로, 음수 행은 위쪽으로 오프셋됩니다.

"OFFSET" 함수의 주요 목적은 공식이 사용 가능한 데이터나 사용자 입력에 동적으로 조정될 수 있도록 하는 것입니다. 이 함수는 차트나 피벗 테이블의 동적 명명된 범위를 생성하는 데 사용될 수 있으며, 원본 데이터가 항상 최신 상태로 유지될 수 있도록 합니다.

 

"OFFSET" 함수 사용 예시

다양한 유형의 범위를 반환하기 위해 "OFFSET" 함수를 구성하는 몇 가지 예시를 살펴보겠습니다. 이 예시들은 Excel 365에서 캡처되었으며, 결과가 하나 이상의 셀인 경우 "OFFSET" 함수는 동적 배열을 반환합니다. Excel의 이전 버전에서는 결과를 확인하기 위해 F9 키를 사용할 수 있습니다.

예시 1: 두 번째 열에서 세 번째 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 두 번째 열(West)에서 세 번째 값(March)을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 3, 2) // D6을 반환합니다.

예시 2: 세 번째 열에서 마지막 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 세 번째 열(North)에서 마지막 값(June)을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 6, 3) // E9를 반환합니다.

예시 3: 세 번째 열의 모든 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 세 번째 열(North)의 모든 값을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 1, 3, 6) // E4:E9를 반환합니다.

예시 4: 다섯 번째 행의 모든 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 다섯 번째 행(May)의 모든 값을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 5, 1, 1, 4) // C8:F8을 반환합니다.

예시 5: West 지역의 April, May, June 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 West 지역의 April, May, June 값을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 4, 2, 3, 1) // D7:D9를 반환합니다.

예시 6: West와 North의 April, May, June 값 가져오기
다음 화면에서는 "OFFSET"을 사용하여 West와 North 지역의 April, May, June 값을 가져옵니다. H5의 공식은 다음과 같습니다:
=OFFSET(B3, 4, 2, 3, 2) // D7:E9를 반환합니다.

이러한 예시들은 "OFFSET" 함수를 사용하여 다양한 조건에 맞는 특정 값을 또는 범위를 동적으로 가져오는 방법을 보여줍니다. 함수의 인수를 조정함으로써 출력을 원하는 대로 사용자 정의할 수 있습니다.

 

분야별 활용 사례

"OFFSET" 함수는 다양한 분야에서 여러 가지 이점을 제공하며 다양한 용도로 활용될 수 있습니다. 여러분야에서 "OFFSET" 함수의 구체적인 사용 사례를 살펴보겠습니다:

금융 분석
"OFFSET" 함수는 금융 분석에서 매우 유용합니다. 예를 들어, Pivot 테이블이나 차트의 원본 데이터가 항상 최신 상태로 유지되어야 하는 경우 "OFFSET" 함수를 사용하여 동적으로 범위를 조정할 수 있습니다. 이를 통해 금융 데이터의 분석과 시각화에 효율적으로 작업할 수 있습니다.

예산 및 재무 분석
"OFFSET" 함수는 예산 및 재무 분석에서도 유용하게 활용될 수 있습니다. 예를 들어, 수입과 지출의 변동 패턴을 분석하거나 예산 편성을 위해 특정 기간의 데이터를 추출할 수 있습니다. "OFFSET" 함수를 사용하면 범위를 동적으로 조정하여 예산과 재무 모델링을 효과적으로 수행할 수 있습니다.

데이터 관리 및 보고서 작성
"OFFSET" 함수는 데이터 관리와 보고서 작성에도 유용합니다. 특정 조건에 따라 데이터를 추출하거나 필요한 정보를 동적으로 업데이트하는 데 사용할 수 있습니다. 예를 들어, 특정 지표의 변화 추이를 분석하거나 필요한 데이터를 자동으로 가져와서 보고서를 작성하는 데 "OFFSET" 함수를 활용할 수 있습니다.

 

Excel의 "OFFSET" 함수는 다양한 분야에서 유용하게 활용될 수 있는 강력한 도구입니다. 이 함수를 사용하여 데이터 조작, 분석, 예산 편성, 보고서 작성 등 다양한 작업을 효율적으로 수행할 수 있습니다. "OFFSET" 함수의 다양한 예시와 사용 사례를 통해 데이터 관리와 분석에 대한 이해를 높일 수 있습니다. 자신의 작업에 맞게 "OFFSET" 함수를 적절히 활용하여 엑셀의 잠재력을 최대한 활용해 보세요.

 

*참고 자료:
Exceljet - OFFSET Function

OFFSET Function

Exceljet - Dynamic Named Range with OFFSET

Dynamic named range with OFFSET