제안/자동 완성을 통한 Excel 데이터 검증

제안/자동 완성을 통한 Excel 데이터 검증

엑셀의 이해도가 낮아서 죄송합니다, 제가 원하는 것은 불가능할 수도 있습니다.

데이터 유효성 검사로 사용하고자 하는 120개 항목의 목록이 있습니다.하지만 사람들이 120개의 옵션을 아래로 스크롤하는 대신에, 그들이 기꺼이 쓰는 것과 일치하는 옵션을 볼 수 있다면 좋을 것입니다.자동 완성과 같은, 이 팁을 찾았습니다.

저는 타이핑이 시작되면 더 많은 옵션을 가지고 싶습니다.

예를 들어, 이는 목록의 일부입니다.

Awareness  
Education  
Budget  
Budget Planning  
Enterprise Budget 

"B"를 입력할 때 마지막 세 가지 옵션이 나타나고 그 중 하나를 클릭합니다.여기에 vlookup을 포함할 수 있는 방법이 있는지 모르겠습니다.

이상적으로는 하나의 셀에 여러 개의 "태그"가 있는 것이 이상적이지만, 엑셀로는 절대 불가능합니다.

의견을 주셔서 정말 감사드리며, 제가 제대로 설명하지 못했다면 죄송합니다.

ExtendOffice.com 은 Excel 2016에서 사용할 수 있는 VBA 솔루션을 제공합니다.다음은 단계에 대한 설명입니다.저는 더 쉽게 하기 위해 추가 세부사항을 포함했습니다.VBA 코드도 약간 수정했습니다.이렇게 해도 효과가 없으면 단계를 다시 시도하거나 Extend Office 페이지의 지침을 확인하십시오.

1. 셀(또는 셀 범위)에 데이터 유효성 검사를 추가합니다.= 목록을 허용합니다.소스 = [자동 완성/드롭다운에 사용할 값이 있는 범위].확인을 클릭합니다.이제 드롭다운이 있지만 약한 자동 완성 기능이 있어야 합니다.

2. 새로 추가한 데이터 유효성 검사가 포함된 셀을 사용하여 ActiveX 콤보 상자(양식 제어 콤보 상자가 아님)를 삽입합니다.이 작업은 개발자 리본에서 수행됩니다.개발자 리본이 없는 경우 Excel 옵션 메뉴에서 리본을 추가해야 합니다.

3. [제어] 섹션의 [개발자] 탭에서 "설계 모드"를 누릅니다.방금 삽입한 콤보 상자를 선택합니다.그런 다음 동일한 리본 섹션에서 "속성"을 클릭합니다.속성 창에서 콤보 상자의 이름을 "TempCombo"로 변경합니다.

4. Alt + F11 눌 Visual Basic Editor (Visual Basic 편집기)가 있습니다.왼쪽에서 데이터 유효성 검사가 있는 워크시트를 두 번 클릭하여 해당 시트의 코드를 엽니다.다음 코드를 복사하여 시트에 붙여넣습니다.참고: 코드를 약간 수정하여 다음과 같은 경우에도 작동하도록 했습니다.Option Explicit시트의 맨 위에서 활성화됩니다.

   Option Explicit
   
   Private Sub Worksheet_SelectionChange(ByVal target As Range)
   'Update by Extendoffice: 2018/9/21
   ' Update by Chris Brackett 2018-11-30
   
   Dim xWs As Worksheet
   Set xWs = Application.ActiveSheet
   
   On Error Resume Next
   
   Dim xCombox As OLEObject
   Set xCombox = xWs.OLEObjects("TempCombo")
   
   ' Added this to auto select all text when activating the combox box.
   xCombox.SetFocus
   
   With xCombox
       .ListFillRange = vbNullString
       .LinkedCell = vbNullString
       .Visible = False
   End With
   
   
   Dim xStr As String
   Dim xArr
   
   
   If target.Validation.Type = xlValidateList Then
       ' The target cell contains Data Validation.
   
       target.Validation.InCellDropdown = False
   
   
       ' Cancel the "SelectionChange" event.
       Dim Cancel As Boolean
       Cancel = True
   
   
       xStr = target.Validation.Formula1
       xStr = Right(xStr, Len(xStr) - 1)
   
       If xStr = vbNullString Then Exit Sub
   
       With xCombox
           .Visible = True
           .Left = target.Left
           .Top = target.Top
           .Width = target.Width + 5
           .Height = target.Height + 5
           .ListFillRange = xStr
   
           If .ListFillRange = vbNullString Then
               xArr = Split(xStr, ",")
               Me.TempCombo.List = xArr
           End If
   
           .LinkedCell = target.Address
   
       End With
   
       xCombox.Activate
       Me.TempCombo.DropDown
   
   End If
   End Sub
   
   Private Sub TempCombo_KeyDown( _
                   ByVal KeyCode As MSForms.ReturnInteger, _
                   ByVal Shift As Integer)
       Select Case KeyCode
           Case 9  ' Tab key
               Application.ActiveCell.Offset(0, 1).Activate
           Case 13 ' Pause key
               Application.ActiveCell.Offset(1, 0).Activate
       End Select
   End Sub
   

