엑셀 드롭다운 목록에서 사용된 항목 자동으로 제거하는 2가지 방법

엑셀에서 데이터 유효성 검사를 활용하다 보면 드롭다운 목록에서 이미 선택된 항목을 제거해야 하는 상황이 생깁니다. 대표적인 예로, 여러 직원에게 서로 다른 근무 교대 시간을 배정하면서 한 명의 직원이 두 번 이상 배정되지 않도록 해야 하는 경우를 들 수 있습니다. 또는 경기에서 선수들을 각 포지션에 배정할 때, 한 선수는 하나의 포지션만 맡아야 하는 상황도 마찬가지입니다.

이런 경우 드롭다운 목록에서 항목을 한 번 선택하면, 해당 항목이 이후 목록에서 자동으로 사라지도록 만들면 매우 편리합니다. 이번 글에서는 엑셀에서 사용된 항목을 드롭다운 목록에서 자동으로 제거하는 2가지 방법을 소개합니다.

실습 시나리오

조직의 직원 이름 목록이 담긴 워크시트가 있다고 가정해 보겠습니다. 각 직원을 서로 다른 근무 교대에 배정해야 하며, 동일한 직원이 중복 배정되어서는 안 됩니다. 따라서 직원 이름으로 구성된 드롭다운 목록이 필요하고, 누군가 배정되면 그 이름이 목록에서 자동으로 제거되어야 합니다. 아래 이미지는 이번 글에서 다룰 워크시트로, 이미 사용된 항목이 제거되는 드롭다운 목록이 적용된 모습입니다.

방법 1: 보조 열(Helper Columns)을 활용하여 사용된 항목 제거하기

드롭다운 목록에서 사용된 항목을 제거하는 가장 기본적인 방법은 보조 열 2개를 활용하는 것입니다. 단계별로 살펴보겠습니다.

단계 1: 행 번호 열 작성

• 먼저 '행 번호' 열의 첫 번째 셀인 C5에 아래 수식을 입력합니다.

수식 설명:

• IF 함수는 논리 조건 COUNTIF($F$5:$F$14, B5)>=1을 검사합니다.
• COUNTIF 함수는 셀 B5의 값이 절대 참조 범위 $F$5:$F$14에 한 번 이상 나타나는지 확인합니다.
• B5 값이 범위 내에 한 번 이상 존재하면 IF 함수는 빈 문자열("")을 반환합니다.
• 그렇지 않으면 ROW 함수를 통해 B5의 행 번호를 반환합니다.

• Enter 키를 누르면 C5 셀에 B5의 행 번호가 표시됩니다.
• 이후 C5 셀의 채우기 핸들을 아래로 끌어 나머지 셀까지 수식을 복사하면, 모든 직원의 행 번호를 얻을 수 있습니다.

단계 2: 직원 이름 열 작성

• 다음으로 '직원 이름' 열의 첫 번째 셀인 D5에 아래 수식을 입력합니다.

수식 설명:

• IF 함수는 논리 조건 ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14)을 검사합니다.
• COUNT 함수는 절대 참조 범위 C$5:C$14 내 숫자 셀의 개수를 셉니다.
• SMALL 함수는 범위 C$5:C$14에서 k번째로 작은 값을 찾습니다. 여기서 k는 1+ROW(B5)-ROW(B$5)로 결정됩니다.
• INDEX 함수는 SMALL 함수가 찾은 k번째 값을 행 번호(row_num) 인수로 받아 해당 셀의 참조값을 반환합니다.

• Enter 키를 누르고 채우기 핸들로 수식을 아래까지 복사하면, 아직 배정되지 않은 모든 직원 이름이 표시됩니다.

단계 3: 이름 정의 만들기

• 리본 메뉴에서 수식(Formulas) 탭 → 이름 정의(Define Name)를 클릭합니다.
• '새 이름 편집' 창이 열리면 이름(Name) 입력란에 Employee를 입력합니다.
• 참조 대상(Refers to) 입력란에 아래 수식을 입력합니다.

수식 설명:

• Helper는 현재 작업 중인 워크시트의 이름입니다.
• COUNTA 함수는 절대 참조 범위 $D$5:$D$14에서 값이 있는 모든 셀의 개수를 셉니다.
• COUNTBLANK 함수는 같은 범위에서 비어 있는 셀의 개수를 셉니다. 두 값의 차이를 통해 실제 데이터가 있는 셀 수만큼만 목록 범위가 지정됩니다.

• 확인(OK)을 클릭하여 이름 정의를 완료합니다.

