Как создать пользовательский список в Microsoft Excel
Вам часто приходится заполнять одни и те же наборы данных в электронных таблицах? Просто создайте собственный список в Excel. В следующий раз, когда вам понадобится один из ваших списков, пусть AutoFill сделает тяжелую работу
После настройки этой функции Excel вы можете легко заполнить диапазон ячеек своими собственными списками, такими как названия отделов, клиенты, возрастные диапазоны и любой другой список, который вам может понадобиться.
Давайте посмотрим, как работает автозаполнение и как вы можете создавать свои собственные пользовательские списки в Excel.
Самое главное, чтобы значения в первом столбце писались одинаково. Достаточно допустить одну опечатку, и эта строка не попадет во вторую ячейку, поскольку происходит дословный поиск значений.
Как сделать раскрывающийся (выпадающий) список в Excel 2007, 2010, 2013
- Если вы создаете электронные таблицы с данными для отделов в вашей компании, вы можете создать собственный список, содержащий названия отделов. Например, бухгалтерия, HR, маркетинг, разработка, техническая поддержка. Затем вы можете использовать автозаполнение для быстрого создания заголовков для столбцов или строк.
- Может быть, вы отслеживаете инвентарь одежды. Таким образом, вам могут потребоваться списки для размеров (S, M, L, XL и XXL), стилей (шея экипажа, V-образный вырез, макет шеи), длины рукава (короткий рукав, длинный рукав, три четверти рукава, без рукавов) и цвета (черный, белый, серый, синий, красный, розовый, фиолетовый, зеленый, желтый). Из этих списков вы можете быстро создать согласованные раскрывающиеся списки, содержащие эти параметры.
- Вы учитель? Вы можете создать собственный список всех имен ваших учеников и легко вставить этот список имен в строку или столбец электронной таблицы для отслеживания информации, такой как оценки или посещаемость.
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Простой способ
Для того чтобы произошла подстановка данных из таблицы, достаточно выполнить следующее.
Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.
Данный способ отлично работает и тогда, когда в таблице содержатся повторяющиеся значения. При этом в выпадающем списке будут только уникальные записи.
Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.
Умные Таблицы Excel – секреты эффективной работы.
- В окне «Форматирование объекта» необходимо:
- Указать диапазон значений для формирования списка.
- Выбрать ячейку, в которую будет выводиться результат.
- Указать количество строк будущего списка.
- Нажать на «OK» для сохранения.
Здесь мы, как и с упорядочиванием, используем ссылку на столбец, однако, делаем его абсолютной ссылкой, поскольку формулу будем растягивать вправо и нужно чтобы столбец не менялся (в случае поиска наименьшего нам нужно было, чтобы столбец менялся).
Раскрывающийся список по условию
Для создания выпадающего списка по условию выбираем ячейку под названием столбца «Вид» и нажимаем ссылку «Проверка данных» на вкладке «Данные» ленты инструментов. В открывшемся окне «Проверка вводимых значений» выбираем тип данных «Список», указываем источник =ЕСЛИ(A2=»Грибы»;Грибы;ЕСЛИ(A2=»Насекомые»;Насекомые;ЕСЛИ(A2=»Рыбы»;Рыбы;Списки!$E$1))) и нажимаем кнопку «OK».
Если в первом раскрывающемся списке (Группа) не будет выбрана ни одна позиция, во втором выпадающем списке (Вид) будет отображаться значение ячейки по адресу Списки!$E$1 (Выберите группу).
Смотрите, как для раскрывающегося списка можно использовать динамический диапазон на основе умной таблицы.
Раскрывающийся список в Excel (по условию)
Вам часто приходится заполнять одни и те же наборы данных в электронных таблицах? Просто создайте собственный список в Excel. В следующий раз, когда вам понадобится один из ваших списков, пусть AutoFill сделает тяжелую работу
Суть решения
Принцип действия
Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.
Действительно, нам нужно найти в левом столбце «маркер» списка, т.е. название и вернуть элемент такого списка уже из правого столбца. Однако, здесь есть один нюанс, который заключается в том, что функция ВПР, равно как и вышеупомянутая сборка-аналог вернут только первую запись, т.е. максимум мы сможем выбрать таким способом по одному элементу для каждого списка.
Соответственно, нам нужно сделать реализации чего-то на подобие множественного ВПР, когда значения будут выбираться все значения, а не только первое.
Основная идея реализации
Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.
Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.
Создание автоматически заполняемых списков в Excel — Центр обучения
Теперь полученную ячейку можно скопировать в любое место на листе, она будет содержать перечень необходимых элементов таблицы. Также её можно растянуть, чтобы получить диапазон с выпадающими списками.
