0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Выпадающий список в Excel с помощью инструментов или макросов

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

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

Создание раскрывающегося списка

Вы найдете его, выбрав меню «Данные» — инструмент «Валидация данных» — вкладка «Параметры». Тип данных — «Список».

Создание выпадающего списка.

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

  1. Вручную через «точку-с-запятой» в поле «Источник». Ввод значений.
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком. Проверка вводимых значений.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов приведет к одному и тому же результату.

Выпадающий список в Excel с подстановкой данных

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

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу». Форматировать как таблицу.
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона: Выпадающий список.
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Давайте проверим это. Вот наша таблица со списком на одном листе:

Список и таблица.

Давайте добавим в таблицу новое значение «Рождественская елка».

Добавлено значение елка.

Теперь удалите значение «берёза».

Удалено значение береза.

Мы смогли воплотить эту идею в жизнь с помощью «умного стола», который легко расширяется и изменяется.

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

  1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК. Создание имени.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения. Сообщение об ошибке.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов». Сообщение об ошибке.
  6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».
Читайте так же:
Как быстро создать вафельную диаграмму в Excel?

Выпадающий список спрашивает: «Можно ли добавить в выпадающий список введенное имя baobab?», когда имя вводится в пустую ячейку.

После нажатия кнопки «Да» появится еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

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

  1. Активируйте ячейку, в которой нужно разместить выпадающий список.
  2. Откройте настройки управления данными. В поле «Источник» введите формулу: =DVSource(«[List1.xlsx]Sheet1!$A$1:$A$9»).

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

Как сделать зависимые выпадающие списки

Возьмем три ряда с именами :

Три именованных диапазона.

Это необходимое условие. Вот как назвать список (с помощью «Менеджера имен»). Убедитесь, что имя не содержит пробелов или знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов. Список диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки. Таблица со списком.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона. Второй раскрывающийся список.

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

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

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

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

Выпадающий список с поиском

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки). Вставить ActiveX.
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка. Элемент ActiveX.
  3. Жмем «Свойства» – открывается перечень настроек. Свойства ActiveX.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Динамическая диаграмма в Excel лого

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

Описание проблемы

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

Таблица данных

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

Создание динамической диаграммы

Первым шагом будет создание выпадающего меню, из которого мы выберем нужный нам индикатор. Щелкните на вкладке Developer в группе Controls и выберите Insert -> Form Controls -> List Field.

Поле со списком

При нажатии на значок «Поле со списком» ваш курсор превратится в значок перекрестия, что означает, что Excel готов указать, где разместить элемент управления. Поместите раскрывающийся список в нужное место на рабочем листе. Изначально поле списка выглядит как большое квадратное поле с треугольником внутри. Установите нужные размеры элемента, используя маркеры по краям элемента.

Щелкните правой кнопкой мыши по раскрывающемуся списку и выберите Format Object (Форматировать объект). В появившемся диалоге Format Controls укажите диапазон ячеек, из которых будет сформирован список (в данном случае список всех показателей, для которых мы собираемся создать график), а также ячейку, в которую будет помещен результат выбора списка.

Формат объекта

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

Затем перейдите на вкладку Формулы в группе Определенные имена, а затем выберите Менеджер имен и создайте два диапазона со значением и именем, а также формулы для их соответствия.

Диспетчер имен

Затем в рабочем листе с таблицей данных выделите диапазон A1:H2, перейдите на вкладку Вставка в группе Диаграммы и выберите Диаграмма с областями. С одной строкой данных Excel построил нам диаграмму.

Диаграмма с областями

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

Формула РЯД

Измените значения первого и третьего параметров на уже подготовленные именованные диапазоны

=РЯД(ДинамДиагр! $A$2 ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! $B$2:$H$2 ;1)

Вот как все должно быть:

=РЯД(ДинамДиагр! название ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! значения ;1)

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

Динамическая диаграмма

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

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

Название диаграммы

Динамическая схема подготовлена.

Вам также могут быть интересны следующие статьи

15 комментариев

есть подозрение, что формула смещения должна быть =СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)
а в формате объекта «поле со списком» должна быть связь с ячейкой A16….иначе фокус не удается…..

Динамический выпадающий список в EXCEL

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

Динамичность» динамического выпадающего списка заключается в том, что после ввода значения в столбец выпадающего списка список изменяется — введенное значение исчезает из выпадающего списка. Таким образом, динамический выпадающий список может обеспечить ввод в диапазон только неповторяющихся значений (см. файл примера).

Алгоритм решения проблемы выглядит следующим образом:

  • В рабочем листе Список создайте начальный выпадающий список, например, список сотрудников компании
  • В рабочем листе Динамический список определите диапазон, в который следует ввести сотрудников, например, тех, кто номинирован на премии. Диапазон должен содержать имена, которые не повторяются (иначе кто-то получит 2 или 3 бонуса! ), и все сотрудники должны быть из компании (иначе кто-то другой получит бонус!)
  • Создайте модифицированный список сотрудников в рабочем листе List, исключив имена, уже введенные в рабочий лист DynamicList;
  • Создайте простой выпадающий список, используя модифицированный список сотрудников, созданный на предыдущем этапе.
  • На листе Список создайте начальный список имен сотрудников в столбце A. Введите название — Сотрудники.

  • В рабочем листе DynamicList область, в которую нужно ввести имена сотрудников, определяется с помощью выпадающего списка («Премиум инвентарь»). Это диапазон A3:A16 .
  • На рабочем листе Список, в столбце B, для каждого значения в исходном списке введите формулу = IF(COUNT(DynamicList!$A$3:$A$16;A2);»»;STRING())

В формуле, если в A3:A16 на листе DynamicList уже введены фамилии, значением будет Пустой текст («»). Если значение не найдено, отображается номер строки, в которой находится формула;

  • На листе Список , в ячейке С2 , введем Формулу массива = ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$9; НАИМЕНЬШИЙ($B$2:$B$9;СТРОКА(1:1))-СТРОКА($A$1));»») После ввода вместо ENTER нажмите CTRL+SHIFT+ENTER .

Рассмотрим формулу более подробно. Столбец B сортируется по возрастанию функцией LOWEST(), и она выводит значение для каждой строки. На основе результата функции LARGE() функция INDEX() извлекает фамилии из исходного списка. Отсчитайте исходный список сотрудников, скопировав формулу. Таким образом, в столбце C получится список фамилий, еще не внесенных на лист DynamicList. Именно это и является нашей целью. Когда вы нажимаете F9, промежуточный результат легко увидеть (например, вы выбираете INCOME($B$2:$B$9;ARROW(1:1)) и нажимаете F9 для отображения результата).

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

  • На вкладке Формулы в группе Определенные имена выберите команду Назначить имя;
  • В поле Имя введите: Сотрудники;
  • В поле Область введите Книга;
  • В поле Диапазон введите формулу.

Если вы хотите получить числовое значение, введите =MEMBERSY(List!$C$2;;SUMMproV(—NUMBER(List!$C$2:$C$29))).

  • Нажмите ОК.

И последнее, но не менее важное — создайте динамический выпадающий список:

  • На листе DynamicList выберите диапазон, в котором будут введены значения, используя динамический выпадающий список ( A3:A16 ) ;
  • Вызовите инструмент проверки данных ( Data / Working with data / Validate data ), установите тип данных List, в поле Source определено = Employees.

Давайте проверим наше решение. На листе DynamicList с помощью раскрывающегося списка выберите фамилию Сидоров.

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

Динамический раскрывающийся список — это вариант связанного списка.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector