OFFSET함수로 동적범위설정하기

OFFSET함수로 동적범위설정하기

OFFSET 함수를 사용하여 동적 범위를 생성할 때, 해당 함수는 지정된 기준 셀 또는 범위에서 시작하여 특정 행 및 열의 수에 따라 범위를 동적으로 조정하는 데 사용됩니다. 이를 통해 데이터가 변경되면 동적 범위도 자동으로 조정되므로 보고서나 분석 작업을 간편하게 관리할 수 있습니다.

 

 

 

OFFSET함수로 동적범위설정

 

 

OFFSET 함수의 구문

기본함수식	내용
OFFSET(reference, rows, columns, [height], [width])	reference: OFFSET 함수의 기준이 되는 셀 또는 범위입니다. rows: 기준 셀로부터 이동할 행 수를 나타내는 값입니다. 양수 또는 음수 모두 사용 가능합니다. columns: 기준 셀로부터 이동할 열 수를 나타내는 값입니다. 양수 또는 음수 모두 사용 가능합니다. height (선택적): 반환할 범위의 높이를 나타내는 값입니다. 이 인수를 지정하지 않으면 기본적으로 reference의 높이가 사용됩니다. width (선택적): 반환할 범위의 너비를 나타내는 값입니다. 이 인수를 지정하지 않으면 기본적으로 reference의 너비가 사용됩니다.

 

 

OFFSET함수의 동적범위 예제 1

우리는 월별 판매 데이터가 저장된 엑셀 시트를 가지고 있으며, 이 데이터에는 각 월별로 매출이 기록되어 있습니다. 우리는 OFFSET 함수를 사용하여 동적 범위를 생성하고 월별 매출을 추출하려고 합니다.

 

	A	B	C
1	월별	매출($)
2	1월	5000
3	2월	6000
4	3월	5500
5	4월	7500
6	5월	6200
7	6월	5900

 

 

동적 범위 설정:

동적 범위를 설정하기 위해 OFFSET 함수를 사용합니다. 예를 들어, 월별 매출을 추출하기 위해 다음과 같이 OFFSET 함수를 사용할 수 있습니다:

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

이 함수는 $B$2 셀을 기준으로 시작하며, 오른쪽으로 1 열 이동하고, COUNTA(B:B)-1만큼의 행을 포함하는 동적 범위를 반환합니다.  OFFSET은 결국 C2:C7의 범위가 설정될것입니다.. 

 

위의 함수식을 분석해보면 $B$2는 기준셀이되는 위치B2셀을 기점으로 '0'은 열방향으로의 이동이 없다는 의미이고, '1'은 행방향으로의 이동이 1만큼 이동하라는 의미로 결국 C열을, 'COUNTA(B:B)-1' 은 B열전체를 의미하는데, 머리글인 1행을 제외하라는 의미로 결국 COUNTA함수는 설정된 범위에서 연속된 데이터의 갯수를 반환할것이므로 7이됩니다..

 

즉 B열의 끝은 B7입니다.. 결국 C2:C7의 범위가 잡히게 되는 겁니다.. OFFSET함수는 그자체로서 큰 의미는 없고 먼저배운 VLOOKUP함수나 SUM함수 등과 조합으로 쓰일때 비로서 의미가 있는 함수입니다.... 

 

VLOOKUP함수등과 조인되어 더 유동적이고 융통성있는 범위를 설정할수 있게 됩니다...

 

 

동적 범위의 이점:

데이터가 변경될 때: 

데이터가 변경되면 OFFSET 함수를 사용하여 생성된 동적 범위도 자동으로 조정됩니다. 새로운 데이터가 추가되거나 기존 데이터가 삭제되어도 함수를 다시 수정할 필요가 없습니다.

보고서 및 분석의 효율성:

 동적 범위를 사용하면 데이터 분석 및 보고서 작성 프로세스를 더 효율적으로 관리할 수 있습니다. 새로운 데이터가 동적 범위에 자동으로 반영되므로 업데이트 및 관리가 용이합니다.

이처럼 OFFSET 함수를 사용하여 동적 범위를 설정하면 데이터를 더 효과적으로 다룰 수 있으며, 다양한 데이터 분석 및 보고서 작업에 활용할 수 있습니다.

 

자동 업데이트: 

