직장인 엑셀 필수: 오타 0% 보장! '드롭다운 목록(데이터 유효성 검사)' 만들고 VLOOKUP과 연동하기
안녕하세요! 여러분의 엑셀 작업 시간을 반으로 줄여드리는 실무 노하우를 전해드립니다.
부서원들이 다 함께 취합하는 엑셀 파일을 열어보았을 때, 똑같은 '운영팀'을 누구는 '운영 팀', 누구는 '운영', 심지어 오타를 내서 '우녕팀'이라고 적어놓은 것을 본 적 있으신가요? 이렇게 데이터 형식이 중구난방이면 앞서 배운 SUMIFS나 VLOOKUP 함수가 전혀 작동하지 않게 됩니다.
오늘은 다른 사람이 엑셀 파일에 엉뚱한 값을 입력하지 못하도록 원천 차단하고, 내가 정해놓은 목록 안에서만 마우스로 선택하게 만드는 마법의 기능, '데이터 유효성 검사(드롭다운 목록)'에 대해 깊이 있게 파헤쳐 보겠습니다.
1. 데이터 유효성 검사란 무엇일까?
'유효성 검사'라는 말이 조금 어렵게 들릴 수 있지만, 쉽게 말해 "이 셀에는 내가 허락한 데이터만 입력할 수 있어!"라고 엑셀에 규칙을 걸어두는 것입니다.
그중에서도 가장 많이 쓰이는 것이 바로 클릭하면 아래로 메뉴가 펼쳐지는 '드롭다운 목록(목록 상자)'입니다. 회원가입 창에서 이메일 주소 뒤에 '@naver.com', '@gmail.com'을 클릭해서 고르는 것과 똑같은 원리입니다.
2. 기본 드롭다운 목록 만들기 (클릭 3번 완성)
가장 기초적인 부서명 선택 목록을 만들어 보겠습니다.
- 셀 선택: 드롭다운 목록을 만들고 싶은 빈 셀(또는 범위)을 마우스로 드래그합니다.
- 메뉴 진입: 엑셀 상단 메뉴에서 [데이터] - [데이터 유효성 검사]를 클릭합니다.
- 조건 설정: 팝업창이 뜨면 '제한 대상'을 '모든 값'에서 [목록]으로 변경합니다.
- 원본 입력: '원본' 칸에 내가 목록으로 띄우고 싶은 단어들을 쉼표(,)로 구분하여 직접 적어줍니다.
* 예시 입력:영업팀,기획팀,마케팅팀,재무팀 - [확인]을 누르면 끝! 이제 해당 셀을 클릭하면 옆에 작은 화살표(▼)가 생기고, 클릭하면 방금 적은 부서명만 선택할 수 있습니다.
3. 실무 응용: 표를 참조하여 드롭다운 만들기 (데이터가 많을 때)
만약 상품명이 100개가 넘는다면 쉼표로 일일이 적을 수 없겠죠? 이럴 때는 다른 시트에 정리해 둔 표의 범위를 그대로 끌어와서 목록으로 만들 수 있습니다.
[상품 마스터 데이터 시트]
| A열 (판매 중인 상품 목록) |
|---|
| 프리미엄 무선 마우스 |
| 저소음 기계식 키보드 |
| 알루미늄 노트북 거치대 |
| 고속 충전 C타입 케이블 |
[세팅 방법]
- 아까와 똑같이 [데이터 유효성 검사] - [목록]까지 들어갑니다.
- '원본' 칸을 클릭한 뒤, 글자를 적는 대신 마우스로 마스터 데이터 시트에 있는 상품명 범위(A2:A5)를 쭉 드래그해 줍니다.
- 원본 칸에
=마스터!$A$2:$A$5처럼 수식이 자동으로 들어가면 [확인]을 누릅니다.
(💡 꿀팁: 나중에 마스터 시트에 새 상품을 추가하면 드롭다운 목록에도 자동으로 반영됩니다!)
4. 엑셀 실무 최강 콤보: '드롭다운' + 'VLOOKUP' 연동하기
이제 여러분은 엑셀 고수의 길에 접어들었습니다. 드롭다운으로 값을 '선택'하면, 그 값에 맞는 단가를 VLOOKUP이 알아서 '불러오게' 만들어 보겠습니다.
[완성할 화면: 자동 견적서]
| 상품명 (드롭다운으로 선택) | 단가 (VLOOKUP 자동 입력) |
|---|---|
| [▼클릭하여 키보드 선택] | (선택하는 순간 자동으로 89,000원 입력됨) |
[수식 세팅 방법]
- '상품명' 셀에는 앞서 배운 대로 유효성 검사를 통해 드롭다운 목록을 걸어둡니다.
- '단가' 셀에는 지난번에 완벽하게 마스터한 VLOOKUP 공식을 적어줍니다.
=VLOOKUP(A2, '상품마스터'!$A$2:$B$100, 2, 0)
이제 상품명 드롭다운에서 다른 상품을 클릭할 때마다, 옆에 있는 단가가 그에 맞춰 1초 만에 휙휙 바뀌는 마법 같은 '자동화 서식'이 완성됩니다.
5. 자주 묻는 질문 (Troubleshooting)
Q1. 누군가 드롭다운 목록에 없는 글자를 강제로 타이핑하면 어떻게 되나요?
A. 엑셀이 곧바로 에러 경고창을 띄우며 입력을 튕겨냅니다! 이게 바로 유효성 검사를 쓰는 가장 큰 이유입니다. 완벽하게 오타와 오류 데이터를 막아줍니다.
Q2. 만들어둔 드롭다운 목록을 싹 지우고 싶어요!
A. 지우고 싶은 셀을 드래그한 뒤 다시 [데이터 유효성 검사] 메뉴로 들어갑니다. 팝업창 왼쪽 아래에 있는 [모두 지우기] 버튼을 누르고 확인을 클릭하면 깔끔하게 일반 셀로 돌아옵니다.
6. 마무리
오늘 배운 데이터 유효성 검사는 다른 사람과 파일을 공유해야 할 때 내 데이터를 안전하게 지켜주는 강력한 방패입니다. 여기에 VLOOKUP까지 결합하면 그 어떤 자동화 프로그램 부럽지 않은 실무용 엑셀 폼을 만드실 수 있습니다.
지금 바로 회사에서 자주 쓰는 기안서나 발주서 양식에 드롭다운을 적용해 보세요! 팀원들의 감탄을 자아낼 수 있을 것입니다.
댓글
댓글 쓰기