드롭다운 목록에서 빈칸 대신 첫 번째 항목을 표시하려면 어떻게 해야 하나요?

Excel 워크시트의 드롭다운 목록은 데이터 입력을 간소화하고 표준화하는 데 유용한 기능입니다. 사용자는 값을 개별적으로 입력하는 대신 미리 정의된 옵션에서 선택할 수 있습니다. 그러나 드롭다운 셀을 클릭했을 때 처음에 빈칸이 아닌 실제 데이터의 첫 항목이 표시되지 않는 경우가 발생할 수 있습니다. 이 문제는 소스 데이터 목록이 편집되고 끝에 빈 행이 남아 있거나 마지막 근처의 항목이 삭제되었을 때, 데이터 유효성 검사가 목록 상단에 의도하지 않은 빈 슬롯을 포함하게 되면서 종종 발생합니다. 특히 긴 목록에서 빈 항목을 지나쳐 항상 첫 번째 유효 항목으로 돌아가야 하는 것은 비효율적이고 짜증스러울 수 있습니다.

이 문제를 해결하면 사용자 편의성을 높일 뿐만 아니라 실수로 빈 값을 선택하는 것을 방지하여 후속 데이터 처리 또는 보고 작업에 영향을 미치는 것을 방지할 수 있습니다. 이 문서에서는 드롭다운 목록의 첫 번째 항목이 항상 상단에 표시되도록 하여 이러한 불필요한 빈칸을 제거하는 실용적인 방법을 배우게 됩니다.

데이터 유효성 검사 기능을 사용하여 빈칸 대신 드롭다운 목록의 첫 항목을 표시하기

VBA 코드를 사용하여 빈칸 대신 드롭다운 목록의 첫 항목을 자동으로 표시하기

Excel 테이블을 데이터 소스로 사용하기

데이터 유효성 검사 기능을 사용하여 빈칸 대신 드롭다운 목록의 첫 항목을 표시하기

드롭다운 목록 상단에 빈 항목이 나타나는 것을 방지하는 효과적인 방법 중 하나는 공식을 사용하여 데이터 유효성 검사를 설정하여 동적으로 올바른 범위를 결정하는 것입니다. 이 방법은 소스 목록의 끝에 있는 데이터를 삭제해 생긴 빈 행과 관계없이 소스 목록에서 채워진 셀만 포함되도록 합니다. 이 솔루션은 특히 소스 목록을 자주 수정하거나 매크로 없이 공식 기반 조정을 원하는 사용자에게 적합합니다.

1. 드롭다운 목록을 만들고자 하는 셀을 선택하세요. 그런 다음 Excel 리본 메뉴로 가서 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사를 클릭합니다. 아래와 같이 데이터 유효성 검사 대화 상자가 열릴 것입니다:

2. 데이터 유효성 검사 대화 상자의 설정 탭에서 '허용'을 '목록'으로 설정합니다. '소스' 상자에 다음 공식을 입력하여 실제 데이터를 포함하는 범위만 동적으로 참조하도록 합니다:

=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A)-1,1)

참고: 이 공식에서 Sheet3은 소스 데이터가 있는 시트를 의미하며, A1은 목록의 시작 셀입니다. 특정 워크시트 레이아웃에 따라 필요에 맞게 이를 조정하세요. COUNTA의 사용은 A1부터 시작하여 빈 셀이 아닌 것들만 포함되도록 합니다. 소스 목록 내부에 의도적인 빈 행이 포함된 경우(끝에만 있는 경우는 제외), 이 방법은 그들을 완전히 배제하지 않을 수 있으므로, 최상의 결과를 위해 소스 목록을 연속적으로 유지하세요.

3. 확인을 클릭하여 설정을 적용하세요. 이제 구성한 드롭다운 목록 셀을 클릭하면 목록은 첫 번째 실제 데이터 항목이 상단에 오도록 표시됩니다. 소스 데이터가 변경되어도, 범위가 A열의 모든 항목을 덮고 주 데이터 블록 내에 빈 셀이 없는 한 이는 사실입니다. 아래 결과를 참조하세요:

팁: 나중에 소스 목록을 확장하거나 축소해야 할 경우 데이터 유효성 검사 설정을 업데이트할 필요가 없습니다. 범위의 시작에 빈 셀이 없는 경우, 공식이 자동으로 조정됩니다. 그러나 목록 내부(끝에만 있는 것이 아님)에 빈칸이 있는 경우, 계산 카운트에서 건너뛸 수 있지만 드롭다운에서 의도하지 않은 간격을 생성할 수 있습니다.

잠재적인 문제: 데이터 소스에 의도적인 간격이 있거나 병합된 셀이나 비연속 데이터가 있는 경우, Excel 테이블을 소스 범위로 사용하거나 더 유연한 처리를 위해 아래 VBA 방법을 검토하세요.

