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