ТИХООКЕАНСКИЙ ГОСУДАРСТВЕННЫЙ  ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ

РЕФЕРАТ

На тему: «Сводные таблицы в Microsoft Office Excel 2007»

Выполнила:

студентка 121 К гр.

Шарипова Е. Л.

Владивосток 2010 

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

Что такое сводная  таблица

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

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

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

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

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

Лучший способ понять концепцию  сводных таблиц — увидеть их в  действии. На рис. 1 показаны

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

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

• дата открытия каждого счета;
• сумма каждого счета;
• тип счета (депозит, текущий, срочный или на предъявителя);

• сведения о том, кем был открыт счет (кассиром или уполномоченным по новым счетам);
• отделение, в котором был открыт счет;
• сведения о клиенте (новый или существующий).

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

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

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

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

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

Отбор данных для  сводных таблиц

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

Поля в табличных базах данных могут относиться к одному из двух типов.

• Данные. В полях этою типа содержатся значения (на рис.1 это поле Счет, в котором содержатся суммы по каждому счету).
• Категория. Поля этого типа описывают данные (на рис.1 это поля Дата, Тип, Открыл и Клиент, которые описывают данные поля Счет).

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

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

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

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

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

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

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

• Группа. Набор элементов, которые обрабатываются как один элемент. Элементы можно объединить в труппу вручную или автоматически (например, объединить даты в группу конкретного месяца). Сводная таблица на рисунке не содержит групп.
• Исходные данные. Данные, которые используются для создания сводной таблицы. Они могут содержаться на рабочем листе или во внешней базе данных.
• Область Значения. Ячейки сводной таблицы, в которых содержатся итоговые данные. Над значениями нолей, помешенных в эту область выполняются сводные (обобщающие, консолидирующие, итоговые и т.д.) вычисления, например суммирование, вычисление среднего, поиск максимального и минимального значений и т.п
• Область Названия столбцов. Элементы поля, назначенного этой области, размещаются вдоль верхнего края сводной таблицы в качестве заголовков столбцов. На рисунке в качестве области столбцов используется поле Клиент, которое состоит из двух элементов (Существующий и Новый). Область столбца может быть иерархической (т.е. иметь вложенные подобласти).
• Область Названия строк. Значения поля, присвоенного этой области, размешаются вдоль левого края сводной таблицы в качестве заголовков строк. Поля этой области могут быть вложенными. На рисунке эта область содержит поля Отделение и Тип.
• Область Фильтр отчета Эта область как бы добавляет третье измерение в сводную таблицу. Поле, назначенное этой области, создает раскрывающееся меню, которое располагается над таблицей и позволяет выбирать любые значения этого поля На рисунке в качестве этой области используется поле Открыл. Отметим, что в предыдущих версиях Excel эта область называлась областью страниц.
• Обновление сводной таблицы. Вычисление сводной таблицы, после того как были внесены изменения в исходные данные.
• Обще итоги. Строка или столбец, в котором показаны общие итоги для всех ячеек строки или столбца сводной таблицы. Всегда можно либо задать способ подведения общих итогов — для строк, столбцов, для тех и других либо вообще не подводить никаких итогов. В сводной таблице на рисунке подводятся общие итоги как для строк, так и для столбцов.
• Промежуточные итоги. Строка или столбец, в которых отображаются промежуточные итоги для отдельных строк или столбцов сводной таблицы. На рисунке промежуточные итоги подводятся для каждого отделения.
• Элемент. Элемент полч, назначенного определенной области Элементы выполняют роль заголовков строк и/или столбцов сводной таблицы На рисунке ячейки с текстом Новый и Старый являются элементами поля Клиент. Поле Отделение содержит три элемента: Западное, Северное и Центральное. К полю Тип относятся четыре элемента: Депозит, На предъявителя, Срочный и Текущий.

Определение местонахождения  данных

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

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

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

Определение местоположения сводной таблицы

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

Щелкните на кнопке ОК, и  Excel создаст пустую сводную таблицу и отобразит панель Список полей сводной таблицы, как показано на рис.7

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

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

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

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

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

Чтобы активизировать его, выберите команду Работа со сводными таблицами^→ Параметры^→Сводная таблицам→ Параметры. В открывшемся диалоговом окне Параметры сводной таблицы перейдите на вкладку Вывод и установите флажок Классический макет сводной таблицы (разрешено перетаскивание полей).

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

1. Поле Счет перемещено в область Значения. Теперь сводная таблица будет подсчитывать сумму всех значений в поле Счет.
2. Поле Тип перемещено в область Названия строк. Значения поля Тип будут заголовками строк сводной таблицы.
3. Поле Отделение перемещено в область Названия столбцов. Значения поля Отделение будут заголовками столбцов сводной таблицы.

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

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

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

К сводной таблице можно  применить любой встроенный стиль. Выделите любую ячейку сводной таблицы  и для выбора стиля выполните  команду Работа со сводными таблицами→Конструктор^→Стили сводной таблицы.

С помощью команд группы Работа со сводными таблицами→Конструктор→Макет можно управлять отдельными элементами сводной таблицы. Например, можно скрыть общие итоги.

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

Еще больше параметров сводной таблицы  доступно в диалоговом окне Параметры сводной таблицы (рис.9). Для того чтобы открыть это окно, выберите команду Работа со сводными таблицами→ Параметры→Сводная таблица→Параметры либо щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите в контекстном меню команду Параметры сводной таблицы