Сводные таблицы 1 Инструмент Сводные таблицы l

Зарегистрируйтесь, чтобы просмотреть полный документ!

Сводные таблицы 1

Инструмент Сводные таблицы l l Инструмент Сводная таблица из меню Вставка является чрезвычайно мощным и удобным средством анализа и трансформации данных. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация. 2

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

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

Формирование сводных таблиц l l l Сначала выбирают поля добавления в отчет, При этом автоматически эти поля фиксируются в нижнем окне, И формируется сводная таблица 5

Этап 1 Указание вида источника данных: в качестве источника могут быть использованы • списки (базы данных Excel); • внешний источник данных; • несколько диапазонов консолидации; • данные из другой сводной таблицы. В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим наиболее распространенный случай использования списков собственной таблицы Excel. 6

Этап 2 l l Указание адреса (диапазона ячеек, содержащего исходные данные) исходных данных. Список (база данных Excel) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде [имя_книги]имя_листа!диапазон ячеек Запрос появляется при нажатии кнопки Далее. Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопка Обзор, в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек. 7

Построение структуры сводной таблицы. После нажатия кнопки Далее появляется следующее окно Мастера, в котором нужно нажать кнопку Макет. Структура сводной таблицы состоит из следующих областей, определяемых в макете: страница — на ней размещаются поля, значения которых обеспечивают отбор записей на первом уровне; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи — сверху вниз; страницу определять необязательно; столбец — поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно; строка — поля размещаются сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку необязательно; данные — поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно. В правой части окна отображены кнопки-названия всех полей, участвующих в сводке, которые с помощью мыши можно перетаскивать в свободные сейчас области структуры макета документа. В области Столбец и Строка переносятся поля, сведения о которых будут находиться на соответствующих осях формируемой таблицы Каждое поле размещается только один раз в областях: страница, строка или столбец. По этим полям можно формировать группы и получать итоговые значения в области данные — группированные поля. В области данные могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данные. Для каждого такого поля задается вид функции и выполняется необходимая настройка. Этап 3 8

Этап 4 l Указание местоположения готовой сводной таблицы. Нужно указать, куда поместить таблицу, на новый лист или на существующий Ее местоположение, в общем, безразлично (указать ячейку, например, А 13), 9

Пример ФИО Товар Выручка Дата ПЕТР стул 10 01. янв ИВАН стол 20 25. янв ОЛЕГ шкаф 30 10. фев ПЕТР стул 20 12. фев ОЛЕГ стол 50 25. фев ПЕТР стол 100 02. мар ОЛЕГ стул 40 21. мар ИВАН шкаф 60 25. мар ИВАН стул 80 12. апр ОЛЕГ шкаф 10 16. апр l l Определим структуру сводной таблицы. Положим, надо создать таблицу где по вертикали будут представлены даты продаж, а по горизонтали суммы выручки для каждого продавца с разбивкой по видам проданных товаров. 10

Мастер сводных таблиц l l l Поля ФИО и Товар переносятся в область столбец, поле Дата в область Строка, а поле Выручка – в область Данные. При перемещении в о область Данные появляется кнопка Сумма по полю Выручка, указывающая на то, что данные будут суммироваться. Если вы хотите выбрать другую итоговую функцию, следует дважды щелкнуть по этой кнопке, после чего появится меню доступных функций. Далее нажимаем Готово. 11

Манипулирование данными Сводная таблица является не просто отображением данных, но и «живым» объектом со своими средствами управления. В этом легко убедиться, дважды щелкнув мышью по любой из клеток – сразу последует какая-то реакция. Кроме того, здесь имеются три кнопки управления (затемнены) ДАТА, ФИО. ТОВАР. Манипулирование данными здесь возможно как непосредственно в таблице, так и с помощью меню Данные или контекстного меню. Попробуем это сделать. l l Часто бывает нужно видеть сводные данные, не расписанные по конкретным дням, а группированные по месяцам. Здесь это легко сделать, если нажать правую кнопку мыши на любой клетке, содержащей конкретную дату. В появившемся меню выбрать Группа и структура, а в меню следующего уровня – пункт Группировать. 17

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

Удаление кнопок l. Выполним еще одну манипуляцию – из сводной таблицы удалим кнопку Товар. Для этого нужно отбуксировать мышью кнопку в любое место (кроме пространства над таблицей) за пределы сводной таблицы. l. В момент, когда кнопка уходит за ее границы, на ней появляются символы перечеркивания. l. Если отпустить мышь, кнопка исчезнет. Таблица приобрела более обобщенный характер (сведения о товаре не предъявляются). l. Восстановить – меню Данные-Сводная таблица. Мастер-вернуть на место. 19

Скрытие подчиненных полей l l Во многих случаях размер сводной таблицы оказывается так велик, что затрудняет обзор и анализ данных пользователем. Скрыть подчиненные поля можно двойным щелчком на старшем поле (пример, щелкнем по полю ОЛЕГ). Вернуть – снова двойной щелчок. 20

Скрытие всех подчиненных полей l l l Если нужно скрыть все подчиненные поля, нужно перенести это подчиненное поле в любое место над таблицей, даже если она находится в самой первой строке (смотрим с ФИО), при этом курсор принимает форму каскада из трех окон. Вернуть также. 21

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

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

Средства настройки сводной таблицы В заключении рассмотрим средства настройки сводной таблицы, которые вызываются кнопкой Параметры через контекстное меню. Задается имя сводной таблицы (по умолчанию (сводная таблица 1). В разделе Формат указываются параметры, определяющие внешний вид таблицы: l общая сумма по строкам — в сводной таблице формируется итоговый столбец; l сохранить данные вместе с таблицей — сохраняется не только макет, но и результат построения сводной таблицы, на который можно ссылаться из других таблиц. 24

Быстрый способ доступа к средствам управления сводной таблицы можно получить, если вызвать на экран группу пиктограмм Сводные таблицы через меню ВИД-Панели инструментов – Сводные таблицы. 25

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

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

Скачать презентацию Сводные таблицы 1 Инструмент Сводные таблицы l

Svod_tab.pptx

Количество слайдов: 27