Решение задачи с использованием СУБД MS Access и табличного процессора MS Excel

В случае использования для решения задачи двух программных средств (СУБД и табличного процессора), в разделе «Проектирование форм выходных документов и графического представления данных по выбранной задаче» необходимо представить:

• описание логической структуры записи таблиц базы данных (рис. 14, 16, 18);
• заполненные исходными данными таблицы базы данных (рис. 15, 17, 19);
• растровый рисунок окна конструктора запроса по таблицам Заказчики, Товары и Заказы (рис. 20);
• вид формы по запросу;
• отчет по запросу (рис. 21);
• таблицу с итоговыми данными по каждому заказу (рис. 11), построенную по таблице «Список заказов на сентябрь 2001 г.» с результатами запроса, экспортированную из СУБД на рабочий лист табличного процессора;
• построенную по таблице «Список заказов на сентябрь 2001 г.» сводную таблицу и результаты вычислений в графическом виде (рис. 13).

Поле	Признак ключа	Формат поля
имя поля	наименование (реквизит)	тип данных	размер поля	точность
Код фирмы	Код фирмы	*	счетчик	дл. целое
Фирма	Наименование фирмы		текстовый
Адрес	Адрес		текстовый
Телефон	Телефон		текстовый
Контактная персона	Контактная персона		текстовый

Рис. 14. Описание структуры записи таблицы Заказчики

Код фирмы	Наименование фирмы	Адрес	Телефон	Контактная персона
	Сервис	Кутузовский пр., 2	222-22-32	Сидоров В.В.
	Проект-М	Ул. Ботаническая, 3	331-09-65	Бородин К.П.
	Факториал	Ул. Тверская, 15	976-43-23	Власова И.А.
	Приват	Ул. Ордынка, 33	123-34-56	Столетова С.М.
	Медик	Ул. Грибоедова, 46	456-34-12	Кравченко А.И.

Рис. 15. Содержимое таблицы Заказчики базы данных

Поле	Признак ключа	Формат поля
имя поля	наименование (реквизит)	тип данных	размер поля	точность
Код товара	Код товара	*	счетчик	дл. целое
Товар	Наименование товара		текстовый
Характеристика	Характеристика товара		текстовый
Единица измерения	Единица измерения		текстовый
Цена, руб.	Цена, руб.		денежный

Рис. 16. Описание структуры записи таблицы Товары

Код товара	Товар	Характеристика	Единица измерения	Цена, руб.
	Изготовление дверей	Материал фирмы, сосна	кв. м.	3000.00
	Изготовление фундамента	Материал фирмы, бетон	куб. м.	1200.00
	Кровельные работы	Материал фирмы, сосна	кв. м.	300.00
	Пиломатериалы	Хвойные породы	куб. м.	3000.00

Рис. 17. Содержимое таблицы Товары базы данных

Поле	Признак ключа	Формат поля
имя поля	наименование (реквизит)	тип данных	размер поля	точность
Код	Номер записи	*	счетчик	дл. целое
Дата	Дата		дата
Код фирмы	Код фирмы		числовой	дл. целое
Код заказа	Код заказа		числовой	дл. целое
Код товара	Код товара		числовой	дл. целое
Количество	Количество		числовой	дл. целое

Рис. 18. Описание структуры записи таблицы Заказы

Код	Дата	Код фирмы	Код заказа	Код товара	Количество
	02.09.01
	03.09.01
	05.09.01
	05.09.01
	08.09.01
	15.09.01
	15.09.01
	17.09.01
	30.09.01

Рис. 19. Содержимое таблицы Заказы базы данных

Рис. 20. Растровый рисунок окна конструктора запроса по связанным таблицам

Рис. 21. Отчет по запросу

Варианты заданий

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

Вариант 1

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик точки по заданному закону движения».

Значения параметров принять а= 1, b= 3.

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

Задача. Необходимо построить таблицы по приведенным ниже формам. Для получения значений итоговых граф используйте расчетные формулы.

