Ref-sgain.ru

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

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

Раскрывающийся список в Excel

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

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

Удобный интерфейс Microsoft Excel предоставляет несколько вариантов помощи при вводе повторяющихся данных в ячейки рабочего листа.

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

Вариант №0 — «Элементарный».

Фрагмент №0 листа Excel с примеромДелая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.

У этого метода есть один «недостаток». Пользователь иногда должен вводить несколько букв, и нет возможности заранее создать каталог имен, что ограничивает использование этого метода.

Теперь перейдем непосредственно к опциям создания выпадающих списков.

Вариант №1 — «Простейший».

Фрагмент №1 листа Excel с раскрывающимся спискомЕсли активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.

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

Вариант №2 — «Простой».

Фрагмент №2 листа Excel с раскрывающимся списком

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

В этом варианте для создания выпадающего списка необходимо выполнить ряд шагов

1 Создайте список возможных значений и храните их в столбце, по одному в каждой ячейке. Предположим, что это список в ячейках A2…A8.

2. Установив курсор в ячейку, в которой вы хотите разместить выпадающий список, вы можете активизировать эту ячейку. Пусть это будет та же ячейка A9.

Выберите кнопку «Данные» — «Управление…». в главном меню.

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

5. Если в поле «Тип данных:» выбрать «Список» (аналогично тому, что мы создаем), значение будет добавлено на лист.

6. Выберите диапазон в появившемся поле «Источник:».

7. Включите (если по умолчанию не установлен) флажок «Список допустимых значений» и нажмите кнопку «OK».

Выпадающее окно Excel "Проверка вводимых значений"

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

Вариант №3 — «Сложный».

Фрагмент №3 листа Excel с раскрывающимся списком

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

С помощью этого метода вы можете создать выпадающее меню.

Читайте так же:
Как скрыть строки с нулевым значением в Excel?

1. создайте список ссылок в ячейках A2…A8.

2. Выберите в главном меню кнопку «Вид» — «Панели инструментов» — «Формы».

3. В появившейся панели «Формы» выберите «Окно со списком» и нарисуйте его, например, в ячейке A9.

Панель Excel "Формы"

В отличие от «поля списка», этот элемент размещается не в самой ячейке, а над ней. Большой элемент может быть размещен над несколькими ячейками.

4. Щелкните правой кнопкой мыши на нарисованном элементе и выберите Формат объекта.

5. В появившемся окне «Format Object» на вкладке «Control Element» заполните поля, как показано ниже, и нажмите «OK».

Выпадающее окно Excel "Форматирование объекта"

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

Функцию INDEX можно использовать для вывода значения из списка в ячейку. Предположим, что нам нужно вывести значение в ячейку A9, часть элемента «Поле со списком».

Для этого введите формулу в ячейку A9: =ИНДЕКС(A2:A8;C9).

Подробный пример вы можете найти в статье «Окно списка и функция ИНДЕКС в Excel». Вы можете нажать на ссылку, чтобы прочитать его.

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

Вариант №4 — «Самый сложный».

Фрагмент №4 листа Excel с раскрывающимся спискомДля создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.

1. В главном меню выберите «Вид» — «Панели инструментов» — «Элементы управления».

2. В появившейся панели «Controls» выберите «List box» и нарисуйте его в ячейке A9. Элемент ActiveX «List box» размещается не в самом поле, а поверх него, закрывая его.

Панель Excel "Элементы управления"

Выпадающее окно Excel "Properties"3. Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.

4. Среди других параметров, которые можно изменить, — шрифт, цвет и цвет фона. Использование опции «Самое сложное» не представляет собой ничего сложного — вы убедитесь в этом сами. Все интуитивно понятно, хотя базовое понимание английского языка не помешает!

5. Отжимаем кнопку «Выход из режима конструктора» на панели «Элементы управления» и проверяем работу раскрывающегося списка. Все работает! Выбранное значение записано в ячейку А9, в нашем примере — под элементом «Поле со списком». Вообще связанной ячейкой может быть абсолютно любая кроме ячеек, где располагается базовый список.

Итоги.

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

На практике я чаще всего создаю раскрывающиеся списки в Excel, используя варианты №1 и №3, реже — вариант №2 и совсем редко — вариант №4, хотя именно он является, безусловно, самым гибким, предоставляющим самые широкие возможности.

Читайте так же:
Как скопировать выбранный диапазон в новую книгу в Excel?

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

Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы и не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку «Спам» — все зависит от настроек вашей почты).

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

Статьи с близкой тематикой

Отзывы

12 комментариев на «Раскрывающийся список в Excel»

    Михаил 19 Мар 2014 08:58

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

Добрый день, Майкл!

Я никогда этого не делал, но думаю, что это можно сделать с помощью программирования VBA.

Какую задачу вы решаете? Я предпочитаю «сложное» разбивать на «простое» и не использовать макросы до последнего. Даже без VBA возможности Excel огромны.

