Автоматическое заполнение ячеек в excel по условию

Обновлено: 07.01.2025

Сегодня будет большая тема. Я постараюсь сделать её как можно меньше по объему, чтобы легче читалось. Во всех примерах буду преследовать цель - показать базовые возможности.

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

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

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

Отпускаем кнопку и получаем копии главной ячейки.

2. В нижнем правом углу появились параметры автозаполнения. Нажимаем на них:

3. Чтобы продолжить список значений по порядку, необходимо выбрать меню " Заполнить ":

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

5. Аналогично, нажимаем на параметры автозаполнения и ставим " Заполнить ":

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

Автозаполнение формул.

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

Давайте посмотрим на простой пример.

1. Напишем несколько значений для использования в формуле (выделено желтым). Все эти значения мы будем умножать на цифру 5 (синяя ячейка):

2. Напишем простую формулу умножения:

Я поставил значок доллара перед буквой B и цифрой 11 , чтобы данные значения оставались без изменения при использовании автозаполнения.

3. Полученную формулу можно скопировать, используя все тот же значок в правом нижнем углу ячейки:

4. В окончательном варианте получилось вот так:

Вывод: использование автозаполнения в формулах - основа для работы в Excel. Не забывайте про оператор "$" если хотите оставить значение без изменений.

Автозаполнение через инструменты.

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

1. Давайте в этот раз напишем не число, а день недели:

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

2. Переходим на вкладку " Главная ". Смотрим в раздел " Редактирование " и нажимаем на кнопку " Заполнить ":

3. Откроется список, выбираем пункт " Прогрессия ":

4. Перед нами окно с настройками прогрессии. Нажимаем на " Автозаполнение " и подтверждаем действие кнопкой " Ок ":

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

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

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

Если Вы внимательно читали прошлый пункт, то наверняка перед Вами возник вопрос - что еще может продолжить программа кроме дней недели?

1. Нажимаем на вкладку " Файл ". Спускаемся в самый низ и нажимаем на " Параметры ":

2. Переходим на вкладку " Дополнительно ". Спускаемся в самый низ и нажимаем на " Изменить списки ":

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

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

4. Чтобы добавить свой список, нужно выбрать " Новый список ", написать свои значения и сохранить их:

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

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

Спасибо за прочтение этой статьи! Если понравилось - ставьте лайки. Задавайте вопросы в комментариях. Буду рад помочь!

Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.

Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.

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

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

В следующем открывшемся окне видны те списки, которые существуют по умолчанию.

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

Жмем ОК. Список создан, можно изпользовать для автозаполнения.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выбирается нужный тип:

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

