Общие итоги сводной таблицы по другой формуле. Сортировка данных в подписях столбцов или строк. Размещение полей на оси страниц

15.10.2018 Офисные программы

Цель работы:

1. Общие сведения

Сводная таблица является специальным типом таблицы, которая подытоживает информацию из конкретных полей списка или БД. Создание и организация таблицы производится с помощью мастера. Под организацией таблицы понимают построение макета и задание типа вычислений. После построения таблицы можно изменить ее так, чтобы рассмотреть данные под другим углом зрения. Таким образом, таблицы являются мощным аналитическим средством. Сводная таблица связана с исходными данными, но автоматически не перечитывается при их изменении. Для обновления таблицы нужно воспользоваться кнопкой Обновить данные на панели инструментов Сводные табл и цы или пунктом меню Обновить данные контекстного меню.

Когда вы впервые входите в сводный редактор

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

Понимание элементов сводной таблицы

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

2. Терминология сводных таблиц

Ось . Служит для обозначения ориентации полей в сводной таблице (ось столбцов, ось строк, ось страниц).

Источник данных . Список, на основе которого получена сводная таблица.

Заголовок поля. Надпись, описывающая содержимое поля.

Элемент. Элемент поля, т. е. Его (поля) конкретные значения.

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

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

Отображение и скрытие детальных данных

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

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

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

Чтобы изменить порядок элементов поворота в категории элементов с поворотным элементом

Перетащите элемент внутри его элемента элемента сворачивания, чтобы изменить его.

Передача элементов поворота между категориями элементов поворота

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

Настройка элементов для сводной таблицы

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

3. Создание сводных таблиц

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

Шаг 1 : задание типа источника данных .

Можно использовать один из четырех типов источников данных.

список Excel ;

внешний источник данных, доступный через Microsoft Qnery ;

Сортировка данных в подписях столбцов или строк

Фильтр времени всегда присутствует, когда вы создаете опорный стержень; вы не можете удалить его. Предельные фильтры позволяют вам каким-то образом ограничить количество результатов, возвращаемых точкой поворота. Он определяет временной диапазон, для которого результат возвращает результат. . Вот пример ограничения фильтра: скажите, что у вас есть интернет-магазин, предлагающий сотни продуктов, и вы хотите узнать больше о предметах, которые были приобретены за прошедшую неделю. Вы можете создать сводный отчет, который разбивает общее количество покупок события по названию продукта и через это быстро увидеть, какие из ваших продуктов были лучшими продавцами за этот период.

несколько диапазонов консолидации (отдельных списков Excel );

другую сводную таблицу.

Шаг 2: указание местонахождения исходных данных

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

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

Добавление и удаление полей

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

Чтобы упростить чтение таблицы, вы можете добавить столбец разделяемой строки, который показывает цену и столбец «Столбец», который показывает сумму цены. Вот как выглядит этот пример с данными, подобными данным учебника модели данных, отсортированным по «сумме цены». Обратите внимание, что продукты с наиболее успешными покупками не находятся в верхней части списка.

Шаг 3: задание макета таблицы

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

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

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

Настраиваемые вычисления, которые можно применить

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

Как ограничить: ваши варианты самые высокие и самые низкие.
Максимальное количество возвращаемых результатов - это может быть любое число.
Функция статистики применяется для ограничения.
Доступные функции здесь зависят от типа поля, которое вы хотите ограничить.

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

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

Настроить элемент разделенной строки или разделения столбца

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

Параметры конфигурации, общие для разделяемых строк и разделенных элементов столбца, независимо от типа поля. Должен быть неотрицательным; значение 0 означает, что максимум не установлен. . Параметры конфигурации, специфичные для разделения элементов строки независимо от типа поля.

Шаг 4: указание места для размещения таблицы.

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

на новом рабочем месте,

на существующем месте. В этом случае нужно указать диапазон ячеек на месте, куда будет помещена таблица.

После нажатия кнопки Готово Excel создает сводную таблицу. Следует отметить, что мастер сводных таблиц автоматически создает промежуточные и общие итоги, если не указано обратное).

Используйте это, чтобы переопределить имя поля с другой текстовой строкой для целей отчетности. Например, вы можете использовать его, чтобы убедиться, что поле под названием «имя продукта» отображается как «Продукт» в своде. Доступные значения: по умолчанию, по убыванию и по возрастанию. По умолчанию используется значение по умолчанию. Когда значение Сортировка «Убывание» или «Восхождение», строки будут отсортированы по значению первого элемента «Значения столбцов», который выводит метрическое значение.

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

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

4. Реорганизация сводной таблицы

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

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

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

