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

Режимы фильтрации и временные фильтры можно использовать в ситуации, когда кто-то из ваших коллег воспользовался обычной фильтрацией, а вы не хотите его фильтр сбивать. Создайте свой фильтр и смотрите в нем таблицу вместе со скрытыми строками.

Подробнее о фильтрах в Google (и аналоге в Microsoft 365) — в главе «Совместная работа».

Вычисления в Excel: функции и формулы

Что такое формула и функция в Excel

Формула Excel (как и Google Таблиц) — это вычисление в ячейке. В отличие от значения, введенного в ячейку вручную (текста, числа или даты, вставленных как значения или введенных с клавиатуры), формула вычисляется и возвращает результат, который пересчитывается, если изменяются ячейки, которые в этой формуле используются (на которые она ссылается).

Автоматический пересчет формул можно отключить — например, если их в книге очень много и вам не хочется после любого действия ждать, пока большое количество формул обновится. Это можно сделать в параметрах Excel: Параметры → Формулы → Вычисления в книге → Вручную (Options → Formulas → Workbook Calculation → Manual).

А также на ленте во вкладке «Формулы».

Там же справа есть кнопки для пересчета всех листов (F9, Fn + F9) или текущего листа (Shift + F9, Fn + ⇑ + F9).

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

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

Кроме того, можно включить режим отображения формул: вкладка «Формулы» — «Показать формулы» (Show Formulas), тогда вы будете видеть их прямо в ячейках.

Числовые значения в таком режиме будут отображаться без форматирования.

Ввод формулы начинается со знака «равно» (=). Если вы хотите ввести текст, который начинается с этого знака, введите перед ним апостроф (') — он автоматически превращает значение в ячейке в текст, и оно не будет вычисляться как формула.

В формулах используются:

• знаки математических операций: плюс (+), минус (-), деление (/), умножение (*), возведение в степень (^);

• знак «амперсанд» (&) — он используется для «склеивания» (объединения, конкатенации) текстовых значений;

• круглые скобки для определения порядка операций;

• числа и текст, введенные вручную прямо в формуле (текст указывается в формулах в кавычках);

• ссылки на ячейки и диапазоны (они указываются без кавычек);

• пробел: это оператор пересечения (например, A1:E5 B2:D6 — это ссылка на пересечение двух диапазонов (на их общие ячейки));

• решетка (#): это ссылка на массив, который возвращается формулой из указанной ячейки (такой тип ссылок работает только в Excel 2021 / Microsoft 365); например, A1# — это ссылка на массив значений, который возвращает формула, находящаяся в ячейке A1 (мы подробно обсудим в главе «Динамические массивы», зачем нужны и как работают такие ссылки);

• знаки сравнения: «равно» (=) (знак «равно» в начале формулы присутствует всегда, а еще один может использоваться, если мы хотим сравнить два значения), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);

• функции рабочего листа Excel (например, СУММ / SUM, СРЗНАЧ / AVERAGE, ВПР / VLOOKUP и другие, всего функций в Excel более 500 — количество разнится в зависимости от версии); ниже мы поговорим о том, как вставлять функции в формулы.

Ссылки на ячейки в формулах

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

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

• Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов — это латинские буквы).

• Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором — выделяет ячейки. Переключаются эти режимы клавишей F2 (^ + U).

Когда вы вводите формулу, ссылки на ячейки или диапазоны выделяются цветами в самой формуле и теми же цветами на листе.

То же самое происходит не только при первом вводе формулы, но и если войти в режим редактирования (нажать F2 или щелкнуть двойным щелчком по ячейке с формулой или щелкнуть на строку формул).

АБСОЛЮТНЫЕ И ОТНОСИТЕЛЬНЫЕ ССЫЛКИ

Обычная ссылка на ячейку, которая формируется по умолчанию и выглядит как A1, — это относительная ссылка.

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

Если мы ссылаемся из ячейки C2 на A2 — это ссылка на ячейку на два столбца левее от формулы, а не на ячейку с адресом A2.

Потому что стоит скопировать и вставить или протянуть (мышкой за правый нижний угол ячейки) такую формулу вниз в следующую строку — и ссылка на A2 превратится в A3. А если скопировать формулу вправо — то в B2. Это по-прежнему будет ссылка на ячейку на два столбца левее от формулы, потому она и называется относительной.

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

Достаточно сослаться на соседнюю ячейку B2 из столбца C в той же второй строке и затем протянуть вниз — в каждой строке ссылка будет меняться, соответственно, в формуле будет использоваться значение из той же строки, в которой находится формула (а не