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

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

​Смотрите также​Для Excel версий​ в поле может​ добавим еще один​ ячеек в списке​ элементами для выпадающего​ роль «шапки» и​ «ActiveX». Здесь нам​ Рассмотрим пути реализации​ + vbQuestion) If​ они автоматически отражаются​Щелкните свойство​

​ можно связать с​ со списком можно​

Создание дополнительного списка

​Основные вкладки​ это мы уже​ а именно с​При работе в программе​ ниже 2007 те​ привести к нежелаемым​

​ столбец и введем​1​ списка (A2:A5) и​ содержит название столбца.​ нужна кнопка «Поле​ задачи.​ lReply = vbYes​ в раскрывающемся списке.​ForeColor​ ячейкой, где отображается​ программно разместить в​установите флажок для​ делали ранее с​ использованием ActiveX. По​ Microsoft Excel в​

​ же действия выглядят​ результатам.​ в него такую​- размер получаемого​ введите в поле​ На появившейся после​ со списком» (ориентируемся​

​Создаем стандартный список с​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Выделяем диапазон для выпадающего​(Цвет текста), щелкните​ номер элемента при​ ячейках, содержащих список​ вкладки​ обычными выпадающими списками.​ умолчанию, функции инструментов​ таблицах с повторяющимися​ так:​Итак, для создания​

​ страшноватую на первый​ на выходе диапазона​ адреса имя для​ превращения в Таблицу​ на всплывающие подсказки).​ помощью инструмента «Проверка​ 1, 1) =​ списка. В главном​

​ стрелку вниз, откройте​ его выборе из​ проверки данных.​Разработчик​В первой ячейке создаём​ разработчика отсутствуют, поэтому​ данными, очень удобно​Второй​ выпадающего списка необходимо:​ взгляд формулу:​ по горизонтали, т.е.​ этого диапазона (без​

​ вкладке​Щелкаем по значку –​ данных». Добавляем в​ Target End If​ меню находим инструмент​ вкладку​ списка. Введите номер​Выберите столбец, который можно​и нажмите кнопку​

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

​ список точно таким​ нам, прежде всего,​ использовать выпадающий список.​: воспользуйтесь​1.​=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))​ один столбец​ пробелов), например​Конструктор (Design)​ становится активным «Режим​ исходный код листа​ End If End​ «Форматировать как таблицу».​Pallet​

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

​Диспетчером имён​Создать список значений,​или, соответственно,​Теперь выделите ячейки, где​Стажеры,​можно изменить стандартное​ конструктора». Рисуем курсором​ готовый макрос. Как​ If End Sub​Откроются стили. Выбираем любой.​(Палитра) и выберите​ отображаться номер элемента.​ и создайте список,​.​

​ делали это ранее,​ включить. Для этого,​ можно просто выбирать​(Excel версий выше​ которые будут предоставляться​

​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ вы хотите создать​и нажмите на​ имя таблицы на​

​ (он становится «крестиком»)​ это делать, описано​Сохраняем, установив тип файла​ Для решения нашей​ цвет.​Например, в ячейке C1​ введя по одному​

​В разделе​ через проверку данных.​ переходим во вкладку​ нужные параметры из​ 2003 — вкладка​

​ на выбор пользователю​При всей внешней жуткости​

​ выпадающие списки, и​Enter​ свое (без пробелов!).​ небольшой прямоугольник –​ выше. С его​ «с поддержкой макросов».​ задачи дизайн не​

Связанные списки

​Связь с ячейкой для​ отображается значение 3, если​ значению в ячейки.​Элементы управления формы​Во второй ячейке тоже​ «Файл» программы Excel,​ сформированного меню. Давайте​ «​ (в нашем примере​ вида, эта формула​ выберите в старых​:​ По этому имени​ место будущего списка.​ помощью справа от​Переходим на лист со​ имеет значения. Наличие​

​ отображения значения, выбранного​ выбрать пункт​Примечание:​выберите элемент управления​ запускаем окно проверки​ а затем кликаем​

​ выясним, как сделать​Формулы​ это диапазон​ делает одну простую​ версиях Excel в​

​Фактически, этим мы создаем​ мы сможем потом​Жмем «Свойства» – открывается​ выпадающего списка будут​ списком. Вкладка «Разработчик»​

​ заголовка (шапки) важно.​ в списке​Фруктовое мороженое​ Можно также создать список​Список (элемент управления формы)​ данных, но в​ по надписи «Параметры».​

​ раскрывающийся список различными​

​» — группа «​M1:M3​ вещь — выводит​ меню​ именованный динамический диапазон,​ адресоваться к таблице​ перечень настроек.​ добавляться выбранные значения.Private​

​ В нашем примере​Щелкните свойство​, так как это​ на другом листе​.​ графе «Источник» вводим​В открывшемся окне переходим​ способами.​Определённые имена​), далее выбрать ячейку​ очередное по номеру​Данные — Проверка (Data​ который ссылается на​

Добавление списка или поля со списком на лист в Excel

​Вписываем диапазон в строку​​ Sub Worksheet_Change(ByVal Target​ «Макросы». Сочетание клавиш​ это ячейка А1​LinkedCell​ третий элемент в​ той же книги.​Щелкните ячейку, в которой​ функцию «=ДВССЫЛ» и​ в подраздел «Настройка​Скачать последнюю версию​»), который в любой​ в которой будет​ имя сотрудника (используя​ — Validation)​ данные из нашей​ этой книги:​ ListFillRange (руками). Ячейку,​ As Range) On​ для быстрого вызова​ со словом «Деревья».​(Связанная ячейка).​

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

Добавление списка на лист

​ Excel​ версии Excel вызывается​ выпадающий список (в​ функцию НАИМЕНЬШИЙ) из​

​. В открывшемся окне​​ умной таблицы. Теперь​​Теперь выделите ячейки где​​ куда будет выводиться​​ Error Resume Next​

​ – Alt +​​ То есть нужно​​Связывание поля со списком​​Совет:​Разработчик​​Нажмите кнопку​​ Например, =ДВССЫЛ($B3).​​ флажок напротив значения​​Самым удобным, и одновременно​​ сочетанием клавиш​​ нашем примере это​​ списка или пустую​​ на вкладке​ имя этого диапазона​​ вы хотите создать​​ выбранное значение –​​ If Not Intersect(Target,​​ F8. Выбираем нужное​

​ выбрать стиль таблицы​​ и списка элементов​​ Чтобы вместо номера​​нажмите кнопку​​Свойства​

​Как видим, список создан.​ «Разработчик». Жмем на​

​ наиболее функциональным способом​​Ctrl+F3​​ ячейка​​ ячейку, если имена​​Параметры (Settings)​

​ можно ввести в​​ выпадающие списки (в​​ в строку LinkedCell.​ Range(«Е2:Е9»)) Is Nothing​

​ имя. Нажимаем «Выполнить».​​ со строкой заголовка.​Щелкните поле рядом со​ отображать сам элемент,​Вставить​

​и на вкладке​​Теперь, чтобы и нижние​​ кнопку «OK».​ создания выпадающего списка,​

​.​​К1​ свободных сотрудников уже​выберите вариант​ окне создания выпадающего​ нашем примере выше​ Для изменения шрифта​ And Target.Cells.Count =​

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

​После этого, на ленте​​ является метод, основанный​Какой бы способ​​), потом зайти во​​ кончились.​​Список (List)​​ списка в поле​ — это D2)​ и размера –​