5. Размещение полей на оси страниц

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

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

Настроить элемент значения столбца

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

Управление отображением и форматом сводной таблицы

Для числовых полей доступны следующие значения: Сумма, График, Среднее значение, Макс. Мин. Стандартное отклонение и Список различимых значений. Примечание. Вы можете управлять разбиением на страницы результатов сводной таблицы с помощью выпадающего меню.

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

Даже если св. Таблица имеет ось страниц, вся таблица хранится на одном месте книги. С помощью кнопки Отобразить страницы на панели инструментов Сводные таблицы можно создать ряд связанных сводных таблиц, каждая из которых будет отображать один элемент поля страницы. Excel создает новые места для каждого элемента поля Год , помещая их перед местом сводной таблицы и используя заголовки элементов в качестве имен листов.

Что будем делать с полученным материалом

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

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

6. Выделение элементов сводной таблицы

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

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

Размещение полей на оси страниц

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

Чтобы включить или выключить структурное выделение, нужно выделить часть сводной таблицы. Затем выбрать команду Выделить из контекстного меню.

Вкл./выкл. структ. Выделение

Если кнопка возле команды Разрешить выделение нажать, то становятся доступными три верхние команды меню. Эти команды позволяют выделить только заголовки, Только данные, Заголовки и данные.

Чтобы отформатировать сводную таблицу и сохранить форматирование, вы должны выбрать всю таблицу и выбрать «Включить выбор». Это активирует выбор структур и требуется для форматирования или для уточнения формулы расчетного элемента. После нажатия форматируйте таблицу по желанию.

Вы можете изменить формат номеров, отображаемых в области данных. Выберите любую ячейку и выберите. Это идентично вкладке «Число», отображаемой в диалоговом окне. Вы можете изменить формат номера. Единственное реальное различие между Сводными таблицами и стилями таблицы - это количество элементов в сводном стиле. В то время как стили таблиц имеют 13 элементов, стили сводной таблицы имеют 25 элементов. Дополнительные элементы связаны с тем, что сводные таблицы более сложны, чем таблицы, поэтому для определения форматирования доступно больше элементов таблицы.

Чтобы выделить всю сводную таблицу, нужно выбрать команду Таблица целиком.

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

6. Форматирование сводной таблицы

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

Условное форматирование в сводной таблице запрещено. Не сохраняются форматы границ.

Можно выполнить Автоформат: Формат/автоформат

Чтобы применить числовой формат сразу по всем ячейкам области данных сводной таблицы, нужно выделить ячейку области данных, нажать кнопку Поле сводной таблицы (пункт Поле контекстного меню). В окне Вычисление поля сводной таблицы кнопка Формат позволит задать нужный числовой формат.

6.1. Отображение пустых ячеек в сводной таблице

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

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

7. Добавление и удаление полей

Вызвать 3-е окно мастера сводных таблиц.

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

8. Переименование полей

Чтобы переименовать поле или элемент, нужно его выделить и ввести новое значение.

9. Сортировка элементов

автосортировка: сортировка элементов пол с помощью значений данных:

1.1) выделить элемент поля или кнопку поля, которое нужно отсортировать;

1.2) выбрать команду Поле из контекстного меню;

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

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

Чтобы включить автосортировку, нужно установить переключатель По возрастанию или по Убыванию.

Автосортировка ---------- все изменение в иск. данных и корректирует отображение данных в сводной таблице в соответствии с параметрами автосортировки.

В списке с помощью поля нужно выбрать нужное поле (поле данных).

простая сортировка: сортировка элементов поля по заголовкам.

При отключенной автосортировке можно испытать обычные команды сортировки:

Данные/Сортировка

При включенной автосортировке нужно поступить одним из следую9их способов:

выключить автосортировку, используя переключатель Вручную д. О. Дополн. Параметры св. Таблицы. А затем испытать обычные команды сортировки Excel ;

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

использование нестандартного порядка сортировки:
создать свой порядок на ----- Списки, д. о. Параметры, вызываемого командой Сервис/Параметры. Затем выбрать команду Данные/сортировка. В появившемся д. о. Нажать кнопку Параметры и задать порядок (свой) сортировки в списке Сортировка по первому ключу;
можно использовать простую замену заголовков: на месте одного написать другое, и они таким образом поменяются местами.

10. Отображение и скрытие детальных данных

Перемещение полей на ось страниц является одним из способов изменения св. Табл., позволяющим сосредоточить внимание на некоторых детальных данных.

отображение и скрытие элементов полей:
выделить поле,
выбрать команду Поле из контекстного меню,
в списке Скрыть элементы выбрать элемента, которые не будут отображаться в св. Табл.
нажать ОК.

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

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

