«Умные» таблицы для экономического анализа в Excel

В предыдущих статьях, посвященных инструментам Excel для экономистов и бухгалтеров (см. «ЭГ» № 20 от 16.03.2018 и № 31 от 27.04.2018), мы рассмотрели, как производить расчет и анализ данных с помощью как самых простых, так и более сложных формул. Но иногда, имея «выгрузку» какого-либо отчета, для того чтобы сделать основные выводы, достаточно лишь правильной обработки и интерпретации имеющихся данных.

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

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

Предположим, у нас есть отчет по плановой и фактической выручке за один месяц розничной сети, состоящей из 15 магазинов различных районов Минска. Для того чтобы обычные «плоские» данные сделать таблицей Excel, необходимо выделить весь наш отчет и нажать «Вставка-Таблица». После этого появится небольшое окно, в котором необходимо подтвердить ссылку на расположение преобразуемых значений.

В итоге мы получим следующее: слева – данные в обычном виде, справа – «официальную» таблицу Excel (см. табл. 1).

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

Новые возможности дарит и вкладка «Конструктор», которая появляется при нажатии на любую из ячеек нашей уже «поумневшей» таблицы.

Разберем основные полезные инструменты, способные существенно облегчить работу экономиста или бухгалтера.

Внешний вид

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

Но, пожалуй, самым важным при работе со стилем является возможность вносить изменения в его определенные элементы. Для этого на вкладке «Конструктор – Параметры стилей таблиц» с помощью расстановки галочек можно добавлять элементы в таблицу, а также активировать некоторые параметры форматирования.

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

Строка итогов

По умолчанию в итогах отображаются суммы числовых значений в столбцах (см. табл. 2).

Таблица 2

Однако можно выбрать другие варианты итогового значения: данная строка способна заменить нам самые простые функции, которые мы рассматривали ранее, а также позволяет выбирать из «Библиотеки» любую необходимую формулу.

В качестве примера рассчитаем среднее значение фактической и плановой выручки по магазинам.

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

Фильтрация данных

Предположим, мы хотим отследить все магазины Фрунзенского района г. Минска. Для этого в «Районах» открываем выпадающий список. Здесь предусмотрена возможность сортировки по возрастанию и убыванию, по цвету, имеются текстовые фильтры. Такой список есть в каждом названии столбца рассматриваемой таблицы.

В данном случае нас интересует выбор отображаемых данных – фильтрация. Для этого мы снимаем галочку «Выделить все» и ставим ее только напротив Фрунзенского района.

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

Продолжим работу с нашим примером отображения данных по месторасположению магазинов.

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

Сортировка

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

Обратите внимание: если файл с сортировкой был сохранен и закрыт, то, открыв его позже, вы не сможете отменить сортировку, вернув таблицам исходный вид.

Формулы

Если вам необходимо провести дополнительные расчеты в отчете, то и здесь наша «умная» таблица поможет упростить вашу работу.

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

Вставка может осуществляться двумя способами:

1) выделяем весь столбец с фактической выручкой и, нажав правую кнопку мыши, осуществляем вставку столбца справа;

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

Далее в первую же ячейку, где должен осуществляться расчет, вносим формулу отклонения фактической выручки от плановой. И как только мы ее ввели, функция автоматически «протягивается» на весь наш столбец расчета «Отклонения».

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

Визуализация

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

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