Бизнес-аналитика. Извлечение, преобразование и загрузка данных

Учебное пособие

Валентин Юльевич Арьков

© Валентин Юльевич Арьков, 2020

ISBN 978-5-4498-4086-8

Создано в интеллектуальной издательской системе Ridero

Введение

Системы бизнес-аналитики работают с различными источниками данных с помощью функций ETL (Extract-Transform-Load). Название ETL можно перевести как «извлечение, преобразование и загрузка данных». Имеется в виду загрузка в хранилище данных для дальнейшей обработки в системе бизнес-аналитики [1—5]. В простейшем случае это загрузка данных в виде одной, объединённой таблицы Excel.

Задание. Прочитайте в Википедии статью «ETL» и выясните, какие действия над данными производят на каждом из трёх этапов.

Отчёт оформляем в виде рабочей книги Excel — как и в предыдущих работах. Процедура оформления отчёта подробно описана в работе [6].

Не забывайте записывать в отчёт ответы на задания типа «выясните…». Когда вы что-то узнали, зафиксируйте это в своём произведении. Упражнения на изучение — это часть данной лабораторной работы.

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

Задание. Создайте файл отчёта. Оформите титульный лист и оглавление. Сохраните файл под коротким информативным названием.

В качестве исходных данных мы будем использовать сведения об акциях. Это так называемые ЦЕННЫЕ БУМАГИ. Акцию конкретной компании иногда называют просто «бумага». Акции приобретают с разными целями. Торговля акциями, в основном, происходит на бирже.

Задание. Прочитайте в Википедии статью «Акция (финансы)» и выясните, какие права она даёт владельцу, а также какие два вида акций существуют.

В оформлении обложки использованы изображения, разработанные компанией OnlineWebFonts.

1. Инструменты ETL в Microsoft Excel

В пакете Microsoft Excel версий 2010 и 2013 функции ETL реализованы с помощью надстройки Power Query.

Power Query позволяет решать следующие задачи:

— извлечение — импорт данных из различных источников;

— преобразование — приведение данных к единому формату;

— загрузка — консолидация / объединение данных из нескольких источников в виде одной таблицы Excel.

Название Power Query можно расшифровать как «Средство из комплекта PowerBI для выполнения запросов к источникам данных». В свою очередь, PowerBI — это программный комплекс (или платформа) бизнес-аналитики (BI — Business Intelligence), включающий целый ряд отдельных компонентов.

Задание. Прочитайте в Википедии статью «Power BI» и выясните, какие компоненты входят в настольную (локальную) версию Power BI Desktop и какова стоимость этой версии.

Платформа Power BI доступна в разных вариантах. Есть версии программы, которые можно скачать и установить бесплатно. Естественно, с рядом ограничений. Но это уже позволяет познакомиться с ключевыми возможностями продукта.

Задание. Перейдите на следующий сайт:

https://powerbi.microsoft.com.

Выясните, какие версии PowerBI доступны для скачивания.

Для установки Power Query потребуется сказать эту надстройку. Заходим на страницу Центра загрузки Microsoft:

https://download.microsoft.com

Вызываем поиск:

Power Query Excel.

Получаем ссылки для скачивания (рис. 1.1).

Рис. 1.1. Power Query в Центре загрузки

Задание. Найдите страницу Power Query в Центре загрузки и выясните год выпуска этой надстройки и поддерживаемые версии Excel.

Кроме ссылок для скачивания файлов, здесь имеются пояснения по поводу инструментов загрузки данных. Переходим по следующей ссылке:

Get & Transform Data.

Выясняем, как всё это связано с надстройкой Power Query и пакетом PowerBI (рис. 1.2).

Рис. 1.2. Инструменты загрузки данных

Задание. Выясните, как связан инструментарий Get & Transform с Power BI и Power Query.

В пакете Excel версий 2016, 2019 и 365 функции ETL доступны в группе инструментов Get & Transform (рис. 1.3).

Рис. 1.3. Группа инструментов ETL

Задание. Найдите группу Get & Transform в разделе Data и изучите названия основных кнопок на ленте, а также всплывающую подсказку для кнопки Get Data.

В группе Get & Transform скрывается кнопка запуска редактора запросов Power Query Editor:

Data — Get & Transform — Get Data — Launch Power Query Editor.

Всплывающая подсказка поясняет, что Power Query позволяет нам создавать новые запросы и редактировать существующие (рис. 1.4). Значит, Power Query по-прежнему жив и здоров.

Рис. 1.4. Запуск редактора запросов

Задание. Прочитайте статью Get & Transform and Power Pivot in Excel (Получение и преобразование и Power Pivot в Excel) в Центре справки и поддержки Microsoft Office:

https://support.office.com

Обратите внимание на следующие разделы:

— What is Get & Transform?

— What is Power Pivot?

— What is Power BI?

Кратко запишите в отчёт, что удалось выяснить про три варианта инструмента ETL.

2. Загрузка таблицы из Википедии

Один из стандартных источников данных —это интернет. Из интернет-страницы можно извлечь и загрузить выбранную таблицу.

Перейдём на сайт Википедии в браузере и найдём статью «Power BI» (рис. 2.1). В тексте статьи есть таблица с описанием лицензий. Попробуем загрузить эту таблицу в Excel. Скопируем адрес выбранной страницы в буфер обмена.

Рис. 2.1. Страница сайта в интернете

Задание. Найдите таблицу по лицензиям в статье «Power BI».

Возвращаемся в Excel.

Выбираем в верхнем меню загрузку данных из интернета (рис. 2.2):

Data — Get & Transform Data — From Web.

Рис.2.2. Получение данных из интернета

В диалоговом окне From Web вставляем адрес интернет-страницы из буфера обмена (рис. 2.3). По умолчанию выбран вариант загрузки Basic. Нажимаем кнопку OK.

Рис. 2.3. Ввод адреса страницы

Задание. Вызовите загрузку данных со страницы статьи «Power BI» в Википедии.

В диалоговом окне Navigator можно увидеть список из нескольких таблиц, которые были найдены на указанной странице сайта. Выбираем таблицу по лицензированию. Рассматриваем нашу таблицу в окне предварительного просмотра

Table View.

Убеждаемся, что выбрали нужную таблицу (рис. 2.4). Нажимаем кнопку

Load.

Рис. 2.4. Выбор таблицы для загрузки

Задание. Выберите для загрузки таблицу по лицензиям.

Таблица загружается в Excel (рис. 2.5). Сравниваем с источником (рис. 2.1). Заголовки столбцов и содержимое ячеек распознаны и загружены без ошибок. Таблица оформлена как таблица Excel.

Переходим в группу Table Tools в верхнем меню ленты. Выбираем раздел Design. Проверяем название таблицы:

Table Name — Лицензирование…

Значит мы загрузили не только саму таблицу, но и её заголовок (название).

Рис. 2.5. Загруженная таблица

Задание. Загрузите в Excel таблицу «Лицензирование» из статьи «Power BI» на сайте Википедии и ознакомьтесь с результатами.

Рассмотрим поподробнее название таблицы и сравним его с заголовком раздела в источнике данных (рис. 2.1).

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

Кроме сведений о таблице можно увидеть запрос на загрузку данных:

Queries & Connections — Лицензирование…

Пока в этом разделе у нас есть один запрос (рис. 2.6). Название запроса тоже соответствует заголовку раздела в источнике данных (рис. 2.1).

Рис. 2.6. Запрос для загрузки

Задание. Сравните название запроса и заголовок раздела статьи.

Исправим название таблицы.

Вызываем инструмент Диспетчер имён:

Formulas — Defined Names — Name Manager (рис. 2.7).

Рис. 2.7. Запуск Диспетчера имён объектов

Задание. Запустите Диспетчер имён.

Появляется диалоговое окно Диспетчера имён:

Name Manager (рис. 2.8).

Рассмотрим сведения о нашем объекте.

В графе Name (Название) указано:

Лицензирование_править___править_код.

В графе Value (Значения) можно видеть содержимое ячеек таблицы по строкам.

В графе Refers To указан лист рабочей книги Excel и диапазон ячеек, в который была загружена наша таблица.

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

Задание. Изучите сведения об объекте «Лицензирование…».

Нажимаем кнопку Edit.

В окне редактирования Edit Name исправляем название и оставляем слово «Лицензирование». Добавляем комментарий (рис. 2.9).

Рис. 2.9. Редактирование названия объекта

Задание. Исправьте название таблицы.

Вернёмся к списку запросов (рис. 2.6). Для этого укажем на таблицу и выберем в верхнем меню

Data — Queries & Connections — Queries & Connections.

Видим, что название запроса не изменилось.

Подводим курсор к запросу и рассматриваем вплывающее окно с подробным описанием запроса (рис. 2.10).

В верхней части окна можно видеть название запроса.

Ниже видим саму таблицу.

Далее идёт список столбцов

Columns.

Здесь же указано количество столбцов:

(3).

Кроме того, нам сообщают время, когда было сделано последнее обновление данных из источника Last refreshed.

В нижней части окна указан источник данных

Data Sources.

Это ссылка на страницу в интернете.

Нам также сообщают, что мы использовали всего один источник данных

(1).

Рис. 2.10. Сведения о запросе

Задание. Изучите сведения о запросе.

