#ExcelWorldChamp 3-2 или получить Office 2016 ProPlus бесплатно

Во второй части заданий манипуляции с данными Data Relays третьего тура #ExcelWorldChamp необходимо загрузить две таблицы в модель данных, построить между ними взаимосвязь, потом построить сводную таблицу из модели данных и рассчитать среднее время для каждой страны. В завершение отсортировать страны по среднему результату забега на 100 метров.

Задание, в сравнении с другими заданиями третьего тура, чрезвычайно простое, однако, есть один подвох, который заключается в том, что для решения понадобиться профессиональная версия Excel, т.к. домашней версии нет инструментов вкладки Power Pivot.

Команды вкладки Power Pivot

Команды вкладки Power Pivot

Здесь нам понадобиться модель данных (Data Model). С помощью данного инструмента можно строить взаимоотношения между таблицами, превращая Excel в некий аналог систем управления базами данных.

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

Скачать Office 2016 Professional Plus Trial (32 бит)

Скачать Office 2016 Professional Plus Trial (64 бит)

Ключ для активации: NKGG6-WBPCC-HXWMY-6DQGJ-CPQVG

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

Для решения задачи первым дело следует загрузить таблицы в модель данных. Это делается стандартной командой "Из таблицы" (From Table) группы "Скачать и преобразовать" (Get & Transform) вкладки "Данные" (Data), единственное, что, когда делается выгрузка данных необходимо поставить опцию "Добавить данные в модель данных" (Add this data to the Data Model).

Загрузка данным в модель данных

Загрузка данным в модель данных

Далее строим взаимоотношения между таблицами, это можно сделать двумя способами: с помощью команды "Relationships" или непосредственно через управление моделью данных "Manage Data Model". В первом случае получаем довольно неудобное диалоговое окно с выбором таблицы и ключевых полей.

Создание связи между таблицами в диалоговом окне

Создание связи между таблицами в диалоговом окне

А вот во втором, красивую схему, где простым перетягиванием можно установить отношения, аналог MS Access.

Создание связи между таблицами в Power Pivot

Создание связи между таблицами в Power Pivot

Кстати, для построения связей профессиональная версия Excel не нужно в домашней версии также доступна команда "Отношения", а вот рассчитать "меру" (measure) которая будет считать среднее время можно только в Power Pivot, а это прерогатива профессиональной версии Excel.

Создание переменной (measure)

Создание переменной (measure)

Построение таких "мер" делается с помощью формул DAX. DAX – это выражения для анализа данных (Data Analysis Expressions). Функции DAX очень похожи на стандартные функции Excel (в английском варианте, конечно), соответствие английских и русских названий функций Excel вы можете посмотреть у нас на сайте в Справочнике по функциям Excel.

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

После ввода формулы, выбираем команду "Сводная таблица" (Pivot Table) и указываем место расположения будущей таблицы.

Создание сводной таблицы

Создание сводной таблицы

 

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

Сводная таблица с рассчитанным средним временем забега

Сводная таблица с рассчитанным средним временем забега

В качестве строк выступают страны, а в качестве значений – средней результат. Остается только отсортировать полученный результат по возрастанию и готово.