Как автоматически заполнять последовательные данные в Excel с помощью маркера заполнения

Маркер заполнения в Excel позволяет автоматически заполнять список данных (чисел или текста) в строке или столбце, просто перетаскивая маркер. Это может сэкономить много времени при последовательном вводе данных в большие рабочие листы и повысить производительность.

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

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

Один из способов использования маркера заполнения - ввести серию линейных данных в строку или столбец соседних ячеек. Линейный ряд состоит из чисел, где следующее число получается добавлением «значения шага» к числу перед ним. Простейшим примером линейного ряда является 1, 2, 3, 4, 5. Однако линейный ряд также может быть серией десятичных чисел (1,5, 2,5, 3,5…), уменьшающих числа на два (100, 98, 96 …) Или даже отрицательные числа (-1, -2, -3). В каждой линейной серии вы добавляете (или вычитаете) одно и то же значение шага.

Допустим, мы хотим создать столбец последовательных чисел, увеличиваясь на единицу в каждой ячейке. Вы можете ввести первое число, нажать Enter, чтобы перейти к следующей строке в этом столбце, и ввести следующее число, и так далее. Очень утомительно и требует много времени, особенно для больших объемов данных. Мы сэкономим время (и избавимся от скуки), используя дескриптор заполнения для заполнения столбца линейными рядами чисел. Для этого введите 1 в первую ячейку столбца, а затем выберите эту ячейку. Обратите внимание на зеленый квадрат в правом нижнем углу выделенной ячейки? Это дескриптор заполнения.

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

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

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

Почему он не заполнил линейный ряд (в нашем примере 1, 2, 3, 4, 5)? По умолчанию, когда вы вводите одно число, а затем используете маркер заполнения, это число копируется в соседние ячейки, а не увеличивается.

ПРИМЕЧАНИЕ. Чтобы быстро скопировать содержимое ячейки над текущей выбранной ячейкой, нажмите Ctrl + D, или чтобы скопировать содержимое ячейки слева от выбранной ячейки, нажмите Ctrl + R. Имейте в виду, что копирование данных из соседней ячейки заменяет любые данные, которые в данный момент находятся в выбранной ячейке.

Чтобы заменить копии линейной серией, нажмите кнопку «Параметры автозаполнения», которая отображается, когда вы закончите перетаскивать маркер заполнения.

По умолчанию используется первый вариант «Копировать ячейки». Вот почему мы получили пять единиц, а не линейный ряд от 1 до 5. Чтобы заполнить линейный ряд, мы выбираем «Fill Series» во всплывающем меню.

Остальные четыре единицы заменяются цифрами 2–5, и наш линейный ряд заполняется.

Однако вы можете сделать это, не выбирая «Серия заливки» в меню «Параметры автозаполнения». Вместо того, чтобы вводить только одно число, введите первые два числа в первые две ячейки. Затем выберите эти две ячейки и перетащите маркер заполнения, пока не выберете все ячейки, которые хотите заполнить.

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

Вы также можете щелкнуть и перетащить маркер заполнения правой кнопкой мыши, а не левой. Вам все равно нужно выбрать «Fill Series» во всплывающем меню, но это меню автоматически отображается, когда вы прекращаете перетаскивать и отпускаете правую кнопку мыши, так что это может быть удобным ярлыком.

Заполните линейную серию смежных ячеек с помощью команды Fill

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

Чтобы использовать команду «Заливка» на ленте, введите первое значение в ячейку и выберите эту ячейку и все соседние ячейки, которые вы хотите заполнить (либо вниз, либо вверх по столбцу, либо слева или справа по строке). Затем нажмите кнопку «Заливка» в разделе «Редактирование» на вкладке «Главная».

В раскрывающемся меню выберите «Серии».

В диалоговом окне «Серии» выберите тип «Серии в строках или столбцах». В поле «Тип» выберите на данный момент «Линейный». Мы обсудим параметры «Рост» и «Дата» позже, а параметр «Автозаполнение» просто копирует значение в другие выбранные ячейки. Введите «Значение шага» или приращение для линейного ряда. В нашем примере мы увеличиваем числа в нашей серии на 1. Нажмите «ОК».

Линейный ряд заполняется выбранными ячейками.

Если у вас есть действительно длинный столбец или строка, которые вы хотите заполнить линейным рядом, вы можете использовать значение Stop в диалоговом окне Series. Для этого введите первое значение в первую ячейку, которую вы хотите использовать для ряда в строке или столбце, и снова нажмите «Заполнить» на вкладке «Главная». В дополнение к параметрам, которые мы обсуждали выше, введите значение в поле «Stop value», которое вы хотите использовать в качестве последнего значения в серии. Затем нажмите «ОК».

В следующем примере мы помещаем 1 в первую ячейку первого столбца, а числа от 2 до 20 будут автоматически введены в следующие 19 ячеек.

Заполните линейный ряд, пропуская ряды

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

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

Затем перетащите маркер заполнения вниз (или поперек), пока не заполните нужное количество ячеек.

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

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

Вы также можете пропускать столбцы при заполнении строк.

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

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

СВЯЗАННЫЕ: Зачем вам нужны формулы и функции?

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

СВЯЗАННЫЕ: Как вручную рассчитать только активный рабочий лист в Excel

