Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

Microsoft Excel에서 데이터 유효성 검사 목록은 워크시트의 데이터를 검증할 수 있게 해주는 강력한 도구입니다. 특정 값 범위를 선택할 때 많은 시간을 절약할 수 있으며, 셀이 특정 값만 입력받아야 한다면 매번 직접 타이핑할 필요가 없습니다. 대신 Excel 워크시트에 데이터 유효성 검사용 드롭다운 목록을 만들면 됩니다. 이 튜토리얼에서는 Excel VBA를 사용하여 배열(Array)로부터 데이터 유효성 검사 목록을 처음 만드는 방법을 정확히 알려드립니다.

이 글에는 적절한 예제와 상세한 설명이 포함되어 있습니다. 지식을 넓히고 싶으시다면 끝까지 읽어보세요.

Excel의 데이터 유효성 검사란 무엇인가?

데이터 유효성 검사(Data Validation)는 셀에 입력되는 값을 제어할 수 있는 기능입니다. 입력해야 할 값이 제한적일 때 드롭다운 목록을 활용하면 데이터를 손쉽게 검증할 수 있습니다. 같은 데이터를 반복해서 타이핑할 필요가 없어지고, 입력 오류도 방지됩니다.

'유효성 검사'라 불리는 이유는 유효한(valid) 데이터만 목록에 들어갈 수 있도록 보장하기 때문입니다.

특히 데이터셋을 처음 접하는 사용자에게 유용합니다. 데이터를 일일이 직접 입력하는 대신, 미리 만들어 둔 드롭다운 또는 유효성 검사 목록에서 원하는 값을 선택하기만 하면 됩니다.

Excel VBA로 배열에서 데이터 유효성 검사 목록 만들기: 단계별 절차

먼저 사용할 데이터셋을 살펴보겠습니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

여기에는 영업 사원들의 데이터셋이 있습니다. 각 사원의 근무 지역(Region)과 판매 제품(Product) 정보가 담겨 있는데, Region 열과 Product 열에 데이터 유효성 검사 목록을 만들어 보겠습니다.

만들 유효성 검사 목록은 다음과 같습니다.

  • Region: "North", "South", "East", "West"
  • Product: "TV", "Fridge", "Mobile", "Laptop", "AC"

물론 전통적인 방법으로도 유효성 검사 목록을 만들 수 있습니다. 하지만 여기서는 VBA 코드를 사용하겠습니다. VBA 코드 안에서 이 값들을 배열에 담고, 그 배열로부터 데이터를 검증하는 방식입니다.

다음 섹션에서 코드를 단계별로 작성해 보겠습니다. 저와 함께 직접 코드를 따라 작성하시는 것을 추천합니다. 그렇게 하면 코드를 훨씬 잘 이해하게 됩니다. 시작해 볼까요?

VBA 코드 작성: 배열로부터 데이터 유효성 검사 목록 생성하기

이 섹션에서는 Excel의 VBA 코드를 사용해 배열 기반의 데이터 유효성 검사 목록을 만드는 방법을 배웁니다. Region 열과 Product 열에 드롭다운 목록이 생기도록 구현하겠습니다.

📌 1단계: VBA 편집기 열기

  • 먼저 키보드에서 Alt+F11을 눌러 VBA 편집기를 엽니다.
  • 그다음 삽입 > 모듈(Insert > Module)을 선택합니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

📌 2단계: 서브 프로시저(Sub-procedure) 선언

이제 아래 코드를 입력합니다.

Sub data_validation_from_array()

End Sub

이것이 우리의 서브 프로시저입니다. 앞으로 모든 코드를 이 안에 작성하게 됩니다.

📌 3단계: 필요한 변수 선언

이제 앞으로 사용할 변수들을 선언할 차례입니다.

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

End Sub

배열은 Variant 형식으로 선언했습니다. 이 변수에는 여러 문자열이 담기게 됩니다.

region_range, product_range: 이 두 변수는 Region 열과 Product 열의 범위(Range)를 저장하는 용도입니다.

📌 4단계: 배열 설정하기

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

End Sub

보시다시피 region과 product 변수에 문자열들을 저장했습니다. 이 값들이 곧 드롭다운 목록의 항목이 됩니다.

📌 5단계: 데이터 유효성 검사 범위 설정하기

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

End Sub

Set region_range = Range("C5:C10"): 이 코드 줄은 Region 열을 지정합니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

Set product_range = Range("D5:D10"): 그리고 이 코드 줄은 Product 열을 지정합니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

📌 6단계: Region 열에 데이터 유효성 검사 목록 만들기

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

End Sub