Автоотображение. Можно отображать в поле можно несколько наибольших и наименьших элементов, основываясь на значениях в области данных:
выделить заголовок или элемент поля,
выбрать команду Поле,
выбрать кнопку Далее,
чтобы вкл./выкл. автоотображение нужно установить переключатель автоматическое/вручную, выбрать подходящие параметры отображения в раскрывающихся списках.
Отображение/скрытие элементов внутреннего поля

Скрыть/отобразить элементы внутреннего поля можно дважды щелкнув на заголовке внешнего поля,

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

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

11. Группировка данных

Создание группировка элементов
выделить элементы поля, которые нужно объединить в группу,
в контекстное меню выбрать команду Группа и структура/Группировать.

Excel создает новое поле, в котором сгруппирует выделенные элементы в новый элемент Группа 1. Можно этот элемент переименовать.

Группировка числовых элементов

Чтобы сгруппировать числовые элементы поля, нужно выделить элемент поля и нажать кнопку Группировать на панели инструментов Сводной таблицы

Появляется окно

12. Использование общих и промежуточных итогов

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

Общие итоги

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

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

Промежуточные итоги

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

дважды щелкнуть по заголовку поля,
в окне диалога выбрать одну или несколько функций и нажать ОК.

Удалить: установить переключатель Нет.

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

Применение нескольких итоговых функций к одному полю

Можно применить количество итоговых функций. Для этого нужно вызвать 3-е окно мастера сводных таблиц и перетащить в область данных диаграммы макета кнопку этого поля еще раз. Затем дважды щелкнуть на копии заголовка и сменить организацию.

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

установить курсор на область данных и в контекстовом меню выбрать команду Поле или используя 3-е окно Мастера сводных таблиц, щелкнуть дважды по полю в области данных,
в диалоговом окне Вычисление поля сводной таблицы воспользоваться кнопкой Дополнительно.
группа Дополнительные вычисления позволяет задать специальные вычисления.

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

Доля: значения ячеек области данных отображаются в процентах к заданному элементу.

Приведенное отличие: значения ячеек области данных отображаются в виде разности с заданным элементов, нормированной к значению этого элемента. Проще Доля - 100 % .

С нарастающим итогом в поле: значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Сделать с нарастающим итогом: по год: 1 квартал 1996 = знач. 1996 знач. По квартал: в течение года поквартально добавляются данные.

Доля от суммы по строке: отображаются в процентах от итога по строке.

Доля от суммы по столбцу: отображаются в процентах от итога по столбцу.

Доля от общей суммы: отображаются в процентах от общей суммы.

Индекс:

Создание вычисляемого поля (в области данных)

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

Вычислить размер отчислений в 15% от поля Получено.

Создание вычисляемого элемента (к полям добавиться новое)

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

Выделение только заголовков или только данных. Выделение всей таблицы.

Форматирование таблиц. Отображение пустых ячеек. Правая кнопка Параметры.

Добавление и удаление полей. Добавить новое поле или убрать. Добавить поле количество в область данных.

Переименование полей.

Сортировка элементов

простая (обычная) сортировка Данные/Сортировка;

автосортировка, конт. Меню/Поле

Нестандартный порядок сортировки Данные/Сортировка

Отображение и открытие детальных данных

Группировка

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

изменение функции общих итогов

изменение итогов (дважды щелкнуть на Заголовке поля, выбрать одну или несколько промежуточных итогов),

точно также для самого внутр. Поля.

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

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

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

Применение нескольких итоговых функций к одному полю (нужно в мастере поместить несколько экз.-ров поля). Дополнительные вычисления нарастают по кварталу. Создание вычисляемого поля выделить ячейку, выбрать формулы, вычисляемое поле.

Создание отчета сводной таблицы или сводной диаграммы

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

Выберите ячейку из диапазона ячеек внутри таблицы Microsoft Office Excel.

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

Выберите источник данных.

Выберите пункт Выбрать таблицу или диапазон .

Введите в поле Таблица или диапазон диапазон ячеек или ссылку на таблицу.

Если перед запуском мастера была выбрана ячейка в диапазоне ячеек или установлена точка вставки в таблице, соответствующий диапазон ячеек или ссылка на таблицу будут помещены в поле Таблица или диапазон .

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

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

Примечание. Если диапазон ячеек находится на другом листе той же книги или в другой книге, введите имя книги и листа, используя следующий синтаксис: ([имякниги]имялиста!диапазон).

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

на новый лист , начиная с ячейки A1, щелкните пункт На новый лист .

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

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

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

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

Структура отчета сводной таблицы

