Автоматическое подведение итогов

Задание 4.

Дан список со следующими полями (рис. 9): N п/п, Продавец, Товар, Номер партии, Цена, Количество, Итого, Дата продажи, Покупатель. Определить количество товаров, проданных конкретным продавцом за конкретную дату.

Рис. 9. Список продаж

Решение

1. Выделите список (или – установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала – по полю Продавец, затем – по полю Дата продажи (рис. 10).

2. Примените команду Данные | Промежуточные итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 11: для получения верхнего (первого) уровня итогов – общее количество товаров, проданных конкретным продавцом.

3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Промежуточные итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 12.

4. Полученные промежуточные итоги представлены на рис. 13.

 

Рис. 11. Сортировка списка

Рис. 12. Окно Промежуточные итоги для получения итогов по полю Продавец

Рис. 13. Окно Промежуточные итоги для получения итогов по полю Дата продажи

Рис. 14. Вложенные промежуточные итоги

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

Задание 5.

Данные, которые представлены в виде списка со следующими полями: (рис. 15): Товар, Стоимость, Количество, расположены на листе 1, листе 2 и листе 3. Объединить данные о количестве и стоимости проданных товаров в сети магазинов.

Решение.

1. Укажите местоположение будущих консолидированных данных.

2. Выберите команду Данные | Консолидация.

3. Заполните окно Консолидация в соответствии с данными рис. 16. Объединенные данные представлены на рис. 17.

 

 

Рис. 15. Исходные данные Лист1, Лист2, Лист3

Рис. 16. Ввод данных в окно Консолидация

Рис. 17. Представление консолидированных данных

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

Задание 6.

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

Решение.

1. Сводные таблицы создаются с помощью команды Вставка | Сводная таблица. Задайте исходный диапазон данных и выберите место для сводной таблицы (рис. 18).

2. Установите необходимые параметры сводной таблицы в Списке полей сводной таблицы (рис. 19).

3. Для определения необходимой операции для поля, помещенного в область Значения:вывода итогов по средней цене, щелкните левой кнопкой мыши на поле Цена и выберите в контекстном меню пункт Параметры полей значений…. Заполните диалоговое окно как показано на рис. 20.

4. Для задания вычисляемого поля выполните команду Параметры | Формулы и выполните необходимые действия в окне Вставка вычисляемого поля (рис. 21).

 

Рис. 18. Определение местоположения данных для сводной таблицы

 

Рис. 19. Формирование макета сводной таблицы

 

Рис. 20. Окно Параметры поля значений

 

Рис. 21. Окно Вставка вычисляемого поля

 

5. Выполните группировку по полю Год выпуска – после создания сводной таблицы выделите мышью необходимые года выпуска и воспользуйтесь пунктом контекстного меню Группировать.

6. Подготовленная сводная таблица представлена на рис. 22.

 

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

 

Задание для самостоятельной работы

1. Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Марка автомашина

Цифры номера

Буквы номера

Год выпуска

Год приобретения

Цвет машины

Пробег

Цена

Владелец

2. Используя автофильтр, вывести все записи, удовлетворяющие заданным критериям:

− определить белые Ауди, год выпуска которых больше 2006, но меньше либо равен 2016. Отсортировать полученные данные по возрастанию года выпуска автомобилей.

− определить красные Форды, пробег которых больше либо равен 150000 км, а цена – меньше либо равна 10000 у.е. Отсортировать полученные данные по убыванию цены.

3. Используя расширенный автофильтр, вывести все записи, удовлетворяющие заданным критериям:

− определить белые и черные Мерседесы или Ауди, год выпуска которых больше 2006 года, а пробег находится в пределах от 150000 до 200000 км.

− определить машины, год выпуска и год приобретения которых совпадает, а также – вторая буква номера которых «С» или «Х».

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

− средняя, максимальная и минимальная цены определенной марки машины с учетом конкретного года приобретения.

− суммарный и средний пробег определенной марки машины с учетом конкретного года выпуска.

5. Используя консолидацию, вывести все записи, удовлетворяющие заданному критерию:

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

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

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

«Создание приложений средствами Microsoft Access»

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

В архитектуре БД выделяют следующие три уровня:

1. Внутренний – уровень, определяющий физический вид БД и связан со способами сохранения информации на физических устройствах хранения.

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

3. Внешний уровень – структурный уровень БД, определяющий пользовательские представления данных.

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

В 1970 году появилась статья Тэдда Кодда о представлении данных, организованных в виде двумерных таблиц, называемых отношениями. С этого момента реляционная модель широко используется при создании различных БД.

Основные объекты реляционной модели данных перечислены в табл. 1.

Таблица 1

Основные объекты реляционной модели данных

Реляционный термин	Описание
База данных	Набор таблиц и других объектов, необходимых для абстрактного представления части реального мира (решаемой задачи)
Схема базы данных	Набор заголовков таблиц, взаимосвязанных друг с другом
Отношение	Таблица – совокупность объектов реального мира, которые характеризуются общими свойствами и характеристиками (поля таблицы)

Продолжение табл.1

Заголовок отношения	Заголовок таблицы – названия полей (столбцов) таблицы
Тело отношения	Тело таблицы – совокупность значений для всех объектов реального мира, которая представима в виде записей таблицы
Схема отношения	Строка заголовков столбцов таблицы (заголовок таблицы)
Атрибут отношения	Наименование столбца таблицы (поле таблицы)
Кортеж отношения	Строка таблицы (запись) – однозначное представление объекта реального мира, созданное с использованием значений полей таблицы
Домен	Множество допустимых значений атрибута
Значение атрибута	Значение поля в записи
Первичный ключ	Один или несколько атрибутов, который уникальным (единственным) образом определяет значение кортежа (значение строки таблицы)
Внешний ключ	Атрибут таблицы, значения которого соответствуют значениям первичного ключа в другой связанной таблице.
Степень (арность) отношения	Количество столбцов таблицы
Мощность отношения	Количество строк таблицы (количество кортежей)
Тип данных	Тип значений элементов таблицы
Базовое отношение	Отношение, которое содержит один или несколько столбцов, характеризующих свойства объекта, а также первичный ключ
Связь	Устанавливает взаимосвязь между совпадающими значениями в ключевых полях – первичным ключом одной таблицы и внешним ключом другой таблицы
Связь «один-к-одному»	При использовании такого вида связи запись в одной таблице может иметь не более одной связанной записи в другой таблице

 

 

Окончание табл. 1

Связь «один-ко-многим»	При использовании такого вида связи записи первой таблицы может соответствовать несколько записей второй таблицы, а каждой записи второй таблицы может соответствовать лишь одна запись первой таблицы. В первой таблице обязательно должен быть задан первичный ключ, во второй – внешний.
Связь «многие-ко-многим»	При данном типе связи одной записи в первой таблице может соответствовать несколько записей второй таблицы, и наоборот. Уникальность ключей для таких таблиц не требуется.

Основные элементы данных реляционной модели представлены на рис.1.

Рис. 1. Элементы данных реляционной модели

При создании проекта СУРБД MS Access можно придерживаться следующих этапов:

1. Разработка общих концепций будущей системы – выяснение требований пользователей и сбор информации по конкретной предметной области.

2. Проектирование отчётов: планирование состава и внешнего вида выходных документов создаваемого приложения.

3. Определение структуры данных, которая является основой создаваемого приложения. Данные, полученные на предыдущих 2 этапах, анализируются и разбиваются по группам, исключающим повторение, но предусматривающим определённую взаимосвязь и восприятие их как единого целого в виде совокупности таблиц с необходимыми заголовками. Связь между таблицами осуществляется в виде повторяющихся полей, т.е. в одной таблице данные являются уникальными (не имеют повторений в пределах данного столбца), а в другой – возможно дублирование данных в аналогичном столбце. На этом этапе предусматривается также детальная проработка полей таблиц, типов хранимых данных, связей между таблицами, ограничений на данные и т.д.

4. Разработка структуры запросов к БД, т.е. определение тех выборок данных из базы, которые необходимы для создания отчётов и получения определённой информации, участвующей в тех или иных документах приложения.

5. Проектирование экранных форм приложения:

· создание пользовательских окон, предназначенных для ввода и редактирования информации (в таблицы);

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

6. Придание приложению MS Access законченного вида. На этом этапе разрабатываются кнопочные и обычные меню, а также ключевые формы приложения для облегчения работы пользователей СУРБД.

Основу СУРБД MS Access составляют 7 объектов.

Таблица – основная единица хранения данных в базе. Понятие таблицы в MS Access соответствует понятию двумерной таблицы (запись X поле) реляционной модели данных. Среди основных операций над данными можно выделить: просмотр и обновление (ввод, модификация и удаление), сортировку, фильтрацию и печать.

Запросы – позволяют осуществить выборку данных по некоторому критерию или выполнить определённые действия с данными.Одновременно выборка может производиться из 16 таблиц. В запрос можно включать до 255 полей.

Форма представляет собой объект БД MS Access, в котором можно разместить элементы управления, принимающие действия пользователей или служащие для ввода, отображения и изменения данных в таблицах или запросах.

Отчёты предназначены для печати данных, содержащихся в таблицах и запросах в соответствии с некоторыми правилами оформления.

Страница доступа к данным используется для добавления, редактирования, просмотра или манипулирования текущими данными в БД MS Access или SQL – сервера.

Макросы представляют собой последовательность макрокоманд встроенного языка MS Access, которые задают автоматическое выполнение определённых операций.

Модуль – совокупность описаний, инструкций и процедур на языке VBA, сохранённая под общим именем.

Создание базы данных

Работа по созданию таблиц и схемы данных может быть разбита на следующие этапы:

1. Создание и определение структуры таблиц:

· создание новой таблицы;

· определение полей, типов данных, описаний (при необходимости) и свойств полей, включая маски ввода и условия на значение;

· задание первичного ключа (возможны составные);

· создание индексов для необходимых полей;

· сохранение таблицы в базе.

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

3. Определение полей подстановки для удобства работы с данными.

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

· Некоторая фирма занимается реализацией и доставкой товаров;

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

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