Запустим редактор запросов.

Нажимаем на наш запрос правой кнопкой мыши и выбираем в контекстном меню

Edit (рис. 2.11).

Рис. 2.11. Вызов редактора запросов

Задание. Запустите редактирование своего запроса.

Появляется окно Power Query Editor (рис. 2.12).

Как видим, редактор запросов по-прежнему называется Power Query.

Исправим название запроса и оставим только слово «Лицензирование».

Закрываем окно редактора:

Home — Close — Close & Load.

Рис. 2.12. Редактирование названия запроса

Задание. Измените название запроса.

Обратим внимание на сведения о запросе (рис. 2.13).

Название запроса изменилось. Мы убрали лишние слова и знаки. В дальнейшей работе будет гораздо проще работать с простыми и понятными названиями.

Рис. 2.13. Запрос с новым названием

Задание. Убедитесь, что название запроса изменилось.

Мы загрузили данные из Википедии. И мы знаем, про какие лицензии идёт речь. Но через пару дней или недель всё забудется. А в нашем файле будет ещё несколько десятков разных запросов.

Так что лучше бы нам сразу дать информативное название.

Например, сказать, что это сведения о вариантах лицензий на программные средства из линейки Power BI и что мы взяли эти данные в статье на сайте Википедии. Объяснение получается длинное. Попробуем оставить буквально несколько слов — то, что уже нельзя сократить без потери информации.

Теперь начинает проясняться смысл высказывания «Краткость — сестра таланта». Надо по-настоящему понять материал, чтобы кратко что-то сообщить, но при этом передать смысл, идею. Интересно, кто-нибудь вспомнит автора этой фразы?

Задание. Сделайте название запроса коротким и информативным.

3. Загрузка таблицы дивидендов

Загрузим ещё одну табличку из интернета. На этот раз нас будет интересовать таблица размеров дивидендов на сайте компании «Газпром».

Открываем в браузере страницу сайта компании ПАО «Газпром»:

https://www.gazprom.ru/

Переходим в следующий раздел:

Акционерам и инвесторам — Дивиденды — История дивидендных выплат (рис. 3.1).

Рис. 3.1. Таблица дивидендов

Задание. Откройте страницу дивидендов на сайте ПАО «Газпром».

Скопируем адрес страницы в буфер обмена.

Вызываем функцию загрузки данных из интернета:

Data — Get & Transform — From Web.

Указываем анонимный доступ к сайту без ввода пароля (рис. 3.2).

Нажимаем кнопку:

Connect.

Рис. 3.2. Анонимный доступ

Задание. Выберите анонимный доступ к сайту.

Появляется диалоговое окно

Navigator.

Выбираем таблицу дивидендов (рис. 3.3).

Нажимаем кнопку

Load.

Рис. 3.3. Выбор таблицы для загрузки

Задание. Загрузите таблицу дивидендов Газпрома.

Рассмотрим загруженную таблицу (рис. 3.4). Нам предстоит кое-что исправить. Причём почти всё.

На этот раз название таблицы и соответствующего запроса получилось не очень информативное:

Table_0.

Заголовки столбцов после загрузки были продублированы.

Числовые значения распознаны как текстовые строки. Содержимое ячеек прижато влево — это текстовый формат по умолчанию.

Английская версия Excel ожидает точку как десятичный разделитель целой и дробной частей. А русская страница сайта использует для этой цели запятую.

Мало того, пробел разделяет группы по три разряда — тысячи, миллионы, миллиарды.

Рис. 3.4. Загруженная таблица дивидендов

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

Переходим к преобразованиям.

Открываем редактор запросов (рис. 3.4):

Data — Get & Transform — Get Data — Launch Power Query Editor.

Рис. 3.4. Запуск редактора запросов

Задание. Запустите редактор запросов.

Появляется окно редактора запросов (рис. 3.5).

Видим название запроса:

Table_0.

В заголовках всех колонок указано, что тип данных — текст:

ABC

В верхнем меню тоже говорится, что здесь данные текстового типа:

Home — Transform — Data Type — Text.

Рис. 3.5. Редактор запросов

Задание. Обратите внимание на название запроса и тип данных.

Начнём исправления загруженной таблицы.

Зададим новое название запроса:

Дивиденды Газпрома.

Изменим тип данных для первого столбца:

Home — Transform — Data Type — Decimal Number.

Рис. 3.6. Изменение типа данных

Задание. Измените тип данных.

Появляется диалоговое окно

Change Column Type.

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

Нам не потребуется возвращаться к текстовому типу номера года. Поэтому выбираем первый вариант:

Replace current.

Рис. 3.7. Параметры преобразования типа

Задание. Выберите замену преобразования.

Рассмотрим результаты преобразования типа данных (рис. 3.8).

Тип данных для первого столбца теперь установлен ка числовой:

Data Type — Decimal Number.

В заголовке столбца тоже указано, что здесь находятся числа:

1.2.

Сами числа в первой колонке теперь прижаты вправо. Это формат числа по умолчанию.

В разделе Применённые шаги указано преобразование типа:

Query Settings — Applied Steps — Changed Type.

Рис. 3.8. Результаты преобразования типа

Задание. Рассмотрите результаты преобразования типа данных в первом столбце.

Первая строка, которая дублирует заголовок, содержит текст. Поэтому преобразование завершилось ошибкой, о чём нам и сообщают:

Error.

Щёлкнем по этой ячейке и получим более подробное пояснение (рис. 3.9).

В разделе Применённые шаги появляется новая строка:

Год.

Щёлкаем по красному крестику и возвращаемся к предыдущему шагу.

Рис. 3.9. Сообщение об ошибке

Задание. Изучите сообщение об ошибке формата данных.

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

Выбираем в верхнем меню

Home — Reduce Rows — Remove Rows — Remove Errors.

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

Рис. 3.10. Удаление строк с ошибками

После удаления строк с ошибками получаем более красивую таблицу (рис. 3.11).

Кроме в разделе Применённые шаги добавилась строка

Removed Errors.

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

Рис. 3.11. Таблица после удаления ошибок

Задание. Удалите строки с ошибками и обратите внимание на количество строк.

Переходим ко второму столбцу — величине дивидендов.

Зададим числовой тип данных.

Содержимое действительно стало числовым (рис. 3.12).

Сравним с оригиналом — числа стали гораздо больше. Программа просто проигнорировала запятые. Это искажает данные. Откатим изменения.

Рис. 3.12. Результаты преобразования второго столбца

Задание. Задайте целый тип данных для второго столбца и обратите внимание на изменения.

Попробуем заменить запятые на точки.

Находим инструмент поиска и замены в меню (рис. 3.13):

Home — Transform — Replace Values

Рис. 3.13. Функция замены значений

Задание. Прочитайте всплывающую подсказку для кнопки Замена значений.

Нажимаем кнопку Замена значений:

Replace Values.

Заменим запятые на точки (рис. 3.14).

Рис. 3.14. Поиск и замена

Задание. Замените запятые на точки.

Рассмотрим результаты поиска и замены (рис. 3.15).

Действительно, теперь у нас во втором столбце точки вместо запятых.

При этом тип данных по-прежнему текстовый.

Рис. 3.15. Результаты поиска и замены

Задание. Замените запятые на точки во втором столбце.

Изменим тип данных во втором столбце с текстового на числовой:

Decimal Number.

Обращаем внимание на изменение форматирования и сообщение о типе в заголовке столбца.

В одной ячейке за 1998 год был прочерк. Возможно, это означает, что дивиденды не выплачивались. То есть равны нулю.

Теперь в этой ячейке выводится сообщение об ошибке (рис. 3.16).

Рис. 3.16. Преобразование типа второго столбца

Задание. Измените тип данных на числовой и обратите внимание на сообщение об ошибке.

Отменяем последний шаг преобразований.

Заменяем прочерк, то есть символ тире, на ноль.

Щёлкаем по ячейке с неправильным содержимым и копируем её в буфер обмена. Затем вызываем функцию поиска и замены. В строке поиска будет загадочное тире. В строке замены — ничего.

Снова изменяем тип данных на числовой.

Теперь всё прошло успешно (рис. 3.17).

В ячейке за 1998 год стоит ноль.

Ошибок нет.

Все выполненные шаги по преобразованию данных выводятся в списке

APPLIED STEPS

Рис. 3.17. Второй столбец преобразован

Задание. Завершите преобразование второго столбца и убедитесь в отсутствии ошибок.

Здесь мы столкнулись с длинным тире. Это не просто чёрточка. Неплохо было бы с этими чёрточками познакомиться. А ещё есть символ минуса. Это тоже какая-то чёрточка. Или это одно и то же?

Задание. Прочитайте в Википедии статьи «Тире», «Дефис» и «Минус». Выясните английские названия этих символов, какие символы для каких целей используют, а также как вводить разные виды тире в текстовом редакторе Microsoft Word.

Преобразование данных в третьем столбце потребует нескольких действий:

— удалить пробелы, которые мешают преобразованию в число;

— возможно, удалить запятую и два нуля в конце строк;

— преобразовать тип данных в числовой и настроить формат вывода на экран.

Мало того, в данных за 2015 год отсутствует один пробел.

Выделяем третий столбец и начинаем проводить перечисленные преобразования.

