Как повторить формулу в следующих строках Excel

Обновлено: 02.07.2024

Предположим, у вас есть рабочий лист, содержащий набор формул в ячейках A1:A3. Ячейка A1 содержит формулу =Лист1!B4, ячейка A2 содержит формулу =Лист1!B18, а ячейка A3 содержит формулу =Лист1!B32. Возможно, вам придется продолжить этот шаблон вниз по столбцу, чтобы A4 содержал =Sheet1!B46 и т. д.

Проблема в том, что если вы просто скопируете ячейки, шаблон не будет продолжен. Вместо этого формулы корректируются на основе отношения целевой ячейки к исходной ячейке. Таким образом, если вы вставите A1:A3 в A4:A6, то A4 будет содержать =Sheet1!B7, что вам не нужно. (Это происходит, если вы специально копируете и вставляете или заполняете ячейки, перетаскивая маркер заполнения.)

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

Эта формула создает ссылку на основе положения ячейки, в которой находится формула. Если эта формула помещена в ячейку A1, то функция СТРОКА возвращает 1, строку, в которой находится формула. Таким образом, формула становится такой:

Возвращается значение на Sheet1!B4, как и требовалось изначально. Однако, когда вы копируете эту формулу вниз по столбцу, функция СТРОКА возвращает что-то другое в каждой строке. По сути, функция СТРОКА становится способом увеличить смещение каждой формулы на 14 строк по сравнению с предыдущей — именно то, что вы хотели.

Вы также можете использовать немного другой подход, на этот раз с помощью функции OFFSET:

Эта формула получает значение на основе строки, в которой находится формула (опять же, с помощью функции ROW), и смещения от ячейки Sheet1!B4. Помещенная в первую строку столбца, а затем скопированная вниз по этому столбцу, формула возвращает значения в соответствии с желаемым шаблоном.

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

Начните с нового пустого рабочего листа в рабочей книге, содержащей рабочий лист с именем "Лист1".
В ячейку A1 введите формулу =Лист1!B4.
Поскольку шаблон формулы состоит из каждых 14 строк, выделите диапазон A1:A14. Формула должна быть только в первой ячейке; остальные 13 пусты.
Перетащите маркер заполнения (маленький квадрат в правом нижнем углу выделенного фрагмента) вниз на достаточное количество строк — примерно 1000. Диапазон A1:A1000 все равно должен быть выделен.
Выберите «Редактировать» | Перейти | Особый. Excel отображает диалоговое окно «Перейти к специальному». (См. рис. 1.)

Рисунок 1. Диалоговое окно «Перейти к специальному».

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

Нажмите ячейку, содержащую формулу, которую вы хотите скопировать в строку.

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

Нажмите «Ctrl-R», чтобы автоматически заполнить все ячейки одной и той же формулой.

Форматирование с помощью параметров автозаполнения

Выберите ячейку, содержащую формулу. Щелкните маркер "Заполнить" в ячейке, который представляет собой маленький черный квадрат в правом нижнем углу ячейки.

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

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

Чтобы скопировать формулу вниз по столбцу, выберите ячейку с формулой, перетащите курсор вниз, пока не будут выбраны все ячейки в столбце, в который вы хотите скопировать формулу, а затем нажмите "Ctrl-D".< /li>

Информация в этой статье относится к Microsoft Excel 2013 или Office 365, работающим в Windows 8. Она может незначительно или существенно отличаться в зависимости от других версий или продуктов.

Андреа Руис уже более десяти лет профессионально пишет для блогов, развлекательных онлайн-журналов и веб-сайтов телевизионных сетей.Руиз также был разработчиком веб-сайтов и социальных сетей, консультантом по интернет-бизнесу и программистом с 1999 года и четыре года работал профессиональным менеджером сообщества. Руис имеет степень бакалавра искусств Массачусетского университета в Бостоне.

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

Если вы ищете самые простые способы повторения шаблонов формул в Excel, то вы попали по адресу. Перейдем к основной статье.

Загрузить книгу Excel

8 способов повторить шаблон формулы в Excel

В следующем наборе данных у меня есть 8 столбцов и 9 строк. Здесь у меня есть несколько пустых ячеек, где я буду повторять шаблоны формул различными способами, чтобы легко заполнить ячейки. Я объясню разные способы на примере этих пустых ячеек.

Способ 1: использование автозаполнения

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

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

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

Метод 2: использование функции Flash Fill для повторения узора

Предположим, мне нужно добавить Имя и Фамилию в столбец Полное имя. Итак, я написал имя и фамилию человека в первой строке столбца «Полное имя».

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

Шаг 02: Затем, как показано на следующем рисунке, имена будут автоматически заполнены с использованием заданного шаблона.

Способ 3. Повторение формулы путем перетаскивания и двойного щелчка

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

Шаг 02: таким образом будет сформирована следующая таблица.

Метод 4: копирование и вставка формулы для повторения шаблона

Шаг 01. Здесь я ввел формулу в E4 и хочу использовать эту формулу в других пустых ячейках с соответствующими значениями. Для этого мне нужно выбрать E4 и нажать CTRL+C, а затем выбрать пустые ячейки и нажать CTRL+V

Шаг 02. Таким образом, другие пустые ячейки будут заполнены шаблоном формулы, как показано ниже.

Похожие чтения:

Метод 5: использование Power Query для повторения шаблона

