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

Лабораторная работа №6

Тема. Консолидация данных. Сводные таблицы

Цель: изучить понятие про консолидацию данных. Научиться вычислять суммы, средние значения, и проводить статистическую обработку данных, используя данные их разных таблиц; составить документы на основе сводных таблиц..

Задание:

1.на листе 1 построить выходную таблицу загрязнения воздуха, почвы, водоемов в январе; на 2 листке - в феврале; на 3 листве – в марте 9за данными наблюдений).

2. На основе листков 1 – 3 создать сводную таблицу и разместить на листке 4.

3. Оформить сводную таблицу.

3. Переименовать в квартальный отчет

5. По очереди изменять функции для обчисления итог в таблице (минимум, максимум, среднее значение, количество чисел)

Ход работы

Ответы на контрольные вопросы:

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

Консолидация данных в EXCEL

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

Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.

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

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

Возможны следующие виды консолидации:

- по физическому расположению (п.1.2);

- по заголовкам строк и столбцов (п.1.3);

- с использованием ссылок (п.1.4);

- ручная консолидация (п.1.5).

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

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

Проблемам связывания объектов посвящен раздел 2.

Общее описание процесса консолидации

Процесс консолидации предполагает обязательное указание

- диапазона назначения;

- источников данных;

- способа консолидации;

- наличия связи между объектами консолидации;

- типа (функции) консолидации.

Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).

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

- отдельной ячейки;

- строки ячеек;

- столбца ячеек;

- диапазона ячеек, куда необходимо поместить обобщенные данные.

Заполнение его осуществляется по правилам, представленным в табл.1.

Таблица 1. Заполнение диапазона назначенияВыделение Результат

Ячейка Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных

Строка ячеек Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения

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

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

Примечания:

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

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

Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.

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

- выделение исходного диапазона с помощью мыши;

- ввод ссылки на диапазон с клавиатуры.

Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.

Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.

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

Синтаксис внутренней ссылки:

='Имя_листа'! Адрес_диапазона

Пример 1.

Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).

Для этого в ячейку В2 введите формулу:

='Лист2'! $D$4

Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.

Синтаксис внешней ссылки:

='Путь\[Имя_рабочей_книги]Имя_листа'! Адрес_диапазона

Пример 2.

Необходимо в ячейку В2 первого листа (Лист1) записать значение ячейки А1 из листа 2кв1996 файла КВАРТАЛ2.xls, находящегося на диске Е: в папке USERS.

ля этого в ячейку В2 следует ввести следующую формулу:

='E: \USERS\[КВАРТАЛ2.xls]2кв1996'! $A$1

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

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

Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).

Для этого вводим ссылку

=Бюджет

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

=Бухгалтерия! Бюджет

Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите='[1996.xls]Дальний Восток'! Продажи

Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:

='[C: \Бюджет\Отдел продаж.xls]Февраль'! Оборот

Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Например '[1996.xls]'! Продажи или '[C: \Бюджет\Отдел продаж.xls]'! Оборот в примерах 5 и 6.

Удаление и редактирование ссылок

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

Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить.

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

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

Наличие связи между объектами. При наличии связи результаты будут обновляться при изменении данных, а в области назначения будет создана структура. Т.е. в диапазон консолидации между итоговых строк будет вставлена детальная информация, связанная внешними ссылками с исходными диапазонами. Причем, диапазон назначения будет структурирован. Верхним уровнем структуры будут итоговые данные, нижним — исходные (см. пример _8.).

Тип (функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций: - Сумма - Количество значений

- Среднее значение - Смещенное отклонение

- Максимум - Несмещенное отклонение

- Минимум - Смещенная дисперсия

- Произведение - Несмещенная дисперсия

- Количество чисел

По умолчанию используется функция Сумма.

Опишите, как создается сводная таблица на основе списка.

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

Откроется следующее диалоговое окно:

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

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

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

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

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

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

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

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

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

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

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

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

Настройка нашей таблицы должна выглядеть вот так:

Тогда наша сводная таблица будет иметь следующий вид:

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

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

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

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

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

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

3. Какая последовательность форматирования данных в сводной таблице.

Изменить формат таблицы можно двумя способами: путем обычного изменения формата ячеек и с помощью автоматического форматирование сводной таблицы. Чтобы отформотировать ячейку или диапазон ячеек, выделите их и выберете команду Формат – Ячейки. Когда Excel отобразит на экране диалоговое окно Формат ячеек используйте расположенные на нем вкладки для определения формата выделенного диапазона.

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

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

1. Выделите любую ячейку, содержащую число, в области данных сводной таблицы.

Щелкните правой кнопкой мыши и из появившегося контекстного меню выберите команду Параметры поля. На экране появится диалоговое окно Вычисление поля сводной таблицы.

Щелкните на кнопке Формат.

Выберите необходимый формат представления чисел.