Замена пробела на «ничто» не удалась.

Попробуем выяснить причину.

Возвращаемся в браузер и вызываем просмотр исходного текста страницы сайта (рис. 3.18). Нажимаем правую кнопку мыши и в контекстном меню выбираем

View Page Source.

Мы работаем в браузере Firefox. В других браузерах функция просмотра исходного текста может скрываться в другом месте.

Рис. 3.18. Переход к исходному тексту страницы

Задание. Откройте исходный текст страницы.

Рассматриваем полученный текст (рис. 3.19).

Между тройками цифр стоит загадочная комбинацию букв.

А в неудачной строке за 2015 год есть и пробел, и эти загадочные буквы. В конце числа пробел действительно отсутствует.

Рис. 3.19. Фрагмент исходного текста

Задание. Ознакомьтесь с представлением данных за 2015 и 2016 годы в исходном тексте.

Поищем в интернете ответ на вопрос:

что такое &nbsp;

Выясняется, что это так называемый НЕРАЗРЫВНЫЙ ПРОБЕЛ (рис. 3.20).

Английское название: non-breaking space.

Сокращённое обозначение: NBSP — Non-Breaking Space.

Рис. 3.20. Описание неразрывного пробела

Задание. Прочитайте в Википедии статью «Неразрывный пробел» и выясните, в каких случаях его рекомендуют применять, а также как его вставлять в редакторе Microsoft Word.

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

Снова вызываем функцию поиска и замены:

Replace Values.

На этот раз сделаем всё правильно (рис. 3.30).

Раскрываем дополнительные возможности

Advanced Options.

Ставим отметку для выбора специальных символов:

Replace using special characters.

Неразрывный пробел относится как раз к специальным символам.

Щёлкаем по строке поиска:

Value To Find.

Раскрываем выпадающий список:

Insert special character.

Выбираем в этом списке тот самый неразрывный пробел:

Non-breaking Space.

А вот в строке замены не пишем ничего.

Значит пробел заменят на ничто. То есть мы все эти символы удаляем.

Нажимаем ОК.

Рис. 3.30. Удаление неразрывного пробела

Задание. Удалите неразрывные пробелы в третьем столбце.

Рассмотрим полученные результаты (рис. 3.31).

Числа стали похожи на числа.

Но в данных за 2015 год остался один пробел.

Изучаем исходный текст и обнаруживаем один настоящий пробел. Похоже при составлении данной страницы каждый год в таблицу дивидендов добавляли новую строчку. И данные за 2015 год ввели по-другому. Может быть, использовали другой редактор HTML. А, может, просто была плохая погода. Или, наоборот, хорошая. Нас интересует получить числа. А по пути приходится выяснять причины ошибок и справляться с ними.

Рис. 3.31. Третий столбец после удаления NBSP

Задание. Изучите результаты замены для 2015 года.

Задаём поиск и удаление обычного пробела.

Пробел исчезает. Остаются красивые числа.

Изменяем тип данных на числовой.

Теперь в третьем столбце появились числа.

А в пустых ячейках появилось слово NULL. Это не число «ноль». Это «ничего», то есть «данные отсутствуют». Скорее всего, этот столбец добавили в 2013 году. А, может быть, до 2013 года действительно не объявляли размер дивидендов.

Рис. 3.32. Третий столбец — числовые значения

Задание. Удалите пробелы.

Нажимаем кнопку Закрыть редактор и загрузить данные:

Close & Load.

Мы здесь не просто редактировали табличку в Excel.

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

Рассмотрим, что же мы загрузили (рис. 3.33).

Рис. 3.33. Результаты загрузки

Задание. Изучите результаты загрузки.

Числа в третьем столбце выводятся в формате с плавающей точкой. То есть 3,0 умножить на десять в тринадцатой степени. Это приятно для программиста, но тяжело воспринимается на глаз.

Зададим формат вывода без дробной части и с разделителями тысяч-миллионов.

Выделяем диапазон числовых значений в третьей колонке.

Вызываем контекстное меню и выбираем форматирование ячеек (рис. 3.34):

Format Cells.

Number — Category — Number — Decimal places — 0.

Дополнительно выбираем пункт с разделителями:

Use 1000 separator (,).

Здесь только один вариант разделителя — запятая.

Результат форматирования нам сразу же показывают в разделе предварительного просмотра

Sample.

Само число можно видеть в строке формул. Здесь число даётся без украшательства.

Теперь у нас числа читаются немного легче. Можно посчитать тройки и выяснить, что речь идёт о миллионах или о чём-то ещё.

Рис. 3.34. Формат числа с разделителями

Задание. Настройте формат вывода длинных чисел на экран.

Обратим внимание, что колонка «Год» получила вещественный числовой тип. Это подчёркивается обозначением 1.2. То есть одна целая и две десятых.

У нас есть два числовых типа:

— Decimal Number — вещественное число;

— Whole Number — целое число.

Задание. Измените тип колонки «Год» на целое и обратите внимание на указание типа в заголовке столбца.

Кстати, пришло время освежить в памяти названия больших чисел. Что идёт после тысячи? А после миллиона? Само собой, нас интересуют степени числа ТЫСЯЧА. Все остальные, промежуточные числа составлены из этих названий.

Задание. Составьте таблицу чисел и их названий.

4. Задание по загрузке таблицы из интернет

Мы рассмотрели некоторые приёмы, которые помогут извлекать данные из страничек в интернете. Пришло время для самостоятельной работы.

4.1. Загрузка сведений о дивидендах

Возьмём для дальнейшей работы список компаний из российского индекса голубых фишек. Действующий состав индекса можно найти на сайте МосБиржи. Выбираем компанию под номером, который соответствует варианту задания. Если выбранная компания не выплачивает дивиденды, берём следующую компанию из списка.

Номер варианта задания — последняя цифра номера зачётки. Если последняя цифра — ноль, выбираем число 10.

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

Акционерные компании выплачивают владельцам акций дивиденды. Это часть прибыли компании. Соответственно, на своём сайте такие компании обязаны сообщать о размере дивидендов.

Задание. Извлеките данные о дивидендах из страницы для инвесторов/акционеров на сайте акционерной компании в соответствии с вариантом задания.

В интернете можно найти сайты, которые ведут аналитику дивидендных выплат. Один из примеров таких ресурсов — сайт управляющей компании «ДОХОДЪ»:

https://www.dohod.ru/

Задание. Найдите страницу компании в соответствии с вариантом задания и загрузите в Excel таблицу дивидендов.

При загрузке из интернета Excel выводит диалоговое окно выбора таблицы (рис. 3.35):

Navigator.

В предыдущих опытах мы нажимали кнопку загрузки:

Load.

У нас есть и другой вариант — это кнопка преобразования:

Transform Data.

Попробуем нажать эту кнопку.

Рис. 3.35. Преобразование вместо загрузки

Задание. Запустите преобразование данных при загрузке таблицы с сайта.

4.2. Загрузка котировок Yahoo! Finance

Следующее задание — извлечение и загрузка данных из сайта Финансы Yahoo:

https://finance.yahoo.com/

На сайте можно найти котировки компании, если знать её биржевое обозначение — тикер.

Рис. 3.36. Строка поиска службы Финансы

Чтобы узнать тикер, рассмотрим список компаний из индекса голубых фишек (рис. 3.37). Выбираем последнюю строку списка. Нас будет интересовать биржевой тикер НЛМК. Это четыре английские буквы:

NLMK

Рис. 3.37. Список голубых фишек

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

Задаём поиск по тикеру NLMK.

В результатах поиска находим данные Московской биржи (рис. 3.38).

У нас есть два указания на котировки данной ценной бумаги именно на Московской бирже:

NLMK.ME

Equity — MCX.

Рис. 3.38. Поиск по тикеру

Задание. Найдите страницу котировок компании на МосБирже

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

Переходим по ссылке для скачивания исторических данных:

Historical Data.

Рис. 3.39. Страница сведений о компании

Задание. Перейдите на страницу исторических данных.

На странице исторических данных можно увидеть таблицу котировок (рис. 3.40).

В строке адреса в браузере видим намёк на историю:

history.

Рис. 3.40. Исторические данные

Выбираем даты начала и конца исторического периода (рис. 3.41).

После настройки дат нажимаем кнопку

Done.

Убеждаемся, что нужные даты выставлены в строке

Time Period.

Нажимаем кнопку

Apply.

Рис. 3.41. Выбор диапазона дат

Задание. Настройте даты начала и конца периода так, чтобы получить данные за последние два года.

На странице сайта есть возможность скачать файл:

Download Data.

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

Для формирования запроса копируем строку адреса.

Возвращаемся в Excel.

Вызываем загрузку из интернета (рис. 3.42).

В строке адреса указан не только адрес страницы. В параметрах имеются ссылки на даты начала и конца периода.

Здесь имеется указание на загрузку дневных данных:

frequency=1d.

Рис. 3.42. Загрузка из интернета

Задание. Загрузите исторические данные с помощью запроса.

Дальнейшие действия мы уже обсуждали.

Выбираем нужную таблицу для извлечения.

Настраиваем преобразование данных.

Загружаем таблицу в Excel.

Задание. Извлеките и, преобразуйте и загрузите исторические данные в Excel.

