Excel пользовательское автозаполнение

Question

У меня есть длинный список на листе в моем файле Excel:

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

На другом листе мне нужно ввести эти цифры. Но так как я не могу вспомнить, какой номер принадлежит какому тексту, я хотел бы иметь некоторую форму функции автозаполнения, которая показывает мне, какое значение принадлежит какому номеру. Например, если я наберу 12 , я бы хотел увидеть подсказку, которая показывает мне следующее:

123 - Text 123
127 - Another text

потому что и 123 и 127 начинаются с 12 .

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

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