중복 값 제거와 데이터 유효성 검사: 오타 없는 깔끔한 입력창 만들기

지난 편들에서 VLOOKUP이나 SUMIF 같은 강력한 함수들을 배웠습니다. 그런데 수식을 완벽하게 짰는데도 결괏값이 이상하게 나올 때가 있습니다. 원본 데이터를 자세히 들여다보면 원인은 십중팔구 '오타'에 있습니다.

여러 사람이 함께 쓰는 취합 파일의 경우, 똑같은 '영업1팀'을 두고 누군가는 "영업 1팀"(띄어쓰기), 누군가는 "영업1부"라고 제멋대로 입력합니다. 엑셀 입장에서는 이 세 가지를 완전히 다른 부서로 인식하기 때문에 함수가 제대로 작동할 리 없습니다. 취합된 데이터를 일일이 눈으로 확인하며 수정하는 것은 엄청난 시간 낭비입니다. 오늘은 이런 참사를 막기 위해, 애초에 오타를 낼 수 없도록 입력창을 통제하는 마법의 기능 '데이터 유효성 검사'와 '중복 값 제거'에 대해 알아보겠습니다.

1. 사전 준비: '중복된 항목 제거'로 깔끔한 기준표 만들기

드롭다운(선택) 목록을 만들기 위해서는 먼저 기준이 되는 깔끔한 목록(마스터 데이터)이 필요합니다. 수천 줄의 데이터에 섞여 있는 부서명 중, 오직 '고유한 부서명'만 딱 한 개씩 남기고 싶을 때 사용하는 기능입니다.

  1. 부서명이 적힌 열 전체를 복사해서, 빈 공간(다른 시트나 옆 칸)에 그대로 값 붙여넣기를 합니다.

  2. 복사한 데이터를 드래그한 상태에서 상단 메뉴의 [데이터] - [중복된 항목 제거]를 클릭합니다.

  3. 확인을 누르면 "00개의 중복된 값이 제거되고 5개의 고유한 값만 유지됩니다"라는 알림과 함께, 엑셀이 알아서 겹치는 이름들을 싹 지우고 고유한 부서명 5개만 깔끔하게 남겨줍니다.

이 5개의 고유한 부서명이 앞으로 우리가 만들 '드롭다운 목록'의 훌륭한 재료가 됩니다.

2. 엑셀의 퀄리티를 높이는 '데이터 유효성 검사(목록)'

이제 사람들이 부서명을 직접 타이핑하지 못하게 하고, 마우스로 클릭해서 고르도록 '드롭다운 목록(콤보박스)'을 만들어 볼 차례입니다.

  1. 부서명을 입력받아야 할 빈 셀들(예: C2:C100)을 쭉 드래그하여 선택합니다.

  2. 상단 메뉴의 [데이터] - [데이터 유효성 검사]를 클릭합니다.

  3. 설정 탭에서 '제한 대상'을 '모든 값'에서 '목록'으로 변경합니다.

  4. 아래에 나타난 '원본' 칸을 클릭하고, 앞서 1번 단계에서 만들어둔 '고유한 부서명 5개'의 범위를 마우스로 드래그해 줍니다. (또는 원본 칸에 직접 영업1팀,영업2팀,마케팅팀처럼 쉼표로 구분해서 타이핑해도 됩니다.)

확인을 누르고 셀을 클릭해 보세요. 셀 옆에 작은 아래쪽 화살표(▼)가 생겼을 것입니다. 이제 이 칸에는 화살표를 눌러 목록에 있는 부서명만 선택할 수 있습니다.

3. 오타의 원천 차단: 오류 메시지 띄우기

데이터 유효성 검사가 정말 무서운 이유는 통제력에 있습니다. 만약 누군가 화살표를 누르지 않고 고집스럽게 키보드로 "영업 1팀"이라고 띄어쓰기를 섞어 타이핑한 뒤 엔터를 치면 어떻게 될까요?

엑셀은 즉시 삐~ 소리와 함께 "이 값은 이 셀에 정의된 데이터 유효성 검사 제한에 부합하지 않습니다."라는 무시무시한 경고창을 띄우고 입력을 거부합니다. 사용자가 목록에 있는 정확한 이름을 선택하거나 똑같이 타이핑할 때까지 다음 칸으로 넘어갈 수 없습니다.

이 기능 하나만 설정해 두면, 여러분이 취합 파일을 배포한 뒤 수십 명의 오타를 수정하느라 야근할 일이 영원히 사라지게 됩니다.

4. 실무 응용: 날짜나 숫자 길이 제한하기

데이터 유효성 검사는 '목록' 외에도 다양하게 활용할 수 있습니다.

  • 날짜 제한: 제한 대상을 '날짜'로 두고 해당 연도의 1월 1일부터 12월 31일까지만 설정해 두면, 누군가 실수로 2024년을 2042년으로 잘못 입력하는 대참사를 막을 수 있습니다.

  • 텍스트 길이 제한: 제한 대상을 '텍스트 길이'로 두고 '해당 범위(13자)'로 설정해 두면, 휴대폰 번호(010-0000-0000)를 입력할 때 숫자를 빼먹거나 더 적는 실수를 완벽하게 방지할 수 있습니다.

