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 МАГИЯ POWERPOINT
учимся делать эффектные презентации
image ПРОДУКТИВНОСТЬ 2.0
возьми свою жизнь под контроль
image НЕТВОРКИНГ
строим сеть контактов для работы и жизни
mail
Подпишитесь и получайте лучшие материалы от LABA
photo

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

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