Интересна реализация функции «Если—то». Полезна при расчете однотрубной горизонтальной системы отопления.

Для расчетов я часто использую Exel, потому что он предлагает так много возможностей. Выпадающий список опций — очень полезная информация. Я как раз искал информацию об этой опции. Автору респект. (teplosniks.ru)

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

Вы можете попробовать сделать это с помощью формул или VBA.

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

Пример: на «заказе» у нас есть форма, из выпадающего списка мы выбираем компанию ООО «Стелс», (на листе 2 у нас есть в столбце a название компании, в столбце B адрес, в столбце C ИНН, в столбце D адрес). Как сделать так, чтобы при выборе значения из выпадающего списка, в других ячейках появлялись соответствующие значения. ?

Артур, добрый день.

Вы можете сделать это так, как описано здесь.

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

Эмиль, здравствуйте. В настоящее время в Excel 2003 (который я использую) эта функция отсутствует. Вы можете попробовать реализовать ее только с помощью VBA. Возможно, эта функция была добавлена в новые версии Excel. Посмотрите также надстройку PLEX и вообще весь сайт Николая Павлова planetaexcel.ru

Читайте так же:
Как создать кольцевую диаграмму в Excel?

Привет, Алехандро!

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

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

Как создать в ячейке таблицы Excel раскрывающийся список

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

На рисунке 45.1 показан пример. 45.1 показан пример. Когда выбрана ячейка B2, появляется стрелка вниз. Нажмите на кнопку со стрелкой, и вы получите список элементов (в данном случае названия месяцев). Нажмите на элемент, и он появится в ячейке. Выпадающий список может содержать текст, числовые значения или даты. Разумеется, ваши формулы могут ссылаться на ячейки, содержащие выпадающий список. В формулах всегда используется текущее отображаемое значение.

Рис. 45.1. Создать раскрывающийся список в ячейке легко и без использования макросов

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

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

  1. Введите список элементов в диапазоне. В данном примере названия месяцев находятся в диапазоне E1:E12 .
  2. Выберите ячейку, в которой будет находиться выпадающий список (в данном примере ячейка B2).
  3. Выберите данные ► Манипулируйте данными ► Проверьте данные. Откройте вкладку Настройки диалогового окна Проверка входных данных. В списке Тип данных выберите Список.
  4. В поле Источник укажите диапазон, который содержит элементы. В данном примере диапазон E1:E12 .
  5. Убедитесь, что установлен флажок Список допустимых значений (рис. 45.2) и нажмите OK.

Если ваш список короткий, вы можете обойтись без шага 1. В этом случае просто введите элементы списка (разделенные запятыми) в поле Source окна Validate entries.

Рис. 45.2. Использование окна Проверка вводимых значений для создания раскрывающегося списка

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

Раньше элементы списка должны были находиться на том же листе, что и выпадающий список. В Excel 2010 и 2013 этой проблемы нет.

Если вы планируете поделиться рабочей книгой с другими пользователями, использующими более старые версии Excel, убедитесь, что значения находятся на том же листе, что и выпадающий список. Вы также можете разместить список на любом листе, если это именованный диапазон. Например, вы можете выбрать Формулы ► Определенные имена ► Присвоить имя, чтобы определить имя MonthNames для E1:E12 . Затем введите =MonthNames в поле Source диалогового окна Check Input Values.

Работа со списками данных в Excel

Spisok dannih 1 Работа со списками данных в Excel

Приветствую вас, дорогой читатель!

Сегодня я хочу поговорить об одной из основных возможностях — это работа со списками данных в Excel. К самим спискам можно отнести практически любые структурированные данные, такие как, номера телефонов, адреса, ФИО, номенклатурные наименования товаров, перечень заведений, поставщики, сотрудники и много-много другой информации, своего рода база данных. Я думаю, с такими данными вы сталкивались, а значится и инструменты для систематизации и анализа таких данных будут очень полезны, особенно при создании дашбордов. По большому счёту от обычной таблицы списки ничем особым не отличаются, за исключением своих размеров, они достаточно велики. При работе со списками используют понятия: для строк – записи, а для столбиков – поля.

Spisok dannih 2 Работа со списками данных в Excel

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

  • Каждой колонке должен быть присвоен только один тип информации. Например, в колонке с данными о рождении следует вводить только такие данные, с именами сотрудников — только фамилии, а смешение типов данных не допускается;
  • Лучше всего разделить информацию по максимуму. Например, имя и фамилию следует разделить на три разных поля, так как их легче найти и с ними легче работать (по фамилии можно поздравлять людей с праздниками);
  • У каждого поля должен быть заголовок, хотя Excel не очень хорошо работает с многоуровневыми «шапками»;
  • В списке не должно быть пустых строк и столбцов, так как это определяется как конец списка и вызовет проблемы и ошибки при последующем отображении данных;
  • Не рекомендуется откладывать другие данные, так как в настоящее время