По данным таблицы на рис. 2 постройте гистограмму с заголовком, названием осей координат и легендой.

№ лицевого счета	Вид вклада	Сумма вклада
остаток входящий, тыс. руб.	приход, тыс. руб.	расход, тыс. руб.	остаток исходящий, тыс. руб.
R6798	До востребования
F5774	Праздничный
S3354	Срочный
G6723	До востребования
Z3421	Срочный

Рис. 1. Операционный дневник

Расчетная формула: гр. 6 = гр. 3 + гр. 4 - гр. 5.

№ лицевого счета	Вид вклада	Остаток вклада
R6798	До востребования
F5774	Праздничный
S3354	Срочный
G6723	До востребования
Z3421	Срочный

Рис. 2. Остаток вклада с начисленным процентом

Вид вклада	Процентная ставка, %
До востребования
Праздничный
Срочный

Рис. 3. Процентная ставка

Используя расширенный фильтр, создайте список вкладов – Срочный и До востребования, где исходящий остаток больше 50 тыс.р.

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

Вариант 2

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Нахождение закона движения и кинематических характеристик точки колеса, катящегося без скольжения».

Значение параметра принять а= 1.2.

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

Задача. Агентство по грузоперевозкам предоставляет услуги по перевозке грузов по различным маршрутам. Данные о маршрутах, выполненных в течение недели, по каждому водителю приведены на рис. 1. Справочные данные о технических характеристиках автомобилей и протяженности маршрутов приведены на рис. 2.

Необходимо:

- Построить таблицы по приведенным данным.

- Выполнить расчет количества израсходованного топлива каждым водителем и веса перевезенного груза, данные расчета занести в таблицу (рис. 1).

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

- Сформировать и заполнить ведомость расхода горючего каждым водителем за неделю (рис. 3).

- Результаты расчета количества израсходованного топлива за неделю представить в графическом виде.

Рис. 1. Данные о выполненных маршрутах

Сведения о выполненных маршрутах
№ п/п	ФИО водителя	Марка автомобиля	№ рейса	Выполнено рейсов, шт.	Протяженность рейса, км	Расход топлива на 100 км, л	Израсходовано топлива, л	Грузоподъемность, тонн	Вес перевезенного груза, тонн
	Соловьев В.В.	КАМАЗ	А112
	Михайлов С.С.	ЗИЛ	С431
	Кузнецов Я.Я.	МАЗ	А112
	Иванов К.К	МАЗ	М023
	Сидоров А.А.	ЗИЛ	В447
	Волков Д.Д.	КАМАЗ	С431
	Быков Л.Л.	КАМАЗ	В447
	ИТОГО	х	х	х
	В СРЕДНЕМ	х	х	х

Рис. 2. Технические характеристики автомобилей и данные о протяженности выполняемых рейсов

Технические характеристики автомобилей			Протяженность рейсов
№ п/п	Марка автомобиля	Расход топлива на 100 км, л	Грузоподъемность, тонн			№ п/п	№ рейса	Протяжен-ность рейса, км
	ЗИЛ						А112
	КАМАЗ						В447
	МАЗ						М023
							С431

Рис. 3. Ведомость расхода горючего

	Агентство по грузоперевозкам "Летучий голландец"
			Отчетный период
			с	по
			__.__.20__	__.__.20__
	ВЕДОМОСТЬ РАСХОДА ГОРЮЧЕГО
	ФИО водителя	№ рейса	Выполнено рейсов, шт.	Израсходовано топлива, л
	Соловьев В.В.
	Михайлов С.С.
	Кузнецов Я.Я.
	Иванов К.К.
	Сидоров А.А.
	Волков Д.Д.
	Быков Л.Л.
	ИТОГО
	Бухгалтер

Используя расширенный фильтр, сформируйте список машин Камаз, у которых протяженность рейса больше средней.

Вариант 3

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Нахождение закона движения точки шатуна кривошипно-шатунного механизма».

Значение параметра принять S =1,2.

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

