Что такое автозаполнение и как оно выполняется. Автозаполнение ячеек и копирование формул в Excel

Как вы уже знаете, очень полезной возможностью MS Excel является автозаполнение ячеек типовыми наборами данных. То есть если ввести в ячейку «Апрель», а затем протащить её мышью на несколько соседних ячеек, они последовательно заполнятся названиями других месяцев: «Май», «Июнь» и так далее. Аналогичный фокус проходит с датами, названиями дней недели и даже просто с цифрами, что особенно удобно при нумерации строк таблицы.

Автозаполнение в MS Excel — очень удобная штука. Вписал первое значение, а остальные появятся автоматом

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

Итак, сегодня я избавлю вас от нудной рутины, когда один и тот же список приходится набивать вручную (на худой конец копировать из другого документа) — самое время научится формировать в MS Excel собственные списки автозаполнения!

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

Для начала, неплохо было бы как-то наш будущий список автозаполнения зафиксировать. Вы можете открыть таблицу с уже заполненным примером такого списка или набить его заново на черновике. Лично я начну с черновика и создам простейший автозаполняемый список из четырех пунктов с номерами годовых кварталов: «Первый квартал», «Второй квартал»… итак далее.

Сделали? Теперь выделяем наш список целиком, идем на вкладку «Файл» и выбираем в появившемся меню пункт «Параметры» .

Как только на экран будет выведено окно настроек программы, щелкаем в списке слева пункт «Дополнительно» , прокручиваем экран настроек почти до самого низа и находим кнопку «Изменить списки» .

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

Список автозаполнения Excel по умолчанию. А внизу — диапазон выбранных нами ячеек

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

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

Обратите внимание: удалить или редактировать заданные по умолчанию списки автозаполнения MS Excel (месяцы, дни недели и т.п.) — нельзя.

Ну всё, нажимаем «Ок» (и ещё раз «Ок») чтобы применить изменения. Самое время попробовать что у нас получилось. Пишем в первой ячейке «Первый квартал», протягиваем её за уголок… и получаем полностью сформированный автозаполняемый список, с чем вас и поздравляю.

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

Остается только добавить, что вы можете создать любое число пользовательских списков автозаполнения — никаких ограничений на этот счет в MS Excel не заложено.

Автозаполнение ячеек Excel — это автоматический ввод серии данных в некоторый диапазон. Введем в ячейку «Понедельник», затем удерживая левой кнопкой мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (или в другую сторону). Результатом будет список из дней недели. Можно использовать краткую форму типа Пн, Вт, Ср и т.д. Эксель поймет. Аналогичным образом создается список из названий месяцев.

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

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

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

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

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

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

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

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

Рассмотрим настройки.

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

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

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

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

• автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).

Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение , Шаг и Предельное значение .

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Автозаполнение чисел с помощью мыши

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

Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить .

Если выбрать Заполнить только форматы , будут продлены только форматы ячеек.
Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl .

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

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

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

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

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

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

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

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

Автозаполнение ячеек

Форматирование ячеек

• Выравнивание данных
• Установка параметров шрифта

Задания для самостоятельной работы

Автозаполнение ячеек

Автоматическое повторение элементов, уже введенных в столбец

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

Если необходимо чтобы запись полностью совпадала, то, как только она появилась в ячейке, нужно нажать клавишу Enter . Если запись нужна не полностью, то дойдя до нужного символа нажать клавишу Backspace (рис. 3.1).

Рис. 3.1. Автоматическое завершение

Заполнение данными с помощью маркера заполнения

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

После перемещения маркера заполнения отображается кнопка Параметры автозаполнения в виде квадратика, с помощью которой можно выбрать параметры заполнения выделенного диапазона ячеек. Так, для заполнения только форматов ячеек (установленное начертания, тип, размер, цвет шрифта) необходимо выбрать вариант Заполнить только форматы (Рис. 3.3.а), для заполнения только содержимого ячейки - вариант Заполнить только значения (Рис. 3.3.б), для копирования данного диапазона – Копировать ячейки (Рис. 3.3.в).

Рис. 3.3 Выбор команд с помощью маркера заполнения

Заполнение активной ячейки содержимым смежной ячейки

