엑셀 ‘데이터 유효성 검사’ 활용 하여 동적 드롭다운 목록 만들기

 

엑셀 ‘데이터 유효성 검사’ 활용 심화: 동적 드롭다운 목록 만들기

엑셀의 데이터 유효성 검사는 데이터를 정확하게 입력하도록 돕는 강력한 도구입니다. 이 글에서는 기본 드롭다운 메뉴를 넘어 **동적 드롭다운 목록**과 **다단계(계층적) 드롭다운 목록**을 만드는 방법을 소개합니다. 초보자도 쉽게 따라할 수 있도록 예제와 함께 설명합니다.

목차

1. 데이터 유효성 검사란?
2. 기본 드롭다운 메뉴 만들기
3. OFFSET 함수로 동적 드롭다운 목록 만들기
4. 다단계(계층적) 드롭다운 목록 생성하기
5. 실무에서 동적 드롭다운 목록 활용 사례

1. 데이터 유효성 검사란?

엑셀의 데이터 유효성 검사는 사용자가 특정 셀에 입력할 수 있는 값을 제한하거나 검증하는 기능입니다. 데이터 입력 오류를 줄이고, 효율적인 데이터 관리를 도와줍니다.

유효성 검사를 설정하려면 다음 단계를 따르세요:

1. 제한을 설정할 셀을 선택합니다.
2. 리본 메뉴에서 데이터 → 데이터 유효성 검사를 클릭합니다.
3. 조건을 설정하고 확인을 누릅니다.

2. 기본 드롭다운 메뉴 만들기

기본 드롭다운 메뉴는 정해진 목록에서만 데이터를 선택할 수 있도록 설정하는 방식입니다. 간단한 예제를 통해 살펴보겠습니다:

예제: 기본 드롭다운 메뉴 만들기

1. 드롭다운 메뉴를 설정할 셀(A1)을 선택합니다.

2. 리본 메뉴에서 데이터 → 데이터 유효성 검사를 클릭합니다.

3. **제한 대상**을 "목록"으로 선택한 뒤, 원본에 아래와 같이 입력합니다:

사과, 바나나, 포도

4. 확인을 누르면 드롭다운 메뉴가 생성됩니다!

3. OFFSET 함수로 동적 드롭다운 목록 만들기

기본 드롭다운 메뉴는 고정된 값만 사용할 수 있지만, **OFFSET 함수**를 활용하면 데이터 범위가 동적으로 변경되는 목록을 만들 수 있습니다. 예제를 통해 알아보겠습니다:

예제: 동적 드롭다운 목록 만들기

1. 데이터가 있는 범위(A1:A10)를 선택합니다.

2. 리본 메뉴에서 **수식 → 이름 관리자**를 클릭합니다.

3. 새 이름을 정의합니다(예: "동적목록")

4. **참조 대상**에 아래 수식을 입력합니다:

=OFFSET($A$1, 0, 0, COUNTA($A$1:$A$100), 1)

5. 드롭다운을 설정할 셀을 선택하고, 데이터 유효성 검사에서 원본을 =동적목록으로 설정합니다.

결과: 데이터가 추가되거나 삭제되어도 드롭다운 목록이 자동으로 업데이트됩니다!

4. 다단계(계층적) 드롭다운 목록 생성하기

다단계 드롭다운은 첫 번째 선택에 따라 두 번째 드롭다운 메뉴가 달라지는 방식입니다. 예제를 통해 만들어 보겠습니다:

예제: 다단계 드롭다운 메뉴 만들기

1. 첫 번째 목록(예: "과일", "채소")과 각각의 세부 목록을 준비합니다.

2. 각 세부 목록에 이름을 정의합니다:

• "과일" → 사과, 바나나, 포도
• "채소" → 당근, 감자, 브로콜리

3. 첫 번째 드롭다운을 생성합니다(위 단계 참고).

4. 두 번째 드롭다운의 데이터 유효성 검사에서 원본을 아래와 같이 설정합니다:

=INDIRECT(A1)

결과: A1에서 "과일"을 선택하면 B1에는 "사과, 바나나, 포도"가 표시됩니다.

5. 실무에서 동적 드롭다운 목록 활용 사례

• 재고 관리: 카테고리별 품목을 관리하고 선택
• 프로젝트 관리: 업무 분류에 따라 세부 작업 선택
• 보고서 작성: 데이터 입력 오류를 줄이고 자동화된 필터링 제공

동적 드롭다운 목록은 대량 데이터 작업을 간소화하고 효율적으로 관리하는 데 큰 도움을 줍니다.

결론

엑셀의 데이터 유효성 검사와 동적 드롭다운 목록 기능은 데이터를 체계적으로 관리하고 입력 오류를 줄이는 데 유용합니다. OFFSET 함수와 INDIRECT 함수를 활용하면 더 강력한 드롭다운 시스템을 구현할 수 있습니다. 이 방법을 실무에 적용해 보세요!