Гибкое формирование отчетов в Exсel

В MS Excel существует множество инструментов для анализа, необходимых экономистам и бухгалтерам для ежедневной работы, но, пожалуй, одним из самых полезных и интересных являются cводные таблицы (СТ).

С помощью СТ всего в несколько кликов вы сможете проанализировать большую базу данных, причем сделать это множеством способов, и представить итоговую информацию в любом виде. Данный ин­струмент очень обширный, но в рамках настоящей статьи мы сосредоточимся на основных возможностях и нюансах его использования, благодаря чему вы точно сможете продолжить знакомство с СТ самостоятельно.

Предположим, у нас есть выгрузка отчета по продажам аксессуаров, велосипедов, компонентов и одежды за 2017 г. (см. таблицу).

В этой базе данных содержится много информации, но она вся не систематизирована. Конечно, можно начать работать с формулами (основные функции для анализа мы рассмотрели ранее – см. Информбанк «ЭГ» № 20 от 16.03.2018 г., № 31 от 27.04.2018 г., а также № 34 от 11.05.2018 г. и № 42 от 08.06.2018 г.). Однако проще использовать СТ, к тому же их применение обезопасит вас от ошибок в анализе из-за неправильных формул.

Перед тем как строить СТ на основе базы данных, необходимо учесть два обязательных правила.

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

Для того чтобы начать пользоваться всеми возможностями СТ, необходимо выделить весь наш отчет, затем нажать «Вставка-Сводная таблица». После чего появится окно, где нужно указать расположение таблиц (на новом или существующем листе).

В нашем примере будем строить отчеты на новом листе. На нем вы увидите поле для создания СТ, а также панель задач «Список полей сводной таблицы», в котором происходит выбор полей (столбцов) из нашей базы в качестве Столбцов, Строк, Значений, а также Фильтров отчета.

Предположим, в нашем случае мы хотим проанализировать выручку по товарным группам. Для формирования такого отчета нам необходимо отправить поле «Товарная группа» в Строки, а «Выручку» – в Значения (см. рис. 1).

Рисунок 1

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

В итоге получим информацию следующего вида (рис. 2).

Рисунок 2

Как видно, автоматически в отчет добавилась строка Итог. А вот Выручка по умолчанию представлена в плохо считываемом числовом формате. Для того чтобы изменить формат отображения значений, необходимо в «Списке полей сводной таблицы», в поле значений (рис. 2), нажать на Сумма по полю Выручка -Параметры полей значений.

В появившемся всплывающем окне есть возможность:

– изменить название поля выбранного поля значений;

– выбрать операцию для вычисления (по умолчанию в нашем случае стоит сумма, но, к примеру, если бы мы хотели видеть количество отгрузок, то необходимо изменить операцию на «Количество»);

– задать числовой формат (для нашего примера необходимо задать формат «Денежный с 2» цифрами после запятой;

– выбрать дополнительные вычисления. Пример наиболее часто используемого вида – доля от суммы по строке/столбцу.

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

Предположим, в нашем примере мы хотим рассчитать комиссионные, которые составляют 15% от Выручки. Для этого мы нажимаем вкладку Анализ – Поля, элементы и наборы – Вычисляемый объект либо Параметры – Формулы – Вычисляемое поле (зависит от версии Excel).

Перед нами появляется всплывающее окно, где заполняется Имя поля, а также записывается Формула. Если в формуле используется поле из наших данных (в нашем примере для расчета комиссионных используется Выручка), то выбор осуществляется двойным щелчком из списка, находящегося под строкой ввода функции. Заполненное окно для нашего примера представлено на рис. 3.

Рисунок 3

По умолчанию наше созданное вычисляемое поле добавится в поле Значения СТ и будет отображаться в отчете рядом с Выручкой.

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

Поскольку далее мы работаем только с Выручкой, сначала нам необходимо удалить Комиссионные из нашего отчета. Для этого в окне Список полей Сводной таблицы – Значения необходимо выбрать Сумму по полю Комиссионные и перетащить его обратно в общий список полей либо нажатием правой кнопкой мыши по выпадающей стрелочке выбрать Удалить поле.

После этого мы перетягиваем поле Страна в названия столбцов, Категория – в названия строк (после «Товарной группы»), а Модель и Цвет отправляем в «Фильтры» (см. рис. 4).

Рисунок 4

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

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

Для этого на вкладке Анализ либо Параметры (зависит от версии Excel) необходимо нажать кнопку Обновить (см. рис. 5).

Рисунок 5

Также разберем самые важные и необходимые настройки СТ. Чтобы открыть окно с параметрами, необходимо щелкнуть в любом месте нашей таблицы правой кнопкой мыши – Параметры сводной таблицы. Здесь мы видим несколько вкладок, но самыми важными настройками являются:

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

– итоги и фильтры. Здесь вы можете отключить подсчет общих итогов;

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

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