Во второй части заданий манипуляции с данными Data Relays третьего тура #ExcelWorldChamp необходимо загрузить две таблицы в модель данных, построить между ними взаимосвязь, потом построить сводную таблицу из модели данных и рассчитать среднее время для каждой страны. В завершение отсортировать страны по среднему результату забега на 100 метров.
Задание, в сравнении с другими заданиями третьего тура, чрезвычайно простое, однако, есть один подвох, который заключается в том, что для решения понадобиться профессиональная версия Excel, т.к. домашней версии нет инструментов вкладки Power Pivot.
Смотрите также видеоверсию статьи «#ExcelWorldChamp 3-2 или получить Office 2016 ProPlus бесплатно».
Здесь нам понадобиться модель данных (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.
Кстати, для построения связей профессиональная версия Excel не нужно в домашней версии также доступна команда «Отношения», а вот рассчитать «меру» (measure) которая будет считать среднее время можно только в Power Pivot, а это прерогатива профессиональной версии Excel.
Построение таких «мер» делается с помощью формул DAX. DAX – это выражения для анализа данных (Data Analysis Expressions). Функции DAX очень похожи на стандартные функции Excel (в английском варианте, конечно), соответствие английских и русских названий функций Excel вы можете посмотреть у нас на сайте в Справочнике по функциям Excel.
Основная суть таких формул заключается в том, что результат среднего будет зависеть от того какие данные будут представлены в сводной таблице. Например, если выбрать спортсмена, который сделал несколько забегов, то будет среднее его забегов, а если выбрать страну за которую выступали несколько спортсменов, то будет среднее этих спортсменов конкретной страны. Более того в таблице, где мы рассчитывали среднее, нет страны, она есть в связанной таблице.
После ввода формулы, выбираем команду «Сводная таблица» (Pivot Table) и указываем место расположения будущей таблицы.
После этого идет стандартная настройка полей сводной таблицы, здесь правильно выбрать связанный таблицы (они будут отмечены значком базы данных)
В качестве строк выступают страны, а в качестве значений – средней результат. Остается только отсортировать полученный результат по возрастанию и готово.
