- •Лабораторные работы Базы данных в Excel
- •Лабораторная работа № 1
- •Лабораторная работа Консолидация баз данных и сводные таблицы Excel.
- •Изучить теоретическое обоснование.
- •Оформить отчет: Times New Roman, 14; поля: верхнее – 2, нижнее – 1,5 левое – 3, правое – 1,5. Общая страница – не менее 15 стр.
- •Защищать Отчет у преподавателя.
- •Консолидация данных
- •Лабораторная работа Построение диаграмм в Excel
- •Изучить теоретическое обоснование.
- •Изучить теоретическое обоснование.
Лабораторная работа Консолидация баз данных и сводные таблицы Excel.
Цель занятия.
Изучение консолидации данных базы данных и построения сводных таблиц и диаграмм по данным исходных таблиц.
Начало работы: Открыть программу Word и создать файл «Отчет по лаборатории №2»; Открыть программу Excel и создать файл «Лабораторная работа №2».
Открыть Интернет для поиска значений команд по справочным системам.
Задание.
Изучить теоретическое обоснование.
Проанализировав структуру базы данных созданной вами в ходе предыдущей лабораторной работы сформулировать и реализовать требование по построению таблицы консолидированных данных. Сохранить файл, содержащий консолидированные данные. Задание по консолидации и таблицу и копию экрана поместить в файл отчета. Дать объяснение каждой копии и каждой команде.
Проанализировав структуру базы данных созданной вами в ходе предыдущей лабораторной работы сформулировать и реализовать требование по построению сводной таблицы данных. Сохранить файл, содержащий сводные таблицу и диаграмму. Задание по построению сводной таблицы, саму таблицу и копию экрана поместить в файл отчета. Дать объяснение каждой копии и каждой команде.
Оформить отчет: Times New Roman, 14; поля: верхнее – 2, нижнее – 1,5 левое – 3, правое – 1,5. Общая страница – не менее 15 стр.
Защищать Отчет у преподавателя.
Заключение.
В результате выполнения лабораторной работы вы должны знать и уметь выполнять:
консолидацию данных базы
построения сводных таблиц и диаграмм по данным исходных таблиц
Теоретическое обоснование.
Консолидация данных
Консолидация – это объединение данных, находящихся в одной таблице, разных таблицах на одном рабочем листе, на разных листах или разных книгах. Важным при этом является то, что таблицы должны иметь одинаковую структуру. Итоговая таблица, получаемая в результате консолидации, будет иметь одну строку-заголовок с ключевым полем (ключевым считается самое левое поле исходной таблицы или ее выделенной части) и данные, преобразованные в зависимости от того, какая функция преобразования будет выбрана.
Применение консолидации данных рассмотрим на примере. В качестве исходных данных будем использовать исходную базу данных созданную вами в предыдущей работе (см. рис.15 предыдущей работы).
В таблице повторяются названия издательств. Зададимся целью получить объединенные данные по продажам для каждого издательства за период с января по апрель 2000 года. Консолидированную таблицу разместим в новом листе.
Решение задачи складывается из следующих шагов.
Создаем новый лист. Находясь в этом листе вызываем диалоговое окно Консолидация (ДАННЫЕ->Консолидация…) (Data-Consolidate…) (рис.1).
Рис.1
Рис.1-mac
Заполняем поля окна, как показано на рисунке. В поле Функция (Function) задается функция, применяемая к консолидируемым данным. Поскольку мы хотим получить объединенные данные по продажам для каждого издательства, то выбираем функцию Сумма (Sum).
В поле Ссылка (Reference) задается диапазон, для которого выполняется консолидация данных. База данных находится на Листе 1. Поэтому указываем его до координат записей с января по апрель (см. рис.15 предыдущей работы). Чтобы в верхней строке консолидированной таблицы отобразились названия полей, а ключевым полем считался крайний левый столбец выделенного фрагмента, включаем соответствующие переключатели. Включение флажка Создавать связи с исходными данными позволяет получить консолидированную таблицу в которой будут происходить изменения при изменении в исходной базе данных (в консолидируемых полях). После нажатия OK получаем консолидированную таблицу (рис.2).
Рис.2
Поскольку поля Жанр и Цена нас не интересуют их можно удалить и получить модифицированную консолидированную таблицу (рис.3).
Рис.3
Сводные таблицы.
Сводные таблицы являются средством трансформации и анализа исходных данных. Как и для предыдущих задач, процесс построение сводной таблицы рассмотрим на конкретном примере. Прежде всего, сформулируем описание структуры сводной таблицы. Сводная таблица должна нести наглядную информацию о количестве заказанных книг по каждому издательству и жанру.
Сводные таблицы создаются с помощью Мастера сводных таблиц за три шага.
Шаг 1. Выделяем любую ячейку исходной таблицы. Выбираем команду ДАННЫЕ->Сводная таблица… (Data-PivotTable Report…). Результатом станет появление окна рис.4.
Рис.4
Рис.4-mac
Устанавливаем переключатели как показано на рисунке и нажимаем кнопку Далее (Next).
Шаг 2. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.5.) задаем диапазон ячеек исходной таблицы. Нажимаем кнопку Далее (Next).
Рис.5
Рис.5-mac
Шаг 3. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.6.) устанавливаем переключатель новый лист чтобы сводная таблица была построена на новом листе. Нажимаем кнопку Макет (Layout…).
Рис.6
Рис.6-mac
В появившемся диалоговом окне (рис.7) показан макет сводной таблицы. Перетаскиваем кнопки с наименованиями полей исходной таблицы на план-макет сводной таблицы.
Выбор кнопок для перетаскивания и место их расположения на самом макете определяется требуемой структурой сводной таблицы.
Рис.7
Рис.7-mac
В соответствии с требованиями к структуре сводной таблицы сформулированных выше расположим названия издательств в строках, названия жанров в столбцах, а количества заказанных книг соответствующего издательства и жанра составят содержание поля данных (рис.8).
Рис.8
Рис.8-mac
Нажимаем OK. и вернувшись в окно рис.6 нажимаем кнопку Готово (Finish).
В результате создается новый лист и в нем сводная таблица (рис.9).
Рис.9
Рис.9-mac
С помощью кнопки Мастер диаграмм на панели Сводные таблицы можно выбрать тип диаграммы для графического представления данных сводной таблицы.
Рис.10