Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 Год издания: 2023 Автор: Джелен Билл Переводчик: Гинько А. Ю. Издательство: ДМК Пресс ISBN: 978-5-93700-127-6 Язык: Русский Формат: PDF Качество: Издательский макет или текст (eBook) Интерактивное оглавление: Да Количество страниц: 556 Описание: Сводные таблицы – один из наиболее мощных инструментов в Excel. Помимо возможности быстро и удобно агрегировать исходные данные, они позволяют менять структуру анализа на лету, перемещая поля из одной области в другую. Из этой книги вы узнаете, как создавать базовые сводные таблицы, которые помогут сократить время формирования аналитических отчетов, а также более сложные таблицы с возможностью выполнения детализации и выводом сопутствующих диаграмм. К концу чтения вы сможете создавать полноценные системы отчетности на основе динамических сводных таблиц. Издание предназначено как для опытных аналитиков, так и для обычных пользователей Excel, обладающих базовыми навыками работы с программой. Для выполнения примеров, описываемых в книге, понадобится установленный Microsoft Excel на платформе Windows.
Примеры страниц (скриншоты)
Оглавление
От издательства...........................................................................................16 Благодарности.............................................................................................17 Об авторе....................................................................................................18 Глава 1 Введение.........................................................................................19 Чему вы научитесь в этой книге....................................................................19 Что нового в сводных таблицах Microsoft 365 Excel.........................................20 Пример: жизнь до появления сводных таблиц................................................20 Изобретение сводных таблиц........................................................................22 Пример: жизнь после появления сводных таблиц...........................................23 Создание сводной таблицы с применением искусственного интеллекта.................................................................................................25 Для кого эта книга......................................................................................26 Организация книги.....................................................................................26 Сопроводительные материалы....................................................................26 Системные требования..............................................................................26 Глава 2 Основы сводных таблиц................................................................27 Почему стоит использовать сводные таблицы.............................................28 Когда необходимо применять сводные таблицы..........................................30 Анатомия сводных таблиц.........................................................................30 Область значений....................................................................................31 Область строк.........................................................................................31 Область столбцов...................................................................................32 Область фильтров.................................................................................32 За кулисами сводных таблиц.................................................................33 Обратная совместимость сводных таблиц...............................................34 Еще немного о совместимости...............................................................34 Глава 3 Создание базовой сводной таблицы..........................................36 Требования к исходным данным...........................................................36 Убедитесь, что данные представлены в виде таблицы............................37 Избегайте хранения данных в заголовках секций..................................38 Избегайте представления групп данных в виде столбцов.......................38 Избегайте появления пропусков и пустых ячеек в данных.....................39 Применяйте правильные типы данных к полям......................................39 Итоговые требования к источнику данных.............................................40 Создание базовой сводной таблицы......................................................45 Добавление полей в отчет....................................................................48 Основы расположения данных в сводных таблицах...............................48 Добавление слоев в сводную таблицу...................................................51 Изменение структуры сводной таблицы................................................51 Создание фильтра отчета....................................................................54 Рекомендуемые сводные таблицы и инструмент анализа данных............54 Использование срезов для фильтрации отчетов....................................57 Создание стандартного среза..............................................................57 Создание среза в виде временной шкалы.............................................60 Поддержка актуальности данных из источника.....................................62 Поддержка изменений в источнике данных..........................................63 Поддержка расширения источника данных в связи с добавлением строк/столбцов............................................................63 Совместное использование кеша сводных таблиц или создание нового кеша......................................................................................64 Побочные действия от совместного использования кеша сводной таблицы...............................................................................65 Экономия времени с новыми инструментами сводных таблиц................66 Отложенное обновление макета.........................................................66 Начать с нуля одним щелчком.............................................................67 Перемещение сводной таблицы..........................................................67 Глава 4 Настройка сводной таблицы...................................................69 Внесение общих косметических изменений..........................................70 Применение табличного стиля для восстановления линий сетки................................................................................................71 Изменение формата числовых значений с добавлением разделителей разрядов......................................................................72 Заполнение пустых ячеек нулями........................................................74 Изменение имени поля........................................................................75 Изменение макета отчета.....................................................................76 Макет в сжатой форме..........................................................................77 Макет в форме структуры.....................................................................78 Макет в табличной форме.....................................................................80 Пустые строки, общие итоги и прочие настройки....................................83 Настройка внешнего вида сводной таблицы с помощью стилей и тем..................................................................................................86 Настройка стилей................................................................................86 Настройка стилей с использованием тем...............................................89 Изменение агрегации полей.................................................................91 Исправление ошибки с подсчетом количества элементов........................91 Изменение вычислений в полях значений.............................................91 Процент от общей суммы.....................................................................95 Использование доли для сравнения строк............................................96 Опции ранжирования.........................................................................96 Абсолютный и относительный нарастающий итог..................................97 Разница по сравнению с предыдущим полем.......................................98 Процент от родительской суммы........................................................99 Относительная важность элементов и опция Индекс...................100 Добавление и удаление промежуточных итогов.................................101 Подавление вывода промежуточных итогов с несколькими полями в строках.................................................................................101 Дополнительные промежуточные итоги для поля.........................102 Форматирование одной ячейки в Microsoft 365...................................103 Глава 5 Группировка, сортировка и фильтрация сводных таблиц...............105 Использование списка выбора поля в сводной таблице.....................105 Закрепление и открепление панели полей сводной таблицы......107 Минимизирование панели полей сводной таблицы.....................108 Настройка панели полей сводной таблицы.....................................108 Использование разделов с областями..............................................109 Сортировка в сводной таблице...............................................................110 Сортировка покупателей по выручке в порядке убывания..........110 Ручной метод сортировки..................................................................114 Использование пользовательских списков для сортировки.........115 Фильтрация в сводной таблице..............................................................118 Использование фильтров для полей в строках и столбцах................119 Фильтрация с использованием флажков.........................................120 Фильтрация с использованием поля поиска...................................121 Фильтрация по подписи.....................................................................122 Фильтрация с использованием информации из колонки со значениями......................................................................................124 Использование фильтра Первые 10..................................................125 Фильтрация дат....................................................................................127 Использование полей в области фильтров...........................................129 Добавление полей в область фильтров............................................129 Выбор одного элемента в фильтре....................................................130 Выбор нескольких элементов в фильтре.........................................130 Формирование отдельных сводных таблиц для каждого элемента в фильтре.............................................................................130 Фильтрация с помощью срезов и временных шкал.......................132 Фильтрация по датам с использованием временных шкал..........134 Управление несколькими сводными таблицами с помощью одного набора срезов..........................................................................135 Группировка в сводной таблице.............................................................138 Группировка числовых полей............................................................138 Ручная группировка полей с датами................................................143 Включение лет при группировке дат по месяцам..........................144 Группировка полей с датами по неделям........................................145 Создание простого отчета для сравнения двух лет........................146 Создание иерархий в сводной таблице.................................................148 Глава 6 Выполнение вычислений в сводных таблицах.................................153 Введение в вычисляемые поля и вычисляемые объекты...................153 Способ 1: ручное добавление вычисляемого поля в исходные данные..............................................................................155 Способ 2: использование формулы за пределами сводной таблицы.................................................................................................155 Способ 3: добавление вычисляемого поля в сводную таблицу напрямую..............................................................................................157 Создание вычисляемых полей................................................................158 Создание вычисляемых объектов..........................................................166 Правила и недостатки вычислений в сводных таблицах....................170 Порядок выполнения операторов.....................................................170 Использование ссылок на ячейки и именованные диапазоны.............................................................................................171 Использование функций рабочего листа.........................................171 Использование констант....................................................................171 Ссылка на итоги...................................................................................172 Правила, применяемые к вычисляемым полям.............................172 Правила, применяемые к вычисляемым объектам........................178 Управление и поддержка вычислений в сводных таблицах..............179 Редактирование и удаление пользовательских вычислений в сводных таблицах.............................................................................179 Изменение порядка вычислений объектов.....................................180 Документирование формул...............................................................181 Глава 7 Использование сводных диаграмм и других приемов визуализации данных.............................................................................182 Что такое сводные диаграммы?..............................................................182 Создание сводной диаграммы................................................................183 Кнопки полей сводной диаграммы..................................................186 Создание сводной диаграммы с нуля...............................................187 Правила и ограничения сводных диаграмм.........................................187 Изменения в исходной сводной таблице оказывают влияние на сводную диаграмму........................................................................187 Расположение полей в сводной таблице может не подойти для сводной диаграммы.....................................................................187 Ограничения форматирования в Excel............................................190 Альтернативы сводным диаграммам....................................................194 Способ 1: сохранение сводной таблицы в виде значений............195 Способ 2: удаление базовой сводной таблицы...............................196 Способ 3: распространение изображения сводной таблицы........196 Способ 4: использование в качестве источника для диаграммы ячеек, связанных со сводной таблицей...............197 Использование условного форматирования в сводных таблицах.....................................................................................................199 Пример использования условного форматирования....................199 Программируемые сценарии условного форматирования..........202 Создание пользовательских правил условного форматирования.......................................................................................202 Глава 8 Анализ разрозненных источников данных при помощи сводных таблиц........................................................................................209 Использование модели данных..............................................................210 Строим свою первую модель данных...............................................210 Управление связями в модели данных............................................213 Добавление таблиц в модель данных...............................................214 Ограничения модели данных............................................................214 Построение сводной таблицы на основе внешних источников данных........................................................................................................215 Создание сводной таблицы на основе данных из Microsoft Access.....................................................................................................216 Создание сводной таблицы на основе данных из Microsoft SQL Server..............................................................................................219 Использование Power Query для извлечения и преобразования данных...............................................................................................223 Основы Power Query............................................................................223 Примененные шаги.............................................................................229 Обновление данных в Power Query...................................................232 Управление существующими запросами.........................................233 Действия со столбцами.......................................................................234 Действия с таблицами.........................................................................237 Типы подключения в Power Query....................................................239 Еще один пример использования Power Query...............................241 Глава 9 Распространение дашбордов с помощью Power BI..........................245 Знакомство с Power BI Desktop................................................................245 Подготовка данных в Excel.................................................................246 Импорт данных в Power BI.................................................................246 Знакомимся с Power BI........................................................................247 Подготовка данных в Power BI...........................................................249 Определение синонимов для полей в Power BI Desktop................250 Построение интерактивных отчетов в Power BI Desktop....................252 Ваша первая визуализация................................................................252 Ваша вторая визуализация................................................................256 Перекрестная фильтрация элементов визуализации....................257 Создание иерархий.............................................................................258 Импорт пользовательских визуальных элементов........................259 Публикация в Power BI.............................................................................261 Разработка для мобильных устройств..............................................261 Публикация в рабочую область.........................................................262 Глава 10 Использование формул кубов с моделью данных или данными OLAP..................................................................................264 Преобразование сводных таблиц в формулы кубов............................265 Введение в технологию OLAP.................................................................275 Подключение к кубу OLAP.......................................................................276 Понимание структуры куба OLAP..........................................................279 Ограничения сводных таблиц OLAP......................................................281 Создание автономного куба....................................................................281 Слом парадигмы сводных таблиц с помощью функций кубов..................284 Обзор функций кубов.........................................................................284 Добавление вычислений в сводные таблицы OLAP..............................286 Создание вычисляемых мер..............................................................287 Создание вычисляемых элементов..................................................291 Управление вычислениями OLAP.....................................................294 Выполнение анализа «что если» применительно к данным OLAP................................................................................................294 Глава 11 Эпоха моделей данных и Power Pivot....................................297 Замена функции ВПР (VLOOKUP) на модель данных.............................297 Скрытые возможности моделей данных...............................................302 Подсчет уникальных значений в сводной таблице................................302 Включение отобранных фильтром элементов в промежуточные итоги.....................................................................304 Расчет медианы в сводной таблице при помощи меры DAX...................307 Вывод текста в области значений.....................................................309 Обработка больших данных при помощи Power Query......................310 Добавление столбца при помощи Power Query...............................312 Power Query – это как запись макросов в Excel, но лучше.............314 Минуя Excel – в модель данных.........................................................315 Добавление связанной таблицы........................................................316 Определение связей между таблицами с использованием представления диаграммы................................................................317 Добавление вычисляемых столбцов в окне Power Pivot................318 Сортировка одного столбца на основании другого........................319 Создание сводной таблицы на основе модели данных.................320 Использование продвинутых техник Power Pivot................................320 Сложные связи.....................................................................................321 Использование логики операций со временем..............................322 Преодоление ограничений моделей данных........................................324 Другие преимущества Power Pivot....................................................325 Создавайте все сводные таблицы в будущем с использованием моделей данных..................................................326 Глава 12 Анализ географических данных с помощью 3D-карты...................327 Подготовка данных для 3D-карты..........................................................327 Геокодирование данных.....................................................................328 Построение гистограммы на 3D-карте............................................330 Навигация на карте.............................................................................331 Пометка отдельных точек данных....................................................333 Создание круговой и пузырьковой диаграмм на карте.................333 Использование тепловой карты и региональной карты...............333 Исследование настроек 3D-карты.....................................................335 Тонкая настройка 3D-карт.................................................................335 Комбинирование двух наборов данных..................................................337 Анимация данных с течением времени...................................................337 Создание обзора...................................................................................338 Создание видео с 3D-картой...................................................................339 Глава 13 Улучшение отчетов сводных таблиц при помощи макросов..........343 Использование макросов совместно со сводными таблицами.....................343 Запись макросов....................................................................................344 Создание пользовательского интерфейса с помощью элементов управления формы................................................................346 Изменение записанных макросов для добавления функционала...............349 Добавление полосы прокрутки..............................................................350 Создание макросов с помощью Power Query............................................353 Глава 14 Использование VBA и TypeScript для создания сводных таблиц.................................................................................................364 Активация VBA в вашем Excel.................................................................365 Использование форматов файлов с включенными макросами..................366 Использование редактора Visual Basic..................................................366 Инструменты Visual Basic в Excel...........................................................367 Использование средства записи макросов...........................................368 Введение в объектно ориентированный код........................................369 Полезные трюки при написании кода...................................................369 Поддержка диапазонов любых размеров........................................369 Использование объектных переменных..........................................371 Использование With и End With для сокращения кода.................372 Версии Excel.................................................................................372 Создание сводной таблицы в Excel с помощью VBA...........................373 Добавление полей в область данных................................................375 Форматирование сводной таблицы..................................................376 Обход ограничений сводных таблиц.....................................................379 Заполнение пустых ячеек в области данных...................................379 Заполнение пустых ячеек в области строк......................................379 Предотвращение ошибок при вставке или удалении ячеек.........379 Управление итогами...........................................................................380 Преобразование сводной таблицы в значения...............................382 Новый уровень сводных таблиц: создание отчета о выручке по категориям.................................................................................385 Использование табличного макета...................................................387 Агрегация данных по датам...............................................................388 Избавление от пустых ячеек..............................................................390 Контролируем порядок сортировки с помощью AutoSort............390 Изменение числового формата по умолчанию..............................391 Исключение промежуточных итогов для нескольких полей в области строк.....................................................................................392 Копирование сводной таблицы в новую рабочую книгу в виде значений...................................................................................393 Итоговое форматирование................................................................394 Добавление промежуточных итогов для разрывов страниц........394 Собираем все вместе...........................................................................396 Вычисления в сводных таблицах............................................................399 Решение проблем с несколькими полями данных.........................399 Использование альтернативных типов вычислений.....................401 Использование вычисляемых полей................................................402 Использование вычисляемых объектов...........................................404 Вычисляемые группы..........................................................................405 Использование дополнительных вычислений.........................................407 Использование продвинутых техник при работе со сводными таблицами..................................................................................................409 Использование метода AutoShow при создании отчетов для руководства...................................................................................410 Использование свойства ShowDetail для фильтрации набора данных.....................................................................................412 Создание отдельных отчетов для каждого региона или модели...........................................................................................414 Ручная фильтрация по двум и более элементам в сводной таблице..................................................................................................418 Использование концептуальных фильтров.....................................419 Использование фильтра поиска........................................................422 Настройка срезов для фильтрации сводных таблиц......................423 Использование модели данных Excel....................................................426 Добавление таблиц в модель данных...............................................426 Создание связи между таблицами....................................................427 Определение кеша сводной таблицы и создание сводной таблицы.................................................................................................428 Добавление полей в сводную таблицу..............................................428 Добавление числовых полей в область значений...........................429 Собираем все вместе...........................................................................430 Применение TypeScript в Excel Online для создания сводных таблиц.........................................................................................................432 Глава 15 Продвинутые техники работы со сводными таблицами.................436 Совет 1: автоматическое обновление сводных таблиц.......................437 Совет 2: одновременное обновление всех сводных таблиц в рабочей книге.........................................................................................437 Совет 3: сортировка элементов данных в уникальном порядке, а не по возрастанию или убыванию......................................................438 Совет 4: использование (или отказ от использования) пользовательских списков при сортировке сводных таблиц.............439 Совет 5: изменение настроек по умолчанию для будущих сводных таблиц.........................................................................................442 Совет 6: сохранение сводной таблицы в виде значений....................442 Совет 7: заполнение пустых ячеек в области строк значениями......443 Вариант 1: использование опции повторения всех подписей элементов.............................................................................................444 Вариант 2: использование опции выделения ячеек...............................445 Совет 8: добавление в сводную таблицу поля с рангом......................447 Совет 9: сокращение размера отчетов сводных таблиц.....................448 Удаление листа с источником данных.............................................449 Совет 10: создание автоматически расширяемого диапазона данных........................................................................................................449 Совет 11: сравнение данных в таблицах с использованием сводных таблиц.........................................................................................450 Совет 12: использование автофильтра в сводной таблице.............................452 Совет 13: использование разных числовых форматов данных в одном поле..............................................................................................455 Совет 14: форматирование отдельных значений в сводной таблице.......................................................................................................457 Совет 15: форматирование разделов сводной таблицы...................................458 Совет 16: создание частотного распределения с помощью сводной таблицы.......................................................................................460 Совет 17: использование сводных таблиц для создания наборов данных на отдельных вкладках...............................................462 Совет 18: наложение ограничений на сводные таблицы и поля.......464 Ограничения на сводные таблицы...................................................464 Ограничения на поля сводных таблиц.............................................465 Совет 19: использование сводных таблиц для создания наборов данных в отдельных рабочих книгах.....................................................467 Совет 20: вывод разницы в процентах между годами........................469 Совет 21: двойной ВПР с помощью Power Query..................................472 Совет 22: использование срезов для управления данными из двух разных наборов...........................................................................478 Совет 23: форматирование срезов.........................................................481 Глава 16 Единство и борьба противоположностей функции ПОЛУЧИТЬ. ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ..............................................................485 Исцеление от ненависти к функции ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ................................................................................486 Ручной ввод формулы.........................................................................489 Отключение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ. ТАБЛИЦЫ..............................................................................................490 Зачем эта возможность включена по умолчанию?..................................491 Использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ. ТАБЛИЦЫ для решения проблем в сводных таблицах........................492 Создание страшной сводной таблицы.............................................494 Создание оболочки отчета.................................................................496 Заполнение оболочки отчета данными...........................................499 Обновление отчета для будущих месяцев.......................................503 Глава 17 Создание сводных таблиц в Excel Online..................................505 Как зарегистрироваться в Excel Online..................................................506 Создание сводной таблицы в Excel Online............................................508 Изменение настроек сводной таблицы в Excel Online..............................511 А как же остальные возможности?.........................................................515 Глава 18 Создание отчетов с помощью формул динамических массивов и Power Query..........................................................................517 Использование расширенных фильтров и таблиц данных........................518 Получение списка уникальных значений при помощи расширенного фильтра.......................................................................518 Агрегация выручки с использованием функции БДСУММ (DSUM).............................................................................................520 Повторение функции БДСУММ (DSUM) для каждой комбинации сектора и региона.........................................................521 Преимущества и недостатки этого метода..........................................522 Использование динамических массивов..............................................523 Получение списка уникальных значений при помощи динамических массивов.....................................................................523 Заполнение данных о выручке с помощью функции СУММЕСЛИМН (SUMIFS)....................................................................524 Преимущества и недостатки этого метода......................................525 Сведение данных при помощи Power Query.........................................526 Получение исходных данных в Power Query...................................526 Расчет выручки по секторам и регионам в Power Query...............527 Сортировка и сведение данных в Power Query...............................528 Заключительные шаги очистки.........................................................529 Преимущества и недостатки этого метода......................................531 Глава 18 Отмена свертывания данных с помощью Power Query...................532 Почему данные в заголовках – это плохо?............................................532 Использование отмены свертывания данных в Power Query для очистки................................................................................................534 Отмена свертывания в таблицах с двумя уровнями заголовков........................537 Отмена свертывания из ячейки с разделителями в новые строки.........................................................................................................545 Заключение................................................................................................547 Послесловие от автора.................................................................................548 Предметный указатель.................................................................................550
[only-soft.org].t192734.torrent
Торрент:
Зарегистрирован
[ 2024-03-02 00:40 ]
18 KB
Статус:
√проверено
Скачан:
1 раз
Размер:
112 MB
Оценка:
(Голосов: 0)
Поблагодарили:
0
Джелен Билл - Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 [2023, PDF, RUS] скачать торрент бесплатно и без регистрации
Вы не можете начинать темы Вы не можете отвечать на сообщения Вы не можете редактировать свои сообщения Вы не можете удалять свои сообщения Вы не можете голосовать в опросах Вы не можете прикреплять файлы к сообщениям Вы можете скачивать файлы