Как сделать выпадающий список в excel с несколькими условиями

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​ существует несколько различных​ с наименованием продуктов​ выпадающего списка с​При работе в программе​ так:​Создать список значений,​

​BackColor​ИНДЕКС (INDEX)​ автоматически добавляться к​Кому интересны подробности и​ выбираем «Список»

А​Внимание!​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​ список стран:​ страну, а в​ поставьте напротив него​ активируйте строку с​ вариантов того, как​ и мер измерения.​ помощью инструментов разработчика,​ Microsoft Excel в​

​Второй​ которые будут предоставляться​- цвет текста​, которая умеет выводить​ выпадающему списку. Еще​ нюансы всех описанных​ в строке «Источник»​

​В списках названия​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​=Sheet3!$A$3:$A$5​ ячейке​ галочку. После этого​ названием «Присвоить имя…».​ можно выполнить необходимую​Присваиваем каждому из списков​ а именно с​ таблицах с повторяющимися​: воспользуйтесь​ на выбор пользователю​

​ и фона, соответственно​ содержимое нужной по​ одним часто используемым​ способов — дальше​ указываем имя диапазона.​ столбцов (В, С,​Что же делает эта​Нажмите​

​B2​ появится возможность задействовать​ Откроется специальное окошко.​ операцию.​ именованный диапазон, как​ использованием ActiveX. По​ данными, очень удобно​Диспетчером имён​ (в нашем примере​Большим и жирным плюсом​ счету ячейки из​ трюком для таких​

​ по тексту.​ Например, «=Наименование_товара».​ D) должны полностью​ формула? Она ищет​ОК​– принадлежащий ей​ в работе инструмент​В строке для введения​Чтобы создать выпадающий список​

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

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

​ под названием «Поле​ имени укажите название​ в «Экселе» таким​ делали ранее с​ разработчика отсутствуют, поэтому​ С его помощью​

​ 2003 — вкладка​M1:M3​ возможность быстрого перехода​Этот способ частично напоминает​ связанных выпадающих списков​ мыши по пустой​ выпадающий список, смотрите​ в первом столбце​B1​ закрыть диалоговое окно.​ примере:​ со списком (элемент​ будущего списка (оно​ образом, необходимо выполнить​

​ обычными выпадающими списками.​ нам, прежде всего,​ можно просто выбирать​ «​), далее выбрать ячейку​

​ к нужному элементу​ предыдущий. Основное отличие​ (когда содержимое одного​ ячейке под столбцом​

​ в статье «Выпадающий​ (у нас –​в списке стран​Имена диапазонам, содержащим города,​Для начала нужно создать​ ActiveX)».​ будет в дальнейшем​

​ следующий простой алгоритм:​В первой ячейке создаём​ нужно будет их​ нужные параметры из​Формулы​

​ в которой будет​ в списке при​

​ в том, что​ списка меняется в​ с данными, команда​ список в Excel».​ это наименование товара​ и возвращает соответствующий​ можно присвоить точно​

Связанные списки

​ базу данных. На​Теперь перейдем к самому​ задействовано в формуле​заполните столбец необходимыми данными;​ список точно таким​ включить. Для этого,​ сформированного меню. Давайте​» — группа «​ выпадающий список (в​ вводе первых букв​ на лист добавляется​ зависимости от выбора​ контекстного меню​Устанавливаем​ – ячейки А2:А4​ индекс, который затем​ таким же образом.​

​ втором листе я​ процессу реализации списка:​ для подстановки). Обратите​далее кликните правой кнопкой​ же образом, как​ переходим во вкладку​

​ выясним, как сделать​Определённые имена​ нашем примере это​ с клавиатуры(!), чего​ не элемент управления,​

​ в другом).​Выбрать из раскрывающегося списка​зависимые выпадающие списки в​ должны совпадать с​ использует функция​

​Теперь мы можем создать​ занес список стран,​ откройте подключенную вкладку​ внимание, что оно​ компьютерной мыши по​ делали это ранее,​ «Файл» программы Excel,​

​ раскрывающийся список различными​

​»), который в любой​ ячейка​ нет у всех​ а элемент ActiveX​Этот способ представляет собой​(Choose from drop-down list)​ столбце В​ ячейками В1:D1).​

​CHOOSE​

