[엑셀] 함수를 활용한 유효성검사 가변형 목록 만들기

데이터 유효성 검사를 활용해 [목록]을 활용해서 문서를 만들때 데이터의 변화에 따라 [목록]의 값도 바뀌어야 하는데 일반적인 방법은 그렇지 못합니다.

아래 움짤은 VBA를 사용하지 않고 함수만 활용한 방법이며, [목록] 즉 [리스트]의 개수가 달라질때 유효성검사의 [목록상자]도 변하는 예 입니다.

<유효성 검사 가변형 목록>

이름정의로 범위 지정하기

COUNTA 함수로 리스트의 개수를 파악하고 OFFSET 함수로 리스트의 영역(범위)를 반환 받습니다.

리스트의 개수가 늘어나면 영역도 자동으로 늘어나고 줄어들면 반대로 줄어 들겠죠.

1. [수식] - [이름정의]를 클릭하면 [이름편집] 창이 나타남

2. [참조대상]에 아래의 수식 입력

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

수식설명

1. OFFSET 함수 : 참조하는 셀(A1)에서 지정하는 범위를 반환

2. COUNTA 함수 : 리스트의 개수만큼 행의 범위를 지정(리스트의 개수가 변하면 OFFSET함수의 범위가 조정 됨)

데이터 유효성 검사

1. [데이터] - [데이터 유효성 검사] 하면 데이터 유효성 창이 나타남

2. [제한대상]에서 [목록] 선택

3. [원본]에는 [=사번] 입력 (여기서 [사번]은 이름정의에서 정한 [사번] 임)

4. [확인] 버튼 클릭

관련글 보기

OFFSET 함수		인사카드 만들기

첨부파일 보기

유효성검사(가변형 목록만들기).xlsx