[엑셀/EXCEL]변동범위, 주소반환 함수 OFFSET

엑셀의 함수를 만들다 보면 범위를 고정해놓기 때문에 답답함을 느낄 때가 있습니다.

데이터를 일자에 맞춰 아래로 혹은 오른쪽으로 더 늘려줘야 하는 경우라면 매번 그에 맞춰 함수를 조정해줘야 합니다.

하지만 OFFSET 함수를 사용하면 값이 아닌 범위를 반환하여 데이터 범위가 바뀔 때마다 자동으로 변경될 수 있습니다.

 

여기서 값을 반환한다 와 범위를 반환한다가 이해가 되지 않을 수 있기 때문에 간단히 설명 드리겠습니다.

OFFSET을 이용하게 되면 이 값 1을 반환받는 것이 아니라 A1을 반환받을 수 있습니다.

또한 이 범위를 변수로 늘리거나 줄일 수 있기 때문에 데이터가 변동되어도 맞춰서 반영할 수 있습니다.

---

함수의 구조가 복잡한데, 함수 사용 방법이 어렵다기보다는 개념 이해가 어렵습니다.

아래 표로 설명을 드리겠습니다.

OFFSET의 Reference범위는 [B2]입니다. 즉 B2를 기준으로 범위와 값을 반환한다는 뜻입니다.

그 뒤 Rows 값은 [4]입니다. 아래로 총 4칸을 이동하게 된다는 뜻으로 B2의 아래로 4번째 Rows [B6]의 위치가 됩니다.

그리고 Cols 값은 [5]로 우측으로 5칸 즉 [G6]의 위치가 반환됩니다.

현재는 그곳에 값이 1이 있기 때문에 해당 함수를 실행할 경우 1의 값을 반환하게 됩니다.

---

그럼 범위를 반환한다는 것에 대해서 설명드리겠습니다.

위의 함수를 실행하게 되면 45(10+12+1+3+19)를 반환하게 됩니다.

또한 아래와 같이 범위를 변수로 지정할 경우 그 범위에 맞춰 계산이 다시 이루어지게 됩니다.

 

---

그럼 가변 범위를 더 효율적으로 사용하는 방법을 설명드리겠습니다.

세로 범위를 Counta로 지정하게 될 경우 해당 범위의 값이 추가될 때마다 자동으로 더하는 값이 늘어나게 됩니다.

 

이런 식으로 데이터에 새로운 값이 들어올 때마다 해당하는 범위를 자동으로 계산하도록 만들 수 있습니다.

이를 이용하면 지속적으로 raw가 가변 되는 데이터라고 하더라도 함수의 변화 없이 추가적인 데이터 적재가 가능합니다.

offset함수는 범위를 반환할 수 있다는 것을 잘 기억하시고 가변되는 데이터를 핸들링할 때는 offset을 잘 사용하시는 것이 중요합니다.