Для заполнения смежных ячеек необходимо выделить пустые ячейки, захватывая в выделении и ячейку с данными, снизу, справа, сверху или слева от ячейки, которая содержит данные для заполнения. На вкладке Главная в группе Редактирование выбрать команду Заполнить , а затем в открывшемся списке команду Вниз , Вправо , Вверх или Влево (рис. 3.4).

Примечание . Для быстрого заполнения ячейки данными из ячейки, которая находится сверху или слева, можно воспользоваться комбинациями клавиш Ctrl +D или Ctrl +R .

Рис. 3.4. Команда Заполнить

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

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

• ввести начальное значение в ячейку
• ввести значения в следующие ячейки, чтобы задать образец заполнения
• выделить заполненные ячейки

Например, если требуется задать последовательность 1, 2, 3, 4, 5..., в первые две ячейки нужно ввести значения 1 и 2. Если необходима последовательность 2, 4, 6, 8... – последовательность 2 и 4. Если необходима последовательность 2, 2, 2, 2..., то вторую ячейку можно оставить пустой.

Перетащить маркер заполнения по диапазону (до нужной ячейки), который нужно заполнить.

Примечание . Для заполнения в порядке возрастания необходимо перетащить маркер вниз или вправо. Для заполнения в порядке убывания – вверх или влево.

Если начальным значением является дата, например, «янв.13», то для получения ряда, состоящего из названия месяцев в том же формате, нужно выбрать команду Прогрессия из меню Заполнить группы Редактирование вкладки Главная (рис. 3.5.) и в появившемся диалоговом окне поставить в поле Единицы переключатель напротив значения месяц .

Рис. 3.5.Диалоговое окно Прогрессия

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

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

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

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

В диалогом окне Списки (рис. 3.6) нажать кнопку Импорт (в данном поле указываются адреса ячеек, которые включены в список). Элементы списка автоматически добавятся в виде нового списка. По завершению нажмите кнопку Ок .

Рис. 3.6. Диалоговое окно Списки

Чтобы создать новый список, так же его можно ввести в поле Элементы списка диалогового она Списки . Каждый элемент отделяется друг от друга нажатием на клавишу Enter . Затем нажать на кнопку Добавить и он отразиться в виде списка в поле Списки .

Для удаления списка нужно его выделить в диалоговом окне Списки и нажать кнопку Удалить . Подтвердить удаление и нажать кнопку Ок .

Форматирование ячеек

Для форматирования ячеек: выбор формата данных в ячейке, размещение текста в ячейке, установки типа, размера, цвета шрифта, оформление границ ячейки или таблицы, команда заливки – используются команды вкладки Главная групп Шрифт , Выравнивание , Число или диалоговое окно Формат ячеек .

Выбор формата отображения значения в ячейке

В предыдущей лекции рассматривался вопрос о типах данных: текст, число, формула. С помощью Числовых форматов можно указывать какие значения принимают данные в ячейке, например, денежный формат (2,00р. или $ 2,00), дата (15.07.13 или 15 июля 2013 г.), процентный (20,00%) и другие. Также для каждого формата устанавливаются свои параметры Для этого можно вызвать на экран диалоговое окно Формат ячеек (рис. 3.7) или воспользоваться командами группы Число вкладки Главная (рис. 3.8).

Рис. 3.7. Диалоговое окно Формат ячеек: Число

Рис. 3.8. Команды группы Число вкладки Главная

Помимо команд, относящихся к формату данных, в данной группе можно установить формат с разделителями (разделение разрядности, например, 2 000), увеличить / уменьшить разрядность (количество знаков после запятой, например, 2,35346 – при уменьшении разрядности до одного знака получится 2,4).

Выравнивание данных

Для выравнивания данных можно использовать команды группы Выравнивание вкладки Главная или команды диалогового окна Формат ячеек: выравнивание (рис. 3.9). В области выравнивание указывается расположение данных относительно ячейки по горизонтали и вертикали; в области отображение Переносить по словам можно заполнить данные ячейки в несколько строк, Автоподбор ширины – изменить размер шрифта, если данные оказались больше ширины ячейки, Объединение ячеек – из нескольких ячеек делает одну; в области Ориентация можно указать на какой угол нужно повернуть текст.

Рис. 3.9 Диалоговое окно Формат ячеек: Выравнивание

Установка параметров шрифта