Задача. Необходимо построить таблицу по приведенной ниже форме (рис. 1). Для получения значений граф 5, 7 и 8 используйте расчетные формулы. Результаты округлить до двух знаков после запятой, используя функцию ОКРУГЛ.

Рассчитайте процент скидки по каждому наименованию продукции исходя из того, что процент скидки назначается в зависимости от последней цифры номенклатурного номера: 1 - 5%, 2 - 7%, 3 - 10%, 4 - 15%, 5 - 20%. Для расчета используйте функцию ЕСЛИ, а для определения последней цифры номенклатурного номера - функцию ОСТАТ.

По данным таблицы (графы 2, 5 и 8) постройте гистограмму с заголовком, названием осей координат и легендой.

Номенклатурный номер	Наименование продукции	Количество, шт.	Цена, тыс. руб.	Сумма, тыс. руб.	% скидки	Сумма скидки, тыс. руб.	Стоимость с учетом скидки, тыс. руб.
	Монитор		12,00
	Клавиатура		0,25
	Дискета		0,02
	Принтер		10,00
	Сканер		8,00

Рис. 1. Ведомость расчета стоимости продукции с учетом скидки

Расчетные формулы:

гр. 5 = гр. 3 х гр. 4;

гр. 7 = гр. 5 х гр. 6/100;

гр. 8 = гр. 5 - гр. 7.

Используя расширенный фильтр, сформируйте список наименований продукции с теми номенклатурными номерами, по которым стоимость с учетом скидки находится в пределах от 5 до 10 тыс. руб.

Вариант 4

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Нахождение закона движения точки камня в кулисном механизме».

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

Задача. Рассчитайте стоимость продукции с учетом скидки. Результаты округлите до 2-х знаков после запятой.

Номенклат. номер	Наименование прдукции	Количество (шт.)	Цена (тыс.руб)	Стоимость (тыс.руб.)	% скидки	Сумма скидки (тыс. руб.)	Стоимость с учетом скидки (тыс. руб.)
	Монитор
	Клавиатура		0,25
	Дискета		0,02
	Принтер
	Сканер
	Итого

Формулы для расчетов:

Процент скидки определяется исходя из следующего:

- 1%, если Стоимостьменее 60 тыс. руб.;

- 7%, если Стоимостьот 60 до 100 тыс. руб.;

- 10%, если Стоимостьбольше 100 тыс. руб.

Для заполнения столбца Процент скидки используйте функцию ЕСЛИ из категории «Логические».

1. Используя расширенный фильтр, сформируйте список наименований продукции с теми номенклатурными номерами, по которым стоимость с учетом скидки находится в пределах от 5 до 10 тыс. руб.

2. Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общую сумму скидки для продукции с ценой больше 5тыс. руб.,

3. Постройте объемную гистограмму изменения стоимостей по наименованиям продукции.

Вариант 5

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик поступательного движения».

Значения параметров принять a= 22 см; b =17см; d=12 см.

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

Задача. Некоторые крупнейшие компании по рыночной стоимости (капитализации) на 1 сентября 2000 года

Компания	Капитализация компании, руб.	Цена (котировка) обыкновенной акции, долл.	Число обыкновенных акций, шт.	Оценка котировки акций
ОАО "Сургутнефтегаз"		0,3863
НК "Лукойл"		16,0694
ОАО "Газпром"		0,3167
НК "Юкос"		1,6711
Мобильные телесистемы		1,4250
Ростелеком		2,3550
Аэрофлот		0,2057
Максимальная цена, долл.
Курс ЦБ на 01.09.2000 (руб/долл)		27,75

Формулы для расчетов:

Капитализация компании = Число обыкновенных акций / Цена *Курс ЦБ/ 1000000

Максимальная цена акции = максимальное значение по графе Цена обыкновенной акции (выберите соответствующую функцию в категории «Математические).

Оценка котировки акций определяется исходя из следующего:

- «спад», если цена котировки устанавливается ниже отметки 1;

