Маркер автозаполнения в Excel: что это такое и как им пользоваться?

Всех приветствую на портале WiFiGiD.RU. В сегодняшнем небольшом уроке мы поговорим про очень важную функцию в Excel – автозаполнение ячеек. Напомню, что она позволяет в разы сократить время работы с большими таблицами. Функция позволяет автоматически заполнять ячейки выбранной информацией: текстом, числами, датами, формулами, днями недели, месяцами и своими списками. Давайте проверим, как же она работает. Поехали!

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

Как использовать функцию

Чтобы сделать автозаполнение ячеек в Excel – достаточно использовать специальный маркер заполнения. Что это такое? Маркер автозаполнения – это функциональный инструмент, который позволяет использовать автоматическое заполнение ячеек определенными данными, используя текст, цифры, формулы, дату и т.д.

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

Если маркера нет. Иногда пользователь не видит маленький квадратик в углу ячейки. Проверьте настройку: «Файл» – «Параметры» – «Дополнительно» – блок «Параметры правки» – включите пункт «Разрешить маркеры заполнения и перетаскивание ячеек». После этого маркер должен появиться. Если вы работаете в Excel Online или на Mac, внешний вид может немного отличаться, но сама логика автозаполнения остается такой же.

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

1. Выделить основной блок левой кнопкой мыши.
2. Теперь наведите курсор (или стрелочку) в правый нижний угол ячейки. Как только стрелка превратится в крестик – зажимаем левую кнопку мыши (ЛКМ) и тянем вниз до определенной строки.

3. Вы увидите, как рабочие блоки будут подсвечены полужирной рамкой.

4. Отпустите левую кнопку.
5. Теперь текстовая информация скопировалась во все выделенные ячейки.

Почему я начал именно с текста? – чтобы вы поняли, как программа работает с текстовыми ячейками. Она их просто копирует без каких-либо изменений. Можно таким же образом копировать информацию из двух, трех или любого количества ячеек. В таком случае они будут поочередно копировать все выделенные строки. Но вот куда более интересно обстоят дела с цифрами и формулами.

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

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

Работа с числовыми значениями

В этой главе мы прикоснемся уже к числовым значениям, и начнем с самого простого. Как вы думаете – сколько минимум чисел необходимо для автозаполнения, и что будет, если мы установим только одну цифру? Давайте проверим это. Смотрим на скриншот ниже. Если кто помнит уроки информатики в школе, то нас учили, что для быстрого заполнения цифрами нужно как минимум 2 цифры – например, 1 и 2. Попробуйте написать единицу и потянуть маркер автозаполнения.

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

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

Тут важно понимать разницу между «копировать» и «заполнить». Если Excel просто копирует единицу, то в каждой ячейке будет одно и то же число. Если выбрать заполнение ряда, программа начнет строить последовательность: 1, 2, 3, 4 и так далее. Для нумерации строк это как раз то, что нужно. А если вам нужно, чтобы во всех строках стоял один и тот же номер, например номер договора или код отдела, тогда оставляем обычное копирование.

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

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

Если нужно создать обычную нумерацию, я советую писать сразу два первых числа: 1 и 2. Тогда Excel почти всегда правильно понимает, что нужно продолжить ряд. Если написать 10 и 20, программа продолжит 30, 40, 50. Если написать 100 и 95, получится обратная последовательность 90, 85, 80. То есть Excel смотрит на шаг между первыми значениями и продолжает его дальше.

Полезный пример. Нужно быстро сделать номера строк от 1 до 500. Пишите в первые две ячейки 1 и 2, выделяете обе ячейки, тянете маркер вниз. Если рядом уже есть заполненный столбец с данными, можно дважды щелкнуть по маркеру. Excel сам протянет нумерацию до конца соседней таблицы.

Инструмент

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

1. Поставьте две цифры прогрессии и выделите все ячейки, которые вы хотите заполнить.

2. Перейдите на вкладку «Главная».

