Рассмотрены современные методы очистки, анализа и визуализации данных в Microsoft Excel. Описаны инструменты Power Query для создания воспроизводимых процессов подготовки данных, средства Power Pivot для построения реляционных моделей и настройки аналитических показателей. Приведены практические примеры использования динамических массивов, функций на базе искусственного интеллекта и интеграции с языком Python. Показано, как создавать отчеты и аналитические материалы, ранее считавшиеся трудновыполнимыми в Excel. Книга ориентирована на специалистов по данным, бизнес-аналитиков и пользователей Excel, заинтересованных в расширении своих возможностей.
Для аналитиков данных
Электронный архив к книге можно скачать по ссылке https://github.com/stringfestdata/modern-analytics-excel-book
Если вы все еще не модернизировали процессы очистки данных и создания отчетов в Microsoft Excel, вы, возможно, упускаете шанс существенно повысить эффективность своей работы. А если ваша цель — глубокий и детальный анализ данных, стоит знать: возможности Excel гораздо шире, чем принято думать. Это практическое руководство открывает читателю доступ к современному арсеналу функций Excel и демонстрирует другие мощные инструменты аналитики.
Автор показывает бизнес-аналитикам, специалистам по данным и всем, кто работает с цифрами, как извлечь максимум пользы из привычных таблиц, используя новейшие возможности Excel. Вы научитесь создавать воспроизводимые сценарии очистки данных с помощью Power Query и строить реляционные модели прямо внутри рабочей книги, используя Power Pivot. Кроме того, вы познакомитесь с современными инструментами анализа — от динамических массивов и функций на базе искусственного интеллекта до интеграции с языком Python.
Откройте для себя способы создания отчетов и аналитики, которые раньше казались трудновыполнимыми, а порой и вовсе невозможными в Excel.
• Создавайте воспроизводимые и надежные процессы очистки данных в Excel с помощью Power Query
• Проектируйте реляционные модели и настраивайте аналитические показатели, используя Power Pivot
• Быстро извлекайте и трансформируйте данные благодаря функциям динамических массивов
• Применяйте возможности искусственного интеллекта для выявления скрытых закономерностей и трендов
• Интегрируйте Python в работу с Excel для автоматизации анализа и отчётности
Книгу “Современная аналитика данных в Excel: Использование Power Query, Power Pivot и других инструментов для расширенного анализа данных” можно купить в нашем интенет-магазине.
Предисловие…………………………………………………………………………………………… 11
Цель обучения……………………………………………………………………………………………………………………………. 11
Предварительные требования………………………………………………………………………………………………….. 11
Технические требования………………………………………………………………………………………………… 11
Необходимые навыки…………………………………………………………………………………………………….. 12
Как я к этому пришел?………………………………………………………………………………………………………………. 12
Что такое «современная аналитика»? Почему именно Excel?……………………………………………….. 13
Структура книги………………………………………………………………………………………………………………………… 14
Упражнения в конце глав………………………………………………………………………………………………………….. 14
Отбор тем……………………………………………………………………………………………………………………………………. 15
Условные обозначения……………………………………………………………………………………………………………… 15
Использование примеров кода…………………………………………………………………………………………………. 16
Контакты…………………………………………………………………………………………………………………………………….. 16
Благодарности…………………………………………………………………………………………………………………………… 16
Часть I. Очистка и преобразование данных
в Power Query………………………………………………………………………………….. 19
Глава 1. Таблицы — проводники в современный Excel…………………………. 21
Создание заголовков таблицы и ссылки на них……………………………………………………………………… 21
Добавление строки итогов к таблице………………………………………………………………………………………. 23
Именование таблиц Excel………………………………………………………………………………………………………….. 25
Форматирование таблиц Excel…………………………………………………………………………………………………. 25
Изменение диапазона таблицы………………………………………………………………………………………………… 26
Упорядочивание данных для анализа…………………………………………………………………………………….. 26
Заключение………………………………………………………………………………………………………………………………… 27
Упражнения………………………………………………………………………………………………………………………………… 28
Глава 2. Первые шаги в Power Query…………………………………………………….. 29
Что такое Power Query?……………………………………………………………………………………………………………… 29
Power Query как «разрушитель мифов» об Excel……………………………………………………………………… 29
«Excel не воспроизводит результаты»………………………………………………………………………….. 29
«В Excel нет настоящего null»………………………………………………………………………………………… 30
«Excel не может обработать более 1 048 576 строк»………………………………………………….. 31
Power Query как инструмент ETL в Excel…………………………………………………………………………………. 31
Extract (Извлечение)……………………………………………………………………………………………………….. 31
Transform (Преобразование)…………………………………………………………………………………………. 33
Load (Загрузка)……………………………………………………………………………………………………………….. 33
Обзор редактора Power Query…………………………………………………………………………………………………… 34
Лента……………………………………………………………………………………………………………………………….. 34
Запросы…………………………………………………………………………………………………………………………… 36
Импортированные данные…………………………………………………………………………………………….. 37
Выход из редактора Power Query………………………………………………………………………………….. 40
Возвращение в редактор Power Query…………………………………………………………………………… 41
Профилирование данных в Power Query………………………………………………………………………………….. 41
Что такое профилирование данных?……………………………………………………………………………. 42
Опции предварительного просмотра данных……………………………………………………………… 42
Monospaced и Show whitespace……………………………………………………………………………… 42
Column quality и Column distribution…………………………………………………………………….. 43
Что такое «допустимое» значение?…………………………………………………………………. 43
Отсутствующие значения………………………………………………………………………………… 43
Ошибки в ячейках……………………………………………………………………………………………… 44
Column profile (Профиль столбца)………………………………………………………………………… 45
Как убрать ограничение на тысячу строк?………………………………………………………………….. 46
Окончание профилирования данных……………………………………………………………………………. 47
Заключение………………………………………………………………………………………………………………………………… 47
Упражнения………………………………………………………………………………………………………………………………… 47
Глава 3. Преобразование строк в Power Query……………………………………….. 48
Удаление пропущенных значений…………………………………………………………………………………………… 48
Обновление запроса………………………………………………………………………………………………………………….. 50
Разделение данных на строки…………………………………………………………………………………………………… 52
Заполнение заголовков и пустых ячеек…………………………………………………………………………………… 55
Замена заголовков столбцов…………………………………………………………………………………………. 55
Заполнение пропущенных значений…………………………………………………………………………….. 55
Заключение………………………………………………………………………………………………………………………………… 56
Упражнения………………………………………………………………………………………………………………………………… 56
Глава 4. Преобразование столбцов в Power Query………………………………….. 57
Изменение регистра столбца…………………………………………………………………………………………………….. 57
Разделение на столбцы……………………………………………………………………………………………………………… 58
Изменение типов данных…………………………………………………………………………………………………………… 59
Удаление столбцов……………………………………………………………………………………………………………………. 59
Работа с датами…………………………………………………………………………………………………………………………. 60
Создание пользовательских столбцов…………………………………………………………………………………….. 61
Загрузка и проверка данных…………………………………………………………………………………………. 62
Вычисляемые столбцы и собственные расчеты………………………………………………………….. 62
Изменение структуры данных………………………………………………………………………………………………….. 63
Заключение………………………………………………………………………………………………………………………………… 65
Упражнения………………………………………………………………………………………………………………………………… 65
Глава 5. Объединение и добавление данных в Power Query…………………… 66
Добавление нескольких источников………………………………………………………………………………………… 66
Подключение к внешним рабочим книгам Excel………………………………………………………….. 66
Добавление запросов……………………………………………………………………………………………………… 69
Реляционные соединения………………………………………………………………………………………………………….. 70
Левое внешнее соединение: почти то же, что и VLOOKUP()………………………………………. 72
Внутреннее соединение: только точное соответствие………………………………………………… 75
Управление вашими запросами……………………………………………………………………………………………….. 76
Группировка запросов…………………………………………………………………………………………………… 76
Просмотр зависимостей запросов…………………………………………………………………………………. 77
Заключение………………………………………………………………………………………………………………………………… 78
Упражнения………………………………………………………………………………………………………………………………… 79
Часть II. Моделирование и анализ данных
с помощью Power Pivot……………………………………………………………… 81
Глава 6. Знакомство с Power Pivot………………………………………………………….. 83
Что такое Power Pivot?……………………………………………………………………………………………………………….. 83
Зачем нужен Power Pivot?………………………………………………………………………………………………………….. 83
Power Pivot и модель данных…………………………………………………………………………………………………….. 86
Подключение надстройки Power Pivot……………………………………………………………………………………… 87
Краткий обзор надстройки Power Pivot……………………………………………………………………………………. 88
Заключение………………………………………………………………………………………………………………………………… 89
Упражнения………………………………………………………………………………………………………………………………… 90
Глава 7. Создание реляционной модели данных в Power Pivot………………. 91
Подключение данных к Power Pivot…………………………………………………………………………………………. 91
Создание взаимосвязей между таблицами………………………………………………………………………………. 92
Таблицы фактов и таблицы измерений……………………………………………………………………………………. 95
Упорядочивание диаграммы…………………………………………………………………………………………. 95
Редактирование связей…………………………………………………………………………………………………… 96
Загрузка результатов в Excel……………………………………………………………………………………………………. 97
Понятие кардинальности………………………………………………………………………………………………………… 100
Связь «один к одному»…………………………………………………………………………………………………. 100
Связь «один ко многим»……………………………………………………………………………………………….. 101
Связь «многие ко многим»……………………………………………………………………………………………. 102
Почему так важна кардинальность?…………………………………………………………………………… 102
Направление фильтрации……………………………………………………………………………………………………….. 103
Фильтрация orders через users…………………………………………………………………………………….. 104
Фильтрация users через orders…………………………………………………………………………………….. 105
Направление фильтрации и кардинальность…………………………………………………………….. 105
От теории к практике……………………………………………………………………………………………………. 106
Создание вычисляемых столбцов в Power Pivot……………………………………………………………………. 106
Вычисления в Power Query или в Power Pivot?……………………………………………………………. 106
Пример: расчет нормы прибыли…………………………………………………………………………………. 107
Замена значений в столбце с помощью SWITCH()…………………………………………………….. 108
Создание иерархий и работа с ними……………………………………………………………………………………… 110
Создание иерархии в Power Pivot………………………………………………………………………………… 110
Использование иерархии в сводной таблице…………………………………………………………….. 111
Загрузка модели данных в Power BI………………………………………………………………………………………. 112
Power BI как третий инструмент «современного Excel»……………………………………………. 112
Импорт модели данных в Power BI……………………………………………………………………………… 113
Просмотр данных в Power BI………………………………………………………………………………………. 115
Заключение………………………………………………………………………………………………………………………………. 116
Упражнения……………………………………………………………………………………………………………………………… 116
Глава 8. Создание мер DAX и показателей KPI в Power Pivot………………. 118
Создание мер DAX…………………………………………………………………………………………………………………… 118
Создание неявных мер…………………………………………………………………………………………………. 118
Создание явных мер……………………………………………………………………………………………………… 120
Создание показателей KPI……………………………………………………………………………………………………… 124
Настройка стилей значков…………………………………………………………………………………………… 127
Добавление показателя KPI в сводную таблицу……………………………………………………….. 127
Заключение………………………………………………………………………………………………………………………………. 128
Упражнения……………………………………………………………………………………………………………………………… 128
Глава 9. Функции DAX в Power Pivot…………………………………………………… 130
Функция CALCULATE()……………………………………………………………………………………………………………. 130
Контекст фильтра…………………………………………………………………………………………………………. 130
Функция CALCULATE() с одним условием…………………………………………………………………. 131
Функция CALCULATE() с несколькими условиями…………………………………………………….. 132
Условие И……………………………………………………………………………………………………………… 132
Условие ИЛИ………………………………………………………………………………………………………… 132
Функция CALCULATE() с условием ALL()…………………………………………………………………… 132
Функции аналитики времени………………………………………………………………………………………………….. 135
Добавление таблицы дат…………………………………………………………………………………………….. 136
Создание базовых мер для аналитики времени…………………………………………………………. 137
Заключение………………………………………………………………………………………………………………………………. 141
Упражнения……………………………………………………………………………………………………………………………… 141
Часть III. Инструменты аналитики в Excel……………………….. 143
Глава 10. Введение в функции динамических массивов………………………. 145
Функции динамических массивов………………………………………………………………………………………….. 145
Что такое массив в Excel?…………………………………………………………………………………………….. 145
Ссылки на массивы………………………………………………………………………………………………………. 146
Ссылки на статические массивы………………………………………………………………………… 146
Ссылки на динамические массивы……………………………………………………………………… 147
Формулы массива…………………………………………………………………………………………………………. 147
Формулы статического массива…………………………………………………………………………. 147
Функции динамического массива……………………………………………………………………….. 149
Использование функций динамического массива………………………………………………………………… 149
Поиск уникальных и неповторяющихся значений
с помощью функции UNIQUE()……………………………………………………………………………………. 150
Разница между уникальными и отличающимися значениями…………………………………. 150
Использование оператора динамического диапазона……………………………………………… 151
Фильтрация записей с помощью функции FILTER()……………………………………………………………… 152
Добавление заголовков столбцов……………………………………………………………………………….. 153
Фильтрация по нескольким условиям…………………………………………………………………………. 154
Условие И……………………………………………………………………………………………………………… 154
Условие ИЛИ………………………………………………………………………………………………………… 154
Вложенные условия И/ИЛИ………………………………………………………………………………… 154
Сортировка с помощью функции SORTBY()…………………………………………………………………………… 154
Сортировка по нескольким диапазонам…………………………………………………………………….. 155
Сортировка без включения столбца сортировки в результат………………………………….. 156
Современный поиск с помощью функции XLOOKUP()…………………………………………………………. 156
Сравнение функций XLOOKUP() и VLOOKUP()…………………………………………………………. 157
Базовые возможности функции XLOOKUP()……………………………………………………………… 158
Обработка ошибок с помощью функции XLOOKUP()………………………………………………. 158
Функция XLOOKUP() и столбцы слева………………………………………………………………………. 159
Другие функции динамического массива………………………………………………………………………………. 159
Динамические массивы и современный Excel……………………………………………………………………….. 160
Заключение………………………………………………………………………………………………………………………………. 161
Упражнения……………………………………………………………………………………………………………………………… 161
Глава 11. Дополненная аналитика и будущее Excel……………………………… 162
Растущая сложность данных и аналитики……………………………………………………………………………. 162
Excel и self-service BI-системы………………………………………………………………………………………………… 163
Excel для дополненной аналитики…………………………………………………………………………………………. 164
Использование Analyze Data для получения результатов, сгенерированных ИИ…………….. 164
Построение статистических моделей с помощью XLMiner…………………………………………………. 168
Чтение данных с изображения……………………………………………………………………………………………….. 171
Анализ настроений с помощью Azure Machine Learning……………………………………………………… 173
Заключение………………………………………………………………………………………………………………………………. 177
Упражнения……………………………………………………………………………………………………………………………… 177
Глава 12. Python и Excel……………………………………………………………………….. 178
Предварительные требования………………………………………………………………………………………………… 178
Роль Python в современном Excel…………………………………………………………………………………………… 179
«Клей» для огромного набора инструментов…………………………………………………………….. 179
Сетевой эффект сокращает время разработки…………………………………………………………… 180
Добавьте современные технологии к Excel………………………………………………………………… 180
Модульное тестирование……………………………………………………………………………………. 180
Системы контроля версий……………………………………………………………………………………. 181
Разработка пакетов и их распространение……………………………………………………….. 181
Совмещение Python и Excel с помощью пакетов pandas и openpyxl………………………… 182
Зачем нужен pandas для работы с Excel?………………………………………………………….. 182
Ограничения при работе с pandas………………………………………………………………………. 182
Что умеет openpyxl?…………………………………………………………………………………………….. 182
Использование openpyxl вместе с pandas…………………………………………………………… 183
Другие пакеты Python для Excel………………………………………………………………………………….. 183
Пример автоматизации Excel с помощью pandas и openpyxl………………………………………………. 184
Очистка данных с помощью pandas…………………………………………………………………………… 185
Работа с метаданными………………………………………………………………………………………… 186
Поиск по шаблону и регулярные выражения…………………………………………………….. 186
Обработка отсутствующих значений………………………………………………………………… 187
Процентильное ранжирование……………………………………………………………………………. 188
Создание отчета с помощью openpyxl……………………………………………………………………….. 189
Создание рабочего листа для отчета…………………………………………………………………. 189
Вставка диаграмм………………………………………………………………………………………………… 190
Способ 1: создание диаграммы Excel…………………………………………………………… 190
Способ 2: вставка изображения из Python…………………………………………………… 191
Диаграммы Excel и Python…………………………………………………………………………………… 193
Добавление стилизованной таблицы…………………………………………………………………………. 194
Изменение формата на проценты……………………………………………………………………….. 194
Преобразование в таблицу Excel………………………………………………………………………… 194
Применение условного форматирования…………………………………………………………… 195
Автоподбор ширины столбцов……………………………………………………………………………. 195
Заключение………………………………………………………………………………………………………………………………. 196
Упражнения……………………………………………………………………………………………………………………………… 196
Глава 13. Заключение и дальнейшие шаги…………………………………………… 197
Другие функциональности Excel……………………………………………………………………………………………. 197
Функции LET() и LAMBDA()…………………………………………………………………………………………. 197
Power Automate, сценарии Office и Excel Online………………………………………………………… 198
Дальнейшее изучение Power Query и Power Pivot………………………………………………………………….. 199
Power Query и M……………………………………………………………………………………………………………. 199
Power Pivot и DAX…………………………………………………………………………………………………………. 200
Power BI для информационных панелей и отчетов…………………………………………………… 201
Azure и облачные вычисления………………………………………………………………………………………………… 201
Программирование на Python………………………………………………………………………………………………… 202
Большие языковые модели и инженерия запросов……………………………………………………………….. 202
Напутствие……………………………………………………………………………………………………………………………….. 203
Предметный указатель…………………………………………………………………………. 204
Об авторе……………………………………………………………………………………………… 206
Об изображении на обложке………………………………………………………………… 207