- «подъем», если цена котировки устанавливается выше отметки больше 10;

- «стабильно», если цена котировки устанавливается на отметке от 1 до 10.

Для заполнения графы Оценка котировки акций используйте функцию ЕСЛИ из категории «Логические».

2. Используя расширенный фильтр, составьте список компаний, у которых число обыкновенных акций находиться в пределах от 1000000000 до 20000000000 шт.

3. Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество компаний, у которых цена за 1 акцию превышает 1 доллар.

4. Постройте объемную круговую диаграмму, отражающую уровень капитализации компаний.

Вариант 6

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «определение кинематических характеристик вращательного движения».

Значение параметра принять b= 12 cм.

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

Задача. Необходимо выполнить калькуляцию стоимости изделия с учетом акциза, НДС и налога с продаж в табличной форме (рис. 1). В табличной форме представьте платежи фирмы, вносимые в бюджет (рис. 2).

Введите текущее значение даты между таблицей и ее названием.

В графическом виде представьте структуру стоимости изделия.

№ п/п	Наименование статьи	%-я ставка	Сумма, руб.
	Стоимость материала		10000.00
	в том числе
	стоимость сырья
	сумма НДС
	сумма налога с продаж
	Стоимость работы		1000.00
	НДС на стоимость производства
	Сумма акциза
	Налог с продаж
	Итого стоимость изделия

Рис. 1. Калькуляция стоимости изделия

№ п/п	Наименование статьи	Сумма, руб.
	Налог на добавленную стоимость
	Акциз
	Налог с продаж
	Итого

Рис. 2. Платежи в бюджет, вносимые фирмой

Вариант 7

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик точек камня кулисного механизма».

Значения параметров принять a=3K; b=2K; c=10K; d=2c; K= 1.2.

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

Задача. Необходимо рассчитать таблицу доходности хозяйствующего субъекта по заданным показателям (рис. 1). Соотношение показателей установите по данным за прошлый год.

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

Показатели	Обозначения, формулы зависимости показателей	Прошлый год, млн. руб.	Отчетный год	Отклонения
План, млн. руб.	Отчет, млн. руб.	к плану	к прошлому году
абсолютное	%	абсолютное	%
Выручка от реализации продукции	Р
Налог на добавленную стоимость	Н
В процентах к выручке	Н / Р × 100	16.38
Себестоимость продукции	С
В процентах к выручке	С / Р × 100	67.24
Прибыль от реализац. продукции	П = Р – Н – С
Уровень рентабельности	П / С × 100	24.36
Прибыль от реализации продукции в % к выручке	П / Р×100	16.38
Прибыль от прочей реализации	ПП
Доходы от внереализац. операций	ДВ
Расходы от внереализац. операций	РВ
Балансовая прибыль	БП = П + ПП + ДВ – РВ
Налоги, выплачиваемые из прибыли	НП
Чистая прибыль	БП – НП

Рис. 1. Показатели доходности хозяйствующего субъекта

Используя расширенный фильтр, сформируйте список показателей, по которым суммы за прошлый год находится в пределах от 5000 до 10000 млн. руб.

Вариант 8

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик звеньев и точек кривошипно-шатунного механизма».

Значение параметра принять K= 1.1 см.

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

Задача. Выполните анализ основных показателей финансово-экономической деятельности промышленных предприятий по данным, приведенным в таблице.

Классы предприятий по основным фондам, млрд. руб.	Количество	Объем товарной продукции, млрд. руб.	Численность, тыс. чел.	Место по объему товарной продукции
0 – 1		53,525	4,343
1 – 5		488,95	21,380
5 – 10		390,693	20,830
10 – 50		1964,749	68,631
50 – 100		901,538	55,899
100 – 200		717,813	40,625
> 200		103,033	71,880
Итого:

Формулы для расчетов:

Место каждого предприятия по объему товарной продукции определяется исходя из следующего:

– 1 место, если Объем больше 1000 млрд.руб.