- - день – перечень календарных дат (с указанным ниже шагом)
  - рабочий день – последовательность рабочих дней (пропускаются выходные)
  - месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
  - год – меняются только годы
  - автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).
  Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.
  
  Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.
  
  Автозаполнение чисел с помощью мыши
  
  Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.
  
  Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.
  
  Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
  Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.
  
  Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.
  
  Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.
  
  При этом добавляются несколько команд. Прогрессия позволяет использовать дополнительные операции автозаполнения (настройки см. выше). Правда, диапазон получается выделенным и длина последовательности будет ограничена последней ячейкой.
  
  Чтобы произвести автозаполнение до необходимого предельного значения (числа или даты), можно проделать следующий трюк. Берем правой кнопкой мыши за маркер чуть оттягиваем вниз, сразу возвращаем назад и отпускаем кнопку – открывается контекстное меню автозаполнения. Выбираем прогрессию. На этот раз выделена только одна ячейка, поэтому указываем направление, шаг, предельное значение и создаем нужную последовательность.
  
  Очень интересными являются пункты меню Линейное и Экспоненциальное приближение. Это экстраполяция, т.е. прогнозирование, данных по указанной модели (линейной или экспоненциальной). Обычно для прогноза используют специальные функции Excel или предварительно рассчитывают уравнение тренда (регрессии), в которое подставляют значения независимой переменной для будущих периодов и таким образом рассчитывают прогнозное значение. Делается примерно так. Допустим, есть динамика показателя с равномерным ростом.
  
  Для прогнозирования подойдет линейный тренд. Расчет параметров уравнения можно осуществить с помощью функций Excel, но часто для наглядности используют диаграмму с настройками отображения линии тренда, уравнения и прогнозных значений.
  
  Чтобы получить прогноз в числовом выражении, нужно произвести расчет на основе полученного уравнения регрессии (либо напрямую обратиться к формулам Excel). Таким образом, получается довольно много действий, требующих при этом хорошего понимания.
  
  Так вот прогноз по методу линейной регрессии можно сделать вообще без формул и без графиков, используя только автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой кнопкой мыши на нужное количество ячеек, соответствующее длине прогноза, и выбираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.
  
  Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.
  
  Более быстрого способа прогнозирования, пожалуй, не придумаешь.
  
  Автозаполнение дат с помощью мыши
  
  Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.
  
  По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).
  
  Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.
  
  Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.
  
  На этом, пожалуй, все. В видеоуроке показано, как сделать автозаполнение ячеек в Excel.
  
  Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
  
  из сделать
  
  В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
  
  Способ 1. Без макросов
  
  Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
  
  Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
  
  Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
  
  И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
  
  В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
  
  Способ 2. Заполнение пустых ячеек макросом
  
  Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
  
  Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
  
  Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
  
  Способ 3. Power Query
  
  Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
  - Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
  - При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
  Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
  
  После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
  
  В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
  
  Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
  
  В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
  
  Иногда в Excel приходится вводить однотипные данные в определенной последовательности, например дни недели, даты или порядковые номера строк. Для автоматизации таких процессов разработчики внедрили в программу очень удобную функцию, которая называется Автозаполнение ячеек.
  
  Автозаполнение смежных ячеек
  
  В правом нижнем углу текущей ячейки имеется черный квадратик – маркер заполнения.
  
  При наведении указателя мыши на этот маркер, он приобретает форму тонкого черного крестика.
  
  Для проведения Автозаполнения:
  - введите значение в ячейку
  - снова сделать ячейку активной
  - наведите курсор на маркер заполнения, при появлении черного крестика нажмите на него левой или правой кнопкой мыши и, не отпуская кнопку, «протяните» маркер в нужном направлении по горизонтали или по вертикали
  - список заполнится автоматически, после того как кнопка будет отпущена.
  Автозаполнение используют в разных целях:
  - Копирование данных в другие ячейки (числа, текст, формулы);
  - Создание арифметических и геометрических прогрессий;
  - Создание различных последовательностей (названия месяцев, дни недели, даты, время);
  - Создание пользовательских последовательностей;
  - Копирование оформления ячеек.
  Примеры автозаполнения :
  
  Ряды, полученные с помощью автозаполнения
  
  Автозаполнение смежных ячеек числами
  
  1. Протягивание левой кнопкой мыши маркера ячейки, содержащей число, скопирует это число в последующие ячейки. Если при протягивании маркера удерживать клавишу <Ctrl> , то ячейки будут заполнены последовательными числами
  
  При протягивании вправо или вниз числовое значение увеличивается, при протягивании влево или вверх – уменьшается. По ходу протягивания на экране появляется всплывающая подсказка, которая показывает то значение, которое программа собирается вставить в следующую ячейку.
  Кнопка , которая появляется рядом с последней заполненной ячейкой, называется смарт-тегом автозаполнения . Щелкнув по этой кнопке, можно выбрать тип автозаполнения ячеек.
  
  2. При протягивании маркера заполнения правой кнопкой мыши появится контекстное меню, в котором можно выбрать нужную команду:
  - Копировать – все ячейки будут содержать одно и то же число;
  - Заполнить – ячейки будут содержать последовательные значения (с шагом арифметической прогрессии 1).
  Заполнение числами с шагом отличным от 1
  - заполнить две соседние ячейки нужными значениями;
  - выделить эти ячейки;
  - протянуть маркер заполнения.
  Таким образом, для сложных последовательностей, перед применением автозаполнения, необходимо самостоятельно заполнить сразу несколько ячеек, чтобы Excel правильно смог определить общий алгоритм вычисления их значений.
  - ввести начальное значение в первую ячейку;
  - протянуть маркер заполнения правой кнопкой мыши;
  - в контекстном меню выбрать команду Прогрессия ;
  - в диалоговом окне выбрать тип прогрессии – арифметическая и установить нужную величину шага (в нашем примере это – 3).
  Заполнение датами
  
  При автозаполнении дат можно выбрать: заполнение по дням, месяцам, годам, а также по рабочим дням.
  
  Читайте также: