Note: The other languages of the website are Google-translated. Back to English

 Как создать раскрывающийся список, но отображать разные значения в Excel?

На листе Excel мы можем быстро создать раскрывающийся список с функцией проверки данных, но пробовали ли вы когда-нибудь показать другое значение, когда вы щелкаете раскрывающийся список? Например, у меня есть следующие два столбца данных в столбце A и столбце B, теперь мне нужно создать раскрывающийся список со значениями в столбце Name, но когда я выбираю имя из созданного раскрывающегося списка, соответствующий значение в столбце Number отображается, как показано на следующем снимке экрана. Эта статья познакомит вас с деталями решения этой задачи.

раскрывающийся список документов разные значения 1

Создать раскрывающийся список, но отображать другое значение в ячейке раскрывающегося списка


Создать раскрывающийся список, но отображать другое значение в ячейке раскрывающегося списка

Чтобы завершить эту задачу, выполните следующие действия шаг за шагом:

1. Создайте имя диапазона для значений ячеек, которые вы хотите использовать в раскрывающемся списке, в этом примере я введу раскрывающееся имя в поле Имя Box, а затем нажмите Enter ключ, см. снимок экрана:

раскрывающийся список документов разные значения 2

2. Затем выберите ячейки, в которые вы хотите вставить раскрывающийся список, и нажмите Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:

раскрывающийся список документов разные значения 3

3. В проверка достоверности данных диалоговое окно под Настройки , выберите Список из Разрешить раскрывающийся список, а затем щелкните раскрывающийся список документов разные значения 5 кнопку, чтобы выбрать список имен, который вы хотите использовать в качестве раскрывающихся значений в Источник текстовое окно. Смотрите скриншот:

раскрывающийся список документов разные значения 4

4. После вставки раскрывающегося списка щелкните правой кнопкой мыши вкладку активного листа и выберите Просмотреть код из контекстного меню, а в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой модуль:

Код VBA: отобразить другое значение из раскрывающегося списка:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    selectedNa = Target.Value
    If Target.Column = 5 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

раскрывающийся список документов разные значения 6

Внимание: В приведенном выше коде число 5 в Если Target.Column = 5 Then скрипт - это номер столбца, в котором находится раскрывающийся список, «падать" в этом selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("раскрывающийся список"), 2, False) code - это имя диапазона, которое вы создали на шаге 1. Вы можете изменить их по своему усмотрению.

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

раскрывающийся список документов разные значения 7


Демонстрация: создание раскрывающегося списка, но отображение разных значений в Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

 


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно

 

Сортировать комментарии по
Комментарии (42)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли это сделать на разных листах? Я имею в виду, что на листе 1 выпадающий список, а на листе 2 диапазон. Как мне это закодировать? Заранее спасибо. Тина.
Этот комментарий был сведен к минимуму модератором на сайте
Что делать, если я хочу сделать более одного раскрывающегося списка, который возвращает разные значения на одном листе? Можете ли вы показать мне пример кодирования для двух или более?
Этот комментарий был сведен к минимуму модератором на сайте
Ли Энн

Если вы просто скопируете и вставите код из If в EndIf и измените столбец # и таблицу, он должен работать:


Sub Worksheet_Change (Цель ByVal как диапазон)
selectedNa = Target.Value
Если Target.Column = 5 Then
selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("раскрывающийся список"), 2, False)
Если Не Ошибка(выбранноеЧисло), Тогда
Target.Value = selectedNum
End If
End If
Если Target.Column = 9 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown1"), 2, False)
Если Не Ошибка(выбранноеЧисло), Тогда
Target.Value = selectedNum
End If
End If
End Sub

