[엑셀] 이름정의로 데이터 유효성검사 동적(가변) 드롭다운 목록 만들기

데이터 유효성검사는 사용자가 셀에 입력하는 데이터 데이터 형식 또는 값을 제한 할 수 있습니다. 그 중에서 많이 활용하는 기능은 드롭다운 목록을 만들어 지정된 값을 입력하는 것입니다.

드롭다운 목록은  콤보상자 (양식컨트롤 및 ActiveX 컨트롤)과 유사하지만 표와 같이 여러 셀에 적용하려면 유효성 검사가 훨씬 편리합니다.

그런데 드롭다운 목록은 그 목록수가 한정되어 있기보다는 추가되어 늘거나 줄어드는 경우가 있습니다.

오늘은 목록의 추가에 따라 목록이 동적으로 변하는 가변 드롭박스 만드는  방법에 대해 설명 드립니다.

이름정의로 목록 범위 정의

1. 리본메뉴 [수식] - [이름정의]를 클릭합니다.

2. [새 이름] 창에 [이름]을 입력하고 [참조대상]에 아래의 수식을 입력합니다.

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

4. [확인] 버튼을 클릭하면 "품명"이라는 이름이 정의 됩니다.

☞ 수식설명

1. COUNTA 함수 : 품명이 있는 A열의 비어있지 않는 셀의 개수를 반환합니다. (주의 : A열 전체를 참조 할때는 A열에는 품명외 다른 값이 입력되면 안됨)

2. OFFSET 함수 :  참조영역 A1셀에서 행방향으로 1, 열방향으로 0 떨어진 위치에서 COUNTA - 1 만큼의 행수의 참조 범위를 반환 합니다.

3. A열에 품명이 추가되면 COUNTA 함수에 의해 참조 범위가 변합니다.

데이터 유효성검사 드롭다운 목록 만들기

1. 리본메뉴 [데이터] - [데이터 유효성 검사]를 클릭합니다.

2. [제한대상]을 [목록]을 선택합니다.

3. [원본]에 수식 = 품명 을 입력합니다. (여기서 품명은 위에서 정의한 이름입니다.)

4. [확인] 버튼을 클릭합니다.

동적(가변) 드롭다운 목록상자

품명이 추가되면 드롭다운 목록 리스트도 자동으로 추가되어 범위를 수정할 필요가 없습니다.

관련글 보기

이름정의

인사카드 만들기

03

첨부파일 보기

동적 드롭다운목록 만들기.xlsx