데이터 입력 오류를 원천 차단하는 '데이터 유효성 검사' 목록 만들기
엑셀 드롭다운 메뉴 만들기, 입력 오류 제한, 셀 선택 목록, 화살표 버튼 생성, 데이터 정제 예방
회사에서 여러 사람이 공동으로 사용하는 엑셀 시트를 운영하거나, 가계부에 대분류 항목을 입력하다 보면 꼭 오타나 예외적인 입력 때문에 골머리를 앓게 됩니다. 예를 들어 '식비'라고 통일해서 적어야 나중에 합계를 구할 수 있는데, 누군가는 '식사비', '점심값', 혹은 실수로 '식비 '처럼 뒤에 공백을 넣어 입력하는 식입니다. 이렇게 데이터가 제각각으로 입력되면 지난 편에서 배웠던 정렬, 필터, 함수 등이 제대로 작동하지 않아 결국 데이터를 하나하나 수정해야 하는 중노동이 발생합니다.
저 역시 팀원들에게 양식을 배포해 취합할 때, 분명히 지정된 항목만 적어달라고 공지했음에도 불구하고 각자만의 방식으로 오타를 잔뜩 적어 보내어 밤새 데이터를 수정했던 아픈 기억이 있습니다. 이때 필요한 것이 바로 사용자가 엉뚱한 값을 입력하지 못하도록 셀에 울타리를 쳐주는 '데이터 유효성 검사(Data Validation)' 기능입니다. 셀을 클릭하면 아래로 선택지가 펼쳐지는 '드롭다운 목록(화살표 버튼)'을 만들어, 오타를 원천 차단하는 방법을 알아보겠습니다.
1. 데이터 유효성 검사의 원리: 엑셀에 규칙 정하기
'데이터 유효성 검사'는 말 그대로 "이 셀에는 내가 허락한 규칙에 맞는 데이터만 입력할 수 있다"라고 제한을 거는 보안 장치입니다. 규칙에 맞지 않는 텍스트를 억지로 타이핑하고 엔터를 누르면, 엑셀이 경고창을 띄우며 입력을 강제로 거부합니다.
실무에서 가장 많이 쓰이는 형태는 목록(List) 규칙입니다. 사용자가 직접 글자를 타이핑하는 대신 마우스 클릭 한 번으로 정해진 메뉴 중 하나를 고르게 유도하는 방식으로, 데이터의 일관성을 완벽하게 유지할 수 있습니다.
2. 30초 만에 뚝딱: 셀에 드롭다운(목록) 화살표 만드는 법
가계부의 '대분류' 열에 [식비, 교통비, 주거비, 문화비]만 입력할 수 있도록 드롭다운 목록을 직접 만들어보겠습니다.
규칙을 적용할 셀(또는 열 전체)을 마우스로 쭉 드래그하여 선택합니다.
상단 리본 메뉴에서 [데이터] 탭 ➔ [데이터 도구] 그룹 ➔ [데이터 유효성 검사] 아이콘을 클릭합니다.
설정 창이 뜨면 [설정] 탭의 제한 대상 기본값인 '모든 값'을 클릭하여 [목록]으로 변경합니다.
아래쪽에 [원본]이라는 입력 칸이 활성화됩니다. 이곳에 사용자가 선택할 항목들을 쉼표(,)로 구별하여 직접 타이핑합니다.
예시 입력:
식비,교통비,주거비,문화비(쉼표 뒤에 띄어쓰기를 하지 않는 것이 좋습니다.)
[확인]을 누르면 설정이 완료됩니다.
이제 지정한 셀을 클릭해 보면 셀 오른쪽 끝에 작은 화살표(▼) 단추가 생깁니다. 이 버튼을 누르면 내가 입력한 4가지 항목이 목록으로 예쁘게 펼쳐지며, 마우스로 콕 집어 선택만 하면 오타 없이 정확하게 입력됩니다. 만약 이 칸에 '간식비'라고 강제로 타이핑하고 엔터를 치면 에러 메시지가 뜨며 입력이 차단됩니다.
3. 한 단계 더 깔끔하게: 별도의 목록 범위를 연결하기
선택해야 할 항목이 4~5개라면 쉼표로 직접 적는 것이 편하지만, 만약 '전국 시/도 명단'이나 '회사 거래처 50개 목록'처럼 선택지가 너무 많을 때는 원본 칸에 일일이 적는 것이 불가능합니다. 이때는 시트 한구석에 목록을 미리 적어두고 주소로 연결하면 깔끔합니다.
시트의 안 보이는 곳(예: M열 등)이나 별도의 기준 정보 시트에 원하는 항목 리스트를 세로로 쭉 적어둡니다.
똑같이 데이터 유효성 검사 창을 열고 제한 대상을 [목록]으로 선택합니다.
[원본] 칸을 마우스로 한 번 클릭한 뒤, 아까 시트에 세로로 적어둔 데이터 영역을 마우스로 쭉 드래그하여 범위 주소(예:
=$M$2:$M$15)가 들어가게 만듭니다.[확인]을 누르면 해당 범위의 글자들이 드롭다운 메뉴로 완벽하게 연동됩니다. 이 방식은 나중에 항목이 추가되거나 이름이 바뀌었을 때, 원본 데이터만 수정하면 드롭다운 메뉴에도 자동으로 반영되므로 대량의 데이터를 관리할 때 매우 편리합니다.
[핵심 요약 3줄]
데이터 유효성 검사는 지정된 규칙 외의 데이터가 셀에 입력되는 것을 막아 오타와 시스템 수식 깨짐을 원천 차단하는 기능이다.
[데이터] 탭의 [데이터 유효성 검사] 메뉴에서 제한 대상을 '목록'으로 바꾸고 원본 칸에 항목을 쉼표(,)로 적으면 간단한 드롭다운 메뉴가 만들어진다.
선택지가 많을 때는 시트에 목록을 먼저 작성한 뒤 [원본] 칸에 해당 범위를 드래그하여 연결하는 방식을 사용하는 것이 유지 관리에 효율적이다.
댓글
댓글 쓰기