☰

Excel 드롭다운 목록에서 값을 변경하는 방법

tag-icon • tag-icon microsoft-excel
Excel 드롭다운 목록에서 값을 변경하는 방법

미리 정의된 세트(드롭다운 목록)의 값을 사용하는 열이 있습니다.데이터 검증. 지금목록에서 일부 값을 변경하면, 드롭다운(열면)에서 즉시 이 새로운 값을 제공합니다. 하지만,테이블은 자동으로 업데이트되지 않습니다.즉, 열의 일부 값은 수동으로 수정할 때까지 유효하지 않습니다.

매크로/VBA를 기반으로 한 솔루션을 본 적이 있지만 Excel UI에 직접적으로 영리한 방법이 있지 않습니까?

답변1

댓글에서 언급했듯이 이를 수행하는 유일한 방법은 VBA를 사용하는 것입니다.

여기에 하나의 옵션이 있습니다. 코드 전체에 주석을 추가했습니다. 이는 "List"라는 유효성 검사 목록에 대해 명명된 범위를 사용하고 있으며 해당 범위가 유효성을 검사하는 셀과 동일한 시트에 있다고 가정합니다.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

다음을 다운로드할 수도 있습니다.예시 스프레드시트테스트해보려고 같이 모았습니다. (매크로가 포함되어 있습니다!)

답변2

하지만 Excel UI에 직접적으로 영리한 방법이 없을까요?

내가 아는 것 중 하나는 적어도 귀하의 요청을 모두 충족하는 것 같습니다.

  1. 설정해야합니다동적으로 변경되는 명명된 영역데이터 검증을 위한 소스로 사용됩니다. 이는 OFFSET함수를 사용하여 달성할 수 있습니다. A열 Sheet1(수식에만 이름이 중요함) 의 드롭다운 상자에 대한 값 목록이 있고 셀 A1에 머리글이 있고 List of values값이 시작 A2및 아래에 배치되어 있다고 가정하면 다음을 수행해야 합니다. 리본으로 이동하여 Formulas > Name Manager새 지역을 생성합니다( ) 라고 부르고 Items해당 영역이 셀을 가리키지 않고 대신 다음 공식을 사용하도록 설정합니다 =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1).
  2. 세트데이터 유효성 검사규칙: 목록의 셀 범위를 가리키는 대신 다음을 입력하세요 =Items. 그러면 명명된 영역이 목록 항목 소스로 사용됩니다.
  3. 위의 결과로 모든 요구 사항을 충족하는 진정한 동적 목록을 받게 됩니다. A열의 항목을 자유롭게 변경/추가할 수 있으며 이러한 변경 사항은 다음에 사용할 때 드롭다운에 즉시 반영됩니다. 동시에 이전 값은 그대로 유지됩니다.

저는 이 솔루션을 업무에 약 2년 동안 사용했습니다. 당신도 유용하게 사용할 수 있기를 바랍니다!

추신: 실제 샘플 파일은 다음과 같습니다.동적 드롭다운

관련 정보