Автозаполнение ячеек Excel — это автоматическая вставка ряда данных в определенный диапазон. Вводим в ячейку «Понедельник», затем зажимаем индикатор автозаполнения (квадрат в правом нижнем углу) левой кнопкой мыши и перетаскиваем его вниз (или в другую сторону). Результатом будет список дней недели. Вы можете использовать короткие формы, такие как понедельник, вторник, среда и т.д. Excel поймет. Аналогичным образом создается список названий месяцев.
Автозаполнение ячеек также используется для расширения последовательности чисел с заданным шагом (арифметическая прогрессия). Чтобы создать список нечетных чисел, вам нужно указать 1 и 3 в двух ячейках, затем выбрать обе ячейки и перетащить вниз.
Excel также умеет распознавать числа в тексте. Следовательно, легко создать список окрестностей. Входим в ячейку «1 четверть» и удлиняем ее.
именно здесь у большинства пользователей Excel не хватает знаний об автозаполнении. Но это еще не все, и другие эффективные и интересные приемы будут рассмотрены далее.
Автозаполнение в Excel из списка данных
понятно, что кроме дней недели и месяцев могут понадобиться и другие списки. Например, часто приходится вводить список городов, в которых расположены сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Для начала нужно создать и сохранить (в правильном порядке) полный список имен. Перейдите в Файл — Параметры — Дополнительно — Общие — Редактировать списки.
В следующем открывшемся окне вы увидите списки, которые существуют по умолчанию.
Как видите, их не так много. Но добавить свой легко. Вы можете использовать окно справа, где и отдельная запятая, и столбец перечисляют желаемую последовательность. Однако импорт будет быстрее, особенно если данных много. Для этого мы сначала создаем список имен где-нибудь на листе Excel, затем создаем ссылку на него и нажимаем «Импорт.
Щелкните ОК. Список создан, вы можете использовать его для автозаполнения.
В дополнение к текстовым спискам вы с большей вероятностью создадите последовательности чисел и дат. Один из вариантов рассматривался в начале статьи, но он примитивен. Есть более интересные приемы. Во-первых, вам нужно выбрать одно или несколько первых значений в серии, а также диапазон (правый или нижний), в который будет расширена последовательность значений. Далее мы вызываем диалог развития: Главная — Заливка — Прогресс.
Давайте посмотрим на настройки.
В левой части окна с помощью переключателя можно задать направление построения последовательности: нижнее (по строкам) или правое (по столбцам).
В центре выбирается нужный тип:
- арифметическая прогрессия — каждое последующее значение изменяется на число, указанное в поле Step
- геометрическая прогрессия — каждое последующее значение умножается на число, указанное в поле Step
- дата — создать последовательность дат. Когда выбран этот тип, активируются переключатели справа, где вы можете выбрать тип единицы измерения. Есть 4 варианта:
- день — список календарных дат (с шагом, указанным ниже)
- рабочий день — последовательность рабочих дней (выходные пропускаются
- месяц — меняются только месяцы (число фиксированное, как в первой ячейке)
- год — меняются только годы
- автозаполнение: эта команда эквивалентна перетаскиванию левой кнопкой мыши. То есть дерево Excel само определяет: продолжить ли последовательность чисел или расширить список. Если вы предварительно заполните две ячейки значениями 2 и 4, в других выбранных ячейках отобразится 6, 8 и так далее. Если вы предварительно заполните несколько ячеек, Excel рассчитает приближение, используя метод линейной регрессии, то есть прогноз по прямой линии тренда.
Нижняя часть окна прогрессии используется для создания последовательности любой длины на основе конечного значения и шага. Например, вам нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Перетаскивать мышью неудобно. Следовательно, вы должны сначала выбрать только ячейку с первым значением. Затем в окне «Прогресс» укажите значение «Положение», «Шаг» и «Предельное значение».
Результатом будет полный столбец от 2 до 1000. Точно так же можно выполнить последовательность рабочих дней на год вперед (предельное значение должно быть последней датой, например, 31.12.2016). Возможность заполнения столбца (или строки) последним значением очень полезна, поскольку исключает серию ненужных действий при прошивке. На этом настройка автозаполнения завершена. Поехали дальше.
Автозаполнение чисел с помощью мыши
Автозаполнение в Excel удобнее делать с помощью мыши, у которой есть левая и правая кнопки. Вам понадобится и то, и другое.
Допустим, вам нужно создать порядковые номера чисел, начиная с 1. Обычно две ячейки заполняются числами 1 и 2, затем левой кнопкой мыши рисуется арифметическая прогрессия. Вы можете поступить иначе. Давайте просто заполним ячейку 1. Растяните ее, и вы получите столбец с ними. Затем откройте квадрат, который появляется сразу после растяжки в правом нижнем углу, и выберите «Заливка.
Если вы выберете «Только форматы заполнения», будут расширены только форматы ячеек.
Вы можете создать последовательность чисел еще быстрее. Перетаскивая ячейку, удерживайте клавишу Ctrl.
Этот трюк работает только с последовательностью чисел. В других ситуациях нажатие Ctrl приведет к копированию данных вместо автозаполнения.
Если вы используете правую кнопку мыши во время перетаскивания, контекстное меню открывается сразу после отпускания кнопки.
Это добавляет несколько команд. Прогресс позволяет использовать дополнительные операции автозаполнения (см. Настройки выше). Верно, диапазон выбран и длина последовательности будет ограничена последней ячейкой.
Для автоматического заполнения до необходимого лимита (числа или даты) вы можете выполнить следующий трюк. Щелкните индикатор правой кнопкой мыши, слегка потяните его вниз, сразу верните и отпустите кнопку: откроется контекстное меню автозаполнения. Выбор прогрессии. На этот раз выбрана только одна ячейка, поэтому мы указываем направление, шаг, предельное значение и создаем желаемую последовательность.
Очень интересны пункты меню Линейное и экспоненциальное приближение. Это экстраполяция, т.е данные прогноза по указанной модели (линейной или экспоненциальной). Обычно для прогнозирования используются специальные функции Excel или предварительно вычисляется уравнение тренда (регрессии), в котором значения независимой переменной заменяются на будущие периоды, а затем рассчитывается значение прогноза. Примерно так и делается. Допустим, есть динамика индикатора с равномерным ростом.
Линейный тренд подходит для прогнозирования. Расчет параметров уравнения можно выполнить с помощью функций Excel, но часто для наглядности используется диаграмма с настройками для отображения линии тренда, уравнения и прогнозируемых значений.
Чтобы получить прогноз в числовом выражении, необходимо произвести расчет на основе полученного уравнения регрессии (или обратиться непосредственно к формулам Excel). Поэтому есть некоторые действия, требующие хорошего понимания.
Следовательно, прогнозирование с использованием метода линейной регрессии может выполняться без формул и без диаграмм, используя только ячейки с автозаполнением в Excel. Для этого выберите данные, на которых строится прогноз, перетащите правой кнопкой мыши на необходимое количество ячеек, соответствующее длине прогноза, и выберите Линейное соответствие. Получаем прогнозы. Ни шума, ни пыли, ни формулы, ни диаграммы.
Если ваши данные быстро растут (например, счет на депозите), вы можете использовать экспоненциальную модель. Опять же, чтобы не тратить время на вычисления, вы можете использовать автозаполнение, выбрав Экспоненциальное приближение.
Вы не можете представить себе более быстрый способ прогнозирования.
Автозаполнение дат с помощью мыши
Очень часто бывает необходимо расширить список дат. Возьмите дату и перетащите ее левой кнопкой мыши. Откройте квадрат и выберите способ заливки.
Будние дни — отличный вариант для бухгалтеров, HR и других специалистов, которые занимаются составлением различных планов. Другой пример. Допустим, запланированные платежи должны производиться 15-го числа и в последний день каждого месяца. Указываем первые две даты, удлиняем и заполняем по месяцам (любой кнопкой мыши).
Обратите внимание, что 15-е число является фиксированным, а последний день месяца всегда остается последним.
Используя правую кнопку мыши, вы можете использовать настройки прогрессии. Например, составьте список рабочих дней до конца года. В списке команд при щелчке правой кнопкой мыши также присутствует Мгновенное заполнение. Эта функция была представлена в Excel 2013. Используется для заполнения ячеек по причине. Но по этому поводу уже была статья, советую ознакомиться. Это также поможет сэкономить более часа работы.
Это, пожалуй, все. В видеоуроке показано, как автоматически заполнять ячейки в Excel.