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

МИНОБРНАУКИ РОССИИ

 

Федеральное Государственное  Бюджетное Образовательное  Учреждение Высшего Профессионального Образования

«Санкт-Петербургский  Государственный Университет Сервиса  и Экономики»

 

 

Институт Региональной Экономики Управления

 

 

 

 

 

 

 

 

 

Контрольная работа по дисциплине

                      «Информационные технологии управления»

                              На тему «Создание сводных таблиц»

 

 

 

 

 

 

 

 

 

 

 

Выполнила студентка 2 курса

заочной формы  обучения

специальность 080507.65 (061100)

Марданова Зарина Зокир кизи

Проверил: Пупышев А.В.

 

 

 

 

 

 

 

Санкт-Петербург

2012

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

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

Списки или базы данных Microsoft Excel

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

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

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

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

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

Внешние источники данных.

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

Необходимые условия получения  данных.   Чтобы получить данные с веб-узла, нужна связь с интрасетью или Интернетом.

 

Чтобы получить другие типы внешних данных, следует установить Microsoft Query и соответствующие драйверы (ODBC) или источников данных. Query предоставляет драйверы для многих типов внешних данных, включая Microsoft SQL Server, Microsoft Access и базы данных текстовых файлов.

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

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

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

Веб-запросы.   Чтобы  получить данные из Интернета, создайте или запустите веб-запрос, а затем  расположите отчет из запроса  в диапазоне рабочего листа.

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

Шаблоны отчетов и файлы  запросов.   Чтобы открыть файл запроса (.dqy) или использовать шаблон отчета (.xlt), который еще не включен в отчет сводной таблицы, сначала откройте файл запроса или шаблон. Поместите отчет запроса или шаблона в диапазон рабочего листа.

Файлы ODC (Office data connection — подключение к данным Microsoft Office).   Чтобы использовать файл ODC, откройте его в Microsoft Excel. После этого есть два пути. Первый — можно импортировать данные непосредственно в отчет сводной таблицы. Второй — импортировать данные на рабочий лист и разместить отчет в его диапазоне.

Исходные данные OLAP.   Когда данные извлекаются из базы данных OLAP или файла куба, они  могут быть переданы в Microsoft Excel только как отчет сводной таблицы. Можно создать отчеты сводных диаграмм по этому отчету сводной таблицы. Microsoft Query используется как средство задания источника данных для доступа к базе данных.

 

Несколько диапазонов консолидации

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

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

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

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

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

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

Несколько полей страниц.   Можно создать четыре поля страницы и назначить собственные имена  исходным данным, что позволит создавать  частичную или полную консолидацию. Например, в одном поле можно консолидировать  «Маркетинг» и «Сбыт», в другом - все три подразделения. Вы можете объединить данные всех диапазонов, создав консолидацию без полей страницы. Для этого на шаге 2а мастера  нажмите Создать поля страницы, а на шаге 2б создайте поля страницы и назначьте диапазоны как элементы.

 

Другой отчет  сводной таблицы.

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

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

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

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

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

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

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

Изменение исходных данных существующего отчета

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

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

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

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

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

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

Преобразование отчета сводной  диаграммы в статическую диаграмму

Найдите и выберите отчет  сводной таблицы, связанный с отчетом сводной диаграммы.

Инструкции

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

Щелкните отчет сводной диаграммы.

На панели инструментов Сводные  таблицы нажмите кнопку Сводная диаграмма.

Выберите команду Параметры  и запишите имя, указанное в поле Имя.

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

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

Нажмите клавишу DEL.

Совет

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

Создание диаграммы из данных в отчете сводной таблицы

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

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

Нажмите кнопку Копировать .

Выделите ячейку в пустой области.

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

Выберите Значения, а затем нажмите кнопку OK.

Нажмите кнопку Мастер диаграмм  и следуйте инструкциям мастера  для создания диаграммы.