Цель работы:

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

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

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

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

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

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

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

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

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

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

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

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

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

— список Excel ;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Общие итоги

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Индекс:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Группировка

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

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

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

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

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

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

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

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

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

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

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

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

Областью данных называется часть сводной таблицы, содержащая итоговые данные. В ячейках области данных отображаются итоги для элементов полей строки или столбца. Значения в каждой ячейке области данных соответствуют исходным данным. В этом примере в ячейке C6 суммируются все записи исходных данных, содержащие одинаковое название продукта, распространителя и определенный квартал Мясо, ТОО Мясторг и КВ2. Элементы поля - это подкатегории поля сводной таблицы. В данном примере значения Мясо и Дары моря являются элементами поля в поле Продукты.

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

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

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

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

Конец работы -

Эта тема принадлежит разделу:

Обработка табличной информации с помощью сводных таблиц средствами Microsoft Excel

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

Если Вам нужно дополнительный материал на эту тему, или Вы не нашли то, что искали, рекомендуем воспользоваться поиском по нашей базе работ:

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

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

Все темы данного раздела:

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

Детальные данные сводной таблицы и выполняемые над ними действия
Детальные данные сводной таблицы и выполняемые над ними действия. Детальные данные являются подкатегорией сводной таблицы. Эти элементы сводной таблицы являются уникальными элементами таблицы или с

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?

Совет 1. Автоматическое обновление сводных таблиц

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

1. Щелкните правой кнопкой мыши на сводной таблице и в контекстном меню выберите пункт Параметры сводной таблицы .
2. В появившемся диалоговом окне Параметры сводной таблицы выберите вкладку Данные .
3. Установите флажок Обновить при открытии файла .

Рис. 1. Включите опцию Обновить при открытии файла

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

Скачать заметку в формате или , примеры в формате (файл содержит код VBA).

Совет 2. Одновременное обновление всех сводных таблиц книги

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

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

Способ 3. Воспользуйтесь кодом VBA для обновления всех сводных таблиц в рабочей книге по требованию. Данный подход предусматривает использование метода RefreshAll объекта Workbook. Для использования этой методики создайте новый модуль и введите следующий код:

Sub Refresh_All()

ThisWorkbook.RefreshAll

Совет 3. Сортировка элементов данных в произвольном порядке

На рис. 2 показан заданный по умолчанию порядок отображения регионов в сводной таблице. Регионы отсортированы в алфавитном порядке: Запад, Север, Средний Запад и Юг. Если ваши корпоративные правила требуют, чтобы сначала отображался регион Запад, а затем - регионы Средний Запад, Север и Юг, выполните ручную сортировку. Просто введите Средний Запад в ячейку С4 и нажмите клавишу Enter . Порядок сортировки регионов изменится.

Совет 4. Преобразование сводной таблицы в жестко заданные значения

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

Для преобразования части сводной таблицы выполните следующие действия:

1. Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
2. Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).

Если нужно преобразовать всю сводную таблицу, выполните следующие действия:

1. Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать . Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
2. Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка .
3. Выберите параметр Значения и щелкните ОК .

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

Совет 5. Заполнение пустых ячеек в полях СТРОКИ

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

Рис. 3. Использовать эту преобразованную сводную таблицу без заполнения пустых ячеек в левой части проблематично

Обратите внимание на то, что поля Регион и Рынок сбыта сохраняет ту же структуру строк, которая присуща при нахождении этих данных в области СТРОКИ сводной таблицы. В Excel 2013 существует быстрый способ заполнения ячеек в области СТРОКИ значениями. Кликните в области сводной таблицы, после чего пройдите по меню Конструктор -> Макет отчета -> (рис. 4). После этого можно преобразовать сводную таблицу в значения, в результате чего вы получите таблицу данных без пробелов.

Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки

Совет 6. Ранжирование числовых полей сводной таблицы