5. "Microsoft Forms 2.0 Object Library"가 참조되었는지 확인합니다.Visual Basic Editor에서 Tools(도구) > References(참조)로 이동하여 해당 라이브러리 옆에 있는 상자(아직 선택하지 않은 경우)를 선택하고 OK(확인)를 클릭합니다.작동했는지 확인하려면 Debug > Compile VBA Project로 이동합니다.

6. 마지막으로 프로젝트를 저장하고 추가한 데이터 유효성 검사가 있는 셀을 클릭합니다.입력한 각 문자로 업데이트되는 제안의 드롭다운 목록이 있는 콤보 상자가 나타납니다.

만약 여러분이 VBA의 길을 가고 싶지 않다면, 이전 질문에서 나온 이 속임수가 있습니다.

Excel 2010: 유효성 검사 목록에서 자동 완성을 사용하는 방법

시트의 맨 위에 성가신 크기와 잠재적인 유지보수(추가 옵션이 필요한 경우, 직원 목록에서 사용자 이름 추가, 새 프로젝트 등)를 추가하지만 모두 동일하게 작동합니다.

http://www.ozgrid.com/Excel/autocomplete-validation.htm 에는 기본적으로 다음과 같은 복잡한 해결 방법이 있습니다.

1. 셀 값에 대한 자동 완성 사용 가능Tools - Options > Edit;
2. 유효성 검사 기준이 있는 항목 바로 위 셀에 유효성 검사 항목 목록을 다시 작성합니다.
3. 유효한 항목 목록이 있는 줄을 숨깁니다.

다음은 VBA를 사용하여 자동 완성 드롭다운 목록을 만드는 방법입니다.

---

먼저 워크시트에 콤보 상자를 삽입하고 속성을 변경한 다음 VBA 코드를 실행하여 자동 완성을 활성화해야 합니다.

1. 자동으로 완료할 드롭다운 목록이 포함된 워크시트로 이동합니다.

2. 콤보 상자를 삽입하기 전에 리본에서 개발자 탭을 활성화해야 합니다.

. Excel 2010 및 2013에서 파일 > 옵션을 클릭합니다.옵션 대화 상자의 오른쪽 창에서 리본 사용자 지정을 클릭하고 개발자 상자를 선택한 다음 확인 단추를 클릭합니다.

. Outlook 2007에서 Office 버튼 > Excel 옵션을 클릭합니다.Excel 옵션 대화 상자의 오른쪽 막대에서 인기를 클릭한 다음 리본 상자에서 개발자 표시 탭을 선택하고 마지막으로 확인 단추를 클릭합니다.

3. 그런 다음 ActiveX 컨트롤 아래에서 Developer > Insert > Combo Box를 클릭합니다.

4. 현재 열려 있는 워크시트에서 콤보 상자를 그린 다음 마우스 오른쪽 단추로 클릭합니다.마우스 오른쪽 버튼으로 클릭하는 메뉴에서 Properties(속성)를 선택합니다.

5. Developer > Design Mode를 클릭하여 Design Mode를 해제합니다.

6. 현재 열려 있는 워크시트 탭을 마우스 오른쪽 단추로 클릭하고 코드 보기를 클릭합니다.

7. 현재 워크시트 코드 편집기가 열려 있는지 확인한 다음 아래 VBA 코드를 복사하여 붙여 넣습니다.

extendoffice.com 에서 빌린 코드

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr



    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

9. 파일 > 닫기 및 Microsoft Excel로 돌아가기를 클릭하여 Microsoft Visual Basic for Application 창을 닫습니다.

10. 이제, 드롭다운 목록이 있는 셀을 클릭하기만 하면, 드롭다운 목록이 콤보 상자로 표시되는 것을 볼 수 있고, 상자에 첫 번째 문자를 입력하면 해당 단어가 자동으로 완성됩니다.

참고: 이 VBA 코드는 병합된 셀에 적용되지 않습니다.