Читайте так же:
Как сделать метки строк на одной строке в сводной таблице?

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

Любой пользователь сталкивается с проблемой, когда при работе с большими таблицами заголовки полей выходят за пределы окна, создавая неудобства. Чтобы избежать этого, необходимо зафиксировать «заголовок» нашей таблицы, чтобы полоса прокрутки не влияла на отображение первых строк и столбцов ваших списков. Перейдите на вкладку «Вид» в панели управления, выберите блок «Окно», а затем выберите опцию «Закрепить область».

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

  1. Установить область — установить область слева и сверху от текущей ячейки, т.е. по горизонтали и вертикали одновременно;
  2. Установить верхнюю строку — установить по горизонтали до верха заданной ячейки таблицы;
  3. Установить первый столбец — установить списки по вертикали слева от заданной ячейки.

Spisok dannih 3 Работа со списками данных в Excel Также существует возможность разделения рабочей области одновременно на четыре части для независимой работы и прокручивания данных, получая возможность одновременно работать и в начале и в конце списка. Для разделения вам необходимо на панели управления, во вкладке «Вид», в блоке «Окно» нажать кнопку «Разделить», предварительно установив курсор на ячейку, по границам которой и будет происходить разделение. Отключить разделения можно повторно нажав на туже самую кнопку. Spisok dannih 4 Работа со списками данных в Excel Сами же данные в списках, возможно, отбирать, используя несколько инструментов, выбор которых зависит от ваших целей. Для этих задач можно использовать:

  1. Фильтрация списков;
  2. Сортировка данных;
  3. Создание промежуточных итогов;
  4. Сводные таблицы;
  5. Группировка элементов таблицы.

Отбор с помощью фильтра

Excel позволяет фильтровать списки по определенным критериям двумя способами:

  1. Усовершенствованный фильтр;
  2. Автоматический фильтр.

Spisok dannih 5 Работа со списками данных в Excel

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

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

Подробности работы фильтров я описал в статье «Автофильтр в Excel», которую вы можете прочитать, перейдя по этой ссылке.

Читайте так же:
Как скопировать ячейку без разрыва строки в Excel?

Создаем промежуточные отчёты

Очень часто возникает необходимость группировать данные списков по определенным показателям с расчётами по ним итогов. Так создаются удобные и очень полезные в работе отчёты и анализы, прекрасный инструмент для любого бухгалтера и экономиста. Spisok dannih 6 Работа со списками данных в Excel Создать такой детализированный список данных в Excel с выделением групп и подбитием итогов по группам и общий по полю, не очень трудно. Всё это можно произвести в несколько шагов, но обязательным условием применения промежуточных итогов к спискам, это сортировка данных по полю для которого создается итог. Spisok dannih 7 Работа со списками данных в Excel Подробно и в деталях об этом можно узнать, прочитав статью «Промежуточные итоги в Excel», перейдя по ссылке.

Сортируем свои списки

Spisok dannih 8 Работа со списками данных в Excel

При формировании списков информация почти всегда формируется в произвольном порядке, что затрудняет их эффективное использование. Чтобы избежать этого и упростить использование, в Excel есть инструмент под названием «Сортировка». Он запускается с панели инструментов, вкладки «Данные» и кнопки «Сортировка». В появившемся диалоговом окне можно выбрать поле сортировки, установить возможный приоритет для порядка сортировки и выбрать критерии (по убыванию или по возрастанию). Более подробную информацию об этом инструменте вы можете найти в статье «Сортировка данных в Excel».

Работаем со сводными таблицами

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

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

Для создания сводной таблицы необходимо установить курсор на любую ячейку таблицы или базы данных, и на панели управления во вкладке «Вставка» выбрать пункт «Сводная таблица». В диалоговом окне указываем, где размещены данные для анализа (по умолчанию будет указан диапазон таблицы, где стоит курсор) и куда нужно поместить результат. Spisok dannih 9 Работа со списками данных в Excel Следующим шагом в «Конструкторе сводной таблицы» вы можете из полей и записей вашей БД создать отчёт в таком виде, который вам нужен. Spisok dannih 10 Работа со списками данных в Excel При внесении изменений в базу данных, автоматических изменений в сводной таблице не происходит. Все изменения стают, доступны только при нажатии кнопки «Обновить данные», через контекстное меню или вкладка «Данные» и кнопка «Обновить всё».

Группируем элементы таблицы

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

И это все для меня! Надеюсь, вы нашли всю вышеизложенную информацию о работе со списками данных в Excel полезной и понятной. Я буду очень признателен за ваши комментарии, так как они являются показателем читательской аудитории и вдохновляют на написание новых статей! Поделитесь прочитанным с друзьями и поставьте лайк!

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