엑셀 데이터 유효성 검사 목록상자 기본값 설정 방법 :: 실무기초

간단한 설정으로 데이터유효성 검사 목록 상자의 기본값 표시 방법을 단계별로 알아봅니다.

홈페이지 » 엑셀 데이터 유효성 목록 기본값 설정 방법 :: 실무기초

엑셀 데이터 유효성 목록상자 기본값 설정 방법 :: 실무기초

데이터 유효성 목록 기본값 설정 목차 바로가기
영상강의

예제파일 다운로드

데이터 유효성 목록상자 추가하기

데이터 유효성 목록 상자란 지정한 셀에 입력되는 값이 특정 범위(또는 목록) 내 값만 입력 가능하도록 제한하는 기능입니다.

엑셀 데이터 유효성 목록 상자
데이터 유효성 목록 상자 활용 예제

예를들어, [ 상의, 하의, 자켓, 블라우스 ] 라는 4개의 제품 구분으로만 값을 입력해야 할 경우, 데이터 유효성 목록 상자 기능을 사용하면 위 4개의 값 중 하나만 선택해서 입력할 수 있게 됩니다.

  1. 예제파일의 C9 셀을 선택한 뒤, [ 데이터 - 데이터 유효성 검사 - 데이터 유효성 검사 ] 로 이동합니다.

    데이터 데이터 유효성 검사 이동
    데이터 - 데이터 유효성 검사 항목으로 이동합니다.
  2. 제한 대상으로 '목록'을 선택한 뒤, 원본 범위로 H9:H18 범위를 선택합니다. 확인 버튼을 눌러 창을 닫습니다.

    유효성 원본 범위 설정
    제한대상으로 '목록'을 선택한 뒤, 원본 범위를 설정합니다.
  3. 데이터 유효성 검사 목록상자가 적용되었습니다.

    데이터 유효성 검사 목록 완료
    데이터 유효성 검사 목록 상자가 적용되었습니다.
  4. 원본 범위에 새로운 데이터가 추가될 시, 범위가 자동으로 확장되도록 하려면 표기능이나 동적범위를 활용합니다. 동적범위에 대한 자세한 설명은 아래 OFFSET 동적범위 기초 강의를 참고하세요.

데이터 유효성 목록 기본값 설정 #1 (정렬기능 활용)

데이터 유효성 목록 상자의 기본값을 설정할 수 있을까요? 예를들어, 아무런 값도 선택되지 않았을 경우, '----- 값을 선택하세요 ------' 와 같은 안내 문구를 출력하는 것 처럼요.

셀의 정렬기능을 활용하면 데이터 유효성 검사 목록 외에도 주변 셀이 빈칸일 경우에 기본값을 손쉽게 출력할 수 있습니다.

  1. 알파벳 'C'를 클릭하여 C열을 전체선택 한 뒤, 우클릭 - 삽입을 클릭하여 새로운 열을 추가합니다. (단축키 Ctrl + Shift + +)

    새로운 열 삽입
    C열을 전체 선택한 뒤, 우클릭 - 삽입을 클릭하여 새로운 열을 삽입합니다.
  2. C9셀을 선택한 뒤, [ '----- 값을 입력하세요 ----- ] 를 입력합니다. (앞에 작은따옴표(')를 추가하는 것에 주의합니다)

    기본값 입력
    새로 추가된 열의 셀을 클릭한 후, 기본값을 입력합니다.
  3. 키보드 Ctrl 키를 누른채로 D9, D12, D15, ... D27 셀까지 기존 데이터유효성 목록이 설정 된 셀을 모두 선택합니다. 셀을 우클릭하여 '셀 서식'으로 이동합니다.

    범위 선택 후 셀 서식 이동
    기존 데이터유효성 목록이 적용된 셀 선택 후, 셀 서식으로 이동합니다.
  4. '테두리'로 이동한 뒤, 왼쪽 테두리를 '없음'으로 설정합니다. 확인을 눌러 창을 닫습니다.

    데이터유효성 검사 테두리 설정
    왼쪽 테두리를 없음으로 변경합니다.
  5. C9 셀을 선택한 뒤, 우클릭하여 셀서식으로 이동합니다. 이번에는 위쪽/아래쪽/왼쪽 테두리를 추가한 뒤, 확인을 눌러 창을 닫습니다.

    유효성 목록 기본값 셀 테두리
    셀의 위쪽, 아래쪽, 왼쪽 테두리를 설정합니다.
  6. C9셀을 복사합니다. 이후 C9, C12, C15, ... C27 까지 범위를 선택한 뒤, Ctrl + V 로 복사한 값을 붙여넣기 합니다.

    값 복사 후 붙여넣기
    C9셀을 복사한 뒤 다른 남아있는 범위에 붙여넣기 합니다.
  7. C열을 전체선택한 뒤, 홈탭의 맞춤 그룹에서 '들여쓰기' 버튼을 클릭하여 한번 들여쓰기 합니다.

    들여쓰기 설정
    C열을 전체선택 한 뒤 들여쓰기 합니다.
  8. C열이 선택된 상태로 우클릭하여 열 너비로 이동합니다. 열 너비를 0.05로 변경합니다.

    열너비 0.05
    C열의 열너비를 0.05로 최대한 좁게 변경합니다.
  9. 데이터 유효성 목록 기본값 설정이 완료되었습니다.

    엑셀 데이터 유효성 목록 기본값 GIF
    데이터유효성 검사 기본값 설정이 완료되었습니다.

첫번째 방법의 제한사항

