Проверка данных Excel с помощью предложений/автозаполнения

Извините за мой низкий уровень понимания Excel, возможно, то, что я ищу, не возможно.

У меня есть список из 120 записей, которые я хочу использовать для проверки данных. Но вместо того, чтобы людям приходилось прокручивать 120 вариантов, было бы здорово, если бы они могли начать печатать и увидеть варианты, которые могли бы соответствовать тому, что они готовы написать. Вроде как в автозаполнении, я нашел этот совет.

Я хотел бы иметь больше вариантов, когда начинается набор текста.

Например, это будет частью списка:

Awareness  
Education  
Budget  
Budget Planning  
Enterprise Budget

При наборе "B" я бы хотел, чтобы появились три последних варианта и щелкнул один из них. Я не знаю, есть ли способ включить vlookup здесь...

В идеале идеальным вариантом было бы иметь несколько "тегов" в одной ячейке, но я боюсь, что с Excel это абсолютно невозможно.

Большое спасибо за любой вклад и извините, если я не объяснил себя должным образом.

Ответы

Ответ 1

Если вы не хотите идти по пути VBA, есть этот трюк из предыдущего вопроса.

Excel 2010: как использовать автозаполнение в списке валидации

Это добавляет некоторую раздражающую массу в верхнюю часть ваших листов и потенциальное обслуживание (если вам нужно больше вариантов, добавление имен людей из списка сотрудников, новые проекты и т.д.), но все равно работает.

Ответ 2

Там беспорядочное обходное решение в http://www.ozgrid.com/Excel/autocomplete-validation.htm, которое в основном работает следующим образом:

Включить "Автозаполнение для значений ячеек" на Tools - Options > Edit;
Создайте список допустимых элементов в ячейке, непосредственно выше той, где указаны критерии проверки;
Скрыть строки со списком допустимых элементов.

Ответ 3

Ни одно из вышеупомянутых решений не работало. Тот, который, казалось, работал, обеспечивает только функциональность только для одной ячейки

Недавно мне пришлось вводить много имен и без предложений, это была огромная боль. Мне посчастливилось иметь эту надстрочную надстройку автозаполнения, чтобы включить автозаполнение. Нижняя сторона - это то, что вам нужно включить макрос (но вы всегда можете отключить его позже)

Ответ 4

ExtendOffice.com предлагает решение VBA, которое работало для меня в Excel 2016. Здесь описано описание шагов. Я включил дополнительные детали, чтобы облегчить их работу. Я также немного изменил код VBA. Если это не сработает для вас, повторите шаги или ознакомьтесь с инструкциями на странице ExtendOffice.

Добавить проверку данных в ячейку (или диапазон ячеек). Allow = List. Source = [диапазон со значениями, которые вы хотите для автоматического завершения/раскрывающегося списка]. Нажмите "ОК". Теперь вы должны иметь раскрывающийся список, но со слабой функцией автозаполнения.
В ячейке, содержащей вашу недавно добавленную проверку данных, вставьте поле со списком ActiveX (НЕ комбинированное поле управления формой). Это делается с ленты разработчика. Если у вас нет ленты для разработчиков, вам нужно добавить ее из меню опций Excel.
На вкладке "Разработчик" в разделе "Элементы управления" нажмите "Режим разработки". Выберите поле со списком, которое вы только что вставили. Затем в том же разделе ленты нажмите "Свойства". В окне "Свойства" измените имя поля со списком на "TempComboBox".

Нажмите ALT + F11, чтобы перейти к редактору 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

Убедитесь, что ссылка на "Библиотека объектов Microsoft Forms 2.0" указана. В редакторе Visual Basic перейдите в "Инструменты"> "Ссылки", установите флажок рядом с этой библиотекой (если он еще не отмечен) и нажмите "ОК". Чтобы убедиться, что это сработало, перейдите в Debug> Compile VBA Project.
Наконец, сохраните проект и щелкните в ячейке с добавленной вами проверкой данных. Вы должны увидеть поле со списком с раскрывающимся списком предложений, которые обновляются с каждой буквой, которую вы вводите.

Ответ 5

Вот решение, как сделать выпадающий список автозаполнения с VBA:

Сначала необходимо вставить поле со списком в лист и изменить его свойства, а затем запустить код VBA для включения автозаполнения.

Зайдите в рабочий лист, который содержит выпадающий список, который вы хотите, чтобы он был автозаполнен.
Перед вставкой поля со списком необходимо включить вкладку "Разработчик" на ленте.

а). В Excel 2010 и 2013 щелкните Файл> Параметры. А в диалоговом окне "Параметры" нажмите "Настроить ленту" на правой панели, установите флажок "Разработчик" и нажмите кнопку "ОК".

б). В Outlook 2007 нажмите кнопку Office> Параметры Excel. В диалоговом окне "Параметры Excel" нажмите "Популярные" на правой панели, затем откройте вкладку "Показать разработчика" на ленте и, наконец, нажмите кнопку "ОК".

Затем нажмите "Разработчик"> "Вставить"> "Поле со списком" в разделе "Элементы управления ActiveX".
Нарисуйте поле со списком в текущем открытом листе и щелкните его правой кнопкой мыши. Выберите Свойства в контекстном меню.
Выключите Режим дизайна, нажав Разработчик> Режим дизайна.
Щелкните правой кнопкой мыши на текущей открытой вкладке листа и выберите "Просмотреть код".
Убедитесь, что текущий редактор кода рабочего листа открыт, а затем скопируйте и вставьте в него приведенный ниже код VBA.

Код заимствован у exteoffice.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

Нажмите Файл> Закрыть и вернуться в Microsoft Excel, чтобы закрыть окно Microsoft Visual Basic для приложений.
Теперь просто щелкните ячейку с раскрывающимся списком, и вы увидите, что раскрывающийся список отображается в виде поля со списком, затем введите первую букву в поле, соответствующее слово будет заполнено автоматически.

Примечание. Этот код VBA не применяется к объединенным ячейкам.

Источник: Как выполнить автозаполнение при наборе в раскрывающемся списке Excel?

Ответ 6

код работает хорошо, когда в списке проверки данных нет формулы.

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

есть ли способ решить это?

Спасибо всем