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

ход вычисления по порядку.

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

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

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

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

Лента инструментов: Формулы — Окно контрольного значения (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. Она работает по методу экспоненциального сглаживания, то есть для прогнозирования используются предыдущие (фактические) значения переменной, но «старые» значения экспоненциально теряют свою значимость (вес) со временем.

Обратите внимание, что «Лист прогноза» не справится с анализом сезонности, если она у вас сразу на нескольких уровнях, — например, высокая посещаемость ресторана по пятницам-субботам в рамках недели и высокий сезон в некоторые месяцы в рамках года.

Сводные таблицы (Pivot Tables)

Файл с примерами: Сводные таблицы.xlsx

Сводные таблицы (Pivot Tables) — один из самых простых в применении и в то же время один из самых мощных инструментов анализа данных в Excel (и в Google Таблицах).

А еще это очень гибкий инструмент: можно буквально в пару кликов переделать сводную таблицу и изменить ее структуру, получив ответы на свои вопросы. Сводные существуют в Excel уже несколько десятилетий (с версии 97), хотя с каждой версией программы, разумеется, получают новые возможности и опции.

Если вам нужно проанализировать «сырые» данные (причем это могут быть как данные в той же книге Excel, что и сводная, так и из внешних источников), найти в них взаимосвязи, проанализировать структуру, динамику, увидеть итоги по тем или иным категориям (городам, отделам, сотрудникам, компаниям, клиентам, etc.), то сводные таблицы могут быть одним из лучших инструментов.

Какие данные подходят для построения сводных таблиц?

Данные для сводной таблицы

Данные для сводных таблиц должны отвечать следующим условиям.

• Быть представленными в табличном виде (не обязательно «официальная» таблица Excel, но обязательно прямоугольный диапазон без пустых строк или столбцов).

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

• Каждый столбец — это один параметр, и в каждом столбце есть только один тип данных (текст, числа, даты и так далее).

• Каждая строка описывает одну совокупность всех параметров (одну операцию, транзакцию, сотрудника или клиента, одну рекламную кампанию или одно действие пользователя).

Какими могут быть источники данных?

• Просто диапазон Excel (например, A1:E550). Это допустимый вариант, но если вы предполагаете, что к нему будут добавляться новые строки, то лучше использовать таблицу Excel.

• Диапазон Excel со столбцами целиком (например, A: E). В таком случае в сводную попадут вообще все строки на листе, включая (пока) пустые. Их придется фильтровать. К тому же это может отрицательно повлиять на быстродействие.

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

• Внешний источник данных (например, база данных, информация с сайта, Google Таблица и т. д.). Данные из таких источников можно загрузить с помощью Power Query. Эта надстройка может быть установлена бесплатно в Excel 2010–2013 и является частью Excel 2016 и 2019 (и Excel для Mac в Microsoft 365). В последних версиях ее можно найти на ленте инструментов: Данные → Получить и преобразовать (Data → Get & Transform Data).

Каким бы ни был источник данных в сводной, но если эти данные изменились, то сводная сама не обновится (как это происходит с формулами). Ее нужно обновить (Refresh) — в контекстном меню по щелчку правой кнопки мыши.

Создание сводной таблицы

Сводная создается через вкладку «Вставка»