양식의 디테일이 칼퇴를 결정합니다

"이 정도 오타는 내가 그냥 고치고 말지"라고 생각하며 무심코 넘어갔던 데이터들이 쌓이면, 결국 연말 결산이나 대규모 피벗 테이블을 돌릴 때 여러분의 발목을 뼈아프게 잡게 됩니다. 문서를 처음 설계할 때 데이터 유효성 검사를 걸어두는 단 1분의 투자가, 미래의 1시간을 아껴주는 가장 확실한 엑셀 재테크입니다.

핵심 요약

  1. 중복된 항목 제거: 수많은 데이터 중 겹치는 것을 지우고 고유한 항목(마스터 데이터)만 1초 만에 뽑아낼 수 있습니다.

  2. 데이터 유효성 검사 (목록): 셀에 화살표(드롭다운)를 만들어 사용자가 지정된 목록의 값만 마우스로 선택하게 유도할 수 있습니다.

  3. 오류 차단: 목록에 없는 값, 잘못된 날짜, 규격에 맞지 않는 텍스트 길이를 입력하면 엑셀이 경고창을 띄워 오타를 원천 차단합니다.

다음 편 예고

데이터를 오타 없이 깔끔하게 모으는 법까지 배우셨습니다. 이제 이렇게 모인 완벽한 데이터를 가지고 본격적인 '분석'을 시작할 차례입니다. 다음 11편에서는 수천 줄의 데이터를 단 5분 만에 월별, 부서별 보고서로 요약해 주는 엑셀 데이터 분석의 꽃, '피벗 테이블 1편'을 시작해 보겠습니다.

독자님을 위한 질문

회사에서 다른 사람들과 엑셀 취합 파일을 공유할 때, 사람들이 가장 많이 틀리게 적어서 여러분을 괴롭혔던 항목(예: 연락처 양식, 부서명 띄어쓰기 등)은 무엇이었나요?

댓글

댓글 쓰기

이 블로그의 인기 게시물

식재료 보관의 신: 대파부터 고기까지, 오래 보관하는 소분법

지난 2편에서 우리는 마트의 유혹을 뿌리치고 꼭 필요한 만큼만 장을 보는 데 성공했습니다. 자, 이제 양손 무겁게 집에 돌아왔습니다. 여러분은 지금 사 온 식재료를 어떻게 하시나요? 아마 열에 아홉은 장바구니에 있던 비닐봉지 그대로 냉장고 빈칸에 쑤셔 넣고 피곤한 몸을 뉘이실 겁니다. 하지만 이 사소한 행동이 식비 낭비의 가장 큰 주범이라는 사실을 아시나요? 장을 보고 온 직후의 30분, 귀찮음을 이겨내는 이 짧은 시간이 식재료의 수명을 3일에서 3주로 늘려주는 골든타임입니다. 오늘은 1인 가구가 가장 자주 버리게 되는 대표 식재료들의 수명을 극적으로 늘려주는 실전 소분법을 공유합니다. 1. 마트 포장 용기는 세균의 온상입니다 가장 먼저 해야 할 일은 마트에서 담아준 포장재를 모두 벗겨내는 것입니다. 특히 고기가 담겨 있는 검은색 스티로폼 랩 포장은 겉보기엔 깔끔해 보이지만, 유통 과정에서 수많은 사람의 손을 거치며 세균에 노출되었을 확률이 높습니다. 게다가 공기가 드나들기 쉬워 식재료의 수분을 빼앗고 산화를 촉진합니다. 집에 도착하자마자 모든 식재료는 비닐과 스티로폼에서 탈출시켜야 합니다. 이때 필요한 무기는 딱 두 가지입니다. 공기를 차단해 줄 '지퍼백(또는 밀폐용기)'과 수분을 조절해 줄 '키친타월'입니다. 2. 1인 가구의 웬수, 대파와 마늘 완벽 보관법 한국인 밥상에 빠지지 않지만 1인 가구가 다 먹기엔 항상 양이 많은 대파와 마늘. 이 녀석들의 최대 적은 바로 '수분'입니다. 대파: 흙을 깨끗이 씻어낸 후, 물기가 완벽하게 마를 때까지 건조하는 것이 핵심입니다. (물기가 남으면 금방 짓무릅니다.) 그 후 용도에 맞게 송송 썰거나 길게 썰어 밀폐용기에 담습니다. 이때 바닥과 뚜껑 쪽에 키친타월을 한 장씩 깔아두면 타월이 습기를 흡수해 한 달 가까이 싱싱하게 보관할 수 있습니다. 오래 두고 먹을 절반은 지퍼백에 넓게 펴서 냉동실로 보냅니다. 깐 마늘: 밀폐용기 바닥에 설탕을 1cm 두께로 깔고 키친타월을 덮은 ...
자세한 내용 보기

장보기의 기술: 버려지는 식재료를 줄이는 스마트한 소량 구매 전략

