programing

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

megabox 2023. 5. 9. 22:14
반응형

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

몇 가지 vlookup() 함수가 종속된 대규모 검증 목록을 사용하고 있습니다.이 목록은 점점 더 커지고 있습니다.항목을 검색하는 목록을 수동으로 아래로 스크롤하는 대신 내가 찾고 있는 목록 항목의 첫 글자를 입력하는 방법이 있습니까?

구글링을 몇 번 해봤지만, 이것은 이것이 이전 버전의 Excel에서는 실제로 가능하지만 Excel 2010에서는 불가능하다는 것을 시사합니다.도움이 되길 바랍니다.

다음은 이 문제를 처리하는 매우 좋은 방법입니다(오즈그리드에서 확인됨).

당신의 목록이 있다고 가정해 보겠습니다.Sheet2자동 완성 기능이 설정된 유효성 검사 목록을 사용하려는 경우Sheet1.

온Sheet1 A1를 입력하십시오.=Sheet2!A1필요한 수의 예비 행을 포함하여 복사할 수 있습니다(총 300행).이러한 행을 숨기고 MyList라고 하는 동적 명명된 범위에 대해 참조:에서 다음 공식을 사용합니다.

=OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$300,-1),1)

이제 마지막 숨겨진 행 바로 아래 셀에서 데이터 유효성 검사 및 목록 소스 사용=MyList

[EDIT] Excel 2007+용으로 수정된 버전(2010년에는 테스트할 수 없지만 AFAIK, 버전에는 특별히 명시된 것이 없습니다.)
데이터 소스가 켜져 있다고 가정합니다.Sheet2!A1:A300그리고 유효성 검사 목록(자동 완성이라고도 함)이 셀에 있다고 가정해 보겠습니다.Sheet1!A1.

  1. 동적 명명된 범위 만들기MyList유효성 검사를 수행하는 셀의 값에 따라 달라집니다.

    =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

  2. 셀에 유효성 검사 목록 추가Sheet1!A1그것은 목록에 언급될 것입니다.=MyList

주의사항

  1. 먼저 입력한 다음 유효성 확인 화살표를 클릭해야 하므로 실제 자동 완성이 아닙니다. 그러면 목록이 목록의 첫 번째 일치 요소에서 시작됩니다.

  2. 목록은 데이터가 끝날 때까지 계속됩니다.보다 정확하게(일치하는 요소만 목록에 유지) 하려면 다음을 변경할 수 있습니다.COUNTA와 함께SUMLPRODUCT일치하는 요소의 수를 계산합니다.

  3. 원본 목록을 정렬해야 합니다.

다른 방법이 있습니다.유효성 검사가 활성화된 셀 위에 ActiveX ComboBox를 놓은 다음 ComboBox에 자동 완성 기능을 제공하는 방식으로 작동합니다.

Option Explicit

' Autocomplete - replacing validation lists with ActiveX ComboBox
'
' Usage:
'   1. Copy this code into a module named m_autocomplete
'   2. Go to Tools / References and make sure "Microsoft Forms 2.0 Object Library" is checked
'   3. Copy and paste the following code to the worksheet where you want autocomplete
'      ------------------------------------------------------------------------------------------------------
'      - autocomplete
'      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'          m_autocomplete.SelectionChangeHandler Target
'      End Sub
'      Private Sub AutoComplete_Combo_KeyDown(ByVal KeyCode As msforms.ReturnInteger, ByVal Shift As Integer)
'          m_autocomplete.KeyDownHandler KeyCode, Shift
'      End Sub
'      Private Sub AutoComplete_Combo_Click()
'          m_autocomplete.AutoComplete_Combo_Click
'      End Sub
'      ------------------------------------------------------------------------------------------------------

' When the combobox is clicked, it should dropdown (expand)
Public Sub AutoComplete_Combo_Click()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object
    If cbo.Visible Then cb.DropDown
End Sub