3. В разделе редактирования найдите инструмент «Заполнить» и выберите команду «Вниз».

4. Как видите, ничего не получилось, и функция просто скопировала содержимое первой ячейки. Самое интересное, что параметры мы вызывать тоже не можем. Давайте вернемся на шаг назад, зажав кнопки:

Ctrl + Z

5. Выберите команду «Прогрессия».

6. Откроется окно с дополнительными параметрами. Давайте немного разберем их:
   1. Расположение – можно изменить расположение по строкам или столбцам, но лучше работать с выделением и не менять этот параметр.
   2. Тип – здесь мы можем задать конфигурацию прогрессии. По умолчанию в прошлом способе программа выбирает арифметическую прогрессию. Давайте для примера попробуем геометрическую.
   3. Единицы. Прогрессия может работать с датой – например, вы можете задать прибавление по дням, месяцам, годам или по рабочим дням.
   4. Шаг – параметр, задающий количество значений в одном шаге. Можно установить автоматическое значение.
   5. Предельное значение – если вам нужно задать ограничение, то его стоит вводить в эту строку.

7. В конце жмем «ОК».

Инструмент «Прогрессия» особенно удобен, когда нужно заполнить большой диапазон без протягивания мышью. Например, вам нужна последовательность от 1 до 10000 или даты через каждые 7 дней. Тянуть маркер до конца такой таблицы неудобно, а через «Прогрессию» можно сразу указать шаг и предельное значение. Это также помогает, если мышь или тачпад работают неудобно, а таблица большая.

Где искать инструмент. В большинстве версий Excel он находится на вкладке «Главная» – блок «Редактирование» – «Заполнить» – «Прогрессия». Если окно Excel узкое, кнопка может быть спрятана в свернутом меню. Разверните окно на весь экран или поищите значок в правой части вкладки «Главная».

Формулы

Функция также может работать с выделенными формулами. Давайте разберем на конкретном примере. В нашем виртуальном магазине на складе у нас хранится определенный товар (шоколад, вода, молоко, чай, кофе и т.д.). У нас есть информация о количестве товара в штуках (пачках) и о цене за одну единицу.

1. Чтобы не считать все вручную, мы будем использовать формулы. В столбце «Общей стоимости» пытаемся умножить:

Количество * Цена за штуку

2. Выделяем ячейку и вводим формулу, начиная со знака равно (=). Напомню, что адреса ячеек не обязательно вводить вручную, достаточно их выделить ЛКМ. Можете посмотреть на пример как у меня.

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

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

В данном примере мы использовали формулы с динамическими показателями. То есть при автозаполнении программа изменяет адреса ячеек по строкам. Если первая формула ссылалась на строку 2, то ниже она начнет ссылаться на строку 3, 4, 5 и так далее. Это нормальное поведение Excel, потому что чаще всего при протягивании формул нам как раз и нужно, чтобы расчет переходил на следующую строку.

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

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

1. Я добавил в нашу табличку еще один столбец – там будет отображена стоимость товаров в нашем магазине. Теоретически – это прибыль, которую мы получим за продажу всех этих товаров. И коэффициент, который равен 1,2.

2. Теперь умножаем общую стоимость и коэффициент, который находится в отдельной ячейке. Но так как мы будем использовать не изменяющуюся цифру в одной ячейке, то нам нужно ее зафиксировать. Для этого мы используем в формуле знаки доллара ($). Причем мы можем отдельно зафиксировать изменение значения по строке или по столбцу. Например, если вы хотите, чтобы не менялся только столбец, тогда ставим доллар перед буквой. Если вы хотите, чтобы не менялась только строка, то ставим доллар перед цифрой.

$B$8 – статическая ячейка.
$B8 – не меняется только столбец.
B$8 – не меняется только строка.

3. А теперь еще раз проверьте, чтобы в других строках были правильные формулы.

