Выполнение лабораторной работы. Лабораторная работа №2. Консолидация баз данных и сводные таблицы в Excel.

Лабораторная работа №2. Консолидация баз данных и сводные таблицы в Excel.

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

Начало работы: Открыть программу Word и создать файл «Отчет по лаборатории №2-Е»; Открыть программу Excel и создать файл «Лабораторная работа №2-Е».

Открыть Интернет для поиска значений команд по справочным системам.

 

 

Задание.

1. Изучить теоретическое обоснование.

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

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

4. Создать мини-глоссарий команд и поместить в Отчет.

5. Оформить отчет: Times New Roman, 14; поля: верхнее – 2, нижнее – 1,5 левое – 3, правое – 1,5. Общая страница – не менее 15 стр.

6. Защищать Отчет у преподавателя.

Заключение.

В результате выполнения лабораторной работы вы должны знать и уметь выполнять:

консолидацию данных базы,

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

Выполнение лабораторной работы.

 

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

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

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

Рис.1а Таблица для консолидации данных

Именно эту таблицу используем для консолидации данных. В таблице повторяются названия производителей. Зададимся целью получить объединенные данные по продажам продукции для каждого производителя за период с января по апрель 2000 года. Консолидированную таблицу разместим в новом листе.

Решение задачи складывается из следующих шагов.

Создаем новый лист. Находясь в этом листе, вызываем диалоговое окно Консолидация (ДАННЫЕ->Консолидация…) (Data-Consolidate…) (рис.1).

 

Рис.1

 

 

Заполняем поля окна, как показано на рис.1. В поле Функция (Function) задается функция, применяемая к консолидируемым данным. Поскольку мы хотим получить объединенные данные по продажам для каждого производителя, то выбираем функцию Сумма (Sum).

В поле Ссылка (Reference) задается диапазон, для которого выполняется консолидация данных. База данных находится на Листе «Копия основной таблицы», поэтому указываем её координат записей с января по апрель (см. рис.1 предыдущей работы). Чтобы в верхней строке консолидированной таблицы отобразились названия полей, а ключевым полем считался крайний левый столбец выделенного фрагмента, включаем соответствующие переключатели Подписи верхней строки и Значения левого столбца. Включение флажка Создавать связи с исходными данными позволяет получить консолидированную таблицу, в которой будут происходить изменения при изменении в исходной базе данных (в консолидируемых полях). После нажатия OK получаем консолидированную таблицу (рис.2).

 

Рис.2

 

Можно изменить структуру консолидированной таблицы, если не будут нужны некоторые столбцы. Предположим, что поля Вид и Цена нас не интересуют, их можно удалить и получить модифицированную консолидированную таблицу (рис.3).

Рис.3

 

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

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

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

Сводные таблицы создаются с помощью Мастера сводных таблиц за три шага.

Шаг 1. Выделяем любую ячейку исходной таблицы. Выбираем команду ДАННЫЕ->Сводная таблица… (Data-PivotTable Report…). Результатом станет появление окна рис.4.

Рис.4

 

Устанавливаем переключатели как показано на рисунке и нажимаем кнопку Далее (Next).

Шаг 2. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.5.) задаем диапазон ячеек исходной таблицы. Нажимаем кнопку Далее (Next).

 

Рис.5

 

Шаг 3. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.6.) устанавливаем переключатель Новый лист, чтобы сводная таблица была построена на новом листе. Нажимаем кнопку Макет (Layout…).

 

Рис.6

 

В появившемся диалоговом окне (рис.7) показан макет сводной таблицы. Перетаскиваем кнопки с наименованиями полей исходной таблицы на план-макет сводной таблицы.

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

Рис.7

 

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

Рис.8

 

После перетаскивания нажимаем OK, и вернувшись в окно рис.6, нажимаем кнопку Готово (Finish).

В результате создается новый лист и в нем сводная таблица (рис.9).

 

Рис.9

В сводной таблице (рис.9) представлены данные производителей по всем продукциям. Вертикальный столбец «Общий итог» указывает на общее количество всех видов продукции (общее количество всех видов молока) для одного производителя, а строка «Общий итог» указывает на общее количество одной продукции всех производителей.

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

Рис.10

 

Контрольные вопросы:

 

1. Что такое консолидация?

2. Какие данные можно консолидировать?

3. Какие данные нельзя консолидировать?

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

5. Чем отличается сводная таблица от консолидированной?

6. В чем вы видите преимущества сводной таблицы в сравнении с консолидированной?

7. Что такое диаграмма?

8. Какими преимуществами обладает диаграмма по сравнению со сводной таблицей?

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

10. Для чего нужен переключатель «подписи верхней строки» в окне «Консолидация»?

11. На что указывает переключатель «значения левого столбца» в окне «Консолидация»?

12. Что значит «принимать решение» с точки зрения информационных технологий?