АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

Тема 2 АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

1.Основные понятия

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

Сводная таблица - это динамическая таблица итоговых данных, извлеченных или

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

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

• Дату открытия каждого счета и его сумму.

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

• Отделение, в котором открыт счет.

•Информацию о том, кем открыт счет (кассиром или уполномоченным по новым

счетам).

•Информацию о клиенте (новый или старый).

Рис. 1. Пример базы данных

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

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

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

1

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

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

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

Рис. 3. Сводная таблица, которая использует поле страницы

2.Типы данных

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

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

типов.

2

•Данные. В полях этого типа содержатся значения. Например, поле Счет (см.

рис. 1).

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

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

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

Рис. 4. База данных

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

Рис. 5. Сводная таблица

3

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

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

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

Если выполнить эту команду, то появится первое из трех диалоговых окон (рис. 6). На этом этапе определяется источник данных.

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

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

1.Данные в списке Excel. Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком. База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.

2.Данные во внешнем источнике данных. Если для создания сводной таблицы используется внешняя база данных, то данные вводятся в рабочий лист с помощью отдельного приложения Microsoft Query, входящего в поставку Excel или Office. Можно использовать файлы dBASE, данные SQL-сервера или другие источники. Выбор источника данных осуществляется на втором шаге работы Мастера сводных таблиц и диаграмм.

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

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

4

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

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

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

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

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

Последнее диалоговое окно Мастера сводных таблиц и диаграмм показано на рис. 8. Здесь необходимо указать местоположение создаваемой сводной таблицы.

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

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

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

5

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

Это диалоговое окно содержит следующие опции:

•Имя. Это поле позволяет определить название сводной таблицы. Excel по умолчанию назьшает их СводнаяТаблица1, СводнаяТаблица2 и т.д.

•Общая сумма по столбцам. Установите флажок этой опции, если необходимо подвести общие итоги по столбцам.

•Общая сумма по строкам. Установите флажок этой опции, если необходимо подвести общие итоги по строкам.

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

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

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

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

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

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

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

•Для ошибок отображать. Можно указать значение, которое будет выводиться

вячейках сводной таблицы при возникновении ошибки.

6

•Для пустых ячеек отображать. Можно указать значение, которое будет выводиться в пустых ячейках.

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

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

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

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

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

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

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

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

Для создания макета сводной таблицы Excel предоставляет два способа:

•с помощью мастера сводных таблиц и диаграмм;

•самостоятельно на рабочем листе с использованием инструментов панели

Сводные таблицы (рис. 10).

Рис. 10. Панель инструментов Сводные таблицы

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

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

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

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

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

•Данные. Поле, для которого подводятся итоги в сводной таблице.

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

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

При перетаскивании кнопки поля в область данных Мастер сводных таблиц по умолчанию применит функцию СУММ, если поле содержит числовые значения, и функцию СЧЁТ — если поле содержит нечисловые значения.

7

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

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

Если случайно перетащили поле не в ту область, то необходимо выведите его за пределы макета, чтобы удалить из области. На рис. 12 показано диалоговое окно после перетаскивания некоторых кнопок полей в сводную таблицу. Эта сводная таблица будет отображать сумму по полю Счет в зависимости от типа счета (поле строки Тип) и клиента (поле столбца Клиент). А поле Отделение будет использоваться в качестве поля страницы. Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм, нужно щелкнуть на кнопке ОК.

Рис. 12. Готовый макет сводной таблицы

8

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

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

Выполните два первых шага Мастера сводных таблиц и диаграмм. Укажите местоположение сводной таблицы во втором диалоговом окне Мастера сводных таблиц и диаграмм и щелкните на кнопке Готово в этом окне. Excel отобразит в рабочем листе шаблон сводной таблицы, как показано на рис. 13.

Рис. 13. Готовый макет сводной таблицы

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

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

9

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

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

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

Изменение структуры сводной таблицы

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

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

Удаление поля

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

Добавление нового поля

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

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

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

•Выберите команду Данные→ Обновить данные.

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

•Щелкните на кнопке Обновить данные , которая расположена на панели инструментов Сводные таблицы

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

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

Вычисление поля сводной таблицы, показанное на рис. 14.

10