엑셀 시리즈 #18 함수 - OFFSET 함수로 자동 확장형 보고서 만들기

엑셀 시리즈 #18 함수 - OFFSET 함수로 자동 확장형 보고서 만들기

안녕하세요, 7옥타브고양이입니다 😊
오늘은 OFFSET 함수를 활용해 자동 확장형 보고서를 만드는 실무형 강의예제를 준비했습니다.
사무직 엑셀 실무에서 자주 쓰이는 보고서, 인사관리표, 재고표, 매출리포트 등에 활용하면 자동으로 데이터 범위를 인식하는 보고서를 만들 수 있습니다.

엑셀 시리즈 #18 함수 - OFFSET 함수로 자동 확장형 보고서 만들기

---

🔹 OFFSET 함수 기본 구조

OFFSET은 지정한 셀을 기준으로 일정 행과 열만큼 이동한 범위를 참조하는 함수입니다.

기본 구문

=OFFSET(기준셀, 행이동, 열이동, 높이, 너비)

인수	설명
기준셀	시작점이 되는 셀
행이동	위·아래로 이동할 행 수
열이동	좌·우로 이동할 열 수
높이	가져올 행 개수
너비	가져올 열 개수

예를 들어,

=OFFSET(A1,2,1)

👉 A1 기준에서 아래로 2행, 오른쪽으로 1열 이동한 셀(B3)을 참조합니다.

---

💼 예제 ① 월간 실적 자동 합계 보고서

엑셀_시리즈_18_실무데이터.xlsx

0.02MB

시나리오: 매월 영업 실적을 입력하면 합계가 자동으로 업데이트되는 보고서를 만들어보겠습니다.

월	실적(만원)
1월	320
2월	280
3월	350
4월	400
5월	370
6월	(추가예정)

💡 새 달이 추가될 때마다 합계 범위를 수정하지 않고 자동으로 계산되게 하려면 다음 수식을 사용합니다.

=SUM(OFFSET(B2,0,0,COUNTA(B2:B100),1))

• B2 → 실적 데이터 시작 셀
• COUNTA(B2:B100) → 실제 입력된 개수만큼 자동 계산
• 데이터가 늘어나면 SUM 범위도 자동 확장

매월 데이터가 추가되어도 자동으로 반영되므로 “합계 수정 스트레스”가 사라집니다.

월간 실적 자동 합계 보고서

반응형

---

💼 예제 ② 인사관리표 자동 인원 집계

OFFSET과 COUNTIF를 결합해 부서별 인원수를 자동 계산해볼게요.

사번	이름	부서	직급	입사일
A001	김현우	영업팀	대리	2020-03-15
A002	이지민	인사팀	과장	2019-02-12
A003	박선우	영업팀	사원	2021-08-23
A004	조유나	재무팀	대리	2022-01-04
A005	김도윤	인사팀	주임	2023-05-11

📋 영업팀 인원 자동 계산 수식

=COUNTIF(OFFSET(C2,0,0,COUNTA(C2:C100)), "영업팀")

부서 데이터가 늘어나도 OFFSET이 자동으로 범위를 확장하므로,
신규 직원이 추가되어도 인원수가 자동으로 갱신됩니다.

인사관리표 자동 인원 집계

---

💡 OFFSET으로 자동 차트 만들기

보고서에 시각화까지 자동화하려면 OFFSET을 차트 데이터 범위로 활용하세요.

=OFFSET(매출!$B$2,0,0,COUNTA(매출!$B$2:$B$100),1)

이 수식을 ‘이름 관리자’에 등록한 뒤, 차트의 데이터 범위로 지정하면
새로운 달이 추가될 때마다 차트가 자동으로 업데이트됩니다 🎯

이름 관리자 "실적범위 추가

B2:B6셀 선택 - 삽입탭 - 2차원 세로 막대형 선택 - 차트에서 마우스 오른쪽 데이터 선택 클릭

차트 데이터 선택

계열1 클릭 - 편집 클릭

계열1 - 편집

 

아래 코드를 복사하여 계열 값(V)에 붙여 넣고 확인을 누르세요

아래 '엑셀_시리즈_18_실무데이터.xlsx' 부분은 본인이 차트를 넣고자 하는 파일이름으로 수정해서 사용하세요

!실적범위: 부분은 이름관리자에서 적용한 차트 범위 입니다.

='엑셀_시리즈_18_실무데이터.xlsx'!실적범위

계열 값 입력

비어있는 6월과 7월을 실적을 작성하면 자동으로 변경되는 차트를 보실 수 있습니다.

이제는 별도 데이터 범위 지정 없이 자동으로 차트에 반영 됩니다.

자동 차트

---

📎 실무 활용 팁

• OFFSET + COUNTA → 데이터 자동 확장
• OFFSET + COUNTIF → 자동 집계 보고서
• OFFSET + CHART → 동적 그래프 구현
• OFFSET + VLOOKUP → 실시간 데이터 참조

OFFSET은 단독으로도 강력하지만, 다른 함수와 조합하면
엑셀의 자동화 수준을 한 단계 끌어올릴 수 있습니다.

다음 포스팅에서는 엑셀 시리즈 #19 자동화 - INDIRECT 함수로 시트 자동 연결하기를 다룰 예정이에요.

 

💬 사무직에서 매달 반복 입력하던 실적표, 인사관리표, 재고표가 이 한 가지 함수로 ‘자동 보고서’로 바뀝니다.

---

✍️ 작성자: 7옥타브고양이