' Make it easier to navigate between cells
Public Sub KeyDownHandler(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Const UP As Integer = -1
    Const DOWN As Integer = 1

    Const K_TAB_______ As Integer = 9
    Const K_ENTER_____ As Integer = 13
    Const K_ARROW_UP__ As Integer = 38
    Const K_ARROW_DOWN As Integer = 40

    Dim direction As Integer: direction = 0

    If Shift = 0 And KeyCode = K_TAB_______ Then direction = DOWN
    If Shift = 0 And KeyCode = K_ENTER_____ Then direction = DOWN
    If Shift = 1 And KeyCode = K_TAB_______ Then direction = UP
    If Shift = 1 And KeyCode = K_ENTER_____ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_UP__ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_DOWN Then direction = DOWN

    If direction <> 0 Then ActiveCell.Offset(direction, 0).Activate

    AutoComplete_Combo_Click
End Sub

Public Sub SelectionChangeHandler(ByVal Target As Range)
    On Error GoTo errHandler

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object

    ' Try to hide the ComboBox. This might be buggy...
    If cbo.Visible Then
        cbo.Left = 10
        cbo.Top = 10
        cbo.ListFillRange = ""
        cbo.LinkedCell = ""
        cbo.Visible = False
        Application.ScreenUpdating = True
        ActiveSheet.Calculate
        ActiveWindow.SmallScroll
        Application.WindowState = Application.WindowState
        DoEvents
    End If

    If Not HasValidationList(Target) Then GoTo ex

    Application.EnableEvents = False

    ' TODO: the code below is a little fragile
    Dim lfr As String
    lfr = Mid(Target.Validation.Formula1, 2)
    lfr = Replace(lfr, "INDIREKTE", "") ' norwegian
    lfr = Replace(lfr, "INDIRECT", "") ' english
    lfr = Replace(lfr, """", "")
    lfr = Application.Range(lfr).Address(External:=True)

    cbo.ListFillRange = lfr
    cbo.Visible = True
    cbo.Left = Target.Left
    cbo.Top = Target.Top
    cbo.Height = Target.Height + 5
    cbo.Width = Target.Width + 15
    cbo.LinkedCell = Target.Address(External:=True)
    cbo.Activate
    cb.SelStart = 0
    cb.SelLength = cb.TextLength
    cb.DropDown

    GoTo ex

errHandler:
    Debug.Print "Error"
    Debug.Print Err.Number
    Debug.Print Err.Description
ex:
    Application.EnableEvents = True
End Sub

' Does the cell have a validation list?
Function HasValidationList(Cell As Range) As Boolean
    HasValidationList = False
    On Error GoTo ex
    If Cell.Validation.Type = xlValidateList Then HasValidationList = True
ex:
End Function

' Retrieve or create the ComboBox
Function GetComboBoxObject(ws As Worksheet) As OLEObject
    Dim cbo As OLEObject
    On Error Resume Next
    Set cbo = ws.OLEObjects("AutoComplete_Combo")
    On Error GoTo 0
    If cbo Is Nothing Then
        'Dim EnableSelection As Integer: EnableSelection = ws.EnableSelection
        Dim ProtectContents As Boolean: ProtectContents = ws.ProtectContents

        Debug.Print "Lager AutoComplete_Combo"
        If ProtectContents Then ws.Unprotect
        Set cbo = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
                            Left:=50, Top:=18.75, Width:=129, Height:=18.75)
        cbo.name = "AutoComplete_Combo"
        cbo.Object.MatchRequired = True
        cbo.Object.ListRows = 12
        If ProtectContents Then ws.Protect
    End If
    Set GetComboBoxObject = cbo
End Function

JMax의 답변을 바탕으로 동적 명명된 범위에 대해 다음 공식을 사용하여 여러 행에 대해 솔루션이 작동하도록 합니다.

=OFFSET(Sheet2!$A$1,MATCH(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),4))&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

항목의 수직 열이 있을 때마다 Excel이 자동으로 이 작업을 수행합니다.열 아래(또는 위)에 있는 빈 셀을 선택하고 입력을 시작하면 열에 있는 모든 항목을 기준으로 자동 완성됩니다.

다른 사람들이 제안했듯이, 당신은 콤보 박스를 사용해야 합니다.그러나 대부분의 튜토리얼에서는 콤보 상자를 하나만 설정하는 방법을 보여주며 이 과정은 매우 지루합니다.

이전에 목록에서 대량의 데이터를 입력할 때 이 문제가 발생했기 때문에 이 자동 완성 추가 기능을 사용하는 것이 좋습니다.선택한 셀에 콤보 상자를 만드는 데 도움이 되며 드롭다운에 표시할 목록을 정의할 수 있습니다.

=OFFSET(NameList!$A$2:$A$200,MATCH(INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*",NameList!$A$2:$A$200,0)-1,0,COUNTIF($A$2:$A$200,INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*"),1)

  1. 을 트시이다작성으로 NamelistA열에는 데이터 채우기 목록이 있습니다.

  2. 을 다으로시이만들기름으로 합니다.FillData원하는 대로 데이터 유효성 검사 목록을 만들 수 있습니다.

  3. 첫 번째 알파벳을 입력하고 선택하면 유형에 따라 드롭다운 메뉴가 나타납니다.

언급URL : https://stackoverflow.com/questions/7989763/excel-2010-how-to-use-autocomplete-in-validation-list

반응형

'programing' 카테고리의 다른 글

SQL WHERE ID(id1, id2, ..., idn)  (0) 2023.05.14
DLL 프로젝트에서 WPF 창을 만들지 않습니까?  (0) 2023.05.09
Xcode의 빌드 폴더는 어디에 있습니까?  (0) 2023.05.09
Windows에서 Redis를 실행하려면 어떻게 해야 합니까?  (0) 2023.05.09
GPG 키로 Git에서 커밋을 "자동 서명"하는 방법이 있습니까?  (0) 2023.05.09

'programing'의 다른글

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

관련글

댓글

티스토리툴바