엑셀 드롭다운 목록 만들기 | 데이터 유효성 검사로 입력 오류 줄이는 방법

배송상태 드롭다운 및 자동 집계

엑셀로 직원 명단이나 고객 관리표, 주문서를 작성하다 보면 같은 항목을 반복해서 입력하는 일이 많습니다.

예를 들어 주문 상태를 입력할 때 어떤 직원은 '배송중', 다른 직원은 '배송 중'이라고 입력할 수 있습니다.

사람이 보기에는 같은 의미지만 엑셀에서는 서로 다른 값으로 처리될 수 있습니다.

이러한 문제를 줄이는 방법이 바로 데이터 유효성 검사를 활용한 드롭다운 목록입니다.

오늘은 엑셀 초보자도 쉽게 따라 할 수 있도록 실제 주문 관리표를 예제로 설명하겠습니다.

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

데이터 유효성 검사는 셀에 입력할 수 있는 값이나 조건을 제한하는 기능입니다.

미리 정해 놓은 항목 중 하나를 선택하도록 설정할 수 있으며, 숫자나 날짜의 입력 범위를 제한할 수도 있습니다.

특히 여러 사람이 함께 사용하는 엑셀 파일에서 유용합니다.

예를 들어 다음과 같은 주문 관리표가 있다고 가정하겠습니다.

주문번호	고객명	주문금액	배송상태
A001	김민수	35,000	배송완료
A002	이영희	48,000	배송중
A003	박철수	26,000	배송준비
A004	최지은	72,000	배송완료
A005	정수진	19,000	배송중

배송상태를 직접 입력하지 않고 목록에서 선택하도록 만들면 입력 방식이 통일됩니다.

2. 드롭다운 목록 만드는 방법

배송상태가 D열에 입력되어 있다고 가정하겠습니다.

① 드롭다운을 적용할 셀 선택

D2부터 D100까지 선택합니다.

② 데이터 메뉴 선택

엑셀 상단 메뉴에서 다음 순서로 이동합니다.

데이터 → 데이터 유효성 검사

③ 허용 조건 설정

설정 창의 '제한 대상' 또는 '허용' 항목에서 '목록'을 선택합니다.

④ 원본 입력

원본 입력란에 다음과 같이 작성합니다.

배송준비,배송중,배송완료,주문취소

⑤ 확인 버튼 클릭

설정이 완료되면 해당 셀에서 드롭다운 목록을 사용할 수 있습니다.

이제 직원이 배송상태를 직접 작성하지 않고 정해진 항목 중 하나를 선택할 수 있습니다.

3. 별도 셀에 목록을 만들어 관리하기

배송상태가 자주 변경된다면 목록을 셀에 따로 만들어 두는 것이 편리합니다.

예를 들어 F열에 다음과 같이 입력합니다.

F열
배송준비
배송중
배송완료
주문취소

F2부터 F5까지 항목을 입력한 뒤 데이터 유효성 검사 원본에 다음 수식을 지정합니다.

=$F$2:$F$5

이렇게 하면 F열의 항목을 수정할 때 드롭다운 목록에도 변경 내용이 반영됩니다.

다만 목록 아래에 새로운 항목을 추가하는 경우에는 원본 범위를 확장해야 합니다.

4. 잘못된 데이터 입력을 막는 방법

드롭다운 목록을 만들었다고 해서 모든 입력 오류가 자동으로 방지되는 것은 아닙니다.

데이터 유효성 검사 창에서 오류 메시지 설정을 확인해야 합니다.

오류 메시지 표시를 활성화하고 스타일을 '중지'로 선택하면 목록에 없는 값을 직접 입력했을 때 오류 메시지가 나타납니다.

예를 들어 '배송종료'라는 항목을 입력하면 다음과 같은 안내 문구를 표시할 수 있습니다.

제목: 입력 오류

메시지: 목록에 있는 배송상태만 선택해 주세요.

단, 데이터를 복사해 붙여넣는 과정에서는 유효성 검사 규칙이 우회되거나 덮어써질 수 있으므로 주의해야 합니다.

5. COUNTIF 함수로 배송상태별 건수 계산하기

드롭다운 목록을 만들었다면 배송상태별 주문 건수도 간단하게 계산할 수 있습니다.

배송완료 건수를 계산하는 수식입니다.

=COUNTIF(D2:D100,"배송완료")

배송중인 주문 건수는 다음과 같이 계산합니다.

=COUNTIF(D2:D100,"배송중")

위의 예제 데이터에서는 배송완료 2건, 배송중 2건, 배송준비 1건입니다.

드롭다운으로 입력값을 통일하면 COUNTIF 함수의 계산 결과도 더 안정적으로 관리할 수 있습니다.

6. 실무에서 활용할 수 있는 사례

드롭다운 목록은 배송 관리 외에도 다양한 업무에서 사용할 수 있습니다.

• 직원 관리: 재직, 휴직, 퇴사
• 고객 상담: 접수, 처리중, 완료
• 주문 관리: 배송준비, 배송중, 배송완료
• 업무 관리: 예정, 진행중, 보류, 완료

특히 항목이 정해져 있고 반복 입력이 필요한 업무에 적합합니다.

7. 드롭다운 목록이 보이지 않을 때

드롭다운 화살표가 나타나지 않는다면 데이터 유효성 검사 설정을 확인해야 합니다.

먼저 해당 셀에 목록 규칙이 적용되어 있는지 확인합니다.

목록 설정에서 '셀 안에 드롭다운' 옵션이 선택되어 있는지도 살펴봅니다.

또한 복사한 셀을 붙여넣으면서 기존 설정이 바뀌지 않았는지 확인하는 것이 좋습니다.

 

엑셀_드롭다운_데이터유효성검사_실습예제.xlsx

0.01MB

마무리

엑셀에서 업무 속도를 높이는 방법은 복잡한 함수를 많이 사용하는 것만이 아닙니다.

반복 입력하는 항목을 드롭다운 목록으로 관리하면 입력 시간을 줄이고 데이터의 일관성을 높일 수 있습니다.

여기에 COUNTIF 함수를 함께 사용하면 상태별 업무 현황도 간편하게 집계할 수 있습니다.

드롭다운 목록으로 입력 오류를 예방하고, COUNTIF로 결과를 확인하는 것이 오늘의 핵심입니다.

직원 명단이나 주문 관리표처럼 반복해서 사용하는 엑셀 파일부터 적용해 보시기 바랍니다.