​ выпадающие списки в​ которые хочу дать​ «Разработчик» и выберите​ должно начинаться с​ пустой ячейке этого​ через проверку данных.​ а затем кликаем​ способами.​ версии Excel вызывается​К1​ остальных способов. Приятным​»Поле со списком»​ вставку на лист​

​или нажать сочетание​

lumpics.ru>

Инструмент «Заполнить»

В программе имеется отдельный инструмент, находящийся во вкладке «Главная» в разделе «редактирование». Последовательность действий:

1. В любое место таблицы ввести слово, выделить его и диапазон, который требуется заполнить.
2. Кликнуть на кнопку «заполнить» и выбрать направление.
3. После проведения манипуляций значение из первой ячейки скопируются в другие. Это позволит заполнить ячейки одинаковыми данными.

Данный инструмент также позволяет заполнить элементы прогрессией. Как это сделать:

1. Поставить число и выделить область, которую нужно заполнить. Нажать на кнопку «заполнить» и кликнуть на «прогрессия».
2. В открывшемся окошке появятся настройки автозаполнения. Провести ряд действий и задать условия: выбрать месторасположение прогрессии, тип, шаг (по умолчанию стоит 1), указать предельное значение. После настроек щелкнуть на ОК.
3. Выделенная область будет заполнена согласно введенным настройкам по правилам прогрессии.

Автозаполнение формулами

Формулы являются одним из основных инструментов Эксель. Для того чтобы скопировать их, нужно следовать инструкции:

1. Выбрать ячейку и напечатать в ней формулу.
2. Навести курсор мышки на выделенный элемент, и поместить его в нижний правый угол. Появится плюсик, два раза кликнуть на него для автозаполнения формул.

Заполнение различными значениями

В качестве примера рассмотрено автозаполнение таблицы списками месяцев. Последовательность действий:

1. Установить курсор в А1 и напечатать «январь». Кликнуть на Enter или поставить галочку на панели инструментов.
2. Навести курсор на правый угол внизу ячейки.
3. Сразу появится черный крестик, тут же нажать на левую кнопку мыши и потянуть ее к А12. В результате образуется рама.
4. Отпустить кнопку, выделенная область заполнится названиями месяцев.

Добавление своего списка

Если возникла необходимость, пользователь может использовать автозаполнение в Эксель из собственного списка данных. В этом случае при вводе в элемент слова, находящегося в перечне, после применения маркера заполнится весь диапазон. Для создания и сохранения списка нужно следовать инструкции:

1. Зайти во вкладку «файл».
2. Перейти в раздел параметров.
3. Кликнуть на «дополнительно».
4. В общих настройках автозаполнения в середине окна нажать на кнопку «изменить списки».
5. Появится окошко, в котором слева указаны имеющиеся списки. Для добавления своего нужно напечатать слова в поле «элементы списка». Каждое значение должно располагаться с новой строчки. В конце нажать на «добавить».
6. Потом закрыть окно и снова его открыть. Пользователь увидит свои названия в списке активных.

После ввода в таблицу слова из созданного перечня нужно протянуть маркер. Выделенная область автоматически заполнится нужными значениями.

Используем маркер автозаполнения

Чтобы увидеть данный инструмент, нужно навести указатель мышки на правый угол внизу выбранной ячейки.

Как только появится маленький черный крестик, нужно зажать его левой клавишей мыши и потянуть в ту сторону, в которой должны располагаться данные.

Мгновенное заполнение

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

1. Начать печатать сведения. Когда функция установит закономерность, под выделенной ячейкой отобразится примерный список вариантов.
2. Щелкнуть на Enter, в итоге данные добавятся в таблицу.

Для отмены или изменения действия, кликнуть на смарт-тег, появившийся рядом с заполненными именами.

Инструментом автозаполнения в Экселе пользоваться удобно и полезно. Человек сэкономит свое время и силы при вводе в таблицу повторяющихся данных. Достоинством является то, что инструмент можно настраивать, добавлять списки, менять имеющиеся.

голоса

Рейтинг статьи

Использование инструментов от разработчика

Альтернативный вариант создания списка в экселе – это использование ActiveX. В сравнении с первым решением, инструкция немного сложнее, поскольку список опций несколько шире: разрешено задавать большое количество элементов, менять внешний вид списков.

Инструкция по использованию инструментов от разработчика следующая:

1. Активировать опции, поскольку они автоматически отключены. Для этого нужно перейти в меню «Файл».

2. В списке слева найти меню «Параметры» и кликнуть по нему.