Чтобы не ставить доллары вручную, можно использовать клавишу F4. Поставьте курсор внутри ссылки на ячейку в формуле и нажимайте F4. Excel будет переключать варианты: $B$8, B$8, $B8 и снова B8. Это сильно ускоряет работу, когда вы часто протягиваете формулы и используете коэффициенты, проценты, курсы валют или фиксированные значения.

Пример из жизни. У вас есть список товаров и рядом цена закупки. В отдельной ячейке стоит наценка 20%. Если не закрепить ячейку с коэффициентом, после протягивания формула начнет съезжать вниз и брать пустые ячейки. В итоге часть строк посчитается неправильно. Поэтому коэффициенты, ставки, проценты и курсы чаще всего нужно фиксировать знаками доллара. Если хотите отдельно потренироваться с процентами, посмотрите урок: процент от числа в Excel.

Мгновенное заполнение

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

Ctrl + E

Например, в одном столбце у вас написано «Иванов Иван Петрович», а рядом вы вручную пишете только фамилию «Иванов». Если ниже нажать Ctrl + E, Excel может сам вытащить фамилии из остальных строк. То же самое работает для объединения имени и фамилии, извлечения части артикула, отделения номера дома или преобразования текста по образцу.

Проверяйте результат. «Мгновенное заполнение» угадывает шаблон, но иногда ошибается. Если в списке есть разные форматы записи, лишние пробелы, дефисы, двойные фамилии или неполные данные, результат может быть неправильным. Поэтому после такого заполнения хотя бы бегло просмотрите несколько строк сверху, в середине и внизу таблицы.

Если функция не срабатывает автоматически, ее можно запустить вручную через вкладку «Данные» – «Мгновенное заполнение» или клавишами Ctrl + E. В некоторых случаях нужно дать Excel два-три примера, чтобы он понял закономерность. Это удобно, когда нужно быстро разобрать текстовые данные без сложных формул. А если вам нужно не просто заполнение, а поиск значений в другой таблице, уже лучше смотреть функции вроде ВПР: формула ВПР в Excel для чайников.

Дата

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

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

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

Дополнительно можно изменять только цифру, но оставлять строковое значение. Например, вы можете расставить все номера домов, корпусов, комнат или других подобных объектов. Excel автоматически находит цифру и оставляет параметр строки в покое. Лучше всего использовать автозаполнение от двух строк.

Для рабочих графиков и расписаний полезно знать про заполнение по рабочим дням. Если протянуть дату и открыть параметры автозаполнения, можно выбрать вариант «Заполнить по рабочим дням». Тогда Excel пропустит субботу и воскресенье. Это удобно для графиков поставок, планов задач, рабочих отчетов и расписаний. Но праздники Excel сам не учитывает, если вы отдельно не создаете свой календарь или не используете дополнительные формулы.

Свой список

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

1. Откройте первую вкладку «Файл».

2. Перейдите в «Параметры».

3. В разделе «Дополнительно» листаем до подраздела «Общие» и жмем по кнопке «Изменить списки».

4. В правый блок через перенос на новую строчку (Enter) вводим значения элементов списка. В конце жмем «Добавить».

5. Проверьте, чтобы список добавился. Два раза жмем «ОК».

6. Теперь пробуем его использовать.

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

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

Если автозаполнение не работает

Иногда маркер вроде есть, но автозаполнение ведет себя странно: копирует одно значение вместо ряда, не протягивает формулу, не показывает параметры или вообще не реагирует. Начните с простого:

• Проверьте, включен ли маркер заполнения: «Файл» – «Параметры» – «Дополнительно» – «Разрешить маркеры заполнения и перетаскивание ячеек».
• Убедитесь, что вы тянете именно маленький квадратик в правом нижнем углу выделения.
• Если ряд не продолжается, выделите два первых значения, например 1 и 2, а не только одну единицу.
• Если формула скопировалась неправильно, проверьте относительные и абсолютные ссылки.
• Если числа не считаются, проверьте формат ячеек – возможно, они записаны как текст.
• Если автозаполнение остановилось раньше времени, рядом может быть пустая строка или пустой столбец.

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