Добавление поля со списком на лист

​ 1 Then Application.EnableEvents​ пустую ячейку выпадающего​ диапазона:​ListFillRange​ ИНДЕКС. В нашем​Примечание:​задайте необходимые свойства:​ же свойства, как​ появляется вкладка с​

​ на построении отдельного​ Вы не выбрали​ вкладку «​в Excel 2003 и​и введите в​Источник (Source)​ и выберите в​ Font.​ = False If​ списка новое наименование,​Ставим курсор в ячейку,​(Диапазон элементов списка)​ примере поле со​ Если вкладка​В поле​ и в предыдущий​ названием «Разработчик», куда​ списка данных.​ в итоге Вы​Данные​ старше идем в​ поле​:​ старых версиях Excel​Скачать пример выпадающего списка​

​ Len(Target.Offset(0, 1)) =​ появится сообщение: «Добавить​ где будет находиться​ и укажите диапазон​ списком связано с​

​Разработчик​​Формировать список по диапазону​ раз, выделяем верхние​ мы и перемещаемся.​

​Прежде всего, делаем таблицу-заготовку,​​ должны будете ввести​​», группа «​​ меню​​Источник (Source)​

​В старых версиях Excel​​ в меню​​При вводе первых букв​​ 0 Then Target.Offset(0,​ введенное имя баобаб​​ выпадающий список. Открываем​​ ячеек для списка.​​ ячейкой B1, а​​не отображается, на​​введите диапазон ячеек,​​ ячейки, и при​​ Чертим в Microsoft​​ где собираемся использовать​ имя (я назвал​​Работа с данными​​Вставка — Имя -​​вот такую формулу:​​ до 2007 года​

​Данные — Проверка (Data​ с клавиатуры высвечиваются​ 1) = Target​

​ в выпадающий список?».​​ параметры инструмента «Проверка​​Изменение количества отображаемых элементов​​ диапазон ячеек для​ вкладке​​ содержащий список значений.​

​ нажатой клавише мышки​

​ Excel список, который​​ выпадающее меню, а​​ диапазон со списком​​», кнопка «​ Присвоить (Insert -​​=Люди​

​ не было замечательных​ — Validation)​ подходящие элементы. И​ Else Target.End(xlToRight).Offset(0, 1)​Нажмем «Да» и добавиться​

​ данных» (выше описан​​ списка​

​ списка — A1:A2. Если​Файл​Примечание:​ «протаскиваем» вниз.​ должен стать выпадающим​ также делаем отдельным​list​Проверка данных​

​ Name — Define)​После нажатия на​ «умных таблиц», поэтому​, а в новых​ это далеко не​

Форматирование элемента управления формы «Поле со списком»

​ = Target End​ еще одна строка​ путь). В поле​​Щелкните поле​​ в ячейку C1​

​выберите​​ Если нужно отобразить в​​Всё, таблица создана.​ меню. Затем, кликаем​

​ списком данные, которые​​) и адрес самого​»​

​в Excel 2007 и​​ОК​ придется их имитировать​ нажмите кнопку​ все приятные моменты​ If Target.ClearContents Application.EnableEvents​ со значением «баобаб».​ «Источник» прописываем такую​ListRows​

​ ввести формулу​Параметры​ списке больше элементов,​​Мы разобрались, как сделать​​ на Ленте на​ в будущем включим​ диапазона (в нашем​

​Для Excel версий​​ новее — жмем​ваш динамический список​ своими силами. Это​Проверка данных (Data Validation)​ данного инструмента. Здесь​ = True End​Когда значения для выпадающего​ функцию:​и введите число​=ИНДЕКС(A1:A5;B1)​>​​ можно изменить размер​​ выпадающий список в​ значок «Вставить», и​ в это меню.​ примере это​

​ ниже 2007 те​​ кнопку​ в выделенных ячейках​ можно сделать с​на вкладке​ можно настраивать визуальное​ If End Sub​ списка расположены на​Протестируем. Вот наша таблица​ элементов.​, то при выборе​Настроить ленту​ шрифта для текста.​ Экселе. В программе​

​ среди появившихся элементов​​ Эти данные можно​​’2′!$A$1:$A$3​

Форматирование элемента ActiveX «Поле со списком»

​ же действия выглядят​​Диспетчер Имен (Name Manager)​​ готов к работе.​​ помощью именованного диапазона​​Данные​

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

​ можно создавать, как​ в группе «Элемент​ размещать как на​)​

​Я знаю, что делать,​

​ и функции​​(Data)​​ в качестве источника​ снизу, вставляем другой​ в другой книге,​​ одном листе:​​Properties​ ячейке C1 появится​

​Основные вкладки​Связь с ячейкой​

​ простые выпадающие списки,​​ ActiveX» выбираем «Поле​​ этом же листе​​6.​​2.​Формулы (Formulas)​ но не знаю​

​. В открывшемся окне​​ сразу два столбца.​​ код обработчика.Private Sub​ стандартный способ не​Добавим в таблицу новое​​(Свойства) и нажмите​​ текст «Фруктовое мороженое».​установите флажок для​

​введите ссылку на​ так и зависимые.​ со списком».​

​ документа, так и​​Теперь в ячейке​​Выбираем «​

​и создаем новый именованный​куда потом девать​

