Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МИНОБРНАУКИ РОССИИ
Кумертауский филиал
федерального государственного бюджетного образовательного учреждения
высшего профессионального образования
«Оренбургский государственный университет»
(Кумертауский филиал ОГУ)
Кафедра общих математических и естественнонаучных дисциплин
|
Утверждаю Директор _____________________В.А. Анищенко подпись «____»_________________200… г |
Методические указания по проведению
лабораторно-практических работ
по дисциплине
«Экономическая информатика»
Направление подготовки: 080100 «Экономика и управление»
Специальность: 080100 – «Экономика»
Специализация: 080100.62 – «Бакалавр экономики»
Форма обучения: очная
Кумертау 2011г.
Перечень лабораторных работ по дисциплине
«Информатика».
Лабораторная работа №1
Тема: Прогнозирование и регрессионный анализ в Excel .
Лабораторная работа №2
Тема: Подбор параметров, поиск решения и сценариев в электронных таблицах MS Excel.
Лабораторная работа №3-4
Тема: Решение оптимизационных задач в Excel с помощью инструмента Принятие решений.
Лабораторная работа №5
Тема: Промежуточные итоги и консолидация данных в электронной таблице MS Excel.
Лабораторная работа №6
Тема: Построение сводной таблицы в и анализ данных с помощью электронной таблицs MS Excel.
Лабораторная работа №7
Тема: Проектирование многотабличных баз данных в MS Access
Лабораторная работа №8
Тема: Элемент управления типа «Поле со списком»
Лабораторная работа №9
Тема: Элемент управления типа «Флажок»
Лабораторная работа №10
Тема: Создание связей между таблицами в MS Access
Лабораторная работа №11
Тема: Создание запросов в режиме Конструктора.
Лабораторная работа №12
Тема: Запрос на выборку. Критерии отбора записей.
Лабораторная работа № 13
Тема: Параметрические запросы.
Лабораторная работа № 14
Тема: Создание вычисляемых полей в запросах. Использование построителя выражений.
Лабораторная работа № 15
Тема: Форматирование форм в MS Access.
Лабораторная работа № 16
Тема: Построение кнопочных форм в MS Access.
Лабораторная работа № 17
Тема: Создание отчетов с помощью мастеров в MS Access.
Лабораторная работа № 18
Тема: Создание и редактирование отчетов с помощью конструктора в MS Access.
Лабораторная работа №1.
Тема: Прогнозирование и регрессионный анализ в Excel.
Цель работы: изучить способы представления числовой информации с помощью систем счисления.
Теоретический материал
Уравнение линейной парной регрессии.
Уравнение линейной парной регрессии выглядит следующим образом: Y=a0+а1X
При помощи этого уравнения переменная Y выражается через константу a0 и угол наклона прямой (или угловой коэффициент) а1, умноженный на значение переменной X. Константу a0 также называют свободным членом, а угловой коэффициент - коэффициентом регрессии. Параметры уравнения могут быть определены с помощью метода наименьших квадратов (МНК)
Метод наименьших квадратов
(в справочных системах англоязычных программ - Least Squares Мethod, LS) является одним из основных методов определения параметров регрессионных уравнений, дающий наилучшие линейные несмещенные оценки. Именно он используется в MS Excel. Линейные – относится к характеру взаимосвязи переменных. Несмещенные значит, что ожидаемые значения коэффициентов регрессии должны быть истинными коэффициентами. То есть точки, построенные по исходным данным , должны лежать как можно ближе к точкам линии регрессии. Сущность данного метода заключается в нахождении параметров модели, при которых сумма квадратов отклонений эмпирических (фактических) значений результирующего признака от теоретических, полученных по выбранному уравнению регрессии, то есть:
,
где – значение, вычисленное по уравнению регрессии; – отклонение (ошибка, остаток) (рис. 1); n – количество пар исходных данных.
Рис. 1 Понятие отклонения для случая линейной регрессии
В регрессионном анализе предполагается, что математическое ожидание случайной величины равно нулю и ее дисперсия одинакова для всех наблюдаемых значений Y. Отсюда следует, что рассеяние данных возле линии регрессии должно быть одинаково при всех значениях параметра X. В случае, показанном на рис. 2 данные распределяются вдоль линии регрессии неравномерно, поэтому метод наименьших квадратов в этом случае неприменим.
Рис.2. Неравномерное распределение исходных точек вдоль линии регрессии
Проведя необходимые преобразования, получим систему двух уравнений с двумя неизвестными а0 и а1, которые найдем решив систему.
(1)
(2)
Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициента регрессии (коэффициента а1).
Если знак при коэффициенте регрессии - положительный, связь зависимой переменной с независимой будет положительной. В нашем случае знак коэффициента регрессии положительный, следовательно, связь также является положительной.
Если знак при коэффициенте регрессии - отрицательный, связь зависимой переменной с независимой является отрицательной (обратной).
Для анализа общего качества уравнения уравнения регрессии используют обычно множественный коэффициент детерминации R2, называемый также квадратом коэффициента множественной корреляции R. R2 (мера определенности) всегда находится в пределах интервала [0;1].
Если значение R2 близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значение R-квадрата, близкое к нулю, означает плохое качество построенной модели.
Коэффициент детерминации R2 показывает, на сколько процентов () найденная функция регрессии описывает связь между исходными значениями факторов X и Y
где – объясненная вариация; – общая вариация (рис.3).
Рис. 3 Графическая интерпретация коэффициента детерминации для случая линейной регрессии
Соответственно, величина показывает, сколько процентов вариации параметра Y обусловлены факторами, не включенными в регрессионную модель. При высоком () значении коэффициента детерминации можно делать прогноз для конкретного значения .
Нелинейная регрессия
Рассмотрим наиболее простые случаи нелинейной регрессии: гиперболу, экспоненту и параболу. При нахождении коэффициентов гиперболы и экспоненты используют прием приведения нелинейной регрессионной зависимости к линейному виду. Это позволяет использовать для вычисления коэффициентов функций регрессии выше приведенные формулы.
Гипербола. Для приведения уравнения вида к линейному виду вводят новую переменную , тогда уравнение гиперболы принимает линейный вид . После этого используют формулы (1) и (2) для нахождений линейной функции, но вместо значений используются значения :
; . (3)
Экспонента. Для приведения к линейному виду уравнения экспоненты проведем логарифмирование:
;
;
.
Введем переменные и , тогда , откуда следует, что можно применять формулы (1) и (2), в которых вместо значений надо использовать :
; (4)
При этом мы получим численные значения коэффициентов и , от которых надо перейти к и , используемых в модели экспоненты. Исходя из введенных обозначений и определения логарифма, получаем
, .
Парабола. Для нахождения коэффициентов уравнения параболы необходимо решить линейную систему из трех уравнений:
Сила регрессионной связи для гиперболы и параболы определяется непосредственно по той же формуле что и для линейной модели. При вычислении коэффициента детерминации для экспоненты все значения параметра Y (исходные, регрессионные, среднее) необходимо заменить на их логарифмы, например, – на и т.д.
Если функция регрессии определена, интерпретирована и обоснована, и оценка точности регрессионного анализа соответствует требованиям, можно считать, что построенная модель и прогнозные значения обладают достаточной надежностью.
Прогнозные значения, полученные таким способом, являются средними значениями, которые можно ожидать.
Методические рекомендации
Для проведения регрессионного анализа и прогнозирования необходимо:
Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel
Режим работы "Регрессия" служит для расчета параметров уравнения линейной регрессии и проверки его адекватности исследуемому процессу.
Для решения задачи регрессионного анализа в MS Excel выбираем в меню Сервис команду Анализ данных и инструмент анализа "Регрессия".
В появившемся диалоговом окне задаем следующие параметры:
После нажатия кнопки ОК в выходном диапазоне получаем отчет.
Пример выполнения лабораторной работы
Задача: Некоторая фирма занимается поставками различных грузов на короткие расстояния внутри города. Оценить стоимость таких услуг, зависящую от затрачиваемого на поставку времени. В качестве наиболее важного фактора, влияющего на время поставки, выбрано пройденное расстояние. Были собраны исходные данные о десяти поставках (таблица 1)
Таблица 1
|
Расстояние, миль |
3,5 |
2,4 |
4,9 |
4,2 |
3,0 |
1,3 |
1,0 |
3,0 |
1,5 |
4,1 |
|
Время, мин |
16 |
13 |
19 |
18 |
12 |
11 |
8 |
14 |
9 |
16 |
Определите характер зависимости между расстоянием и затраченным временем, используя мастер диаграмм MS Еxcel, проанализируйте применимость метода наименьших квадратов, постройте уравнение регрессии, используя МНК, проанализируйте силу регрессионной связи. Проведите регрессионный анализ, используя режим работы "Регрессия" в MS Еxcel и сравните с результатами, полученными ранее. Сделайте прогноз времени поездки на 2 мили. Посчитать и построить графически меру ошибки регрессионной модели используя табличный процессор Excel.
Решение
На графике строим исходные данные по десяти поездкам.
Рис.4. График исходных данных и предполагаемая линия регрессии
Помимо расстояния на время поставки влияют пробки на дорогах, время суток, дорожные работы, погода, квалификация водителя, вид транспорта. Построенные точки не находятся точно на линии, что обусловлено описанными выше факторами. Но эти точки собраны вокруг прямой линии, поэтому можно предположить линейную связь между параметрами. Все исходные точки равномерно распределены вдоль предполагаемой прямой линии, что позволяет применить метод наименьших квадратов.
Вычислим суммы, необходимые для расчета коэффициентов уравнения линейной регрессии и коэффициента детерминации R2 с помощью вспомогательной таблицы (таблица 2).
Таблица 2
|
3,5 |
16 |
12,25 |
56,00 |
15,223 |
2,634129 |
5,76 |
|
2,4 |
13 |
5,76 |
31,2 |
12,297 |
1,697809 |
0,36 |
|
4,9 |
19 |
24,01 |
93,1 |
18,947 |
28,59041 |
29,16 |
|
4,2 |
18 |
17,64 |
75,60 |
17,085 |
12,14523 |
19,36 |
|
3,0 |
12 |
9,00 |
36,00 |
13,893 |
0,085849 |
2,56 |
|
1,3 |
11 |
1,69 |
14,30 |
9,371 |
17,88444 |
6,76 |
|
1,0 |
8 |
1,00 |
8,00 |
8,573 |
25,27073 |
31,36 |
|
3,0 |
14 |
9,00 |
42,00 |
13,893 |
0,085849 |
0,16 |
|
1,5 |
9 |
2,25 |
13,50 |
9,903 |
13,66781 |
21,16 |
|
4,1 |
16 |
16,81 |
65,60 |
16,819 |
10,36196 |
5,76 |
|
28,9 |
136 |
99,41 |
435,30 |
– |
112,4242 |
122,4 |
.
Вычислим коэффициенты линейной регрессии по формулам (1) и (2):
Таким образом, искомая регрессионная зависимость имеет вид:
Наклон линии регрессии 2,66 минут на милю – это количество минут, приходящееся на одну милю расстояния. Координата точки пересечения прямой с осью Y 5,913 минут – это время, которое не зависит от пройденного расстояния, а обуславливается всеми остальными возможными факторами, явно не учтенными при анализе.
Вычислим коэффициент детерминации:
или 91,8%.
Проведем регрессионный анализ с использованием режима Регрессия MS Excel. Значения параметров, установленных в одноименном диалоговом окне, представлены на рис.6.
Рис. 6.
Сгенерируются результаты по регрессионной статистике, представленные в таблице 3.
Таблица 3.
|
ВЫВОД ИТОГОВ |
|
|
Регрессионная статистика |
|
|
Множественный R |
0,958275757 |
|
R-квадрат |
0,918292427 |
|
Нормированный R-квадрат |
0,90807898 |
|
Стандартная ошибка |
1,11809028 |
|
Наблюдения |
10 |
Рассмотрим представленную в таблице 3 регрессионную статистику.
Величина R-квадрат, называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала [0;1]. В нашем примере мера определенности равна 0,91829, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным и совпадает с коэффициентом детерминации R2, вычисленным по формуле.
Таким образом, линейная модель объясняет 91,8% вариации времени доставки, что означает правильность выбора фактора (расстояния). Не объясняется вариации времени поездки, которые обусловлены остальными факторами, влияющими на время поставки, но не включенными в линейную модель регрессии.
Рассчитанный уровень значимости αр=1,26E-05<0,05(показатель значимость F в таблице Дисперсионный анализ) подтверждает значимость R2.
Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y) и равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы. В простом линейном регрессионном анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона (0,95827), который вычисляется по формуле:
Теперь рассмотрим среднюю часть расчетов, представленную в таблице 4 (приведена в сокращенном варианте). Здесь даны коэффициент регрессии а1 (2,65970168) и смещение по оси ординат, т.е. константа a0 (5,913462144).
Таблица 4
|
|
Коэффициенты |
Стандартная ошибка |
t-статистика |
P-Значение |
|
Y-пересечение |
5,913462144 |
0,884389599 |
6,686489927 |
0,00015485 |
|
Переменная X 1 |
2,65970168 |
0,280497238 |
9,482095791 |
1,26072E-05 |
Исходя из расчетов, можем записать уравнение регрессии таким образом:
(*)
Видим, что это уравнение, совпадает с уравнением, полученным нами при расчете по МНК вручную с точностью до ошибки округления.
Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициента регрессии (коэффициента а1). В нашем случае знак коэффициента регрессии положительный, следовательно, связь также является положительной.
Далее проверим значимость коэффициентов регрессии: а0 и а1.Сравнивая попарно значения столбцов Коэффициенты и Стандартная ошибка в таблице 4, видим, что абсолютные значения коэффициентов больше чем их стандартные ошибки. К тому же эти коэффициенты являются значимыми, о чем можно судить по значениям показателя Р-значение в таблице 4, которые меньше заданного уровня значимости α=0,05.Таблица 5
|
ВЫВОД ОСТАТКА |
|||
|
Наблюдение |
Предсказанное Y |
Остатки |
Стандартные остатки |
|
1 |
15,22241803 |
0,777581975 |
0,737641894 |
|
2 |
12,29674618 |
0,703253823 |
0,667131568 |
|
3 |
18,94600038 |
0,053999622 |
0,051225961 |
|
4 |
17,0842092 |
0,915790799 |
0,868751695 |
|
5 |
13,89256718 |
-1,892567185 |
-1,795356486 |
|
6 |
9,371074328 |
1,628925672 |
1,545256778 |
|
7 |
8,573163824 |
-0,573163824 |
-0,543723571 |
|
8 |
13,89256718 |
0,107432815 |
0,101914586 |
|
9 |
9,903014664 |
-0,903014664 |
-0,8566318 |
|
10 |
16,81823903 |
-0,818239033 |
-0,776210624 |
В таблице 5. представлены результаты вывода остатков. При помощи этой части отчета мы можем видеть отклонения каждой точки от построенной линии регрессии. Наибольшее абсолютное значение остатка в нашем случае - 1,89256, наименьшее - 0,05399. Для лучшей интерпретации этих данных воспользуемся графиком исходных данных и построенной линией регрессии, представленными на рис. 7. Как видим, линия регрессии хорошо "подогнана" под значения исходных данных.
Рис.7.
Приблизительным, но самым простым и наглядным способом проверки удовлетворительности регрессионной модели является графическое представление отклонений:
Рис. 8. График отклонений
Отложим отклонения по оси Y, для каждого значения . Если регрессионная модель близка к реальной зависимости, то отклонения будут носить случайный характер и их
Рис.8.
сумма будет близка к нулю. В рассмотренном примере .
Обычно мерой ошибки регрессионной модели служит среднее квадратическое отклонение
Для нормально распределенных процессов приблизительно 67% точек находится в пределах одного отклонения от линии регрессии и 95% - в пределах (на рисунке 8 трубки А и В соответственно).
Решим задачу прогнозирования. Поскольку коэффициент детерминации R2 имеет достаточно высокое значение и расстояние 2 мили, для которого надо сделать прогноз, находится в пределах диапазона исходных данных (таблица 1), то мы можем использовать полученное уравнение линейной регрессии для прогнозирования
минут.
При прогнозах на расстояния, не входящие в диапазон исходных данных, нельзя гарантировать справедливость полученной модели. Это объясняется тем, что связь между временем и расстоянием может изменяться по мере увеличения расстояния. На время дальних перевозок могут влиять новые факторы такие, как использование скоростных шоссе, остановки на отдых, обед и т.п.
Таким образом, в результате использования регрессионного анализа в пакете Microsoft Excel мы:
Варианты задач для самостоятельного решения
Задача №1
Постройте регрессионную модель (линейную) для исходных данных приведенных в таблице. Для облегчения расчетов исходные данные содержат только четыре пары значений .
Исходные данные задачи №1
|
№ варианта |
Координаты |
Точки |
x* |
|||
|
1 |
X |
1 |
2 |
3 |
4 |
1.6 |
|
Y |
30 |
7 |
8 |
1 |
? |
|
|
2 |
X |
1 |
2 |
3 |
4 |
2.3 |
|
Y |
25 |
7 |
7 |
2 |
||
|
3 |
X |
9 |
5 |
2 |
3 |
2.9 |
|
Y |
25 |
7 |
7 |
2 |
? |
|
|
4 |
X |
1 |
2 |
3 |
4 |
2.6 |
|
Y |
15 |
10 |
7 |
0.5 |
? |
|
|
5 |
X |
10 |
3 |
6 |
4 |
8 |
|
Y |
25 |
7 |
7 |
2 |
? |
|
|
6 |
X |
9 |
5 |
2 |
3 |
2.5 |
|
Y |
15 |
8.5 |
7.5 |
5 |
? |
|
|
7 |
X |
2 |
3 |
7 |
8 |
7.5 |
|
Y |
11 |
8.5 |
6.5 |
5 |
? |
|
|
8 |
X |
10 |
3 |
6 |
4 |
9 |
|
Y |
15 |
7 |
8 |
6 |
? |
|
|
9 |
X |
2 |
3 |
4 |
5 |
4.5 |
|
Y |
13 |
9 |
8 |
7 |
? |
|
|
10 |
X |
1 |
2 |
3 |
4 |
1.5 |
|
Y |
7.5 |
7 |
5 |
3.5 |
? |
|
|
11 |
X |
1 |
2 |
3 |
4 |
3.6 |
|
Y |
13 |
9 |
8 |
7 |
? |
|
|
12 |
X |
3 |
4 |
6 |
10 |
8 |
|
Y |
7.5 |
7 |
6.5 |
3.5 |
? |
|
|
13 |
X |
3 |
4 |
5 |
6 |
7.8 |
|
Y |
9 |
7 |
5 |
3 |
? |
|
|
14 |
X |
7 |
5.6 |
13 |
14.7 |
15 |
|
Y |
7.5 |
7 |
5 |
3.5 |
? |
|
|
15 |
X |
9 |
5 |
2 |
3 |
5.7 |
|
Y |
13 |
9 |
8 |
7 |
? |
|
|
16 |
X |
3 |
4 |
6 |
8 |
5 |
|
Y |
7.5 |
7 |
6.5 |
5 |
? |
|
|
17 |
X |
2 |
3 |
7 |
8 |
7.5 |
|
Y |
9 |
9 |
8 |
7 |
? |
|
|
18 |
X |
9 |
10 |
11 |
12 |
10.5 |
|
Y |
13 |
9 |
8 |
7 |
? |
|
|
19 |
X |
1 |
2 |
3 |
4 |
3.5 |
|
Y |
5 |
4.5 |
3 |
3 |
? |
|
|
20 |
X |
11 |
12 |
13 |
16 |
13.6 |
|
|
Y |
7.6 |
8 |
6.5 |
4.2 |
? |
|
21 |
X |
5 |
6 |
7 |
8 |
6.5 |
|
Y |
5 |
4.5 |
3 |
3 |
? |
|
|
22 |
X |
9 |
10 |
12 |
14 |
12.5 |
|
Y |
8 |
7 |
6.5 |
4.2 |
? |
|
|
23 |
X |
7 |
8 |
9 |
10 |
9.6 |
|
Y |
8 |
7 |
6 |
4.2 |
? |
|
|
24 |
X |
1.5 |
2.5 |
3.5 |
4.5 |
3.9 |
|
Y |
5 |
4.5 |
3 |
3 |
? |
|
|
25 |
X |
1 |
2 |
5 |
6 |
3.9 |
|
Y |
5 |
4 |
3 |
3 |
? |
|
|
26 |
X |
1.5 |
2.4 |
3.8 |
6.9 |
4.1 |
|
Y |
5.5 |
5.5 |
4.8 |
1.1 |
? |
|
|
27 |
X |
1 |
2 |
3 |
4 |
3.6 |
|
Y |
12 |
3 |
9 |
5 |
? |
|
|
28 |
X |
1 |
2 |
3 |
7 |
2.8 |
|
Y |
5 |
5.5 |
4.8 |
1.1 |
? |
|
|
29 |
X |
11 |
12 |
13 |
16 |
14.1 |
|
Y |
0.25 |
0.19 |
5.2 |
8 |
? |
|
|
30 |
X |
1 |
2 |
3 |
4 |
3.4 |
|
Y |
13 |
4 |
10 |
6 |
? |
Исследуйте модель с помощью режима Регрессия в MS Excel и сделайте прогноз для x*.
Задача № 2
Для исходных данных, представленных в таблице, были построены следующие регрессионные модели:
Исходные данные задачи №2
|
X |
3 |
8 |
5 |
10 |
7 |
6 |
4 |
9 |
1 |
2 |
|
Y |
6 |
5 |
9 |
1 |
8 |
9 |
8 |
4 |
2 |
4 |
С помощью графика отклонений выберите удовлетворительную модель и проверьте свой выбор с помощью соответствующего расчета.
Задача №3
В таблице представлены данные о ценах на комплектующие для ПЭВМ. Комплектующие производятся различными компаниями-производителями и разбиты на группы по своим функциональным возможностям.
Исходные данные задачи №3
|
Группа |
1 |
1 |
2 |
2 |
2 |
3 |
3 |
3 |
4 |
4 |
|
Цена, $ |
50 |
60 |
70 |
80 |
95 |
100 |
115 |
120 |
105 |
120 |
|
Группа |
4 |
5 |
5 |
5 |
6 |
6 |
6 |
7 |
7 |
7 |
|
Цена, $ |
130 |
110 |
150 |
190 |
120 |
130 |
220 |
145 |
265 |
270 |
Постройте график исходных данных и с его помощью проанализируйте применимость метода наименьших квадратов. Подтвердите свои выводы с помощью расчета (для линейной модели). Прокомментируйте экономические причины полученного результата.
Литература:
Лабораторная работа №2.
Тема: Подбор параметров, поиск решения и сценариев в электронных таблицах MS Excel.
Цель работы: научить студента решать обратные задачи с одним неизвестным, нелинейные уравнения с использованием Подбора параметра;
изучение приемов построения и использования сценариев.
Оборудование, технические средства, инструмент: персональный компьютер, Microsoft Excel
Порядок выполнения
Подбор параметров
Запустите табличный процессор Ехсеl.
Создайте новый документ и сохраните его под именем Подбор.xls.
Программа Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
Подбор параметра выполняется с помощью команды меню СЕРВИС/Подбор параметра.
Используя инструмент Подбор параметра, решите следующую задачу:
Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящие для его владельца.
Присвойте листу 1 имя Подбор. Создайте таблицу, приведенную ниже.
|
А |
В |
|
|
1 |
Размер вклада |
5 000 р. |
|
2 |
Срок вклада, лет |
5 |
|
3 |
Процентная ставка |
5% |
|
4 |
Коэффициент увеличения вклада |
=(1+В3)^В2 |
|
5 |
Сумма возврата вклада |
=В1*В4 |
Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+В3)^2, где ВЗ - процентная ставка, В2 - срок возврата вклада, а символ ^ - оператор «возведение в степень».
Сумма возврата вклада вычисляется в ячейке В5 по формуле:=В1*В4.
Введите формулы в ячейки В4 и В5.
Скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.
Сохраните работу.
Введите команду СЕРВИС/Подбор параметра и в первой копии таблицы на листе Подбор рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8 000 руб.
Во второй копии таблицы на этом же листе рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8 000 руб.
Сохраните работу.
Использование надстройки Поиск решения и сценариев
Программа Поиск решения позволяет получить результат на основе изменения значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия - ввести ограничения.
При поиске решения, так же как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.
Введите команду СЕРВИС/Надстройки и в диалоговом окне Надстройки подключите надстройку Поиск решения - установите соответствующий переключатель.
Сделайте активным лист 2 и присвойте ему имя Поиск.
Сделайте на этом же листе еще одну копию таблицы.
В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 руб. Для этого выполните следующие действия:
1. Введите команду СЕРВИС/Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:
• адрес целевой ячейки - $В$5 - сумма возврата вклада;
• подбираемое для целевой ячейки значение - 8 000р.;
• в поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки.
Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значением целевой ячейки В5 = В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4 = (1+ВЗ)^В2.
2. Введите ограничения для ячейки со сроком вклада - цел -целое число лет.
3. Щелкните по кнопке Выполнить.
В диалоговом окне Результаты поиска решения установите:
• Сохранить найденное решение;
• Тип отчета - Результаты.
Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий лист Поиск.
Проанализируйте полученные результаты.
Сохраните работу.
Во второй копии таблицы на листе Поиск выполните еще раз операцию Поиск решения, установив следующие параметры:
• адрес и значение целевой ячейки - сумма возврата вклада 8000р.;
• в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;
• добавьте ограничения для ячейки с величиной процентной ставки: < = 7 %.
Сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения.
Введите команду СЕРВИС/Сценарии и с помощью диалогового окна Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой - 10%.
Выведите сценарий Поиск1 и создайте отчет по сценариям в виде структуры.
Проанализируйте полученные результаты.
Сохраните документ.
Используя программу Поиск решения, решите задачу оптимизации выпуска изделий на предприятии "Протон". Предприятие выпускает приборы трех типов - прибор 1, 2 и 3. Количество комплектующих элементов, используемых для производ ства одного прибора каждого типа, и ежедневный запас комплектующих элементов приведены в таблице.
|
|
A |
B |
C |
D |
E |
F |
||
|
1 |
Оптимизация выпуска изделий на предприятии «Протон» |
|||||||
|
2 |
Прибор 1 |
Прибор 2 |
Прибор 3 |
Запас элементов |
Расход элементов |
|||
|
3 |
Элемент 1 |
3 |
2 |
1 |
800 |
|||
|
4 |
Элемент 2 |
2 |
4 |
3 |
1 200 |
|||
|
5 |
Элемент 3 |
4 |
3 |
4 |
1 500 |
|||
|
6 |
Выпуск приборов |
120 |
120 |
120 |
Суммарный выпуск приборов |
|||
Расход элементов каждого типа вычисляется по формуле =В3*$В$6+С3*$С$6+D3*$D$6.
Определите, каким образом следует изменить соотношение типов выпускаемых приборов, чтобы обеспечить максимальный Суммарный выпуск приборов при оптимальном расходе имеющегося запаса комплектующих элементов?
Для решения этой задачи установите для целевой ячейки F6 максимальное значение.
Введите следующие ограничения:
• Количество выпускаемых приборов должно быть целым числом.
• Расход элементов не должен превышать их запас.
• Выпуск приборов каждого типа не должен быть ниже 120 шт.
Сценарии используются в том случае, когда необходимо изучить зависимость некоторых значений таблицы более, чем от одного параметра. Под сценарием понимается определенный вариант развития событий, когда пользователь задает предполагаемые значения некоторых параметров (изменяемые ячейки), а программа подставляет их таблицу и вычисляет значения зависимых ячеек. При этом пользователь может:
наблюдать результат экспериментов непосредственно в таблице;
сформировать отчет в виде структурированной итоговой таблицы;
сформировать отчет в виде сводной таблицы.
Контрольные вопросы
1. Для каких задач используется подбор параметра?
2. Порядок выполнения подбора параметра. Заполнение элементов диалогового окна Подбор параметра.
3. Задание погрешности вычислений.
4. Решение нелинейных уравнений.
Литература:
Лабораторные работы №3-4 .
Решение оптимизационных задач в Excel с помощью инструмента Принятие решений.
Цель работы: изучить встроенные в Excel возможности анализа данных на примере проведения регрессионного анализа. Ознакомиться со стандартными надстройками Excel на примере надстройки «Принятие решений», использовать её для решения задачи линейного программирования.
Методика выполнения работы
Статистический анализ данных, моделирование и прогноз
Электронный процессор Excel позволяет строить математические модели по имеющимся табличным данным. Математическая модель дает возможность прогнозировать состояние моделируемого объекта и выбирать на этой основе оптимальное управление объектом. Для этих целей Excel содержит пакет анализа данных, в который входят: регрессионный анализ, корреляция, дисперсионный анализ и другие средства.
Регрессионный анализ
Регрессионный анализ позволяет получить функциональную зависимость между некоторой случайной величиной Y и некоторыми влияющими на Y величинами X. Такая зависимость получила название уравнения регрессии. Различают простую (парную) и множественную регрессию линейного и нелинейного типа.
Пример простой линейной регрессии:
y=m1x+b.
Пример множественной линейной регрессии:
y=m1x1+m2x2+... + mkxk+b. (1)
Для оценки степени связи между величинами используется коэффициент множественной корреляции R Пирсона (корреляционное отношение), который может принимать значения от 0 до 1. R=0 если между величинами нет никакой связи и R=1, если между величинами имеется функциональная (детерминированная) связь. В большинстве случаев R принимает промежуточные значения от 0 до 1. Величина R2 называется коэффициентом детерминации.
Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.
Для оценки значимости R применяется F-критерий Фишера, вычисляемый по формуле:
где n - размер выборки (количество экспериментов); k - число коэффициентов модели. Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной. Таблицы критических значений F приводятся в справочниках по математической статистике.
Таким образом, значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако, если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>>k).
Для построения линейной регрессионной модели необходимо:
1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину y должен быть либо первым, либо последним в списке);
2) обратиться к меню Сервис/Анализ данных/Регрессия
Если пункт "Анализ данных" в меню "Сервис" отсутствует, то следует обратиться к пункту "Надстройки" того же меню и установить флажок "Пакет анализа".
3) в диалоговом окне "Регрессия" задать:
4) нажать "Ok" и проанализировать результаты.
Пример использования множественной линейной регрессии
Предположим, что застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе.
Застройщик может использовать множественный регрессионный анализ для оценки цены офисного здания в заданном районе на основе следующих переменных.
y - оценочная цена здания под офис;
x1 - общая площадь в квадратных метрах;
x2 - количество офисов;
x3 - количество входов (0,5 входа означает вход только для доставки корреспонденции);
x4 - время эксплуатации здания в годах.
В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1, x2, x3 и x4) и зависимой переменной (y), то есть ценой здания под офис в данном районе. Исходные данные показаны на рисунке.
Настройки для решения поставленной задачи показаны на рисунке окна "Регрессия". Результаты расчетов размещены на отдельном листе в трех таблицах
В итоге мы получили следующую математическую модель:
y = 52318 + 27,64*x1 + 12530*x2 + 2553*x3 - 234,24*x4.
Теперь застройщик может определить оценочную стоимость здания под офис в том же районе. Если это здание имеет площадь 2500 квадратных метров, три офиса, два входа и время эксплуатации - 25 лет, можно оценить его стоимость, используя следующую формулу:
y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 у.е.
В регрессионном анализе наиболее важными результатами являются:
На t-статистике следует остановиться особо. Очень часто при построении регрессионной модели неизвестно, влияет тот или иной фактор x на y. Включение в модель факторов, которые не влияют на выходную величину, ухудшает качество модели. Вычисление t-статистики помогает обнаружить такие факторы. Приближенную оценку можно сделать так: если при n>>k величина t-статистики по абсолютному значению существенно больше трех, соответствующий коэффициент следует считать значимым, а фактор включить в модель, иначе исключить из модели. Таким образом, можно предложить технологию построения регрессионной модели, состоящую из двух этапов:
1) обработать пакетом "Регрессия" все имеющиеся данные, проанализировать значения t-статистики;
2) удалить из таблицы исходных данных столбцы с теми факторами, для которых коэффициенты незначимы и обработать пакетом "Регрессия" новую таблицу.
Для примера рассмотрим переменную x4. В справочнике по математической статистике t-критическое с (n-k-1)=6 степенями свободы и доверительной вероятностью 0,95 равно 1,94. Поскольку абсолютная величина t, равная 17,7 больше, чем 1,94, срок эксплуатации - это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных:
|
Общая площадь |
5,1 |
|
Количество офисов |
31,3 |
|
Количество входов |
4,8 |
|
Срок эксплуатации |
17,7 |
Все эти значения имеют абсолютную величину большую, чем 1,94; следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.
Поиск решения
Excel имеет несколько программ-надстроек, выполняющих решение различных задач. Одной из надстроек является "Поиск решения", позволяющая решать оптимизационные задачи в Excel. Чаще всего это задачи линейного программирования (ЛП).
Общая формулировка задачи ЛП: найти неотрицательное решение X системы линейных уравнений AX=B, при котором целевая функция f=CX принимает максимальное (минимальное) значение, где A — матрица коэффициентов; B — объемы ресурсов.
Экономический смысл системы AX=B заключается в задании ограничений на расходуемые ресурсы.
Экономический смысл целевой функции f=CX заключается в максимальной прибыли или минимальной себестоимости, получаемой от оптимального решения X. Например, если X — вектор объемов выпуска продукции, а С - вектор прибыли, получаемой от единицы каждого вида продукции, то f — суммарная прибыль от выпуска всей продукции.
Рассмотрим работу надстройки "Поиск решения" на примере задачи о рационе кормления животных. Требуется составить такой рацион кормления животных тремя видами корма, при котором они получат необходимое количество питательных веществ A и B и себестоимость кормов будет минимальна. Цены кормов, требуемое количество питательных веществ и их содержание в каждом корме показаны в таблице.
|
Питательные вещества |
Корм 1 |
Корм 2 |
Корм 3 |
Требуемое количество (ед. пит. вещества) |
|
А (ед./кг) |
10 |
6 |
12 |
50 |
|
Б (ед./кг) |
7 |
10 |
11 |
45 |
|
Цена корма (руб/кг) |
2,20 |
1,95 |
2,87 |
|
Если обозначить X=(x1, x2, x3) — искомое количество кормов, то задача ЛП формулируется так:
Найти решение X системы
при котором целевая функция
принимает минимальное значение.
Математическую формулировку задачи необходимо оформить в виде таблицы, отражающей основные зависимости.
Ячейки таблицы имеют следующий смысл:
Вызов программы поиска решения выполняется через меню "Сервис\Поиск решения...". В открывшемся окне "Поиск решения" необходимо установить следующие параметры:
Для запуска программы необходимо в окне "Поиск решения" нажать кнопку "Выполнить". Результаты вычислений будут записаны в изменяемые ячейки таблицы. В итоге таблица должна иметь следующий вид.
Таким образом, животных следует кормить первым кормом в количестве 0,38 кг, третьим — 3,85 кг и не использовать второй корм вообще. При таком рационе затраты на кормление одного животного составят 11,88 руб.
Пример постановки задачи линейного программирования
Акционерное общество производит и реализует оптовыми партиями соответственно по цене 45 руб. и 30 руб. книги и настольные календари. Постоянные затраты общества (управленческие расходы, содержание офиса и т.п.) составляют 202200 руб. в квартал, переменные затраты (стоимость бумаги, услуги типографии и т.п.) – соответственно 15.75 руб. и 12.3 руб. в расчете на одну книгу и один календарь. Налог на добавленную стоимость составляет 16.67 % цены изделия.
Опыт реализации изделий в предыдущие плановые периоды показал, что в течение квартала можно реализовать не более 100000 книг и 40000 календарей.
Требуется на планируемый квартал определить объем производства и реализации изделий (структуру производства и реализации), при котором общество получит максимальную прибыль, а суммарные затраты на производство и реализацию изделий не будут превосходить 2000000 руб.
Обозначим через х1(х2) неизвестный объем производства и реализации книг (календарей). Суммарные затраты Z на производство и реализацию изделий можно выразить в виде функции неизвестных объемов реализации изделий:
Z(x1,x2)=202200+15.75x1+12.3x2. (1.1)
Ожидаемая сумма выручки общества V, рассматриваемая как функция неизвестных объемов производства и реализации, составит:
V(x1,x2)=45x1+30x2. (1.2)
Сумма налога на добавленную стоимость N также выражается в виде функции неизвестных объемов реализации:
N(x1,x2)=0.1667*(45x1+30x2)=7.5x1+5x2. (1.3)
Прибыль акционерного общества P можно записать в виде функции неизвестных объемов x1 и x2 следующим образом:
P(x1,x2)=V(x1,x2)-Z(x1,x2)-N(x1,x2) = 45x1+30x2-202200 –157.5x1-123x2-7.5x1-5x2=
=21.75x1+12.7x2-202200. (1.4)
Таким образом, математическая модель ситуации записывается следующим образом:
21.75x1+12.7x2-202200max, (1.5)
202200+15.75x1+12.3x22000000, (1.6)
0 x1 100000, 0 x2 40000. (1.7)
Модель (1.5)–(1.7) можно записать в следующем виде:
21.75x1+12.7x2max, (1.8)
15.75x1+12.3x21797800, (1.9)
x1 100000, (1.10)
x2 40000, (1.11)
x1 0, x2 0. (1.12)
Рис. 1. Таблица для ввода условий задачи линейного программирования
Рис. 2. Диалоговое окно Поиск решения
Рис. 3. Диалоговое окно Результаты поиска решения
|
|
Товары |
|
|
|
|
|
имя |
Книги(х1) |
Календари(х2) |
|
|
|
|
значение |
|
|
|
|
|
|
нижняя граница |
|
|
|
|
|
|
верхняя граница |
|
|
ЦФ |
напр |
|
|
коэф. в ЦФ |
21,75 |
12,70 |
|
макс. |
|
|
|
Ограничения |
|
|
||
|
вид |
|
|
левая часть |
знак |
правая часть |
|
b1 |
15,75 |
12,30 |
|
<= |
1797800,00 |
|
b2 |
1,00 |
0,00 |
|
<= |
100000,00 |
|
b3 |
0,00 |
1,00 |
|
<= |
40000,00 |
Рис. 4. Выходная таблица решения задачи линейного программирования
Задание к работе
Произвести регрессионный анализ данных, проанализировать влияние начальных данных на оценочные коэффициенты регрессионной модели, и при необходимости пересчитать её. Решить задачу линейного программирования средствами Excel.
Порядок выполнения работы
Требование к защите работы
Содержание отчета
Контрольные вопросы
Литература:
Варианты задание к лабораторной работе
Регрессионный анализ
|
№ вар. |
Регрессия |
Прогноз |
№ вар. |
Регрессия |
Прогноз |
||
|
y |
x |
y |
x |
||||
|
1 |
Y1 |
X1-X4 |
январь |
16 |
Y6 |
X5-X8 |
апрель |
|
2 |
Y2 |
X1-X4 |
февраль |
17 |
Y7 |
X5-X8 |
май |
|
3 |
Y3 |
X1-X4 |
март |
18 |
Y8 |
X5-X8 |
июнь |
|
4 |
Y4 |
X1-X4 |
апрель |
19 |
Y9 |
X5-X8 |
июль |
|
5 |
Y5 |
X1-X4 |
май |
20 |
Y10 |
X5-X8 |
август |
|
6 |
Y6 |
X5-X8 |
июнь |
21 |
Y1 |
X1-X4 |
сентябрь |
|
7 |
Y7 |
X5-X8 |
июль |
22 |
Y2 |
X1-X4 |
октябрь |
|
8 |
Y8 |
X5-X8 |
август |
23 |
Y3 |
X1-X4 |
ноябрь |
|
9 |
Y9 |
X5-X8 |
сентябрь |
24 |
Y4 |
X1-X4 |
декабрь |
|
10 |
Y10 |
X5-X8 |
октябрь |
25 |
Y5 |
X1-X4 |
январь |
|
11 |
Y1 |
X1-X4 |
ноябрь |
26 |
Y6 |
X5-X8 |
февраль |
|
12 |
Y2 |
X1-X4 |
декабрь |
27 |
Y7 |
X5-X8 |
март |
|
13 |
Y3 |
X1-X4 |
январь |
28 |
Y8 |
X5-X8 |
апрель |
|
14 |
Y4 |
X1-X4 |
февраль |
29 |
Y9 |
X5-X8 |
май |
|
15 |
Y5 |
X1-X4 |
март |
30 |
Y10 |
X5-X8 |
июнь |
Данные для проведения регрессионного анализа
|
№ |
x1 |
x2 |
x3 |
x4 |
x5 |
x6 |
x7 |
x8 |
y1 |
y2 |
y3 |
y4 |
y5 |
y6 |
y7 |
y8 |
y9 |
y10 |
|
1 |
4,4 |
8,5 |
2,4 |
3,7 |
2,2 |
7,4 |
3,7 |
9,9 |
28,0 |
32,4 |
23,5 |
11,5 |
15,2 |
22,2 |
30,0 |
28,6 |
27,2 |
3,5 |
|
2 |
8,3 |
6,4 |
2,5 |
5,8 |
5,7 |
8,7 |
2,6 |
6,1 |
41,7 |
21,5 |
14,7 |
25,1 |
18,4 |
32,4 |
18,8 |
40,4 |
29,6 |
19,2 |
|
3 |
9,5 |
4,4 |
7,3 |
1,9 |
4,1 |
5,3 |
4,6 |
1,2 |
32,1 |
31,8 |
21,4 |
33,5 |
20,1 |
21,4 |
18,3 |
23,0 |
20,8 |
16,3 |
|
4 |
1,6 |
9,6 |
3,2 |
9,7 |
4,1 |
9,2 |
8,7 |
4,4 |
28,7 |
31,0 |
29,5 |
4,3 |
26,9 |
28,3 |
30,3 |
33,6 |
26,9 |
16,5 |
|
5 |
7,4 |
3,7 |
0,7 |
9,9 |
2,4 |
2,5 |
5,8 |
5,1 |
49,5 |
6,3 |
7,3 |
27,3 |
23,3 |
11,6 |
30,2 |
10,3 |
10,8 |
7,5 |
|
6 |
1 |
0,9 |
0,6 |
4,1 |
5,4 |
3,9 |
4,0 |
7,0 |
19,5 |
4,4 |
7,5 |
11,1 |
13,1 |
19,6 |
27,2 |
24,4 |
13,9 |
15,9 |
|
7 |
2,6 |
4,2 |
0,8 |
2,7 |
8,7 |
4,1 |
3,1 |
8,1 |
18,5 |
14,5 |
11,5 |
12,0 |
9,4 |
27,6 |
25,5 |
33,0 |
18,4 |
24,0 |
|
8 |
6,7 |
7,8 |
5,0 |
4,9 |
7,7 |
0,4 |
3,9 |
3,4 |
32,9 |
32,5 |
24,1 |
21,3 |
23,1 |
18,5 |
24,8 |
18,9 |
6,4 |
27,5 |
|
9 |
8,2 |
6,8 |
7,0 |
6,9 |
6,2 |
6,2 |
9,6 |
3,4 |
38,4 |
34,4 |
25,6 |
24,6 |
30,6 |
27,2 |
37,3 |
26,5 |
16,3 |
22,7 |
|
10 |
5,5 |
7,6 |
8,2 |
8,9 |
1,6 |
2,8 |
5,0 |
7,9 |
33,3 |
36,4 |
30,3 |
17,2 |
39,1 |
11,5 |
32,9 |
11,8 |
12,5 |
5,5 |
|
11 |
7,3 |
4,9 |
4,6 |
0,6 |
7,5 |
5,9 |
4,2 |
4,5 |
24,9 |
29,6 |
18,5 |
23,4 |
12,2 |
30,1 |
20,9 |
33,6 |
21,3 |
26,9 |
|
12 |
2,5 |
3,4 |
3,4 |
8,1 |
5,9 |
8,6 |
8,5 |
4,0 |
29,6 |
13,9 |
16,5 |
10,9 |
26,8 |
32,8 |
32,4 |
35,3 |
24,4 |
21,5 |
|
13 |
1,9 |
0,2 |
9,9 |
0,5 |
3,2 |
8,5 |
2,6 |
4,8 |
6,6 |
26,5 |
24,3 |
11,9 |
22,2 |
27,6 |
16,1 |
35,0 |
29,5 |
12,5 |
|
14 |
5,8 |
4 |
7,1 |
9,5 |
0,0 |
3,6 |
8,0 |
2,0 |
37,8 |
23,0 |
22,9 |
23,0 |
37,3 |
11,1 |
32,2 |
8,3 |
10,9 |
3,1 |
|
15 |
5,1 |
1,7 |
7,6 |
4,8 |
7,5 |
2,9 |
3,5 |
0,7 |
27,1 |
21,2 |
20,6 |
20,3 |
27,5 |
24,8 |
15,7 |
25,6 |
13,8 |
27,4 |
|
16 |
3,6 |
1,4 |
0,8 |
8,9 |
0,1 |
4,8 |
2,3 |
0,3 |
37,7 |
3,6 |
7,1 |
18,6 |
22,6 |
11,8 |
8,0 |
15,9 |
21,7 |
5,1 |
|
17 |
4,6 |
3,5 |
1,5 |
4,4 |
9,8 |
6,9 |
8,6 |
4,8 |
28,6 |
13,1 |
10,1 |
17,1 |
15,5 |
34,6 |
35,5 |
37,1 |
19,6 |
29,9 |
|
18 |
5,5 |
5,5 |
9,5 |
5,9 |
3,2 |
2,0 |
7,6 |
4,0 |
26,7 |
33,1 |
32,3 |
17,9 |
35,4 |
13,0 |
35,2 |
10,8 |
8,1 |
12,3 |
|
19 |
6,4 |
9,6 |
9,4 |
2,5 |
0,3 |
5,8 |
7,7 |
1,7 |
22,1 |
51,7 |
38,9 |
16,1 |
26,5 |
14,4 |
26,7 |
16,3 |
17,7 |
5,0 |
|
20 |
3,8 |
2,4 |
9,3 |
3,7 |
7,1 |
5,8 |
7,2 |
5,1 |
15,7 |
26,5 |
27,1 |
15,5 |
29,5 |
28,0 |
32,0 |
27,4 |
17,7 |
22,0 |
|
21 |
3,4 |
6,3 |
6,9 |
8,6 |
1,7 |
6,5 |
9,2 |
4,0 |
28,8 |
27,4 |
27,2 |
10,9 |
34,9 |
19,9 |
35,9 |
19,6 |
18,9 |
6,4 |
|
22 |
5,3 |
6,6 |
4,3 |
0,3 |
4,0 |
2,8 |
7,0 |
7,5 |
20,9 |
31,4 |
20,8 |
19,0 |
13,2 |
16,7 |
39,2 |
15,3 |
8,1 |
10,0 |
|
23 |
3,7 |
7 |
6,4 |
0,5 |
9,9 |
4,9 |
4,8 |
7,9 |
15,4 |
36,9 |
27,9 |
13,6 |
18,0 |
34,6 |
29,6 |
36,3 |
17,2 |
27,7 |
|
24 |
9,3 |
1,3 |
8,6 |
0,4 |
3,3 |
5,4 |
5,6 |
2,8 |
28,7 |
26,0 |
17,5 |
36,2 |
20,4 |
20,9 |
23,8 |
23,3 |
19,6 |
12,6 |
|
25 |
5,2 |
4,6 |
2,0 |
0,4 |
6,4 |
8,3 |
9,4 |
9,8 |
21,7 |
20,4 |
13,7 |
18,0 |
9,1 |
32,1 |
43,6 |
31,3 |
22,7 |
16,3 |
|
26 |
4,3 |
5,1 |
7,0 |
9,7 |
7,6 |
6,2 |
7,9 |
2,6 |
33,5 |
25,8 |
27,3 |
16,7 |
35,1 |
30,0 |
28,8 |
31,8 |
20,1 |
27,7 |
|
27 |
5 |
2 |
2,5 |
2,2 |
2,5 |
0,6 |
5,2 |
9,5 |
24,8 |
12,4 |
9,9 |
22,0 |
11,8 |
8,2 |
39,0 |
6,0 |
3,1 |
3,5 |
|
28 |
8,6 |
0,7 |
1,3 |
1,2 |
1,1 |
0,5 |
2,0 |
2,5 |
34,9 |
7,1 |
2,6 |
31,7 |
8,2 |
7,9 |
14,9 |
6,6 |
8,9 |
8,3 |
|
29 |
1,3 |
4,4 |
7,5 |
2,7 |
7,0 |
7,1 |
4,6 |
9,2 |
9,2 |
32,3 |
26,6 |
8,7 |
25,1 |
30,7 |
32,7 |
36,2 |
26,1 |
18,2 |
|
30 |
9,3 |
9,7 |
1,3 |
6,9 |
3,6 |
6,0 |
5,7 |
1,9 |
47,3 |
31,0 |
19,5 |
26,0 |
21,4 |
23,3 |
19,3 |
25,8 |
20,3 |
14,7 |
|
31 |
2,3 |
8,6 |
7,3 |
8,7 |
2,0 |
3,4 |
5,6 |
8,7 |
25,7 |
36,1 |
34,3 |
8,1 |
34,5 |
13,6 |
37,3 |
15,6 |
12,3 |
3,6 |
|
32 |
8,5 |
5 |
1,9 |
1,2 |
3,2 |
3,4 |
1,5 |
9,5 |
35,9 |
23,0 |
10,8 |
27,4 |
8,2 |
18,0 |
24,3 |
18,4 |
16,0 |
6,6 |
|
33 |
5 |
1,9 |
7,2 |
1,7 |
1,1 |
4,4 |
4,1 |
7,0 |
20,4 |
23,1 |
18,1 |
19,8 |
21,9 |
15,1 |
28,6 |
16,3 |
16,0 |
1,9 |
|
34 |
2,2 |
3,2 |
5,5 |
2,0 |
0,7 |
6,1 |
1,6 |
6,0 |
13,5 |
25,6 |
21,7 |
12,0 |
18,7 |
17,3 |
16,6 |
24,7 |
23,8 |
1,9 |
|
35 |
5,4 |
4,1 |
0,9 |
9,9 |
8,7 |
1,4 |
2,7 |
4,0 |
41,3 |
8,7 |
11,2 |
20,0 |
23,6 |
22,9 |
22,4 |
25,6 |
11,1 |
30,6 |
|
36 |
7,2 |
3,7 |
0,4 |
3,0 |
6,0 |
5,8 |
0,9 |
8,9 |
35,6 |
14,5 |
8,8 |
27,5 |
9,2 |
24,6 |
19,2 |
35,1 |
23,0 |
16,3 |
|
37 |
2,9 |
8 |
6,3 |
9,7 |
0,8 |
7,6 |
4,6 |
2,2 |
29,6 |
32,6 |
30,0 |
8,4 |
34,6 |
19,0 |
18,3 |
25,9 |
25,0 |
7,2 |
|
38 |
1,3 |
5,6 |
2,7 |
8,8 |
6,8 |
3,1 |
4,2 |
1,5 |
26,4 |
17,5 |
23,0 |
8,2 |
25,7 |
23,2 |
18,6 |
25,1 |
15,0 |
24,4 |
|
39 |
3,6 |
5,3 |
4,2 |
2,1 |
4,7 |
7,7 |
8,7 |
2,3 |
17,0 |
27,3 |
22,6 |
14,1 |
14,8 |
29,2 |
29,5 |
29,6 |
21,7 |
18,9 |
|
40 |
1,9 |
0,8 |
6,7 |
0,4 |
3,4 |
9,9 |
4,6 |
5,6 |
6,3 |
20,0 |
19,0 |
12,4 |
15,5 |
30,0 |
22,3 |
38,4 |
33,0 |
12,3 |
|
41 |
2,7 |
0,5 |
6,9 |
3,5 |
8,7 |
7,3 |
3,9 |
6,7 |
16,7 |
17,6 |
18,0 |
15,4 |
24,9 |
36,4 |
24,6 |
41,8 |
24,2 |
27,8 |
|
42 |
6,3 |
1,1 |
5,7 |
6,7 |
2,3 |
3,6 |
9,7 |
1,7 |
34,0 |
13,9 |
13,8 |
24,3 |
28,1 |
13,3 |
33,4 |
12,6 |
8,0 |
12,1 |
|
43 |
3,5 |
6,9 |
6,8 |
0,7 |
1,4 |
3,7 |
6,6 |
3,0 |
11,6 |
36,8 |
28,7 |
11,0 |
19,2 |
14,4 |
27,8 |
11,0 |
11,5 |
9,8 |
|
44 |
9,1 |
3 |
4,7 |
2,1 |
0,6 |
3,0 |
5,9 |
4,6 |
35,6 |
20,6 |
14,0 |
30,3 |
16,4 |
8,6 |
29,4 |
10,5 |
12,9 |
3,0 |
|
45 |
7,2 |
0,9 |
0,4 |
6,4 |
6,8 |
9,7 |
4,2 |
7,4 |
40,8 |
2,2 |
0,0 |
28,7 |
14,6 |
37,8 |
24,8 |
42,1 |
31,1 |
18,2 |
|
46 |
3,1 |
0,2 |
8,2 |
0,6 |
9,6 |
9,7 |
9,2 |
5,4 |
10,0 |
22,4 |
21,4 |
18,2 |
19,2 |
40,0 |
33,7 |
42,2 |
27,1 |
30,9 |
|
47 |
0,5 |
9,3 |
8,4 |
4,4 |
8,4 |
3,2 |
9,8 |
6,8 |
9,2 |
45,5 |
42,1 |
2,0 |
30,1 |
25,5 |
44,4 |
23,6 |
7,9 |
25,1 |
|
48 |
8,7 |
0,7 |
1,7 |
5,2 |
9,3 |
5,8 |
6,7 |
7,2 |
44,7 |
6,4 |
3,1 |
31,4 |
18,7 |
32,7 |
36,3 |
32,3 |
18,1 |
26,5 |
|
49 |
7,6 |
2,6 |
1,5 |
4,1 |
8,4 |
7,3 |
0,8 |
0,8 |
36,6 |
11,0 |
8,1 |
27,7 |
13,7 |
34,7 |
3,8 |
44,4 |
29,3 |
31,3 |
|
50 |
8,4 |
6,6 |
4,5 |
2,8 |
1,7 |
6,2 |
5,8 |
9,7 |
35,5 |
32,7 |
20,8 |
25,5 |
17,2 |
19,0 |
36,3 |
20,1 |
21,7 |
3,5 |
Варианты задач линейного программирования
Задача 1.
В трех пунктах отправления A, B, C сосредоточен однородный товар. Этот товар необходимо перевезти в четыре пункта назначения P, Q, R, S. Запасы товара в пунктах отправления и потребности пунктов назначения показаны в таблице.
|
Запасы (тонн) |
Потребности (тонн) |
|||||
|
A |
B |
C |
P |
Q |
R |
S |
|
42 |
36 |
40 |
26 |
41 |
25 |
31 |
Стоимости перевозок 1 т. груза из i-го пункта отправления в j-й пункт назначения заданы матрицей.
|
A |
B |
C |
|
|
P |
200 |
400 |
300 |
|
Q |
700 |
500 |
800 |
|
R |
600 |
900 |
700 |
|
S |
550 |
430 |
650 |
Учесть, что в случае недостатка запасов, все потребности не могут быть удовлетворены.
Задача 2.
Радиозавод выпускает магнитофоны двух моделей: A и B. Завод должен выпускать в месяц не менее Ya и Yb магнитофонов. Каждая модель приносит доход в размере Sa и Sb рублей. Процесс производства магнитофонов состоит из трех стадий: изготовления деталей, сборки изделия и его упаковки. Нормативы времени на эти стадии заданы матрицей T. Производственные мощности завода позволяют расходовать в месяц не более 600 часов на выпуск деталей, 500 часов на сборку магнитофонов и 200 часов на упаковку. Составить план выпуска магнитофонов, обеспечивающий заводу максимальный доход. Определить, какая из стадий в наибольшей степени сдерживает рост производства.
|
Sa |
Sb |
Ya |
Yb |
T (час./1 шт.) |
|||
|
Модель |
Стадия 1 |
Стадия 2 |
Стадия 3 |
||||
|
40 |
60 |
400 |
600 |
1 2 |
0.3 0.35 |
0.4 0.5 |
0.1 0.15 |
Задача 3.
Акционерное общество может возделывать на поле площадью S га. четыре культуры. Наиболее трудоемким этапом является уборка урожая, которая должна осуществляться в два этапа: сначала убираются 1-я и 2-я культуры, а затем 3-я и 4-я. Трудоемкости уборки каждой культуры составляют соответственно Т1, Т2, Т3 и Т4 человеко-часов на 1 га. Для уборки общество может выделить R1 человеко-часов на 1-й этап и R2 на 2-й. Составить план посева культур, даюший максимальную прибыль, если 1 га каждой из культур (при ожидаемой урожайности) приносит прибыль соответственно в С1, С2, С3, С4 (тыс.руб.). Определить, какой из этапов уборки ограничивает рост прибыли.
|
S |
T1 |
T2 |
T3 |
T4 |
R1 |
R2 |
C1 |
C2 |
C3 |
C4 |
|
400 |
100 |
150 |
200 |
90 |
25000 |
30000 |
400 |
500 |
820 |
300 |
Задача 4.
Рацион стада крупного рогатого скота из 220 голов включает пищевые продукты А, В, С, D и Е. В сутки одно животное должно съедать не менее 2 кг продукта вида A, 1,5 кг продукта В, 0,9 кг продукта С, 3 кг продукта D и 1,8 кг продукта E. Однако в чистом виде указанные продукты не производятся. Они содержатся в концентратах К-1, К-2 и К-3. Их цена соответственно 0,5; 0,4; 0,9 руб. за килограмм. Содержание продуктов в килограмме концентрата (в %) указано в таблице.
|
Концентраты |
Продукты |
||||
|
A |
B |
C |
D |
E |
|
|
К-1 К-2 К-3 |
15 19 5 |
22 17 12 |
0 0 25 |
0 14 5 |
4 7 8 |
.Построить модель, на основе которой составить план покупки концентратов при котором затраты на покупку будут минимальны.
Задача 5.
Кондитерская фабрика для производства трех видов карамели A, B и C использует три вида сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья на производство 1 кг. карамели заданы в таблице.
|
Наименование сырья |
Нормы расхода (кг./кг.) |
||
|
A |
B |
C |
|
|
Сахарный песок |
0.6 |
0.5 |
0.6 |
|
Патока |
0.4 |
0.4 |
0.3 |
|
Фруктовое пюре |
0.1 |
0.2 |
0.2 |
Запасы сырья на складе соответственно равны V1, V2 и V3 кг. Прибыль от реализации 1 кг. продукции каждого вида определяется значениями PA, PB и PC. Найти план производства карамели, обеспечивающий максимальную прибыль. Выяснить, какое сырье ограничивает рост прибыли.
|
Запасы сырья (кг.) |
Прибыль от реализации ( руб./кг.) |
||||
|
V1 |
V2 |
V3 |
PA |
PB |
PC |
|
800 |
600 |
120 |
1.08 |
1.12 |
1.28 |
Задача 6.
Мебельная фабрика выпускает столы, стулья, кресла и кровати. При изготовлении этих товаров используется два вида досок. Запасы досок и трудовых ресурсов показаны в таблице.
|
Ресурсы |
||
|
Доски 1 типа (м) |
Доски 2 типа (м) |
Трудовые (чел.-час.) |
|
1500 |
1000 |
1500 |
Нормы расхода сырья, трудоемкость производства продукции и прибыль от реализации единицы продукции показаны в таблице.
|
Изделия |
Нормы расхода |
|||
|
Ресурсы |
Столы |
Стулья |
Кресла |
Кровати |
|
Доски 1 типа (м) |
5 |
1 |
9 |
12 |
|
Доски 2 типа (м) |
2 |
3 |
4 |
6 |
|
Трудоемкость (чел.-час.) |
3 |
2 |
5 |
10 |
|
Прибыль ( руб.) |
12 |
5 |
15 |
18 |
С учетом спроса на товары фабрика должна выпустить не более 10 кроватей, а соотношение столов и стульев должно быть 1:6.
Найти план производства мебели, дающий фабрике максимальную прибыль.
Задача 7.
Полуфабрикат поступает на фабрику в виде двух партий листов фанеры. Первая партия содержит 400 листов, вторая - 250 листов. Из этой фанеры необходимо производить комлекты деталей: 1 комплект содержит 4 детали 1-го типа, 3 детали 2-го и 3 детали 3-го типа. Листы фанеры можно раскраивать различными способами. Количество деталей, которое можно получить из 1 листа фанеры показано в таблице.
|
1 партия |
2 партия |
|||||
|
Деталь |
Способ |
Деталь |
Способ |
|||
|
1 |
2 |
3 |
1 |
2 |
||
|
1 |
0 |
6 |
9 |
1 |
6 |
3 |
|
2 |
4 |
3 |
4 |
2 |
5 |
4 |
|
3 |
10 |
16 |
0 |
3 |
8 |
0 |
Составить план раскроя фанеры так, чтобы получить максимальное количество комплектов деталей.
Задача 8.
Ткань трех артикулов производится на ткацких станках двух типов с различной производительностью. Сырьем для изготовления тканей являются пряжа и красители. Основные параметры производства, его себестоимость и цены реализации готового продукта показаны в таблице. Составить план производства тканей, обеспечивающий максимальную прибыль.
|
Вид ресурса |
Обьем ресурсов |
Производительность и норма расхода |
||
|
Артикул 1 |
Артикул 2 |
Артикул 3 |
||
|
Станки 1 типа |
29000 часов |
20 м/час. |
10 м/час |
25 м/час |
|
Станки 2 типа |
30000 часов |
8 м/час |
20 м/час |
10 м/час |
|
Пряжа (кг) |
30000кг |
0.12 кг/м |
0.18 кг/м |
0.21 кг/м |
|
Красители (кг) |
3000 кг |
0.01 кг/м |
0.005 кг/м |
0.008 кг/м |
|
Цена 1 м ткани (руб.) |
45 |
45 |
60 |
|
|
Себестоимость 1 м ткани (руб) |
24 |
20 |
40 |
Задача 9.
Нефтеперерабатывающий завод имеет запасы 4-х полуфабрикатов: алкилата (S1); крекинг-бензина (S2); бензина прямой перегонки (S3) и изопентона (S4). В результате смешивания этих компонентов в различных пропорциях, получают 3 сорта авиационного бензина: A, B и C. Запасы сырья, состав бензина и себестоимость его производства даны в таблице.
|
Запасы сырья (л) |
Состав бензина S1:S2:S3:S4 |
|||||
|
S1 |
S2 |
S3 |
S4 |
A |
B |
C |
|
200000 |
300000 |
300000 |
150000 |
2:3:5:2 |
3:1:2:1 |
2:2:1:3 |
|
Прибыль от 1 тыс. л бензина (руб) |
1200 |
1000 |
1500 |
Определить план производства бензина различных сортов, обеспечивающий максимальную прибыль производства.
Задача 10.
Оптовая база занимается закупкой и продажей сезонного товара. Одновременно на базе может храниться до 100 единиц товара. Закупка товара на очередной квартал осуществляется в конце предыдущего квартала. К началу первого квартала на складе имелось 30 ед. товара. Цены приобретения и продажи единицы товара по кварталам показаны в таблице.
|
Цена приобретения на ... |
Цена продажи в ... |
||||||
|
1 кв. |
2 кв |
3 кв |
4 кв |
1 кв. |
2 кв |
3 кв |
4 кв |
|
350 |
500 |
500 |
400 |
500 |
600 |
750 |
550 |
Сколько требуется покупать и продавать товара за каждый квартал в течение года, чтобы оптовая база получила максимальную прибыль?
Задача 11.
Цех мебельного комбината выпускает трельяжи, трюмо и тумбочки под телевизоры. Норма расхода материала в расчете на одно изделие, плановая себестоимость, оптовая цена предприятия, плановый (месячный) ассортимент и трудоемкость единицы продукции приведены в таблице. Запас древесностружечных плит, досок еловых и березовых 90, 30 и 14 м3 соответственно. Плановый фонд рабочего времени 16800 человеко-часов.
|
Показатели |
Трельяжи |
Трюмо |
Тумбочки |
|
Норма расхода материала, м3 древесностружечные плиты. доски: еловые ...... березовые ..... Трудоемкость, чел.-ч. .... Плановая себестоимость, руб. Оптовая цена предприятия, руб. Плановый ассортимент, шт. . |
0,032 0,020 0,005 10,2 88,81 93,00 350 |
0031 0,020 0,005 7,5 63,98 67,00 290 |
0,038 0.006 0,006 5,8 29,60 30,00 1200 |
Исходя из необходимости выполнения плана по ассортименту и возможности его перевытюлнения по отдельным (или даже всем) показателям построить модель, на основе которой можно получить план производства мебели, максимизирующий прибыль.
Задача 12
Фабрика выпускает кожаные брюки, куртки и пальто специального назначения в ассортименте, заданном отношением 2:1:3. В процессе изготовления изделия проходят три производственных участка—дубильный, раскройный и пошивочный. Фабрика имеет практически неограниченную сырьевую базу, однако сложная технология предъявляет высокие требования к квалификации рабочих. Численность их в рамках планируемого периода ограничена.
Время обработки изделий на каждом участке, их плановая себестоимость, оптовая цена предприятия приведены в таблице.
|
Показатели |
Брюки |
Куртки |
Пальто |
|
Норма времени на участках, чел.-ч дубильном раскройном пошивочном Полная себестоимость, руб. Оптовая цена предприятия, руб. |
0,3 0,4 0,5 15 17,5 |
0,4 0,4 0,4 40,5 42 |
0,6 0,7 0,8 97,8 100 |
Ограничения на фонд времени для дубильного, раскройного, и пошивочного участков составляют соответственно 3360, 2688 и 5040 ч.
Учитывая заданный ассортимент, построить модель, на основе которой определить план производства одежды, максимизирующий прибыль от реализованной продукции.
Задача 13
На приобретение оборудования для нового производственного участка выделено 300 тыс. руб. Его предполагается разместить на площади 45 кв. м. Участок может быть оснащен оборудованием трех видов — машинами стоимостью 6 тыс. руб. (здесь и далее все показатели приводятся на единицу оборудования), размещающимися на площади 9 кв. м, производительностью 8 тыс. единиц продукции за смену; машинами стоимостью 3 тыс. руб., занимающими площадь 4 кв. м, производительностью 4 тыс. единиц продукции за смену; машинами стоимостью 2 тыс. руб. Занимаемая ими площадь 3 кв. м, производительность 3 тыс. единиц продукции.
Построить модель, на основе которой определить план приобретения оборудования, обеспечивающий наибольшую производительность всего участка.
Задача 14
В плановом году строительные организации города переходят к сооружению домов типов Д-1, Д-2, Д-3 и Д-4. Данные о количестве квартир разного типа в каждом из указанных типов домов, их плановая себестоимость приведены в таблице. Годовой план ввода жилой площади составляет соответственно 800, 1000, 900, 2000 и 7000 квартир указанных типов.
|
Показатели |
Д-1 |
Д-2 |
Д-3 |
Д-4 |
|
Типы квартир однокомнатные |
10 |
18 |
20 |
15 |
|
двухкомнатные: смежные |
40 |
20 |
||
|
несмежные |
20 |
60 |
||
|
трехкомнатные. |
60 |
90 |
10 |
|
|
четырехкомнатные |
20 |
10 |
5 |
|
|
Плановая себестоимость, тыс. руб. |
830 |
835 |
360 |
.450 |
На жилищное строительство утвержден объем капиталовложений в размере 40 млн. руб. (часть этих средств, которая не будет использована в плановом году по прямому назначению, предназначена для расширения сети коммунальных предприятий города).
Построить модель и найти план строительства домов на финансовый год, при котором себестоимость всех вводимых домов будет минимальной.
Задача 15
Предприятие выпускает обычный, специальный и декоративный сплавы латуни и реализует их соответственно по 3; 4, 5 и 6 руб. за единицу веса. Его производственная мощность позволяет производить (за плановый период) не более 500 ед. веса обычного сплава, 700 ед. специального и 250 ед. декоративного. Обязательными составляющими сплавов являются медь, цинк, свинец и никель. Их цена соответственно 0,9; 0,7; 0,5 и 1,1 руб. за единицу веса.
По технологии декоративный сплав должен содержать не менее 7% никеля, 49% меди и не более 29% свинца; специальный—не менее 3% никеля, 71% меди, 9% цинка и не более 21% свинца. В обычный сплав составляющие входят без ограничений.
Считая, что себестоимость сплавов складывается только из стоимости его ингредиентов, построить модель, на основе которой найти план выпуска сплавов,- обеспечивающий максимальную прибыль.
Задача 16
На заводе ежемесячно скапливается около 14 т отходов металла, из которого можно штамповать большие и малые шайбы. Месячная потребность завода в больших шайбах 600 тыс. шт., в малых 1100 тыс. шт. Расход металла на тысячу больших шайб 22 кг, на тысячу малых 8 кг. Для изготовления шайб используются два пресса холодной штамповки. Производительность каждого за смену 9 тыс. шт. больших шайб либо 11,5 тыс. шт. малых. Завод работает в две смены.
Недостающее количество шайб закупается. Оптовая цена больших шайб 11,9 руб. (за тысячу штук), а малых 5,2 руб. Определить месячный план производства шайб, обеспечивающий максимальную долю в валовой продукции предприятия.
Задача 17
Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии – 60 изделий, второй линии – 75 изделий. На радиоприемник первой модели расходуется 10 однотипных элементов электронных схем, на радиоприемник второй модели – 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного радиоприемника первой и второй моделей равна 30 и 20 долл., соответственно.
Определить оптимальный суточный объем производства первой и второй моделей.
Задача 18.
Фирма имеет возможность рекламировать свою продукцию, используя местное радио и телевидение. Затраты на рекламу в бюджете фирмы ограничены суммой $ 1000 в месяц. Каждая минута радиорекламы обходится в $ 5, а каждая минута телерекламы – в $ 100. Фирма хотела бы использовать радиосеть, по крайней мере, в два раза чаще, чем телевидение. Опыт прошлых лет показал, что объем сбыта, который обеспечивает каждая минута телерекламы, в 25 раз больше объема сбыта, обеспечиваемого одной минутой радиорекламы.
Определить оптимальное распределение ежемесячно отпускаемых средств между радио- и телерекламой.
Задача 19
Фирма производит два вида продукции – А и В. Объем сбыта продукции А составляет не менее 60% общего объема реализации продукции обоих видов. Для изготовления продукции А и В используется одно и то же сырье, суточный запас которого ограничен величиной 100 кг. Расход сырья на единицу продукции А составляет 2 кг, а на единицу продукции В – 4 кг. Цены продукции А и В равны 20 и 40 долл., соответственно.
Определить оптимальное распределение сырья для изготовления продукции А и В.
Задача 20
Завод получает 4 вида полуфабрикатов Вi в количествах: В1 – 400 т, В2 – 250 т, В3 – 350 т и В4 – 100 т. В результате смешения этих компонентов получают 3 вида продукции Аj. Пропорции смешиваемых полуфабрикатов следующие: для А1 – 2:3:5:2, для А2 – 3:1:2:1, для А3 – 2:2:1:3. Стоимость 1 т продукции Аj составляет: А1 – 1200 руб., А2 – 1000 руб., А3 – 1500 руб.
Составить оптимальный план выпуска продукции по критерию:
1) максимальной стоимости выпущенной продукции;
2) максимального использования полуфабрикатов.
Задача 21. На заготовительный участок поступили стальные прутья длиной 111 см. Необходимо разрезать их на заготовки по 19, 23 и 30 см. Этих заготовок требуется, соответственно, 311, 215 и 190 шт. Построить экстремальную задачу выбора варианта выполнения этой работы, при котором число разрезаемых прутьев минимально.
Задача 22. На заготовительный участок поступило 69 металлических прутьев длиной 107 см. Их необходимо разрезать на заготовки по 13, 15 и 31 см в комплектности, задаваемой отношением 1:4:2. Построить модель, на основе которой можно сформулировать экстремальную задачу максимизации комплектов заготовок.
Указание к задачам 29, 30. Стандартная методика решения задач раскройного типа заключается в составлении возможных вариантов раскроя (для этого оформляется отдельная таблица) и постановке экстремальной задачи выбора их наилучшей комбинации по заданному критерию.
Литература:
Лабораторная работа №5.
по дисциплине «Экономическая информатика».
Тема: Промежуточные итоги и консолидация данных в электронной таблице MS Excel.
Цель: Изучение средств консолидации данных.
Методика выполнения работы:
Под консолидацией понимается объединение данных, представленных в одинаковых по строению таблицах (или их фрагментах), а также в таблицах, состоящих из одинаковых столбцов и разных строк или наоборот.
Для объединения обычно используется функция суммирования соответствующих ячеек (могут использоваться и другие функции - среднее, минимум, максимум и т.п.). Консолидация возможна только в том случае, если смысл задачи допускает, например, суммирование данных в консолидируемых ячейках. Редко встречаются таблицы, которые могут быть консолидированы полностью, чаще всего консолидация возможна только некоторых частей таблицы.
К первому типу таблиц относятся таблицы жесткой структуры, состоящие из одинаковых столбцов и строк, например формы каких-либо отчетов. Такие таблицы могут быть консолидированы по расположению. При консолидации по расположению, выделяют только ту часть таблиц, которая должна быть объединена. Заголовки столбцов и названия строк добавляют путем копирования.
Примером таблиц второго типа может служить расчетная ведомость, которая получается в результате связывания таблиц, расположенных на разных листах рабочей книги. При этом предполагалось, что каждый месяц состав сотрудников не менялся, а в таблице их фамилии располагались в строках с одинаковыми номерами. На практике эти условия трудно выдержать, так как принимаются на работу новые сотрудники, некоторые увольняются, таблица пересортировывается, что ведет, в конечном итоге, к изменению количества строк в таблице и координат конкретных сотрудников. При этом все столбцы расчетной ведомости остаются одинаковыми. Данные таких таблиц можно консолидировать по категориям, а в качестве категорий использовать фамилии сотрудников. В результате могут быть получены итоговые данные по каждому сотруднику, например, за несколько календарных месяцев.
1. Создайте таблицу и сразу сохраните ее под именем Задача5.xls. Снимите защиту с листа, если она установлена.
2. Вставьте новый лист и дайте ему имя Консолидация за 1кв.
Консолидация по расположению
3. Откройте лист Январь и установите курсор на заработную плату первого сотрудника и запомните его координаты(адрес).
4. Сделайте активным лист Консолидация за 1кв и установите курсор в ячейку с теми же координатами. Это место будет левым верхним углом консолидированной таблицы.
5. Войдите в п. Данные - Консолидация и установите консолидирующей функцией функцию Сумма.
6. Установите курсор в строку Ссылка, где будет задаваться диапазон ячеек, затем перейдите на лист Январь, выделите фрагмент таблицы без заголовков, фамилий, итоговой строки и нажмите кнопку Добавить. Выделенный диапазон перенесется в нижнюю часть окна консолидации.
7. Активизируйте лист Февраль. На этом листе вам будет автоматически предложен тот же диапазон ячеек для консолидации. Нажмите кнопку Добавить для включения его в список консолидируемых диапазонов. Аналогичные действия сделайте для листа Март.
8. Таким образом, вы определяете адреса ячеек, подлежащих консолидации. Внизу окна консолидации есть три переключателя, которые для данного примера должны быть сброшены. Если это не так, сбросьте их щелчком мыши.
9. Нажмите кнопку Ok для выполнения операции. На новом листе будет создана таблица без заголовков и фамилий, содержащая суммы соответствующих ячеек консолидируемых диапазонов.
10. Путем копирования через буфер обмена скопируйте заголовки и фамилии. Отформатируйте итоговую таблицу. Сравните ее с данными на листе 1-й квартал, который остался от работы №7. Измените заработную плату нескольким сотрудникам, например в январе, и вновь сравните названные таблицы. Как изменились их данные?
11. Обдумайте полученный результат, а затем сохраните рабочую книгу под именем Задача8.xls.
Консолидация по категориям
12. Сохраните рабочую книгу под именем Задача8-2.xls.
13. Удалите лист 1-й квартал, так как дальнейшие изменения условия задачи приведут к его искажению.
14. Сделайте активным лист Февраль. Выделите табельные номера и фамилии и скопируйте их в буфер обмена. Установите курсор на табельный номер первого сотрудника, войдите в п. Правка-Специальная вставка и вставьте только значения.
Аналогичные действия проделайте на листе Март. Это необходимо для ликвидации ссылок на листы Январь и Февраль, которые остались от работы №7.
15. Перейдите на лист Февраль и добавьте двух новых сотрудников в середину списка. Установите им определенную заработную плату и скопируйте все формулы. Отсортируйте таблицу по фамилиям.
16. Сделайте активным лист Март и внесите в таблицу данные о сотрудниках, принятых на работу в феврале, и добавьте еще несколько новых сотрудников. Затем удалите двух - трех сотрудников, из числа тех, которые были в январе. Отсортируйте таблицу по фамилиям сотрудников.
17. Проанализируйте состав сотрудников на каждом листе рабочей книги - он должен быть разный. При этом желательно, чтобы номера строк у большинства сотрудников, которые повторяются на каждом листе, не совпадали. Таким образом, мы создали ситуацию, которая обычно имеет место на практике. Подобные таблицы консолидировать по расположению невозможно.
18. Добавьте новый лист Консолидация по категориям. Установите курсор в позицию, соответствующую фамилии первого сотрудника.
19. Войдите в п. Данные - Консолидация и установите консолидирующей функцией функцию Сумма.
20. Установите курсор в строку Ссылка, затем перейдите на лист Январь, выделите диапазон, начиная с фамилии и включая всю цифровую часть таблицы (без заголовков и итогов) и нажмите кнопку Добавить. Выделенный диапазон перенесется в нижнюю часть окна консолидации. Внесите в список такие же диапазоны с листов Февраль и Март. Обратите внимание, что они будут разные, так как количество сотрудников на этих листах неодинаковое.
21. Флажок Использовать в качестве имен значения левого столбца включите, остальные выключите.
22. Нажмите кнопку Ok для выполнения операции. На новом листе будет создана таблица без заголовков, но с фамилиями.
23. Путем копирования через буфер обмена скопируйте заголовки. Отформатируйте итоговую таблицу.
24. Проверьте правильность счета по нескольким строкам итоговой таблицы. Обдумайте полученный результат. Сохраните таблицу.
Установление связи с исходными данными
25. Выделите консолидированную таблицу без названий столбцов. Войдите в п. Данные - Консолидация и включите флажок Создавать связи с исходными данными. Нажмите кнопку Ok для выполнения консолидации. Внешний вид таблицы несколько измениться - слева появятся символы структуры со знаком «плюс».
26. Нажмите на один из них - вы увидите все записи, из которых были образованы консолидированные суммы. Проанализируйте полученные результаты. Поменяйте заработную плату нескольким сотрудникам в январе, и проверьте изменились или нет консолидированные данные?
27. Сохраните рабочую книгу в вашей папке.
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа №6
по дисциплине «Экономическая информатика».
Тема: Построение сводной таблицы в и анализ данных с помощью электронной таблицs MS Excel.
Цель: изучение основ построения сводных таблиц.
Методика выполнения работы:
Мастер сводных таблиц представляет собой инструмент, позволяющий на основе таблиц, списков и баз данных произвести сложную обработку информации и представить результат в удобном для восприятия виде. Построение сводных таблиц осуществляется путем перетаскивания нужных элементов в макет сводной таблицы. Благодаря этому создается впечатление, что построение сводных таблиц выполняется чрезвычайно просто. И это на самом деле так, если исходная таблица имеет четкую структуру, а пользователь хорошо представляет себе, что он желает получить в итоге. Поэтому в данной работе рассматривается сводная таблица простейшего вида, чтобы действия пользователя в процессе изучения инструмента были очевидны.
Затем укажите, что сводная таблица создается на новом листе - и вы получите таблицу, показанную ниже.
|
Сумма по полю |
Подразделение |
|||
|
Регион |
Бухгалтерия |
Отдел кадров |
Транспортный |
Общий итог |
|
Кострома |
452 |
464 |
458 |
1373 |
|
Москва |
455 |
413 |
311 |
1178 |
|
Тула |
905 |
308 |
776 |
1988 |
|
Общий итог |
1811 |
1184 |
1544 |
9075 |
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа №7
по дисциплине «Экономическая информатика».
Тема: Проектирование многотабличных баз данных в MS Access.
Цель:.
Ход работы:
Методика выполнения работы:
Задание 1.
1. Создать таблицу «Преподаватели»: Используйте «Мастер таблиц».
· Выбрать для создания таблицы образец «Сотрудники» и выбрать из предложенного перечня полей следующие:
¸ фамилия;
¸ имя;
¸ отчество;
¸ должность;
¸ название отдела (переименовать, как «Кафедра»).
· Назвать созданную таблицу “Преподаватели”, связи не определять, т.е. нажать кнопку «Далее».
· Выбрать режим «Ввести данные непосредственно в таблицу – Готово».
· В режиме конструктора определить тип данных для каждого поля:
· Определить свойства полей на вкладке “Общие” в режиме конструктора следующим образом:
· Для поля «Кафедра» изменить элемент управления следующим образом:
¸ Откройте вкладку «Подстановка» и установите следующие свойства для поля «Кафедра».
Задание 2.
1. Создайте форму для заполнения таблицы «Преподаватели».
· выбрать вкладку «Формы», и создать форму, используя «Мастер форм», определив в качестве источника таблицу «Преподаватели»;
· отметить мышкой все поля для создания формы;
· выбрать вид формы «Ленточная»;
· стиль выбрать на свое усмотрение;
· имя формы «Преподаватели»;
· дальнейшие действия «Открыть форму для просмотра».
2. Заполните форму. Обратите внимание на то, что столбец
«Кафедра» заполняется путем подстановки из сформированного Вами списка.
Вопросы при сдаче лабораторной работы:
7. Какие типы данных вы знаете?
8. Как можно переименовать поле?
9. Как можно создать поле с раскрывающимся списком?
10. С каким расширением сохраняется файл БД Access?
Литература:
Лабораторная работа №8
по дисциплине «Экономическая информатика».
Тема:. Элемент управления типа «Поле со списком»
Цель:
Методика выполнения работы:
Задание 1.
3. Создайте «Форму» для заполнения таблицы «Дисциплины».
Для этого:
· перейдите на вкладку «Формы»;
· нажмите кнопку «Создать»;
· выбрать «Автоформа: ленточная», в качестве «Источника
данных» выберете таблицу «Дисциплины».
4. Введите названия дисциплин, которые читаются на втором
курсе в «Форму».
Задание 2.
1. Создайте таблицу «Экзамен» в режиме конструктора
таблиц с параметрами:
2. Определить Свойства полей на вкладке “Общие” следующим образом:
Задание 3.
1. Для поля «Группа» изменить элемент управления:
· Откройте вкладку «Подстановка» и установите следующие свойства для поля «Группа»:
2. Аналогично изменить элемент управления для полей «Дисциплина» и «Студент», используя в качестве строк соответственно таблицы «Дисциплины» и «Анкета».
3. Для поля «Результат контроля» изменить элемент управления следующим образом:
· Откройте вкладку «Подстановка» и установите следующие свойства для поля «Результат контроля»:
Задание 4.
1. Создайте «Форму» для заполнения таблицы «Экзамен»:
· перейдите на вкладку «Формы»;
· нажмите кнопку «Создать»;
· выбрать «Автоформа: ленточная», в качестве «Источника данных» выберете таблицу «Экзамен».
Задание 5.
1. Пользуясь созданной в Задании 4 формой «Экзамен» введите данные по двум дисциплинам на Ваш выбор по всем студентам своей группы в таблицу «Экзамен». Оценки проставлять произвольно (желательно разнообразные).
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа № 9
Элемент управления типа «Флажок»
Цель работы:
Задание 1.
1. Создайте таблицу «Зачет» в режиме конструктора таблиц с параметрами:
3. Для поля «Группа» изменить элемент управления:
4. Аналогично изменить элемент управления для полей «Дисциплина» и Студент», используя в качестве строк соответственно таблицы «Дисциплины» и «Анкета».
Задание 2.
1. Создайте форму для заполнения таблицы «Зачет»:
· перейдите на вкладку «Формы»;
· нажмите кнопку «Создать»;
· выбрать «Автоформа: ленточная», в качестве «Источника данных» выберете таблицу «Зачет».
2. Пользуясь созданной формой «Зачет» введите данные по всем студентам своей группы по двум дисциплинам на свой выбор.
Зачет отмечайте флажком v.
Контрольные вопросы.
Литература:
Лабораторная работа № 10
Тема: Создание связей между таблицами в MS Access
Цель работы:
Порядок выполнения работы.
Связь между таблицами возможна благодаря наличию в этих таблицах полей с совпадающими значениями данных. В роли таких полей в одной из таблиц выступает ее первичный ключ, а во второй - так называемый внешний ключ. Благодаря механизму связей в Access можно создавать формы, запросы и отчеты, в которых одновременно отображается логически связанная информация из нескольких таблиц.
Таблица состоит из строк и столбцов и имеет уникальное имя в базе данных. База данных содержит множество таблиц, связь между которыми устанавливается с помощью совпадающих полей. MS Access поддерживает четыре типа отношений между таблицами:
Один-к-одному (каждая запись в одной таблице соответствует только одной записи в другой таблице. Один-ко-многим (каждая запись в одной таблице соответствует многим записям в другой таблице).
Много-к-одному (аналогична записи «один-ко-многим).
Много-ко-многим ( одна запись из первой таблицы может быть связана более чем с одной записью из второй таблицы либо одна запись из второй таблицы может быть связана более чем с одной записью из первой таблицы ).
Тип отношения «один-ко-многим» является наиболее общим.
Рассмотрим на конкретном примере, для чего может понадобиться связь между таблицами. Допустим Вы уже по всем группам заполнили таблицы «Экзамен» и «Зачет» полностью и выяснилось, что одна из дисциплин в таблице «Дисциплины» названа неверно, что в свою очередь отразилось на заполнении таблиц «Зачет», либо «Экзамен». Аналогичная ситуация может случиться с фамилией студента и т.д.
Как сделать чтобы, исправив название дисциплины в таблице «Дисциплины», либо изменив фамилию студента в таблице «Анкета» - таблицы «Зачет» и «Экзамен» были бы исправлены автоматически.
Для этого надо установить связи между таблицами:
Задание 1.
Установите связь между таблицами «Зачет» –[ «Дисциплины», «Анкета» ]
1. Для этого выполните следующие действия:
· перейти в режим «Таблицы»;
· если у вас есть открытые таблицы, или формы - закройте их;
· нажмите кнопку «Схема данных» на панели управления;
· пользуясь правой кнопкой мышки, добавьте в схему все таблицы «Зачет», «Анкета», «Дисциплины»;
· закройте окно «Добавление таблицы». В схеме данных должны появиться три схемы выше указанных таблиц;
· поместите указатель мыши на поле «Дисциплины» таблицы «Зачет» и нажмите левую кнопку и, не отпуская ее, перетащите появившийся значок поля на поле «Дисциплина» таблицы «Дисциплины». Появится диалоговое окно «Связи»;
· поставьте флажок «Обеспечение целостности данных»;
· поставьте флажок «Каскадное обновление связанных полей»;
· нажмите кнопку «Объединение»;
· выберете опцию 3 - OK;
· нажмите кнопку «Создать», чтобы подтвердить создание связи;
· перейдите в окно «Схема данных», Вы должны увидеть появившуюся связь «один-ко-многим».
2. Аналогично создайте связь между полем Фамилия таблицы «Анкета» и полем Студент таблицы «Зачет» (последовательность действий та же, что и при установлении связи между таблицами «Дисциплины» – «Зачет», только связываются другие поля).
Задание 2.
Если Вы правильно выполнили задание 1, то:
Задание 3.
1. Установите связь между таблицами «Экзамен» - [«Анкета», «Дисциплины»]. Проверьте, как работают связи.
Вопросы при сдаче лабораторной работы:
1. Для чего предназначены формы?
2. Почему форма является незаменимым средством в БД?
3. С помощью чего можно создавать формы?
4. На основе чего можно создавать формы?
5. Как создать кнопку на форме?
6. Как можно разместить несколько таблиц и запросов на одной форме?
7. Как создать главную кнопочную форму?
Литература:
Лабораторная работа № 11
Тема: Создание запросов в режиме Конструктора.
Цель работы:
Порядок выполнения работы.
Одной из основополагающих частей любой системы управления базами данных является запрос. Запрос можно считать фильтром, который специально сохраняется отдельно от таблицы для дальнейшего использования в других таблицах. Важным отличием любого запроса от фильтра является возможность строить на его основе отчеты и формы, а также выполнять достаточно сложные вычисления.
Задание. 1) Создайте запрос (при сохранении название этого и последующих запросов придумываете самостоятельно): вывести всех клиентов из города Ельца, причем их фамилии должны быть расположены в порядке следования букв в алфавите. 2) Создайте запрос, который выводит модели компьютеров с ценой более 15000 руб., причем цены расположите в порядке убывания. 3) Создайте запрос: вывести все модели компьютеров, которые имеют лазерный диск, причем выводить только поля Модель и Цена. 4) Создайте запрос, выводящий на экран заказчиков, которые приобрели модель компьютера со следующими параметрами: объем жесткого диска больше 30 Gb, а цена меньше 25000 руб. 5) Создайте запрос: вывести всех заказчиков, их ФИО и Телефон, количество приобретенных компьютеров которых превышает 10 штук.
Теперь поупражняемся с базой данных Университет, открыв ее из своего каталога. Все запросы сохраняйте с именем, соответствующим действию запроса.
Задание. 1) Создайте запрос, выводящий номера и фамилии студентов, родившихся после 1 сентября 1983 года. 2) Создайте запрос: вывести фамилии и имена всех мальчиков экономического факультета в алфавитном порядке. 3) Создайте запрос, выводящий фамилию, имя, отчество, ученую степень и ученое звание всех преподавателей с ученой степенью доктор наук и ученым званием профессор. 4) Создайте запрос, в котором покажете список студентов физического факультета – их фамилии и имена, факультет, группу и преподавателя, который ведет на данном факультете. 5) Создайте запрос: вывести фамилию, имя, отчество всех преподавателей юридического факультета, у которых фамилия начинается на букву И (используйте ввод по маске (* - любое количество любых символов)).
Задание. В базе данных Продажа компьютеров создайте запрос, который подсчитывает сумму покупки клиента с учетом количества проданного товара, скидки и цены за единицу.
Задание. Самостоятельно в базе данных Продажа компьютеров создайте запрос, который бы отображал Сумму покупки только тех клиентов, количество заказанных штук которых превышает 10 (Отобразить поля: Ф.И.О. заказчика, Дата заказа, Количество, Сумма заказа).
Задание. Самостоятельно создайте запрос с параметром в базе данных Продажа компьютеров, который выводит ФИО клиента, Количество заказа и Дату заказа за определенный период времени, т.е. при запуске запроса требуется ввести Начало периода и Конец периода. В качестве Условия отбора в столбце Дата заказа введите выражение Between [Начало периода:] And [Конец периода:]. При вводе значений параметров типа Дата желательно контролировать
значения дат, для чего нужно указать тип данных для этого параметра.
Задание. Самостоятельно сформируйте запрос с параметром в базе данных Университет, который выводит в одном столбце ФИО студента, в другом – факультет, причем второе условие будет параметром.
Задание. Самостоятельно сформируйте запрос с параметром в базе данных Университет, который выводит Фамилию преподавателя, его Ученую степень и факультет, с учетом того, что при запуске запроса предлагается ввести дату рождения преподавателя начиная с некоторого определенного значения. Например, вывести данные о преподавателях, родившихся после 01.01.70.
Задание. Самостоятельно создайте таблицу из запроса базы данных Университет, в которую поместите следующие поля: ФИО студента, Дата рождения.
Задание. 1) Для наглядного представления рассматриваемого вида запроса самостоятельно в базу данных Университет в таблицу Студенты введите запись с новым (последующим) Номером студента, а остальные реквизиты – Фамилия, Имя, Отчество – возьмите из уже имеющихся, т.е. имитируйте повторяющуюся запись. 2) Создайте запрос, который отыскивает повторяющиеся записи по фамилии, имени и отчеству.
Задание. Аналогично создайте запрос в базе данных Университет на поиск повторяющихся записей в таблице Преподаватели по фамилии и имени.
Задание. Самостоятельно замените в базе данных Университет в таблице Преподаватели все значения Математический на Физико-математический.
Задание. Самостоятельно удалите с помощью запроса на удаление в базе данных Университет из таблицы Преподаватели все записи с фамилией Петров.
Задание. В базе данных Продажа компьютеров создать запрос, который подсчитывает общую сумму покупки для каждого заказчика (заказчики в таблице Клиенты должны повторяться для более наглядного воспроизведения результатов запроса). Следует отметить, что запрос, как и таблица, может быть источником данных для другого запроса.
Задание. Самостоятельно в базе данных Университет создайте запрос, который подсчитывает количество студентов по каждому факультету.
Контрольное задание. Создайте и заполните произвольно пятью записями базу данных «Книжный магазин», которая включает в себя следующие таблицы:
Авторы (Код автора, Фамилия, Имя, Отчество)
Издательства (Код издательства, Издательство)
Тематические разделы (Код раздела, Раздел)
Издания (Код издания, Код раздела, Код автора, Название книги, Год издания, Код издательства, Число страниц, Цена) – 15 записей!
Покупатели (Код покупателя, Покупатель, Скидка)
Заказы (Код заказа, Дата заказа, Код издания, Код покупателя, Количество)
Установите связи между таблицами. Используйте подстановки!!!
Создайте и сохраните следующие запросы:
вывести данные о всех издательствах (Код издательства, Издательство);
вывести данные об изданиях, цена которых менее 100 руб. (Код издания, Раздел, Фамилия автора, Название книги, Цена);
вывести данные о всех имеющихся в магазине изданиях (Код издания, Раздел, Ф.И.О. автора, Название книги, Издательство, Год издания, Число страниц, Цена);
вывести данные о покупателях, которые не имеют скидки (Код покупателя, Покупатель);
вывести данные о всех заказах, которые были оформлены с 01.10.2002 по 01.11.2002 (Код заказа, Дата заказа, Название книги, Автор, Количество, Покупатель);
вывести данные о сумме покупок клиентов (Код заказа, Покупатель, Название книги, Фамилия автора, Цена, Количество, Скидка, Сумма покупки);
вывести данные о заказах за определенную дату, которая вводится при запуске запроса (запрос с параметром) (Код заказа, Дата заказа, Фамилия автора, Название книги, Покупатель, Количество);
вывести данные о количестве книг по разделам (Раздел, Количество книг).
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа № 12
Тема: Запрос на выборку. Критерии отбора записей.
Цель работы:
Порядок выполнения работы.
Понятие запроса
При работе с таблицами можно в любой момент выбрать из базы данных необходимую информацию с помощью запросов.
Запрос - это обращение к БД для поиска или изменения в базе данных информации, соответствующей заданным критериям.
С помощью Access могут быть созданы следующие типы запросов: запросы на выборку, запросы на изменение, перекрестные запросы, запросы с параметром.
Одним из наиболее распространенных запросов является запрос на выборку, который выполняет отбор данных из одной или нескольких таблиц по заданным пользователем критериям, не приводящий к изменениям в самой базе данных.
Создание запроса
Для начала создания запроса следует открыть базу данных, и, перейдя на вкладку Запросы нажать кнопку Создать. Появится окно Новый запрос для выбора способа построения запроса ( рис 1).
Рис. 1.Диалоговое окно Новый запрос
Конструктор - создает запрос на основе пустого бланка запроса.
Простой запрос - создает простой запрос из определенных полей.
Перекрестный запрос - создает запрос, данные в котором имеют компактный формат, подобный формату сводных таблиц в Excel.
Повторяющиеся записи – создает запрос, выбирающий повторяющие записи из таблицы или простого запроса.
Записи без подчиненных - создает запрос, выбирающий из таблицы записи, не связанные с записями из другой таблицы.
При выборе Конструктора через диалоговое окно Добавление таблицы (рис. 2).добавляются имена таблиц в окно конструктора запроса
Рис 2. Окно Добавление таблицы
Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов, предлагаемых программой для проектирования запроса: Таблицы, Запросы и Таблицы и запросы. При выборе вкладки Таблицы следует выделить нужную нам таблицу из предложенного списка и с помощью кнопки Добавить можно добавить несколько таблиц. Например, на рис.2 выбрана таблица Студенты.
Имена таблиц должны быть представлены в окне конструктора запроса (рис 3).
Окно конструктора запроса
Окно конструктора (рис.3) разделено на две части. В верхней части находятся окна таблиц со списками полей. Имя каждой таблицы отображается в строке заголовка такого окна.
Рис 3. Окно конструктора запроса. Пример ввода условия.
Например, на рис.3 представлены таблицы Студенты и Студенты и занятия.
Нижняя часть является бланком запроса, или, как его называют, QBE – областью (Query by Example – запрос по образцу). Здесь указываются параметры запроса и данные, которые нужно отобрать, а также определяется способ их отображения на экране.
Для перемещения из верхней панели окна в нижнюю и обратно используется клавиша F6.
Включение полей в запрос
В запрос не следует включать все поля выбранных таблиц.
Добавить нужные поля в бланк запроса можно путем перетаскивания их имен из списка, находящегося в верхней части окна конструктора в строку бланка Поле.
Еще один способ – двойной щелчок по имени поля.
Например, на рис.3 в бланк запроса включены поля Фамилия, Имя и Город из таблицы Студент.
Примечание:
Если был установлен флажок Имена таблиц из меню Вид, то во второй строке бланка QBE выйдет на экран имя таблицы, из которой выбрано поле (см. рис.3).
В строке Вывод на экран флажком помечаются те поля, которые должны быть выведены на экран. В общем случае поля, вводимые в наборе записей запроса, наследуют свойства, заданные для соответствующих полей таблицы.Можно определить другие значения свойств, выполнив команду Свойства из меню Вид: Описание (текст, содержащий описание объекта), Формат поля (представление данных на экране), Число десятичных знаков (для числовых данных, Маска ввода, Подпись (заголовок столбца).
Удалить поле из бланка запроса можно клавишей [Delete] или через меню Правка командой Удалить столбцы. Чтобы удалить таблицу, следует маркировать ее в верхней части окна конструктора запроса, выполнив щелчок по имени, и нажать [Delete] или в меню Правка командой Удалить.
Установка критериев отбора записей
При создании запроса можно задать критерии, вследствие чего по запросу будет осуществлен отбор только нужных записей.
Чтобы найти записи с конкретным значением в каком либо поле, нужно ввести это значение в данное поле в строке бланка QBE Условие отбора (см. рис.3).
Критерии, устанавливаемые в QBE – области, должны быть заключены в кавычки. Если ACCESS 97 идентифицирует введенные символы как критерии отбора, то заключает их в кавычки автоматически, а если нет, то сообщает о синтаксической ошибке.
Например, как показано на рис.3, построен запрос, по которому из данных по баллам будут выбраны фамилии и имена студентов с оценками только 4 и 5.
Виды критериев
Для создания запроса с несколькими критериями пользуются различными операторами.
Логическая операция или
Можно задать несколько условий отбора, соединенных логическим оператором или (or), для некоторого поля одним из двух способов:
1) можно ввести все условия в одну ячейку строки Условие отбора, соединив их логическим оператором или (or). В этом случае будут выбраны данные, удовлетворяющие хотя бы одному из условий.
Например, запись
4 or 5
соответствует тому, что будут выбраны фамилии с оценками 4 или 5.
2) ввести второе условие в отдельную ячейку строки или. И если используется несколько строк или, то чтобы запись была выбрана, достаточно выполнения условий хотя бы в одной из строк или, как, например, показано на рис. 4.
Рис 4. Пример записи условия с использованием оператора или (or).
При такой записи условия также будут выбраны фамилии с оценками 4 или 5.
6.3.2. Логическая операция и
Логическая операция и (and) используется в том случае, когда должны быть выполнены оба условия и только в этом случае запись будет выбрана.
Например, записав условие
>2 and <5
будут выбраны только оценки 3 и 4.
Чтобы объединить несколько условий отбора оператором и (and), следует привести их в одной строке.
Например, на рис 5 показано как можно задать условие для выбора фамилий студентов, проживающих в городе Уфе, которые учатся только на 5.
Рис 5. Пример записи условия с использованием оператора и (and ).
Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий
< > 4
В этом случае можно не использовать кавычки.
Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или.
Оператор Between
Оператор Between позволяет задать диапазон значений, например:
between 10 and 20
Оператор In позволяет задавать используемый для сравнения список значений. Например:
in (“первый”,”второй”,”третий”)
Оператор Like
Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:
* — обозначает любое количество ( включая нулевой) символов;
? — любой одиночный символ;
# — указывает что в данной позиции должна быть цифра.
Например: для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать
like П*ов
Операторы для даты и времени
Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например:
#10 мая 1998#
>#31.12.96#
В Access используется ряд других функций, которые помогут задать условия отбора для даты и времени, например:
Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31
Month(дата) – возвращает значение месяца года в диапазоне от 1 до 12
Year(дата) – возвращает значение года в диапазоне от 100 до 9999
Сортировка данных в запросе
Данные можно упорядочить по возрастанию или убыванию.
Для упорядочения отобранных записей целесообразно воспользоваться возможностями сортировки самого запроса в строке Сортировка окна конструктора запроса (см рис 3).
Вычисляемые поля
Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе.
Для этого в строке Поле бланка QBE вводится формула для вычисления, причем имена полей заключаются в квадратные скобки.
Например: =[ Оклад]*0.15
В выражениях можно использовать следующие операторы:
- арифметические: * умножение; + сложение; - вычитание; / деление; ^ возведение в степень;
- соединение частей текста при помощи знака & , например:
=[ Фамилия] & “ “&[Имя]
В кавычки заключен пробел для того, чтобы запись не была слитной.
Использование построителя выражений
При создании выражений для вычисляемых полей можно использовать Построитель выражений . Для этого нужно щелкнуть по пустому полю в бланке QBE, а затем по кнопке панели инструментов Построить,
при этом откроется окно Построитель выражений (рис. 6 ).
Рис.6. Окно построителя выражений
В верхней части окна расположена пустая область ввода, в которой создается выражение. Можно самим ввести выражение, но проще использовать различные кнопки, расположенные под областью ввода.
Для начала нужно щелкнуть дважды в левом списке по папке Таблицы, и выбрать саму таблицу ,а затем в колонке Код само поле и щелкнуть по кнопке Вставить.
Например, как показано на рис 6, была выбрана таблица Сотрудники.
Все имена объектов, из которых строится выражение для вычисления, заключены в квадратные скобки, причем перед именем поля может стоять восклицательный знак ( ! ) разделяющий его и имя таблицы.
Ошибку при составлении выражения можно отменить , щелкнув по кнопке Отмена.
В результате произведенных действий получится выражение, например как показано на рисунке. 6 задано выражение для поля, которое будет состоять из суммы оклада и премии сотрудника.
Если щелкнуть по кнопке ОК , то полученный результат будет перенесен в бланк QBE.
Итоговые запросы
Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа:
- поля, по которым осуществляется группировка данных;
- поля, для которых проводятся вычисления.
Для составления итогового запроса, находясь в режиме конструктора, следует нажать кнопку Групповые операции на панели инструментов или воспользоваться командой Групповые операции из меню Вид.
В результате чего в бланке запроса появится строка Групповая операция. Если для соответствующего поля из списка выбрать функцию Группировка (рис 7), то при выполнении запроса записи по этому полю группируются по значениям в этом поле , но итог не подводится.
Группировка в итоговом запросе производится только по одному полю. Во всех остальных полях вводятся итоговые функции.
Рис 7. Строка Групповая операция в бланке QBE
Access предоставляет ряд функций, обеспечивающих выполнение групповых операций. Можно задать нужную функцию, набрав на клавиатуре ее имя в строке, Групповая операция или выбрав ее из раскрывающегося списка.
Основные групповые функции, которыми можно воспользоваться:
SUМ - вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом;
AVG - вычисляет среднее значение в тех записях определенного поля, которые отобраны запросом ( для числовых или денежных полей);
MIN - выбирает минимальное значение в записях определенного поля, отобранных запросом;
MAX – выбирает максимальное значение в записях определенного поля, отобранных запросом;
COUNT – вычисляет количество записей, отобранных запросом в определенном поле, в которых значения данного поля отличны от нуля;
FIRST - определяет первое значение в указанном поле записей;
LAST -. определяет последнее значение в указанном поле записей.
Выполнение запроса
Готовый запрос выполняется после щелчка по кнопке панели инструментов в режиме Конструктора запросов или при активизации команды Запуск из меню Запрос. В результате будет получена таблица с ответом на заданные условия . Например, на рис 8 показан результат запроса, построенного на рис. 3.
Рис.8. Результат выполненного запроса
6.8. Запросы к нескольким таблицам
Запросы можно создавать для отбора данных как из одной, так и из нескольких таблиц. Запросы к нескольким таблицам производятся аналогично запросам к однотабличным БД с той лишь разницей, что в окно конструктора запроса добавляются все таблицы, данные которых нужны в запросе.
При этом следует учитывать наличие связей между таблицами (см. лаб. раб. по созданию многотабличной БД ).
На рис. 3 представлены две таблицы Студенты и Студенты и занятия, где показана связь один-ко-многим.
МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ
Создать простой запрос - выбрать несколько произвольных полей из таблицы Студенты.
С помощью Конструктора создать запросы, удовлетворяющие условиям:
- единственное значение факультета;
- два различных факультета;
- фамилии студентов, начинающиеся с определенной буквы (использовать шаблоны);
- фамилии студентов, заканчивающиеся на “ов”;
- фамилии студентов одного факультета и одного курса;
- фамилии и имена студентов, проживающие в одном из городов или обучающиеся на одном из факультетов;
- фамилии студентов, у которых стипендия больше 400 рублей;
- фамилии студентов, занимающиеся не в 1-ой группе и стипендия которых в пределах от 200 до 500 р.
Примечание:
В запрос должны быть включены поля Фамилия , Имя, Отчество и те поля, где вводятся критерии.
После того как был задан критерий для запроса, запрос нужно выполнить и сохранить под именем, подходящим по смыслу.
Для запросов с полем типа Дата/время добавить поле Дата рождения и выбрать записи, удовлетворяющие условиям:
- дата больше 1.1.80;
- дата в интервале значений и задан факультет;
- фамилии и имена студентов, родившихся в 80-х годах;
- вычислить возраст студентов;
- фамилии и имена студентов, родившихся в первой половине месяца;
Создать итоговый запрос:
- оставить в запросе поля Факультет, Стипендия, Номер зачетки, вычислить максимальное значение стипендии для каждого факультета и подсчитать количество студентов на каждом факультете (используя Count).
Запрос с вычисляемыми полями:
- включить в запрос вычисляемое поле, которое является результатом сцепления текстовых полей Фамилия, Имя, Отчество. Назваить поле Ф. И. О. студента.
- используя построитель выражений, подсчитать надбавку студентам, равную 15% от стипендии;
Создать запрос, в котором используются поля из двух ранее созданных и связанных таблиц, задав ему имя Запрос для 2-х таблиц
– убрать несколько полей таблицы Студенты и добавить поля Предмет и Оценка из таблицы Успеваемость;
– выбрать поле Фамилия, предмет и Оценка, вычислить минимальное значение по полю Оценка;
– сгруппировать по номеру зачетки и вычислить среднюю оценку для каждого студента.
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа № 13
Тема: Параметрические запросы.
Цель работы
Порядок выполнения работы.
Параметрический запрос
Как правило, запросы с параметром создаются в тех случаях, когда предполагается выполнять этот запрос многократно, изменяя лишь условия отбора. В отличие от запроса на выборку, где для каждого условия отбора создается свой запрос и все эти запросы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный
запрос и вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз получая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые будут выведены в запросе. Значение параметра задается в специальном диалоговом окне. В случае, когда значение выводимых данных должно быть больше или меньше указываемого значения параметра, в поле "Условие отбора" бланка запроса перед параметром, заключенным в квадратные скобки ставится соответствующий знак. Можно также создавать запрос с несколькими параметрами, которые связываются друг с другом логическими операциями "И" и "ИЛИ". В момент запуска запроса на выполнение MS Access отобразит на экране диалоговое окно для каждого из параметров. Помимо определения параметра в бланке запроса, необходимо указать с помощью команды Параметры (меню Запрос) соответствующий ему тип данных:
1. Откройте в режиме Конструктора окно запроса и добавьте в него таблицу. Создайте запрос, "перетащив" необходимые поля в бланк запроса и задав условие выбора.
2. В качестве условия введите параметр, заключенный в квадратные скобки (например, [Введите название] или >[Выше какого роста?]).
3. Выберите команду Параметры (меню Запрос).
4. В появившемся окне ПАРАМЕТРЫ ЗАПРОСА введите без квадратных скобок параметр (для точности ввода воспользуйтесь "быстрыми" клавишами копирования и вставки из буфера обмена) и укажите соответствующий ему тип данных. Нажмите ОК.
5. Нажмите кнопку ЗАПУСК панели инструментов.
6. В появившемся окне укажите значение параметра.
7. Результат запроса будет содержать только те записи, которые удовлетворяют заданному значению параметра.
Вопросы при сдаче лабораторной работы:
Литература:
Лабораторная работа №14
по дисциплине «Экономическая информатика».
Тема: Создание вычисляемых полей в запросах. Использование построителя выражений.
Цель:
Ход работы
Вычисления в запросах
Запрос можно использовать для выполнения расчетов и подведения итогов из исходных таблиц.
Для создания вычисляемых полей используются математические и строковые операторы. При этом Access проверяет синтаксис выражения и автоматически вставляет следующие символы:
квадратные скобки ([]), в них заключаются имена элементов управления;.
знаки номеров (#), в них заключаются распознанные даты;
кавычки (""), в них заключается текст, не содержащий пробелов или знаков пунктуации.
Выражения, определяемые пользователем, дают возможность выполнять действия с числами, датами и текстовыми значениями в каждой записи с использованием данных из одного или нескольких полей. Например, обычное выражение позволяет найти разность значений двух полей типа даты, соединять несколько строковых значений в текстовом поле или умножить значения одного поля на итоговое значение.
Поле, содержимое которого является результатом расчета по содержимому других полей, называется вычисляемым полем. Вычисляемое поле существует только в результирующей таблице. Общий формат вычисляемого поля выглядит так:
Имя вычисляемого поля: Выражение для создания вычисляемого поля.
Примеры: Прибыль:[Доход]-[Расход]
Цена со скидкой:[Цена]-[Цена]*0,1
Для расчетов с использованием формул, определяемых пользователем, требуется создать новое вычисляемое поле прямо в бланке запроса путем простого ввода выражения для вычисления в ячейку «Поле» пустого столбца бланка запроса.
После выполнения запроса вычисляемое поле, основанное на этом выражении, выводит на экран результат вычислений, а не само выражение.
1. В строку «Поле» пустого столбца бланка запроса введите выражение, начинающееся со знака «=» и состоящее из имен полей, записанных в квадратные скобки и какой-либо арифметической или другой операции.
2. После выполнения запроса в результирующей таблице появится новое поле с названием «Выражение1», используемым в качестве имени вычисления выражения.
3. В режиме конструктора запроса измените имя «Выражение1» на более значимое.
Для того чтобы ввести сложные вычисления используйте окно ПОСТРОИТЕЛЬ ВЫРАЖЕНИЙ, которое вызывается нажатием кнопки ПОСТРОИТЬ панели инструментов, либо соответствующей командой контекстного меню. Построитель выражений облегчает создание выражений, позволяя выбирать его составляющие элементы (арифметические операции, встроенные функции, названия полей имеющихся в БД таблиц и запросов и т.п.) при помощи кнопок и списков.
Результаты вычислений также могут быть использованы в условиях отбора для определения записей, которые выбираются в запросе, или для определения записей, над которыми производятся какие-либо действия.
Например, следующее выражение в ячейке строки Условие отбора позволяет отбирать в запросе только те записи, которые в поле «Дата продажи» имеют значение, попадающее в интервал между текущей датой и датой, отстоящей от нее на один месяц, т.е данные за последний месяц.
Between Date() And DateAdd("m",1,Date())
Запросы позволяют производить итоговые вычисления. Для этих целей в Access 97 предусмотрены статистические функции SQL. Статистическую функцию задают в строке Групповая операция бланка запросов, которая появляется при выполнении команды Вид Групповые операции. Заполняя ячейки в строке Групповая операция, можно выполнить расчеты для групп записей и вычислить сумму, среднее, количество или другой тип итогового значения для вычисляемого поля.
|
Функция SQL |
Действие |
|
Sum |
Суммирование значений определенного поля |
|
Avg |
Вычисление среднего значения данных определенного поля |
|
Min |
Вычисление минимального значения поля |
|
Max |
Вычисление максимального значения поля |
|
Count |
Вычисление количества записей, отобранных запросом по условию |
|
First |
Определяется первое значение в указанном поле записей, отобранных запросом |
|
Last |
Определяется последнее значение в указанном поле записей, отобранных запросом |
|
StDev |
Вычисляется стандартное отклонение значений данного поля, для всех записей, отобранных запросом |
|
Var |
Вычисляется вариация значений данного поля для всех записей, отобранных запросом |
Для выполнения запроса на итоговое вычисление:
1. Находясь в режиме Конструктора Запроса, выберите команду Групповая операция (меню Вид) или нажмите кнопку ГРУППОВАЯ ОПЕРАЦИЯ панели инструментов. В результате чего в бланке запроса появится строка «Групповая операция».
2. Для соответствующего поля выберите нужную функцию из списка «Группировка».
Вопросы при сдаче лабораторной работы:
1) Способы активизации окон Delphi.
2) Свойства Left, Top, Width и Height формы окна Object Inspertor .
3) Как сохранить проект и как исполнить готовый проект.
4)Что такое визуальное программирование и чем оно отличается от обычного?
Литература:
Список лабораторных работ
Лабораторная работа №15
по дисциплине «Экономическая информатика».
Тема: Форматирование форм в MS Access.
Цель:
Ход работы:
Создание формы
Формы Access 97 позволяют создавать пользовательский интерфейс для таблиц базы данных. Хотя для выполнения тех же самых функций можно использовать режим таблицы, формы предоставляют преимущества для представления данных в упорядоченном и привлекательном виде.
Форма представляет собой некий электронный бланк, в котором имеются поля для ввода данных. В Форме каждое поле можно разместить в точно заданном месте, выбрать для него цвет и заливку. В Форму можно помещать вычисляемые поля. OLE-обьекты можно увидеть только в форме или отчете. В Форме намного проще работать с большими текстами поля типа МЕМО в текстовом окне с полосами прокрутки.
Форма строится на основе Access-таблицы или запроса. При каждом открытии сохраненной формы обновляются данные запроса, на основе которого создается форма. Благодаря этому содержимое Формы всегда соответствует информации в таблицах и запросах.
Формы могут быть выведены на экран в трех видах: режим конструктора, режим формы и режим таблицы. Для перехода из одного режима в другой используются команды меню Вид или кнопка Вид панели инструментов.
Microsoft Access предоставляет быстрый способ создания формы на основе таблицы с использованием Мастера Форм. Он задает пользователю вопросы о структуре и оформлении формы. Результатом диалога
пользователя и Мастера Форм является «готовая к употреблению» форма.
Для создания формы самостоятельно без помощи Мастера Форм:
1. В окне БАЗА ДАННЫХ щелкните на вкладке ФОРМА.
2. В открывшемся окне щелкните на кнопке Создать. Появится окно НОВАЯ ФОРМА.
3. Если форма создается на основе таблицы, то выберите имя таблицы, являющейся источником данных формы.
4. Щелкните на кнопке КОНСТРУКТОР. При открытии окно конструктора ФОРМА содержит одну область – область данных. Помимо этого, форма может содержать область заголовка, примечания и колонтитулов (верхнего и нижнего). Для добавления этих областей используются команды Заголовок/примечание формы и Колонтитулы (меню Вид).
(Данные, размещаемые в заголовке, в области данных и в области примечания, отображаются в форме. Области колонтитулов отображаются только при печати.)
5. Для индикации инструментального окна выберите команду Панель элементов (меню Вид).
Выберите команду Список полей (меню Вид) для выбора полей, которые будут включены в форму. Разместите поля в области данных. Поля, добавляемые таким способом, состоят из надписи и поля для ввода данных. Если выделить надпись или само поле, то ко второму элементу автоматически добавляется манипулятор перемещения и можно перемещать их в паре или по отдельности. В случае, когда нет необходимости в выводе надписи поля рядом с самим полем, удалить ее можно следующим образом: выделить объект Надпись и нажать клавишу Delete.
Формы для связанных таблиц
В таких формах можно одновременно отобразить информацию из двух (или более) связанных таблиц. Кроме того, такая форма позволяет выполнить редактирование данных, содержащихся в обеих таблицах.
В результате создания этой формы на экране выводятся только те записи подчиненной таблицы, которые связаны с текущей записью исходной (главной) таблицы:
1.Выберите пиктограмму НОВЫЙ ОБЪЕКТ: АВТОФОРМА.
2.В появившемся окне НОВАЯ ФОРМА выберите режим МАСТЕР ФОРМ.
4. В следующем окне выберите переключатель Подчиненные формы.
5. Далее выберите вид подчиненной формы.
6. Далее выберите стиль формы.
7. Озаглавьте главную и подчиненную формы и нажмите кнопку ГОТОВО.
Для просмотра записей главной формы используются кнопки просмотра в нижней части окна. Выше нее выводится строка для просмотра записей подчиненной формы, которые представлены в виде таблицы.
Вопросы при сдаче лабораторной работы:
Литература:
Список лабораторных работ
Лабораторная работа №16
по дисциплине «Экономическая информатика».
Тема: Построение кнопочных форм в MS Access.
Цель
Ход работы:
Для создания кнопочного меню выполните следующие действия:
Примечание. Диспетчер напрямую может связать кнопку с открытием формы или отчета. Чтобы открыть таблицу или запрос, надо создать соответствующий макрос и указать это в диспетчере.
Примечания. Для возврата из любой открытой формы или отчета в меню достаточно закрыть их.
Как в формах, так и в отчетах помимо информации из БД можно отображать и дополнительную информацию. Окно формы может содержать следующие элементы: подписи, поля, поля со списком, списки, выключатели, переключатели, флажки и кнопки. Кроме того, форму (отчет) можно дополнить иллюстрацией (рисунком или диаграммой), текстом и линиями различного типа. Для оформления форм (отчетов) также может быть использована возможность изменения начертания, стиля и выравнивания данных, которые отображаются в полях, а также цвета символов, фона и границы [панель инструментов Формат (Форма/Отчет)].
Создание элементов окна осуществляется в режиме Конструктора.
Для индикации инструментального окна выберите команду Панель
элементов (меню Вид). Каждая пиктограмма этой панели предназначена для создания элемента определенного типа. Назначение каждой пиктограммы однозначно определяется их наименованиями.
Существует три основных типа элементов управления: присоединенные, свободные, вычисляемые.
Присоединенные элементы управления - элементы, связанные с полем таблицы. При вводе значения в присоединенный элемент управления поле таблицы в текущей записи автоматически обновляется. Большинство элементов управления, в том числе обьекты OLE, можно присоединить к полю. Чаще всего присоединенные элементы управления содержат данные текстового типа, а также даты, числа, логические данные (Да/Нет), рисунки и поля МЕМО.
Свободные элементы управления сохраняют введенную величину, не обновляя при этом поля таблицы. Их можно использовать для отображения: текста; значений, которые должны быть переданы макросам; линий и прямоугольников. Кроме того, их можно использовать для хранения обьектов OLE (например рисунков), которые расположены не в таблице, а в самой форме.
Свободные элементы управления называют также переменными или переменными памяти.
Вычисляемые элементы управления создают на основе выражений, например, функций или формул. Поскольку они не присоединены к полям таблицы, они не обновляют содержание полей таблицы. Этот элемент управления позволяет производить необходимые вычисления, используя данные полей таблицы, с последующим отображением в форме.
Выбор объектов — позволяет изменить указатель курсора на инструмент выбора объекта.
Мастера элементов — позволяет включать и отключать мастера по созданию элементов управления.
Надпись — предназначена для вывода на экран не изменяющегося текста, например, заголовков, подписей или пояснений. Надпись относится к свободным элементам управления, в которые нельзя вводить данные.
Поле — позволяет создать область для отображения, ввода или изменения данных.
В поле можно использовать данные любого типа: текст, числа, дата/время, логические величины и МЕМО. Поля могут быть как присоединенными, так и свободными. В них можно использовать поля
из таблиц или запросов, а также вычисляемые выражения, поэтому такие элементы управления называют связанными полями. При создании связанного поля вместе с ним одновременно образуется еще один элемент управления - присоединенная надпись.
Группа параметров — позволяет создать область настраиваемого размера для размещения набора флажков, переключателей или выключателей, представляющих набор альтернативных значений.
Выключатель — позволяет создать кнопку, связанную с логическим полем. Элемент может находиться в двух состояниях: Истина — кнопка нажата, Ложь — кнопка отжата.
Переключатель — предназначен для создания кнопки (называемой радиокнопкой). Ее функции аналогичны функциям выключателя. Элемент находится в двух состояниях: Истина — кружок с точкой, Ложь — пустой кружок. С кнопкой можно связать команды, например, выполняющие фильтрацию.
Флажок — предназначен для создания флажка связанного с логическим полем. Действуют аналогично переключателям, но в отличие от них, допускают множественный выбор. Элемент может находится в двух состояниях: Истина — квадрат с галочкой, Ложь — пустой квадрат.
Поле со списком — позволяет создать составной элемент управления, объединяющий поле и раскрывающийся список значений. Для ввода значения, можно ввести значение в поле или выбрать значение в списке.
Список — позволяет создать список, допускающий прокрутку, и предназначенный для выбора значения. Позволяет отображать список значений в форме или отчете. В списках можно также отображать заголовки столбцов.
Кнопка — позволяет создать кнопку, используемую для выполнения набора макрокоманд Access или процедур VBA.
Рисунок — позволяет создать рамку, в которой в форме или отчете выводится неизменяемый рисунок. Поскольку рисунок не является объектом OLE, то после помещения рисунка в форму или отчет не допускается его изменение из Microsoft Access.
Свободная рамка объекта — позволяет создать рамку для отображения в форме или отчете объектов OLE, как правило, набор иллюстраций или диаграмму. Рамка не связана ни с каким полем таблиц базы данных.
Присоединенная рамка объекта —для отображения в форме или
объектов OLE, таких как набор иллюстраций или диаграммы. С присоединенной рамкой связано одно из полей таблиц. При переходе от записи к записи в форме или отчете выводятся разные объекты.
Конец страницы — позволяет создать элемент управления, указывающий принтеру начало новой страницы в печатной форме или новой страницы в отчете. Этот элемент управления не появляется в форме или запросе в режиме формы.
Вкладка — позволяет вставить элемент управления Вкладка для создания вложенных форм. Страницы элемента управления Вкладка могут содержать другие элементы управления.
Подчиненная форма/отчет — предназначена для добавления в основную форму или основной отчет подчиненной формы или подчиненного отчета соответственно. Добавляемые подчиненная форма или подчиненный отчет должны существовать.
Линия — позволяет создать прямую линию, которую можно перемещать и размеры которой можно изменять. Цвет и толщину линии можно изменить с помощью кнопок панели инструментов Панель форматирования или окна свойств. Используется для разделения элементов формы или отчета.
Прямоугольник — позволяет создать прямоугольник, который можно перемещать и размеры которого можно изменять. Используется для выделения элементов формы.
Дополнительные элементы — выбор этой кнопки открывает список дополнительных элементов управления ActiveX, которые можно использовать в формах и отчетах.
Для создания элемента управления: текста, поля, линии, прямоугольника (рамки), кнопки и др.:
1. Щелкните на соответствующей пиктограмме.
2. Укажите курсором мыши (крест с уменьшенным изображением создаваемого элемента) место для создаваемого элемента.
После того, как будет отпущена кнопка мыши для создания некоторых элементов (таких как, например, поле со списком или кнопка) Access выводит на экран Мастер. Так, после создания кнопки появляется Мастер, предлагающий выбрать тип действия, которое будет привязано к этой кнопке (переходы между записями, работа с формой или другие типы, например, работа с запросами в пункте "Разное").
Внешний вид, структура и режимы работы отдельных управляющих
элементов определяются значениями характеристик этих объектов (команда Свойства, меню Вид).
Вопросы при сдаче лабораторной работы:
Литература:
Глинский Я.М. “TURBO PASCAL i DELPHI”.
Список лабораторных работ
Лабораторная работа №17
по дисциплине «Экономическая информатика».
Тема: Создание отчетов с помощью мастеров в MS Access.
Цель
Ход работы:
Общие положения
Отчеты представляют собой наилучшее средство представления информации из базы данных в виде печатного документа. По сравнению с другими методами вывода данных на печать отчеты обладают двумя принципиальными преимуществами:
– отчеты предоставляют широкие возможности для группировки и вычисления промежуточных и общих итогов для больших наборов данных;
– отчеты могут быть использованы для получения красиво оформленных материалов, наклеек и других документов.
В основном отчеты применяются для просмотра «картины в целом», поэтому часто базируются на многотабличных запросах.
Основные сведения об отчетах
Рис. 1 Вид окна Новый отчет
Для создания отчета в окне базы данных выбираем пункт Отчет в раскрывающемся списке кнопки Новый объект на панели инструментов, или кнопку Создать на вкладке Отчеты . Access выведет на экран окно диалога Новый отчет, представленное на рис.1.
Access показывает имя выбранного источника в поле со списком в нижней части окна диалога.
Выбор мастера по разработке отчетов
В окне диалога Новый отчет можно выбрать один из шести вариантов.
Конструктор позволяет создавать отчет «с нуля» и редактировать уже созданный отчет.
Мастер отчетов запускает основного мастера для создания отчетов, позволяющего выбрать поля для отчета, задать форматы, условия группировки и итоговые функции.
Автоотчет: в столбец этот мастер создает отчет, в котором каждая запись базового запроса или таблицы представлена в виде простого списка, содержащего названия и значения полей.
Автоотчет: ленточный в отчете этого типа данные записи базового запроса или таблицы выводятся в одной строке. Если таблицы базового запроса связаны отношением «один-ко-многим», то мастер создает группу для данных, поступающих со стороны отношения «один», но не вычисляет никаких итоговых значений. При создании отчета используется последний стиль, который был выбран в мастере отчетов или применен с помощью автоформата в конструкторе отчетов.
Мастер диаграмм этот мастер помогает построить диаграмму и создает в отчете свободную рамку объекта OLE с внедренной диаграммой Microsoft Graph.
Почтовые наклейки этот мастер позволяет выбрать поля с именами и адресами, отформатировать их и создать отчет для печати почтовых наклеек. Вы можете выбрать любой тип и настроить размер наклеек.
Разделы отчета
Пустой макет отчета содержит разделы Верхний колонтитул, Нижний колонтитул, между которыми находится Область данных.
Добавить или удалить верхний и нижний колонтитулы можно с помощью команды Вид - Колонтитулы. При создании отчета можно задать заголовок, который будет напечатан только в самом начале отчета, и примечание, выводимое в конце отчета. Чтобы добавить в отчет эти разделы, выберите команду Вид Заголовок/примечание отчета.
Линейка с делениями по верхнему и левому краям окна отчета помогают планировать расположение данных на странице. Изменить размер любого отдела можно, перетащив его границу. В каждом разделе Access выводит сетку.
Окно отчета и его инструменты
Панель элементов содержит все кнопки для всех элементов управления, которые можно использовать при создании отчета. Это поля, надписи, рисунки, флажки и т.д. Чтобы открыть эту панель нужно выбрать команду Вид Панель элементов, либо щелкнуть на кнопке Панель элементов на панели инструментов. Кнопка Другие элементы предоставляет доступ ко всем элементам управления ActiveX (в том числе к элементу управления Календарь) установленным в системе. Чтобы разместить в отчете конкретный элемент управления, щелкните на соответствующей кнопке на панели элементов, нажмите кнопку мыши, протащите указатель мыши до того места, где должен быть элемент, и отпустите кнопку.
Список полей упрощает размещение в отчете присоединенных элементов управления (элементов управления, связанных с полями таблицы или запроса).Он открывается щелчком на кнопке Список полей на панели инструментов или выбором команды Вид - Список полей. В строке заголовка списка полей Access выводит имя базовой таблицы или запроса. Чтобы создать присоединенный элемент управления с помощью списка полей, выберете его тип, щелкнув на соответствующей кнопке на панели элементов. Затем перетащите поле из списка в нужное место отчета. Если кнопка не выбрана, типом элемента управления по умолчанию является Поле
В окне свойств можно установить свойства самого отчета, его разделов и элементов управления. Набор свойств, представленных в нем зависит от объекта. Чтобы открыть окно свойств, выделяем нужный объект и щелкаем на кнопке Свойства на панели инструментов или выбираем команду Вид Свойства.
Рис. 2. Окно отчета.
Сортировка и группировка
Отчеты представляют возможность группировать выводимую информацию с помощью окна Сортировка и группировка. Чтобы открыть его, щелкните на кнопку Сортировка и группировка на панели инструментов или выберите команду ВидСортировка и группировка. В окне Сортировка и группировка можно определить до 10 полей или выражений, которые будут использоваться в отчете для группировки данных. Первый элемент списка определяет основную группу, а последующие подгруппы внутри групп.
Рис 3. Окно Сортировка и группировка.
В первой строке столбца Поле/выражение раскрывается список полей базовой таблицы или запроса. Начав ввод со знака равенства(=), можно ввести в этом столбце выражение, содержащее ссылку на любое поле базовой таблицы или запроса.
По умолчанию Access сортирует значения по возрастанию. Для изменения порядка сортировки выберите значение По убыванию в раскрывающемся списке, который появляется после щелчка в ячейке столбца Порядок сортировки.
В отчете должно быть зарезервировано место для заголовка каждой группы и примечания, в котором могут выводиться вычисляемые итоговые поля. Чтобы добавить в отчет эти разделы, установите для свойств Заголовок группы и Примечание группы значение Да. Кроме того, можно попросить Access выводить все детальные данные для данного уровня группировки на одной странице (если это возможно), выбрав для того же свойства значение Вся группа.
Задание диапазонов группировки
Для каждого поля или выражения, заданного в верхней части окна сортировки и группировки, можно установить свойства Группировка и Интервал. Обычно данные группируются по каждому значению поля или выражения. Но записи можно группировать не только по отдельным значениям, но и по диапазонам значений. Вид и размер диапазона зависят от типа данных поля или выражения.
При группировке по текстовому полю можно начинать новую группу при изменении первого или нескольких начальных символов значения поля. Например, чтобы образовать по одной группе для каждой буквы алфавита, определите создание новой группы при изменении первой буквы значения поля – таким образом, вы получите группу записей, в которых значение поля начинается с буквы А, группу записей со значением поля, начинающегося с буквы Б, и т.д. Чтобы задать группировку по первым символам, установите для свойства Группировка значение По первым знакам, а в ячейке свойства Интервал введите число начальных символов, по которым вы хотите образовывать группы.
Для чисел можно установить свойство Группировка в значение Интервал. В этом случае свойство Интервал определяет диапазоны группировки. Access вычисляет диапазоны, начиная с 0. Если задать интервал, равный 10, то будут формироваться группы значений от –20 до –11, от –10 до –1, от 0 до 9, от 10 до 19, от 20 до 29 и т.д.
Для полей даты/времени можно установить в качестве значения свойства Группировка такие единицы измерения как По годам, По кварталам, По месяцам, По неделям, По дням, По часам, По минутам. Для свойства Интервал укажите число единиц измерения, если вы хотите проводить группировку по определенному отрезку времени. Например, если вы хотите создать группы с интервалом в два года, установите для свойства Группировка значение По годам, а в ячейке свойства Интервал введите 2.
Использование вычисляемых значений
Обширные возможности отчетов Access во многом определяются их способностью выполнять простые и сложные вычисления с данными базовых таблиц или запросов. Access предоставляет десятки встроенных функций, предназначенных для обработки данных или для добавления информации в отчет.
Добавление текущей даты и номера страницы
Для дат Microsoft Access предоставляет две встроенные функции, которые можно использовать для включения в отчет текущей даты и времени. Функция Date возвращает текущую системную дату и время в виде значения даты/времени.
Чтобы добавить текущую дату в отчет, создайте свободное поле в верхнем колонтитуле и введите выражение = Now() в ячейке его свойства Данные, а для свойства Формат поля установите значение длинный формат даты. Вернитесь в отчет и введите с клавиатуры в надпись поля содержательный текст или вовсе удалите ее.
Чтобы добавить номер страницы, используйте свойство отчета Page. Вы не увидите его в окне свойств, так как оно поддерживается Access на системном уровне. Кроме того, в Access существует свойство Pages, содержащее общее число страниц в отчете. Чтобы добавить в отчет номер текущей страницы, создайте свободное поле и в ячейке его свойства Данные введите выражение = “Page ” & [Page] & “ of ” & [Pages]
Вычисления в области данных
В качестве значения свойства Данные любого элемента управления, позволяющего выводить данные, можно задать выражения любой сложности с использованием арифметических операторов. Кроме того, в вычислениях можно использовать любые встроенные функции, а также функции, которые вы сами определили в модуле. При желании вы можете воспользоваться помощью построителя выражений.
Чтобы сообщить Access, что в свойстве Данные вы хотите использовать выражение, начните его ввод со знака равенства(=).
Например, чтобы разместить в области данных поле Надбавка, выберите на панели элементов кнопку Поле и укажите ее размещение в области данных. Вызовите окно Свойств и в свойстве Данные введите = [Стипендия]*0,5, т.е. Надбавка составляет 50% для всех записей.
6.3.3 Вычисление промежуточных итогов для групп и общего итога.
Еще одна задача, которую обычно приходиться решать при составлении отчетов, это получение итоговых величин по группам данных. Для получения промежуточных итоговых значений для групп в эти разделы можно поместить дополнительные свободные элементы управления, в которых используется выражение, построенное с применением итоговых функций: Sum, Min, Max, Avg, Count, First, Last, StDev и Var.
Выражение в разделе группы, использующее итоговую функцию, не должно ссылаться на элемент управления, размещенный в области данных. Так, например Вам не удастся создать вычисляемое поле в области данных и сослаться на него в выражении ,использующем итоговую функцию. Но вы можете повторить вычисляемое выражение в итоговом поле. Например, если вычисляемое поле в области данных с именем Цена содержит выражение = [Цена за единицу]*[Количество], то в разделе примечаний группы вы должны использовать выражение =Sum([Цена за единицу]*[Количество]), а не = Sum([Цена]).
Используя раздел примечаний отчета, можно получить общие итоги по всем записям для любого поля или выражения. В примечании отчета, как и в примечаниях групп, допустимы любые итоговые функции.
Объединение текстовых значений и скрытие повторяющихся значений
Когда в нескольких соседних строках отображаются одинаковые значения, отчет трудно читается и выглядит непрофессионально. Свойства поля Не выводить повторы (оно доступно только в отчетах) позволяет управлять выводам повторяющихся значений.
Иногда, не выводя повторы, мы можем скрыть нужные данные. Чтобы этого избежать, можно объединить значения соответствующих полей в одном элементе управления. Для объединения служит символ амперсанда(&), обозначающий операцию конкатенации при объединении текстовых значений. Например, =Format([Номер студенческого билета];”000000”)&” ”&[Фамилия]. Функция Format возвращает ровно шесть цифр. Вторая текстовая строка в этом выражении добавляет несколько пробелов между двумя полями данных.
Вычисление процентов
В любом отчете, группирующем и суммирующем данные, можно определить долю внешнего промежуточного или общего итога, приходящуюся на конкретную сумму. Это возможно в силу того, что Access дважды просматривает данные отчета. При первом проходе Access вычисляет выражения в детальных строках, итоговые значения для групп, общие итоги для всего отчета и определяют длину отчета, а при втором вычисляет все ыражения, ссылающиеся на итоги, рассчитанные во время первого прохода. Следовательно в области данных и разделах, содержащих итоги для групп, можем создать выражение для вычисления процентов от общих сумм или промежуточных сумм, расчитанных для группировки более высокого уровня. Создав выражение для нахождения процентов от суммы, устанавливаем свойство Формат поля в значение Процентный. В режиме просмотра можем убедиться,что в итогах по группе отразится процент от общего итога.
Вычисление итогов с накоплением
Наряду с вычислением итогов в разделах групп можно создавать итоги с накоплением в области данных. Свойство Сумма с накоплением любого числового поля, размещенного в области данных, позволяет вычислять итоги, которые обнуляются в начале каждой группы или накапливаются на протяжении всего отчета. Разместив в области данных поле, установите для него свойства Данные – значения поля, для которого вычисляется сумма, а для свойства Сумма с накоплением - значение Для группы.
Создание и внедрение подчиненных отчетов
Вывод в отчете детальных данных из нескольких таблиц, связанных отношением один-ко-многим, не представляет особого труда. Вы можете внедрять в область данных создаваемого отчета подчиненные отчеты для вывода нескольких детальных строк из таблицы или запроса, находящегося на стороне "многие" по отношению к текущей строке в области данных.
Для создания подчиненного отчета в окне базы данных выделите подчиненную таблицу или запрос и выберите пункт Отчет в раскрывающемся списке кнопки Новый объект на панели инструментов. В окне диалога Новый отчет выберите любой пункт и откройте отчет в нужном режиме. Обычно в подчиненном отчете не бывает колонтитулов.
Откройте основной отчет, т.е. тот, в который вы хотите внедрить подчиненный отчет, в области данных создайте элемент управления типа "подчиненный отчет". В качестве значения свойства Объект-источник введите имя отчета, который нужно внедрить, задайте связанные поля, т.е. выберите значения свойств Основные поля и Подчиненные поля. Кроме того, нужно установить для свойств расширение и сжатие значение Да, чтобы Access мог расширять или сжимать подчиненный отчет. Чтобы настроить размер внедренного отчета, выберите команду ФорматРазмерПо размеру данных для этого элемента.
Упростить внедрение подчиненного отчета можно с помощью Мастера подчиненных отчетов и форм. Все необходимые параметры Мастер запрашивает в ходе диалога. Чтобы начать работу Мастера нажмите кнопку , а затем кнопку на панели элементов.
Настройка отчетов
Чтобы изменить размер выделенного элемента управления, можно воспользоваться маркерами изменения размера, находящимися на сторонах, в нижних углах и в правом верхнем углу. Для перемещения невыделенного элемента управления используйте указатель мыши.
Если элемент управления имеет присоединенную надпись, она перемещается вместе с ним. перемещать элемент управления и присоединенную надпись можно независимо друг от друга с помощью маркеров перемещения, расположенных в левых верхних углах элемента управления и надписи.
Для вывода данных с помощью отчетов мы должны быть уверены, что элементы управления имеют достаточный размер для размещения данных. В Access есть такая команда, подбирающая размер надписей в соответствии с длиной и высотой текста - Формат - Размер - По размеру данных.
Для выравнивания элементов управления по краям или узлам сетки в меню Формат существует команда Выровнять.
Просмотр готового отчета
Режим предварительного просмотра предназначен для того, чтобы посмотреть как будет выглядеть отчет на печати, т.е. редактирование в этом режиме невозможно. Окно предварительного просмотра можно расширить, чтобы вывести на экран большую часть отчета. Для перемещения по окну можно использовать горизонтальную и вертикальную полосы прокрутки, различные масштабы.
Для просмотра других страниц отчета пользуйтесь полем номера страницы и кнопками перехода , которые расположены в нижней части окна просмотра.
Окно просмотра также позволяет просмотреть несколько страниц одновременно с помощью кнопки Несколько страниц.
Методические рекомендации
– В окне БД перейти на вкладку Отчеты и нажать кнопку Создать;
– В качестве таблицы для создания отчета указать таблицу Студенты;
– Выбрать режим созданияМастер отчетов;
– На первом шаге Мастера отчетов включить в отчет поля Фамилия Имя Факультет Курс Группа Стипендия;
– На втором шаге задать уровень группировки по полю Факультет;
– На третьем шаге указать порядок сортировки записей в отчете: в первую очередь по полю Курс, во вторую по полю Фамилия;
– Нажать кнопку Итоги 1/4 и выбрать в качестве итога сумму по полю Стипендия;
– На четвертом и пятом шагах выбрать стиль оформления отчета;
– На последнем шаге указать имя отчета «Начисление стипендий по факультетам».
3 Просмотреть отчет на экране, анализируя его содержимое (группировка записей, вид итогов, элементы страницы отчета и т.д.).
4 Перейти в режим Конструктора данного отчета (нажав кнопку Конструктор на панели инструментов) и произвести в отчете следующие изменения:
– Отредактировать подпись в примечании группы Факультет таким образом, чтобы она приняла вид:
Итого стипендий по факультету [Название факультета]
– Удалить название итогового поля Sum;
– Перенести само итоговое поле на уровень его подписи.
5 Отредактированный отчет вновь просмотреть на экране и сохранить под именем Начисление стипендий.
6 Создать базовый запрос к таблицам Студенты Экзамены, включив в него поля Номер зачетки Фамилия, Предмет, Оценка. Сохранить запрос под именем Данные для отчета по успеваемости.
7 Создать отчет № 2 на основе запроса Данные для отчета по успеваемости:
– В качестве режима создания выбрать Мастер отчетов;
– На первом шаге Мастера выбрать все поля исходного запроса для включения в отчет;
– На втором шаге выбрать тип представления данных по таблице Студенты;
– На третьем шаге добавить уровень группировки по полю Номер зачетки;
– На четвертом шаге указать сортировку по полю Предмет и Вид итогов Среднее (Avg) по полю Оценка;
– На последующих шагах Мастера задать стиль оформления отчета и его название Результаты экзаменационной сессии.
8 Просмотреть отчет на экране, проанализировать его содержание (группировка, итоговые поля, области отчета).
9 Перейти в режим Конструктора для данного отчета и отредактировать его так, чтобы:
– В примечании группы заголовок был таким:
Средний балл за текущую сессию;
– Результат в итоговом поле находился непосредственно рядом с заголовком.
10 Добавить новое вычисляемое поле Надбавка. Для этого
– В области верхнего колонтитула разместите надпись Надбавка;
– В области данных разместите свободное поле и удалите присоединенную надпись для него;
– Вызовите окно свойств и с помощью пунктов Построителя выражений Функции управления в строке Данные введите =IIf([Avg _ Оценка]=5;150;IIf([Avg _ Оценка]>4 Or [Avg _ Оценка]=4;100;50)), т.е. надбавка начисляется в зависимости от результатов сессии.
11 Добавьте в базовый запрос Данные для отчета по успеваемости. Поле Стипендия.
12 Добавьте новое поле Удержания, учитывая что удерживается 10% от значения Стипендии
13 Для значения Свойства Не выводить повторы полей Номер студенческого билета,Фамилия и установите Да.
14 В окне сортировки и группировки по полю Оценка задайте группировку по полному значению и Сортировку по убыванию, а для поля Стипендия группировку по интервалу 10 и сортировку по возрастанию.
15 Добавить в область заголовка любой графический рисунок, например C:\WINDOWS\WINLOGO.GIF
16 Создать ленточный автоотчет по таблице Экзамены и сохранить его с именем Экзамены
17 Создать ленточный автотчет по таблице Студенты.
18 Отрыть его в режиме Конструктора и отредактировать его следующим образом:
– На панели элементов включить режим мастера и нажать кнопку Подчиненная форма/отчет;
– Укажите указателем мыши место для размещения подчиненного отчета в области данных;
– На первом шаге Мастера создания Подчиненных форм и отчетов для определения нового объекта укажите отчет Экзамены
– На втором шаге самостоятельно укажите поле связи “Номер студенческого билета”
– На 3 шаге сохраните подчиненный отчет с именем Результаты экзаменов
– Отредактируйте полученный отчет таким образом, чтобы все данные о студенте разместились на 1 листе.
19 С помощью мастера Диаграмм построить диаграмму Microsoft Graph по таблице Студенты:
– на первом шаге из доступных полей выбираем поля для диаграммы (Фамилия, Стипендия);
– на 2-м шаге выбираем тип диаграммы;
– на 3-м шаге выбираем тип отображения данных на диаграмме: для осей выбираем поле Фамилия, для данных – поле Стипендия (Итоги отсутствуют);
– на 4-м шаге сохраняем диаграмму с именем Стипендия студентов;
– в режиме Конструктора можно редактировать диаграмму двойным щелчком по любой области
Вопросы при сдаче лабораторной работы:
1. Что представляют собой отчеты в Access?
2. В чем их преимущества над другими методами вывода данных?
3. С чего начинается создание отчета?
4. Какие существуют мастера для создания отчета?
5. В чем отличие автоотчетов от других способов создания отчета?
6. Чем характеризуется режим Конструктора?
7. Назовите разделы отчета.
8. Назовите инструменты окна отчета.
9. В каком разделе отчета выводятся итоги по группе?
10. В поле какого типа можно задать группировку по интервалу? По годам?
Литература:
Лабораторная работа №18
по дисциплине «Экономическая информатика».
Тема: Создание и редактирование отчетов с помощью конструктора в MS Access.
Цель
Вопросы при сдаче лабораторной работы:
Литература:
Список лабораторных работ
Лист
№ докум.
Подпись
Дата
Лист
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
зм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
КФ ОГУ 080100.90.12.03.15 ТО
Разраб.
Морковина Ю
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Решение оптимизационных задач в Excel с помощью инструмента.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
КФ ОГУ 080100.90.12.03.15 ТО
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
1
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
1
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
8
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
3
Изм.
Лист
№ докум.
Подпись
Дата
Лист
3
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
1
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
2
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
1
Разраб.
Провер.
Скачкова Л.П.
Реценз.
Н. Контр.
Утверд.
Лит.
Листов
КФ ГОУ ОГУ
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист
Изм.
Лист
№ докум.
Подпись
Дата
Лист