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

2024 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2024-01-12 05:25

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Существует два способа заполнения рядов роста путем ввода первых двух чисел и ввода первого числа и значения шага.

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

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

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

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

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

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

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

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

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

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

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

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

Заполните ряд дат, используя команду заполнения

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

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

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

Заполнение серии использованием пользовательских элементов

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

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

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

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

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

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

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

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

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

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

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

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