Как создать собственный список в excel
Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.
Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.
Сравнение встроенных и настраиваемых списков
В Excel есть указанные ниже встроенные списки дней недели и месяцев года.
Встроенные списки
Пн, Вт, Ср, Чт, Пт, Сб, Вс
Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье
янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек
Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь
Примечание: Изменить или удалить встроенный список невозможно.
Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.
Настраиваемые списки
Высокое, Среднее, Низкое
Большое, Среднее, Малое
Север, Юг, Восток, Запад
Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель
Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки.
Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.
Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.
Введение значений напрямую
Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.
В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.
В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.
Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.
После ввода каждого элемента нажимайте клавишу ВВОД.
Завершив создание списка, нажмите кнопку Добавить.
На панели Списки появятся введенные вами элементы.
Нажмите два раза кнопку ОК.
Создание настраиваемого списка на основе диапазона ячеек
Выполните указанные ниже действия.
В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно "Списки".
Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.
На панели Списки появятся выбранные вами элементы.
Два раза нажмите кнопку ОК.
Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.
Выполните указанные ниже действия.
По приведенным выше инструкциям откройте диалоговое окно "Списки".
Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.
Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с Службы Excel, для которых может быть опубликована ваша книга.
Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.
При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Не знаете, какой параметр выбрать в поле Вид?
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Если вам нужно отобразить список значений, которые сможет выбирать пользователь, добавьте на лист список.
Добавление списка на лист
Создайте перечень элементов, которые должны отображаться в списке, как показано на рисунке.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).
Щелкните ячейку, в которой нужно создать список.
В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.
Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.
В поле Связь с ячейкой введите ссылку на ячейку.
Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.
В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.
Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX "Список".
Добавление поля со списком на лист
Упростите ввод данных для пользователей, позволив им выбирать значение из поля со списком. Поле со списком состоит из текстового поля и списка, которые вместе образуют раскрывающийся список.
Можно добавить поле со списком одного из двух типов: элемент управления формы или элемент ActiveX. Если вы хотите создать поле со полем, которое позволит пользователю редактировать текст в текстовом поле, можно использовать ActiveX поле со ActiveX. Поле со списком ActiveX Control является более универсальным, так как можно изменить свойства шрифта, чтобы текст на листе с увеличенным масштабом было удобнее читать, и с помощью программирования отображать его в ячейках, содержащих список проверки данных.
Выберите столбец, который можно скрыть на листе, и создайте список, введя по одному значению в ячейки.
Примечание: Можно также создать список на другом листе той же книги.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
Выберите тип поля со списком, которое нужно добавить:
в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);
в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).
Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.
Чтобы изменить размер поля, наведите указатель мыши на один из маркеров изменения размера и перетащите границу элемента управления до достижения нужной высоты и ширины.
Чтобы переместить поле со списком на листе, выделите его и перетащите в нужное место.
Форматирование элемента управления формы "Поле со списком"
Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.
Откройте вкладку Элемент управления и настройте следующие параметры.
Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.
Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст "Фруктовое мороженое".
Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.
Форматирование элемента ActiveX "Поле со списком"
На вкладке Разработчик нажмите кнопку Режим конструктора.
Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.
Вот как можно настроить свойства поля со списком на этом рисунке:
Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Тип, начертание или размер шрифта
Щелкните свойство Font (Шрифт), нажмите кнопку . и выберите тип, размер или начертание шрифта.
Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Связь с ячейкой для отображения значения, выбранного в списке
Щелкните свойство LinkedCell (Связанная ячейка).
Связывание поля со списком и списка элементов
Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
Изменение количества отображаемых элементов списка
Щелкните поле ListRows и введите число элементов.
Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Вам часто приходится заполнять одни и те же наборы данных в электронных таблицах? Просто создайте собственный список в Excel. В следующий раз, когда вам понадобится один из ваших списков, пусть AutoFill сделает тяжелую работу
После настройки этой функции Excel вы можете легко заполнить диапазон ячеек своими собственными списками, такими как названия отделов, клиенты, возрастные диапазоны и любой другой список, который вам может понадобиться.
Давайте посмотрим, как работает автозаполнение и как вы можете создавать свои собственные списки в Excel.
Как автозаполнить в Excel
Excel включает несколько встроенных списков, включая месяцы и дни недели. Давайте посмотрим на простой пример, чтобы увидеть, как работает автозаполнение.
Введите Sun (для воскресенья) в ячейку B2 . Затем поместите курсор на правый нижний угол ячейки B2, пока он не превратится в знак плюс, и перетащите вправо на ячейку H2 .
Когда вы отпускаете кнопку мыши, функция автозаполнения автоматически заполняет ячейки сокращениями для дней недели. Вы также можете сделать это с полными названиями дней недели и короткими и полными названиями месяцев года. Автозаполнение также работает для чисел, дат и времени.
Как Excel знает, что положить в ячейки? Дни недели — это заранее определенный список в Excel. Вы можете ввести любой элемент в этом списке в ячейку и перетащить для автоматического заполнения. остальных предметов. Если вы перетаскиваете более шести ячеек по дням недели, Excel переместится в начало списка.
Как создать пользовательский список в Excel
Вы можете использовать функцию настраиваемого списка для создания и хранения часто используемых списков, таких как список названий отделов, имен сотрудников, размеров одежды, состояний и практически любой другой список, который вы можете себе представить. Эти списки можно использовать в качестве заголовков в столбцах или строках или для заполнения раскрывающихся списков, чтобы сделать ввод данных быстрее и точнее.
Есть три способа создать свой собственный список.
1. Введите ваш список напрямую
Первый способ создать свой собственный список — это ввести его прямо в диалоговом окне « Пользовательские списки ».
2. Импортируйте ячейки из листа
Второй способ создать пользовательский список — это импортировать их из ячеек в одной из ваших таблиц.
3. Импортируйте список из именованного диапазона ячеек
Третий способ создания настраиваемого списка — это импорт списка из именованного диапазона ячеек.
- Перед открытием диалогового окна « Пользовательские списки » введите каждый элемент в списке в отдельной ячейке либо в одном столбце, либо в одной строке на рабочем листе в своей книге. Выберите элементы, введите имя для диапазона выбранных ячеек в поле « Имя» и нажмите клавишу « Ввод» .
- Затем в диалоговом окне « Пользовательские списки » введите знак равенства ( = ), а затем имя, назначенное диапазону ячеек, содержащему ваш список (без пробела между знаком равенства и текстом), в поле « Импорт списка из ячеек» . Например, мы назвали наш диапазон ячеек Еда , поэтому мы ввели = Еда . Нажмите Импорт .
Примечание. При импорте настраиваемого списка из именованного диапазона ячеек на листе список в диалоговом окне « Настраиваемые списки » не связывается с исходным списком на листе.Если вы измените список на листе, настраиваемый список в диалоговом окне « Пользовательские списки » не изменится, и наоборот. - Если вы создали списки в именованных диапазонах ячеек в своей книге, этот метод упрощает добавление их в качестве пользовательских списков, которые будут доступны в любой новой или существующей книге. электронную при
3 примера пользовательских списков Excel
Существует много вариантов использования пользовательских списков в Excel, и мы не можем охватить все их здесь. Но вот несколько примеров того, как вы можете использовать собственные списки, чтобы сделать создание электронных таблиц и ввод данных проще и эффективнее в Excel.
- Если вы создаете электронные таблицы с данными для отделов в вашей компании, вы можете создать собственный список, содержащий названия отделов. Например, бухгалтерия, HR, маркетинг, разработка, техническая поддержка. Затем вы можете использовать автозаполнение для быстрого создания заголовков для столбцов или строк.
- Может быть, вы отслеживаете инвентарь одежды. Таким образом, вам могут потребоваться списки для размеров (S, M, L, XL и XXL), стилей (шея экипажа, V-образный вырез, макет шеи), длины рукава (короткий рукав, длинный рукав, три четверти рукава, без рукавов) и цвета (черный, белый, серый, синий, красный, розовый, фиолетовый, зеленый, желтый). Из этих списков вы можете быстро создать согласованные раскрывающиеся списки, содержащие эти параметры.
- Ты учитель? Вы можете создать собственный список всех имен ваших учеников и легко вставить этот список имен в строку или столбец электронной таблицы для отслеживания информации, такой как оценки или посещаемость.
Создайте свои собственные пользовательские списки
Функция пользовательских списков в Excel позволяет легко и быстро настраивать электронные таблицы в соответствии с вашими потребностями. После добавления настраиваемого списка в Excel он становится доступным во всех новых и существующих электронных таблицах.
Какие пользовательские списки вы создаете? Какой метод добавления пользовательского списка вы предпочитаете? Поделитесь своими мыслями и опытом с нами в комментариях ниже.
При работе в электронных таблицах Excel бывает нужно постоянно вводить одни и те же значения (из списка). Стандартными списками являются последовательности дней недели или месяцев, но порой хочется создать свой список автозаполнения. Например, список класса, возраст учеников, размеры одежды или любых других данных, к которым постоянно приходится обращаться. Чтобы не вводить каждый раз эти значения вручную, можно создать свой список автозаполнения, а затем использовать его — в Excel есть такая возможность.
Для создания своего списка автозаполнения выполните следующие действия.
Если используется Excel версии 2003, то нужно выбрать меню Сервис — Параметры — Списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
Если используется Excel версии 2007 (2010), то нужно выбрать Файл — Параметры — Дополнительно — в Общие Изменить списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
После создания своего списка автозаполнения достаточно в нужную ячейку таблицы ввести первое значение из списка (в примере Иванов Антон) и протянуть маркер заполнения ячейки в нужном направлении. Смотрите подробнее Как пользоваться списками автозаполнения и вводить стандартные последовательности.
Что делать, если нет маркера автозаполнения?
Если маркер (курсор) заполнения отсутствует, то нужно настроить Excel так, чтобы маркер отображался.
Для этого, если Вы используете версию 2003, выбираем Сервис — Параметры — на вкладке Параметры устанавливаем галочку Перетаскивание ячеек.
Если Вы используете версию 2007 или 2010, Файл (кнопка Офис) — Параметры — Дополнительно — Разрешить маркеры заполнения и перетаскивания ячеек — ОК.
Ввод данных экспресс-методом
Если таблица содержит в нескольких ячейках одинаковые данные, для быстрого ввода этих данных можно использовать экспресс-метод.
Используя клавишу Ctrl, выделим ячейки, в которые нужно ввести одинаковые значения.
В строку формул введем нужное значение и нажмем на клавиатуре сочетание клавиш Ctrl+Enter. Все выделенные ячейки автоматически заполнятся нужными данными.
Кратко об авторе:
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ "СОШ", с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Читайте также: