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


Скачать 110.82 Kb.
НазваниеСводные таблицы Excel 2007
Дата публикации05.08.2013
Размер110.82 Kb.
ТипДокументы
userdocs.ru > Информатика > Документы
Сводные таблицы Excel 2007

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

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

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

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

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

http://on-line-teaching.com/excel/img/2007/lsn030_1.jpg

За 17 дней продаж у нас получилась большая таблица на 350 записей. Но эта таблица не решает наших проблем. Нам необходимо узнать объемы продаж в денежном и количественном выражении по датам и по отдельным магазинам, но как это сделать? Сортировать таблицу и суммировать отдельные её части? Это требует времени, а завтра поступят новые данные, и всю работу нужно будет снова повторить.

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

http://on-line-teaching.com/excel/img/2007/lsn030_2.jpg

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

http://on-line-teaching.com/excel/img/2007/lsn030_3.jpg

Таблица сразу же отобразит нужные нам результаты:

http://on-line-teaching.com/excel/img/2007/lsn030_4.jpg

Этот пример наглядно демонстрирует преимущества сводных таблиц, к которым относятся:

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

возможность консолидировать данные из разных таблиц и даже из разных источников;

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

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

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

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

^ Создание и настройка сводных таблиц Excel 2007

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

http://on-line-teaching.com/excel/img/2007/lsn031_9.jpg

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

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

Помимо выбора исходной таблицы Excel предоставляет возможность использовать в качестве источника данных базы данных и таблицы, созданные в других программах (Access, SQL Server и других).

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

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

http://on-line-teaching.com/excel/img/2007/lsn031_1.jpg

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

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

а) в верхней части окна настроек отмечаем все названия необходимых нам столбцов:

http://on-line-teaching.com/excel/img/2007/lsn030_2.jpg

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

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

http://on-line-teaching.com/excel/img/2007/lsn031_3.jpg

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

http://on-line-teaching.com/excel/img/2007/lsn031_4.jpg

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

http://on-line-teaching.com/excel/img/2007/lsn031_5.jpg

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

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

http://on-line-teaching.com/excel/img/2007/lsn031_10.jpg

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



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

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

При создании новой сводной таблицы Excel автоматически именует её столбцы и заголовки. Однако это легко исправить - достаточно отредактировать ячейку заголовка столбца или таблицы. Например, мы переименовали предыдущий заголовок таблицы:

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_1.jpg

в более понятный:

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_2.jpg

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

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_3.jpg

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

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

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_4.jpg

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

Команда ^ Макет отчета предлагает три варианта:

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

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_5.jpg

  1. Показать в форме структуры - базовый стиль сводной таблицы, который мы использовали в наших примерах:

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_6.jpg

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

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_7.jpg

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

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

Команда ^ Общие итоги предлагает вывести общие итоги только для строк, только для столбцов, для строк и для столбцов одновременно, или не выводить их вообще.

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

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_8.jpg

а вот так после:

e:\lsn031\форматирование сводной таблицы excel 2007_files\lsn032_9.jpg

^ Анализ данных сводной таблицы Excel 2007

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

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

http://on-line-teaching.com/excel/img/2007/lsn033_1.jpg

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

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

  • автоматически - подсчитывает сумму для каждого условия таблицы;

  • нет - промежуточные итоги не подсчитываются;

  • другие - позволяет самостоятельно выбрать действие для подведения промежуточных итогов.

Установив автоматическое подведение промежуточных итогов, мы получим следующую таблицу, которая содержит промежуточные итоги для каждого условия:

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_2.jpg

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

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

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_3.jpg

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

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

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_4.jpg

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

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

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_5.jpg

Останется только переименовать название группы путём простого редактирования ячейки:

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_6.jpg

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

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

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

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_7.jpg

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

e:\lsn031\анализ данных сводной таблицы excel 2007_files\lsn033_8.jpg

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

^ Создание сводной диаграммы Excel 2007

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

1. Выберите необходимую Вам сводную таблицу, кликнув по ней;

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_1.jpg

2. На вкладке Вставка в группе Диаграммы выберите необходимый тип диаграммы.

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_2.jpg

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

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_3.jpg

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

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_4.jpg

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

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_5.jpg

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

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_6.jpg

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

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

1. Выделить нужный нам диапазон данных (или установить курсор на нужную нам таблицу - тогда Excel автоматически подставит всю таблицу в диапазон данных);

2. На вкладке Вставка в группе Таблицы выбрать раздел Сводная таблица, а затем пункт Сводная диаграмма.

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_7.jpg

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

Excel создаст новую сводную таблицу и сводную диаграмму:

e:\lsn031\создание сводной диаграммы excel 2007_files\lsn034_8.jpg

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

Похожие:

Сводные таблицы Excel 2007 iconВнимание! внимание!
Если вы с компьютером на «ТЫ» и Вас не пугают массивы цифр, авс-анализ, сводные таблицы, диаграммы и формулы
Сводные таблицы Excel 2007 iconОсновы работы в ms excel. Создание «Титульный лист» в Excel
Наработать устойчивые навыки работы с программой ms office Excel. Научиться производить простейшие действия по набору, форматированию...
Сводные таблицы Excel 2007 iconМетодическое пособие Основные понятия (термины) и сводные таблицы...
Реклама – неперсонифицированная передача информации (обычно имеющая характер убеждения) о продукции, услугах или идеях известных...
Сводные таблицы Excel 2007 iconУказатель мыши в ms excel имеет вид при
В электронной таблице ms excel знак «$» (или «!») перед номером строки в обозначении ячейки указывает на
Сводные таблицы Excel 2007 icon1 Основні поняття Excel
Електрона таблиця Excel дозволяє оброблювати числа, які введені в клітинки таблиці, створювати графіки та користуватися інформацією...
Сводные таблицы Excel 2007 iconТаблицы
Статистические таблицы это наиболее рациональная форма представления результатов статистической сводки и группировки
Сводные таблицы Excel 2007 iconОсновы работы в Microsoft Excel
Функциональные возможности и вычислительные средства Excel позволяют решать многие инженерные и экономические задачи, представляя...
Сводные таблицы Excel 2007 iconОсновы работы в Microsoft Excel
Функциональные возможности и вычислительные средства Excel позволяют решать многие инженерные и экономические задачи, представляя...
Сводные таблицы Excel 2007 iconИнструкция по использованию microsoft Excel для решения злп для того...
Приобретение навыков решения задач линейного программирования (злп) в табличном редакторе Microsoft Excel
Сводные таблицы Excel 2007 iconВопросы для освоения: ввод формул в ячейки
Цель работы: освоение методики работы с формулами и функциями в табличном процессоре Microsoft Office Excel (далее – Excel)
Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2015
контакты
userdocs.ru
Главная страница