​, которая умеет выдавать​ на вкладке​​Задача​​ Worksheet_Change(ByVal Target As​ работает. Решить задачу​ значение «елка».​

​ кнопку​Количество строк списка:​

​ вкладки​​ ячейку.​​ При этом, можно​Кликаем по месту, где​

​ на другом, если​​ с выпадающим списком​​Тип данных​ диапазон​​ тела. ​​ ссылку на динамический​

​Параметры (Settings)​: создать в ячейке​ Range) On Error​ можно с помощью​Теперь удалим значение «береза».​​Режим конструктора​​количество строк, которые​

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

​Разработчик​Совет:​ использовать различные методы​ должна быть ячейка​ вы не хотите,​ укажите в поле​» -«​

​Имена​Имеем в качестве примера​ диапазон заданного размера.​выберите вариант​ выпадающий список для​ Resume Next If​ функции ДВССЫЛ: она​Осуществить задуманное нам помогла​.​ должны отображаться, если​

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

​и нажмите кнопку​ Выбираемая ячейка содержит число,​ создания. Выбор зависит​ со списком. Как​ чтобы обе таблице​

​ «Источник» имя диапазона​Список​по следующей формуле:​ недельный график дежурств,​

1. ​Откройте меню​Список (List)​
2. ​ удобного ввода информации.​ Not Intersect(Target, Range(«Н2:К2»))​ сформирует правильную ссылку​ «умная таблица», которая​
3. ​Завершив форматирование, можно щелкнуть​ щелкнуть стрелку вниз.​ОК​ связанное с элементом,​

​ от конкретного предназначения​ видите, форма списка​

​ располагались визуально вместе.​

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

​7.​» и указываем диапазон​=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))​ который надо заполнить​Вставка — Имя -​и введите в​ Варианты для списка​ Is Nothing And​

1. ​ на внешний источник​ легка «расширяется», меняется.​ правой кнопкой мыши​ Например, если список​
2. ​.​ выбранным в списке.​ списка, целей его​ появилась.​Выделяем данные, которые планируем​Готово!​ списка​в англоязычной версии =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10))​ именами сотрудников, причем​ Присвоить (Insert -​ поле​ должны браться из​ Target.Cells.Count = 1​
3. ​ информации.​Теперь сделаем так, чтобы​ столбец, который содержит​ содержит 10 элементов и​Выберите тип поля со​ Его можно использовать​ создания, области применения,​Затем мы перемещаемся в​

​ занести в раскрывающийся​Для полноты картины​3.​

​Фактически, мы просто даем​ для каждого сотрудника​

​Источник (Source)​ заданного динамического диапазона,​ Then Application.EnableEvents =​

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

1. ​ «Режим конструктора». Жмем​ список. Кликаем правой​ добавлю, что список​Если есть желание​ диапазону занятых ячеек​
2. ​ максимальное количество рабочих​или нажмите​вот такую формулу:​ т.е. если завтра​ False If Len(Target.Offset(1,​
3. ​ хотим поместить раскрывающийся​ новые значения прямо​ команду​ использовать прокрутку, вместо​ добавить:​ получения фактического элемента​Автор: Максим Тютюшев​
4. ​ на кнопку «Свойства​ кнопкой мыши, и​ значений можно ввести​ подсказать пользователю о​ в синем столбце​ дней (смен) ограничено.​Ctrl+F3​=ДВССЫЛ(«Таблица1[Сотрудники]»)​ в него внесут​ 0)) = 0​ список.​ в ячейку с​Скрыть​ значения по умолчанию​в разделе​ из входного диапазона.​Примечание:​ элемента управления».​ в контекстном меню​ и непосредственно в​ его действиях, то​ собственное название​ Идеальным вариантом было​. В открывшемся окне​=INDIRECT(«Таблица1[Сотрудники]»)​ изменения — например,​ Then Target.Offset(1, 0)​Открываем параметры проверки данных.​ этим списком. И​.​ введите 10. Если​Элементы управления формы​В группе​
5. ​Мы стараемся как​Открывается окно свойств элемента​
6. ​ выбираем пункт «Присвоить​ проверку данных, не​ переходим во вкладку​Имена​ бы организовать в​ нажмите кнопку​Смысл этой формулы прост.​ удалят ненужные элементы​

​ = Target Else​ В поле «Источник»​ данные автоматически добавлялись​Под выпадающим списком понимается​ ввести число, которое​выберите элемент управления​

​Возможен выбор​ можно оперативнее обеспечивать​ управления. В графе​

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

​ имя…».​ прибегая к вынесению​ «​.​ ячейках B2:B8 выпадающий​Добавить (New)​ Выражение​ или допишут еще​ Target.End(xlDown).Offset(1, 0) =​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ в диапазон.​

1. ​ содержание в одной​ меньше количества элементов​Поле со списком (элемент​
2. ​установите переключатель​ вас актуальными справочными​ «ListFillRange» вручную через​

​Открывается форма создания имени.​ значений на лист​Сообщение для ввода​Осталось выделить ячейки B2:B8​ список, но при​, введите имя диапазона​Таблица1[Сотрудники]​ несколько новых -​ Target End If​Имя файла, из которого​

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

​Сформируем именованный диапазон. Путь:​

​ ячейке нескольких значений.​ в списке, появится​ управления формы)​одинарного значения​ материалами на вашем​ двоеточие прописываем диапазон​ В поле «Имя»​ (это так же​

1. ​» и заполняем заголовок​ нашего графика и​ этом сделать так,​
2. ​ (любое, но без​- это ссылка​ они должны автоматически​ Target.ClearContents Application.EnableEvents =​ берется информация для​
3. ​ «Формулы» — «Диспетчер​ Когда пользователь щелкает​ полоса прокрутки.​;​и нажмите кнопку​ языке. Эта страница​ ячеек таблицы, данные​ вписываем любое удобное​ позволит работать со​ и текст сообщения​ добавить в них​ чтобы уже занятые​ пробелов и начинающееся​

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

​ на столбец с​ отразиться в выпадающем​ True End If​ списка, заключено в​ имен» — «Создать».​

1. ​ по стрелочке справа,​Нажмите кнопку​ИЛИ:​ОК​ переведена автоматически, поэтому​ которой будут формировать​ наименование, по которому​ списком на любом​которое будет появляться​ выпадающий список с​ сотрудники автоматически убирались​ с буквы, например​ данными для списка​ списке:​ End Sub​ квадратные скобки. Этот​ Вводим уникальное название​ появляется определенный перечень.​ОК​в разделе​.​ ее текст может​ пункты выпадающего списка.​ будем узнавать данный​ листе). Делается это​ при выборе ячейки​
2. ​ элементами диапазона​ из выпадающего списка,​ -​ из нашей умной​Простой и удобный способ​Чтобы выбираемые значения отображались​ файл должен быть​ диапазона – ОК.​ Можно выбрать конкретное.​.​Элементы ActiveX​Примечание:​ содержать неточности и​Далее, кликаем по ячейке,​ список. Но, это​ так:​ с выпадающим списком​Имена​ оставляя только свободных:​
3. ​Люди​ таблицы. Но проблема​ почти без формул.​ в одной ячейке,​ открыт. Если книга​
   ​Создаем раскрывающийся список в​Очень удобный инструмент Excel​
   ​На вкладке​выберите элемент управления​
   ​ Если вы хотите​ грамматические ошибки. Для​ и в контекстном​ наименование должно начинаться​
   ​То есть вручную,​
   ​4.​
   ​. Для этого​
   ​Чтобы реализовать подобный вариант​
   ​) и в поле​ в том, что​ Использует новую возможность​
   ​ разделенные любым знаком​ с нужными значениями​ любой ячейке. Как​
   ​ для проверки введенных​
   ​Разработчик​
   ​Поле со списком (элемент​
   ​ выбрать параметр​ нас важно, чтобы​
   ​ меню последовательно переходим​
   ​ обязательно с буквы.​
   ​ через​

​Так же необязательно​в Excel 2003 и​ выпадающего списка выполним​Ссылка (Reference)​ Excel почему-то не​ последних версий Microsoft​

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

1. ​ препинания, применим такой​ находится в другой​ это сделать, уже​ данных. Повысить комфорт​нажмите кнопку​ ActiveX)​
2. ​набора значений​ эта статья была​ по пунктам «Объект​ Можно также вписать​;​ можно создать и​
3. ​ старше — откроем​ несколько простых шагов.​
4. ​введите вот такую​ хочет понимать прямых​ Excel начиная с​ модуль.​ папке, нужно указывать​ известно. Источник –​ работы с данными​Режим конструктора​

​или​ вам полезна. Просим​ ComboBox» и «Edit».​ примечание, но это​(точка с запятой) вводим​ сообщение, которое будет​ меню​Сначала давайте подсчитаем кто​ формулу:​ ссылок в поле​

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

​ 2007 версии -​​Private Sub Worksheet_Change(ByVal​ путь полностью.​ имя диапазона: =деревья.​ позволяют возможности выпадающих​.​Щелкните ячейку, в которую​списка значений​ вас уделить пару​Выпадающий список в Microsoft​ не обязательно. Жмем​ список в поле​ появляться при попытке​Данные — Проверка (Data​ из наших сотрудников​=СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1)​

Способ 1. Если у вас Excel 2007 или новее

​Источник (Source)​ «Умные Таблицы». Суть​ Target As Range)​Возьмем три именованных диапазона:​Снимаем галочки на вкладках​ списков: подстановка данных,​Щелкните правой кнопкой мыши​ нужно добавить поле​, подумайте о том,​ секунд и сообщить,​ Excel готов.​ на кнопку «OK».​ «​ ввести неправильные данные​ — Validation)​ уже назначен на​=OFFSET(A2;0;0;COUNTA(A2:A100);1)​, т.е. нельзя написать​ его в том,​

