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

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

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

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

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

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

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

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

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

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

Файл с примером: F9 и ВПР.xlsx

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

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

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

На втором шаге эта формула будет выглядеть так. Вычислено название («Номенклатура») искомого товара.

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

Окно контрольного значения

Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе, — добавьте эту ячейку в окно контрольного значения.

Лента инструментов: Формулы — Окно контрольного значения (Formulas — Watch Window).

Далее в диалоговом окне добавляем те ячейки, значения которых хотим отслеживать.

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

Анализ данных

Подбор параметра

Файл с примером: Подбор параметра.xlsx

Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».

Он помогает ответить на вопрос «Какой должна быть переменная X, чтобы на выходе получить N?». Допустим, у нас есть простейшая модель с выручкой и маржинальной прибылью. Есть параметры, введенные как значения (производство в штуках, цена и себестоимость), и есть результирующие показатели, которые вычисляются формулами.

Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из: допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.

Результат после нажатия ОК:

Увы, менять можно только один параметр.

Лист прогноза (Forecast Sheet)

Книга с примером: Лист прогноза.xlsx

Хотя функции для прогнозирования были в Excel давно, в версии 2016 появился инструмент, который сильно упрощает прогнозирование, — «Лист прогноза» (Forecast Sheet) на вкладке «Данные».

Выделите данные (нужны и даты, и количественный показатель, который мы будем прогнозировать) и нажмите «Лист прогноза».

Здесь достаточно указать дату завершения прогноза, но можно раскрыть «Параметры», где можно задать свою сезонность, — например, если вы точно знаете, что у вас данные ведут себя согласно некому шаблону в пределах недели или месяца. Иначе Excel будет определять сезонность автоматически.

В правом верхнем углу можно выбрать тип диаграммы — график или гистограмма.

Будет создан лист, на котором Excel построит прогноз (таблица и диаграмма по ней) с помощью функции ПРЕДСКАЗ.ETS / FORECAST.ETS. Она работает по методу экспоненциального сглаживания, то есть для прогнозирования используются предыдущие (фактические) значения переменной, но «старые» значения экспоненциально теряют свою значимость (вес) со временем.