Сводные таблицы в Microsoft Excel 2021 и Microsoft 365

Содержание
От издательства
Отзывы и пожелания
Список опечаток
Нарушение авторских прав
Благодарности
Об авторе
Введение
Чему вы научитесь в этой книге
Что нового в сводных таблицах Microsoft 365 Excel
Пример: жизнь до появления сводных таблиц
Изобретение сводных таблиц
Пример: жизнь после появления сводных таблиц
Создание сводной таблицы с применением искусственного интеллекта
Для кого эта книга
Организация книги
Сопроводительные материалы
Системные требования
1. Основы сводных таблиц
Почему стоит использовать сводные таблицы
Когда необходимо применять сводные таблицы
Анатомия сводных таблиц
Область значений
Область строк
Область столбцов
Область фильтров
За кулисами сводных таблиц
Обратная совместимость сводных таблиц
Еще немного о совместимости
2. Создание базовой сводной таблицы
Требования к исходным данным
Убедитесь, что данные представлены в виде таблицы
Избегайте хранения данных в заголовках секций
Избегайте представления групп данных в виде столбцов
Избегайте появления пропусков и пустых ячеек в данных
Применяйте правильные типы данных к полям
Итоговые требования к источнику данных
Пример: очистка данных для создания сводной таблицы
Создание базовой сводной таблицы
Как найти панель с полями сводной таблицы?
Добавление полей в отчет
Основы расположения данных в сводных таблицах
Откуда Excel знает, куда помещать выбранные поля?
Добавление слоев в сводную таблицу
Изменение структуры сводной таблицы
Тоска по перетаскиванию полей внутри сводной таблицы
Создание фильтра отчета
Рекомендуемые сводные таблицы и инструмент анализа данных
Новый инструмент анализа данных с использованием искусственного интеллекта
Использование срезов для фильтрации отчетов
Создание стандартного среза
Создание среза в виде временной шкалы
Пример: анализ деятельности по рынкам
Поддержка актуальности данных из источника
Поддержка изменений в источнике данных
Поддержка расширения источника данных в связи с добавлением строк/столбцов
Совместное использование кеша сводных таблиц или создание нового кеша
Побочные действия от совместного использования кеша сводной таблицы
Экономия времени с новыми инструментами сводных таблиц
Отложенное обновление макета
Начать с нуля одним щелчком
Перемещение сводной таблицы
3. Настройка сводной таблицы
Внесение общих косметических изменений
Применение табличного стиля для восстановления линий сетки
Изменение формата числовых значений с добавлением разделителей разрядов
Заполнение пустых ячеек нулями
Изменение имени поля
Изменение макета отчета
Макет в сжатой форме
Макет в форме структуры
Макет в табличной форме
Пример: преобразование сводной таблицы в значения
Пустые строки, общие итоги и прочие настройки
Настройка внешнего вида сводной таблицы с помощью стилей и тем
Настройка стилей
Настройка стилей с использованием тем
Пример: археология сводных таблиц в Excel
Изменение агрегации полей
Исправление ошибки с подсчетом количества элементов
Изменение вычислений в полях значений
Процент от общей суммы
Использование доли для сравнения строк
Опции ранжирования
Абсолютный и относительный нарастающий итог
Разница по сравнению с предыдущим полем
Процент от родительской суммы
Относительная важность элементов и опция Индекс
Добавление и удаление промежуточных итогов
Подавление вывода промежуточных итогов с несколькими полями в строках
Дополнительные промежуточные итоги для поля
Форматирование одной ячейки в Microsoft 365
4. Группировка, сортировка и фильтрация сводных таблиц
Использование списка выбора поля в сводной таблице
Закрепление и открепление панели полей сводной таблицы
Минимизирование панели полей сводной таблицы
Настройка панели полей сводной таблицы
Использование разделов с областями
Сортировка в сводной таблице
Сортировка покупателей по выручке в порядке убывания
Ручной метод сортировки
Использование пользовательских списков для сортировки
Фильтрация в сводной таблице
Использование фильтров для полей в строках и столбцах
Фильтрация с использованием флажков
Фильтрация с использованием поля поиска
Фильтрация по подписи
Фильтрация с использованием информации из колонки со значениями
Использование фильтра Первые 10
Фильтрация дат
Использование полей в области фильтров
Добавление полей в область фильтров
Выбор одного элемента в фильтре
Выбор нескольких элементов в фильтре
Формирование отдельных сводных таблиц для каждого элемента в фильтре
Фильтрация с помощью срезов и временных шкал
Фильтрация по датам с использованием временных шкал
Управление несколькими сводными таблицами с помощью одного набора срезов
Группировка в сводной таблице
Группировка числовых полей
Пример: группировка текстового поля для перераспределения районов
Ручная группировка полей с датами
Включение лет при группировке дат по месяцам
Группировка полей с датами по неделям
Создание простого отчета для сравнения двух лет
Создание иерархий в сводной таблице
5. Выполнение вычислений в сводных таблицах
Введение в вычисляемые поля и вычисляемые объекты
Способ 1: ручное добавление вычисляемого поля в исходные данные
Способ 2: использование формулы за пределами сводной таблицы
Способ 3: добавление вычисляемого поля в сводную таблицу напрямую
Создание вычисляемых полей
Пример: прогноз на следующий год
Создание вычисляемых объектов
Правила и недостатки вычислений в сводных таблицах
Порядок выполнения операторов
Использование ссылок на ячейки и именованные диапазоны
Использование функций рабочего листа
Использование констант
Ссылка на итоги
Правила, применяемые к вычисляемым полям
Пример: использование мер DAX вместо вычисляемых полей
Правила, применяемые к вычисляемым объектам
Управление и поддержка вычислений в сводных таблицах
Редактирование и удаление пользовательских вычислений в сводных таблицах
Изменение порядка вычислений объектов
Документирование формул
6. Использование сводных диаграмм и других приемов визуализации данных
Что такое сводные диаграммы?
Создание сводной диаграммы
Кнопки полей сводной диаграммы
Создание сводной диаграммы с нуля
Правила и ограничения сводных диаграмм
Изменения в исходной сводной таблице оказывают влияние на сводную диаграмму
Расположение полей в сводной таблице может не подойти для сводной диаграммы
Ограничения форматирования в Excel
Пример: создание интерактивного отчета по выручке в разрезе товаров и периодов
Альтернативы сводным диаграммам
Способ 1: сохранение сводной таблицы в виде значений
Способ 2: удаление базовой сводной таблицы
Способ 3: распространение изображения сводной таблицы
Способ 4: использование в качестве источника для диаграммы ячеек, связанных со сводной таблицей
Использование условного форматирования в сводных таблицах
Пример использования условного форматирования
Программируемые сценарии условного форматирования
Создание пользовательских правил условного форматирования
7. Анализ разрозненных источников данных при помощи сводных таблиц
Использование модели данных
Строим свою первую модель данных
Важное замечание о первичных ключах
Управление связями в модели данных
Добавление таблиц в модель данных
Ограничения модели данных
Построение сводной таблицы на основе внешних источников данных
Создание сводной таблицы на основе данных из Microsoft Access
Создание сводной таблицы на основе данных из Microsoft SQL Server
Использование Power Query для извлечения и преобразования данных
Основы Power Query
Примененные шаги
Расширенный редактор Power Query
Обновление данных в Power Query
Управление существующими запросами
Действия со столбцами
Действия с таблицами
Типы подключения в Power Query
Пример: транспонирование набора данных с помощью Power Query
Еще один пример использования Power Query
8. Распространение дашбордов с помощью Power BI
Знакомство с Power BI Desktop
Подготовка данных в Excel
Импорт данных в Power BI
Знакомимся с Power BI
Подготовка данных в Power BI
Определение синонимов для полей в Power BI Desktop
Построение интерактивных отчетов в Power BI Desktop
Ваша первая визуализация
Ваша вторая визуализация
Перекрестная фильтрация элементов визуализации
Создание иерархий
Импорт пользовательских визуальных элементов
Публикация в Power BI
Разработка для мобильных устройств
Публикация в рабочую область
9. Использование формул кубов с моделью данных или данными OLAP
Преобразование сводных таблиц в формулы кубов
Введение в технологию OLAP
Подключение к кубу OLAP
Понимание структуры куба OLAP
Ограничения сводных таблиц OLAP
Создание автономного куба
Слом парадигмы сводных таблиц с помощью функций кубов
Обзор функций кубов
Добавление вычислений в сводные таблицы OLAP
Немного об MDX
Создание вычисляемых мер
Создание вычисляемых элементов
Управление вычислениями OLAP
Выполнение анализа «что если» применительно к данным OLAP
10. Эпоха моделей данных и Power Pivot
Замена функции ВПР (VLOOKUP) на модель данных
Скрытые возможности моделей данных
Подсчет уникальных значений в сводной таблице
Включение отобранных фильтром элементов в промежуточные итоги
Расчет медианы в сводной таблице при помощи меры DAX
Вывод текста в области значений
Обработка больших данных при помощи Power Query
Добавление столбца при помощи Power Query
Power Query – это как запись макросов в Excel, но лучше
Минуя Excel – в модель данных
Добавление связанной таблицы
Определение связей между таблицами с использованием представления диаграммы
Добавление вычисляемых столбцов в окне Power Pivot
Сортировка одного столбца на основании другого
Создание сводной таблицы на основе модели данных
Использование продвинутых техник Power Pivot
Сложные связи
Использование логики операций со временем
Преодоление ограничений моделей данных
Другие преимущества Power Pivot
Создавайте все сводные таблицы в будущем с использованием моделей данных
11. Анализ географических данных с помощью 3D-карты
Подготовка данных для 3D-карты
Геокодирование данных
Построение гистограммы на 3D-карте
Навигация на карте
Пометка отдельных точек данных
Создание круговой и пузырьковой диаграмм на карте
Использование тепловой карты и региональной карты
Исследование настроек 3D-карты
Тонкая настройка 3D-карт
Комбинирование двух наборов данных
Анимация данных с течением времени
Создание обзора
Создание видео с 3D-картой
Пример: использование плана магазина вместо глобуса
12. Улучшение отчетов сводных таблиц при помощи макросов
Использование макросов совместно со сводными таблицами
Запись макросов
Немного о безопасности макросов
Создание пользовательского интерфейса с помощью элементов управления формы
Изменение записанных макросов для добавления функционала
Добавление полосы прокрутки
Создание макросов с помощью Power Query
13. Использование VBA и TypeScript для создания сводных таблиц
Активация VBA в вашем Excel
Использование форматов файлов с включенными макросами
Использование редактора Visual Basic
Инструменты Visual Basic в Excel
Использование средства записи макросов
Введение в объектно ориентированный код
Полезные трюки при написании кода
Поддержка диапазонов любых размеров
End+вниз против End+вверх
Использование объектных переменных
Использование With и End With для сокращения кода
Версии Excel
Создание сводной таблицы в Excel с помощью VBA
Добавление полей в область данных
Форматирование сводной таблицы
Обход ограничений сводных таблиц
Заполнение пустых ячеек в области данных
Заполнение пустых ячеек в области строк
Предотвращение ошибок при вставке или удалении ячеек
Управление итогами
Преобразование сводной таблицы в значения
Новый уровень сводных таблиц: создание отчета о выручке по категориям
Использование табличного макета
Агрегация данных по датам
Избавление от пустых ячеек
Контролируем порядок сортировки с помощью AutoSort
Изменение числового формата по умолчанию
Исключение промежуточных итогов для нескольких полей в области строк
Копирование сводной таблицы в новую рабочую книгу в виде значений
Итоговое форматирование
Добавление промежуточных итогов для разрывов страниц
Собираем все вместе
Вычисления в сводных таблицах
Решение проблем с несколькими полями данных
Использование альтернативных типов вычислений
Использование вычисляемых полей
Использование вычисляемых объектов
Вычисляемые группы
Использование дополнительных вычислений
Использование продвинутых техник при работе со сводными таблицами
Использование метода AutoShow при создании отчетов для руководства
Использование свойства ShowDetail для фильтрации набора данных
Создание отдельных отчетов для каждого региона или модели
Ручная фильтрация по двум и более элементам в сводной таблице
Использование концептуальных фильтров
Использование фильтра поиска
Настройка срезов для фильтрации сводных таблиц
Использование модели данных Excel
Добавление таблиц в модель данных
Создание связи между таблицами
Определение кеша сводной таблицы и создание сводной таблицы
Добавление полей в сводную таблицу
Добавление числовых полей в область значений
Собираем все вместе
Применение TypeScript в Excel Online для создания сводных таблиц
14. Продвинутые техники работы со сводными таблицами
Совет 1: автоматическое обновление сводных таблиц
Совет 2: одновременное обновление всех сводных таблиц в рабочей книге
Совет 3: сортировка элементов данных в уникальном порядке, а не по возрастанию или убыванию
Совет 4: использование (или отказ от использования) пользовательских списков при сортировке сводных таблиц
Совет 5: изменение настроек по умолчанию для будущих сводных таблиц
Совет 6: сохранение сводной таблицы в виде значений
Совет 7: заполнение пустых ячеек в области строк значениями
Вариант 1: использование опции повторения всех подписей элементов
Вариант 2: использование опции выделения ячеек
Совет 8: добавление в сводную таблицу поля с рангом
Совет 9: сокращение размера отчетов сводных таблиц
Удаление листа с источником данных
Совет 10: создание автоматически расширяемого диапазона данных
Совет 11: сравнение данных в таблицах с использованием сводных таблиц
Совет 12: использование автофильтра в сводной таблице
Совет 13: использование разных числовых форматов данных в одном поле
Совет 14: форматирование отдельных значений в сводной таблице
Совет 15: форматирование разделов сводной таблицы
Совет 16: создание частотного распределения с помощью сводной таблицы
Совет 17: использование сводных таблиц для создания наборов данных на отдельных вкладках
Совет 18: наложение ограничений на сводные таблицы и поля
Ограничения на сводные таблицы
Ограничения на поля сводных таблиц
Совет 19: использование сводных таблиц для создания наборов данных в отдельных рабочих книгах
Совет 20: вывод разницы в процентах между годами
Совет 21: двойной ВПР с помощью Power Query
Совет 22: использование срезов для управления данными из двух разных наборов
Совет 23: форматирование срезов
15. Единство и борьба противоположностей функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Исцеление от ненависти к функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Ручной ввод формулы
Отключение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Зачем эта возможность включена по умолчанию?
Использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для решения проблем в сводных таблицах
Создание страшной сводной таблицы
Создание оболочки отчета
Заполнение оболочки отчета данными
Обновление отчета для будущих месяцев
16. Создание сводных таблиц в Excel Online
Как зарегистрироваться в Excel Online
Создание сводной таблицы в Excel Online
Изменение настроек сводной таблицы в Excel Online
А как же остальные возможности?
17. Создание отчетов с помощью формул динамических массивов и Power Query
Использование расширенных фильтров и таблиц данных
Получение списка уникальных значений при помощи расширенного фильтра
Агрегация выручки с использованием функции БДСУММ (DSUM)
Повторение функции БДСУММ (DSUM) для каждой комбинации сектора и региона
Преимущества и недостатки этого метода
Использование динамических массивов
Получение списка уникальных значений при помощи динамических массивов
Заполнение данных о выручке с помощью функции СУММЕСЛИМН (SUMIFS)
Преимущества и недостатки этого метода
Сведение данных при помощи Power Query
Получение исходных данных в Power Query
Расчет выручки по секторам и регионам в Power Query
Сортировка и сведение данных в Power Query
Заключительные шаги очистки
Преимущества и недостатки этого метода
18. Отмена свертывания данных с помощью Power Query
Почему данные в заголовках – это плохо?
Использование отмены свертывания данных в Power Query для очистки
Отмена свертывания в таблицах с двумя уровнями заголовков
Отмена свертывания из ячейки с разделителями в новые строки
Заключение
Послесловие от автора
Предметный указатель