Я не говорю, что это правильный способ, но он работал на моей тестовой версии. Я использую Эксель 2013
Этот комментарий был сведен к минимуму модератором на сайте
Только что попробовал. И это сработало!! Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, помогите, это не работает, можете ли вы вставить сюда весь код для 2 столбцов?
Этот комментарий был сведен к минимуму модератором на сайте
Тони: Верно, но следующий шаг — избегать поощрения: а) нарушений принципа DRY (и, следовательно, увеличения: а.1) вероятности ошибок и затрат на их исправление и а.2) затрат на возможные будущие изменения/улучшения) путем не поощрять программирование «Копировать-вставить», показывая, как его можно «рефакторить» для уменьшения дублирования кода, и б) жестко закодированные литеральные («магические» «числа» / «строки») константы путем объявления и использования именованных констант, которые гораздо сложнее ошибиться, не вызывая ошибки компилятора. Единственное, что отличается между двумя скопированными блоками кода, — это номера столбцов и имена диапазонов, поэтому, насколько это возможно, все остальное не должно дублироваться, поэтому вместо этого код должен быть, например, таким:

' -- ПРИМЕЧАНИЕ. Объявите "EmptyString" в модуле глобальных объявлений.
Public Const EmptyString as String = ""

' -- Колонка #s. ПРИМЕЧАНИЕ. На практике, например, "Col5Header" будет, например, "ProductID", а "Col9Header" будет, например, "SalesID".
Private Const Col5HeaderColumnNumber As Integer = 5
Private Const Col9HeaderColumnNumber As Integer = 9

' -- Имена диапазонов раскрывающихся списков столбцов
Private Const Col5HeaderDropDownRangeName as String = "col5HeaderDropDownRangeName"
Private Const Col9HeaderDropDownRangeName as String = "col9HeaderDropDownRangeName"

Sub Worksheet_Change _
( _
ByVal Target As Range _
)

' -- BEGIN Для применимых столбцов = извлеките идентификатор для описания, выбранного из раскрывающихся списков.
Dim dropDownListRangeName как строка
dropDownListRangeName = ПустаяСтрока
Выберите Case Target.Column
Дело Col5HeaderColumnNumber
dropDownListRangeName = Col5HeaderDropDownRangeName
Дело Col9HeaderColumnNumber
dropDownListRangeName = Col9HeaderDropDownRangeName
End Select ' -- Case Target.Column
Если (dropDownListRangeName <> EmptyString) Тогда
Затемнить selectedId как строку
selectedId = Application.VLookup(selectedNa, ActiveSheet.Range(dropDownListRangeName), 2, False)
Если Не Ошибка(выбранныйId) Тогда
Target.Value = selectedId
End If ' -- Not IsError (selectedId)
End If ' -- (dropDownListRangeName <> EmptyString)
-- END Для применимых =columns вставьте идентификатор для описания, выбранного из раскрывающихся списков.

