[엑셀 목록상자 자동갱신] offset 동적범위 함수로 목록상자 자동화하기

엑셀에서 OFFSET 함수를 사용하여 동적 범위를 만들고 이를 **목록상자(데이터 유효성 검사)**에 연결하면, 데이터를 추가하거나 삭제할 때마다 자동으로 목록이 업데이트됩니다.

 

---

✅ 1. 이름 정의(동적 범위 설정)

1. [수식] → [이름 관리자] 클릭

1. [새로 만들기] 클릭
2. 이름: 동적목록 > 변경 가능
3. 참조 대상: 목록 수식 입력

=OFFSET(Sheet1!$F$3, 0, 0, COUNTA(Sheet1!$F$2:$F$8)-1, 1)

❗ 설명:

• OFFSET(Sheet1!$F$3, 0, 0, ...): A2 영업팀 셀을 시작으로 오프셋 설정 > 동적목록 첫번째 목록인 [영업팀] 란 지정 
• COUNTA(Sheet1!$F$2:$F$8)-1, 1): 동적목록으로 자동화할 [영업팀 - 연구팀]까지 영역 지정 > 셀 전체도 가능
• 1: 열 너비 1

 

---

✅ 2. 목록상자 설정 (데이터 유효성 검사)

1. 목록상자를 설정할 셀 선택 (=B3:B9)
2. [데이터] → [데이터 유효성 검사] → [설정]
3. 유효성 기준: 목록

F3 누르면 나오는 화면

1. 원본: 아래처럼 입력 or "F3" 누르고 선택 

=동적목록

 

 

---

✅ 3. 사용 결과

이제 데이터를 변경하면, 목록상자의 목록도 자동으로 변경됩니다.

 

 

---

✅ 참고 팁

• 항목 중간에 공백 셀이 생기면 COUNTA가 제대로 작동하지 않음 → 이 경우 INDEX + MATCH 등을 활용한 다른 방식 필요
• 여러 열로 구성된 데이터의 경우 OFFSET의 열 인자와 너비 조정 필요

 

 

 

 

OFFSET을 활용한 기본 방식 외에 다중 범위, 조건부 목록, Excel 테이블을 활용한 동적 목록 자동화 방법을 정리해두었습니다.

 

 

---

🧠 1. Excel 테이블로 더 쉽게 동적 목록 자동화하기 (가장 쉬운 방법)

✅ 장점

• 자동 범위 갱신 (추가/삭제 시 자동 반영)
• OFFSET/COUNTA 필요 없음
• 직관적 관리 가능

🔧 방법

1. 부서명을 A열에 입력 (예: A1:A4)
2. 해당 범위 선택 후 Ctrl + T 눌러 테이블로 변환
   • 테이블 이름 예: 부서테이블
3. 수식 → 이름 관리자 → 새 이름 만들기
   • 이름: 부서목록
   • 참조 대상:
   • =부서테이블[부서명]
4. 데이터 유효성 설정 → 목록 → =부서목록 입력

 

---

🧠 2. 조건부 목록 자동화 (예: 대분류에 따라 소분류 자동 변경)

✅ 시나리오

• B열: 대분류 (예: 과일, 야채)
• C열: 대분류에 따라 소분류 자동 목록 제공

🔧 단계

① 데이터 준비

A B C

대분류	소분류	선택된 항목
과일	사과
과일	바나나
야채	상추
야채	배추

② 이름 정의 (대분류별 목록)

• 사과, 바나나 범위를 선택하고 이름을 과일로 설정
• 상추, 배추 범위를 선택하고 이름을 야채로 설정

③ 유효성 검사 설정

1. B열: 대분류 (목록: 과일, 야채)
2. C열: 소분류
   • 유효성 원본 수식:
   • =INDIRECT(B2)

이제 B2가 “과일”이면 C2에서는 과일 목록만 나옴!

 

 

---

🧠 3. 다중 범위 동적 목록 (예: 여러 열 합치기)

✅ 예: A열, B열 각각 항목 있음 → 하나의 목록으로 만들기

① 수식 정의 (OFFSET + COUNT)

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

• 두 번째 목록:

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

② 둘을 합치는 목록(배열 수식)

• Excel 365 이상: =VSTACK(목록1, 목록2) 사용 가능
• Excel 2019 이전: VBA 또는 수동 조합 필요

 

---

🎁 추천: Excel 테이블 + 동적 드롭다운 조합

가장 실용적이고 유지관리 쉬운 방식은 다음 조합입니다:

• Excel Table 사용해서 동적 범위 생성
• INDIRECT로 조건부 목록 구현
• SORT/UNIQUE 함수로 중복 없는 깔끔한 목록 생성