Уроки новичкам

Cохранить формулу в Google таблице при добавлении / удалении строки

Cохранить формулу в Google таблице при добавлении / удалении строки

Вставка и удаление строк с сохранением формул в Google таблицах.

Довольно часто требуется выполнять простые математические отношения: сложение, вычитание, умножение и деление в ячейках таблиц (как в Excel, так и в Google). Выражения представляют собой некую базу данных из чисел. На примере ниже: в столбце Е (сумма) происходит процесс умножения Количества и Цены.

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

Когда вставляю строки, в формулах все сбивается и не считается ничего

Как вставить или удалить строки в Google таблицах, не нарушая формул?

Как на текущем листе, так и на другом (служебном) можно настроить функцию запросов в google sheets QUERY. Она позволит производить математические отношения, не сбиваясь и не выводя ошибку при добавлении или случайном удалении ячейки, строки или столбца. Т.е., можно добавлять новые строки в «тело» массива с данными, заполнять как нужно, а функция будет проводить заложенные в нее арифметические действия (в нашем случае перемножать ячейки C и D, выводя результат в столбце E).

=QUERY(D:E;"select D*E label D*E 'Сумма'")
  • Где D:E — это два столбца с данными, которые нужно перемножить. Можно выбрать любую по протяженности область ячеек, либо сразу целые столбцы.
  • select D*E — запрос с математической командой умножения.
  • label D*E ‘Сумма’ — задаем имя заголовка для столбца F (в который и помещается функция QUERY).

Обратите внимание: на примере в тело таблицы вставлена новая 10 строка. Если внести в ячейки D10 и E10 новые данные, то сохранившаяся формула функции QUERY их автоматически перемножит.
Даже если по неосторожности удалить ячейку F10, функция автоматически пересчитает и заново выдаст произведение данных ячеек D10 и E10.

Чтобы формула сама умножала или делила во вставленной строке

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

Еще один вариант автоматического распространения математического действия на ячейки новой вставленной строки: использовать функцию массива — ARRAYFORMULA.

=ARRAYFORMULA(D5:D*E5:E)

Где D5:D и E5:E — диапазоны с данными, по которым нужно вывести произведение в столбец F. Можно выбрать определенные области ячеек, а можно сразу столбцы.

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

Но и в этом варианте, при вставке новой строки — функция ARRAYFORMULA автоматически сохранит формулу в новой ячейке. Строку или саму ячейку можно смело удалять — все автоматически обновится.

=IFERROR(ARRAYFORMULA(D4:D*E4:E);"Сумма")

Где функция массива, перемножающая значения из столбцов D и E — выдаст ошибку, так как нельзя перемножать текст в ячейках D4 и E4.
Функция массива помещается в функцию исправления ошибки IFERROR, выводящую вместо выданной ошибки #VALUE! — слово Сумма (тем самым мы даем заголовок столбцу, приводя таблицу в визуальный порядок).

Статьи по теме