☰

Excel 2010에서 끊임없이 변화하는 값의 열에 대한 드롭다운 목록을 어떻게 만듭니까?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Excel 2010에서 끊임없이 변화하는 값의 열에 대한 드롭다운 목록을 어떻게 만듭니까?

나는 머리글과 함께 수년간의 열이 포함된 Excel 스프레드시트를 가지고 있습니다.

Years
1993
1993
1994
1994
1994
...
2011
2011

해당 연도에 중복된 값이 있으며 시간이 지남에 따라 추가 행이 추가됩니다.

연도에 대한 드롭다운 목록을 표시해야 하지만 고유 연도만 표시해야 하는 또 다른 셀이 있습니다. Excel 2011에서 데이터 유효성 검사 기능을 사용해 보았지만 두 가지 문제가 있습니다.

  1. 중복된 연도를 표시합니다.
  2. 전체 열을 사용하도록 지시하면 드롭다운 목록에 빈 셀이 포함됩니다.

추가 행이 추가되면 자동으로 업데이트하면서 고유한 값만 표시하는 연도 드롭다운 목록을 얻으려면 어떻게 해야 합니까?

편집하다: 조금 더 많은 정보를 제공합니다. 드롭다운 목록은 Access 양식과 같이 계산된 데이터를 표시하기 위해 별도의 시트에 사용됩니다. 사용자는 연도 범위를 선택할 수 있으며 이에 따라 데이터가 업데이트됩니다. 원본 시트는 모든 데이터의 목록일 뿐입니다.

답변1

이러한 종류의 유효성 검사를 위해 VBA + 하나의 더티 트릭을 사용합니다.

먼저 Alt+F11을 눌러 VBA 편집기를 입력한 다음 해당 워크시트에 "동적 목록 유효성 검사 코드"(tm) :)를 입력합니다.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

이 코드는 데이터->유효성 검사->오류 메시지->제목에 입력된 수식으로 생성된 목록으로 셀 유효성 검사 목록을 업데이트합니다. 이렇게 하면 목록 유효성 검사가 포함된 각 셀에 고유한 공식이 있을 수 있습니다.

그런 다음 모듈을 추가하고(삽입->모듈) 다음 코드를 새 모듈에 넣습니다.

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

이 함수는 공백이나 반복 없이 범위 내의 모든 셀을 반환합니다. 그런 다음 목록 유효성 검사가 있는 각 셀에서 데이터 유효성 검사의 오류 메시지 제목에 GenDynList(범위)를 추가합니다.

답변2

지저분한. 자동으로 업데이트하는 기본 제공 방법은 없습니다. 데이터 세트의 연도로 제한하는 것보다 관심이 있을 수 있는 모든 연도를 포함하는 별도의 목록을 만드는 것이 더 간단할 것입니다.

답변3

다른 시트의 데이터에서 피벗 테이블을 추가합니다. 연도를 행으로 사용하면 테이블의 나머지 부분은 관련이 없습니다. 필요에 따라 행을 정렬하고 필터링합니다(예: "[공백]"을 명시적으로 필터링). 행 레이블이 있는 셀을 데이터 유효성 검사 대상으로 만듭니다.

항상 새로운 라벨을 모두 사용하려면 확장된 명명된 범위를 사용하세요. http://www.ozgrid.com/Excel/DynamicRanges.htm

국가, 주 등과 같은 다른 열에 대해서도 반복합니다. 모든 피벗을 동일한 데이터 범위에서 기반으로 하는 경우 그 중 하나를 새로 고칠 때 모두 함께 새로 고쳐집니다. 이제 프로세스는 다음과 같습니다. 새 데이터 추가, 피벗 새로 고침, 업데이트된 데이터 유효성 검사 사용.

추가 고려 사항: 원본 데이터에 테이블을 사용하면 항상 전체 테이블을 사용하므로 피벗 테이블을 쉽게 업데이트할 수 있습니다. 또는 더 많은 행을 추가할 때 문제를 방지하려면 확장된 명명된 범위를 데이터 소스로 사용하세요.

명명된 범위를 정의하면 다른 워크시트의 범위를 데이터 유효성 검사의 원본으로 사용할 수 있습니다. 다른 시트를 명시적으로 참조하는 일반 범위를 사용할 수 없습니다.

관련 정보