OFFSET 함수를 사용하여 생성된 동적 범위는 데이터가 변경될 때 자동으로 업데이트됩니다. 이는 데이터가 추가, 삭제 또는 수정되었을 때 수동으로 범위를 조정할 필요가 없음을 의미합니다.

다양한 분석 작업: 

동적 범위를 사용하면 데이터베이스 관리, 보고서 작성, 데이터 검색, 그래프 작성 등 다양한 데이터 분석 작업을 효과적으로 수행할 수 있습니다.

 

유연성:

 OFFSET 함수를 사용하면 범위의 크기와 위치를 동적으로 조정할 수 있으므로, 다양한 데이터 구조 및 레이아웃에 대응할 수 있습니다.

간결성: 

OFFSET 함수를 사용하면 복잡한 수식이나 매크로 없이도 동적 범위를 쉽게 설정할 수 있습니다.

 

동적범위의 단점:

계산 비용: 

 

OFFSET 함수는 작동 시 데이터를 동적으로 검색하고 반환하므로 큰 데이터 범위에 사용할 경우 계산 비용이 높을 수 있습니다. 특히 많은 OFFSET 함수를 시트에 사용할 경우 성능에 영향을 미칠 수 있습니다.

 

유지 보수 어려움: 

 

OFFSET 함수를 사용하여 설정한 동적 범위는 수식 자체로 어떤 범위를 가리키는지 한눈에 파악하기 어렵습니다. 이로 인해 나중에 업데이트 및 유지 보수가 어려울 수 있습니다.

 

다른 함수로 대체 가능성: 

 

OFFSET 함수는 특정 상황에서 다른 함수로 대체할 수 있는 경우가 있으며, 다른 함수를 사용하는 것이 더 효율적일 수 있습니다.

요약하면, OFFSET 함수를 사용한 동적 범위는 데이터를 관리하고 분석하는 데 유용한 도구이지만, 성능 문제와 유지 보수 어려움 등 일부 단점을 고려해야 합니다. 데이터의 크기와 복잡성, 사용 사례에 따라 OFFSET 함수를 활용할지 다른 방법을 고려할지 결정해야 합니다.

 

 

OFFSET함수의 동적범위 예제 2

쇼핑몰의 상품 결제금액 합계를 내는 함수를 사용할때 : SUM함수를 사용시 간단하게 SUM(D6:D24) 이런 함수식으로 쉽게 구할수 있을겁니다. 그러나 이런 수식은 신규판매로 결제금액이 추가가될 때 수식을 고쳐야 하는 불편함이 있습니다..

 

그럴때  많은 신규데이터를 추가처리할 때나 삭제할때등 데이터가 변경될때 유동적으로 범위가 변경되어 자동계산되는 수식을 사용하게되면 유용할겁니다...

 

 

 

 

동적범위를 이용한 결제금액합계를 내는 수식으로는

동적 범위를 설정하는 수식으로 =OFFSET(SUM함수!$D$6,,,COUNTA(SUM함수!$D$6:$D$200)) 이와 같은 수식을 사용하면 데이터가 추가되거나 삭제되어도 원하는 값을 일일이 수정하지 않아도 쉽게 구할수 있을 겁니다...

 

위의 수식을 FORMULAS(수식)->DEFINE NAME(이름정의하기)에서 참조대상에 수식을 넣어 정의하면 SUM함수와 조합하여 구할수 있습니다...

 

이름정의에서 이름을  '결제금액동적범위' 라고 정의한다고 가정하면 H5셀의 수식으로 =SUM(결제금액동적범위)를 입력시 값을 구할수 있습니다.. 

 

위의 엑셀표를 보면 결제금액의 합계는 D6셀부터 D24셀까지의 합계입니다..  D24셀까지의 합계이므로 일반적으로 구하는 합계 함수식의 결과값 즉 G5셀의 값과 동일하게 됩니다.

 

여기서 신규로 판매되는 상품이 늘어나거나 줄어들 경우라면 G5에 들어있는 수식 즉 SUM(D6:D24)으로 값을 구한다면 부정확한 결과가 표시될것입니다... 

 

이처럼 데이터의 추가 삭제시 그범위를 유동적으로 설정하여 이름정의해놓으면 에러없이 함수식 수정없이 정확한 값을 구할수 있습니다.. 

 

 