Отчет сводной таблицы	Описание
ПОЛЯ СТРАНИЦ (Фильтр отчетов)	Используется для фильтрации всего отчета на основе выбранных элементов фильтра отчета.
ПОЛЯ СТОЛБЦОВ (Названия столбцов)	Используются для вывода полей в виде столбцов сверху отчета. Столбец, расположенный ниже, вложен в столбец, непосредственно предшествующий ему.
ПОЛЯ СТРОК (Названия строк)	Используются для вывода полей в виде строк сбоку от отчета. Строка, расположенная ниже, вложена в строку, непосредственно предшествующую ей.
ЭЛЕМЕНТЫ ДАННЫХ (Значения)	Используются для вывода суммарных числовых данных.

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

После создания отчета сводной таблицы или сводной диаграммы добавить к нему поля можно при помощи списка полей сводной таблицы. Для того, чтобы изменить отчет сводной таблицы или диаграммы, используйте список полей для изменения порядка полей и их удаления. По умолчанию список полей сводной таблицы имеет две секции: секцию полей в верхней части - для добавления и удаления полей, и секцию макета в нижней части - для изменения порядка полей и их положения. Можно закрепить список полей сводной таблицы у одной из сторон экрана и изменить его горизонтальный размер. Можно также снять прикрепление списка полей сводной таблицы к экрану, в этом случае можно будет изменить его размер как по вертикали, так и по горизонтали.

Примечания

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

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

Добавление полей

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

Для добавления полей к отчету можно также выполните одно или несколько следующих действий:

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

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

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

Изменение положения полей

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

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

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

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

Удаление полей

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

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

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

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

Данные в поле значений суммируют данные источников в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых - функция СЧЁТ. Однако суммирующая функция может быть изменена. Можно также создать настраиваемое вычисление.

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

На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле , а затем - кнопку Параметры поля . (Или раскройте ниспадающее меню соответствующего поля в области значений)

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

В поле Имя источника отображается имя источника данных поля.

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

Перейдите на вкладку Операция .

Выберите нужную итоговую функцию в списке Операция .

Доступные функции суммирования

Функция	Результат
	Сумма значений. Эта функция используется по умолчанию для числовых значений.
Количество	Число значений. Функция сложения «»Количество работает так же, как функция книги СЧЁТЗ. Является функцией по умолчанию для нечисловых значений.
	Среднее арифметическое значение.
Максимум	Наибольшее значение.
	Наименьшее значение.
Произведение	Произведение значений.
Количество чисел	Число значений, которые представляют собой числа. Функция суммирования «Количество чисел» работает так же, как функция листа СЧЁТ.
Смещенное отклонение	Оценка стандартного отклонения совокупности, где в качестве примера используется выборка данных из совокупности.
Несмещенное отклонение	Смещенная оценка стандартного отклонения генеральной совокупности по выборке значений.
Смещенная дисперсия	Оценка дисперсии совокупности, где в качестве примера используется выборка данных из совокупности.
Несмещенная дисперсия	Оценка дисперсии совокупности, где в качестве примера используются все значения совокупности.

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

При необходимости можно использовать настраиваемое вычисление следующим способом.

Откройте вкладку Дополнительные вычисления .

В поле Дополнительные вычисления выберите нужный тип вычисления.

Настраиваемые вычисления, которые можно применить

Функция	Результат
	Выключение настраиваемого вычисления.
	Отображение значения в виде разницы по отношению к значению элемента в поле.
	Отображение доли в процентах от значения элемента в поле.
Приведенное отличие	Отображение значения в виде разницы в процентах по отношению к значению элемента в поле.
С нарастающим итогом в поле	Отображение значений в виде нарастающего итога для последовательных элементов в поле.
Доля от суммы по строке	Отображение значений в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Доля от суммы по столбцу	Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
Доля от общей суммы	Отображение значений в процентах от общей суммы значений или элементов данных в отчете.
Указатель	Производит вычисления следующим образом: ((Значение в ячейке) x (Общий итог)) / ((Итог строки) x (Итог столбца))

Выберите значения в полях поле и элемент, если они доступны для нужных вычислений.

Сортировка данных в подписях столбцов или строк

Выберите столбец или строку в отчете сводной таблицы или сводной диаграммы.

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

На вкладке Параметры в группе Сортировка кнопку Сортировка.

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

Чтобы выбрать поле для сортировки по возрастанию, выберите параметр По возрастанию (от А до Я) по полю, а затем выберите поле в раскрывающемся списке.

Чтобы выбрать поле для сортировки по убыванию, выберите параметр По убыванию (от Я до А) по полю, а затем выберите поле в раскрывающемся списке.