cover
Как упростить работу с цифрами: 5 инструментов Excel
Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных.
author
Евгений Довженко
специалист по автоматизации бизнес-задач

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


Excel — незаменимый помощник для достижения этих целей. Мы импортируем информацию, "подтягиваем" ее, систематизируем. На ее основе строим диаграммы, сводные таблицы, планируем, прогнозируем.

Однако в Excel до недавнего времени было 2 важных ограничения:

, иконка 1

Мы не могли разместить на рабочем листе Excel более миллиона строк (а наши данные о продажах за 2 года занимают, например, 10 млн строк).

, иконка 2

Мы знали, как создать и настроить интерактивные и обновляемые отчеты, но это отнимало много времени.

Единственный инструмент в Excel — сводные таблицы — позволял быстро обрабатывать наши данные.

С другой стороны, есть категория пользователей, которые работают со сложными BI-системами. Это системы бизнес-аналитики (business intelligence), которые дают возможность быстро визуализировать, "крутить" данные и извлекать из них ценную информацию (data mining). Однако внедрение и поддержка таких систем требует значительного участия IT-специалистов и больших финансовых вложений.

До Excel 2010 было четкое разделение на анализ малого и большого объема данных: Excel с одной стороны и сложные BI-системы — с другой.

Начиная с версии 2010, в Excel добавили инструменты, в названиях которых присутствует слово power: Power Query, Power Pivot и Power View. Они позволили сгладить грань между пользователями Excel и комплексных BI-систем.

Power Query

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

Для этого и необходим Power Query. До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft.

В версии 2016 это уже встроенный в программу инструментарий, находящийся на вкладке "Данные" (Data) в разделе "Скачать и преобразовать" (Get and Transform).

РЕКОМЕНДУЕМ ПРОЧЕСТЬ
image
ИНСТРУКЦИЯ
по работе с Excel

Перечень источников информации, к которым можно подключаться — огромный: от баз данных (их в последней версии 10) до Facebook и Google таблиц (рис. 1).

Рис 1. Выбор источника данных в Power Query

Вот некоторые возможности Power Query по подготовке и преобразованию данных:

, иконка 1

отбор строк и столбцов, создание пользовательских (вычисляемых) столбцов

, иконка 2

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

, иконка 3

транспонирование таблицы, разворачивание по столбцам (Pivot) и наоборот — сворачивание данных, организованных по столбцам, в построчный вид (Unpivot)

, иконка 4

объединение нескольких таблиц: как вниз — одну под другую, так и связывание по общей колонке (единому ключу)

Рис 2. Окно редактора Power Query

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

Пример

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

Таблица на сайте непригодна для прямого использования (рисунок 2-1):    

, иконка 1

все валюты не нужны

, иконка 2

в колонке "Курс" в качестве разделителя целой и дробной частей используется точка (в наших региональных настройках — запятая)

, иконка 3

в колонке "Курс" отображается показатель за разное количество единиц валюты: за 100, за 1000 и т. д. (указано в отдельной колонке "Количество единиц")

Рис. 2-1. Так выглядит таблица с курсами валют на сайте Нацбанка.

С помощью Power Query мы подключаемся к таблице текущих курсов валют на сайте НБУ и в этом редакторе готовим запрос на извлечение данных:

, иконка 1

В колонке "Курс" меняем точку на запятую (инструмент "Замена значений").

, иконка 2

Создаем вычисляемый столбец, в котором курсы валют в колонке "Курс" делятся на количество единиц валюты из колонки "Количество единиц".

, иконка 3

Удаляем лишние столбцы и оставляем только строки валют, с которыми работаем.

, иконка 4

Выгружаем полученную таблицу на рабочий лист Excel.

Результат показан на рисунке 2-2.

Рис. 2-2. Так выглядит результирующая таблица в нашем Excel файле.

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

Power Pivot

У вас данные находятся в разрозненных источниках? Некоторые таблицы содержат больше 1 млн строк? Вам нужно все это объединить в одну модель данных и анализировать с помощью, например, сводной таблицы Excel? Здесь понадобится Power Pivot — надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

В Power Pivot вы можете добавлять данные из разных источников, связывать таблицы между собой (рисунок 3). Таблицы при этом не обязательно должны находиться на рабочих листах Excel. Вместо этого они по-прежнему будут храниться в файле Excel, но просматривать их можно в окне Power Pivot (рис. 4). Поэтому нет ограничения на количество строк — в вашем файле Excel могут находиться таблицы и в сотни миллионов строк.

Рис. 3. Окно Power Pivot в представлении диаграммы

Рис. 4. Окно Power Pivot в представлении данных

Вот некоторые возможности Power Pivot, помимо описанных выше:

, иконка 1

добавлять вычисляемые столбцы и поля (меры), в том числе основанные на расчетах из нескольких таблиц

, иконка 2

создавать и мониторить в сводной таблице ключевые показатели эффективности (KPI)

, иконка 3

создавать иерархические структуры (например, по географическому признаку — регион, область, город, район)

И обрабатывать все это с помощью сводной таблицы Excel, построенной на модели данных.

Пример. У предприятия в базе данных (или отдельных файлах Excel) в 5 таблицах хранится информация о продажах, клиентах, товаре и его классификации, менеджерах по продажам и закупочных ценах продукции. Необходимо провести анализ по объемам продаж и маржинальности по менеджерам.

С помощью Power Pivot:

, иконка 1

добавляем все 5 таблиц в модель данных

, иконка 2

связываем таблицы по общим ключам (столбцам)

, иконка 3

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

, иконка 4

создаем вычисляемое поле (меру) "Маржа"

, иконка 5

с помощью инструмента "Ключевые показатели эффективности" устанавливаем цель по маржинальности и настраиваем визуализацию — как выполнение цели будет визуализироваться в сводной таблице

Теперь можно "крутить" эти данные в сводной таблице или в отчете Power View (следующий инструмент) и анализировать маржинальность по товарам, менеджерам, регионам, клиентам.

Power View

Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

В отличие от сводной таблицы, в отчет Power View можно добавлять диаграммы и другие визуальные объекты. Здесь нет такого количества настроек, как в диаграммах Excel. Но в том то и сила инструмента — мы не тратим время на настройку, а быстро создаем отчет, визуализирующий данные в определенном разрезе.

Вот некоторые возможности Power View:

, иконка 1

- быстро добавлять в отчет таблицы, диаграммы (без необходимости настройки)

, иконка 2

организовывать срезы и фильтры

, иконка 3

уходить на разные уровни детализации данных

, иконка 4

добавлять карты и располагать на них данные

, иконка 5

создавать анимированные диаграммы

Пример отчета Power View — на рисунке 5.

Рис. 5. Пример отчета Power View

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

Последние материалы
Ближайшие курсы
image ADVANCED EXCEL
продвинутые возможности для анализа данных
image ФИНАНСОВЫЙ МЕНЕДЖМЕНТ
практики управления финансами в компании
image МЕНЕДЖМЕНТ ПО АДИЗЕСУ
как устоять, когда другие падают
mail
Подпишитесь и получайте лучшие материалы от LABA
photo

Евгений Довженко

Курс
ADVANCED EXCEL
c 25 октября - по 29 ноября
  • сводные таблицы: от простого к сложному
  • решение задач оптимизации с надстройкой "поиск решения"
  • встроенная проверка данных и условное форматирование
  • возможности MS Eхcel в визуализации данных
записаться