5. Извлечение данных из файлов

При загрузке данных из файлов можно использовать сразу несколько источников данных. Можно извлечь данные из нескольких файлов в выбранном каталоге и объединить их в одну таблицу.

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

5.1. Варианты заданий

В каждом варианте указан отраслевой индекс Московской Биржи.

Номер варианта — последняя цифра номера зачётки. Для нуля берём вариант 10.

Варианты заданий приведены в таблице 5.1.

Задание. Опишите в отчёте свой вариант задания.

Отраслевые индексы включают в свой состав наиболее ликвидные акции компаний соответствующей отрасли экономики.

Чтобы ознакомиться с составом индекса, откроем в браузере сайт МосБиржи:

https://www.moex.com/

Переходим в следующий раздел:

Индексы — Все индексы.

Мы будем рассматривать в качестве примера индекс голубых фишек.

Далее переходим в раздел выбранного индекса и выбираем следующую ссылку:

База расчёта.

Получаем список тикеров акций, входящих в состав индекса (рис. 5.1).

Рис. 5.1. Состав индекса

Задание. Внесите в отчёт состав своего индекса.

5.2. Выбор акций

Выбираем в составе индекса пару компаний с самой большой КАПИТАЛИЗАЦИЕЙ — рыночной стоимостью всех акций. В составе индекса голубых фишек это следующие бумаги:

— SBER;

— GAZP.

Задаём поиск на сайте биржи по тикеру и выясняем, какие это акции:

— Публичное акционерное общество «Газпром», акция обыкновенная (GAZP);

— Публичное акционерное общество «Сбербанк России», акция обыкновенная (SBER).

Записываем в отчёте названия выбранных бумаг.

Задание. Выберите в составе индекса компании с самой высокой капитализацией и внесите в отчёт названия ценных бумаг.

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

Нам совершенно не подходят такие названия:

— Папка;

— Папка 2;

— Новая папка;

— Моя папка.

Эти слова не сообщают читателю ничего полезного.

Рис. 5.2. Папка для котировок

Задание. Создайте папку для котировок.

Мы будем экспортировать котировки на сайте компании «Финам»:

https://www.finam.ru/

Переходим в следующий раздел сайта:

Теханализ — Экспорт котировок.

Выбираем торговую площадку:

МосБиржа акции

Указываем название актива:

ГАЗПРОМ ао.

Выбираем интервал:

один год.

Периодичность:

1 день.

Нажимаем кнопку

Получить файл (рис. 5.3).

Сохраняем данные в файл типа *. CSV.

Рис. 5.3. Настройки экспорта котировок

Задание. Экспортируйте котировки двух выбранных компаний.

Просматриваем содержимое полученных файлов с помощью Блокнота (рис. 5.4):

Open with — Notepad.

Видим, что разделитель полей — точка с запятой.

Разделитель целой и дробной частей — точка.

В нашем файле есть заголовки.

Рис. 5.4. Содержимое файла котировок

Задание. Изучите содержимое полученных файлов.

5.3. Загрузка файлов из папки

Для загрузки файлов из каталога выбираем в верхнем меню следующий пункт:

Data — Get & Transform Data — From File — From Folder.

Всплывающая подсказка поясняет, что мы можем сделать с помощью этого инструмента (рис. 5.5).

Рис. 5.5. Загрузка файлов из каталога

В диалоговом окне Folder (Папка) выбираем каталог для загрузки файлов (рис. 5.6).

Нажимаем кнопку OK.

Рис. 5.6. Выбор каталога для загрузки

Задание. Вызовите загрузку файлов из вашего каталога.

Появляется диалоговое окно для настройки варианта загрузки (рис. 5.7).

Нам предлагают объединить данные из всех файлов в указанном каталоге:

Combine & Transform Data.

Попробуем это проделать.

Рис. 5.7. Выбор файлов для загрузки

Задание. Выберите вариант объединения и преобразования данных.

Появляется диалоговое окно объединения данных из файлов:

Combine Files.

Рассматриваем пример распознанного содержимого файлов (рис. 5.8).

Здесь также указана кодировка исходных файлов и разделитель полей.

Нажимаем кнопку ОК.

Рис. 5.8. Настройка объединения файлов

Следующее окно — редактор запросов (рис. 5.9):

Power Query Editor.

Рассмотрим полученную таблицу.

Первая строка использована для формирования заголовков.

Даты представлены как целые числа.

Последние 5 столбцов — числовые значения.

Рис. 5.9. Редактор запросов

Задание. Загрузите данные и изучите форму запроса.

Теперь можно заняться очисткой и преобразованием данных.

Попробуем удалить лишние столбцы:

— Source_Name;

— PER;

— TIME.

Щёлкаем правой кнопкой по первому столбцу и выбираем в контекстном меню:

Remove.

Удалим два других лишних столбца.

Нажимаем кнопку Закрыть и загрузить:

Close & Load.

Рис. 5.10. Удаление столбца

Задание. Удалите лишние столбцы.

Наши файлы имеют однотипный формат. В первой строке файла расположены названия полей (столбцов).

Рассмотрим объединённую таблицу (рис. 5.11).

На границе данных по Газпрому и Сбербанку лишних строк не наблюдается.

Будем считать, что заголовки обработаны корректно. Теперь у нас один общий заголовок.

Рис. 5.11. Результаты загрузки

Задание. Рассмотрите данные в полученной таблице и убедитесь в отсутствии лишних строк (второго заголовка).

Возвращаемся к редактору запросов.

Щёлкаем правой кнопкой по нашему запросу и выбираем в контекстном меню редактирование:

Edit.

Проверим, что у нас есть в колонке тикеров (рис. 5.12).

Нажимаем кнопку фильтрации в заголовке первого столбца.

Список значений содержит всего два варианта:

— GAZP

— SBER

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

Рис. 5.12. Проверка лишних строк

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

Поработаем со столбцом дат.

Здесь четыре цифры года, две цифры месяца и две цифры числа.

Щёлкнем по столбцу дат и попробуем выбрать тип значений — Дата:

Home — Transform — Data Type — Date.

Попытка не удалась. Во всех ячейках столбца выводится сообщение об ошибке (рис. 5.13).

Нажимаем красный крестик в списке применённых шагов и откатываем последнее преобразование.

Рис. 5.13. Ошибка преобразования типа «Дата»

Задание. Измените тип данных на «Дату». При появлении сообщений об ошибке откатите изменения.

Подойдём к преобразованию даты с другой стороны.

Выделим год, месяц и день в отдельные колонки.

Щёлкаем по столбцу дат.

Нажимаем в верхнем меню кнопку разделения столбца:

Home — Transform — Split Column — By Number of Characters.

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

Рис. 5.14. Разделение столбца

Появляется диалоговое окно для настройки разделения окна:

Split Column by Number of Characters.

Указываем количество символов (рис. 5.15):

Number of characters — 4.

Задание. Разделите столбец на части по 4 символа.

Рассмотрим результаты (рис. 5.16).

Теперь у нас два столбца вместо одного.

В обоих столбцах тип данных — целые числа.

Номер года выглядит хорошо. А вот номер месяца и дня пострадал. Вместо 0306 получилось 306. Ноль перед числом удалили, и осталось три цифры. Если нам попадутся месяцы 10, 11 или 12, то в ячейке будет четыре цифры. И с таким содержимым работать будет совсем тяжело.

Рис. 5.16. Результаты разделения столбца

Задание. Изучите результаты разделения столбца.

Обратим внимание, что в списке применённых шагов есть два действия:

— Split Column by Position — разделение столбцов по количеству символов;

— Changed Type1 — изменение типа значения в ячейках.

Откатим изменение типа и посмотрим на результат (рис. 5.17).

Обе колонки стали текстовыми, и обе содержат по четыре символа.

Рис. 5.17. Тип значений — текст

Задание. Отмените преобразование типа и обратите внимание на тип данных в столбцах.

Разделим второй столбец на два — чтобы в каждом было по два символа. И снова отменим преобразование типа.

Теперь у нас три столбца, и все три — текстовые (рис. 5.18).

Рис. 5.18. Текстовый тип значений

Задание. Разделите столбец на месяц и день и отмените преобразование типа.

Объединим наши три столбца, но поставим между значения разделитель — точку.

Выделяем три столбца, нажав клавишу Ctrl и щёлкая по столбцам.

Вызываем объединение столбцов:

Transform — Text Column — Merge Columns.

Появляется диалоговое окно

Merge Columns.

Настроим параметры объединения столбцов (рис. 5.19).

Разделитель — пользовательская настройка — точка:

Separator — Custom — (.).

Название объединённого столбца — Дата:

New column name — Date.

Нажимаем ОК.

Рис. 5.19. Настройка объединения столбцов

Задание. Объедините столбцы с использованием разделителя.

Посмотрим, что у нас получилось (рис. 5.20).

Четыре цифры — год. Точка. Две цифры — месяц. Точка. Две цифры — день.

Тип данных — текст.

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

Задание. Рассмотрите результаты объединения столбцов.

После преобразования типа наш столбец стал похож на дату (рис. 5.21). Правда, это дата в американском формате:

месяц/день/год.

В заголовке столбца указано, что это дата — видно значок календаря.

В группе Transform тоже говорится, что это дата:

