Валентин Арьков - Анализ и визуализация данных в электронных таблицах

Ставим запятую и переходим к вводу второго аргумента.

Щёлкаем по заголовку столбца Season.

Получаем второй аргумент функции в строке формул (рис. 6.5).

Рис. 6.5. Второй аргумент функции

Нажимаем клавишу Enter.

Появляется новый столбец. Смотрим на значения в ячейках последнего столбца (рис. 6.6).

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

Рис. 6.6. Объединение столбцов

Задание. Введите формулу и объедините указанные столбцы.

Название столбца выбирается по умолчанию:

Calculated Column 1.

Это выражение мало что нам сообщает.

Переименуем новый столбец.

Дважды щёлкнем по заголовку столбца.

Введём новое название:

EditionID.

Это будет идентификатор года проведения Олимпийских игр (рис. 6.7).

Рис. 6.7. Переименованный столбец

Теперь нам нужно создать ключевое поле в таблице Medals.

Выбираем таблицу Medals.

Создаём новый столбец.

Вводим формулу извлечения номера года из даты:

= YEAR ([Edition]).

Получаем в новой колонке номер года (рис. 6.8).

Рис. 6.8. Номер года

Переименуем новый столбец:

YEAR.

Теперь будет понятно, что в этом столбце указан год (рис. 6.9).

Рис. 6.9. Переименованный столбец

Теперь создадим ещё один новый столбец:

Add Column.

У нас есть год и время года. Объединим их в один столбец.

Вводим формулу:

=CONCATENATE ([Year]; [Season]).

Получаем новый, объединённый столбец (рис. 6.10).

Рис. 6.10. Объединённый столбец

Название столбца по умолчанию снова назначено не слишком информативное.

Будет лучше, если название ключевого поля будет совпадать в обеих таблицах.

Переименуем столбец:

EditionID.

Результаты представлены на рис. 6.11.

Рис. 6.11. Ключевое поле

Мы будем создавать связь между таблицами типа «один ко многим». Для каждой Олимпиады у нас есть уникальная привязка ко времени: «Год + Время года».

Проверим, насколько корректно у нас получилось создать ключевое поле.

Отсортируем таблицу Hostsпо полю EditionID.

Проверим уникальность поля EditionID.

Рис. 6.12. Сортировка по ключевому полю

Задание. Проведите сортировку таблицы Hostsпо ключевому полю.

Данные отсортированы.

В таблице Hostsне так много строк. Их всего 47.

Так что просмотрим их вручную.

Пролистаем таблицу. На глаз не видно повторяющихся дат.

Рис. 6.13. Отсортированные данные

Задание. Проверьте уникальность данных в столбце ключевого поля.

Другое дело — сведения о медалях.

Для каждой записи о выигранных медалях может быть только одно значение ключевого поля «Год + Время года». А вот медалей каждый год может много. Даже очень много.

Поэтому у нас будет связь «один ко многим». Можно даже сказать: «один к очень многим».

Отсортируем таблицу Medalsпо полю EditionID.

Пролистаем таблицу и проверим уникальность поля EditionID.

Строк здесь достаточно много, больше 32 тысяч. Нам для демонстрации повторения значений достаточно пролистать несколько страниц (экранов).

Рис. 6.14. Отсортированные данные

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

У нас всё готово. Мы организовали ключевое поле в обеих таблицах.

Пора создавать связь между таблицами Medalsи Hostsпо полю EditionID.

Переключаемся в режим Diagram View.

Перетаскиваем поле EditionIDиз таблицы Hostsна поле EditionIDв таблице Medals.

Получаем связь между таблицами.

Щёлкаем по стрелке и убеждаемся, что связь установлена по выбранному полю (рис. 6.15).

Обратим внимание на направление стрелки. Она идёт от таблицы Hostsк таблице Medals.

Рис. 6.15. Новая связь между таблицами

Задание. Создайте описанную связь между таблицами и убедитесь в правильности настроек.

Проверим, как работают новые связи в модели данных.

Создадим новый лист и назовём его Cities.

Создадим на этом листе сводную таблицу «Страна — Город — Медали».

В качестве исходных данных указываем модель данных текущей рабочей книги:

Choose the data you want to analyze — Use this workbook’s Data Model.

Расположим сводную таблицу на текущем листе (рис. 6.16):

Choose where you want the PivotTable report to be placed — Existing Worksheet.

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