단계 4: 데이터 유효성 검사로 드롭다운 목록 만들기

• 드롭다운 목록을 만들 'Drop-Down' 열의 모든 셀을 선택합니다.
• 데이터(Data) 탭 → 데이터 유효성 검사 드롭다운을 클릭한 뒤, 데이터 유효성 검사를 선택합니다.
• 대화상자가 열리면 제한 대상(Allow) 드롭다운에서 목록(List)을 선택합니다.
• 원본(Source) 입력란에 =Employee를 입력하고 확인을 클릭합니다.

• 이제 Drop-Down 열의 각 셀에 드롭다운 목록이 생성된 것을 볼 수 있습니다.
• F5 셀의 드롭다운에서 Gus Fring을 선택해 보겠습니다.

• 두 번째 드롭다운을 클릭하면 Gus Fring이 목록에 포함되어 있지 않습니다. 이미 사용된 항목이므로 이후 목록에서 자동으로 제거된 것입니다.

• 같은 방식으로 다른 드롭다운에서도 이름을 선택하면, 선택된 항목들이 뒤따르는 목록에서 계속 제거되는 것을 확인할 수 있습니다.

방법 2: FILTER와 COUNTIF 함수 결합하기 (Microsoft 365 전용)

Microsoft Office 365를 사용 중이라면, Excel 365 전용 함수인 FILTER를 활용하는 것이 가장 간편합니다. 아래 단계를 따라 해 보세요.

단계 1: FILTER 수식 입력

• 첫 번째 셀인 C5에 아래 수식을 입력합니다.

수식 설명:

• FILTER 함수는 조건 COUNTIF(E5:E14, B5:B14)=0을 기준으로 범위 B5:B14를 필터링합니다.
• COUNTIF 함수는 B5:B14 범위의 각 값이 E5:E14 범위에 존재하는지 여부를 판별합니다. 즉, 아직 선택되지 않은 직원만 결과로 남게 됩니다.

• Enter 키를 누르면 아직 배정되지 않은 모든 직원 이름이 한 번에 표시됩니다.

단계 2: 데이터 유효성 검사 설정

• Drop-Down 열의 모든 셀을 선택한 후, 데이터 탭 → 데이터 유효성 검사를 실행합니다.
• 제한 대상에서 목록을 선택합니다.
• 원본 입력란에 $C$5:$C$14를 입력합니다. 또는 =$C$5#(스필 범위 참조)를 입력해도 됩니다.
• 확인을 클릭합니다.

• 각 셀에 드롭다운 목록이 생성되었는지 확인한 뒤, F5 셀에서 Stuart Bloom을 선택해 봅니다.

• 두 번째 드롭다운을 열면 Stuart Bloom이 더 이상 목록에 없습니다. 사용된 항목이 자동으로 제거된 것입니다.

• 이후 다른 드롭다운에서도 항목을 선택할 때마다 선택된 이름이 계속 목록에서 빠져나가는 것을 확인할 수 있습니다.

알아두면 좋은 팁

🎯 FILTER 함수는 현재 Excel 365(Microsoft 365)에서만 사용 가능한 전용 함수입니다. PC에 Excel 365가 설치되어 있지 않다면 이 방법은 작동하지 않으므로, 방법 1의 보조 열 방식을 사용하세요.

🎯 고유값(unique)만 포함된 드롭다운 목록을 만드는 추가 방법이 궁금하다면 관련 글도 함께 참고해 보시기 바랍니다.

마무리

이번 글에서는 엑셀 드롭다운 목록에서 사용된 항목을 자동으로 제거하는 방법을 두 가지 살펴보았습니다. 오래된 버전의 Excel이라면 OFFSET과 SMALL 함수를 활용한 보조 열 방식을, Excel 365 사용자라면 FILTER 함수를 활용한 방식을 추천합니다. 이제 중복 배정 걱정 없이 깔끔하게 드롭다운 목록을 운영할 수 있을 것입니다. 궁금한 점이나 추가로 알고 싶은 내용이 있다면 댓글로 남겨 주세요!

함께 읽으면 좋은 글

• 엑셀에서 검색 가능한 드롭다운 목록 만들기 (2가지 방법)
• 엑셀 드롭다운 목록에 색상 넣는 방법 (2가지 방법)
• 엑셀 드롭다운 목록이 작동하지 않을 때 (8가지 문제와 해결책)
• 엑셀에서 범위로 목록 만들기 (3가지 방법)
• 엑셀 드롭다운 목록 자동 업데이트하기 (3가지 방법)