Data Type — Date.

Рис. 5.21. Столбец «Дата» в формате даты

Задание. Преобразуйте тип данных в дату.

Теперь для полноты картины подправим заголовки остальных столбцов. Щёлкаем по заголовку и выбираем в контекстном меню пункт Rename. Редактируем каждый заголовок и убираем угловые скобки (рис. 5.22).

Рис. 5.22. Отредактированные заголовки

Задание. Отредактируйте заголовки и обратите внимание на изменения в списке применённых шагов.

Мы настроили преобразование данных.

Нажимаем ОК и получаем объединённую таблицу (рис. 5.23).

Заголовки исправлены.

Числа не пострадали.

Но дата пока что выводится в американском формате.

Рис. 5.23. Объединённая таблица.

Нам остаётся настроить формат даты.

Выделяем диапазон ячеек с датами и настраиваем формат вывода даты (рис. 5.24).

Рис. 5.24. Формат даты

Задание. Настройте формат даты.

Теперь добавим файлы в нашу папку.

Скачаем котировки ещё трёх крупнейших компаний из состава индекса:

— ROSN — Роснефть;

— LKOH — Лукойл;

— GMKN — Горно-металлургический комбинат (ГМК) Норильский никель.

Теперь в нашей папке пять файлов с котировками (рис. 5.25).

Рис. 5.25. Добавляем файлы с котировками

Задание. Добавьте три файла с котировками.

Мы добавили файлы в папку.

Теперь обновим запрос к данным.

Щёлкаем правой кнопкой мыши по нашему запросу в списке

Queries & Connections.

В контекстном меню выбираем обновление:

Refresh.

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

Посмотрим, все ли компании у нас собраны в таблице.

Нажимаем кнопку фильтрации в заголовке столбца TICKER.

В разделе выбора значений видим пять знакомых названий. Лишних значений нет.

Пока всё идёт нормально.

Рис. 5.26. Список загруженных котировок

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

Осталось одно «НО»: формат даты.

Мы настроили формат даты.

Но после обновления запроса и добавления данных формат вернулся к первоначальному (рис. 5.27).

С этим нужно что-то сделать…

Попробуйте на досуге с этой проблемой справиться.

Рис. 5.27. Формат даты

Задание. Рассмотрите объединённую таблицу и обратите внимание на формат даты.

5.4. Дубликаты данных

При загрузке данных из нескольких источников можно получить несколько совершенно одинаковых, идентичных строк. Это называется ДУБЛИКАТЫ данных. Если это две разные записи, относящиеся к одному и тому же факту, нужно оставить только одну запись, а дублирующие строки удалить.

Средства ETL позволяют проводить ОЧИСТКУ данных. Это включает, в том числе, и удаление дубликатов. Смоделируем ситуацию с загрузкой дубликатов и посмотрим, как с этим справиться.

Загрузим ещё один файл для первой ценной бумаги с данными за последний календарный месяц. Поместим файл в каталог для загрузки (рис. 5.28).

Теперь в нашем каталоге два файла с котировками акций Газпрома:

— GAZP_190306_200306.csv;

— GAZP_200301_200306.csv.

В первом файле содержатся данные за период с 6 марта 2019 года по 6 марта 2020 года.

Во втором файле — с 1 марта 2020 года по 6 марта 2020 года.

Второй файл дублирует пять строк первого файла.

Рис. 5.28. Файл с дубликатами данных

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

Итак, мы подготовили файлы с дубликатами данных.

Обновим наш запрос и посмотрим на результаты.

Обратим внимание на количество строк рядом с названием запроса.

Рассмотрим объединённую таблицу (рис. 5.29).

Данные в строках 250—254 в точности совпадают с данными в строках 255— 259. Это и есть наши дубликаты, от которых нам нужно буде избавиться.

Рис. 5.29. Дубликаты данных в объединённой таблице

Задание. Обновите запрос и проверьте, появились ли дубликаты в объединённой таблице.

Возвращаемся в редактор запросов.

Выбираем в верхнем меню удаление дубликатов:

Home — Reduce Rows — Remove Rows — Remove Duplicates.

Но не будем спешить нажимать ОК и Next.

Для начала почитаем всплывающую подсказку (рис. 5.30).

Нам сообщают, что будут удалены строки, где есть дубликаты в выбранных столбцах.

Рис. 5.30. Удаление дубликатов

Задание. Изучите всплывающую подсказку по поводу удаления дубликатов.

Что будет, если выбрать первый столбец с тикерами и удалить дубликаты? Попробуем.

У нас осталось всего пять строк (рис. 5.31).

Мы потеряли практически все данные, зато теперь тикеры не повторяются.

Нам такое не подходит. Откатываем назад последнее преобразование:

Removed Duplicates.

Нажимаем красный крестик рядом с названием шага преобразования с списке применённых шагов.

Рис. 5.31. Удаление дубликатов тикеров

Задание. Удалите дубликаты по первому столбцу и изучите результаты преобразования. Откатите последнее изменение.

Мы лишний раз убедились в справедливости следующего шуточного высказывания:

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

Так что же мы ХОТЕЛИ сделать? Нам нужно было удалить строки с одинаковыми записями. То есть строки с одинаковыми датами для одной и той же бумаги. Стало быть, нас интересуют дубликаты тикера и даты одновременно.

Выделяем два столбца, нажав клавишу Ctrl.

Ещё раз вызываем удаление дубликатов.

Нажимаем кнопку Закрыть и загрузить:

Close & Load.

Задание. Удалите дубликаты.

Проверяем, что получилось.

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

View — Window — Freeze Panes — Freeze Top Row.

Теперь можно листать таблицу, а заголовок останется в верхней строке листа (рис. 5.32).

Рис. 5.32. Закрепляем заголовки

Задание. Закрепите заголовки таблицы.

Находим строки, которые раньше были продублированы.

Теперь дубликатов нет (рис. 5.33).

Очистка данных прошла успешно.

Рис. 5.33. Дубликаты отсутствуют

Задание. Изучите объединённую таблицу и убедитесь, что дубликатов больше нет.

5.5. Просмотр запроса

Поработаем ещё немного с запросами.

Сохраним и закроем файл. Откроем его снова.

Чтобы перейти к запросам, нажмём кнопку просмотра списка запросов:

Data — Queries & Connections — Queries & Connections.

Прочитаем всплывающую подсказку (рис. 5.34).

Здесь нам поясняют разницу между двумя инструментами ETL.

Рис. 5.34. Запросы и соединения

Задание. Прочитайте всплывающую подсказку и выясните, что такое Queries, что такое Connections и для чего их используют.

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

Познакомимся с текстом запроса.

Вызываем редактор запросов.

Затем вызываем в верхнем меню продвинутую версию редактора запросов (рис. 5.35).

Home — Query — Advanced Editor.

Чтобы закрыть окно «улучшенного» редактора, можно нажать кнопку

Done.

Рис. 5.35. Текст запроса к данным

Задание. Запустите просмотр текста запроса и ознакомьтесь с программой. Сравните строки программы запроса и строки в списке применённых шагов запроса Applied Steps. Обратите внимание на параметры каждой команды.

6. Анализ данных

Мы загрузили данные, теперь их можно анализировать.

У нас есть котировки и объёмы торгов по каждой бумаге. Мы будем проводить анализ распределения и взаимосвязей по этим данным с помощью сводных таблиц.

Это задание основано на знаниях и навыках, полученных в работах [6—10].

6.1. Анализ распределения объёмов

Напомним, что ОБЪЁМ торгов — это количество акций, которые сменили хозяев за рассматриваемый период. То есть измеряется объём в количестве акций, в «штуках». Стало быть, по определению это целое число. В нашем примере мы используем так называемые ДНЕВНЫЕ данные. То есть данные за один торговый день, это около девяти часов.

Объём торгов — это случайная величина. Каждый день объём принимает разное значение. но случайность не означает полную непредсказуемость.

Закономерности в поведении случайной величины описываются, прежде всего, РАСПРЕДЕЛЕНИЕМ. Это частота (вероятность) появления разных значений. Оценка формы кривой распределения делается с помощью гистограммы. А гистограмма строится по результатам группировки данных.

Мы проведём группировку данных по объёмам для каждой бумаги и построим гистограммы распределения.

Используем правило Стёрджеса:

n = 1 +3,322 lg N, где

n — число интервалов группировки;

N — объём выборки;

lg — десятичный логарифм.

В нашем примере мы загрузили по 253 строки для каждой бумаги.

Получаем:

n = 1 +3,322 * lg 253 = 1 +3,322 * 2,4 = 9.

Получается такая рекомендация: группировать данные по 9 интервалов.

Задание. Определите число интервалов группировки.

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

Определим минимальное и максимальное значение по каждой бумаге. Для этого построим вспомогательную сводную таблицу.

Щёлкаем по объединённой таблице и выбираем в верхнем меню вставку сводной таблицы (рис. 6.1):

Insert — Tables — PivotTable.

Рис. 6.1. Вставка сводной таблицы

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

— Rows — по строкам — тикер;

— Values — значения — объём торгов.

В поле Значения мы перетаскиваем объёмы VOL два раза.

По умолчанию выбирается метод агрегирования — сумма. Вручную устанавливаем определение минимального и максимального значений.