В процессе сортировки и ранжирования полей, содержащих большое количество элементов данных, не всегда легко определить числовой ранг анализируемого элемента данных. Более того, если сводная таблица будет преобразована в значения, назначенный каждому элементу данных числовой ранг, отображенный в целочисленном поле, значительно облегчит анализ созданного набора данных. Откройте сводную таблицу, подобную показанной на рис. 5. Обратите внимание на то, что один и тот же показатель - Сумма по полю Объем продаж - отображается дважды. Щелкните правой кнопкой мыши на втором экземпляре показателя и в контекстном меню выберите команду Дополнительные вычисления -> Сортировка от максимального к минимальному (рис. 6.)

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

Совет 7. Уменьшение размера отчета сводной таблицы

При формировании отчета сводной таблицы Excel создает снимок данных и сохраняет его в кеше сводной таблицы. Кеш сводной таблицы представляет собой специальную область памяти, в которой хранится копия источника данных для ускорения доступа. Другими словами, Excel создает копию данных, а затем хранит ее в кеше, связанном с рабочей книгой. Кеш сводной таблицы обеспечивает оптимизацию рабочего процесса. Любые изменения, внесенные в сводную таблицу, такие как изменение расположения полей, добавление новых полей либо сокрытие каких-либо элементов, выполняются быстрее, а требования к системным ресурсам оказываются гораздо скромнее. Основной недостаток кеша сводной таблицы заключается в том, что в результате его применения практически вдвое увеличивается размер файла рабочей книги при каждом создании сводной таблицы «с нуля».

Удаляйте исходные данные. Если рабочая книга содержит исходный набор данных и сводную таблицу, размер ее файла увеличивается вдвое. Поэтому можете спокойно удалить исходные данные, и это совершенно не отразится на функциональности вашей сводной таблицы. После удаления исходных данных не забудьте сохранить сжатую версию файла рабочей книги. После удаления исходных данных можно использовать сводную таблицу в обычном режиме. Единственная проблема заключается в невозможности обновления сводной таблицы из-за отсутствия исходных данных. Если же вам понадобятся исходные данные, щелкните дважды на пересечении строки и столбца в области общих итогов (на рис. 7 это ячейка В18). При этом Excel выгружает содержимое кеша сводных таблиц на новый рабочий лист.

Совет 8. Создание автоматически развертываемого диапазона данных

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

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

Для реализации описанной методики выделите исходные данные, а затем щелкните на значке таблицы, находящемся на вкладке Вставка (рис. 8) или нажмите Ctrl+T (Т английское). Щелкните ОК в открывшемся окне. Обратите внимание на то, что, хотя диапазон исходных данных в сводной таблице переопределять не нужно, но при добавлении исходных данных в диапазон в сводной таблице все равно придется щелкнуть на кнопке Обновить .

Совет 9. Сравнение обычных таблиц с помощью сводной таблицы

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

В процессе сравнения создается одна таблица, на основе которой создается сводная таблица. Убедитесь в том, что у вас имеется способ пометить данные, относящиеся к этим таблицам. В рассматриваемом примере для этого используется столбец Фискальный год (рис. 10). После объединения двух таблиц воспользуйтесь полученным комбинированным набором данных для создания новой сводной таблицы. Отформатируйте сводную таблицу таким образом, чтобы в качестве тега таблицы (идентификатор, указывающий на происхождение таблицы) использовалась область столбцов сводной таблицы. Как показано на рис. 11, годы находятся в области столбцов, а сведения о заказчиках - в области строк. В области данных содержатся объемы продаж для каждого заказчика.

Совет 10. Автоматическая фильтрация сводной таблицы

Как известно, в сводных таблицах нельзя применять автофильтры. Тем не менее существует трюк, позволяющий включить автофильтры в сводную таблицу. Принцип использования этой методики заключается в том, чтобы поместить указатель мыши справа от последнего заголовка сводной таблицы (ячейка D3 на рис. 12), а затем перейдите на ленту и выбрать команду Данные -> Фильтр . Начиная с этого момента в вашей сводной таблице появляется автофильтр! Например, вы сможете выбрать всех заказчиков с уровнем транзакций выше среднего. С помощью автофильтров в сводную таблицу добавляется дополнительный уровень аналитики.

Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах

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

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

Шаг 1. Объединение всех полей, не относящихся к области столбцов, в один столбец. Для создания сводных таблиц с несколькими консолидированными диапазонами следует создать единственный столбец размерности. В рассматриваемом примере все, что не относится к полю месяца, рассматривается как размерность. Поэтому поля Рынок сбыта и Описание услуги следует объединить в один столбец. Для объединения полей в один столбец просто введите формулу, которая выполняет конкатенацию этих двух полей, используя точку с запятой в качестве разделителя. Присвойте новому столбцу имя. Введенная формула отображается в строке формул (рис. 14).

Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги

После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения . Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).

Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги

Шаг 2. Создание сводной таблицы с несколькими диапазонами консолидации. Теперь нужно вызвать знакомый многим пользователям по предыдущим версиям Excel мастер сводных таблиц и диаграмм. Для вызова этого мастера нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. . После запуска мастера установите переключатель В нескольких диапазонах консолидации . Кликните Далее . Установите переключатель Создать поля страницы и щелкните Далее . Определите рабочий диапазон и кликните Готово (подробнее см. ). Вы создадите сводную таблицу (рис. 16).

Шаг 3. Дважды щелкните на пересечении строки и столбца в строке общих итогов. На этом этапе в вашем распоряжении окажется сводная таблица (рис. 16), включающая несколько диапазонов консолидации, которая является практически бесполезной. Выберите ячейку, находящуюся на пересечении строки и столбца общих итогов, и дважды щелкните на ней (в нашем примере это ячейка N88). Вы получите новый лист, структура которого напоминает структуру, показанную на рис. 17. Фактически этот лист представляет собой транспонированную версию исходных данных.

Шаг 4. Разбиение столбца Строка на отдельные поля. Осталось разбить столбец Строка на отдельные поля (вернуться к изначальной структуре). Добавьте один пустой столбец сразу же после столбца Строка . Выделите столбец А, а затем перейдите на вкладку ленты Данные и щелкните на кнопке Текст по столбцам . На экране появится диалоговое окно Мастер распределения текстов по столбцам . На первом шаге выберите переключатель С разделителями и щелкните на кнопке Далее. В следующем шаге выберите переключатель точка с запятой и щелкните Готово . Отформатируйте текст, добавьте заголовок и превратите исходные данные в таблицу путем нажатия Ctrl+T (рис. 18).

Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)

Совет 12. Включение двух числовых форматов в сводную таблицу

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

Несмотря на то что эта таблица может служить примером неплохого форматирования, не все так хорошо. Обратите внимание на то, что одни показатели должны отображаться в числовом формате, а другие - в процентном. Но в исходной базе данных поле Значение имеет тип Double. При создании сводной таблицы на основе набора данных невозможно присвоить два разных числовых формата одному полю Значение . Здесь действует простое правило: одно поле соответствует одному числовому формату. Попытка назначить числовой формат полю, которому был присвоен процентный формат, приведет к тому, что процентные значения превратятся в обычные числа, которые завершаются знаком процента (рис. 20).

Для решения этой проблемы применяется пользовательский числовой формат, который любое значение, большее 1,5, форматирует как число. Если же значение меньше 1,5, оно форматируется как процент. В диалоговом окне Формат ячеек выберите вкладку (все форматы) и в поле Тип введите следующую форматирующую строку (рис. 21): [>=1,5]$# ##0; [<1,5]0,0%

Рис. 21. Примените пользовательский числовой формат, в котором любые числа, меньшие 1,5, форматируются как проценты

Полученный результат показан на рис. 22. Как видите, теперь каждый показатель отформатирован корректно. Конечно, приведенный в этом совете рецепт не универсален. Скорее, он указывает направление, в котором стоит экспериментировать.

Совет 13. Создание частотного распределения для сводной таблицы

Если вы когда-либо создавали частотные распределения с помощью функции ExcelЧастота , то, наверное, знаете, что это весьма непростая задача. Более того, после изменений диапазонов данных все приходится начинать сначала. В этом разделе вы научитесь создавать простые частотные распределения с помощью обычной сводной таблицы. Вначале создайте сводную таблицу, в области строк которой находятся данные. Обратите внимание на рис. 23, где в области строк находится поле Объем продаж .