Быстрая проверка. Если Excel не продолжает нумерацию, напишите в соседних ячейках 1 и 2, выделите обе и протяните вниз. Если так сработало, значит с маркером все нормально, просто Excel не понял закономерность по одному числу. Если не сработало даже так, проверяйте настройки маркера заполнения.

Горячие клавиши для заполнения

Не всегда удобно тянуть мышью. В Excel есть несколько быстрых сочетаний:

• Ctrl + D – заполнить вниз. Копирует значение или формулу из верхней ячейки выделенного диапазона вниз.
• Ctrl + R – заполнить вправо. Копирует значение или формулу из левой ячейки выделенного диапазона вправо.
• Ctrl + E – мгновенное заполнение по образцу.
• Ctrl + Z – отменить действие, если заполнили не так.
• F4 – переключить ссылку в формуле между относительной и абсолютной.

Эти сочетания особенно полезны, когда таблица большая. Например, выделили формулу и пустые ячейки ниже, нажали Ctrl + D – формула скопировалась вниз. Или выделили значение и ячейки справа, нажали Ctrl + R – значение ушло вправо. А если случайно заполнили лишнее, всегда можно нажать Ctrl + Z. По отмене действий у нас есть отдельная простая инструкция: как отменить действие на компьютере.

FAQ – частые вопросы

Почему Excel копирует число, а не продолжает ряд?
Чаще всего потому, что вы выделили только одну ячейку с числом. Excel не всегда понимает, что нужно продолжить 1, 2, 3, 4. Напишите первые два числа, например 1 и 2, выделите обе ячейки и протяните маркер. Еще можно открыть параметры автозаполнения после протягивания и выбрать заполнение ряда.

 

Где находится маркер автозаполнения?
Он находится в правом нижнем углу выделенной ячейки или диапазона. Это маленький квадратик. Наведите на него мышь, и курсор должен превратиться в тонкий черный крестик. Если квадратика нет, проверьте настройку «Разрешить маркеры заполнения и перетаскивание ячеек» в параметрах Excel.

 

Можно ли автозаполнить формулы вниз без мыши?
Да. Выделите ячейку с формулой и пустые ячейки ниже, затем нажмите Ctrl + D. Формула скопируется вниз. Если нужно заполнить вправо, используйте Ctrl + R. Но после копирования все равно проверьте ссылки в формулах.

 

Почему формула после протягивания считает неправильно?
Скорее всего, в формуле сдвинулась ссылка на ячейку, которую нужно было закрепить. Например, коэффициент, процент или курс валют должен оставаться в одной ячейке, а Excel при протягивании сдвигает его вниз. В таком случае используйте абсолютную ссылку со знаками доллара, например $B$8. Переключать варианты удобно клавишей F4.

 

Что такое мгновенное заполнение?
Это функция, которая заполняет данные по вашему примеру. Например, вы показали Excel, как из полного имени нужно получить фамилию, и программа попробует повторить это для остальных строк. Запускается через [kbd]Ctrl + E или через вкладку «Данные» – «Мгновенное заполнение». Она удобна для текста, но результат нужно проверять.

 

Можно ли создать свой порядок автозаполнения?
Да. Для этого откройте «Файл» – «Параметры» – «Дополнительно» – «Изменить списки» и добавьте свои значения. Например, можно создать список отделов, филиалов, статусов задач или этапов сделки. После этого Excel сможет продолжать этот список через маркер автозаполнения.

 

Почему автозаполнение даты показывает числа?
Excel хранит дату как число, а показывает ее в формате даты. Если после автозаполнения вы видите обычные числа, значит у ячеек стоит числовой или общий формат. Выделите диапазон, нажмите правой кнопкой мыши, откройте «Формат ячеек» и выберите «Дата».

В этом уроке мы узнали много нового. Если у вас еще остались какие-то вопросы по теме – пишите их в комментариях. На этом я с вами прощаюсь и жду вас в новом уроке. Пока-пока.

Видео

YouTube