Рис. 6.2. Минимум и максимум

Задание. Найдите наибольшие и наименьшие значения по каждой бумаге.

Вычисляем размер интервалов.

Вводим простую формулу:

(Vmax — Vmin) / 9.

Excel автоматически вставляет вызов функции, которая ссылается на ячейки сводной таблицы (рис. 6.3):

GETPIVOTDATA (рис. 6.3).

Копируем формулу в остальные ячейки столбца, потянув за маркер автозаполнения.

Однако, все результаты оказались одинаковыми.

Единственный адрес, который есть в формуле, — это адрес сводной таблицы. Данные из этой сводной таблицы извлекаются по значению поля, а не по адресу ячейки.

Рис. 6.3. Копирование формулы

Задание. Введите формулу вычисления длины интервала и скопируйте её в остальные ячейки столбца. Рассмотрите формулы в ячейках и выясните, как оформлены ссылки на обобщённые показатели.

Копировать формулы не удалось. Значит, нам придётся вручную сформировать каждую формулу (рис. 6.4).

Теперь в каждой формуле появляются ссылки на разные значения поля «Тикер».

Рис. 6.4. Ссылки на ячейки сводной таблицы

Задание. Введите формулы вычисления интервала вручную. Обратите внимание на ссылки на ячейки сводной таблицы.

Переходим к группировке данных.

Создаём новую сводную таблицу.

Настраиваем поля следующим образом (рис. 6.5):

— Rows — VOL;

— Values — VOL;

— Filters — TICKER.

По строкам у нас будут значения объёмов. Далее мы их сгруппируем по интервалам.

Обобщённый показатель — количество значений по полю «Объём». Метод агрегирования выбираем вручную.

Фильтрация по полю «Тикер» позволит нам выбрать данные только по одной ценной бумаге.

Рис. 6.5. Поля сводной таблицы

Задание. Создайте сводную таблицу (рис. 6.5).

Устанавливаем фильтрацию по выбранному значению тикера (рис. 6.6).

Теперь мы обрабатываем данные только по одной акции.

Рис. 6.6. Фильтр по одной бумаге

Задание. Установите фильтрацию по одной бумаге.

Щёлкаем по заголовку любой строки и вызываем группировку в контекстном меню:

Group (рис. 6.7).

Рис. 6.7. Вызов группировки данных

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

Нам остаётся вручную ввести величину интервала группировки. Введём округлённое значение (рис. 6.8).

Нажимаем ОК.

Рис. 6.8. Параметры группировки данных

Задание. Установите параметры группировки данных.

Рассмотрим результаты группировки (рис. 6.9).

Длинные заголовки пока не будем трогать. При желании можно перевести числа в миллионы вместо единиц.

В графе Count выводится количество заполненных ячеек. Общее количество равно 253, как и было в исходных данных.

Рис. 6.9. Результаты группировки данных

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

Щёлкаем правой кнопкой по ячейке в графе «Количество» и выбираем в контекстном меню вывод в процентах от общего итога:

Show Values As — %of Grand Total.

Рис. 6.10. Относительные частоты

Задание. Настройте вывод частоты в процентах.

Зададим уникальное, краткое и информативное название нашей сводной таблице:

PivotTable Tools — Analyze — PivotTable — PivotTable Name.

Укажем, что это объёмы торгов акциями Газпрома (рис. 6.11):

GAZP_VOL.

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

Задание. Присвойте короткое информативное название сводной таблице.

Рассмотрим список именованных объектов (рис. 6.12):

Formulas — Defined Names — Name Manager.

Как ни странно, ни одной сводной таблицы в этом списке нет. Здесь есть только «обычные» таблицы Excel, которые были отформатированы как таблицы.

Зато здесь можно пройтись по обычным таблицам и увидеть их названия. Неплохо будет навести порядок с названиями — хотя бы для них.

Рис. 6.12. Названия объектов

Задание. Просмотрите список названий объектов (таблиц). Присвойте таблицам Excel простые, короткие, понятные названия. Обратите внимание на отсутствие сводных таблиц в этом списке.

Мы провели группировку данных. Теперь по результатам группировки построим гистограмму (рис. 6.13).

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

Insert — Charts — Insert Column or Bar Chart — 2-D Column — Clustered Column.

Рис. 6.13. Вставка гистограммы

Щёлкаем правой кнопкой по пустому прямоугольнику будущего графика и выбираем в контекстном меню:

Select Data.

Появляется диалоговое окно

Select Data Source.

В разделе

Legend Entries (Series)

нажимаем кнопку

Add (рис. 6.14).

Рис. 6.14. Добавление данных для графика

Выбираем столбец относительных частот в диалоговом окне

Edit Series (рис. 6.15).

Итоговое значение 100% нам не требуется.

Заголовок столбца тоже не выделяем.

Рис. 6.15. Выбор относительных частот

После выбора частот график начинает напоминать гистограмму. Пока что это столбиковая диаграмма с настройками по умолчанию.

Обратим внимание на строку

Series Values (рис. 6.15).

Здесь указано название страницы и выбранный диапазон адресов ячеек.

Нажимаем ОК.

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

Рассмотрим полученный график.

Высота столбиков указана в процентах. Это хорошо.

А вот подписи под столбцами — это просто порядковые номера наших столбиков.

Хотелось бы что-то более осмысленное.

В окне

Select Data Source

в разделе

Horizontal (Category) Axis Labels

нажимаем кнопку

Edit (рис. 6.16).

Рис. 6.16. Настройка меток по горизонтальной оси

В диалоговом окне

Axis Labels

Выбираем

Axis label range (рис. 6.17).

Нажимаем ОК и ещё раз ОК.

Рис. 6.17. Выбор меток по горизонтальной оси

Задание. Выберите метки по горизонтальной оси.

Щёлкаем по нашему графику.

Теперь можно увидеть выделение диапазонов ячеек для диаграммы (рис. 6.18).

Рис. 6.18. Диапазоны данных для диаграммы

Украсим наш график заголовками (рис. 6.19).

Щёлкнем по полю графику и нажмём кнопку

[+] Chart Elements

справа от диаграммы.

Выбираем следующие элементы диаграммы:

— Axis Titles;

— Chart Title.

Настраиваем заголовки.

Рис. 6.19. Заголовки на графике

Задание. Настройте заголовки для диаграммы в целом и по каждой оси.

Теперь займёмся настройкой оформления графика.

Щёлкнем по графику, чтобы вызвать меню

Формат области диаграммы.

Настроим изображение столбиков (рис. 6.20).

Щёлкаем по столбикам, выбираем настройки в меню.

Столбики без заливки:

Format Data Series — Series Options — Fill & Line — Fill — No fill.

Линии должны быть чёрными:

Border — Solid line — Color — Black.

Width — 1.5 pt.

Столбики примыкают друг к другу без просветов между ними:

Series Options — Gap Width — 0%.

Напомним, что это требование международных и российских стандартов: столбики гистограммы должны вплотную примыкать друг к другу.

По вертикальной оси оставим только целые значения процентов:

Format Axis — Axis Options — Number — Decimal places — 0.

Рис. 6.20. Гистограмма после настройки

Задание. Настройте оформление гистограммы.

Рассмотрим полученный график (рис. 6.20). Оформление в порядке.

Но столбики слишком широкие. Можно видеть, что распределение несимметричное, скошенное влево. Хотелось бы увидеть побольше подробностей.

Попробуем уменьшить размер интервала группировки. Для начла уменьшим в два раза (рис. 6.21).

Чтобы перестроить график, щёлкаем по диаграмме и визуально настраиваем диапазон исходных данных для графика. Перетаскиваем мышкой выделенные границы диапазонов ячеек.

Оформление гистограммы сохранилось, и это радует.

Рис. 6.21. Уменьшаем интервал группировки

Задание. Уменьшите интервал группировки, чтобы рассмотреть больше подробностей на графике.

Метки по горизонтальной оси получились слишком длинные.

Посмотрим, что можно сделать.

Диапазон значений объёмов по Газпрому

3.846.150 — 221.190.060.

Ширина интервала группировки:

12.000.000.

Начнём интервалы от нуля и округлим ширину интервала до 10 миллионов

0 — 230.000.000.

Цифры должны быть красивые и круглые (рис. 6.22).

Рис. 6.22. Круглые числа

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

Неплохо было бы поделить все цифры на 10 миллионов. Получились бы числа покороче и попроще:

0 — 10;

10 — 20;

…

220 — 230.

Как это сделать — творческое задание.

Задание. Разработайте способ получить короткие метки по горизонтальной оси.

Мы построили гистограмму для одной акции.

Таких акций в нашей объединённой таблице ещё четыре inerb штуки осталось.

Оставшиеся данные тоже нужно обработать.

Задание. Постройте описанным способом гистограммы объёмов торгов для оставшихся бумаг.

6.2. Анализ взаимосвязи

Мы построили гистограмму — оценку формы распределения. Это одна сторона случайности.

Если рассматривать несколько случайных величин одновременно, можно обнаружить ещё одну закономерность. Курсы некоторых акций меняются в общем направлении. Особенно это часто наблюдается в пределах одной отрасли экономики. Например, падает «вся нефтянка» или растут «все металлы».