3. Открыть раздел с настройками ленты и перейти к «основным вкладкам», поставить галочку напротив разработчика. Остается кликнуть на ОК, чтобы сохранить опции.

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

5. Перейти в «Разработчик». В подпункте элементов управления кликнуть на кнопку вставки. В появившемся окне выбрать опцию «элементы ActiveX» и кликнуть на значок «Поле со списком».

6. Нажать на нужную ячейку, чтобы появилось окно со списком. По желанию можно настроить параметры и границы. При выделении списка, на панели инструментов появится «режим конструктора». Нажать на «Свойства», если требуется продолжить настройку.

7. В параметрах найти ListFillRange. В столбике поставить рядом «:» и описать координаты ячеек, чтобы создать определенный диапазон. Закрыть окно.

8. Правой кнопкой мышки кликнуть на список в окне, выбрать «объект ComboBox» и кликнуть на edit.

9. Должен появиться список с заранее заданным параметрами.

10. Если требуется сделать вставку списка в несколько ячеек, но понадобится навести курсор в правой части нижнем углу страницы. Далее зажать левую кнопку мыши и потянуть вниз до строки, где должен присутствовать список.

На этом миссию можно считать выполненной. Список будет отображаться в заранее заданных ячейках.
Как сделать связанный список

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

• минимальное количество столбиков – три. В первом указываются наименования, второй и третий – для единиц или количества. При необходимости можно задавать большее количество столбиков;

• для начала создается один, общий список для всех названий. Выделяются ячейки с наименованиями посредством контекстного меню;

• задать определенное имя;

• по аналогии формируются отдельные списки для каждого наименования с определением количества единиц;

• далее потребуется вставить общий список с наименованиями в верхней ячейке в первом столбике главной страницы;

• указать в качестве основного источника ранее заданную таблицу;

• кликнуть по верхней части столбика, где указаны единицы измерения, зайти в проверочное окно и в источнике указать «=ДВССЫЛ(A2)», вместо А2 может быть любая ячейка с наименованием продукта;

• список готов. Теперь можно растянуть по желанию строки и таблицы.

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

Как нам это может пригодиться?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.

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

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список, размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10. Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник.xlsx), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

При работе с перечнем элементов, расположенным в другой книге, файл Источник.xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки.

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных, то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список, содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список.

Зависимые раскрывающиеся списки

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

1. Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать». Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»

   Нажимаем «Создать»

2. Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.

   Пишем имя «Деревья», нажимаем «ОК»

3. По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать. В данном примере это ещё два диапазона: «Кустарники» и «Травы».

   Создаем таким же способом остальные диапазоны

4. Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте. В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»

   Выпадающий список с названием диапазона ячеек

5. Теперь вам нужно создать дополнительный раскрывающийся список по той же схеме. В этом списке будут отражаться те слова, которые соответствуют заголовку. Например, если вы выбрали «Дерево», то это будут «береза», «липа», «клен» и так далее. Чтобы осуществить это, повторите вышеуказанные шаги, но в поле ввода «Источник» введите функцию «=ДВССЫЛ(E1)». В данном случае «E1» – это адрес ячейки с именем первого диапазона. По такому же способу вы сможете создавать столько взаимосвязанных списков, сколько вам потребуется. В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»

   Результат выпадающего связанного списка

Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль. Private Sub Worksheet_Change( ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) 0 And oldval newVal Then Target = Target & «,» & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Процесс создания списка

Чтобы сгенерировать всплывающее меню, следует кликнуть на пункты меню по пути «Данные» – «Проверка данных». Откроется диалоговое окошко, где надо найти вкладку «Параметры» и нажать по ней, если она до этого еще не была открыта. В нем есть множество настроек, но нам важен пункт «Тип данных». Из всех значений «Список» – это то, что надо.

1

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

1. Самостоятельное указание элементов списка через точку с запятой в поле «Источник», расположенного на той же вкладке того же диалогового окна.
2. Предварительное указание значений. В поле «Источник» содержится диапазон, где имеется необходимая информация.
3. Указание именованного диапазона. Метод, повторяющий прошлый, но только необходимо предварительно назвать диапазон.

Любой из этих методов выдаст требуемый результат. Давайте разберем методы генерации раскрывающихся перечней в реальных ситуациях.

На основе данных из перечня

Допустим, у нас есть таблица, описывающая виды разных фруктов.

5

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

Выбрать ячейку, отведенную под будущий перечень.

На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных».

