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

Сравнение данных, поиск данных (те же ВПР и прочие): если у вас ошибка Н/Д, может быть проблема в разных форматах данных. Например, в исходнике, который вы импортируете, артикулы текстового формата, а у вас в вашей таблице — числового. Проверить можно с помощью функций ЕТЕКСТ / ISTEXT, ЕЧИСЛО / ISNUMBER.

Кроме того, всегда есть риск банальных ошибок ввода данных — лишние пробелы, перепутанные кириллица/латиница, сокращения. Для предотвращения таких ошибок используйте проверку данных. А для отлавливания — функцию УНИК / UNIQUE (выводите список уникальных значений и смотрите, есть ли там разные варианты написания одного и того же значения).

Если вдруг ВПР или другая функция в упор не находит значение, а визуально они кажутся одинаковыми, не забывайте, что всегда можно сравнить две ячейки формулой (=A1=A2) или посмотреть, одинаковой ли они длины по количеству символов (с помощью функции ДЛСТР / LEN).

Убрать лишние пробелы (до и после текстовой строки и все, что свыше одного пробела между слов) помогает функция СЖПРОБЕЛЫ / TRIM.

Забыли протянуть формулу / изменить диапазон: по возможности используйте таблицы в Excel и ссылки на них в формулах.

В 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 (или нажатием на крестик справа сверху).

В Excel всплывающие подсказки, показывающие, чему равен фрагмент формулы, появились только в 2023 году (то есть их нет в версиях вплоть до Excel 2021, только в Microsoft 365).

После нажатия F9 вычисленный фрагмент становится значением, и нужно выйти через Esc, чтобы он так и не остался фиксированным значением, а подсказка просто отображает значение выделенного фрагмента, как в Google Таблицах.

Можно посмотреть, чему равны:

• одна из функций в формуле;

• сочетание функций, выражение внутри формулы;

• что хранится в той или иной ячейке;

• какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы.

Отключаются и включаются подсказки сочетанием Ctrl + Alt + P.

Но иногда превратить диапазон в значения — именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже «внутри формулы». И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы, то этот способ вам подойдет.

Так окажется выглядеть формула после нажатия F9 — таблица в строках 11–14 уже будет не нужна.

Пошаговое вычисление

Когда в формуле применяется несколько шагов (как правило, несколько разных функций), можно использовать инструмент «Вычислить формулу» (Evaluate Formula), чтобы проследить