​On Error Resume​Это обязательное условие. Выше​ «Сообщение для ввода»,​ отображение данных другого​​ поле со списком​​ со списком, и​​ чтобы использовать элемент​ помогла ли она​Чтобы сделать и другие​​Переходим во вкладку «Данные»​Источник​Если Вы не​

​,​ дежурство и на​Функция​ в поле Источник​ что любой диапазон​ Next​​ описано, как сделать​​ «Сообщение об ошибке».​ листа или файла,​ и выберите пункт​ нарисуйте его с​ ActiveX «Список».​ вам, с помощью​​ ячейки с выпадающим​​ программы Microsoft Excel.​», в том порядке​ сделаете пункты 3​в Excel 2007 и​ сколько смен. Для​СЧЁТЗ (COUNTA)​ выражение вида =Таблица1[Сотрудники].​ можно выделить и​

​If Not Intersect(Target,​ обычный список именованным​ Если этого не​ наличие функции поиска​Свойства​ помощью перетаскивания.​Упростите ввод данных для​ кнопок внизу страницы.​​ списком, просто становимся​ Выделяем область таблицы,​​ в котором мы​ и 4, то​​ новее — жмем​​ этого добавим к​​подсчитывает количество непустых​ ​ Поэтому мы идем​​ отформатировать как Таблицу.​ Range(«C2:C5»)) Is Nothing​​ диапазоном (с помощью​​ сделать, Excel не​​ и зависимости.​​. Откройте вкладку​Советы:​​ пользователей, позволив им​​ Для удобства также​

​ на нижний правый​

​ где собираемся применять​

​ хотим его видеть​проверка данных​​ кнопку​​ зеленой таблице еще​ ячеек в столбце​ на тактическую хитрость​ Тогда он превращается,​ And Target.Cells.Count =​ «Диспетчера имен»). Помним,​ позволит нам вводить​Путь: меню «Данные» -​Alphabetic​​ ​​ выбирать значение из​ приводим ссылку на​ край готовой ячейки,​ выпадающий список. Жмем​ (значения введённые слева-направо​работать будет, но​Проверка данных (Data Validation)​ один столбец, введем​ с фамилиями, т.е.​​ — вводим ссылку​​ упрощенно говоря, в​ 1 Then​ что имя не​

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

​ количество строк в​ как текст (в​ «резиновый», то есть​Application.EnableEvents = False​ может содержать пробелов​Вызываем редактор Visual Basic.​ — вкладка «Параметры».​ измените нужные свойства.​ наведите указатель мыши​ Поле со списком​ языке) .​ и протягиваем вниз.​ данных», расположенную на​​ ячейке сверху вниз).​​ не будет появляться​​Данные (Data)​​ формулу:​

​ диапазоне для выпадающего​ кавычках) и используем​ сам начинает отслеживать​newVal = Target​ и знаков препинания.​ Для этого щелкаем​ Тип данных –​Вот как можно настроить​ на один из​​ состоит из текстового​​Если вам нужно отобразить​

Способ 2. Если у вас Excel 2003 или старше

​Также, в программе Excel​ Ленте.​При всех своих​ сообщение пользователю о​В открывшемся окне выберем​=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной​ списка. Функция​ функцию​ изменения своих размеров,​​Application.Undo​​Создадим первый выпадающий список,​ правой кнопкой мыши​ «Список».​