– 2 место, если Объем больше 800 млрд.руб.

– 3 место, если Объем больше 600 млрд.руб.

Для заполнения столбца Место по объему товарной продукции, используйте функцию ЕСЛИ из категории «Логические».

1. Используя расширенный фильтр, сформируйте список классов предприятий, объем товарной продукции у которых находится в интервале от 200 до 900 млрд. руб.

2. Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общий объем товарной продукции тех предприятий, у которых численность меньше 50 тыс. чел.

3. Постройте объемную круговую диаграмму распределения численности предприятий по классам.

Вариант 9

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик звеньев и точек шарнирного четырехзвенника».

Значение параметра принять K= 1.3 см.

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

Задача. Необходимо рассчитать таблицу оценки эффективности маркетинговой деятельности по заданным показателям (соотношение показателей указаны в шапке таблицы на рис. 1).

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

Период	Доходы, млн. руб. (Д)	Прибыль, млн. руб. (П)	Расходы на маркетинг, млн. руб. (М)	Д/М	П/М	М/(Д-П)%	Рейтинг
Январь	60.4	20.0	5.0
Февраль	70.7	26.0	8.0
Март	80.8	28.0	10.0
Апрель	90.8	32.0	2.0
Май	83.5	30.0	6.0
Июнь	102.0	35.0	12.0
Июль	103.0	40.0	15.0
Август	134.0	38.5	5.0
Сентябрь	133.8	38.0	10.5
Октябрь	139.8	40.0	22.0
Ноябрь	144.5	50.0	20.0
Декабрь	153.0	50.0	20.8
Всего за период							*
В среднем за месяц				*	*	*	*

Рис. 1. Таблица показателей для оценки эффективности маркетинговой деятельности

Рейтинг определяется исходя из следующего:

– высокий, если прибыль за месяц >= 40;

– средний, если прибыль > 30 и < 40

– низкий – в ином случае.

Для заполнения строки Рейтинг, используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список месяцев, где доходы находятся в интервале от 90 до 140 млрд. руб.

Вариант 10

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Определение кинематических характеристик точек колеса планетарного механизма».

Значения параметров принять K= 1.1 см.

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

Задача. Необходимо создать таблицу данных «Автомагазин», включающую таблицы «Модели автомобилей» и «Клиенты и заказы» (рис. 1, 2).

Заполните таблицы информацией.

Создайте запрос для определения количества заявок на определенную модель.

Создайте запрос для определения клиентов из данного города с указанием всех характеристик заказанных ими автомобилей.

Создайте отчет «Модели автомобилей».

Создайте отчет «Обзор заказов» с полями: код модели, фамилия заказчика, дата заказа, выполнив группировку по полю «Код модели».

Введите текущее значение даты и времени между таблицей и ее названием.

Код модели	Модель	Цвет	Коробка передач	Обивка	Заводская цена, руб.

Рис. 1. Таблица «Модели автомобилей»

Код модели	№ заказа	Фамилия заказчика	Город	Телефон	Дата заказа

Рис. 2. Таблица «Клиенты и заказы»

Вариант 11

Задание 1. Изучить возможности использования пакета MathCAD для реализации инженерных расчетах посредством решения следующей задачи.

Задача: «Равновесие произвольной плоской системы сил».

Значение параметра принять K= 1.1 м.

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

Задача. Фирма предоставляет услуги по перевозке грузов. Для определения затрат на приобретение материалов ежемесячно ведется учет количества приобретаемого топлива. Данные о ценах и количестве приобретенного топлива в течение месяца приведены на рис. 1.

1. Построить таблицы по приведенным ниже данным.

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

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

4. Сформировать и заполнить сводную ведомость с диаграммой затрат на приобретение топлива за квартал, определить среднюю цену 1 л топлива за квартал (рис. 2).

5. Результаты расчета средней цены 1 л топлива по каждому месяцу и по каждому виду топлива представить в графическом виде.

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

Дата добавления: 2014-12-10