셀 정렬기능을 활용한 기본값 표시방법에는 제한사항이 있습니다. 그것은 바로, 유효성 검사가 적용된 셀이 병합되어 있을 경우 기본값이 표시되지 않는다는 것 인데요. 그럴 경우 아래 두번째, 세번째 방법을 이용합니다.

셀병합 시 기본값 표시 문제
유효성검사 목록상자가 적용된 셀이 병합되어 있을 경우 첫번째 방법을 사용할 수 없습니다.

데이터 유효성 목록 기본값 설정 #2 (오류메세지 해제)

  1. 데이터 유효성 검사가 적용된 셀을 선택한 뒤, 데이터 - 데이터유효성 검사 - 데이터 유효성 검사로 이동합니다.

    데이터 데이터 유효성 검사 이동
    데이터 - 데이터유효성 검사 설정으로 이동합니다.
  2. '오류 메시지' 탭으로 이동한 뒤, 유효하지 않은 데이터를 입력하면 오류 메시지 표시 체크박스를 비활성화 합니다. '확인'버튼을 눌러 창을 종료합니다.

    오류메시지 체크박스 비활성화
    오류메시지 탭으로 이동한 뒤, 체크박스를 비활성화 합니다.
  3. 셀에 기본값을 입력합니다.

    유효성 목록 기본값 입력
    기본값을 입력합니다.
  4. 다시 데이터유효성 검사 설정으로 이동한 뒤, 체크박스를 활성화합니다. '확인'버튼을 눌러 종료합니다.

    유효성 목록 체크박스 활성화
    다시 유효성 목록 설정으로 이동한 뒤, 체크박스를 활성화합니다.
  5. 데이터 유효성 목록 기본값 설정이 완료되었습니다. 단, 두번째 방법은 유효성 검사 값을 입력하기 전 최초 1회만 기본값이 출력되며, 한번 값이 선택되면 그 이후에는 기본값이 출력되지 않는다는 단점이 있습니다.

    기본값 설정 완료
    데이터 유효성 목록 기본값 설정이 완료되었습니다.

데이터 유효성 목록 기본값 설정 #3 (매크로 활용)

매크로를 사용하면 병합된 셀에 적용된 데이터 유효성 목록에서도 기본값을 표시할 수 있습니다. 완성된 전체 코드를 제공해드리니 필요한 부분만 적절히 수정하시면 아주 간단하게 데이터 유효성 목록 기본값을 적용할 수 있습니다.

  1. 개발도구 - Visual Basic 을 클릭하거나 단축키 Alt + F11 키를 눌러 매크로 편집기로 이동합니다. 개발도구가 보이지 않을 경우, 아래 개발도구 활성화하는 방법 관련 포스트를 확인하세요.

    개발도구 매크로편집기 이동
    개발도구 - Visual Basic 편집창으로 이동합니다.
  2. 데이터 유효성 목록상자의 기본값을 적용할 시트를 더블클릭하면 시트 모듈이 활성화 됩니다.

    시트 더블클릭 시트모듈
    해당 시트를 더블클릭하면 시트모듈이 활성화됩니다.
  3. 아래 명령문을 복사한 뒤, 시트 모듈안에 붙여넣기 합니다.
    Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim rng As Range
    Dim sRng As String: Dim vRngs As Variant: Dim vRng As Variant
    Dim sMsg As String
     
    Application.EnableEvents = False
     
    ' 데이터 유효성 검사가 활성화된 셀 주소를 쉼표(,) 로 구분하여 입력하세요.
    sRng = "셀1, 셀2, 셀3"
    ' 데이터 유효성 검사 기본값으로 출력 될 메세지를 입력하세요.
    sMsg = "------ 값을 선택하세요 ----- "
     
    vRngs = Split(sRng, ",")
     
    For Each vRng In vRngs
        If Trim(vRng) = Target.Resize(1, 1).Address(0, 0) Then
            On Error GoTo EH:
            If Len(Target.Text) = 0 Then
                On Error Resume Next
                Target.Validation.ShowError = False
                Target.Value = sMsg
                Target.Validation.ShowError = True
                Exit For
            End If
        End If
    Next
     
    EH:
    Application.EnableEvents = True
     
    End Sub
  4. sRng = "셀1, 셀2, 셀3" 을 실제 데이터 유효성 목록이 적용된 셀 주소로 변경합니다. (예제파일 기준 D9, D12, D15, ... D27)
    sRng = "D9, D12, D15, D18, D21, D24, D27"
  5. sMsg = "------ 값을 선택하세요 ----- " 을 실제 출력할 데이터 유효성 목록상자의 기본값으로 변경합니다.
    sMsg = "------ ★★ 값을 선택하세요 ★★ ------"
  6. 이후 Sheet1 로 이동한 뒤 데이터유효성 목록 상자의 값을 지우면 매크로에 입력한 기본값이 표시됩니다.

    엑셀 데이터 유효성 목록 기본값 매크로 완성 GIF
    매크로를 활용한 데이터유효성 목록 기본값 설정이 완료되었습니다.
5 2 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
2 Comments
Inline Feedbacks
View all comments
따스함
따스함
2020년 7월 12일 8:08 오전
게시글평점 :
     

고맙습니다.

혬짱짱
혬짱짱
2020년 8월 17일 10:36 오후
게시글평점 :
     

그냥 볼때는 할수있을것 같은데 막상 해보면 자꾸 까먹네요 ㅎㅎㅎ 숙달될 수 있게 연습도 많이하고 많은 노력이 필요할 것 같아요 감사합니다!!

2
0
여러분의 생각을 댓글로 남겨주세요.x