출처: Excel 드롭다운 목록에 입력할 때 자동으로 완료하는 방법?

위에서 언급한 솔루션 중 어떤 것도 작동하지 않았습니다.작동하는 것처럼 보이는 것은 단 하나의 셀에 대한 기능만 제공합니다.

최근에 많은 이름을 입력해야 했고 제안 없이는 큰 고통이었습니다.저는 운 좋게도 자동 완성을 가능하게 하기 위해 이 엑셀 자동 완성 애드인을 가지고 있었습니다.단점은 매크로를 사용하도록 설정해야 한다는 것입니다(그러나 나중에 항상 해제할 수 있음).

저는 크리스비의 답변을 각색했습니다.이 예와 마찬가지로 셀을 클릭하면 임시 콤보 상자가 표시됩니다.추가로:

1. 콤보 상자 항목 목록이 사용자 유형으로 업데이트되고 일치하는 항목만 표시됩니다.
2. 콤보 상자에서 항목을 선택하면 이 오류로 인해 필터링이 건너뜁니다.

Option Explicit

Private Const DATA_RANGE = "A1:A16"
Private Const DROPDOWN_RANGE = "F2:F10"
Private Const HELP_COLUMN = "$G"


Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    
    On Error Resume Next
    
    With Me.TempCombo
        .LinkedCell = vbNullString
        .Visible = False
    End With
    
    If target.Cells.count > 1 Then
        Exit Sub
    End If
    
    Dim isect As Range
    Set isect = Application.Intersect(target, Range(DROPDOWN_RANGE))
    If isect Is Nothing Then
       Exit Sub
    End If
       
    With Me.TempCombo
        .Visible = True
        .Left = target.Left - 1
        .Top = target.Top - 1
        .Width = target.Width + 5
        .Height = target.Height + 5
        .LinkedCell = target.Address

    End With

    Me.TempCombo.Activate
    Me.TempCombo.DropDown
End Sub

Private Sub TempCombo_Change()
    If Me.TempCombo.Visible = False Then
        Exit Sub
    End If
    
    Dim currentValue As String
    currentValue = Range(Me.TempCombo.LinkedCell).Value
    
    If Trim(currentValue & vbNullString) = vbNullString Then
        Me.TempCombo.ListFillRange = "=" & DATA_RANGE
    Else
        If Me.TempCombo.ListIndex = -1 Then
             Dim listCount As Integer
             listCount = write_matching_items(currentValue)
             Me.TempCombo.ListFillRange = "=" & HELP_COLUMN & "1:" & HELP_COLUMN & listCount
             Me.TempCombo.DropDown
        End If

    End If
End Sub


Private Function write_matching_items(currentValue As String) As Integer
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet

    Dim cell As Range
    Dim c As Range
    Dim firstAddress As Variant
    Dim count As Integer
    count = 0
    xWs.Range(HELP_COLUMN & ":" & HELP_COLUMN).Delete
    With xWs.Range(DATA_RANGE)
        Set c = .Find(currentValue, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
              Set cell = xWs.Range(HELP_COLUMN & "$" & (count + 1))
              cell.Value = c.Value
              count = count + 1
             
              Set c = .FindNext(c)
              If c Is Nothing Then
                GoTo DoneFinding
              End If
           Loop While c.Address <> firstAddress
        End If
DoneFinding:
    End With
    
    write_matching_items = count

End Function

Private Sub TempCombo_KeyDown( _
                ByVal KeyCode As MSForms.ReturnInteger, _
                ByVal Shift As Integer)

    Select Case KeyCode
        Case 9  ' Tab key
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13 ' Pause key
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

주의:

1. 는 ComboBoxe의 MatchEntry로 .2 - fmMatchEntryNone을 ComboBox로 마십시오.TempCombo
2. 콤보박스 옵션을 설정하기 위해 listFillRange를 사용하고 있습니다.범위는 연속형이어야 하므로 일치하는 항목은 도움말 열에 저장됩니다.
3. 나는 그것을 성취하려고 노력했습니다.ComboBox.addItem하지만 목록 상자를 사용자 유형으로 다시 칠하는 것은 어려운 것으로 나타났습니다.

피벗을 사용하는 것이 어떻습니까?

샘플 표 필터 피벗

엑셀의 웹 버전에는 이 기능이 있습니다.목록 기반 유효성 검사를 추가하면 셀에 입력을 시작하면 목록에서 일치하는 항목의 목록이 팝업됩니다.

언급URL : https://stackoverflow.com/questions/19818075/excel-data-validation-with-suggestions-autocomplete