End Sub
Этот комментарий был сведен к минимуму модератором на сайте
Нет ничего более разочаровывающего, чем ввести подробный вопрос только для того, чтобы его сдуло. Если вы введете неправильный 6-значный код для подтверждения своего человека, опубликованное сообщение будет удалено. Может захочется это исправить. Теперь мой комментарий таков: я пытался сделать то же самое, что вы показали в видео и письменных инструкциях, и все, что я получаю, это то, что когда я выбираю имя в списке, это имя, а не номер. Кроме того, как это вообще работает, поскольку проверка данных должна ограничивать выбор только тем, что есть в списке. Как это обманывает систему? Раньше мне всегда приходилось назначать код vba кнопке или ярлыку, как этот код активируется? Как вы тестируете, чтобы убедиться, что он работает?
Этот комментарий был сведен к минимуму модератором на сайте
Как работает формула, когда вы хотите добавить данные на отдельный лист рабочей книги? Я хочу скрыть данные.
Этот комментарий был сведен к минимуму модератором на сайте
Меняй здесь, бро!
selectedNum = Application.VLookup(selectedNa, Worksheets("YourSheetName").Range("dropdown"), 2, False)
Этот комментарий был сведен к минимуму модератором на сайте
«YourSheetName» ссылается на лист, содержащий диапазон данных, или на лист, на котором я хочу использовать список dropdwon?
Этот комментарий был сведен к минимуму модератором на сайте
Как работает формула, когда вы хотите перечислить данные на отдельном листе/вкладке в книге?
Этот комментарий был сведен к минимуму модератором на сайте
это не работает в текущих версиях excel-устаревших. Проверка данных, а затем список больше не отображается в vba, поскольку объект excel уже пытался несколько раз, и он не отображается.
Этот комментарий был сведен к минимуму модератором на сайте
В этом примере, что, если вы хотите, чтобы он просматривал значение в каждой из ячеек в 5, но поместил значение в соседнюю ячейку в 6
Этот комментарий был сведен к минимуму модератором на сайте
Как код должен измениться, если я хочу создать ссылку/ссылку в E1 на источник раскрывающегося списка на основе выбранного значения?
Выгода будет заключаться в том, что в случае изменения выпадающего источника (например, «Хенрик» => «Хендрик» изменение автоматически отразится в E1.
Этот комментарий был сведен к минимуму модератором на сайте
Кто-нибудь знает, как заставить это работать в таблицах Google?
Этот комментарий был сведен к минимуму модератором на сайте
я хочу выбрать несколько вариантов из выпадающего списка.
результат такой: AA1001,BB1002
Возможно ли это?
Этот комментарий был сведен к минимуму модератором на сайте
Вы нашли решение?
Этот комментарий был сведен к минимуму модератором на сайте
Si los datos de la lista están en otra hoja, cuál sería el código? Грасиас.
Этот комментарий был сведен к минимуму модератором на сайте
cómo buscar un valor hacia la izquierda
Этот комментарий был сведен к минимуму модератором на сайте
Private Sub Worksheet_Change (ByVal Target As Range)
selectedNa = Target.Value
Если Target.Column = 5 Then

Sheets("Nombre de la hoja en donde esta la lista").Активировать
selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("раскрывающийся список"), 2, False)
Sheets("Nombre de la hoja en donde estas trabajando").Активировать
Если Не Ошибка(выбранноеЧисло), Тогда
Target.Value = selectedNum
End If
End If
End Sub
Этот комментарий был сведен к минимуму модератором на сайте
Кто-то знает, как искать значение справа налево
Этот комментарий был сведен к минимуму модератором на сайте
Мне нужно использовать одно и то же раскрывающееся меню более чем в одном столбце, какой будет код?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуй!
Это действительно полезно! Благодарю вас!
Я запускаю ситуацию, когда ячейка не обновляется автоматически или при использовании функции обновления. Мне нужно щелкнуть другую ячейку, а затем снова щелкнуть ячейку в работе, чтобы отобразить значение.
В настоящее время я работаю в Office Standard 2019. Кто-нибудь знает, связана ли эта проблема с версией Excel, которую я использую?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Код работал нормально, если мы определяем список и создаем раскрывающийся список на том же листе.
Но как нам добиться определения списка значений и кодов на одном листе и раскрывающегося списка, созданного на другом листе?
Этот же код не работает, как показано, и ошибка в этой строке ("selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("выпадающий список"), 2, False)").
Кроме того, у меня есть требование, например, если у меня есть несколько списков, определенных на одном листе с идентификатором и именами, и несколько раскрывающихся списков на другом листе, где одно раскрывающееся значение зависит от выбранного значения в другом раскрывающемся списке.

Надеюсь, вы поняли мой запрос.

Пожалуйста, помогите мне в решении этой проблемы.
Этот комментарий был сведен к минимуму модератором на сайте
Это мне очень помогло, спасибо. Поскольку моя таблица находилась на другом листе, чем мой список, я добавил пару строк кода, чтобы сделать это, а также чтобы экран не мигал.

Приложение.ScreenUpdating = False
Листы("ЛистСТаблицеОнит").Активировать

Sheets("SheetWithDropDownListOnIt").Активировать
Application.ScreenUpdating = True
Этот комментарий был сведен к минимуму модератором на сайте
где именно вы добавили эти коды?
Этот комментарий был сведен к минимуму модератором на сайте
Я получаю ошибку компиляции: синтаксическая ошибка в строке «Если Trarget.Column = 6 Then», когда я пытаюсь использовать код? Есть идеи, почему?
Этот комментарий был сведен к минимуму модератором на сайте
Он работает, но когда вы выходите из файла и открываете его снова, он не работает ... его нельзя сохранить как .xls, только как .xlsm. Есть ли какое-либо решение для этого? Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Марко, после копирования и вставки кода в книгу, когда вы сохраняете файл, вы должны сохранить его в формате книги Excel с поддержкой макросов, пожалуйста, попробуйте, спасибо!
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL