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

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

Microsoft Excel에서 데이터 유효성 검사 목록은 워크시트의 데이터 유효성을 검사할 수 있는 도구 중 하나입니다. 실제로 특정 범위의 값을 선택하는 데 많은 시간을 절약하는 데 도움이 됩니다. 셀이 특정 값만 사용하는 경우 반복해서 입력할 필요가 없습니다. 대신 Excel 워크시트에서 데이터 유효성 검사를 위한 드롭다운 목록을 만들 수 있습니다. 이 자습서에서는 Excel VBA를 사용하여 배열에서 첫 번째 데이터 유효성 검사 목록을 만드는 방법을 정확히 배웁니다.

이 튜토리얼은 적절한 예와 적절한 일러스트레이션으로 포인트가 될 것입니다. 따라서 지식을 풍부하게 하려면 전체 기사를 읽으십시오.

이 연습 워크북을 다운로드하십시오.

Excel의 데이터 유효성 검사란 무엇입니까?

이제 데이터 유효성 검사를 통해 셀의 입력을 제어할 수 있습니다. 필드에 입력할 값이 제한되어 있는 경우 드롭다운 목록을 사용하여 데이터를 확인할 수 있습니다. 반복해서 입력하여 데이터를 입력할 필요가 없습니다. 데이터 유효성 검사 목록은 또한 입력에 오류가 없는지 확인합니다.

이제 데이터 유효성 검사라고 하는 이유는 무엇입니까? 유효한 데이터만 목록에 포함되도록 하기 때문입니다.

데이터세트를 처음 접하는 사용자에게 기본적으로 도움이 됩니다. 데이터를 수동으로 입력할 필요가 없습니다. 대신 사용자가 생성한 드롭다운 또는 데이터 유효성 검사 목록에서 값을 선택할 수 있습니다.

Excel VBA를 사용하여 배열에서 데이터 유효성 검사 목록을 만드는 단계별 절차

우선 데이터 세트를 살펴보십시오.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

여기에 일부 영업 사원의 데이터 세트가 있습니다. 우리는 그들의 작업 지역과 판매 제품이 있습니다. 여기에서 지역 및 제품 열에 대한 데이터 유효성 검사 목록을 만들 것입니다.

데이터 유효성 검사 목록에는 다음이 포함됩니다.

  • 지역 :"북쪽", "남쪽", "동쪽", "서쪽"
  • 제품 :"TV", "냉장고", "모바일", "노트북", "AC"

기존 방식으로 유효성 검사 목록을 만들 수 있습니다. 그러나 여기서는 VBA 코드를 사용합니다. 이제 VBA 코드에서 배열에 넣습니다. 그리고 그 배열에서 데이터의 유효성을 검사합니다.

다음 섹션에서는 단계별 방식으로 코드를 빌드합니다. 저와 함께 코드를 작성하는 것이 좋습니다. 이렇게 하면 코드를 더 잘 배울 수 있습니다. 그 속으로 들어가 봅시다.

배열에서 Excel 데이터 유효성 검사 목록을 만들기 위한 VBA 코드 작성

이 섹션에서는 Excel의 VBA 코드를 사용하여 배열에서 데이터 유효성 검사 목록을 작성하는 방법을 배웁니다. 여기, 우리의 지역 및 제품 열에는 드롭다운 목록이 포함됩니다.

📌 1단계:VBA 편집기 열기

  • 먼저 Alt+F11을 누릅니다. 키보드에서 VBA 편집기를 엽니다.
  • 그런 다음 삽입>모듈을 선택합니다. .

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

📌 2단계:하위 절차 선언

이제 다음 코드를 입력하세요.

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

배열을 변형으로 선언합니다. . 이 변수에는 몇 가지 문자열이 있습니다.

region_range, product_range: 이 변수는 Region 및 Product 열의 범위를 저장합니다.

📌 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

보시다시피 지역 및 제품 변수에 일부 문자열을 저장했습니다. 드롭다운 목록을 만드는 데 사용할 것입니다.

자세히 알아보기: VBA에서 문자열을 배열로 분할하는 방법(3가지 방법)

📌 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

region_range 설정 =범위("C5:C10") :이 코드 줄은 지역 을 나타냅니다. 칼럼.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

제품 범위 설정 =범위("D5:D10") :그리고 이 코드 줄은 제품 을 지정하고 있습니다. 칼럼.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

자세히 알아보기: Excel VBA에서 범위를 배열로 변환하는 방법(3가지 방법)

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

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

region_range.Validation 사용: 이 줄을 사용하여 지역 열을 선택합니다. .

.삭제: 기존 유효성 검사 목록이 있으면 삭제합니다.

.추가 유형:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(지역, ",") :여기에 데이터 유효성 검사 목록을 추가하고 있습니다.

  • 경고 스타일 사용자가 목록 외부에 항목을 입력할 경우 표시할 경고 유형을 결정합니다.
  • Formula1:=Join(지역, ",") :공식 에 의해 유효성 검사 목록에 값을 제공하고 있습니다. 지역 에 일부 문자열이 있습니다. 가입 을 사용하여 메서드에서 구분 기호 쉼표(,)로 결합합니다. 이러한 값 또는 항목은 유효성 검사 목록의 소스가 됩니다.

.IgnoreBlank =참 :이 줄에서 공백 값을 허용합니다.

.InCellDropdown =True :허용되는 값으로 드롭다운 목록을 표시합니다.

.ErrorTitle ="오류" :데이터 유효성 검사 오류 대화 상자의 제목을 설정하고 있습니다.

.ErrorMessage ="올바른 입력을 제공하십시오." :데이터 유효성 검사 오류 대화 상자에 오류 메시지를 설정합니다.

.ShowInput =True: 사용자가 데이터 검증 범위의 셀을 클릭할 때마다 데이터 검증 입력 메시지를 표시합니다.

.ShowError =True: 사용자가 잘못된 입력을 제공하면 오류 대화 상자가 표시됩니다.

자세히 알아보기: Excel의 열에서 배열로 고유한 값을 가져오는 VBA(3가지 기준)

유사한 수치

  • Excel에서 배열을 전치하는 VBA(3가지 방법)
  • Excel에서 테이블 배열의 이름을 지정하는 방법(간단한 단계 사용)
  • CSV 파일을 배열로 읽는 Excel VBA(4가지 이상적인 예)
  • Excel VBA:배열에서 여러 기준으로 필터링하는 방법(7가지 방법)

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

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_range.Validation 사용: 이 줄을 사용하여 제품 열을 선택합니다. .

.삭제: 기존 유효성 검사 목록이 있으면 삭제합니다.

.추가 유형:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(제품, ",") :여기에 데이터 유효성 검사 목록을 추가하고 있습니다.

  • 경고 스타일 사용자가 목록 외부에 항목을 입력할 경우 표시할 경고 유형을 결정합니다.
  • Formula1:=Join(지역, ",") :공식에 의해 , 유효성 검사 목록에 값을 제공하고 있습니다. 제품 에 일부 문자열이 있습니다. 가입 을 사용하여 메서드에서 구분 기호 쉼표(,)로 결합합니다. 이러한 값 또는 항목은 유효성 검사 목록의 소스가 됩니다.

.IgnoreBlank =참 :이 줄에서 공백 값을 허용합니다.

.InCellDropdown =True :허용되는 값으로 드롭다운 목록을 표시합니다.

.ErrorTitle ="오류" :데이터 유효성 검사 오류 대화 상자의 제목을 설정하고 있습니다.

.ErrorMessage ="올바른 입력을 제공하십시오." :데이터 유효성 검사 오류 대화 상자에 오류 메시지를 설정합니다.

.ShowInput =True: 사용자가 데이터 검증 범위의 셀을 클릭할 때마다 데이터 검증 입력 메시지를 표시합니다.

.ShowError =True: 사용자가 잘못된 입력을 제공하면 오류 대화 상자가 표시됩니다.

VBA 코드 실행

우리는 이미 VBA 코드를 구축했습니다. 이제 코드가 제대로 작동하는지 확인할 차례입니다. 현재 시트에서 이 코드를 실행할 것입니다.

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

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

다음으로 data_validation_from_array를 선택합니다. 실행을 클릭합니다. .

이제 지역 의 아무 셀이나 클릭하십시오. 칼럼.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

여기에서 셀 옆에 드롭다운 아이콘을 볼 수 있습니다. 이제 드롭다운 아이콘을 클릭하십시오.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

여기에서 유효성 검사 목록의 모든 값을 볼 수 있습니다. 우리는 지역 에 이 값을 부여했습니다. 정렬. 이제 각 셀의 데이터를 선택하십시오.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

제품 을 확인해 보겠습니다. 열. 제품 에서 아무 셀이나 클릭하십시오. 칼럼.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

여기에 드롭다운 아이콘도 있습니다. 이제 드롭다운을 클릭하세요.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

보시다시피 제품 에 부여한 모든 가치는 VBA 코드의 배열이 여기에 표시됩니다. 따라서 Excel에서 VBA 코드를 성공적으로 사용하여 배열에서 유효성 검사 목록을 만들었습니다.

이제 주어진 배열에 없는 값을 지정해 보겠습니다. "헤드폰 ".

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

이제 Enter 키를 누릅니다. . 그러면 다음이 표시됩니다.

배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA

보시다시피 오류 대화 상자가 표시됩니다. 우리는 이미 VBA 코드에 오류 제목과 오류 메시지를 설정했고 정확히 그것을 보여주고 있습니다.

자세히 알아보기: Excel에서 다중 선택으로 데이터 유효성 검사 드롭다운 목록 만들기

💬 기억해야 할 사항

✎ 데이터 유효성 검사가 있는 모든 셀을 복사하여 다른 셀에 붙여넣을 수 있습니다. 결과 셀에는 동일한 데이터 유효성 검사 목록이 있습니다.

✎ 다이나믹 어레이가 아닙니다. 데이터 유효성 검사 목록을 확장하려면 배열에 문자열로 추가하기만 하면 됩니다. 잘 될 것입니다.

결론

결론적으로 이 자습서가 VBA 코드를 사용하여 Excel에서 데이터 유효성 검사 목록을 만드는 데 유용한 지식을 제공했기를 바랍니다. 이 모든 지침을 배우고 데이터 세트에 적용하는 것이 좋습니다. 실습 워크북을 다운로드하여 직접 시도해 보십시오. 또한 의견 섹션에 자유롭게 의견을 제공하십시오. 귀하의 소중한 피드백은 우리가 이와 같은 튜토리얼을 만들도록 동기를 부여합니다.

Exceldemy.com 웹사이트를 확인하는 것을 잊지 마세요. 다양한 Excel 관련 문제 및 해결 방법에 대해 알아보세요.

계속해서 새로운 방법을 배우고 성장하세요!

관련 기사

  • Excel의 데이터 유효성 검사 수식에서 IF 문을 사용하는 방법(6가지 방법)
  • Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위 사용
  • Excel VBA에서 2D 배열을 ReDim 보존하는 방법(2가지 쉬운 방법)
  • Excel VBA:배열에서 중복 제거(예제 2개)
  • VBA(매크로, UDF 및 UserForm)를 사용하여 배열의 평균 계산