Мы попробуем увидеть, насколько «связаны» между собой курсы разных бумаг. Для этого мы будем использовать сводные графики в виде ДИАГРАММЫ РАЗБРОСА. Дополнительно, мы будем усреднять значения курсов за месяц.

На диаграмме разброса можно наглядно представить взаимосвязь в форме КОРРЕЛЯЦИИ. Можно очень приближённо сказать, что корреляция — это такая форма взаимосвязи между случайными величинами, когда на графике мы видим «полосу», или «трубку», или «канал». Точки разбросаны вокруг некоторой прямой или кривой линии. Конечно, самой линии на графике нет. А вот ощущение связи есть. Это зависимость «в среднем».

Такой график ещё называют КОРРЕЛЯЦИОННОЕ ПОЛЕ. Потому что на нём можно увидеть эту самую корреляцию.

Задание. Прочитайте в Википедии статью «Корреляция» и выясните, какое определение даётся термину «корреляция» и где применяется корреляционный анализ.

Начинаем со вставки сводного графика.

Щёлкнем по объединённой таблице и выберем в верхнем меню вставку сводного графика и сводной таблицы (рис. 6.23):

Insert — Charts — PivotChart — PivotChart & PivotTable.

Почитаем всплывающую подсказку.

Здесь нам дают такое пояснение:

…graphically summarize data…

Это графическое представление данных в обобщённом виде.

По сути, сводный график — это график, построенный по данным из сводной таблицы.

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

Рис. 6.23. Подсказка для сводного графика

Задание. Прочитайте всплывающую подсказку для сводного графика.

На экране появляются области вывода сводной таблицы и сводного графика (рис. 6.24).

В правой части экрана есть область управления полями сводного графика:

PivotChart Fields.

Кроме того, в верхнем меню появляется раздел настройки сводного графика:

PivotChart Tools — Analyze / Design / Format.

Рис. 6.24. Вставка сводного графика

Задание. Найдите на экране все перечисленные выше элементы и посмотрите, какие инструменты предлагают нам разделы Analyze, Design и Format.

Займёмся настройкой полей для сводного графика.

Перетаскиваем выбранные поля в соответствующие окна (рис. 6.25).

— Axis (Categories) — Date;

— Legend (Series) — TICKER;

— Values — CLOSE.

По умолчанию выбирается сумма как метод агрегирования значений.

Для анализа взаимосвязей возьмём цены закрытия CLOSE. Специалисты по биржевой торговле часто подчёркивают, что цены закрытия важнее остальных значений цен: не важно, как проходила торговля, важно, чем она закончилась.

Нас интересует средний объём. Настраиваем вычисление среднего значения по полю CLOSE:

Value Field Settings — Summarize value field by — Average.

Тип графика — столбиковая диаграмма — выбирается по умолчанию.

Рис. 6.25. Настройка полей

Задание. Настройте поля сводного графика (рис. 6.25).

Настройка полей сводного графика очень похожа на выбор исходных данных для обычной диаграммы Excel — такие же названия для данных по оси «иксов» и по оси «игреков».

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

Теперь в правой части окна Excel выводится область настройки полей сводной таблицы (рис. 6.26)

PivotTable Fields:

— Rows — Date;

— Columns — TICKER;

— Values — Average of CLOSE.

Рис. 6.26. Поля сводной таблицы

Задание. Изучите, чем различаются меню настройки полей сводного графика и сводной таблицы.

Попробуем изменить тип сводного графика на диаграмму разброса:

Change Chart Type — X Y (Scatter).

В ответ мы получаем любопытное сообщение (рис. 6.27).

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

Рис. 6.27. Тип диаграммы не поддерживается

Задание. Попробуйте изменить тип графика на диаграмму разброса и прочитайте сообщение.

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

Задание. Проверьте все пункты списка типов диаграмм Change Chart Type и выясните, какие диаграммы можно построить, а какие нельзя.

Итак, сводный график нам не помог. Щёлкаем по графику и удаляем его, нажав клавишу Del.

Сводная таблица осталась на месте. Вот с ней мы и будем дальше работать.

Задание. Удалите сводный график и обратите внимание на оставшуюся сводную таблицу.

Чтобы работать со сводной таблицей было поприятнее, настроим оформление (рис. 6.28).

Сделаем понятные заголовки:

— Средняя цена закрытия;

— Год;

— Тикер;

— Всего.

Зададим формат вывода целыми числами — то есть без дробной части. Обратим внимание, что это только форма вывода значений на экран. Сами значения не округляются. Это видно в строке формул.

Рис. 6.28. Оформление таблицы

Задание. Настройте заголовки и формат вывода.

Теперь попробуем построить диаграмму разброса «народными средствами» — как обычный график.

Развернём детализацию до уровня кварталов.

Щёлкаем по любой ячейке за пределами сводной таблицы.

Выбираем в верхнем меню вставку диаграммы разброса (рис. 6.29):

Insert — Charts — Insert Scatter… — Scatter — Scatter.

Чтобы всё получше запутать, в русской версии программы такой график называется ТОЧЕЧНАЯ ДИАГРАММА. То есть график в виде точек. В таком названии теряется смысл, предназначение. Становится непонятно, зачем он нужен. Когда мы говорим про диаграмму разброса, это намекает на разброс вокруг будущей линии. Когда мы говорим про корреляционное поле, мы имеем в виду возможность показать корреляцию, взаимосвязь между случайными величинами.

Перед тем, скорее-скорее нажать нужную кнопку, всё-таки прочитаем очередную всплывающую подсказку. Читать полезно. Можно узнать что-нибудь новое. И стать в большей степени специалистом, профессионалом. Итак, читаем. Нам намекают, что с помощью диаграммы разброса можно наглядно показать взаимосвязи:

Show relationships…

Рис. 6.29. Вставка диаграммы разброса

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

Теперь выберем данные для графика.

Щёлкаем правой кнопкой по полю графика и в контекстном меню выбираем следующий пункт:

Select Data.

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

Select Data Source.

Затем выбираем диапазон ячеек с данными:

Legend Entries (Series) — Add.

Появляется новое диалоговое окно

Edit Series.

Судя по названию, нам предлагают изменить (отредактировать) ряд или набор данных. Слово Series означает «ряд данных». На самом деле, мы просто выбираем данные для графика.

Назовём наши данные в строке «Название ряда»:

Series Name — GMKN vs GAZP.

Мы сообщаем будущему читателю, а заодно и самим себе (чтобы случайно не забыть), что будем рассматривать поведение курса акций Норильского никеля в сравнении с курсом Газпрома. Загадочные буквы vs — это сокращение от слова versus, которое означает «против» или «одно по сравнению с другим». То есть это либо противопоставление, либо сравнение.

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

По оси «иксов» у нас будет курс акций Газпрома.

Series X Values — диапазон значений в колонке GAZP (без общего итога).

По оси «игреков» выбираем курса акций Норникеля:

Series Y Values — колонка GMKN (тоже без графы «Всего»).

Рис. 6.30. Данные для графика

Задание. Выберите данные для графика.

Настроим заголовки по осям (рис. 6.31). Добавляем элементы диаграммы:

Chart Elements — Axis Titles.

Теперь мы можем указать, что по какой оси отложено и что у нас тут показано в целом.

То, что у нас нарисована диаграмма разброса — это и так понятно. А вот какой именно параметр по Газпрому и Норникелю использован — непонятно. Лучше про это сказать открытым текстом.

Рис. 6.31. Заголовки по осям

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

Рассмотрим полученный график (рис. 6.31). Здесь всего пять точек. Этого маловато для анализа — мало что можно из такого графика узнать.

Раскроем детализацию до месяцев.

Щёлкаем правой кнопкой мыши по заголовку любой строки.

В контекстном меню выбираем настройку группировки данных:

Group.

Снимаем выбор с квартальных данных и оставляем годы и месяцы (рис. 6.32).

Нажимаем ОК.

Однако, график не слишком изменился. Вернее. Совсем не изменился.

Рис. 6.32. Изменение параметров группировки

Задание. Оставьте группировку по годам и месяцам. Обратите внимание на изменения в графике.

Поработаем с данными для графика.

Щёлкнем по любому маркеру наших данных на графике — по любой точке.

В сводной таблице выделяются диапазоны ячеек, которые использованы в графике. Можно видеть, что теперь нашим вниманием охвачены не все ячейки (рис. 6.33).

Рис. 6.33. Диапазон данных для графика

Задание. Рассмотрите выделенный диапазон данных для графика.

Исправим это упущение. Растянем выделение диапазона за уголок на все интересующие нас ячейки (рис. 6.34).

Рис. 6.34. Полный диапазон данных

Графа «Всего» в нижней строке и крайней правой колонке нам ничего полезного не сообщает. Удалим её.

Щёлкаем правой кнопкой мыши по заголовку строки «Всего» и выбираем в контекстном меню удаление общего итога (рис. 6.35):

Remove Grand Total.

Аналогично поступим с итоговой колонкой.

Рис. 6.35. Удаление итогов

Задание. Удалите итоговые строку и столбец в сводной таблице.

Таблица стала более компактной.

Щёлкаем по графику и видим выделение наших данных (рис. 6.36).

Теперь вроде всё хорошо.

Но как говорят Сибирские сказители: «Хорошо — хорошо, да не очень-то».

