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

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

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

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

Один из способов использовать дескриптор заполнения - это ввести ряд линейных данных в строку или столбец соседних ячеек. Линейный ряд состоит из чисел, где следующее число получается путем добавления «значения шага» к числу перед ним. Простейшим примером линейного ряда является 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» во всплывающем меню..

Другие четыре 1 заменены на 2-5, и наша линейная серия заполнена.

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

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

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

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

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

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

Выберите «Серия» из выпадающего меню.

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

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

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

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

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

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

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

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

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

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

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

Заполните формулы в соседних клетках

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заполните серию, используя встроенные элементы

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

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

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

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

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

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

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

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

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

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

На правой панели прокрутите вниз до раздела General и нажмите кнопку «Edit Custom Lists».

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

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

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

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

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

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

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

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

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

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

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

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