После нажатия 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. Она работает по методу экспоненциального сглаживания, то есть для прогнозирования используются предыдущие (фактические) значения переменной, но «старые» значения экспоненциально теряют свою значимость (вес) со временем.
Обратите внимание, что «Лист прогноза» не справится с анализом сезонности, если она у вас сразу на нескольких уровнях, — например, высокая посещаемость ресторана по пятницам-субботам в рамках недели и высокий сезон в некоторые месяцы в рамках года.
Сводные таблицы (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) — в контекстном меню по щелчку правой кнопки мыши.
Создание сводной таблицы
Сводная создается через вкладку «Вставка» (Insert) на ленте. Там есть две опции: «Сводная таблица» и (начиная с версии 2013) «Рекомендуемые сводные таблицы» (Recommended PivotTables).
Кнопка «Сводная таблица» вызовет диалоговое окно. Убедитесь, что в качестве источника данных для сводной будет использован правильный диапазон или таблица, и укажите, где должна быть создана сводная — на отдельном листе или на одном из уже существующих в книге.
«Рекомендуемые сводные» (Recommended PivotTables) появились в версии Excel 2013. При выборе этого варианта Excel в большинстве случаев предложит вам несколько вариантов сводных таблиц.
В большинстве случаев это будет не то, что вам нужно на 100%, но от предложенных вариантов можно отталкиваться и дорабатывать их, так что это все равно может сэкономить время.
Настройка сводной таблицы. Список полей
После создания сводной появляется пока еще пустой отчет, а справа будет список полей сводной таблицы. Это столбцы (поля) из исходных данных.
Хотя в будущем этот список может пополниться, так как в сводных можно создавать новые поля.
На ленте инструментов появятся контекстные (то есть возникающие только при активации сводной) вкладки.
• «Анализ сводной таблицы» (PivotTable Analyze): там можно изменить источник данных, добавить вычисляемое поле, вставить срез и временную шкалу и многое другое — все операции со сводной здесь.
• «Конструктор» (Design): на этой вкладке настраивается внешний вид сводной — стиль и макет.
Именно в области «Поля сводной таблицы» (PivotTable Fields) происходит настройка ее структуры. Здесь есть четыре области.
• Фильтры (Filters). Если поле перенесено сюда, его значения не будут показываться в сводной, по ним можно будет фильтровать, то есть выбирать только определенные значения.
• Строки (Rows). При перенесении сюда полей будут выводиться уникальные значения из них — соответственно в строках и столбцах.
• Столбцы (Columns). Аналогично строкам.