각 코드 줄의 역할을 자세히 살펴보겠습니다.

  • With region_range.Validation: Region 열의 유효성 검사 개체를 선택합니다.
  • .Delete: 기존에 설정된 유효성 검사 목록이 있다면 삭제합니다.
  • .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ","): 새 데이터 유효성 검사 목록을 추가합니다.
    • AlertStyle은 목록 외부의 값을 입력했을 때 어떤 종류의 경고를 표시할지 결정합니다.
    • Formula1:=Join(region, ","): Formula 인자를 통해 유효성 검사 목록에 들어갈 값을 전달합니다. region 배열에 담긴 문자열들을 Join 메서드로 쉼표(,) 구분자와 함께 하나로 합친 것이 바로 목록의 소스(source)가 됩니다.
  • .IgnoreBlank = True: 빈(blank) 값을 허용합니다.
  • .InCellDropdown = True: 셀 내에 허용된 값들의 드롭다운 목록을 표시합니다.
  • .ErrorTitle = "Error": 데이터 유효성 검사 오류 대화상자의 제목을 설정합니다.
  • .ErrorMessage = "Please Provide a Valid Input": 오류 대화상자에 표시될 메시지를 설정합니다.
  • .ShowInput = True: 사용자가 유효성 검사 범위 내의 셀을 클릭할 때마다 입력 메시지를 표시합니다.
  • .ShowError = True: 사용자가 잘못된 값을 입력하면 오류 대화상자를 표시합니다.

📌 7단계: Product 열에 데이터 유효성 검사 목록 만들기

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

With product_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

End Sub

Product 열에 적용하는 코드도 구조는 동일합니다. 차이점은 With product_range.Validation으로 Product 열을 선택하고, Formula1:=Join(product, ",")에서 product 배열의 값을 Join 메서드로 결합해 목록 소스로 사용한다는 것뿐입니다.

VBA 코드 실행하기

코드 작성이 완료되었습니다. 이제 코드가 제대로 작동하는지 확인할 차례입니다. 현재 시트에서 코드를 실행해 보겠습니다.

먼저 키보드에서 Alt+F8을 눌러 매크로 대화상자를 엽니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

그다음 data_validation_from_array를 선택하고 실행(Run)을 클릭합니다.

이제 Region 열의 아무 셀이나 클릭해 보세요.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

셀 옆에 드롭다운 아이콘이 나타난 것을 확인할 수 있습니다. 드롭다운 아이콘을 클릭해 보세요.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

유효성 검사 목록의 모든 값이 표시됩니다. 이 값들은 우리가 region 배열에 담았던 값들입니다. 이제 각 셀마다 데이터를 선택해 주세요.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

이번에는 Product 열을 확인해 보겠습니다. Product 열의 아무 셀이나 클릭하세요.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

여기에도 드롭다운 아이콘이 나타납니다. 드롭다운을 클릭해 보세요.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

VBA 코드에서 product 배열에 담았던 모든 값이 그대로 표시됩니다. 이렇게 Excel에서 VBA 코드를 사용해 배열로부터 유효성 검사 목록을 성공적으로 만들었습니다.

이제 목록에 없는 값을 입력해 보겠습니다. "Headphone"이라는 제품을 입력해 보겠습니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

Enter 키를 누르면 다음과 같은 화면이 나타납니다.

Excel VBA 배열을 활용한 데이터 유효성 검사 드롭다운 목록 만들기 (단계별 가이드)

보시는 것처럼 오류 대화상자가 표시됩니다. VBA 코드에서 미리 설정해 둔 오류 제목과 오류 메시지가 정확히 나타나는 것을 확인할 수 있습니다.

💬 알아두면 좋은 팁

✎ 데이터 유효성 검사가 적용된 셀을 복사해서 다른 셀에 붙여넣으면, 붙여넣은 셀에도 동일한 유효성 검사 목록이 적용됩니다.

✎ 이 방식으로 만든 것은 동적 배열(dynamic array)이 아닙니다. 유효성 검사 목록을 확장하고 싶다면 배열에 문자열을 추가하기만 하면 됩니다. 그것만으로 충분히 작동합니다.

마치며

이 튜토리얼이 VBA 코드를 사용해 Excel에서 데이터 유효성 검사 목록을 만드는 데 도움이 되었기를 바랍니다. 설명드린 모든 방법을 실제 데이터셋에 직접 적용해 보시길 권합니다. 연습용 통합 문서를 다운로드하여 스스로 시도해 보세요. 피드백은 언제든 댓글로 남겨주세요. 여러분의 소중한 의견이 더 좋은 튜토리얼을 만드는 원동력이 됩니다.

다양한 Excel 관련 문제와 해결책은 저희 웹사이트 Exceldemy.com에서 확인하실 수 있습니다.

새로운 방법을 꾸준히 배우며 계속 성장하세요!

관련 문서

  • Excel에서 데이터 유효성 검사 수식에 IF문 활용하기 (6가지 방법)
  • Excel VBA로 명명된 범위(Named Range)를 활용한 데이터 유효성 검사 목록 만들기
  • Excel VBA에서 ReDim Preserve로 2차원 배열 재선언하기 (2가지 쉬운 방법)
  • Excel VBA: 배열에서 중복값 제거하기 (2가지 예제)
  • VBA로 배열의 평균값 계산하기 (매크로, UDF, UserForm)