Шаг 01. Здесь я хочу заполнить столбец "Общий доход", введя формулу только один раз. Для этого вам нужно выбрать следующую вкладку «Данные» >> «Из таблицы/диапазона»

Шаг 02. Появится диалоговое окно «Создать таблицу». Затем вам нужно выбрать весь диапазон, нажать на опцию «Моя таблица имеет заголовки» и нажать «ОК».

Шаг 03. Откроется редактор Power Query. Вам нужно просто ввести формулу в E4, как показано ниже, и нажать клавишу ВВОД.

Шаг 04: таким образом шаблон формулы будет автоматически повторяться во всех пустых ячейках.

Способ 6: ввод формулы в несколько ячеек

Шаг 01. Сначала вам нужно выбрать все ячейки, в которых вы хотите использовать формулу, а затем начать вводить формулу в любой из ячеек, а затем нажать CTRL+ENTER.

Шаг 02: после этого оставшиеся ячейки будут заполнены формулой.

Метод 7: повторение шаблона формулы с использованием функции ДВССЫЛ

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

Шаг-01: Сначала вам нужно написать формулы в E4, E5, E6 соответственно. Здесь использовалась функция ДВССЫЛ.

= C4 *ДВССЫЛ(" D4 ",ИСТИНА)

= C5 *ДВССЫЛ("D5",ИСТИНА)

= C6 *ДВССЫЛ(" D6 ",ИСТИНА)

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

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

Метод 8: использование функции SEQUENCE для повторения шаблона

Шаг 01. В столбце идентификаторов я хочу заполнить ячейки идентификаторами с помощью функции ПОСЛЕДОВАТЕЛЬНОСТЬ.

=ПОСЛЕДОВАТЕЛЬНОСТЬ(строки,столбцы,начало,шаг)

Здесь строки = 8, столбцы = 1, начало = 121001, шаг = 2

=ПОСЛЕДОВАТЕЛЬНОСТЬ(8,1,121001,2)

Шаг 02: после входа в функцию появится следующая таблица.

Заключение

В этой статье я попытался описать самые простые способы повторения шаблонов формул в Excel. Надеюсь, эта статья вам очень поможет. Если у вас есть дополнительные идеи, связанные с этой темой, вы можете поделиться ими с нами. Здесь вы можете задать любой вопрос. Спасибо.

Чтобы копировать значения или генерировать ссылки с шаблоном, таким как каждая 3-я строка, каждая 7-я строка и т. д., вы можете использовать формулу, основанную на функциях OFFSET и ROW. В показанном примере формула в D5 выглядит следующим образом:

Который копирует значения из каждой 3-й строки в столбце B по мере копирования формулы.

В этом примере цель состоит в том, чтобы скопировать каждое n-е значение из столбца B, где n – это переменная, которую можно изменить по мере необходимости.

В Excel сложно создавать формулы, которые пропускают строки в соответствии с определенным шаблоном, потому что ссылки в формуле будут автоматически изменяться, когда формула копируется с шагом 1 в ячейках. Однако, немного поработав, можно создать ссылки на формулы, соответствующие определенным шаблонам. В этом примере мы используем функцию СМЕЩ, предназначенную для создания ссылок на другие ячейки или диапазоны ячеек на основе заданной начальной точки или источника.

Начиная с n

В показанном примере формула в D5, скопированная вниз, выглядит следующим образом:

которое копирует каждое третье значение из диапазона B5:B59, начиная с B7, которая является третьей ячейкой в диапазоне. Отправной точкой внутри функции OFFSET является аргумент reference, представленный как абсолютная ссылка:

Ссылка на B5 заблокирована, поэтому она не изменится при копировании формулы. Следующий аргумент — rows, который указывает желаемое смещение строки от начальной ссылки. Вместо типичного жестко заданного числа строки представлены в виде выражения, которое вычисляет требуемое смещение:

Здесь n указывается равным 3, чтобы копировать каждое третье значение. Здесь функция ROW используется для получения номера строки для ячейки D1. Мы начинаем с D1, потому что мы хотим начать с 1 для первого значения. Поскольку формула копируется вниз по столбцу, значение, возвращаемое ROW, увеличивается на 1, поскольку ссылка на D1 является относительной. Этот результат из ROW умножается на n, что создает шаблон «каждый n-й», в данном случае «каждый 3-й». По мере копирования формулы выражение оценивается следующим образом:

Эти числа могут показаться вам странными в контексте "каждого третьего значения", но помните, что это смещение, начиная с ячейки B5. Причина, по которой мы вычитаем 1, заключается в том, что функция OFFSET не включает ссылочную ячейку, когда применяется аргумент rows. Другими словами, смещение на одну строку от A1 возвращает A2:

Вычитание 1 учитывает это поведение.

Наконец, аргумент столбца предоставляется равным нулю (0), так как нам не нужно смещение столбца; мы хотим остаться в столбце B. Когда формула копируется вниз, она возвращает необходимые ссылки:

Число n можно изменить по мере необходимости. Например, если n изменить на 5, формула будет использовать каждое пятое значение.

Начиная с 1

Чтобы начать копирование с первой строки в заданном диапазоне, а затем следовать каждому n-му шаблону, вы можете изменить формулу следующим образом:

В этой версии мы вычитаем 1 непосредственно из результата функции ROW. Это «обнуляет» первый экземпляр строк, так что OFFSET возвращает ссылку на текущую ячейку. Формула в ячейке F5 использует следующий подход:

Читайте также: