на предыдущую 

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

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

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

В работе со сводными таблицами и сводными диаграммами нам, как и прежде, помогут Мастер сводных таблиц и диа­грамм и панель инструментов Сводные таблицы. Вывести эту панель инструментов на экран можно, например, выбрав команду Вид Панели инструментовСводные таблицы .

Чтобы запустить, Мастер сводных таблиц и диаграмм, выберите команду Данные a Сводная таблица или щелкните на кнопке Сводные таблицы  панели инструментов Сводные таблицы .

Технология создания сводных таблиц

Рассмотрим технологию создания сводной таблицы, а впоследствии и сводной диаграммы, на основе данных таблицы «Список отпущенных товаров» (рис. 8.1).

1. Установите курсор в любую ячейку списка и дайте команду ДанныеСводная таблица или щелкните на кнопке Сводные таблицы панели инстру­ментов Сводные таблицы, чтобы запустить Мастер сводных таблиц и диаграмм. На экране появится первое диалоговое окно Мастера, на экране появится первое диалоговое окно Мастера сводных таблиц и диаграмм –шаг 1 из 3 (рис. 9.1).
2. В группе Создать таблицу на основе данных, находящихся установите переключатель, соответствующий имеющимся ис­точникам данных. В нашем примере данные представлены в списке Microsoft Excel.
3.   В группе Вид создаваемого отчета установите переключатель Сводная таблица для соответствующего представления данных (рис. 9.1).

   

   Рис. 9.1. Окно диалога Мастера Сводных таблиц - шаг 1 из 3

4. Щелкните на кнопке Далее. На экране появится второе диалоговое окно Мастера сводных таблиц и диаграмм (рис. 9.2).

   

   Рис. 9.2. Окно диалога Мастера сводных таблиц - шаг 2 из 3

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

6. Щелкните на кнопке Далее. На экране появится третье и последнее диалоговое окно Мастера сводных таблиц и диаграмм (рис. 9.3) .

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

   

   Рис. 9.3. Окно диалога Мастера сводных таблиц - шаг 3 из 3

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

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

  Макет сводной таблицы содержит 4 области :

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

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

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

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

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

  

  Рис.9.4. Диалоговое окно Мастер сводных таблиц и диаграмм — макет

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

  

  Рис. 9.5. Окно диалога с созданным макетом

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

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

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

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

Параметры создаваемой сводной таблицы можно установить на третьем этапе со­здания сводной таблицы. Для этого нужно щелкнуть на кнопке Параметры в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 3 из 3. Так же можно изменить и параметры уже существующей сводной таблицы. Щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите пункт Параметры таб­лицы. На экране появится диалоговое окно Параметры сводной таблицы (рис.9.7).

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

Рис.9.7. Диалоговое окно Параметры сводной таблицы

Установите флажки:

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

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

• Объединять ячейки заголовков для объедине­ния ячеек с подписями всех внешних строк и столбцов.
• Форматирование, чтобы за­щитить форматы ячеек сводной таблицы от изменений при ее обновлении или изменении макета.
• Сохранять данные вместе с таблицей для копирования исходных данных, на основе которых была создана сводная таблица, чтобы всегда иметь к ним доступ.
• Развертывание разрешено, чтобы иметь возможность отображать подробные данные двойным щелчком на области дан­ных сводной таблицы.

• Обновить при открытии, чтобы сводная таблица обновлялась при открытии книги Excel, в которой она содержится.

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

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

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

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

Закройте диалоговое окно Параметры сводной таблицы щелчком на кнопке ОК.

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

1. Дважды щелкните на кнопке поля данных, параметры отображения элементов которого должны быть изменены. На экране появится диалоговое окно Вычисление поля сводной таблицы  (см. рис. 2.34).
2. Щелкните на кнопке Макет. На экране появится диалоговое окно Расположение полей сводной таблицы.
3. В группе Параметры вывода на экран  выберите один из пере­ключателей вариантов отображения: в виде таблицы или в виде структуры.

4. Установите флажок
   • Промежуточные итоги в заголовке группы, если нужно представлять сначала итоги, а затем данные элемента поля.
   • Пустая строка после каждого элемента, если нужно, чтобы элементы поля данных отделялись друг от друга пустой строкой.
   • Разрыв страницы после каждого элемента, чтобы данные каждого элемента поля выводились на печать на отдельных листах.
5. Щелкните на кнопке ОК, чтобы параметры были изменены в соответствии с новыми требованиями. Снова щелкните на кнопке ОК, чтобы вернуться к свод­ной таблице.

Фильтрация данных в сводной таблице

Данные сводной таблицы можно отфильтровать.

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

Рис. 9.8. Фрагмент сводной таблицы с результатами фильтрации по продавцу Петрова

Чтобы скрыть элементы поля страницы, дважды щелкните на кнопке поля стра­ницы. На экране появится диалоговое окно Вычисление поля сводной таблицы (рис. 9.9).

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

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

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

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

Группировка и обновление данных в сводных таблицах

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

Чтобы сгруппировать даты/ числа/ текст поместите указатель мыши на ячейку, содержащую дату/ число/ текст. Щелчком правой кнопки мыши вызовите контекстное меню и выберите команду Группа и структура Группировать. В появив­шемся диалоговом окне выберите соответственно единицу времени для группировки дат, например, месяц / начальное и конечное значения. Также можно для объединения в группы чисел выбрать значения шага, а содержащие текст ячейки можно выбрать произвольно. Щелкните на кнопке ОК, чтобы вернуться к уже измененной сводной таблице. На рис.9.10 представлен результат группировки по месяцам.

Рис.9.10. Результат группировки по месяцам

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

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

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

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

1. Выведите на экран панель инструментов Сводные таблицы.
2. Установите указатель мыши на любую ячейку сводной таблицы.
3. Щелкните на кнопке Обновить данные панели инструментов Сводные таблицы.
4. Чтобы установить обновление сводной таблицы при каждом открытии книги, откройте диалоговое окно Параметры сводной таблицы (см. рис.9.7). Для этого щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите команду Параметры таблицы. Затем в группе Источник установите флажок обновить при открытии.

Перемещение и редактирование поля Данные

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

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

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

Изменение функции вычисления общих итогов

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

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

• Щелкните на кнопке Дополнительно, чтобы расширить диалоговое окно.
•   Выберите в списке Дополнительные вычисления нужную функцию.
• В случае необходимости в списке поле  выберите имя поля, а в списке элемент  — элемент, используемый в качестве базового значения при выполнении дополнительных вычислений (по отношению к которому указывается отличие или доля).
•   Щелкните на кнопке ОК. Список Дополнительные вычисления содержит функ­ции, представленные в таблице 9.1.

Таблица 9.1. Функции из списка Дополнительные вычисления

Кроме этого Excel позволяет создавать вычисляемые поля и вычисляемые элементы поля.

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

1. В сводной таблице выделите ячейку, перед которой будет вставлено новое вы­числяемое поле/поле данных сводной таблицы, в которое будет вставлен вычисляе­мый элемент.

2. Выберите на панели инструментов Сводные таблицы  команду Сводная таблицаФормулы Вычисляемое поле/ Вычисляемый объект. На экране появится диалоговое окно Вставка вычисляемого поля.
3. Введите в поле Имя имя нового вычисляемого поля.
4.   Введите формулу для расчетов в поле Формула.
5. Чтобы использовать в создаваемой формуле значение поля данных сводной таблицы, выделите нужное поле данных в списке Поля  щелкните на кнопке Добавить поле.
6. Щелкните на кнопке Добавить, чтобы вновь созданное поле оказалось в списке Поля.
7. Щелкните на кнопке ОК, чтобы вернуться к сводной таблице с новыми вычисляемыми полями.

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

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

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

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

1. Выведите на экран панель инструментов Сводные таблицы.
2. Щелкните на любой ячейке сводной таблицы и выберите команду Сводная таблицаВыделитьРазрешить выделение.
3. Подведите указатель мыши к полю данных, элементу поля или строкам итогов — он должен превратиться в стрелку. Укажите на нужную часть таблицы и щелк­ните левой кнопкой мыши.
4. Укажите, какие именно группы сведений должны быть выделены: заголовки, данные или то и другое. Для этого выберите команду Сводная таблицаВыделитьТолько Заголовки/Только Данные/Заголовки и данные.
5. Чтобы выделить всю таблицу, выберите команду Сводная таблицаВыделитьТаблица целиком.
6. Укажите, какие части сводной таблицы выделить: заголовки, данные или то и другое. Процедура аналогична шагу 4.
7. Выберите необходимое форматирование с помощью меню Формат или одноименной панели инструментов.

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

1. Выделите ячейку или диапазон ячеек, для которых нужно задать числовой формат.
2. Вызовите контекстное меню щелчком правой кнопки мыши и выберите команду Параметры поля или щелкните на одноименной кнопке на панели инструментов Сводные таблицы. На экране появится диалоговое окно Вычисление поля сводной таблицы  (см. рис. 9.9).
3. Щелкните на кнопке Формат. На экране появится диалоговое окно Формат ячеек с единственной вкладкой Число.
4. Укажите нужный формат.
5. Закройте диалоговые окна, щелкая на кнопках ОК.

в начало

на предыдущую  

Copyright © 2010-2024
Ющик Е.В. All Rights Reserved

E-mail:
mailto:yuschikev@yandex.ru?subject=Письмо автору