Обновление сводной таблицы

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

• Выберите команду Данные | Обновить данные.
• Щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите из появившегося контекстного меню команду Обновить данные.
• Щелкните на кнопке Обновить данные , которая расположена на панели инструментов Сводные таблицы

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

Рис. 1. Диалоговое окно Вычисление поля сводной таблицы

Опции этого диалогового окна:

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

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

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

Когда сводная таблица создана, всегда можно применить другой тип автоформатирования. Поместите табличный курсор внутрь сводной таблицы и щелкните на кнопке Формат отчета на панели инструментов Сводные таблицы. Excel отобразит диалоговое окно Автоформат, показанное на рисунке 1. Выберите нужный автоформат и щелкните на кнопке ОК.

Рис. 1. Диалоговое окно Автоформат

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

1. Выделите любую ячейку, содержащую число, в области данных сводной таблицы.
2. Щелкните правой кнопкой мыши и из появившегося контекстного меню выберите команду Параметры поля. На экране появится диалоговое окно Вычисление поля сводной таблицы.
3. Щелкните на кнопке Формат.
4. Выберите необходимый формат представления чисел.

На следующем шаге мы рассмотрим группировку элементов сводной таблицы.

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

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

Рис. 1. Пример сводной таблицы

Чтобы создать группу, выделите ячейки, которые будут сгруппированы, в данном случае - А6:А7. Затем выберите команду Данные | Группа и структура | Группировать. В результате Excel создаст новое поле и назовет его Отделение2. В этом поле находиться два элемента: Западное и Группа1 (рис. 2).

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

Теперь можно удалить исходное поле Отделение и переименовать названия полей и элементов. На рисунке 3 показана сводная таблица после этих изменений. Новое название поля не может совпадать с названием существующего поля. При несовпадении имен Excel просто добавляет новое поле к сводной таблице. Поэтому в рассмотренном примере нельзя переименовать Отделение2 в Отделение без удаления исходного поля.

Рис. 3. Сводная таблица после выполненных преобразований

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

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

Рис. 4. Пример сводной таблицы

Чтобы создать группу автоматически, отметьте любой элемент поля Счет. Затем выберите команду Данные | Группа и структура | Группировать. Появится диалоговое окно Группирование, показанное на рисунке 5.

Рис. 5. Диалоговое окно Группирование

По умолчанию в нем будут показаны наименьшее и наибольшее значения, которые можно изменить по своему усмотрению. Например, чтобы создать группу с шагом в 5 000, введите 0 в поле Начиная с, 100 000 - в поле По и 5 000 - в поле С шагом. Щелкните на кнопке OK, и Excel создаст указанные Вами группы. На рисунке 6 показана результирующая сводная таблица.

Рис. 6. Результирующая сводная таблица

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

Рис. 1. Пример сводной таблицы

Рис. 2. Новый лист со списком данных

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

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

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

Рис. 3. Диалоговое окно Отображение страниц

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

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

Для создания вычисляемого поля следует выполнить следующие действия:

1. Поместить курсор в область сводной таблицы.
2. В меню выбрать команду Вставка | Вычисляемое поле. В результате появится диалоговое окно Вставка вычисляемого поля (рис. 1).

Рис. 1. Диалоговое окно Вставка вычисляемого поля

1. Ввести полное имя поля и определить формулу. В формуле можно использовать другие поля. В данном примере, имя поля - Проект, формула имеет вид =Счет*1,15.
2. Щелкнуть на кнопке Добавить для добавления нового поля.
3. Для создания дополнительных вычисляемых полей, повторить пункты 3 и 4. Щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.

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

На рисунке 2 показана сводная таблица с вычисляемым полем.

Рис. 2. Пример сводной таблицы с вычисляемым полем

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

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

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

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

Рис. 1. Диалоговое окно Вставка вычисляемого элемента

1. Введите имя для нового элемента, а затем формулу. В формуле могут быть использованы элементы из других полей, но не функции рабочего листа. В данном примере новый элемент называется Депозит и Срочный, а формула выглядит следующим образом: =Депозит + Срочный.
2. Щелкните на кнопке Добавить.
3. Для создания дополнительных вычисляемых элементов повторите пункты 3 и 4. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно.

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

На рисунке 2 показан пример сводной таблицы с вычисляемым элементом.

Рис. 2. Пример сводной таблицы с вычисляемым элементом

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

---

Дата добавления: 2015-07-20