Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

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

=HYPERLINK("mailto: "&E2&"?subject=Тема письма";"Письмо")

Вместо такой формулы сделайте ее по открытому диапазону E2:E, и она будет работать бесконечно для любого количества строк. Чтобы в пустых строках ничего не отображалось, добавьте проверку на пустоту ячейки с имейлом (с помощью ЕСЛИ / IF).

Чтобы обрабатывать весь массив, нужно всю формулу поместить внутрь функции ArrayFormula (также можно нажать Ctrl + Shift + Enter, как для ввода формулы массива в Excel, и эта функция появится автоматически):

=ArrayFormula(IF(F2:F="";""; HYPERLINK("mailto: "&E2:E&"?subject=Подключение тарифа";"Письмо")))

Формула в одной ячейке, а дальше во всем столбце ссылки появляются автоматом — только если есть имейл.

ЧТО ПОМОЖЕТ НАПИСАТЬ/РАЗОБРАТЬ/ПОЧИНИТЬ СЛОЖНУЮ ФОРМУЛУ

Принцип луковицы

Так этот подход назвал эксперт по Google Таблицам и автор книги Spice Up Your Sheet Life Бен Коллинз. Идея в следующем: вместо того чтобы с ходу прописывать сложную формулу, сначала прописываем часть, смотрим, как она работает и правильно ли вычисляет промежуточное значение, затем ссылаемся на эту формулу из другой формулы, в которой она будет выступать одним из аргументов, и, если все заработает, удаляем столбец с промежуточной формулой, а ее переносим в итоговую формулу (на то место, где мы на нее ссылались).

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

В приведенном выше примере с гиперссылкой можно сначала сформировать текстовую формулу, которая будет соединять mailto и адрес электронной почты из ячейки, и сослаться на ячейку с этой формулой из ГИПЕРССЫЛКИ. И если все заработает, уже потом скопировать первую формулу и вставить вместо ссылки на ячейку с ней.

Переход на следующую строку в формуле

В строке формул можно переходить на следующую строку с помощью Alt + Enter. Это позволяет визуально разделить отдельные фрагменты/функции, тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).

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

Также можно пробелами ставить отступы, если это поможет вам с восприятием формулы.

Клавиша F9

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

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

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

Можно вычислить значение отдельной ячейки.

Можно вычислять и значение отдельной функции.

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

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