Найти пункт «Тип данных» и переключить значение на «Список».

В поле, обозначающем опцию «Источник», ввести нужный диапазон

Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась.

Кроме этого, предусмотрена функция генерации списков сразу больше, чем в одной ячейке. Чтобы этого добиться, следует выбрать их все, и совершить аналогичные описанным раньше действия. Снова необходимо удостовериться, что записаны абсолютные ссылки. Если в адресе нет значка доллара возле названия колонки и ряда, то нужно их добавить путем нажатия клавиши F4 до тех пор, пока возле названия столбца и ряда не будет стоять знак $.

С ручной записью данных

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

Допустим, перед нами стоит задача создать список, содержащий два возможных варианта выбора: да и нет. Чтобы реализовать поставленную задачу, необходимо:

1. Нажать по ячейке, отведенной под перечень.
2. Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
3. Снова выбираем тип «Список».
4. Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

11

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

Как выбрать несколько значений из выпадающего списка?

Иногда нет возможности отдать предпочтение только одному значению, поэтому надо выбрать больше одного. Тогда надо добавить в код страницы макрос. С использованием комбинации клавиш Alt + F11 открывается редактор Visual Basic. И туда вставляется код.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Е2:Е9”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(0, 1)) = 0 Then

            Target.Offset(0, 1) = Target

        Else

            Target.End(xlToRight).Offset(0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Чтобы содержимое ячеек показывались внизу, вставляем в редактор такой код.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Н2:К2”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(1, 0)) = 0 Then

            Target.Offset(1, 0) = Target

        Else

            Target.End(xlDown).Offset(1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Ну и наконец, для записи в одной ячейке используется этот код.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“C2:C5”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        newVal = Target

        Application.Undo

        oldval = Target

        If Len(oldval) <> 0 And oldval <> newVal Then

            Target = Target & “,” & newVal

        Else

            Target = newVal

        End If

        If Len(newVal) = 0 Then Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Диапазоны редактируемы.

Как создать выпадающий список с гиперссылками в Excel?

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

Создать раскрывающийся список с гиперссылками с помощью формулы

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

После создания раскрывающегося списка введите следующую формулу: = ГИПЕРССЫЛКА (D2, «Перейти на веб-сайт!») (D2 — ячейка раскрывающегося списка) в ячейке E2, которая находится рядом с ячейкой раскрывающегося списка, и нажмите Enter Затем, когда вы выберете одну гиперссылку URL из раскрывающегося списка и щелкните ячейку с формулой, гиперссылка будет открыта.

Создать раскрывающийся список с гиперссылками с помощью поля со списком

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

Сначала создайте два диапазона имен для списка гиперссылок и связанной ячейки.

1. Дайте вашему списку гиперссылок название диапазона «Гиперссылки»И назовите пустую ячейку«Linked_cell», Которая является связанной ячейкой значения поля со списком в Имя Box, смотрите скриншоты:

Во-вторых, создайте поле со списком и отформатируйте элемент управления.

2. Нажмите разработчик > Вставить > Поле со списком, и перетащите курсор, чтобы нарисовать поле со списком по мере необходимости.

Советы: Если нет разработчик вкладка в вашей ленте, прочтите эту статью Как отобразить вкладку разработчика в ленте Excel 2007/2010/2013? для его активации.

3. После вставки поля со списком щелкните его правой кнопкой мыши и выберите Управление форматом, В Управление форматом диалоговое окно, нажмите Управление вкладка и введите Гиперссылки и Linked_cell которые представляют собой имена диапазонов, которые вы создали на шаге 1 в Диапазон ввода и Сотовая ссылка текстовые поля отдельно, см. снимок экрана:

4. Затем нажмите OK кнопку, чтобы завершить настройки, щелкните правой кнопкой мыши поле со списком и выберите Назначить макрос из контекстного меню в Назначить макрос диалоговое окно, нажмите НОВИНКИ кнопку, см. снимок экрана:

5. В выскочившем Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код между скриптами, см. снимок экрана:

Код VBA: активируйте гиперссылки из поля со списком:

HyperLink_Index = Range("Linked_cell")
      If Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Name <> "" Then
           Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If

6. Затем сохраните этот код, и теперь, когда вы выберете одну гиперссылку из поля со списком, гиперссылка откроется автоматически.

Демонстрация: создание раскрывающегося списка с гиперссылками в Excel

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

От: admin

Эта тема закрыта для публикации ответов.