엑셀에 제대로 관심을 갖으면서 알게된 함수 offset은
엑셀로 자동화를 가능하게 해주는 최고의 함수중 하나입니다.
특히 주기적으로 데이터를 관리한다면 offset 함수는 필수 입니다.
월매출 관리 시트를 대부분 자동화하고 맨 마지막에 전년도 누계를 해결하지 못했을때
검색하고 또 검색해서 해결책으로 찾은 offset 함수를 쓰고 나서부터는
굳이 자동화 프로그램을 쓰지 않고도 엑셀만으로도 자동화가 가능해졌다.
예를들어, 전년도 누계를 구한다고 가정하면,
2월에는 1~2월 합계, 3월에는 1~3월합계를 올해 누계 vs 전년 누계를 보통 비교합니다.
흔히 'YOY'라고 표현하지요.
offset 함수를 모르면 매월 sum을 구합니다.
깜빡하고 수정을 안하면... 팀장님한테 불려가거나, 발표자료에 실수가 생길수 있는데요.
하지만 offset 함수와 month 함수를 사용하면 매달 자동으로 범위가 변경되면서
매월 자동 계산이 됩니다. 엑셀 수식만으로도 자동화가 가능하다니...
저는 offset 함수를 접하고 또한번 엑셀에 더더 애정하게 되었습니다.
offset 함수 로직
=OFFSET(기준셀, 행_이동량, 열_이동량, [높이], [너비])
쉽게 풀어서 설명하자면,
- 기준셀에서 아래(행)로 몇칸 이동할지, 옆(열)으로는 몇칸 이동할지를 정하고
- []의 높이, 너비는 생략할 수도 있습니다.
offset 함수의 매력은 다른 함수와 함께 사용함으로서 동적인 함수가 되면서 매력이 폭발합니다.
예를들어,
미국, 영국의 국가별 매출이 위와 같고, 현재는 10월이라고 가정하면,
22년 1~9월까지의 누계를 구하는 함수는
=SUM(B3:OFFSET(A3,0,MONTH(TODAY())-1)
SUM 함수로만 구한다면 B3에서 J3 까지 구하는건 =SUM(B3:J3) 입니다.
같은 결과를 보려면 위와 같은 함수를 만들 수 있습니다.
다만, 이렇게 설정해둔다면, 2월말부터 12월말까지 이 수식으로 2022년 누계는 매번 변경할 필요가 없어집니다.
여기서 추가적으로 MONTH, TODAY 함수를 설명 드리자면,
=TODAY()
항상 () 와 함께 사용해야하며, 오늘 연도,월,날짜를 표기하는 함수입니다.
=MONTH(TODAY())
이렇게 중복사용하게되면, 오늘 날짜에서 '월'만을 반환하게 되고,
이걸 이용해서 날짜가 바뀌고 월이 바뀌면서 OFFSET 함수가 자동 계산되게 됩니다.
-1을 쓰면 10월이면 9칸, 11월이면 10칸을 가게 되겠지요.
그런데 매달 매출을 정리하고 2022년의 누계를 SUM으로 수정하고 계시진 않나요?
이렇게 매달 변경해야하는 데이터를 SUM함수로만 해놓았을 경우에 단점은
일단 수정해야한다는 번거로움,
두번째는 수정을 잊어버렸을 경우 데이터의 정확성이 문제가 된다는 점입니다.
매출 누계를 구하셔야한다면 OFFSET 함수를 MONTH, TODAY 함수와 꼭 함께 사용해보세요.
OFFSET 함수를 알고는 정말 엑셀에 위대함에 다시 한번 찬사를 보냈습니다.
엑셀을 알면 알수록 신기하고 재미있습니다.
엑셀과 친해지면 업무는 빠르고 정확해집니다.
논리적인 사고력도 향상됩니다.
오늘도 칼퇴와 여유로운 직장 생활을!!
OFFSET 만세!!!!
'직장인 필수 엑셀' 카테고리의 다른 글
| 직장인의 엑셀 - 피벗테이블 월 그룹을 날짜로 바꾸는 법 (0) | 2024.02.05 |
|---|