1편에서 우리 집 냉장고를 비우고 식습관을 진단해보셨나요? 아마 유통기한이 지나 쓰레기통으로 향하는 식재료들을 보며 적지 않은 충격을 받으셨을 겁니다. 식비 절약의 핵심은 단순히 싼 재료를 사는 것이 아니라, '내가 산 식재료를 100% 소비하는 것'에 있습니다. 많은 1인 가구가 장보기 단계에서부터 실패를 경험합니다. 마트의 할인 유혹에 넘어가 '1+1' 상품을 집어 들거나, 무심코 큰 용량의 재료를 사서 결국 반 이상 버리게 되는 것이죠. 오늘은 1인 가구의 장보기 실패를 원천 차단하는 스마트한 소량 구매 전략을 정리해 드립니다. 1. '1+1'은 1인 가구에게 가장 비싼 상품입니다 마트에 가면 '2개 사면 30% 할인'이나 '1+1' 같은 문구가 우리를 유혹합니다. 1인 가구의 가장 큰 실수는 이 할인율에 속아 필요 이상의 양을 구매하는 것입니다. 생각해보세요. 대파 한 단을 할인받아 2,000원에 샀다고 칩시다. 하지만 혼자 사는 사람이 일주일에 소비할 수 있는 대파의 양은 정해져 있습니다. 다 못 먹고 절반을 버린다면, 결과적으로 나는 2,000원을 주고 1,000원어치 대파만 먹은 셈입니다. 할인받은 금액보다 버리는 식재료의 값이 더 크다면, 그것은 절대 경제적인 장보기가 아닙니다. 1인 가구에게 진정한 할인은 '내가 기한 내에 다 먹을 수 있는 적정량'을 사는 것입니다. 2. 장보기 전 '3-Day 리스트' 만들기 매주 한 번씩 몰아서 장을 보는 것이 효율적이라고 생각하시나요? 하지만 경험상 1인 가구에게 일주일 치 식단은 너무 깁니다. 갑작스러운 야근, 친구와의 약속, 배달 음식의 유혹이 변수로 작용하기 때문입니다. 이제부터는 '일주일'이 아닌 '3일 치' 리스트를 작성해보세요. 3일은 내 냉장고의 식재료가 신선하게 유지되는 기간이자, 내 식사 계획을 현실적으로 통제할 수 있는 최적의 시간 단위입니다. 3일 뒤에 무엇을...
자세한 내용 보기

계좌만 파면 끝? 방치된 IRP/ISA에 생기를 불어넣을 실전 상품 매수 가이드

연말정산 혜택을 위해, 혹은 비과세라는 말에 이끌려 큰맘 먹고 IRP와 ISA 계좌를 개설하셨나요? 매월 꼬박꼬박 자동이체까지 걸어두었다면 노후 준비의 절반은 해내신 셈입니다. 하지만 여기서 안심하기엔 이릅니다. 과거의 제가 그랬듯, 많은 직장인들이 계좌에 돈을 입금한 것만으로 모든 투자가 끝났다고 착각합니다. 1년 뒤 수익률을 확인해 보려 앱을 열었을 때, 수익률이 0%대 머물러 있어 당황했던 기억이 생생합니다. 그 이유는 제가 입금한 돈이 어떤 금융 상품도 사지 않은 채 '현금성 대기 자금(예수금)' 상태로 덩그러니 방치되어 있었기 때문입니다. ISA와 IRP는 돈을 불려주는 요술 램프가 아니라, 금융 상품을 담는 '빈 장바구니'일 뿐입니다. 오늘은 빈 장바구니에 어떤 물건을 담아야 할지 막막한 4050 세대를 위해, 복잡한 주식 공부 없이도 안전하게 계좌에 생기를 불어넣는 실전 상품 매수 가이드를 정리해 드립니다. 1. 개별 주식의 유혹을 버려라 "삼성전자 사면 되나요?", "요즘 테슬라가 좋다던데 ISA로 살 수 있나요?" 계좌를 막 개설한 분들이 가장 많이 하는 질문입니다. 결론부터 말씀드리면, 우리의 피 같은 노후 자금은 개별 기업의 흥망성쇠에 운명을 맡겨서는 안 됩니다. 특히 4050 세대는 투자 실패 시 이를 만회할 시간적 여유가 부족합니다. 한 기업의 악재로 자산이 반토막 나는 리스크를 피하려면, 수십에서 수백 개의 우량 기업을 하나의 바구니에 담아 파는 상품을 고르는 것이 정답입니다. 고민할 것 없이 아래 두 가지 선택지만 기억하시면 됩니다. 2. 귀찮은 게 싫다면: TDF (Target Date Fund) 투자 공부할 시간도 없고, 매번 앱을 들여다보며 스트레스받기 싫은 분들에게 최적화된 상품이 바로 TDF입니다. TDF는 내 은퇴 예상 연도(Target Date)에 맞춰 전문가가 알아서 주식과 채권의 비중을 조절해 주는 '자율주행 펀드'입니다. 상품명 뒤에 ...
자세한 내용 보기