위의 표를 보면 추가로 25행과 26행에 데이터가 추가된것을 볼수 있는데,,  일반적인 방법으로 SUM함수를 이용하여 계산을 하면 SUM(D6:D24)였던 원래 함수식에서 끝의 범위 D24를 D26으로 수정해야 됩니다.. 데이터가 추가될때마다 함수식을 수정해야하는 번거로움이 있습니다... 그럼 OFFSET함수를 이용하여 동적범위를 설정하면 아주 편할겁니다..

 

먼저 설명한 FORMULAS-(수식) -> DEFINE NAME(이름정의하기)에서 이름을  '결제금액동적범위'리고 입력후 참조대상에서 =OFFSET(SUM함수!$D$6,,,COUNTA(SUM함수!$D$6:$D$200))  이런 함수식을 사용한다면 위의 표처럼 신규로 데이터가 추가로 늘어나거나 삭제 변경되어도 원하는 값을 구할수 있습니다..

 

위의 식을 분석하면 'SUM함수의 시트' 의 D6셀을 기점으로 COUNTA(SUM함수!$D$6:$D$200)' 이것은 D6부터 D200까지의 연속된 데이터의 갯수를 새는 것입니다.. 여기서는 새로늘어난 데이터까지 합하면 21개가 됩니다... 물론 27행의 데이터는 빈공란입니다.. 즉 21개의 데이터 D6셀부터 21개의 데이터즉 D6:D26 이 됩니다... 이범위를 합산하라는 겁니다.. 

 

 

위의 그림을 살펴보면 데이터가 추가되었는데 일반식으로 구한 결제금액의 합계는 값에 변경이 없는 것을 볼수 있고 동적범위를 이용하여 구한 결제금액합계는 값이 변경된것을 확인할수 있습니다...

 

만일 200행을 넘어가면 위의 식도 부정확한 값을 산출할것이기 때문에 더 안전한 코드로 바꿀필요가 있을 겁니다..  그것은 끝의 셀을 D200에서 $D:$D이런식으로 바꾸면 더 확실할 겁니다... =OFFSET(SUM함수!$D$6,,,COUNTA(SUM함수!$D$6:$D$200)) 을 =OFFSET(SUM함수!$D$6,,,COUNTA(SUM함수!$D:$D)-1)  이런식으로 바꾸면 오류를 예방할수 있습니다.. D열전체에서 머리글행을 하나 없앤 갯수를 구한다는 것입니다...

 

OFFSET함수의 동적범위 예제 3

 

 

위의 표에서 결제금액에 기본배송비를 더한 판매대금을 구하라는 예제가 주어진다면  OFFSET을 이용해 동적범위를 설정하되, D열 뿐아니라, E열까지도 합계를 내야하므로, 새로운 이름정의로 '판매대금동적범위' 등의 이름을 설정하여 정의해놓고 범위를 동적범위를 활용하여 D6부터 연속된 데이터의 범위를 모두 설정하도록 하면 구할수 있습니다...

 

앞서 설명한 OFFSET함수를 약간만 수정하면 구할수 있습니다...

 

 

위의 그림처럼 =OFFSET(SUM함수!$D$6,,,COUNTA(SUM함수!$D:$D)-1,COUNTA(SUM함수!$D$6:$E$6)) 참조범위를 설정합니다...

 

분석하면 COUNTA(SUM함수!$D:$D)-1 은 종방향으로의 갯수를 COUNTA(SUM함수!$D$6:$E$6)은 횡방향으로의 개수를 의미하고,, D열의 연속된 데이터의 갯수에서 1을 빼고, COUNTA(SUM함수!$D$6:$E$6) 은 횡방향의 너비입니다..

 

즉 2개가 됩니다.. SUM함수를 이용하면 추가된 데이터2개 까지 더하면 21, 2개의 범위를 의미 D6:E26 까지의 합  즉 결제대금의 총합에서 기본배송비의 총합을 더하라고 하는 함수식을 만들수 있습니다..

 

SUM(판매대금동적범위) 를 L4에 이용할수 있습니다...  

 

내용이 너무 길어 지니 오늘은 여기서 끊고 이어서 내일 다룰 내용은 내일 포스팅올리겠습니다...