Как создать свой список автозаполнения в excel?

Вариант 2: Ручное добавление элементов списка

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

1. Выделите заранее подготовленную область, где хотите расположить перечень. Перейдите на вкладку «‎Данные» и откройте «‎Проверка данных».

2. Выберите в качестве типа данных список, а в «‎Источник» перечислите значения, которые желаете в него добавить. Используйте «‎;», чтобы отделить одно значение от другого.

3. Подтвердите изменения и вернитесь к таблице. Теперь при развертывании списка вы сможете выбрать один из добавленных вариантов.

Дополнение 2. Сообщение об ошибках

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

Можно настроить это сообщение при создании или редактировании выпадающего списка во вкладке Сообщение об ошибке.

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

Заполните серию, используя пользовательские элементы

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

На экране за кулисами нажмите «Опции» в списке элементов слева.

Нажмите «Дополнительно» в списке элементов в левой части диалогового окна «Параметры Excel».

На правой панели прокрутите вниз до раздела General и нажмите кнопку «Edit Custom Lists».

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

Метод первый: заполнить пользовательский ряд на основе нового списка элементов

В диалоговом окне «Пользовательские списки» убедитесь, что в поле «Пользовательские списки» выбран НОВЫЙ СПИСОК. Нажмите в поле «Список записей» и введите элементы в ваших пользовательских списках, по одному элементу в строке. Обязательно вводите элементы в том порядке, в котором вы хотите, чтобы они были заполнены в ячейках. Затем нажмите «Добавить».

Пользовательский список добавляется в поле «Пользовательские списки», где вы можете выбрать его, чтобы редактировать его, добавляя или удаляя элементы из поля «Записи списка», и снова нажимая «Добавить», или вы можете удалить список, нажав «Удалить». Нажмите «ОК».

Нажмите «ОК» в диалоговом окне «Параметры Excel».

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

Метод второй: заполнить пользовательский ряд на основе существующего списка элементов

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

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

Теперь нажмите «Импорт».

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

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

Подстановка динамических данных Excel

Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив. Выделите его и на вкладке «Главная» выберите любой стиль таблицы.

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

=ДВССЫЛ(«Таблица1»)

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

Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов.

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

=Адрес_ячейки

Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу

=D1

Как работает выпадающий список в Excel

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

Чтобы создать такой выпадающий список, перейдите в раздел «Данные» на панели инструментов, в группе «Работа с данными» выберите пункт «Проверка данных».

Далее всплывает окно «Проверка вводимых значений».

#1. Параметры

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

• Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
• Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
• Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
• Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
• Источник. Здесь перечисляются значения для проверки данных или задается формула.
• Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
• Очистить все — удаляет установленную проверку данных с выделенных ячеек.

#2. Подсказка по вводу

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

#3. Сообщение об ошибке

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

Создать вкладку надстройки в интерфейсе Excel

Отдельная вкладка с кнопкой запуска нашего макроса будет появляться в
интерфейсе Excel после подключения нашей надстройки.

Мы будем писать код вкладки с помощью бесплатной программы для редактирования XML

Ribbon XML Editor

1. Закрыть все окна Excel.
2. Запустить программу Ribbon XML Editor и открыть в ней файл надстройки Excel Plus.xlam.
3. При помощи кнопки tabs в левом верхнем углу добавить
   заготовку кода для новой вкладки
4. В кавычки по стрелке нужно вписать id — любые уникальные
   идентификаторы вкладки и группы, а в label — названия
   вкладки и группы кнопок на ней:

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

— Id – идентификатор кнопки;

— label — текст на кнопке;

— imageMso — условное название изображения на кнопке(иконка). Иконку можно выбрать из
большого числа изображений, щелкнув по кнопке:

— onAction
— имя процедуры обратного вызова — специального короткого макроса, который
будет запускать основной макрос FindCyr. Назвать эту
процедуру можно, например, FindCyrStart.

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

Закрыть Ribbon XML Editor

Открыть Excel, зайти в редактор VBA по Alt+F11 и добавить к нашему
макросу процедуру вызова FindCyrStart, для запуска
основного макросы поиска кириллицы.

Сохранить изменения в редакторе VBA и в Excel проверить результат:

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

Способ 2. Применяем именованный диапазон.

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

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

Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.

Так вы присвоите какое-то имя этому диапазону Excel.

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

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

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

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

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

Переключатели

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

Для объединения Элементов в группу помещаем на лист Элемент управления Группа (через меню Разработчик/ Элементы управления/ Вставить). В рамках группы создаем 3 переключателя (также через меню Разработчик/ Элементы управления/ Вставить) и связываем их все с одной ячейкой С2 (выделив элемент управления, правой клавишей вызываем контекстное меню, Формат объекта…, вкладка Элемент управления).

Удерживая клавишу CTRL выделяем 3 переключателя и элемент Группа, вызываем правой клавишей мыши контекстное меню и выбираем Группировка/ Группировать. Теперь при выборе одного из трех Переключателей в Группе, в ячейке С2 будет выводиться значение 1, 2 или 3.