С помощью команд группы Шрифт вкладки Главная или диалогового окна Формат ячеек: Шрифт (рис. 3.10) можно изменить тип, размер, начертание, цвет шрифта. С помощью команд Надстрочный и Подстрочный в поле Видоизменения устанавливаются верхний (t о) и нижний (t о) индексы.

Рис. 3.10 Диалоговое окно Формат ячеек: Шрифт

Обрамление выделенного диапазона и заливка

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

Предварительно выделив таблицу, в диалоговом окне Формат ячеек: Границы (рис.3.11) можно выбрать тип линии, установить цвет, а затем в области Отдельные указать с помощью мыши только те границы, которые следует установить для таблицы.

Рис. 3.11 Диалоговое окно Формат ячеек: Границы

Для заливки ячейки или группы ячеек используется команда Заливка из группы Шрифт вкладки Главная или диалогового окна Формат ячейки : Заливка . В последнем можно указать тип узора заливки и его цвет.

Задания для самостоятельной работы

1. Запустите программу MS Excel .

2. Создайте пользовательский автоматический список, состоящих из любых 4 элементов (например, времен года – зима, весна, лето, осень).

3. Оформите пользовательский список на листе 1 .

4. Переименуйте лист 2 в Факторы .

5. Оформите таблицу по образцу, учитывая расположение текста в ячейках, начертание, установленные границы к таблице:

6. Для данной таблице установите тип шрифта Arial; размер шрифта – 12, цвет – синий.

7. Поверните текст в ячейке В3 на 90 о.

8. На листе 3 создайте произвольную таблицу, в которую включены такие поля как Дата (установите нужный формат), Числовой (установите нужный формат, например, числовой с

указанием два знака после запятой, денежный, процентный). Установите границы к данной таблице и скопируйте ее на лист 1 .

9. Сохраните данный файл под именем Форматирование .

10. Закройте программу.

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

Например, на рис. 35.1 показан ряд последовательных чисел в столбце А. Ячейка А1 содержит значение 1, а ячейка А2 содержит формулу, которая была скопирована вниз по столбцу: =А1+1

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

1. Введите 1 в ячейку А1.
2. Введите 2 в ячейку А2.
3. Выберите А1:А2.
4. Переместите указатель мыши в правый нижний угол ячейки А2 (так называемый маркер заполнения ячейки) и, когда указатель мыши превратится в черный знак «плюс», перетащите его вниз по столбцу, чтобы заполнить ячейки.

Вы можете включать и отключать это поведение. Если у ячеек нет маркера заполнения, выберите Файл Параметры , перейдите в раздел Дополнительно диалогового окна Параметры Excel. Здесь в области Параметры правки установите флажок Разрешить маркеры заполнения и перетаскивание ячеек .

Данные, введенные в шагах 1 и 2, обеспечивают Excel необходимой информацией для определения типа серии, которую надо использовать. Если бы вы ввели 3 в ячейку А2, то серия бы состояла из нечетных чисел: 1,3, 5, 7 и т. д.

Вот еще один трюк автозаполнения: если данные, с которых вы начинаете, являются беспорядочными, Excel завершает автозаполнение, выполняя линейную регрессию и заполняя диапазон спрогнозированными значениями. На рис. 35.2 приведен лист с ежемесячными значениями продаж за январь-июль. При использовании автозаполнения после выбора С2:С8 Excel продлевает наиболее вероятную линейную тенденцию продаж и заполняет недостающие значения. На рис. 35.3 показаны спрогнозированные значения, а также график.

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

Таблица 35.1. Типы данных с возможностью автозаполнения

Вы также можете создавать собственные списки элементов для автоматического заполнения. Для этого откройте диалоговое окно Параметры Excel и перейдите в раздел Дополнительно . Затем прокрутите окно вниз и нажмите кнопку Изменить списки для отображения диалогового окна Списки . Введите ваши элементы в поле Элементы списка (каждый на новой строке). Затем нажмите кнопку Добавить , чтобы создать список. На рис. 35.4 показан пользовательский список названий регионов, которые используют римские цифры.

Для большего контроля над тем, что происходит при использовании автозаполнения, используйте правую кнопку мыши при перетаскивании маркера заполнения. Отпустив кнопку, вы увидите контекстное меню с некоторыми параметрами (рис. 35.5). Элементы, которые доступны в контекстном меню, зависят от типа выбранных данных. Например, если первая ячейка в серии содержит дату, то будут включены связанные с датой параметры.