Как протянуть формулу на весь столбец в Google Таблицах и Excel

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

Как протянуть формулу на весь столбец в Excel

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

Способ 1. Автозаполнение с помощью маркера

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

Как это сделать:

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

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

Рассмотрим этот способ на примере таблицы с маркетинговыми кампаниями. В ней мы считаем показатель CTR — делим количество кликов на количество показов. Для этого в ячейке D2 вводим формулу =C2/B2, а затем протягиваем её маркером вниз. В результате CTR рассчитывается сразу для всех строк.

Способ 2. Горячие клавиши

Формулу можно протянуть не только с помощью маркера, но и комбинацией клавиш. Этот способ удобен, если нужно сразу заполнить несколько строк.

Как это сделать:

1. Введите формулу в первую ячейку.
2. Выделите диапазон строк, которые нужно заполнить, включая ячейку с формулой.
3. Нажмите Ctrl+D (Windows) или Command+D (Mac).

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

А теперь в той же таблице рассчитаем CPC: у нас есть столбец Бюджет (E) и столбец Клики (C). Нужно поделить бюджет на клики. Вводим формулу в F2, а затем с помощью горячих клавиш протягиваем формулу.

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

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

Рассмотрим её на той же самой формуле, где мы считаем CPC.

Как это сделать:

1. Вместо того чтобы писать формулу только в строке 2 (=E2/C2) и протягивать её вниз, можно сразу задать массивную формулу для всего столбца: «=E2:E6/C2:C6» 
2. Excel автоматически «разольет» результат по всем строкам, создавая целый столбец значений.

Если нужно, чтобы формула работала для всего столбца без ограничения по строкам, используйте конструкцию с «E2:E/С2:С», то есть без указания финального числа. При добавлении новых данных формула будет учитывать их автоматически.

Как протянуть формулу на весь столбец в Google Таблицах

В Google Таблицах формулы тоже можно протянуть на весь столбец несколькими способами. Разберем пошагово, как это сделать.

Способ 1. Маркер автозаполнения

В Google Таблицах маркер автозаполнения работает почти так же, как в Excel. Это маленький квадратик в правом нижнем углу активной ячейки.

Как это сделать:

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

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

Рассмотрим этот способ на примере таблицы с KPI сотрудников. В ней мы считаем процент выполнения плана продаж — делим факт продаж на план. Для этого в ячейке D2 вводим формулу =C2/B2, а затем протягиваем её маркером вниз.

Способ 2. Автоматическое заполнение по шаблону

Google Таблица может сама предложить распространить формулу на весь столбец. Это удобно, когда нужно быстро применить один и тот же расчет ко всем строкам.

Как это сделать:

1. Введите формулу в первую ячейку.
2. Таблица автоматически определит закономерность и покажет серую подсказку с результатами для всего столбца.
3. Нажмите Ctrl+Enter (Command+Enter), чтобы подтвердить автозаполнение.

Формула сразу применяется ко всем строкам. Если добавлять новые данные, Google Таблица продолжат копировать её автоматически.

А теперь в той же таблице рассчитаем KPI сотрудников, применяя чуть более сложную формулу, с условиями. 

Способ 3. Горячие клавиши

Вместо маркера можно использовать комбинацию клавиш. Это ускоряет работу, если нужно заполнить сразу несколько строк.

Как это сделать:

1. Введите формулу в первую ячейку.
2. Выделите диапазон строк, включая ячейку с формулой.
3. Нажмите Ctrl+D (Windows) или Command+D (Mac).

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

Способ 4. Функция ARRAYFORMULA

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

Как это сделать:

1. Выберите первую ячейку и вначале, после знака =, введите ARRAYFORMULA.
2. После этого введите основную формулу в скобках. Например: «=ARRAYFORMULA(ЕСЛИ(D2:D>=1, C2:C*0.1, 0))». Следите, чтобы все скобки были правильно расставлены.
3. Отдельно проверьте, чтобы в формуле были правильно заданы диапазоны данных. Чтобы формула работала для всего столбца, используйте конструкцию с «D2:D», «С2:С» и т.п., то есть без указания финального числа. При добавлении новых данных формула будет учитывать их автоматически. Если данных в ячейках нет, система автоматически выставит нули.
4. Формула сразу отобразит результаты во всех строках.

ARRAYFORMULA подходит не только для вычислений. С её помощью можно применять текстовые и логические функции. Например, формула «=ARRAYFORMULA(UPPER(A2:A))» переведёт все значения столбца A в верхний регистр.

Если в формуле в Excel или Google Sheets нужно зафиксировать значение

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

Чтобы зафиксировать конкретную ячейку, используют знак $. Он показывает, что эта ссылка всегда должна указывать на одну и ту же ячейку, даже если формула копируется вниз или в сторону. Поставить знак $ можно вручную или с помощью кнопки F4. Знак $ можно поставить перед названием столбца и строки:

• $A$1 — фиксируются и строка, и столбец.
• $A1 — фиксируется только столбец.
• A$1 — фиксируется только строка.