Щелкните правой кнопкой мыши на любом значении в области строк и в контекстном меню выберите параметр Группировать . В диалоговом окне Группирование (рис. 24) определите значения параметров, определяющих начало, конец и шаг частотного распределения. Щелкните ОК.

Рис. 24. В диалоговом окне Группирование настройте параметры частотного распределения

Если в сводную таблицу добавить поле Заказчик (рис. 25), получим частотное распределение транзакций заказчиков относительно размера заказов (в долларах).

Рис. 25. Теперь в вашем распоряжении оказалось распределение транзакций заказчиков в соответствии с размерами заказов (в долларах)

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

Совет 14. Использование сводной таблицы для распределения набора данных по листам книги

Аналитикам часто приходится создавать различные отчеты сводных таблиц для каждого региона, рынка сбыта, менеджера и т.п. Выполнение этой задачи обычно подразумевает длительный процесс копирования сводной таблицы на новый лист и последующее изменение поля фильтра с учетом соответствующего региона и менеджера. Этот процесс выполняется вручную и повторяется для каждого вида анализа. Но вообще-то создание отдельных сводных таблиц можно поручить Excel. В результате применения параметра автоматически создается отдельная сводная таблица для каждого элемента, находящегося в области полей фильтра. Для использования этой функции просто создайте сводную таблицу, включающую поле фильтра (рис. 27). Поместите курсор в любом месте сводной таблицы и на вкладке Анализ в группе команд Сводная таблица щелкните на раскрывающемся списке Параметры (рис. 28). Затем щелкните на кнопке Отобразить страницы фильтра отчета .

Рис. 28. Щелкните на кнопке Отобразить страницы фильтра отчета

В появившемся диалоговом окне (рис. 29) можно выбрать поле фильтра, для которого будут созданы отдельные сводные таблицы. Выберите подходящее поле фильтра и щелкните ОК .

Рис. 29. Диалоговое окно Отображение страниц фильтра отчета

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

Совет 15. Использование сводной таблицы для распределения набора данных по отдельным книгам

В совете 14 мы воспользовались специальной опцией для разделения сводных таблиц по рынкам сбыта на разных листах рабочей книги. Если же вам нужно разделить исходные данные по разным рынкам сбыта в отдельных книгах, можно воспользоваться небольшим кодом VBA. Для начала поместите поле, на основе которого будет выполняться фильтрация, в область полей фильтра. Поместите поле Объем продаж в область значений (рис. 31). Предлагаемый код VBA поочередно выбирает каждый элемент ФИЛЬТРА и вызывает функцию Показать детали , создавая новый лист с данными. Затем этот лист сохраняется в новой рабочей книге

Код VBA.

Sub ExplodeTable()

Dim PvtItem As PivotItem

Dim PvtTable As PivotTable

Dim strfield As PivotField

‘Изменение переменных в соответствии со сценарием

ConststrFieldName = " Рынок сбыта " ‘<—Изменение имени поля

Const strTriggerRange = " A4 " ‘<—Изменение диапазона триггера

‘Изменение названия сводной таблицы (при необходимости)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") ‘<—Изменение названия сводной

‘Циклический обход каждого элемента выделенного поля

For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = True

‘Присваивание имени временному листу

ActiveSheet.Name = " TempSheet "

‘Копирование данных в новую книгу и удаление временного листа

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

Filename:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook.Close

Sheets(" Tempsheet ").Delete

Application.DisplayAlerts = True

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

• Const strFieldName. Имя поля, используемого для разделения данных. Другими словами, это поле, которое помещается в область фильтра/страниц сводной таблицы.
• Const strTriggerRange. Ячейка триггера, в котором хранится единственное число из области данных сводной таблицы. В нашем случае ячейкой триггера является А4 (см. рис. 31).

В результате выполнения кода VBA данные для каждого рынка сбыта будут сохранены в отдельной книге.

Заметка написана на основе книги Джелен, Александер. . Глава 14.