Книга посвящена практическому использованию Microsoft Excel для решения широкого спектра задач. Показано создание информативных диаграмм и графиков-спарклайнов, работа с 3D-картами, использование сводных таблиц для сегментации, фрагментации и обобщения данных, проведение статистического и финансового анализа с помощью формул, листов прогноза и пакета анализа. Рассмотрены функции динамических массивов, демонстрируются методы импорта и обработки данных с помощью Power Query, а также создание пользовательских функций с использованием лямбда-формул и возможностей VBA для автоматизации работы. Книга содержит более 350 готовых решений по различным темам, сочетает традиционные и современные подходы, помогая экономить время, повышать продуктивность и эффективно применять Excel в профессиональной деятельности.
Для пользователей Excel любого уровня
Электронный архив к книге можно скачать по ссылке https://github.com/stringfestdata/modern-analytics-excel-book
Эта книга, наполненная практическими советами, приемами и объяснениями проверенных методов работы, станет вашим незаменимым помощником в изучении Microsoft Excel. В ней собрано более 350 готовых решений по различным темам — от формул и сводных таблиц до диаграмм, Power Query и рекомендаций по использованию других полезных инструментов. Каждый раздел начинается с конкретной задачи и сопровождается подробным описанием способа её решения, который вы можете применить сразу, не выискивая ответ в других источниках.
Неважно, кто вы — аналитик данных, руководитель проекта, финансовый директор или начинающий пользователь Excel — автор поможет вам отыскать правильное решение ваших задач. Книга идеально подходит как в качестве справочного пособия, так и для обучения более эффективной работе, она помогает сэкономить время и заметно повысить продуктивность. С ней вы сможете быстро получить ответы на любые вопросы, возникающие в ходе работы с Excel.
Откройте для себя способы создания отчетов и аналитики, которые раньше казались трудновыполнимыми, а порой и вовсе невозможными в Excel.
• Создавайте воспроизводимые и надежные процессы очистки данных в Excel с помощью Power Query
• Проектируйте реляционные модели и настраивайте аналитические показатели, используя Power Pivot
• Быстро извлекайте и трансформируйте данные благодаря функциям динамических массивов
• Применяйте возможности искусственного интеллекта для выявления скрытых закономерностей и трендов
• Интегрируйте Python в работу с Excel для автоматизации анализа и отчётности
Дон Гриффитс — автор и преподаватель с более чем 20-летним опытом разработки программного обеспечения для настольных компьютеров и Интернета.
Вместе Дэвид и Дон написали несколько книг, включая Head First Android Development и Head First Kotlin. Также они проводят он-лайн консультации для компании O’Reilly.
Книгу “Excel. Сборник рецептов” можно купить в нашем интенет-магазине.
Предисловие…………………………………………………………………………………………… 15
Условные обозначения……………………………………………………………………………………………………………… 15
Использование примеров кода…………………………………………………………………………………………………. 16
Платформа онлайн-обучения O’Reilly……………………………………………………………………………………… 17
Как с нами связаться?………………………………………………………………………………………………………………… 17
Благодарности…………………………………………………………………………………………………………………………… 18
Глава 1. Рабочие книги, рабочие листы и ячейки………………………………….. 19
1.1. Применяем темы………………………………………………………………………………………………………………….. 19
1.2. Применяем стили ячеек………………………………………………………………………………………………………. 20
1.3. Форматируем ячейки………………………………………………………………………………………………………….. 21
1.4. Форматируем значение ячейки………………………………………………………………………………………….. 22
1.5. Задаем пользовательский числовой формат……………………………………………………………………. 24
1.6. Объединяем ячейки……………………………………………………………………………………………………………… 28
1.7. Создаем шаблоны……………………………………………………………………………………………………………….. 29
1.8. Защищаем файлы, рабочие книги, рабочие листы и ячейки Excel…………………………………. 31
1.9. Используем условное форматирование……………………………………………………………………………. 32
1.10. Применяем формат по образцу………………………………………………………………………………………… 34
1.11. Используем специальную вставку…………………………………………………………………………………… 35
1.12. Используем автозаполнение……………………………………………………………………………………………. 36
1.13. Используем пользовательские списки…………………………………………………………………………….. 38
1.14. Используем мгновенное заполнение……………………………………………………………………………….. 40
1.15. Настраиваем автозамену…………………………………………………………………………………………………. 42
1.16. Используем заметки и примечания………………………………………………………………………………….. 43
1.17. Находим и выделяем ячейки и перемещаемся по ним…………………………………………………… 45
1.18. Создаем представление……………………………………………………………………………………………………. 47
1.19. Настраиваем ленту и ее вкладки…………………………………………………………………………………….. 48
1.20. Используем панель быстрого доступа……………………………………………………………………………. 49
1.21. Проверяем читаемость……………………………………………………………………………………………………… 50
Глава 2. Ссылки и структурированные данные…………………………………….. 53
2.1. Используем относительные и абсолютные ссылки…………………………………………………………. 53
2.2. Используем относительные и абсолютные ссылки в условном форматировании……….. 55
2.3. Используем стиль ссылок R1C1………………………………………………………………………………………… 56
2.4. Ссылаемся на другой рабочий лист или книгу………………………………………………………………… 58
2.5. Используем 3D-ссылки……………………………………………………………………………………………………….. 59
2.6. Именуем ячейки, диапазоны, константы и формулы……………………………………………………….. 60
2.7. Создаем динамические именованные диапазоны……………………………………………………………. 63
2.8. Проверяем данные………………………………………………………………………………………………………………. 64
2.9. Создаем правило пользовательской проверки данных…………………………………………………… 67
2.10. Вводим данные с помощью раскрывающегося списка…………………………………………………. 67
2.11. Определяем зависимые, или каскадные, раскрывающиеся списки………………………………. 69
2.12. Используем форму ввода данных……………………………………………………………………………………. 70
2.13. Сортируем данные по значению, формату или пользовательскому списку……………….. 72
2.14. Фильтруем данные……………………………………………………………………………………………………………. 73
2.15. Закрепляем области………………………………………………………………………………………………………….. 75
2.16. Используем автосумму…………………………………………………………………………………………………….. 76
2.17. Используем структуру для добавления промежуточных итогов и групп……………………. 77
2.18. Используем таблицы………………………………………………………………………………………………………… 78
2.19. Используем структурированные ссылки………………………………………………………………………… 81
Глава 3. Используем формулы……………………………………………………………….. 85
3.1. Используем операторы и порядок очередности………………………………………………………………. 85
3.2. Используем Excel в различных регионах и на разных языках……………………………………….. 88
3.3. Используем константы массивов………………………………………………………………………………………. 89
3.4. Используем динамические и устаревшие формулы массивов………………………………………… 90
3.5. Используем ссылки на диапазон разлива…………………………………………………………………………. 92
3.6. Предотвращаем поведение динамического массива……………………………………………………….. 93
3.7. Используем Вставку функции или инструмент Построитель формул………………………….. 94
3.8. Добавляем заметки в числовые формулы…………………………………………………………………………. 95
3.9. Отображаем формулы………………………………………………………………………………………………………… 96
3.10. Используем Окно контрольного значения……………………………………………………………………….. 96
3.11. Отображаем взаимозависимости ячеек…………………………………………………………………………… 97
3.12. Выполняем фоновую проверку ошибок………………………………………………………………………….. 99
3.13. Проверяем ошибки в таблице………………………………………………………………………………………… 100
3.14. Ищем источники ошибок………………………………………………………………………………………………… 101
3.15. Исправляем ошибки в значениях…………………………………………………………………………………… 102
3.16. Вычисляем формулы………………………………………………………………………………………………………. 104
3.17. Меняем режим вычисления……………………………………………………………………………………………. 106
3.18. Настраиваем точность округления……………………………………………………………………………….. 108
3.19. Работаем с циклическими ссылками…………………………………………………………………………….. 109
Глава 4. Математические операции……………………………………………………… 111
4.1. Генерируем числа…………………………………………………………………………………………………………….. 111
4.2. Преобразуем текст или булево выражение в число………………………………………………………. 112
4.3. Узнаем знак и абсолютное значение числа……………………………………………………………………. 113
4.4. Подсчитываем, суммируем и усредняем значения ячеек………………………………………………. 113
4.5. Используем критерии для подсчета, суммирования и среднего значения…………………… 115
4.6. Складываем и вычитаем значения в квадрате……………………………………………………………….. 116
4.7. Используем умножение и кратные значения………………………………………………………………….. 118
4.8. Находим частные, остатки и делители…………………………………………………………………………… 119
4.9. Округляем до десятичных разрядов и целых чисел………………………………………………………. 120
4.10. Округляем до значащих цифр и кратных значений…………………………………………………….. 121
4.11. Используем степени, экспоненты, квадратные корни и логарифмы………………………….. 123
4.12. Суммируем степенной ряд……………………………………………………………………………………………… 124
4.13. Используем факториалы, перестановки и комбинации………………………………………………. 124
4.14. Используем тригонометрию…………………………………………………………………………………………… 126
4.15. Работаем с матрицами……………………………………………………………………………………………………. 126
4.16. Преобразуем одну систему счисления в другую…………………………………………………………. 128
4.17. Выполняем побитовые операции………………………………………………………………………………….. 129
4.18. Работаем с комплексными числами………………………………………………………………………………. 130
Глава 5. Работа с текстом…………………………………………………………………….. 133
5.1. Объединяем текст……………………………………………………………………………………………………………… 133
5.2. Используем символьный код……………………………………………………………………………………………. 134
5.3. Генерируем последовательность символов……………………………………………………………………. 135
5.4. Генерируем случайные буквы…………………………………………………………………………………………. 136
5.5. Определяем длину текстовой строки………………………………………………………………………………. 136
5.6. Определяем местоположение текста в текстовой строке………………………………………………. 136
5.7. Извлекаем текст фиксированной ширины из текстовой строки……………………………………. 138
5.8. Извлекаем текст из текстовой строки с помощью разделителя…………………………………….. 139
5.9. Разбиваем текст в текстовой строке на цифровой и нецифровой…………………………………. 141
5.10. Заменяем, вставляем и удаляем текст…………………………………………………………………………… 142
5.11. Удаляем лишние символы……………………………………………………………………………………………… 143
5.12. Подсчитываем слова или конкретные символы…………………………………………………………… 144
5.13. Меняем регистр текста…………………………………………………………………………………………………… 145
5.14. Повторяем символы………………………………………………………………………………………………………… 145
5.15. Преобразуем массив в текст…………………………………………………………………………………………… 146
5.16. Форматируем текст в формат валюты………………………………………………………………………….. 147
5.17. Включаем числовые значения в текстовую строку……………………………………………………… 148
5.18. Включаем дату/время в текстовую строку…………………………………………………………………… 149
Глава 6. Дата и время…………………………………………………………………………… 151
6.1. Возвращаем текущие дату и время…………………………………………………………………………………. 151
6.2. Получаем часть значения даты/времени………………………………………………………………………… 152
6.3. Получаем день недели и неделю года…………………………………………………………………………….. 152
6.4. Получаем календарный или финансовый квартал………………………………………………………… 153
6.5. Создаем даты с помощью дня, месяца и года………………………………………………………………… 154
6.6. Создаем время с помощью часов, минут и секунд…………………………………………………………. 156
6.7. Преобразуем текстовое значение в порядковый номер даты/времени………………………… 157
6.8. Извлекаем дату и время из порядкового номера……………………………………………………………. 158
6.9. Добавляем к дате дни, месяцы и годы…………………………………………………………………………….. 159
6.10. Добавляем ко времени часы, минуты и секунды………………………………………………………….. 160
6.11. Получаем последний день месяца…………………………………………………………………………………. 161
6.12. Вычисляем долю года…………………………………………………………………………………………………….. 162
6.13. Вычисляем разницу между значениями дат и времени……………………………………………….. 163
6.14. Обрабатываем рабочие дни…………………………………………………………………………………………… 164
6.15. Получаем последовательность дат………………………………………………………………………………. 165
Глава 7. Массивы, логика и функции поиска………………………………………. 167
7.1. Получаем уникальные значения……………………………………………………………………………………… 167
7.2. Сортируем массив…………………………………………………………………………………………………………….. 168
7.3. Фильтруем массив…………………………………………………………………………………………………………….. 169
7.4. Преобразовываем массивы………………………………………………………………………………………………. 171
7.5. Используем логические критерии Истина и Ложь………………………………………………………… 173
7.6. Оцениваем условия И и ИЛИ в формулах массивов………………………………………………………. 174
7.7. Работаем с типами и ошибками в значениях………………………………………………………………….. 175
7.8. Выбираем возвращаемые значения………………………………………………………………………………… 176
7.9. Ищем точные и ближайшие значения……………………………………………………………………………… 178
7.10. Ищем индекс сопоставимого значения…………………………………………………………………………. 180
7.11. Используем индекс для возврата значения…………………………………………………………………… 181
7.12. Создаем косвенные ссылки на ячейки и диапазоны……………………………………………………. 183
7.13. Извлекаем адрес ячейки…………………………………………………………………………………………………. 185
7.14. Используем смещенные ссылки…………………………………………………………………………………….. 185
Глава 8. Статистический анализ………………………………………………………….. 187
8.1. Создаем таблицу частот………………………………………………………………………………………………….. 187
8.2. Отображаем накопленные и процентные частоты………………………………………………………… 189
8.3. Используем гистограмму или диаграмму Парето………………………………………………………….. 191
8.4. Вычисляем среднее…………………………………………………………………………………………………………… 193
8.5. Ранжируем числовые данные………………………………………………………………………………………….. 195
8.6. Находим k-е наибольшее или наименьшее значение…………………………………………………….. 196
8.7. Разделяем данные на квартили и процентили……………………………………………………………….. 197
8.8. Рассчитываем диапазоны и дисперсии…………………………………………………………………………… 198
8.9. Поиск выбросов…………………………………………………………………………………………………………………. 199
8.10. Используем диаграмму «ящик с усами»……………………………………………………………………….. 200
8.11. Рассчитываем асимметрию……………………………………………………………………………………………. 201
8.12. Рассчитываем вероятности с помощью таблицы вероятностей…………………………………. 202
8.13. Рассчитываем математическое ожидание и дисперсию……………………………………………… 204
8.14. Используем биномиальное распределение………………………………………………………………….. 204
8.15. Используем отрицательное биномиальное распределение………………………………………… 206
8.16. Используем гипергеометрическое распределение………………………………………………………. 207
8.17. Используем распределение Пуассона………………………………………………………………………….. 208
8.18. Используем экспоненциальное распределение……………………………………………………………. 208
8.19. Используем нормальное распределение………………………………………………………………………. 209
8.20. Используем Z-значения………………………………………………………………………………………………….. 211
8.21. Вычисляем доверительный интервал для среднего генеральной совокупности………. 212
8.22. Используем критерий независимости хи-квадрат (χ2)…………………………………………………. 214
8.23. Ищем линию наилучшего соответствия……………………………………………………………………….. 215
8.24. Получаем уравнение линии наилучшего соответствия………………………………………………. 217
Глава 9. Надстройка Пакет анализа…………………………………………………….. 219
9.1. Устанавливаем Пакет анализа………………………………………………………………………………………… 219
9.2. Генерируем описательную статистику…………………………………………………………………………… 220
9.3. Генерируем порядковые номера и процентные ранги…………………………………………………… 222
9.4. Генерируем частотное распределение…………………………………………………………………………… 224
9.5. Генерируем скользящее среднее……………………………………………………………………………………… 227
9.6. Используем экспоненциальное сглаживание…………………………………………………………………. 229
9.7. Генерируем случайную выборку…………………………………………………………………………………….. 231
9.8. Генерируем периодическую выборку…………………………………………………………………………….. 233
9.9. Извлекаем случайные числа из распределения……………………………………………………………… 233
9.10. Генерируем корреляционную матрицу………………………………………………………………………… 235
9.11. Получаем матрицу ковариаций…………………………………………………………………………………….. 237
9.12. Проводим линейный регрессионный анализ………………………………………………………………… 238
9.13. Используем двухвыборочный t-критерий Стьюдента…………………………………………………. 241
9.14. Проводим двухвыборочный z-тест……………………………………………………………………………….. 243
9.15. Проводим парный двухвыборочный t-тест………………………………………………………………….. 245
9.16. Проводим двухвыборочный F-тест для дисперсии……………………………………………………… 247
9.17. Проводим однофакторный дисперсионный анализ…………………………………………………….. 249
9.18. Проводим двухфакторный дисперсионный анализ…………………………………………………….. 251
9.19. Проводим анализ Фурье…………………………………………………………………………………………………. 253
Глава 10. Финансовый анализ……………………………………………………………… 257
10.1. Рассчитываем платежи по кредитам с фиксированной процентной ставкой……………. 257
10.2. Рассчитываем выплаты процентов и основной суммы кредита…………………………………. 258
10.3. Составляем график амортизации кредита с плавающей ставкой………………………………. 260
10.4. Рассчитываем срок для кредита с фиксированной ставкой………………………………………… 262
10.5. Рассчитываем номинальную или текущую стоимость……………………………………………….. 263
10.6. Конвертируем номинальную процентную ставку в реальную…………………………………… 263
10.7. Рассчитываем будущую стоимость единовременной инвестиции
с фиксированной ставкой……………………………………………………………………………………………….. 265
10.8. Рассчитываем будущую стоимость единовременной инвестиции с
плавающей ставкой………………………………………………………………………………………………………… 266
10.9. Рассчитываем будущую стоимость инвестиции с регулярными вложениями………….. 267
10.10. Достигаем инвестиционных целей………………………………………………………………………………. 268
10.11. Рассчитываем чистую приведенную стоимость………………………………………………………… 269
10.12. Рассчитываем внутреннюю ставку доходности………………………………………………………… 271
10.13. Рассчитываем амортизацию………………………………………………………………………………………… 273
10.14. Получаем данные акций и валют………………………………………………………………………………… 274
10.15. Получаем исторические данные акций и валют………………………………………………………… 276
10.16. Используем биржевые диаграммы………………………………………………………………………………. 276
10.17. Рассчитываем бета-коэффициент акции…………………………………………………………………….. 278
10.18. Прогнозируем линейный и экспоненциальный рост…………………………………………………. 279
10.19. Прогнозируем сезонный рост………………………………………………………………………………………. 280
Глава 11. Сводные таблицы…………………………………………………………………. 285
11.1. Организуем данные для сводных таблиц……………………………………………………………………… 285
11.2. Вставляем сводную таблицу…………………………………………………………………………………………. 286
11.3. Добавляем строки, столбцы и значения……………………………………………………………………….. 288
11.4. Используем дополнительные строки…………………………………………………………………………….. 290
11.5. Обновляем данные сводной таблицы……………………………………………………………………………. 291
11.6. Перемещаем сводную таблицу……………………………………………………………………………………… 292
11.7. Изменяем внешний вид сводной таблицы…………………………………………………………………….. 293
11.8. Изменяем макет по умолчанию……………………………………………………………………………………… 294
11.9. Изменяем агрегацию значений………………………………………………………………………………………. 295
11.10. Отображаем различные вычисления значений………………………………………………………….. 296
11.11. Создаем пользовательские промежуточные итоги……………………………………………………. 298
11.12. Сортируем данные……………………………………………………………………………………………………….. 299
11.13. Перемещаем элементы вручную…………………………………………………………………………………. 300
11.14. Фильтруем данные……………………………………………………………………………………………………….. 300
11.15. Используем фильтр для создания нескольких сводных таблиц……………………………….. 302
11.16. Группируем по дате/времени………………………………………………………………………………………. 303
11.17. Группируем по числу…………………………………………………………………………………………………… 305
11.18. Группируем вручную по текстовым значениям…………………………………………………………. 306
11.19. Включаем группы с отсутствующими данными………………………………………………………… 307
11.20. Изменяем формат пустых ячеек…………………………………………………………………………………… 308
11.21. Используем вычисляемые поля……………………………………………………………………………………. 309
11.22. Используем вычисляемые поля для подсчета элементов………………………………………….. 312
11.23. Используем вычисляемые элементы…………………………………………………………………………… 312
11.24. Ссылаемся на местоположение в формуле вычисляемого элемента……………………….. 316
11.25. Изменяем порядок расчета вычисляемого элемента…………………………………………………. 316
11.26. Формируем список пользовательских формул…………………………………………………………… 319
11.27. Изменяем источник данных сводной таблицы…………………………………………………………… 319
11.28. Используем кеш сводной таблицы………………………………………………………………………………. 320
11.29. Фильтруем несколько сводных таблиц, совместно использующих один кеш………… 322
11.30. Уменьшаем размер файла рабочей книги…………………………………………………………………… 323
11.31. Восстанавливаем исходные данные сводной таблицы…………………………………………….. 324
11.32. Ссылаемся на значения сводной таблицы…………………………………………………………………. 325
Глава 12. Диаграммы…………………………………………………………………………… 327
12.1. Используем различные типы диаграмм………………………………………………………………………… 327
12.2. Вставляем диаграмму…………………………………………………………………………………………………….. 333
12.3. Фильтруем диаграмму……………………………………………………………………………………………………. 334
12.4. Настраиваем внешний вид диаграммы…………………………………………………………………………. 335
12.5. Добавляем и удаляем элементы диаграммы………………………………………………………………… 336
12.6. Форматируем элементы диаграммы……………………………………………………………………………… 337
12.7. Создаем динамические заголовки и метки……………………………………………………………………. 342
12.8. Настраиваем текст меток данных…………………………………………………………………………………. 342
12.9. Управляем осями и линиями сетки диаграммы……………………………………………………………. 344
12.10. Отображаем отрицательные значения……………………………………………………………………….. 345
12.11. Используем в гистограммах рисунки………………………………………………………………………….. 346
12.12. Форматируем вторичную круговую и линейчатую диаграмму……………………………….. 347
12.13. Форматируем гистограммы…………………………………………………………………………………………. 348
12.14. Задаем типы диаграмм для комбинированной диаграммы………………………………………. 350
12.15. Управляем пустыми ячейками…………………………………………………………………………………….. 351
12.16. Основываем диаграмму на независимых данных……………………………………………………… 352
12.17. Изменяем имена рядов данных и элементы легенды…………………………………………………. 353
12.18. Добавляем ряд или изменяем источник данных………………………………………………………… 354
12.19. Основываем диаграмму на динамическом именованном диапазоне………………………. 355
12.20. Вставляем сводную диаграмму…………………………………………………………………………………… 357
12.21. Создаем диаграмму Ганта…………………………………………………………………………………………… 358
12.22. Создаем и используем шаблоны диаграмм………………………………………………………………… 359
Глава 13. Графика, спарклайны и 3D-карты……………………………………….. 361
13.1. Вставляем символы………………………………………………………………………………………………………… 361
13.2. Вставляем уравнения……………………………………………………………………………………………………… 362
13.3. Вставляем фигуры…………………………………………………………………………………………………………… 363
13.4. Используем инструмент Рисование………………………………………………………………………………. 364
13.5. Используем SmartArt………………………………………………………………………………………………………. 365
13.6. Вставляем рисунки…………………………………………………………………………………………………………. 366
13.7. Группируем объекты………………………………………………………………………………………………………. 368
13.8. Перемещаем и изменяем размеры объектов вместе с ячейками………………………………….. 369
13.9. Вставляем связанный рисунок………………………………………………………………………………………. 370
13.10. Используем спарклайны………………………………………………………………………………………………. 371
13.11. Используем группы спарклайнов………………………………………………………………………………… 374
13.12. Используем 3D-карты…………………………………………………………………………………………………… 375
13.13. Создаем видео с помощью 3D-карт…………………………………………………………………………….. 378
Глава 14. Анализ «что если»…………………………………………………………………. 381
14.1. Создаем таблицу данных с одной переменной……………………………………………………………. 381
14.2. Создаем ориентированную на строку таблицу данных с одной переменной………….. 383
14.3. Создаем таблицу данных с двумя переменными…………………………………………………………. 384
14.4. Редактируем таблицы данных………………………………………………………………………………………. 386
14.5. Используем диспетчер сценариев…………………………………………………………………………………. 387
14.6. Объединяем сценарии…………………………………………………………………………………………………….. 390
14.7. Генерируем сводки сценариев……………………………………………………………………………………….. 390
14.8. Используем инструмент Подбор параметра……………………………………………………………….. 392
14.9. Находим несколько решений с помощью подбора параметра………………………………….. 394
14.10. Находим решение формулы, описывающей функцию, имеющую разрывы, с помощью подбора параметра 395
14.11. Устанавливаем надстройку Поиск решения………………………………………………………………. 397
14.12. Решаем с помощью надстройки Поиск решения проблемы оптимизации……………….. 398
14.13. Используем в надстройке Поиск решения ограничение «только целые числа»……… 403
14.14. Используем в в надстройке Поиск решения ограничение «только двоичные»………… 406
14.15. Создаем с помощью надстройки Поиск решения изменяемые ячейки
с разными значениями………………………………………………………………………………………………….. 410
14.16. Решаем с помощью надстройки Поиск решения проблему разрывов функции………. 413
14.17. Находим с помощью надстройки Поиск решения несколько решений……………………. 415
14.18. Находим с помощью надстройки Поиск решения глобальный минимум или максимум функции 417
14.19. Настаиваем параметры надстройки Поиск решения………………………………………………… 419
14.20. Сохраняем и загружаем параметры надстройки Поиск решения…………………………….. 422
14.21. Сохраняем сценарии, сгенерированные надстройкой Поиск решения……………………. 423
14.22. Отображаем отчеты надстройки Поиск решения………………………………………………………. 423
Глава 15. Power Query………………………………………………………………………….. 425
15.1. Получаем и загружаем данные……………………………………………………………………………………… 425
15.2. Получаем и загружаем данные из файлов в папке………………………………………………………. 427
15.3. Указываем, куда загрузить данные……………………………………………………………………………….. 428
15.4. Редактируем настройки и безопасность источника данных………………………………………. 430
15.5. Обновляем данные запроса……………………………………………………………………………………………. 432
15.6. Управляем запросами…………………………………………………………………………………………………….. 433
15.7. Редактируем запрос………………………………………………………………………………………………………… 435
15.8. Управляем шагами запроса…………………………………………………………………………………………… 437
15.9. Управляем столбцами…………………………………………………………………………………………………….. 437
15.10. Используем типы данных…………………………………………………………………………………………….. 438
15.11. Сортируем и фильтруем данные…………………………………………………………………………………. 440
15.12. Фильтруем файлы при загрузке данных из папки……………………………………………………… 441
15.13. Удаляем дубликаты, пустые строки и ошибки…………………………………………………………… 442
15.14. Преобразуем данные в столбцах………………………………………………………………………………… 443
15.15. Разделяем и объединяем столбцы……………………………………………………………………………….. 447
15.16. Выполняем сведение столбцов……………………………………………………………………………………. 449
15.17. Отменяем сведение столбцов……………………………………………………………………………………….. 450
15.18. Преобразуем структурированные столбцы……………………………………………………………….. 451
15.19. Возвращаем значение или список……………………………………………………………………………….. 452
15.20. Добавляем новые столбцы…………………………………………………………………………………………… 453
15.21. Добавляем столбцы, основанные на примерах…………………………………………………………. 455
15.22. Добавляем условный столбец……………………………………………………………………………………… 457
15.23. Добавляем настраиваемый столбец……………………………………………………………………………. 458
15.24. Используем параметры………………………………………………………………………………………………… 460
15.25. Создаем пользовательскую функцию…………………………………………………………………………. 462
15.26. Добавляем столбец, вызывая пользовательскую функцию………………………………………. 465
15.27. Дублируем отчет…………………………………………………………………………………………………………… 466
15.28. Ссылаемся на запрос……………………………………………………………………………………………………. 466
15.29. Добавляем данные из нескольких запросов……………………………………………………………….. 468
15.30. Объединяем данные из нескольких запросов…………………………………………………………….. 469
15.31. Редактируем М-код запроса………………………………………………………………………………………… 471
Глава 16. Power Pivot и модель данных………………………………………………… 473
16.1. Устанавливаем Power Pivot……………………………………………………………………………………………. 473
16.2. Добавляем данные в модель данных…………………………………………………………………………….. 474
16.3. Управляем соединениями данных Power Pivot…………………………………………………………….. 475
16.4. Просматриваем таблицы модели данных и управляем ими……………………………………….. 477
16.5. Обновляем данные модели данных………………………………………………………………………………. 479
16.6. Работаем со столбцами таблицы………………………………………………………………………………….. 480
16.7. Создаем и изменяем взаимосвязи…………………………………………………………………………………… 482
16.8. Добавляем вычисляемый столбец…………………………………………………………………………………. 484
16.9. Создаем сводную таблицу или сводную диаграмму на основе модели данных……… 487
16.10. Вставляем меры…………………………………………………………………………………………………………….. 489
16.11. Используем КПЭ…………………………………………………………………………………………………………… 492
16.12. Создаем иерархии………………………………………………………………………………………………………… 496
16.13. Создаем таблицу данных…………………………………………………………………………………………….. 498
16.14. Используем именованные наборы………………………………………………………………………………. 500
16.15. Преобразуем сводную таблицу в формулы……………………………………………………………….. 504
16.16. Используем формулы кубов…………………………………………………………………………………………. 505
16.17. Фильтруем формулы кубов с помощью срезов и временных шкал………………………….. 508
Глава 17. LET, LAMBDA и вспомогательные лямбда-функции……………… 511
17.1. Повышаем эффективность формулы……………………………………………………………………………… 511
17.2. Пишем и тестируем лямбда-функции……………………………………………………………………………. 512
17.3. Делаем лямбда-аргументы необязательными……………………………………………………………… 514
17.4. Определяем пользовательскую лямбда-функцию……………………………………………………….. 515
17.5. Пишем рекурсивные лямбда-формулы…………………………………………………………………………. 516
17.6. Копируем пользовательскую лямбда-функцию в другую рабочую книгу………………… 518
17.7. Применяем лямбда-формулу в каждом столбце…………………………………………………………… 519
17.8. Применяем лямбда-формулу в каждой строке……………………………………………………………… 521
17.9. Создаем массив вычисляемых значений………………………………………………………………………. 522
17.10. Преобразуем значения в массивах……………………………………………………………………………… 524
17.11. Вычисляем кумулятивные значения……………………………………………………………………………. 525
17.12. Возвращаем конечное значение кумулятивного вычисления…………………………………… 527
Глава 18. Инструменты разработчика: макросы, VBA, элементы управления и XML 531
18.1. Отображаем вкладку Разработчик………………………………………………………………………………. 531
18.2. Записываем макросы……………………………………………………………………………………………………… 532
18.3. Используем личную книгу макросов…………………………………………………………………………….. 534
18.4. Редактируем настройки макроса…………………………………………………………………………………… 535
18.5. Запускаем макрос…………………………………………………………………………………………………………… 536
18.6. Просматриваем и редактируем VBA-код макроса………………………………………………………. 537
18.7. Используем абсолютные и относительные ссылки……………………………………………………… 540
18.8. Создаем макрос путем написания VBA-кода………………………………………………………………. 541
18.9. Создаем пользовательскую функцию VBA………………………………………………………………….. 542
18.10. Используем события рабочих листов и рабочих книг………………………………………………. 544
18.11. Переопределяем комбинацию клавиш с помощью OnKey………………………………………… 547
18.12. Планируем выполнение кода с помощью OnTime……………………………………………………… 549
18.13. Удаляем макрос или функцию…………………………………………………………………………………….. 550
18.14. Копируем код в другой VBA-проект…………………………………………………………………………… 551
18.15. Исправляем ошибки VBA-кода……………………………………………………………………………………. 552
18.16. Используем встроенные диалоговые окна…………………………………………………………………. 557
18.17. Используем элементы управления формы………………………………………………………………….. 558
18.18. Используем элементы управления ActiveX………………………………………………………………… 562
18.19. Создаем пользовательскую форму……………………………………………………………………………… 568
18.20. Создаем пользовательскую надстройку Excel…………………………………………………………… 571
18.21. Настраиваем параметры безопасности и конфиденциальности……………………………… 573
18.22. Импортируем и экспортируем XML-файлы……………………………………………………………….. 574
Дальнейшие шаги………………………………………………………………………………… 577
Предметный указатель…………………………………………………………………………. 579
Об авторе……………………………………………………………………………………………… 589
Об изображении на обложке………………………………………………………………… 591