VBA 코드를 사용하여 빈칸 대신 드롭다운 목록의 첫 항목을 자동으로 표시하기

일부 시나리오에서는 데이터 유효성 검사 소스를 조정하는 것만으로는 충분하지 않을 수 있습니다. 예를 들어, 데이터가 자주 변경되거나 소스 범위 내에 구조적 이유로 인해 빈칸이 나타날 위험이 있는 경우입니다. 간단한 VBA 코드를 사용하면, 데이터 유효성 검사 셀이 활성화될 때마다 드롭다운이 항상 첫 번째 사용 가능한 항목을 자동으로 선택하고 표시하도록 할 수 있습니다. 또한 이는 사용자 클릭을 줄임으로써 데이터 입력 속도를 향상시킬 수 있습니다.

1. 드롭다운 목록을 삽입한 후, 드롭다운이 포함된 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 '코드 보기'를 선택하세요. Microsoft Visual Basic for Applications 편집기가 나타납니다. 창에 관련 워크시트 모듈(표준 모듈이 아닌)에 다음 코드를 붙여넣으세요. 이 코드는 백그라운드에서 작동하며, 데이터 유효성 검사 셀을 선택할 때마다 드롭다운을 초기화합니다:

VBA 코드: 드롭다운 목록에서 첫 번째 데이터 항목을 자동으로 표시하기:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xFormula As String
    On Error GoTo Out:
    xFormula = Target.Cells(1).Validation.Formula1
    If Left(xFormula, 1) = "=" Then
        Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
    End If
Out:
End Sub

2. 코드를 붙여넣은 후, 통합 문서를 저장하세요(매크로 지원 파일로 .xlsm 확장자를 사용하는 것이 좋습니다). 그리고 VBA 편집기 창을 닫으세요. 이제 시트로 돌아가 드롭다운 목록이 있는 아무 셀을 클릭해보세요 — 셀을 활성화하면 드롭다운 목록의 첫 번째 항목이 자동으로 표시됩니다.

팁 및 고려 사항: 이 VBA 접근 방식은 특히 동적 또는 긴 소스 목록, 또는 피할 수 없는 빈 항목이 포함된 목록에서 원활한 사용자 경험을 원할 때 이상적입니다. 이 기능이 작동하려면 매크로를 활성화해야 하며, 일부 환경에서는 보안상의 이유로 매크로 사용이 제한될 수 있으므로 다른 사용자들에게 이를 알리는 것이 중요합니다.

문제 해결: 코드가 작동하지 않는 경우, VBA 편집기에서 올바른 워크시트 코드 창에 코드가 배치되었는지 다시 확인하세요. 또한 드롭다운이 표준 데이터 유효성 검사 목록을 사용하는지 확인하세요.

제한 사항: VBA 솔루션은 사용자가 드롭다운 셀을 선택할 때만 트리거됩니다. 셀이 다른 방식(예: 수식 결과 또는 붙여넣기를 통해)으로 채워진 경우에는 작동하지 않습니다. 셀에서 드롭다운을 제거하거나 VBA 코드가 없는 다른 시트로 셀을 이동하면 자동 선택 동작이 사라집니다.

Excel 테이블을 데이터 소스로 사용하기

드롭다운 목록의 소스 목록이 동적이고 유지 관리성을 높이고 싶다면, 소스 목록을 Excel 테이블로 변환하는 것을 고려해보세요. 테이블은 데이터가 추가되거나 삭제됨에 따라 자동으로 크기가 조정되므로 목록이 항상 최신 상태를 유지합니다. 단, Excel 테이블은 자동으로 빈 셀을 제외하지 않으며, 테이블의 빈 항목들은 여전히 드롭다운 목록에 나타날 수 있습니다. 이를 방지하려면 (예: Excel 365 및 Excel 2021에서 사용 가능한 FILTER 함수를 사용하여) 명시적으로 필터링해야 합니다.

1. 소스 데이터를 선택하고 Ctrl + T를 눌러 테이블로 변환하세요. 상단에 빈칸이 없는지 확인하세요. 테이블 디자인 탭을 사용하여 MyList와 같은 의미 있는 이름을 할당하세요.

2. 데이터 유효성 검사를 설정할 때, 테이블 열에 대한 구조화된 참조를 사용하세요. 데이터 유효성 검사의 소스에서 다음을 입력하세요:

=INDIRECT("MyList[Column1]")

Column1을 실제 열 이름(열 머리글)으로 바꾸세요. 이 방법은 테이블 열에 채워진 모든 항목을 동적으로 포함하며, 데이터를 업데이트함에 따라 목록의 무결성을 유지합니다.

이 접근 방식은 소스 데이터가 자주 업데이트되고 여러 사용자가 검증된 목록을 효율적으로 관리해야 하는 환경에서 특히 적합합니다.