Создаем пользовательский список в Excel

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

В этот момент я бы также запустил проверку орфографии, это делается простым нажатием F7.

Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).

В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).

Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).

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

Создаем список с нуля

У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!

Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.

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

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

Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК, чтобы закрыть окно с параметрами списка, и ещё раз ОК, чтобы закрыть окно параметров Excel.

Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).

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

Как сделать выпадающий список в Excel

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт. Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список». Снизу появится строка для указания источников. Указывать информацию можно по-разному.

• Ручной ввод Введите перечень через точку с запятой.
• Выбор диапазона значений с листа Excel Для этого начните выделять ячейки мышью. Как отпустите – окно снова станет нормальным, а в строке появятся адреса.
• Создание выпадающего списка в Excel с подстановкой данных

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

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

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

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

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

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

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

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

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

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

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

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

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

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

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

Расширенные возможности пользовательского автофильтра

Допустим нас интересует 10 продаж с наибольшими ценами. Быстрее всего можно реализовать данную задачу с помощью специальной опции автофильтра. Из выпадающего списка на столбце «Цена» выберите опцию: «Числовые фильтры»-«Первые 10». Данная функция автофильтра позволяет находить в этой таблице первые десять товаров с наибольшими ценами или 10 товаров (при необходимости и больше, например, 12) с наибольшей или наименьшей суммой продаж и т.п. Как видно из названия группы опции «Числовые фильтры» ее можно применять только к числовым значениям в столбцах таблицы, а также к датам (ведь дата в Excel – это число).

Допустим мы визуально анализируем отчет по продажам товаров на несколько сотен позиций и наименований, которые повторяются в смешанном порядке. Нас интересуют в первую очередь: какие товары из магазина №5 попали в ТОП-7 самых продаваемых? Если мы отсортируем все товары по наименованию, а потом будем суммировать количество проданных товаров по отдельности, то это займет много времени. Вместо суммирования групп позиций по отдельности можно воспользоваться промежуточными итогами или автофильтром. В несколько кликов мышки мы скроем ненужную информацию и оставим только необходимые данные содержащие соответственные итоговые значения. Для этого:

1. Сначала удалите критерий фильтрования из предыдущего примера: «ДАННЫЕ»-«Сортировка и фильтр»-«Очистить».
2. Из выпадающего списка на столбце «Магазин» отметьте галочкой только опцию: «Магазин 5».

Из выпадающего списка на столбце «Сумма» выберите опцию: «Числовые фильтры»-«Первые 10».
В появившемся окне «Наложения условия по списку» установите следующие параметры: наибольших; 7; элементов списка. И нажмите ОК.

В результате мы получили список из ТОП-7 самых продаваемых товаров в Магазине №5.

Создание списка посредством контекстного меню

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

Инструкция проста и состоит из нескольких шагов:

• в дополнительной таблице фиксируем наименования (каждое с новой строчки в отдельной ячейке), чтобы получился один столбец;

• далее нужно выделить ячейки, кликнуть правой кнопкой мыши в любом выделенном месте и выбрать из выпавшего списка функцию «Присвоить имя»;

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

• переключиться на вкладку «Данные» в главном окне экселя. Отметить группу с ячейками, для которой потребуется создать список и кликнуть на кнопку «Проверка данных» и выбрать подпункт «работа с данными»;

• должно появиться окно «проверка вводимых значений». В параметрах найти подпункт список. В «источнике» поставить = «название списка». Нажать ОК;

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

Основным преимуществом метода является минимальная вероятность опечаток.

Как выделить все ячейки, содержащие выпадающий список в Экселе

Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:

• Нажмите на вкладку “Главная” на Панели инструментов;
• Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек“:

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

Нажмите “ОК“

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

Создание книги

​ командой сайта office-guru.ru​Перейдите к представлению Backstage,​ с Microsoft Excel​.​ случае использования ВПР().​ треугольником в левом​ с указанием, соответственно,​ на клавиатуре несколько​ назначить переносы и​Выделяем столбец /строку правее​ щелкаем по его​ нужно сделать.хорошо. в​ копировании формулы.​hakervanya​ совпадение, т.е. 0.​

​ но для столбца​

​В столбцах Цена и​

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

1. ​ т.д.​ /ниже того места,​ названию (латинской букве)​ файле «объемы потребления…»​

2. ​Второй — написать​: подскажите пожалуйста как​Номер столбца: снова нужен​

​ с номером телефона​ помощью гистограмм, цветовой​Автор/переводчик: Антон Андронов​

1. ​Открыть​ новый документ, либо​Создание из имеющейся книги​ НДС введите соответственно​Через меню Данные/ Работа​

2. ​ НДС.​ Enter.​Простейший способ создания таблиц​

   ​ где нужно вставить​ левой кнопкой мыши.​

​ в первом столбце​​ обработчик события на​ создать справочник в​

support.office.com>