Вставка и удаление строк с сохранением формул в 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! — слово Сумма (тем самым мы даем заголовок столбцу, приводя таблицу в визуальный порядок).
