☰

Excel 사용자 정의 자동 완성

tag-icon • tag-icon microsoft-excel autocomplete
Excel 사용자 정의 자동 완성

내 Excel 파일의 시트에 긴 목록이 있습니다.

Number   Value
123      Text 123
127      Another text
131      Yet another text
...      ...

다른 시트에 해당 숫자를 입력해야 합니다. 하지만 어떤 숫자가 어떤 텍스트에 속하는지 기억할 수 없기 때문에 어떤 값이 어떤 숫자에 속하는지 알려주는 일종의 자동 완성 기능을 갖고 싶습니다. 예를 들어 를 입력하면 12다음을 보여주는 도구 설명을 보고 싶습니다.

123 - Text 123
127 - Another text

123와 둘 다 127로 시작하기 때문입니다 12.

그런 일이 가능합니까? 내장된 기능을 사용하거나 추가 기능 또는 VBA 스크립트를 생성하여 가능한지는 중요하지 않습니다.

답변1

결과

이는 VBA(VBScript가 아님!)로 수행할 수 있는 작업입니다. 셀 값이 변경될 때마다 자동으로 주석이 생성됩니다.

결과

암호

소스 범위

코드가 작동하려면명명된 범위 Source귀하의 소스 데이터를 위해.

여기에 이미지 설명을 입력하세요

표준 코드 모듈

이를 수행하는 데 필요한 두 가지 기능을 위해 Visual Basic Editor( Alt+ ) 에서 표준 코드 모듈을 만들어야 합니다 . F11참조 Microsoft Scripting Runtime도 설정해야 합니다.

참고자료

표준 코드 모듈에 다음 코드를 붙여넣습니다.

Option Explicit

Function GetMatches(strInput As String) As String
    Dim dict As Scripting.Dictionary
    Dim key As Variant
    Dim strOutput As String

    strOutput = "Matches found:" & vbCrLf

    Set dict = GenerateDictionary()

    For Each key In dict.Keys
        If key Like strInput & "*" Then strOutput = _
            strOutput & vbCrLf & key & " - " & dict(key)
    Next

    GetMatches = strOutput
    Set dict = Nothing
End Function

Private Function GenerateDictionary() As Scripting.Dictionary
    Dim source As Range
    Dim cell As Range
    Dim dict As New Scripting.Dictionary
    Dim number As Integer
    Dim value As String

    Set source = Range("Source").SpecialCells(xlCellTypeConstants)

    For Each cell In source
        If cell.Row < 2 Then GoTo PassRow
        If cell.Column = 1 Then number = cell.value
        If cell.Column = 2 Then value = cell.value
        If number <> 0 And value <> "" And cell.Column = 2 Then _
            dict.Add number, value
PassRow:
    Next

    Set GenerateDictionary = dict
    Set dict = Nothing
End Function

이 코드에서는 숫자가 열 1에 있고 값이 열 2에 있다고 가정합니다. 또한 첫 번째 행을 건너뜁니다. 필요에 따라 더 잘 조정할 수 있습니다.

워크시트 코드

워크시트 코드에 다음 코드를 붙여넣으세요.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strInput As String

    If Not Intersect(Target, Me.Range("D1")) Is Nothing Then
        strInput = Target.value
        Target.ClearComments
        Target.AddComment (GetMatches(strInput))
        Target.Comment.Shape.TextFrame.AutoSize = True
    End If
End Sub

Me.Range원하는 단일 셀로 변경할 수 있습니다 .

용법

지정한 셀에 값을 입력하기만 하면 모든 일치 항목이 포함된 설명이 자동으로 추가됩니다.

우려사항

이 코드는 일치하는 항목을 검색할 때마다 사전을 생성합니다. 중소 규모 범위에서는 큰 문제가 아닙니다 Source(최대 10,000까지 테스트했지만 여전히 몇 밀리초 내에 실행됩니다).

일치하는 항목이 많으면 주석이 화면 밖으로 흘러나가는 경향이 있습니다. 이를 해결하는 유일한 실제 방법은 보다 구체적인 값을 입력하는 것입니다.

Source를 사용하면 범위 의 빈 셀이 무시됩니다. 이는 수식에는 작동하지 않습니다. 값이 있는 셀만 선택하는 방법 xlCellTypeConstants으로 전환하거나 다른 방법을 찾아야 합니다 .xlCellTypeFormulas

관련 정보