☰

Пользовательское автозаполнение 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.

Возможно ли что-то подобное? Неважно, возможно ли это с помощью встроенного функционала или путем создания Add-In или VBA-скрипта.

решение1

Полученные результаты

Это то, что можно сделать с помощью VBA (не VBScript!). Он автоматически создает комментарий всякий раз, когда изменяется значение ячейки.

Полученные результаты

Код

Диапазон источника

Для работы кода вам необходимо создатьименованный диапазон Sourceдля ваших исходных данных.

введите описание изображения здесь

Стандартный модуль кода

Вам нужно будет создать стандартный модуль кода в редакторе Visual Basic ( 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либо найти другой способ выбора только ячеек со значениями.

Связанный контент