ПРИМЕЧАНИЕ. Скопированные формулы не будут пересчитаны, если у вас не включен автоматический расчет книги.

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

Заполните линейную серию двойным щелчком по ручке заливки

Вы можете быстро заполнить линейный ряд данных в столбце, дважды щелкнув маркер заполнения. При использовании этого метода Excel заполняет ячейки в столбце только на основе самого длинного соседнего столбца данных на вашем листе. Смежный столбец в этом контексте - это любой столбец, который Excel обнаруживает справа или слева от заполняемого столбца, пока не будет достигнут пустой столбец. Если столбцы по обе стороны от выбранного столбца пусты, вы не можете использовать метод двойного щелчка для заполнения ячеек в столбце. Кроме того, по умолчанию, если некоторые из ячеек в диапазоне ячеек, которые вы заполняете, уже содержат данные, заполняются только пустые ячейки над первой ячейкой, содержащей данные. Например, на изображении ниже в ячейке G7 есть значение, поэтому, когда вы дважды щелкаете дескриптор заполнения в ячейке G2, формула копируется только через ячейку G6.

Заполните серию роста (геометрический узор)

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

Есть два способа заполнить ряд роста: ввести первые два числа и ввести первое число и значение шага.

Метод первый: введите первые два числа в ряду роста

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

ПРИМЕЧАНИЕ. Для этого метода необходимо ввести два числа. Если вы этого не сделаете, опция «Тенденция роста» будет недоступна.

Excel знает, что значение шага равно 2 из двух чисел, которые мы ввели в первые две ячейки. Итак, каждое последующее число вычисляется путем умножения предыдущего числа на 2.

Что, если вы хотите начать с числа, отличного от 1, используя этот метод? Например, если вы хотите начать вышеуказанный ряд с 2, вы должны ввести 2 и 4 (потому что 2 × 2 = 4) в первых двух ячейках. Excel определит, что значение шага равно 2, и продолжит серию роста с 4, умножая каждое последующее число на 2, чтобы получить следующее в строке.

Метод второй: введите первое число в серии роста и укажите значение шага

Чтобы заполнить ряд роста на основе одного числа и значения шага, введите первое число (оно не обязательно должно быть 1) в первую ячейку и перетащите маркер заполнения по ячейкам, которые вы хотите заполнить. Затем выберите «Серии» во всплывающем меню, которое отображается автоматически.

В диалоговом окне «Серии» выберите тип заполнения «Серии - строки» или «Столбцы». В разделе «Тип» выберите «Рост». В поле «Значение шага» введите значение, на которое нужно умножить каждое число, чтобы получить следующее значение. В нашем примере мы хотим умножить каждое число на 3. Нажмите «ОК».

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

Заполнение серии с помощью встроенных элементов

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

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

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

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

В диалоговом окне «Серии» автоматически выбирается параметр «Серии в», соответствующий выбранному набору ячеек. Тип также автоматически устанавливается на Дата. Чтобы указать приращение, используемое при заполнении ряда, выберите единицу измерения даты (день, день недели, месяц или год). Укажите значение Шага. Мы хотим заполнить ряд каждым днем ​​недели, поэтому мы вводим 1 в качестве значения шага. Щелкните «ОК».

Серии заполнены датами, которые относятся только к будним дням.

Заполнение серии с помощью настраиваемых элементов

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

На экране за кулисами щелкните «Параметры» в списке элементов слева.

Щелкните «Дополнительно» в списке элементов в левой части диалогового окна «Параметры Excel».

На правой панели прокрутите вниз до раздела «Общие» и нажмите кнопку «Редактировать настраиваемые списки».

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

Метод первый: заполнение настраиваемой серии на основе нового списка элементов

В диалоговом окне «Пользовательские списки» убедитесь, что в поле «Пользовательские списки» выбран параметр «НОВЫЙ СПИСОК». Щелкните поле «Список записей» и введите элементы в свои настраиваемые списки, по одному элементу в строку. Обязательно вводите элементы в том порядке, в котором они должны быть заполнены в ячейки. Затем нажмите «Добавить».

Пользовательский список добавляется в поле «Пользовательские списки», где вы можете выбрать его и отредактировать, добавив или удалив элементы из поля «Записи списка» и снова нажав «Добавить», или вы можете удалить список, нажав «Удалить». Щелкните «ОК».

Нажмите «ОК» в диалоговом окне «Параметры Excel».

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

Метод второй: заполнение настраиваемой серии на основе существующего списка элементов

Возможно, вы храните свой собственный список на отдельном листе в своей книге. Вы можете импортировать свой список из рабочего листа в диалоговое окно «Настраиваемые списки». Чтобы создать настраиваемый список на основе существующего списка на листе, откройте диалоговое окно «Настраиваемые списки» и убедитесь, что НОВЫЙ СПИСОК выбран в поле «Настраиваемые списки», как и в первом методе. Однако для этого метода нажмите кнопку диапазона ячеек справа от поля «Импортировать список из ячеек».

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

Теперь нажмите «Импорт».

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

Дескриптор заполнения в Excel - очень полезная функция, если вы создаете большие листы, содержащие много последовательных данных. Вы можете сэкономить много времени и утомления. Удачного наполнения!