​ свойства поля со​​ маркеров изменения размера​ поля и списка,​ список значений, которые​​ можно создавать связанные​​Открывается окно проверки вводимых​​ плюсах выпадающий список,​ его предполагаемых действиях,​​ в списке допустимых​​ версии =COUNTIF($B$2:$B$8;E2)​СМЕЩ (OFFSET)​ДВССЫЛ (INDIRECT)​ автоматически растягиваясь-сжимаясь при​oldval = Target​​ куда войдут названия​​ по названию листа​​Ввести значения, из которых​​ списком на этом​ и перетащите границу​

​ которые вместе образуют​

​ сможет выбирать пользователь,​

​ выпадающие списки. Это​​ значений. Во вкладке​​ созданный вышеописанным образом,​ а вместо сообщения​ значений вариант​Фактически, формула просто вычисляет​формирует ссылку на​, которая преобразовывает текстовую​​ добавлении-удалении в него​​If Len(oldval) <>​ диапазонов.​ и переходим по​ будет складываться выпадающий​

• ​ рисунке:​​ элемента управления до​
• ​ раскрывающийся список.​​ добавьте на лист​ такие списки, когда​ «Параметры» в поле​ имеет один, но​
• ​ об ошибке с​​Список (List)​ сколько раз имя​ диапазон с нужными​ ссылку в настоящую,​
• ​ данных.​​ 0 And oldval​Когда поставили курсор в​ вкладке «Исходный текст».​ список, можно разными​Настраиваемое свойство​ достижения нужной высоты​
• ​Можно добавить поле со​​ список.​ при выборе одного​ «Тип данных» выбираем​ очень «жирный» минус:​

​ вашим текстом будет​и укажем​ сотрудника встречалось в​ нам именами и​ живую.​Выделите диапазон вариантов для​​ <> newVal Then​ поле «Источник», переходим​​ Либо одновременно нажимаем​ способами:​​Действие​​ и ширины.​​ списком одного из​​Создайте перечень элементов, которые​ значения из списка,​​ параметр «Список». В​​ проверка данных работает​

​ появляться стандартное сообщение.​

​Источник (Source)​​ диапазоне с именами.​​ использует следующие аргументы:​Осталось только нажать на​ выпадающего списка (A1:A5​

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

​Target = Target​ на лист и​
​ клавиши Alt +​Вручную через «точку-с-запятой» в​

Постановка задачи

​Цвет заливки​Чтобы переместить поле со​ двух типов: элемент​ должны отображаться в​ в другой графе​ поле «Источник» ставим​ только при непосредственном​5.​данных:​Теперь выясним, кто из​A2​ОК​ в нашем примере​ & «,» &​ выделяем попеременно нужные​ F11. Копируем код​

​ поле «Источник».​Щелкните свойство​ списком на листе,​

Шаг 1. Кто сколько работает?

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

​ выше) и на​ newVal​

​ ячейки.​ (только вставьте свои​Ввести значения заранее. А​BackColor​

Шаг 2. Кто еще свободен?

​ выделите его и​ элемент ActiveX. Если​ на рисунке.​ ему параметры. Например,​ сразу без пробелов​ клавиатуры. Если Вы​ находится на другом​ при назначении сотрудников​ свободен, т.е. не​0​

​ к нашей таблице​

Шаг 3. Формируем список

​Главной (Home)​Else​Теперь создадим второй раскрывающийся​ параметры).Private Sub Worksheet_Change(ByVal​ в качестве источника​(Цвет фона), щелкните​ перетащите в нужное​ необходимо создать поле​На вкладке​ при выборе в​ пишем имя списка,​ попытаетесь вставить в​

​ листе, то вышеописанным​

​ на дежурство их​

​ исчерпал запас допустимых​

​- сдвиг начальной​ новые элементы, то​вкладке нажмите кнопку​Target = newVal​ список. В нем​ Target As Range)​ указать диапазон ячеек​ стрелку вниз, откройте​ место.​ со списком, в​Разработчик​

Шаг 4. Создаем именованный диапазон свободных сотрудников

• ​ списке продуктов картофеля,​ которое присвоили ему​ ячейку с​​ образом создать выпадающий​ имена будут автоматически​ смен. Добавим еще​
• ​ ячейки по вертикали​ они будут автоматически​Форматировать как таблицу (Home​​End If​​ должны отражаться те​​ Dim lReply As​

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

​ предлагается выбрать как​

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

Шаг 5. Создаем выпадающий список в ячейках

​ в нее включены,​ — Format as​If Len(newVal) =​ слова, которые соответствуют​ Long If Target.Cells.Count​​Назначить имя для диапазона​​Pallet​

• ​ поле со списком​ изменять текст в​Вставить​​ меры измерения килограммы​ кнопку «OK».​​значения из буфера​
• ​ (до версии Excel​ списка, оставляя только​ введем в него​​ количество строк​​ а значит -​​ Table)​

​ 0 Then Target.ClearContents​ выбранному в первом​ > 1 Then​​ значений и в​​(Палитра) и выберите​​ и выберите команду​​ текстовом поле, рассмотрите​

​.​ и граммы, а​Выпадающий список готов. Теперь,​ обмена, т.е скопированные​ 2010). Для этого​ тех, кто еще​ формулу, которая будет​0​

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

​ добавятся к нашему​. Дизайн можно выбрать​Application.EnableEvents = True​ списке названию. Если​ Exit Sub If​ поле источник вписать​ цвет.​Формат объекта​ возможность использования элемента​Примечание:​ при выборе масла​ при нажатии на​
​ предварительно любым способом,​ необходимо будет присвоить​
​ свободен.​​ выводить номера свободных​- сдвиг начальной​ выпадающему списку. С​ любой — это​End If​​ «Деревья», то «граб»,​​ Target.Address = «$C$2″​ это имя.​Тип, начертание или размер​.​ ActiveX «Поле со​​ Если вкладка​​ растительного – литры​ кнопку у каждой​​ то Вам это​​ имя списку. Это​​Выпадающий список в​​ сотрудников:​​ ячейки по горизонтали​​ удалением — то​

​ роли не играет:​End Sub​ «дуб» и т.д.​ Then If IsEmpty(Target)​

​Любой из вариантов даст​​ шрифта​​Откройте вкладку​​ списком». Элемент ActiveX​​Разработчик​​ и миллилитры.​ ячейки указанного диапазона​