Рис. 6.36. Сводная таблица без итоговых показателей

Задание. Рассмотрите выделенный диапазон данных и выясните, что необходимо исправить.

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

Щёлкаем правой кнопкой по заголовку строки «Годы» и снимаем в контекстном меню выбор промежуточных итогов (рис. 6.37):

Subtotal «Years».

Рис. 6.37. Удаление промежуточных итогов

Задание. Удалите промежуточные годовые итоги.

Рассмотрим график (рис. 6.37) ещё раз.

Наши данные должны занимать всё поле графика.

Точки не должны ютиться в дальнем углу.

Ценность графика в том, чтобы увидеть картину в целом, распознать закономерность — если она, конечно, есть.

Настроим масштаб по осям.

Щёлкаем по оси.

Появляется боковое меню настройки параметров оси:

Format Axis — Axis Options — Axis Options — Bounds — Minimum / Maximum.

Выбираем пределы изменения значений по осям так, чтобы наши точки занимали весь график (рис. 6.38).

Рис. 6.38. Масштаб по осям

Задание. Настройте масштаб по осям.

Рассмотрим полученную диаграмму разброса (рис. 6.38).

Точки разбросаны широко. Небольшая корреляция, конечно присутствует.

Здесь может быть ещё кое-что. Добавим соединение точек прямыми отрезками.

Щёлкаем по диаграмме правой кнопкой и выбираем в контекстном меню изменение типа диаграммы (рис. 6.39):

Change Chart Type — X Y (Scatter) — Scatter with Straight Lines and Markers.

Рис. 6.39. Выбор типа диаграммы

Задание. Настройте тип графика.

Второй вариант — настройка графика. Щёлкаем по любому маркеру и переходим в раздел

Format Data Series — Fill & Line — Line — Solid line.

Теперь можно увидеть «траекторию» движения (рис. 6.40).

Рис. 6.40. Диаграмма с прямыми линиями

Задание. Измените тип диаграммы и обратите внимание на возможную закономерность.

Добавим на наш график исходные ежедневные данные.

Щёлкаем по графику правой кнопкой и выбираем в контекстном меню данные для графика:

Select Data.

В диалоговом окне

Select Data Source

в разделе

Legend Entries (Series)

нажимаем кнопку

Add.

Выбираем в загруженной объединённой таблице столбец цен закрытия Газпрома по оси «иксов» и Норникеля по оси «игреков».

Получаем комбинированную диаграмму.

Настраиваем маркеры (серые, небольшого размера) и убираем линии (рис. 6.41):

Format Data Series — Series Options — Fill & Line — Line — No line.

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Built-in — Size — 2.

Fill — Solid fill — Color — Light Gray.

Border — No line.

Можно видеть траекторию, на которой одна бумага отстаёт от изменений второй. В этом конкретном примере «металлы» и «газ» изменяются почти «в противофазе». Это явление обычно учитывают в моделях динамики за счёт введения запаздывания.

Рис. 6.41. Комбинированная диаграмма

Задание. Постройте комбинированную диаграмму.

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

Построим новую сводную таблицу на новом листе.

В этот раз оставим группировку по годам и дням. То есть данные не будут сгруппированы вообще.

Удаляем общие и промежуточные годовые итоги.

Мы получили слегка изменённую объединённую таблицу (рис. 6.42).

Теперь у нас котировки по каждой бумаге расположены в отдельных колонках. Так будет немного легче работать.

Рис. 6.42. Данные по бумагам без группировки

Задание. Постройте сводную таблицу (рис. 6.42).

Построим диаграмму динамики

Insert — Charts — Insert Line or Area Chart — 2D Line — Line.

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

Выбираем данные для графика.

Это будут два разных набора данных их разных колонок.

По оси «иксов» пока ничего не указываем. Здесь отображаются порядковые номера точек.

Поскольку уровни цен разные, настроим раздельные оси для каждого графика.

Format Data Series — Series Options — Series Options — Plot Series On — Secondary Axis.

Задаём масштаб по каждой оси отдельно, чтобы на графике не было пустого места (рис. 6.43).

Горизонтальную ось отключаем:

Primary Horizontal

На таком графике можно видеть развитие событий во времени.

Рис. 6.43. Оформление графика

Задание. Постройте диаграмму динамики.

Мы рассмотрели процедуру анализа для одной пары бумаг.

Теперь нужно проделать описанные шаги для остальных возможных сочетаний. Для начала попробуйте определить, сколько вариантов нам предстоит рассмотреть. СОЧЕТАНИЕ — это понятие из области комбинаторики.

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

В специализированных пакетах программ есть возможность проводить анализ взаимосвязи сразу по списку из нескольких переменных. Результаты такого анализа — «матрица» из нескольких графиков — гистограмм и диаграмм разброса.

Задание. Найдите в Картинках Яндекса изображения на тему correlation matrix plot.

Конечно, мы не будем исследовать все возможные комбинации. Хотя, конечно, было бы интересно. Мы ограничимся тремя дополнительными заданиями.

Задание. Проведите анализ взаимосвязи для трёх сочетаний (по две акции).

Мы прошлись по основным этапам загрузки и обработки данных. Мы прошли по кругу и вернулись к началу.

Функции ETL настраивают в виде сценариев или процедур так, чтобы затем многократно их использовать. Когда в источнике данных происходят изменения, эти новые данные загружаются в систему бизнес-аналитики автоматически. Или по требованию пользователя:

Refresh.

Соответственно, должна обновиться наша объединённая таблица, а вслед за ней — все результаты анализа, таблицы и графики.

Задание. Скачайте данные для выбранных для исследования акций за последние пять лет и поместите файлы в каталог для загрузки. Вызовите обновление запроса на загрузку. Убедитесь в изменении объединённой таблицы и результатов анализа.

Заключение

В этой работе мы познакомились с процедурой извлечения, преобразования и загрузки данных. Английское название: ETL. Это подготовка данных для анализа в системах бизнес-аналитики.

Мы убедились в том, что это долгая и кропотливая работа. Она подходит не всем. Работа с данными, с числами требует терпения, усидчивости и внимательности. А ещё требуется некоторый кругозор. Все источники данных разные. Да ещё в них будут самые разные ошибки и нестыковочки.

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

Попутно мы немного расширили свой кругозор и эрудицию. Немного приблизились к общему пониманию. Ещё это называется цельная или целостная картина мира. Зачем же это нужно? Есть такое любопытное высказывание: знание общих закономерностей может скомпенсировать незнание какого-то отдельного факта.

Задание. Найдите в интернете последнюю фразу и выясните, кто автор этого высказывания.

Литература

1. Паклин Н. Б., Орешков В. И. Бизнес-аналитика — от данных к знаниям: учебное пособие. — СПб.: ПИТЕР, 2013. — 702 с.

2. Винстон У. Л. Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. — СПб.: Питер, 2018. — 864 с.

3. Александер М. Excel 2019. Библия пользователя. Исчерпывающее руководство. — СПб.: Диалектика, 2019. — 1136 с.

4. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2013. — М.: Вильямс, 2017. — 448 с.

5. Барсегян А. А. Анализ данных и процессов: учебное пособие. — СПб.: БХВ, 2019. — 512 с.

6. Арьков В. Ю. Анализ распределения в Excel: Учебное пособие. — [б. м.]: Издательские решения, 2019. — 158 с.

Режим доступа:

https://ridero.ru/books/analiz_raspredeleniya_v_excel/

7. Арьков В. Ю. Статистический анализ взаимосвязи в Excel: Учебное пособие. — [б. м.]: Издательские решения, 2019. — 146 с.

Режим доступа:

https://ridero.ru/books/statisticheskii_analiz_vzaimosvyazi_v_excel/

8. Арьков В. Ю. Анализ рядов динамики в Excel: Учебное пособие — [б. м.]: Издательские решения, 2020. — 130 с.

Режим доступа:

https://ridero.ru/books/analiz_ryadov_dinamiki_v_excel/

9. Арьков В. Ю. Бизнес-аналитика в Excel. Сводные таблицы: Учебное пособие. — [б. м.]: Издательские решения, 2020. — 180 с.

Режим доступа:

https://ridero.ru/books/biznes-analitika_v_excel_svodnye_tablicy/

10. Арьков В. Ю. Сводные таблицы Excel. Часть 2: Учебное пособие. — [б. м.]: Издательские решения, 2020. — 160 с.

Режим доступа:

https://ridero.ru/books/svodnye_tablicy_excel_chast_2/

Content

Бизнес-аналитика. Извлечение, преобразование и загрузка данных
Учебное пособие
Валентин Юльевич Арьков

Введение

1. Инструменты ETL в Microsoft Excel

2. Загрузка таблицы из Википедии

3. Загрузка таблицы дивидендов

4. Задание по загрузке таблицы из интернет

4.1. Загрузка сведений о дивидендах

4.2. Загрузка котировок Yahoo! Finance

5. Извлечение данных из файлов

5.1. Варианты заданий

5.2. Выбор акций

5.3. Загрузка файлов из папки

5.4. Дубликаты данных

5.5. Просмотр запроса

6. Анализ данных

6.1. Анализ распределения объёмов

6.2. Анализ взаимосвязи

Заключение

Литература