​ удастся. Более того,​​ можно сделать несколько​ ячейке позволяет пользователю​=ЕСЛИ(F2-G2​ вправо на заданное​ же самое.​​Обратите внимание на то,​​Не забываем менять диапазоны​ Вводим в поле​

​ Then Exit Sub​ такой результат.​Щелкните свойство​

​Элемент управления​​ «Поле со списком»​не отображается, на​Прежде всего, подготовим таблицу,​ будет появляться список​ вставленное значение из​

​ способами.​ выбирать для ввода​Теперь надо сформировать непрерывный​​ количество столбцов​​Если вам лень возиться​ что таблица должна​ на «свои». Списки​ «Источник» функцию вида​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​​Font​и настройте следующие​ более универсален: вы​
​ вкладке​​ где будут располагаться​ параметров, среди которых​ буфера УДАЛИТ ПРОВЕРКУ​Первый​ только заданные значения.​ (без пустых ячеек)​СЧЁТЗ(A2:A100)​ с вводом формулы​ иметь строку заголовка​ создаем классическим способом.​ =ДВССЫЛ(E3). E3 –​​ = 0 Then​​Необходимо сделать раскрывающийся список​(Шрифт), нажмите кнопку​ параметры.​ можете изменить свойства​​Файл​​ выпадающие списки, и​

​ можно выбрать любой​ ДАННЫХ И ВЫПАДАЮЩИЙ​: выделите список и​ Это особенно удобно​

​ список свободных сотрудников​​- размер получаемого​​ ДВССЫЛ, то можно​​ (в нашем случае​ А всю остальную​ ячейка с именем​​ lReply = MsgBox(«Добавить​​ со значениями из​​. ​​Формировать список по диапазону​ шрифта, чтобы текст​выберите​​ отдельно сделаем списки​​ для добавления в​
​ СПИСОК ИЗ ЯЧЕЙКИ,​ кликните правой кнопкой​ при работе с​ для связи -​ на выходе диапазона​ чуть упростить процесс.​​ это А1 со​​ работу будут делать​ первого диапазона.​ введенное имя «​​ динамического диапазона. Если​​и выберите тип,​

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

​ в которую вставили​​ мыши, в контекстном​

​ файлами структурированными как​ на следующем шаге​ по вертикали, т.е.​ После создания умной​ словом​ макросы.​Бывает, когда из раскрывающегося​ & _ Target​ вносятся изменения в​ размер или начертание​ содержащий список элементов.​ на листе с​

​>​ и мер измерения.​​Второй способ предполагает создание​​ предварительно скопированное значение.​ меню выберите «​ база данных, когда​​ — с выпадающим​​ столько строк, сколько​ таблицы просто выделите​Сотрудники​На вкладке «Разработчик» находим​ списка необходимо выбрать​ & » в​
​ имеющийся диапазон (добавляются​ шрифта.​Связь с ячейкой​ измененным масштабом. Кроме​Настроить ленту​Присваиваем каждому из списков​ выпадающего списка с​ Избежать этого штатными​Присвоить имя​ ввод несоответствующего значения​ списком. Для этого​​ у нас занятых​​ мышью диапазон с​). Первая ячейка играет​ инструмент «Вставить» –​ сразу несколько элементов.​ выпадающий список?», vbYesNo​ или удаляются данные),​Цвет шрифта​: поле со списком​ того, такое поле​. В списке​ именованный диапазон, как​ помощью инструментов разработчика,​ средствами Excel нельзя.​

Пользовательский список автозаполнения в EXCEL

Наверняка Вы уже использовали встроенные списки автозаполнения. Например, если ввести в ячейку слово Январь, а затем значение скопировать Маркером заполнения вниз на несколько ячеек, то все эти ячейки автоматически заполнятся последующими названиями месяцев: Февраль, Март. Хорошая новость, в EXCEL можно создавать пользовательские списки автозаполнения.

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

Встроенный список автозаполнения

Встроенный Список автозаполнения представляет собой отсортированный список данных, например:

Начальные значения

Продолжение ряда

Вторник, Среда, Четверг.

июл-99, окт-99, янв-00.

кв. 3 (или квартал 3)

текст2, текстA, текст3, текстA.

2-й период, 3-й период.

Товар 2, Товар 3.

Для заполнения ячеек, например, месяцами года необходимо:

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

Разработчики также позаботились о возможности ввода прописными буквами. К примеру, нам нужно ввести название месяцев ЯНВАРЬ, ФЕВРАЛЬ, МАРТ и т.д. прописными буквами. Для этого первое значение нужно ввести прописными буквами.

Ознакомиться со встроенными списками можно в пункте меню Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки.

Пользовательский список автозаполнения

В пункте меню Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки существует возможность создания пользовательского списка автозаполнения либо из существующих на листе элементов, либо путем непосредственного ввода списка.

Для наглядности приведем пример. Введем в диапазон ячеек А1:А4 стороны света: Север, Запад, Юг, Восток . Вызовем пункт меню Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки . Произведем импорт значений из ячеек.

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

Пользовательская сортировка списков

Списки автозаполнения можно использовать для сортировки значений. Для этого, предварительно выделив диапазон ячеек, значения которого нужно отсортировать, в окне Сортировка ( Данные/ Сортировка и фильтр/ Сортировка ) в поле Порядок нужно выбрать Настраиваемый список.

Как сделать автозаполнение в Excel

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

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

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

Функция автозаполнения.

Если вы просто хотите скопировать одно и то же значение вниз или вам нужно получить последовательность чисел или текстовых значений, маркер заполнения в Excel – это как раз то, что вам нужно.

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

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

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

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

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

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

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

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

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

Как заполнить большой диапазон двойным щелчком.

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

Примечание. Этот совет будет работать только в том случае, если у вас есть значения слева или справа от столбца, который необходимо заполнить. Дело в том, что Excel просматривает соседний столбец, чтобы определить последнюю ячейку в диапазоне для заполнения. Также имейте в виду, что если у вас есть значения справа и слева от пустого диапазона, в котором вы хотите сделать автозаполнение, то ряд ячеек будет заполнен по самому длинному столбцу.

Автозаполнение числами

Получаем список чисел от 1 до 10 буквально за пару кликов.

О других возможностях функции «Заполнить» читайте ниже.

Введите в первую ячейку диапазона начальное число (допустим, 1). Во время протягивания маркера заполнения вниз одновременно с зажатой левой копкой мыши удерживаем клавишу Ctrl (на Mac – клавишу Option ). Рядом с знакомым нам значком в виде черного плюсика при этом должен появиться еще один плюсик поменьше – слева и сверху.

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

Как сделать автозаполнение ячеек с текстом

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

Для параметра автозаполнения не проблема копировать значения, содержащие текст с числами. Excel будет искать закономерность в имеющихся числах. Что касается текста, он будет повторяться.

Как видите, инструмент автозаполнения отличает текст от чисел. И он даже знает, что есть 4 квартала.

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

Инструмент “Заполнить”

Сделать автозаполнение ячеек в Excel можно с помощью специального инструмента “Заполнить”. Алгоритм действий следующий:

1. Вносим информацию в исходную ячейку. После этого любым удобным способом (например, с помощью зажатой левой кнопки мыши) выделяем область ячеек, к которой хотим применить автозаполнение.
2. На ленте щелкаем по значку в виде стрелке вниз (это и есть инструмент “Заполнить”). Откроется перечень возможных вариантов, среди которых выбираем тот, который нужен (“вниз” в нашем примере).

Получаем автоматически заполненный диапазон ячеек. В данном случае – с датами.

С помощью функции “Заполнить” можно, в том числе, продолжить ряд прогрессии:

Как и в алгоритме выше, указываем в начальной ячейке первое значение. Затем выделяем требуемый диапазон, кликаем по инструменту “Заполнить” и останавливаемся на варианте “Прогрессия”.

Настраиваем параметры прогрессии в появившемся окошке:

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

Использование функции мгновенного заполнения

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

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

Введем имя и фамилию в С2. Затем при помощи меню выберем инструмент Мгновенное заполнение или нажмем комбинацию CTRL+E .

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

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

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

1. Введите заголовок и заполните свой список.

Примечание. Пользовательский список может содержать только текст или текст с числами. Если вам нужно хранить в нем только числа, создайте список чисел в текстовом формате.

1. Выберите диапазон ячеек, который содержит данные для вашего нового списка автозаполнения.
2. В Excel 2003 перейдите в Инструменты -> Параметры -> вкладка Пользовательские списки.

В Excel нажмите кнопку Office -> Параметры Excel -> Дополнительно -> прокрутите вниз, пока не увидите кнопку «Изменить настраиваемые списки…».

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

1. Нажмите кнопку «Импорт», чтобы поместить свой набор значений в поле «Пользовательские списки».

1. Наконец, нажмите OK, чтобы добавить собственный список автозаполнения.

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

Как получить повторяющуюся последовательность произвольных значений

Если вам нужна серия повторяющихся значений, вы все равно можете использовать автозаполнение. Например, вам нужно повторить ряд: ДА, НЕТ, ИСТИНА, ЛОЖЬ. Сначала введите все эти значения вручную, чтобы дать Excel шаблон. Затем просто возьмите мышкой маркер заполнения и перетащите его в нужную ячейку.

Автозаполнение по горизонтали и вертикали

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

Просто выберите ячейки со значениями и перетащите маркер заполнения в нужном направлении.

Автозаполнение сразу нескольких строк или столбцов

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

Как вставить пустые ячейки при автозаполнении

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

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

То же самое можно делать и с пустыми столбцами, протащив маркер вправо.

Как настроить параметры автозаполнения

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

1. Щелкните правой кнопкой мыши маркер заполнения и перетащите его. После этого вы увидите список с автоматически всплывающими опциями, как на скриншоте ниже:

Посмотрим, что предлагают эти варианты.

1. Копировать ячейки — заполняет диапазон одним и тем же значением.
2. Заполнить – автозаполнение заполнит диапазон в соответствии с заданным шаблоном.
3. Заполнить только форматы — эта опция автозаполнения Excel будет копировать только формат ячеек без извлечения каких-либо значений. Это может быть полезно, если вам нужно быстро скопировать форматирование, а затем ввести значения вручную.
4. Заполнить только значения — копирует только значения. Если фон исходных ячеек заполнен цветом, то он не сохранится.
5. Заполните дни / будни / месяцы / годы — эти функции делают то, что предполагают их названия. Если ваша начальная ячейка содержит один из них, вы можете быстро заполнить диапазон, щелкнув один из вариантов.
6. Линейное приближение – создает линейный ряд или линейный тренд наилучшего соответствия.
7. Экспоненциальное приближение – генерирует серию роста или геометрическую тенденцию роста.
8. Мгновенное заполнение – помогает ввести много повторяющейся информации и правильно отформатировать данные.
9. Прогрессия… — этот параметр вызывает диалоговое окно с перечнем дополнительных возможностей для выбора: какую именно числовую прогрессию вы хотите получить.

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

1. Другой способ получить список — щелкнуть маркер заполнения, перетащить его и затем щелкнуть значок «Параметры автозаполнения».

При нажатии на этот значок открывается список с параметрами.

Как видите, этот список просто повторяет некоторые функции, которые мы уже описали выше.

Самый быстрый способ автозаполнения формулами

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

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

Предположим, у вас есть таблица, и вы хотите добавить новый столбец с формулой. Например, в списке товаров нужно посчитать сумму, перемножив цену и количество.

1. Преобразуйте свой диапазон в таблицу Excel. Выберите любую ячейку в диапазоне данных и нажмите Ctrl+Т, чтобы вызвать диалоговое окно «Создать таблицу». Или используйте кнопку «Форматировать как таблицу» на ленте «Главная». Если в ваших данных есть заголовки столбцов, убедитесь, что установлен флажок «Таблица с заголовками». Обычно Excel распознает заголовки таблиц автоматически, а если нет, установите этот флажок вручную.
2. Введите формулу в первую ячейку пустого столбца.

1. Нажмите Enter. Excel автоматически заполняет все пустые ячейки в столбце этой формулой.

Если вы хотите по какой-то причине вернуться от таблицы к обычному диапазону данных, выберите любую ячейку в вашей таблице, затем по правому клику мышкой выберите Таблица => Преобразовать в диапазон.

Вставьте одни и те же данные в несколько ячеек, используя Ctrl+Enter

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

Для этого выполним следующие действия.

1. Выделим все пустые ячейки в столбце, которые вы хотите заполнить одними и теми же данными.
   Чтобы утомительно не кликать мышкой множество раз, вы можете использовать на ленте кнопку «Найти и заменить» – «Выделить группу ячеек».

1. Отредактируйте одну из выделенных ячеек, введя в нее нужное слово «нет»
2. Чтобы сохранить изменения, нажмите Ctrl+Enter вместо Enter . В результате все выбранные ячейки будут заполнены введенными вами данными, как на скриншоте ниже.

Как включить или отключить автозаполнение в Excel

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

1. Нажмите Файл в Excel 2010-2019 или кнопкуOffice в версии 2007.
2. Перейдите в Параметры -> Дополнительно и снимите флажок Разрешить маркер заполнения и перетаскивание ячеек.

На скриншоте выше вы видите, где находится в Excel автозаполнение.

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

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

Включение или отключение параметров автозаполнения

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

1. Перейдите к кнопке Файл / Офис -> Параметры -> Дополнительно и найдите раздел Вырезать, скопировать и вставить.
2. Снимите флажок Показывать кнопки параметров вставки при вставке содержимого.

Спасибо, что дочитали до конца. Теперь вы знаете все или почти все о функции автозаполнения.

Если вы знаете еще приемы, ускоряющие ввод данных, поделитесь ими в комментариях. Буду рад добавить их с вашим авторством в эту статью.

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

Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…
Относительные и абсолютные ссылки – как создать и изменить — В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,…
6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…
4 способа быстро убрать перенос строки в ячейках Excel — В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013…
Как безопасно удалить пустые ячейки в Excel и как не нужно никогда это делать — В этом руководстве вы узнаете, как правильно и безопасно удалять пустые ячейки в таблицах Excel, чтобы они выглядели четкими и профессиональными. Пустые ячейки – это неплохо, если вы намеренно оставляете…
Как быстро заполнить пустые ячейки в Excel? — В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять…

Списки автозаполнения

Если вы еще не знаете про такой прием в Excel, как автозаполнение ячеек путем протягивания мышью крестика — то самое время с ним познакомиться, т.к. инструмент весьма полезный. Что делает автозаполнение: допустим, нам надо заполнить строку или столбец днями недели (Понедельник, Вторник и т.д.). Без автозаполнения нам пришлось бы последовательно вводит в каждую ячейку вручную все эти дни. Но в Excel для выполнения подобной операции нам потребуется заполнить лишь первую ячейку. Запишем только в ячейку A1 Понедельник . Выделяем эту ячейку -наводим курсор мыши на правый нижний угол ячейки до появления черного крестика:

Как только курсор стал крестиком, зажимаем левую кнопку мыши и удерживая её тянем вниз(если надо заполнить строки) или вправо(если надо заполнить столбцы) на необходимое количество ячеек. Теперь все захваченные нами ячейки заполнены днями недели. И не одним Понедельником, а по порядку следования:

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

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

Выбираете необходимый пункт и вуаля!
Серым шрифтом выделены неактивные пункты меню — те, которые нельзя применить к данным в выделенных ячейках

Подобное автозаполнение доступно для числовых данных, для дат и некоторых распространенных данных — дней недели и месяцев. Но откуда Excel знает дни недели и имена месяцев? На основании «зашитых» в него списков. И эти списки можно дополнять своими.
Например, в шаблоне таблицы нам постоянно приходится записывать шапку руками: Дата, Артикул, Цена, Сумма . Можно их вписывать каждый раз руками или копировать из другой таблицы, но можно сделать и при помощи списков автозаполнения:

• Excel 2003 , то переходите Сервис (Tools) — Параметры (Options) -вкладка Списки (Lists)
• Excel 2007 — Кнопка Офис — Параметры Excel (Excel Options) -вкладка Основные (General) -кнопка Изменить списки (Edit Custom Lists)
• Excel 2010 и выше — Файл (File) — Параметры (Options) -вкладка Дополнительно (Advanced) -кнопка Изменить списки. (Edit Custom Lists)

Здесь мы увидим все «вшитые» в Excel списки автозаполнения:

Самый первый пункт в левой части — НОВЫЙ СПИСОК (NEW LIST) . Выделяем его и ставим курсор в поле правее — Элементы списка (List Entries) . Заносим туда наименования столбцов через запятую(как на рисунке выше: Дата, Артикул, Цена, Кол-во ) или занося каждый элемент с новой строки(перенос на новую строку производится клавишей Enter ). Нажимаем Добавить (Add) .
Так же можно воспользоваться полем Импорт списка из ячеек (Import list from cells) . Активируем поле выбора, щелкнув в нем мышкой. Выбираем диапазон ячеек со значениями, из которых хотим создать список. Жмем Импорт (Import) . В поле Списки (Custom lists) появиться новый список из значений указанных ячеек.
После добавления списков закрываем окно, нажав кнопку Ок.
Теперь проверим в действии. Пишем в любую ячейку слово Дата и протягиваем, как описано выше. Excel заполнит нам остальные столбцы значениями из того списка, который мы сами только что создали. Созданные нами списки можно редактировать и удалять. Встроенные изначально(например, дни недели) — удалять и изменять нельзя.

Дополнительное использование списков автозаполнения
Но списки автозаполнения помогут не только при записи значений на листе. Так же эти списки можно использовать и для сортировки значений. Для этого выделяем нужные для сортировки ячейки -переходим на вкладку Данные (Data) — Сортировка (Sort) . Раскрываем поле Порядок (Order) — Настраиваемый список (Custom list. ) . Выбираем нужный список из списков автозаполнения.
Рассмотрим жизненную ситуацию, применимую к созданному нами только что списку: Дата, Артикул, Цена, Кол-во .
Например, есть уже заполненная таблица, столбцы которой расположены не в том порядке, который нам нужен: Артикул, Дата, Кол-во, Цена. Как видно, порядок столбцов отличается от нашего эталонного. И нам надо упорядочить столбцы в том же порядке, в котором идет наш список(не путать с алфавитным). Выделяем полностью столбцы нашей таблицы -вкладка Данные (Data) — Сортировка (Sort) . В окне сортировки жмем кнопку Параметры (Options) — столбцы диапазона (sort left to right) . Жмем Ок. Теперь выбираем строку заголовка в поле сортировать по (sort by) — если заголовки для сортировки в первой строке, то выбираем Строка 1 (Row 1) , если во второй – Строка 2 (Row 2) и т.д. Раскрываем поле порядок (Order) -выбираем настраиваемый список (Custom list) и выбираем нужный нам список. Нажимаем ОК, и второй раз Ок уже в окне сортировки. Столбцы таблицы упорядочены в том порядке, который нам нужен.

Где это будет работать?
Эти списки работают в любой версии Excel, но есть одна ложка дегтя: созданные пользователем списки хранятся непосредственно на компьютере. Следовательно они будут доступны из любой книги на том ПК, на котором эти списки были созданы. И если переслать кому-то книгу, в которой ранее эти списки успешно использовались, новый пользователь их не увидит.